Excel在財(cái)務(wù)預(yù)測(cè)中的應(yīng)用

1、第4章 Exccel在財(cái)務(wù)務(wù)預(yù)測(cè)中的應(yīng)應(yīng)用4.1 財(cái)務(wù)預(yù)預(yù)測(cè)概述 財(cái)務(wù)預(yù)測(cè)，是是指對(duì)企業(yè)未未來(lái)的收入、成成本、利潤(rùn)、現(xiàn)現(xiàn)金流量及融融資需求等財(cái)財(cái)務(wù)指標(biāo)所作作的估計(jì)和推推測(cè)。財(cái)務(wù)預(yù)預(yù)測(cè)是編制投投資和融資計(jì)計(jì)劃的基礎(chǔ)，是是公司制訂成成長(zhǎng)戰(zhàn)略的基基本要素。稱稱職的財(cái)務(wù)管管理人員應(yīng)該該能夠充分利利用公司的有有關(guān)信息資料料，預(yù)測(cè)公司司的財(cái)務(wù)需要要并做出相應(yīng)應(yīng)的安排。公公司成長(zhǎng)主要要由銷售增長(zhǎng)長(zhǎng)來(lái)決定，銷銷售增長(zhǎng)需要要相應(yīng)的資產(chǎn)產(chǎn)增長(zhǎng)，如果果企業(yè)已經(jīng)是是滿負(fù)荷運(yùn)轉(zhuǎn)轉(zhuǎn)，不僅流動(dòng)動(dòng)資產(chǎn)、而且且固定資產(chǎn)都都要增長(zhǎng)，而而資產(chǎn)增長(zhǎng)需需要相應(yīng)的融融資增長(zhǎng)。同同時(shí)，企業(yè)進(jìn)進(jìn)行對(duì)外投資資和調(diào)整資本本結(jié)構(gòu)，也需需要籌措資金

2、金。企業(yè)所需需要的這些資資金，一部分分來(lái)自企業(yè)內(nèi)內(nèi)部，另一部部分通過外部部融資取得。由由于對(duì)外融資資時(shí)，企業(yè)不不但需要尋找找資金提供者者，而且還需需做出還本付付息的承諾或或提供企業(yè)盈盈利前景等信信息，使資金金提供者確信信其投資是安安全的并可獲獲利，這個(gè)過過程往往需要要花費(fèi)較長(zhǎng)的的時(shí)間。因此此，企業(yè)需要要預(yù)先知道自自身的財(cái)務(wù)需需求，確定資資金的需要量量，提前安排排融資計(jì)劃，以以免影響資金金周轉(zhuǎn)。財(cái)務(wù)預(yù)測(cè)有助于于改善企業(yè)的的投資決策。雖雖然投資是決決定籌資與否否和籌資多少少的重要因素素，但是根據(jù)據(jù)銷售前景估估計(jì)出的融資資需求，并不不一定能夠得得到全部滿足足。這時(shí)，就就需要根據(jù)可可能籌措到的的資金來(lái)

3、安排排銷售增長(zhǎng)以以及有關(guān)的投投資項(xiàng)目，使使投資決策建建立在可行的的基礎(chǔ)上。財(cái)務(wù)預(yù)測(cè)一般按按以下幾個(gè)步步驟進(jìn)行。1銷售預(yù)測(cè)銷售預(yù)測(cè)是指根根據(jù)市場(chǎng)調(diào)查查所得到的有有關(guān)資料，通通過對(duì)有關(guān)因因素的分析研研究，預(yù)計(jì)和和測(cè)算特定產(chǎn)產(chǎn)品在未來(lái)一一定時(shí)期內(nèi)的的市場(chǎng)銷售量量水平及變化化趨勢(shì)，進(jìn)而而預(yù)測(cè)企業(yè)產(chǎn)產(chǎn)品未來(lái)銷售售量的過程。企企業(yè)的一切財(cái)財(cái)務(wù)需求都可可以看作是因因銷售引起的的，銷售量的的增減變化，將將會(huì)引起庫(kù)存存量、現(xiàn)金流流量、應(yīng)收與與應(yīng)付賬款以以及公司其他他資產(chǎn)和負(fù)債債的變化。因因此銷售預(yù)測(cè)測(cè)在企業(yè)預(yù)測(cè)測(cè)系統(tǒng)中處于于先導(dǎo)地位，它它對(duì)于指導(dǎo)利利潤(rùn)預(yù)測(cè)、成成本預(yù)測(cè)和資資金預(yù)測(cè)，進(jìn)進(jìn)行長(zhǎng)短期決決策，安排經(jīng)經(jīng)營(yíng)

4、計(jì)劃，組組織生產(chǎn)等都都起著重要的的作用。2估計(jì)收入、費(fèi)費(fèi)用和利潤(rùn)收入和費(fèi)用與銷銷售量之間也也存在一定的的函數(shù)關(guān)系，因因此，可以根根據(jù)銷售數(shù)據(jù)據(jù)估計(jì)收入和和費(fèi)用，并確確定凈利潤(rùn)。凈凈利潤(rùn)和股利利支付率，共共同決定了內(nèi)內(nèi)部留存收益益所能提供的的資金數(shù)額。3估計(jì)需要的的資產(chǎn)資產(chǎn)通常是銷售售收入的函數(shù)數(shù)，根據(jù)歷史史數(shù)據(jù)可以分分析出二者之之間的函數(shù)關(guān)關(guān)系。根據(jù)預(yù)預(yù)計(jì)銷售收入入和資產(chǎn)與銷銷售之間的函函數(shù)關(guān)系，可可以預(yù)測(cè)所需需資產(chǎn)的總量量。某些流動(dòng)動(dòng)負(fù)債也是銷銷售收入的函函數(shù)，相應(yīng)地地也可以預(yù)測(cè)測(cè)負(fù)債的自發(fā)發(fā)增長(zhǎng)額，這這種增長(zhǎng)可以以減少企業(yè)外外部融資的數(shù)數(shù)額。4估計(jì)所需融融資根據(jù)預(yù)計(jì)資產(chǎn)總總量，減去已已有的

5、資金來(lái)來(lái)源、負(fù)債的的自發(fā)增長(zhǎng)和和內(nèi)部提供的的留存收益，可可得出所需的的外部融資數(shù)數(shù)額。第4章 Exccel在財(cái)務(wù)務(wù)預(yù)測(cè)中的應(yīng)應(yīng)用4.2 財(cái)務(wù)預(yù)預(yù)測(cè)的分析方方法 預(yù)測(cè)分析的方法法有很多種，企企業(yè)應(yīng)根據(jù)不不同的需要選選擇不同的預(yù)預(yù)測(cè)方法?？偪偟膩?lái)說(shuō)，預(yù)預(yù)測(cè)分析方法法可分為兩大大類：定量預(yù)預(yù)測(cè)法和定性性預(yù)測(cè)法。4.2.1 定量預(yù)測(cè)法法定量預(yù)測(cè)法是指指在掌握與預(yù)預(yù)測(cè)對(duì)象有關(guān)關(guān)的各種要素素的定量資料料的基礎(chǔ)上，運(yùn)運(yùn)用現(xiàn)代數(shù)學(xué)學(xué)方法進(jìn)行數(shù)數(shù)據(jù)處理，從從而建立起能能夠反映有關(guān)關(guān)變量之間關(guān)關(guān)系的各類預(yù)預(yù)測(cè)模型的方方法。在財(cái)務(wù)預(yù)測(cè)中，經(jīng)經(jīng)常使用的定定量預(yù)測(cè)法主主要有以下幾幾種。4.2.1.11 移動(dòng)平平均法移動(dòng)

6、平均法是一一種改良的算算術(shù)平均法，是是一種最簡(jiǎn)單單的自適應(yīng)預(yù)預(yù)測(cè)模型。它它根據(jù)近期數(shù)數(shù)據(jù)對(duì)預(yù)測(cè)值值影響較大，而而遠(yuǎn)期數(shù)據(jù)對(duì)對(duì)預(yù)測(cè)值影響響較小的事實(shí)實(shí)，把平均數(shù)數(shù)逐期移動(dòng)。移移動(dòng)期數(shù)的大大小視具體情情況而定，移移動(dòng)期數(shù)少，能能快速地反映映變化，但不不能反映變化化趨勢(shì)；移動(dòng)動(dòng)期數(shù)多，能能反映變化趨趨勢(shì)，但預(yù)測(cè)測(cè)值帶有明顯顯的滯后偏差差。常用的移動(dòng)平均均法主要有一一次移動(dòng)平均均法和二次移移動(dòng)平均法。1一次移動(dòng)平平均法一次移動(dòng)平均法法是根據(jù)時(shí)間間序列，逐期期移動(dòng)，依次次計(jì)算包含一一定項(xiàng)數(shù)的時(shí)時(shí)間序列平均均數(shù)，形成一一個(gè)平均時(shí)間間數(shù)序列，并并據(jù)此進(jìn)行預(yù)預(yù)測(cè)。預(yù)測(cè)模模型為式中第t+1期的預(yù)測(cè)測(cè)值；、將被被

