商務數(shù)據(jù)處理與分析第7章-商務數(shù)據(jù)分析課件

第7章

商務數(shù)據(jù)處理分析Excel提供了非常實用的數(shù)據(jù)分析工具，利用這些分析工具，可解決數(shù)據(jù)管理中的許多問題，例如財務分析工具、統(tǒng)計分析工具、工程分析工具、規(guī)劃求解工具、方案管理器等等。下面主要介紹財務管理與統(tǒng)計分析中常用的一些數(shù)據(jù)分析工具。7.1用模擬分析方法求解

1.單變量求解

單變量求解是求解只有一個變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多數(shù)據(jù)管理中涉及到一個變量的求解問題。[例1]某企業(yè)擬向銀行以7%的年利率借入期限為5年的長期借款，企業(yè)每年的償還能力為100萬元，那么企業(yè)最多總共可貸款多少？

圖7.1貸款總額計算

圖7.2“單變量求解”對話框2.模擬運算表模擬運算表是將工作表中的一個單元格區(qū)域的數(shù)據(jù)進行模擬計算，測試使用一個或兩個變量對運算結果的影響。在Excel中，可以構造兩種模擬運算表：單變量模擬運算表和多變量模擬運算表。（1）單變量模擬運算表單變量模擬運算表是基于一個輸入變量，用它來模擬對一個或多個公式計算結果的影響。[例1]企業(yè)向銀行貸款10000元，期限5年，使用“模擬運算表”模擬計算不同的利率對月還款額的影響，圖7.3單變量模擬運算表圖7.4“模擬運算表”對話框

圖7.5單變量的模擬運算表3．

方案管理器在企業(yè)的生產經(jīng)營活動中，由于市場的不斷變化，企業(yè)的生產銷售受到各種因素的影響，企業(yè)需要估計這些因素并分析其對企業(yè)生產銷售的影響。Excel提供了稱為方案的工具來解決上述問題，利用其提供的方案管理器，可以很方便地對多種方案進行模擬分析。例如，不同的市場狀況、不同的定價策略等,所可能產生的結果，也即利潤會怎樣變化。[例1]某企業(yè)生產光盤，現(xiàn)使用方案管理器，假設生產不同數(shù)量的光盤（例如3000，5000，10000），對利潤的影響。已知：在該例中有4個可變量：單價、數(shù)量、推銷費率和單片成本。利潤=銷售金額－成本－費用*（1+推銷費率）銷售=單價*數(shù)量費用=20000成本=固定成本＋單價*單片成本固定成本=70000（1）建立方案步驟如下：建立模型：將數(shù)據(jù)、變量及公式輸入在工作表中，如圖7.9所示。在單元格“B7:B10”中保存著要進行模擬的4個變量,分別是：單價、數(shù)量、單片成本和推銷費率。圖7.9建立模型圖7.10“方案管理器”對話框

圖7.11“編輯方案”對話框圖7.12“方案量值”對話框（2）顯示方案設定了各種模擬方案后，任何時候都可以執(zhí)行方案，察看模擬的結果。

圖7.13顯示運算結果（按鈕。圖7.13顯示運算結果（3）修改、刪除或增加方案（4）建立方案報告當需要將所有的方案執(zhí)行結果都顯示出來時，可建立方案報告。圖7.14（a）“方案總結”對話框

（b）“方案總結”工作表7.2線性回歸分析回歸分析法，是在掌握大量觀察數(shù)據(jù)的基礎上，利用數(shù)理統(tǒng)計方法建立因變量與自變量之間的回歸關系函數(shù)表達式（稱回歸方程式）?；貧w分析中，當研究的因果關系只涉及因變量和一個自變量時，叫做一元回歸分析；當研究的因果關系涉及因變量和兩個或兩個以上自變量時，叫做多元回歸分析。回歸分析中，又依據(jù)描述自變量與因變量之間因果關系的函數(shù)表達式是線性的還是非線性的，分為線性回歸分析和非線性回歸分析。通常線性回歸分析法是最基本的分析方法，遇到非線性回歸問題可以借助數(shù)學手段化為線性回歸問題處理。[例1]

