［关键词］运用EXCEL分析应收账款回收

［摘要］应收账款回收情况的监督主要通过编制账龄分析表进行，对于客户众多、业务频繁的企业来说，运用EXCEL快速、准确编制账龄分析表，并据此制定有效的收账政策，对于加强应收账款管理，提高资金回笼速度，降低经营风险具有非常重要的意义。

应收账款回收情况的监督主要通过编制账龄分析表进行，一般来讲，应收账款拖欠时间越长，款项收回的可能性越小，形成坏账的可能性就越大。通过账龄分析可以揭示每个客户的风险性和每笔应收账款产生坏账的可能性，并以此作为提取坏账准备的依据，因此对于客户众多、业务频繁的企业来说，运用EXCEL快速、准确编制账龄分析表，并据此制定有效的收账政策，对于加强应收账款管理，提高资金回笼速度，降低经营风险具有非常重要的意义。

一、账龄的确定

账龄的时间段一般根据客户的信用期，结合会计准则对计提坏账准备标准划分。划分账龄的时间段越细、间隔越短，坏账估计就越精确。通常情况下，对1年以内应收账款账龄划分较细，而对1年以上的应收账款则不能做太多区分。

二、回收情况表的制作

为数据提取阶段，主要提取应收账款明细账的“科目名称”、“年初余额”、“借方发生数”、“贷方发生数”，将以上数据分别复制到回收情况表的“客户名称”、“年初欠款余额”、“本月发生应收账款”、“本月收回账款”等，然后制作成表格。要求每月单独制作一个工作表

三、账龄分析表的制作

为数据分析阶段，先从回收情况表提取有关数据，由于每月都有可能增加或减少应收账款客户，导致每月客户的顺序都不一样，所以要用较为复杂的EXCEL函数嵌套搜索同样的客户名并返回相应的数值，对于账龄和金额部分我们假设客户归还账款时按照应收账款发生的先后顺序收回账款，即归还的账款先冲销时间较长的应收账款。

下面通过实例来说明运用EXCEL分析应收账款收回情况的方法和步骤。

［例］某企业2005年1－6月的应收账款明细账如下，请根据明细账对应收账款回收情况进行分析。

某单位2005年1-6月应收账款明细账

单位:元

科目名称年初余额1月份2月份3月份4月份5月份6月份

借方贷方借方贷方借方贷方借方贷方借方贷方借方贷方

东方公司1000030003000300050006000300070008000

南方公司200004000500080002000300050007000

西方公司300005000100030002000

北方公司100040002000600030002000

合计60000400080009000300090001500080002000160007000300017000

（1）确定账龄时间段划分标准

可以将账龄时间段划分为0～30天、31～60天、61～90天、91～120天、121～180天、180天以上。

（2）制作应收账款回收情况表

第一步：数据提取

将应收账款明细账转化为EXCEL文件，便于复制，如果财务软件不具备该功能，可以用数据库运用程序打开存放应收账款明细账数据库文件，如扩展名为DBF文件，可用FOXPRO程序打开，然后导出EXCEL格式文件。以利于应收账款回收情况表提取相关数据。

第二步：编制应收账款回收情况表

根据2005年1－6月明细账编制一个回收情况表，该表为计算账龄的基础数据表格。首先，制作一个“应收账款回收2005.xls”，在该文件中每月设一个工作表，工作表格分别为“05.01”、“05.02”、“05.03”、“05.04”、“05.05”和“05.06”。分别存放2005年1－6月应收账款回收情况表的数据，将应收账款明细账的“客户名”、“年初余额”、“借方”、“贷方”等列内容分别复制到“05.01”工作表的“客户名称”、“上年余额”、“本期应收账款”、“本期回收款”等列。如下表

某单位2005年1月应收帐款回收情况表

单位：元

行号客户名称上年欠款余额本月发生应收账款本月应收账款

1东方公司100003000

2南方公司200004000

3西方公司300005000

4北方公司

合计6000040008000

（3）制作应收账款账龄分析表

ABCDEFGHIJKLMNOPQRSTUVWX

1某单位2005年6月应收账款账龄分析表

2单位:元

3行

号客户

名称年初

欠款

余额2005年应收账款发生数2005年收回应收账款累计

欠款

余额账龄及金额

4小计1月2月3月4月5月6月小计1月2月3月4月5月6月0～

30天31～

60天61～

90天91～

120天121～

180天180天

以上

51东方

