利用EXCEL建立企業應收賬款賬齡分析管理模型

高雪平

(浙江經貿職業技術學院，浙江 杭州 310007)

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

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

一、應收賬款賬齡管理目標需求

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

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

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

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

二、基于EXCEL的應收賬款賬齡管理模型建設思路

1.賬期確定

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

2.計算已到期應收賬款金額以及超期天數

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

3.分客戶、分超期天數繪制動態圖表

從客戶維度分析各個客戶的不同超期時間段的應收賬款情況，從超期天數維度分析各個客戶的應收賬款情況。

4.基礎數據

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

三、基于EXCEL的應收賬款賬齡管理模型建設步驟

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

圖1 客戶及信用額度

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

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

圖2 定義客戶名稱

2.創建應收賬款信息表

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

圖3 應收賬款基本信息

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

(2)填入該表各項要素

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

(3)計算欠款

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

(4)計算到期日

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

(5)判斷是否超期

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

(6)計算超期天數

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

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

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

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

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

圖4 超期應收賬款突出顯示

3.創建應收賬款賬齡分析表

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

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

圖5 賬齡分析表

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

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

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

4.創建應收賬款超期催收表

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

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

圖6 超期催收表

(1)定義名稱“賬齡”

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

圖7 定義賬齡

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

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

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

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

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

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

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

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

選中C3:I3區域，一起復制公式至3～8行。

(3)匯總

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

(4)突出顯示

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

圖8 突出顯示超期公司

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

圖9 突出顯示超期金額

(5)校驗結果

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

5.創建各公司應收賬款客戶－賬齡分析動態圖

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

圖10 賬齡分析圖

(1)設置活動控件按鈕

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

圖11 繪制活動控件

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

(2)設置動態數據

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

(3)繪制動態圖表

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

6.創建公司整體應收賬款賬齡－客戶分析動態圖

新建一張sheet，命名為“總體應收賬款賬齡分析圖”。建立動態圖形如下圖所示:

圖12 總體應收賬款賬齡分析圖

(1)設置賬齡選擇區

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

圖13 設置賬齡選擇

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

(2)設置賬齡分析表

A5:H6區域設置如圖13所示的分析表。

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

(3)繪制動態圖表

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

四、結論及應用建議

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

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

［1］鐘愛軍.EXCEL在財務與會計中的高級應用［M］.武漢:武漢大學出版社，2012.

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