Excel读书笔记26——账龄分析表——IF函数经典应⽤⽰例
账龄不仅是分析各类应收应付款项的重要依据，更是各类应收（预付）类科⽬计提坏账的主要参照标准之⼀。账龄的理论虽然简单，但是在实际⼯作中，特别是在样本量较⼤的情况下，统计各核算项⽬的账龄就会变成⼀项枯燥⼜耗时的⼯程。
本节中，我们以逸凡公司2013年12⽉31⽇的应收账款为例，讨论如何⽤Excel来设计⼀套提升账龄统计⼯作效率的“账龄统计表”（参见⽰例⽂件“表5-1账龄统计表”）。
⼀、基本框架与功能展⽰
“账龄统计表”由基础数据表（简称“数据表”，见图5-1）、账龄分布表（简称“分布表”，见图5-2）和坏账计提表（简称“计提表”，见图5-3）构成。
图5-1 基础数据表
图5-2 账龄分布表（⾃动⽣成）
图5-3 坏账计提表（设置参数后⾃动⽣成）
在数据表录⼊数据时，核算项⽬和统计⽇余额⼀般可以从财务核算系统中引出相关辅助科⽬余额表后直接粘贴。近三个期间的数据则可以在引出相关报表后，通过VLOOKUP函数进⾏引⽤。
注：本案例统计⽇为2013年12⽉31⽇，故1年以内借⽅累计发⽣额是指2013年的借⽅累计发⽣额，以此类推。
我们的⽬标是：只需要在基础表中⼿⼯录⼊核算项⽬名称、坏账计提性质（简称“计提性质”）、统计⽇余额和前三个账龄期内各期借⽅累计发⽣额（注意：若是⽤于应付账款等负债类科⽬账龄统计，则应为贷⽅累计发⽣额），在计提表中⼿⼯设置各类计提性质对应的坏账计提⽐例和前期已计提坏账⾦额，就可以⾃动实现以下功能。
1.主要信息功能
（1）分布表将⾃动统计各核算项⽬的账龄分布。
（2）计提表将⾃动汇总：①各类计提性质的科⽬余额、账龄分布、坏账计提⾦额；②计提表将⾃动计算累计应计提的坏账⾦额和本期应计提的坏账⾦额。
2.逻辑校验功能
（1）分布表和计提表将⾃动提⽰账龄分布总额与统计⽇余额是否匹配。
（2）分布表将⾃动提⽰是否存在⾮法数据（例如账龄分布中出现负数）。
⼆、基本前提及假设
1.“先⽋先还”假设
在会计实务中，为了简化统计过程，⼤多采⽤“先⽋先还”的原则进⾏账龄统计。归纳起来，表现为以下⼏点。
（1）收款⾸先冲销账龄最长的款项。
例如：逸凡公司2013年12⽉31⽇应收账款——A公司账龄如图5-4（单位：元）所⽰。
图5-4 逸凡公司A客户账龄
如果逸凡公司2014年新增A公司应收账款1500.00元，收回A公司应收账款1500.00元。⽆论A公司该笔付款偿付的是哪次交易，均默认为⾸先冲销其账龄最早的1～2年账龄（在2014年年末账龄为2～3年）的⽋款（1000.00元）及1年以内账龄（在2014年年末账龄为1～2年）的⽋款（500.00元）。
即2014年年末A公司应收账款余额为3000.00元，账龄为：1年以内1500.00元，1～2年1500.00元。
（2）同⼀账龄期间内红字冲销的累计发⽣额，不超过当期蓝字累计发⽣额的部分视为对当期数据的调整。
同⼀账龄期间，是指以统计⽇为基准，跨度为1年的期间，如图5-5所⽰。
图5-5 同⼀账龄期间⽰例
仍以上述A公司2013年12⽉31⽇应收账款为例，如果逸凡公司在2014年新增A公司应收账款1500.00元后，在当年以借⽅红字⽅式冲回A公司应收账款1000.00元（未超过同⼀账龄期间蓝字累计发⽣额），则视为对同⼀账龄期间新增额的调整。
即2014年年末A公司应收账款余额为3500.00元，账龄为：1年以内500.00元，1～2年2000.00元，2～3年1000.00元。
（3）同⼀账龄期间内红字冲销的累计⾦额，超过当期蓝字累计发⽣额的部分，视为冲销最早账龄期间⾦额。
仍以上述A公司2013年12⽉31⽇应收账款为例，如果逸凡公司在2014年新增A公司应收账款1500.00元后，在当年以借⽅红字⽅式借⽅冲回A 公司应收账款2000.00元（超过同⼀账龄期间蓝字累计发⽣额），则在对同⼀账龄期间新增额进⾏全额冲销调整后，超出部分（500.00元）视为对最早账龄期间（2013年年末为1～2年账龄，2014年年末为2～3年账龄）⾦额的冲销调整。
即2014年年末A公司应收账款余额为2500.00元，账龄为：1年以内0.00元，1～2年2000.00元，2～3年500.00元。
2.⾮负数假设
同⼀账龄期间内，贷⽅累计发⽣额（若是⽤于应付账款等负债类科⽬账龄统计，则此处应为借⽅累计发⽣额）不得为负数。
由于贷⽅核算款项回收，⼀般在公司⽇常债权核对、资⾦核对等内部控制活动的监管下，不会出现需要回冲的情况。即使出现因挂账串户需要红字回冲的情况，也极少出现同⼀账龄期间内贷⽅累计发⽣额为负数的情况。故“账龄统计表”不对因此造成的特殊情况进⾏识别。如果确实出现此种情况，则需要通过⼿⼯单独调整。
3.四期账龄假设
本案例中设计的“账龄统计表”，采⽤的是⽬前最常见的四期账龄设置，其账龄分布及其正常风险组合下坏账计提⽐率如图5-6所⽰。
图5-6 账龄分布区间及坏账计提⽐率
4.单独计提模式下，只考虑全额计提
单独计提⼀般⽤于⼀些特殊性质的应收账款。如某债务⼈确实⼭穷⽔尽疑⽆路后没有看到柳暗花明⼜⼀村的征兆，按照谨慎性原则，就应该根据评估的⽆法回收的⽐例计提坏账。所以该类应收账款的计提⽐率与账龄没有相关性，为简化处理，本案例中针对此类计提性质只考虑全额计提。
三、注意事项
1.充分考虑科⽬重分类
如果某核算项⽬的统计⽇余额为负数，则该核算项⽬的余额应在财务报表中重分类为“预收款项”项⽬列报，并在预收账款的“账龄统计表”中予以反映（其他科⽬也有类似的重分类规则，不赘述）。故⽆论是何种科⽬的账龄统计，统计⽇余额均不得为负数。
2.及时排除⽆效数据
由于只有统计⽇余额⼤于0的项⽬才列⼊统计，所以从财务记账系统引出统计⽇余额表后，应⾸先删除余额⼩于等于0的项⽬，再将相关数据粘贴到基础信息表。这样不仅可以使基础信息表中记录的信息具
有⾼度实⽤性，⽽且还能杜绝⼤量⽆效数据影响整套“账龄统计表”的运算效率。所以处理⽆效数据应趁早。
3.⾼度警惕重名现象
前⾯我们提到过，在数据表录⼊信息时，⼀般需要通过VLOOKUP函数引⼊最近三个账龄期间的借⽅累计发⽣额。由于使⽤VLOOKUP函数进⾏数据匹配时要求索引信息的唯⼀性，所以我们需要考虑核算项⽬的重名问题（特别是核算项⽬为职员时，存在同名的概率⽐买中500万⼤多了）。否则数据出了差错，你在⽼板⾯前⽤⼀句“纯属巧合”肯定是摆不平的。规避这个问题的常⽤⽅法是，录⼊核算项⽬信息时，带上相关的编码。这样，每个项⽬便有了唯⼀性。
4.使⽤数据有效性限制信息填写
在很多表格中，总有那么⼏个⼿⼯填写的参数具有较强的限定性，实际上是在做选择题⽽⾮填空题。例如数据表中的计提性质，就只允许填写规定的三种性质之⼀。如果不⽤数据有效性加以监管，就很容易出现同物不同名，进⽽影响数据统计的情况。所以，我们要养成勤⽤数据有效性的好习惯，来限定某些参数输⼊内容的范围。
四、知识点装备
在阅读本节下⾯的内容前，请各位读者朋友⾸先确认⼤脑中是否已经基本装备了图5-7中的相关知识点。
图5-7 相关知识点
五、主要信息的公式设计⽅法
在对“账龄统计表”的框架、功能和相关注意事项有了⼤致的认识后，我们就以逸凡公司应收账款的案例来讨论“账龄统计表”的设计。
【案例5-1】逸凡公司2013年12⽉31⽇应收账款核算项⽬的科⽬余额表如图5-8所⽰（单位：元）。其
中，逸凡A公司为全资⼦公司，⽆须计提坏账，⽽泥沙⼯业已经陷⼊财务困境多年，将全额计提坏账。假设前期已计提坏账准备200元。
图5-8 逸凡公司客户应收账款科⽬余额表
注：据此，在数据表录⼊的信息参见图5-1。
结合“账龄统计表”结构，相关设计⽅法如下。
1.“分布表”公式设计⽅法
（1）核算项⽬等同步信息（A4:C14单元格区域）的公式设计。
“分布表”的核算项⽬、坏账计提性质及统计⽇余额信息与数据表的同名字段是同步的。但需要提醒的是，当被关联的单元格为空⽩时主单元格将显⽰为0。为了使主单元格也为空⽩，我们需要通过IF函数来配合“=”实施信息的同步关联。
A4单元格的公式为：=IF(数据表!A4="",""，数据表!A4)
B4单元格的公式为：=IF(数据表!B4="",""，数据表!B4)
C4单元格的公式为：=IF(数据表!C4="",""，数据表!C4)
（2）账龄1年以内⾦额分布的公式设计（D4:D14单元格区域）。
vlookup函数8种用法第⼀步：⾦额为0的情况。
由于账龄分布中不可能存在负数，且根据“先⽋先还”的原则可知，账龄1年以内分布的⾦额其最⼩值为0，最⼤值为1年以内借⽅累计发⽣额。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。