《Excel在財(cái)務(wù)管理中高級(jí)應(yīng)用》第7章課件

1、學(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ì)第1頁(yè)，共49頁(yè)。第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ù)平滑法和回歸分析法。第2頁(yè)，共49頁(yè)。 移動(dòng)平均法移動(dòng)平均法是一種改良的算術(shù)平均法，是一種最簡(jiǎn)單的自適應(yīng)預(yù)

2、測(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è)值帶有明顯的滯后偏差。第3頁(yè)，共49頁(yè)。移動(dòng)平均法根據(jù)預(yù)測(cè)時(shí)使用的各元素的權(quán)重不同，可以分為：簡(jiǎn)單移動(dòng)平均和加權(quán)移動(dòng)平均。第4頁(yè)，共49頁(yè)。 指數(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)平均。第5頁(yè)，共49頁(yè)。 回歸分析預(yù)測(cè)

3、法 回歸分析預(yù)測(cè)法是通過(guò)研究?jī)山M或兩組以上變量之間的關(guān)系，建立相應(yīng)的回歸預(yù)測(cè)模型，對(duì)變量進(jìn)行預(yù)測(cè)的一種預(yù)測(cè)方法。第6頁(yè)，共49頁(yè)。第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第7頁(yè)，共49頁(yè)。 指數(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ù)，

4、它們所使用的參數(shù)都基本相同，如表71、72、73所示。第8頁(yè)，共49頁(yè)。 表71 預(yù)測(cè)函數(shù)的參數(shù)及含義第9頁(yè)，共49頁(yè)。 表72 回歸統(tǒng)計(jì)值返回的順序第10頁(yè)，共49頁(yè)。第11頁(yè)，共49頁(yè)。知識(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_ys,known_xs,const,stats)Known_ys 是關(guān)系表達(dá)式 y = ax + b 中已知的 y 值集合。第12頁(yè)，共49頁(yè)。 如果數(shù)組 known_ys 在單獨(dú)一

5、列中，則 known_xs 的每一列被視為一個(gè)獨(dú)立的變量。 如果數(shù)組 known-ys 在單獨(dú)一行中，則 known-xs 的每一行被視為一個(gè)獨(dú)立的變量。Known_xs是關(guān)系表達(dá)式 y = ax + b 中已知的可選 x 值集合。數(shù)組 known_xs 可以包含一組或多組變量。如果只用到一個(gè)變量，只要 known_ys 和known_xs 維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，則 known_ys 必須為向量（即必須為一行或一列）。第13頁(yè)，共49頁(yè)。 如果省略 known_xs，則假設(shè)該數(shù)組為 1,2,3,.，其大小與 known_ys 相同。Const 為一邏輯值，用于指

6、定是否將常量 b 強(qiáng)制設(shè)為 0。 如果 const 為 TRUE 或省略，b 將按正常計(jì)算。 如果 const 為 FALSE，b 將被設(shè)為 0，并同時(shí)調(diào)整 m 值使 y = mx。Stats 為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。 如果 stats 為 TRUE，則 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。第14頁(yè)，共49頁(yè)。 函數(shù)說(shuō)明：可以使用斜率和 y 軸截距描

