如何運用excel分析應收賬款回收情況

1、<p>　　如何運用EXCEL分析應收賬款回收情況</p><p>　?。坳P鍵詞］運用EXCEL 分析 應收賬款回收</p><p>　?。壅?要］應收賬款回收情況的監(jiān)督主要通過編制賬齡分析表進行，對于客戶眾多、業(yè)務頻繁的企業(yè)來說，運用EXCEL快速、準確編制賬齡分析表，并據(jù)此制定有效的收賬政策，對于加強應收賬款管理，提高資金回籠速度，降低經(jīng)營風險具有非常重要的意義。</p

2、><p>　　應收賬款回收情況的監(jiān)督主要通過編制賬齡分析表進行，一般來講，應收賬款拖欠時間越長，款項收回的可能性越小，形成壞賬的可能性就越大。通過賬齡分析可以揭示每個客戶的風險性和每筆應收賬款產(chǎn)生壞賬的可能性，并以此作為提取壞賬準備的依據(jù)，因此對于客戶眾多、業(yè)務頻繁的企業(yè)來說，運用EXCEL快速、準確編制賬齡分析表，并據(jù)此制定有效的收賬政策，對于加強應收賬款管理，提高資金回籠速度，降低經(jīng)營風險具有非常重要的意義。&l

3、t;/p><p><b>　　一、賬齡的確定</b></p><p>　　賬齡的時間段一般根據(jù)客戶的信用期，結(jié)合會計準則對計提壞賬準備標準劃分。劃分賬齡的時間段越細、間隔越短，壞賬估計就越精確。通常情況下，對1年以內(nèi)應收賬款賬齡劃分較細，而對1年以上的應收賬款則不能做太多區(qū)分。</p><p>　　二、回收情況表的制作</p><

4、;p>　　為數(shù)據(jù)提取階段，主要提取應收賬款明細賬的“科目名稱”、“年初余額”、“借方發(fā)生數(shù)”、“貸方發(fā)生數(shù)”，將以上數(shù)據(jù)分別復制到回收情況表的“客戶名稱”、“年初欠款余額”、“本月發(fā)生應收賬款”、“本月收回賬款”等，然后制作成表格。要求每月單獨制作一個工作表</p><p>　　三、賬齡分析表的制作</p><p>　　為數(shù)據(jù)分析階段，先從回收情況表提取有關數(shù)據(jù)，由于每月都有可能增加

5、或減少應收賬款客戶，導致每月客戶的順序都不一樣，所以要用較為復雜的EXCEL函數(shù)嵌套搜索同樣的客戶名并返回相應的數(shù)值，對于賬齡和金額部分我們假設客戶歸還賬款時按照應收賬款發(fā)生的先后順序收回賬款，即歸還的賬款先沖銷時間較長的應收賬款。</p><p>　　下面通過實例來說明運用EXCEL分析應收賬款收回情況的方法和步驟。</p><p>　　［例］某企業(yè)2005年1－6月的應收賬款明細賬如下

6、，請根據(jù)明細賬對應收賬款回收情況進行分析。</p><p>　　某單位2005年1-6月應收賬款明細賬</p><p><b>　　單位:元</b></p><p>　　科目名稱 年初余額 1月份 2月份 3月份 4月份 5月份 6月份</p><p>　　借方 貸方 借方 貸方 借方 貸方 借方 貸方 借方 貸方 借方

7、 貸方</p><p>　　東方公司 10000 　 3000 3000 3000 5000 6000 3000 　 7000 　 　 8000</p><p>　　南方公司 20000 4000 　 5000 　 　 8000 2000 　 3000 5000 　 7000</p><p>　　西方公司 30000 　 5000 　 　 　 1000 3000 　

8、 　 2000 　 　</p><p>　　北方公司 　 　 　 1000 　 4000 　 　 2000 6000 　 3000 2000</p><p>　　合 計 60000 4000 8000 9000 3000 9000 15000 8000 2000 16000 7000 3000 17000</p><p>　　（1）確定賬齡時間段劃分標準</p

9、><p>　　可以將賬齡時間段劃分為0～30天、31～60天、61～90天、91～120天、121～180天、180天以上。</p><p>　?。?）制作應收賬款回收情況表</p><p><b>　　第一步：數(shù)據(jù)提取</b></p><p>　　將應收賬款明細賬轉(zhuǎn)化為EXCEL文件，便于復制，如果財務軟件不具備該功能，可

