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所有函数集


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
Excel