利用EXCEL建立企業(yè)應(yīng)收賬款賬齡分析管理模型

高雪平

(浙江經(jīng)貿(mào)職業(yè)技術(shù)學(xué)院，浙江 杭州 310007)

應(yīng)收賬款管理是企業(yè)保持資金的良性循環(huán)、降低資金占用成本和保證資金安全回收的必要手段，其中賬齡分析是應(yīng)收賬款管理最基本的環(huán)節(jié)。通過賬齡分析可以揭示每個客戶的風(fēng)險性和每筆應(yīng)收賬款產(chǎn)生壞賬的可能性，并以此作為提取壞賬準備的依據(jù)。因此，準確、快速地編制應(yīng)收賬款賬齡分析表，對企業(yè)加強應(yīng)收賬款管理、提高資金回籠速度、降低經(jīng)營風(fēng)險具有非常重要的意義。EXCEL工具可以簡便地進行各種數(shù)據(jù)的處理、統(tǒng)計分析，目前已經(jīng)廣泛地應(yīng)用于統(tǒng)計、經(jīng)濟管理等各領(lǐng)域。本文以浙江T公司為例，介紹EXCEL在應(yīng)收賬款賬齡管理中的運用。

T公司是一家以施工、設(shè)計、服務(wù)為主要業(yè)務(wù)的大型國有集團企業(yè)，有眾多的子分公司。其客戶絕大多數(shù)為信用度較好的國有企業(yè)，信用風(fēng)險較低，且長期保持合作關(guān)系，不適宜在信用期內(nèi)進行頻繁催收活動。加上國有企業(yè)工作流程較為復(fù)雜，T公司難免會出現(xiàn)應(yīng)收賬款超期的現(xiàn)象，因此公司對于應(yīng)收賬款管理的重心放在對超期應(yīng)收賬款的管理上。即需要對各筆應(yīng)收賬款是否超期、超期幾天，金額多大，隸屬于什么公司等信息均能夠動態(tài)地把控。另一方面，由于T公司是大型的集團公司，需要每個月對應(yīng)收賬款的分析以圖表的形式向上級及下屬子分公司匯報公示。

一、應(yīng)收賬款賬齡管理目標需求

T公司由于各個客戶公司信用期不同，其每筆應(yīng)收賬款的可催收開始的時間、力度應(yīng)有所不同。公司對于應(yīng)收賬款的管理聚焦于對可催收應(yīng)收賬款的管控，即對于超期的應(yīng)收賬款重點管理。因而應(yīng)收賬款信息表中需要特別顯示出每筆應(yīng)收賬款的超期天數(shù)。

另一方面，由于T公司的業(yè)務(wù)人員是按照客戶維度配置接口業(yè)務(wù)員的，因此也需要對每一個客戶公司進行可催可收的應(yīng)收賬款統(tǒng)計。

在滿足以上需求的同時，管理人員還需要有一個直觀的、公司整體的應(yīng)收賬款賬齡情況。

結(jié)合以上T公司實際運營情況以及管理需求，本文將采用EXCEL工具構(gòu)建一個應(yīng)收賬款賬齡分析管理模型。

二、基于EXCEL的應(yīng)收賬款賬齡管理模型建設(shè)思路

1.賬期確定

T公司的客戶大多數(shù)為長期合作關(guān)系，因此應(yīng)收賬款賬期主要按照客戶的通常付款期限決定，一般來說，T公司對于客戶設(shè)置的信用期限一般是1－3個月，即30天到90天。

2.計算已到期應(yīng)收賬款金額以及超期天數(shù)

首先對應(yīng)收賬款明細表中的到期日和統(tǒng)計日期做對比分析，計算出每一筆應(yīng)收賬款的賬齡，統(tǒng)計出總應(yīng)收賬款金額和到期應(yīng)收賬款金額，計算出每一客戶公司應(yīng)收賬款的總額及在總應(yīng)收賬款中的百分比。通過這些統(tǒng)計分析，可以清楚地在表格中看出整個公司的應(yīng)收賬款統(tǒng)計信息。

3.分客戶、分超期天數(shù)繪制動態(tài)圖表

從客戶維度分析各個客戶的不同超期時間段的應(yīng)收賬款情況，從超期天數(shù)維度分析各個客戶的應(yīng)收賬款情況。

4.基礎(chǔ)數(shù)據(jù)

考慮到模型的普遍適用性以及數(shù)據(jù)簡化易懂性，本文不采用從T公司項目管理系統(tǒng)(PM系統(tǒng))中直接導(dǎo)出的數(shù)據(jù)作為原始數(shù)據(jù)，而是采用重新采集的客戶應(yīng)收賬款信息作為示例說明。

三、基于EXCEL的應(yīng)收賬款賬齡管理模型建設(shè)步驟

