《Excel在財務(wù)管理中的應(yīng)用》課件 07EXCEL表在財務(wù)預(yù)測中的應(yīng)用

EXCEL表在財務(wù)預(yù)測中的應(yīng)用目錄Contents基本理論0102高低點法03

回歸分析法04

函數(shù)預(yù)測分析法05銷售百分比法01基本理論一、財務(wù)預(yù)算的基本內(nèi)容

財務(wù)預(yù)測作為財務(wù)管理的一個重要環(huán)節(jié)，其內(nèi)容包括資金需要量預(yù)測、成本費用預(yù)測、收入利潤預(yù)測等，其中，資金需求量預(yù)測是財務(wù)預(yù)測的重要內(nèi)容。資金需求量預(yù)測：1.企業(yè)所需要的資金，一部分來自企業(yè)內(nèi)部，另一部分通過外部融資取得。2.企業(yè)需要預(yù)先知道自身的財務(wù)需求，確定資金的需要量，提前安排融資計劃，以免影響資金周轉(zhuǎn)。

資金需要量的預(yù)測的方法有：銷售百分比法、高低點法和回歸分析法等。二、資金需要量預(yù)測的步驟（1）銷售預(yù)測

銷售預(yù)測是企業(yè)財務(wù)預(yù)測的起點；企業(yè)資金需要量預(yù)測以銷售預(yù)測為基礎(chǔ)。（2）估計需要的資產(chǎn)

根據(jù)銷售量，預(yù)測所需資產(chǎn)總量和負(fù)債總量。（3）估計收入、費用和留存收益

收入和費用與銷售額之間也存在一定的函數(shù)關(guān)系；凈利潤和股利支付率，共同決定了留存收益所能提供的資金數(shù)額。（4）估計需要追加的資金需要量，確定外部融資數(shù)額

預(yù)計資產(chǎn)總量減去負(fù)債總量和內(nèi)部提供的留存收益就是企業(yè)所需的外部融資數(shù)額。02

銷售百分比法三、資金需要量預(yù)測模型設(shè)計

（一）銷售百分比法

該方法有兩個基本假定；其一，假定某項目與銷售額存在比例關(guān)系，能夠計算且保持不變；其二，假定未來的銷售額能夠預(yù)測。

銷售百分比法一般通過編制預(yù)測資產(chǎn)負(fù)債表來確定企業(yè)的資金需用量。企業(yè)通過編制預(yù)測資產(chǎn)負(fù)債表，可以預(yù)測資產(chǎn)、負(fù)債及留存收益等有關(guān)項目的數(shù)額，從而確定企業(yè)需要從外部籌資的數(shù)額。預(yù)測的步驟為：（1）確定資產(chǎn)、負(fù)債中與銷售額敏感性項目；（2）計算每個項目占銷售收入的比重；（3）確定計劃期內(nèi)部留存收益的增加額；（4）確定企業(yè)計劃期需要從外部籌措的資金需要量。

計算公式為：M＝式中：M為外部融資需求量，A為隨銷售變化的資產(chǎn)；B為隨銷售變化的負(fù)債；S1為基期銷售額；S2為預(yù)測期銷售額；

為銷售變動額；P為銷售凈利潤率；E為保留盈余比率；

為變動資產(chǎn)占基期銷售額的百分比；

為變動負(fù)債占基期銷售額的百分比。1.敏感資產(chǎn)項目：如：現(xiàn)金、存貨、應(yīng)收賬款等；2.敏感負(fù)債項目：如：應(yīng)付賬款、預(yù)收賬款、預(yù)提費用、應(yīng)付職工薪酬等；3.非敏感項目：對外投資、固定資產(chǎn)、短期借款、非流動負(fù)債、實收資本等；4.固定資產(chǎn)項目既可能是非敏感性資產(chǎn)，也可能是敏感性資產(chǎn)【例1】

