《Excel在財務管理中高級應用》第7章課件

1、學習目標學習完本章后，學生應當能夠獨立運用分析工具、函數(shù)、規(guī)劃求解等工具進行財務預測模型設(shè)計，包括：一元線性回歸模型、一元非線性回歸模型、多元線性回歸模型，并在設(shè)計過程中繪制相應的擬合圖形。第7章 財務預測模型設(shè)計第1頁，共49頁。第1單元 預測模型設(shè)計分析工具財務預測，是指對企業(yè)未來的收入、成本、利潤、現(xiàn)金流量及融資需求等財務指標所作的估計和推測。財務預測是編制投資和融資計劃的基礎(chǔ)，是公司制訂成長戰(zhàn)略的基本要素。Excel的數(shù)據(jù)分析工具庫提供了3種統(tǒng)計觀測分析工具，它們是移動平均法、指數(shù)平滑法和回歸分析法。第2頁，共49頁。 移動平均法移動平均法是一種改良的算術(shù)平均法，是一種最簡單的自適應預

2、測模型。它根據(jù)近期數(shù)據(jù)對預測值影響較大，而遠期數(shù)據(jù)對預測值影響較小的事實，把平均數(shù)逐期移動。移動期數(shù)的大小視具體情況而定，移動期數(shù)少，能快速地反映變化，但不能反映變化趨勢；移動期數(shù)多，能反映變化趨勢，但預測值帶有明顯的滯后偏差。第3頁，共49頁。移動平均法根據(jù)預測時使用的各元素的權(quán)重不同，可以分為：簡單移動平均和加權(quán)移動平均。第4頁，共49頁。 指數(shù)平滑法指數(shù)平滑法是在移動平均法基礎(chǔ)上發(fā)展起來的一種時間序列分析預測法，它是通過計算指數(shù)平滑值，配合一定的時間序列預測模型對現(xiàn)象的未來進行預測。其原理是任一期的指數(shù)平滑值都是本期實際觀察值與前一期指數(shù)平滑值的加權(quán)平均。第5頁，共49頁。 回歸分析預測

3、法 回歸分析預測法是通過研究兩組或兩組以上變量之間的關(guān)系，建立相應的回歸預測模型，對變量進行預測的一種預測方法。第6頁，共49頁。第2單元 預測模型設(shè)計函數(shù)法Excel提供了關(guān)于估計線性模型和指數(shù)模型參數(shù)的幾個預測函數(shù)。線性模型和指數(shù)模型的數(shù)學表達式如下：線性模型：y = mx + b 或 y = m1x1 + m2x2 + + b第7頁，共49頁。 指數(shù)模型： 式中，y為因變量；x是自變量；m、m1、.、mn-1、mn、b分別為預測模型的待估計參數(shù)。Excel提供的預測函數(shù)主要有LINEST函數(shù)、LOGEST函數(shù)、TREND函數(shù)、FORECAST函數(shù)、SLOPE函數(shù)和INTERCEPT函數(shù)，

4、它們所使用的參數(shù)都基本相同，如表71、72、73所示。第8頁，共49頁。 表71 預測函數(shù)的參數(shù)及含義第9頁，共49頁。 表72 回歸統(tǒng)計值返回的順序第10頁，共49頁。第11頁，共49頁。知識點講解 LINEST函數(shù) LINEST函數(shù)是常用的統(tǒng)計函數(shù)之一，它是使用最小二乘法對已知數(shù)據(jù)進行最佳直線擬合，并返回描述此直線的數(shù)組。因為此函數(shù)返回數(shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。函數(shù)語法：LINEST(known_ys,known_xs,const,stats)Known_ys 是關(guān)系表達式 y = ax + b 中已知的 y 值集合。第12頁，共49頁。 如果數(shù)組 known_ys 在單獨一

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

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

7、述任何直線： 斜率 (a)：通常記為 a，如果需要計算斜率，則選取直線上的兩點，(x1,y1) 和 (x2,y2)；斜率等于 (y2 - y1)/(x2 - x1)。第15頁，共49頁。Y 軸截距 (b)：通常記為 b，直線的 y 軸的截距為直線通過 y 軸時與 y 軸交點的數(shù)值。直線的公式為 y = ax + b。如果知道了 a 和 b 的值，將 y 或 x 的值代入公式就可計算出直線上的任意一點。還可以使用 TREND 函數(shù)。當只有一個自變量 x 時，可直接利用下面公式得到斜率和 y 軸截距值： 第16頁，共49頁。 斜率：=INDEX(LINEST(known_ys,known_xs),

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

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

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

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

12、 const 為 TRUE 或省略，b 將按正常計算。如果 const 為 FALSE，則常量 b 將設(shè)為 1，而 m 的值滿足公式 y=mx。 Stats 為一邏輯值，指定是否返回附加回歸統(tǒng)計值。 如果 stats 為 TRUE，函數(shù) LOGEST 將返回附加的回歸統(tǒng)計值，因此返回的數(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頁，共49頁。 函數(shù)說明： 由數(shù)據(jù)繪出的圖越近似于指數(shù)曲線，則計算出來的曲線就越

13、符合原來給定的數(shù)據(jù)。正如 LINEST 函數(shù)一樣，LOGEST 函數(shù)返回一組描述數(shù)值間相互關(guān)系的數(shù)值數(shù)組，但 LINEST 函數(shù)是用直線來擬合數(shù)據(jù)，而 LOGEST 函數(shù)則以指數(shù)曲線來擬合數(shù)據(jù)。當僅有一個自變量 x 時，可直接用下面的公式計算出斜率 (m) 和 y 軸截距 (b) 的值。 第26頁，共49頁。斜率 (m)：INDEX(LOGEST(known_ys,known_xs),1)Y 軸截距 (b)：INDEX(LOGEST(known_ys,known_xs),2) 可用 y=b*mx 公式來預測 y 的值，但是 Microsoft Excel 另外提供了可以預測因變量 y 值的 G

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

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

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

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

18、的數(shù)據(jù)。第34頁，共49頁。第35頁，共49頁。FORECAST函數(shù)FORECAST函數(shù)根據(jù)已有的數(shù)值計算或預測未來值。此預測值為基于給定的 x 值推導出的 y 值。已知的數(shù)值為已有的 x 值和 y 值，再利用線性回歸對新值進行預測?？梢允褂迷摵瘮?shù)對未來銷售額、庫存需求或消費趨勢進行預測。函數(shù)語法：FORECAST(x,known_ys,known_xs)X 為需要進行預測的數(shù)據(jù)點。Known_ys 因變量數(shù)組或數(shù)據(jù)區(qū)域。Known_xs 為自變量數(shù)組或數(shù)據(jù)區(qū)域。第36頁，共49頁。函數(shù)說明：如果 x 為非數(shù)值型，函數(shù) FORECAST 返回錯誤值 #VALUE!。 如果 known_ys 和

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

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