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又是一栏,成功了之后看是将中间的栏位隐藏,还是贴上值之删除皆可,不过如果要删除的话,最好将原始公式档案留备留存。

将各位函数值拆分

十一、部门的部份,依样画葫芦即可,看它是几列循环一次,更改公式值,也可以在参数表填上循环次数,直接带,这样部门数量有变动,直接更新参数表就好了。

部门的部份,依样画葫芦

以上,是间接部门的分摊表,因为是分摊到所有工段,设置上相对简单,如果是直接部门,每个部门对应不同的工段,这个就麻烦了,之后有时间再来写看看。


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


當前文章分類:
Excel