巧用Excel實施應收賬款的賬齡分析

1、從新的角度探討一種以 excel 作為分析工具的相對比較實用、比較有效、更加易于財務人員掌握的分析方法, 其基本思路是日常在應收賬款發(fā)生或回款時建立 excel 數(shù)據(jù)清單進行詳細記錄,在編制賬齡分析表前進行業(yè)務的核銷, 隨后建立數(shù)據(jù)透視表編制賬齡分析表, 這樣的表可以被靈活地進行重新設置, 以滿足不同的賬齡分析目標?！娟P鍵詞】應收賬款; 賬齡分析; excel; 數(shù)據(jù)透視表中圖分類號 f232文獻標識碼 a文章編號 1673- 0194(2006)10- 0037- 04應收賬款是指企業(yè)因銷售商品、產(chǎn)品或提供勞務等原因, 應向客戶或接受勞務的客戶收取的款項或代 墊的運雜費等。應收賬款發(fā)生后,

2、企業(yè)應采取各種措 施, 盡量爭取按期收回款項, 否則會因拖欠時間過長而 發(fā)生壞賬, 使企業(yè)蒙受損失。其中一個重要的措施就是 及時地實施應收賬款賬齡分析, 編制賬齡分析表。ex- cel 是一種實施應收賬款賬齡分析的很好的工具, 應用 excel 編制賬齡分析表相對手工編制賬齡分析表更加 快捷、精確, 賬齡分析的結果更便于企業(yè)對其應收賬款 實施監(jiān)督, 以及時地收回款項, 降低企業(yè)經(jīng)營風險。應 用 excel 實施賬齡分析, 編制應收賬款賬齡分析表的方 法有多種, 本文從一個新的角度, 探討如何綜合應用 excel 的多種功能, 建立數(shù)據(jù)透視表來編制應收賬款賬 齡分析表的方法, 這種方法相對其他應

3、用 excel 實施賬 齡分析的方法效率更高、更加實用。一、應用 exce l 實施應收賬款賬齡分析的模式 的確定定期對應收賬款進行整理, 分析企業(yè)應收賬款在 各個賬齡時間段的金額、百分比等分布情況, 編制應收 賬款賬齡分析表, 這就是應收賬款的賬齡分析。表 1 是某企業(yè) 2005 年 12 月 31 日的應收賬款賬齡分析表。表 1應收賬款賬齡分析表通過賬齡分析可以了解到企業(yè)應收賬款的分布情況, 可以運用最適當?shù)氖湛钫呤栈乜铐? 此外還可以 預測有多少欠款會因拖欠時間太久而可能成為壞賬。 對可能發(fā)生的壞賬損失, 企業(yè)應提前做好準備, 充分估 計這一因素對損益的影響, 從而對應收賬款的回收情

4、況實施有效的監(jiān)督和管理?？梢? 賬齡分析是相當重要 的。應用 excel 實施賬齡分析可以對傳統(tǒng)的模式進行擴 展, 以增加賬齡分析的效果, 這完全是由于 excel 具有 豐富的自動數(shù)據(jù)處理功能。1. 賬齡時間段的確定賬齡是指應收賬款的發(fā)生時間與分析評價時間之 間的間隔。例如, 一筆應收賬款的發(fā)生時間為 2006 年 1應收賬款賬齡應收賬款金額( 元)百分比未到期30 00038%過期 30 天20 00025%過期 60 天15 00019%過期 90 天10 00012%過期 90 天以上5 0006%合計80 000100%慮信用期, 因為企業(yè)為了吸引客戶, 一般允許客戶從購2. 篩選貨

5、到付款之間有一段時間的間隔, 這就是信用期間。一般來說, 在信用期內(nèi)的應收賬款我們一般認為其是良 性的, 這樣, 我們編制的應收賬款賬齡分析表中的賬齡 可以這樣劃分: 信用期內(nèi)、超過信用期 30 天、超過信用 期 60 天、超過信用期 90 天、超過信用期 90 天以上等, 這樣的賬齡的分析表對應收賬款是否可以收回有更精 確的表示。此外, 對賬齡時間段的劃分, 企業(yè)可以根據(jù)賬齡分 析的目標以及企業(yè)的特點來確定。當然, 一般來說, 賬 齡時間段劃分越靈活, 越細致, 所編制的賬齡分析表更 易于對應收賬款的可回收性進行判斷, 更易于精確地 估計壞賬, 但是能否編制出更加靈活細致的賬齡時間 段的分析

