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

1、芭第4章 Exc班el在財務(wù)預(yù)測柏中的應(yīng)用敖4.1 財務(wù)預(yù)叭測概述 八跋 隘財務(wù)預(yù)測，是指藹對企業(yè)未來的收懊入、成本、利潤敖、現(xiàn)金流量及融拌資需求等財務(wù)指艾標(biāo)所作的估計和哎推測。財務(wù)預(yù)測伴是編制投資和融霸資計劃的基礎(chǔ)，佰是公司制訂成長敖戰(zhàn)略的基本要素把。稱職的財務(wù)管骯理人員應(yīng)該能夠按充分利用公司的佰有關(guān)信息資料，扮預(yù)測公司的財務(wù)唉需要并做出相應(yīng)白的安排。公司成版長主要由銷售增巴長來決定，銷售八增長需要相應(yīng)的搬資產(chǎn)增長，如果熬企業(yè)已經(jīng)是滿負(fù)霸荷運(yùn)轉(zhuǎn)，不僅流啊動資產(chǎn)、而且固班定資產(chǎn)都要增長爸，而資產(chǎn)增長需拜要相應(yīng)的融資增艾長。同時，企業(yè)稗進(jìn)行對外投資和奧調(diào)整資本結(jié)構(gòu)，柏也需要籌措資金辦。企業(yè)所需要

2、的壩這些資金，一部罷分來自企業(yè)內(nèi)部按，另一部分通過哎外部融資取得。癌由于對外融資時拌，企業(yè)不但需要疤尋找資金提供者叭，而且還需做出拜還本付息的承諾跋或提供企業(yè)盈利隘前景等信息，使斑資金提供者確信埃其投資是安全的胺并可獲利，這個跋過程往往需要花哎費較長的時間。懊因此，企業(yè)需要盎預(yù)先知道自身的哀財務(wù)需求，確定熬資金的需要量，扒提前安排融資計安劃，以免影響資般金周轉(zhuǎn)。愛財務(wù)預(yù)測有助于埃改善企業(yè)的投資氨決策。雖然投資爸是決定籌資與否半和籌資多少的重俺要因素，但是根稗據(jù)銷售前景估計搬出的融資需求，艾并不一定能夠得埃到全部滿足。這捌時，就需要根據(jù)伴可能籌措到的資案金來安排銷售增胺長以及有關(guān)的投疤資項目，使

3、投資罷決策建立在可行爸的基礎(chǔ)上。昂財務(wù)預(yù)測一般按奧以下幾個步驟進(jìn)吧行。1銷售預(yù)測靶銷售預(yù)測是指根岸據(jù)市場調(diào)查所得昂到的有關(guān)資料，柏通過對有關(guān)因素芭的分析研究，預(yù)藹計和測算特定產(chǎn)爸品在未來一定時板期內(nèi)的市場銷售瓣量水平及變化趨安勢，進(jìn)而預(yù)測企拔業(yè)產(chǎn)品未來銷售扳量的過程。企業(yè)昂的一切財務(wù)需求澳都可以看作是因靶銷售引起的，銷笆售量的增減變化阿，將會引起庫存鞍量、現(xiàn)金流量、埃應(yīng)收與應(yīng)付賬款半以及公司其他資耙產(chǎn)和負(fù)債的變化暗。因此銷售預(yù)測氨在企業(yè)預(yù)測系統(tǒng)壩中處于先導(dǎo)地位扮，它對于指導(dǎo)利八潤預(yù)測、成本預(yù)百測和資金預(yù)測，俺進(jìn)行長短期決策矮，安排經(jīng)營計劃爸，組織生產(chǎn)等都盎起著重要的作用凹。鞍2岸估計收入、費拜

4、用和利潤案收入和費用與銷艾售量之間也存在巴一定的函數(shù)關(guān)系瓣，因此，可以根翱據(jù)銷售數(shù)據(jù)估計拔收入和費用，并翱確定凈利潤。凈拔利潤和股利支付哎率，共同決定了傲內(nèi)部留存收益所背能提供的資金數(shù)凹額。鞍3捌估計需要的資伴產(chǎn)板資產(chǎn)通常是銷售霸收入的函數(shù)，根半據(jù)歷史數(shù)據(jù)可以昂分析出二者之間鞍的函數(shù)關(guān)系。根唉據(jù)預(yù)計銷售收入靶和資產(chǎn)與銷售之疤間的函數(shù)關(guān)系，岸可以預(yù)測所需資佰產(chǎn)的總量。某些癌流動負(fù)債也是銷拜售收入的函數(shù)，扒相應(yīng)地也可以預(yù)皚測負(fù)債的自發(fā)增哎長額，這種增長巴可以減少企業(yè)外吧部融資的數(shù)額。扳4爸估計所需融資隘根據(jù)預(yù)計資產(chǎn)總暗量，減去已有的半資金來源、負(fù)債板的自發(fā)增長和內(nèi)疤部提供的留存收癌益，可得出所需埃

5、的外部融資數(shù)額頒。擺第4章 Exc熬el在財務(wù)預(yù)測案中的應(yīng)用氨4.2 財務(wù)預(yù)翱測的分析方法 俺預(yù)測分析的方法奧有很多種，企業(yè)稗應(yīng)根據(jù)不同的需罷要選擇不同的預(yù)八測方法?？偟膩戆僬f，預(yù)測分析方班法可分為兩大類吧：定量預(yù)測法和邦定性預(yù)測法。艾4.2.1 按定量預(yù)測法耙定量預(yù)測法是指哀在掌握與預(yù)測對耙象有關(guān)的各種要岸素的定量資料的伴基礎(chǔ)上，運(yùn)用現(xiàn)邦代數(shù)學(xué)方法進(jìn)行唉數(shù)據(jù)處理，從而熬建立起能夠反映骯有關(guān)變量之間關(guān)邦系的各類預(yù)測模吧型的方法。隘在財務(wù)預(yù)測中，阿經(jīng)常使用的定量襖預(yù)測法主要有以礙下幾種。拔4.2.1.1俺 艾移動平均法藹移動平均法是一版種改良的算術(shù)平壩均法，是一種最扮簡單的自適應(yīng)預(yù)埃測模型。它根

6、據(jù)絆近期數(shù)據(jù)對預(yù)測稗值影響較大，而耙遠(yuǎn)期數(shù)據(jù)對預(yù)測傲值影響較小的事扮實，把平均數(shù)逐氨期移動。移動期癌數(shù)的大小視具體俺情況而定，移動吧期數(shù)少，能快速白地反映變化，但哀不能反映變化趨襖勢；移動期數(shù)多礙，能反映變化趨疤勢，但預(yù)測值帶熬有明顯的滯后偏搬差。拜常用的移動平均澳法主要有一次移澳動平均法和二次骯移動平均法。凹1敖一次移動平均藹法凹一次移動平均法靶是根據(jù)時間序列板，逐期移動，依傲次計算包含一定般項數(shù)的時間序列疤平均數(shù)，形成一笆個平均時間數(shù)序敖列，并據(jù)此進(jìn)行柏預(yù)測。預(yù)測模型襖為班式中敗疤第爸t安+1拔期的預(yù)測值；案、邦、懊熬、啊罷將被平均的擺n唉個觀測值；吧n芭敖移動平均的項數(shù)吧，即移動期數(shù)。敗

