管理利劍之EXCEL 05 決策分析

1、序序號號工工作作表表名名稱稱1目目錄錄2方方案案管管理理例例子子3方方案案摘摘要要4了了解解規(guī)規(guī)劃劃求求解解5運(yùn)運(yùn)算算結(jié)結(jié)果果報報告告 16Shipping7Allocation8Investments9回回歸歸分分析析例例題題10回回歸歸分分析析結(jié)結(jié)果果返回Resource Cost VariablesHourly labor cost38Material cost62Product AProduct BProduct CHours per unit121424Material per unit6914Cost to produce$828$1,090$1,780Sales price$79

2、5$1,295$2,195Unit profit-$33$205$415Units produced361812Total profit per product-$1,188$3,690$4,980Total Profit$7,482返回方案摘要當(dāng)前值:Worst CaseMost LikelyCreated by JohnWalkenbach on2/20/2003Created by JohnWalkenbach on2/20/2003可變單元格:Hourly_labor_cost383834Material_cost626259結(jié)果單元格:ProductA_Profit-$1,188-$

3、1,188$1,188ProductB_Profit$3,690$3,690$5,184ProductC_Profit$4,980$4,980$6,636Total_Profit$7,482$7,482$13,008注釋: “當(dāng)前值”這一列表示的是在建立方案匯總時，可變單元格的值。每組方案的可變單元格均以灰色底紋突出顯示。Best CaseNew CaseCreated by JohnWalkenbach on2/20/2003創(chuàng)建者 Brandon日期 2007-1-29修改者 Brandon日期 2007-1-2930305757$3,348$3,348$6,516$6,516$8,124

4、$8,124$17,988$17,988返回1產(chǎn)產(chǎn)品品生生產(chǎn)產(chǎn)組組合合問問題題下下表表是是三三種種產(chǎn)產(chǎn)品品的的收收益益表表ProductProfit/UnitProduct A$13Product B$18Product C$22該該公公司司的的生生產(chǎn)產(chǎn)條條件件及及市市場場條條件件可可總總結(jié)結(jié)如如下下：1)組組合合生生產(chǎn)產(chǎn)能能力力為為每每天天300單單位位；2)公公司司至至少少需需要要50單單位位的的產(chǎn)產(chǎn)品品A，來來滿滿足足現(xiàn)現(xiàn)有有的的訂訂單單要要求求；3)公公司司至至少少需需要要40單單位位的的產(chǎn)產(chǎn)品品B，來來滿滿足足預(yù)預(yù)訂訂單單；4)由由于于市市場場對對產(chǎn)產(chǎn)品品C的的需需求求是是相相當(dāng)當(dāng)有

5、有限限的的，公公司司不不想想生生產(chǎn)產(chǎn)40單單位位以以上上的的產(chǎn)產(chǎn)品品C請請問問該該公公司司為為了了獲獲得得最最大大的的收收益益，應(yīng)應(yīng)如如何何組組合合生生產(chǎn)產(chǎn)A、B、C三三種種產(chǎn)產(chǎn)品品使使用用規(guī)規(guī)劃劃求求解解來來解解決決的的過過程程如如下下：1)建建立立含含有有數(shù)數(shù)值值與與公公式式的的工工作作表表，保保證證建建立立的的單單元元格格的的邏邏輯輯性性。2)選選擇擇“工工具具”“規(guī)規(guī)劃劃求求解解”命命令令，調(diào)調(diào)出出“規(guī)規(guī)劃劃求求解解參參數(shù)數(shù)”對對話話框框。3)指指定定目目標(biāo)標(biāo)單單元元格格4)指指定定可可變變單單元元格格5)指指定定約約束束條條件件6)如如果果有有必必要要，改改變變規(guī)規(guī)劃劃求求解解選選項(xiàng)

6、項(xiàng)7)使使用用方方案案求求解解UnitsProfit/UnitProfitProduct A50$13$650Product B210$18$3,780Product C40$22$880Total300$5,3102規(guī)規(guī)劃劃求求解解選選項(xiàng)項(xiàng)1)最最長長運(yùn)運(yùn)算算時時間間：指指定定用用戶戶需需要要規(guī)規(guī)劃劃求求解解求求解解一一個個問問題題所所花花費(fèi)費(fèi)時時間間的的最最大大值值。如如果果規(guī)規(guī)劃劃求求解解報報告告求求解解超超出出了了時時間間限限制制，用用戶戶可可以以增增加加用用于于求求解解問問題題所所花花費(fèi)費(fèi)的的時時間間。2)迭迭代代次次數(shù)數(shù)：鍵鍵入入需需要要規(guī)規(guī)劃劃求求解解執(zhí)執(zhí)行行的的近近似似求求解解

