9.1Excel在資金需要量預(yù)測的應(yīng)用9.2Excel在資本成本計算中的應(yīng)用第9章籌資管理內(nèi)容簡介：本章主要講解如何利用Excel進(jìn)行資金管理，主要包括Excel對資金需要量進(jìn)行預(yù)測，和通過利用Excel函數(shù)對資本成本進(jìn)行計算，從而幫你你做出最優(yōu)資本決策。學(xué)習(xí)目標(biāo)：通過本章學(xué)習(xí)，學(xué)生應(yīng)該掌握財務(wù)管理中資金需要量預(yù)測和資本成本的基礎(chǔ)上，熟練使用Excel函數(shù)、Excel建模，和其自帶的選項卡上的功能，如預(yù)測工作表。第9章Excel在資金管理中的應(yīng)用2026/1/1429.1Excel在資金需要量預(yù)測中的應(yīng)用9.1.1因素分析法9.1.2銷售百分比法9.1.3回歸直線法9.1.4高低點法2026/1/1439.1.1因素分析法1.因素分析法因素分析法又稱分析調(diào)整法，是以有關(guān)項目基期年度的平均資金需要量為基礎(chǔ)，根據(jù)預(yù)測年度的生產(chǎn)經(jīng)營任務(wù)和資金周轉(zhuǎn)加速的要求，進(jìn)行分析調(diào)整，來預(yù)測資金需要量的一種方法。這種方法計算簡便，容易掌握，但預(yù)測結(jié)果不太精確。它通常用于品種繁多、規(guī)格復(fù)雜、資金用量較小的項目。例如：小型超市、賣場等。因素分析法的計算公式如下：資金需要量=（基期資金平均占用額-不合理資金占用額）*（1+預(yù)測期銷售增長率）*（1-預(yù)測期資金周轉(zhuǎn)速度增長率）2026/1/1449.1.1因素分析法2.知識鏈接Average函數(shù)AVERAGE(number1,number2…)