7、平均的n個(gè)觀測(cè)值；n移動(dòng)平均的的項(xiàng)數(shù)，即移移動(dòng)期數(shù)。在實(shí)際預(yù)測(cè)中，可可以多取幾個(gè)個(gè)n數(shù)，并將得得到的預(yù)測(cè)值值與實(shí)際值進(jìn)進(jìn)行比較，選選用誤差最小小的n值。2二次移動(dòng)平平均法二次移動(dòng)平均法法是對(duì)時(shí)間序序列計(jì)算一次次移動(dòng)平均數(shù)數(shù)后，再對(duì)一一次移動(dòng)平均均數(shù)序列進(jìn)行行一次移動(dòng)平平均運(yùn)算。預(yù)預(yù)測(cè)模型為。式中二次移移動(dòng)平均數(shù)；第t+1期的的預(yù)測(cè)值，即即。二次移動(dòng)平均法法解決了一次次移動(dòng)平均法法只能預(yù)測(cè)下下一期的局限限性，它可以以進(jìn)行近、短短期的預(yù)測(cè)。但但它仍不能解解決中長(zhǎng)期的的預(yù)測(cè)問題。4.2.1.22 指數(shù)平平滑法指數(shù)平滑法實(shí)際際上也是一種種加權(quán)平均法法，是一種改改良的加權(quán)平平均法，預(yù)測(cè)測(cè)模型為式中 平滑

8、滑系數(shù)，01。在指數(shù)平滑法中中，確定合適適的值和初始始值是非常重重要的。越大大，t期的實(shí)際值值對(duì)新預(yù)測(cè)值值的貢獻(xiàn)就越越大；越小，t期的實(shí)際值值對(duì)新預(yù)測(cè)值值的貢獻(xiàn)就越越小。一般情情況下，可以以取幾個(gè)不同同的值進(jìn)行預(yù)預(yù)測(cè)，比較它它們的預(yù)測(cè)誤誤差，選擇預(yù)預(yù)測(cè)誤差最小小的值。4.2.1.33 回歸分分析預(yù)測(cè)法回歸分析預(yù)測(cè)法法是通過研究究?jī)山M或兩組組以上變量之之間的關(guān)系，建建立相應(yīng)的回回歸預(yù)測(cè)模型型，對(duì)變量進(jìn)進(jìn)行預(yù)測(cè)的一一種預(yù)測(cè)方法法。1回歸分析預(yù)預(yù)測(cè)法的基本本程序進(jìn)行回歸分析的的步驟如下：（1）收集有關(guān)關(guān)資料。將各各種可能的影影響因素的有有關(guān)數(shù)據(jù)盡可可能多地收集集起來(lái)。（2）判斷趨勢(shì)勢(shì)。根據(jù)收集集到的

9、數(shù)據(jù)，判判斷其變化趨趨勢(shì)，從而為為建立相應(yīng)的的數(shù)學(xué)模型做做準(zhǔn)備。對(duì)于于變量不多的的問題，可以以通過繪制散散點(diǎn)圖來(lái)判斷斷變化趨勢(shì)。（3）建立預(yù)測(cè)測(cè)數(shù)學(xué)模型。根根據(jù)歷史數(shù)據(jù)據(jù)的變化趨勢(shì)勢(shì)，選擇相應(yīng)應(yīng)的描寫該問問題的數(shù)學(xué)模模型，并采用用相關(guān)的計(jì)算算技術(shù)來(lái)估計(jì)計(jì)數(shù)學(xué)模型的的參數(shù)。（4）相關(guān)檢驗(yàn)驗(yàn)。對(duì)建立的的預(yù)測(cè)數(shù)學(xué)模模型，必須進(jìn)進(jìn)行有關(guān)的檢檢驗(yàn)，主要是是通過計(jì)算預(yù)預(yù)測(cè)模型的相相關(guān)系數(shù)、方方差（或標(biāo)準(zhǔn)準(zhǔn)差）以及顯顯著性等指標(biāo)標(biāo)，來(lái)判斷預(yù)預(yù)測(cè)模型的準(zhǔn)準(zhǔn)確性、是否否需要修正、采采用何種方法法修正等。2回歸模型建建立的方法建立回歸模型的的一般方法是是采用最小二二乘法，其原原理如下：考慮m個(gè)自變量量x1、x2、

10、x m和因變量量y的關(guān)系，現(xiàn)現(xiàn)有n組觀測(cè)數(shù)據(jù)據(jù)，不同xki （k=1,2,m；i=1,2,n）下的y的觀測(cè)值為為yi，函數(shù)y=f(xk)的待估計(jì)參參數(shù)為ak（k=1,2,m+1，這里，每每個(gè)自變量有有一個(gè)待估計(jì)計(jì)系數(shù)，還有有一個(gè)待估計(jì)計(jì)常數(shù)，故有有m+1個(gè)待估計(jì)計(jì)參數(shù)），通通過回歸預(yù)測(cè)測(cè)模型得到不不同xki下的預(yù)測(cè)測(cè)值為 ，則有：殘差平方和SEE：剩余標(biāo)準(zhǔn)差SSS：相關(guān)系數(shù)R2：y為觀測(cè)值yii的平均值：那么，最小二乘乘法的原理就就是尋找最優(yōu)優(yōu)的待估計(jì)參參數(shù)ak，使殘差平平方和最小。3財(cái)務(wù)預(yù)測(cè)中中常用的幾種種回歸模型（1）一元線性性回歸模型當(dāng)只有兩個(gè)變量量（一個(gè)自變變量和一個(gè)因因變量），并并且

11、它們之間間存在線性關(guān)關(guān)系時(shí)，可以以用一元線性性回歸模型來(lái)來(lái)描述。一元元線性回歸模模型為式中a、b回歸系數(shù)，其其中a代表截距，bb代表斜率。（2）一元非線線性回歸模型型當(dāng)變量x和y之之間的關(guān)系不不能用線性關(guān)關(guān)系來(lái)描述時(shí)時(shí)，則需要建建立一元非線線性回歸模型型。根據(jù)變量量x和y之間的關(guān)系系，一元非線線性回歸模型型常見的幾種種情況有：對(duì)數(shù)模型：指數(shù)模型：乘冪模型：雙曲線模型：以上幾種一元非非線性模型均均可通過數(shù)學(xué)學(xué)變換化成一一元線性模型型。（3）多元線性性回歸模型當(dāng)自變量有兩個(gè)個(gè)或兩個(gè)以上上，且因變量量與這些自變變量之間呈線線性組合關(guān)系系時(shí)，它們就就構(gòu)成了多元元線性回歸模模型，模型形形式為式中a、b1

12、1、b2、bm估計(jì)參數(shù)；x1、x2、xm自變量。（4）多元非線線性回歸模型型多元非線性回歸歸模型用來(lái)描描述因變量與與多個(gè)自變量量之間呈非線線性組合關(guān)系系的情況。例例如，柯柏道格拉斯生生產(chǎn)函數(shù)就是是典型的多元元非線性模型型：式中：L和K分分別為勞動(dòng)力力和固定資本本；a、b、c為系數(shù)。4.2.1.44 模擬法法在企業(yè)的實(shí)際經(jīng)經(jīng)濟(jì)活動(dòng)中，各各種經(jīng)濟(jì)參數(shù)數(shù)往往并不是是確定的，而而是隨機(jī)變化化的，比如產(chǎn)產(chǎn)品的銷售量量往往隨市場(chǎng)場(chǎng)的變化而變變化，在這種種情況下，就就需要對(duì)這些些參數(shù)的不確確定性進(jìn)行分分析，而對(duì)其其預(yù)測(cè)也就需需要采用與傳傳統(tǒng)的確定性性分析不同的的方法來(lái)進(jìn)行行。一般情況況下，可以采采用模擬法來(lái)

