利用EXCEL建立企业应收账款账龄分析管理模型

高雪平

(浙江经贸职业技术学院，浙江 杭州 310007)

应收账款管理是企业保持资金的良性循环、降低资金占用成本和保证资金安全回收的必要手段，其中账龄分析是应收账款管理最基本的环节。通过账龄分析可以揭示每个客户的风险性和每笔应收账款产生坏账的可能性，并以此作为提取坏账准备的依据。因此，准确、快速地编制应收账款账龄分析表，对企业加强应收账款管理、提高资金回笼速度、降低经营风险具有非常重要的意义。EXCEL工具可以简便地进行各种数据的处理、统计分析，目前已经广泛地应用于统计、经济管理等各领域。本文以浙江T公司为例，介绍EXCEL在应收账款账龄管理中的运用。

T公司是一家以施工、设计、服务为主要业务的大型国有集团企业，有众多的子分公司。其客户绝大多数为信用度较好的国有企业，信用风险较低，且长期保持合作关系，不适宜在信用期内进行频繁催收活动。加上国有企业工作流程较为复杂，T公司难免会出现应收账款超期的现象，因此公司对于应收账款管理的重心放在对超期应收账款的管理上。即需要对各笔应收账款是否超期、超期几天，金额多大，隶属于什么公司等信息均能够动态地把控。另一方面，由于T公司是大型的集团公司，需要每个月对应收账款的分析以图表的形式向上级及下属子分公司汇报公示。

一、应收账款账龄管理目标需求

T公司由于各个客户公司信用期不同，其每笔应收账款的可催收开始的时间、力度应有所不同。公司对于应收账款的管理聚焦于对可催收应收账款的管控，即对于超期的应收账款重点管理。因而应收账款信息表中需要特别显示出每笔应收账款的超期天数。

另一方面，由于T公司的业务人员是按照客户维度配置接口业务员的，因此也需要对每一个客户公司进行可催可收的应收账款统计。

在满足以上需求的同时，管理人员还需要有一个直观的、公司整体的应收账款账龄情况。

结合以上T公司实际运营情况以及管理需求，本文将采用EXCEL工具构建一个应收账款账龄分析管理模型。

二、基于EXCEL的应收账款账龄管理模型建设思路

1.账期确定

T公司的客户大多数为长期合作关系，因此应收账款账期主要按照客户的通常付款期限决定，一般来说，T公司对于客户设置的信用期限一般是1－3个月，即30天到90天。

2.计算已到期应收账款金额以及超期天数

首先对应收账款明细表中的到期日和统计日期做对比分析，计算出每一笔应收账款的账龄，统计出总应收账款金额和到期应收账款金额，计算出每一客户公司应收账款的总额及在总应收账款中的百分比。通过这些统计分析，可以清楚地在表格中看出整个公司的应收账款统计信息。

3.分客户、分超期天数绘制动态图表

从客户维度分析各个客户的不同超期时间段的应收账款情况，从超期天数维度分析各个客户的应收账款情况。

4.基础数据

考虑到模型的普遍适用性以及数据简化易懂性，本文不采用从T公司项目管理系统(PM系统)中直接导出的数据作为原始数据，而是采用重新采集的客户应收账款信息作为示例说明。

三、基于EXCEL的应收账款账龄管理模型建设步骤

1.建立客户信息表，定义名称

图1 客户及信用额度

建立一张sheet，命名为“客户及信用额度”把客户的名称信息、编号信息输入，同时给各个客户定义简称，分别为大写的公司首字母。为了以后录入信息方便，我们需要把各个公司进行简称定义，操作如下:选中B2:C7单元格，点击工具栏中的“公式”——“名称的定义”——“根据所选内容创建”，在跳出的对话框中，勾选“最左列”即可。

选中B2:B9区域，点击工具栏中的“公式”——“名称的定义”——“定义名称”，在跳出的对话框中，在名称对应处填上“客户”，如下图所示:

图2 定义客户名称

2.创建应收账款信息表

新建一张Sheet，命名为“应收账款基本信息”。

图3 应收账款基本信息

(1)在 D2单元格输入公式:“=”当前日期:“＆TEXT(TODAY()，“yyyy年m月d日”)＆”“＆TEXT(TODAY()，“［$－804］aaaa;@”)”，这样可以显示出表格打开当天的日期。

(2)填入该表各项要素

该表中的信息要素包括:开票日期、发票号、发票金额等。其中蓝色区域为自动计算部分，白色区域为财务人员手工录入区域(T公司由于有PM系统，所以白色区域可以直接从系统中导入)。其中单位名称列由于已经进行了名称定义，不需要输入公司中文名称，只需要输入简称即可。例如在D6单元格只要输入“=LQ”，再按回车键，表格会自动跳出“临泉公司”四字。

(3)计算欠款

