Excel成本分析表範例：損益兩平與變動邊際貢獻
目錄
Excel樞紐分析表和VLOOKUP函數是職場應用兩大工具。本文以銷貨毛利及存貨成本為例，介紹如何計算變動成本及邊際毛利，再編製成本差異分析的管理追踪報表。
上一節範例得到了哪些料號有異常，需要進一步分析說明。實務上進行分析時，往往需要將存貨料號展開到料工費的明細，再配合製造命令或製造工單瞭解到更細部的差異，而既然已經有存貨成本的細項，也許大分類為變動成本及非變動成本，確認邊際貢獻毛利以決定是否繼續生產銷售，或者找出成本滾算過程中較為原始的投入差異。這一節便以範例介紹在成本分析過程中，如何有效運用Excel相關的指令和函數。
一、損益兩平點Excel
標準格式的銷貨毛利表，欄位有：品號、數量、收入、成本、毛利。有了這張表，很清楚這個月那些品號賺了、哪些品號虧了、哪些品號損益兩平。
二、變動成本公式
標準格式的存貨期末成本表，欄位有：品號、材料、人工、加工、製費、總成本，最後面黃色那一欄，是系統報表外手工加的變動成本：「=F2-E2」，用意是製費以外的成本要素，都當作是變動成本，金額會隨著出貨量增加。實務上應當會有更為複雜、更為細項的區別，這裡為了範例說明起見，予以簡化，重點在於固定成本和變動成本有必要區分開來。
三、毛利邊際貢獻
變動成本是只有生產銷售才會發生的成本，非變動成本則是不論是否生產銷售、都會發生的成本，因此在考慮是否生產銷售時，應當依據邊際毛利判斷、而非毛利。
四、成本分析表範例
想針對負毛利品號進一步分析，取得公司ERP系統的製令生產明細表，由於原始報表在同一製令的每一行領料明細，都有全部的已生產量，在彙總樞紐的時候會造成產量重覆而失真，所以後面加一欄「產量」：「=IF(A2=A1,0,C2)」，表示如果和上一行製令相同，產量便掛零，如果不同，便去抓「已生產量」，把公式往下拉便達到目的了，一張製令只會有一行總產量。
五、建立樞紐分析表
建立樞紐分析表，欄位清單的設置如圖所示，關於欄位配置的詳細說明法可參考贊贊小屋相關文章。
六、樞紐分析表補充
先為了建立樞紐分析表時不失真，將產量改為每張製令一筆總數，現在樞紐完成了，為了讓報表好看，又想讓每個完工品號都有當期產量，輸入公式：「=IF(C2=0,E1,C2)」，意思是如果產量為零，那就帶出上一列數量，如果不為零，那就帶出不為零的那個產量，公式往下拉，果然是我們要的樣子。A欄產品品號有類似狀況，也可以使用同樣的公式處理，讓每一列都有品號。
七、存貨成本明細
先前第二步驟有提到存貨期末成本表，通常庫存存貨都會有加權平均計算出來的成本，成品和材料都一樣，在此簡單假設各材料的成本明細。
八、VLOOKUP成本
「=VLOOKUP(B2,七!B:C,2,0)」，依照材料品號將成本明細帶過來，VLOOKUP是會計人函數基本功，因此贊贊小屋有蠻多相關的文章分享，有興趣可以參考看看。
九、三個月平均成本
材料成本因為缺料加班趕工、品質不良重工等因素，有可能單月波動大，為了有個較為客觀的實際成本，有些情況喜歡便用期間平均值，例如抓三個月平均成本，首先是先跑出三個月的存貨期末成本表，這裡也是較為簡化的範例，實際報表的資料筆數會相當多。
十、樞紐分析表計數
先前贊贊小屋文章有提到樞紐分析表加總與計數的設定，實務上比較少情況會用到計數的值欄位設定，這裡的範例剛好用上。抓一個期間計數，再抓一個總成本，總成本除以期間個數便是平均成本，也就是評估用的標準成本。
十一、成本分析表格
有了標準成本明細表，同樣用Vlookup帶過來，再設一些簡單的差異比較公式：「=F2-G2」、「=H2/G2」這能看出各品號當月成本相對於前三個月平均的標準成本而言，是多了還是少了。此處是看總成本，如果想再細一點，還能就材料、人工、加工、製費作差異比較。其實只要原始資料充足，想得到的大概就做得出來，問題只是好不好分析其原因而已。
Excel成本分析表範例應用
這一節主要是以材料成本進行某個角度的分析，因為公司的製程大多會有好幾層，所謂的材料有可能是最原始的採購原料，也有可能是中間製程再投入的半成品，只是在系統皆是以單一製程的角度稱之為原料和成品。再以邊際貢獻毛利為例，假使沒有成本會計的基礎知識，也許較不容易理解總成本和變動成本的概念區分。另外每家公司的狀況和產品不同，有可能需要以不同方式執行成本分析，在此是以簡單案例作示範，重點在於如何綜合運用Excel指令函數，讀者可作為參考並應用在各自的實務案例中。
學習清單：贊贊小屋成本會計Excel文章合輯。
延伸閱讀：