關(guān)于如何運(yùn)用EXCEL分析應(yīng)收賬款回收情況的論文
［關(guān)鍵詞］運(yùn)用EXCEL 分析 應(yīng)收賬款回收
［摘 要］應(yīng)收賬款回收情況的監(jiān)督主要通過(guò)編制賬齡分析表進(jìn)行，對(duì)于客戶(hù)眾多、業(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)具有非常重要的意義。
應(yīng)收賬款回收情況的監(jiān)督主要通過(guò)編制賬齡分析表進(jìn)行，一般來(lái)講，應(yīng)收賬款拖欠時(shí)間越長(zhǎng)，款項(xiàng)收回的可能性越小，形成壞賬的可能性就越大。通過(guò)賬齡分析可以揭示每個(gè)客戶(hù)的風(fēng)險(xiǎn)性和每筆應(yīng)收賬款產(chǎn)生壞賬的可能性，并以此作為提取壞賬準(zhǔn)備的依據(jù)，因此對(duì)于客戶(hù)眾多、業(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)具有非常重要的意義。
一、賬齡的確定
賬齡的時(shí)間段一般根據(jù)客戶(hù)的信用期，結(jié)合會(huì)計(jì)準(zhǔn)則對(duì)計(jì)提壞賬準(zhǔn)備標(biāo)準(zhǔn)劃分。劃分賬齡的時(shí)間段越細(xì)、間隔越短，壞賬估計(jì)就越精確。通常情況下，對(duì)1年以?xún)?nèi)應(yīng)收賬款賬齡劃分較細(xì)，而對(duì)1年以上的應(yīng)收賬款則不能做太多區(qū)分。
二、回收情況表的制作
為數(shù)據(jù)提取階段，主要提取應(yīng)收賬款明細(xì)賬的“科目名稱(chēng)”、“年初余額”、“借方發(fā)生數(shù)”、“貸方發(fā)生數(shù)”，將以上數(shù)據(jù)分別復(fù)制到回收情況表的“客戶(hù)名稱(chēng)”、“年初欠款余額”、“本月發(fā)生應(yīng)收賬款”、“本月收回賬款”等，然后制作成表格。要求每月單獨(dú)制作一個(gè)工作表
三、賬齡分析表的制作
為數(shù)據(jù)分析階段，先從回收情況表提取有關(guān)數(shù)據(jù)，由于每月都有可能增加或減少應(yīng)收賬款客戶(hù)，導(dǎo)致每月客戶(hù)的順序都不一樣，所以要用較為復(fù)雜的EXCEL函數(shù)嵌套搜索同樣的客戶(hù)名并返回相應(yīng)的數(shù)值，對(duì)于賬齡和金額部分我們假設(shè)客戶(hù)歸還賬款時(shí)按照應(yīng)收賬款發(fā)生的先后順序收回賬款，即歸還的賬款先沖銷(xiāo)時(shí)間較長(zhǎng)的應(yīng)收賬款。
下面通過(guò)實(shí)例來(lái)說(shuō)明運(yùn)用EXCEL分析應(yīng)收賬款收回情況的方法和步驟。
［例］某企業(yè)2005年1－6月的應(yīng)收賬款明細(xì)賬如下，請(qǐng)根據(jù)明細(xì)賬對(duì)應(yīng)收賬款回收情況進(jìn)行分析。
某單位2005年1-6月應(yīng)收賬款明細(xì)賬
單位:元
科目名稱(chēng) 年初余額 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
合 計(jì) 60000 4000 8000 9000 3000 9000 15000 8000 2000 16000 7000 3000 17000
（1）確定賬齡時(shí)間段劃分標(biāo)準(zhǔn)
可以將賬齡時(shí)間段劃分為0～30天、31～60天、61～90天、91～120天、121～180天、180天以上。
（2）制作應(yīng)收賬款回收情況表
第一步：數(shù)據(jù)提取
將應(yīng)收賬款明細(xì)賬轉(zhuǎn)化為EXCEL文件，便于復(fù)制，如果財(cái)務(wù)軟件不具備該功能，可以用數(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ù)。
第二步：編制應(yīng)收賬款回收情況表
根據(jù)2005年1－6月明細(xì)賬編制一個(gè)回收情況表，該表為計(jì)算賬齡的基礎(chǔ)數(shù)據(jù)表格。首先，制作一個(gè)“應(yīng)收賬款回收2005 .xls”，在該文件中每月設(shè)一個(gè)工作表，工作表格分別為“05.01”、“05.02”、“05.03”、“05.04”、“05.05”和“05.06”。分別存放2005年1－6月應(yīng)收賬款回收情況表的數(shù)據(jù)，將應(yīng)收賬款明細(xì)賬的“客戶(hù)名”、“年初余額”、“借方”、“貸方”等列內(nèi)容分別復(fù)制到“05.01”工作表的“客戶(hù)名稱(chēng)”、“上年余額”、“本期應(yīng)收賬款”、“本期回收款”等列。如下表
某單位2005年1月應(yīng)收帳款回收情況表
單位：元
行號(hào) 客戶(hù)名稱(chēng) 上年欠款余額 本月發(fā)生應(yīng)收賬款 本月應(yīng)收賬款
1 東方公司 10000 3000
2 南方公司 20000 4000
3 西方公司 30000 5000
4 北方公司
合 計(jì) 60000 4000 8000
（3）制作應(yīng)收賬款賬齡分析表
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月應(yīng)收賬款賬齡分析表
2 單位:元
3 行
號(hào) 客戶(hù)
名稱(chēng) 年初
欠款
余額 2005年應(yīng)收賬款發(fā)生數(shù) 2005年收回應(yīng)收賬款 累計(jì)
欠款
余額 賬齡及金額
4 小計(jì) 1月 2月 3月 4月 5月 6月 小計(jì) 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 合 計(jì) 60000 49000 4000 9000 9000 8000 16000 3000 52000 8000 3000 15000 2000 7000 17000 57000 3000 16000 6000 1000 9000 22000
第一步：數(shù)據(jù)提取
應(yīng)收賬款賬齡分析表中的“年初欠款余額”、“應(yīng)收賬款發(fā)生數(shù)”、“收回應(yīng)收賬款”有關(guān)數(shù)據(jù)應(yīng)從“應(yīng)收賬款回收2005”文件中提取，由于各月的客戶(hù)有增有減使每個(gè)客戶(hù)在工作表中的位置可能不一樣，因此在使用取數(shù)公式時(shí)，需用“查找與引用”函數(shù)INDEX，具體公式如下，以東方公司“年初欠款余額”C5單元格為例。
=IF(ISERROR(INDEX(#39;應(yīng)收賬款回收2005.xls)05.01#39;!$A$1:$Z$200,MATCH($B5,#39; [應(yīng)收賬款回收2005.xls]05.01#39;!$B$1:$B$200,),3))=TRUE,"",INDEX(#39; [應(yīng)收賬款回收2005.xls]05.01#39;!$A$1:$Z$200,MATCH($B5,#39; [應(yīng)收賬款回收2005.xls]05.01#39;!$B$1:$B$200,),3))
該公式看似復(fù)雜，其實(shí)用了一個(gè)嵌套，IF（ISERROR(INDEX函數(shù))＝TRUE，“”，INDEX函數(shù)），其含義是如果INDEX函數(shù)出現(xiàn)了錯(cuò)誤的結(jié)果（如＃NA，＃VALUE），讓本單元格數(shù)據(jù)為空，否則運(yùn)行INDEX函數(shù)。
INDEX(#39; [應(yīng)收賬款回收2005.xls]05.01#39;!$A$1:$Z$200,MATCH($B5,#39; [應(yīng)收賬款回收2005.xls]05.01#39;!$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列的第幾行，都可以取得其對(duì)應(yīng)的第3列“上年欠款余額”的'值。
將該公式復(fù)制下去，可得到本列其他各列的值；將該公式橫向復(fù)制，并將公式中的相應(yīng)月份（如“05.02”）替換為單元格所對(duì)應(yīng)的月份（如“05.01”），可自動(dòng)得到其他各行的值。
第二步：數(shù)據(jù)分析
我們假設(shè)客戶(hù)歸還賬款時(shí)按照應(yīng)收賬款發(fā)生的先后順序回款，即歸還的賬款先沖銷(xiāo)時(shí)間較長(zhǎng)的應(yīng)收賬款
以東方公司為例。其賬齡及金額段公式如下
賬齡 東方公司公式 所在單元格
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
因?yàn)槲覀兗僭O(shè)歸還的賬款先沖銷(xiāo)時(shí)間較長(zhǎng)的應(yīng)收賬款，所以“累計(jì)欠款余額”由最近幾個(gè)月的“應(yīng)收賬款發(fā)生數(shù)”組成。在設(shè)計(jì)公式時(shí)，要先將“累計(jì)欠款余額”與相應(yīng)期間的“應(yīng)收賬款發(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ù)”的差額。
將以上公式復(fù)制第5行以下各行。至此，6月份的賬齡分析表文件“應(yīng)收賬款賬齡分析表”即告完成。
綜上所述，根據(jù)單位應(yīng)收賬款明細(xì)賬有關(guān)基礎(chǔ)數(shù)據(jù)，利用EXCEL函數(shù)公式定義需要計(jì)算的各個(gè)項(xiàng)目，則可自動(dòng)地編制出應(yīng)收賬款賬齡分析表，更加方便、快捷、最大限度地提高工作效率，對(duì)于應(yīng)收賬款發(fā)生比較多的單位其效果尤為明顯。
【如何運(yùn)用EXCEL分析應(yīng)收賬款回收情況的論文】相關(guān)文章：
應(yīng)收賬款論文03-24
應(yīng)收賬款管理的論文04-01
應(yīng)收賬款審計(jì)論文03-03
應(yīng)收賬款論文的摘要06-12
應(yīng)收賬款管理研究論文04-01