应收帐款帐龄分析模型(公式版)
2010-02-03 11:32:56| 分类： 默认分类|举报|字号 订阅
应收帐款帐龄分析模型(公式版)(2008-04-30 19:47:43) 标签：财经 分类：财务模型 应收帐款是企业最重要的资产之一。因其流动性强、风险大的特点，加上其在企业资产中一般均占有相当大的比重，因而对应收帐款的管理往往是企业财务管理甚至企业经营管理中的重点。应收帐款帐龄分析是实现应收帐款管理的主要手段，通过帐龄分析可以揭示出每个客户的风险性和每笔应收帐款产生坏帐的可能性，并以此作为提取坏帐准备的依据。因此，准确、快速地编制应收帐款帐龄分析表，对企业加强应收帐款管理、提高资金回笼速度、降低经营风险具有非常重要的意义。
然而，对于财务人员来说，按月编制应收帐款帐龄分析表并不是一件轻松的事情。我国多数企业的信息化程度较低，受规模、管理规范程度等因素的制约，很多企业没有必要或条件实现业务、财务的一体化信息化管理，大量信息通过手工进行收集、汇总和分析，因此对于象帐龄分析这类需连续进行的、复杂程度较高的工作，多数企业的财务人员选择了按半年或年度开展一次的做法，无法按月分析，这在一定程度上加大了应收帐款的管理风险。遗憾的是，目前我国的财务软件在这一方面也无所作为。
目前对应收帐款帐龄的采集，多数采用从应收帐款明细帐中逐笔提取、分析的方式进行的，其结果必然是速度慢、工作量大，其准确性也取决于财务人员工作的认真细致程度，对于客户数量众多、业务频繁的企业来说，帐龄分析近乎一件“不可能的任务”。
笔者在多年的管理实践和对EXCEL的熟练应用中，总结出一种结合财务软件、利用EXCEL自动快速地编制应收帐款帐龄分析表的方法，现介绍如下。
本方法是在按月收集销售客户销售额、回款额的基础上，将近几年每个客户、每个月销售额、回款额的数据反映在同一张工作表中，然后运用EXCEL函数，自动、快速地编制出各种帐龄段金额分布的应收帐款帐龄分析表。操作步骤如下：
一、确定帐龄时间段
帐龄时间段的划分，一般要根据企业给予客户的信用期，还要结合会计准则对计提坏帐准备标准的规定，帐龄时间段划分越细、间隔越短，坏帐的估计就越精确。通常情况下，对1年以内应收帐款的帐龄划分较细，而对1年以上的应收帐款则不做太多区分。以下是笔者采用的帐龄时间段的划分标准：
≤30天 ≤60天 ≤90天 ≤120天 ≤180天 ≤270天 ≤360天 1~2年 2~3年 ≥3年
财务人员可根据本企业的实际情况进行帐龄时间段的具体划分。本文将根据以上标准进行表格公式的设置。
帐龄时间段的划分标准，决定了本文涉及的表格数据采集时间段的长度及表格格式。由于帐龄分析需要3年以上数据，因此以2003年为基准时间计算，需要取得2000、2001、2002年连续3年的数据。
二、创建两类EXCEL工作文件
将这两类文件分别命名为“销售回款2003”和“应收帐龄分析200306”。前者为计算帐龄的基础数据表格，每年一个文件，每月一个工作表，工作表名称分别为0301、0302，…；后者为数据汇总和分析结果表格，由于设置的公式多、数据量大，文件容量较大，为操作方便，最好每月一个文件，文件名后六位数代表所在年月。
三、 “销售回款”表的设计与制作
1、 “销售回款”表的格式：应收帐款帐龄分析的依据无外乎销售发生的时间和金额、回款的时间和金额等几个因素，因此，本表的设置应体现出这些因素。我们先设置“销售回款2003”表的格式：
2003年1月销售回款明细表
金额单位：人民币元
序号 客户名称 客户类别 所在城市 上年欠款余额 销售净额 回款额
1
2
3
合计
上表在“客户名称”后面设置的“客户类别”、“所在城市”等栏目，是对客户属性的描述，对于今后开展管理分析将大有裨益，可根据企业业务特点进行灵活的设置。
将该工作表命名为“0301”（代表1月份的数据），并在同一个工作薄中复制11份，分别命名为“0302”、…、“0312”，代表2003年2~12月的数据。
以同样方法制作“销售回款2000”、“销售回款2001”、“销售回款2002”三个表格文件。
2、 “销售回款”表的数据收集
对于未实行会计电算化的企业，该表只能通过手工方式进行数据录入了，当然如果数据已在其它EXCEL文档中，也可从其它EXCEL表格中导入。对于已实行会计电算化的企业来说，完全可以实现上表数据的自动导入，以“销售回款2003”中“0301”工作表为例，对操作方法说明如下：
a) 在财务软件中，将当月应收帐款客户级明细帐（不同财务软件的操作方法不一样，这里无法更明确地提出详细的操作细则）输出至EXCEL文件；
b) 将该EXCEL文件中的“客户名称”、“上年欠款余额”、“借方金额”、“贷方金额”列内容分别复制到“0301”工作表的“客户名称”、“期初余额”、“销售净额”、“回款额”列，至于“客户类别”、“所在城市”等信息，如果财务软件无法提供，可以手工输入或从事先建立的客户基础信息表格中取得。
以同样方法可以收集到其它各月（年）的数据。
四、 “应收帐龄分析”表的设计与制作
帐龄分析公式的前提假设：⑴将每个客户当作一个整体看待，帐龄分析不具体到客户的某笔单项业务；⑵客户回款时，总是按照应收帐款发生的先后顺序回款，即回款先冲销时间较长的应收帐款（类似于存货核算中的先进先出法）。
由于在具体做1~3年帐龄分析公式时，要将近四年的销售、回款数据列在同一张表上，会使本文的文字表述及表格显示极为繁琐，因此本文仅介绍1年以内帐龄段的帐龄分析方法。
1、 “应收帐龄分析
”表的格式：
2、 “应收帐龄分析”表的数据来源：
表头的字母A、B、…、EG，表示EXCEL表格中的列标（其中隐藏了一些列，隐藏列中含2000、2001年各月数字，不用手工录入，在下面的公式设置中会引用到）；表头的1、2、…、12，表示EXCEL表格中的行标，可根据客户数量在第9行前增加行，表中的公式将不受影响。
上表有四种区域，其数据来源不同，分别按以下步骤得到：
a) 无底纹的单元格区域，需手工录入。
b) 底纹为竖条的区域，即“客户名称”列，需要从“销售回款”表所在月份“0301”工作表（这里以2003年1月为例）中复制过来。
c) 底纹为斜条纹的区域，即“销售额”、“回款额”和“欠款额”的“上年余额”列，需从“销售回款”各月的工作表中取数。由于各月的客户数量和每个客户在工作表中的位置（行标）可能均不一样，因此在使用取数公式时，不能用简单的“=”号从各月工作表中取数，而用“查找与引用”函数INDEX，具体公式如下（以AC列0201为例）：
=IF(ISERROR(INDEX("[销售回款
2002.xls]0201"!$A$1:$Z$200,MATCH($B5,"[销售回款
2002.xls]0201"!$B$1:$B$200,),6))=TRUE,"",INDEX("[销售回款
2002.xls]0201"!$A$1:$Z$200,MATCH($B5,"[销售回款
2002.xls]0201"!$B$1:$B$200,),6))
该公式用了三个函数钳套，分别是IF、ISERROR和INDEX，其含义是，如果INDEX函数出现了错误的结果（如#NA、#VALUE等，即B5中的客户名称在“销售回款”0201工作表中不存在），让本单元格显示为空，否则运行
INDEX函数。IF
函数帮助我们美化工作表，避免在表格中出现诸多错误信息符号。
INDEX函数即本公式第三部分、即第二组划线部分的含义是，在“销售回款2002”0201工作表第6列（销售额）中，查找并引用对应于B5单元格内容的值，即B5单元格内容在0201工作表中的第6列值。运用这个公式的好处是，无论“应收帐龄分析”表中的“A公司”在“销售回款2002”0201工作表B列的第几行，都可以取得其对应的第6列“销售额”的值。
将该公式纵向复制下去，可得到本列其它行的公式和值；将该公式横向复制，并将公式中的相应年月（如“2002”、“0201”）替换为单元格所对应的年月（如“2003”、“0301”），可自动得到其它各年（月）的值。
这种数据导入技巧能帮助我们避免大量的数据重复录入工作，在我们的实际工作中应用范围极广。
d) 底纹为散点状的单元格区域，为帐龄分析区，帐龄及坏帐额的计算公式放置于此。
以2003年1月为例，各种帐龄段的公式如下表。
e) 在表格中的“坏帐准备计提比例”所在行对应的DX~EF，可以根据企业实际情况设定各种帐龄段的坏帐准备计提比例，在EG5单元格设置以下公式即可计算出每个客户的坏帐准备额：
=SUMPRODUCT(DX5:EE5,DX$11:EE$11)
f) 将以上公式复制到第5行以下的各行。至此，1月份的帐龄分析表文件“应收帐龄分析0301”即告完成。
将“应收帐龄分析0301”文件在同一文件目录中复制11份，并分别更名为“应收帐龄分析0302”、…、0312。重复上述b~f步骤，但需将公式中表示年月的数字替换为文件所属年月，2~12月的帐龄分析公式见附表1。
依此类推，可得到其它年度的帐龄分析表。