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

高雪平

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

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

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

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

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

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

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

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

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

1.賬期確定

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

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

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

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

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

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

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

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

1.建立客戶信息表，定義名稱(chēng)

圖1 客戶及信用額度

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

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

圖2 定義客戶名稱(chēng)

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

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

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

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

(2)填入該表各項(xiàng)要素

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

(3)計(jì)算欠款

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

(4)計(jì)算到期日

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

(5)判斷是否超期

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

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

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

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

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

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

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

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

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

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

對(duì)于客戶賬齡段的劃分，以15天為一個(gè)時(shí)間段，共分為7段，如下圖:

圖5 賬齡分析表

其中A列、B列為財(cái)務(wù)人員手工輸入的客戶編號(hào)及客戶名稱(chēng)。

在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整個(gè)區(qū)域，這樣就將6個(gè)公司的各個(gè)時(shí)段賬齡都匯總出來(lái)。J列用SUM函數(shù)進(jìn)行行數(shù)據(jù)合計(jì)，在第9行用SUM函數(shù)進(jìn)行列數(shù)據(jù)合計(jì)。在K3單元格輸入:“=J3/$J$9”，再?gòu)?fù)制公式至K9，在C10單元格輸入公式:“=C9/$J$9”，再?gòu)?fù)制公式至J10單元格。這樣就得到各公司、各賬齡應(yīng)收賬款占總數(shù)的百分比。

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

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

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

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

圖6 超期催收表

(1)定義名稱(chēng)“賬齡”

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

圖7 定義賬齡

(2)計(jì)算各公司分分時(shí)段超期金額

在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)匯總

對(duì)超期催收表按行、按列進(jìn)行匯總并且求出占比。

(4)突出顯示

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

圖8 突出顯示超期公司

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

圖9 突出顯示超期金額

(5)校驗(yàn)結(jié)果

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

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

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

圖10 賬齡分析圖

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

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

圖11 繪制活動(dòng)控件

在LinkedCell格內(nèi)填上 A6，在 ListFillRange格內(nèi)填上“客戶”1?；剀?chē)后活動(dòng)控件設(shè)置完成。

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

按照上圖6所示，在B5:H5單元格畫(huà)好表格。在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)繪制動(dòng)態(tài)圖表

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

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

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

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

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

鼠標(biāo)選擇B3單元格，選擇“數(shù)據(jù)——數(shù)據(jù)有效性——數(shù)據(jù)有效性”，在跳出的對(duì)話框中，“允許”單元格選擇序列，“來(lái)源”單元格輸入“=賬齡”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)繪制動(dòng)態(tài)圖表

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

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

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

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

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

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