Excel會計與財務(wù)管理-理論、方案暨模型（第二版） 桂良軍課件第10章 Excel在財務(wù)預(yù)測中應(yīng)用

第10章Excel在財務(wù)預(yù)測中應(yīng)用本章教學(xué)要求：掌握財務(wù)預(yù)測相關(guān)的函數(shù)掌握利用數(shù)據(jù)分析工具進行財務(wù)預(yù)測的方法掌握建立銷售預(yù)測模型的方法并進行銷售預(yù)測主要內(nèi)容：10.1財務(wù)預(yù)測概述及方法10.2財務(wù)預(yù)測相關(guān)的函數(shù)10.3利用數(shù)據(jù)分析工具進行財務(wù)預(yù)測10.4銷售預(yù)測模型第10章Excel在財務(wù)預(yù)測中應(yīng)用10.1財務(wù)預(yù)測概述及方法

10.1.1財務(wù)預(yù)測概述財務(wù)預(yù)測是指以企業(yè)過去一定時期財務(wù)活動資料為依據(jù)，結(jié)合企業(yè)現(xiàn)在面臨和即將面臨的各種因素變化，運用科學(xué)的定量和定性方法，對企業(yè)未來的財務(wù)活動和財務(wù)成果所做出的科學(xué)預(yù)計和合理判斷。10.1.2財務(wù)預(yù)測方法

移動平均法指數(shù)平滑法時間序列預(yù)測法因果預(yù)測法——回歸分析法定量預(yù)測意見匯集法專家小組法德爾菲法定性預(yù)測一元線性回歸二元線性回歸多元線性回歸10.2財務(wù)預(yù)測相關(guān)的函數(shù)

在Excel中經(jīng)常用到的財務(wù)預(yù)測函數(shù)有兩類：1.用于進行線性變動趨勢預(yù)測的函數(shù)

INTERCEPT函數(shù)

SLOPE函數(shù)

LINEST函數(shù)FORECAST函數(shù)TREND函數(shù)2.用于進行指數(shù)變動趨勢預(yù)測的函數(shù)LOGEST函數(shù)GROWTH函數(shù)。（1）INTERCEPT函數(shù)功能：INTERCEPT函數(shù)專門用來計算一次線性回歸模型y=a+bx方程中的a的值。語法格式：INTERCEPT(known_y’s,known_x’s)參數(shù)known_y’s為所觀察的因變量數(shù)據(jù)或數(shù)據(jù)組，known_x’s為所觀察的自變量數(shù)據(jù)或數(shù)據(jù)組。需要注意的是，⑴參數(shù)必須是數(shù)字、名稱、數(shù)組或包含有數(shù)字的引用；⑵如果數(shù)組或引用參數(shù)包含文字、邏輯值，或空白單元格，則忽略其值，但零值將考慮在內(nèi)。⑶如果known_y’s和known_x’s的數(shù)據(jù)點數(shù)目不相等或不包含任何數(shù)據(jù)點，則函數(shù)INTERCEPT返回錯誤值#N/A。（2）SLOPE函數(shù)功能：SLOPE函數(shù)專門用來計算一次線性回歸模型y=a+bx方程中的b的值。語法格式：SLOPE(known_y’s,known_x’s)【例10-1】某企業(yè)今年1-6月份各月的實際銷售額分別為280萬元、300萬元、290萬元、310萬元、320萬元、330萬元，要求求出擬合一次線性方程。（3）LINEST函數(shù)功能：LINEST函數(shù)用來計算一次線性回歸模型y=a+bx方程中的a、b的值。語法格式：LINEST(known_y’s,known_x’s,const,stats)參數(shù)：Const

為一邏輯值，用于指定是否將常量a強制設(shè)為0。如果const為TRUE或省略，a將按正常計算。如果const為FALSE，a將被設(shè)為0，并同時調(diào)整b值使y=bx。Stats