7、在實際預(yù)測中，八可以多取幾個癌n凹數(shù)，并將得到的擺預(yù)測值與實際值岸進(jìn)行比較，選用襖誤差最小的皚n伴值。巴2凹二次移動平均白法拔二次移動平均法鞍是對時間序列計傲算一次移動平均骯數(shù)后，再對一次爸移動平均數(shù)序列熬進(jìn)行一次移動平拌均運(yùn)算。預(yù)測模氨型為。埃式中熬壩二次移動平均數(shù)疤；哎百第絆t絆+1敖期的預(yù)測值，即澳。叭二次移動平均法骯解決了一次移動辦平均法只能預(yù)測叭下一期的局限性辦，它可以進(jìn)行近拌、短期的預(yù)測。翱但它仍不能解決鞍中長期的預(yù)測問啊題。安4.2.1.2懊 盎指數(shù)平滑法斑指數(shù)平滑法實際癌上也是一種加權(quán)翱平均法，是一種翱改良的加權(quán)平均哎法，預(yù)測模型為岸式中辦 扮礙平滑系數(shù)，皚0邦案哎1捌。跋在指

8、數(shù)平滑法中盎，確定合適的矮值和初始值是非頒常重要的。伴越大，啊t百期的實際值對新版預(yù)測值的貢獻(xiàn)就辦越大；案越小，隘t艾期的實際值對新爸預(yù)測值的貢獻(xiàn)就白越小。一般情況搬下，可以取幾個艾不同的翱值進(jìn)行預(yù)測，比熬較它們的預(yù)測誤搬差，選擇預(yù)測誤敖差最小的百值。凹4.2.1.3霸 背回歸分析預(yù)測法叭回歸分析預(yù)測法埃是通過研究兩組拌或兩組以上變量哀之間的關(guān)系，建傲立相應(yīng)的回歸預(yù)拌測模型，對變量叭進(jìn)行預(yù)測的一種巴預(yù)測方法。稗1爸回歸分析預(yù)測辦法的基本程序扒進(jìn)行回歸分析的靶步驟如下：俺（百1熬）收集有關(guān)資料襖。將各種可能的案影響因素的有關(guān)辦數(shù)據(jù)盡可能多地藹收集起來。擺（白2耙）判斷趨勢。根敗據(jù)收集到的數(shù)據(jù)骯，

9、判斷其變化趨隘勢，從而為建立邦相應(yīng)的數(shù)學(xué)模型拔做準(zhǔn)備。對于變隘量不多的問題，吧可以通過繪制散叭點圖來判斷變化伴趨勢。凹（白3藹）建立預(yù)測數(shù)學(xué)拜模型。根據(jù)歷史把數(shù)據(jù)的變化趨勢襖，選擇相應(yīng)的描俺寫該問題的數(shù)學(xué)扮模型，并采用相班關(guān)的計算技術(shù)來捌估計數(shù)學(xué)模型的百參數(shù)。擺（邦4唉）相關(guān)檢驗。對奧建立的預(yù)測數(shù)學(xué)傲模型，必須進(jìn)行胺有關(guān)的檢驗，主安要是通過計算預(yù)瓣測模型的相關(guān)系般數(shù)、方差（或標(biāo)拌準(zhǔn)差）以及顯著斑性等指標(biāo)，來判吧斷預(yù)測模型的準(zhǔn)藹確性、是否需要皚修正、采用何種胺方法修正等。擺2癌回歸模型建立扳的方法邦建立回歸模型的盎一般方法是采用佰最小二乘法，其擺原理如下：辦考慮按m哎個自變量瓣x艾1胺、擺x拌2

10、暗、奧疤、礙x拔 m絆和因變量跋y把的關(guān)系，現(xiàn)有吧n霸組觀測數(shù)據(jù)，不扳同昂x凹ki霸 熬（耙k敗=1,2,拌鞍,胺m盎；芭i瓣=1,2,斑,般n捌）下的叭y安的觀測值為埃y案i啊，函數(shù)半y襖=埃f叭(巴x吧k拌)佰的待估計參數(shù)為挨a案k懊（捌k白=1,2,拌襖,翱m芭+1芭，這里，每個自皚變量有一個待估俺計系數(shù)，還有一礙個待估計常數(shù)，俺故有藹m半+1敗個待估計參數(shù)）背，通過回歸預(yù)測哎模型得到不同唉x骯ki阿下的預(yù)測值為扮 哎，則有：藹殘差平方和巴SE拌：皚剩余標(biāo)準(zhǔn)差阿SS?。合嚓P(guān)系數(shù)R2：版y礙為觀測值扳y般i啊的平均值：奧那么，最小二乘斑法的原理就是尋敖找最優(yōu)的待估計癌參數(shù)唉a白k頒，使殘差

11、平方和鞍最小。爸3拌財務(wù)預(yù)測中常笆用的幾種回歸模耙型懊（鞍1叭）一元線性回歸按模型拜當(dāng)只有兩個變量懊（一個自變量和白一個因變量），藹并且它們之間存拔在線性關(guān)系時，搬可以用一元線性拜回歸模型來描述拌。一元線性回歸巴模型為俺式中罷a啊、佰b藹跋回歸系數(shù)，其中吧a叭代表截距，扮b百代表斜率。隘（霸2安）一元非線性回襖歸模型唉當(dāng)變量案x盎和唉y半之間的關(guān)系不能澳用線性關(guān)系來描罷述時，則需要建搬立一元非線性回按歸模型。根據(jù)變版量版x背和奧y把之間的關(guān)系，一耙元非線性回歸模艾型常見的幾種情稗況有：對數(shù)模型：指數(shù)模型：乘冪模型：雙曲線模型：襖以上幾種一元非案線性模型均可通按過數(shù)學(xué)變換化成礙一元線性模型。挨（

12、拌3案）多元線性回歸捌模型拌當(dāng)自變量有兩個氨或兩個以上，且班因變量與這些自岸變量之間呈線性拔組合關(guān)系時，它爸們就構(gòu)成了多元愛線性回歸模型，襖模型形式為爸式中隘a矮、頒b爸1壩、扳b扮2澳、岸吧、半b爸m背擺估計參數(shù)；阿x拔1擺、般x骯2斑、敖絆、俺x礙m敖爸自變量。邦（骯4氨）多元非線性回扳歸模型班多元非線性回歸扮模型用來描述因巴變量與多個自變凹量之間呈非線性霸組合關(guān)系的情況唉。例如，柯柏氨八道格拉斯生產(chǎn)函澳數(shù)就是典型的多啊元非線性模型：巴式中：癌L氨和啊K叭分別為勞動力和安固定資本；拌a哎、把b扳、巴c傲為系數(shù)。半4.2.1.4疤 按模擬法俺在企業(yè)的實際經(jīng)捌濟(jì)活動中，各種安經(jīng)濟(jì)參數(shù)往往并辦不

13、是確定的，而愛是隨機(jī)變化的，凹比如產(chǎn)品的銷售跋量往往隨市場的阿變化而變化，在阿這種情況下，就靶需要對這些參數(shù)絆的不確定性進(jìn)行靶分析，而對其預(yù)襖測也就需要采用爸與傳統(tǒng)的確定性罷分析不同的方法笆來進(jìn)行。一般情白況下，可以采用耙模擬法來解決不按確定性情況下的瓣財務(wù)預(yù)測問題，安概率法、蒙特卡扳羅模擬方法就是白較實用的方法。罷4.2.2 爸定性預(yù)測法擺定性預(yù)測法是由愛有關(guān)方面的專業(yè)霸人員或?qū)＜腋鶕?jù)耙自己的經(jīng)驗和知矮識，結(jié)合預(yù)測對扳象的特點進(jìn)行綜翱合分析，對事物岸的未來狀況和發(fā)背展趨勢作出推測氨的預(yù)測方法。定澳性預(yù)測法由于帶板有較多的個人主皚觀性，因而在實氨踐中最好作為一昂種補(bǔ)充的預(yù)測方班法。半第4章 E

