實(shí)驗(yàn)五決策支持和商務(wù)智能

1、實(shí)驗(yàn)五 決策支持和商務(wù)智能（4學(xué)時(shí)）一、實(shí)驗(yàn)?zāi)康?理解DSS支持管理人員解決半結(jié)構(gòu)化、非結(jié)構(gòu)化決策問題。2理解DSS基本部件中的數(shù)據(jù)庫及其管理系統(tǒng)、模型庫及其管理系統(tǒng)、方法庫及其管理系統(tǒng)如何綜合運(yùn)用有關(guān)數(shù)據(jù)、求解模型、計(jì)算方法等。3掌握相關(guān)的數(shù)理統(tǒng)計(jì)與定量分析方法在Microsoft Excel 2010中的具體操作；了解Microsoft Excel 2010在企業(yè)管理與決策、個(gè)人日常生活中的高級(jí)應(yīng)用。4在SQL Server中體驗(yàn)和認(rèn)識(shí)OLAP在決策支持中的應(yīng)用，以及數(shù)據(jù)挖掘如何發(fā)現(xiàn)隱藏在數(shù)據(jù)中的有用信息并輔助于決策。二、實(shí)驗(yàn)內(nèi)容實(shí)驗(yàn)5-1 指數(shù)平滑預(yù)測(cè)法實(shí)驗(yàn)背景：指數(shù)平滑法是依據(jù)事物變化

2、的連續(xù)性原理，通過掌握事物過去運(yùn)動(dòng)軌跡（即利用歷史數(shù)據(jù)）來預(yù)測(cè)未來事物發(fā)展的規(guī)律的一種預(yù)測(cè)方法。這里采用Excel 2003中的指數(shù)平滑計(jì)算公式： 公式中的St+1和St分別代表第t+1期和第t期的指數(shù)平滑值，yt代表第t期的變量原始數(shù)據(jù)；是阻尼系數(shù)，其中0<<1。公式中的t+1期平滑值St+1就是預(yù)測(cè)值。經(jīng)過迭代，并取S1=y1，我們可以將公式化為：從此形式不難發(fā)現(xiàn)，新估計(jì)值等于各期原始數(shù)據(jù)的加權(quán)平均值，且各期原始數(shù)據(jù)的權(quán)重隨著期數(shù)的前推以指數(shù)形式衰減，阻尼系數(shù)決定了權(quán)重衰減速度。值越小，權(quán)重衰減速度越快，相應(yīng)的，近期數(shù)據(jù)在預(yù)測(cè)值St+1中所占的比重越大；增大，權(quán)重衰減速度減慢，

3、近期數(shù)據(jù)對(duì)St+1值的影響程度也隨之減弱；越接近1，St+1值越接近全部數(shù)據(jù)的平均數(shù)據(jù)值。下面，以實(shí)例說明利用Excel進(jìn)行指數(shù)平滑預(yù)測(cè)的步驟以及對(duì)預(yù)測(cè)結(jié)果的影響。實(shí)驗(yàn)數(shù)據(jù)： 假設(shè)某商場(chǎng)2014年家電部銷售額（百萬元）的記錄如表5.1所示，現(xiàn)用指數(shù)平滑預(yù)測(cè)法，分別取=0.2、=0.5和=0.8對(duì)2015年1月份的銷售額進(jìn)行預(yù)測(cè)，并比較的3種不同取值對(duì)預(yù)測(cè)結(jié)果的影響。表5.1 某商場(chǎng)2014年家電部銷售額月份123456789101112銷售額354258402842626251675558實(shí)驗(yàn)步驟：（1）首先在Excel中輸入數(shù)據(jù)和相關(guān)信息，如圖5.1所示。圖5.1 輸入數(shù)據(jù)和相關(guān)信息 （2）

