1、蔣煒上海交通大學(xué)安泰經(jīng)濟(jì)與管理學(xué)院 管理決策與統(tǒng)計(jì)分析第七講基于Excel的決策方法主要內(nèi)容 決策模型的分類 電子表格建模及優(yōu)化 復(fù)雜模型及優(yōu)化 線性規(guī)劃 蒙特卡洛模擬仿真EMBA - 管理決策與統(tǒng)計(jì)分析7-3“選擇一個(gè)門” 不要打開它“現(xiàn)在打開門X” (揭示了一只山羊)“堅(jiān)持選擇或換一扇門?”“現(xiàn)在打開你選擇的門” 結(jié)果是？Marilyns Goats (Monty Hall問題)ABC EMBA - 管理決策與統(tǒng)計(jì)分析7-4堅(jiān)持沒區(qū)別換堅(jiān)持原來的選擇還是換一扇門?:9 of 30我們可以用統(tǒng)計(jì)和概率模型來驗(yàn)證! 決策樹蒙特卡洛仿真 EMBA - 管理決策與統(tǒng)計(jì)分析7-5模型是對(duì)一個(gè)真正的系

2、統(tǒng)、想法或?qū)ο蟮某橄蠡虼砟Ｐ涂梢允菆D、電子表格或數(shù)學(xué)關(guān)系模型包括數(shù)據(jù)、不可控變量以及決策變量決策模型是可以用來理解、分析或支持決策的模型決策模型EMBA - 管理決策與統(tǒng)計(jì)分析7-6描述性 - 描述關(guān)系和提供評(píng)估信息規(guī)范的(優(yōu)化模型) - 確定一個(gè)最優(yōu)策略,即決策者應(yīng)該采取最大化或最小化一些目標(biāo)的最佳行動(dòng) (人們?yōu)榱诉_(dá)到一定的目標(biāo)，從若干個(gè)可能的策略, 例如行動(dòng)、方案等中, 選取效果最好的策略的過程，例如田忌賽馬）決策模型的種類EMBA - 管理決策與統(tǒng)計(jì)分析7-7依據(jù)決策環(huán)境和標(biāo)準(zhǔn)，決策可以劃分為確定環(huán)境不確定環(huán)境概率模型非概率模型決策理論不確定型決策 確定型決策 風(fēng)險(xiǎn)型決策 未來狀態(tài)做決

3、策確定的非概率型不確定的概率型決策環(huán)境決策標(biāo)準(zhǔn)EMBA - 管理決策與統(tǒng)計(jì)分析7-8確定的: 每個(gè)選擇的決策結(jié)果已知決策環(huán)境確定的不確定的決策環(huán)境Example: Must print 10,000 color brochuresOffset press A: $2,000 fixed cost + $.24 per pageOffset press B: $3,000 fixed cost + $.12 per page*哥尼斯堡七橋問題：是否可以設(shè)計(jì)一種方案，使得人們從自己家里出發(fā)，經(jīng)過每座橋恰好一次，最后回到家里。EMBA - 管理決策與統(tǒng)計(jì)分析7-9不確定的: 一個(gè)選擇后將發(fā)生的結(jié)果未

4、知決策環(huán)境不確定的確定的決策環(huán)境例子: 你必須決定現(xiàn)在購(gòu)買一個(gè)物品或者等待。如果你現(xiàn)在買價(jià)格是$2,000. 如果你之后買價(jià)格將可能下跌到 $1,500 或者上升到$2,200。也許之后會(huì)有一個(gè)更好特性的物品。 *EMBA - 管理決策與統(tǒng)計(jì)分析7-10決策準(zhǔn)則非概率決策標(biāo)準(zhǔn): 決策規(guī)則可以用于不確定事件的概率未知的情形。非概率型概率型決策準(zhǔn)則* 極大極大準(zhǔn)則 極大極小準(zhǔn)則 極小極大后悔準(zhǔn)則年月日，微軟公司總裁的比爾蓋茨宣布，他要向陷入危機(jī)的蘋果電腦公司注入資金億美元要求決策者具有：豐富的經(jīng)驗(yàn)淵博的知識(shí)敏銳的洞察力活躍的邏輯思維 EMBA - 管理決策與統(tǒng)計(jì)分析7-11決策理論問題有以下組成部