10、以用數(shù)據(jù)庫運用程序打開存放應收賬款明細賬數(shù)據(jù)庫文件，如擴展名為DBF文件，可用FOXPRO程序打開，然后導出EXCEL格式文件。以利于應收賬款回收情況表提取相關數(shù)據(jù)。</p><p>　　第二步：編制應收賬款回收情況表</p><p>　　根據(jù)2005年1－6月明細賬編制一個回收情況表，該表為計算賬齡的基礎數(shù)據(jù)表格。首先，制作一個“應收賬款回收2005 .xls”，在該文件中每月設一個工作

11、表，工作表格分別為“05.01”、“05.02”、“05.03”、“05.04”、“05.05”和“05.06”。分別存放2005年1－6月應收賬款回收情況表的數(shù)據(jù)，將應收賬款明細賬的“客戶名”、“年初余額”、“借方”、“貸方”等列內(nèi)容分別復制到“05.01”工作表的“客戶名稱”、“上年余額”、“本期應收賬款”、“本期回收款”等列。如下表</p><p>　　某單位2005年1月應收帳款回收情況表</p&g

12、t;<p><b>　　單位：元</b></p><p>　　行號 客戶名稱 上年欠款余額 本月發(fā)生應收賬款 本月應收賬款</p><p>　　1 東方公司 10000 　 3000</p><p>　　2 南方公司 20000 4000 　</p><p>　　3 西方公司 30000 　 5000<

13、;/p><p>　　4 北方公司 　 　 　</p><p>　　合 計 60000 4000 8000</p><p>　?。?）制作應收賬款賬齡分析表</p><p>　　A B C D E F G H I J K L M N O P Q R S T U V W X</p><p>　　1 某單位2005年6月應收賬款

14、賬齡分析表</p><p><b>　　2 單位:元</b></p><p><b>　　3 行</b></p><p><b>　　號 客戶</b></p><p><b>　　名稱 年初</b></p><p><b>

15、;　　欠款</b></p><p>　　余額 2005年應收賬款發(fā)生數(shù) 2005年收回應收賬款 累計</p><p><b>　　欠款</b></p><p><b>　　余額 賬齡及金額</b></p><p>　　4 小計 1月 2月 3月 4月 5月 6月 小計 1月 2月 3月

16、4月 5月 6月 0～</p><p><b>　　30天 31～</b></p><p><b>　　60天 61～</b></p><p><b>　　90天 91～</b></p><p><b>　　120天 121～</b></p>

17、<p><b>　　180天 180天</b></p><p><b>　　以上</b></p><p><b>　　5 1 東方</b></p><p>　　公司 10000 18000 0 3000 5000 3000 7000 0 20000 3000 3000 6000 0 0 8

18、000 8000 0 7000 1000 0 0 0</p><p><b>　　6 2 南方</b></p><p>　　公司 20000 14000 4000 5000 0 2000 3000 0 20000 0 0 8000 0 5000 7000 14000 0 3000 2000 0 9000 0</p><p><b>　

19、　7 3 西方</b></p><p>　　公司 30000 3000 0 0 0 3000 0 0 8000 5000 0 1000 0 2000 0 25000 0 0 3000 0 0 22000</p><p><b>　　8 4 北方</b></p><p>　　公司 0 14000 0 1000 4000 0 6000

20、3000 4000 0 0 0 2000 0 2000 10000 3000 6000 0 1000 0 0</p><p>　　9 合 計 60000 49000 4000 9000 9000 8000 16000 3000 52000 8000 3000 15000 2000 7000 17000 57000 3000 16000 6000 1000 9000 22000</p><p&g

21、t;<b>　　第一步：數(shù)據(jù)提取</b></p><p>　　應收賬款賬齡分析表中的“年初欠款余額”、“應收賬款發(fā)生數(shù)”、“收回應收賬款”有關數(shù)據(jù)應從“應收賬款回收2005”文件中提取，由于各月的客戶有增有減使每個客戶在工作表中的位置可能不一樣，因此在使用取數(shù)公式時，需用“查找與引用”函數(shù)INDEX，具體公式如下，以東方公司“年初欠款余額”C5單元格為例。</p><p&

