第四章 Excel在財務預測中的的應用1

1、2022-3-27杜茂寶第四章 Excel在財務預測中的應用2022-3-27杜茂寶-2-第一節(jié) 財務預測概述 財務預測，是指對企業(yè)未來的收入、成本、利潤、現(xiàn)金流量及融資需求等財務指標所作的估計和推算。 財務預測有助于改善企業(yè)的投資決策。雖然投資決策是決定籌資與否和籌資多少的重要因素，但是根據(jù)銷售前景估計出融資需求，并不一定能夠得到全部滿足。這時，就需要根據(jù)可能籌措到的資金來安排銷售增長以及有關(guān)的投資項目，使投資決策建立在可行的基礎(chǔ)上。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-3- 一、財務預測的一般步驟 1. 銷售量預測 銷售量預測是指根據(jù)市場調(diào)查所得到的有關(guān)資料，通過對

2、有關(guān)因素的分析研究，預計和推算特定產(chǎn)品在未來一定時期內(nèi)的市場銷售量水平及變化趨勢，進而預測企業(yè)產(chǎn)品未來銷售量的過程。 2. 估計收入、費用和利潤 收入和費用與銷售量也存在一定的函數(shù)關(guān)系，因此，可以根據(jù)銷售數(shù)據(jù)估計收入和費用，并確定凈利潤。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-4- 3. 估計需要的資產(chǎn) 資產(chǎn)通常是銷售收入的函數(shù)，根據(jù)歷史數(shù)據(jù)可以分析出二者之間的函數(shù)關(guān)系。根據(jù)預計銷售收入和資產(chǎn)與銷售之間的函數(shù)關(guān)系，可以預測所需要資產(chǎn)的總量。某些流動負債也是銷售收入的函數(shù)，相應的可以預測負債的自發(fā)增長額，這種增長可以減少企業(yè)外部融資的數(shù)額。 4. 估計所需融資 根據(jù)預計資產(chǎn)

3、總量，減去已有的資金來源、負債的自發(fā)增長和內(nèi)部提供的留存收益，可得出所需的外部融資數(shù)額。 第四章 Excel在財務預測中的應用2022-3-27杜茂寶-5- 二、財務預測的分析方法 定量預測方法 1、移動平均法移動平均法一次移動平均法二次移動平均法2、指數(shù)平滑法第四章 Excel在財務預測中的應用2022-3-27杜茂寶-6- 3、回歸預測法 財務預測中常用的幾種回歸模型 一元線性回歸模型bmxy 多元線性回歸bxmxmxmynn2211 式中，y為因變量；x是自變量；m1、m2、mn、b分別為預測模型的待估計參數(shù)第四章 Excel在財務預測中的應用2022-3-27杜茂寶-7- 一元非線性回

4、歸模型 xbmy 多元指數(shù)模型321321xxxmmmby 式中，y為因變量；x是自變量；m1、m2、mn、b分別為預測模型的待估計參數(shù) 定型預測方法第四章 Excel在財務預測中的應用2022-3-27杜茂寶-8-第二節(jié) Excel中的有關(guān)預測函數(shù)及其應用 Excel提供的預測函數(shù)主要有LINEST函數(shù)、LOGEST函數(shù)、TREND函數(shù)、GROWH函數(shù)、SLOPE函數(shù)、INTERCEPT函數(shù)。它們所使用的函數(shù)基本相同： Known_ys：因變量y的觀測值 集合； Known_xs：自變量x的觀測值集合； Const：指明是否使常數(shù)b為0(線性模型)或為1(指數(shù)模型)，如Const為True或

5、忽略，b將被正常計算。如Const為False，b將被設為0(線性模型)或設為1(指數(shù)模型) ；第四章 Excel在財務預測中的應用2022-3-27杜茂寶-9- Stats：邏輯值，指明是否返回附加回歸統(tǒng)計值。如果Stats為True，則函數(shù)返回附加回歸統(tǒng)計值(為數(shù)組)。如果Stats為False或省略 ，函數(shù)只返回預測模型的待估參數(shù)。 附加回歸統(tǒng)計值返回的順序見表4-1 表4-1中各參數(shù)說明見表4-2。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-10-m mn nm mn-1n-1m m2 2m m1 1b bSESEn nSESEn-1n-1SESE2 2SESE1 1