13、來(lái)解決不確定定性情況下的的財(cái)務(wù)預(yù)測(cè)問問題，概率法法、蒙特卡羅羅模擬方法就就是較實(shí)用的的方法。4.2.2 定性預(yù)測(cè)法法定性預(yù)測(cè)法是由由有關(guān)方面的的專業(yè)人員或或?qū)＜腋鶕?jù)自自己的經(jīng)驗(yàn)和和知識(shí)，結(jié)合合預(yù)測(cè)對(duì)象的的特點(diǎn)進(jìn)行綜綜合分析，對(duì)對(duì)事物的未來(lái)來(lái)狀況和發(fā)展展趨勢(shì)作出推推測(cè)的預(yù)測(cè)方方法。定性預(yù)預(yù)測(cè)法由于帶帶有較多的個(gè)個(gè)人主觀性，因因而在實(shí)踐中中最好作為一一種補(bǔ)充的預(yù)預(yù)測(cè)方法。第4章 Exccel在財(cái)務(wù)務(wù)預(yù)測(cè)中的應(yīng)應(yīng)用4.3 Exccel中的有有關(guān)預(yù)測(cè)函數(shù)數(shù)及其應(yīng)用(1) Excel提供供了關(guān)于估計(jì)計(jì)線性模型和和指數(shù)模型參參數(shù)的幾個(gè)預(yù)預(yù)測(cè)函數(shù)。線線性模型和指指數(shù)模型的數(shù)數(shù)學(xué)表達(dá)式如如下：線性模型：y =

14、 mx + b 或 y = m1x1 + m2x2 + + b指數(shù)模型：或式中，y為因變變量；x是自變量；m、m1、.、mn-1、mn、b分別為預(yù)測(cè)測(cè)模型的待估估計(jì)參數(shù)。Excel提供供的預(yù)測(cè)函數(shù)數(shù)主要有LIINEST函函數(shù)、LOGGEST函數(shù)數(shù)、TRENND函數(shù)、GROOWTH函數(shù)數(shù)、FOREECAST函函數(shù)、SLOOPE函數(shù)和和INTERRCEPT函函數(shù)，它們所所使用的參數(shù)數(shù)都基本相同同，現(xiàn)列于表表4-1中，以以供參考。表表4-1 預(yù)測(cè)函數(shù)的的參數(shù)及含義義參數(shù)含義known_yys因變量y的觀測(cè)測(cè)值集合known_xxs自變量x的觀測(cè)測(cè)值集合。它它可以是一個(gè)個(gè)變量（即一一元模型）或或多個(gè)變

15、量（即即多元模型）的的集合。如果只用到一個(gè)個(gè)變量，只要要 knowwn-ys 和 knowwn-xs 維數(shù)數(shù)相同，它們們可以是任何何形狀的選定定區(qū)域。如果果用到不只一一個(gè)變量，kknown_ys 必必須是向量（也也就是說(shuō)，必必須是一行或或一列的區(qū)域域）。如果省省略 knoown_xs，則假設(shè)設(shè)該數(shù)組是 1,2,3.，其大小與與 knowwn_yss 相同const邏輯值，指明是是否強(qiáng)制使常常數(shù)b為0（線性模型型）或?yàn)?（指數(shù)模型型）。 如果consst 為 TRUEE或省略，b將被正常計(jì)計(jì)算。如果cconst為為FALSEE，b將被設(shè)為0（線性模型型）或設(shè)為11（指數(shù)模型型）stats邏輯值，

16、指明是是否返回附加加回歸統(tǒng)計(jì)值值。 如果 staats 為 TRUEE，則函數(shù)返返回附加回歸歸統(tǒng)計(jì)值，這這時(shí)返回的數(shù)數(shù)組為 mmn，mn-1，.，m1，b；sen，sen-1，.，se1，seb，r2，sey；F，df；ssreg，ssresiid。如果 sttats為FALSEE或省略，函函數(shù)只返回系系數(shù)預(yù)測(cè)模型型的待估計(jì)參參數(shù)m、mn、mn-1、.、m1和b。附加回歸統(tǒng)計(jì)值值返回的順序序見表4-22。表4-2中的各各參數(shù)說(shuō)明見見表4-3。如果要得到附加加回歸統(tǒng)計(jì)值值數(shù)組中的值值，需用INNDEX函數(shù)數(shù)將其取出表4-2 附附加回歸統(tǒng)計(jì)計(jì)值返回的順順序1234561mnmn-1m2m1b2se

17、nsen-1se2se1se b3r2sey4Fdf5ssregssresidd表4-3 各各參數(shù)說(shuō)明參數(shù)說(shuō)明se1,se22,.,seen系數(shù) m1,mm2, .,mmn 的標(biāo)準(zhǔn)誤差差值Seb常數(shù)項(xiàng) b 的的標(biāo)準(zhǔn)誤差值值（當(dāng) coonst 為為 FALSSE時(shí)，seb = #NN/A ）參數(shù)說(shuō)明r2相關(guān)系數(shù)，范圍圍在 0 到 1 之間。如如果為 1，則樣本本有很好的相相關(guān)性，Y 的估計(jì)值與與實(shí)際值之間間沒有差別。反反之，如果相相關(guān)系數(shù)為 0，則回歸歸方程不能用用來(lái)預(yù)測(cè) YY 值seyY 估計(jì)值的標(biāo)標(biāo)準(zhǔn)誤差FF 統(tǒng)計(jì)值或FF 觀察值。使使用F 統(tǒng)計(jì)可以以判斷因變量量和自變量之之間是否偶爾爾發(fā)生

18、過觀察察到的關(guān)系Df自由度。用于在在統(tǒng)計(jì)表上查查找 F 臨界值值。所查得的的值和函數(shù) LINESST 返回的的F統(tǒng)計(jì)值的比比值可用來(lái)判判斷模型的置置信度ssreg回歸平方和ssresidd殘差平方4.3.1 LINESST函數(shù)LINEST函函數(shù)的功能是是使用最小二二乘法計(jì)算對(duì)對(duì)已知數(shù)據(jù)進(jìn)進(jìn)行最佳線性性擬合的直線線方程，并返返回描述此線線性模型的數(shù)數(shù)組。因?yàn)榇舜撕瘮?shù)返回?cái)?shù)數(shù)值為數(shù)組，故故必須以數(shù)組組公式的形式式輸入。函數(shù)公式為= LINESST（knownn_ys，knownn_xs，constt，statss）下面舉例說(shuō)明LLINESTT函數(shù)的應(yīng)用用。1一元線性回回歸分析LINEST函函數(shù)可用

19、于一一元線性回歸歸分析，也可可以用于多元元線性回歸分分析，以及時(shí)時(shí)間數(shù)列的自自回歸分析。當(dāng)只有一個(gè)自變變量 x （即一一元線性回歸歸分析）時(shí)，可可直接利用下下面的公式得得到斜率和 y 軸的截截距值以及相相關(guān)系數(shù)： 斜率：INDEEX（LINESST（knownn_ys，knownn_xs）,1,1）；或INDDEX（LINESST（knownn_ys,knownn_xs）,1）截距：INDEEX（LINESST（knownn_ys，knownn_xs）,1,2）；或INDEEX（LINESST（knownn_ys,knownn_xs）,2）相關(guān)系數(shù)：INNDEX（LINESST（knownn_

20、ys,knownn_xs,true,true）,3,1）【例4-1】某某企業(yè)19月份的總成成本與人工小小時(shí)及機(jī)器工工時(shí)的數(shù)據(jù)如如圖4-1所示。假假設(shè)總成本與與人工小時(shí)之之間存在著線線性關(guān)系，則則在單元格BB13中插入入公式“=INDEEX(LINNEST(BB2:B10,DD2:D10),2)”，在單元格格B14插入公公式“=INDEEX(LINNEST(BB2:B10,DD2:D10),1)”，在單元格格B15插入公公式“=INDEEX(LINNEST(BB2:B10,DD2:D10,TTRUE,TTRUE),3,1)”，即得總成成本與人工小小時(shí)的一元線線性回歸分析析方程為：YY=562.7

21、27566+4.411444X11，相關(guān)系數(shù)數(shù)為R2=0.999801，如如圖4-1所示。圖4-1 一一元線性回歸歸分析2多元線性回回歸分析仍以例4-1的的數(shù)據(jù)為例，首首先選取單元元格區(qū)域A117:D21，再以以數(shù)組公式方方式輸入公式式“=LINEEST(B22:B10,CC2:D10,TTRUE,TTRUE)”，即得該二二元線性回歸歸的有關(guān)參數(shù)數(shù)如圖4-22所示，從而而得到：圖4-2 二二元線性回歸歸分析回歸方程：Y = 4711.43666+3.61165X1+3.43323X2相關(guān)系數(shù)：R22 =0.99990標(biāo)準(zhǔn)差：Seyy =11.7792。4.3.2 LOGESST函數(shù)LOGEST

