Excel在財務(wù)預測中的應用

60/60第4章Excel在財務(wù)預測中的應用

4.1財務(wù)預測概述

財務(wù)預測，是指對企業(yè)未來的收入、本錢、利潤、現(xiàn)金流量及融資需求等財務(wù)指標所作的估計和推測。財務(wù)預測是編制投資和融資方案的基礎(chǔ)，是公司制訂成長戰(zhàn)略的根本要素。稱職的財務(wù)管理人員應該能夠充分利用公司的有關(guān)信息資料，預測公司的財務(wù)需要并做出相應的安排。公司成長主要由銷售增長來決定，銷售增長需要相應的資產(chǎn)增長，如果企業(yè)已經(jīng)是滿負荷運轉(zhuǎn)，不僅流動資產(chǎn)、而且固定資產(chǎn)都要增長，而資產(chǎn)增長需要相應的融資增長。同時，企業(yè)進行對外投資和調(diào)整資本結(jié)構(gòu)，也需要籌措資金。企業(yè)所需要的這些資金，一局部來自企業(yè)內(nèi)部，另一局部通過外部融資取得。由于對外融資時，企業(yè)不但需要尋找資金提供者，而且還需做出還本付息的承諾或提供企業(yè)盈利前景等信息，使資金提供者確信其投資是平安的并可獲利，這個過程往往需要花費較長的時間。因此，企業(yè)需要預先知道自身的財務(wù)需求，確定資金的需要量，提前安排融資方案，以免影響資金周轉(zhuǎn)。財務(wù)預測有助于改善企業(yè)的投資決策。雖然投資是決定籌資與否和籌資多少的重要因素，但是根據(jù)銷售前景估計出的融資需求，并不一定能夠得到全部滿足。這時，就需要根據(jù)可能籌措到的資金來安排銷售增長以及有關(guān)的投資工程，使投資決策建立在可行的基礎(chǔ)上。財務(wù)預測一般按以下幾個步驟進行。1．銷售預測銷售預測是指根據(jù)市場調(diào)查所得到的有關(guān)資料，通過對有關(guān)因素的分析研究，預計和測算特定產(chǎn)品在未來一定時期內(nèi)的市場銷售量水平及變化趨勢，進而預測企業(yè)產(chǎn)品未來銷售量的過程。企業(yè)的一切財務(wù)需求都可以看作是因銷售引起的，銷售量的增減變化，將會引起庫存量、現(xiàn)金流量、應收與應付賬款以及公司其他資產(chǎn)和負債的變化。因此銷售預測在企業(yè)預測系統(tǒng)中處于先導地位，它對于指導利潤預測、本錢預測和資金預測，進行長短期決策，安排經(jīng)營方案，組織生產(chǎn)等都起著重要的作用。2．估計收入、費用和利潤收入和費用與銷售量之間也存在一定的函數(shù)關(guān)系，因此，可以根據(jù)銷售數(shù)據(jù)估計收入和費用，并確定凈利潤。凈利潤和股利支付率，共同決定了內(nèi)部留存收益所能提供的資金數(shù)額。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)總量，減去已有的資金來源、負債的自發(fā)增長和內(nèi)部提供的留存收益，可得出所需的外部融資數(shù)額。第4章Excel在財務(wù)預測中的應用

4.2財務(wù)預測的分析方法

預測分析的方法有很多種，企業(yè)應根據(jù)不同的需要選擇不同的預測方法。總的來說，預測分析方法可分為兩大類：定量預測法和定性預測法。4.2.1

定量預測法定量預測法是指在掌握與預測對象有關(guān)的各種要素的定量資料的基礎(chǔ)上，運用現(xiàn)代數(shù)學方法進行數(shù)據(jù)處理，從而建立起能夠反映有關(guān)變量之間關(guān)系的各類預測模型的方法。在財務(wù)預測中，經(jīng)常使用的定量預測法主要有以下幾種。4.2.1.1

移動平均法移動平均法是一種改進的算術(shù)平均法，是一種最簡單的自適應預測模型。它根據(jù)近期數(shù)據(jù)對預測值影響較大，而遠期數(shù)據(jù)對預測值影響較小的事實，把平均數(shù)逐期移動。移動期數(shù)的大小視具體情況而定，移動期數(shù)少，能快速地反映變化，但不能反映變化趨勢；移動期數(shù)多，能反映變化趨勢，但預測值帶有明顯的滯后偏差。常用的移動平均法主要有一次移動平均法和二次移動平均法。1．一次移動平均法一次移動平均法是根據(jù)時間序列，逐期移動，依次計算包含一定項數(shù)的時間序列平均數(shù)，形成一個平均時間數(shù)序列，并據(jù)此進行預測。預測模型為式中—第t+1期的預測值；、、…、—將被平均的n個觀測值；n—移動平均的項數(shù)，即移動期數(shù)。在實際預測中，可以多取幾個n數(shù)，并將得到的預測值與實際值進行比較，選用誤差最小的n值。2．二次移動平均法二次移動平均法是對時間序列計算一次移動平均數(shù)后，再對一次移動平均數(shù)序列進行一次移動平均運算。預測模型為。式中—二次移動平均數(shù)；—第t+1期的預測值，即。二次移動平均法解決了一次移動平均法只能預測下一期的局限性，它可以進行近、短期的預測。但它仍不能解決中長期的預測問題。4.2.1.2

指數(shù)平滑法指數(shù)平滑法實際上也是一種加權(quán)平均法，是一種改進的加權(quán)平均法，預測模型為式中

—平滑系數(shù)，0≤≤1。在指數(shù)平滑法中，確定適宜的值和初始值是非常重要的。越大，t期的實際值對新預測值的奉獻就越大；越小，t期的實際值對新預測值的奉獻就越小。一般情況下，可以取幾個不同的值進行預測，比較它們的預測誤差，選擇預測誤差最小的值。4.2.1.3

回歸分析預測法回歸分析預測法是通過研究兩組或兩組以上變量之間的關(guān)系，建立相應的回歸預測模型，對變量進行預測的一種預測方法。1．回歸分析預測法的根本程序進行回歸分析的步驟如下：（1）收集有關(guān)資料。將各種可能的影響因素的有關(guān)數(shù)據(jù)盡可能多地收集起來。（2）判斷趨勢。根據(jù)收集到的數(shù)據(jù)，判斷其變化趨勢，從而為建立相應的數(shù)學模型做準備。對于變量不多的問題，可以通過繪制散點圖來判斷變化趨勢。（3）建立預測數(shù)學模型。根據(jù)歷史數(shù)據(jù)的變化趨勢，選擇相應的描寫該問題的數(shù)學模型，并采用相關(guān)的計算技術(shù)來估計數(shù)學模型的參數(shù)。（4）相關(guān)檢驗。對建立的預測數(shù)學模型，必須進行有關(guān)的檢驗，主要是通過計算預測模型的相關(guān)系數(shù)、方差（或標準差）以及顯著性等指標，來判斷預測模型的準確性、是否需要修正、采用何種方法修正等。2．回歸模型建立的方法建立回歸模型的一般方法是采用最小二乘法，其原理如下：考慮m個自變量x1、x2、…、xm和因變量y的關(guān)系，現(xiàn)有n組觀測數(shù)據(jù)，不同xki（k=1,2,…,m；i=1,2,…,n）下的y的觀測值為yi，函數(shù)y=f(xk)的待估計參數(shù)為ak（k=1,2,…,m+1，這里，每個自變量有一個待估計系數(shù)，還有一個待估計常數(shù)，故有m+1個待估計參數(shù)），通過回歸預測模型得到不同xki下的預測值為，則有：殘差平方和SE：剩余標準差SS：相關(guān)系數(shù)R2：y為觀測值yi的平均值：那么，最小二乘法的原理就是尋找最優(yōu)的待估計參數(shù)ak，使殘差平方和最小。3．財務(wù)預測中常用的幾種回歸模型（1）一元線性回歸模型當只有兩個變量（一個自變量和一個因變量），并且它們之間存在線性關(guān)系時，可以用一元線性回歸模型來描述。一元線性回歸模型為式中a、b—回歸系數(shù)，其中a代表截距，b代表斜率。（2）一元非線性回歸模型當變量x和y之間的關(guān)系不能用線性關(guān)系來描述時，則需要建立一元非線性回歸模型。根據(jù)變量x和y之間的關(guān)系，一元非線性回歸模型常見的幾種情況有：對數(shù)模型：指數(shù)模型：乘冪模型：雙曲線模型：以上幾種一元非線性模型均可通過數(shù)學變換化成一元線性模型。（3）多元線性回歸模型當自變量有兩個或兩個以上，且因變量與這些自變量之間呈線性組合關(guān)系時，它們就構(gòu)成了多元線性回歸模型，模型形式為式中a、b1、b2、…、bm—估計參數(shù)；x1、x2、…、xm—自變量。（4）多元非線性回歸模型多元非線性回歸模型用來描述因變量與多個自變量之間呈非線性組合關(guān)系的情況。例如，柯柏—道格拉斯生產(chǎn)函數(shù)就是典型的多元非線性模型：式中：L和K分別為勞動力和固定資本；a、b、c為系數(shù)。4.2.1.4

