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

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

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

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

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

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

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

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

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

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

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

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

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

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

14、xc礙el在財(cái)務(wù)預(yù)測(cè)埃中的應(yīng)用扮4.3 Exc按el中的有關(guān)預(yù)案測(cè)函數(shù)及其應(yīng)用霸(1) 氨Excel百提供了關(guān)于估計(jì)跋線性模型和指數(shù)唉模型參數(shù)的幾個(gè)安預(yù)測(cè)函數(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愛(ài)、巴.襖、矮m安n凹-1拌、安m懊n巴、敖b埃分別為預(yù)測(cè)模型胺的待估計(jì)參數(shù)。跋Exce胺l絆提供的預(yù)測(cè)函數(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ù)測(cè)函數(shù)的參數(shù)澳及含義靶參數(shù)胺含義拌known_y把s板因變量盎y矮的觀測(cè)值集合笆known_x捌s巴自變量壩x背的觀測(cè)值集合。安它可以是一個(gè)變矮量（即一元模型凹）或多個(gè)變量（瓣即多元模型）的伴集合。唉如果只用到一個(gè)阿變量，只要藹 known奧-岸ys 壩和唉 known哀-骯xs 埃維數(shù)相同，它們稗可以是任何形狀埃的選定區(qū)域。如柏果用到不只一個(gè)捌變量，疤known_y疤s 笆必須是向量（也扮就是說(shuō)，必須是背一行或一列的區(qū)版域）。如果省略扮 kn

16、own_霸xs岸，則假設(shè)該數(shù)組芭是案 1,2,3伴.礙靶，其大小與俺 known_埃ys 胺相同伴const霸邏輯值，指明是笆否強(qiáng)制使常數(shù)八b艾為百0按（線性模型）或稗為把1案（指數(shù)模型）。敗 拔如果班const 襖為埃 TRUE笆或省略，跋b絆將被正常計(jì)算。爸如果頒const懊為昂FALSE敖，懊b暗將被設(shè)為懊0芭（線性模型）或昂設(shè)為暗1佰（指數(shù)模型）版stats背邏輯值，指明是挨否返回附加回歸俺統(tǒng)計(jì)值。霸 跋如果艾 stats 壩為柏 TRUE稗，則函數(shù)返回附案加回歸統(tǒng)計(jì)值，埃這時(shí)返回的數(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ù)測(cè)模捌型的待估計(jì)參數(shù)藹m耙、愛(ài)m凹n百、盎m埃n胺-癌1唉、巴.擺、矮m絆1拔和暗b盎。扒附加回歸統(tǒng)計(jì)值爸返回的順序見(jiàn)表暗4-2霸。壩表扳4-2拜中的各參數(shù)說(shuō)明哀見(jiàn)表跋4-3骯。板如果要得到附加啊回歸統(tǒng)計(jì)值數(shù)組絆中的值，需用八INDEX澳函數(shù)將其取出靶表邦4-2 藹附加回歸統(tǒng)計(jì)值捌返回的順序佰把1凹2耙3鞍4吧5巴6拌1藹m埃n白m翱n礙-壩1爸拜m柏2叭m安1罷b拔2耙se版n班se愛(ài)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ù)說(shuō)明埃參數(shù)柏說(shuō)明胺se把1芭,se扳2敖,澳.奧,se板n按系數(shù)壩 m暗1俺,m安2拔,阿 .熬,m礙n襖 百的標(biāo)準(zhǔn)誤差值百Se芭b稗常數(shù)項(xiàng)搬 b 霸的標(biāo)準(zhǔn)誤差值（暗當(dāng)絆 const 疤為捌 FALSE凹時(shí)，盎se懊b扮 = #N/A八 八）安伴鞍參數(shù)啊說(shuō)明絆r般2瓣相關(guān)系數(shù)，范圍疤在版 0 昂到背 1 巴之間。如果為白 1襖，則樣本有很好擺的相關(guān)性，耙Y 唉的估計(jì)值與實(shí)際敗值之間沒(méi)有差別澳。反之，如果相辦關(guān)系數(shù)為吧 0疤，則回歸方程不懊能用來(lái)預(yù)測(cè)班 Y 哀

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

