如何运用EXCEL分析应收账款回收情况
出处:论文网
时间:2006-10-16
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天
以上
5 1 东方
公司 10000 18000 0 3000 5000 3000 7000 0 20000 3000 3000 6000 0 0 8000 8000 0 7000 1000 0 0 0
6 2 南方
公司 20000 14000 4000 5000 0 2000 3000 0 20000 0 0 8000 0 5000 7000 14000 0 3000 2000 0 9000 0
7 3 西方
公司 30000 3000 0 0 0 3000 0 0 8000 5000 0 1000 0 2000 0 25000 0 0 3000 0 0 22000
8 4 北方
公司 0 14000 0 1000 4000 0 6000 3000 4000 0 0 0 2000 0 2000 10000 3000 6000 0 1000 0 0
9 合 计 60000 49000 4000 9000 9000 8000 16000 3000 52000 8000 3000 15000 2000 7000 17000 57000 3000 16000 6000 1000 9000 22000
第一步:数据提取
应收账款账龄分析表中的“年初欠款余额”、“应收账款发生数”、“收回应收账款”有关数据应从“应收账款回收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函数公式定义需要计算的各个项目,则可自动地编制出应收账款账龄分析表,更加方便、快捷、最大限度地提高工作效率,对于应收账款发生比较多的单位其效果尤为明显。