具體步驟：1、單擊要輸入函數(shù)的單元格。2、單擊“插入函數(shù)”按鈕，選擇AVERAGE函數(shù)，彈出“函數(shù)參數(shù)”對話框。3、指定參數(shù)，單擊“確定”按鈕。4、按住單元格右下角的填充手柄向下拖動至單元格進(jìn)行復(fù)制.2026/1/1459.1.1因素分析法3.例題B傳媒公司五年來的年度資金占用額如表9.1所示，經(jīng)分析，其中不合理部分200萬元，預(yù)計本年度銷售增長5%，資金周轉(zhuǎn)加速2%，則本年度的資金需要量是多少？2026/1/1469.1.1因素分析法步驟1：根據(jù)歷史資金需求量利用AVERAGE函數(shù)計算出資金平均占用額。步驟2：根據(jù)集團(tuán)的實際情況預(yù)測銷售增長率，可以依據(jù)歷史數(shù)據(jù)的平均值，也可以根據(jù)老員工的經(jīng)驗進(jìn)行預(yù)測得到預(yù)測銷售增長率。步驟3：根據(jù)集團(tuán)的實際情況預(yù)測資金周轉(zhuǎn)速度，可以依據(jù)歷史數(shù)據(jù)的平均值，也可以根據(jù)老員工的經(jīng)驗進(jìn)行預(yù)測得到預(yù)測資金周轉(zhuǎn)速度。2026/1/147步驟4：根據(jù)公式：資金需要量=（基期資金平均占用額-不合理資金占用額）*（1+預(yù)測期銷售增長率）*（1-預(yù)測期資金周轉(zhuǎn)速度增長率），來計算如圖9.2所示：2026/1/1489.1.2銷售百分比法1.銷售百分比法銷售百分比法是假設(shè)資產(chǎn)和負(fù)債與銷售額之間存在穩(wěn)定的百分比關(guān)系，根據(jù)這個假設(shè)預(yù)計外部資金需求量的方法。因為企業(yè)銷售規(guī)模擴(kuò)大時，要相應(yīng)的增加流動資產(chǎn)；如果銷售規(guī)模增加很多，還必須增加長期資產(chǎn)。為取得擴(kuò)大銷售所需增加的資產(chǎn)，企業(yè)需要籌措資金。這些資金，一部分來自隨銷售收入同比例增加的流動負(fù)債，還有一部分來自預(yù)測期的留存收益，另一部分通過外部籌資取得。資金各個項目與銷售收入之間的依存關(guān)系,并結(jié)合銷售收入的增長情況來預(yù)測計劃期企業(yè)需要從外部追加籌措資金的數(shù)額的方法。2026/1/1499.1.2銷售百分比法2.IF函數(shù)語法：IF(logical_test,value_if_true,value_if_false)參數(shù):（1）Logical_test表示計算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。（2）Value_if_true表示logical_test為TRUE時返回的值。（3）Value_if_false表示logical_test為FALSE時返回的值。2026/1/14109.1.2銷售百分比法3.例題步驟一：新建工作簿并命名為“9章籌資管理”步驟2：打開“9章籌資管理”工作簿，雙擊“sheet1“工作表標(biāo)簽，將其命名為”資金需要量預(yù)測表“。2026/1/14119.1.2銷售百分比法步驟3：根據(jù)案例資料已知條件將資產(chǎn)負(fù)債表的相關(guān)信息輸入，并建立計算區(qū)域，分析資產(chǎn)負(fù)債表各項是否是敏感性項目。利用邏輯函數(shù)IF判斷資產(chǎn)項目的敏感性,并計算出各個項目占基期銷售收入的百分比。在C11單元格中輸入公式“=IF(B11=“是”,B3/$F$3,“不適用”)”,利用填充柄將其填充至C15單元格;在C16單元格中輸入公式“=SUM(C11:C15)”,并設(shè)置C11:C16單元格格式為“百分比”（快捷鍵CTRL+1）。2026/1/14129.1.2銷售百分比法步驟4：利用邏輯函數(shù)IF判斷負(fù)債及權(quán)益項目的敏感性,并計算出各個項目占基期銷售收入的百分比。在F11單元格中輸入公式“=IF(E11="是",D3/$F$3,"不適用")”,利用填充柄將其填充至F15單元格;在F6單元格中輸入公式“=SUM(F11:F15)”,并設(shè)置F11:F16單元格區(qū)域格式為“百分比”。2026/1/14139.1.2銷售百分比法2026/1/1414步驟5：根據(jù)銷售百分比法計算公式來確定外部融資需求量。在B17單元格中輸入公式“=C16*(G3-F3)-F16*(G3-F3)-G3*G4*(1-G5)+G6”,即計算出該公司2018年的外部資金需求量為1000萬元。9.1.3回歸直線法2026/1/14151.回歸直線法回歸直線法是資金習(xí)性預(yù)測法中的一種。資金習(xí)性預(yù)測法是根據(jù)資金同業(yè)務(wù)量之間的依存關(guān)系來預(yù)測未來資金需要量的一種方法,資金習(xí)性是指資金的變動同產(chǎn)銷量(或銷售額)變動之間的依存關(guān)系。資金按照習(xí)性可以分為不變資金、變動資金和半變動資金。9.1.3回歸直線法2.知識鏈接有些功能在office的工具欄中的找不到，并不是office這個功能不存在，而是隱藏在了office的加載項中。Office加載項是Office程序為了完成某種功能而需要在啟動程序時自動加載的模塊，例如書法字帖功能、稿紙功能、制作信封功能等。用戶可以根據(jù)工作需要啟用或禁用Office加載項，以提高Office程序的運行效率。2026/1/14163.例題B傳媒公司2020—2024年的銷售收入與資金占用之間的關(guān)系如表9.2所示。2025年的預(yù)計銷售收入為700萬元。要求:用回歸直線法預(yù)測該公司2025年的資金需求量。2026/1/14179.1.3回歸直線法年度20202021202220232024銷售額x(萬元)500520480540690資金占用y(萬元)100110120125130步驟1：通過office加載項添加分析工具。文件-選項-加載項-分析工具庫。2026/1/14189.1.3回歸直線法步驟2：調(diào)用分析數(shù)據(jù)庫。管理Excel加載項,單擊“轉(zhuǎn)到”按鈕,勾選“分析工具庫”選項,單擊“確定”按鈕,如圖9.8所示。2026/1/14199.1.3回歸直線法步驟3：選擇回歸分析方法。單擊“數(shù)據(jù)”選項卡,選擇“數(shù)據(jù)分析”功能,在彈出的“數(shù)據(jù)分析”對話框中選擇“回歸”,單擊“確定”按鈕,如圖9.9和9.10所示。2026/1/14209.1.3回歸直線法步驟4：設(shè)置因變量和自變量計算區(qū)域。在“回歸”對話框的“Y值輸入?yún)^(qū)域”中,輸入因變量指標(biāo),資金占用額“$B$4:$F$4”;在“X值輸入?yún)^(qū)域”中,輸入自變量指標(biāo),銷售額“$B$3:$F$3”;在“輸出區(qū)域”中,輸入“$A$8”,2026/1/14219.1.3回歸直線法