14、xc礙el在財務(wù)預(yù)測埃中的應(yīng)用扮4.3 Exc按el中的有關(guān)預(yù)案測函數(shù)及其應(yīng)用霸(1) 氨Excel百提供了關(guān)于估計跋線性模型和指數(shù)唉模型參數(shù)的幾個安預(yù)測函數(shù)。線性笆模型和指數(shù)模型鞍的數(shù)學(xué)表達(dá)式如邦下：線性模型：骯y鞍 = 俺mx背 + 白b辦 版或隘 安y柏 = 把m鞍1安x班1懊 + 捌m案2懊x暗2八 + + 靶b指數(shù)模型：或百式中，敖y(tǒng)靶為因變量；按x版是自變量；安m熬、跋m安1愛、巴.襖、矮m安n凹-1拌、安m懊n巴、敖b埃分別為預(yù)測模型胺的待估計參數(shù)。跋Exce胺l絆提供的預(yù)測函數(shù)挨主要有艾LINEST氨函數(shù)、哎LOGEST啊函數(shù)、芭TREND笆函數(shù)、暗GROWTH板函數(shù)、板FOR

15、ECAS扮T吧函數(shù)、骯SLOPE扮函數(shù)和吧INTERCE敖PT唉函數(shù)，它們所使八用的參數(shù)都基本巴相同，現(xiàn)列于表昂4-1斑中，以供參考。爸表八4-1 白預(yù)測函數(shù)的參數(shù)澳及含義靶參數(shù)胺含義拌known_y把s板因變量盎y矮的觀測值集合笆known_x捌s巴自變量壩x背的觀測值集合。安它可以是一個變矮量（即一元模型凹）或多個變量（瓣即多元模型）的伴集合。唉如果只用到一個阿變量，只要藹 known奧-岸ys 壩和唉 known哀-骯xs 埃維數(shù)相同，它們稗可以是任何形狀埃的選定區(qū)域。如柏果用到不只一個捌變量，疤known_y疤s 笆必須是向量（也扮就是說，必須是背一行或一列的區(qū)版域）。如果省略扮 kn

16、own_霸xs岸，則假設(shè)該數(shù)組芭是案 1,2,3伴.礙靶，其大小與俺 known_埃ys 胺相同伴const霸邏輯值，指明是笆否強(qiáng)制使常數(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ù)組藹為笆 m拔n按，岸m把n癌-疤1扳，昂.扒，暗m啊1凹，暗b扳；阿se頒n傲，奧se哎n埃-皚1哀

17、，懊.拌，絆se擺1案，白se昂b芭，班r澳2背，皚se靶y皚；懊F百，伴df擺；胺ss佰reg唉，捌ss矮resid耙骯。如果懊 stats昂為埃FALSE俺或省略，函數(shù)只癌返回系數(shù)預(yù)測模捌型的待估計參數(shù)藹m耙、愛m凹n百、盎m埃n胺-癌1唉、巴.擺、矮m絆1拔和暗b盎。扒附加回歸統(tǒng)計值爸返回的順序見表暗4-2霸。壩表扳4-2拜中的各參數(shù)說明哀見表跋4-3骯。板如果要得到附加啊回歸統(tǒng)計值數(shù)組絆中的值，需用八INDEX澳函數(shù)將其取出靶表邦4-2 藹附加回歸統(tǒng)計值捌返回的順序佰把1凹2耙3鞍4吧5巴6拌1藹m埃n白m翱n礙-壩1爸拜m柏2叭m安1罷b拔2耙se版n班se愛n白-骯1白拔se扒2班

18、se拜1拜se白 b襖3案r絆2般se靶y扒哀凹霸敖4爸F懊df柏哀艾半爸5芭ss昂reg案ss盎resid頒背挨半拔表拔4-3 辦各參數(shù)說明埃參數(shù)柏說明胺se把1芭,se扳2敖,澳.奧,se板n按系數(shù)壩 m暗1俺,m安2拔,阿 .熬,m礙n襖 百的標(biāo)準(zhǔn)誤差值百Se芭b稗常數(shù)項搬 b 霸的標(biāo)準(zhǔn)誤差值（暗當(dāng)絆 const 疤為捌 FALSE凹時，盎se懊b扮 = #N/A八 八）安伴鞍參數(shù)啊說明絆r般2瓣相關(guān)系數(shù)，范圍疤在版 0 昂到背 1 巴之間。如果為白 1襖，則樣本有很好擺的相關(guān)性，耙Y 唉的估計值與實際敗值之間沒有差別澳。反之，如果相辦關(guān)系數(shù)為吧 0疤，則回歸方程不懊能用來預(yù)測班 Y 哀

19、值癌se扒y佰Y 擺估計值的標(biāo)準(zhǔn)誤艾差矮F胺F 斑統(tǒng)計值或暗F 埃觀察值。使用案F 敖統(tǒng)計可以判斷因俺變量和自變量之疤間是否偶爾發(fā)生吧過觀察到的關(guān)系翱Df絆自由度。用于在頒統(tǒng)計表上查找按 F 伴臨界值。所查得襖的值和函數(shù)靶 LINEST扮 捌返回的翱F背統(tǒng)計值的比值可癌用來判斷模型的拌置信度斑ss暗reg扮回歸平方和霸ss挨resid唉殘差平方拜4.3.1 阿LINEST爸函數(shù)凹LINEST捌函數(shù)的功能是使哀用最小二乘法計伴算對已知數(shù)據(jù)進(jìn)胺行最佳線性擬合挨的直線方程，并埃返回描述此線性唉模型的數(shù)組。因白為此函數(shù)返回數(shù)稗值為數(shù)組，故必跋須以數(shù)組公式的奧形式輸入。函數(shù)公式為哎= LINES絆T埃（

20、扳known_y耙s襖，捌known_x襖s百，扮const唉，背stats阿）伴下面舉例說明熬LINEST辦函數(shù)的應(yīng)用。半1佰一元線性回歸瓣分析骯LINEST爸函數(shù)可用于一元半線性回歸分析，扳也可以用于多元皚線性回歸分析，般以及時間數(shù)列的安自回歸分析。笆當(dāng)只有一個自變背量癌 x 藹（即一元線性回藹歸分析）時，可氨直接利用下面的挨公式得到斜率和拜 y 皚軸的截距值以及隘相關(guān)系數(shù)：背 襖斜率：傲INDEX氨（矮LINEST礙（伴known_y叭s按，艾known_x隘s凹）藹,1,罷1藹）；或耙INDEX搬（疤LINEST跋（矮known_y把s,know鞍n_xs隘）頒,1唉）絆截距：吧IND

21、EX版（爸LINEST頒（翱known_y按s絆，安known_x阿s敗）版,1,2板）；或襖INDEX熬（般LINEST瓣（般known_y扳s,know斑n_xs板）骯,2案）骯相關(guān)系數(shù)：拌INDEX稗（澳LINEST頒（翱known_y般s,know鞍n_xs,t把rue,tru版e哎）板,3,1奧）俺【例熬4-1癌】襖某企業(yè)板1捌百9敗月份的總成本與耙人工小時及機(jī)器熬工時的數(shù)據(jù)如圖啊4-1辦所示。假設(shè)總成版本與人工小時之搬間存在著線性關(guān)邦系，則在單元格藹B13按中插入公式擺“叭=INDEX(壩L瓣INEST(B按2盎:霸B10,D2爸:拔D10),2)半”按，在單元格安B14靶插入公式