22、函函數(shù)的功能是是在回歸分析析中，計(jì)算最最符合觀測(cè)數(shù)數(shù)據(jù)組的指數(shù)數(shù)回歸擬合曲曲線，并返回回描述該指數(shù)數(shù)模型的數(shù)組組。由于這個(gè)個(gè)函數(shù)返回一一個(gè)數(shù)組，必必須以數(shù)組公公式輸入。LOGEST函函數(shù)的公式為為= LOGESST(knoown_ys,knoown_xs,connst,sttats) 【例4-2】某某企業(yè)12個(gè)月某產(chǎn)產(chǎn)品的生產(chǎn)量量（X）與生產(chǎn)成成本（Y）的有關(guān)資資料如圖4-3所示，假假設(shè)它們之間間有如下關(guān)系系：。選取單單元格區(qū)域BB15:C18，輸入入公式“=LOGEEST(C22:C13,BB2:B13,TTRUE,TTRUE)”（數(shù)組公式式輸入），即即得回歸參數(shù)數(shù)，如圖4-3所示，參參數(shù)m

23、=0.8887，參參數(shù)b=18891.77729，生產(chǎn)產(chǎn)成本與生產(chǎn)產(chǎn)量的回歸曲曲線為：Y=1791.77290.88887X，相關(guān)系數(shù)數(shù)R2=0.955885。圖4-3 指指數(shù)回歸回歸方程的系數(shù)數(shù)及相關(guān)系數(shù)數(shù)也可以利用用下面的公式式直接計(jì)算參數(shù)m：INDDEX(LOOGEST(C2:C13,BB2:B13),1)=0.8887參數(shù)b：INDDEX(LOOGEST(C2:C13,BB2:B13),1,2)=1791.7729：EGCBTT,=54.3.3 TRENDD函數(shù)TREND函數(shù)數(shù)的功能是返返回一條線性性回歸擬合線線的一組縱坐坐標(biāo)值（y 值），即找找到適合給定定的數(shù)組 kknown_ys

24、和和 knowwn_xss 的直線（用用最小二乘法法），并返回回指定數(shù)組 new_xxs 值在在直線上對(duì)應(yīng)應(yīng)的 y 值。TREND函數(shù)數(shù)的公式為= TRENDD(knowwn_yss,knowwn_xss,new_xs,cconst)式中 neww_xs 需要函數(shù) TTREND 返回對(duì)應(yīng) yy 值的新 x 值。 neww_xs 與 knowwn_xss 一樣，每每個(gè)獨(dú)立變量量必須為單獨(dú)獨(dú)的一行（或或一列）。因因此，如果 knownn_ys 是單列的，kknown_xs 和和 new_xs 應(yīng)應(yīng)該有同樣的的列數(shù)，如果果 knowwn_yss 是單行的的，knowwn_xss 和 new_xs 應(yīng)

25、應(yīng)該有同樣的的行數(shù)。如果果省略 neew_xss，將假設(shè)它它和 knoown_xs 一樣?！纠?-3】某某企業(yè)過去一一年的銷售量量為下列數(shù)據(jù)據(jù)：3000，356，374，410，453，487，501，534，572，621，650，670，將將它們保存在在單元格A11:A12中，則則下一年的11、2、3月的銷售量量預(yù)測(cè)步驟為為：選中單元元格區(qū)域B11:B3，輸入公公式“=TRENND(A1:A12,13;114;15)”（數(shù)組公式式輸入），即即得來(lái)年的11、2、3月份的銷售售量分別為7710、743和777。這個(gè)個(gè)公式默認(rèn)1;2;33;4;5;6;7;88;9;100;11;112作為kno

26、wwn_xss的參數(shù)，故故數(shù)組133;14;115就對(duì)應(yīng)應(yīng)其后的3個(gè)月份。4.3.4 GROWTTH函數(shù)GROWTH函函數(shù)的功能是是返回給定的的數(shù)據(jù)預(yù)測(cè)的的指數(shù)增長(zhǎng)值值。根據(jù)已知知的x值和y值，函數(shù)GRROWTH返返回一組新的的x值對(duì)應(yīng)的y值?？梢允故褂肎ROWWTH工作表表函數(shù)來(lái)擬合合滿足給定xx值和y值的指數(shù)曲曲線。GROWTH函函數(shù)的公式為為= GROWTTH(knoown_ys,knoown_xs,neww_xs,constt)式中，各參數(shù)的的含義同TRREND函數(shù)數(shù)。但需注意意的是，如果果knownn_ys中中的任何數(shù)為為零或?yàn)樨?fù)，函函數(shù) GROOWTH將返返回錯(cuò)誤值 #NUM!。

27、【例4-4】以以例4-3的資料料為例，利用用GROWTTH函數(shù)預(yù)測(cè)測(cè)來(lái)年的1、2、3月的銷售量量。預(yù)測(cè)步驟驟為：選中單單元格區(qū)域BB1:B3，輸入公公式“=GROWWTH(A11:A12,13;114;15)”（數(shù)組公式式輸入），即即得來(lái)年的11、2、3月份的銷售售量分別為7756、811和870。這個(gè)個(gè)公式同樣默默認(rèn)1;22;3;4;5;6;77;8;9;10;111;12作作為knowwn_xss的參數(shù)，故故數(shù)組133;14;115就對(duì)應(yīng)應(yīng)后面的3個(gè)月份。4.3.5 FORECCAST函數(shù)數(shù)FORECASST函數(shù)的功功能是根據(jù)給給定的數(shù)據(jù)計(jì)計(jì)算或預(yù)測(cè)未未來(lái)值。此預(yù)預(yù)測(cè)值為基于于一系列已知知

28、的 x 值推導(dǎo)導(dǎo)出的 y 值。以數(shù)組組或數(shù)據(jù)區(qū)域域的形式給定定 x 值和 y 值后，返返回基于 xx 的線性回回歸預(yù)測(cè)值。FORECAST函數(shù)的計(jì)算公式為 a+bx式中，；。FORECASST函數(shù)的公公式為= FORECCAST(xx,knowwn_yss,knowwn_xss)式中x需要要進(jìn)行預(yù)測(cè)的的數(shù)據(jù)點(diǎn)。需要說(shuō)明的是： 如果 x 為非非數(shù)值型，函函數(shù) FORRECASTT 返回錯(cuò)誤誤值 #VAALUE!。如果 knowwn_yss 和 knowwn_xss 為空或含含有不同數(shù)目目的數(shù)據(jù)點(diǎn)，函函數(shù) FORRECASTT 返回錯(cuò)誤誤值 #N/A。如果 knowwn_xss 的方差為為零，函數(shù)

29、 FORECCAST 返返回錯(cuò)誤值 #DIV/0!。例如：FOREECAST(30,66,7,9,15,211,200,28,331,38,40) = 10.607255。4.3.6 SLOPEE函數(shù)SLOPE函數(shù)數(shù)的功能是返返回根據(jù) kknown_ys 和和 knowwn_xss 中的數(shù)據(jù)據(jù)點(diǎn)擬合的線線性回歸直線線的斜率。斜斜率為直線上上任意兩點(diǎn)的的垂直距離與與水平距離的的比值，也就就是回歸直線線的變化率。SLOPE函數(shù)數(shù)的公式為 = SLOPEE(knowwn_yss,knowwn_xss)說(shuō)明：參數(shù)可以以是數(shù)字，或或者是涉及數(shù)數(shù)字的名稱、數(shù)數(shù)組或引用。如如果數(shù)組或引引用參數(shù)里包包含文本、

30、邏邏輯值或空白白單元格，這這些值將被忽忽略。但包含含零值的單元元格將計(jì)算在在內(nèi)。如果 knownn_ys 和 knowwn_xss 為空或其其數(shù)據(jù)點(diǎn)數(shù)目目不同，函數(shù)數(shù) SLOPPE 返回錯(cuò)錯(cuò)誤值 #NN/A。例如：SLOPPE(2,3,9,11,8,7,5,66,5,111,7,5,4,4) = 0.3055556。4.3.7 INTERRCEPT函函數(shù)INTERCEEPT函數(shù)的的功能是利用用已知的 xx 值與 y 值計(jì)算算直線與 yy 軸的截距距。截距為穿穿過 knoown_xs 和 knowwn_yss 數(shù)據(jù)點(diǎn)的的線性回歸線線與 y 軸的交交點(diǎn)。公式為 = INTERRCEPT (know

31、wn_yss,knowwn_xss)例如：INTEERCEPTT(2, 3, 9, 1, 88, 66, 5, 11, 77, 5) = 0.04838871。第4章 Exccel在財(cái)務(wù)務(wù)預(yù)測(cè)中的應(yīng)應(yīng)用4.4 利用數(shù)數(shù)據(jù)分析工具具解決預(yù)測(cè)問問題(1) 除了利用前面介介紹的幾個(gè)預(yù)預(yù)測(cè)函數(shù)進(jìn)行行回歸預(yù)測(cè)分分析外，我們們還可以使用用Excell的數(shù)據(jù)分析析工具庫(kù)提供供的統(tǒng)計(jì)觀測(cè)測(cè)分析工具來(lái)來(lái)解決回歸預(yù)預(yù)測(cè)問題。Excel的數(shù)數(shù)據(jù)分析工具具庫(kù)提供了33種統(tǒng)計(jì)觀測(cè)測(cè)分析工具，它它們是移動(dòng)平平均法、指數(shù)數(shù)平滑法和回回歸分析法。下下面結(jié)合實(shí)例例來(lái)說(shuō)明這33種方法的具具體應(yīng)用。4.4.1 移動(dòng)平均法法【例4-