4、單擊“數(shù)據(jù)”項(xiàng)下的“數(shù)據(jù)分析”，如圖5.2示，從打開的“數(shù)據(jù)分析”對(duì)話框中選擇“指數(shù)平滑”分析工具，如圖5.3所示，單擊“確定”按鈕，打開“指數(shù)平滑”對(duì)話框。圖5.2 單擊“數(shù)據(jù)”下的“數(shù)據(jù)分析”命令圖5.3 “數(shù)據(jù)分析”對(duì)話框 需要說明的是，中文版Office 2010在初次安裝后，并未加載“分析工具庫”，需要先進(jìn)行安裝。其具體方法是，單擊“文件”|“選項(xiàng)”命令，如圖5.4所示，從打開的對(duì)話框中選中“加載項(xiàng)”下的“分析工具庫”復(fù)選框，如圖5.5所示，單擊“轉(zhuǎn)到”按鈕，在彈出的對(duì)話框中勾選“分析工具庫”，如圖5.6，并單擊“確定”即可。圖5.4 單擊“文件”|“選項(xiàng)”命令 圖5.5 選中“分析

5、工具庫”復(fù)選框圖5.6 勾選“分析工具庫”（3）將光標(biāo)移至“輸入?yún)^(qū)域”輸入框，輸入銷售額數(shù)據(jù)(光標(biāo)在輸入框閃爍時(shí)，選中銷售額數(shù)據(jù)區(qū)域即可)，如圖5.7所示。然后，在“阻尼系數(shù)”輸入框中輸入0.2，如圖5.8所示。再將光標(biāo)移至“輸出區(qū)域”輸入框，選中單元格C2，如圖5.9所示，然后單擊“確定”按鈕，得到平滑值。將鼠標(biāo)移至最后一個(gè)平滑值所在單元格的右下角，鼠標(biāo)變?yōu)閺?fù)制手柄，向下拖動(dòng)一格，即在此格內(nèi)產(chǎn)生了2015年1月份的預(yù)測(cè)值，如圖5.10所示。圖5.7 輸入銷售額數(shù)據(jù)圖5.8 輸入阻尼系數(shù)圖5.9 選中C2單元格圖5.10 向下拖動(dòng)一格（4）按照上述方法，分別取=0.5和=0.8進(jìn)行預(yù)測(cè)。（5）

6、選中原始數(shù)據(jù)和3次的平滑數(shù)據(jù)，如圖5.11所示，單擊工具欄上的“插入”按鈕，選擇“折線圖”選項(xiàng)，如圖5.12所示，即可產(chǎn)生這兩組數(shù)據(jù)的折線圖，如圖5.13所示。 圖5.11 選中原始數(shù)據(jù)和3次的平滑數(shù)據(jù)圖5.12 選擇“折線圖”選項(xiàng)圖5.13 折線圖結(jié)果實(shí)驗(yàn)結(jié)果：（1）將Excel文件命名為“學(xué)號(hào)姓名-指數(shù)平滑預(yù)測(cè)法”，如“20114030306張三-指數(shù)平滑預(yù)測(cè)法.xlsx”。以下實(shí)驗(yàn)結(jié)果的相關(guān)截圖中都應(yīng)有Excel文件名的信息。（2）給出圖5.13的截圖，并要求在Excel中的圖表區(qū)為“系列”修改名稱、為“圖表”添加標(biāo)題、為“坐標(biāo)軸”添加名稱。也可在圖表區(qū)進(jìn)行更多的細(xì)節(jié)標(biāo)注與修飾，以達(dá)到更

7、好的圖示效果。（3）分析和比較分別為0.2、0.5、0.8三個(gè)不同值時(shí)，預(yù)測(cè)曲線與原始數(shù)據(jù)曲線的擬合效果。（4）按照本實(shí)驗(yàn)的操作步驟，為設(shè)置兩個(gè)另外的數(shù)值，也給出已設(shè)置細(xì)節(jié)標(biāo)注與修飾的截圖，并分析和比較分別為不同值時(shí)，預(yù)測(cè)曲線與原始數(shù)據(jù)曲線的擬合效果。實(shí)驗(yàn)5-2 一元線性回歸預(yù)測(cè)法實(shí)驗(yàn)背景：線性回歸預(yù)測(cè)法是利用事物發(fā)展的相關(guān)性和相似性規(guī)律。 同指數(shù)平滑一樣，需利用歷史統(tǒng)計(jì)數(shù)據(jù)，從中分析出事物發(fā)展的因變量與自變量之間的比例關(guān)系回歸方程，以預(yù)測(cè)未來。一元線性回歸預(yù)測(cè)法，是指兩個(gè)具有線性關(guān)系的變量，根據(jù)自變量的變動(dòng)來預(yù)測(cè)因變量平均發(fā)展趨勢(shì)的方法，其模型為： y=a+bx模型的回歸系數(shù)采用最小二乘法（