模擬法在企業(yè)的實際經(jīng)濟活動中，各種經(jīng)濟參數(shù)往往并不是確定的，而是隨機變化的，比方產(chǎn)品的銷售量往往隨市場的變化而變化，在這種情況下，就需要對這些參數(shù)的不確定性進行分析，而對其預測也就需要采用與傳統(tǒng)確實定性分析不同的方法來進行。一般情況下，可以采用模擬法來解決不確定性情況下的財務(wù)預測問題，概率法、蒙特卡羅模擬方法就是較實用的方法。4.2.2

定性預測法定性預測法是由有關(guān)方面的專業(yè)人員或?qū)＜腋鶕?jù)自己的經(jīng)驗和知識，結(jié)合預測對象的特點進行綜合分析，對事物的未來狀況和開展趨勢作出推測的預測方法。定性預測法由于帶有較多的個人主觀性，因而在實踐中最好作為一種補充的預測方法。第4章Excel在財務(wù)預測中的應用

4.3Excel中的有關(guān)預測函數(shù)及其應用(1)

Excel提供了關(guān)于估計線性模型和指數(shù)模型參數(shù)的幾個預測函數(shù)。線性模型和指數(shù)模型的數(shù)學表達式如下：線性模型：y=mx+b或y=m1x1+m2x2+…+b指數(shù)模型：或式中，y為因變量；x是自變量；m、m1、...、mn-1、mn、b分別為預測模型的待估計參數(shù)。Excel提供的預測函數(shù)主要有LINEST函數(shù)、LOGEST函數(shù)、TREND函數(shù)、GROWTH函數(shù)、FORECAST函數(shù)、SLOPE函數(shù)和INTERCEPT函數(shù)，它們所使用的參數(shù)都根本相同，現(xiàn)列于表4-1中，以供參考。表4-1

預測函數(shù)的參數(shù)及含義參數(shù)含義known_y's

因變量y的觀測值集合known_x's

自變量x的觀測值集合。它可以是一個變量（即一元模型）或多個變量（即多元模型）的集合。如果只用到一個變量，只要known-y's和known-x's維數(shù)相同，它們可以是任何形狀的選定區(qū)域。如果用到不只一個變量，known_y's必須是向量（也就是說，必須是一行或一列的區(qū)域）。如果省略known_x's，則假設(shè)該數(shù)組是{1,2,3...}，其大小與known_y's相同const邏輯值，指明是否強制使常數(shù)b為0（線性模型）或為1（指數(shù)模型）。如果const為TRUE或省略，b將被正常計算。如果const為FALSE，b將被設(shè)為0（線性模型）或設(shè)為1（指數(shù)模型）stats邏輯值，指明是否返回附加回歸統(tǒng)計值。如果stats為TRUE，則函數(shù)返回附加回歸統(tǒng)計值，這時返回的數(shù)組為{mn，mn-1，...，m1，b；sen，sen-1，...，se1，seb，r2，sey；F，df；ssreg，ssresid}。如果stats為FALSE或省略，函數(shù)只返回系數(shù)預測模型的待估計參數(shù)m、mn、mn-1、...、m1和b。附加回歸統(tǒng)計值返回的順序見表4-2。表4-2中的各參數(shù)說明見表4-3。如果要得到附加回歸統(tǒng)計值數(shù)組中的值，需用INDEX函數(shù)將其取出表4-2

附加回歸統(tǒng)計值返回的順序

1234561mnmn-1…m2m1b2sensen-1…se2se1seb3r2sey

4Fdf

5ssregssresid

表4-3

各參數(shù)說明參數(shù)說明se1,se2,...,sen系數(shù)m1,m2,...,mn的標準誤差值Seb常數(shù)項b的標準誤差值（當const為FALSE時，seb=#N/A）

參數(shù)說明r2相關(guān)系數(shù)，范圍在0到1之間。如果為1，則樣本有很好的相關(guān)性，Y的估計值與實際值之間沒有差異。反之，如果相關(guān)系數(shù)為0，則回歸方程不能用來預測Y值seyY估計值的標準誤差FF統(tǒng)計值或F觀察值。使用F統(tǒng)計可以判斷因變量和自變量之間是否偶爾發(fā)生過觀察到的關(guān)系Df自由度。用于在統(tǒng)計表上查找F臨界值。所查得的值和函數(shù)LINEST返回的F統(tǒng)計值的比值可用來判斷模型的置信度ssreg回歸平方和ssresid殘差平方4.3.1

LINEST函數(shù)LINEST函數(shù)的功能是使用最小二乘法計算對數(shù)據(jù)進行最正確線性擬合的直線方程，并返回描述此線性模型的數(shù)組。因為此函數(shù)返回數(shù)值為數(shù)組，故必須以數(shù)組公式的形式輸入。函數(shù)公式為=LINEST（known_y's，known_x's，const，stats）下面舉例說明LINEST函數(shù)的應用。1．一元線性回歸分析LINEST函數(shù)可用于一元線性回歸分析，也可以用于多元線性回歸分析，以及時間數(shù)列的自回歸分析。當只有一個自變量x（即一元線性回歸分析）時，可直接利用下面的公式得到斜率和y軸的截距值以及相關(guān)系數(shù)：斜率：INDEX（LINEST（known_y's，known_x's）,1,1）；或INDEX（LINEST（known_y's,known_x's）,1）截距：INDEX（LINEST（known_y's，known_x's）,1,2）；或INDEX（LINEST（known_y's,known_x's）,2）相關(guān)系數(shù)：INDEX（LINEST（known_y's,known_x's,true,true）,3,1）【例4-1】某企業(yè)1～9月份的總本錢與人工小時及機器工時的數(shù)據(jù)如圖4-1所示。假設(shè)總本錢與人工小時之間存在著線性關(guān)系，則在單元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)〞，在單元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)〞，在單元格B15插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)〞，即得總本錢與人工小時的一元線性回歸分析方程為：Y=562.72756+4.41444X1，相關(guān)系數(shù)為R2=0.99801，如圖4-1所示。圖4-1

一元線性回歸分析2．多元線性回歸分析仍以例4-1的數(shù)據(jù)為例，首先選取單元格區(qū)域A17:D21，再以數(shù)組公式方式輸入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)〞，即得該二元線性回歸的有關(guān)參數(shù)如圖4-2所示，從而得到：圖4-2

二元線性回歸分析回歸方程：Y=471.4366+3.6165X1+3.4323X2相關(guān)系數(shù)：R2=0.9990標準差：Sey=11.7792。4.3.2

LOGEST函數(shù)LOGEST函數(shù)的功能是在回歸分析中，計算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該指數(shù)模型的數(shù)組。由于這個函數(shù)返回一個數(shù)組，必須以數(shù)組公式輸入。LOGEST函數(shù)的公式為=LOGEST(known_y's,known_x's,const,stats)【例4-2】某企業(yè)12個月某產(chǎn)品的生產(chǎn)量（X）與生產(chǎn)本錢（Y）的有關(guān)資料如圖4-3所示，假設(shè)它們之間有如下關(guān)系：。選取單元格區(qū)域B15:C18，輸入公式“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)〞（數(shù)組公式輸入），即得回歸參數(shù)，如圖4-3所示，參數(shù)m=0.8887，參數(shù)b=1891.7729，生產(chǎn)本錢與生產(chǎn)量的回歸曲線為：Y=1791.7729×0.8887X，相關(guān)系數(shù)R2=0.95885。圖4-3

指數(shù)回歸回歸方程的系數(shù)及相關(guān)系數(shù)也可以利用下面的公式直接計算參數(shù)m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887參數(shù)b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.77294.3.3

TREND函數(shù)TREND函數(shù)的功能是返回一條線性回歸擬合線的一組縱坐標值（y值），即找到適合給定的數(shù)組known_y's和known_x's的直線（用最小二乘法），并返回指定數(shù)組new_x's值在直線上對應的y值。TREND函數(shù)的公式為=TREND(known_y's,known_x's,new_x's,const)式中

new_x's——需要函數(shù)TREND返回對應y值的新x值。new_x's與known_x's一樣，每個獨立變量必須為單獨的一行（或一列）。因此，如果known_y's是單列的，known_x's和new_x's應該有同樣的列數(shù)，如果known_y's是單行的，known_x's和new_x's應該有同樣的行數(shù)。如果省略new_x's，將假設(shè)它和known_x's一樣。【例4-3】某企業(yè)過去一年的銷售量為以下數(shù)據(jù)：{300，356，374，410，453，487，501，534，572，621，650，670}，將它們保存在單元格A1:A12中，則下一年的1、2、3月的銷售量預測步驟為：選中單元格區(qū)域B1:B3，輸入公式“=TREND(A1:A12,,{13;14;15})〞（數(shù)組公式輸入），即得來年的1、2、3月份的銷售量分別為710、743和777。這個公式默認{1;2;3;4;5;6;7;8;9;10;11;12}作為known_x's的參數(shù)，故數(shù)組{13;14;15}就對應其后的3個月份。4.3.4