7、述任何直線： 斜率 (a)：通常記為 a，如果需要計(jì)算斜率，則選取直線上的兩點(diǎn)，(x1,y1) 和 (x2,y2)；斜率等于 (y2 - y1)/(x2 - x1)。第15頁(yè)，共49頁(yè)。Y 軸截距 (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 軸截距值： 第16頁(yè)，共49頁(yè)。 斜率：=INDEX(LINEST(known_ys,known_xs),

8、1) Y 軸截距：=INDEX(LINEST(known_ys,known_xs),2)第17頁(yè)，共49頁(yè)。 數(shù)據(jù)的離散程度決定了 LINEST 函數(shù)計(jì)算的精確度。數(shù)據(jù)越接近線性，LINEST 模型就越精確。LINEST 函數(shù)使用最小二乘法來(lái)判定最適合數(shù)據(jù)的模型。 第18頁(yè)，共49頁(yè)。 回歸分析時(shí)，Microsoft Excel 計(jì)算每一點(diǎn)的 y 的估計(jì)值和實(shí)際值的平方差。這些平方差之和稱為殘差平方和 (ssresid)。然后 Microsoft Excel 計(jì)算總平方和 (sstotal)。當(dāng) const = TRUE 或被刪除時(shí)，總平方和是 y 的實(shí)際值和平均值的平方差之和。當(dāng) const

9、 = FALSE 時(shí)，總平方和是 y 的實(shí)際值的平方和（不需要從每個(gè) y 值中減去平均值）?；貧w平方和 (ssreg) 可通過(guò)公式 ssreg = sstotal - ssresid 計(jì)算出來(lái)。殘差平方和與總平方和的比值越小，判定系數(shù) r2 的值就越大，r2 是表示回歸分析公式的結(jié)果反映變量間關(guān)系的程度的標(biāo)志。r2 等于 ssreg/sstotal。 第19頁(yè)，共49頁(yè)。第20頁(yè)，共49頁(yè)。第21頁(yè)，共49頁(yè)。 LOGEST 函數(shù) LOGEST 函數(shù)是在回歸分析中，計(jì)算最符合數(shù)據(jù)的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，故必須以數(shù)組公式的形式輸入。 此曲線的公式為

10、： y = b*mx 或y = (b*(m1x1)*(m2x2)*_)（如果有多個(gè) x 值）其中因變量 y 是自變量 x 的函數(shù)值。m 值是各指數(shù) x 的底，而 b 值是常量值。注意：公式中的 y、x 和 m 均可以是向量，LOGEST 函數(shù)返回的數(shù)組為 mn,mn-i,.,m1,b。第22頁(yè)，共49頁(yè)。函數(shù)語(yǔ)法：LOGEST(known_ys,known_xs,const,stats)Known_ys 滿足指數(shù)回歸擬合曲線 y=b*mx 的一組已知的 y 值。如果數(shù)組 known_ys 在單獨(dú)一列中，則 known_xs 的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組 known-ys 在單獨(dú)一行中

11、，則 known-xs 的每一行被視為一個(gè)獨(dú)立的變量。Known_xs 滿足指數(shù)回歸擬合曲線 y=b*mx 的一組已知的 x 值，為可選參數(shù)。第23頁(yè)，共49頁(yè)。 known_xs 數(shù)組可以包括一組或多組自變量。如果僅使用一個(gè)變量，那么只要 known_xs 和 known_ys 具有相同的維數(shù)，則它們可以是任何形狀的區(qū)域。如果使用多個(gè)變量，則 known_ys 必須是向量（即具有一列高度或一行寬度的單元格區(qū)域）。如果省略 known_xs，則假設(shè)該數(shù)組為 1,2,3,.，其大小與 known_ys 相同。 Const 為一邏輯值，用于指定是否將常數(shù) b 強(qiáng)制設(shè)為 1。第24頁(yè)，共49頁(yè)。如果

12、 const 為 TRUE 或省略，b 將按正常計(jì)算。如果 const 為 FALSE，則常量 b 將設(shè)為 1，而 m 的值滿足公式 y=mx。 Stats 為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值。 如果 stats 為 TRUE，函數(shù) LOGEST 將返回附加的回歸統(tǒng)計(jì)值，因此返回的數(shù)組為 mn,mn-1,.,m1,b;sen,sen-1,.,se1,seb;r 2,sey; F,df;ssreg,ssresid。 如果 stats 為 FALSE 或省略，則函數(shù) LOGSET 只返回系數(shù) m 和常量 b。第25頁(yè)，共49頁(yè)。 函數(shù)說(shuō)明： 由數(shù)據(jù)繪出的圖越近似于指數(shù)曲線，則計(jì)算出來(lái)的曲線就越

13、符合原來(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) 的值。 第26頁(yè)，共49頁(yè)。斜率 (m)：INDEX(LOGEST(known_ys,known_xs),1)Y 軸截距 (b)：INDEX(LOGEST(known_ys,known_xs),2) 可用 y=b*mx 公式來(lái)預(yù)測(cè) y 的值，但是 Microsoft Excel 另外提供了可以預(yù)測(cè)因變量 y 值的 G

14、ROWTH 函數(shù)。 對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。 當(dāng)需要輸入一個(gè)數(shù)組常量（如 known_xs）作為參數(shù)時(shí)，以逗號(hào)作為同一行中數(shù)據(jù)的分隔符，以分號(hào)作為不同行數(shù)據(jù)的分隔符。第27頁(yè)，共49頁(yè)。第28頁(yè)，共49頁(yè)。第29頁(yè)，共49頁(yè)。TREND函數(shù)TREND函數(shù)是返回一條線性回歸擬合線的值。即找到適合已知數(shù)組 known_ys 和 known_xs 的直線（用最小二乘法），并返回指定數(shù)組 new_xs 在直線上對(duì)應(yīng)的 y 值。函數(shù)語(yǔ)法：TREND(known_ys,known_xs,new_xs,const)Known_ys 是關(guān)系表達(dá)式 y = mx + b 中已知的 y

15、 值集合。第30頁(yè)，共49頁(yè)。如果數(shù)組 known_ys 在單獨(dú)一列中，則 known_xs 的每一列被視為一個(gè)獨(dú)立的變量。如果數(shù)組 known-ys 在單獨(dú)一行中，則 known-xs 的每一行被視為一個(gè)獨(dú)立的變量。 Known_xs 是關(guān)系表達(dá)式 y = mx + b 中已知的可選 x 值集合。 數(shù)組 known_xs 可以包含一組或多組變量。如果只用到一個(gè)變量，只要 known_ys 和 known_xs 維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，known_ys 必須為向量（即必須為一行或一列）。第31頁(yè)，共49頁(yè)。 如果省略 known_xs，則假設(shè)該數(shù)組為 1,2,3,

16、.，其大小與 known_ys 相同。New_xs 為需要函數(shù) TREND 返回對(duì)應(yīng) y 值的新 x 值。New_xs 與 known_xs 一樣，每個(gè)獨(dú)立變量必須為單獨(dú)的一行（或一列）。因此，如果 known_ys 是單列的，known_xs 和 new_xs 應(yīng)該有同樣的列數(shù)。如果 known_ys 是單行的，known_xs 和 new_xs 應(yīng)該有同樣的行數(shù)。 如果省略 new_xs，將假設(shè)它和 known_xs 一樣。 如果 known_xs 和 new_xs 都省略，將假設(shè)它們?yōu)閿?shù)組 1,2,3,.，大小與 known_ys 相同。第32頁(yè)，共49頁(yè)。Const 為一邏輯值，用于指

