如何運(yùn)用excel分析應(yīng)收賬款回收情況

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

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

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

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

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

6、，請(qǐng)根據(jù)明細(xì)賬對(duì)應(yīng)收賬款回收情況進(jìn)行分析。</p><p>　　某單位2005年1-6月應(yīng)收賬款明細(xì)賬</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>　　合 計(jì) 60000 4000 8000 9000 3000 9000 15000 8000 2000 16000 7000 3000 17000</p><p>　?。?）確定賬齡時(shí)間段劃分標(biāo)準(zhǔn)</p

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

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

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

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

13、;/p><p>　　4 北方公司 　 　 　</p><p>　　合 計(jì) 60000 4000 8000</p><p>　　（3）制作應(yīng)收賬款賬齡分析表</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月應(yīng)收賬款

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

15、;　　欠款</b></p><p>　　余額 2005年應(yīng)收賬款發(fā)生數(shù) 2005年收回應(yīng)收賬款 累計(jì)</p><p><b>　　欠款</b></p><p><b>　　余額 賬齡及金額</b></p><p>　　4 小計(jì) 1月 2月 3月 4月 5月 6月 小計(jì) 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 合 計(jì) 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>　　應(yīng)收賬款賬齡分析表中的“年初欠款余額”、“應(yīng)收賬款發(fā)生數(shù)”、“收回應(yīng)收賬款”有關(guān)數(shù)據(jù)應(yīng)從“應(yīng)收賬款回收2005”文件中提取，由于各月的客戶有增有減使每個(gè)客戶在工作表中的位置可能不一樣，因此在使用取數(shù)公式時(shí)，需用“查找與引用”函數(shù)INDEX，具體公式如下，以東方公司“年初欠款余額”C5單元格為例。</p><p&

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

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

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

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

26、照應(yīng)收賬款發(fā)生的先后順序回款，即歸還的賬款先沖銷時(shí)間較長(zhǎng)的應(yīng)收賬款</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>　　因?yàn)槲覀兗僭O(shè)歸還的賬款先沖銷時(shí)間較長(zhǎng)的應(yīng)收賬款，所以“累計(jì)欠款余額”由最近幾個(gè)月的“應(yīng)收賬款發(fā)生數(shù)”組成。在設(shè)計(jì)公式時(shí)，要先將“累計(jì)欠款余額”與相應(yīng)期間的“應(yīng)收賬

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