8、Ordinary Least square）進(jìn)行估計(jì)，記 根據(jù)極值原理，對(duì)a和b分別求偏導(dǎo)數(shù)，并令其等于零，得到回歸參數(shù)的估計(jì)值： 一元線性回歸的常用檢驗(yàn)有兩種，一是擬合優(yōu)度的檢驗(yàn)，其指標(biāo)是可決系數(shù)R2，R2值越大，說明回歸曲線與原曲線的擬合程度越高。另一種是顯著性的檢驗(yàn)，相應(yīng)的參數(shù)是P值，這個(gè)數(shù)值說明了該變量的顯著程度。例如，若變量x的P值是0.019，這說明，x的系數(shù)b為零的可能性不到0.02，也就是說x在98%的置信度下是顯著的。Excel內(nèi)嵌了回歸分析工具，只需輸入數(shù)據(jù)即可得到參數(shù)值和相關(guān)檢驗(yàn)值。下面，通過一個(gè)實(shí)例來說明Excel中一元線性回歸的具體操作。實(shí)驗(yàn)數(shù)據(jù)：表5.2是某企業(yè)20

9、14年的月銷售量（單位：件）和月銷售成本（單位：元）的統(tǒng)計(jì)數(shù)據(jù)。現(xiàn)使用一元線性回歸模型對(duì)一定月銷售量下的月銷售成本進(jìn)行預(yù)測(cè)。在y=a+bx中，a為銷售成本中的固定成本；bx為銷售成本中的總變動(dòng)成本；b為單位變動(dòng)成本；x為銷售量。表5.2 某企業(yè)2014年的月銷售量和月銷售成本月份銷售量（單位：件）銷售成本（單位：元）140893551542508945733735800538658445683897305461241000064908424273752144678828588848950094555412289106532567798116258526787124605401134實(shí)驗(yàn)步驟：（

10、1）首先在Excel中輸入相關(guān)信息，如圖5.14所示，然后，同時(shí)選中自變量和因變量區(qū)域，如圖5.15所示，點(diǎn)擊“插入”按鈕，選擇“散點(diǎn)圖”選項(xiàng)，并選擇第一個(gè)散點(diǎn)圖圖標(biāo)，如圖5.16所示，繪制出散點(diǎn)圖，如圖5.17所示。通過觀察散點(diǎn)圖可以發(fā)現(xiàn)，月銷售成本隨著月銷售額的增長基本呈現(xiàn)線性增長態(tài)勢(shì)，因此，可以嘗試使用一元線性回歸進(jìn)行預(yù)測(cè)。圖5.14 輸入相關(guān)信息圖5.15 同時(shí)選中自變量和因變量區(qū)域圖5.16 選擇“散點(diǎn)圖”圖標(biāo)圖5.17 散點(diǎn)圖 (2) 單擊“數(shù)據(jù)”項(xiàng)下的“數(shù)據(jù)分析”，在彈出的對(duì)話框中選擇“回歸”命令，打開“回歸”對(duì)話框，如圖5.18所示，并將月銷售成本和月銷售額數(shù)據(jù)分別輸入“Y值輸

11、入?yún)^(qū)域”和“X值輸入?yún)^(qū)域”輸入框中，選中“線性擬合圖”，單擊“確定”按鈕。分析結(jié)果將在新的工作表中產(chǎn)生，如圖5.19所示。圖5.18 “回歸”對(duì)話框圖5.19 分析結(jié)果結(jié)果表單中包括3個(gè)部分： 回歸統(tǒng)計(jì)表、方差分析表和統(tǒng)計(jì)檢驗(yàn)表。從回歸統(tǒng)計(jì)表中可以看到，R2的值約為0.945885421，從經(jīng)驗(yàn)上看，擬合程度可以接受。從下方的統(tǒng)計(jì)檢驗(yàn)表中可以看到，變量x的系數(shù)b為零的可能性，也就是相應(yīng)的P值，約為1.17E-07，也就是1.17×10-7。顯然，x的顯著程度很高。實(shí)驗(yàn)結(jié)果：（1）將Excel文件命名為“學(xué)號(hào)姓名-一元線性回歸預(yù)測(cè)法”，如“20114030306張三-一元線性回歸預(yù)測(cè)法