32、5】某某企業(yè)20000年12個(gè)月的銷銷售額如圖44-4所示，分分別按3期、5期和7期移動(dòng)平均均所做的預(yù)測(cè)測(cè)分析如圖44-4中的C4E13區(qū)域所所示。以3期移動(dòng)平均均為例為例，具具體計(jì)算步驟驟如下：圖4-4 一一次移動(dòng)平均均法實(shí)例（1）從【工具具】菜單中選選中【數(shù)據(jù)分分析】命令，則則彈出【數(shù)據(jù)據(jù)分析】對(duì)話話框，如圖44-5所示。圖4-5 【數(shù)數(shù)據(jù)分析】對(duì)對(duì)話框（2）在【數(shù)據(jù)據(jù)分析】對(duì)話話框中的【分分析工具】框框中選中【移移動(dòng)平均】選選項(xiàng)，則彈出出【移動(dòng)平均均】對(duì)話框，如如圖4-6所示。圖4-6 【移移動(dòng)平均】對(duì)對(duì)話框（3）在【移動(dòng)動(dòng)平均】對(duì)話話框中，【輸輸入?yún)^(qū)域】框框中輸入“$B$2$B$133

33、”，【間隔】框框中輸入“3”，【輸出區(qū)區(qū)域】框中輸輸入“$C$2”，最后選中中【圖表輸出出】選項(xiàng)；（4）單擊【確確定】按鈕，則則運(yùn)算結(jié)果就就顯示在單元元格區(qū)域C44:C13中，如如圖4-4所示（圖圖中的第133行預(yù)測(cè)數(shù)據(jù)據(jù)即為下月即即第13月的預(yù)測(cè)測(cè)值），并自自動(dòng)出現(xiàn)輸出出圖表，如圖圖4-7所示。圖4-7 移動(dòng)動(dòng)期數(shù)為3時(shí)的輸出圖圖表用同樣的方法，可可以分析當(dāng)移移動(dòng)期數(shù)為55和7時(shí)的分析結(jié)結(jié)果，如圖44-4所示。4.4.2 指數(shù)平滑法法【例4-6】某某企業(yè)的有關(guān)關(guān)銷售數(shù)據(jù)如如圖4-8所示，利利用指數(shù)平滑滑法進(jìn)行預(yù)測(cè)測(cè)分析，其步步驟如下：圖4-8 指數(shù)數(shù)平滑法實(shí)例例（1）從【工具具】菜單中選選中

34、【數(shù)據(jù)分分析】命令，則則彈出【數(shù)據(jù)據(jù)分析】對(duì)話話框，在【數(shù)數(shù)據(jù)分析】對(duì)對(duì)話框中的【分分析工具】框框中選中【指指數(shù)平滑】選選項(xiàng)，則彈出出【指數(shù)平滑滑】對(duì)話框，如如圖4-9所示。圖4-9 【指指數(shù)平滑】對(duì)對(duì)話框（2）在【指數(shù)數(shù)平滑】對(duì)話話框中，【輸輸入?yún)^(qū)域】框框中輸入“$B$2:$B$133”，【阻尼系系數(shù)】框中輸輸入“0.2”，【輸出區(qū)區(qū)域】框中輸輸入“$C$3”，最后選中中【圖表輸出出】選項(xiàng)。（3）單擊【確確定】按鈕，則則運(yùn)算結(jié)果就就顯示在單元元格區(qū)域C33:C13中（圖圖中的第133行預(yù)測(cè)數(shù)據(jù)據(jù)即為下月即即第13月的預(yù)測(cè)測(cè)值），如圖圖4-8所示，并并自動(dòng)出現(xiàn)輸輸出圖表，如如圖4-100所示。

35、圖4-10 指數(shù)平滑法法預(yù)測(cè)輸出圖圖（阻尼系數(shù)數(shù)0.2）用同樣的方法，可可以分析當(dāng)阻阻尼系數(shù)為00.4和0.6時(shí)的分分析結(jié)果如圖圖4-8所示。需要注意的是，【數(shù)數(shù)據(jù)分析】中中的指數(shù)平滑滑法所使用的的阻尼系數(shù)并并不是4.22.1.2節(jié)節(jié)介紹的指數(shù)數(shù)平滑法預(yù)測(cè)測(cè)方程中的平平滑系數(shù)，二二者的關(guān)系為為：阻尼系數(shù)數(shù)=1。4.4.3 回歸法利用Excell的回歸工具具進(jìn)行預(yù)測(cè)分分析有兩種方方法：一是圖圖表法；二是是回歸分析法法。4.4.3.11 圖表法法圖表法僅能解決決一元線性或或非線性回歸歸問題，不能能解決多元回回歸問題?！纠?-7】某某企業(yè)連續(xù)99年的產(chǎn)品銷銷售收入Y（萬(wàn)元）與與廣告支出XX1（萬(wàn)元）

36、和和居民平均收收入X2（元）的有有關(guān)數(shù)據(jù)如圖圖4-11所示示，則利用圖圖表法進(jìn)行回回歸分析，其其方法和步驟驟如下，這里里僅以銷售收收入Y（萬(wàn)元）與與廣告支出XX1（萬(wàn)元）的的一元線性關(guān)關(guān)系為例：圖4-11 某企業(yè)的有有關(guān)銷售數(shù)據(jù)據(jù)（1）選擇單元元格區(qū)域B22:C10。（2）單擊工具具欄上的【圖圖表導(dǎo)向】按按鈕，在【圖圖表導(dǎo)向4步驟之1圖表類型】中中選“XY散點(diǎn)圖”，其【子圖圖表類型】選選第1種，如圖4-12所示。圖4-12 準(zhǔn)備作散點(diǎn)點(diǎn)圖（3）單擊【下下一步】按鈕鈕，出現(xiàn)【圖圖表導(dǎo)向4步驟之2圖表源數(shù)據(jù)據(jù)】對(duì)話框，單單擊【系列】，在在【名稱】欄欄中填入“銷售收入”，在【X值】欄中輸輸入“=Sh

37、eeet1!$CC$2:$C$100”，在【Y值】欄中輸輸入“=Sheeet1!$BB$2:$B$100”（用鼠標(biāo)拾拾取單元格區(qū)區(qū)域），如圖圖4-13所示示。圖4-13 填入源數(shù)據(jù)據(jù)（4）單擊【下下一步】按鈕鈕，出現(xiàn)【圖圖表導(dǎo)向4步驟之3圖表選項(xiàng)】對(duì)對(duì)話框，填入入各標(biāo)題文字字，如圖4-14所示。圖4-14 填入各標(biāo)題題文字（5）單擊【下下一步】按鈕鈕，出現(xiàn)【圖圖表導(dǎo)向4步驟之4圖表位置】對(duì)對(duì)話框，不作作任何輸入，單單擊【完成】按按鈕，則在工工作表上看到到輸出的圖形形，對(duì)其進(jìn)行行必要的調(diào)整整（如坐標(biāo)、字字體、位置等等）。（6）在系列【數(shù)數(shù)據(jù)點(diǎn)】上的的任一點(diǎn)上，按按鼠標(biāo)左鍵，使使各數(shù)據(jù)點(diǎn)出出現(xiàn)記號(hào)

38、，再再單擊【工具具欄】上的【圖圖表】按鈕，選選中【添加趨趨勢(shì)線】項(xiàng)，或或在數(shù)據(jù)點(diǎn)上上按鼠標(biāo)右鍵鍵，選【添加加趨勢(shì)線】項(xiàng)項(xiàng)，出現(xiàn)【添添加趨勢(shì)線】對(duì)對(duì)話框，如圖圖4-15所示示。圖4-15 【添加趨勢(shì)勢(shì)線】對(duì)話框框（7）在【添加加趨勢(shì)線】中中的【類型】對(duì)對(duì)話框中，有有【線性】、【對(duì)對(duì)數(shù)】、【多多項(xiàng)式】、【乘乘冪】、【指指數(shù)】和【移移動(dòng)平均】66個(gè)選項(xiàng)。通通過觀察XYY散點(diǎn)圖可知知，產(chǎn)品銷售售收入與廣告告支出之間呈呈明顯的線性性關(guān)系，故這這里選【線性性】。（8）在【添加加趨勢(shì)線】中中的【選項(xiàng)】對(duì)對(duì)話框中，勾勾選【顯示公公式】、【顯顯示R平方值】，如如圖4-166所示。圖4-16 【添加趨勢(shì)勢(shì)線】的【