在欠款额这列(G列)相应单元格输入公式:“=IF(A4＜＞，E4－F4，)”，表示欠款为应收金额减去回款额。

(4)计算到期日

到期日的计算为开票日加信用期，因此在I4单元格输入公式:“=IF(A4＜ ＞，A4+H4，)”，按回车键后向下复制公式至I16单元格，得到了每笔应收账款的到期日。

(5)判断是否超期

J列为判断是否超期，在J4单元格输入公式:“=IF(A4=，IF(TODAY()＜I4，“否”，“超期”))”，按回车键后向下复制公式至J16单元格，就可以得到每笔应收账款是否超期。

(6)计算超期天数

在K4单元格输入“=IF(A4＜＞“”，TODAY()－A4，“”)”，按回车;然后在 L4单元格输入“=IF(J4=“否”，“”，K4－H4)”，按回车。再讲K4、L4单元格公式向下复制至第5～16行。

(7)计算总计应收账款金额及超期应收账款金额

在B18单元格输入“=SUM(G4:G16)”，即可求出总计应收账款金额。在B19单元格输入“=SUMIF(J4:J16，“超期”，G4:G16)”，按回车即可求出超期应收账款金额。

(8)对超期的应收账款突出显示

选择 A4:L16区域，点击“开始——格式——条件格式——新建规则”，选择“使用公式确定要设置格式的单元格”，在编辑栏中输入“=VALUE($L4)＞0”，点格式后设置单元格格式，选择“填充”，然后就可以设置希望突出显示的颜色了，同理，想要修改突出显示的字体也可以在这里修改。

图4 超期应收账款突出显示

3.创建应收账款账龄分析表

新建一张sheet，命名为“账龄分析表”。账龄分析表展示的主要是分客户的各自不同账龄欠款情况。例如下图中和美公司的应收账款账龄在16－30天的有60000元，账龄超过90天的有20000元。总体看，T公司的应收账款账龄主要集中在31－45天，占到应收账款总额的35.15%。

对于客户账龄段的划分，以15天为一个时间段，共分为7段，如下图:

图5 账龄分析表

其中A列、B列为财务人员手工输入的客户编号及客户名称。

在C3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$K$4:$K$16，“＜ =15”，应收账款基本信息!$D$4:$D$16，B3)”，然后复制该公式至C3:I8整个区域，这样就将6个公司的各个时段账龄都汇总出来。J列用SUM函数进行行数据合计，在第9行用SUM函数进行列数据合计。在K3单元格输入:“=J3/$J$9”，再复制公式至K9，在C10单元格输入公式:“=C9/$J$9”，再复制公式至J10单元格。这样就得到各公司、各账龄应收账款占总数的百分比。

为防止数据出错，在B12:C12设置数据校验，C12单元格输入:“=IF(J9=应收账款基本信息!B18，“正确”，“错误”)”。

4.创建应收账款超期催收表

由于应收账款账龄分析表展示的是各公司的应收账款账龄情况，但是由于各个公司的信用条件不一致，不能直观地显示各个公司是否存在应收账款到期、超期情况。因此在实际工作中需要财务人员对每一笔应收账款结合各个公司的信用条件一一对比，虽然简单，但是在一定程度上加大了为员工工作量。为了解决这个简单但是繁琐的工作，T公司设计了应收账款超期催收表。

新建一张sheet，命名为“超期催收表”。超期催收表展示的主要是分客户的应收账款到期可催收的情况，要素以及表样结构与“应收账款账龄分析表”类似。例如下图中和美公司的超期的应收账款有20000元，超期时间超过了90天。临泉公司超期的应收账款达到75000元，超期时间为16－30天。总体看T公司超期的应收账款金额达到12万元，其中超期16－30天的有7.5万，超期31－45天的有2.5万，超期超过90天的有2万元。

图6 超期催收表

(1)定义名称“账龄”

选择C2:I2区域，点击工具栏中的“公式”——“名称的定义”——“定义名称”，在跳出的对话框中，在名称对应处填上“账龄”，如下图所示:

图7 定义账龄

(2)计算各公司分分时段超期金额

在C3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$L$4:$L$16，“＜ =15”，应收账款基本信息!$D$4:$D$16，B3)”。

在D3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$D$4:$D$16，$B3，应收账款基本信息!$L$4:$L$16，“＞ =16”，应收账款基本信息!$L$4:$L$16，“＜ =30”)”。

在E3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$D$4:$D$16，$B3，应收账款基本信息!$L$4:$L$16，“＞ =31”，应收账款基本信息!$L$4:$L$16，“＜ =45”)”。

在F3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$D$4:$D$16，$B3，应收账款基本信息!$L$4:$L$16，“＞ =46”，应收账款基本信息!$L$4:$L$16，“＜ =60”)”。