對銷售額進行多元回歸分析預測，數(shù)據(jù)見圖。解：本題可用二元線性回歸分析來求解：設定變量：Y=銷售額，X1=電視廣告費用，X2=報紙廣告費用方程為：Y=a1X1+a2X2+b通過線性回歸分析確定a1,a2,b的值，從而確定方程。1．操作方法與步驟(1)

建立數(shù)據(jù)模型(2)工具→加載宏/“分析工具庫”復選框(3)工具→數(shù)據(jù)分析/“回歸”(4）設置“回歸”對話框中參數(shù):“Y值輸入?yún)^(qū)”，“X值輸入?yún)^(qū)”，“輸出區(qū)域”(5)根據(jù)結果得出方程圖7.15在EXCEL工作表中建立數(shù)據(jù)模型

圖7.16“回歸”對話框

中參數(shù)的設置由回歸分析結果可見：回歸方程y=a1*x1+a2*x2+b中，a1=2.2901836209178；a2=1.30098909825998；b=832.300916901311表7.1 回歸結果整理4.預測未來的銷售額假設2002年的電視廣告費用預算為35萬元，報紙廣告費用預算為18萬元，則根據(jù)多元線性回歸方程y=2.290183621*x1+1.300989098*x2+832.3009169可計算出2002年的銷售額為2.290183621*35+1.300989098*18+832.3009169即913.7583萬元。多元回歸方程：y=2.290183621*x1+1.300989098*x2+832.3009169標準差：a1=0.304064556a2=0.320701597b=15.73868952判定系數(shù)=0.9191356y估計值的標準誤差=6.425873026F統(tǒng)計值=28.37776839自由度=5回歸平方和=2343.540779殘差平方和=206.45922087.3規(guī)劃求解

“規(guī)劃”是數(shù)學概念，它是指運用微積分和線性代數(shù)的方法，在滿足一組約束條件的情況下，求出一個多變量函數(shù)極值的模型。數(shù)學規(guī)劃是運籌學的一個分支，主要包括線性規(guī)劃、非線性規(guī)劃、動態(tài)規(guī)劃和整數(shù)規(guī)劃等。規(guī)劃求解可求得工作表上某個單元格（目標單元格）中公式的最優(yōu)值。（對直接或間接與目標單元格中公式相關聯(lián)的一組單元格中的數(shù)值進行調整，最終在目標單元格公式中求得期望的結果）意義：規(guī)劃求解用于在生產或工作中的一些問題或項目在受多個因素的制約的前提下如何獲得最佳的結果。如獲得最大的利潤、最小的成本等。注意事項：在創(chuàng)建模型過程中，對“規(guī)劃求解”模型中的可變單元格數(shù)值應用約束條件，而且約束條件可以引用其他影響目標單元格公式的單元格。添加規(guī)劃求解命令:單擊文件選項卡→選項→加載項→規(guī)劃求解加載項→轉到，在“加載宏“對話框中選定“規(guī)劃求解”復選框，然后單擊“確定”按鈕。

1、求解線性規(guī)劃問題 118例：工廠生產3種玩具：狗、娃娃、大熊貓；3種玩具的凈利潤（元）：90、75、503種玩具各用機工（小時）：3、4、53種玩具各用手工（小時）：4、3、2工廠極限生產能力：機工：<=400h

手工：<=280h

要求：生產玩具狗的數(shù)量<=50件生產玩具大熊貓數(shù)量>=32件

求滿足以上條件時，如何安排各玩具的生產數(shù)量以獲得最大利潤。