39、選選項(xiàng)】設(shè)置（9）單擊【確確定】按鈕，則則在圖形上顯顯示出較粗的的預(yù)測(cè)線、回回歸方程和RR平方值，然然后進(jìn)行必要要的調(diào)整，得得到如圖4-17的結(jié)果果。圖4-17 輸輸出圖形用同樣的方法還還可以確定銷銷售收入與居居民平均收入入的關(guān)系。4.4.3.22 回歸分分析法回歸分析法可以以對(duì)一元線性性或多元線性性以及某些可可以轉(zhuǎn)化為線線性的非線性性問題進(jìn)行回回歸分析。1線性回歸【例4-8】仍仍以例4-77的有關(guān)資料料為例，回歸歸分析的步驟驟如下：（1）從【工具具】菜單中選選中【數(shù)據(jù)分分析】命令，則則彈出【數(shù)據(jù)據(jù)分析】對(duì)話話框，在【數(shù)數(shù)據(jù)分析】對(duì)對(duì)話框中的【分分析工具】框框中選中【回回歸】選項(xiàng)，如如圖4-1

40、88所示，則彈彈出【回歸】對(duì)對(duì)話框。圖4-18 【數(shù)據(jù)分析析】對(duì)話框（2）在【回歸歸】對(duì)話框中中，【Y值輸入?yún)^(qū)域域】中輸入“$B$1:$B$100”，【X值輸入?yún)^(qū)域域】中輸入“$C$1:$D$100”，在【輸出出選項(xiàng)】中勾勾選【輸出區(qū)區(qū)域】，填入入“$A$122”，然后根據(jù)據(jù)實(shí)際需要，勾勾選其他需要要的選項(xiàng)，如如圖4-199所示。圖4-19 【回回歸】選項(xiàng)（3）單擊【確確定】按鈕，回回歸分析的摘摘要就輸出在在本工作表上上，如圖4-20所示。對(duì)對(duì)這些數(shù)據(jù)進(jìn)進(jìn)行分析可知知：R平方值為0.9903，說(shuō)說(shuō)明因變量與與自變量之間間相關(guān)性很高高；F的顯著值為為2.9611E-07，已已達(dá)0.055的檢驗(yàn)標(biāo)

41、準(zhǔn)準(zhǔn)；其他統(tǒng)計(jì)計(jì)檢驗(yàn)也達(dá)到到相應(yīng)的標(biāo)準(zhǔn)準(zhǔn)。從而得到到回歸方程為為：Y=2229.84009+9.22794X11+0.00082 X22。圖4-20 回歸分析的的計(jì)算機(jī)輸出出當(dāng)自變量只有一一個(gè)（即一元元回歸）時(shí)，上上述方法同樣樣適用。2非線性回歸歸對(duì)于某些可以化化為線性關(guān)系系的非線性問問題，同樣可可以進(jìn)行回歸歸分析。舉例例如下?！纠?-9】某某地區(qū)科研系系統(tǒng)近10年的凈收收入Y（千萬(wàn)元）與與研究經(jīng)費(fèi)XX1（千萬(wàn)元）和和研究人員數(shù)數(shù)X2（萬(wàn)人）的的統(tǒng)計(jì)資料，如如圖4-211所示，假設(shè)設(shè)它們之間存存在著以下的的函數(shù)關(guān)系：式中，a、b、c為待估計(jì)參參數(shù)。若利用回歸工具具求解此類非非線性問題，解解決的

42、辦法是是將此方程進(jìn)進(jìn)行數(shù)學(xué)變換換，即對(duì)方程程兩邊取對(duì)數(shù)數(shù)，得，將各各個(gè)觀測(cè)值進(jìn)進(jìn)行變換，如如圖4-211所示，即在在單元格E22:E11中輸入入公式“=LN(BB2:B11)”（數(shù)組公式式輸入），然然后將單元格格E2:E11復(fù)制到到單元格F22:F11和G2:G11中。圖4-21 某某地區(qū)科研系系統(tǒng)有關(guān)資料料再對(duì)變換后的數(shù)數(shù)據(jù)利用Exxcel的回回歸工具進(jìn)行行回歸分析，具具體步驟可參參閱【例4-7】，其中中【Y值輸入?yún)^(qū)域域】中輸入“$E$1:$E$111”，在【X值輸入?yún)^(qū)域域】中輸入“$F$1:$G$111”，在【輸出出選項(xiàng)】中勾勾選【輸出區(qū)區(qū)域】，填入入“$A$122”，得到如圖圖4-22所

43、示示的分析結(jié)果果，最后得到到：a = e0.008214 = 0.99211，b =0.44477，c = 0.6046，相相關(guān)系數(shù)為00.98088（注意此相相關(guān)系數(shù)是變變換后的線性性方程的相關(guān)關(guān)系數(shù)，并不不是原非線性性方程的相關(guān)關(guān)系數(shù)）， 回歸方程為為：。圖4-22 回歸分析結(jié)結(jié)果第4章 Exccel在財(cái)務(wù)務(wù)預(yù)測(cè)中的應(yīng)應(yīng)用4.5 利用規(guī)規(guī)劃求解工具具解決預(yù)測(cè)問問題 雖然我們們可以利用EExcel提提供的各種預(yù)預(yù)測(cè)分析工具具解決大多數(shù)數(shù)財(cái)務(wù)預(yù)測(cè)中中的實(shí)際問題題，但這些預(yù)預(yù)測(cè)分析工具具并不是萬(wàn)能能的，其預(yù)測(cè)測(cè)誤差也隨著著實(shí)際問題的的復(fù)雜化而增增大。比如對(duì)對(duì)于一些非線線性預(yù)測(cè)問題題，常常是將將其

44、通過變量量替換而轉(zhuǎn)換換為線性問題題。但是，這這種變換過程程一方面增加加了計(jì)算工作作量，另一方方面也可能導(dǎo)導(dǎo)致分析精度度下降，因?yàn)闉樽儞Q后的數(shù)數(shù)據(jù)容易使觀觀測(cè)數(shù)據(jù)的性性質(zhì)發(fā)生變化化，導(dǎo)致自變變量與因變量量之間的關(guān)系系發(fā)生扭曲，從從而影響回歸歸方程的精度度，因此，這這種將非線性性轉(zhuǎn)換為線性性的做法是存存在一定的缺缺陷的。此外外，有些非線線性問題根本本無(wú)法直接轉(zhuǎn)轉(zhuǎn)換為線性問問題，除非作作出大量的簡(jiǎn)簡(jiǎn)化，這必然然使得到的回回歸方程嚴(yán)重重失真。因此，對(duì)于非線線性回歸問題題，最好的方方法是直接進(jìn)進(jìn)行回歸分析析，即求解使使殘差平方和和最小、或使使相關(guān)系數(shù)最最大的回歸方方程，但非線線性回歸過程程是一個(gè)循環(huán)環(huán)尋

45、優(yōu)過程，需需要先設(shè)置回回歸方程系數(shù)數(shù)的初值，然然后計(jì)算觀測(cè)測(cè)值與預(yù)測(cè)值值的殘差平方方和，不斷尋尋找使殘差平平方和最小的的回歸方程系系數(shù)，這實(shí)際際上是一個(gè)優(yōu)優(yōu)化問題，因因此，可以利利用Exceel的規(guī)劃求求解工具求解解非線性回歸歸問題，當(dāng)然然也可以用來(lái)來(lái)求解線性回回歸問題。在利用規(guī)劃求解解工具直接求求解非線性回回歸問題時(shí)，需需要使用以下下幾個(gè)計(jì)算公公式：自由度df為式中，n為觀測(cè)測(cè)次數(shù)；m為待估計(jì)參參數(shù)個(gè)數(shù)。殘差平方和SEE為式中，Yi、 分別為第i個(gè)觀測(cè)值和和預(yù)測(cè)值（ii =1,2,n）。剩余標(biāo)準(zhǔn)差SSS為相關(guān)系數(shù)R2為為式中，為觀測(cè)值值的平均值。下面結(jié)合實(shí)例說(shuō)說(shuō)明在Exccel上進(jìn)行行非線性