20、扳known_y耙s襖，捌known_x襖s百，扮const唉，背stats阿）伴下面舉例說(shuō)明熬LINEST辦函數(shù)的應(yīng)用。半1佰一元線性回歸瓣分析骯LINEST爸函數(shù)可用于一元半線性回歸分析，扳也可以用于多元皚線性回歸分析，般以及時(shí)間數(shù)列的安自回歸分析。笆當(dāng)只有一個(gè)自變背量癌 x 藹（即一元線性回藹歸分析）時(shí)，可氨直接利用下面的挨公式得到斜率和拜 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敗月份的總成本與耙人工小時(shí)及機(jī)器熬工時(shí)的數(shù)據(jù)如圖啊4-1辦所示。假設(shè)總成版本與人工小時(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)凹”敗，即得總成本與拌人工小時(shí)的一元半線性回歸分析方胺程為：俺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ù)的功能是在稗回歸分析中，計(jì)搬算最符合觀測(cè)數(shù)霸據(jù)組的指數(shù)回歸挨擬合曲線，并返拔回描述該指數(shù)模皚型的數(shù)組。由于半這個(gè)函數(shù)返回一艾個(gè)數(shù)組，必須以靶數(shù)組公式輸入。搬LOGEST矮函數(shù)的公式為扳= 埃LOGEST(拔known_y哎s,kn

24、ow澳n_xs,c傲onst擺,stats)捌 艾【例昂4-2敗】邦某企業(yè)挨12扮個(gè)月某產(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ù)愛(ài)R骯2拜=0.9588氨5隘。搬圖岸4-3 稗指數(shù)回歸背回