GROWTH函數(shù)GROWTH函數(shù)的功能是返回給定的數(shù)據(jù)預測的指數(shù)增長值。根據(jù)的x值和y值，函數(shù)GROWTH返回一組新的x值對應的y值。可以使用GROWTH工作表函數(shù)來擬合滿足給定x值和y值的指數(shù)曲線。GROWTH函數(shù)的公式為=GROWTH(known_y's,known_x's,new_x's,const)式中，各參數(shù)的含義同TREND函數(shù)。但需注意的是，如果known_y's中的任何數(shù)為零或為負，函數(shù)GROWTH將返回錯誤值#NUM!?！纠?-4】以例4-3的資料為例，利用GROWTH函數(shù)預測來年的1、2、3月的銷售量。預測步驟為：選中單元格區(qū)域B1:B3，輸入公式“=GROWTH(A1:A12,,{13;14;15})〞（數(shù)組公式輸入），即得來年的1、2、3月份的銷售量分別為756、811和870。這個公式同樣默認{1;2;3;4;5;6;7;8;9;10;11;12}作為known_x's的參數(shù)，故數(shù)組{13;14;15}就對應后面的3個月份。4.3.5

FORECAST函數(shù)FORECAST函數(shù)的功能是根據(jù)給定的數(shù)據(jù)計算或預測未來值。此預測值為基于一系列的x值推導出的y值。以數(shù)組或數(shù)據(jù)區(qū)域的形式給定x值和y值后，返回基于x的線性回歸預測值。FORECAST函數(shù)的計算公式為a+bx式中，；。FORECAST函數(shù)的公式為=FORECAST(x,known_y's,known_x's)式中x—需要進行預測的數(shù)據(jù)點。需要說明的是：如果x為非數(shù)值型，函數(shù)FORECAST返回錯誤值#VALUE!。如果known_y's和known_x's為空或含有不同數(shù)目的數(shù)據(jù)點，函數(shù)FORECAST返回錯誤值#N/A。如果known_x's的方差為零，函數(shù)FORECAST返回錯誤值#DIV/0!。例如：FORECAST(30,{6,7,9,15,21},{20,28,31,38,40})=10.60725。4.3.6

SLOPE函數(shù)SLOPE函數(shù)的功能是返回根據(jù)known_y's和known_x's中的數(shù)據(jù)點擬合的線性回歸直線的斜率。斜率為直線上任意兩點的垂直距離與水平距離的比值，也就是回歸直線的變化率。SLOPE函數(shù)的公式為=SLOPE(known_y's,known_x's)說明：參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)里包含文本、邏輯值或空白單元格，這些值將被忽略。但包含零值的單元格將計算在內(nèi)。如果known_y's和known_x's為空或其數(shù)據(jù)點數(shù)目不同，函數(shù)SLOPE返回錯誤值#N/A。例如：SLOPE({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})=0.305556。4.3.7

INTERCEPT函數(shù)INTERCEPT函數(shù)的功能是利用的x值與y值計算直線與y軸的截距。截距為穿過known_x's和known_y's數(shù)據(jù)點的線性回歸線與y軸的交點。公式為=INTERCEPT(known_y's,known_x's)例如：INTERCEPT({2,3,9,1,8},{6,5,11,7,5})=0.0483871。第4章Excel在財務(wù)預測中的應用

4.4利用數(shù)據(jù)分析工具解決預測問題(1)

除了利用前面介紹的幾個預測函數(shù)進行回歸預測分析外，我們還可以使用Excel的數(shù)據(jù)分析工具庫提供的統(tǒng)計觀測分析工具來解決回歸預測問題。Excel的數(shù)據(jù)分析工具庫提供了3種統(tǒng)計觀測分析工具，它們是移動平均法、指數(shù)平滑法和回歸分析法。下面結(jié)合實例來說明這3種方法的具體應用。4.4.1

移動平均法【例4-5】某企業(yè)2000年12個月的銷售額如圖4-4所示，分別按3期、5期和7期移動平均所做的預測分析如圖4-4中的C4︰E13區(qū)域所示。以3期移動平均為例為例，具體計算步驟如下：圖4-4

一次移動平均法實例（1）從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對話框，如圖4-5所示。圖4-5

【數(shù)據(jù)分析】對話框（2）在【數(shù)據(jù)分析】對話框中的【分析工具】框中選中【移動平均】選項，則彈出【移動平均】對話框，如圖4-6所示。圖4-6

【移動平均】對話框（3）在【移動平均】對話框中，【輸入?yún)^(qū)域】框中輸入“$B$2︰$B$13〞，【間隔】框中輸入“3〞，【輸出區(qū)域】框中輸入“$C$2〞，最后選中【圖表輸出】選項；（4）單擊【確定】按鈕，則運算結(jié)果就顯示在單元格區(qū)域C4:C13中，如圖4-4所示（圖中的第13行預測數(shù)據(jù)即為下月即第13月的預測值），并自動出現(xiàn)輸出圖表，如圖4-7所示。圖4-7移動期數(shù)為3時的輸出圖表用同樣的方法，可以分析當移動期數(shù)為5和7時的分析結(jié)果，如圖4-4所示。4.4.2

指數(shù)平滑法【例4-6】某企業(yè)的有關(guān)銷售數(shù)據(jù)如圖4-8所示，利用指數(shù)平滑法進行預測分析，其步驟如下：圖4-8指數(shù)平滑法實例（1）從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對話框，在【數(shù)據(jù)分析】對話框中的【分析工具】框中選中【指數(shù)平滑】選項，則彈出【指數(shù)平滑】對話框，如圖4-9所示。圖4-9

【指數(shù)平滑】對話框（2）在【指數(shù)平滑】對話框中，【輸入?yún)^(qū)域】框中輸入“$B$2:$B$13〞，【阻尼系數(shù)】框中輸入“0.2〞，【輸出區(qū)域】框中輸入“$C$3〞，最后選中【圖表輸出】選項。（3）單擊【確定】按鈕，則運算結(jié)果就顯示在單元格區(qū)域C3:C13中（圖中的第13行預測數(shù)據(jù)即為下月即第13月的預測值），如圖4-8所示，并自動出現(xiàn)輸出圖表，如圖4-10所示。圖4-10

指數(shù)平滑法預測輸出圖（阻尼系數(shù)0.2）用同樣的方法，可以分析當阻尼系數(shù)為0.4和0.6時的分析結(jié)果如圖4-8所示。需要注意的是，【數(shù)據(jù)分析】中的指數(shù)平滑法所使用的阻尼系數(shù)并不是4.2.1.2節(jié)介紹的指數(shù)平滑法預測方程中的平滑系數(shù)，二者的關(guān)系為：阻尼系數(shù)=1－。4.4.3

回歸法利用Excel的回歸工具進行預測分析有兩種方法：一是圖表法；二是回歸分析法。4.4.3.1

圖表法圖表法僅能解決一元線性或非線性回歸問題，不能解決多元回歸問題。【例4-7】某企業(yè)連續(xù)9年的產(chǎn)品銷售收入Y（萬元）與廣告支出X1（萬元）和居民平均收入X2（元）的有關(guān)數(shù)據(jù)如圖4-11所示，則利用圖表法進行回歸分析，其方法和步驟如下，這里僅以銷售收入Y（萬元）與廣告支出X1（萬元）的一元線性關(guān)系為例：圖4-11

某企業(yè)的有關(guān)銷售數(shù)據(jù)（1）選擇單元格區(qū)域B2:C10。（2）單擊工具欄上的【圖表導向】按鈕，在【圖表導向—4步驟之1—圖表類型】中選“XY散點圖〞，其【子圖表類型】選第1種，如圖4-12所示。圖4-12

準備作散點圖（3）單擊【下一步】按鈕，出現(xiàn)【圖表導向—4步驟之2—圖表源數(shù)據(jù)】對話框，單擊【系列】，在【名稱】欄中填入“銷售收入〞，在【X值】欄中輸入“=Sheet1!$C$2:$C$10〞，在【Y值】欄中輸入“=Sheet1!$B$2:$B$10〞（用鼠標拾取單元格區(qū)域），如圖4-13所示。圖4-13

填入源數(shù)據(jù)（4）單擊【下一步】按鈕，出現(xiàn)【圖表導向—4步驟之3—圖表選項】對話框，填入各標題文字，如圖4-14所示。圖4-14

填入各標題文字（5）單擊【下一步】按鈕，出現(xiàn)【圖表導向—4步驟之4—圖表位置】對話框，不作任何輸入，單擊【完成】按鈕，則在工作表上看到輸出的圖形，對其進行必要的調(diào)整（如坐標、字體、位置等）。（6）在系列【數(shù)據(jù)點】上的任一點上，按鼠標左鍵，使各數(shù)據(jù)點出現(xiàn)記號，再單擊【工具欄】上的【圖表】按鈕，選中【添加趨勢線】項，或在數(shù)據(jù)點上按鼠標右鍵，選【添加趨勢線】項，出現(xiàn)【添加趨勢線】對話框，如圖4-15所示。圖4-15

【添加趨勢線】對話框（7）在【添加趨勢線】中的【類型】對話框中，有【線性】、【對數(shù)】、【多項式】、【乘冪】、【指數(shù)】和【移動平均】6個選項。通過觀察XY散點圖可知，產(chǎn)品銷售收入與廣告支出之間呈明顯的線性關(guān)系，故這里選【線性】。（8）在【添加趨勢線】中的【選項】對話框中，勾選【顯示公式】、【顯示R平方值】，如圖4-16所示。圖4-16

【添加趨勢線】的【選項】設(shè)置（9）單擊【確定】按鈕，則在圖形上顯示出較粗的預測線、回歸方程和R平方值，然后進行必要的調(diào)整，得到如圖4-17的結(jié)果。圖4-17輸出圖形用同樣的方法還可以確定銷售收入與居民平均收入的關(guān)系。4.4.3.2

