Excel在會計與財務中的應用課件：Excel在應收賬款管理中的應用

7.1應收賬款統(tǒng)計7.2逾期應收賬款分析7.3應收賬款賬齡分析7.4信用政策下應收賬款管理Excel在應收賬款管理中的應用內(nèi)容簡介：本章主要講解利用Excel工具進行應收賬款管理，包括運用Excel建立應收賬款統(tǒng)計表格以及對應收賬款賬齡分析，并對逾期應收賬款重點關(guān)注計提壞賬。結(jié)合實際，企業(yè)為加速回收應收賬款會設置不同信用政策，通過Excel輔助進行信用決策選擇。學習目標：通關(guān)本章的學習，學生應掌握運用Excel統(tǒng)計和分析應收賬款，提高應收賬款管理效率，制定合理的信用政策。內(nèi)容簡介和學習目標建立應收賬款明細登記表根據(jù)企業(yè)自身管理需要，將企業(yè)現(xiàn)有應收賬款信息登記到工作表中。以下僅提供一個參考：7.1應收賬款統(tǒng)計各債務人的應收賬款統(tǒng)計現(xiàn)有的各項應收賬款登記完畢后,由于債務人眾多,為了方便了解某一債務人所欠本公司款項的總額,利用Excel提供的數(shù)據(jù)命令,建立針對不同債權(quán)人所欠金額進行匯總的功能。分類匯總功能選擇“開始”選項卡中的“排序和篩選”→“自定義排序”命令。在“主要關(guān)鍵字”選擇“債務人姓名”,然后單擊“添加條件”按鈕,在出現(xiàn)的“次要關(guān)鍵字”選擇“賒銷日期”,其中“排列依據(jù)”默認為“單元格值”,“次序”默認為“升序”,單擊“確定”按鈕。7.1應收賬款統(tǒng)計各債務人的應收賬款統(tǒng)計執(zhí)行命令后,原來按照應收賬款發(fā)生先后順序登記的數(shù)據(jù),重排序為按照債務人姓名進行排序。如圖所示。7.1應收賬款統(tǒng)計各債務人的應收賬款統(tǒng)計對各債務人的應收賬款金額進行匯總,選中B1單元格即選中“債務人名稱”單元格,選擇“數(shù)據(jù)”→“分級顯示”→“分類匯總”命令.7.1應收賬款統(tǒng)計各債務人的應收賬款統(tǒng)計在彈出的“分類匯總”對話框中,“分類字段”選擇“債務人姓名”,“匯總方式”選擇“求和”,“選定匯總項”選擇“應收金額”,默認選擇“替換當前分類匯總”和“匯總結(jié)果顯示在數(shù)據(jù)下方”后,單擊“確認”按鈕。7.1應收賬款統(tǒng)計各債務人的應收賬款統(tǒng)計執(zhí)行命令后,即可顯示按照債務人姓名針對應收賬款金額進行的匯總數(shù)據(jù)。7.1應收賬款統(tǒng)計通過匯總數(shù)據(jù)可以看出客戶丁和客戶乙欠本公司款項金額較大，應對其進行重點管理。各債務人的應收賬款統(tǒng)計執(zhí)行命令后,原來按照應收賬款發(fā)生先后順序登記的數(shù)據(jù),重排序為按照債務人姓名進行排序。如圖所示。7.1應收賬款統(tǒng)計完成應收賬款明細登記表后，可以對該信息進行加工，使其自動判斷應收賬款隨著時間的推移，是否到期，未到期金額發(fā)生的變動情況。7.2逾期應收賬款分析提示：根據(jù)if函數(shù)以及if函數(shù)的嵌套，進行函數(shù)設置，自己可以嘗試動手操作下~~為了下文進一步分析應收賬款賬齡,還需要利用Excel進一步計算各項應收賬款的逾期天數(shù),以便提供更加詳細的管理數(shù)據(jù)。設計逾期天數(shù)分析表：在“逾期應收賬款分析”工作表的數(shù)據(jù)右側(cè)建立“逾期天數(shù)分析表”,對逾期天數(shù)進行分類篩選。實際工作中,通常對逾期天數(shù)劃分不同等級,如“0~30”“30~60”“60~90”“90天以上”等。7.2逾期應收賬款分析用IF函數(shù)分析逾期“0~30”天的應收賬款：選中J4單元格,輸入公式“=IF(AND($L$2-G4>0,$L$2-G4<=30),C4-E4,0)”,按“Enter”鍵確認。并將該公式復制到J5:J17單元格區(qū)域。7.2逾期應收賬款分析該公式表示某債務人逾期應收賬款的具體金額進行判斷計算,如果該表中“L2”單元格(即當前日期為2020年7月29日)大于“G4”單元格(即到期日),即該項應收賬款已經(jīng)逾期,但逾期天數(shù)在30天以內(nèi)(包括30天)。滿足上述條件,返回“C4-E4”計算公式,計算該項已經(jīng)逾期應收賬款的剩余未收金額;如果不滿足未到期條件,則返回“0”,表示未到期金額為0,即逾期應收賬款不在0~30天期限內(nèi)。同理，同學們可以自行完成30-60天，60-90天的逾期應收賬款的函數(shù)設置。7.2逾期應收賬款分析根據(jù)應收賬款逾期天數(shù)分析表所提供的信息,可使企業(yè)了解各債務人收款、欠款情況,判斷欠款的可收回程度和可能發(fā)生的損失。同時,企業(yè)還可酌情作出采取放寬或緊縮商業(yè)信用政策,并可作為衡量負責收款部門和資信部門工作效率的依據(jù)。建立應收賬款賬齡分析表7.3應收賬款賬齡分析利用“應收賬款逾期天數(shù)分析表”建立“應收賬款賬齡分析表”如圖所示7.3應收賬款賬齡分析（1）計算各賬齡所涉及的應收賬款金額編輯SUM公式引用未到期金額7.3應收賬款賬齡分析（2）計算各賬齡所涉及的應收賬款占應收賬款總額的百分比

