Excel：邊際貢獻分析表 - 會計

Excel：邊際貢獻分析表

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

網誌圖文版：

http://www.b88104069.com/archives/2461

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

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

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

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

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

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

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

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

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

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

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


延伸閱讀：

建立樞紐分析表：

http://www.b88104069.com/archives/2448

價量分析：

http://www.b88104069.com/archives/2347



--

會計人的Excel小教室：

https://www.facebook.com/acctexcel


--