6、SESEb bR R2 2SESEy yF F D Df fSSSSregregSSSSresidresid回歸統(tǒng)計值返回的順序列號 1 2 n-1 n n+1 12345行號表4-1第四章 Excel在財務預測中的應用2022-3-27杜茂寶-11-表4-2第四章 Excel在財務預測中的應用2022-3-27杜茂寶-12- 一、LINEST( )函數(shù)用于直線回歸分析 LINEST函數(shù)的功能是使用最小二乘法計算對已知數(shù)據(jù)最佳線性擬合的直線方程，并返回描述此線性模型的數(shù)組。 語法： LINEST(known_ys,known_xs,const,stats) 一元線性回歸分析 LINEST函數(shù)可

7、用于一元線性回歸分析，也可用于多元線性回歸分析以及時間數(shù)列的回歸分析。 設一元回歸方程為：y=mx+b,則參數(shù)和相關(guān)系數(shù)的求解過程為：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-13- 如:m參數(shù)是數(shù)組中第1行、第1列元素，因此，m參數(shù)的計算公式為： INDEX(LINEST(Y變量單元區(qū)域,X變量單元區(qū)域,TRUE,TRUE),1,1) b參數(shù)是數(shù)組中第1行、第2列元素，因此，b參數(shù)的計算公式為： INDEX(LINEST(Y變量單元區(qū)域,X變量單元區(qū)域,TRUE,TRUE),1,2) R2參數(shù)是數(shù)組中第3行、第1列元素，計算公式為： INDEX(LINEST(Y變量單元區(qū)

8、域,X變量單元區(qū)域,TRUE,TRUE),3,1)第四章 Excel在財務預測中的應用2022-3-27杜茂寶-14- 例41：某企業(yè)19月份的總成本與人工小時及機器工時的數(shù)據(jù)見表43。假設總成本與人工小時之間存在線性關(guān)系，如第10、11、12各期的人工小時為308、335、363。用一元線性回歸分析方程預測第10、11、12期的總成本。求解過程第四章 Excel在財務預測中的應用2022-3-27杜茂寶-15- 第一步：設置如圖4-1所示的多元預測模型結(jié)構(gòu)，設預測模型為：Y=m1x+b圖4-1第四章 Excel在財務預測中的應用2022-3-27杜茂寶-16- 第二步：在單元格I6中輸入公式

9、“=INDEX(LINEST(D6:D14,E6:E14,TRUE),1,1)”，計算參數(shù)m。 第三步：在單元格J6中輸入公式“=INDEX(LINEST(D6:D14,E4:E16+E4:E14,TRUE),1,2)”，計算參數(shù)b。 第四步：在單元格J9中輸入公式“=INDEX(LINEST(D6:D14,E6:E14,TRUE),3,1)”，計算相關(guān)系數(shù)R2。 第五步：在單元格J10中輸入公式“=INDEX(LINEST(D6:D14,E6:E14,TRUE),3,2)”，計算預測標準差。 第六步：在單元格J14中輸入公式“=I6*J13+J6”，計算y值。第四章 Excel在財務預測中的

10、應用2022-3-27杜茂寶-17- 例42：根據(jù)表4-4 的數(shù)據(jù)，設計二元回歸方程預總成本。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-18- 第一步：設置如圖4-1所示的多元預測模型結(jié)構(gòu)圖4-2第四章 Excel在財務預測中的應用2022-3-27杜茂寶-19- 第二步：在單元格I5中輸入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),1,1)”，計算參數(shù)m2。 第三步：在單元格J5中輸入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),1,2)”，計算參數(shù)m1。 第四步：在單元格K5中輸入公式“=INDEX(LINEST

11、(D5:D13,E5:F13,TRUE),1,3)”，計算參數(shù)b。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-20- 第五步：在單元格K8中輸入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),3,1)”，計算相關(guān)系數(shù)R2。 第六步：在單元格K9中輸入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),3,2)”，計算預測標準差。 第七步：在單元格K14中輸入公式“=J5*K12+I5*K13+K5”，計算y值。 例4-2的設計過程：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-21- 二、LOGEST函數(shù) LOG

