Excel如何設置直接部門成本分攤




先前介紹過間接部門的成本分攤設置,由於間接部門的成本,會分攤到所有製程工段,在設置上相對單純,直接部門則是各部門有相對應的製程工段,比較麻煩,現在分享直接部門的成本分攤設置:

一、如圖所示,為了方便說明,簡單假設有三個直接部門,分別有2、1、3個製程工段,兩個會科。

簡單假設有三個直接部門

二、3個直接部門相對應的工段如表格列示。

直接部門相對應的工段

三、實際上有可能某個部門沒有某個會科,但為了在設置上力求完美,將每個部門會科都設置進去,第一個部門「製一課」為例,有兩個會科兩個工段,總共應該有四筆資料(2X2=4),至於各個工段的分攤權數,看公司具體怎麼決定,我這裡是簡便處理,各工段平均分攤部門成本。暫不考暫函數怎麼弄,手工一筆一筆輸入的話,結果應該如此。

手工一筆一筆輸入的結果

四、實務上一間工廠的部門工段會科絶對沒有這麼少,真正的分攤設置可能有成千上萬筆資料,所以最好藉助EXCEL函數。要下函數之前,先要找出資料裡的規律。以部門而言,因為分別有2、1、3個工段,每個部門兩個會科,所以分別有4、2、6筆資料,以項次而言,1-4是「製一課」、5-6是「製二課」、7-12是「製三課」,簡單設個函數「=C4*2+F4」把項次帶出來。第一列因為要設為0,所以最好是第一列和第二列都手動輸入,沒辦法直接帶相同的公式。

找出資料裡的規律

五、規律找到了,並且有整理出來,下一步便可以把資料帶過來。我們想要的是序列1-4是1,序列5-6是2,序列7-12是3,這樣就可以把部門資料VLOOKUP過來。使用LOOKUP函數「=LOOKUP(H2,部門!$F$2:$F$5,部門!$A$2:$A$5)」,意思是在「部門」這張工作表的F2-F5的範圍內,找出H2(值為1)的相對位置,並根據這個位階,傳回A2-A5相對應的值。LOOKUP函數的特性是查找範圍(F2-F5)必須是遞增順序挑列,因為如果找不到相同的值,函數會去抓小於或等於查找值中的最大值。以「I2」儲存格為例,查找值是H2(1),查找範圍中(F2-F5)沒有1,這個範圍內{0,5,7,13}小於等於1的最大值是0,傳回範圍中(A2-A5)和0(F2)相同位階的是A2,傳回的值是1。

使用LOOKUP函數

六、把部門順序排出來之後,便可以輕鬆VLOOKUP部門名稱或是代碼。

VLOOKUP部門名稱或是代碼

七、會科的部份,因為只有兩個要重覆循環,簡便處理,以利用函數「ISODD」判斷是否為奇數:「=IF(ISODD(H2),部門!$D$2,部門!$D$3)」,如果為序列為奇數,帶部門工作表的「D2」,否則的話帶「D3」。

利用函數「ISODD」判斷是否為奇數

八、部門工段的部份,依照之前方式整理出規律。

部門工段整理出規律

九、再用LOOKUP把工段的順序表排出來。

用LOOKUP把工段的順序表排出來

十、把工段順序排出來之後,便可以輕鬆VLOOKUP工段名稱或是代碼。

VLOOKUP工段名稱或是代碼

十一、權數的部份,設置為平均分攤:「=1/VLOOKUP(I2,部門!B:C,2,0)」就好了,看起來會有尾差,但其實EXCEl計算的位數很夠,加總合計是1,實際上看ERP系統小數位的設置情形,再看看是否要修正。

權數的部份,設置為平均分攤






當前文章延伸閱讀:
成本會計