學(xué)習(xí)目標(biāo)學(xué)習(xí)完本章后，學(xué)生應(yīng)當(dāng)能夠獨(dú)立運(yùn)用分析工具、函數(shù)、規(guī)劃求解等工具進(jìn)行財(cái)務(wù)預(yù)測(cè)模型設(shè)計(jì)，包括：一元線性回歸模型、一元非線性回歸模型、多元線性回歸模型，并在設(shè)計(jì)過(guò)程中繪制相應(yīng)的擬合圖形。第7章財(cái)務(wù)預(yù)測(cè)模型設(shè)計(jì)學(xué)習(xí)目標(biāo)第7章財(cái)務(wù)預(yù)測(cè)模型設(shè)計(jì)1第1單元預(yù)測(cè)模型設(shè)計(jì)－分析工具財(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)略的基本要素。Excel的數(shù)據(jù)分析工具庫(kù)提供了3種統(tǒng)計(jì)觀測(cè)分析工具，它們是移動(dòng)平均法、指數(shù)平滑法和回歸分析法。第1單元預(yù)測(cè)模型設(shè)計(jì)－分析工具財(cái)務(wù)預(yù)測(cè)，是指對(duì)企業(yè)未來(lái)的2⒈移動(dòng)平均法移動(dòng)平均法是一種改良的算術(shù)平均法，是一種最簡(jiǎn)單的自適應(yīng)預(yù)測(cè)模型。它根據(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)平均法3移動(dòng)平均法根據(jù)預(yù)測(cè)時(shí)使用的各元素的權(quán)重不同，可以分為：簡(jiǎn)單移動(dòng)平均和加權(quán)移動(dòng)平均。移動(dòng)平均法根據(jù)預(yù)測(cè)時(shí)使用的各元素的權(quán)重不同，可以分為：簡(jiǎn)單移4⒉指數(shù)平滑法指數(shù)平滑法是在移動(dòng)平均法基礎(chǔ)上發(fā)展起來(lái)的一種時(shí)間序列分析預(yù)測(cè)法，它是通過(guò)計(jì)算指數(shù)平滑值，配合一定的時(shí)間序列預(yù)測(cè)模型對(duì)現(xiàn)象的未來(lái)進(jìn)行預(yù)測(cè)。其原理是任一期的指數(shù)平滑值都是本期實(shí)際觀察值與前一期指數(shù)平滑值的加權(quán)平均。⒉指數(shù)平滑法5⒊回歸分析預(yù)測(cè)法回歸分析預(yù)測(cè)法是通過(guò)研究?jī)山M或兩組以上變量之間的關(guān)系，建立相應(yīng)的回歸預(yù)測(cè)模型，對(duì)變量進(jìn)行預(yù)測(cè)的一種預(yù)測(cè)方法。⒊回歸分析預(yù)測(cè)法6第2單元預(yù)測(cè)模型設(shè)計(jì)－函數(shù)法Excel提供了關(guān)于估計(jì)線性模型和指數(shù)模型參數(shù)的幾個(gè)預(yù)測(cè)函數(shù)。線性模型和指數(shù)模型的數(shù)學(xué)表達(dá)式如下：線性模型：y=mx+b或y=m1x1+m2x2+…+b第2單元預(yù)測(cè)模型設(shè)計(jì)－函數(shù)法Excel提供了關(guān)于估計(jì)7指數(shù)模型：式中，y為因變量；x是自變量；m、m1、...、mn-1、mn、b分別為預(yù)測(cè)模型的待估計(jì)參數(shù)。Excel提供的預(yù)測(cè)函數(shù)主要有LINEST函數(shù)、LOGEST函數(shù)、TREND函數(shù)、FORECAST函數(shù)、SLOPE函數(shù)和INTERCEPT函數(shù)，它們所使用的參數(shù)都基本相同，如表7－1、7－2、7－3所示。指數(shù)模型：8

表7－1預(yù)測(cè)函數(shù)的參數(shù)及含義表7－1預(yù)測(cè)函數(shù)的參數(shù)及含義9