回歸分析法回歸分析法可以對一元線性或多元線性以及某些可以轉(zhuǎn)化為線性的非線性問題進行回歸分析。1．線性回歸【例4-8】仍以例4-7的有關(guān)資料為例，回歸分析的步驟如下：（1）從【工具】菜單中選中【數(shù)據(jù)分析】命令，則彈出【數(shù)據(jù)分析】對話框，在【數(shù)據(jù)分析】對話框中的【分析工具】框中選中【回歸】選項，如圖4-18所示，則彈出【回歸】對話框。圖4-18

【數(shù)據(jù)分析】對話框（2）在【回歸】對話框中，【Y值輸入?yún)^(qū)域】中輸入“$B$1:$B$10〞，【X值輸入?yún)^(qū)域】中輸入“$C$1:$D$10〞，在【輸出選項】中勾選【輸出區(qū)域】，填入“$A$12〞，然后根據(jù)實際需要，勾選其他需要的選項，如圖4-19所示。圖4-19【回歸】選項（3）單擊【確定】按鈕，回歸分析的摘要就輸出在本工作表上，如圖4-20所示。對這些數(shù)據(jù)進行分析可知：R平方值為0.9903，說明因變量與自變量之間相關(guān)性很高；F的顯著值為2.961E-07，已達0.05的檢驗標準；其他統(tǒng)計檢驗也到達相應的標準。從而得到回歸方程為：Y=229.8409+9.2794X1+0.0082X2。圖4-20

回歸分析的計算機輸出當自變量只有一個（即一元回歸）時，上述方法同樣適用。2．非線性回歸對于某些可以化為線性關(guān)系的非線性問題，同樣可以進行回歸分析。舉例如下?！纠?-9】某地區(qū)科研系統(tǒng)近10年的凈收入Y（千萬元）與研究經(jīng)費X1（千萬元）和研究人員數(shù)X2（萬人）的統(tǒng)計資料，如圖4-21所示，假設(shè)它們之間存在著以下的函數(shù)關(guān)系：式中，a、b、c為待估計參數(shù)。若利用回歸工具求解此類非線性問題，解決的方法是將此方程進行數(shù)學變換，即對方程兩邊取對數(shù)，得，將各個觀測值進行變換，如圖4-21所示，即在單元格E2:E11中輸入公式“=LN(B2:B11)〞（數(shù)組公式輸入），然后將單元格E2:E11復制到單元格F2:F11和G2:G11中。圖4-21某地區(qū)科研系統(tǒng)有關(guān)資料再對變換后的數(shù)據(jù)利用Excel的回歸工具進行回歸分析，具體步驟可參閱【例4-7】，其中【Y值輸入?yún)^(qū)域】中輸入“$E$1:$E$11〞，在【X值輸入?yún)^(qū)域】中輸入“$F$1:$G$11〞，在【輸出選項】中勾選【輸出區(qū)域】，填入“$A$12〞，得到如圖4-22所示的分析結(jié)果，最后得到：a=e0.08214=0.9211，b=0.4477，c=0.6046，相關(guān)系數(shù)為0.9808（注意此相關(guān)系數(shù)是變換后的線性方程的相關(guān)系數(shù)，并不是原非線性方程的相關(guān)系數(shù)），回歸方程為：。圖4-22

回歸分析結(jié)果第4章Excel在財務(wù)預測中的應用

4.5利用規(guī)劃求解工具解決預測問題

雖然我們可以利用Excel提供的各種預測分析工具解決大多數(shù)財務(wù)預測中的實際問題，但這些預測分析工具并不是萬能的，其預測誤差也隨著實際問題的復雜化而增大。比方對于一些非線性預測問題，常常是將其通過變量替換而轉(zhuǎn)換為線性問題。但是，這種變換過程一方面增加了計算工作量，另一方面也可能導致分析精度下降，因為變換后的數(shù)據(jù)容易使觀測數(shù)據(jù)的性質(zhì)發(fā)生變化，導致自變量與因變量之間的關(guān)系發(fā)生扭曲，從而影響回歸方程的精度，因此，這種將非線性轉(zhuǎn)換為線性的做法是存在一定的缺陷的。此外，有些非線性問題根本無法直接轉(zhuǎn)換為線性問題，除非作出大量的簡化，這必然使得到的回歸方程嚴重失真。因此，對于非線性回歸問題，最好的方法是直接進行回歸分析，即求解使殘差平方和最小、或使相關(guān)系數(shù)最大的回歸方程，但非線性回歸過程是一個循環(huán)尋優(yōu)過程，需要先設(shè)置回歸方程系數(shù)的初值，然后計算觀測值與預測值的殘差平方和，不斷尋找使殘差平方和最小的回歸方程系數(shù)，這實際上是一個優(yōu)化問題，因此，可以利用Excel的規(guī)劃求解工具求解非線性回歸問題，當然也可以用來求解線性回歸問題。在利用規(guī)劃求解工具直接求解非線性回歸問題時，需要使用以下幾個計算公式：自由度df為式中，n為觀測次數(shù)；m為待估計參數(shù)個數(shù)。殘差平方和SE為式中，Yi、分別為第i個觀測值和預測值（i=1,2,…,n）。剩余標準差SS為相關(guān)系數(shù)R2為式中，為觀測值的平均值。下面結(jié)合實例說明在Excel上進行非線性回歸的具體方法和步驟?！纠?-10】以例4-9的有關(guān)資料為例，利用Excel的規(guī)劃求解工具來求解非線性回歸問題的方法和步驟如下：（1）如圖4-23所示，單元格G2:G4為變動單元格，分別存放待估計參數(shù)a、b、c，其初值可設(shè)為0。（2）在單元格E2:E11中輸入預測值公式“=G2*(C2:C11)^G3*(D2:D11)^G4〞（數(shù)組公式輸入）。圖4-23利用規(guī)劃求解工具進行非線性回歸分析（3）在單元格G5中輸入觀測值的平均值公式“=AVERAGE(B2:B11)〞；在單元格G6中輸入自由度公式“=COUNT(B2:B11)-COUNT(G2:G4)〞；在單元格G7中輸入殘差平方和公式“=SUM((B2:B11-E2:E11)^2)〞（數(shù)組公式輸入）；在單元格G8中輸入剩余標準差公式“=SQRT(G7/G6)〞；在單元格G9中輸入相關(guān)系數(shù)R2的計算公式“=1-G7/SUM((B2:B11-G5)^2)〞（數(shù)組公式輸入）。（4）單擊EXCEL工具菜單，選擇【規(guī)劃求解】項，出現(xiàn)【規(guī)劃求解參數(shù)】對話框；（5）在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標單元格】設(shè)置為單元格“$G$7〞，即目標函數(shù)為殘差平方和；【等于】設(shè)置為“最小〞；【可變單元格】設(shè)置為“$G$2:$G$4〞。然后單擊【求解】，即可得到回歸方程的系數(shù)a、b、c，出現(xiàn)“規(guī)劃求解結(jié)果〞對話框，然后單出【確定】按鈕，保存規(guī)劃求解結(jié)果。需要注意的是，若系數(shù)a、b、c的初值設(shè)置不適宜的話，則一次求解過程（即在Excel上進行【工具】【規(guī)劃求解】【求解】【確定】這樣一個求解過程）可能得不到最優(yōu)結(jié)果（或得不到解），這時需要進行屢次求解，即在第一次求解結(jié)果的基礎(chǔ)上，再進行第二次求解，得到第二次求解結(jié)果，然后在第二次求解結(jié)果的基礎(chǔ)上，再進行第三次求解，得到第三次求解結(jié)果，如此繼續(xù)下去，直到求出的系數(shù)a、b、c的值不再變化、且殘差平方和最小為止，即得到最優(yōu)結(jié)果。在上例中，當a、b、c的初始值設(shè)為0時，經(jīng)過2次求解過程即得到最優(yōu)結(jié)果，如圖4-23所示。若采用非線性轉(zhuǎn)換為線性的方法，如例4-9所示，可得到有關(guān)系數(shù)如圖4-22所示，將此系數(shù)代入回歸方程，計算不同X1和X2下的預測值，進而計算出殘差平方和與剩余標準差分別為157.1139和4.7376，與圖4-23的直接非線性回歸的結(jié)果（殘差平方和154.03、剩余標準差4.6909）進行比較，可見非線性轉(zhuǎn)換為線性的方法得出的結(jié)果誤差要大于直接進行非線性回歸的誤差。第4章Excel在財務(wù)預測中的應用

4.6銷售預測

銷售預測的準確程度，對企業(yè)的興衰成敗會產(chǎn)生很重要的影響。銷售預測比較準確，會使企業(yè)在有方案的財務(wù)安排下順利運作；而如果銷售預測與實際情況偏離很遠，則會使企業(yè)遇到麻煩，甚至陷入困境。因此，銷售預測是企業(yè)進行財務(wù)預測的首要工作，是企業(yè)制定財務(wù)方案的基礎(chǔ)。

銷售預測主要應根據(jù)市場需求的變化，結(jié)合企業(yè)的利潤目標、實現(xiàn)企業(yè)市場份額的目標，并綜合考慮企業(yè)內(nèi)外部的各種限制條件的影響來進行。一般情況下，可首先分別對未來各期的銷售量和銷售價格進行預測，在此基礎(chǔ)上，根據(jù)預測的產(chǎn)品銷售價格乘以預測的銷售量得到預測的銷售額；也可以直接根據(jù)銷售額的有關(guān)歷史資料，采用適當?shù)姆椒ㄟM行預測。4.6.1

