基于Excel的应收账款管理动态模型设计

景秋云 王莉婕 赵小龙

【摘 要】 企业在进行應收账款的管理时，往往面临着烦琐的数据处理问题。Excel作为最普及的办公软件之一，在数据处理、统计分析和辅助决策上有很强的优势，而且操作相对简单，为应收账款管理提供了很大的帮助。按照应收账款管理的目标需求，利用Excel创建了应收账款管理的模型，包括应收账款信用政策决策模型、应收账款账龄分析模型和重点欠款客户模型等。该模型可以简化工作人员在统计、分析数据时的工作量，并且采用分析表和分析图两种显示方式，能够生动、直观地反映相关的数据，及时、准确、高效地为决策者提供有用的信息，实现应收账款的动态管理。

【关键词】 应收账款管理; Excel建模; 信用政策; 账龄分析; 客户管理

【中图分类号】 F234.3  【文献标识码】 A  【文章编号】 1004-5937（2019）13-0138-05

应收账款管理是企业管理的重中之重，企业需要从庞杂的应收账款数据中快速找到有用的信息，提高管理的效率。目前，专业的财务软件虽然很普及，但是这些软件也有局限。比如，软件开发只考虑到多数企业的普遍问题，没考虑到不同企业的独特细节;购买成本高，后期维护费用大。Excel软件具有应用广泛、操作简单、数据分析能力强的特点，目前多数企业在应收账款管理时，还是会选择用专业财务软件提供基础数据，用Excel进行分析的方法。本文用Excel搭建了一个简便的模型，可以直观地选出信用决策方案，显示应收账款的账龄情况，筛选出需重点催收的客户，可以简化应收账款管理的工作[1]。

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

（一）制定应收账款信用政策

宽松的信用政策，可以扩大销售量、增加销售收入，同时也会增加应收账款的成本;相反，严格的信用政策，会影响企业的竞争力，使销量下降，销售收入减少，同时也会节约应收账款的成本。因此，选择信用政策就得在销售毛利和信用成本之间衡量。若只有一种信用政策方案，销售毛利减去信用成本后的收益大于零时方案才可行;若是有多种信用政策方案，在销售毛利减去信用成本后的收益大于零的基础上选择收益最大的方案。

（二）分析应收账款账龄

应收账款账龄是指企业销售商品时从发生赊欠开始，到应收账款收回的天数。账龄分析的目的是分时间段统计应收账款，有助于监督应收账款的回收情况，是实施催款计划和估计坏账损失的基础。

（三）筛选重点欠款客户

在管理应收账款时，要对客户进行分类管理，欠款越多，赊欠时间越长的客户，需要采取更强烈的催收策略，必要时要停止或限制供货。所以要按总欠款金额和赊欠的时间筛选出欠款比例较高和赊欠时间较长的客户。

二、基于Excel应用的应收账款管理模型的设计思路

本模型从信用政策决策、账龄分析、重点欠款客户三方面对应收账款的管理进行模型的搭建，使用的版本是Excel 2016。模型搭建的流程如图1。

1.搭建应收账款的信用政策决策模型，在多个信用政策方案中选择利润大于零且最高的方案。

2.建立客户基本信息，包括客户信息表和应收账款明细表，为账龄分析模型和重点欠款客户模型的搭建提供基础数据，这两表的数据可以从专业的财务软件导入，也可以用Excel创建。后期应收账款和客户的增减变化只需修改这两个表，账龄分析表和重点欠款客户表中相关数据会随之自动更新。

3.搭建应收账款账龄分析模型，按赊欠的时间段进行分析。根据“应收账款明细表”中的数据，使用Excel函数统计不同账龄段应收账款的数量、金额、所占的比例，并用图表直观地反映结果。

4.搭建重点欠款客户模型，按客户赊欠的金额进行分析。统计出每个客户赊欠的总金额和赊欠的时间，从每位客户欠款占总欠款比重和欠款时间等方面对应收账款进行分析。

三、基于Excel应用的应收账款管理模型的设计步骤

（一）建立信用政策决策模型

对信用政策产生影响的主要因素有信用成本前收益、机会成本、管理费用、坏账损失、折扣成本。计算公式：信用成本后收益=信用成本前收益-机会成本-管理费用-坏账损失-折扣成本。

1.信用成本前收益

销售量在一定范围内，不会引起固定成本的变化，固定成本是与决策无关的成本，此时，信用成本前收益=边际贡献=年赊销额-变动成本。

但随着信用政策的放宽，销售超过一定量时，导致固定成本增加，就要在决策中考虑固定成本的变化对信用决策的影响。此时，信用成本前收益=年赊销额-变动成本-增加的固定成本。

2.机会成本

应收账款的机会成本是指没有收回的应收账款中被占用的资金所丧失的潜在收益，固定成本不变时只考虑变动成本中被占用的资金。

