基于Excel的應(yīng)收賬款管理動態(tài)模型設(shè)計(jì)

景秋云 王莉婕 趙小龍

【摘 要】 企業(yè)在進(jìn)行應(yīng)收賬款的管理時(shí)，往往面臨著煩瑣的數(shù)據(jù)處理問題。Excel作為最普及的辦公軟件之一，在數(shù)據(jù)處理、統(tǒng)計(jì)分析和輔助決策上有很強(qiáng)的優(yōu)勢，而且操作相對簡單，為應(yīng)收賬款管理提供了很大的幫助。按照應(yīng)收賬款管理的目標(biāo)需求，利用Excel創(chuàng)建了應(yīng)收賬款管理的模型，包括應(yīng)收賬款信用政策決策模型、應(yīng)收賬款賬齡分析模型和重點(diǎn)欠款客戶模型等。該模型可以簡化工作人員在統(tǒng)計(jì)、分析數(shù)據(jù)時(shí)的工作量，并且采用分析表和分析圖兩種顯示方式，能夠生動、直觀地反映相關(guān)的數(shù)據(jù)，及時(shí)、準(zhǔn)確、高效地為決策者提供有用的信息，實(shí)現(xiàn)應(yīng)收賬款的動態(tài)管理。

【關(guān)鍵詞】 應(yīng)收賬款管理; Excel建模; 信用政策; 賬齡分析; 客戶管理

【中圖分類號】 F234.3 ?【文獻(xiàn)標(biāo)識碼】 A ?【文章編號】 1004-5937（2019）13-0138-05

應(yīng)收賬款管理是企業(yè)管理的重中之重，企業(yè)需要從龐雜的應(yīng)收賬款數(shù)據(jù)中快速找到有用的信息，提高管理的效率。目前，專業(yè)的財(cái)務(wù)軟件雖然很普及，但是這些軟件也有局限。比如，軟件開發(fā)只考慮到多數(shù)企業(yè)的普遍問題，沒考慮到不同企業(yè)的獨(dú)特細(xì)節(jié);購買成本高，后期維護(hù)費(fèi)用大。Excel軟件具有應(yīng)用廣泛、操作簡單、數(shù)據(jù)分析能力強(qiáng)的特點(diǎn)，目前多數(shù)企業(yè)在應(yīng)收賬款管理時(shí)，還是會選擇用專業(yè)財(cái)務(wù)軟件提供基礎(chǔ)數(shù)據(jù)，用Excel進(jìn)行分析的方法。本文用Excel搭建了一個簡便的模型，可以直觀地選出信用決策方案，顯示應(yīng)收賬款的賬齡情況，篩選出需重點(diǎn)催收的客戶，可以簡化應(yīng)收賬款管理的工作[1]。

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

（一）制定應(yīng)收賬款信用政策

寬松的信用政策，可以擴(kuò)大銷售量、增加銷售收入，同時(shí)也會增加應(yīng)收賬款的成本;相反，嚴(yán)格的信用政策，會影響企業(yè)的競爭力，使銷量下降，銷售收入減少，同時(shí)也會節(jié)約應(yīng)收賬款的成本。因此，選擇信用政策就得在銷售毛利和信用成本之間衡量。若只有一種信用政策方案，銷售毛利減去信用成本后的收益大于零時(shí)方案才可行;若是有多種信用政策方案，在銷售毛利減去信用成本后的收益大于零的基礎(chǔ)上選擇收益最大的方案。

（二）分析應(yīng)收賬款賬齡

應(yīng)收賬款賬齡是指企業(yè)銷售商品時(shí)從發(fā)生賒欠開始，到應(yīng)收賬款收回的天數(shù)。賬齡分析的目的是分時(shí)間段統(tǒng)計(jì)應(yīng)收賬款，有助于監(jiān)督應(yīng)收賬款的回收情況，是實(shí)施催款計(jì)劃和估計(jì)壞賬損失的基礎(chǔ)。

（三）篩選重點(diǎn)欠款客戶