7、的的最最大大次次數(shù)數(shù)。3)精精度度：指指定定單單元元格格定定位位和和約約束束公公式式必必須須滿滿足足約約束束條條件件的的接接近近程程度度。用用戶戶指指定定約約束束越越精精確確，Excel求求解解問問題題的的速速度度越越慢慢。4)允允許許誤誤差差：指指定定整整數(shù)數(shù)解解的的最最大大誤誤差差百百分分比比（只只有有當(dāng)當(dāng)使使用用整整數(shù)數(shù)約約束束時時有有效效）5)收收斂斂度度：指指定定一一個個數(shù)數(shù)值值確確定定何何時時規(guī)規(guī)劃劃求求解解應(yīng)應(yīng)該該停停止止求求更更佳佳解解。收收斂斂僅僅適適用用于于非非線線性性問問題題而而且且只只能能介介于于0和和1之之間間。6)采采用用線線性性模模型型：選選擇擇這這一一復(fù)復(fù)選選框

8、框可可以以加加速速求求解解過過程程，但但只只有有在在模模型型中中的的所所有有關(guān)關(guān)系系都都是是線線性性的的時時候候才才可可以以使使用用這這一一模模型型。如如果果可可調(diào)調(diào)整整單單元元格格是是多多個個或或者者是是分分開開的的，或或者者問問題題使使用用的的是是指指數(shù)數(shù)，那那么么用用戶戶都都不不可可以以使使用用這這一一選選項(xiàng)項(xiàng)。7)假假定定非非負(fù)負(fù)：選選擇擇這這一一復(fù)復(fù)選選框框是是為為所所有有未未設(shè)設(shè)定定下下限限的的可可調(diào)調(diào)整整單單元元格格假假設(shè)設(shè)下下限限為為零零。8)自自動動按按比比例例縮縮放放：用用于于當(dāng)當(dāng)問問題題在在數(shù)數(shù)量量上上存存在在巨巨大大差差異異的的時時候候，例例如如當(dāng)當(dāng)用用戶戶試試圖圖通通

9、過過改改變變非非常常巨巨大大的的單單元元格格來來最最大大化化百百分分比比時時。9)顯顯示示迭迭代代結(jié)結(jié)果果：通通過過選選擇擇這這一一復(fù)復(fù)選選框框可可以以使使規(guī)規(guī)劃劃求求解解在在每每次次迭迭代代結(jié)結(jié)束束以以后后軼軼并并顯顯示示結(jié)結(jié)果果。10)估估計計、導(dǎo)導(dǎo)數(shù)數(shù)和和搜搜索索：使使用用這這些些選選項(xiàng)項(xiàng)可可以以控控制制解解的的某某些些技技術(shù)術(shù)方方面面的的問問題題。通通常常情情況況下下，用用戶戶不不需需改改變變這這些些設(shè)設(shè)置置。11)裝裝入入模模型型：單單擊擊這這一一按按鈕鈕使使Excel顯顯示示“裝裝入入模模型型”對對話話框框。在在這這一一對對話話框框中中，可可以以指指定定一一個個區(qū)區(qū)域域包包含含用用

10、戶戶需需要要載載入入的的模模型型。12)保保存存模模型型：單單擊擊這這一一按按鈕鈕使使Excel顯顯示示“保保存存模模型型”對對話話框框。在在這這一一對對話話框框中中，可可以以指指定定一一個個區(qū)區(qū)域域，Excel將將把把模模型型參參數(shù)數(shù)存存儲儲在在這這一一區(qū)區(qū)域域中中。MicrosoftMicrosoft ExcelExcel 11.011.0 運(yùn)運(yùn)算算結(jié)結(jié)果果報報告告返回返回工工作作表表 ExcelExcel 高高級級 規(guī)規(guī)劃劃求求解解.xls.xls了了解解規(guī)規(guī)劃劃求求解解報報告告的的建建立立: : 2007-5-112007-5-11 15:14:1515:14:15目標(biāo)單元格 (最大值

11、)單單元元格格名名字字初初值值終終值值$E$31Total Profit$5,310$5,310可變單元格單單元元格格名名字字初初值值終終值值$C$28Product A Units5050$C$29Product B Units210210$C$30Product C Units4040約束單單元元格格名名字字單單元元格格值值公公式式狀狀態(tài)態(tài)型型數(shù)數(shù)值值$C$31Total Units300 $C$31=300 未到限制值0$C$30Product C Units40 $C$30=50 到達(dá)限制值-$C$29Product B Units210 $C$29=40 未到限制值1702311e5

12、fd4bb848edab1ea2e52efe0ffd.xlsPage 8返回ShippingShipping CostsCosts TableTableL.A.L.A. St.St. LouisLouisBostonBostonDenver$58$47$108Houston$87$46$100Atlanta$121$30$57Miami$149$66$83Seattle$62$115$164Detroit$128$28$38NumberNumberNo.No. toto shipship from.from.No. to beStoreStoreNeededNeededL.A.L.A.St.S