在G3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$D$4:$D$16，$B3，应收账款基本信息!$L$4:$L$16，“＞ =61”，应收账款基本信息!$L$4:$L$16，“＜ =75”)”。

在H3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$D$4:$D$16，$B3，应收账款基本信息!$L$4:$L$16，“＞ =76”，应收账款基本信息!$L$4:$L$16，“＜ =90”)”。

在I3单元格输入:“=SUMIFS(应收账款基本信息!$G$4:$G$16，应收账款基本信息!$D$4:$D$16，$B3，应收账款基本信息!$L$4:$L$16，“＞ =91”)”

选中C3:I3区域，一起复制公式至3～8行。

(3)汇总

对超期催收表按行、按列进行汇总并且求出占比。

(4)突出显示

对存在超期应收账款的公司设置突出显示。选中B3:B8区域，选择:开始——条件格式——新建规则——使用公式确定要设置格式的单元格，设置如下图:

图8 突出显示超期公司

对超期金额进行突出显示，选择C3:I8区域，选择:开始——条件格式——突出显示单元格规则——大于，对区域中大于0的数设置突出显示，设置如下图:

图9 突出显示超期金额

(5)校验结果

为防止数据出错，在B13:C13设置数据校验，C13单元格输入:“=IF(J9=应收账款基本信息!B19，“正确”，“错误”)”。

5.创建各公司应收账款客户－账龄分析动态图

新建一张sheet，命名为“账龄分析图”。建立动态图形如下图所示:

图10 账龄分析图

(1)设置活动控件按钮

打开“开发工具——插入——Active X 控件——组合框”。在B3单元格相应位置画一个组合框，点右键——属性，跳出的属性框中如下图:

图11 绘制活动控件

在LinkedCell格内填上 A6，在 ListFillRange格内填上“客户”1。回车后活动控件设置完成。

(2)设置动态数据

按照上图6所示，在B5:H5单元格画好表格。在B6单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，2，FALSE)”，在 C6 单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，3，FALSE)”，在 D6 单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，4，FALSE)”，在 E6 单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，5，FALSE)”，在 F6 单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，6，FALSE)”，在 G6 单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，7，FALSE)”，在 H6 单元格输入公式:“=VLOOKUP($A6，账龄分析表!$B$2:$I$9，8，FALSE)”，在 I6 单元格输入:“=SUM(B6:H6)”，表格完成。

(3)绘制动态图表

选择A5:H6单元格，选择“插入——图表——条形图”即生成相应公司的应收账款账龄分析动态图。

6.创建公司整体应收账款账龄－客户分析动态图

新建一张sheet，命名为“总体应收账款账龄分析图”。建立动态图形如下图所示:

图12 总体应收账款账龄分析图

(1)设置账龄选择区

鼠标选择B3单元格，选择“数据——数据有效性——数据有效性”，在跳出的对话框中，“允许”单元格选择序列，“来源”单元格输入“=账龄”2。操作如下图，账龄区间选择菜单完成。

图13 设置账龄选择

然后在B3下拉菜单中选择“16－30天”。

(2)设置账龄分析表

A5:H6区域设置如图13所示的分析表。

首先在A6单元格输入公式:“=B3”。其次在B6单元格输入公式:“=HLOOKUP($A6，账龄分析表!$B$2:$I$8，MATCH(B$5，超期催收表!$B$2:$B$8，0)，FALSE)”，然后复制该公式至C6～G6单元格。H6单元格输入公式:“=SUM(B6:G6)”。账龄－客户分析表完成。

(3)绘制动态图表

选择A5:G6单元格，选择“插入——图表——条形图”即生成公司总体的相应账龄的各公司应收账款动态图。

四、结论及应用建议

通过以上6个步骤，应收账款账龄管理模型建立完成。模型总计6张sheet，分别为:客户及信用额度、应收账款基本信息、账龄分析表、超期催收表、账龄分析图、总体应收账款账龄分析图。

通过该模型，应收账款管理人员可以简便快速地输入客户信息、应收账款信息;可以直观地查询到每笔应收账款的金额、是否到期以及超期天数;可以满足不同客户经理的各自查询要求;可以提供公司整体的应收账款账龄情况以及超期应收账款情况。极大程度地简化了操作人员具体工作中的内容步骤，也为管理人员提供了及时有效的信息，目前在T公司应收账款管理中已经得到广泛应用，但是由于T公司的客户对象众多且不断变化，该模型尚需要使用人员不断地调整更新。另一方面，实际工作中本模型需要与公司的其他管理系统相对接，例如数据来源于公司的项目管理系统，输出的表格要应用于公司的下发报表系统等，因此在实际应用重要注意与其他系统的衔接与一致性。

［1］钟爱军.EXCEL在财务与会计中的高级应用［M］.武汉:武汉大学出版社，2012.

［2］Excel Home.Excel数据处理与分析实战技巧精粹［M］.北京:人民邮电出版社，2010.