銷售預測的根本方法銷售預測是一項比較復雜的工作，需要考慮的因素很多，作出準確的預測是非常困難的。通?？衫闷髽I(yè)過去的數(shù)據(jù)進行統(tǒng)計分析，并結(jié)合經(jīng)濟環(huán)境對未來市場的影響以及企業(yè)內(nèi)外部各種條件的限制，作出銷售預測。進行銷售預測的方法很多，常用的方法包括以下幾種。1．時間序列預測法時間序列預測法，是指將觀察或記錄的一些歷史數(shù)據(jù)，按時間的先后排列成數(shù)據(jù)系列，進行統(tǒng)計分析，找出過去長期的銷售量或銷售額的增減變化趨勢，再根據(jù)此變化趨勢分析的結(jié)果，預測未來時期的銷售量或銷售額。常見的時間序列的預測方法有簡單平均法、移動平均法、指數(shù)平滑法、或以時間為自變量的回歸分析法等，這些方法的根本原理可參閱前面的有關(guān)內(nèi)容。2．因果關(guān)系預測法因果關(guān)系預測法，是指利用有關(guān)因素與產(chǎn)品銷售量或銷售額之間的固有因果關(guān)系，通過建立一定的數(shù)學模型來預測企業(yè)未來的產(chǎn)品銷售水平的一種方法。企業(yè)產(chǎn)品銷售水平的上下，往往受到諸多宏觀或微觀、外部或內(nèi)部、客觀或主觀等因素的影響，通常可以通過回歸分析的方法檢驗出哪些因素與銷售水平之間具有因果關(guān)系，在此基礎(chǔ)上可建立回歸方程，進行銷售預測。有關(guān)如何建立回歸方程及進行相關(guān)檢驗的方法可參閱前面的有關(guān)內(nèi)容。3．通過生產(chǎn)能力或訂貨合同進行銷售量（銷售額）預測企業(yè)生產(chǎn)的產(chǎn)品如果在市場占有穩(wěn)定的份額或供不應求，則可按本企業(yè)的生產(chǎn)能力預測產(chǎn)品的銷售量，計算公式如下：方案期銷售量=方案期初庫存量+方案期預計生產(chǎn)量-方案期末預計庫存量4.6.2

銷售預測模型及其應用在很多情況下，通過建立企業(yè)的銷售預測模型，可以很方便地實現(xiàn)銷售預測。下面介紹兩個銷售預測模型。4.6.2.1

一元線性（非線性）回歸預測模型【例4-11】根據(jù)圖4-24中所給的資料建立一元線性（非線性）回歸預測模型。圖4-24

一元線性（非線性）回歸銷售預測模型下面利用線性回歸中的LINEST函數(shù)和指數(shù)回歸中的LOGEST函數(shù)，來建立一元線性（非線性）回歸預測模型。（1）首先建立銷售預測模型，如圖4-24所示，這里以過去12期的銷售量為歷史數(shù)據(jù)（可以是以年計算，也可以是以月計算，圖4-24為以年計算）。（2）設(shè)置回歸模型選擇控件，控件的數(shù)據(jù)源區(qū)域為$A$7:$A$8，單元格鏈接為$B$7，下拉顯示項數(shù)為2。（3）選取單元格區(qū)域B3:M3，單擊【插入】【名稱】【定義】命令，或直接單擊編輯欄中的名稱框，將影響因素所在的單元格區(qū)域B3:M3定義為“影響因素序列〞；用同樣的方法，將銷售量所在的單元格區(qū)域B4:M4定義為“銷售序列〞。（4）在單元格E8中輸入公式“=IF(B7=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,2),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),1,2))〞，計算系數(shù)A。（5）在單元格F8中輸入公式“=IF(B7=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,1),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),1,1))〞，計算系數(shù)B。（6）在單元格G8中輸入公式“=IF(B7=1,INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),3,1),INDEX(LOGEST(銷售序列,影響因素序列,TRUE,TRUE),3,1))〞，計算相關(guān)系數(shù)R2。（7）在單元格J8:M8中輸入公式“=IF(B7=1,E8+F8*J7:M7,E8*F8^J7:M7)〞（數(shù)組公式輸入），計算未來第1～4期的預測值。在影響因素和銷售量兩列輸入歷史數(shù)據(jù)，并在J7:M7中輸入未來4期的影響因素預測數(shù)值后，即可得到回歸預測模型及未來的預測值。通過選擇不同的回歸模型，可以分別計算一元線性模型和一元指數(shù)模型下的回歸結(jié)果及預測值。由計算結(jié)果可知，采用指數(shù)模型（相關(guān)系數(shù)為0.9742）要比線性模型（相關(guān)系數(shù)為0.9452）更為準確。4.6.2.2

多元線性回歸預測模型【例4-12】根據(jù)圖4-25中所給的資料建立多元線性回歸預測模型。圖4-25

多元線性回歸銷售預測模型當影響銷售量（額）的因素不止一個時，就需要建立多元線性回歸模型。下面就二元線性回歸預測模型的建立進行說明，對于影響因素在兩個以上的情況，可參照本模型建立。（1）首先建立銷售預測模型，如圖4-25所示，這里以過去12期的歷史數(shù)據(jù)為依據(jù)（可以是按年計算，也可以是按月計算，圖4-25為按月計算）。（2）選取單元格區(qū)域B3:M4，單擊【插入】【名稱】【定義】命令，或直接單擊編輯欄中的名稱框，將影響因素所在的單元格區(qū)域B3:M4定義為“影響因素序列〞；用同樣的方法，將銷售額所在的單元格區(qū)域B5:M5定義為“銷售序列〞。（3）在單元格D9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,3)〞，計算系數(shù)A。（4）在單元格E9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,2)〞，計算系數(shù)B。（5）在單元格F9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),1,1)〞，計算系數(shù)C。（6）在單元格G9中輸入公式“=INDEX(LINEST(銷售序列,影響因素序列,TRUE,TRUE),3,1)〞，計算相關(guān)系數(shù)R2。（7）在單元格J9:M9中輸入公式“=D9+E9*J7:M7+F9*J8:M8〞（數(shù)組公式輸入），計算未來第1～4期的預測值。在影響因素和銷售額各列輸入歷史數(shù)據(jù)，并在J7:M8中輸入未來4期的影響因素預測數(shù)值后，即可得到回歸預測模型及未來的預測值，如圖4-25所示第4章Excel在財務(wù)預測中的應用

4.7本錢預測

4.7.1

本錢預測的方法本錢是指企業(yè)為生產(chǎn)和銷售產(chǎn)品所花費的全部費用。本錢可以按很多不同的標準進行分類，如常見的按經(jīng)濟職能分類和按本錢性態(tài)分類：本錢按經(jīng)濟職能劃分可分為生產(chǎn)本錢和非生產(chǎn)本錢兩大類。生產(chǎn)本錢又稱制造本錢，包括生產(chǎn)過程中發(fā)生的直接材料、直接人工和制造費用三個工程；非生產(chǎn)本錢又稱非制造本錢，包括為銷售產(chǎn)品所花費的銷售費用和為組織企業(yè)的生產(chǎn)所花費的管理費用。本錢性態(tài)又稱本錢習性，是指本錢總額對業(yè)務(wù)量（如產(chǎn)銷量）總數(shù)的依存關(guān)系。按本錢性態(tài)可將全部本錢分為變動本錢、固定本錢和混合本錢三類。變動本錢是指總額與業(yè)務(wù)量總數(shù)成正比例變動關(guān)系的本錢，如直接材料、直接人工、變動性制造費用等。固定本錢是指在一定的業(yè)務(wù)量范圍內(nèi)，總額不受業(yè)務(wù)量增減變動影響的本錢，如按直線法計提的固定資產(chǎn)折舊費、管理人員工資等?；旌媳惧X是指總額隨業(yè)務(wù)量總數(shù)發(fā)生變化、但不成正比例變動的本錢，通過采用適當?shù)姆椒梢詫⒒旌媳惧X分解為變動本錢和固定本錢兩大類。本錢預測是根據(jù)企業(yè)未來的開展目標和現(xiàn)實條件，參考其他資料，利用專門的方法對企業(yè)未來本錢水平及其變動趨勢進行估算和預測。本錢預測可為本錢決策和實施本錢控制提供有用的信息。本錢預測的方法主要有：（1）歷史本錢法。這種方法主要是根據(jù)本錢的歷史資料來預測未來的本錢水平，常用的方法有上下點法和回歸分析法。（2）目標利潤推算法。這種方法主要是根據(jù)有關(guān)的經(jīng)濟預測（銷售量、銷售價格）和企業(yè)的目標利潤等數(shù)據(jù)，來反算本錢的一種方法。（3）因素分析法。這種方法是根據(jù)預期影響本錢的各種因素的變化來預測未來本錢，它需要企業(yè)擁有較為詳細的資料。（4）比例推算法。這種方法是利用企業(yè)的生產(chǎn)消耗與企業(yè)有關(guān)重大經(jīng)營成果指標之間的依存關(guān)系，按被確認的報告年度本錢與這些指標的比例關(guān)系推算預測期的本錢水平。這種方法只是一種大概的推算方法。上述的本錢預測方法中，回歸分析法較為科學也比較常用。下面主要介紹在Excel上利用回歸分析法進行本錢預測的具體方法。4.7.2