2026/1/14229.1.3回歸直線法

2026/1/14239.1.4高低點法2.知識鏈接MAX函數(shù)MIN函數(shù)INDEX函數(shù)MATCH函數(shù)2026/1/14249.1.4高低點法3.例題

B傳媒公司2020—2024年的產(chǎn)量與資金占用數(shù)據(jù)如表9.3所示,該集團(tuán)2025年的計劃產(chǎn)量為120件。要求:利用高低點法預(yù)測2025年的資金需要量。2026/1/14259.1.4高低點法年度產(chǎn)量x(件)資金占用量y(萬元)2020601212021701322022801402023901472024100151步驟1：根據(jù)案例資料的已知條件輸入相關(guān)信息，并建立計算結(jié)果區(qū)域。2026/1/14269.1.4高低點法步驟2：使用MAX函數(shù)找出高點。選擇F3單元格，輸入公式“=MAX(B3:B7)”，選擇F4單元格輸入公式=“MIN（C3:C7）”2026/1/14279.1.4高低點法步驟3:使用INDEX函數(shù)找出高點對應(yīng)的占用資金。單元格F3中輸入公式“=INDEX(C3:C7,MATCH(F3,B3:B7))”,F4中輸入“=INDEX(C3:C7,MATCH(F4,B3:B7))”2026/1/14289.1.4高低點法