25、歸方程的系數(shù)壩及相關(guān)系數(shù)也可胺以利用下面的公半式直接計(jì)算拌參數(shù)白m隘：扮INDEX(L阿OGEST(C靶2白:版C13,B2愛(ài):稗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 愛(ài)值），即找到適拔合給定的數(shù)組八 known_稗ys 扳和頒 kno

26、wn_拌xs 柏的直線（用最小拌二乘法），并返熬回指定數(shù)組靶 new_x般s 案值在直線上對(duì)應(yīng)頒的背 y 阿值。半TREND斑函數(shù)的公式為安= 把TREND(k背n挨own_ys拜,known_愛(ài)xs,new矮_xs,co隘nst)疤式中拔 new_x傲s 半耙 拔需要函數(shù)拜 TREND 擺返回對(duì)應(yīng)搬 y 埃值的新哀 x 邦值。按 new_x壩s 案與百 known_板xs 把一樣，每個(gè)獨(dú)立奧變量必須為單獨(dú)百的一行（或一列癌）。因此，如果拌 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è)過(guò)去一年艾的銷售量為下列拔數(shù)據(jù)：扳300扮，芭356疤，八374澳，礙410叭，爸453哀，白487疤，按501敖，瓣534頒，斑572巴，瓣621安，矮650皚，班670爸，將它們保存在爸單元格半A1扮:艾A12柏中，則下一年的芭1翱、柏2稗、懊3跋月的銷售量預(yù)測(cè)頒步驟為：選中單澳元格區(qū)域隘B1捌:岸B3岸，輸入公式巴“跋=TREND(敖A1耙:佰A12,1案3;14;15案)頒”背（數(shù)組公式輸入佰），即得來(lái)年的扳1襖、瓣2啊、奧3敗月份的銷

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

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

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

31、 啊為非數(shù)值型，函佰數(shù)挨 FORECA唉ST 岸返回錯(cuò)誤值芭 #VALUE背!懊。壩如果岸 known_奧ys 般和壩 known_氨xs 阿為空或含有不同版數(shù)目的數(shù)據(jù)點(diǎn)，胺函數(shù)鞍 FORECA昂ST 哀返回錯(cuò)誤值絆 #N/A俺。扮如果傲 known_叭xs 安的方差為零，函壩數(shù)白 FORECA跋ST 巴返回錯(cuò)誤值半 #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、點(diǎn)擬合阿的線性回歸直線扒的斜率。斜率為扳直線上任意兩點(diǎn)安的垂直距離與水鞍平距離的比值，柏也就是回歸直線骯的變化率。罷SLOPE芭函數(shù)的公式為阿 壩= SLOPE唉(known_按ys,kno懊wn_xs)鞍說(shuō)明：唉參數(shù)可以是數(shù)字啊，或者是涉及數(shù)翱字的名稱、數(shù)組俺或引用。如果數(shù)板組或引用參數(shù)里瓣包含文本、邏輯爸值或空白單元格背，這些值將被忽澳略。但包含零值背的單元格將計(jì)算岸在內(nèi)。如果絆 known_瓣ys 半和澳 known_柏xs 暗為空或其數(shù)據(jù)點(diǎn)傲數(shù)目不同，函數(shù)版 SLOPE 骯返回錯(cuò)誤值伴 #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 斑值計(jì)算直線與阿 y 澳軸的截距。截距澳為穿過(guò)艾 known_氨xs 愛(ài)和靶 known_斑ys 藹數(shù)據(jù)點(diǎn)的線性回芭歸線與靶 y 把軸的交點(diǎn)。公式為 拜= 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在財(cái)務(wù)預(yù)測(cè)稗中的應(yīng)用半4.4 利用數(shù)阿據(jù)分析工具解決捌預(yù)測(cè)問(wèn)題(1) 澳除了利

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

35、敖實(shí)例版（跋1阿）從【工具】菜礙單中選中【數(shù)據(jù)藹分析】命令，則佰彈出【數(shù)據(jù)分析敗】對(duì)話框，如圖爸4-5跋所示。般圖埃4-5 凹【數(shù)據(jù)分析】對(duì)艾話框襖（扳2氨）在【數(shù)據(jù)分析跋】對(duì)話框中的【埃分析工具】框中霸選中【移動(dòng)平均案】選項(xiàng)，則彈出笆【移動(dòng)平均】對(duì)佰話框，如圖襖4-6叭所示。壩圖叭4-6 稗【移動(dòng)平均】對(duì)稗話框罷（礙3熬）在【移動(dòng)平均傲】對(duì)話框中，【靶輸入?yún)^(qū)域】框中扒輸入爸“罷$B$2俺把$B$13捌”骯，【間隔】框中礙輸入拔“皚3敖”暗，【輸出區(qū)域】靶框中輸入藹“罷$C$2頒”笆，最后選中【圖絆表輸出】選項(xiàng)；半（壩4埃）單擊【確定】俺按鈕，則運(yùn)算結(jié)按果就顯示在單元拌格區(qū)域爸C4胺:凹C13

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

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

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

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

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

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

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

43、，則扳彈出【數(shù)據(jù)分析拜】對(duì)話框，在【隘數(shù)據(jù)分析】對(duì)話翱框中的【分析工拜具】框中選中【叭回歸】選項(xiàng)，如疤圖板4-18邦所示，則彈出【傲回歸】對(duì)話框。矮圖佰4-18 稗【數(shù)據(jù)分析】對(duì)岸話框艾（礙2瓣）在【回歸】對(duì)稗話框中，【岸Y瓣值輸入?yún)^(qū)域】中靶輸入白“般$B$1愛(ài):爸$B$10愛(ài)”哀，【熬X背值輸入?yún)^(qū)域】中挨輸入挨“搬$C$1挨:板$D$10暗”盎，在【輸出選項(xiàng)哀】中勾選【輸出吧區(qū)域】，填入班“敖$A$12鞍”跋，然后根據(jù)實(shí)際頒需要，勾選其他叭需要的選項(xiàng)，如拔圖斑4-19靶所示。頒圖芭4-19 昂【回歸】選項(xiàng)巴（邦3隘）單擊【確定】襖按鈕，回歸分析瓣的摘要就輸出在鞍本工作表上，如瓣圖跋4-20阿

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

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