12、.xlsx”。（2）給出圖5.19的截圖，截圖中應(yīng)有Excel文件名的信息。（3）根據(jù)圖5.19的分析結(jié)果，請(qǐng)寫出回歸預(yù)測(cè)模型。（4）假如當(dāng)前時(shí)間為2015年元月初，該企業(yè)預(yù)計(jì)2015年1月銷售量為6000件，請(qǐng)根據(jù)回歸預(yù)測(cè)模型對(duì)2015年1月的銷售成本進(jìn)行預(yù)測(cè)。實(shí)驗(yàn)5-3 線性規(guī)劃決策法實(shí)驗(yàn)背景：線性規(guī)劃決策法是模型選優(yōu)決策法中的一種，線性規(guī)劃也是運(yùn)籌學(xué)的一個(gè)重要分支，是解決資源的有效利用和管理問題一種數(shù)學(xué)方法：運(yùn)用線性規(guī)劃來求解實(shí)際問題，歸結(jié)一點(diǎn)就是一個(gè)極值問題的求解問題，即在線性等式和線性不等式的約束條件下求目標(biāo)函數(shù)極值的問題。問題的一般形式為：目標(biāo)函數(shù)：約束條件：Excel 提供了求解

13、各種最優(yōu)化問題的工具，可以很方便的求出問題的解。下面結(jié)合一個(gè)實(shí)例，具體說明操作過程。實(shí)驗(yàn)數(shù)據(jù)：某小型木材加工廠僅生產(chǎn)桌子和椅子兩種家具，已有木板300板英尺(木材的一種計(jì)量單位)，可利用的工時(shí)為110小時(shí)，每種家具所需的材料、工時(shí)及利潤如表5.3所示。表5.3 每種家具所需的材料、工時(shí)及利潤單位產(chǎn)品桌子椅子木板(英尺)3020工時(shí)(小時(shí))510利潤(元)68設(shè)桌子和椅子的最優(yōu)數(shù)量分別為x和y，則有：目標(biāo)函數(shù)：約束條件：實(shí)驗(yàn)步驟：（1）按照?qǐng)D所示在Excel中輸入相關(guān)信息，如圖5.20所示。其中“總額”一列應(yīng)該以公式形式輸入，其具體輸入內(nèi)容由上至下依次為“=B2*B5+C2*C5”、“=B3*B

14、5+C3*C5”、“=B4*B5+C4*C5”，如圖5.21所示。圖5.20 輸入相關(guān)信息圖5.21 “總額”列以公式形式輸入需要說明的是，中文版Office 2010在初次安裝后，同樣并未加載“規(guī)劃求解加載項(xiàng)”，需要先進(jìn)行安裝。其具體方法是，單擊“文件”|“選項(xiàng)”命令，如圖5.22所示，從打開的對(duì)話框中選中“加載項(xiàng)”下的“規(guī)劃求解加載項(xiàng)”復(fù)選框，如圖5.23所示，單擊“轉(zhuǎn)到”按鈕，在彈出的對(duì)話框中勾選“規(guī)劃求解加載項(xiàng)”，如圖5.24，并單擊“確定”即可。圖5.22 單擊“文件”|“選項(xiàng)”命令圖5.23 選中“規(guī)劃求解加載項(xiàng)”復(fù)選框圖5.24 勾選“規(guī)劃求解加載項(xiàng)” (2) 單擊“數(shù)據(jù)”項(xiàng)下的

15、“規(guī)劃求解”命令，如圖5.25所示，打開“規(guī)劃求解參數(shù)”對(duì)話框，如圖5.26所示； 將光標(biāo)移至“設(shè)置目標(biāo)”輸入框內(nèi)，然后選中D4單元格，如圖5.27所示； 再將光標(biāo)移至“通過更改可變單元格”輸入框內(nèi)，然后同時(shí)選中B5和C5單元格區(qū)域，如圖5.28所示。圖5.25 單擊“數(shù)據(jù)”項(xiàng)下的“規(guī)劃求解”命令圖5.26 “規(guī)劃求解參數(shù)”對(duì)話框圖5.27 選中D4單元格圖5.28 同時(shí)選中B5和C5單元格 (3) 在“規(guī)劃求解參數(shù)”對(duì)話框中勾選“使無約束變量為非負(fù)數(shù)”，選中“單純線性規(guī)劃”復(fù) 選框，如圖5.29所示。 圖5.29 勾選“使無約束變量為非負(fù)數(shù)”，選中“單純線性規(guī)劃”復(fù)選框(4) 在“規(guī)劃求解參