1.建立客戶信息表，定義名稱

圖1 客戶及信用額度

建立一張sheet，命名為“客戶及信用額度”把客戶的名稱信息、編號信息輸入，同時給各個客戶定義簡稱，分別為大寫的公司首字母。為了以后錄入信息方便，我們需要把各個公司進行簡稱定義，操作如下:選中B2:C7單元格，點擊工具欄中的“公式”——“名稱的定義”——“根據(jù)所選內(nèi)容創(chuàng)建”，在跳出的對話框中，勾選“最左列”即可。

選中B2:B9區(qū)域，點擊工具欄中的“公式”——“名稱的定義”——“定義名稱”，在跳出的對話框中，在名稱對應(yīng)處填上“客戶”，如下圖所示:

圖2 定義客戶名稱

2.創(chuàng)建應(yīng)收賬款信息表

新建一張Sheet，命名為“應(yīng)收賬款基本信息”。

圖3 應(yīng)收賬款基本信息

(1)在 D2單元格輸入公式:“=”當前日期:“＆TEXT(TODAY()，“yyyy年m月d日”)＆”“＆TEXT(TODAY()，“［$－804］aaaa;@”)”，這樣可以顯示出表格打開當天的日期。

(2)填入該表各項要素

該表中的信息要素包括:開票日期、發(fā)票號、發(fā)票金額等。其中藍色區(qū)域為自動計算部分，白色區(qū)域為財務(wù)人員手工錄入?yún)^(qū)域(T公司由于有PM系統(tǒng)，所以白色區(qū)域可以直接從系統(tǒng)中導(dǎo)入)。其中單位名稱列由于已經(jīng)進行了名稱定義，不需要輸入公司中文名稱，只需要輸入簡稱即可。例如在D6單元格只要輸入“=LQ”，再按回車鍵，表格會自動跳出“臨泉公司”四字。

(3)計算欠款

在欠款額這列(G列)相應(yīng)單元格輸入公式:“=IF(A4＜＞，E4－F4，)”，表示欠款為應(yīng)收金額減去回款額。

(4)計算到期日

到期日的計算為開票日加信用期，因此在I4單元格輸入公式:“=IF(A4＜ ＞，A4+H4，)”，按回車鍵后向下復(fù)制公式至I16單元格，得到了每筆應(yīng)收賬款的到期日。

(5)判斷是否超期

J列為判斷是否超期，在J4單元格輸入公式:“=IF(A4=，IF(TODAY()＜I4，“否”，“超期”))”，按回車鍵后向下復(fù)制公式至J16單元格，就可以得到每筆應(yīng)收賬款是否超期。

(6)計算超期天數(shù)

在K4單元格輸入“=IF(A4＜＞“”，TODAY()－A4，“”)”，按回車;然后在 L4單元格輸入“=IF(J4=“否”，“”，K4－H4)”，按回車。再講K4、L4單元格公式向下復(fù)制至第5～16行。

(7)計算總計應(yīng)收賬款金額及超期應(yīng)收賬款金額

在B18單元格輸入“=SUM(G4:G16)”，即可求出總計應(yīng)收賬款金額。在B19單元格輸入“=SUMIF(J4:J16，“超期”，G4:G16)”，按回車即可求出超期應(yīng)收賬款金額。

(8)對超期的應(yīng)收賬款突出顯示

選擇 A4:L16區(qū)域，點擊“開始——格式——條件格式——新建規(guī)則”，選擇“使用公式確定要設(shè)置格式的單元格”，在編輯欄中輸入“=VALUE($L4)＞0”，點格式后設(shè)置單元格格式，選擇“填充”，然后就可以設(shè)置希望突出顯示的顏色了，同理，想要修改突出顯示的字體也可以在這里修改。

圖4 超期應(yīng)收賬款突出顯示

3.創(chuàng)建應(yīng)收賬款賬齡分析表

新建一張sheet，命名為“賬齡分析表”。賬齡分析表展示的主要是分客戶的各自不同賬齡欠款情況。例如下圖中和美公司的應(yīng)收賬款賬齡在16－30天的有60000元，賬齡超過90天的有20000元。總體看，T公司的應(yīng)收賬款賬齡主要集中在31－45天，占到應(yīng)收賬款總額的35.15%。

對于客戶賬齡段的劃分，以15天為一個時間段，共分為7段，如下圖:

圖5 賬齡分析表

其中A列、B列為財務(wù)人員手工輸入的客戶編號及客戶名稱。