机会成本=年赊销额÷360×信用期×变动成本率×机会成本率。

其中，信用期是企业允许顾客从购货到付款之间的时间。

变动成本率=变动成本÷销售额。

机会成本率可采用投资者要求的必要投资报酬率或同期的有价证券利率。

3.管理成本

管理成本主要由管理人员的工资、办公费用、对客户资信调查的费用、收集信用信息的费用、催收的费用等组成。管理成本=年赊销额×收账费用率。

4.坏账损失

应收账款的坏账损失是指因应收账款没有收回而产生的损失。坏账损失=年赊销额×坏账损失率。坏账损失率可以根据企业应收账款的实际情况预计，采用的方法有销售百分比法、账龄分析法、应收账款余额百分比法等。

5.折扣成本

现金折扣是企业为了鼓励顾客在规定的还款期内早日还款，而给予的还款金额的减让。现金折扣带来折扣成本，其次还会影响到平均收款的天数，导致机会成本的变化。

本例中，有4个备选方案，見图2信用政策决策模型中的方案1至方案4，从中选出最佳方案。选择最佳决策方案的依据为：所有的方案信用成本后收益都小于零，则显示无方案，否则选择信用成本后收益最高的方案。

图2中灰色区域为自动计算部分，公式如下：

B7=B5*B6，B9=B7*B8，B11=B7-B9-B10，B14=

B7/360*B4*B8*B13，B16=B7*B15，B18=B7*B17，B20=

B14+B16+B18，B21=B11-B20，各行其余公式用“自动填充”功能填充。

D4=60%*10+15%*20+（1-60%-15%）*60，在方案3中假定60%的客户在10天内还款，15%的客户在20天内还款，D4为方案3的平均收款期。

D19=D7*（2%*60%+1%*15%），D19为方案3的平均折扣成本。

B22=IF（MAX（B21：E21）<0，"无方案"，"应采用"&INDEX（B3：E3，1，MATCH（MAX（B21：E21），B21：E21，0）））。

此信用政策决策模型可满足一般的需求，复杂一些的信用政策决策模型的设计还可以考虑到外部环境的变化、企业对风险的偏好、借款利息、所得税等因素[2]。

（二）建立客户的基本信息

客户的基本信息包含“客户信息表”和“应收账款明细表”两部分。

1.创建“客户信息表”，如图3。

该表不仅可以用来管理往来客户的信息，还可以为今后编制各种应收账款的分析表提供基础数据，方便其他表直接生成数据。

2.创建“应收账款明细表”，如图4。

本例数据分析日期为2017年12月22日，为验证本例数据，请将计算机系统当前日期调整为2017年12月22日。

在本例中，根据图2信用政策决策模型，得知该企业的最佳信用政策为方案3（2/10，1/20，n/60），信用期为60天。灰色区域为自动计算部分，公式如下：

（1）客户名称：C4=VLOOKUP（B4，客户信息表！$A$3：$B$24，2，FALSE），目的是用VLOOKUP函数在客户信息表和应收账款明细表之间建立动态链接，方便后期数据的维护。

（2）应收账款余额：F4=D4-E4。

其中，仍在信用期内的应收账款的金额：

G4=IF（AND（（TODAY（）-A4）>0，（TODAY（）-A4）<=60），$F4，0）。TODAY函数表示系统当前日期。

超期的应收账款金额：

超期1—30天：H4=IF（AND（（TODAY（）-A4）>60，（TODAY（）-A4）<=90），$F4，0）。

超期31—90天：I4=IF（AND（（TODAY（）-A4）>90，（TODAY（）-A4）<=150），$F4，0）。

超期91—180天：J4=IF（AND（（TODAY（）-A4）>150，（TODAY（）-A4）<=240），$F4，0）。

超期181天以上：K4=IF（（TODAY（）-A4）>240，$F4，0）。

各列中其余公式用“自动填充”功能填充。

（三）创建应收账款账龄分析模型

应收账款账龄分析模型包括分析表和分析图两部分。

1.创建应收账款账龄分析表，如图5。

B4=COUNTIF（应收账款明细表！G：G，">0"）

B5=SUM（B6：B9）

B6=COUNTIF（应收账款明细表！H：H，">0"）

B7=COUNTIF（应收账款明细表！I：I，">0"）

B8=COUNTIF（应收账款明细表！J：J，">0"）

B9=COUNTIF（应收账款明细表！K：K，">0"）

B10=B4+B5

C4=SUM（应收账款明细表！G：G）

C5=SUM（C6：C9）

C6=SUM（应收账款明细表！H：H）

C7=SUM（应收账款明细表！I：I）

C8=SUM（应收账款明细表！J：J）

C9=SUM（应收账款明细表！K：K）

C10=C4+C5

D4=C4/$C$10，用“自动填充”功能填充该列。

