巧用Ｅｘｃｅｌ實施應收賬款的賬齡分析

【摘要】 實施應收賬款的賬齡分析是一項重要的應收賬款管理措施，財務人員應該選用高效的方法開展分析，本文從新的角度探討一種以Excel作為分析工具的相對比較實用、比較有效、更加易于財務人員掌握的分析方法，其基本思路是日常在應收賬款發生或回款時建立Excel數據清單進行詳細記錄，在編制賬齡分析表前進行業務的核銷，隨后建立數據透視表編制賬齡分析表，這樣的表可以被靈活地進行重新設置，以滿足不同的賬齡分析目標。

【關鍵詞】 應收賬款；賬齡分析；Excel；數據透視表

[中圖分類號] F232

[文獻標識碼]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）.