22、邦“版=INDEX(罷LINEST(澳B2柏:伴B10,D2藹:邦D10),1)奧”啊，在單元格啊B15八插入公式霸“昂=INDEX(絆LINEST(拜B2伴:澳B10,D2盎:斑D10,TRU笆E,TRUE)擺,3,1)凹”敗，即得總成本與拌人工小時的一元半線性回歸分析方胺程為：俺Y=562.7翱2756+4.案41444X捌1擺，相關(guān)系數(shù)為胺R岸2白=0.9980邦1擺，如圖班4-1暗所示。佰圖捌4-1 皚一元線性回歸分芭析皚2巴多元線性回歸瓣分析八仍以例胺4-1柏的數(shù)據(jù)為例，首芭先選取單元格區(qū)笆域捌A17邦:靶D21板，再以數(shù)組公式柏方式輸入公式巴“扮=LINEST半(B2啊:佰B10,

23、C2柏:叭D10,TRU熬E,TRUE)靶”襖，即得該二元線邦性回歸的有關(guān)參昂數(shù)如圖皚4-2凹所示，從而得到吧：疤圖芭4-2 啊二元線性回歸分絆析板回歸方程：笆Y = 471骯.4366+3版.6165X疤1昂+3.4323邦X埃2礙相關(guān)系數(shù)：安R襖2 白=0.9990奧標(biāo)準(zhǔn)差：癌Se罷y 礙=11.779絆2凹。翱4.3.2 拌LOGEST凹函數(shù)襖LOGEST啊函數(shù)的功能是在稗回歸分析中，計搬算最符合觀測數(shù)霸據(jù)組的指數(shù)回歸挨擬合曲線，并返拔回描述該指數(shù)模皚型的數(shù)組。由于半這個函數(shù)返回一艾個數(shù)組，必須以靶數(shù)組公式輸入。搬LOGEST矮函數(shù)的公式為扳= 埃LOGEST(拔known_y哎s,kn

24、ow澳n_xs,c傲onst擺,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,TRU埃E,TRUE)搬”翱（數(shù)組公式輸入拌），即得回歸參哎數(shù)，如圖版4-3暗所示，參數(shù)骯m=0.芭8887熬，參數(shù)扳b=1891.懊7729礙，生產(chǎn)成本與生巴產(chǎn)量的回歸曲線霸為：八Y=1791.懊77290.把8887八X叭，相關(guān)系數(shù)愛R骯2拜=0.9588氨5隘。搬圖岸4-3 稗指數(shù)回歸背回

25、歸方程的系數(shù)壩及相關(guān)系數(shù)也可胺以利用下面的公半式直接計算拌參數(shù)白m隘：扮INDEX(L阿OGEST(C靶2白:版C13,B2愛:稗B13),1)熬=0.888癌7爸參數(shù)氨b八：芭INDEX(L板OGEST(C芭2哀:伴C13,B2巴:板B13),1,拜2)=1791百.7729骯相關(guān)系數(shù)拔R搬2啊：疤=INDEX(拜LOGEST(叭C2按:癌C13,B2礙:稗B13,TRU擺E,TRUE)澳,3,1)= 耙0.95885唉4.3.3 版TREND頒函數(shù)板TREND搬函數(shù)的功能是返絆回一條線性回歸奧擬合線的一組縱八坐標(biāo)值（癌y 愛值），即找到適拔合給定的數(shù)組八 known_稗ys 扳和頒 kno

26、wn_拌xs 柏的直線（用最小拌二乘法），并返熬回指定數(shù)組靶 new_x般s 案值在直線上對應(yīng)頒的背 y 阿值。半TREND斑函數(shù)的公式為安= 把TREND(k背n挨own_ys拜,known_愛xs,new矮_xs,co隘nst)疤式中拔 new_x傲s 半耙 拔需要函數(shù)拜 TREND 擺返回對應(yīng)搬 y 埃值的新哀 x 邦值。按 new_x壩s 案與百 known_板xs 把一樣，每個獨立奧變量必須為單獨百的一行（或一列癌）。因此，如果拌 known_氨ys 礙是單列的，按known_x伴s 懊和拜 new_x芭s 霸應(yīng)該有同樣的列懊數(shù)，如果阿 known_背ys 罷是單行的，翱known_

27、x氨s 澳和板 new_x凹s 頒應(yīng)該有同樣的行稗數(shù)。如果省略熬 new_x凹s八，將假設(shè)它和澳 known_斑xs 拔一樣。案【例岸4-3澳】白某企業(yè)過去一年艾的銷售量為下列拔數(shù)據(jù)：扳300扮，芭356疤，八374澳，礙410叭，爸453哀，白487疤，按501敖，瓣534頒，斑572巴，瓣621安，矮650皚，班670爸，將它們保存在爸單元格半A1扮:艾A12柏中，則下一年的芭1翱、柏2稗、懊3跋月的銷售量預(yù)測頒步驟為：選中單澳元格區(qū)域隘B1捌:岸B3岸，輸入公式巴“跋=TREND(敖A1耙:佰A12,1案3;14;15案)頒”背（數(shù)組公式輸入佰），即得來年的扳1襖、瓣2啊、奧3敗月份的銷

28、售量分拌別為佰710啊、擺743奧和唉777暗。這個公式默認(rèn)懊1;2;3;昂4;5;6;7案;8;9;10疤;11;12罷作為斑known_x頒s笆的參數(shù)，故數(shù)組襖13;14;盎15礙就對應(yīng)其后的艾3笆個月份。敖4.3.4 傲GROWTH百函數(shù)矮GROWTH傲函數(shù)的功能是返懊回給定的數(shù)據(jù)預(yù)背測的指數(shù)增長值氨。根據(jù)已知的斑x案值和啊y俺值，函數(shù)隘GROWTH安返回一組新的伴x靶值對應(yīng)的襖y拌值?？梢允褂冒糋ROWTH挨工作表函數(shù)來擬懊合滿足給定扒x疤值和辦y哎值的指數(shù)曲線。伴GROWTH氨函數(shù)的公式為巴= 笆GROWTH(癌known_y俺s,know哀n_xs,n疤ew_xs,岸const)絆

29、式中，各參數(shù)的板含義同礙TREND耙函數(shù)。但需注意癌的是，如果癌known_y哎s隘中的任何數(shù)為零八或為負(fù)，函數(shù)佰 GROWTH扒將返回錯誤值扮 #NUM!辦。安 隘【例皚4-4盎】岸以例扳4-3胺的資料為例，利扳用背GROWTH艾函數(shù)預(yù)測來年的隘1絆、拌2背、巴3半月的銷售量。預(yù)邦測步驟為：選中叭單元格區(qū)域捌B1挨:辦B3疤，輸入公式礙“愛=GROWTH爸(A1半:凹A12,1絆3;14;15斑)昂”版（數(shù)組公式輸入艾），即得來年的靶1敗、岸2佰、案3岸月份的銷售量分柏別為埃756板、斑811癌和絆870版。這個公式同樣安默認(rèn)壩1;2;3;敗4;5;6;7瓣;8;9;10半;11;12絆作為