1．建立數(shù)據(jù)模型:圖7.21建立數(shù)據(jù)模型（2）進行求解單擊數(shù)據(jù)選項卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對話框，如圖7.22所示在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖7.25所示的“規(guī)劃求解結果”對話框，選擇“保存規(guī)劃求解結果”項，單擊“確定”，則求解結果顯示在工作表上，如圖7.25所示。在建立好所有的規(guī)劃求解參數(shù)后，單擊“求解”，則系統(tǒng)將顯示如圖7.25所示的“規(guī)劃求解結果”對話框，選擇“保存規(guī)劃求解結果”項，單擊“確定”，求解結果如圖7.25。圖7.25“規(guī)劃求解結果”對話框

圖7.26運算結果

求解的結果可形成：·

運算結果報告·

敏感性報告·

極限值報告2、求解方程組的解 （1）建立計算模型：在工作表中輸入數(shù)據(jù)及公式，如圖7.27所示；圖7.27利用“規(guī)劃求解”工具求解方程組（2）單擊數(shù)據(jù)選項卡→規(guī)劃求解，彈出“規(guī)劃求解參數(shù)”對話框，設置“規(guī)劃求解參數(shù)”對話框中的參數(shù)（3）單擊“求解”，即可得到方程組的解，如圖7.28所示。圖7.28求解結果7.4

移動平均 移動平均法是根據(jù)時間序列資料，逐項推移，依次計算移動平均，來反映現(xiàn)象的長期趨勢。特別是現(xiàn)象的變量值受周期變動和不規(guī)則變動的影響，起伏較大，不能明顯地反映現(xiàn)象的變動趨勢時，運用移動平均法，消除這些因素的影響，進行動態(tài)數(shù)據(jù)的修勻，以利于進行長期趨勢的分析和預測。

簡單移動平均的計算公式：設xi為時間序列中的某時間點的觀測值，其樣本數(shù)為N；每次移動地求算術平均值所采用的觀測值的個數(shù)為n（n的取值范圍：2<n<t-1），則在第t時間點的移動平均值Mi式中：Mi——第t時間點的移動平均值，也可當作第t+1時間點的預測值，即：yi+1=Mi或yi=Mi-1移動平均分析工具及其公式可以基于特定的過去某段時期中變量的均值，對未來值進行預測[例1]某公司1994至2005年銷售額數(shù)據(jù)如圖所示。進行三年移動平均，并預測2006年銷售額。操作步驟：

（1）建立模型：將原始數(shù)據(jù)輸入到單元格區(qū)域A1:B13，如圖7.29所示；圖7.29建立數(shù)據(jù)模型（2）單擊數(shù)據(jù)選項卡→數(shù)據(jù)分析，彈出“數(shù)據(jù)分析”對話框；設置“移動平均”對話框，確定。圖7.30“移動平均”對話框結果如圖。單元格區(qū)域D5:D12即為標準誤差。根據(jù)公式y(tǒng)i+1=Mi得2006年的銷售額預測應為M2005，所以為640。7.31“移動平均”的分析結果7.5指數(shù)平滑指數(shù)平滑是在移動平均的基礎上的進一步擴展。指數(shù)平滑法是用過去時間數(shù)列值的加權平均數(shù)作為趨勢值，越靠近當前時間的指標越具有參考價值，因此給予更大的權重，按照這種隨時間指數(shù)衰減的規(guī)律對原始數(shù)據(jù)進行加權修勻。所以它是加權移動平均法的一種特殊情形。其基本形式是根據(jù)本期的實際值Yt和本期的趨勢值，分別給以不同權數(shù)α和1–α，計算加權平均數(shù)作為下期的趨勢值。[例1]利用上題例題的數(shù)據(jù)，用Excel進行單指數(shù)平滑進行分析。操作步驟：建立數(shù)據(jù)模型；單擊數(shù)據(jù)選項卡→數(shù)據(jù)分析→指數(shù)平滑，“確定”，顯示“指數(shù)平滑”對話框；設置“指數(shù)平滑”對話框圖7.32“指數(shù)平滑”對話框結果：7.6相關分析相關關系是指變量之間存在的不完全確定性的關系。在實際問題中，許多變量之間的關系并不是完全確定性的，例如居民家庭消費與居民家庭收入這兩個變量的關系就不是完全確定的。收入水平相同的家庭，它們的消費額往往不同；消費額相同的家庭，它們的收入也可能不同。對現(xiàn)象之間相關關系密切程度的研究，稱為相關分析。對兩個變量之間線性相關程度的度量稱為簡單相關系數(shù)---r。簡單相關系數(shù)又稱皮爾遜相關系數(shù)，它描述了兩個定距變量間聯(lián)系的緊密程度。樣本的簡單相關系數(shù)一般用r表示。R的性質：