在C3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$K$4:$K$16，“＜ =15”，應(yīng)收賬款基本信息!$D$4:$D$16，B3)”，然后復(fù)制該公式至C3:I8整個區(qū)域，這樣就將6個公司的各個時段賬齡都匯總出來。J列用SUM函數(shù)進行行數(shù)據(jù)合計，在第9行用SUM函數(shù)進行列數(shù)據(jù)合計。在K3單元格輸入:“=J3/$J$9”，再復(fù)制公式至K9，在C10單元格輸入公式:“=C9/$J$9”，再復(fù)制公式至J10單元格。這樣就得到各公司、各賬齡應(yīng)收賬款占總數(shù)的百分比。

為防止數(shù)據(jù)出錯，在B12:C12設(shè)置數(shù)據(jù)校驗，C12單元格輸入:“=IF(J9=應(yīng)收賬款基本信息!B18，“正確”，“錯誤”)”。

4.創(chuàng)建應(yīng)收賬款超期催收表

由于應(yīng)收賬款賬齡分析表展示的是各公司的應(yīng)收賬款賬齡情況，但是由于各個公司的信用條件不一致，不能直觀地顯示各個公司是否存在應(yīng)收賬款到期、超期情況。因此在實際工作中需要財務(wù)人員對每一筆應(yīng)收賬款結(jié)合各個公司的信用條件一一對比，雖然簡單，但是在一定程度上加大了為員工工作量。為了解決這個簡單但是繁瑣的工作，T公司設(shè)計了應(yīng)收賬款超期催收表。

新建一張sheet，命名為“超期催收表”。超期催收表展示的主要是分客戶的應(yīng)收賬款到期可催收的情況，要素以及表樣結(jié)構(gòu)與“應(yīng)收賬款賬齡分析表”類似。例如下圖中和美公司的超期的應(yīng)收賬款有20000元，超期時間超過了90天。臨泉公司超期的應(yīng)收賬款達到75000元，超期時間為16－30天。總體看T公司超期的應(yīng)收賬款金額達到12萬元，其中超期16－30天的有7.5萬，超期31－45天的有2.5萬，超期超過90天的有2萬元。

圖6 超期催收表

(1)定義名稱“賬齡”

選擇C2:I2區(qū)域，點擊工具欄中的“公式”——“名稱的定義”——“定義名稱”，在跳出的對話框中，在名稱對應(yīng)處填上“賬齡”，如下圖所示:

圖7 定義賬齡

(2)計算各公司分分時段超期金額

在C3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$L$4:$L$16，“＜ =15”，應(yīng)收賬款基本信息!$D$4:$D$16，B3)”。

在D3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$D$4:$D$16，$B3，應(yīng)收賬款基本信息!$L$4:$L$16，“＞ =16”，應(yīng)收賬款基本信息!$L$4:$L$16，“＜ =30”)”。

在E3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$D$4:$D$16，$B3，應(yīng)收賬款基本信息!$L$4:$L$16，“＞ =31”，應(yīng)收賬款基本信息!$L$4:$L$16，“＜ =45”)”。

在F3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$D$4:$D$16，$B3，應(yīng)收賬款基本信息!$L$4:$L$16，“＞ =46”，應(yīng)收賬款基本信息!$L$4:$L$16，“＜ =60”)”。

在G3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$D$4:$D$16，$B3，應(yīng)收賬款基本信息!$L$4:$L$16，“＞ =61”，應(yīng)收賬款基本信息!$L$4:$L$16，“＜ =75”)”。

在H3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$D$4:$D$16，$B3，應(yīng)收賬款基本信息!$L$4:$L$16，“＞ =76”，應(yīng)收賬款基本信息!$L$4:$L$16，“＜ =90”)”。

在I3單元格輸入:“=SUMIFS(應(yīng)收賬款基本信息!$G$4:$G$16，應(yīng)收賬款基本信息!$D$4:$D$16，$B3，應(yīng)收賬款基本信息!$L$4:$L$16，“＞ =91”)”

選中C3:I3區(qū)域，一起復(fù)制公式至3～8行。

(3)匯總

對超期催收表按行、按列進行匯總并且求出占比。

(4)突出顯示

對存在超期應(yīng)收賬款的公司設(shè)置突出顯示。選中B3:B8區(qū)域，選擇:開始——條件格式——新建規(guī)則——使用公式確定要設(shè)置格式的單元格，設(shè)置如下圖:

圖8 突出顯示超期公司

對超期金額進行突出顯示，選擇C3:I8區(qū)域，選擇:開始——條件格式——突出顯示單元格規(guī)則——大于，對區(qū)域中大于0的數(shù)設(shè)置突出顯示，設(shè)置如下圖:

圖9 突出顯示超期金額

(5)校驗結(jié)果

為防止數(shù)據(jù)出錯，在B13:C13設(shè)置數(shù)據(jù)校驗，C13單元格輸入:“=IF(J9=應(yīng)收賬款基本信息!B19，“正確”，“錯誤”)”。

5.創(chuàng)建各公司應(yīng)收賬款客戶－賬齡分析動態(tài)圖