30、百known_x哀s捌的參數(shù)，故數(shù)組礙13;14;白15伴就對應(yīng)后面的翱3靶個月份。瓣4.3.5 板FORECAS襖T傲函數(shù)盎FORECAS爸T吧函數(shù)的功能是根爸據(jù)給定的數(shù)據(jù)計辦算或預(yù)測未來值昂。此預(yù)測值為基佰于一系列已知的絆 x 壩值推導(dǎo)出的頒 y 胺值。以數(shù)組或數(shù)隘據(jù)區(qū)域的形式給凹定胺 x 鞍值和巴 y 搬值后，返回基于般 x 巴的線性回歸預(yù)測搬值。百FORECAS疤T白函數(shù)的計算公式襖為絆 礙a霸+扳bx式中，；。矮FORECAS絆T唉函數(shù)的公式為盎= FOREC爸AST(x,k八nown_y愛s,known巴_xs)奧式中氨x跋跋需要進(jìn)行預(yù)測的斑數(shù)據(jù)點。昂需要說明的是：芭 八如果矮 x

31、 啊為非數(shù)值型，函佰數(shù)挨 FORECA唉ST 岸返回錯誤值芭 #VALUE背!懊。壩如果岸 known_奧ys 般和壩 known_氨xs 阿為空或含有不同版數(shù)目的數(shù)據(jù)點，胺函數(shù)鞍 FORECA昂ST 哀返回錯誤值絆 #N/A俺。扮如果傲 known_叭xs 安的方差為零，函壩數(shù)白 FORECA跋ST 巴返回錯誤值半 #DIV/0笆!阿。巴例如：半FORECAS阿T(30,6啊,7,9,15絆,21,2盎0,28,31案,38,40把)按 = 10.6氨0725辦。跋4.3.6 阿SLOPE跋函數(shù)拌SLOPE懊函數(shù)的功能是返奧回根據(jù)隘 known_癌ys 八和按 known_搬xs 百中的數(shù)據(jù)

32、點擬合阿的線性回歸直線扒的斜率。斜率為扳直線上任意兩點安的垂直距離與水鞍平距離的比值，柏也就是回歸直線骯的變化率。罷SLOPE芭函數(shù)的公式為阿 壩= SLOPE唉(known_按ys,kno懊wn_xs)鞍說明：唉參數(shù)可以是數(shù)字啊，或者是涉及數(shù)翱字的名稱、數(shù)組俺或引用。如果數(shù)板組或引用參數(shù)里瓣包含文本、邏輯爸值或空白單元格背，這些值將被忽澳略。但包含零值背的單元格將計算岸在內(nèi)。如果絆 known_瓣ys 半和澳 known_柏xs 暗為空或其數(shù)據(jù)點傲數(shù)目不同，函數(shù)版 SLOPE 骯返回錯誤值伴 #N/A傲。按例如：凹SLOPE(跋2,3,9,1伴,8,7,5癌,6,5,1瓣1,7,5,4藹,4

33、)巴 = 0.30八5556艾。吧4.3.7 骯INTERCE斑PT唉函數(shù)擺INTERCE盎PT耙函數(shù)的功能是利爸用已知的啊 x 巴值與佰 y 斑值計算直線與阿 y 澳軸的截距。截距澳為穿過艾 known_氨xs 愛和靶 known_斑ys 藹數(shù)據(jù)點的線性回芭歸線與靶 y 把軸的交點。公式為 拜= INTER艾CEPT (k般nown_y盎s,known礙_xs)岸例如：骯INTERCE礙PT(2, 白3, 9, 1罷, 8, 瓣6, 5, 1埃1, 7, 5耙)盎 = 0.04阿83871凹。氨第4章 Exc岸el在財務(wù)預(yù)測稗中的應(yīng)用半4.4 利用數(shù)阿據(jù)分析工具解決捌預(yù)測問題(1) 澳除了利

34、用前面介懊紹的幾個預(yù)測函巴數(shù)進(jìn)行回歸預(yù)測凹分析外，我們還?？梢允褂冒疎xcel骯的數(shù)據(jù)分析工具伴庫提供的統(tǒng)計觀罷測分析工具來解藹決回歸預(yù)測問題翱。斑Excel笆的數(shù)據(jù)分析工具藹庫提供了拔3扳種統(tǒng)計觀測分析拔工具，它們是移懊動平均法、指數(shù)凹平滑法和回歸分昂析法。下面結(jié)合敗實例來說明這胺3敖種方法的具體應(yīng)跋用。壩4.4.1 昂移動平均法隘【例扮4-5稗】伴某企業(yè)澳2000吧年埃12班個月的銷售額如癌圖氨4-4扒所示，分別按芭3胺期、骯5般期和挨7頒期移動平均所做捌的預(yù)測分析如圖擺4-4半中的安C4柏板E13奧區(qū)域所示。以傲3安期移動平均為例案為例，具體計算奧步驟如下：板圖靶4-4 岸一次移動平均法

35、敖實例版（跋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埃）單擊【確定】俺按鈕，則運(yùn)算結(jié)按果就顯示在單元拌格區(qū)域爸C4胺:凹C13

36、耙中，如圖熬4-4罷所示（圖中的第凹13辦行預(yù)測數(shù)據(jù)即為凹下月即第般13翱月的預(yù)測值），胺并自動出現(xiàn)輸出拔圖表，如圖把4-7唉所示。邦圖邦4-7 巴移動期數(shù)為凹3矮時的輸出圖表哎用同樣的方法，斑可以分析當(dāng)移動板期數(shù)為搬5暗和哀7愛時的分析結(jié)果，胺如圖百4-4愛所示。昂4.4.2 疤指數(shù)平滑法熬【例版4-6邦】皚某企業(yè)的有關(guān)銷壩售數(shù)據(jù)如圖搬4-8把所示，利用指數(shù)佰平滑法進(jìn)行預(yù)測笆分析，其步驟如版下：敖圖拔4-8 啊指數(shù)平滑法實例搬（癌1拔）從【工具】菜隘單中選中【數(shù)據(jù)爸分析】命令，則耙彈出【數(shù)據(jù)分析罷】對話框，在【襖數(shù)據(jù)分析】對話案框中的【分析工阿具】框中選中【暗指數(shù)平滑】選項瓣，則彈出【指數(shù)百

37、平滑】對話框，吧如圖凹4-9愛所示。敖圖耙4-9 叭【指數(shù)平滑】對扒話框捌（阿2八）在【指數(shù)平滑斑】對話框中，【頒輸入?yún)^(qū)域】框中礙輸入案“伴$B$2拌:班$B$13百”百，【阻尼系數(shù)】班框中輸入癌“背0.2芭”柏，【輸出區(qū)域】昂框中輸入昂“拌$C$3吧”敖，最后選中【圖伴表輸出】選項。板（案3骯）單擊【確定】疤按鈕，則運(yùn)算結(jié)巴果就顯示在單元把格區(qū)域翱C3把:般C13搬中（圖中的第澳13氨行預(yù)測數(shù)據(jù)即為熬下月即第版13翱月的預(yù)測值），愛如圖背4-8唉所示，并自動出壩現(xiàn)輸出圖表，如半圖藹4-10頒所示。盎圖吧4-10 鞍指數(shù)平滑法預(yù)測爸輸出圖（阻尼系熬數(shù)辦0.2伴）壩用同樣的方法，爸可以分析當(dāng)阻尼

38、翱系數(shù)為斑0.4霸和版0.6扮時的分析結(jié)果如扳圖扳4-8壩所示。邦需要注意的是，俺【數(shù)據(jù)分析】中按的指數(shù)平滑法所拜使用的阻尼系數(shù)爸并不是哀4.2.1.2皚節(jié)介紹的指數(shù)平敖滑法預(yù)測方程中叭的平滑系數(shù)盎，二者的關(guān)系為伴：阻尼系數(shù)百=1搬捌。隘4.4.3 柏回歸法罷利用斑Excel翱的回歸工具進(jìn)行暗預(yù)測分析有兩種邦方法：一是圖表鞍法；二是回歸分背析法。八4.4.3.1按 哎圖表法扳圖表法僅能解決鞍一元線性或非線瓣性回歸問題，不疤能解決多元回歸翱問題。班【例藹4-7斑】爸某企業(yè)連續(xù)霸9礙年的產(chǎn)品銷售收爸入按Y奧（萬元）與廣告襖支出跋X霸1白（萬元）和居民隘平均收入頒X靶2翱（元）的有關(guān)數(shù)巴據(jù)如圖扳4-

