Excel毛利率公式及成本利潤分析:樞紐分析表模版

Excel樞紐分析表可以快速彙總資料,本文以銷貨毛利明細表為例,介紹搭配LEFT函數取得類別編碼,編製不同月份的產品別營收占比及毛利率,便於分析管理。

財務報表結帳完成之後,只有損益表和資產負債表並不足夠,它總結了財務狀況和經營績效,卻沒有提供管理決策上任何參考指標,必須再進行財務分析,其中最主要的,便是產品別銷貨毛利。公司的營業收入,來自於產品銷售,公司的利潤所得,也是來自於產品銷售,因此瞭解各項產品的毛利情形,至關重要。在此介紹如何善用Excel作這方面的管理報表。

一、營業成本明細表

簡單範例的兩個的銷貨成本明細表。

Excel毛利率公式及成本利潤分析:樞紐分析表模版 33

二、產品別收入分析

類似像這樣的資料,最適合跑樞紐分析表,例如彙總出兩個月的產品別收入統計。

Excel毛利率公式及成本利潤分析:樞紐分析表模版 35

三、LEFT函數首碼

料號首碼是產品類別,套個公式:「=LEFT(B13,1)」,將每個料號的產品別抓出來。

Excel毛利率公式及成本利潤分析:樞紐分析表模版 37

四、營收報表Excel

再跑一次樞紐,或者直接重新整理即可,將列標籤裡的「產品」改成上一步驟的「產品別」,就變成是產品大類別的月份收入統計。

關於樞紐分析表欄位配置,可參考贊贊小屋相關文章

Excel毛利率公式及成本利潤分析:樞紐分析表模版 39

五、樞紐版面配置

像這種情況,並不需要兩個月的總計(列總計),將游標在樞紐分析表範圍內任意點選一下,上方功能區會出現「樞紐分析表工具」,依序再選擇:「設計」、「總計」、「僅開啓欄」。原本預設是「開啓列與欄」,現在等於是關閉列總計。

關於樞紐分析表版面配置,可參考贊贊小屋相關文章

Excel毛利率公式及成本利潤分析:樞紐分析表模版 41

六、營收占比計算

為了便利說明,依照A、B產品資料,複製出C、D產品,一起加到樞紐彙總。在分析表後面加個公式:「=B3/B$7」,第7行是總計欄,用「$」固定起來,從D欄往右拉到E欄、再往下拉到7行,得到各產品當月的佔營收比。

Excel毛利率公式及成本利潤分析:樞紐分析表模版 43

七、產品別毛利表

按住「Ctrl」不放,同時按住上一步驟的工作表,往右邊拖曳,便可以快速複製工作表,在新工作表上的分析表,把欄位清單裡的「收入」改成「毛利」。

Excel毛利率公式及成本利潤分析:樞紐分析表模版 45

八、成本利潤分析表

將上一步驟的分析表,貼到第六步驟的分析表旁邊。收入跟毛利擺在一起後,Excel樞紐分析表默認按字母排序,所以兩個表同一行會是同類產品,在毛利彙總表後面輸入公式:「=G8/B8」,拉完之後,便架好了毛利率。如此,能夠很清楚看出兩個月份,產品別的營收比重及毛利率變化情形。

Excel毛利率公式及成本利潤分析:樞紐分析表模版 47

銷售組合營收毛利分析

這篇文章所介紹的彙總方式,特別適用於產品銷售組合的毛利率分析,因為所有要的資訊,營收金額、營收佔比、毛利金額、毛利率、以及兩個月的總金額、總毛利率,全都在同一張工作表上了,非常一目瞭然。只要把這個表架好,很容易可以看出產品別的比重變化,並且能分析出因此造成的毛利差異。

學習清單:贊贊小屋成本會計Excel文章合輯