2026/1/14299.1.4高低點法9.2Excel在資本成本計算中的應(yīng)用2026/1/14309.2.1個別資本成本9.2.2權(quán)益資本成本9.2.1個別資本成本債務(wù)資本成本是指借款和發(fā)行債券的成本,包括借款或債券的利息和籌資費用。按照是否考慮貨幣的時間價值分為：不考慮貨幣時間價值的，和考慮貨幣時間價值的。2026/1/14319.2.1個別資本成本【例9.5】A集團(tuán)取得5年長期借款200萬元，年利率10%，每年付息一次，到期一次還本，借款費用率0.2%，企業(yè)所得稅稅率20%，該項借款的資本成本率為多少？2026/1/14329.2.1個別資本成本【操作步驟】：步驟1：打開“第9章籌資管理.xlsx”工作簿，超乳一張新的工作表，將其重命名為“個別資本成本”。步驟2：根據(jù)公司的實際情況將相關(guān)信息輸入，并建立計算結(jié)果區(qū)域。步驟3：在B7單元格中輸入公式“=B3*(1-B6)/(1-B5)”,最終結(jié)果為7.52%。2026/1/14339.2.1個別資本成本【拓展】假如每年6月31日需要向銀行繳納利息，怎么讓Excel像鬧鐘一樣提醒你，不要忘記繳納利息？這就用到了Excel到期提醒日的功能。2026/1/14349.2.1個別資本成本2.知識鏈接IRR函數(shù)語法：IRR(values,[guess])功能：計算投資項目或者債券等的內(nèi)涵報酬率或成本率，使得投資或債券資本成本等于0YIELD函數(shù)語法：YIELD(settlement,maturity,rate,pr,redemption,frequency,[basis])功能：返回定期支付利息的債券的收益。函數(shù)YIELD用于計算債券收益率。2026/1/14359.2.1個別資本成本考慮貨幣時間價值【例題】A集團(tuán)取得5年期長期借款2000萬元,年利率10%,每年付息一次,到期一次還本,籌資費用率為0.5%,所得稅稅率為25%。要求:如果不考慮資本時間價值,計算該公司長期借款的資金成本;如果考慮資本時間價值,計算該公司長期借款的資金成本。2026/1/14369.2.1個別資本成本【操作步驟】：(1)利用Excel的單變量求解的方法步驟1：設(shè)計如圖所示的Excel計算模型,并定義F3單元格格式為百分比2026/1/14379.2.1個別資本成本步驟2：在G3單元格中輸入公式“G3=A3*(1-D3)-PV(F3,C3,-A3*B3*(1-E3),-A3)”。其中,A3*(1-D3)計算的是資本的流入量,PV(F3,C3,-A3*B3*(1-E3),-A3)計算的是資本的流出量。2026/1/14389.2.1個別資本成本步驟3：添加規(guī)劃求解項，單擊文件-選項-加載項-規(guī)劃求解-規(guī)劃求解加載項，勾選之后點擊確定，找到Excel中的“數(shù)據(jù)”選項卡下的“分析”，執(zhí)行規(guī)劃求解。2026/1/14399.2.1個別資本成本步驟4：

選擇“規(guī)劃求解”，利用現(xiàn)金流入值=未來現(xiàn)金流出利用資本成本率進(jìn)行折現(xiàn)的現(xiàn)值，也就是當(dāng)“G3”單元格等于0的時候的資本成本率，就是長期借款資本成本率。所以“設(shè)置目標(biāo)”為“$G$3”單元格，期望現(xiàn)金流入現(xiàn)值與流出現(xiàn)值的目標(biāo)值為“0”，通過改變資本成本“$F$3”來實現(xiàn)。再單擊“確定”按鈕,則求解得到長期借款的資本成本,如圖9.23所示，點擊“保留規(guī)劃求解的解”2026/1/14409.2.1個別資本成本2026/1/14419.2.1個別資本成本(2)利用Excel的IRR函數(shù)的方法IRR函數(shù)法存在兩種方法：一、直接利用公式；二、利用滾動調(diào)節(jié)條。這兩種方法的本質(zhì)都是調(diào)節(jié)利率使得現(xiàn)金流入等于現(xiàn)金流出。

方法一：【操作步驟】：步驟1：設(shè)計如圖9.25所示的Excel計算模型,并定義G2單元格格式為百分比。2026/1/14429.2.1個別資本成本步驟2：在A6:

F6

單元格區(qū)域分別輸入計算公式。A6單元格為籌資凈額(資本流入),輸入公式“A6=A3*(1-D3)”。B6:F6

單元格區(qū)域分別是各年的稅后利息支出額和還本額,即資本的流出量。第1-4年為各年支出的稅后利息,第5年

除了稅后利息支出外,還包括一次性還本額。在B6單元格中輸入公式“=-$A$3*$B$3*(1-$E$3)”,接著利用填充柄將公式橫向填充至E