39、11癌所示，則利用圖拔表法進(jìn)行回歸分阿析，其方法和步芭驟如下，這里僅拔以銷售收入半Y跋（萬元）與廣告叭支出哀X伴1瓣（萬元）的一元啊線性關(guān)系為例：拜圖頒4-11 扮某企業(yè)的有關(guān)銷礙售數(shù)據(jù)吧（耙1哎）選擇單元格區(qū)靶域氨B2扮:骯C10昂。安（凹2斑）單擊工具欄上暗的【圖表導(dǎo)向】瓣按鈕，在【圖表盎導(dǎo)向傲罷4叭步驟之靶1按八圖表類型】中選俺“敖XY八散點圖半”頒，其【子圖表類扒型】選第扳1爸種，如圖搬4-12俺所示。邦圖安4-12 奧準(zhǔn)備作散點圖艾（翱3拔）單擊【下一步霸】按鈕，出現(xiàn)【稗圖表導(dǎo)向白頒4芭步驟之翱2阿愛圖表源數(shù)據(jù)】對般話框，單擊【系斑列】，在【名稱爸】欄中填入板“扒銷售收入霸”搬，在【

40、愛X把值】欄中輸入凹“伴=Sheet1懊!$C$2跋:頒$C$10扳”愛，在【昂Y背值】欄中輸入懊“隘=Sheet1版!$B$2絆:靶$B$10皚”邦（用鼠標(biāo)拾取單辦元格區(qū)域），如胺圖唉4-13班所示。百圖胺4-13 半填入源數(shù)據(jù)壩（暗4耙）單擊【下一步敖】按鈕，出現(xiàn)【版圖表導(dǎo)向叭稗4拜步驟之瓣3懊拜圖表選項】對話阿框，填入各標(biāo)題傲文字，如圖俺4-14骯所示。奧圖癌4-14 芭填入各標(biāo)題文字唉（傲5叭）單擊【下一步芭】按鈕，出現(xiàn)【盎圖表導(dǎo)向襖稗4哎步驟之啊4鞍哎圖表位置】對話捌框，不作任何輸捌入，單擊【完成八】按鈕，則在工挨作表上看到輸出愛的圖形，對其進(jìn)百行必要的調(diào)整（矮如坐標(biāo)、字體、班位置等

41、）。背（艾6艾）在系列【數(shù)據(jù)擺點】上的任一點岸上，按鼠標(biāo)左鍵癌，使各數(shù)據(jù)點出阿現(xiàn)記號，再單擊笆【工具欄】上的唉【圖表】按鈕，扒選中【添加趨勢拔線】項，或在數(shù)扮據(jù)點上按鼠標(biāo)右按鍵，選【添加趨傲勢線】項，出現(xiàn)拌【添加趨勢線】敖對話框，如圖伴4-15頒所示。八圖版4-15 隘【添加趨勢線】艾對話框辦（礙7瓣）在【添加趨勢骯線】中的【類型安】對話框中，有懊【線性】、【對叭數(shù)】、【多項式暗】、【乘冪】、襖【指數(shù)】和【移安動平均】敗6愛個選項。通過觀埃察扒XY擺散點圖可知，產(chǎn)擺品銷售收入與廣敗告支出之間呈明佰顯的線性關(guān)系，熬故這里選【線性胺】。凹（芭8隘）在【添加趨勢按線】中的【選項挨】對話框中，勾稗選【

42、顯示公式】扮、【顯示隘R笆平方值】，如圖傲4-16背所示。耙圖昂4-16 鞍【添加趨勢線】俺的【選項】設(shè)置安（阿9百）單擊【確定】斑按鈕，則在圖形拜上顯示出較粗的拌預(yù)測線、回歸方澳程和皚R愛平方值，然后進(jìn)笆行必要的調(diào)整，霸得到如圖礙4-17邦的結(jié)果。白圖伴4-17 柏輸出圖形八用同樣的方法還伴可以確定銷售收埃入與居民平均收安入的關(guān)系。巴4.4.3.2頒 版回歸分析法柏回歸分析法可以頒對一元線性或多俺元線性以及某些爸可以轉(zhuǎn)化為線性半的非線性問題進(jìn)頒行回歸分析。1線性回歸霸【例哎4-8澳】版仍以例瓣4-7鞍的有關(guān)資料為例背，回歸分析的步襖驟如下：氨（矮1拜）從【工具】菜暗單中選中【數(shù)據(jù)八分析】命令

43、，則扳彈出【數(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阿

44、所示。對這些數(shù)皚據(jù)進(jìn)行分析可知岸：背R愛平方值為霸0.9903襖，說明因變量與藹自變量之間相關(guān)懊性很高；哎F敖的顯著值為稗2.961E-安07礙，已達(dá)昂0.05擺的檢驗標(biāo)準(zhǔn)；其襖他統(tǒng)計檢驗也達(dá)胺到相應(yīng)的標(biāo)準(zhǔn)。扒從而得到回歸方爸程為：罷Y=229.8跋409+9.2襖794X骯1辦+0.0082阿 X扒2扳。白圖唉4-20 挨回歸分析的計算埃機(jī)輸出敖當(dāng)自變量只有一叭個（即一元回歸阿）時，上述方法跋同樣適用。2非線性回歸耙對于某些可以化辦為線性關(guān)系的非氨線性問題，同樣鞍可以進(jìn)行回歸分襖析。舉例如下。鞍【例靶4-9盎】八某地區(qū)科研系統(tǒng)背近敖10胺年的凈收入敗Y唉（千萬元）與研巴究經(jīng)費骯X挨1辦（千萬

45、元）和研頒究人員數(shù)般X奧2佰（萬人）的統(tǒng)計阿資料，如圖岸4-21稗所示，假設(shè)它們熬之間存在著以下柏的函數(shù)關(guān)系：胺式中，拌a皚、般b熬、案c佰為待估計參數(shù)。礙若利用回歸工具哀求解此類非線性疤問題，解決的辦笆法是將此方程進(jìn)壩行數(shù)學(xué)變換，即愛對方程兩邊取對扮數(shù)，得捌，將各個觀測值耙進(jìn)行變換，如圖叭4-21阿所示，即在單元把格罷E2氨:挨E11唉中輸入公式佰“半=LN(B2阿:熬B11)?！卑В〝?shù)組公式輸入隘），然后將單元百格巴E2澳:熬E11罷復(fù)制到單元格奧F2疤:扮F11襖和艾G2唉:斑G11拜中。隘圖愛4-21 愛某地區(qū)科研系統(tǒng)邦有關(guān)資料傲再對變換后的數(shù)伴據(jù)利用伴Excel岸的回歸工具進(jìn)行班回歸

46、分析，具體斑步驟可參閱【例壩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 半= e瓣0.08214翱 熬= 0.921笆1襖，跋b 百=0.4477安，礙c 礙= 0.604扳6背，相關(guān)系數(shù)為岸0.9808罷（注意此相關(guān)系阿數(shù)是變換后的線版性方程的相關(guān)系稗數(shù)，并不是原非哀線性方程的相關(guān)把系數(shù)），白 板回歸方程為：鞍。擺圖佰4-22 岸回歸分析結(jié)果氨第4章 Exc佰el在財務(wù)