13、t. LouisLouisBostonBostonShippedDenveouston22502250225Atlanta10001000100Miami250025225250Seattle12012000120Detroiotal995270350375995Starting Inventory:400350500No. Remaining:1300125Total Shipping Costs:$55,515要求：總運(yùn)輸費(fèi)用最小AllocationPage 9XYZ Toys Inc.返回Materials NeededMaterial

14、Toy AToy BToy CToy DToy EAmt.Avail.Amt. UsedAmt.LeftRed Paint010136256250Blue Paint3101064061921White Paint212021,1001,0982Plastic152218758741Wood303552,2002,1982Glue123231,5001,362138Unit Profit$15$30$20$25$25No. to Make1871816440189Profit$2,805$540$3,280$1,000$4,725Total Profit$12,350要求：總利潤最大返回Inv

15、estmentsPage 10優(yōu)優(yōu)化化投投資資項(xiàng)項(xiàng)目目（練練習(xí)習(xí)）返回返回您您是是一一個個金金融融投投資資機(jī)機(jī)構(gòu)構(gòu)的的項(xiàng)項(xiàng)目目經(jīng)經(jīng)理理，現(xiàn)現(xiàn)做做一一些些投投資資組組合合，以以得得到到最最大大的的回回報報。請請給給出出最最大大的的回回報報率率（應(yīng)應(yīng)是是9.25%）不不過過投投資資方方面面有有以以下下一一些些具具體體的的限限制制：1)用用于于New Car Loans項(xiàng)項(xiàng)目目的的投投資資金金額額至至少少是是用用于于Used Car Loans項(xiàng)項(xiàng)目目的的投投資資額額的的3倍倍2)Car Loans的的投投資資額額至至少少占占投投資資總總額額的的15。3)用用于于Unsecured Loans的的投

16、投資資額額不不得得多多于于總總投投資資額額的的254)至至少少有有10%的的投投資資用用于于Bank CDs項(xiàng)項(xiàng)目目5)投投資資總總額額為為500百百萬萬美美元元以以下下您您可可投投資資的的項(xiàng)項(xiàng)目目的的投投資資收收益益率率InvestmentPct YieldNew Car Loans6.90%Used Car Loans8.25%Real Estate Loans8.90%Unsecured Loans13.00%Bank CDs4.60%InvestmentPct YieldAmountProfitPercentNew Car Loans6.90%0#DIV/0!Used Car Loan

17、s8.25%0#DIV/0!Real Estate Loans8.90%0#DIV/0!Unsecured Loans13.00%0#DIV/0!Bank CDs4.60%0#DIV/0!Total00Car Loans0#DIV/0!返回一一位位醫(yī)醫(yī)院院管管理理專專家家宣宣稱稱通通過過一一個個醫(yī)醫(yī)院院里里的的病病床床數(shù)數(shù)目目（通通常常用用來來衡衡量量一一個個醫(yī)醫(yī)院院的的規(guī)規(guī)模模）就就可可以以估估測測了了這這個個醫(yī)醫(yī)院院里里的的全全職職工工作作人人員員數(shù)數(shù)。有有位位商商業(yè)業(yè)分分析析人人員員決決定定建建立立一一個個回回歸歸模模型型，通通過過醫(yī)醫(yī)院院的的床床位位數(shù)數(shù)來來預(yù)預(yù)測測醫(yī)醫(yī)院院的的全全職職

18、工工作作人人員員數(shù)數(shù)。該該人人員員調(diào)調(diào)查查了了12家家醫(yī)醫(yī)院院，得得到到 了了以以下下數(shù)數(shù)據(jù)據(jù)病病床床數(shù)數(shù)全全職職工工作作人人員員數(shù)數(shù)預(yù)預(yù)測測值值23692995291023511842126461255013854178641566618476176782259080706050403020100050100150200250y = 0.0011x2 - 0.17x + 10.6993R2 = 0.8983SUMMARY OUTPUT返回回歸統(tǒng)計Multiple R0.99271R Square0.98547Adjusted R Square0.89456標(biāo)準(zhǔn)誤差18.54觀測值12方差分析dfSSMSFSignificance F回歸分析1256355256355745.8021E-10殘差11 3781.036343.731總計12260136Coefficients標(biāo)準(zhǔn)誤差t StatP-value Lower 95% Upper 95% 下限 95.0%Intercept0#N/A#N/A#N/A#N/A#N/A#N/A病床數(shù)2.78532 0.10199127.30941.9E-112.560843.00982.56084RESIDUAL OUTPUTPROBABILIT