16、數(shù)”對(duì)話框，如圖5.30所示，單擊“添加”按鈕，打開“添加約束”對(duì)話框，如圖5.31所示，在此對(duì)話框的“單元格引用位置”和“約束值”兩個(gè)輸入框內(nèi)分別輸入D2和E2單元格，如圖5.32所示。單擊“確定”按鈕后關(guān)閉此對(duì)話框，回到“規(guī)劃求解參數(shù)”對(duì)話框，此時(shí)在“約束”輸入框內(nèi)出現(xiàn)一個(gè)約束條件$D$2$E$2，如圖5.33所示。再按照同樣方法輸入條件“$D$3$E$3”。事實(shí)上，在輸入這兩個(gè)條件中間，不必退出“添加約束”對(duì)話框，可以連續(xù)輸入后再退出。圖5.30 單擊“添加”按鈕圖5.31 “添加約束”對(duì)話框圖5.32 輸入“單元格引用位置”和“約束值”圖5.33 顯示約束條件 (5) 兩個(gè)條件輸入完成

17、后，單擊“求解”按鈕，如圖5.34所示，打開“規(guī)劃求解結(jié)果”對(duì)話框，如圖5.35所示，同時(shí)，原先在工作表中輸入的數(shù)據(jù)也已經(jīng)改變?yōu)榻Y(jié)果數(shù)據(jù)。直接單擊對(duì)話框中的“確定”按鈕，保留結(jié)果數(shù)據(jù)。從結(jié)果數(shù)據(jù)中可以看到，桌子和椅子的最優(yōu)生產(chǎn)數(shù)量分別為4和9，此時(shí)最大利潤為96。圖5.34 單擊“求解”按鈕圖5.35 結(jié)果數(shù)據(jù)實(shí)驗(yàn)結(jié)果：（1）將Excel文件命名為“學(xué)號(hào)姓名-線性規(guī)劃決策法”，如“20114030306張三-線性規(guī)劃決策法.xlsx”。以下實(shí)驗(yàn)結(jié)果的相關(guān)截圖中都應(yīng)有Excel文件名的信息。（2）給出圖5.35的截圖。（3）將“利潤”設(shè)置為介于最小值與最大值之間的一個(gè)數(shù)值，嘗試求解桌子與椅子的最

18、優(yōu)數(shù)量（即設(shè)置成本為“值為”）。請(qǐng)給出圖5.35的截圖。實(shí)驗(yàn)5-4 固定資產(chǎn)折舊的計(jì)算實(shí)驗(yàn)背景：固定資產(chǎn)折舊是指在固定資產(chǎn)使用壽命內(nèi)，按照確定的方法對(duì)應(yīng)折舊額進(jìn)行系統(tǒng)分?jǐn)偅黄鋵?shí)質(zhì)是指價(jià)值從固定資產(chǎn)向產(chǎn)品成本的轉(zhuǎn)移。固定資產(chǎn)折舊的計(jì)算有多種不同的方法，本實(shí)驗(yàn)采用了常用的三種方法。實(shí)驗(yàn)數(shù)據(jù)：假設(shè)某公司的固定資產(chǎn)原始數(shù)據(jù)如圖5.36所示。為了方便起見，已經(jīng)建立了工作表“固定資產(chǎn)卡片”?，F(xiàn)分別采用“直線折舊法”、“雙倍余額遞減法”和“年數(shù)總和法”三種方法進(jìn)行固定資產(chǎn)折舊的計(jì)算。圖5.36 公司原始數(shù)據(jù)實(shí)驗(yàn)步驟：（1）根據(jù)工作表“固定資產(chǎn)卡片”，建立工作表“固定資產(chǎn)折舊”，并輸入數(shù)據(jù)。如圖5.37所示。