47、預(yù)測瓣中的應(yīng)用襖4.5 利用規(guī)絆劃求解工具解決斑預(yù)測問題 巴辦 雖然我們可以敗利用Excel拔提供的各種預(yù)測啊分析工具解決大昂多數(shù)財務(wù)預(yù)測中擺的實際問題，但皚這些預(yù)測分析工翱具并不是萬能的藹，其預(yù)測誤差也啊隨著實際問題的俺復(fù)雜化而增大。疤比如對于一些非傲線性預(yù)測問題，皚常常是將其通過案變量替換而轉(zhuǎn)換隘為線性問題。但澳是，這種變換過胺程一方面增加了岸計算工作量，另辦一方面也可能導(dǎo)哎致分析精度下降巴，因為變換后的捌數(shù)據(jù)容易使觀測凹數(shù)據(jù)的性質(zhì)發(fā)生擺變化，導(dǎo)致自變扳量與因變量之間爸的關(guān)系發(fā)生扭曲昂，從而影響回歸頒方程的精度，因板此，這種將非線擺性轉(zhuǎn)換為線性的辦做法是存在一定拌的缺陷的。此外俺，有些非線

48、性問白題根本無法直接扳轉(zhuǎn)換為線性問題背，除非作出大量敖的簡化，這必然啊使得到的回歸方耙程嚴(yán)重失真。俺因此，對于非線芭性回歸問題，最佰好的方法是直接擺進(jìn)行回歸分析，擺即求解使殘差平凹方和最小、或使搬相關(guān)系數(shù)最大的跋回歸方程，但非笆線性回歸過程是搬一個循環(huán)尋優(yōu)過伴程，需要先設(shè)置擺回歸方程系數(shù)的凹初值，然后計算懊觀測值與預(yù)測值拌的殘差平方和，安不斷尋找使殘差擺平方和最小的回翱歸方程系數(shù)，這氨實際上是一個優(yōu)扮化問題，因此，哎可以利用半Excel巴的規(guī)劃求解工具挨求解非線性回歸鞍問題，當(dāng)然也可骯以用來求解線性扮回歸問題。擺在利用規(guī)劃求解拜工具直接求解非百線性回歸問題時叭，需要使用以下半幾個計算公式：自由

49、度df為襖式中，皚n辦為觀測次數(shù)；唉m叭為待估計參數(shù)個靶數(shù)。挨殘差平方和敖SE耙為跋式中，疤Y佰i熬、俺 拜分別為第皚i靶個觀測值和預(yù)測翱值（罷i捌 扮=1,2,岸般,凹n笆）。皚剩余標(biāo)準(zhǔn)差案SS芭為相關(guān)系數(shù)R2為襖式中，癌為觀測值的平均百值。敖下面結(jié)合實例說傲明在把Excel啊上進(jìn)行非線性回耙歸的具體方法和扒步驟。背【例頒4-10芭】版以例啊4-9案的有關(guān)資料為例疤，利用愛Excel鞍的規(guī)劃求解工具笆來求解非線性回耙歸問題的方法和襖步驟如下：岸（安1頒）如圖藹4-23瓣所示，單元格壩G2笆:皚G4耙為變動單元格，敖分別存放待估計吧參數(shù)敗a俺、叭b背、耙c扮，其初值可設(shè)為巴0拔。板（叭2八）在

50、單元格艾E2皚:澳E啊11百中輸入預(yù)測值公疤式澳“扳=G2*(C2唉:拔C11)G3拔*(D2絆:邦D11)G4案”唉（數(shù)組公式輸入胺）。頒圖霸4-23 熬利用規(guī)劃求解工叭具進(jìn)行非線性回耙歸分析唉（愛3疤）在單元格安G5阿中輸入觀測值的俺平均值公式稗“熬=AVERAG翱E(B2版:凹B11)絆”唉；安 搬在單元格拔G6絆中輸入自由度公板式愛“扮=COUNT(搬B2哎:襖B11)-CO岸UN氨T(G2八:伴G4)艾”邦；在單元格半G7澳中輸入殘差平方傲和公式拌“拜=SUM(B跋2邦:耙B11-E2翱:叭E11)2)奧”壩（數(shù)組公式輸入耙）；在單元格霸G8絆中輸入剩余標(biāo)準(zhǔn)骯差公式傲“懊=SQRT(

51、G傲7/G6)挨”伴；在單元格拔G9岸中輸入相關(guān)系數(shù)瓣R巴2隘的計算公式罷“艾=1-G7/S愛UM(B2把:笆B11-G5)罷2)奧”背（數(shù)組公式輸入埃）。扮（叭4拌）單擊半EXCEL壩工具菜單，選擇扳【規(guī)劃求解】項吧，出現(xiàn)【規(guī)劃求罷解參數(shù)】對話框奧；拔（斑5骯）在【規(guī)劃求解骯參數(shù)】對話框中拜，【設(shè)置目標(biāo)單愛元格】設(shè)置為單百元格伴“耙$G$7昂”埃，即目標(biāo)函數(shù)為罷殘差平方和；【安等于】設(shè)置為襖“邦最小俺”擺；【可變單元格背】設(shè)置為吧“爸$G$2敖:靶$G$4巴”巴。然后單擊【求哎解】，即可得到半回歸方程的系數(shù)柏a熬、礙b挨、奧c耙，出現(xiàn)按“敗規(guī)劃求解結(jié)果班”靶對話框，然后單拔出【確定】按鈕埃，

52、保存規(guī)劃求解鞍結(jié)果。班需要注意的是，襖若系數(shù)叭a愛、愛b絆、啊c巴的初值設(shè)置不合班適的話，則一次扮求解過程（即在翱Excel胺上進(jìn)行【工具】暗【規(guī)劃求解】稗 氨【求解】叭 爸【確定】這樣一岸個求解過程）可安能得不到最優(yōu)結(jié)安果（或得不到解鞍），這時需要進(jìn)藹行多次求解，即白在第一次求解結(jié)盎果的基礎(chǔ)上，再阿進(jìn)行第二次求解拜，得到第二次求暗解結(jié)果，然后在敗第二次求解結(jié)果愛的基礎(chǔ)上，再進(jìn)笆行第三次求解，霸得到第三次求解俺結(jié)果，如此繼續(xù)傲下去，直到求出皚的系數(shù)跋a跋、笆b襖、耙c凹的值不再變化、骯且殘差平方和最氨小為止，即得到熬最優(yōu)結(jié)果。在上搬例中，當(dāng)八a凹、唉b暗、澳c擺的初始值設(shè)為擺0柏時，經(jīng)過啊2叭

53、次求解過程即得斑到最優(yōu)結(jié)果，如翱圖邦4-23埃所示。澳若采用非線性轉(zhuǎn)藹換為線性的方法叭，如例昂4-9案所示，可得到有扳關(guān)系數(shù)如圖般4-22跋所示，將此系數(shù)胺代入回歸方程，佰計算不同靶X百1拜和頒X矮2般下的預(yù)測值，進(jìn)搬而計算出殘差平翱方和與剩余標(biāo)準(zhǔn)阿差分別為擺157.113凹9藹和皚4.7376啊，與圖按4-23矮的直接非線性回鞍歸的結(jié)果（殘差盎平方和骯154.03啊、剩余標(biāo)準(zhǔn)差柏4.6909疤）進(jìn)行比較，可氨見非線性轉(zhuǎn)換為挨線性的方法得出拜的結(jié)果誤差要大皚于直接進(jìn)行非線柏性回歸的誤差。辦第4章 Exc芭el在財務(wù)預(yù)測安中的應(yīng)用拜4.6 銷售預(yù)叭測 辦辦 扒銷售預(yù)測的準(zhǔn)確胺程度，對企業(yè)的伴興衰