将D4：D10区域设置为百分比格式，并保留2位小数。

通过函数在应收账款明细表和账龄分析表之间建立动态链接。

通过账龄分析表可以了解到以下信息：

该企业应收账款的总额为1 819 086.40元。

应收账款在信用期内的有1 013 886.40元，占应收账款总额的55.74%。这些款项仍在信用期内，不需要向客户催收，但要进行必要的监督。

应收账款超过了信用期的有805 200.00元，占全部应收账款的44.26%。其中，超过信用期1—30天的金额为78 960.00元，占全部应收账款的4.34%，这部分欠款收回的可能性很大。超过信用期31—90天的金额为251 200.00元，占全部应收账款的13.81%，这部分欠款的回收有一定难度。超过信用期91—180天的金额为374 960.00元，占全部应收账款的20.61%、超过信用期181天以上的金额为100 080.00元，占到了应收账款总金额的5.50%，这部分欠款由于拖欠的时间最长，成为坏账的可能性最大。

对于欠款时间不同的企业采用不同的收账政策，以避免对企业的经营产生较大的影响。

2.创建应收账款账龄分析图，如图6。

使用图表分析应收账款账龄，可使结果更为直观。具体步骤如下：

（1）选定数据

依次选择账龄分析表中A4、D4、A6、D6、A7、D7、A8、D8、A9和D9，创建“应收账款账龄分析图”。需要严格按以上顺序进行选定，即先选择应收账款项目单元格，再选中相应金额单元格，否则制作出的图表不能达到预期的效果[3]。

（2）插入图表

在“插入”选项卡中“图表”组中选择“二维饼图”——“子母饼图”，将图表移动到新工作表中，命名为“应收账款账龄分析图”。修改数据系列格式，将第二绘图区的值改为4，这样第一绘图区显示的是信用期内和超信用期金额的比，第二绘图区显示的是超信用期的四个组成部分。

（3）修饰图表

图表样式选择“样式12”，选择“更改颜色”—“单色”—“颜色11”。添加数据标签，设置数据标签的格式为“百分比”，位置为“数据标签外”;选择数字标签，设置字体为20。图例位置选择“靠右”，图例字体选择18，添加图例实线边框。

通过图表可以看出应收账款的结构：信用期内的占55.74%;超出信用期的占44.26%，其中超期1—30天的占4.34%，超期31—90天的占13.81%，超期91—180天的占20.61%，超期181天以上的占5.50%。该企业超期的应收账款所占的比重过大，应加强管理。

（四）创建重点欠款客户分析模型

客户欠款分析模型包括分析表和分析图两部分，可反映出累计欠款较高的客户。

1.创建重点客户欠款分析表，如图7

同一个客户会发生不同时期的欠款，此表对同一个客户不同时间的欠款进行汇总，目的是为了分析每个客户的欠款总金额和不同时间段欠款的分布情况，找出欠款金额多，时间长的重点客户，进行重点管理。

公式如下：

A4=客户信息表！A3，B4=客户信息表！B3，C4=客户信息表！C3，D4=客户信息表！D3

E4=SUMIF（应收账款明细表！$C：$C，$B4，应收账款明细表！F：F）

拖动E4单元格右下角的填充柄到J4单元格，接着拖动A4：J4区域右下角的填充柄到J25单元格，快速完成A5：J25区域里所有单元格公式的定义。

选择E列到J列，选择“条件格式”——“数据条”——“渐变填充”，可以直观地显示数据的大小。

2.创建重点欠款客户分析图，如图8

选择“重点欠款客户分析表”工作表中的B2：B25和F2：J25区域，单击“插入”选项卡下“图表”分组命令中的“堆积柱形图”，在数轴上用不同的颜色显示出同一个客户在不同时间段的欠款情况和该企业的总欠款情况。能看到欠款较多的是客户13、客户21、客户16等企业，要着重加强这些企业应收账款的管理，以免给企业带来较大的损失。

四、结语

应收账款管理在企业运营中起着重要的作用，首先，用Excel建立应收账款管理模型，从应收账款信用政策决策、应收账款账龄分析和重点欠款客户分析等方面对应收账款进行管理，可以简化员工的工作量，快速准确地给决策层提供数据;其次，在应收账款账龄分析表、重点欠款客户分析表中采用了公式和函数，可以動态链接基础信息和分析表、分析图，方便后期数据的维护，为企业应收账款的管理提供实时信息;最后，该模型采用了图、表两种显示方法，使结果的展示更为直观。

【参考文献】

[1] 周红.Excel在财务工作中的应用探索三例[J].财会学习，2016（11）：13-15.

[2] 穆敏丽.应收账款信用政策决策模型的改进[J].财会月刊，2010（17）：46-47.

[3] 郭燕，张林义，陈海燕.中文版Excel 2007财务应用实例与操作[M].北京：航空工业出版社，2011：122.