Excel結合樞紐分析表及VLOOKUP,編製邊際貢獻分析表

目錄

Excel樞紐分析表和VLOOKUP函數是職場應用兩大工具。本文以銷貨毛利及存貨成本為例,介紹如何計算變動成本及邊際毛利,再編製成本差異分析的管理追踪報表。

上一節範例得到了哪些料號有異常,需要進一步分析說明。實務上進行分析時,往往需要將存貨料號展開到料工費的明細,再配合製造命令或製造工單瞭解到更細部的差異,而既然已經有存貨成本的細項,也許大分類為變動成本及非變動成本,確認邊際貢獻毛利以決定是否繼續生產銷售,或者找出成本滾算過程中較為原始的投入差異。這一節便以範例介紹在成本分析過程中,如何有效運用Excel相關的指令和函數。

一、銷貨毛利明細表

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

二、存貨期末成本表

標準格式的存貨期末成本表,欄位有:品號、材料、人工、加工、製費、總成本,最後面黃色那一欄,是系統報表外手工加的變動成本:「=F2-E2」,用意是製費以外的成本要素,都當作是變動成本,金額會隨著出貨量增加。實務上應當會有更為複雜、更為細項的區別,這裡為了範例說明起見,予以簡化,重點在於固定成本和變動成本有必要區分開來。

三、VLOOKUP函數公式

在第一步驟的銷貨毛利表插入兩欄,如圖黃色部份,變動成本的公式:「=VLOOKUP(A2,二!A:G,7,0)*B2」,意思是依照品號將單位成本乘以數量,另一個邊際毛利的公式:「=C2-E2」,意思是收入減掉變動成本,如此可看出有些品號毛利是負的,但邊際毛利是零、至少不是負數,有些品號毛利是零,但邊際毛利是正的,這些於成本分析上都有其實質意義。

變動成本是只有生產銷售才會發生的成本,非變動成本則是不論是否生產銷售、都會發生的成本,因此在考慮是否生產銷售時,應當依據邊際毛利判斷、而非毛利。

四、IF函數邏輯判斷

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

五、建立樞紐分析表

建立樞紐分析表,欄位清單的設置如圖所示,具體作法可參考上一節範例,或是先前文章《Excel建立樞紐分析表:欄位版面配置與加總計數》

六、樞紐分析表函數補充

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

七、各品號存貨成本明細

先前第二步驟有提到存貨期末成本表,通常庫存存貨都會有加權平均計算出來的成本,成品和材料都一樣,在此簡單假設各材料的成本明細。

八、VLOOKUP取得成本

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

九、三個月存貨成本明細

材料成本因為缺料加班趕工、品質不良重工等因素,有可能單月波動大,為了有個較為客觀的實際成本,有些情況喜歡便用期間平均值,例如抓三個月平均成本,首先是先跑出三個月的存貨期末成本表,這裡也是較為簡化的範例,實際報表的資料筆數會相當多。

十、樞紐分析表計數加總

先前著作《會計人的Excel小教室》第四章「樞紐分析表應用」有提到,少部份情況會用到計數的值欄位設定,這一節的例子剛好用上。抓一個期間計數,再抓一個總成本,總成本除以期間個數便是平均成本,也就是評估用的標準成本。

十一、成本差異分析

有了標準成本明細表,同樣用Vlookup帶過來,再設一些簡單的差異比較公式:「=F2-G2」、「=H2/G2」這能看出各品號當月成本相對於前三個月平均的標準成本而言,是多了還是少了。此處是看總成本,如果想再細一點,還能就材料、人工、加工、製費作差異比較。其實只要原始資料充足,想得到的大概就做得出來,問題只是好不好分析其原因而已。

簡單但足以應用的Excel成本會計範例

這一節主要是以材料成本進行某個角度的分析,因為公司的製程大多會有好幾層,所謂的材料有可能是最原始的採購原料,也有可能是中間製程再投入的半成品,只是在系統皆是以單一製程的角度稱之為原料和成品。再以邊際貢獻毛利為例,假使沒有成本會計的基礎知識,也許較不容易理解總成本和變動成本的概念區分。另外每家公司的狀況和產品不同,有可能需要以不同方式執行成本分析,在此是以簡單案例作示範,重點在於如何綜合運用Excel指令函數,讀者可作為參考並應用在各自的實務案例中。

相關文章