54、成敗會產(chǎn)生辦很重要的影響。頒銷售預(yù)測比較準(zhǔn)邦確，會使企業(yè)在阿有計劃的財務(wù)安扒排下順利運(yùn)作；懊而如果銷售預(yù)測搬與實際情況偏離般很遠(yuǎn)，則會使企伴業(yè)遇到麻煩，甚巴至陷入困境。因疤此，銷售預(yù)測是拜企業(yè)進(jìn)行財務(wù)預(yù)隘測的首要工作，辦是企業(yè)制定財務(wù)半計劃的基礎(chǔ)。凹傲 拜銷售預(yù)測主要應(yīng)澳根據(jù)市場需求的扳變化，結(jié)合企業(yè)埃的利潤目標(biāo)、實愛現(xiàn)企業(yè)市場份額背的目標(biāo)，并綜合罷考慮企業(yè)內(nèi)外部隘的各種限制條件拌的影響來進(jìn)行。哎一般情況下，可版首先分別對未來唉各期的銷售量和暗銷售價格進(jìn)行預(yù)藹測，在此基礎(chǔ)上爸，根據(jù)預(yù)測的產(chǎn)絆品銷售價格乘以叭預(yù)測的銷售量得伴到預(yù)測的銷售額扳；也可以直接根敖據(jù)銷售額的有關(guān)扒歷史資料，采用百適當(dāng)?shù)姆?/p>

55、法進(jìn)行百預(yù)測。霸4.6.1 凹銷售預(yù)測的基本胺方法半銷售預(yù)測是一項哎比較復(fù)雜的工作耙，需要考慮的因半素很多，作出準(zhǔn)搬確的預(yù)測是非常捌困難的。通?？砂忱闷髽I(yè)過去的挨數(shù)據(jù)進(jìn)行統(tǒng)計分疤析，并結(jié)合經(jīng)濟(jì)背環(huán)境對未來市場邦的影響以及企業(yè)半內(nèi)外部各種條件八的限制，作出銷愛售預(yù)測。傲進(jìn)行銷售預(yù)測的愛方法很多，常用柏的方法包括以下愛幾種。辦1伴時間序列預(yù)測艾法哎時間序列預(yù)測法頒，是指將觀察或百記錄的一些歷史爸?jǐn)?shù)據(jù)，按時間的啊先后排列成數(shù)據(jù)昂系列，進(jìn)行統(tǒng)計唉分析，找出過去胺長期的銷售量或隘銷售額的增減變艾化趨勢，再根據(jù)班此變化趨勢分析頒的結(jié)果，預(yù)測未拔來時期的銷售量疤或銷售額。常見阿的時間序列的預(yù)唉測方法有簡單

56、平半均法、移動平均岸法、指數(shù)平滑法矮、或以時間為自伴變量的回歸分析骯法等，這些方法胺的基本原理可參按閱前面的有關(guān)內(nèi)稗容。扮2邦因果關(guān)系預(yù)測八法扮因果關(guān)系預(yù)測法吧，是指利用有關(guān)埃因素與產(chǎn)品銷售艾量或銷售額之間鞍的固有因果關(guān)系哎，通過建立一定襖的數(shù)學(xué)模型來預(yù)班測企業(yè)未來的產(chǎn)襖品銷售水平的一胺種方法。企業(yè)產(chǎn)稗品銷售水平的高奧低，往往受到諸哀多宏觀或微觀、罷外部或內(nèi)部、客霸觀或主觀等因素胺的影響，通?？砂恳酝ㄟ^回歸分析柏的方法檢驗出哪奧些因素與銷售水艾平之間具有因果半關(guān)系，在此基礎(chǔ)俺上可建立回歸方唉程，進(jìn)行銷售預(yù)胺測。唉有關(guān)如何建立回胺歸方程及進(jìn)行相拌關(guān)檢驗的方法可搬參閱前面的有關(guān)凹內(nèi)容。按3瓣通過生

57、產(chǎn)能力稗或訂貨合同進(jìn)行俺銷售量（銷售額襖）預(yù)測傲企業(yè)生產(chǎn)的產(chǎn)品礙如果在市場占有艾穩(wěn)定的份額或供襖不應(yīng)求，則可按哀本企業(yè)的生產(chǎn)能般力預(yù)測產(chǎn)品的銷板售量，計算公式背如下：挨計劃期銷售量昂=翱計劃期初庫存量俺+拌計劃期預(yù)計生產(chǎn)背量把-八計劃期末預(yù)計庫頒存量稗4.6.2 捌銷售預(yù)測模型及罷其應(yīng)用吧在很多情況下，昂通過建立企業(yè)的靶銷售預(yù)測模型，盎可以很方便地實巴現(xiàn)銷售預(yù)測。下按面介紹兩個銷售佰預(yù)測模型。扮4.6.2.1奧 扮一元線性（非線昂性）回歸預(yù)測模傲型挨【例伴4-11背】擺根據(jù)圖疤4-24傲中所給的資料建稗立一元線性（非愛線性）回歸預(yù)測艾模型。凹圖胺4-24 邦一元線性（非線愛性）回歸銷售預(yù)佰測模

58、型佰下面利用線性回叭歸中的礙LINEST案函數(shù)和指數(shù)回歸巴中的凹LOGEST敗函數(shù)，來建立一扮元線性（非線性爸）回歸預(yù)測模型拌。拜（絆1芭）首先建立銷售擺預(yù)測模型，如圖般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拔定

59、義為板“氨影響因素序列霸”案；用同樣的方法案，將銷售量所在伴的單元格區(qū)域按B4挨:癌M4挨定義為爸“背銷售序列跋”頒。熬（隘4巴）在單元格頒E8辦中輸入公式笆“靶=IF(B7=跋1,INDEX耙(LINEST按(擺銷售序列捌,百影響因素序列板,TRUE,T凹RUE),1,懊2),INDE岸X(LOGES敗T(哀銷售序列擺,板影響因素序列傲,TRUE,T哎RUE),1,頒2)藹”暗，計算系數(shù)奧A吧。拌（耙5艾）在單元格襖F8吧中輸入公式背“阿=IF(B7=斑1,INDEX艾(LINEST邦(捌銷售序列艾,半影響因素序列按,TRUE,T哎RUE),1,班1),INDE藹X(LOGES罷T(澳銷售序

60、列把,斑影響因素序列艾,TRUE,T唉RUE),1,吧1)暗”啊，計算系數(shù)拔B熬。跋（敖6跋）在單元格埃G8拔中輸入公式翱“搬=IF(B7=拔1,INDEX凹(LINEST八(扳銷售序列矮,唉影響因素序列阿,TRUE,T癌RUE),3,哀1),INDE氨X(LOGES敖T(敗銷售序列扒,唉影響因素序列礙,TRUE,T扒RUE),3,挨1)版”胺，計算相關(guān)系數(shù)暗R案2扮。暗（懊7絆）在單元格伴J8百:半M8耙中輸入公式百“霸=IF(B7=版1,E8+F8皚*J7拔:敗M7,E8*F俺8J7熬:隘M7)骯”扮（數(shù)組公式輸入俺），計算未來第懊1熬板4叭期的預(yù)測值。爸在影響因素和銷愛售量兩列輸入歷柏史