Excel如何设计函数公式整理固定资产清册




事务所工作的时候,接触过很多家企业的帐册和ERP系统,台湾大型的集团企业蛮多使用SAP系统,稍具规模的中小型企业很多是被鼎新拿下,例如Workflow系统和Tip-top系统。像这些成熟完整的ERP系统,会依照功能分成好几个模组,其中总是会有个固资模组,里面很多制式化报表,包括不可缺少的「财产目录」或者「固定资产清册」。

固定资产清册核心栏位有:资产编号丶资产名称丶规格丶数量丶原始成本丶取得成本丶本期折旧丶累计折旧,这些历史资料可以满足事务所种种查核程序。然而,如果公司财会部门想要做财务分析,例如未来五年十年的预测模型,评估帐上固资未来几年折旧对於损益的影响,此项分析除了已发生的历史,更需要的是未来资讯,也就是折旧到期日。

比较少在ERP系统报表看到这个栏位,事务所不需要,但其实它是公司财务分析模拟未来状况的关键资讯。以下介绍如何在现有固定资产清册的基础上,运用Excel函数公式计算出折旧到期日:

一丶简单扼要的「固定资产清册」,如同文章前言所述,栏位有「资产编号」丶「名称」丶「取得日期」等,目的是利用这些资料算出折旧到期的月份。

Excel如何设计函数公式整理固定资产清册 Excel 第1張

二丶首先是计算出每月折旧的公式:「=ROUND(G5/D5/12,0)」,亦即「取得成本」除以「耐用年限」丶再除「12」(月份)。通常系统报表会有当期折旧,建议还是另外架个公式,刚好也是验算。

Excel如何设计函数公式整理固定资产清册 Excel 第2張

三丶计算出折旧的「最後一年」:「=VALUE(RIGHT(YEAR(C5)+D5,2))」,这里利用「Year」函数将「取得日期」转换成年度,再加「耐用年限」,最後再以「Value」函数强制把计算结果设定为数值型态, 方便再进一步处理。

Excel如何设计函数公式整理固定资产清册 Excel 第3張

四丶考虑到月份有一位数和两位数的差异,为符合一般年月为整齐四位数的表达方式,设计了「IF」函数作为逻辑判断:「=IF(VALUE(MONTH(C5))<10,CONCATENATE(“0”,MONTH(C5)),MONTH(C5))」,计算结果即为「最後折旧月份」,这里是假设取得固资後的次月开始提列折旧。

Excel如何设计函数公式整理固定资产清册 Excel 第4張

五丶简单将年月合并:「=I5&J5」,得到一目了然的「折旧到期」所属年月。

Excel如何设计函数公式整理固定资产清册 Excel 第5張

六丶由於最後一年的折旧通常不会刚好是12个月,必须精心设计计算公式:「=IF($I5<18,0,IF($I5<19,$H5*$J5,$H5*12))」,如果是17年以前到期,於18年当然折旧费用为零,再来如果是小於19年,加上前面已经筛选掉17年以前,判断结果便是当年18年到期,折旧计算为「每月折旧」乘以「最後折旧月份」,前两个条件皆不成立的话,表示在当年折旧不会到期,所以是折12个月。

Excel如何设计函数公式整理固定资产清册 Excel 第6張

七丶上一步骤将栏位的部份前面都加了个「$」,作用为固定住公式中的栏,到了这个要进一步计算「2019」年及「2020」年,公式拖曳复制过来之後,只要修改其中关於年份的部份即可。如此,完美计算出未来三年折旧费用预测。

Excel如何设计函数公式整理固定资产清册 Excel 第7張

文章结语两点补充:

第一,ERP系统虽然很多报表可以使用,但毕竟是制式化软体,不一定符合每个企业状况和需求,很多时候原始报表必须运用Excel进行後加工处理。公式设计时应一并考量延伸性,如同此篇文章范例,想再增加未来预测年度丶过後新的结帐期间要再更新资料,都是很容易的。

第二,後加工的过程愉悦或者痛苦,决定於日积月累的Excel功力。这篇文章有些函数之前我用过了,有些函数之前从没用过,只是知道现在处理跟日期还有文字有关的问题,而我大致了解这两个类型有些什麽函数可以使用,所以解题的过程没遇到太大困难。在这里建议各位读者,既然工作上会一直用到Excel,那麽就在每一次遇到难题的时候,把它当作是挑战和磨练,假以时日,每个人都可以达到赞赞小屋见招拆招丶无招刚好练功的境界。


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


當前文章分類:
Excel