19、圖5.37 建立“固定資產(chǎn)折舊”（2）計(jì)算“凈殘值”。在單元格H3中輸入公式“=F3*G3”，按【Enter】完成輸入，然后將該單元中的公式填充到該列的其他單元格中。如圖5.38所示。圖5.38 計(jì)算凈殘值（3）計(jì)算“已提折舊月數(shù)”。在單元格I3中輸入公式： “=INT(DAYS360(D3,DATE(2013,8,31)/30)”，按【Enter】完成輸入，然后將該單元中的公式填充到該列的其他單元格中。如圖5.39所示。圖5.39 計(jì)算已提折舊月數(shù)（4）直線折舊法（SLN函數(shù)）計(jì)算固定資產(chǎn)折舊額。選定J3單元格，單擊“插入函數(shù)”圖標(biāo)，彈出【插入函數(shù)】對(duì)話框，在【或選擇類別】下拉列表中選擇【財(cái)

20、務(wù)】選項(xiàng)，然后在【選擇函數(shù)】列表框中選擇【SLN】選項(xiàng)。如圖5.40所示。圖5.40 選擇SLN函數(shù)單擊【確定】按鈕，彈出【函數(shù)參數(shù)】對(duì)話框，在【Cost】文本框中輸入“F3”,在【Salvage】文本框中輸入“H3”，在【Life】文本框中輸入“E3*12”。如圖5.41所示。圖5.41 輸入SLN函數(shù)參數(shù)單擊【確定】按鈕，然后將該單元中的公式填充到該列的其他單元格中。如圖5.42所示。圖5.42 得出計(jì)算結(jié)果（5）雙倍余額遞減法（DDB函數(shù)）計(jì)算固定資產(chǎn)折舊額。選定K3單元格，單擊“插入函數(shù)”圖標(biāo)，彈出【插入函數(shù)】對(duì)話框，在【或選擇類別】下拉列表中選擇【財(cái)務(wù)】選項(xiàng)，然后在【選擇函數(shù)】列表框

21、中選擇【DDB】選項(xiàng)。如圖5.43所示。圖5.43 選擇DDB函數(shù)單擊【確定】按鈕，彈出【函數(shù)參數(shù)】對(duì)話框，在【Cost】文本框中輸入“F3”,在【Salvage】文本框中輸入“H3”，在【Life】文本框中輸入“E3*12”,在【Period】文本框中輸入“I3”。如圖5.44所示。圖5.44 輸入DDB函數(shù)參數(shù)單擊【確定】按鈕，然后將該單元中的公式填充到該列的其他單元格中。如圖5.45所示。圖5.45 得出計(jì)算結(jié)果（6）年數(shù)總和法（SYD函數(shù)）計(jì)算固定資產(chǎn)折舊額。選定L3單元格，單擊“插入函數(shù)”圖標(biāo)，彈出【插入函數(shù)】對(duì)話框，在【或選擇類別】下拉列表中選擇【財(cái)務(wù)】選項(xiàng)，然后在【選擇函數(shù)】列表

22、框中選擇【SYD】選項(xiàng)，如圖5.46所示。圖5.46 選擇SYD函數(shù)單擊【確定】按鈕，彈出【函數(shù)參數(shù)】對(duì)話框，在【Cost】文本框中輸入“F3”,在【Salvage】文本框中輸入“H3”，在【Life】文本框中輸入“E3*12”,在【Per】文本框中輸入“I3”,如圖5.47所示。圖5.47 輸入SYD函數(shù)參數(shù)單擊【確定】按鈕，然后將該單元中的公式填充到該列的其他單元格中。如圖5.48所示。圖5.48 得出計(jì)算結(jié)果實(shí)驗(yàn)結(jié)果：（1）將Excel文件重命名為“學(xué)號(hào)姓名-固定資產(chǎn)折舊的計(jì)算”，如“20114030306張三-固定資產(chǎn)折舊的計(jì)算.xlsx”。（2）本實(shí)驗(yàn)步驟（3）是計(jì)算“已提折舊月數(shù)”

23、，嘗試將引用的“DATE(2013,8,31)”修改為其他日期，繼續(xù)完成本實(shí)驗(yàn)。請(qǐng)給出圖5.48的截圖，截圖中應(yīng)有Excel文件名的信息。實(shí)驗(yàn)5-5 利潤表等財(cái)務(wù)報(bào)表的編制實(shí)驗(yàn)背景：企業(yè)發(fā)生的每筆業(yè)務(wù)，最初以“記賬憑證”的形式記錄下來。隨后，這些業(yè)務(wù)信息轉(zhuǎn)移到“會(huì)計(jì)賬簿”中，并進(jìn)行一系列的核算匯總后生成了“會(huì)計(jì)報(bào)表”?！袄麧櫛怼保▽儆跁?huì)計(jì)報(bào)表）中的各項(xiàng)數(shù)據(jù)，則來自于“總帳表”（屬于會(huì)計(jì)賬簿）相對(duì)應(yīng)各項(xiàng)數(shù)據(jù)的計(jì)算結(jié)果，具有高度的匯總性，反映了企業(yè)的經(jīng)營成果。實(shí)驗(yàn)數(shù)據(jù)：假設(shè)某公司的“總帳表”數(shù)據(jù)如圖5.49所示。為了方便起見，已經(jīng)建立了工作表“總帳表”。現(xiàn)進(jìn)行“利潤表”的編制。圖5.49 總賬表實(shí)

