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




鼎新TipTop成本分攤以部門會科為一組單位,分攤到預設的製程工段上。例如,間接部門的費用須分攤到所有工段,第一個是生管的薪資費用,第二個是品保的薪資費用,第三個是生管的攤銷費用,接下來依此類推,所有部門會科都要設置,才能將間接部門的所有人工製費,全部分攤到當月份的工單上。剛開始導入系統結算成本,先要討論出分攤方案,然後在系統裡面依方案設置。

通常成本分攤的資料量會很肥大,假設5個部門10個會科3個工段,這樣將有5X10X3=150項分攤項目,而且任何參數多一個,資料就會多一倍,非常可怕。因此一筆一筆在系統裡面輸入,會讓有人有種回到原始時代的錯覺。比較可行的方法,是請資訊提供系統可接受的資料格式,藉助Excel函數,將分攤設置依照格式建好,再批次導入系統。以下分享實務上的作法:

一、首先如圖所示,三個工段,兩個間接部門,四個會科,因此將有3X4X2=24項分攤設置。這個為了說明的簡化例子,實務上肯定更多,以我自己處理的案子,有43個工段、37個部門、62個會科,所以有43X37X62=98,642項分攤設置,但是一筆一筆在系統設式手工輸入,手指頭會抽筋,人也會變得阿呆,工作擺在眼前,不處理沒辦法下班回家。只能跟資訊協調,用Excel整批匯入取代人工輸入。

三個工段,兩個間接部門,四個會科

二、這是我們要的結果,四個會科依序分攤給三個工段,依照既定的分攤權數,第一個部門好了,接下來第二個部門。每個部門有3X4=12項,兩個部門共24項,擷圖只是完整表格的一部份。

這是我們要的結果

三、開始介紹怎麼運用Excel設置成本分攤。先介紹簡單卻妙用無窮的ROW函數,以微軟的官話講:傳回參照位址中的列號,白話講就是找出儲存格所在的列號,依照這個函數定義,輸入公式「=ROW(B2)-1」,滑鼠移到儲存格右下角,游標變成小黑十字架,連按滑鼠左鍵兩下,便可以將公式往下拉,完成序列。

簡單卻妙用無窮的ROW函數

四、再來設置工段。很簡單就是ABC一直循環,先手工輸入前三個ABC,在第四個輸入「=D2」的公式,一樣將公式往下拉,工段OK。

ABC一直循環

五、工段設好了,就可以利用Vlookup函數將分攤權數帶出來,輸入公式:「=VLOOKUP(J2,分攤!B:C,2,0)」,往下拉,輕輕鬆鬆。

利用Vlookup函數將分攤權數帶出來

六、會科這裡就要動點腦筋。三個工段,因此每個會科要先重覆三次,再跳到下個會科,並且之後要用VLOOKUP將會科帶出來,所以要想辦法做出111222333的挑序內容,這個使用的公式是:「=INT((ROW(I2)-2)/3)+1」,INT函數是將小數點去掉,只保留整數,第一列儲存格的「1」,其實是「(2-2)/3的整數值+1」,下面儲存格每個列號會加1,函數算出來結果如圖所示,很漂亮吧!

INT函數是將小數點去掉,只保留整數

七、用VLOOKUP將會科代碼帶出來,發現第13列開始是「#N/A」,這是因為只有四個會科,而我們的INT公式結果在第13列開始是5以上,因此VLOOKUP找不到。待再想想法子。

第13列開始是「#N/A」

八、規律是四的倍數以上要回到1再重新跑,所以利用除法餘數的函數MOD,如果剛好是四的倍數,餘數是0,要利用IF函數將0變成4,其餘的只要直接取除以四的餘數即可,公式是「=IF(MOD(L2,4)=0,4,MOD(L2,4))」

除法餘數的函數MOD

九、將ROW、INT、MOD、VLOOKUP函數全都套在一塊,完整公式變得如此噁心,噁心歸噁心,計算出來結果是我們要的。這時候小會計通常心裡會有個XXX,這麼麻煩,我直接像工段那樣,先輸入第一個完整循環(12筆資料),然後在第13筆弄成「=I2」,公式下拉就好了。這樣也可以,只是在會科數量有變化的時候,例如從四個增加兩個到六個,便要再重新調整。而我的習慣是如果是會有參數變動的資料,在一開始會將公式設好,日後參數變動了,只要在VLOOKUP查找的參數表作更新,所有資料便會同步更新,一步到位。除此之外也是在考驗自己Excel功力,但凡有脈絡規則可尋的資料,都可以用噁心函數編出來!

將ROW、INT、MOD、VLOOKUP函數全都套在一塊

十、實際上在編函數時,一方面不想讓公式看起來噁心,另方面為了將思惟邏輯更清楚呈現,方便除錯,我常常會將各位函數值拆分,例如INT是一欄、MOD是一欄、最後VLOOKUP又是一欄,成功了之後看是將中間的欄位隱藏,還是貼上值之刪除皆可,不過如果要刪除的話,最好將原始公式檔案留備留存。

將各位函數值拆分

十一、部門的部份,依樣畫葫蘆即可,看它是幾列循環一次,更改公式值,也可以在參數表填上循環次數,直接帶,這樣部門數量有變動,直接更新參數表就好了。

部門的部份,依樣畫葫蘆

以上,是間接部門的分攤表,因為是分攤到所有工段,設置上相對簡單,如果是直接部門,每個部門對應不同的工段,這個就麻煩了,之後有時間再來寫看看。






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