5、分：一組決策選項(xiàng)一組未來可能的狀態(tài)對(duì)應(yīng)每一對(duì)決策和未來狀態(tài)的回報(bào)/損失評(píng)估未來可能發(fā)生事件可能性決策準(zhǔn)則決策理論EMBA - 管理決策與統(tǒng)計(jì)分析7-12未來被分類成 H個(gè)可能狀態(tài): s1, s2, , sH. 每個(gè)狀態(tài) si 也許有一個(gè)關(guān)聯(lián)概率 pi. 我們能選擇 K 個(gè)行動(dòng)中的一個(gè): a1, a2, , aK. 對(duì)于每一組第 i 個(gè)行動(dòng)和第 j個(gè)狀態(tài), 我們得到“回報(bào)”（或“損失”）Mij。為了讓這個(gè)(簡(jiǎn)單的)理論有效, 所有的 ”回報(bào)” 必須基于同樣的量綱(monetary) 測(cè)量 。我們選擇一個(gè)行為來最大化回報(bào)。決策理論沒有禽流感爆發(fā)有小型爆發(fā)禽流感大爆發(fā)不采取行動(dòng)0-500-10000

6、0采取一些防御措施-1-100-10000所有人注射疫苗-1000-1000-1000狀態(tài)行動(dòng)EMBA - 管理決策與統(tǒng)計(jì)分析7-13非概率型概率型決策準(zhǔn)則*概率決策標(biāo)準(zhǔn): 考慮不確定事件的概率 并選擇一個(gè)方案來最大化期望回報(bào)或最小化期望損失 最大化期望值 最小化期望機(jī)會(huì)損失決策準(zhǔn)則在具有多個(gè)自然狀態(tài)的決策問題中，決策者雖然不知道未來哪一個(gè)狀態(tài)一定發(fā)生，但知道（或可估計(jì)）每個(gè)狀態(tài)發(fā)生的可能性有多大，即知道（或可估計(jì)）各自然狀態(tài)發(fā)生的概率。EMBA - 管理決策與統(tǒng)計(jì)分析7-14模型要素F = 自己生產(chǎn)的固定成本V = 自己生產(chǎn)的單位可變成本C = 產(chǎn)品外包的單位成本D = 需求量自己生產(chǎn)的總成

7、本 TMC = F + V * D外包總成本 TOC = C * D.產(chǎn)品外包模型EMBA - 管理決策與統(tǒng)計(jì)分析7-15What-If 分析：評(píng)價(jià)特定參數(shù)組合對(duì)模型產(chǎn)出的影響，又稱敏感性分析 (sensitivity analysis).Excel工具敏感性分析 Data tables情景管理 Scenario manager求解 Goal seek 模型分析寶玉決策：寶釵？黛玉？EMBA - 管理決策與統(tǒng)計(jì)分析7-16計(jì)算一個(gè)或兩個(gè)變量對(duì)結(jié)果的影響Excel工具一個(gè)變量敏感度分析（One-way data tables）兩個(gè)變量敏感度分析（Two-way data tables）敏感度分析

8、 Data TablesEMBA - 管理決策與統(tǒng)計(jì)分析7-17一個(gè)變量（One Way Data Table）EMBA - 管理決策與統(tǒng)計(jì)分析7-18兩個(gè)變量（Two Way Data Table）EMBA - 管理決策與統(tǒng)計(jì)分析7-19情景分析：Scenario AnalysisEMBA - 管理決策與統(tǒng)計(jì)分析7-20在Excel中找出產(chǎn)生一個(gè)已知結(jié)果的輸入變量的值例子: 產(chǎn)品外包模型的盈虧平衡點(diǎn)求解：Goal SeekSet cell is B18; To value = 0; By changing cell is B14 EMBA - 管理決策與統(tǒng)計(jì)分析7-21最優(yōu)化： Solver

