［關鍵詞］運用EXCEL 分析 應收賬款回收
［摘 要］應收賬款回收情況的監督主要通過編制賬齡分析表進行，對於客戶衆多、業務頻繁的企業來說，運用EXCEL快速、準確編制賬齡分析表，並據此制定有效的收賬政策，對於加強應收賬款管理，提高資金回籠速度，降低經營風險具有非常重要的意義。
應收賬款回收情況的監督主要通過編制賬齡分析表進行，一般來講，應收賬款拖欠時間越長，款項收回的可能性越小，形成壞賬的可能性就越大。通過賬齡分析可以揭示每個客戶的風險性和每筆應收賬款產生壞賬的可能性，並以此作爲提取壞賬準備的依據，因此對於客戶衆多、業務頻繁的企業來說，運用EXCEL快速、準確編制賬齡分析表，並據此制定有效的收賬政策，對於加強應收賬款管理，提高資金回籠速度，降低經營風險具有非常重要的意義。
一、賬齡的確定
賬齡的時間段一般根據客戶的信用期，結合會計準則對計提壞賬準備標準劃分。劃分賬齡的時間段越細、間隔越短，壞賬估計就越精確。通常情況下，對1年以內應收賬款賬齡劃分較細，而對1年以上的應收賬款則不能做太多區分。
二、回收情況表的製作
爲數據提取階段，主要提取應收賬款明細賬的“科目名稱”、“年初餘額”、“借方發生數”、“貸方發生數”，將以上數據分別複製到回收情況表的“客戶名稱”、“年初欠款餘額”、“本月發生應收賬款”、“本月收回賬款”等，然後製作成表格。要求每月單獨製作一個工作表
三、賬齡分析表的製作
爲數據分析階段，先從回收情況表提取有關數據，由於每月都有可能增加或減少應收賬款客戶，導致每月客戶的順序都不一樣，所以要用較爲複雜的EXCEL函數嵌套搜索同樣的客戶名並返回相應的數值，對於賬齡和金額部分我們假設客戶歸還賬款時按照應收賬款發生的先後順序收回賬款，即歸還的賬款先沖銷時間較長的應收賬款。
下面通過實例來說明運用EXCEL分析應收賬款收回情況的方法和步驟。
［例］某企業2005年1－6月的應收賬款明細賬如下，請根據明細賬對應收賬款回收情況進行分析。
某單位2005年1-6月應收賬款明細賬
單位:元
科目名稱 年初餘額 1月份 2月份 3月份 4月份 5月份 6月份
借方 貸方 借方 貸方 借方 貸方 借方 貸方 借方 貸方 借方 貸方
東方公司 10000 3000 3000 3000 5000 6000 3000 7000 8000
南方公司 20000 4000 5000 8000 2000 3000 5000 7000
西方公司 30000 5000 1000 3000 2000
北方公司 1000 4000 2000 6000 3000 2000
合 計 60000 4000 8000 9000 3000 9000 15000 8000 2000 16000 7000 3000 17000
（1）確定賬齡時間段劃分標準
可以將賬齡時間段劃分爲0～30天、31～60天、61～90天、91～120天、121～180天、180天以上。
（2）製作應收賬款回收情況表
第一步：數據提取
將應收賬款明細賬轉化爲EXCEL文件，便於複製，如果財務軟件不具備該功能，可以用數據庫運用程序打開存放應收賬款明細賬數據庫文件，如擴展名爲DBF文件，可用FOXPRO程序打開，然後導出EXCEL格式文件。以利於應收賬款回收情況表提取相關數據。
第二步：編制應收賬款回收情況表
根據2005年1－6月明細賬編制一個回收情況表，該表爲計算賬齡的基礎數據表格。首先，製作一個“應收賬款回收2005 ”，在該文件中每月設一個工作表，工作表格分別爲“05.01”、“05.02”、“05.03”、“05.04”、“05.05”和“05.06”。分別存放2005年1－6月應收賬款回收情況表的數據，將應收賬款明細賬的“客戶名”、“年初餘額”、“借方”、“貸方”等列內容分別複製到“05.01”工作表的“客戶名稱”、“上年餘額”、“本期應收賬款”、“本期回收款”等列。如下表
某單位2005年1月應收帳款回收情況表
單位：元
行號 客戶名稱 上年欠款餘額 本月發生應收賬款 本月應收賬款
1 東方公司 10000 3000
2 南方公司 20000 4000
3 西方公司 30000 5000
4 北方公司
合 計 60000 4000 8000
（3）製作應收賬款賬齡分析表
A B C D E F G H I J K L M N O P Q R S T U V W X
1 某單位2005年6月應收賬款賬齡分析表
2 單位:元
3 行
號 客戶
名稱 年初
欠款
餘額 2005年應收賬款發生數 2005年收回應收賬款 累計
欠款
餘額 賬齡及金額
4 小計 1月 2月 3月 4月 5月 6月 小計 1月 2月 3月 4月 5月 6月 0～
30天 31～
60天 61～
90天 91～
120天 121～
180天 180天
以上
5 1 東方
公司 10000 18000 0 3000 5000 3000 7000 0 20000 3000 3000 6000 0 0 8000 8000 0 7000 1000 0 0 0
6 2 南方
公司 20000 14000 4000 5000 0 2000 3000 0 20000 0 0 8000 0 5000 7000 14000 0 3000 2000 0 9000 0
7 3 西方
公司 30000 3000 0 0 0 3000 0 0 8000 5000 0 1000 0 2000 0 25000 0 0 3000 0 0 22000
8 4 北方
公司 0 14000 0 1000 4000 0 6000 3000 4000 0 0 0 2000 0 2000 10000 3000 6000 0 1000 0 0
9 合 計 60000 49000 4000 9000 9000 8000 16000 3000 52000 8000 3000 15000 2000 7000 17000 57000 3000 16000 6000 1000 9000 22000
第一步：數據提取
應收賬款賬齡分析表中的“年初欠款餘額”、“應收賬款發生數”、“收回應收賬款”有關數據應從“應收賬款回收2005”文件中提取，由於各月的客戶有增有減使每個客戶在工作表中的位置可能不一樣，因此在使用取數公式時，需用“查找與引用”函數INDEX，具體公式如下，以東方公司“年初欠款餘額”C5單元格爲例。
=IF(ISERROR(INDEX(#39;應收賬款回收)05.01#39;!$A$1:$Z$200,MATCH($B5,#39; [應收賬款回收]05.01#39;!$B$1:$B$200,),3))=TRUE,"",INDEX(#39; [應收賬款回收]05.01#39;!$A$1:$Z$200,MATCH($B5,#39; [應收賬款回收]05.01#39;!$B$1:$B$200,),3))
該公式看似複雜，其實用了一個嵌套，IF（ISERROR(INDEX函數)＝TRUE，“”，INDEX函數），其含義是如果INDEX函數出現了錯誤的結果（如＃NA，＃VALUE），讓本單元格數據爲空，否則運行INDEX函數。
INDEX(#39; [應收賬款回收]05.01#39;!$A$1:$Z$200,MATCH($B5,#39; [應收賬款回收]05.01#39;!$B$1:$B$200,),3)的含義是，在“應收賬款回收2005”05.01工作表第3列（上年欠款餘額）中，查找並引用對應於B5單元格內容的值，即B5單元格內容在05.01工作表中的第3列值。運用這個公式的.好處是，無論“應收賬款賬齡分析表”中的“東方公司”在“應收賬款回收2005”05.01工作表B列的第幾行，都可以取得其對應的第3列“上年欠款餘額”的值。
將該公式複製下去，可得到本列其他各列的值；將該公式橫向複製，並將公式中的相應月份（如“05.02”）替換爲單元格所對應的月份（如“05.01”），可自動得到其他各行的值。
第二步：數據分析
我們假設客戶歸還賬款時按照應收賬款發生的先後順序回款，即歸還的賬款先沖銷時間較長的應收賬款
以東方公司爲例。其賬齡及金額段公式如下
賬齡 東方公司公式 所在單元格
0～30天 =IF(R5<=0,0,if(r5>=SUM(J5:J5),J5,R5)) S5
31～60天 =IF(R5<=0,0,if(r5>=SUM(I5:J5),I5,IF(R5-SUM(J5:J5)<0,0,R5-SUM(J5:J5)))) T5
61～90天 =IF(R5<=0,0,if(r5>=SUM(H5:J5),H5,IF(R5-SUM(I5:J5)<0,0,R5-SUM(I5:J5)))) U5
91～120天 =IF(R5<=0,0,if(r5>=SUM(G5:J5),G5,IF(R5-SUM(H5:J5)<0,0,R5-SUM(H5:J5)))) V5
121～180天 =IF(R5<=0,0,if(r5>=SUM(E5:J5),E5+F5,IF(R5-SUM(G5:J5)<0,0,R5-SUM(G5:J5)))) W5
180天以上 =IF(R5<=0,0,if(r5>=D5,R5-D5,0)) X5
因爲我們假設歸還的賬款先沖銷時間較長的應收賬款，所以“累計欠款餘額”由最近幾個月的“應收賬款發生數”組成。在設計公式時，要先將“累計欠款餘額”與相應期間的“應收賬款發生數”相比較，以賬齡爲31～60天T5單元格爲例，如果5月－6月“應收賬款發生數”合計小於“累計欠款餘額”則賬齡爲31～60天的金額爲5月份應收賬款發生數，如果5月－6月“應收賬款發生數”合計大於“累計欠款餘額”則賬齡爲31～60天的金額爲“累計欠款餘額”的與6月份“應收賬款發生數”的差額。
將以上公式複製第5行以下各行。至此，6月份的賬齡分析表文件“應收賬款賬齡分析表”即告完成。
綜上所述，根據單位應收賬款明細賬有關基礎數據，利用EXCEL函數公式定義需要計算的各個項目，則可自動地編制出應收賬款賬齡分析表，更加方便、快捷、最大限度地提高工作效率，對於應收賬款發生比較多的單位其效果尤爲明顯。
