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

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