恒泰公司2018年末資產(chǎn)負(fù)債簡表如表。公司當(dāng)年的銷售額為1100萬元，銷售凈利潤率為10％，企業(yè)生產(chǎn)能力尚有剩余。預(yù)計2019年銷售額增長20％，獲利能力保持前一年水平，2019年預(yù)計股利分配率為60％。試預(yù)測2019年籌資需求。資產(chǎn)金額負(fù)債及所有者權(quán)益金額現(xiàn)金200應(yīng)付賬款150應(yīng)收賬款60應(yīng)付票據(jù)20存貨265短期借款9固定資產(chǎn)凈值475應(yīng)付債券0

實收資本600

資本公積140

留存收益81資產(chǎn)合計1000負(fù)債及所有者權(quán)益合計1000銷售百分比計算表資產(chǎn)金額負(fù)債及所有者權(quán)益金額現(xiàn)金18.18%應(yīng)付賬款13.64%應(yīng)收賬款5.45%應(yīng)付票據(jù)1.82%存貨24.09%短期借款N固定資產(chǎn)凈值N應(yīng)付債券N實收資本N資本公積N留存收益N資產(chǎn)合計47.73%負(fù)債及所有者權(quán)益合計15.45%課上練習(xí)1：清源公司2020年銷售收入800萬元，銷售凈利率15%，利潤分配率60%，資產(chǎn)負(fù)債表如表所示：該公司2021年預(yù)計銷售收入增長率20%，銷售凈利率與利潤分配政策與上年保持一致。預(yù)測該公司2021年外部籌資額。課堂小結(jié)1銷售百分比法1.計算敏感項目占銷售額的比重（資產(chǎn)和負(fù)債）；2.凈增加的資金需求量=資產(chǎn)增加的資金需求量-負(fù)債增加的資金需求量；3.留存收益增加額=凈利潤×（1-股利分配率%）4.預(yù)測凈利潤=目前銷售額S1×（1+增長率%）×銷售利潤率%5.外部資金需求量=凈增加的資金需求量-留存收益增加額03

高低點法（二）高低點法

高低點法是根據(jù)資金習(xí)性預(yù)測未來資金需要量的一種方法。從中選出銷量最高和最低的兩點坐標(biāo)建立線性預(yù)測方程，據(jù)此推算不變資金總額和單位變動資金從而預(yù)測資金需要量的一種方法。不變資金是指在一定的產(chǎn)銷量范圍內(nèi),不受產(chǎn)銷量變動的影響而保持固定不變的那部分資金。變動資金是指隨產(chǎn)銷量的變動而成比例變動的那部分資金。具體步驟：1.找出產(chǎn)銷量最高點和最低點，x1,x2y1,y2線性方程y=ax+b2.計算單位變動資金（斜率）

單位變動資金a=高低點資金占用量之差/高低點產(chǎn)量之差=(y1-y2)/(x1-x2)3.計算不變資金總額（截距）

不變資金總額b=高點占用資金-單位變動資金*高點產(chǎn)銷量=y1-ax14.預(yù)測某一時期的資金需求量；根據(jù)預(yù)測銷售量x3,計算資金需求量y3年底產(chǎn)銷量（萬件）資金占用量（萬元）20148972015121352016151682017201622018252052019預(yù)計產(chǎn)銷量（萬件）28

【例2】某公司2014年值2018年的產(chǎn)銷量和資金占用量及2019年預(yù)測的產(chǎn)銷量如表所示。要求建立一個高低點法預(yù)測該公司2019年資金需要量的模型。課上練習(xí)2：某企業(yè)產(chǎn)量和資金需要量的歷史資料如表所示。假設(shè)該企業(yè)2020年產(chǎn)量12萬件，要求用高低點法預(yù)測2020年的資金需求量。已知條件年度產(chǎn)銷量（萬件）資金占用量（萬元）20157.5800201687802017982020188.579020199.59002020預(yù)計產(chǎn)銷量（萬件）1204

回歸分析法（三）回歸分析法

回歸分析法是利用數(shù)理統(tǒng)計中回歸分析來確定兩種或兩種以上的變量間相互依賴的定量關(guān)系的一種統(tǒng)計分析方法?！纠?】

豐源公司2013年--2018年銷售量及對應(yīng)的資金需要量如表所示。預(yù)計2019年的銷售量12萬件，請預(yù)測2019年的資金需要量。年度銷售量X（萬件）資金需要量Y（萬元）2013910201489.5201578201691020171010.520181112項目銷售量X（萬件）資金需要量Y（萬元）2019年1212.70回歸方程：Y=0.9X+1.9