46、回歸歸的具體方法法和步驟?！纠?-10】以例4-9的有關(guān)關(guān)資料為例，利利用Exceel的規(guī)劃求求解工具來(lái)求求解非線性回回歸問題的方方法和步驟如如下：（1）如圖4-23所示，單單元格G2:G4為變動(dòng)單單元格，分別別存放待估計(jì)計(jì)參數(shù)a、b、c，其初值可可設(shè)為0。（2）在單元格格E2:E11中輸入預(yù)預(yù)測(cè)值公式“=G2*(C2:C11)G3*(DD2:D11)G4”（數(shù)組公式式輸入）。圖4-23 利利用規(guī)劃求解解工具進(jìn)行非非線性回歸分分析（3）在單元格格G5中輸入觀觀測(cè)值的平均均值公式“=AVERRAGE(BB2:B11)”； 在單元格G66中輸入自由由度公式“=COUNNT(B2:B11)-COUN

47、TT(G2:G4)”；在單元格格G7中輸入殘殘差平方和公公式“=SUM(B2:B11-EE2:E11)2)”（數(shù)組公式式輸入）；在在單元格G88中輸入剩余余標(biāo)準(zhǔn)差公式式“=SQRTT(G7/GG6)”；在單元格格G9中輸入相相關(guān)系數(shù)R2的計(jì)算公式式“=1-G77/SUM(B2:B11-GG5)2)”（數(shù)組公式式輸入）。（4）單擊EXXCEL工具具菜單，選擇擇【規(guī)劃求解解】項(xiàng)，出現(xiàn)現(xiàn)【規(guī)劃求解解參數(shù)】對(duì)話話框；（5）在【規(guī)劃劃求解參數(shù)】對(duì)對(duì)話框中，【設(shè)設(shè)置目標(biāo)單元元格】設(shè)置為為單元格“$G$7”，即目標(biāo)函函數(shù)為殘差平平方和；【等等于】設(shè)置為為“最小”；【可變單單元格】設(shè)置置為“$G$2:$G$4

48、”。然后單擊擊【求解】，即即可得到回歸歸方程的系數(shù)數(shù)a、b、c，出現(xiàn)“規(guī)劃求解結(jié)結(jié)果”對(duì)話框，然然后單出【確確定】按鈕，保保存規(guī)劃求解解結(jié)果。需要注意的是，若若系數(shù)a、b、c的初值設(shè)置置不合適的話話，則一次求求解過程（即即在Exceel上進(jìn)行【工工具】【規(guī)劃劃求解】 【求解】 【確定】這這樣一個(gè)求解解過程）可能能得不到最優(yōu)優(yōu)結(jié)果（或得得不到解），這這時(shí)需要進(jìn)行行多次求解，即即在第一次求求解結(jié)果的基基礎(chǔ)上，再進(jìn)進(jìn)行第二次求求解，得到第第二次求解結(jié)結(jié)果，然后在在第二次求解解結(jié)果的基礎(chǔ)礎(chǔ)上，再進(jìn)行行第三次求解解，得到第三三次求解結(jié)果果，如此繼續(xù)續(xù)下去，直到到求出的系數(shù)數(shù)a、b、c的值不再變變化、且殘

49、差差平方和最小小為止，即得得到最優(yōu)結(jié)果果。在上例中中，當(dāng)a、b、c的初始值設(shè)設(shè)為0時(shí)，經(jīng)過2次求解過程程即得到最優(yōu)優(yōu)結(jié)果，如圖圖4-23所示示。若采用非線性轉(zhuǎn)轉(zhuǎn)換為線性的的方法，如例例4-9所示，可可得到有關(guān)系系數(shù)如圖4-22所示，將將此系數(shù)代入入回歸方程，計(jì)計(jì)算不同X1和X2下的預(yù)測(cè)值值，進(jìn)而計(jì)算算出殘差平方方和與剩余標(biāo)標(biāo)準(zhǔn)差分別為為157.11139和4.73776，與圖4-223的直接非非線性回歸的的結(jié)果（殘差差平方和1554.03、剩剩余標(biāo)準(zhǔn)差44.69099）進(jìn)行比較較，可見非線線性轉(zhuǎn)換為線線性的方法得得出的結(jié)果誤誤差要大于直直接進(jìn)行非線線性回歸的誤誤差。第4章 Exccel在財(cái)務(wù)務(wù)

50、預(yù)測(cè)中的應(yīng)應(yīng)用4.6 銷售預(yù)預(yù)測(cè) 銷售預(yù)測(cè)的的準(zhǔn)確程度，對(duì)對(duì)企業(yè)的興衰衰成敗會(huì)產(chǎn)生生很重要的影影響。銷售預(yù)預(yù)測(cè)比較準(zhǔn)確確，會(huì)使企業(yè)業(yè)在有計(jì)劃的的財(cái)務(wù)安排下下順利運(yùn)作；而如果銷售售預(yù)測(cè)與實(shí)際際情況偏離很很遠(yuǎn)，則會(huì)使使企業(yè)遇到麻麻煩，甚至陷陷入困境。因因此，銷售預(yù)預(yù)測(cè)是企業(yè)進(jìn)進(jìn)行財(cái)務(wù)預(yù)測(cè)測(cè)的首要工作作，是企業(yè)制制定財(cái)務(wù)計(jì)劃劃的基礎(chǔ)。 銷售預(yù)測(cè)主主要應(yīng)根據(jù)市市場(chǎng)需求的變變化，結(jié)合企企業(yè)的利潤(rùn)目目標(biāo)、實(shí)現(xiàn)企企業(yè)市場(chǎng)份額額的目標(biāo)，并并綜合考慮企企業(yè)內(nèi)外部的的各種限制條條件的影響來(lái)來(lái)進(jìn)行。一般般情況下，可可首先分別對(duì)對(duì)未來(lái)各期的的銷售量和銷銷售價(jià)格進(jìn)行行預(yù)測(cè)，在此此基礎(chǔ)上，根根據(jù)預(yù)測(cè)的產(chǎn)產(chǎn)品銷售價(jià)格格乘以

51、預(yù)測(cè)的的銷售量得到到預(yù)測(cè)的銷售售額；也可以以直接根據(jù)銷銷售額的有關(guān)關(guān)歷史資料，采采用適當(dāng)?shù)姆椒椒ㄟM(jìn)行預(yù)測(cè)測(cè)。4.6.1 銷售預(yù)測(cè)的的基本方法銷售預(yù)測(cè)是一項(xiàng)項(xiàng)比較復(fù)雜的的工作，需要要考慮的因素素很多，作出出準(zhǔn)確的預(yù)測(cè)測(cè)是非常困難難的。通?？煽衫闷髽I(yè)過過去的數(shù)據(jù)進(jìn)進(jìn)行統(tǒng)計(jì)分析析，并結(jié)合經(jīng)經(jīng)濟(jì)環(huán)境對(duì)未未來(lái)市場(chǎng)的影影響以及企業(yè)業(yè)內(nèi)外部各種種條件的限制制，作出銷售售預(yù)測(cè)。進(jìn)行銷售預(yù)測(cè)的的方法很多，常常用的方法包包括以下幾種種。1時(shí)間序列預(yù)預(yù)測(cè)法時(shí)間序列預(yù)測(cè)法法，是指將觀觀察或記錄的的一些歷史數(shù)數(shù)據(jù)，按時(shí)間間的先后排列列成數(shù)據(jù)系列列，進(jìn)行統(tǒng)計(jì)計(jì)分析，找出出過去長(zhǎng)期的的銷售量或銷銷售額的增減減變化趨勢(shì)，再

52、再根據(jù)此變化化趨勢(shì)分析的的結(jié)果，預(yù)測(cè)測(cè)未來(lái)時(shí)期的的銷售量或銷銷售額。常見見的時(shí)間序列列的預(yù)測(cè)方法法有簡(jiǎn)單平均均法、移動(dòng)平平均法、指數(shù)數(shù)平滑法、或或以時(shí)間為自自變量的回歸歸分析法等，這這些方法的基基本原理可參參閱前面的有有關(guān)內(nèi)容。2因果關(guān)系預(yù)預(yù)測(cè)法因果關(guān)系預(yù)測(cè)法法，是指利用用有關(guān)因素與與產(chǎn)品銷售量量或銷售額之之間的固有因因果關(guān)系，通通過建立一定定的數(shù)學(xué)模型型來(lái)預(yù)測(cè)企業(yè)業(yè)未來(lái)的產(chǎn)品品銷售水平的的一種方法。企企業(yè)產(chǎn)品銷售售水平的高低低，往往受到到諸多宏觀或或微觀、外部部或內(nèi)部、客客觀或主觀等等因素的影響響，通?？梢砸酝ㄟ^回歸分分析的方法檢檢驗(yàn)出哪些因因素與銷售水水平之間具有有因果關(guān)系，在在此基礎(chǔ)上可