公司1000018000030005000300070000200003000300060000080008000070001000000

62南方

公司20000140004000500002000300002000000800005000700014000030002000090000

73西方

公司3000030000003000008000500001000020000250000030000022000

84北方

公司014000010004000060003000400000020000200010000300060000100000

9合计6000049000400090009000800016000300052000800030001500020007000170005700030001600060001000900022000

第一步：数据提取

应收账款账龄分析表中的“年初欠款余额”、“应收账款发生数”、“收回应收账款”有关数据应从“应收账款回收2005”文件中提取，由于各月的客户有增有减使每个客户在工作表中的位置可能不一样，因此在使用取数公式时，需用“查找与引用”函数INDEX，具体公式如下，以东方公司“年初欠款余额”C5单元格为例。

=IF(ISERROR(INDEX(''''应收账款回收2005.xls)05.01''''!$A$1:$Z$200,MATCH($B5,''''[应收账款回收2005.xls]05.01''''!$B$1:$B$200,),3))=TRUE,"",INDEX(''''[应收账款回收2005.xls]05.01''''!$A$1:$Z$200,MATCH($B5,''''[应收账款回收2005.xls]05.01''''!$B$1:$B$200,),3))

该公式看似复杂，其实用了一个嵌套，IF（ISERROR(INDEX函数)＝TRUE，“”，INDEX函数），其含义是如果INDEX函数出现了错误的结果（如＃NA，＃VALUE），让本单元格数据为空，否则运行INDEX函数。

INDEX(''''[应收账款回收2005.xls]05.01''''!$A$1:$Z$200,MATCH($B5,''''[应收账款回收2005.xls]05.01''''!$B$1:$B$200,),3)的含义是，在“应收账款回收2005”05.01工作表第3列（上年欠款余额）中，查找并引用对应于B5单元格内容的值，即B5单元格内容在05.01工作表中的第3列值。运用这个公式的好处是，无论“应收账款账龄分析表”中的“东方公司”在“应收账款回收2005”05.01工作表B列的第几行，都可以取得其对应的第3列“上年欠款余额”的值。

将该公式复制下去，可得到本列其他各列的值；将该公式横向复制，并将公式中的相应月份（如“05.02”）替换为单元格所对应的月份（如“05.01”），可自动得到其他各行的值。

第二步：数据分析

我们假设客户归还账款时按照应收账款发生的先后顺序回款，即归还的账款先冲销时间较长的应收账款

以东方公司为例。其账龄及金额段公式如下

账龄东方公司公式所在单元格

0～30天=IF(R5<=0,0,IF(R5>=SUM(J5:J5),J5,R5))S5

31～60天=IF(R5<=0,0,IF(R5>=SUM(I5:J5),I5,IF(R5-SUM(J5:J5)<0,0,R5-SUM(J5:J5))))T5

61～90天=IF(R5<=0,0,IF(R5>=SUM(H5:J5),H5,IF(R5-SUM(I5:J5)<0,0,R5-SUM(I5:J5))))U5

91～120天=IF(R5<=0,0,IF(R5>=SUM(G5:J5),G5,IF(R5-SUM(H5:J5)<0,0,R5-SUM(H5:J5))))V5

121～180天=IF(R5<=0,0,IF(R5>=SUM(E5:J5),E5+F5,IF(R5-SUM(G5:J5)<0,0,R5-SUM(G5:J5))))W5

180天以上=IF(R5<=0,0,IF(R5>=D5,R5-D5,0))X5

因为我们假设归还的账款先冲销时间较长的应收账款，所以“累计欠款余额”由最近几个月的“应收账款发生数”组成。在设计公式时，要先将“累计欠款余额”与相应期间的“应收账款发生数”相比较，以账龄为31～60天T5单元格为例，如果5月－6月“应收账款发生数”合计小于“累计欠款余额”则账龄为31～60天的金额为5月份应收账款发生数，如果5月－6月“应收账款发生数”合计大于“累计欠款余额”则账龄为31～60天的金额为“累计欠款余额”的与6月份“应收账款发生数”的差额。

将以上公式复制第5行以下各行。至此，6月份的账龄分析表文件“应收账款账龄分析表”即告完成。

综上所述，根据单位应收账款明细账有关基础数据，利用EXCEL函数公式定义需要计算的各个项目，则可自动地编制出应收账款账龄分析表，更加方便、快捷、最大限度地提高工作效率，对于应收账款发生比较多的单位其效果尤为明显。