為一邏輯值，指定是否返回附加回歸統(tǒng)計值。如果stats為TRUE，則LINEST函數(shù)返回附加回歸統(tǒng)計值，這時返回的數(shù)組為{mn,mn-1,...,m1,b;sen,sen-,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats為FALSE或省略，LINEST函數(shù)只返回系數(shù)b和常量a。（4）FORECAST函數(shù)功能：FORECAST函數(shù)根據(jù)給定的數(shù)據(jù)計算或預(yù)測一元線性回歸模型的未來值。使用此函數(shù)可以對未來銷售額、庫存需求或消費趨勢進行預(yù)測。語法格式：FORECAST(x,known_y's,known_x's)參數(shù)說明：x為需要進行預(yù)測的數(shù)據(jù)點，F(xiàn)ORECAST函數(shù)給出相應(yīng)的在x點處的預(yù)測值。如果x不是數(shù)字，函數(shù)FORECAST返回錯誤值#VALUE！。如果known_y’s和known_x’s為空或含有不同數(shù)目的數(shù)據(jù)點，函數(shù)FORECAST返回錯誤值#N/A。（5）TREND函數(shù)功能：TREND函數(shù)返回依據(jù)多元線性回歸趨勢而得的預(yù)測值。它先找到適合給定的數(shù)組known_y’s和known_x’s的直線（用最小二乘法），然后對用戶給定的新數(shù)組new_x’s值返回擬合趨勢的y值。（可預(yù)測一組值！）語法TREND(known_y’s,known_x’s,new_x’s,const)見P287例10-2（6）LOGEST函數(shù)功能：LOGEST函數(shù)計算最符合觀測數(shù)據(jù)組的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)組?；貧w擬合曲線的方程為：y=b*m^x語法格式：LOGEST(known_y’s,known_x’s,const,stats)

（7）Growth函數(shù)

自己看課本本章P287-289以上介紹了有關(guān)回歸分析預(yù)測工作表函數(shù)，總結(jié)如下表所示：

函數(shù)名定義語法格式INTERCEPT一元線性回歸模型b的估計值（截距）INTERCEPT(y,x)SLOPE一元線性回歸模型m的估計值（斜率）SLOPE(y,x)LINEST估計一元線性回歸模型的參數(shù)值LINEST(y,x,const,stats)FORECAST依照一元線性回歸模型的預(yù)測值FORECAST(new_x,y,x)TREND依照多元線性回歸模型的預(yù)測值TREND(y,x,new_x,const)LOGEST估計多元指數(shù)回歸模型的參數(shù)值LOGEST(y,x,const,stats)GROWTH依照多元指數(shù)回歸模型的預(yù)測值GROWTH(y,x,new_x,const)10.3利用數(shù)據(jù)分析工具進行財務(wù)預(yù)測10.3.1利用移動平均工具進行財務(wù)預(yù)測移動平均分析工具可以基于特定的過去某段時期中變量的平均值對未來值進行預(yù)測。移動平均值提供了由歷史數(shù)據(jù)的簡單平均之所代表的趨勢信息。使用此工具可以預(yù)測銷售量、庫存或其他趨勢?！纠?0-5】某汽車制造企業(yè)在2010年1-12月份銷售的汽車數(shù)量如表10-5所示。要求在Excel中對2010年各月的汽車銷量繪圖并據(jù)此判斷是否可以采用移動平均法對銷量進行預(yù)測，如果可以的話，利用移動平均工具預(yù)測2011年1月的銷量。所建立的模型的結(jié)構(gòu)如圖10-1所示：圖10-1利用移動平均分析工具進行預(yù)測的結(jié)果10.3.2利用指數(shù)平滑工具進行財務(wù)預(yù)測指數(shù)平滑分析工具是基于前期預(yù)測值到處相應(yīng)的新預(yù)測值，并修正前期預(yù)測值的誤差。此工具將使用阻尼系數(shù)，其大小決定了本次預(yù)測對前期預(yù)測誤差的修正程度?！纠?0-6】沿用【例10-5】的已知條件，要求利用Excel數(shù)據(jù)分析庫中的指數(shù)平滑工具（阻尼系數(shù)=0.4）預(yù)測2011年1月份的汽車銷量。所建立的模型的結(jié)構(gòu)如圖10-8所示：圖10-8利用指數(shù)平滑分析工具進行預(yù)測的結(jié)果10.3.3利用回歸工具進行財務(wù)預(yù)測回歸分析法是指在掌握大量觀測數(shù)據(jù)的基礎(chǔ)上，利用數(shù)理統(tǒng)計方法建立因變量與自變量之間的回歸關(guān)系函數(shù)表達式并據(jù)此進行預(yù)測的方法?！纠?0-7】電視臺的廣告收入與收看該電視臺節(jié)目的觀眾人數(shù)存在一定的關(guān)系，為了研究這一關(guān)系，現(xiàn)抽取某電視臺2010年1-12月的廣告收入和日均觀眾人數(shù)，如表10-6所示，要求根據(jù)電視臺的廣告收入和日均觀眾人數(shù)繪圖并據(jù)此判斷能否進行回歸分析，如果可以的話，利用回歸分析工具預(yù)測2011年1月該電視臺的廣告收入（當(dāng)月的日均觀眾人數(shù)為8358萬人）。所建立的模型的結(jié)構(gòu)如圖10-11所示：圖10-11利用回歸分析工具進行預(yù)測的結(jié)果利用回歸分析工具進行多元線性回歸分析【例10-8】現(xiàn)有一家汽車制造企業(yè)，專門生產(chǎn)某品牌的家用轎車。該企業(yè)的銷售經(jīng)理為了預(yù)測2011年度該企業(yè)在某地區(qū)的銷售額，特將2001-2010年度企業(yè)的年生產(chǎn)能力、在該地區(qū)的廣告投入和銷售額以及該地區(qū)家庭的平均年收入整理匯總?cè)绫?0-7所示，試圖找出銷售額與這三個變量之間的關(guān)系。要求根據(jù)提供的數(shù)據(jù)，利用回歸分析工具建立一個最合適的回歸方程對2011年度該企業(yè)在本地區(qū)的銷售額進行預(yù)測（假設(shè)2011年度企業(yè)的年生產(chǎn)能力為60萬輛，在該地區(qū)的廣告投入為380萬元，該地區(qū)家庭的平均年收入為3.64萬元）。所建立的模型的結(jié)構(gòu)如圖10-14所示：圖10-4預(yù)測的最終結(jié)果10.4銷售預(yù)測模型

