第七讲-基于excel的决策方法

1、蒋炜上海交通大学安泰经济与管理学院 管理决策与统计分析第七讲基于Excel的决策方法主要内容 决策模型的分类 电子表格建模及优化 复杂模型及优化 线性规划 蒙特卡洛模拟仿真EMBA - 管理决策与统计分析7-3“选择一个门” 不要打开它“现在打开门X” (揭示了一只山羊)“坚持选择或换一扇门?”“现在打开你选择的门” 结果是？Marilyns Goats (Monty Hall问题)ABC EMBA - 管理决策与统计分析7-4坚持没区别换坚持原来的选择还是换一扇门?:9 of 30我们可以用统计和概率模型来验证! 决策树蒙特卡洛仿真 EMBA - 管理决策与统计分析7-5模型是对一个真正的系

2、统、想法或对象的抽象或代表模型可以是图、电子表格或数学关系模型包括数据、不可控变量以及决策变量决策模型是可以用来理解、分析或支持决策的模型决策模型EMBA - 管理决策与统计分析7-6描述性 - 描述关系和提供评估信息规范的(优化模型) - 确定一个最优策略,即决策者应该采取最大化或最小化一些目标的最佳行动 (人们为了达到一定的目标，从若干个可能的策略, 例如行动、方案等中, 选取效果最好的策略的过程，例如田忌赛马）决策模型的种类EMBA - 管理决策与统计分析7-7依据决策环境和标准，决策可以划分为确定环境不确定环境概率模型非概率模型决策理论不确定型决策 确定型决策 风险型决策 未来状态做决

3、策确定的非概率型不确定的概率型决策环境决策标准EMBA - 管理决策与统计分析7-8确定的: 每个选择的决策结果已知决策环境确定的不确定的决策环境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*哥尼斯堡七桥问题：是否可以设计一种方案，使得人们从自己家里出发，经过每座桥恰好一次，最后回到家里。EMBA - 管理决策与统计分析7-9不确定的: 一个选择后将发生的结果未

4、知决策环境不确定的确定的决策环境例子: 你必须决定现在购买一个物品或者等待。如果你现在买价格是$2,000. 如果你之后买价格将可能下跌到 $1,500 或者上升到$2,200。也许之后会有一个更好特性的物品。 *EMBA - 管理决策与统计分析7-10决策准则非概率决策标准: 决策规则可以用于不确定事件的概率未知的情形。非概率型概率型决策准则* 极大极大准则 极大极小准则 极小极大后悔准则年月日，微软公司总裁的比尔盖茨宣布，他要向陷入危机的苹果电脑公司注入资金亿美元要求决策者具有：丰富的经验渊博的知识敏锐的洞察力活跃的逻辑思维 EMBA - 管理决策与统计分析7-11决策理论问题有以下组成部

5、分：一组决策选项一组未来可能的状态对应每一对决策和未来状态的回报/损失评估未来可能发生事件可能性决策准则决策理论EMBA - 管理决策与统计分析7-12未来被分类成 H个可能状态: s1, s2, , sH. 每个状态 si 也许有一个关联概率 pi. 我们能选择 K 个行动中的一个: a1, a2, , aK. 对于每一组第 i 个行动和第 j个状态, 我们得到“回报”（或“损失”）Mij。为了让这个(简单的)理论有效, 所有的 ”回报” 必须基于同样的量纲(monetary) 测量 。我们选择一个行为来最大化回报。决策理论没有禽流感爆发有小型爆发禽流感大爆发不采取行动0-500-10000

6、0采取一些防御措施-1-100-10000所有人注射疫苗-1000-1000-1000状态行动EMBA - 管理决策与统计分析7-13非概率型概率型决策准则*概率决策标准: 考虑不确定事件的概率 并选择一个方案来最大化期望回报或最小化期望损失 最大化期望值 最小化期望机会损失决策准则在具有多个自然状态的决策问题中，决策者虽然不知道未来哪一个状态一定发生，但知道（或可估计）每个状态发生的可能性有多大，即知道（或可估计）各自然状态发生的概率。EMBA - 管理决策与统计分析7-14模型要素F = 自己生产的固定成本V = 自己生产的单位可变成本C = 产品外包的单位成本D = 需求量自己生产的总成

7、本 TMC = F + V * D外包总成本 TOC = C * D.产品外包模型EMBA - 管理决策与统计分析7-15What-If 分析：评价特定参数组合对模型产出的影响，又称敏感性分析 (sensitivity analysis).Excel工具敏感性分析 Data tables情景管理 Scenario manager求解 Goal seek 模型分析宝玉决策：宝钗？黛玉？EMBA - 管理决策与统计分析7-16计算一个或两个变量对结果的影响Excel工具一个变量敏感度分析（One-way data tables）两个变量敏感度分析（Two-way data tables）敏感度分析

8、 Data TablesEMBA - 管理决策与统计分析7-17一个变量（One Way Data Table）EMBA - 管理决策与统计分析7-18两个变量（Two Way Data Table）EMBA - 管理决策与统计分析7-19情景分析：Scenario AnalysisEMBA - 管理决策与统计分析7-20在Excel中找出产生一个已知结果的输入变量的值例子: 产品外包模型的盈亏平衡点求解：Goal SeekSet cell is B18; To value = 0; By changing cell is B14 EMBA - 管理决策与统计分析7-21最优化： Solver

9、如何合理定价最大化利润？Solution: Price = $428.57; profit = $115,714.28EMBA - 管理决策与统计分析7-22为了考虑未来的不确定性，我们可以对不确定的变量进行蒙特卡洛仿真Excel中有几个重要的随机数发生器Rand()Rand.between()数据分析模型中的不确定性 蒙特卡洛模拟/仿真EMBA - 管理决策与统计分析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 - 管理决策与统计分析7-24运用历史数据优化报童模型EMBA - 管理决策与统计分析7-25如果y = f(x) 代表一个决策模型, 然后Ey 一般不等于f(Ex)。换言之, 你不能使用一个模型中输入变量的平均值来决定期望产出。平均数的缺陷“Flaw of Averages”EMBA - 管理决策与统计分析7-26两个决策EMBA - 管理决策与统计分析7-27Forecas

11、t prediction interval两个决策EMBA - 管理决策与统计分析7-28Forecast prediction interval两个决策EMBA - 管理决策与统计分析7-29报童模型 仿真EMBA - 管理决策与统计分析7-30我们常常面临选择一些变量来最大化或最小化一个目标函数（objective function）。决策变量的最优解（optimal solution）约束条件 决策变量必须满足的条件。复杂决策模型及优化EMBA - 管理决策与统计分析7-31假设一个葡萄酒厂Walker Wines从当地的种植者那里收购葡萄制作两种葡萄酒：Shiraz和Merlot。制作

12、一瓶Shiraz需要 $1.60，而制作一瓶Merlot需要 $1.40。合同要求 Shiraz的量不低于40%但不高于70%。市场调研估计， Shiraz的最低需求量为1000瓶，但每在广告上多花 $1，销量增加5瓶； Merlot的最低需求量为2000瓶，每在广告上多花 $1，销量增加8瓶；产量不应该超过需求。 每瓶Shiraz和Merlot的零售价为 $6.25和 $5.25。Walker Wines有 $50,000去买葡萄和做广告，经营目标为利润最大化。案例：葡萄酒生产/营销配置EMBA - 管理决策与统计分析7-32确定决策变量 S=Shiraz的产量M=Merlot的产量AS=Shiraz的广告投入AM=Merlot的广告投入确定目标函数利润=(6.25S+5.25M)-(1.60S+1.40M+AS+AM)找到所有的约束条件预算约束 1.60S+1.40M+AS+AM 50,000合同要求：0.4(S+M) S 0.7(S+M)产量不能超过需求量：S 1000+5ASM 2000+8AM建立数学模型优化模型EMBA - 管理决策与统计分析7-33Excel模型EMBA - 管理决策与统计分析7-34如果Shiraz的成本为 $1.65EMBA - 管理决策与统计分析7-35敏感性分析EMBA - 管