12、EST函數(shù)的功能是在回歸分析中，計算最符合數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該指數(shù)模型的數(shù)組。由于這個函數(shù)返回一個數(shù)組，必須以數(shù)組公式輸入。 LOGEST函數(shù)的公式為： =LOGEST(known_y,s,known_x,s,const,stats) 例43：某企業(yè)12個月某產(chǎn)品的生產(chǎn)量(x)與生產(chǎn)成本(Y)的有關(guān)數(shù)據(jù)如表43所示，假設它們之間有如下關(guān)系：Y=bmx。計算該回歸函數(shù)。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-22-月份月份生產(chǎn)量( 萬件)x生產(chǎn)量( 萬件)x生產(chǎn)成本( 元/ 件)生產(chǎn)成本( 元/ 件)1 110.210.2564.3564.32 210.3

13、410.34536.8536.83 310.5510.55518.3518.34 410.9210.92487.6487.65 511.1511.15473.5473.56 611.4311.43456.1456.17 711.5411.54446.4446.48 812.0612.06421.7421.79 912.4512.45401.7401.7101012.9312.93389.6389.6111113.3413.34391.2391.2121213.2513.25378.2378.2生產(chǎn)函數(shù)模型生產(chǎn)函數(shù)模型表4-5第四章 Excel在財務預測中的應用2022-3-27杜茂寶-23-第

14、一步：設置如圖4-3所示的多元預測模型結(jié)構(gòu)圖4-3 求解第四章 Excel在財務預測中的應用2022-3-27杜茂寶-24- 第二步：選取單元格區(qū)域E3:F7，輸入公式“=LOGEST(D3:D14,C3:C14,TRUE,TRUE)”，計算參數(shù)。 第三步：按數(shù)組生成組合鍵“CTRL+SHIFT+ENTER”，生成參數(shù)數(shù)組，見圖4-4。各參數(shù)的位置同線性回歸參數(shù)數(shù)表。圖4-4第四章 Excel在財務預測中的應用2022-3-27杜茂寶-25-第三節(jié) 銷售預測 一、銷售預測的基本方法 進行銷售預測的方法很多，常用的方法包括以下幾種。 時間序列預測 時間序列預測法，是將觀察或記錄的一些歷史數(shù)據(jù)，按

15、時間的先后排列成數(shù)據(jù)系列，進行統(tǒng)計分析，找出過去長期的銷售量或銷售額的增減變化趨勢，在根據(jù)此變化趨勢分析的結(jié)果，預測未來時期的銷售量或銷售額。如簡單平均法、移動平均法、指數(shù)平滑法，或以時間為自變量的回歸分析法等。 第四章 Excel在財務預測中的應用2022-3-27杜茂寶-26- 因果關(guān)系預測法 因果分析法，是指利用有關(guān)因素與產(chǎn)品銷售量或銷售額之間故有因果關(guān)系，通過建立一定的數(shù)學模型預測企業(yè)未來的產(chǎn)品銷售水平的一種方法。 二、銷售預測模型及其應用 一元線性（非線性）回歸預測模型 例44，根據(jù)表44中所給出的資料建立一元線性（非線性）回歸預測模型，并計算未來第14期的預測值。第四章 Excel

16、在財務預測中的應用2022-3-27杜茂寶-27- 建立過程： 第一步：建立預測模型，如圖45所示。 圖4-5第四章 Excel在財務預測中的應用2022-3-27杜茂寶-28- 第二步： 設置回歸模型選擇控件，控件的數(shù)據(jù)區(qū)域為$A$8:$A$9，單元各連接為B9，下拉顯示項為2。 第三步：在C8中輸入公式“=IF(B8=1,Y=m*x+b,Y=b*mx)” 第四步：將單元格區(qū)域B4:M4定義名稱為“影響因素序列”，將單元格區(qū)域B5:M5定義名稱為“銷售序列” 第五步：在單元格E9中輸入公式“IF(B8=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,2),IN

17、DEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),1,2) ”，計算系數(shù)m。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-29- 第六步：在單元格F9中輸入公式 “=IF(B8=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,1),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),1,1)”，計算系數(shù)B。 第七步： 在單元格G9中輸入公式“=IF(D8=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),3,1),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE)

