Excel如何檢查成本分攤設置




成本分攤三元素:製程、工時、成本,月底把這三塊資料統計好了之後,當月所有成本先分攤到各個製程,各製程再依工單工時比例,將製程成本分配到各個工單,由此計算出該工單產出的單位成本,最後所有工單入庫和其他存貨異動加權平均,便可結算出當月成本。

其中成本分攤到製程比較關鍵,一般ERP系統帳結算成本,例如鼎新Tip-top,以部門會科組合起來作為一成本項目,先評估這成本項目該由哪些製程分攤,再設置好分攤權數,明確各製應依多少比例分攤,總分攤權數合計是100%。舉例而言,A部門5100會科當月共有100塊成本,這100塊以3:2比例分給a和b兩個製程。一家公司假設有十個部門、十個會科、十個製程好了,這樣就有10X10X10=1,000筆資料,實在不是個小數目。

更麻煩的是,費一番功夫設置好龐大資料庫,卻並非從此一勞永逸,因為會科並非一成不變、部門組織有可能調整,而且某月份某製程也有可能無工時產生(根本未開工),有時候甚至連分攤比例都可能需要修改。凡此種種情形,原來的設置就必須更新,否則成本結算會跳出錯誤訊息,拋轉成本傳票時會拉不出會科。

如前所述,分攤設置的資料過於龐雜,沒辦法一筆一筆檢視是否有誤,很需要有一套完善機制,能把錯誤訊息偵察出來,倘若系統沒有,資訊人員又沒有客製,那只得靠萬能的會計人自己動手囉。在此分享我所遇到的實例,還有相對應的Excel檢錯方式:

一、首先,如圖所示,已經設置好的部門會科,有些當月沒有交易金額產生,另外當月有些新增的部門會科,這兩種情況都會使得成本結算出問題,想要利用Excel偵錯,因為涉及到部門會科一組兩個變數,必須引用二維數列的概念。

成本分攤設置

二、第一直覺我是在常用函數MAX上動手腳:{=MAX(($D$3:$D$11=A10)*($E$3:$E$11=B10)*$D$3:$D$11)}。這個公式表示在D3到D11範圍裡,同時滿足D3到D11中等於A10、而且E3到E11中等於B10的儲存格,選擇其中最大值。在第十列公式取的是A10B10(會科5300部門D),D欄裡沒有符合的儲存格,所以取值是零。在第九列公式取的是A9B9(會科5300部門C),D欄裡只有一個同時符合這兩個條件,就是公式計算結果的5300。需特別注意陣列符號{},如果是直接輸入,會讓儲存格變成文字而非公式計算,要在輸入「=MAX(($D$3:$D$11=A10)*($E$3:$E$11=B10)*$D$3:$D$11)」之後,滑鼠停留在公式欄,同時按住Ctrl和Shift不放,再按Enter鍵,這樣會自動跑出{},將公式陣列化。

MAX函數

三、上一個步驟求的是實際費用有、分攤設置無的部份,只要依照公式原理,前後欄位稍加替換,便可求出分攤設置有、實際費用無的部份。

MAX函數不同應用

四、一般遇到多條件求值的情況,真正的Excel高手信手捻來就是個陣列函數,我半路出家,陣列觀念不及格,只有簡單函數MAX常用,所以第一時間將MAX陣列化,想出前面那個長相奇怪的公式,勉強還堪用。不過既然是多條件求值,在此當然要介紹名門正宗的陣列函數了。在公式欄輸入「=SUMPRODUCT(($D$3:$D$11=A10)*($E$3:$E$11=B10))」,意思是滿足D3到D11中等於A10、並且E3到E11同一列數也等於B10,這兩個條件都滿足的儲存格個數。在F10儲存格裡的公式,實際費用是會科5300部門D,沒有設置分攤,所以計算結果是0個,在上一格F9的公式裡,實際費用是會科5300部門C,設置裡剛好有個會科部門都相同的分攤組合,所以計算結果有1個相符。

SUMPRODUCT函數

五、和MAX函數情況相同,上一步驟公式求的是實際費用有,分攤設置無的部份,只要依照SUMPRODUCT公式原理,將欄位稍加替換,便可求出分攤設置有,實際費用無的部份。

SUMPRODUCT函數另一應用

六、想全面瞭解SUMPRODUCT函數,在公式欄左邊的「fx」按一下,函數小教室跳出來,如果小教室看了還不夠,左下角還有個「函數說明(H)」超連結,點一下即可查閱官方指南。

SUMPRODUCT函數引數

SUMPRODUCT函數說明

七、Excel方法很多,同樣目的有許多不同路徑可以達到,如同這篇文章的MAX和SUMPRODUCT一般,戲法人人會變,巧妙各有不同,多熟悉一個函數,便多一種戲法,所以平常沒事的時候,只要隨便一個空格按下「fx」,所有函數都在這裡,任君學習!

Excel所有函數集






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