17、定是否將常量 b 強(qiáng)制設(shè)為 0。如果 const 為 TRUE 或省略，b 將按正常計(jì)算。如果 const 為 FALSE，b 將被設(shè)為 0（零），m 將被調(diào)整以使 y = mx。第33頁(yè)，共49頁(yè)。函數(shù)說(shuō)明：可以使用 TREND 函數(shù)計(jì)算同一變量的不同乘方的回歸值來(lái)擬合多項(xiàng)式曲線。例如，假設(shè) A 列包含 y 值，B 列含有 x 值?？梢栽?C 列中輸入 x2，在 D 列中輸入 x3，等等，然后根據(jù) A 列，對(duì) B 列到 D 列進(jìn)行回歸計(jì)算。 對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。 當(dāng)為參數(shù)（如 known_xs）輸入數(shù)組常量時(shí)，應(yīng)當(dāng)使用逗號(hào)分隔同一行中的數(shù)據(jù)，用分號(hào)分隔不同行中

18、的數(shù)據(jù)。第34頁(yè)，共49頁(yè)。第35頁(yè)，共49頁(yè)。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)銷售額、庫(kù)存需求或消費(fèi)趨勢(shì)進(jìn)行預(yù)測(cè)。函數(shù)語(yǔ)法：FORECAST(x,known_ys,known_xs)X 為需要進(jìn)行預(yù)測(cè)的數(shù)據(jù)點(diǎn)。Known_ys 因變量數(shù)組或數(shù)據(jù)區(qū)域。Known_xs 為自變量數(shù)組或數(shù)據(jù)區(qū)域。第36頁(yè)，共49頁(yè)。函數(shù)說(shuō)明：如果 x 為非數(shù)值型，函數(shù) FORECAST 返回錯(cuò)誤值 #VALUE!。 如果 known_ys 和

19、 known_xs 為空或含有不同個(gè)數(shù)的數(shù)據(jù)點(diǎn)，函數(shù) FORECAST 返回錯(cuò)誤值 #N/A。 如果 known_xs 的方差為零，函數(shù) FORECAST 返回錯(cuò)誤值 #DIV/0!。 第37頁(yè)，共49頁(yè)。第38頁(yè)，共49頁(yè)。SLOPE函數(shù)SLOPE函數(shù)是返回根據(jù) known_ys 和 known_xs 中的數(shù)據(jù)點(diǎn)擬合的線性回歸直線的斜率。斜率為直線上任意兩點(diǎn)的重直距離與水平距離的比值，也就是回歸直線的變化率。函數(shù)語(yǔ)法：SLOPE(known_ys,known_xs)Known_ys 為數(shù)字型因變量數(shù)據(jù)點(diǎn)數(shù)組或單元格區(qū)域。Known_xs 為自變量數(shù)據(jù)點(diǎn)集合。第39頁(yè)，共49頁(yè)。函數(shù)說(shuō)明：參數(shù)

20、可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。 如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的單元格將計(jì)算在內(nèi)。 如果 known_ys 和 known_xs 為空或其數(shù)據(jù)點(diǎn)個(gè)數(shù)不同，函數(shù) SLOPE 返回錯(cuò)誤值 #N/A。 第40頁(yè)，共49頁(yè)。第41頁(yè)，共49頁(yè)。INTERCEPT函數(shù)INTERCEPT函數(shù)是利用現(xiàn)有的 x 值與 y 值計(jì)算直線與 y 軸的截距。截距為穿過(guò)已知的 known_xs 和 known_ys 數(shù)據(jù)點(diǎn)的線性回歸線與 y 軸的交點(diǎn)。當(dāng)自變量為 0（零）時(shí)，使用 INTERCEPT 函數(shù)可以決定因變量的值。函數(shù)語(yǔ)法：INTERCEPT(known_ys,known_xs)Known_ys 為因變的觀察值或數(shù)據(jù)集合。Known_xs 為自變的觀察值或數(shù)據(jù)集合。第42頁(yè)，共49頁(yè)。函數(shù)說(shuō)明：參數(shù)可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。 如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的單元格將計(jì)算在內(nèi)。 如果 known_ys 和 known_xs 所包含的數(shù)據(jù)點(diǎn)個(gè)數(shù)不相等或不包含任何數(shù)據(jù)點(diǎn)，則函數(shù) INTERCEPT 返回錯(cuò)誤值 #N/A。 第43頁(yè)，共49頁(yè)。第44頁(yè)，共