課上練習(xí)3：清源公司2020年下半年銷售量和資金需要量的資料如表所示，該公司預(yù)計2021年1月份銷售量150萬件，要求用回歸分析法預(yù)測2020年1月份資金需求量。清源公司2020年下半年銷售量與資金占用量月份789101112銷售量（萬件）1001059095110120資金占用量（萬元）140152138140148158項目銷售量X（萬件）資金需要量Y（萬元）2021年1月150178.64回歸方程：Y=0.69X+75.14課堂小結(jié)2一.銷售百分比法資金需求量=（資產(chǎn)%-負(fù)債%）*（銷售凈增加額）外部資金需求量=資金需求量-凈利潤*（1-股利分配率）二.高低點法y=ax+ba=(y1-y2)/(x1-x2)b=y1-ax1y3=ax3+b三.回歸分析法利用Excel表的回歸分析功能

求出y=ax+b方程，根據(jù)方程求解。05

函數(shù)預(yù)測分析法

（一）LINEST函數(shù)：對已知的數(shù)據(jù)進(jìn)行最佳直線擬合；其用途是使用最小二乘法對已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組，表達(dá)式y(tǒng)=mx+b。LINEST函數(shù)語法為：LINEST(known_y's，known_x's，const，stats)。Known_y's是表達(dá)式y(tǒng)=mx+b中已知的y值集合；Known_x's是關(guān)系表達(dá)式y(tǒng)=mx+b中已知的可選x值集合；Const為一邏輯值，指明是否強(qiáng)制使常數(shù)b為0，如果const為TRUE或省略，b將參與正常計算。如果const為FALSE，b將被設(shè)為0，并同時調(diào)整m值使得y=mx；Stats為一邏輯值，指明是否返回附加回歸統(tǒng)計值。如果stats為TRUE，函數(shù)LINEST返回附加回歸統(tǒng)計值。如果stats為FALSE或省略，函數(shù)LINEST只返回系數(shù)m和常數(shù)項b。四、函數(shù)預(yù)測分析法

利用EXCEL提供的LINEST()、TREND()、LOGEST()、GROWTH()函數(shù)用于預(yù)測分析。

【例4】某企業(yè)2014年-2018年銷售收入與資金需要量的關(guān)系如表所示，2019年預(yù)測銷售收入3000000元，資金需要量是多少元？年份20142015201620172018資金需要量Y12200001440000158000017200001800000銷售收入X18000002025000220000024400002610000（1）E3單元格插入函數(shù)LINEST()Known_y's：B4:F4

Known_x's：B5:F5Const：默認(rèn)Stats：默認(rèn)（2）確定，得出m=0.71（3）利用組合鍵求b:選中E8：F8，按F2，顯示函數(shù)參數(shù)，【ctrl+shift+enter】

b=-18558.87（4）表達(dá)式：y=0.71x-18558.87（5）預(yù)測當(dāng)收入為3000000時，資金需要量2108608.86元