46、分析，具體斑步驟可參閱【例壩4-7拔】，其中【拔Y熬值輸入?yún)^(qū)域】中盎輸入敖“扒$E$1凹:拌$E$11氨”絆，在【扒X矮值輸入?yún)^(qū)域】中般輸入拔“跋$F$1敖:氨$G$11八”拜，在【輸出選項(xiàng)頒】中勾選【輸出氨區(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在財(cái)務(wù)

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

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

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

50、單元格艾E2皚:澳E啊11百中輸入預(yù)測(cè)值公疤式澳“扳=G2*(C2唉:拔C11)G3拔*(D2絆:邦D11)G4案”唉（數(shù)組公式輸入胺）。頒圖霸4-23 熬利用規(guī)劃求解工叭具進(jìn)行非線性回耙歸分析唉（愛(ài)3疤）在單元格安G5阿中輸入觀測(cè)值的俺平均值公式稗“熬=AVERAG翱E(B2版:凹B11)絆”唉；安 搬在單元格拔G6絆中輸入自由度公板式愛(ài)“扮=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隘的計(jì)算公式罷“艾=1-G7/S愛(ài)UM(B2把:笆B11-G5)罷2)奧”背（數(shù)組公式輸入埃）。扮（叭4拌）單擊半EXCEL壩工具菜單，選擇扳【規(guī)劃求解】項(xiàng)吧，出現(xiàn)【規(guī)劃求罷解參數(shù)】對(duì)話框奧；拔（斑5骯）在【規(guī)劃求解骯參數(shù)】對(duì)話框中拜，【設(shè)置目標(biāo)單愛(ài)元格】設(shè)置為單百元格伴“耙$G$7昂”埃，即目標(biāo)函數(shù)為罷殘差平方和；【安等于】設(shè)置為襖“邦最小俺”擺；【可變單元格背】設(shè)置為吧“爸$G$2敖:靶$G$4巴”巴。然后單擊【求哎解】，即可得到半回歸方程的系數(shù)柏a熬、礙b挨、奧c耙，出現(xiàn)按“敗規(guī)劃求解結(jié)果班”靶對(duì)話框，然后單拔出【確定】按鈕埃，

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

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

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

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

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

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

58、型佰下面利用線性回叭歸中的礙LINEST案函數(shù)和指數(shù)回歸巴中的凹LOGEST敗函數(shù)，來(lái)建立一扮元線性（非線性爸）回歸預(yù)測(cè)模型拌。拜（絆1芭）首先建立銷售擺預(yù)測(cè)模型，如圖般4-24百所示，這里以過(guò)癌去絆12哀期的銷售量為歷佰史數(shù)據(jù)（可以是耙以年計(jì)算，也可唉以是以月計(jì)算，捌圖吧4-24骯為以年計(jì)算）。疤（拔2笆）設(shè)置回歸模型般選擇控件，控件奧的數(shù)據(jù)源區(qū)域?yàn)榘?A$7挨:拔$A$8哀，單元格鏈接為啊$B$7佰，下拉顯示項(xiàng)數(shù)巴為爸2昂。安（稗3霸）選取單元格區(qū)俺域芭B3擺:罷M3叭，單擊【插入】奧【名稱】佰【定義】命令，扮或直接單擊編輯阿欄中的名稱框，邦將影響因素所在瓣的單元格區(qū)域氨B3愛(ài):吧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)藹”暗，計(jì)算系數(shù)奧A吧。拌（耙5艾）在單元格襖F8吧中輸入公式背“阿=IF(B7=斑1,INDEX艾(LINEST邦(捌銷售序列艾,半影響因素序列按,TRUE,T哎RUE),1,班1),INDE藹X(LOGES罷T(澳銷售序

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