24、驗(yàn)步驟：（1）選擇“利潤表”工作表，輸入編制單位（設(shè)置一個(gè)公司的名稱），日期為“2014年12月”，單位為“元”。（2）在C列單元格區(qū)域單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開“設(shè)置單元格格式”對(duì)話框。（3）在“分類”列表框中選擇“會(huì)計(jì)專用”選項(xiàng)，“小數(shù)位數(shù)”數(shù)值框中輸入“0”，“貨幣符號(hào)”下拉列表框選擇“無”選項(xiàng)。如圖5.50所示。圖5. 50 設(shè)置單元格格式（4） 選擇C4單元格，輸入公式“總賬表!F31”，按【Enter】鍵計(jì)算出“營業(yè)收入”項(xiàng)目。如圖5.51所示。圖5.51 計(jì)算“營業(yè)收入”（5）選擇C5單元格，輸入公式“總賬表!E32”，按【Enter】鍵計(jì)算

25、出“營業(yè)成本”項(xiàng)目，如圖5.52。圖5.52 計(jì)算“營業(yè)成本”（6）選擇C6單元格，輸入公式“總賬表!E33”，按【Enter】鍵計(jì)算出“營業(yè)稅金及附加”項(xiàng)目。（7）選擇C7單元格，輸入公式“總賬表!E34”，按【Enter】鍵計(jì)算出“銷售費(fèi)用”項(xiàng)目。（8）選擇C8單元格，輸入公式“總賬表!E35”，按【Enter】鍵計(jì)算出“管理費(fèi)用”項(xiàng)目。（9）選擇C9單元格，輸入公式“總賬表!E36”，按【Enter】鍵計(jì)算出“財(cái)務(wù)費(fèi)用”項(xiàng)目。（10）對(duì)于未發(fā)生業(yè)務(wù)的“資產(chǎn)減值損失、公允價(jià)值變動(dòng)損益和投資收益”等項(xiàng)目，選擇C10：C13單元格，輸入數(shù)值“0”，按【CtrlEnter】鍵。（11）選擇C14

26、單元格，輸入公式“C4-SUM(C5:C9)”，按【Enter】鍵計(jì)算出“營業(yè)利潤”項(xiàng)目。（12）對(duì)于未發(fā)生業(yè)務(wù)的“營業(yè)外收入”項(xiàng)目，選擇C15單元格，輸入數(shù)值“0”。（13）選擇C16單元格，輸入公式“總賬表!E37”，按【Enter】鍵計(jì)算出“營業(yè)外支出”項(xiàng)目。（14）選擇C18單元格，輸入公式“C14-C16”，按【Enter】鍵計(jì)算出“利潤總額”項(xiàng)目。（15）選擇C19單元格，輸入公式“總賬表!E38”，按【Enter】鍵計(jì)算出“所得稅費(fèi)用”項(xiàng)目。（16）選擇C20單元格，輸入公式“C18-C19”，按【Enter】鍵計(jì)算出“凈利潤”項(xiàng)目，如圖5.53所示。 圖5.53 計(jì)算“凈利潤”