6、表與分析工具的性能有直接的關系, 手工就 比較難實現(xiàn), 但 excel 完全可以勝任, 可以滿足賬齡時 間段劃分細密的要求。下面是應用 excel 進行賬齡分excel 提供了數(shù)據(jù)篩選功能, 可以由操作人員指定篩選條件, 由系統(tǒng)將滿足條件的數(shù)據(jù)篩選出來。數(shù)據(jù)篩 選又包括自動篩選和高級篩選兩種方式。本文應用的 自動篩選方法是: 單擊命令 【數(shù)據(jù)】|【篩選】|【自動篩 選】, 隨后指定篩選條件進行數(shù)據(jù)的篩選工作。3. 數(shù)據(jù)透視表的建立數(shù)據(jù)透視表是一種對大量數(shù)據(jù)進行自動快速匯總 和交叉列表的交互式表格, 方法是: 單擊命令【數(shù)據(jù)】|【數(shù)據(jù)透視表和圖表報告】, 隨后可以在向導的提示下,由操作員指定被匯

7、總的數(shù)據(jù), 匯總條件, “對某字段求 和、求平均”等匯總方式, 隨后系統(tǒng)會自動生成數(shù)據(jù)透 視表。4. 所調(diào)用的函數(shù)now( ), 是 excel 函數(shù), 其功能是返回當前日期和時間所對應的系列數(shù)。例 如 , 在 a1 單 元 格 輸 入 “=now()”, 則析的一種賬齡時間段的劃分樣本。當然, 因為應收賬款是流動資產(chǎn), 所以對一年之內(nèi)a1 單元格會顯示的結果,的信息可以分得更細致一些,些。2. 增加客戶信息一年以上可以更粗略一表示今天的日期及今天的時間。在應用 excel 進行賬齡分析時可以調(diào)用 now( )函數(shù)表示當前進行分析評價 應收賬款的時間, 這樣編制的賬齡分析表就是一個動 態(tài)的表,

8、 每天的賬齡分析表都會由 excel 自動更新數(shù)據(jù)內(nèi)容, 達到實施監(jiān)督應收賬款發(fā)生情況的目的。此外還有 if( )函數(shù), 用于對賬齡期間的公式進行 定義。三、應用 exce l 實施應收賬款賬齡分析的方法( 一) 日常應用 excel 記錄應收賬款的發(fā)生情況建立 excel 數(shù)據(jù)清單, 包含如下一些數(shù)據(jù)項: 客戶 名稱、發(fā)貨單號、業(yè)務發(fā)生日期、應收賬款借方發(fā)生額、 應收賬款貸方發(fā)生額、信用期限等, 記錄應收賬款相關 信息, 當應收賬款發(fā)生或回款時, 及時將信息記錄在數(shù) 據(jù)清單中。應收賬款數(shù)據(jù)清單如圖 1 所示。編制應收賬款賬齡分析表的目的之一就是要催收款項,所以可以在表 1 模式的賬齡分析表中

9、增加分析每個客戶在不同賬齡時間段內(nèi)的應收賬款分布情況,這樣便于及時通知客戶回款。如果是手工會計, 編制分 析表時增加分析客戶的應收賬款, 可能會增加更多的 工作量, 有時甚至會因耗費太多的人工、時間而不能實 現(xiàn), 但是應用 excel, 其實現(xiàn)起來是相當輕松的, 與編制 表 1 模式的賬齡分析表的工作量沒有特別明顯的增 加。二、應用 exce l 實施應收賬款賬齡分析所用到 的功能1. 排序excel 提供了數(shù)據(jù)排序功能, 可以對工作表中的數(shù) 據(jù)按照某個或某些字段重新進行排列順序。在進行賬 齡分析時, 為了對客戶數(shù)據(jù)進行分類匯總, 可以事先應 用 excel 的排序功能, 其方法是: 選中被排

10、序的數(shù)據(jù)清 單中的任一單元格, 單擊命令【數(shù)據(jù)】|【排序】, 指定關圖 1信用 期內(nèi)30天60天90天120天180天270天360天12年23年3 年 以上生成新的未核銷的應收賬款數(shù)據(jù)清單, 如圖 3 所示。行各種方式的核銷, 生成多張賬齡分析表, 用于比較進行更深入的分析, 這就是運用 excel 進行賬齡分析的魅 力所在。對于應收賬款的核銷, 可以采用系統(tǒng)自動核銷或 手工核銷的方式。本文主要講解手工核銷方法, 按照如 下步驟完成:1. 對應收賬款清單進行排序, 排序的主要關鍵字 段為“客戶名稱”, 次要關鍵字為“業(yè)務發(fā)生時間”。2. 手工核銷對排序過的應收賬款清單, 人工進行對已經(jīng)核銷