9、如何合理定價(jià)最大化利潤(rùn)？Solution: Price = $428.57; profit = $115,714.28EMBA - 管理決策與統(tǒng)計(jì)分析7-22為了考慮未來的不確定性，我們可以對(duì)不確定的變量進(jìn)行蒙特卡洛仿真Excel中有幾個(gè)重要的隨機(jī)數(shù)發(fā)生器Rand()Rand.between()數(shù)據(jù)分析模型中的不確定性 蒙特卡洛模擬/仿真EMBA - 管理決策與統(tǒng)計(jì)分析7-23C = 采購(gòu)成本 purchase costR = 銷售成本 sale priceS = 回收成本 salvage valueD = 單階段需求 demand during a single periodQ = 采購(gòu)量

10、quantity purchased利潤(rùn) Net profit = R * Quantity Sold + S * Surplus Quantity - C * Q報(bào)童模型 （Newsvendor Model）EMBA - 管理決策與統(tǒng)計(jì)分析7-24運(yùn)用歷史數(shù)據(jù)優(yōu)化報(bào)童模型EMBA - 管理決策與統(tǒng)計(jì)分析7-25如果y = f(x) 代表一個(gè)決策模型, 然后Ey 一般不等于f(Ex)。換言之, 你不能使用一個(gè)模型中輸入變量的平均值來決定期望產(chǎn)出。平均數(shù)的缺陷“Flaw of Averages”EMBA - 管理決策與統(tǒng)計(jì)分析7-26兩個(gè)決策EMBA - 管理決策與統(tǒng)計(jì)分析7-27Forecas

11、t prediction interval兩個(gè)決策EMBA - 管理決策與統(tǒng)計(jì)分析7-28Forecast prediction interval兩個(gè)決策EMBA - 管理決策與統(tǒng)計(jì)分析7-29報(bào)童模型 仿真EMBA - 管理決策與統(tǒng)計(jì)分析7-30我們常常面臨選擇一些變量來最大化或最小化一個(gè)目標(biāo)函數(shù)（objective function）。決策變量的最優(yōu)解（optimal solution）約束條件 決策變量必須滿足的條件。復(fù)雜決策模型及優(yōu)化EMBA - 管理決策與統(tǒng)計(jì)分析7-31假設(shè)一個(gè)葡萄酒廠Walker Wines從當(dāng)?shù)氐姆N植者那里收購(gòu)葡萄制作兩種葡萄酒：Shiraz和Merlot。制作

12、一瓶Shiraz需要 $1.60，而制作一瓶Merlot需要 $1.40。合同要求 Shiraz的量不低于40%但不高于70%。市場(chǎng)調(diào)研估計(jì)， Shiraz的最低需求量為1000瓶，但每在廣告上多花 $1，銷量增加5瓶； Merlot的最低需求量為2000瓶，每在廣告上多花 $1，銷量增加8瓶；產(chǎn)量不應(yīng)該超過需求。 每瓶Shiraz和Merlot的零售價(jià)為 $6.25和 $5.25。Walker Wines有 $50,000去買葡萄和做廣告，經(jīng)營(yíng)目標(biāo)為利潤(rùn)最大化。案例：葡萄酒生產(chǎn)/營(yíng)銷配置EMBA - 管理決策與統(tǒng)計(jì)分析7-32確定決策變量 S=Shiraz的產(chǎn)量M=Merlot的產(chǎn)量AS=Shiraz的廣告投入AM=Merlot的廣告投入確定目標(biāo)函數(shù)利潤(rùn)=(6.25S+5.25M)-(1.60S+1.40M+AS+AM)找到所有的約束條件預(yù)算約束 1.60S+1.40M+AS+AM 50,000合同要求：0.4(S+M) S 0.7(S+M)產(chǎn)量不能超過需求量：S 1000+5ASM 2000+8AM建立數(shù)學(xué)模型優(yōu)化模型EMBA - 管理決策與統(tǒng)計(jì)分析7-33Excel模型EMBA - 管理決策與統(tǒng)計(jì)分析7-34如果Shiraz的成本為 $1.65EMBA - 管理決策與統(tǒng)計(jì)分析7-35敏感性分析EMBA - 管