18、,3,1)”，計算相關(guān)系R2。 第八步：在單元格J9:M9中輸入公式“=IF(B8=1,E9+F9*J8:M8,E9*F9J8:M8)”，（數(shù)組公式輸入），計算未來第14器的預測值。 例44第四章 Excel在財務預測中的應用2022-3-27杜茂寶-30- 多元線性回歸預測模型 例45，根據(jù)表45中所給的資料建立多元線性回歸預測模型。建立過程同例44，結(jié)果見圖4-6。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-31-第一步：建立銷售預測模型，如圖4-6所示。圖4-6第四章 Excel在財務預測中的應用2022-3-27杜茂寶-32- 第二步：選取單元格B3:M4，單擊編輯欄

19、中的名稱框，將影響因素所在的單元格區(qū)域B3:M4定義為“影響因素序列”；用同樣的方法，將銷售額所在的單元格區(qū)域B5:M5定義為“銷售序列”。 第三步：在單元格D9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,3)”，計算系數(shù)b。 第四步：在單元格E9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,2)” ，計算系數(shù) m1。 第四章 Excel在財務預測中的應用2022-3-27杜茂寶-33- 第五步：在單元格F9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,1)”

20、，計算系數(shù) m2。 第六步：在單元格G9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),3,1)” ，計算相關(guān)系數(shù)R2 。 第七步：在單元格J9:M9中輸入公式“=D9+E9*J7:M7+F9*J8:M8”，按數(shù)組生成鍵，計算未來1-4期的預測值。 在J7:M7中輸入未來4期的影響因素預測數(shù)值后，即可得到回歸預測模型的預測值，如圖4-6所示。 例4-5預測模型設計過程：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-34-第四節(jié) 成本預測 一、成本預測方法 成本是指企業(yè)生產(chǎn)和銷售產(chǎn)品所花費的全部費用。成本可以按很多不同的標準進行分類。如按成本

21、性態(tài)可將總成本分為變動成本、固定成本和混合成本三類。 成本預測是根據(jù)企業(yè)未來的發(fā)展目標和現(xiàn)實條件，參考其他資料，利用專門的方法對企業(yè)未來成本水平及其變動趨勢進行估算和預測。成本預測的方法主要有：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-35- 1、歷史成本法 這種方法主要是根據(jù)成本的歷史資料來預測未來的成本水平，常用的方法有高低點法和回歸分析法。 2、目標利潤推算法 這種方法主要是根據(jù)有關(guān)的經(jīng)濟預測(銷售量、銷售價格)和企業(yè)的目標利潤等數(shù)據(jù)，來反算成本的一種方法。 3、因素分析法 這種方法是根據(jù)預期影響成本的各因素的變化來預測未來成本，它需要企業(yè)擁有較為詳細的資料。 第四章

22、 Excel在財務預測中的應用2022-3-27杜茂寶-36- 4、比例推算法 這種方法是利用企業(yè)的生產(chǎn)消耗與企業(yè)有關(guān)重大經(jīng)營成果指標之間依存關(guān)系，按被確認的報告年度成本與這些指標的比例關(guān)系推算預測期的成本水平。這種方法這是一種大概的推算方法。 上述的成本預測方法中，回歸分析法較為科學也比較常用。下面重要介紹在Excel上利用回歸分析法進行成本預測的具體方法。 二、成本預測模型 利用回歸分析法進行成本預測首先需要建立回歸分析模型。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-37- 成本預測模型的數(shù)學表達式為： 一元一次模型（線性模型） 銷售成本固定成本單位變動成本銷售量 一元

23、二次模型（非線性） 銷售成本固定成本單位變動成本銷售量 混合成本銷售量平方 在Excel上建立銷售成本的回歸分析模型 例46，建立一元一次（二次）成本預測模型。 相關(guān)資料見表48。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-38- 第一步：建立銷售預測模型，如圖4-7所示。假設已有過去12期的歷史數(shù)據(jù)。 第四章 Excel在財務預測中的應用2022-3-27杜茂寶-39-圖4-7第四章 Excel在財務預測中的應用2022-3-27杜茂寶-40- 第二步：建立一元、二元線性模型選擇控件，控件的數(shù)據(jù)源區(qū)域為$A$8:$A$9，單元格連接為$B$8。 第三步：在單元格B4:M4中