53、可建立回歸方方程，進(jìn)行銷銷售預(yù)測(cè)。有關(guān)如何建立回回歸方程及進(jìn)進(jìn)行相關(guān)檢驗(yàn)驗(yàn)的方法可參參閱前面的有有關(guān)內(nèi)容。3通過生產(chǎn)能能力或訂貨合合同進(jìn)行銷售售量（銷售額額）預(yù)測(cè)企業(yè)生產(chǎn)的產(chǎn)品品如果在市場(chǎng)場(chǎng)占有穩(wěn)定的的份額或供不不應(yīng)求，則可可按本企業(yè)的的生產(chǎn)能力預(yù)預(yù)測(cè)產(chǎn)品的銷銷售量，計(jì)算算公式如下：計(jì)劃期銷售量=計(jì)劃期初庫(kù)庫(kù)存量+計(jì)劃期預(yù)計(jì)計(jì)生產(chǎn)量-計(jì)劃期末預(yù)預(yù)計(jì)庫(kù)存量4.6.2 銷售預(yù)測(cè)模模型及其應(yīng)用用在很多情況下，通通過建立企業(yè)業(yè)的銷售預(yù)測(cè)測(cè)模型，可以以很方便地實(shí)實(shí)現(xiàn)銷售預(yù)測(cè)測(cè)。下面介紹紹兩個(gè)銷售預(yù)預(yù)測(cè)模型。4.6.2.11 一元線線性（非線性性）回歸預(yù)測(cè)測(cè)模型【例4-11】根據(jù)圖4-224中所給的的資料

54、建立一一元線性（非非線性）回歸歸預(yù)測(cè)模型。圖4-24 一元線性（非非線性）回歸歸銷售預(yù)測(cè)模模型下面利用線性回回歸中的LIINEST函函數(shù)和指數(shù)回回歸中的LOOGEST函函數(shù)，來(lái)建立立一元線性（非非線性）回歸歸預(yù)測(cè)模型。（1）首先建立立銷售預(yù)測(cè)模模型，如圖44-24所示示，這里以過過去12期的銷售售量為歷史數(shù)數(shù)據(jù)（可以是是以年計(jì)算，也也可以是以月月計(jì)算，圖44-24為以以年計(jì)算）。（2）設(shè)置回歸歸模型選擇控控件，控件的的數(shù)據(jù)源區(qū)域域?yàn)?A$77:$A$8，單單元格鏈接為為$B$7，下下拉顯示項(xiàng)數(shù)數(shù)為2。（3）選取單元元格區(qū)域B33:M3，單擊【插插入】【名稱稱】【定義】命命令，或直接接單擊編輯欄

55、欄中的名稱框框，將影響因因素所在的單單元格區(qū)域BB3:M3定義為“影響因素序序列”；用同樣的的方法，將銷銷售量所在的的單元格區(qū)域域B4:M4定義為“銷售序列”。（4）在單元格格E8中輸入公公式“=IF(BB7=1,IINDEX(LINESST(銷售序序列,影響因素序序列,TRUUE,TRUUE),1,2),INNDEX(LLOGESTT(銷售序列列,影響因素序序列,TRUUE,TRUUE),1,2)”，計(jì)算系數(shù)數(shù)A。（5）在單元格格F8中輸入公公式“=IF(BB7=1,IINDEX(LINESST(銷售序序列,影響因素序序列,TRUUE,TRUUE),1,1),INNDEX(LLOGESTT(

56、銷售序列列,影響因素序序列,TRUUE,TRUUE),1,1)”，計(jì)算系數(shù)數(shù)B。（6）在單元格格G8中輸入公公式“=IF(BB7=1,IINDEX(LINESST(銷售序序列,影響因素序序列,TRUUE,TRUUE),3,1),INNDEX(LLOGESTT(銷售序列列,影響因素序序列,TRUUE,TRUUE),3,1)”，計(jì)算相關(guān)關(guān)系數(shù)R2。（7）在單元格格J8:M8中輸入公公式“=IF(BB7=1,EE8+F8*J7:M7,E88*F8JJ7:M7)”（數(shù)組公式式輸入），計(jì)計(jì)算未來(lái)第114期的預(yù)測(cè)值值。在影響因素和銷銷售量?jī)闪休斴斎霘v史數(shù)據(jù)據(jù)，并在J77:M7中輸入未未來(lái)4期的影響因因素預(yù)

57、測(cè)數(shù)值值后，即可得得到回歸預(yù)測(cè)測(cè)模型及未來(lái)來(lái)的預(yù)測(cè)值。通通過選擇不同同的回歸模型型，可以分別別計(jì)算一元線線性模型和一一元指數(shù)模型型下的回歸結(jié)結(jié)果及預(yù)測(cè)值值。由計(jì)算結(jié)結(jié)果可知，采采用指數(shù)模型型（相關(guān)系數(shù)數(shù)為0.97742）要比比線性模型（相相關(guān)系數(shù)為00.94522）更為準(zhǔn)確確。4.6.2.22 多元線線性回歸預(yù)測(cè)測(cè)模型【例4-12】根據(jù)圖4-225中所給的的資料建立多多元線性回歸歸預(yù)測(cè)模型。圖4-25 多元線性回回歸銷售預(yù)測(cè)測(cè)模型當(dāng)影響銷售量（額額）的因素不不止一個(gè)時(shí)，就就需要建立多多元線性回歸歸模型。下面面就二元線性性回歸預(yù)測(cè)模模型的建立進(jìn)進(jìn)行說(shuō)明，對(duì)對(duì)于影響因素素在兩個(gè)以上上的情況，可可參

58、照本模型型建立。（1）首先建立立銷售預(yù)測(cè)模模型，如圖44-25所示示，這里以過過去12期的歷史史數(shù)據(jù)為依據(jù)據(jù)（可以是按按年計(jì)算，也也可以是按月月計(jì)算，圖44-25為按按月計(jì)算）。（2）選取單元元格區(qū)域B33:M4，單擊【插插入】【名稱稱】【定義】命命令，或直接接單擊編輯欄欄中的名稱框框，將影響因因素所在的單單元格區(qū)域BB3:M4定義為“影響因素序序列”；用同樣的的方法，將銷銷售額所在的的單元格區(qū)域域B5:M5定義為“銷售序列”。（3）在單元格格D9中輸入公公式“=INDEEX(LINNEST(銷銷售序列,影響因素序序列,TRUUE,TRUUE),1,3)”，計(jì)算系數(shù)數(shù)A。（4）在單元格格E9中

59、輸入公公式“=INDEEX(LINNEST(銷銷售序列,影響因素序序列,TRUUE,TRUUE),1,2)”，計(jì)算系數(shù)數(shù)B。（5）在單元格格F9中輸入公公式“=INDEEX(LINNEST(銷銷售序列,影響因素序序列,TRUUE,TRUUE),1,1)”，計(jì)算系數(shù)數(shù)C。（6）在單元格格G9中輸入公公式“=INDEEX(LINNEST(銷銷售序列,影響因素序序列,TRUUE,TRUUE),3,1)”，計(jì)算相關(guān)關(guān)系數(shù)R2。（7）在單元格格J9:M9中輸入公公式“=D9+EE9*J7:M7+F99*J8:M8”（數(shù)組公式式輸入），計(jì)計(jì)算未來(lái)第114期的預(yù)測(cè)值值。在影響因素和銷銷售額各列輸輸入歷史數(shù)據(jù)

60、據(jù)，并在J77:M8中輸入未未來(lái)4期的影響因因素預(yù)測(cè)數(shù)值值后，即可得得到回歸預(yù)測(cè)測(cè)模型及未來(lái)來(lái)的預(yù)測(cè)值，如如圖4-255所示第4章 Exccel在財(cái)務(wù)務(wù)預(yù)測(cè)中的應(yīng)應(yīng)用4.7 成本預(yù)預(yù)測(cè) 4.7.1 成本預(yù)測(cè)的的方法成本是指企業(yè)為為生產(chǎn)和銷售售產(chǎn)品所花費(fèi)費(fèi)的全部費(fèi)用用。成本可以以按很多不同同的標(biāo)準(zhǔn)進(jìn)行行分類，如常常見的按經(jīng)濟(jì)濟(jì)職能分類和和按成本性態(tài)態(tài)分類：成本按經(jīng)濟(jì)職能能劃分可分為為生產(chǎn)成本和和非生產(chǎn)成本本兩大類。生生產(chǎn)成本又稱稱制造成本，包包括生產(chǎn)過程程中發(fā)生的直直接材料、直直接人工和制制造費(fèi)用三個(gè)個(gè)項(xiàng)目；非生生產(chǎn)成本又稱稱非制造成本本，包括為銷銷售產(chǎn)品所花花費(fèi)的銷售費(fèi)費(fèi)用和為組織織企業(yè)的生產(chǎn)