在管理應(yīng)收賬款時(shí)，要對客戶進(jìn)行分類管理，欠款越多，賒欠時(shí)間越長的客戶，需要采取更強(qiáng)烈的催收策略，必要時(shí)要停止或限制供貨。所以要按總欠款金額和賒欠的時(shí)間篩選出欠款比例較高和賒欠時(shí)間較長的客戶。

二、基于Excel應(yīng)用的應(yīng)收賬款管理模型的設(shè)計(jì)思路

本模型從信用政策決策、賬齡分析、重點(diǎn)欠款客戶三方面對應(yīng)收賬款的管理進(jìn)行模型的搭建，使用的版本是Excel 2016。模型搭建的流程如圖1。

1.搭建應(yīng)收賬款的信用政策決策模型，在多個信用政策方案中選擇利潤大于零且最高的方案。

2.建立客戶基本信息，包括客戶信息表和應(yīng)收賬款明細(xì)表，為賬齡分析模型和重點(diǎn)欠款客戶模型的搭建提供基礎(chǔ)數(shù)據(jù)，這兩表的數(shù)據(jù)可以從專業(yè)的財(cái)務(wù)軟件導(dǎo)入，也可以用Excel創(chuàng)建。后期應(yīng)收賬款和客戶的增減變化只需修改這兩個表，賬齡分析表和重點(diǎn)欠款客戶表中相關(guān)數(shù)據(jù)會隨之自動更新。

3.搭建應(yīng)收賬款賬齡分析模型，按賒欠的時(shí)間段進(jìn)行分析。根據(jù)“應(yīng)收賬款明細(xì)表”中的數(shù)據(jù)，使用Excel函數(shù)統(tǒng)計(jì)不同賬齡段應(yīng)收賬款的數(shù)量、金額、所占的比例，并用圖表直觀地反映結(jié)果。

4.搭建重點(diǎn)欠款客戶模型，按客戶賒欠的金額進(jìn)行分析。統(tǒng)計(jì)出每個客戶賒欠的總金額和賒欠的時(shí)間，從每位客戶欠款占總欠款比重和欠款時(shí)間等方面對應(yīng)收賬款進(jìn)行分析。

三、基于Excel應(yīng)用的應(yīng)收賬款管理模型的設(shè)計(jì)步驟

（一）建立信用政策決策模型

對信用政策產(chǎn)生影響的主要因素有信用成本前收益、機(jī)會成本、管理費(fèi)用、壞賬損失、折扣成本。計(jì)算公式：信用成本后收益=信用成本前收益-機(jī)會成本-管理費(fèi)用-壞賬損失-折扣成本。

1.信用成本前收益

銷售量在一定范圍內(nèi)，不會引起固定成本的變化，固定成本是與決策無關(guān)的成本，此時(shí)，信用成本前收益=邊際貢獻(xiàn)=年賒銷額-變動成本。

但隨著信用政策的放寬，銷售超過一定量時(shí)，導(dǎo)致固定成本增加，就要在決策中考慮固定成本的變化對信用決策的影響。此時(shí)，信用成本前收益=年賒銷額-變動成本-增加的固定成本。

2.機(jī)會成本

應(yīng)收賬款的機(jī)會成本是指沒有收回的應(yīng)收賬款中被占用的資金所喪失的潛在收益，固定成本不變時(shí)只考慮變動成本中被占用的資金。

機(jī)會成本=年賒銷額÷360×信用期×變動成本率×機(jī)會成本率。

其中，信用期是企業(yè)允許顧客從購貨到付款之間的時(shí)間。

變動成本率=變動成本÷銷售額。

機(jī)會成本率可采用投資者要求的必要投資報(bào)酬率或同期的有價(jià)證券利率。

3.管理成本

管理成本主要由管理人員的工資、辦公費(fèi)用、對客戶資信調(diào)查的費(fèi)用、收集信用信息的費(fèi)用、催收的費(fèi)用等組成。管理成本=年賒銷額×收賬費(fèi)用率。

4.壞賬損失

應(yīng)收賬款的壞賬損失是指因應(yīng)收賬款沒有收回而產(chǎn)生的損失。壞賬損失=年賒銷額×壞賬損失率。壞賬損失率可以根據(jù)企業(yè)應(yīng)收賬款的實(shí)際情況預(yù)計(jì)，采用的方法有銷售百分比法、賬齡分析法、應(yīng)收賬款余額百分比法等。

