前言：本站為你精心整理了電子表格應收賬款管理范文，希望能為你的創作提供參考價值，我們的客服老師可以幫助你提供個性化的參考范文，歡迎咨詢。

［關鍵詞］運用EXCEL分析應收賬款回收

［摘要］應收賬款回收情況的監督主要通過編制賬齡分析表進行，對于客戶眾多、業務頻繁的企業來說，運用EXCEL快速、準確編制賬齡分析表，并據此制定有效的收賬政策，對于加強應收賬款管理，提高資金回籠速度，降低經營風險具有非常重要的意義。

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

一、賬齡的確定

賬齡的時間段一般根據客戶的信用期，結合會計準則對計提壞賬準備標準劃分。劃分賬齡的時間段越細、間隔越短，壞賬估計就越精確。通常情況下，對1年以內應收賬款賬齡劃分較細，而對1年以上的應收賬款則不能做太多區分。

二、回收情況表的制作

為數據提取階段，主要提取應收賬款明細賬的“科目名稱”、“年初余額”、“借方發生數”、“貸方發生數”，將以上數據分別復制到回收情況表的“客戶名稱”、“年初欠款余額”、“本月發生應收賬款”、“本月收回賬款”等，然后制作成表格。要求每月單獨制作一個工作表

三、賬齡分析表的制作

為數據分析階段，先從回收情況表提取有關數據，由于每月都有可能增加或減少應收賬款客戶，導致每月客戶的順序都不一樣，所以要用較為復雜的EXCEL函數嵌套搜索同樣的客戶名并返回相應的數值，對于賬齡和金額部分我們假設客戶歸還賬款時按照應收賬款發生的先后順序收回賬款，即歸還的賬款先沖銷時間較長的應收賬款。

下面通過實例來說明運用EXCEL分析應收賬款收回情況的方法和步驟。

［例］某企業2005年1－6月的應收賬款明細賬如下，請根據明細賬對應收賬款回收情況進行分析。

某單位2005年1-6月應收賬款明細賬

單位:元

科目名稱年初余額1月份2月份3月份4月份5月份6月份

借方貸方借方貸方借方貸方借方貸方借方貸方借方貸方

東方公司1000030003000300050006000300070008000

南方公司200004000500080002000300050007000

西方公司300005000100030002000

北方公司100040002000600030002000

合計60000400080009000300090001500080002000160007000300017000

（1）確定賬齡時間段劃分標準

可以將賬齡時間段劃分為0～30天、31～60天、61～90天、91～120天、121～180天、180天以上。

（2）制作應收賬款回收情況表

第一步：數據提取

將應收賬款明細賬轉化為EXCEL文件，便于復制，如果財務軟件不具備該功能，可以用數據庫運用程序打開存放應收賬款明細賬數據庫文件，如擴展名為DBF文件，可用FOXPRO程序打開，然后導出EXCEL格式文件。以利于應收賬款回收情況表提取相關數據。

第二步：編制應收賬款回收情況表

根據2005年1－6月明細賬編制一個回收情況表，該表為計算賬齡的基礎數據表格。首先，制作一個“應收賬款回收2005.xls”，在該文件中每月設一個工作表，工作表格分別為“05.01”、“05.02”、“05.03”、“05.04”、“05.05”和“05.06”。分別存放2005年1－6月應收賬款回收情況表的數據，將應收賬款明細賬的“客戶名”、“年初余額”、“借方”、“貸方”等列內容分別復制到“05.01”工作表的“客戶名稱”、“上年余額”、“本期應收賬款”、“本期回收款”等列。如下表

某單位2005年1月應收帳款回收情況表

單位：元

行號客戶名稱上年欠款余額本月發生應收賬款本月應收賬款

1東方公司100003000

2南方公司200004000

3西方公司300005000

4北方公司

合計6000040008000

（3）制作應收賬款賬齡分析表

ABCDEFGHIJKLMNOPQRSTUVWX

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天

以上

51東方

公司1000018000030005000300070000200003000300060000080008000070001000000

62南方

公司20000140004000500002000300002000000800005000700014000030002000090000

73西方

公司3000030000003000008000500001000020000250000030000022000

84北方

公司014000010004000060003000400000020000200010000300060000100000

9合計6000049000400090009000800016000300052000800030001500020007000170005700030001600060001000900022000

第一步：數據提取

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

=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,''''[應收賬款回收2005.xls]05.01''''!$B$1:$B$200,),3))

該公式看似復雜，其實用了一個嵌套，IF（ISERROR(INDEX函數)＝TRUE，“”，INDEX函數），其含義是如果INDEX函數出現了錯誤的結果（如＃NA，＃VALUE），讓本單元格數據為空，否則運行INDEX函數。

INDEX(''''[應收賬款回收2005.xls]05.01''''!$A$1:$Z$200,MATCH($B5,''''[應收賬款回收2005.xls]05.01''''!$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函數公式定義需要計算的各個項目，則可自動地編制出應收賬款賬齡分析表，更加方便、快捷、最大限度地提高工作效率，對于應收賬款發生比較多的單位其效果尤為明顯。