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