5.折扣成本

現(xiàn)金折扣是企業(yè)為了鼓勵顧客在規(guī)定的還款期內(nèi)早日還款，而給予的還款金額的減讓。現(xiàn)金折扣帶來折扣成本，其次還會影響到平均收款的天數(shù)，導(dǎo)致機(jī)會成本的變化。

本例中，有4個備選方案，見圖2信用政策決策模型中的方案1至方案4，從中選出最佳方案。選擇最佳決策方案的依據(jù)為：所有的方案信用成本后收益都小于零，則顯示無方案，否則選擇信用成本后收益最高的方案。

圖2中灰色區(qū)域?yàn)樽詣佑?jì)算部分，公式如下：

B7=B5*B6，B9=B7*B8，B11=B7-B9-B10，B14=

B7/360*B4*B8*B13，B16=B7*B15，B18=B7*B17，B20=

B14+B16+B18，B21=B11-B20，各行其余公式用“自動填充”功能填充。

D4=60%*10+15%*20+（1-60%-15%）*60，在方案3中假定60%的客戶在10天內(nèi)還款，15%的客戶在20天內(nèi)還款，D4為方案3的平均收款期。

D19=D7*（2%*60%+1%*15%），D19為方案3的平均折扣成本。

B22=IF（MAX（B21：E21）<0，"無方案"，"應(yīng)采用"&INDEX（B3：E3，1，MATCH（MAX（B21：E21），B21：E21，0）））。

此信用政策決策模型可滿足一般的需求，復(fù)雜一些的信用政策決策模型的設(shè)計(jì)還可以考慮到外部環(huán)境的變化、企業(yè)對風(fēng)險(xiǎn)的偏好、借款利息、所得稅等因素[2]。

（二）建立客戶的基本信息

客戶的基本信息包含“客戶信息表”和“應(yīng)收賬款明細(xì)表”兩部分。

1.創(chuàng)建“客戶信息表”，如圖3。

該表不僅可以用來管理往來客戶的信息，還可以為今后編制各種應(yīng)收賬款的分析表提供基礎(chǔ)數(shù)據(jù)，方便其他表直接生成數(shù)據(jù)。

2.創(chuàng)建“應(yīng)收賬款明細(xì)表”，如圖4。

本例數(shù)據(jù)分析日期為2017年12月22日，為驗(yàn)證本例數(shù)據(jù)，請將計(jì)算機(jī)系統(tǒng)當(dāng)前日期調(diào)整為2017年12月22日。

在本例中，根據(jù)圖2信用政策決策模型，得知該企業(yè)的最佳信用政策為方案3（2/10，1/20，n/60），信用期為60天。灰色區(qū)域?yàn)樽詣佑?jì)算部分，公式如下：

（1）客戶名稱：C4=VLOOKUP（B4，客戶信息表！$A$3：$B$24，2，F(xiàn)ALSE），目的是用VLOOKUP函數(shù)在客戶信息表和應(yīng)收賬款明細(xì)表之間建立動態(tài)鏈接，方便后期數(shù)據(jù)的維護(hù)。

（2）應(yīng)收賬款余額：F4=D4-E4。

其中，仍在信用期內(nèi)的應(yīng)收賬款的金額：

G4=IF（AND（（TODAY（）-A4）>0，（TODAY（）-A4）<=60），$F4，0）。TODAY函數(shù)表示系統(tǒng)當(dāng)前日期。

超期的應(yīng)收賬款金額：

超期1—30天：H4=IF（AND（（TODAY（）-A4）>60，（TODAY（）-A4）<=90），$F4，0）。

超期31—90天：I4=IF（AND（（TODAY（）-A4）>90，（TODAY（）-A4）<=150），$F4，0）。

超期91—180天：J4=IF（AND（（TODAY（）-A4）>150，（TODAY（）-A4）<=240），$F4，0）。

超期181天以上：K4=IF（（TODAY（）-A4）>240，$F4，0）。

各列中其余公式用“自動填充”功能填充。