24、輸入計算公式“=B3:M32”，生成數(shù)組，以準備一元二次回歸分析。 第四步：在單元格E9中輸入公式“=IF($B$8=1,INDEX(LINEST($B$5:$M$5,$B$3:$M$3,TRUE,TRUE),1,2),INDEX(LINEST($B$5:$M$5,$B$3:$M$4,TRUE,TRUE),1,3)”，計算參數(shù)b。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-41- 第五步：在單元格F9中輸入公式“=IF($B$8=1,INDEX(LINEST($B$5:$M$5,$B$3:$M$3,TRUE,TRUE),1,1),INDEX(LINEST($B$5:$M$5,

25、$B$3:$M$4,TRUE,TRUE),1,2)”，計算參數(shù)m1。 第六步：在單元格F9中輸入公式“=IF($B$8=1,0,INDEX(LINEST($B$5:$M$5,$B$3:$M$4,TRUE,TRUE),1,1)”，計算參數(shù)m2。 第七步：在單元格G9中輸入公式“=IF($B$8=1,INDEX(LINEST($B$5:$M$5,$B$3:$M$3,TRUE,TRUE),3,1),INDEX(LINEST($B$5:$M$5,$B$3:$M$4,TRUE,TRUE),3,1)”，計算相關(guān)系數(shù)R2。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-42- 第八步：在單元格

26、K9:M9中輸入公式“=E9+F9*K8:M8+G9*K8:M82”，按數(shù)組生成鍵，預測未來1-3期的預測值。 企業(yè)可根據(jù)歷史數(shù)據(jù)選擇所需要的預測模型，進行成本預測。 例4-6的設計過程：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-43-第五節(jié) 利潤預測 利潤預測是指在銷售預測的基礎(chǔ)上，根據(jù)企業(yè)未來發(fā)展和其他相關(guān)資料，預計、推測、或估算未來應該達到和希望實現(xiàn)的利潤水平及其變動趨勢的過程。一般可在對成本、業(yè)務量、利潤之間的關(guān)系進行研究的基礎(chǔ)上作出利潤預測，這種預測方法稱為本量力分析法。 一、確定性條件下單品種利潤敏感性分析模型 敏感性分析是一種定量分析方法，它研究當制約利潤的有

27、關(guān)因素發(fā)生某種變化時對利潤所產(chǎn)生的影響，這對于利潤預測分析，特別是對目標利潤預測有著十分積極的指導意義。 在企業(yè)只生產(chǎn)單品種產(chǎn)品，且產(chǎn)銷平衡的條件下，本量力之間的基本關(guān)系為：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-44- 利潤銷售量(單價單位變動成本)-固定成本 在進行敏感性分析時，假定條件如下：利潤只受銷售量、產(chǎn)品單價、單位變動成本和固定成本影響；上述各因素變動均不會影響其它因素的變動。 例47，根據(jù)圖表48所給出的資料建立確定性條件下單品種利潤敏感性分析模型。項目項目數(shù)值數(shù)值變化后數(shù)據(jù)變化后數(shù)據(jù)變動百分比變動百分比銷售量10001100.0010.0%60產(chǎn)品單價10

28、0110.0010.0%60單位變動成本7063.00-10.0%40固定成本50004500.00-10.0%40變動百分比選擇滾動條變動百分比選擇滾動條基基 本本 數(shù)數(shù) 據(jù)據(jù)圖4-8第四章 Excel在財務預測中的應用2022-3-27杜茂寶-45-預計利潤變動后利潤利潤變動額變動幅度25000472002220088.80%項目項目變動前利潤變動前利潤 因素變動百分比因素變動百分比 變動后利潤變動后利潤 利潤變動額利潤變動額 變動幅度變動幅度銷售量2500010.00%28000.003000.0012.00%產(chǎn)品單價2500010.00%35000.0010000.0040.00%單位

29、變動成本25000-10.00%32000.007000.0028.00%固定成本25000-10.00%25500.00500.002.00%單因素變動對利潤的影響單因素變動對利潤的影響多因素變動對利潤的綜合影響多因素變動對利潤的綜合影響 創(chuàng)建過程： 1、首先設計銷售量的滾動條控件，設計過程參見第三章例34。要求銷售量的變動范圍為50。控件具體位置見模型。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-46- 2、影響利潤的其他因素的股東條設計過程同銷售量。 3、在單元格E4:E7中建立變動百分比與滾動條控件的聯(lián)系，即選取單元格E4:E7并輸入公式“F4：F710050”（數(shù)組

30、公式）。 4、選取單元格D4:D7并輸入變化后各項目數(shù)值的計算公式“C4:C7*（1E4:E7）（數(shù)組公式） 5、在單元格B10輸入預計利潤計算公式：“C4*(C5-C6)C7”，并復制到單元格C10中，在單元格D10輸入公式“C10B10”，在單元格E10輸入公式“D10/B10”。這樣，就得到了多因素變動對利潤的影響。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-47- 6、設計單因素變動影響分析表格 在C13:C16中輸入公式“B10”(數(shù)組輸入公式)，在單元格D13:D16中輸入公式“E4:47”，在單元格E13:E16中分別輸入銷售量、產(chǎn)品單價、單位變動成本和固定成本

31、單獨變動時對利潤的影響計算公式。在單元格F13:F16中輸入公式“E13:E16D13:D16”（數(shù)組公式）；在單元格G13:G16中輸入公式“F13:F16C13：C16”(數(shù)組公式)。 這樣利潤的敏感性分析模型就建立起來了。單擊各個影響因素滾動條的箭頭，改變其變動幅度，就可以很方便地了解各個因素變動對利潤的單獨影響程度以及綜合影響程度。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-48- 二、確定性條件下多品種利潤預測分析模型 例4-8：某企業(yè)同時生產(chǎn)銷售A、B、C三三種產(chǎn)品，產(chǎn)品有關(guān)資料如圖4-9。圖4-9第四章 Excel在財務預測中的應用2022-3-27杜茂寶-49

32、- 數(shù)學求解過程 設生產(chǎn)A、B、C產(chǎn)品的產(chǎn)量分別是X1、X2、X3，則有： 目標函數(shù)： MAX() =(14-10)X1+(20-16)X2+(45-30)X3-30000約束條件：5X1+4X2+4X3400000 3X1+4X2+5X3350000 4X1+3X2+4X3300000 0 X140000 0 X235000 0 X335000 X1、X2、X3為正數(shù)第四章 Excel在財務預測中的應用2022-3-27杜茂寶-50- 求解上模型的過程如下： 第一步：建立如圖4-10所示的模型。圖4-10第四章 Excel在財務預測中的應用2022-3-27杜茂寶-51- 第二步：將單元格C

33、15:E15,作為可變單元格，存放產(chǎn)品A、B、C的產(chǎn)銷量。 第三步：C16為目標單元格，輸入公式” =SUMPRODUCT(C15:E15,C4:E4-C5:E5)-C13”，計算利潤。 第四步：在單元格F7中輸入公式“=SUMPRODUCT($C$15:$E$15,C7:E7)”,然后將其復制到單元格F8和F9 ，分別計算三種產(chǎn)品在甲、乙、丙車間使用的工時合計。 第五步：單擊工具規(guī)劃求解在規(guī)劃求解參數(shù)對話框中進行設置，如圖4-1。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-52- 第六步：單擊求解按鈕，即得在滿足約束條件下企業(yè)實現(xiàn)最大利潤的最佳產(chǎn)品結(jié)構(gòu)為：產(chǎn)品A：16429

34、件，產(chǎn)品B：31429件，產(chǎn)品C：35000件，最大利潤為416428元，如圖4-10所示。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-53-第六節(jié) 資金需要量預測 一、資金需要的預測方法 資金需要量的預測方法有多種，這里重要介紹兩種：銷售百分比法和資金性態(tài)法。 銷售百分比法 由于銷售額是影響資金需要量的最重要的因素，因此，可通過分析資金各個項目與銷售收入總額之間的依存關(guān)系，按預期的銷售收入的增長情況來預測資金需要量，這就是銷售百分比法。 步驟： 第一步：確定敏感性項目與非敏感性項目 敏感性項目是指其金額隨銷售收入自動成正比例增減變動的項目，包括敏感性資產(chǎn)和負債項目。第四章

35、Excel在財務預測中的應用2022-3-27杜茂寶-54- 敏感性資產(chǎn)項目一般包括現(xiàn)金、應收帳款、存貨等，如企業(yè)生產(chǎn)能力沒有剩余，那么繼續(xù)增加銷售收入，就需要增加新的固定資產(chǎn)投資，此時固定資產(chǎn)也成為敏感性資產(chǎn)。 敏感性負債項目一般包括應付帳款、應交稅金等。長期性負債一般為非敏感性項目。 非敏感性項目是指其金額不隨銷售收入自動成正比例變動的項目。 第二步：對于敏感性項目，計算其基期的金額占基期銷售收入的百分比，并分別計算敏感性項目干銷售收入百分比的合計數(shù)。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-55- 第三步：根據(jù)計劃期的銷售收入和銷售凈利率，結(jié)合計劃期支付股利的比率，確

36、定計劃期內(nèi)部留存收益的增加額。 第四步：根據(jù)銷售收入的增長額確定企業(yè)計劃期需要從外部籌措的資金需要量。計算公式為：1101MD1RSSLSSSAM 第四章 Excel在財務預測中的應用2022-3-27杜茂寶-56- 式中式中 M M：外部融資需要量；外部融資需要量； D D：股利支付率股利支付率 S S0 0：基期銷售額；基期銷售額； ( (A/S)A/S)：敏感資產(chǎn)占基期銷售額百分比；敏感資產(chǎn)占基期銷售額百分比； S S1 1：計劃銷售額；計劃銷售額； ( (L/S)L/S)：敏感負債占基期銷售額百分比；敏感負債占基期銷售額百分比； R R：銷售凈利率；銷售凈利率； M M1 1：計劃期零

37、星資金需求。計劃期零星資金需求。 銷售百分比法是資金需要量預測中比較簡單銷售百分比法是資金需要量預測中比較簡單的一種方法，適合于預測較短期的資金變動，但的一種方法，適合于預測較短期的資金變動，但無法對長期資金需要量進行預測。無法對長期資金需要量進行預測。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-57- 例48：某公司2004年度的銷售收入為2000000元，銷售凈利率5，股利支付率40。當年廠房設備利用率已達飽和狀態(tài)。該公司2004年末的簡略資產(chǎn)負債表如圖78所示。該公司預計2005年度的銷售收入將增至2800000元，銷售凈利率和股利支付率都和上年保持相同。2005年的零

38、星資金需要量為15000元。預測該公司2005年的外部資金需要量。 步驟：第四章 Excel在財務預測中的應用2022-3-27杜茂寶-58-項目項目是否敏感項目是否敏感項目占基期銷占基期銷售百分比售百分比項目項目是否敏感項目是否敏感項目占基期占基期銷售百銷售百分比分比貨幣資金貨幣資金是是2.00%2.00% 應付帳款應付帳款是是10.00%10.00%應收帳款應收帳款是是17.00%17.00% 應交稅金應交稅金是是5.00%5.00%存貨存貨是是20.00%20.00% 長期負債長期負債否否無意義無意義固定資產(chǎn)固定資產(chǎn)是是30.00%30.00% 普通股本普通股本否否無意義無意義無形資產(chǎn)無

39、形資產(chǎn)否否無意義無意義 留存收益留存收益否否無意義無意義合計合計69.00%69.00% 合計合計15.00%15.00%3630003630002005年資金需要量預測2005年資金需要量預測2005年資金需要量預測( 元)第四章 Excel在財務預測中的應用2022-3-27杜茂寶-59- 第一步：首先對有關(guān)項目是否敏感進行判斷，填入單元格C4:C8和單元格F4:F8。 第二步：在單元格D4中輸入公式“IF(C4=”是“,資料!B5/資料!$F$5,”無意義“)”，然后將其往下復制到單元格D8；并在單元格D9中輸入公式“=SUM(D4:D8)”； 第三步：在單元格G4中輸入公式“IF(F4=”是“,資料!D5/資料!$F$5,”無意義“)”，然后將其往下復制到單元格G8；并在單元格G9中輸入公式“=SUM(G4:G8)”； 第四步：在單元格D11中輸入計算公式“=D9*(資料!G5-資料!F5)-G9*(資料!G5-資料!F5)-資料!G5*資料!F6*(1-資料!F7)+資料!G8”，即得該公司2005年的外部資金需要量為363000元。第四章 Excel在財務預測中的應用2022-3-27杜茂寶-60- 二、資金性態(tài)法 資金性態(tài)法是指根據(jù)資金的變動通產(chǎn)銷量之間的依存關(guān)系預測未來資金需要的一種方法。資金性態(tài)法具體包括高