22、gt;　　=IF(ISERROR(INDEX('應收賬款回收2005.xls)05.01'!$A$1:$Z$200,MATCH($B5,' [應收賬款回收2005.xls]05.01'!$B$1:$B$200,),3))=TRUE,"",INDEX(' [應收賬款回收2005.xls]05.01'!$A$1:$Z$200,MATCH($B5,' [應收賬款回收2

23、005.xls]05.01'!$B$1:$B$200,),3))</p><p>　　該公式看似復雜，其實用了一個嵌套，IF（ISERROR(INDEX函數(shù))＝TRUE，“”，INDEX函數(shù)），其含義是如果INDEX函數(shù)出現(xiàn)了錯誤的結(jié)果（如＃NA，＃VALUE），讓本單元格數(shù)據(jù)為空，否則運行INDEX函數(shù)。</p><p>　　INDEX(' [應收賬款回收2005.xls

24、]05.01'!$A$1:$Z$200,MATCH($B5,' [應收賬款回收2005.xls]05.01'!$B$1:$B$200,),3)的含義是，在“應收賬款回收2005”05.01工作表第3列（上年欠款余額）中，查找并引用對應于B5單元格內(nèi)容的值，即B5單元格內(nèi)容在05.01工作表中的第3列值。運用這個公式的好處是，無論“應收賬款賬齡分析表”中的“東方公司”在“應收賬款回收2005”05.01工作表B列的

25、第幾行，都可以取得其對應的第3列“上年欠款余額”的值。</p><p>　　將該公式復制下去，可得到本列其他各列的值；將該公式橫向復制，并將公式中的相應月份（如“05.02”）替換為單元格所對應的月份（如“05.01”），可自動得到其他各行的值。</p><p><b>　　第二步：數(shù)據(jù)分析</b></p><p>　　我們假設客戶歸還賬款時按

26、照應收賬款發(fā)生的先后順序回款，即歸還的賬款先沖銷時間較長的應收賬款</p><p>　　以東方公司為例。其賬齡及金額段公式如下</p><p>　　賬齡 東方公司公式 所在單元格</p><p>　　0～30天 =IF(R5&lt;=0,0,IF(R5&gt;=SUM(J5:J5),J5,R5)) S5</p><p>　　3

27、1～60天 =IF(R5&lt;=0,0,IF(R5&gt;=SUM(I5:J5),I5,IF(R5-SUM(J5:J5)&lt;0,0,R5-SUM(J5:J5)))) T5</p><p>　　61～90天 =IF(R5&lt;=0,0,IF(R5&gt;=SUM(H5:J5),H5,IF(R5-SUM(I5:J5)&lt;0,0,R5-SUM(I5:J5)))

28、) U5</p><p>　　91～120天 =IF(R5&lt;=0,0,IF(R5&gt;=SUM(G5:J5),G5,IF(R5-SUM(H5:J5)&lt;0,0,R5-SUM(H5:J5)))) V5</p><p>　　121～180天 =IF(R5&lt;=0,0,IF(R5&gt;=SUM(E5:J5),E5+F5,IF(R5-SUM

29、(G5:J5)&lt;0,0,R5-SUM(G5:J5)))) W5</p><p>　　180天以上 =IF(R5&lt;=0,0,IF(R5&gt;=D5,R5-D5,0)) X5</p><p>　　因為我們假設歸還的賬款先沖銷時間較長的應收賬款，所以“累計欠款余額”由最近幾個月的“應收賬款發(fā)生數(shù)”組成。在設計公式時，要先將“累計欠款余額”與相應期間的“應收賬

30、款發(fā)生數(shù)”相比較，以賬齡為31～60天T5單元格為例，如果5月－6月“應收賬款發(fā)生數(shù)”合計小于“累計欠款余額”則賬齡為31～60天的金額為5月份應收賬款發(fā)生數(shù)，如果5月－6月“應收賬款發(fā)生數(shù)”合計大于“累計欠款余額”則賬齡為31～60天的金額為“累計欠款余額”的與6月份“應收賬款發(fā)生數(shù)”的差額。</p><p>　　將以上公式復制第5行以下各行。至此，6月份的賬齡分析表文件“應收賬款賬齡分析表”即告完成。<