（三）創(chuàng)建應(yīng)收賬款賬齡分析模型

應(yīng)收賬款賬齡分析模型包括分析表和分析圖兩部分。

1.創(chuàng)建應(yīng)收賬款賬齡分析表，如圖5。

B4=COUNTIF（應(yīng)收賬款明細(xì)表！G：G，">0"）

B5=SUM（B6：B9）

B6=COUNTIF（應(yīng)收賬款明細(xì)表！H：H，">0"）

B7=COUNTIF（應(yīng)收賬款明細(xì)表！I：I，">0"）

B8=COUNTIF（應(yīng)收賬款明細(xì)表！J：J，">0"）

B9=COUNTIF（應(yīng)收賬款明細(xì)表！K：K，">0"）

B10=B4+B5

C4=SUM（應(yīng)收賬款明細(xì)表！G：G）

C5=SUM（C6：C9）

C6=SUM（應(yīng)收賬款明細(xì)表！H：H）

C7=SUM（應(yīng)收賬款明細(xì)表！I：I）

C8=SUM（應(yīng)收賬款明細(xì)表！J：J）

C9=SUM（應(yīng)收賬款明細(xì)表！K：K）

C10=C4+C5

D4=C4/$C$10，用“自動填充”功能填充該列。

將D4：D10區(qū)域設(shè)置為百分比格式，并保留2位小數(shù)。

通過函數(shù)在應(yīng)收賬款明細(xì)表和賬齡分析表之間建立動態(tài)鏈接。

通過賬齡分析表可以了解到以下信息：

該企業(yè)應(yīng)收賬款的總額為1 819 086.40元。

應(yīng)收賬款在信用期內(nèi)的有1 013 886.40元，占應(yīng)收賬款總額的55.74%。這些款項(xiàng)仍在信用期內(nèi)，不需要向客戶催收，但要進(jìn)行必要的監(jiān)督。

應(yīng)收賬款超過了信用期的有805 200.00元，占全部應(yīng)收賬款的44.26%。其中，超過信用期1—30天的金額為78 960.00元，占全部應(yīng)收賬款的4.34%，這部分欠款收回的可能性很大。超過信用期31—90天的金額為251 200.00元，占全部應(yīng)收賬款的13.81%，這部分欠款的回收有一定難度。超過信用期91—180天的金額為374 960.00元，占全部應(yīng)收賬款的20.61%、超過信用期181天以上的金額為100 080.00元，占到了應(yīng)收賬款總金額的5.50%，這部分欠款由于拖欠的時(shí)間最長，成為壞賬的可能性最大。

對于欠款時(shí)間不同的企業(yè)采用不同的收賬政策，以避免對企業(yè)的經(jīng)營產(chǎn)生較大的影響。

2.創(chuàng)建應(yīng)收賬款賬齡分析圖，如圖6。

使用圖表分析應(yīng)收賬款賬齡，可使結(jié)果更為直觀。具體步驟如下：

（1）選定數(shù)據(jù)

依次選擇賬齡分析表中A4、D4、A6、D6、A7、D7、A8、D8、A9和D9，創(chuàng)建“應(yīng)收賬款賬齡分析圖”。需要嚴(yán)格按以上順序進(jìn)行選定，即先選擇應(yīng)收賬款項(xiàng)目單元格，再選中相應(yīng)金額單元格，否則制作出的圖表不能達(dá)到預(yù)期的效果[3]。

（2）插入圖表

在“插入”選項(xiàng)卡中“圖表”組中選擇“二維餅圖”——“子母餅圖”，將圖表移動到新工作表中，命名為“應(yīng)收賬款賬齡分析圖”。修改數(shù)據(jù)系列格式，將第二繪圖區(qū)的值改為4，這樣第一繪圖區(qū)顯示的是信用期內(nèi)和超信用期金額的比，第二繪圖區(qū)顯示的是超信用期的四個組成部分。

（3）修飾圖表

