電子表格決策分析實(shí)驗(yàn)報(bào)告.docx

決策支持系統(tǒng)實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)名稱：電子表格決策分析實(shí)驗(yàn)姓名：學(xué)號(hào)：專業(yè)：信息管理與信息系統(tǒng)班級(jí)：指導(dǎo)教師：實(shí)驗(yàn)成績：批閱教師簽字：實(shí)驗(yàn)二 電子表格決策分析實(shí)驗(yàn)（3個(gè)學(xué)時(shí)）【實(shí)驗(yàn)?zāi)康摹?.了解決策支持系統(tǒng)模型的基礎(chǔ)知識(shí)。2.掌握EXCEL提供的函數(shù)的使用方法。3.掌握在EXCEL中進(jìn)行模型開發(fā)與決策分析的方法?！緦?shí)驗(yàn)內(nèi)容】1.理解模型的概念和類別。2.練習(xí)使用EXCEL的投資決策函數(shù)：NPV、XNPV、IRR、XIRR 和 MIRR。3.根據(jù)要求構(gòu)建投資指標(biāo)決策分析模型，并用以進(jìn)行決策分析。【實(shí)驗(yàn)步驟】1.理解模型的概念和類別步驟：（1）查閱EXCEL幫助， 對照教材分析EXCEL對模型實(shí)現(xiàn)的支持方式 （2）總結(jié)EXCEL提供了哪些類型的DSS模型2. 掌握EXCEL提供的函數(shù)的使用方法（1）完成實(shí)驗(yàn)教材“6.3.3使用EXCEL的投資決策函數(shù)”節(jié)的適用于進(jìn)行現(xiàn)金流分析的Excel 函數(shù)示例實(shí)驗(yàn)：NPV、XNPV、IRR、XIRR 和 MIRR。（2）對數(shù)據(jù)分析結(jié)果進(jìn)行討論。3. 根據(jù)要求構(gòu)建投資指標(biāo)決策分析模型，并用以進(jìn)行決策分析（1）完成實(shí)驗(yàn)教程“6.3.4構(gòu)建投資指標(biāo)決策分析模型”節(jié)建模試驗(yàn)（2）對模型進(jìn)行方案試錯(cuò)與優(yōu)化（3）通過模型對投資方案進(jìn)行靈敏度分析（4）總結(jié)最佳方案及其靈敏度（5）根據(jù)以上步驟完成實(shí)驗(yàn)教程第6章練習(xí)題 4.寫實(shí)驗(yàn)報(bào)告，字?jǐn)?shù)800字【實(shí)驗(yàn)過程及結(jié)果分析】1.理解模型的概念和類別步驟：（1）查閱EXCEL幫助， 對照教材分析EXCEL對模型實(shí)現(xiàn)的支持方式。 Excel對模型實(shí)現(xiàn)的支持方式體現(xiàn)在建立模型與模型分析、數(shù)據(jù)訪問與管理以及用戶界面與可視建模三個(gè)方面。1) 建立模型與模型分析。Excel具有靈活的建模能力和對所建模型進(jìn)行What-if分析的功能，利用公式、大量的內(nèi)建財(cái)務(wù)統(tǒng)計(jì)函數(shù)、以及語言建立案例數(shù)值模型，并利用模擬運(yùn)算表、方案管理器等功能模型對“如果決策變量如此變化的話，目標(biāo)變量將如何變化“進(jìn)行分析。Excel利用單變量求解功能求解“為了使目標(biāo)變量達(dá)到某個(gè)特定的目標(biāo)值，決策變量應(yīng)該如何變化的問題。此外，Excel具有大量內(nèi)建函數(shù)，操作者可以利用Excel提供的各種函數(shù)公式，通過確定參數(shù)的數(shù)值，按特定的順序或結(jié)構(gòu)自動(dòng)執(zhí)行復(fù)雜計(jì)算，大大簡化了計(jì)算模型建立的過程，并有助于提高模型的準(zhǔn)確性，特別是Excel的財(cái)務(wù)函數(shù)、統(tǒng)計(jì)函數(shù)等對于財(cái)務(wù)分析相關(guān)模型的建立有很大幫助。2) 數(shù)據(jù)訪問與管理。Excel具有十分強(qiáng)大的數(shù)據(jù)分析功能，并且與各種數(shù)據(jù)庫具有良好的接口。Excel不僅可以創(chuàng)建OLAP多維數(shù)據(jù)集的數(shù)據(jù)源，然后使用該數(shù)據(jù)源進(jìn)行分析，也可以借助于ODBC工具，直接訪問各種由dBase、FoxPro、Access或者SQLServer等生成的外部數(shù)據(jù)庫。因而Excel可以通過簡單的操作從具有不同結(jié)構(gòu)的數(shù)據(jù)庫中獲取所需要的數(shù)據(jù)，利用數(shù)據(jù)分析功能直接進(jìn)行一些復(fù)雜的計(jì)算、統(tǒng)計(jì)工作等，給模型的實(shí)現(xiàn)提供了強(qiáng)大的數(shù)據(jù)支持。3) 用戶界面與可視建模。Excel不僅具有良好的圖形顯示能力，而且允許用戶利用Excel 本身的菜單、工具條、對話框等手段方便地進(jìn)行操作和建立個(gè)性化系統(tǒng)界面。這些功能與其他功能相結(jié)合，決策者可以在決策模型的基礎(chǔ)上經(jīng)過簡單操作制作高質(zhì)量的動(dòng)態(tài)圖形，將抽象的決策模型與直觀生動(dòng)的圖形完美結(jié)合，一定程度上實(shí)現(xiàn)可視建模與分析，從而使模型以最有效的方法幫助決策者進(jìn)行各種必要的決策分析。例如：利用Excel的數(shù)據(jù)透視表功能，可以十分方便地從大量數(shù)據(jù)中提取有用的綜合統(tǒng)計(jì)信息，（2）總結(jié)EXCEL提供了哪些類型的DSS模型excel其中比較常見的模型有以下幾類：1) 預(yù)測與決策模型，包括：項(xiàng)目投資決策、風(fēng)險(xiǎn)性決策、不確定性決策、多目標(biāo)決策分析。2) 決策樹模型。3) 數(shù)學(xué)規(guī)劃模型，包括：線性規(guī)劃模型和非線性規(guī)劃模型。Excel通過其規(guī)劃求解功能將最優(yōu)化算法的能力加入到對工作表數(shù)據(jù)進(jìn)行操縱的能力之中，使得Excel特別適合建立線性規(guī)劃、非線性規(guī)劃等各種優(yōu)化模型。運(yùn)用Excel規(guī)劃求解的基本原理如下圖所示：4) 財(cái)務(wù)報(bào)表模型。5) 管理經(jīng)濟(jì)學(xué)模型。如：工資成本核算模型、個(gè)人所得稅計(jì)算模型、平均稅率計(jì)算模型、員工工資管理模型等。6) 各類計(jì)劃模型，比如生產(chǎn)計(jì)劃。7) 計(jì)量經(jīng)濟(jì)模型。2. 掌握EXCEL提供的函數(shù)的使用方法（1）完成實(shí)驗(yàn)教材“6.3.3使用EXCEL的投資決策函數(shù)”節(jié)的適用于進(jìn)行現(xiàn)金流分析的Excel 函數(shù)示例實(shí)驗(yàn)：NPV、XNPV、IRR、XIRR 和 MIRR。l NPV：語法： NPV(rate,value1,value2, .)a 單擊【公式】|【函數(shù)】命令，在彈出的”插入函數(shù)”對話框（圖2-1）中選擇”財(cái)務(wù)”類別中的NPV函數(shù)，再單擊【確定】后，彈出如圖2-2所示對話框。圖2-1b 按圖2-2所示輸入各參數(shù)，可以看到即時(shí)計(jì)算的結(jié)果，再從公式編輯欄可以看出其計(jì)算公式，即該項(xiàng)目凈現(xiàn)值為：NPV(10%,-10000,3000,4200,6800)= ￥1,188.44。如圖2-3所示。圖2-2圖2-3c 采用公式編輯器或直接為對應(yīng)表格單元輸入函數(shù)的方法來計(jì)算。如圖2-4所示。圖2-4結(jié)果分析：利用NPV計(jì)算可知，該項(xiàng)目凈現(xiàn)值為1188.44元。l XNP：語法：XNPV(rate,values,dates)a. 在工作簿中構(gòu)建如表2-5的計(jì)算凈現(xiàn)值模型。 圖2-5b. 可以看到結(jié)果顯示為（如圖2-6）： 圖2-6結(jié)果分析：該項(xiàng)目的定期現(xiàn)金流的凈現(xiàn)值為2086.65元。l （內(nèi)含報(bào)酬收益率）：語法：IRR(values, guess)設(shè)某項(xiàng)目計(jì)劃投資70000元，并預(yù)期隨后5年的凈收益為：12000、15000、18000、21000、26000(元)。請分別計(jì)算該項(xiàng)目在2、4、5年后的內(nèi)含報(bào)酬收益率。在工作簿中構(gòu)建如表2-7的計(jì)算內(nèi)含報(bào)酬收益率模型。-70,000某項(xiàng)業(yè)務(wù)的初期成本費(fèi)用12,000第一年的凈收入15,000第二年的凈收入18,000第三年的凈收入21,000第四年的凈收入26,000第五年的凈收入公式說明（結(jié)果）=IRR(A1:A5)投資四年后的內(nèi)部收益率 =IRR(A1:A6)五年后的內(nèi)部收益率=IRR(A1:A3,-10%)計(jì)算2年后的內(nèi)含報(bào)酬收益率時(shí)必須包含guess參數(shù)，否則函數(shù) IRR 返回錯(cuò)誤值 #NUM!。圖2-7b. 結(jié)果顯示如圖2-8：圖2-8結(jié)果分析：從結(jié)果來看，隨著投資時(shí)間的延長，內(nèi)部收益率提高。l XIRR()（不定期現(xiàn)金流內(nèi)含報(bào)酬率）：語法：XIRR(values,dates,guess)a. 在工作簿中構(gòu)建如表2-9的計(jì)算內(nèi)含報(bào)酬率模型。圖2-9b. 結(jié)果顯示如圖2-10所示：圖2-10結(jié)果分析：該項(xiàng)目的內(nèi)含報(bào)酬率為0.373。l MIRR（）（修正內(nèi)含報(bào)酬率函數(shù)）：語法：MIRR(values,finance_rate,reinvest_rate)a. 在工作簿中構(gòu)建如表2-11的計(jì)算內(nèi)含報(bào)酬率模型。數(shù)據(jù)說明-120,000資產(chǎn)原值39,000第一年的收益30,000第二年的收益21,000第三年的收益37,000第四年的收益46,000第五年的收益10.00%120,000 貸款額的年利率12.00%再投資收益的年利率公式說明（結(jié)果）=MIRR(A2:A7, A8, A9)五年后投資的修正收益率=MIRR(A2:A5, A8, A9)三年后的修正收益率 =MIRR(A2:A7, A8, 14%)五年的修正收益率（基于 14% 的再投資收益率）表2-11b. 結(jié)果顯示如圖2-12所示。圖2-12結(jié)果分析：再投資收益率影響修正內(nèi)含報(bào)酬率。從本次結(jié)果來看，再投資收益率越高，修正內(nèi)含報(bào)酬率越高。3. 根據(jù)要求構(gòu)建投資指標(biāo)決策分析模型，并用以進(jìn)行決策分析（1）完成實(shí)驗(yàn)教程“6.3.4構(gòu)建投資指標(biāo)決策分析模型”節(jié)建模試驗(yàn)。實(shí)驗(yàn)結(jié)果及分析如圖3-1所示：圖3-1（2） 對模型進(jìn)行方案試錯(cuò)與優(yōu)化。使用What-if分析進(jìn)行方案試錯(cuò)與優(yōu)化。從表3-2中可以看出，無論資本成本率和在投資收益年利率如何變化，方案的其他指標(biāo)始終優(yōu)于方案和方案。但是，當(dāng)其他條件不變，初始投資額減少相同的比例時(shí)，方案的各項(xiàng)指標(biāo)都優(yōu)于方案和。進(jìn)一步試錯(cuò)，發(fā)現(xiàn)當(dāng)初始投資額增加7.9%時(shí)，A方案凈現(xiàn)金值變成了負(fù)數(shù)，方案不可取。如圖3-3且當(dāng)初始投資額增加17%時(shí)，A的內(nèi)含報(bào)酬收益率和修正內(nèi)含報(bào)酬率都小于方案B。如圖3-4所示。圖3-2圖3-3圖3-4 因而，方案A的優(yōu)化方向是在其他條件不變的情況下降低初始投資額。如果有可能的話，盡可能的提高投資再回收年利率和降低資本成本率，是方案更優(yōu)。（3） 通過模型對投資方案進(jìn)行靈敏度分析。1） 分析指標(biāo)：凈現(xiàn)金值、內(nèi)含報(bào)酬收益率、修正內(nèi)含報(bào)酬率。2） 不確定因素：資本成本率、在投資收益的年利率、初期投資成本。3） 變化幅度：三個(gè)因素變化幅度分別?。?30%，-20%，-10%，10%，20%，30%。當(dāng)變動(dòng)因素變動(dòng)時(shí)，凈現(xiàn)金值、內(nèi)含報(bào)酬率、修正內(nèi)含報(bào)酬率變動(dòng)情況如圖3-5所示。圖3-5結(jié)合圖3-5進(jìn)行靈敏度分析：變動(dòng)因素靈敏度分析初始投資額凈現(xiàn)值、內(nèi)含報(bào)酬率和修正內(nèi)含報(bào)酬率對初始投資額的變化很敏感。當(dāng)其他條件不變，投資額增加17%時(shí)，方案B優(yōu)于方案A，但此時(shí)所有的方案的凈現(xiàn)值都為負(fù)數(shù)，所有方案都不可取。資本成本率靈敏度為0。在其他條件不變的情況下，資本成本率的變動(dòng)不會(huì)影響內(nèi)含報(bào)酬率和修正內(nèi)含報(bào)酬率，且在變動(dòng)范圍中，無論資本成本率如何變動(dòng)，A仍是3個(gè)方案中的最優(yōu)方案。再投資收益年利率靈敏度為0，在其他條件不變的情況下，再投資收益年利率的變動(dòng)不會(huì)影響凈現(xiàn)值和內(nèi)含報(bào)酬率，且在變動(dòng)范圍中，投資收益年利率如何變動(dòng)，A仍是3個(gè)方案中的最優(yōu)方案。（4） 總結(jié)最佳方案及其靈敏度。實(shí)際環(huán)境中，投資額過剩的情況不常出現(xiàn)，因而最佳方案是A方案。A方案對初始投資額的變動(dòng)最為敏感，其他條件不變，過度投資會(huì)帶來很大損失。4.第6章練習(xí)題(1) 練習(xí)題的投資指標(biāo)決策分析模型如實(shí)驗(yàn)結(jié)果如圖3-6所示：圖3-6結(jié)果分析：凈現(xiàn)金值均為正值且大小依次是：DABC。但要注意的是，D在0期投入的資金是A的兩倍。內(nèi)含報(bào)酬收益率大小依次是：ADBC。修正內(nèi)含報(bào)酬率的大小依次是DABC，其中，D方案的修正內(nèi)含報(bào)酬率僅是略大于A。如果考慮凈現(xiàn)金流量和修正內(nèi)含報(bào)酬率來看，應(yīng)該選擇D方案。但是如果考慮到投資成本與內(nèi)含報(bào)酬收益率時(shí)，應(yīng)該選擇A方案。所以，如果手上有充足的資金進(jìn)行投資，可以選擇D方案。如果手上資金不足，可以選擇A方案。但總體來說， 相比D方案，A方案投資成本小、內(nèi)含報(bào)酬收益率高，因而推薦A方案。(2) 對模型進(jìn)行方案試錯(cuò)與優(yōu)化。如圖3-7所示：1） 當(dāng)初始投資額增加10%時(shí)，D方案的3個(gè)指標(biāo)相比其余3個(gè)方案都是最好的，但是各個(gè)指標(biāo)并不是最好的，例如凈現(xiàn)值相比于初始投資額不增加時(shí)，明顯下降。進(jìn)一步試錯(cuò)，發(fā)現(xiàn)當(dāng)初始投資額投入增加(8%-25%)時(shí)，D方案最優(yōu)。（圖3-8表示初始投資增加8%時(shí)的情況）。所以D方案適用于投資過剩的情況。2） 當(dāng)資本成本率變化時(shí)，D方案是最優(yōu)方案。3） 當(dāng)再投資收益年利率增加10%以上時(shí)，A的修正內(nèi)含報(bào)酬率大于D方案，此時(shí)，A方案最優(yōu)。圖3-7圖3-84） 從凈現(xiàn)金流量入手對A方案進(jìn)一步試錯(cuò)和優(yōu)化。以下優(yōu)化是指其他條件不改變的情況下，改變方案中的某個(gè)條件進(jìn)行優(yōu)化：經(jīng)計(jì)算，發(fā)現(xiàn)只要各期的凈現(xiàn)金流量增加50及以上，即可使A方案的內(nèi)含報(bào)酬收益率和修正內(nèi)含報(bào)酬率均大于D方案。A方案的變動(dòng)范圍很小。只要保證每期的凈現(xiàn)金流量增加50以上，即可使方案最優(yōu)。5）從凈現(xiàn)金流量入手對A方案進(jìn)一步試錯(cuò)和優(yōu)化。期間A方案凈現(xiàn)金流量優(yōu)化方案0-30000當(dāng)初始投資額=9700時(shí), D方案方案的內(nèi)含報(bào)酬收益率和修正內(nèi)含報(bào)酬率均大于D方案。變動(dòng)范圍是700213500當(dāng)?shù)?期凈現(xiàn)金流量=14350時(shí), D方案方案的內(nèi)含報(bào)酬收益率和修正內(nèi)含報(bào)酬率均大于D方案。變動(dòng)范圍是850321400當(dāng)?shù)?期凈現(xiàn)金流量=22400時(shí), D方案方案的內(nèi)含報(bào)酬收益率和修正內(nèi)含報(bào)酬率均大于D方案。因而，總體說來，D方案的某一期凈現(xiàn)金流量增加800元左右時(shí)，即可使本方案最優(yōu)。所以，施行D方案時(shí)，可以盡量使凈現(xiàn)金流量增加800元左右，達(dá)到最優(yōu)。(3) 通過模型對投資方案進(jìn)行靈敏度分析。1) 分析指標(biāo)：凈現(xiàn)金值、內(nèi)含報(bào)酬收益率、修正內(nèi)含報(bào)酬率。2) 不確定因素：資本成本率、在投資收益的年利率、初期投資成本。3) 變化幅度：三個(gè)因素變化幅度分別取：-30%，-20%，-10%，10%，20%，30%。4) 當(dāng)變動(dòng)因素變動(dòng)時(shí)，凈現(xiàn)金值、內(nèi)含報(bào)酬率、修正內(nèi)含報(bào)酬率變動(dòng)情況如圖3-9所示。圖3-95） 靈敏度分析：變動(dòng)因素靈敏度分析初始投資額凈現(xiàn)值、內(nèi)含報(bào)酬率和修正內(nèi)含報(bào)酬率對初始投資額的變化很敏感。隨著初始投資額的變動(dòng)，這三個(gè)指標(biāo)的變動(dòng)幅度很大。當(dāng)投資額增加10%時(shí)，D的修正內(nèi)含報(bào)酬率大于A方案。資本成本率靈敏度為0。在其他條件不變的情況下，資本成本率的變動(dòng)不會(huì)影響內(nèi)含報(bào)酬率和修正內(nèi)含報(bào)酬率，且在變動(dòng)范圍中，無論資本成本率如何變動(dòng)，D仍是3個(gè)方案中的最優(yōu)方案。再投資收益年利率修正內(nèi)含報(bào)酬率對再投資收益年利率變動(dòng)敏感。且當(dāng)再投資收益年利率增加10%以上時(shí)，A方案的修正內(nèi)含報(bào)酬率大于D方案的。(4) 總結(jié)最佳方案及其靈敏度?？傮w而言，A方案對