第七講-基于excel的決策方法

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

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

3、策確定的非概率型不確定的概率型決策環(huán)境決策標(biāo)準(zhǔn)EMBA - 管理決策與統(tǒng)計分析7-8確定的: 每個選擇的決策結(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è)計一種方案，使得人們從自己家里出發(fā)，經(jīng)過每座橋恰好一次，最后回到家里。EMBA - 管理決策與統(tǒng)計分析7-9不確定的: 一個選擇后將發(fā)生的結(jié)果未

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

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

6、0采取一些防御措施-1-100-10000所有人注射疫苗-1000-1000-1000狀態(tài)行動EMBA - 管理決策與統(tǒng)計分析7-13非概率型概率型決策準(zhǔn)則*概率決策標(biāo)準(zhǔn): 考慮不確定事件的概率 并選擇一個方案來最大化期望回報或最小化期望損失 最大化期望值 最小化期望機會損失決策準(zhǔn)則在具有多個自然狀態(tài)的決策問題中，決策者雖然不知道未來哪一個狀態(tài)一定發(fā)生，但知道（或可估計）每個狀態(tài)發(fā)生的可能性有多大，即知道（或可估計）各自然狀態(tài)發(fā)生的概率。EMBA - 管理決策與統(tǒng)計分析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)計分析7-15What-If 分析：評價特定參數(shù)組合對模型產(chǎn)出的影響，又稱敏感性分析 (sensitivity analysis).Excel工具敏感性分析 Data tables情景管理 Scenario manager求解 Goal seek 模型分析寶玉決策：寶釵？黛玉？EMBA - 管理決策與統(tǒng)計分析7-16計算一個或兩個變量對結(jié)果的影響Excel工具一個變量敏感度分析（One-way data tables）兩個變量敏感度分析（Two-way data tables）敏感度分析

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

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

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

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

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