銷售預(yù)測主要是對銷售量和銷售額進行預(yù)測。本節(jié)將學(xué)習(xí)如何使用函數(shù)實現(xiàn)銷售預(yù)測?！纠?0-9】某汽車制造企業(yè)2010年各月的汽車銷售額的有關(guān)資料如表10-8所示。根據(jù)以往的經(jīng)驗，該企業(yè)的銷售額隨著時間的推移有可能呈線性變動趨勢或指數(shù)變動趨勢。要求建立一個可以選擇銷售額變動趨勢的組合框控件，根據(jù)2010年各月的銷售額數(shù)據(jù)判斷并選擇一種變動趨勢，據(jù)此預(yù)測2011年1-6月份的汽車銷售額。所建立的模型的結(jié)構(gòu)如圖10-22所示：圖10-22利用函數(shù)預(yù)測銷售額的模型【例10-10】某公司2011年上半年各月的產(chǎn)品銷量如表10-9所示，要求預(yù)測該公司2011年下半年各月的產(chǎn)品銷量并對此進行趨勢分析。所建立的模型的結(jié)構(gòu)如圖10-24所示：圖10-24銷量預(yù)測結(jié)果與趨勢分析圖表對于：y=mx+bor

y=m1x1+m2x2+...+b（如果有多個區(qū)域的x值）統(tǒng)計值說明se1,se2,...,sen系數(shù)m1,m2,...,mn的標(biāo)準(zhǔn)誤差值。seb常量b的標(biāo)準(zhǔn)誤差值（當(dāng)const為FALSE時，seb=#N/A）r2判定系數(shù)。Y的估計值與實際值之比，范圍在0到1之間。如果為1，則樣本有很好的相關(guān)性，Y的估計值與實際值之間沒有差別。如果判定系數(shù)為0，則回歸公式不能用來預(yù)測Y值。有關(guān)計算r2的方法的詳細(xì)信息，請參閱本主題后面的“說明”。seyY估計值的標(biāo)準(zhǔn)誤差。FF統(tǒng)計或