選中C4單元格,輸入公式“=B4/$B$9”,按“Enter”鍵確認,即可計算出未到期應收賬款占應收賬款總額的百分比。復制公式到該列其他單元格中,可計算出其他賬齡所涉及的應收賬款占應收賬款總額的百分比，結(jié)果如下圖。7.3應收賬款賬齡分析（3）計算應收賬款壞賬準備的金額估計壞賬準備比例賬齡分析法下,計提壞賬準備的比例簡單得多,通常賬齡越長,發(fā)生壞賬的可能性越大,估計的壞賬準備的比例就越高。假設A集團歷史經(jīng)驗估計,未到期的應收賬款發(fā)生壞賬的可能性是0%,逾期0~30天的應收賬款發(fā)生壞賬的可能性約為1%,逾期30~60天的應收賬款發(fā)生壞賬的可能性約為3%,逾期60~90天的應收賬款發(fā)生壞賬的可能性約為6%,逾期90天以上的應收賬款發(fā)生壞賬的可能性約為10%,將估計的壞賬準備比例分別輸入D4:D9單元格。實際工作中,企業(yè)可以根據(jù)歷史經(jīng)驗進行估計。7.3應收賬款賬齡分析（3）計算應收賬款壞賬準備的金額計算壞賬準備金額①選中E3單元格,輸入“壞賬準備金額”。選中E4單元格,輸入公式“=B4*D4”,按“Enter”鍵確認。

②將E4單元格公式復制到該列已經(jīng)逾期的其他單元格中(即E5:E8),則可計算各賬齡所涉及應收賬款產(chǎn)生的壞賬準備金額。選中E9單元格,輸入公式“=SUM(E4:E8)”,按“Enter”鍵確認,計算壞賬準備總額。7.3應收賬款賬齡分析7.4.1應收賬款信用決策的基本原理在具體決策時，會涉及以下計算：計算方案的收益

邊際貢獻=銷售收入-變動成本=銷售量*單位邊際貢獻計算應收賬款的機會成本

應收賬款占用資金的機會成本=應收賬款占用資金*資金成本其中：應收賬款占用資金=應收賬款平均余額*變動成本率=日賒銷額*平均收現(xiàn)期*變動成本率

存貨占用資金的機會成本=存貨占用資金*資本成本

應付賬款占用資金抵減的機會成本=應付賬款占用資金*資本成本計算收賬費用和壞賬損失

壞賬損失=預計銷售率*壞賬損失率計算折扣成本

折扣成本=賒銷額*折扣率*享受折扣的客戶比率各方案稅前損益

稅前損益=收益-成本費用7.4信用政策下應收賬款管理7.4.2建立應收賬款決策模型[案例]A集團面對競爭，為了穩(wěn)定銷售渠道，擴大商品銷路，降低商品的管理費用，需要向客戶采用信用政策，因此產(chǎn)生應收賬款，然而賒銷政策會給企業(yè)帶來各種風險。該集團目前兩種信用方案資料如圖所示。要求：通過建立應收賬款決策模型，選擇合理的信用政策。

7.4信用政策下應收賬款管理7.4.2建立應收賬款決策模型根據(jù)已有信息，設計模型，如圖。

7.4信用政策下應收賬款管理7.4.2建立應收賬款決策模型設置模型單元格公式步驟如下：在B10單元格中輸入公式“=D4*($B$3-$B$4)”在B11單元格中輸入公式“=(D4*$B$3/$B$6)*D3*($B$4/$B$3)*$B$5”在B12單元格中輸入公式“=D6*$B$4*$B$5”在B13單元格中輸入公式“=D4*D5*$B$3”在B14單元格中輸入公式“=D7”在B15單元格中輸入公式“=B10-SUM(B11:B14)”在B