基于Excel的應收賬款管理動態模型設計

景秋云 王莉婕 趙小龍

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

【關鍵詞】 應收賬款管理; Excel建模; 信用政策; 賬齡分析; 客戶管理

【中圖分類號】 F234.3 ?【文獻標識碼】 A ?【文章編號】 1004-5937（2019）13-0138-05

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

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

（一）制定應收賬款信用政策

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

（二）分析應收賬款賬齡

應收賬款賬齡是指企業銷售商品時從發生賒欠開始，到應收賬款收回的天數。賬齡分析的目的是分時間段統計應收賬款，有助于監督應收賬款的回收情況，是實施催款計劃和估計壞賬損失的基礎。

（三）篩選重點欠款客戶

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

二、基于Excel應用的應收賬款管理模型的設計思路

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

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

2.建立客戶基本信息，包括客戶信息表和應收賬款明細表，為賬齡分析模型和重點欠款客戶模型的搭建提供基礎數據，這兩表的數據可以從專業的財務軟件導入，也可以用Excel創建。后期應收賬款和客戶的增減變化只需修改這兩個表，賬齡分析表和重點欠款客戶表中相關數據會隨之自動更新。

3.搭建應收賬款賬齡分析模型，按賒欠的時間段進行分析。根據“應收賬款明細表”中的數據，使用Excel函數統計不同賬齡段應收賬款的數量、金額、所占的比例，并用圖表直觀地反映結果。

4.搭建重點欠款客戶模型，按客戶賒欠的金額進行分析。統計出每個客戶賒欠的總金額和賒欠的時間，從每位客戶欠款占總欠款比重和欠款時間等方面對應收賬款進行分析。

三、基于Excel應用的應收賬款管理模型的設計步驟

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

對信用政策產生影響的主要因素有信用成本前收益、機會成本、管理費用、壞賬損失、折扣成本。計算公式：信用成本后收益=信用成本前收益-機會成本-管理費用-壞賬損失-折扣成本。

1.信用成本前收益

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

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

2.機會成本

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

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

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

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

機會成本率可采用投資者要求的必要投資報酬率或同期的有價證券利率。

3.管理成本

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

4.壞賬損失

應收賬款的壞賬損失是指因應收賬款沒有收回而產生的損失。壞賬損失=年賒銷額×壞賬損失率。壞賬損失率可以根據企業應收賬款的實際情況預計，采用的方法有銷售百分比法、賬齡分析法、應收賬款余額百分比法等。

5.折扣成本

現金折扣是企業為了鼓勵顧客在規定的還款期內早日還款，而給予的還款金額的減讓。現金折扣帶來折扣成本，其次還會影響到平均收款的天數，導致機會成本的變化。

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

圖2中灰色區域為自動計算部分，公式如下：

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天內還款，15%的客戶在20天內還款，D4為方案3的平均收款期。

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

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

此信用政策決策模型可滿足一般的需求，復雜一些的信用政策決策模型的設計還可以考慮到外部環境的變化、企業對風險的偏好、借款利息、所得稅等因素[2]。

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

客戶的基本信息包含“客戶信息表”和“應收賬款明細表”兩部分。

1.創建“客戶信息表”，如圖3。

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

2.創建“應收賬款明細表”，如圖4。

本例數據分析日期為2017年12月22日，為驗證本例數據，請將計算機系統當前日期調整為2017年12月22日。

在本例中，根據圖2信用政策決策模型，得知該企業的最佳信用政策為方案3（2/10，1/20，n/60），信用期為60天。灰色區域為自動計算部分，公式如下：

（1）客戶名稱：C4=VLOOKUP（B4，客戶信息表！$A$3：$B$24，2，FALSE），目的是用VLOOKUP函數在客戶信息表和應收賬款明細表之間建立動態鏈接，方便后期數據的維護。

（2）應收賬款余額：F4=D4-E4。

其中，仍在信用期內的應收賬款的金額：

G4=IF（AND（（TODAY（）-A4）>0，（TODAY（）-A4）<=60），$F4，0）。TODAY函數表示系統當前日期。

超期的應收賬款金額：

超期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）。

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

（三）創建應收賬款賬齡分析模型

應收賬款賬齡分析模型包括分析表和分析圖兩部分。

1.創建應收賬款賬齡分析表，如圖5。

B4=COUNTIF（應收賬款明細表！G：G，">0"）

B5=SUM（B6：B9）

B6=COUNTIF（應收賬款明細表！H：H，">0"）

B7=COUNTIF（應收賬款明細表！I：I，">0"）

B8=COUNTIF（應收賬款明細表！J：J，">0"）

B9=COUNTIF（應收賬款明細表！K：K，">0"）

B10=B4+B5

C4=SUM（應收賬款明細表！G：G）

C5=SUM（C6：C9）

C6=SUM（應收賬款明細表！H：H）

C7=SUM（應收賬款明細表！I：I）

C8=SUM（應收賬款明細表！J：J）

C9=SUM（應收賬款明細表！K：K）

C10=C4+C5

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

將D4：D10區域設置為百分比格式，并保留2位小數。

通過函數在應收賬款明細表和賬齡分析表之間建立動態鏈接。

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

該企業應收賬款的總額為1 819 086.40元。

應收賬款在信用期內的有1 013 886.40元，占應收賬款總額的55.74%。這些款項仍在信用期內，不需要向客戶催收，但要進行必要的監督。

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

對于欠款時間不同的企業采用不同的收賬政策，以避免對企業的經營產生較大的影響。

2.創建應收賬款賬齡分析圖，如圖6。

使用圖表分析應收賬款賬齡，可使結果更為直觀。具體步驟如下：

（1）選定數據

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

（2）插入圖表

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

（3）修飾圖表

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

通過圖表可以看出應收賬款的結構：信用期內的占55.74%;超出信用期的占44.26%，其中超期1—30天的占4.34%，超期31—90天的占13.81%，超期91—180天的占20.61%，超期181天以上的占5.50%。該企業超期的應收賬款所占的比重過大，應加強管理。

（四）創建重點欠款客戶分析模型

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

1.創建重點客戶欠款分析表，如圖7

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

公式如下：

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

E4=SUMIF（應收賬款明細表！$C：$C，$B4，應收賬款明細表！F：F）

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

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

2.創建重點欠款客戶分析圖，如圖8

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

四、結語

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

【參考文獻】

[1] 周紅.Excel在財務工作中的應用探索三例[J].財會學習，2016（11）：13-15.

[2] 穆敏麗.應收賬款信用政策決策模型的改進[J].財會月刊，2010（17）：46-47.

[3] 郭燕，張林義，陳海燕.中文版Excel 2007財務應用實例與操作[M].北京：航空工業出版社，2011：122.