巧用Excel实施应收账款的账龄分析.doc

从新的角度探讨一种以 Excel 作为分析工具的相对比较实用、比较有效、更加易于财务人员掌握的分析方法, 其基本思路是日常在应收账款发生或回款时建立 Excel 数据清单进行详细记录,在编制账龄分析表前进行业务的核销, 随后建立数据透视表编制账龄分析表, 这样的表可以被灵活地进行重新设置, 以满足不同的账龄分析目标。【关键词】应收账款; 账龄分析; Excel; 数据透视表中图分类号 F232文献标识码 A文章编号 1673- 0194(2006)10- 0037- 04应收账款是指企业因销售商品、产品或提供劳务等原因, 应向客户或接受劳务的客户收取的款项或代 垫的运杂费等。应收账款发生后, 企业应采取各种措 施, 尽量争取按期收回款项, 否则会因拖欠时间过长而 发生坏账, 使企业蒙受损失。其中一个重要的措施就是 及时地实施应收账款账龄分析, 编制账龄分析表。Ex- cel 是一种实施应收账款账龄分析的很好的工具, 应用 Excel 编制账龄分析表相对手工编制账龄分析表更加 快捷、精确, 账龄分析的结果更便于企业对其应收账款 实施监督, 以及时地收回款项, 降低企业经营风险。应 用 Excel 实施账龄分析, 编制应收账款账龄分析表的方 法有多种, 本文从一个新的角度, 探讨如何综合应用 Excel 的多种功能, 建立数据透视表来编制应收账款账 龄分析表的方法, 这种方法相对其他应用 Excel 实施账 龄分析的方法效率更高、更加实用。一、应用 Exce l 实施应收账款账龄分析的模式 的确定定期对应收账款进行整理, 分析企业应收账款在 各个账龄时间段的金额、百分比等分布情况, 编制应收 账款账龄分析表, 这就是应收账款的账龄分析。表 1 是某企业 2005 年 12 月 31 日的应收账款账龄分析表。表 1应收账款账龄分析表通过账龄分析可以了解到企业应收账款的分布情况, 可以运用最适当的收款政策收回款项, 此外还可以 预测有多少欠款会因拖欠时间太久而可能成为坏账。 对可能发生的坏账损失, 企业应提前做好准备, 充分估 计这一因素对损益的影响, 从而对应收账款的回收情 况实施有效的监督和管理。可见, 账龄分析是相当重要 的。应用 Excel 实施账龄分析可以对传统的模式进行扩 展, 以增加账龄分析的效果, 这完全是由于 Excel 具有 丰富的自动数据处理功能。1. 账龄时间段的确定账龄是指应收账款的发生时间与分析评价时间之 间的间隔。例如, 一笔应收账款的发生时间为 2006 年 1应收账款账龄应收账款金额( 元)百分比未到期30 00038%过期 30 天20 00025%过期 60 天15 00019%过期 90 天10 00012%过期 90 天以上5 0006%合计80 000100%虑信用期, 因为企业为了吸引客户, 一般允许客户从购2. 筛选货到付款之间有一段时间的间隔, 这就是信用期间。一般来说, 在信用期内的应收账款我们一般认为其是良 性的, 这样, 我们编制的应收账款账龄分析表中的账龄 可以这样划分: 信用期内、超过信用期 30 天、超过信用 期 60 天、超过信用期 90 天、超过信用期 90 天以上等, 这样的账龄的分析表对应收账款是否可以收回有更精 确的表示。此外, 对账龄时间段的划分, 企业可以根据账龄分 析的目标以及企业的特点来确定。当然, 一般来说, 账 龄时间段划分越灵活, 越细致, 所编制的账龄分析表更 易于对应收账款的可回收性进行判断, 更易于精确地 估计坏账, 但是能否编制出更加灵活细致的账龄时间 段的分析表与分析工具的性能有直接的关系, 手工就 比较难实现, 但 Excel 完全可以胜任, 可以满足账龄时 间段划分细密的要求。下面是应用 Excel 进行账龄分Excel 提供了数据筛选功能, 可以由操作人员指定筛选条件, 由系统将满足条件的数据筛选出来。数据筛 选又包括自动筛选和高级筛选两种方式。本文应用的 自动筛选方法是: 单击命令 【数据】|【筛选】|【自动筛 选】, 随后指定筛选条件进行数据的筛选工作。3. 数据透视表的建立数据透视表是一种对大量数据进行自动快速汇总 和交叉列表的交互式表格, 方法是: 单击命令【数据】|【数据透视表和图表报告】, 随后可以在向导的提示下,由操作员指定被汇总的数据, 汇总条件, “对某字段求 和、求平均”等汇总方式, 随后系统会自动生成数据透 视表。4. 所调用的函数NOW( ), 是 Excel 函数, 其功能是返回当前日期和时间所对应的系列数。例 如 , 在 A1 单 元 格 输 入 “=NOW()”, 则析的一种账龄时间段的划分样本。当然, 因为应收账款是流动资产, 所以对一年之内A1 单元格会显示的结果,的信息可以分得更细致一些,些。2. 增加客户信息一年以上可以更粗略一表示今天的日期及今天的时间。在应用 Excel 进行账龄分析时可以调用 NOW( )函数表示当前进行分析评价 应收账款的时间, 这样编制的账龄分析表就是一个动 态的表, 每天的账龄分析表都会由 Excel 自动更新数据内容, 达到实施监督应收账款发生情况的目的。此外还有 IF( )函数, 用于对账龄期间的公式进行 定义。三、应用 Exce l 实施应收账款账龄分析的方法( 一) 日常应用 Excel 记录应收账款的发生情况建立 Excel 数据清单, 包含如下一些数据项: 客户 名称、发货单号、业务发生日期、应收账款借方发生额、 应收账款贷方发生额、信用期限等, 记录应收账款相关 信息, 当应收账款发生或回款时, 及时将信息记录在数 据清单中。应收账款数据清单如图 1 所示。编制应收账款账龄分析表的目的之一就是要催收款项,所以可以在表 1 模式的账龄分析表中增加分析每个客户在不同账龄时间段内的应收账款分布情况,这样便于及时通知客户回款。如果是手工会计, 编制分 析表时增加分析客户的应收账款, 可能会增加更多的 工作量, 有时甚至会因耗费太多的人工、时间而不能实 现, 但是应用 Excel, 其实现起来是相当轻松的, 与编制 表 1 模式的账龄分析表的工作量没有特别明显的增 加。二、应用 Exce l 实施应收账款账龄分析所用到 的功能1. 排序Excel 提供了数据排序功能, 可以对工作表中的数 据按照某个或某些字段重新进行排列顺序。在进行账 龄分析时, 为了对客户数据进行分类汇总, 可以事先应 用 Excel 的排序功能, 其方法是: 选中被排序的数据清 单中的任一单元格, 单击命令【数据】|【排序】, 指定关图 1信用 期内30天60天90天120天180天270天360天12年23年3 年 以上生成新的未核销的应收账款数据清单, 如图 3 所示。行各种方式的核销, 生成多张账龄分析表, 用于比较进行更深入的分析, 这就是运用 Excel 进行账龄分析的魅 力所在。对于应收账款的核销, 可以采用系统自动核销或 手工核销的方式。本文主要讲解手工核销方法, 按照如 下步骤完成:1. 对应收账款清单进行排序, 排序的主要关键字 段为“客户名称”, 次要关键字为“业务发生时间”。2. 手工核销对排序过的应收账款清单, 人工进行对已经核销 的业务进行判断, 如果正好有一些业务能够借贷抵消, 则直接在各行的核销标志单元格中增加 “结算”两个 字, 如图 2 中第 16 行和 17 行荣乐公司的核销结果, 如 果不能正好核销, 则需要增加一行, 存储剩余未核销的业务, 如图 2 中大洋公司第 6 行和第 8 行的核销, 第 9图 33. 进行公式的定义, 以图 3 内容为例, 介绍公式定 义的步骤如下:( 1) “到期日”公式的定义: 在 F2 单元格输入文本 “到期日”, 在 F3 单元格, 定义公式如下: =C3+E3( 到期 日=业务发生时间+信用期限) 。( 2) 公式向下递推。鼠标指针指向 F3 单元格, 按住 填充柄向下填充进行公式的递推, 直至所有客户的到 期日全部自动生成为止。( 3) “逾期天数”公式的定义: 在 G2 单元格输入“逾 期天数”, 在 G3 单元格, 定义公式如下: =IF(NOW( )- F3>0,NOW( )- F3,0) ( 逾期天数=当前日期- 到期日) , 并 设置该单元格的单元格属性为“数值”型。( 4) 定义账龄公式: 在 H2 单元格输入文本“到期情 况”, 在 H3 输入公式如下:=IF (G3>1080,“(9) 超过信用期 3 年以上”,IF (G3>720,“(8)超过信用期 23 年”,IF(G3>360,“(7)超过信用期12 年”,IF(G3>180,“(6)超过信用期 180360 天”,IF(G3>90,“(5)超过信用期 90180 天”,IF(G3>60,“(4)超过信用 期 6090 天”,IF (G3>30,“(3) 超过信用期 3060 天”,IF (G3>0,“(2)超过信用期 030 天”,“(1)信用期内”)定义公式完成后, 将鼠标指针指向 H3 单元格, 按行是第 6 行和第 8 行核销后的结果,加的内容。是由操作人员增图 2照顺序自动排列数据, 所以上述公式中的各个账龄期称”字段到行区, “到期情况”到列区, 就得到如下格式的数据透视表, 如图 6:间在文本前增加了顺序标号( 1) 、( 2) 等, 主要是为了建立的数据透视表中账龄期间能按账龄时间段的前后顺 序排列。4. 建立数据透视表来生成账龄分析表, 单击 【数 据】|【数据透视表和图表报告】命令启动建立数据透视 表的向导, 在“数据透视表和数据透视图向导3 步骤 之 3”对话框中单击 “版 式 ”按 钮 , 将 “到 期 情 况 ”拖 到 “行”区, 将“客户名称”拖到“列”区, 这样就是指定了行 向和列向汇总的条件, 将“应收账款借方发生额”拖到 “数据”区, 并指定汇总方式为“求和”, 所指定的情况如 图 4 所示:图 6同样的方法可以重新指定行区、列区字段对已经 生成的账龄分析表进行信息变化, 以满足不同的分析 目的。对图 5 的账龄分析表, 可以将 B 列至 I 列的信息 隐藏, 得到表 1 模式的账龄分析表。这就是灵活的账龄 分析。五、结束语建立数据透视表、编制应收账款账龄分析表以实 施账龄分析的方法简单、易学、有效, 所编制的分析表 可以通过重新指定不同的字段到数据透视表“版式”设 置对话框的“页区、行区、列区、数据区”而自动地改变 其样式, 这样的分析表是动态灵活的, 更加便于对应收账款实施有效监督。图 4建立好的数据透视表如图 5:主要参考文献1 北京注册会计师考试委员会办公室.会计M.北京: 中国财政经济出版社, 2006.2 北京注册会计师考试委员会办公室.财务管理M.北京: 中国财政 经济出版社, 2006.3 傅秉潇.应收账款账龄分析表编制方法及应用J.财会通讯, 2005,( 3) .4 翟四美.如何运用 Excel 自动编制应收账款账龄分析表J.财会通 讯, 2003,( 11) .图 5图 5 就是包含客户信息的账龄分析表,这样的账龄分析表不是死的, 而是可以灵活地进行信息变化的,如在“数据透视表”命令按钮面板中单击【数据透视表】|【向导】命令, 在“数据透视表和数据透视图向导3 步