（二）TREND函數(shù)構(gòu)造線性回歸直線方程函數(shù)名為：TREND(known_y's,known_x's,new_x's,const)函數(shù)功能返回一條線性回歸擬合線的值；即找到適合已知數(shù)組known_y's和known_x's的直線（用最小二乘法），并返回指定數(shù)值new_x's在直線上對應(yīng)的y值，表達(dá)式y(tǒng)=mx+b。函數(shù)語法：TREND(known_y's,known_x's,new_x's,const)known_y's：表示為已知y=mx+b中的y值集合。known_x's：表示為已知y=mx+b中可選的x值集合。new_x's：表示新的x值，需要通過TREND函數(shù)返回的對應(yīng)的y值。Const：表示邏輯值，指明是否將常數(shù)b強(qiáng)制為0。【例5】某公司2014年-2018年的收入和資金占用量如表所示。2019年預(yù)測銷售收入305萬元，要求使用TREND函數(shù)，預(yù)測其資金需求量是多少？已知條件（萬元）年度銷售收入資金需要量2019年預(yù)計銷售收入30520148562

201511685

2016175109

2017237128

2018291143

C11單元格插入函數(shù)TREND()known_y's：C4:C8

known_x's：B4:B8new_x's：C10Const：默認(rèn)計算結(jié)果2019年資金需要量（萬元）152.43

（三）LOGEST函數(shù)：回歸擬合曲線返回該曲線的值LOGEST函數(shù)：在回歸分析中，計算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。表達(dá)式：y=b*m^xLOGEST函數(shù)語法為：LOGEST(known_y's，known_x's，const，stats)。Known_y's表示為一組符合y=b*m^x函數(shù)關(guān)系式的y值的集合；Known_x's表示為一組符合y=b*m^x函數(shù)關(guān)系式的可選x值集合；Const為一邏輯值，指明是否強(qiáng)制使常數(shù)b為0，如果const為TRUE或省略，b將參與正常計算。如果const為FALSE，b將被設(shè)為0，并同時調(diào)整m值使得y=m^x；Stats為一邏輯值，指明是否返回附加回歸統(tǒng)計值。如果stats為TRUE，函數(shù)函數(shù)返回附加回歸統(tǒng)計值。如果stats為FALSE或省略，函數(shù)只返回系數(shù)m和常數(shù)項b?！纠?】某企業(yè)2014年-2018年銷售收入與資金需要量的關(guān)系如表所示，2019年銷售收入預(yù)測3000000元，請使用LOGEST()預(yù)測資金需要量是多少元？年份20142015201620172018資金需要量Y24500002650000285000032300003360000銷售收入X18000002025000220000024400002610000（1）輸入函數(shù)LOGEST()Known_y's：B4:F4Known_x's：B5:F5Const：默認(rèn)Stats：默認(rèn)（2）確定，得出m=1.0000004（3）利用組合鍵求b:選中E8：F8，按F2，顯示函數(shù)參數(shù)，【ctrl+shift+enter】b=1167400.15（4）表達(dá)式：y=1167400.15*1.0000004^x（5）E10=POWER(D11,C8)*F8（6）當(dāng)收入為3000000時，預(yù)測資金需要量3980706.17元（四）GROWTH根據(jù)現(xiàn)有的數(shù)據(jù)預(yù)測指數(shù)增長值函數(shù)名為：GROWTH(known_y's,known_x's,new_x's,const)函數(shù)用于給定數(shù)據(jù)預(yù)測指數(shù)增長值。根據(jù)現(xiàn)有的x值和y值，GROWTH函數(shù)返回一組新的x值和y值，可以使用GROWTH函數(shù)來擬合滿足現(xiàn)有的x值和y值的指數(shù)曲線。函數(shù)語法：GROWTH(known_y's,known_x's,new_x's,const)known_y's：表示滿足指數(shù)回歸擬合曲線的一組已知的y值。known_x's：表示滿足指數(shù)回歸擬合曲線的一組已知的x值。new_x's：表示一組新的x值，可以通過GROWTH函數(shù)返回的對應(yīng)的y值。Const：表示邏輯值，指明是否將系數(shù)b強(qiáng)制為1。如果const為TRUE或省略，b將參與正常計算。如果const為FALSE，b將被設(shè)為1?！纠?】某公司2019年1-9月份的資金需求量如表所示，預(yù)測10、11、12月份的資金需求量。月份資金需要量預(yù)測10、11、12月份資金需求量1128951013874.007622132871113882.105323133661213890.20775416899

513600

613697

715123

812956

913135

（1）在D4單元格輸入函數(shù)GROWTH()known_y's：B4:B12

known_x's：A4:A12new_x's：C4:C6Const：省略返回10月份的資金預(yù)測量13874（2）選中D4:D6,按F2，顯示函數(shù)參數(shù)。（3）按【ctrl+shift+enter】，返回11月份資金預(yù)測值13882，12月份的資金預(yù)測值13890。【例8】

達(dá)盟公司2020年銷售資料如表所示，已知該公司生產(chǎn)銷售趨于平穩(wěn)，預(yù)測2020年12月份的銷售額。要求分別利用LNEST()函數(shù)、TREND()函數(shù)、LOGEST()函數(shù)和GROWTH()函數(shù)預(yù)測。已知條件（萬