巧用EXCEL函数计算待摊费用

巧用EXCEL函数计算待摊费用

待摊费用是指企业已经支出，但应由本期和以后各期分别负担的各项费用。企业发生的各项待摊费用，应按照权责发生制原则和配比原则，严格划清费用受益期限，合理分摊到各个受益期间，以便正确计算各期盈亏。根据《企业会计制度》规定：核算企业已经支出，但应由本期和以后各期分别负担的分摊期限在1年以内(包括1年)的各项费用，如低值易耗品摊销、预付保险费、固定资产修理费用，以及一次购买印花税票和一次交纳印花税税额较大需分摊的数额等。超过1年以上摊销的固定资产修理支出和租入固定资产改良支出，以及摊销期限在五年以上的其他费用，应当在“长期待摊费用”科目核算。

我们经常使用的方法是制作计算分月摊销金额而摊销累计金额、摊余金额不能自动计算的表格，如果企业发生的待摊费用的事项比较多，这种方法就不能很好地解决问题。下面我来讲解如何利用EXCEL函数来自动计算本期摊销数、累计摊销数、摊余金额。

每个待摊费用项目在该表中都作为一条记录，该表分四个区域：基本数据区、本期摊销数、累计摊销数、摊余金额，该表只需填列“基本数据区”中的项目、摊销月数、开始摊销日期、成本中心、账面金额，其余四个区域的数据都是自动生成的。本期摊销、累计摊销、摊余金额都是分月列示的。开始摊销月份和每月摊销月份都以每月第一日作为基准来计算摊销费用。

在做本期摊销计算之前，我在基本数据区后本期摊销数前添加一列辅助列，这一列用于后面公式中的倒减计算，该列下的日期可以输入比最早进行摊销的待摊费用开始日期的前一个月的日期，在这里为2012-6-1，该列下面的每一个单元格可以不输入任何数值。见下图1：

图1

本期摊销数的计算：进行摊销计算必须考虑以下几个因素：保证没到开始摊销月份的项目不能进行摊销、摊销期满不再进行摊销、摊销金额累计数与被摊销的项目入账金额之间的尾差在摊销的最后一期能自动调整处理。因此在第一条待摊费用项目(最先开始摊销的项目)开始摊销日期这一列的H5单元格定义如下公式：

=IF($F$5=0,0,IF(H$4<$D5,0,IF(DATEDIF($D5,H$4,"m")=$C5-1,$F5-SUM($G5:G5),IF(DATE DIF($D5,H$4,"m")>$C5-1,0,ROUND($F5/$C5,2)))))，该公式的含义是：首先对“基本数据区”中“账面金额”进行判断，如果“账面金额”为0就不执行摊销计算，否则要进一步判断是否摊销计算；其次对该项目的开始摊销日期与各期计提摊销日期作比较，如果各期计提摊销日期小于开始摊销日期就不进行摊销计算即为0（即该待摊费用项目还未到开始摊销期间），否则就进行摊销计算；在摊销计算中还需要对是否已经摊销到最后一期、是否摊销完毕进行判断，其中IF(DATEDIF($D5,H$4,"m")=$C5-1,$F5-SUM($G5:G5)就是判断是否摊销到最后一期，如果到最后一期就以该低值易耗品的入账金额减去前面各期已摊销金额之和作为摊销金额的计提数，这样采用倒减的方法就可以避免累计摊销金额与被摊销的项目入账金额之间的尾差的状况出现；如果开始摊销日期与计提摊销日期之间的间隔已经大于应摊销月数减去1个月即表示已经摊销完毕，其计算的值也应该为0;排除最后一期摊销与超过摊销期这两种特殊情况后就按正常摊销方法进行计算。在这里上述计算过程中就用到了DATEDIF 函数对两个关键日期进行比较然后根据比较结果进行摊销计算。上述公式定义完毕可以直接

拖动复制该公式到该表合计这一行的上一行，然后选中H列H5：H27这个区域（27行为合计所在行的上一行）向右拖动复制该公式拖动至本期摊销数据区中最后一列。经过上述步骤，每个待摊费用项目的各期摊销金额就计算出来了。见图2

图2

鉴于该表格摊销期数比较多，我们可以考虑使用EXCEL的数据分组功能将本期摊销数据折叠起来，在这里用鼠标选定H:V列，然后点：数据→创建组→选择：创建组，这样就可以将本期摊销数区域进行折叠起来，如果要打开查看数据，点那个“+”号即可，展开后会自动变为“-”号。见图3

图3

累计摊销数的计算：在累计摊销数据区域的开始列W5单元格定义如下公式：

=IF($F$5=0,0,IF(W$4<$D5,0,IF(DATEDIF($D5,W$4,"m")=$C5-1,$F5,IF(DATEDIF($D5, W$4,"m")>$C5-1,0,ROUND($F5/$C5,2)*(DATEDIF($D5,W$4,"m")+1)))))，该公式的含义是：IF($F$5=0,0与上述本期摊销类似，即没有待摊数据时就不执行摊销计算；其次对该项目的开始摊销日期与各期计提摊销日期作比较，如果各期计提摊销日期小于开始摊销日期就不进行摊销计算即为0（即该待摊费用项目还未到开始摊销期间），否则就进行摊销计算；在摊销计算中还需要对是否已经摊销到最后一期、是否摊销完毕进行判断，其中

IF(DATEDIF($D5,W$4,"m")=$C5-1,$F5就是判断是否摊销到最后一期，如果到最后一期则累计摊销金额为该项目的账面金额；如果开始摊销日期与计提摊销日期之间的间隔已经大于

应摊销月数减去1个月即表示已经摊销完毕，其计算的累计摊销值不再予以考虑;排除最后一期摊销与超过摊销期这两种特殊情况后就按正常摊销方法进行累计摊销金额的计算（即平均每期摊销金额乘以截止该期已经摊销的月数）。同理按上述方法向下和向右拖动复制公式即可将每个项目的累计摊销计算完毕。同理利用EXCEL组合也可将累计摊销数据区域折叠起来。见下图4

图4

摊余金额的计算：在摊余金额数据区域的开始列W5单元格定义如下公式：

=IF($F$5=0,0,IF(AG$4<$D5,0,IF(DATEDIF($D5,AG$4,"m")>=$C5,0,IF(DATEDIF($D5,A G$4,"m")<$C5-1,$F5-ROUND($F5/$C5,2)*(DATEDIF($D5,AG$4,"m")+1),0))))，该公式中IF($F$5=0,0,IF(AG$4<$D5,0,这部分与上述公式类似，不再赘述；

IF(DATEDIF($D5,AG$4,"m")>=$C5,0表示如果开始摊销日期与摊销期间的月数已经大于

或等于应该摊销的月数，其摊余金额就为0；

IF(DATEDIF($D5,AG$4,"m")<$C5-1,$F5-ROUND($F5/$C5,2)*(DATEDIF($D5,AG$4,"m") +1)表示如果开始摊销日期与摊销期间的月数小于应摊销月数减去1（即为摊销期数的倒数第二期）即为账面金额减去以前各期已摊销的累计金额；复制拖动公式和创建组合如前所述，不再阐述，这样摊余金额的计算就实现了。见图5