27、實(shí)驗(yàn)結(jié)果：（1）將Excel文件重命名為為“學(xué)號(hào)姓名-利潤表的編制”，如“20114030306張三-利潤表的編制.xlsx”。（2）修改“總賬表”中的某個(gè)數(shù)據(jù)，觀察“利潤表”中的變化。給出“總賬表”的截圖，并加框線標(biāo)注修改部分；給出“利潤表”的截圖，并加框線標(biāo)注變化部分。截圖中應(yīng)有Excel文件名的信息。（3）思考“資產(chǎn)負(fù)債表”、“現(xiàn)金流量表”的編制方法。實(shí)驗(yàn)5-6 住房貸款分期還款的最優(yōu)方案 實(shí)驗(yàn)背景：住房貸款的還款方法有多種，其中“等額本息還款法”是指：在貸款期限內(nèi)每期以相等的額度償還貸款本金和利息的方法。銀行針對(duì)住房貸款客戶的“實(shí)際執(zhí)行利率”，往往是在國家規(guī)定的“基準(zhǔn)利率”上下浮動(dòng)，并

28、以“浮動(dòng)率”表示上下浮動(dòng)的幅度。即：實(shí)際執(zhí)行利率=基準(zhǔn)利率*(1+浮動(dòng)率)。為了便于理解，我們采用假設(shè)數(shù)據(jù)進(jìn)行本實(shí)驗(yàn)，如表5.4所示。表5.4 某銀行基準(zhǔn)利率與實(shí)際執(zhí)行利率基準(zhǔn)利率5%貸款年限1015202530浮動(dòng)率-20%-10%0%10%20%實(shí)際執(zhí)行利率4.00%4.50%5.00%5.50%6.00%PMT(Rate,Nper,Pv,Fv,Type)，基于固定利率及等額本息還款方式，返回貸款的等額分期償還額。其中，各參數(shù)如下：Rate表示貸款利率；Nper表示貸款時(shí)限；Pv表示貸款額；Fv表示為未來值，或在最后一次還款后希望得到的現(xiàn)金余額。Type為 0，表示指定各期的還款時(shí)間是在期

29、末；Type為 1，表示表示指定各期的還款時(shí)間是在期初。實(shí)驗(yàn)數(shù)據(jù)：大學(xué)畢業(yè)后擁有了穩(wěn)定的工作，計(jì)劃通過分期還款的方式購買某小區(qū)一套價(jià)值80萬的住房。若計(jì)劃“還款期限”為30年，目前實(shí)際執(zhí)行利率為6%，現(xiàn)進(jìn)行每月償還額的計(jì)算。實(shí)驗(yàn)步驟：（1）輸入相關(guān)數(shù)據(jù)，如圖5.54所示。圖5.54 輸入相關(guān)數(shù)據(jù) (2) 選擇B4單元格區(qū)域，單擊函數(shù)圖標(biāo)，如圖2所示。選擇“財(cái)務(wù)”類別下的“PMT”函數(shù)，如圖5.55和5.56所示。圖5.55 選擇“財(cái)務(wù)“類別圖5.56 選擇“PMT”函數(shù)（3）在彈出的對(duì)話框中，設(shè)置PMT函數(shù)的各個(gè)參數(shù)，如圖5.57所示。注意：將“年利率”轉(zhuǎn)化為“月利率”，將時(shí)間由“年”轉(zhuǎn)化成“月”。單擊“確定“，單元格B4即得出計(jì)算結(jié)果，如圖5.58所示。圖5.57 設(shè)置PMT函數(shù)的各個(gè)參數(shù)圖5.58 得出計(jì)算結(jié)果實(shí)驗(yàn)結(jié)果：（1）將Excel文件重命名為為“學(xué)號(hào)姓名-住房貸款分期還款的最優(yōu)方案”，如“20114030306張三-住房貸款分期還款的最優(yōu)方案.xlsx”。（2）修改“貸款額”的值，觀察“月償還額”的值的變化。給出計(jì)算結(jié)果的截圖，截圖中應(yīng)有Excel文件名的信息。（4）不同的貸款年限，由于風(fēng)險(xiǎn)不同可能會(huì)執(zhí)行不同的貸款利率?，F(xiàn)進(jìn)行不同貸款年限及貸款利率下的月償還額的計(jì)算，以便根據(jù)自身每月的還款