贊贊小屋

Excel如何編製邊際貢獻分析表

很樂意有讀者來信,因為教學相長,自己的工作範圍難免有限,別人提出問題,我也從中得到啓發。一般我希望每周能寫一篇,最多就一篇,因為我還有其它非工作的文章想寫呀。這個星期比較特別,多弄了一篇Excel,原因就是有所啓發。在這裡想分享的,是如何用Excel編製邊際貢獻分析表:

一、標準配備的銷貨毛利表:品號、數量、收入、成本、毛利。有了這張表,很清楚這個月那些品號賺了、哪些品號虧了、哪些品號損益兩平。

銷貨毛利表

二、標準配備的存貨期末成本表:品號、材料、人工、加工、製費,後面黃色那一欄,是我手工加的變動成本:「=F2-E2」,用意是製費以外的成本要素,都當作是變動成本,金額會隨著出貨量增加。

存貨期末成本表

三、原來的銷貨毛利表插入兩欄,也就是黃色的部份,變動成本的公式:「=VLOOKUP(B2,存貨成本!A:G,7,0)*C2」,意思是依照品號將單位成本乘以數量,另一個邊際毛利的公式:「=D2-F2」,意思是收入減掉變動成本,這麼一來,看出有些品號毛利是負的,但邊際毛利是零,至少不是負數,有些品號毛利是零,但邊際毛利是正的,這些於成本分析上,都有實質意義。

=VLOOKUP(B2,存貨成本!A:G,7,0)*C2

四、想針對負毛利品號進一步分析,跑出系統的製令生產明細表,由於原始報表在同一製令的每一列領料明細,都有全部的已生產量,這樣在彙總樞紐的時候,會造成產量重覆而失真,所以後面加一欄:「=IF(A2=A1,0,C2)」,表示如果和上一列同樣的製令,產量便掛零,如果不同,便去抓「已生產量」,把公式往下拉,達到目的了,一張製令只會有一列總產量。

=IF(A2=A1,0,C2)

五、彙總樞紐,在這裡不再詳細說明欄位清單,有興趣可參考我之前文章:《Excel:建立樞紐分析表》

彙總樞紐

六、先為了彙總樞紐不失真,將產量改為每張製令一筆總數,現在樞紐彙總完,為了讓報表好看,又想讓每個完工品號都有當期產量,輸入公式:「=IF(D3=0,F2,D3)」,意思是如果產量為零,那就帶出上一列數量,如果不為零,那就帶出不為零的那個產量,公式往下拉,果然是我們要的樣子。注意產品品號有類似的狀況,可以使用同樣的公式處理,讓每一列都有品號。

=IF(D3=0,F2,D3)

七、先前第二點有提到存貨期末成本表,一般正常只要是庫存存貨,就是有加權平均計算出來的成本,成品和材料都一樣,在這裡簡單假設各材料的成本明細。

各材料的成本明細

八、「=VLOOKUP(C3,材料成本!B:C,2,0)」,依照材料品號將成本明細帶過來,這是會計人函數基本款。

=VLOOKUP(C3,材料成本!B:C,2,0)

九、材料的成本因為缺料加班趕工、品質不良重工等因素,有可能單月波動很大,為了有個比較客觀的實際成本,部份主管喜歡便用期間平均值,例如,抓三個月的平均成本,首先是先跑出三個月的存貨期末成本表。

三個月的存貨期末成本表

十、在《Excel:建立樞紐分析表》中提到,少部份情況會用到計數的值欄位設定,這個例子剛好用上,抓一個期間計數,再抓一個總成本,把總成本除以期間個數,不就得出了平均成本,也就是假設的標準成本。

總成本除以期間個數

十一、有了標準成本明細表,同樣用Vlookup帶過來,再設一些簡單的差異比較公式:「=G3-H3」、「=I3/H3」這能看出各品號相對於標準成本而言,是多出了還是減少了,此處是看總成本,如果想再細一點,還能就材料、人工、加工、製費作差異比較。其實只要想得到的,大概就做得出來,問題只是能不能分析而已。

差異比較

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1