圖表樣式選擇“樣式12”，選擇“更改顏色”—“單色”—“顏色11”。添加數(shù)據(jù)標(biāo)簽，設(shè)置數(shù)據(jù)標(biāo)簽的格式為“百分比”，位置為“數(shù)據(jù)標(biāo)簽外”;選擇數(shù)字標(biāo)簽，設(shè)置字體為20。圖例位置選擇“靠右”，圖例字體選擇18，添加圖例實(shí)線邊框。

通過圖表可以看出應(yīng)收賬款的結(jié)構(gòu)：信用期內(nèi)的占55.74%;超出信用期的占44.26%，其中超期1—30天的占4.34%，超期31—90天的占13.81%，超期91—180天的占20.61%，超期181天以上的占5.50%。該企業(yè)超期的應(yīng)收賬款所占的比重過大，應(yīng)加強(qiáng)管理。

（四）創(chuàng)建重點(diǎn)欠款客戶分析模型

客戶欠款分析模型包括分析表和分析圖兩部分，可反映出累計(jì)欠款較高的客戶。

1.創(chuàng)建重點(diǎn)客戶欠款分析表，如圖7

同一個客戶會發(fā)生不同時(shí)期的欠款，此表對同一個客戶不同時(shí)間的欠款進(jìn)行匯總，目的是為了分析每個客戶的欠款總金額和不同時(shí)間段欠款的分布情況，找出欠款金額多，時(shí)間長的重點(diǎn)客戶，進(jìn)行重點(diǎn)管理。

公式如下：

A4=客戶信息表！A3，B4=客戶信息表！B3，C4=客戶信息表！C3，D4=客戶信息表！D3

E4=SUMIF（應(yīng)收賬款明細(xì)表！$C：$C，$B4，應(yīng)收賬款明細(xì)表！F：F）

拖動E4單元格右下角的填充柄到J4單元格，接著拖動A4：J4區(qū)域右下角的填充柄到J25單元格，快速完成A5：J25區(qū)域里所有單元格公式的定義。

選擇E列到J列，選擇“條件格式”——“數(shù)據(jù)條”——“漸變填充”，可以直觀地顯示數(shù)據(jù)的大小。

2.創(chuàng)建重點(diǎn)欠款客戶分析圖，如圖8

選擇“重點(diǎn)欠款客戶分析表”工作表中的B2：B25和F2：J25區(qū)域，單擊“插入”選項(xiàng)卡下“圖表”分組命令中的“堆積柱形圖”，在數(shù)軸上用不同的顏色顯示出同一個客戶在不同時(shí)間段的欠款情況和該企業(yè)的總欠款情況。能看到欠款較多的是客戶13、客戶21、客戶16等企業(yè)，要著重加強(qiáng)這些企業(yè)應(yīng)收賬款的管理，以免給企業(yè)帶來較大的損失。

四、結(jié)語

應(yīng)收賬款管理在企業(yè)運(yùn)營中起著重要的作用，首先，用Excel建立應(yīng)收賬款管理模型，從應(yīng)收賬款信用政策決策、應(yīng)收賬款賬齡分析和重點(diǎn)欠款客戶分析等方面對應(yīng)收賬款進(jìn)行管理，可以簡化員工的工作量，快速準(zhǔn)確地給決策層提供數(shù)據(jù);其次，在應(yīng)收賬款賬齡分析表、重點(diǎn)欠款客戶分析表中采用了公式和函數(shù)，可以動態(tài)鏈接基礎(chǔ)信息和分析表、分析圖，方便后期數(shù)據(jù)的維護(hù)，為企業(yè)應(yīng)收賬款的管理提供實(shí)時(shí)信息;最后，該模型采用了圖、表兩種顯示方法，使結(jié)果的展示更為直觀。

【參考文獻(xiàn)】

[1] 周紅.Excel在財(cái)務(wù)工作中的應(yīng)用探索三例[J].財(cái)會學(xué)習(xí)，2016（11）：13-15.

[2] 穆敏麗.應(yīng)收賬款信用政策決策模型的改進(jìn)[J].財(cái)會月刊，2010（17）：46-47.

[3] 郭燕，張林義，陳海燕.中文版Excel 2007財(cái)務(wù)應(yīng)用實(shí)例與操作[M].北京：航空工業(yè)出版社，2011：122.