本錢預測模型利用回歸分析法進行本錢預測首先需要建立回歸分析模型。回歸分析模型就是將總本錢分解成與銷售量無關(guān)的固定本錢和與銷售量有關(guān)的變動本錢，從而根據(jù)未來的預計銷售量對本錢進行預測。模型的數(shù)學表達式如下：一元一次模型（線性模型）：

銷售本錢=固定本錢+單位變動本錢×銷售量

一元二次模型（非線性模型）：銷售本錢=固定本錢+單位變動本錢×銷售量+混合本錢×銷售量平方【例4-13】建立一元一次（二次）本錢預測模型。在Excel上建立銷售本錢的回歸分析模型方法可參閱前面介紹的回歸分析方法，具體步驟如下：（1）首先建立本錢預測模型，如圖4-26所示，這里假設(shè)已有過去12期的數(shù)據(jù)為歷史數(shù)據(jù)；（2）建立線性或非線性模型選擇控件，控件的數(shù)據(jù)源區(qū)域為$A$8:$A$9，單元格鏈接為$B$8，下拉顯示項數(shù)為2。（3）在單元各B4:M4中輸入銷售量平方計算公式“=B3:M3^2〞（數(shù)組公式輸入），以準備進行一元二次回歸分析。圖4-26銷售本錢預測模型（4）在單元格E9中輸入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,2),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,3))〞，計算系數(shù)A。（5）在單元格F9中輸入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,1),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,2))〞，計算系數(shù)B。（6）在單元格G9中輸入公式：“=IF(B8=1,0,INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,1))〞，計算系數(shù)C。（7）在單元格H9中輸入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),3,1),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),3,1))〞，計算相關(guān)系數(shù)R2。（8）在單元格K9:M9中輸入公式：“=E9+F9*K8:M8+G9*K8:M8^2〞（數(shù)組公式輸入），計算未來第1～3期的預測值。這樣企業(yè)的一元線性及一元非線性本錢預測模型就建立起來了。在實際預測中，可以先對銷售量及銷售本錢的歷史數(shù)據(jù)作出散點圖，根據(jù)其趨勢來選擇一元線性或一元非線性預測模型。第4章Excel在財務(wù)預測中的應用

4.8利潤預測(1)

利潤預測是指在銷售預測的基礎(chǔ)上，根據(jù)企業(yè)未來開展目標和其他相關(guān)資料，預計、推測、或估算未來應該到達和希望實現(xiàn)的利潤水平及其變動趨勢的過程。一般可在對本錢、業(yè)務(wù)量（產(chǎn)銷量）、利潤之間的關(guān)系進行研究的基礎(chǔ)上作出利潤預測，這種預測方法稱為本量利分析法。本節(jié)主要介紹單一品種情況下確定性本量利分析和不確定性本量利分析的具體方法和步驟，并采用規(guī)劃求解方法對約束條件下的多品種本量利關(guān)系進行分析。4.8.1

確定性條件下單品種利潤敏感性分析模型敏感性分析是一種定量分析方法，它研究當制約利潤的有關(guān)因素發(fā)生某種變化時對利潤所產(chǎn)生的影響，這對于利潤預測分析，特別是對目標利潤預測有著十分積極的指導意義。在企業(yè)只生產(chǎn)單品種產(chǎn)品，且產(chǎn)銷平衡的條件下，本量利之間的根本關(guān)系為利潤=銷售量×（單價－單位變動本錢）－固定本錢在進行利潤敏感性分析時，假定條件如下：利潤只受銷售量、產(chǎn)品單價、單位變動本錢和固定本錢的影響；上述各因素的變動均不會影響其他因素的變動。【例4-14】根據(jù)圖4-27所給的資料建立確定性條件下單品種利潤敏感性分析模型。下面就建立利潤敏感性分析模型的具體步驟進行說明。（1）設(shè)計利潤分析模型結(jié)構(gòu)，如圖4-27所示，這里假設(shè)只有一種產(chǎn)品。（2）首先設(shè)計銷售量的【滾動條】控件。單擊【視圖】，選擇【工具欄】，再單擊【窗體】，出現(xiàn)【窗體】工具欄，單擊【滾動條】按鈕，然后在工作表的適宜位置（這里為E4～F4單元格）拖曳出一個矩形【組合框】控件，并調(diào)整其大小。圖4-27

利潤敏感性分析模型（3）將鼠標移到新建立的【滾動條】控件上，單擊鼠標右鍵，出現(xiàn)快捷菜單，選擇【設(shè)置控件格式】，出現(xiàn)【設(shè)置控件格式】對話框，選擇【控制】項，在【當前解】欄中輸入50，【最小值】欄中輸入0，【最大值】欄中輸入100，【步長】欄中輸入1，【頁步長】欄中輸入5，在【單元格鏈接】欄中填入“E4〞，然后單擊【確定】按鈕，這就建立了銷售量的【滾動條】控件。這里假設(shè)了利潤的各個影響因素的變動百分比范圍為50%。（4）其他工程的【滾動條】控件可按照上述方法進行設(shè)置。其中，單價、單位變動本錢和固定本錢【滾動條】的【單元格鏈接】分別為單元格E5、E6、和E7。（5）在單元格D4:D7中建立變動百分比與【滾動條】控件的聯(lián)系，即選取單元格D4:D7并輸入公式“=E4:E7/100-50%〞（數(shù)組公式輸入）。說明：本例中各因素的變動范圍為-50%～+50%，而滾動條控制按鈕的值的變化范圍為0～100，為了使?jié)L動條控制按鈕的變化表示為百分數(shù)的變化，這里將控制按鈕的值除以100后再減去50%，則每次單擊滾動條兩端的箭頭，單元格D4:D7中的變動百分比就變化1%，而當滾動條在中間位置，百分比恰好為零；當單擊滾動框與滾動條兩端之間時，每單擊一次，變動百分比就增（或減）5%。（6）選取單元格C4:C7并輸入變化后各工程數(shù)值的計算公式“=B4:B7*(1+D4:D7)〞（數(shù)組公式輸入）。（7）在單元格A10中輸入預計利潤計算公式：“=B4*(B5-B6)-B7〞，并復制到單元格B10中，在單元格C10中輸入公式“=B10-A10〞，在單元格D10輸入公式“=C10/A10〞。這樣，就得到了多因素變動對利潤的綜合影響。（8）設(shè)計單因素變動影響分析表格，如圖4-27所示，在單元格B13:B16中輸入公式“=A10〞（數(shù)組公式輸入），在單元格C13:C16中輸入公式“=D4:D7〞，在單元格D13～D16中分別輸入銷售量、產(chǎn)品單價、單位變動本錢和固定本錢單獨變動時對利潤的影響計算公式，分別為：單元格C13：“=C4*(B5-B6)-B7〞，單元格C14：“=B4*(C5-B6)-B7〞，單元格C15：“=B4*(B5-C6)-B7〞，單元格C16：“=B4*(B5-B6)-C7〞；在單元格E13:E16中輸入公式“=D13:D16-B13:B16〞（數(shù)組公式輸入）；在單元格F13:F16中輸入公式“=E13:E16/B13:B16〞（數(shù)組公式輸入）。這樣利潤敏感性分析模型就建立起來了。單擊各個影響因素滾動條的箭頭，改變其變動幅度，就可以很方便地了解各個因素變動對利潤的單獨影響程度以及綜合影響程度。圖4-27為各個影響因素的變動率及其對利潤影響結(jié)果的一個例子，由圖可見，當銷售量單獨增加10%時，利潤增加12%；當產(chǎn)品單價單獨增加10%時，利潤增加40%；當單位變動本錢減少10%時，利潤增加28%；當固定本錢單獨減少10%時，利潤增加2%。因此，產(chǎn)品單價對利潤的影響最大，敏感性最強，其次是單位變動本錢和銷售量，而固定本錢對利潤的影響最小。當上述4個因素同時朝著使利潤增加的方向變動10%時（見圖4-27），則利潤的增加幅度為88.80%。4.8.2

確定性條件下多品種本量利分析模型在多品種的情況下，假定各產(chǎn)品的品種結(jié)構(gòu)一定，則可以利用加權(quán)平均法進行本量利分析，具體可參閱第8章的有關(guān)內(nèi)容。傳統(tǒng)的本量利分析方法存在著以下明顯的缺陷：一是產(chǎn)銷平衡的假設(shè)，實際上，除非企業(yè)接到一批確定的訂單，并按訂單核算企業(yè)是否有利潤可賺，按訂單安排生產(chǎn)，否則產(chǎn)銷不平衡是經(jīng)常存在的，產(chǎn)品結(jié)構(gòu)也將發(fā)生變化；二是以各產(chǎn)品預計銷售量來確定產(chǎn)品結(jié)構(gòu)，進而采用有關(guān)方法來計算各產(chǎn)品的保本額和保本量，但是，預計銷售量是企業(yè)根據(jù)市場來預測的，它具有很大的不確定性，預測的銷售量也常常是一個區(qū)間，而不是一個固定數(shù)，故以此來確定產(chǎn)品結(jié)構(gòu)不盡合理；三是沒有考慮企業(yè)自身條件的限制，因為企業(yè)的產(chǎn)品結(jié)構(gòu)受企業(yè)本身各種限制條件（如企業(yè)的現(xiàn)有生產(chǎn)技術(shù)工藝、設(shè)備生產(chǎn)能力、企業(yè)資源、銷售渠道等）的影響，而且這些條件也是時常變化的，無論是為了實現(xiàn)保本平衡還是為了實現(xiàn)目標利潤，企業(yè)在同時生產(chǎn)銷售多種產(chǎn)品時的產(chǎn)品結(jié)構(gòu)都必須依據(jù)實際情況進行相應的調(diào)整，不根據(jù)企業(yè)的具體情況及市場情況計算的保本點是沒有實際意義的。因此，對多品種盈虧平衡分析的正確方法是考慮各種約束條件，采用規(guī)劃方法來解決企業(yè)的保本平衡、保利平衡、最優(yōu)經(jīng)營決策等問題。根據(jù)不同的分析目的，可以建立以下幾種多品種本量利分析模型。1．利潤最大化模型假設(shè)企業(yè)的產(chǎn)品銷售不受市場的限制，僅受企業(yè)自身條件的限制，即企業(yè)可以根據(jù)自身的條件隨意安排產(chǎn)品生產(chǎn)，則可建立如下的利潤最大化模型。目標函數(shù)：≤

