電子表格應(yīng)收賬款管理

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

［關(guān)鍵詞］運(yùn)用EXCEL分析應(yīng)收賬款回收

［摘要］應(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)具有非常重要的意義。

應(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)具有非常重要的意義。

一、賬齡的確定

賬齡的時(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ū)分。

二、回收情況表的制作

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

三、賬齡分析表的制作

為數(shù)據(jù)分析階段，先從回收情況表提取有關(guān)數(shù)據(jù)，由于每月都有可能增加或減少應(yīng)收賬款客戶，導(dǎo)致每月客戶的順序都不一樣，所以要用較為復(fù)雜的EXCEL函數(shù)嵌套搜索同樣的客戶名并返回相應(yīng)的數(shù)值，對(duì)于賬齡和金額部分我們假設(shè)客戶歸還賬款時(shí)按照應(yīng)收賬款發(fā)生的先后順序收回賬款，即歸還的賬款先沖銷時(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ì)賬

單位:元

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

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

東方公司1000030003000300050006000300070008000

南方公司200004000500080002000300050007000

西方公司300005000100030002000

北方公司100040002000600030002000

合計(jì)60000400080009000300090001500080002000160007000300017000

（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ì)賬的“客戶名”、“年初余額”、“借方”、“貸方”等列內(nèi)容分別復(fù)制到“05.01”工作表的“客戶名稱”、“上年余額”、“本期應(yīng)收賬款”、“本期回收款”等列。如下表

某單位2005年1月應(yīng)收帳款回收情況表

單位：元

行號(hào)客戶名稱上年欠款余額本月發(fā)生應(yīng)收賬款本月應(yīng)收賬款

1東方公司100003000

2南方公司200004000

3西方公司300005000

4北方公司

合計(jì)6000040008000

（3）制作應(yīng)收賬款賬齡分析表

ABCDEFGHIJKLMNOPQRSTUVWX

1某單位2005年6月應(yīng)收賬款賬齡分析表

2單位:元

3行

號(hào)客戶

名稱年初

欠款

余額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天

以上

51東方

公司1000018000030005000300070000200003000300060000080008000070001000000

62南方

公司20000140004000500002000300002000000800005000700014000030002000090000

73西方

公司3000030000003000008000500001000020000250000030000022000

84北方

公司014000010004000060003000400000020000200010000300060000100000

9合計(jì)6000049000400090009000800016000300052000800030001500020007000170005700030001600060001000900022000

第一步：數(shù)據(jù)提取

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

=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)收賬款回收2005.xls]05.01''''!$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(''''[應(yīng)收賬款回收2005.xls]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列的第幾行，都可以取得其對(duì)應(yīng)的第3列“上年欠款余額”的值。

將該公式復(fù)制下去，可得到本列其他各列的值；將該公式橫向復(fù)制，并將公式中的相應(yīng)月份（如“05.02”）替換為單元格所對(duì)應(yīng)的月份（如“05.01”），可自動(dòng)得到其他各行的值。

第二步：數(shù)據(jù)分析

我們假設(shè)客戶歸還賬款時(shí)按照應(yīng)收賬款發(fā)生的先后順序回款，即歸還的賬款先沖銷時(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è)歸還的賬款先沖銷時(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ā)生比較多的單位其效果尤為明顯。