表7－2回歸統(tǒng)計(jì)值返回的順序表7－2回歸統(tǒng)計(jì)值返回的順序10《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件11知識(shí)點(diǎn)講解⒈LINEST函數(shù)LINEST函數(shù)是常用的統(tǒng)計(jì)函數(shù)之一，它是使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。函數(shù)語(yǔ)法：LINEST(known_y's,known_x's,const,stats)Known_y's是關(guān)系表達(dá)式y(tǒng)=ax+b中已知的y值集合。知識(shí)點(diǎn)講解12如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組known-y's在單獨(dú)一行中，則known-x's的每一行被視為一個(gè)獨(dú)立的變量。Known_x's是關(guān)系表達(dá)式y(tǒng)=ax+b中已知的可選x值集合。數(shù)組known_x's可以包含一組或多組變量。如果只用到一個(gè)變量，只要known_y's和known_x's維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，則known_y's必須為向量（即必須為一行或一列）。如果數(shù)組known_y's在單獨(dú)一列中，則kn13如果省略known_x's，則假設(shè)該數(shù)組為{1,2,3,...}，其大小與known_y's相同。Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，b將被設(shè)為0，并同時(shí)調(diào)整m值使y=mx。Stats為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。如果stats為T(mén)RUE，則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值，這時(shí)返回的數(shù)組為{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats為FALSE或省略，LINEST函數(shù)只返回系數(shù)m和常量b。如果省略known_x's，則假設(shè)該數(shù)組為{1,14函數(shù)說(shuō)明：可以使用斜率和y軸截距描述任何直線：斜率(a)：

通常記為a，如果需要計(jì)算斜率，則選取直線上的兩點(diǎn)，(x1,y1)和(x2,y2)；斜率等于(y2-y1)/(x2-x1)。

函數(shù)說(shuō)明：15Y軸截距(b)：通常記為b，直線的y軸的截距為直線通過(guò)y軸時(shí)與y軸交點(diǎn)的數(shù)值。直線的公式為y=ax+b。如果知道了a和b的值，將y或x的值代入公式就可計(jì)算出直線上的任意一點(diǎn)。還可以使用TREND函數(shù)。當(dāng)只有一個(gè)自變量x時(shí)，可直接利用下面公式得到斜率和y軸截距值：

Y軸截距(b)：16斜率：

=INDEX(LINEST(known_y's,known_x's),1)Y軸截距：

=INDEX(LINEST(known_y's,known_x's),2)斜率：

=INDEX(LINEST(known_y's17數(shù)據(jù)的離散程度決定了LINEST函數(shù)計(jì)算的精確度。數(shù)據(jù)越接近線性，LINEST模型就越精確。LINEST函數(shù)使用最小二乘法來(lái)判定最適合數(shù)據(jù)的模型。數(shù)據(jù)的離散程度決定了LINEST函數(shù)計(jì)算的精確度。18回歸分析時(shí)，MicrosoftExcel計(jì)算每一點(diǎn)的y的估計(jì)值和實(shí)際值的平方差。這些平方差之和稱為殘差平方和(ssresid)。然后MicrosoftExcel計(jì)算總平方和(sstotal)。當(dāng)const=TRUE或被刪除時(shí)，總平方和是y的實(shí)際值和平均值的平方差之和。當(dāng)const=FALSE時(shí)，總平方和是y的實(shí)際值的平方和（不需要從每個(gè)y值中減去平均值）。回歸平方和(ssreg)可通過(guò)公式ssreg=sstotal-ssresid計(jì)算出來(lái)。殘差平方和與總平方和的比值越小，判定系數(shù)r2的值就越大，r2是表示回歸分析公式的結(jié)果反映變量間關(guān)系的程度的標(biāo)志。r2等于ssreg/sstotal。回歸分析時(shí)，MicrosoftExcel計(jì)算每19《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件20《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件21⒉LOGEST函數(shù)LOGEST函數(shù)是在回歸分析中，計(jì)算最符合數(shù)據(jù)的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，故必須以數(shù)組公式的形式輸入。此曲線的公式為：y=b*m^x或y=(b*(m1^x1)*(m2^x2)*_)（如果有多個(gè)x值）其中因變量y是自變量x的函數(shù)值。m值是各指數(shù)x的底，而b值是常量值。注意：公式中的y、x和m均可以是向量，LOGEST函數(shù)返回的數(shù)組為{mn,mn-i,...,m1,b}。⒉LOGEST函數(shù)22函數(shù)語(yǔ)法：LOGEST(known_y's,known_x's,const,stats)Known_y's滿足指數(shù)回歸擬合曲線y=b*m^x的一組已知的y值。如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組known-y's在單獨(dú)一行中，則known-x's的每一行被視為一個(gè)獨(dú)立的變量。Known_x's滿足指數(shù)回歸擬合曲線y=b*m^x的一組已知的x值，為可選參數(shù)。函數(shù)語(yǔ)法：23known_x's數(shù)組可以包括一組或多組自變量。如果僅使用一個(gè)變量，那么只要known_x's和known_y's具有相同的維數(shù)，則它們可以是任何形狀的區(qū)域。如果使用多個(gè)變量，則known_y's必須是向量（即具有一列高度或一行寬度的單元格區(qū)域）。如果省略known_x's，則假設(shè)該數(shù)組為{1,2,3,...}，其大小與known_y's相同。Const為一邏輯值，用于指定是否將常數(shù)b強(qiáng)制設(shè)為1。known_x's數(shù)組可以包括一組或多組自變量。如24如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，則常量b將設(shè)為1，而m的值滿足公式y(tǒng)=m＾x。Stats為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。如果stats為T(mén)RUE，函數(shù)LOGEST將返回附加的回歸統(tǒng)計(jì)值，因此返回的數(shù)組為{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats為FALSE或省略，則函數(shù)LOGSET只返回系數(shù)m和常量b。如果const為T(mén)RUE或省略，b將按正常計(jì)算。25函數(shù)說(shuō)明：由數(shù)據(jù)繪出的圖越近似于指數(shù)曲線，則計(jì)算出來(lái)的曲線就越符合原來(lái)給定的數(shù)據(jù)。正如LINEST函數(shù)一樣，LOGEST函數(shù)返回一組描述數(shù)值間相互關(guān)系的數(shù)值數(shù)組，但LINEST函數(shù)是用直線來(lái)擬合數(shù)據(jù)，而LOGEST函數(shù)則以指數(shù)曲線來(lái)擬合數(shù)據(jù)。當(dāng)僅有一個(gè)自變量x時(shí)，可直接用下面的公式計(jì)算出斜率(m)和y軸截距(b)的值。函數(shù)說(shuō)明：26斜率(m)：INDEX(LOGEST(known_y's,known_x's),1)Y軸截距(b)：INDEX(LOGEST(known_y's,known_x's),2)可用y=b*m＾x公式來(lái)預(yù)測(cè)y的值，但是MicrosoftExcel另外提供了可以預(yù)測(cè)因變量y值的GROWTH函數(shù)。對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。當(dāng)需要輸入一個(gè)數(shù)組常量（如known_x's）作為參數(shù)時(shí)，以逗號(hào)作為同一行中數(shù)據(jù)的分隔符，以分號(hào)作為不同行數(shù)據(jù)的分隔符。斜率(m)：27《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件28《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件29⒊TREND函數(shù)TREND函數(shù)是返回一條線性回歸擬合線的值。即找到適合已知數(shù)組known_y's和known_x's的直線（用最小二乘法），并返回指定數(shù)組new_x's在直線上對(duì)應(yīng)的y值。函數(shù)語(yǔ)法：TREND(known_y's,known_x's,new_x's,const)Known_y's是關(guān)系表達(dá)式y(tǒng)=mx+b中已知的y值集合。⒊TREND函數(shù)30如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組known-y's在單獨(dú)一行中，則known-x's的每一行被視為一個(gè)獨(dú)立的變量。Known_x's是關(guān)系表達(dá)式y(tǒng)=mx+b中已知的可選x值集合。數(shù)組known_x's可以包含一組或多組變量。如果只用到一個(gè)變量，只要known_y's和known_x's維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，known_y's必須為向量（即必須為一行或一列）。如果數(shù)組known_y's在單獨(dú)一列中，則known31如果省略known_x's，則假設(shè)該數(shù)組為{1,2,3,...}，其大小與known_y's相同。New_x's為需要函數(shù)TREND返回對(duì)應(yīng)y值的新x值。New_x's與known_x's一樣，每個(gè)獨(dú)立變量必須為單獨(dú)的一行（或一列）。因此，如果known_y's是單列的，known_x's和new_x's應(yīng)該有同樣的列數(shù)。如果known_y's是單行的，known_x's和new_x's應(yīng)該有同樣的行數(shù)。如果省略new_x's，將假設(shè)它和known_x's一樣。如果known_x's和new_x's都省略，將假設(shè)它們?yōu)閿?shù)組{1,2,3,...}，大小與known_y's相同。如果省略known_x's，則假設(shè)該數(shù)組為{1,32Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，b將被設(shè)為0（零），m將被調(diào)整以使y=mx。Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為33函數(shù)說(shuō)明：可以使用TREND函數(shù)計(jì)算同一變量的不同乘方的回歸值來(lái)擬合多項(xiàng)式曲線。例如，假設(shè)A列包含y值，B列含有x值。可以在C列中輸入x^2，在D列中輸入x^3，等等，然后根據(jù)A列，對(duì)B列到D列進(jìn)行回歸計(jì)算。對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。當(dāng)為參數(shù)（如known_x's）輸入數(shù)組常量時(shí)，應(yīng)當(dāng)使用逗號(hào)分隔同一行中的數(shù)據(jù)，用分號(hào)分隔不同行中的數(shù)據(jù)。函數(shù)說(shuō)明：34《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件35⒋FORECAST函數(shù)FORECAST函數(shù)根據(jù)已有的數(shù)值計(jì)算或預(yù)測(cè)未來(lái)值。此預(yù)測(cè)值為基于給定的x值推導(dǎo)出的y值。已知的數(shù)值為已有的x值和y值，再利用線性回歸對(duì)新值進(jìn)行預(yù)測(cè)。可以使用該函數(shù)對(duì)未來(lái)銷(xiāo)售額、庫(kù)存需求或消費(fèi)趨勢(shì)進(jìn)行預(yù)測(cè)。函數(shù)語(yǔ)法：FORECAST(x,known_y's,known_x's)X為需要進(jìn)行預(yù)測(cè)的數(shù)據(jù)點(diǎn)。Known_y's因變量數(shù)組或數(shù)據(jù)區(qū)域。Known_x's為自變量數(shù)組或數(shù)據(jù)區(qū)域。⒋FORECAST函數(shù)36函數(shù)說(shuō)明：如果x為非數(shù)值型，函數(shù)FORECAST返回錯(cuò)誤值#VALUE!。如果known_y's和known_x's為空或含有不同個(gè)數(shù)的數(shù)據(jù)點(diǎn)，函數(shù)FORECAST返回錯(cuò)誤值#N/A。如果known_x's的方差為零，函數(shù)FORECAST返回錯(cuò)誤值#DIV/0!。函數(shù)說(shuō)明：37《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件38⒌SLOPE函數(shù)SLOPE函數(shù)是返回根據(jù)known_y's和known_x's中的數(shù)據(jù)點(diǎn)擬合的線性回歸直線的斜率。斜率為直線上任意兩點(diǎn)的重直距離與水平距離的比值，也就是回歸直線的變化率。函數(shù)語(yǔ)法：SLOPE(known_y's,known_x's)Known_y's為數(shù)字型因變量數(shù)據(jù)點(diǎn)數(shù)組或單元格區(qū)域。Known_x's為自變量數(shù)據(jù)點(diǎn)集合。⒌SLOPE函數(shù)39函數(shù)說(shuō)明：參數(shù)可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的單元格將計(jì)算在內(nèi)。如果known_y's和known_x's為空或其數(shù)據(jù)點(diǎn)個(gè)數(shù)不同，函數(shù)SLOPE返回錯(cuò)誤值#N/A。函數(shù)說(shuō)明：40《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件41⒍INTERCEPT函數(shù)INTERCEPT函數(shù)是利用現(xiàn)有的x值與y值計(jì)算直線與y軸的截距。截距為穿過(guò)已知的known_x's和known_y's數(shù)據(jù)點(diǎn)的線性回歸線與y軸的交點(diǎn)。當(dāng)自變量為0（零）時(shí)，使用INTERCEPT函數(shù)可以決定因變量的值。函數(shù)語(yǔ)法：INTERCEPT(known_y's,known_x's)Known_y's為因變的觀察值或數(shù)據(jù)集合。Known_x's為自變的觀察值或數(shù)據(jù)集合。⒍INTERCEPT函數(shù)42函數(shù)說(shuō)明：參數(shù)可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的單元格將計(jì)算在內(nèi)。如果known_y's和known_x's所包含的數(shù)據(jù)點(diǎn)個(gè)數(shù)不相等或不包含任何數(shù)據(jù)點(diǎn)，則函數(shù)INTERCEPT返回錯(cuò)誤值#N/A。函數(shù)說(shuō)明：43《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件443單元預(yù)測(cè)模型設(shè)計(jì)－規(guī)劃求解利用Excel提供的各種預(yù)測(cè)分析工具解決財(cái)務(wù)預(yù)測(cè)中的實(shí)際問(wèn)題時(shí)，對(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)換為線性的做法是存在一定的缺陷的。此外，有些非線性問(wèn)題根本無(wú)法直接轉(zhuǎn)換為線性問(wèn)題，除非作出大量的簡(jiǎn)化，這必然使得到的回歸方程嚴(yán)重失真。3單元預(yù)測(cè)模型設(shè)計(jì)－規(guī)劃求解利用Excel提45因此，對(duì)于非線性回歸問(wèn)題，最好的方法是直接進(jìn)行回歸分析，即求解使殘差平方和最小、或使相關(guān)系數(shù)最大的回歸方程，但非線性回歸過(guò)程是一個(gè)循環(huán)尋優(yōu)過(guò)程，需要先設(shè)置回歸方程系數(shù)的初值，然后計(jì)算觀測(cè)值與預(yù)測(cè)值的殘差平方和，不斷尋找使殘差平方和最小的回歸方程系數(shù)，利用Excel的規(guī)劃求解工具即可解決這類問(wèn)題。因此，對(duì)于非線性回歸問(wèn)題，最好的方法是直接進(jìn)行回歸分46知識(shí)點(diǎn)講解SUMXMY2函數(shù)SUMXMY2函數(shù)是返回兩數(shù)組中對(duì)應(yīng)數(shù)值之差的平方和。函數(shù)語(yǔ)法：SUMXMY2(array_x,array_y)Array_x為第一個(gè)數(shù)組或數(shù)值區(qū)域。Array_y為第二個(gè)數(shù)組或數(shù)值區(qū)域。知識(shí)點(diǎn)講解47函數(shù)說(shuō)明：參數(shù)可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的單元格將計(jì)算在內(nèi)。如果array_x和array_y的元素?cái)?shù)目不同，函數(shù)SUMXMY2將返回錯(cuò)誤值#N/A。函數(shù)說(shuō)明：48《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件49學(xué)習(xí)目標(biāo)學(xué)習(xí)完本章后，學(xué)生應(yīng)當(dāng)能夠獨(dú)立運(yùn)用分析工具、函數(shù)、規(guī)劃求解等工具進(jìn)行財(cái)務(wù)預(yù)測(cè)模型設(shè)計(jì)，包括：一元線性回歸模型、一元非線性回歸模型、多元線性回歸模型，并在設(shè)計(jì)過(guò)程中繪制相應(yīng)的擬合圖形。第7章財(cái)務(wù)預(yù)測(cè)模型設(shè)計(jì)學(xué)習(xí)目標(biāo)第7章財(cái)務(wù)預(yù)測(cè)模型設(shè)計(jì)50第1單元預(yù)測(cè)模型設(shè)計(jì)－分析工具財(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)略的基本要素。Excel的數(shù)據(jù)分析工具庫(kù)提供了3種統(tǒng)計(jì)觀測(cè)分析工具，它們是移動(dòng)平均法、指數(shù)平滑法和回歸分析法。第1單元預(yù)測(cè)模型設(shè)計(jì)－分析工具財(cái)務(wù)預(yù)測(cè)，是指對(duì)企業(yè)未來(lái)的51⒈移動(dòng)平均法移動(dòng)平均法是一種改良的算術(shù)平均法，是一種最簡(jiǎn)單的自適應(yīng)預(yù)測(cè)模型。它根據(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)平均法52移動(dòng)平均法根據(jù)預(yù)測(cè)時(shí)使用的各元素的權(quán)重不同，可以分為：簡(jiǎn)單移動(dòng)平均和加權(quán)移動(dòng)平均。移動(dòng)平均法根據(jù)預(yù)測(cè)時(shí)使用的各元素的權(quán)重不同，可以分為：簡(jiǎn)單移53⒉指數(shù)平滑法指數(shù)平滑法是在移動(dòng)平均法基礎(chǔ)上發(fā)展起來(lái)的一種時(shí)間序列分析預(yù)測(cè)法，它是通過(guò)計(jì)算指數(shù)平滑值，配合一定的時(shí)間序列預(yù)測(cè)模型對(duì)現(xiàn)象的未來(lái)進(jìn)行預(yù)測(cè)。其原理是任一期的指數(shù)平滑值都是本期實(shí)際觀察值與前一期指數(shù)平滑值的加權(quán)平均。⒉指數(shù)平滑法54⒊回歸分析預(yù)測(cè)法回歸分析預(yù)測(cè)法是通過(guò)研究?jī)山M或兩組以上變量之間的關(guān)系，建立相應(yīng)的回歸預(yù)測(cè)模型，對(duì)變量進(jìn)行預(yù)測(cè)的一種預(yù)測(cè)方法。⒊回歸分析預(yù)測(cè)法55第2單元預(yù)測(cè)模型設(shè)計(jì)－函數(shù)法Excel提供了關(guān)于估計(jì)線性模型和指數(shù)模型參數(shù)的幾個(gè)預(yù)測(cè)函數(shù)。線性模型和指數(shù)模型的數(shù)學(xué)表達(dá)式如下：線性模型：y=mx+b或y=m1x1+m2x2+…+b第2單元預(yù)測(cè)模型設(shè)計(jì)－函數(shù)法Excel提供了關(guān)于估計(jì)56指數(shù)模型：式中，y為因變量；x是自變量；m、m1、...、mn-1、mn、b分別為預(yù)測(cè)模型的待估計(jì)參數(shù)。Excel提供的預(yù)測(cè)函數(shù)主要有LINEST函數(shù)、LOGEST函數(shù)、TREND函數(shù)、FORECAST函數(shù)、SLOPE函數(shù)和INTERCEPT函數(shù)，它們所使用的參數(shù)都基本相同，如表7－1、7－2、7－3所示。指數(shù)模型：57

表7－1預(yù)測(cè)函數(shù)的參數(shù)及含義表7－1預(yù)測(cè)函數(shù)的參數(shù)及含義58

表7－2回歸統(tǒng)計(jì)值返回的順序表7－2回歸統(tǒng)計(jì)值返回的順序59《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件60知識(shí)點(diǎn)講解⒈LINEST函數(shù)LINEST函數(shù)是常用的統(tǒng)計(jì)函數(shù)之一，它是使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。函數(shù)語(yǔ)法：LINEST(known_y's,known_x's,const,stats)Known_y's是關(guān)系表達(dá)式y(tǒng)=ax+b中已知的y值集合。知識(shí)點(diǎn)講解61如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組known-y's在單獨(dú)一行中，則known-x's的每一行被視為一個(gè)獨(dú)立的變量。Known_x's是關(guān)系表達(dá)式y(tǒng)=ax+b中已知的可選x值集合。數(shù)組known_x's可以包含一組或多組變量。如果只用到一個(gè)變量，只要known_y's和known_x's維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，則known_y's必須為向量（即必須為一行或一列）。如果數(shù)組known_y's在單獨(dú)一列中，則kn62如果省略known_x's，則假設(shè)該數(shù)組為{1,2,3,...}，其大小與known_y's相同。Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，b將被設(shè)為0，并同時(shí)調(diào)整m值使y=mx。Stats為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。如果stats為T(mén)RUE，則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值，這時(shí)返回的數(shù)組為{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats為FALSE或省略，LINEST函數(shù)只返回系數(shù)m和常量b。如果省略known_x's，則假設(shè)該數(shù)組為{1,63函數(shù)說(shuō)明：可以使用斜率和y軸截距描述任何直線：斜率(a)：

通常記為a，如果需要計(jì)算斜率，則選取直線上的兩點(diǎn)，(x1,y1)和(x2,y2)；斜率等于(y2-y1)/(x2-x1)。

函數(shù)說(shuō)明：64Y軸截距(b)：通常記為b，直線的y軸的截距為直線通過(guò)y軸時(shí)與y軸交點(diǎn)的數(shù)值。直線的公式為y=ax+b。如果知道了a和b的值，將y或x的值代入公式就可計(jì)算出直線上的任意一點(diǎn)。還可以使用TREND函數(shù)。當(dāng)只有一個(gè)自變量x時(shí)，可直接利用下面公式得到斜率和y軸截距值：

Y軸截距(b)：65斜率：

=INDEX(LINEST(known_y's,known_x's),1)Y軸截距：

=INDEX(LINEST(known_y's,known_x's),2)斜率：

=INDEX(LINEST(known_y's66數(shù)據(jù)的離散程度決定了LINEST函數(shù)計(jì)算的精確度。數(shù)據(jù)越接近線性，LINEST模型就越精確。LINEST函數(shù)使用最小二乘法來(lái)判定最適合數(shù)據(jù)的模型。數(shù)據(jù)的離散程度決定了LINEST函數(shù)計(jì)算的精確度。67回歸分析時(shí)，MicrosoftExcel計(jì)算每一點(diǎn)的y的估計(jì)值和實(shí)際值的平方差。這些平方差之和稱為殘差平方和(ssresid)。然后MicrosoftExcel計(jì)算總平方和(sstotal)。當(dāng)const=TRUE或被刪除時(shí)，總平方和是y的實(shí)際值和平均值的平方差之和。當(dāng)const=FALSE時(shí)，總平方和是y的實(shí)際值的平方和（不需要從每個(gè)y值中減去平均值）。回歸平方和(ssreg)可通過(guò)公式ssreg=sstotal-ssresid計(jì)算出來(lái)。殘差平方和與總平方和的比值越小，判定系數(shù)r2的值就越大，r2是表示回歸分析公式的結(jié)果反映變量間關(guān)系的程度的標(biāo)志。r2等于ssreg/sstotal。回歸分析時(shí)，MicrosoftExcel計(jì)算每68《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件69《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件70⒉LOGEST函數(shù)LOGEST函數(shù)是在回歸分析中，計(jì)算最符合數(shù)據(jù)的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，故必須以數(shù)組公式的形式輸入。此曲線的公式為：y=b*m^x或y=(b*(m1^x1)*(m2^x2)*_)（如果有多個(gè)x值）其中因變量y是自變量x的函數(shù)值。m值是各指數(shù)x的底，而b值是常量值。注意：公式中的y、x和m均可以是向量，LOGEST函數(shù)返回的數(shù)組為{mn,mn-i,...,m1,b}。⒉LOGEST函數(shù)71函數(shù)語(yǔ)法：LOGEST(known_y's,known_x's,const,stats)Known_y's滿足指數(shù)回歸擬合曲線y=b*m^x的一組已知的y值。如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組known-y's在單獨(dú)一行中，則known-x's的每一行被視為一個(gè)獨(dú)立的變量。Known_x's滿足指數(shù)回歸擬合曲線y=b*m^x的一組已知的x值，為可選參數(shù)。函數(shù)語(yǔ)法：72known_x's數(shù)組可以包括一組或多組自變量。如果僅使用一個(gè)變量，那么只要known_x's和known_y's具有相同的維數(shù)，則它們可以是任何形狀的區(qū)域。如果使用多個(gè)變量，則known_y's必須是向量（即具有一列高度或一行寬度的單元格區(qū)域）。如果省略known_x's，則假設(shè)該數(shù)組為{1,2,3,...}，其大小與known_y's相同。Const為一邏輯值，用于指定是否將常數(shù)b強(qiáng)制設(shè)為1。known_x's數(shù)組可以包括一組或多組自變量。如73如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，則常量b將設(shè)為1，而m的值滿足公式y(tǒng)=m＾x。Stats為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。如果stats為T(mén)RUE，函數(shù)LOGEST將返回附加的回歸統(tǒng)計(jì)值，因此返回的數(shù)組為{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats為FALSE或省略，則函數(shù)LOGSET只返回系數(shù)m和常量b。如果const為T(mén)RUE或省略，b將按正常計(jì)算。74函數(shù)說(shuō)明：由數(shù)據(jù)繪出的圖越近似于指數(shù)曲線，則計(jì)算出來(lái)的曲線就越符合原來(lái)給定的數(shù)據(jù)。正如LINEST函數(shù)一樣，LOGEST函數(shù)返回一組描述數(shù)值間相互關(guān)系的數(shù)值數(shù)組，但LINEST函數(shù)是用直線來(lái)擬合數(shù)據(jù)，而LOGEST函數(shù)則以指數(shù)曲線來(lái)擬合數(shù)據(jù)。當(dāng)僅有一個(gè)自變量x時(shí)，可直接用下面的公式計(jì)算出斜率(m)和y軸截距(b)的值。函數(shù)說(shuō)明：75斜率(m)：INDEX(LOGEST(known_y's,known_x's),1)Y軸截距(b)：INDEX(LOGEST(known_y's,known_x's),2)可用y=b*m＾x公式來(lái)預(yù)測(cè)y的值，但是MicrosoftExcel另外提供了可以預(yù)測(cè)因變量y值的GROWTH函數(shù)。對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。當(dāng)需要輸入一個(gè)數(shù)組常量（如known_x's）作為參數(shù)時(shí)，以逗號(hào)作為同一行中數(shù)據(jù)的分隔符，以分號(hào)作為不同行數(shù)據(jù)的分隔符。斜率(m)：76《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件77《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件78⒊TREND函數(shù)TREND函數(shù)是返回一條線性回歸擬合線的值。即找到適合已知數(shù)組known_y's和known_x's的直線（用最小二乘法），并返回指定數(shù)組new_x's在直線上對(duì)應(yīng)的y值。函數(shù)語(yǔ)法：TREND(known_y's,known_x's,new_x's,const)Known_y's是關(guān)系表達(dá)式y(tǒng)=mx+b中已知的y值集合。⒊TREND函數(shù)79如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組known-y's在單獨(dú)一行中，則known-x's的每一行被視為一個(gè)獨(dú)立的變量。Known_x's是關(guān)系表達(dá)式y(tǒng)=mx+b中已知的可選x值集合。數(shù)組known_x's可以包含一組或多組變量。如果只用到一個(gè)變量，只要known_y's和known_x's維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，known_y's必須為向量（即必須為一行或一列）。如果數(shù)組known_y's在單獨(dú)一列中，則known80如果省略known_x's，則假設(shè)該數(shù)組為{1,2,3,...}，其大小與known_y's相同。New_x's為需要函數(shù)TREND返回對(duì)應(yīng)y值的新x值。New_x's與known_x's一樣，每個(gè)獨(dú)立變量必須為單獨(dú)的一行（或一列）。因此，如果known_y's是單列的，known_x's和new_x's應(yīng)該有同樣的列數(shù)。如果known_y's是單行的，known_x's和new_x's應(yīng)該有同樣的行數(shù)。如果省略new_x's，將假設(shè)它和known_x's一樣。如果known_x's和new_x's都省略，將假設(shè)它們?yōu)閿?shù)組{1,2,3,...}，大小與known_y's相同。如果省略known_x's，則假設(shè)該數(shù)組為{1,81Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，b將被設(shè)為0（零），m將被調(diào)整以使y=mx。Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為82函數(shù)說(shuō)明：可以使用TREND函數(shù)計(jì)算同一變量的不同乘方的回歸值來(lái)擬合多項(xiàng)式曲線。例如，假設(shè)A列包含y值，B列含有x值。可以在C列中輸入x^2，在D列中輸入x^3，等等，然后根據(jù)A列，對(duì)B列到D列進(jìn)行回歸計(jì)算。對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。當(dāng)為參數(shù)（如known_x's）輸入數(shù)組常量時(shí)，應(yīng)當(dāng)使用逗號(hào)分隔同一行中的數(shù)據(jù)，用分號(hào)分隔不同行中的數(shù)據(jù)。函數(shù)說(shuō)明：83《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件84⒋FORECAST函數(shù)FORECAST函數(shù)根據(jù)已有的數(shù)值計(jì)算或預(yù)測(cè)未來(lái)值。此預(yù)測(cè)值為基于給定的x值推導(dǎo)出的y值。已知的數(shù)值為已有的x值和y值，再利用線性回歸對(duì)新值進(jìn)行預(yù)測(cè)。可以使用該函數(shù)對(duì)未來(lái)銷(xiāo)售額、庫(kù)存需求或消費(fèi)趨勢(shì)進(jìn)行預(yù)測(cè)。函數(shù)語(yǔ)法：FORECAST(x,known_y's,known_x's)X為需要進(jìn)行預(yù)測(cè)的數(shù)據(jù)點(diǎn)。Known_y's因變量數(shù)組或數(shù)據(jù)區(qū)域。Known_x's為自變量數(shù)組或數(shù)據(jù)區(qū)域。⒋FORECAST函數(shù)85函數(shù)說(shuō)明：如果x為非數(shù)值型，函數(shù)FORECAST返回錯(cuò)誤值#VALUE!。如果known_y's和known_x's為空或含有不同個(gè)數(shù)的數(shù)據(jù)點(diǎn)，函數(shù)FORECAST返回錯(cuò)誤值#N/A。如果known_x's的方差為零，函數(shù)FORECAST返回錯(cuò)誤值#DIV/0!。函數(shù)說(shuō)明：86《Excel在財(cái)務(wù)管理中高