約束條件：

（j=1,2,…,m）0≤Qi≤Qi，max

（i=1,2,…,n）式中—為企業(yè)的利潤；pi、vi、Qi（i=1,2,…,n）—產(chǎn)品i的單價、單位變動本錢、銷售量；n—為產(chǎn)品種類；ai,j—為每單位第i種產(chǎn)品消耗的第j種資源的數(shù)量；bj—為第j種資源的可用總量；m—為資源種類；Qi,max—第i種產(chǎn)品的最大生產(chǎn)能力。在本模型中，假設(shè)了各產(chǎn)品之間相互獨立。在實際中，某些產(chǎn)品可能存在相互依賴的關(guān)系或互斥關(guān)系等某些特殊的關(guān)系，則在約束條件中需要參加這些條件。2．保利模型設(shè)企業(yè)的目標利潤為，則企業(yè)為實現(xiàn)此利潤的產(chǎn)品結(jié)構(gòu)（各產(chǎn)品產(chǎn)銷量）模型為

≤

（j=1,2,…,m）

0≤Qi≤Qi，max

（i=1,2,…,n）式中，各參數(shù)含義同上，其中Qi,max為第i種產(chǎn)品的最大產(chǎn)銷量。3．保本模型當=0時，上述的保利模型即變?yōu)槿缦碌谋１灸Ｐ停?/p>

≤

（j=1,2,…,m）

0≤Qi≤Qi，max

（i=1,2,…,n）式中，Qi,max可為第i種產(chǎn)品的最大銷售量或生產(chǎn)量。4．本錢控制模型由于企業(yè)的許多產(chǎn)品是有同類產(chǎn)品比較的，故它們的價格不能高于同類產(chǎn)品的價格。在給定產(chǎn)品銷售量及企業(yè)固定本錢的情況下，則可建立企業(yè)實現(xiàn)目標利潤的各產(chǎn)品的變動本錢控制模型為目標函數(shù)：

約束條件：≤

（j=1,2,…,m）

pi≤pi*

（i=1,2,…,n）式中，V為變動本錢總額；pi*為第i種產(chǎn)品的最高售價。其他符號意義同上。在給定各產(chǎn)品的最高單價的情況下，利用上述模型可以確定各產(chǎn)品的最高單位變動本錢。同樣，一般情況下根據(jù)上述模型得出的各產(chǎn)品的單位變動本錢組合有無數(shù)個，但在給定某些產(chǎn)品的最高單位變動本錢后，其他產(chǎn)品的單位變動本錢就可以求出。這樣，根據(jù)實際不同的需要，分別利用Excel的規(guī)劃求解工具求解上述模型，即可進行多品種本量利分析。下面舉例說明多品種本量利分析的方法?！纠?-15】某企業(yè)同時生產(chǎn)銷售A、B、C三種產(chǎn)品，產(chǎn)品有關(guān)資料如圖4-28所示。根據(jù)條件，可建立如下的利潤最大化模型：圖4-28利潤最大化的本量利分析目標函數(shù)：max{}=(14-10)Q1+(20-16)Q2+(45-30)Q3﹣300000約束條件：5Q1+4Q2+4Q3≤4000003Q1+4Q2+5Q3≤3500004Q1+3Q2+4Q3≤3000000≤Q1≤400000≤Q2≤350000≤Q3≤35000式中，Q1、Q2、Q3分別為產(chǎn)品A、B、C的產(chǎn)銷量，且為整數(shù)。如圖4-28所示，求解上述模型的步驟如下：（1）單元格B14:D14作為可變單元格，存放產(chǎn)品A、B、C的產(chǎn)銷量。（2）單元格B15為目標函數(shù)單元格，存放最大利潤，計算公式為“=SUMPRODUCT(B14:D14,B3:D3-B4:D4)-B12〞。（3）在單元格E6中輸入公式“=SUMPRODUCT($B$14:$D$14,B6:D6)〞，然后將其復制到單元格E7和E8，分別計算三種產(chǎn)品在甲、乙和丙車間的使用工時合計。（4）單擊【工具】菜單中【規(guī)劃求解】項，在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標單元格】選“$B$15〞；【等于】選“最大值〞；【可變單元格】選“$B$14:$D$14〞；【約束】中添加以下約束條件：“$E$6<=$B$9〞、“$E$7<=$B$10〞、“$E$8<=$B$11〞、“$B$14<=$B$5〞、“$C$14<=$C$5〞、“$D$14<=$D$5〞、“$B$14:$D$14>=0〞、“$B$14:$D$14=整數(shù)〞。（5）單擊【求解】按鈕，即得在滿足上述約束條件下企業(yè)實現(xiàn)最大利潤的最正確產(chǎn)品結(jié)構(gòu)為：產(chǎn)品A：16429件，產(chǎn)品B：31429件，產(chǎn)品C：35000件，最大利潤416428元，如圖4-28所示?！纠?-16】在例4-15中，假設(shè)企業(yè)的目標利潤為35萬元，且產(chǎn)品A、B、C的最大產(chǎn)銷量均為30000件，如圖4-29所示，而且要求實現(xiàn)此目標利潤消耗最小總工時，則可得如下的目標利潤優(yōu)化模型：目標函數(shù)：min{總工時}=(5+3+4)Q1+(4+4+3)Q2+(4+5+4)Q3約束條件：(14-10)Q1+(20-16)Q2+(45-30)Q3-300000=3500005Q1+4Q2+4Q3≤4000003Q1+4Q2+5Q3≤3500004Q1+3Q2+4Q3≤3000000≤Q1≤300000≤Q2≤300000≤Q3≤30000Q1、Q2、Q3為整數(shù)單元格E6、E7和E8中的計算公式參見例4-29，而單元格B16中的計算公式為“=SUMPRODUCT(B3:D3-B4:D4,B15:D15)-B12〞，單元格B17中的公式為“=SUM(E6:E8)〞。圖4-29

目標利潤且總工時最少的優(yōu)化模型在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標單元格】選“$B$17〞；【等于】選“最小值〞；【可變單元格】選“$B$15:$D$15〞；【約束】中添加以下約束條件：“$E$6<=$B$9〞、“$E$7<=$B$10〞、“$E$8<=$B$11〞、“$B$15<=$B$5〞、“$C$15<=$C$5〞、“$D$15<=$D$5〞、“$B$15:$D$15>=0〞、“$B$15:$D$15=整數(shù)〞、“$B$16=$B$13〞；然后求解，即得最正確銷售量組合為：產(chǎn)品A：20000件，產(chǎn)品B：30000件，產(chǎn)品C：30000件，耗用總工時960000小時【例4-17】某企業(yè)同時生產(chǎn)A、B兩種產(chǎn)品，有關(guān)資料如圖4-30所示。假設(shè)產(chǎn)品A、B的預計銷售量分別為9000件和7000件（它們必須滿足工時限制條件），最高單價分別為22元/件和15元/件。當目標利潤為6萬元時，企業(yè)應如何控制變動本錢，才能實現(xiàn)6萬元的目標利潤？圖4-30

實現(xiàn)目標利潤的本錢控制優(yōu)化模型由條件，可得如下的本錢控制優(yōu)化模型：目標函數(shù)：max{變動本錢總額}=9000v1+7000v2約束條件：9000×(22-v1)+7000×(15-v2)-90000=600009000×6+7000×8≤1200009000×4+7000×9≤100000在單元格D4中輸入公式“=SUMPRODUCT(B7:C7,B4:C4)〞，在單元格B10中輸入公式“=SUMPRODUCT(B7:C7,B2:C2-B11:C11)-B8〞，在單元格B12中輸入公式“=SUMPRODUCT(B7:C7,B11:C11)〞。然后在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標單元格】選“$B$12〞；【等于】選“最大值〞；【可變單元格】選“$B$11:$C$11〞；【約束】中添加以下約束條件：“$B$10=$B$9〞、“$D$3<=$B$5〞、“$D$4<=$B$6〞；然后求解，即得到企業(yè)為實現(xiàn)目標利潤的最大變動本錢總額為153000元，從而產(chǎn)品A、B的最高單位變動本錢必須滿足不等式：9000v1+7000v2≤153000，這樣，只要給出任何一種產(chǎn)品的單位變動本錢，即可計算出另一種產(chǎn)品的最高單位變動本錢，例如，當產(chǎn)品A的單位變動本錢為10元/件時，則產(chǎn)品B的最高單位變動本錢為9元/件，也就是說，為實現(xiàn)6萬元的目標利潤，產(chǎn)品A、B的單位變動本錢分別不得超過10元和9元。圖4-30中的單位變動本錢為某次優(yōu)化計算的結(jié)果，它說明當產(chǎn)品A的單位變動本錢為10.59元/件時，產(chǎn)品B的單位變動本錢不能高于8.24元/件。因為取得最低變動本錢總額的產(chǎn)品A、B的單位變動本錢組合有無數(shù)個，當給予變動單元格B11:C11不同的初始值時，會得到不同的組合解，但目標函數(shù)（變動本錢總額）的結(jié)果是相同的。4.8.3