6單元格。在F6單元格中輸入公式“=-$A$3*$B$3*(1-$E$3)-A3”。

2026/1/14439.2.1個別資本成本步驟3：最后利用IRR函數(shù)計算長期借款的資本成本。在F

2單元格中輸入公式“=IRR(A6:F6)”,結(jié)果為7.62%。

2026/1/14449.2.1個別資本成本方法二：【操作步驟】：步驟1：顯示“開發(fā)工具”，單擊“文件”按鈕，在下拉菜單中

單擊“選項”按鈕，在對話框中

單擊“自定義功能區(qū)”按鈕，在最右邊一欄

可以看到“開發(fā)工具”，單擊“確定”按鈕。2026/1/14459.2.1個別資本成本步驟2：在圖9.29中點擊“開發(fā)工具”下的“插入”工具按鈕，插入“滾動條窗體控件”2026/1/14469.2.1個別資本成本步驟3：將“滾動條窗體控件”移到年利率B2單元格附近，單擊鼠標(biāo)右鍵，選擇設(shè)置控鍵格式，在控制標(biāo)簽頁進(jìn)行設(shè)置。2026/1/14479.2.1個別資本成本步驟4：

在單元格B3中，輸入公式B4/100,格式設(shè)置為“百分比”（ctrl+1），保留兩位小數(shù)。這樣就可以通過滾動條調(diào)節(jié)利率來查看資本成本的變化。如圖9.32所示：步驟5：

同理設(shè)置其他單元格格式2026/1/14489.2.2權(quán)益資本成本

企業(yè)總資本往往是由多種籌資方式形成的,不同籌資方式的資本成本存在差異。企業(yè)為了更好地進(jìn)行籌資和投資決策,需計算綜合資本成本(也稱加權(quán)平均資本成本)。綜合資本成本是對各種個別資本成本按所占資本比重進(jìn)行加權(quán)平均所得到的,故稱為加權(quán)平均資本成本。2026/1/14499.2.2權(quán)益資本成本例題：A集團(tuán)你籌資2，000萬元，其中銀行借款300萬，籌資費用率為2%；債券200萬，籌資費用率為3%；優(yōu)先股300萬，籌資費用率為5%；普通股700萬，籌資費用率為6%，普通股發(fā)行價格為10萬元；留存收益500萬，所得稅稅率為25%，企業(yè)通過調(diào)節(jié)利率來控制其資本成本。要求：改變銀行存款、債券、優(yōu)先股、普通股、留存收益利率對綜合資本成本的影響？【操作步驟】：步驟1：建立“綜合資本成本”工作表，如圖9.35所示。2026/1/14509.2.2權(quán)益資本成本步驟2：顯示“開發(fā)工具”，單擊“文件”按鈕，在下拉菜單里面單擊“選項”按鈕，在對話框里單擊“自定義功能區(qū)”按鈕，在右邊的兩欄里面的右邊一欄可以看到“開發(fā)工具”，單擊“確定”按鈕。（詳情參照例題9.6）步驟3：點擊“開發(fā)工具”下的“插入”工具按鈕，插入“滾動條窗體控件”。步驟4：

將“滾動條窗體控件”移到E3單元格中，單擊鼠標(biāo)右鍵，選擇設(shè)置控鍵格式，在控制標(biāo)簽頁進(jìn)行設(shè)置，或單擊“開發(fā)工具”選項卡中的“屬性”，也會彈出“設(shè)置控件格式”2026/1/14519.2.2權(quán)益資本成本步驟5：

在控制頁標(biāo)簽中設(shè)置2026/1/14529.2.2權(quán)益資本成本步驟6：

使用同樣的方法，將“調(diào)節(jié)值”列的其他單元格輸入。步驟7：

在單元格D3中，輸入公式E3/100，格式設(shè)置為“百分比”。步驟8：

同理，設(shè)置其他單元數(shù)值2026/1/14539.2.2