11、的業(yè)務進行判斷, 如果正好有一些業(yè)務能夠借貸抵消, 則直接在各行的核銷標志單元格中增加 “結算”兩個 字, 如圖 2 中第 16 行和 17 行榮樂公司的核銷結果, 如 果不能正好核銷, 則需要增加一行, 存儲剩余未核銷的業(yè)務, 如圖 2 中大洋公司第 6 行和第 8 行的核銷, 第 9圖 33. 進行公式的定義, 以圖 3 內(nèi)容為例, 介紹公式定 義的步驟如下:( 1) “到期日”公式的定義: 在 f2 單元格輸入文本 “到期日”, 在 f3 單元格, 定義公式如下: =c3+e3( 到期 日=業(yè)務發(fā)生時間+信用期限) 。( 2) 公式向下遞推。鼠標指針指向 f3 單元格, 按住 填充柄向下

12、填充進行公式的遞推, 直至所有客戶的到 期日全部自動生成為止。( 3) “逾期天數(shù)”公式的定義: 在 g2 單元格輸入“逾 期天數(shù)”, 在 g3 單元格, 定義公式如下: =if(now( )- f30,now( )- f3,0) ( 逾期天數(shù)=當前日期- 到期日) , 并 設置該單元格的單元格屬性為“數(shù)值”型。( 4) 定義賬齡公式: 在 h2 單元格輸入文本“到期情 況”, 在 h3 輸入公式如下:=if (g31080,“(9) 超過信用期 3 年以上”,if (g3720,“(8)超過信用期 23 年”,if(g3360,“(7)超過信用期12 年”,if(g3180,“(6)超過信用

13、期 180360 天”,if(g390,“(5)超過信用期 90180 天”,if(g360,“(4)超過信用 期 6090 天”,if (g330,“(3) 超過信用期 3060 天”,if (g30,“(2)超過信用期 030 天”,“(1)信用期內(nèi)”)定義公式完成后, 將鼠標指針指向 h3 單元格, 按行是第 6 行和第 8 行核銷后的結果,加的內(nèi)容。是由操作人員增圖 2照順序自動排列數(shù)據(jù), 所以上述公式中的各個賬齡期稱”字段到行區(qū), “到期情況”到列區(qū), 就得到如下格式的數(shù)據(jù)透視表, 如圖 6:間在文本前增加了順序標號( 1) 、( 2) 等, 主要是為了建立的數(shù)據(jù)透視表中賬齡期間能按

14、賬齡時間段的前后順 序排列。4. 建立數(shù)據(jù)透視表來生成賬齡分析表, 單擊 【數(shù) 據(jù)】|【數(shù)據(jù)透視表和圖表報告】命令啟動建立數(shù)據(jù)透視 表的向導, 在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導3 步驟 之 3”對話框中單擊 “版 式 ”按 鈕 , 將 “到 期 情 況 ”拖 到 “行”區(qū), 將“客戶名稱”拖到“列”區(qū), 這樣就是指定了行 向和列向匯總的條件, 將“應收賬款借方發(fā)生額”拖到 “數(shù)據(jù)”區(qū), 并指定匯總方式為“求和”, 所指定的情況如 圖 4 所示:圖 6同樣的方法可以重新指定行區(qū)、列區(qū)字段對已經(jīng) 生成的賬齡分析表進行信息變化, 以滿足不同的分析 目的。對圖 5 的賬齡分析表, 可以將 b 列至 i 列的信息 隱藏, 得到表 1 模式的賬齡分析表。這就是靈活的賬齡 分析。五、結束語建立數(shù)據(jù)透視表、編制應收賬款賬齡分析表以實 施賬齡分析的方法簡單、易學、有效, 所編制的分析表 可以通過重新指定不同的字段到數(shù)據(jù)透視表“版式”設 置對話框的“頁區(qū)、行區(qū)、列區(qū)、數(shù)據(jù)區(qū)”而自動地改變 其樣式, 這樣的分析表是動態(tài)靈活的, 更加便于對應收賬款實施有效監(jiān)督。圖 4建立好的數(shù)據(jù)透視表如圖 5:主要參考文獻1 北京注冊會計師考試委員會辦公室.會計m.北京: 中國財政經(jīng)濟出版社, 2006.2 北京注冊