最優(yōu)生產(chǎn)決策模型在企業(yè)得到一批訂單后，應根據(jù)自身的具體條件來合理安排生產(chǎn)，以實現(xiàn)最大利潤。這類最優(yōu)生產(chǎn)決策問題，可以利用規(guī)劃求解工具來解決。下面舉例說明?！纠?-18】假定某企業(yè)某月接到客戶訂單如圖4-31所示，其中產(chǎn)品A和C來自客戶甲，客戶甲要求同時接受產(chǎn)品A和C，否則取消訂單；產(chǎn)品B和D來自客戶乙，客戶乙對產(chǎn)品無特殊要求，接受產(chǎn)品B、D均可。那么，根據(jù)圖4-31中企業(yè)的有關(guān)條件及各產(chǎn)品的有關(guān)資料，可以建立如下的最優(yōu)生產(chǎn)決策模型：圖4-31

最優(yōu)生產(chǎn)決策模型約束條件：9000×6×xA+6000×8×xB+3000×14×xC+2000×10×xD≤120000

9000×4×xA+6000×9×xB+3000×10×xC+2000×8×xD≤100000

xA–xC=0

xA、xB、xC、xD=0或1式中，xi為0、1變量，當xi為1時表示i產(chǎn)品被選中，當xi為0時表示i產(chǎn)品被拒絕。在單元格F6中輸入公式“=SUMPRODUCT(B9:E9,B11:E11,B6:E6)〞，在單元格B12中輸入公式“=SUMPRODUCT(B9:E9,B4:E4,B11:E11)-B10〞，在單元格B13中輸入公式“=B11-D11〞。在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標單元格】選“$B$12〞；【等于】選“最大值〞；【可變單元格】選“$B$11:$E$11〞；【約束】中添加以下約束條件：“$B$11:$E$11<=1〞、“$B$11:$E$11>=0〞、“$B$11:$E$11=整數(shù)〞、“$F$5<=$B$7〞、“$F$6<=$B$8〞、“$B$13=0〞。然后求解，得到計算結(jié)果為：xA=xC=xD=1，xB=0，即全部接受客戶甲的訂單，只接受客戶乙的產(chǎn)品D訂單，可實現(xiàn)利潤9萬元。4.8.4

目標利潤分析模型所謂目標利潤，是指企業(yè)在未來一定時期內(nèi)，經(jīng)過努力應該到達的最優(yōu)化利潤控制目標，它是企業(yè)經(jīng)營活動的動力和目標。當企業(yè)根據(jù)生產(chǎn)經(jīng)營中的實際情況規(guī)劃了目標利潤后，如何確保其完成顯得極為重要。通過建立目標利潤分析模型，即可以很方便地解決這類問題。在多品種生產(chǎn)的情況下，由于必須考慮很多約束條件，故可以建立保利模型，將銷售量、產(chǎn)品單價、單位變動本錢或固定本錢中的任意一個作為決策變量，利用規(guī)劃求解工具進行優(yōu)化計算，本書不再介紹。下面僅以單一品種生產(chǎn)的情況為例，介紹目標利潤的分析方法?！纠?-19】根據(jù)圖4-32所給的資料建立單一品種的目標利潤分析模型。圖4-32

目標利潤分析模型在單一品種情況下，可以建立目標利潤分析模型，如圖4-32所示，在目標數(shù)值一列中，各單元格的計算公式為：單元格C3：“=(C8+B6)/(B4-B5)〞；單元格C4：“=(C8+B6)/B3+B5〞；單元格C5：“=B4-(C8+B6)/B3〞；單元格C6：“=B3*(B4-B5)-C8〞。則由圖4-32可知，當銷售利潤由目前的50000元提高到70000元時，可采取的措施有：（1）將銷售量由目前的10000件提高到10571件；（2）將產(chǎn)品單價由目前的100元/件提高到102元/件；（3）將單位變動本錢由目前的65元/件降低到63元/件；（4）將固定本錢由目前的300000降低到280000元。通過目標利潤分析模型，可以很方便地找出實現(xiàn)目標利潤的努力方向。在實際生產(chǎn)經(jīng)營活動中，企業(yè)往往需要采取綜合措施來實現(xiàn)目標利潤，而不是僅靠一種措施，故需要進行綜合分析和平衡。4.8.5

不確定性本量利分析模型在傳統(tǒng)的本量利分析中，一般認為銷售量、單價、單位變動本錢和固定本錢在一定時期內(nèi)保持不變，因而是一種無風險確實定性分析。但在實際中，這些參數(shù)受很多因素的影響，市場的變化和企業(yè)內(nèi)部條件的變化都可能導致產(chǎn)品的銷售量、銷售價格、單位變動本錢和固定本錢發(fā)生變化，在這種情況下，有必要進行風險型本量利分析。進行風險型本量利分析的常用方法是聯(lián)合概率分析，即對銷售量、單價、單位變動本錢及固定本錢確定其可能發(fā)生的數(shù)值及發(fā)生的概率，然后根據(jù)其可能值及發(fā)生概率進行計算。當只有一個參數(shù)（比方只有銷售量）是不確定的，計算過程比較簡單，但當多個參數(shù)均不確定時，計算就非常煩瑣。假設(shè)我們估計的銷售量、單價、單位變動本錢及固定本錢的可能發(fā)生概率次數(shù)分別為6次、5次、5次和5次，就需要計算6×5×5×5=750個組合，計算工作量非常大。利用蒙特卡羅模擬方法則可以在Excel上很方便地進行這種分析和計算。下面結(jié)合實例予以說明?！纠?-20】某企業(yè)生產(chǎn)A產(chǎn)品的銷售量、單價、單位變動本錢及固定本錢均為非確定性因素，在未來可能到達的水平及有關(guān)的概率情況如圖4-33所示。則利用蒙特卡羅模擬在Excel上進行風險型本量利分析的方法和步驟如下：圖4-33

產(chǎn)品的有關(guān)資料（1）首先根據(jù)圖4-33各參數(shù)的累計概率確定隨機數(shù)范圍及對應的隨機數(shù)，以便于在Excel上進行模擬計算（見圖4-33）。（2）在單元格A26、C26、E26和G26中輸入隨機數(shù)公式RANDBETWEEN(0,99)，產(chǎn)生0～99的隨機數(shù)，如圖4-34所示；圖4-34

模擬計算過程（3）利用VLOOKUP函數(shù)尋找符合隨機數(shù)的銷售量、單價、單位變動本錢和固定本錢，公式分別為：單元格B26：“=VLOOKUP(A26,$D$3:$E$9,2)〞；單；單元格H26：“=VLOOKUP(G26,$I$12:$J$16,2)〞，如圖4-34所示。（4）在單元格I26中輸入利潤計算公式“=B26*(D26-F26)-H26〞；在單元格J26中輸入保本量計算公式“=H26/(D26-F26)〞；在單元格K26中輸入保利量計算公式“(H26+$B$19)/(D26-F26)〞，如圖4-34所示。（5）選中單元格區(qū)域A26:K26，拖動單元格區(qū)域右下角的填充柄向下一直拖動到第5025行，即進行5000次模擬計算。（6）在單元格N3:R9中存放模擬計算結(jié)果，如圖4-35所示，各單元格的計算公式分別為。圖4-35

模擬計算結(jié)果各參數(shù)的期望值：單元格N3：“=AVERAGE(I26:I5025)〞；單元格N4：“=AVERAGE(J26:J5025)〞；單元格N5：“=AVERAGE(K26:K5025)〞；單元格N6：“=AVERAGE(B26:B5025)〞；單元格N7：“=AVERAGE(D26:D5025)〞；單元格N8：“=AVERAGE(F26:F5025)〞；單元格N9：“=AVERAGE(H26:H5025)〞。各參數(shù)的標準差：單元格O3：“=STDEV(I26:I5025)〞；單元格O4：“=STDEV(J26:J5025)〞；單元格O5：“=STDEV(K26:K5025)〞；單元格O6：“=STDEV(B26:B5025)〞；單元格O7：“=STDEV(D26:D5025)〞。單元格O8：“=STDEV(F26:F5025)〞；單元格O9：“=STDEV(H26:H5025)〞。各參數(shù)的最小值：單元格P3：“=MIN(I26:I5025)〞；單元格P4：“=MIN(J26:J5025)〞；單元格P5：“=MIN(K26:K5025)〞；單元格P6：“=MIN(B26:B5025)〞；單元格P7：“=MIN(D26:D5025)〞；單元格P8：“=MIN(F26:F5025)〞；單元格P9：“=MIN(H26:H5025)〞。各參數(shù)的最大值：單元格Q3：“=MAX(I26:I5025)〞；單元格Q4：“=MAX(J26:J5025)〞；單元格Q5：“=MAX(K26:K5025)〞；單元格Q6：“=MAX(B26:B5025)〞；單元格Q7：“=MAX(D26:D5025)〞；單元格Q8：“=MAX(F26:F