新建一張sheet，命名為“賬齡分析圖”。建立動態(tài)圖形如下圖所示:

圖10 賬齡分析圖

(1)設(shè)置活動控件按鈕

打開“開發(fā)工具——插入——Active X 控件——組合框”。在B3單元格相應(yīng)位置畫一個組合框，點右鍵——屬性，跳出的屬性框中如下圖:

圖11 繪制活動控件

在LinkedCell格內(nèi)填上 A6，在 ListFillRange格內(nèi)填上“客戶”1。回車后活動控件設(shè)置完成。

(2)設(shè)置動態(tài)數(shù)據(jù)

按照上圖6所示，在B5:H5單元格畫好表格。在B6單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，2，F(xiàn)ALSE)”，在 C6 單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，3，F(xiàn)ALSE)”，在 D6 單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，4，F(xiàn)ALSE)”，在 E6 單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，5，F(xiàn)ALSE)”，在 F6 單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，6，F(xiàn)ALSE)”，在 G6 單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，7，F(xiàn)ALSE)”，在 H6 單元格輸入公式:“=VLOOKUP($A6，賬齡分析表!$B$2:$I$9，8，F(xiàn)ALSE)”，在 I6 單元格輸入:“=SUM(B6:H6)”，表格完成。

(3)繪制動態(tài)圖表

選擇A5:H6單元格，選擇“插入——圖表——條形圖”即生成相應(yīng)公司的應(yīng)收賬款賬齡分析動態(tài)圖。

6.創(chuàng)建公司整體應(yīng)收賬款賬齡－客戶分析動態(tài)圖

新建一張sheet，命名為“總體應(yīng)收賬款賬齡分析圖”。建立動態(tài)圖形如下圖所示:

圖12 總體應(yīng)收賬款賬齡分析圖

(1)設(shè)置賬齡選擇區(qū)

鼠標選擇B3單元格，選擇“數(shù)據(jù)——數(shù)據(jù)有效性——數(shù)據(jù)有效性”，在跳出的對話框中，“允許”單元格選擇序列，“來源”單元格輸入“=賬齡”2。操作如下圖，賬齡區(qū)間選擇菜單完成。

圖13 設(shè)置賬齡選擇

然后在B3下拉菜單中選擇“16－30天”。

(2)設(shè)置賬齡分析表

A5:H6區(qū)域設(shè)置如圖13所示的分析表。

首先在A6單元格輸入公式:“=B3”。其次在B6單元格輸入公式:“=HLOOKUP($A6，賬齡分析表!$B$2:$I$8，MATCH(B$5，超期催收表!$B$2:$B$8，0)，F(xiàn)ALSE)”，然后復(fù)制該公式至C6～G6單元格。H6單元格輸入公式:“=SUM(B6:G6)”。賬齡－客戶分析表完成。

(3)繪制動態(tài)圖表

選擇A5:G6單元格，選擇“插入——圖表——條形圖”即生成公司總體的相應(yīng)賬齡的各公司應(yīng)收賬款動態(tài)圖。

四、結(jié)論及應(yīng)用建議

通過以上6個步驟，應(yīng)收賬款賬齡管理模型建立完成。模型總計6張sheet，分別為:客戶及信用額度、應(yīng)收賬款基本信息、賬齡分析表、超期催收表、賬齡分析圖、總體應(yīng)收賬款賬齡分析圖。

通過該模型，應(yīng)收賬款管理人員可以簡便快速地輸入客戶信息、應(yīng)收賬款信息;可以直觀地查詢到每筆應(yīng)收賬款的金額、是否到期以及超期天數(shù);可以滿足不同客戶經(jīng)理的各自查詢要求;可以提供公司整體的應(yīng)收賬款賬齡情況以及超期應(yīng)收賬款情況。極大程度地簡化了操作人員具體工作中的內(nèi)容步驟，也為管理人員提供了及時有效的信息，目前在T公司應(yīng)收賬款管理中已經(jīng)得到廣泛應(yīng)用，但是由于T公司的客戶對象眾多且不斷變化，該模型尚需要使用人員不斷地調(diào)整更新。另一方面，實際工作中本模型需要與公司的其他管理系統(tǒng)相對接，例如數(shù)據(jù)來源于公司的項目管理系統(tǒng)，輸出的表格要應(yīng)用于公司的下發(fā)報表系統(tǒng)等，因此在實際應(yīng)用重要注意與其他系統(tǒng)的銜接與一致性。

［1］鐘愛軍.EXCEL在財務(wù)與會計中的高級應(yīng)用［M］.武漢:武漢大學(xué)出版社，2012.

［2］Excel Home.Excel數(shù)據(jù)處理與分析實戰(zhàn)技巧精粹［M］.北京:人民郵電出版社，2010.