r>0為正相關，r<0為負相關。R=1表明兩個變量完全線性相關；

r=0表明兩個變量完全不線性相關，但兩個變量之間有可能存在非線性相關。

相關程度劃分:

當r>=0.8，高度相關；

0.5<=r<0.8，視為中度相關；

0.3<=r<0.5，視為低度相關；

|r|<0.3時，視為不相關。[例]根據(jù)下圖的數(shù)據(jù)，對家庭月消費支出與家庭月收入的數(shù)據(jù)進行相關分析。操作步驟：建立數(shù)據(jù)模型；工具→數(shù)據(jù)分析/相關系數(shù)設置“相關系數(shù)”對話框內容“確定”，輸出結果如圖所示。分析結果表明：相關系數(shù)r=0.979747601，表示家庭月消費支出與家庭月收入之間存在高度正相關關系。分析結果表明：相關系數(shù)r=0.979747601，表示家庭月消費支出與家庭月收入之間存在高度正相關關系。操作步驟:（1）建立數(shù)據(jù)模型：將數(shù)據(jù)輸入到工作表區(qū)域A1:E16中，如圖8.38所示；7.7方差分析方差分析(AnalysisofVariance,縮寫為ANOVA)是數(shù)理統(tǒng)計學中常用的數(shù)據(jù)處理方法之一，是經(jīng)濟和科學研究中分析試驗數(shù)據(jù)的一種有效的工具。運用數(shù)理的方法對數(shù)據(jù)進行分析，以鑒別各種因素對研究對象某些特征值的影響大小和影響方式，這種方法就叫做方差分析。所關注的對象的特征稱為指標，影響指標的各種原因叫做因素，在實驗中因素的各種不同狀態(tài)稱為因素的水平。7.7.1單因素方差分析單因素方差分析的作用是通過對某一因素的不同水平進行多次觀測，然后通過統(tǒng)計分析判斷該因素的不同水平對考察指標的影響是否相同。從理論上講，實際上是在檢驗幾個等方差正態(tài)總體的等均值假設。單因素方差分析的基本假設是各組的均值相等。[例1]為了考察不同的銷售渠道對總銷售額的貢獻，連續(xù)半年對不同渠道的業(yè)績進行觀測，得到一組數(shù)據(jù)如圖所示，要求用方差分析判斷各渠道的作用是否相同。本題是一個典型的單因素方差分析問題，渠道作為營業(yè)業(yè)績這個指標的一個主要因素，而不同的渠道可以視做該因素的不同水平。操作步驟:建立數(shù)據(jù)模型單擊工具→數(shù)據(jù)分析/方差分析：單因素方差分析”，設置對話框的內容，“確定”。運算結果說明：運算結果分為概要和方差分析兩部分。概要：返回每組數(shù)據(jù)（代表因素的一個水平）樣本數(shù)、合計、均值和方差。方差分析：返回標準的單因素方差分析表，包括離差平方和、自由度、均方、F統(tǒng)計量、概率值、F臨界值。其中的“組間”就是影響銷售額的因素(不同的銷售渠道)，“組內”就是誤差，“總計”就是總和，“差異源”則是方差來源，“SS”---平方和，“df”---自由度，“MS”---均方，“F”---F比（F統(tǒng)計量），“P-value”則是原假設(結論)成立的概率(