应收账龄分析(公式).docx

应收帐款帐龄分析模型(公式版) 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依此类推，可得到其它年度的帐龄分析表。