《Excel操作》-第13章 EXCEL在管理决策中的应用

本章主要介绍：管理决策的种类和确定型决策，不确定型决策和风险型决策的Excel实现方法。本章简介本章重点重点、难点确定型决策和不确定型决策实现，最优化模型。本章难点最优化模型的Excel实现。113.3不确定型决策13.2确定型决策本章目录13.1管理决策概述13.4风险型决策13.5最优化模型213.1.2管理决策类型13.1.1管理决策含义及作用13.1管理决策概述313.1.1管理决策含义及作用决策就是为了达到一定目标，采用一定的科学方法和手段，从两个以上方案中选择一个满意方案的分析判断过程。美国著名的经济学家西蒙提出了“管理就是决策”的著名论断，在管理中，对各种问题的分析、判断、选择的过程实际上就是在进行问题的决策。制定正确的决策方案，并以正确的方法加以实施，可以提高管理水平，提高经济效益，维护社会安定，实现可持续发展，降低风险，减少损失等。13.1.2管理决策类型①按管理层次分：高层决策（战略决策）、中层决策（战术决策）、基层决策（作业决策）。②按管理过程分：计划决策、组织决策、领导决策、控制决策。③按管理领域分：财务决策、人事决策、生产决策、营销决策、物流管理决策、供应链管理决策等。④按决策方法分：定性决策、定量决策。⑤按决策的结构化分：程序化决策、非程序化决策、半程序化决策。⑥按决策环境分：确定型决策、不确定型决策、风险型决策、博弈型决策。13.2确定型决策6确定型决策也称确定情况下的决策，决策系统所处的环境是明确的，各自然状态变量的未来取值能够确定，每一个备选方案有且只有一个结果。例13-1某零售商店接到两个不同经销商送来的两种类似商品的报价清单，A商品进货成本10元，最低进货量1500个，预计销售价格18元/个，B商品进货成本12元，最低进货量1200个，预计销售价格22元/个，经过市场调查，发现A商品的潜在销售量在2000个左右，B商品的潜在销售量在1500个左右，则零售店应当选择销售哪种商品？具体的操作步骤如下：第一步：建立决策计算表，如图13-1所示。第二步：在单元格F2中输入公式“=(C2-B2)*D2”，计算A商品的预计利润，在单元格F3输入公式“=(C3-B3)*D3”，计算B商品预计利润。第三步：在单元格B4中输入公式“=IF(F2>F4,"销售A商品","销售B商品")”，进行决策。13.3.2“坏中求好”决策法13.3.1“好中求好”决策法13.3不确定型决策13.3.3“最小最大后悔值”决策法13.3.5等概率决策法13.3.4α系数决策法13.3.6用EXCEL进行不确定型决策813.3.1“好中求好”决策法“好中求好”决策法即乐观准则，假设每个方案总是出现最好的结果，那么最满意的方案是所有方案中收益最高（或损失最小）的方案。这种决策方法带有冒险性质，如果决策者在决策时盲目乐观，会对企业造成很大损失。使用该决策方法一定有一个前提：最好状态出现，恰好如愿；最差状态出现，损失不大。13.3.2“坏中求好”决策法“坏中求好”决策法即悲观准则，假设每个方案总是出现最坏的结果，那么最满意的方案就是所有方案的最坏收益中收益最好（或最坏损失中，最小损失）的方案。从表面上看，“坏中求好”决策方法是对不确定型问题的决策，但因决策者在决策问题时，将立足点置于最坏状态，即不论发生何种情况，收益值只会增加或相等，而绝不会减少。因此，实际上是把最小收益的自然状态假定为必然出现的自然状态。这种方法虽然带有保守性质，却留有余地，稳妥可靠。当企业规模小，资金薄弱，或认为在最坏状态发生的可能性较大的情况下，一般采用这种比较稳妥的决策方法。但也应该注意到，这种方法会使决策者丧失进取心，不愿意冒风险而错失企业发展的好机会。13.3.3“最小最大后悔值”决策法“最小最大后悔值”决策法即遗憾准则，通常人们在选择方案后，事实出现的情况并不是所选的方案，人们就会感到后悔，觉得应该选择另一个方案。“最小最大后悔值”就是如果选择某一方案，对应别的方案产生的后悔值为最小。所谓后悔值，就是在一定自然状态下没有取得最好的方案而带来的机会损失。此决策方法也是从坏处着眼，因此也具有悲观保守倾向，与“坏中求好”决策方法类似。但是这种方法不是从收益角度看问题，是从损失考虑，一定程度上也避免了过于保守的选择。这种决策方法一般适用于有一定基础的中小企业，这种企业有一个特征，即能承担一定程度的风险，但风险过大时又力不从心，因而是稳中求发展的一种决策方法。竞争实力相当的企业在竞争决策中也可以采用此决策方法。13.3.4α系数决策法赫威斯准则，这种准则主张折中平衡，既不乐观，也不悲观，以α系数代表乐观程度来进行综合决策。α系数的取值为0～1，越乐观，α值越接近于1；越悲观，α值越接近于0。按下列公式计算收益值，选择收益值最大的方案。收益值=α×最大收益值+(1−α)×最小收益值这是一种指数平均法，是介于最小收益和最大收益之间的评选标准，乐观系数在其中起到了折中的作用，是既稳妥又积极的一种决策方法。但α系数决策法也有不足之处：首先乐观系数不易确定。而乐观系数的取值会影响到收益值的计算，进而影响决策结果。乐观系数的不同取值代表了决策者的风险偏好，使决策有更强的主观性。其次α系数决策法虽然在理论上克服了“好中求好”和“坏中求好”的极端倾向，但只注意最好和最坏两种状态，而没有涉及其他中间状态，这样必然会影响决策结果。13.3.5等概率决策法不确定型决策对各个状态出现的概率是未知的，在分析中人为地认定各种状态出现的概率相等，在这个假设前提下计算各个方案的期望值收益，选择最大期望值收益对应的方案。等概率决策方法是将不确定型决策演变成风险型决策，将状态发生的未知概率人为地设定一个等值，该决策方法继承了α系数决策方法对有关系数的调整，又克服了α系数决策只考虑最好和最坏的状态，充分利用了所有状态所提供的信息，也是一种既稳妥又积极的决策方法。但这种决策方法掩盖了状态发生的主次，决策者不分情况使用，不可避免地会增加某些情况下的决策后悔值。例13-2一个新建的购物中心对即将到来的“五一”黄金周决策其供货水平，以便满足顾客前来购买商品的需求。由于购物中心开业不久，假日期间的确切顾客数不知道，但估计会有四种情况，第一种顾客较少，日均20000人；第二种情况日均顾客25000人；第三种情况日均顾客30000人；第四种日均顾客35000人。但这些情况出现的概率未知。假设有四种供货方案A1、A2、A3、A4。若以成本最低为目标，供应过剩或供应不足都将增大成本，具体成本数据如图13-2所示。请决策。13.3.6用Excel进行不确定型决策例13-21.“好中求好”决策法操作步骤：第一步：建立“好中求好”决策计算表，如图13-3所示。第二步：在单元格F9中输入公式“=MAX(B9:E9)”，并复制到单元格区域F10:F12，计算各方案在四种情况下的最大收益。第三步：在单元格F13中输入公式“=MAX(F9:F12)”，找出最大收益中的最大值，得出对应最优方案为A1。13.3.6用Excel进行不确定型决策例13-22.“坏中求好”决策法操作步骤：第一步：建立“坏中求好”决策计算表，如图13-4所示。第二步：在单元格F9中输入公式“=MIN(B9:E9)”，并复制到单元格区域F10:F12，计算各方案在四种情况下的最小收益。第三步：在单元格F13中输入公式“=MAX(F9:F12)”，找出最小收益中的最大值，得出对应最优方案为A3。13.3.6用Excel进行不确定型决策例13-23.“最小最大后悔值”决策法操作步骤：第一步：建立“最小最大后悔值”决策计算表，如图13-5所示。13.3.6用Excel进行不确定型决策例13-23.“最小最大后悔值”决策法操作步骤：第二步：在单元格B15中输入公式“=MAX($B$8:$B$11)-B8”，并复制到B16:B18，计算第一种情况下各方案的后悔值；在单元格C15中输入公式“=MAX($C$8:$C$11)-C8”，并复制到单元格区域C16:C18，计算第二种情况下各方案的后悔值；在单元格D15中输入公式“=MAX($D$8:$D$11)-D8”，并复制到单元格区域D16:D18，计算第三种情况下各方案的后悔值；在单元格E15中输入公式“=MAX($E$8:$E$11)-E8”，并复制到单元格区域E16:E18，计算第四种情况下各方案的后悔值。13.3.6用Excel进行不确定型决策例13-23.“最小最大后悔值”决策法操作步骤：第三步：在单元格F15中输入公式“=MAX(B15:E15)”，并复制到单元格区域F16:F18，确定各方案的最大后悔值。第四步：在单元格B19中输入公式“=MIN(F15:F18)”，找到最小最大后悔值，得出对应方案为A2。13.3.6用Excel进行不确定型决策例13-24.α系数决策法操作步骤：第一步：建立α系数决策计算表，如图13-6所示。第二步：在单元格F9中输入公式“=$C$7*MAX(B9:E9)+(1-$C$7)*MIN(B9:E9)”，并复制到单元格区域F10:F12，计算各方案的估计收益值。第三步：在单元格F13中输入公式“=MAX(F9:F12)”，找出估计收益值中的最大值，得出对应最优方案为A1。13.3.6用Excel进行不确定型决策例13-25.等概率决策法操作步骤：第一步：建立等概率决策计算表，如图13-7所示。第二步：在单元格F9中输入公式“=AVERAGE(B9:E9)”，并复制到单元格区域F10:F12，计算各方案的收益期望值。第三步：在单元格F13中输入公式“=MAX(F9:F12)”，找出收益期望值的最大值，得出对应最优方案为A2。13.3.6用Excel进行不确定型决策13.4风险型决策22对于存在两个或两个以上自然状态的决策问题，每个方案对应着多个结果，且概率分布是已知的，这类决策问题是风险型决策问题。完全信息价值是指决策者应该完全能肯定未来哪种自然状态会出现，不能肯定哪种自然状态出现是因为决策者没有获得全部信息。但要获得完全的信息，须支付一定的费用。在决定是否要支付这笔费用之前，决策者应首先估算出这些信息的价值。完全信息价值等于因获得这项信息而使决策者的期望收益增加的数值。如果完全信息价值小于或等于所支付的费用就得不偿失，因而，完全信息价值给出了支付信息费的上限。完全信息价值=完全信息条件下的期望收益−最大期望收益例13-3某一专门从事海底石油钻探的公司要对某一海底是否进行钻探决策，资料显示钻出石油的概率为0.4，钻不出石油的概率为0.6。若钻出石油，将获利100万元；若钻不出石油，将亏损50万元。现有一海底石油勘探公司建议先勘探，再决定钻与不钻。为此，石油公司可能支付给勘探公司的最高报酬是多少？分析：若公司支付费用获得完全信息，则钻出石油有获利，而钻不出石油则不会损失。例13-3具体的操作步骤如下：第一步：建立如图13-8所示的数据计算分析表。第二步：在单元格F3中输入公式“=B3*C3+D3*E3”，并复制到单元格F4，计算各方案期望收益。第三步：在单元格F5中输入公式“=MAX(F3:F4)”，确定最大期望收益；在单元格F6中输入公式“=B3*C3+D3*E4”，计算完全信息价值下的期望收益，在单元格F7中输入公式“=F6-F5”，计算完全信息价值。13.5.2EXCEL规划求解模型13.5.1最优化问题概述13.5最优化模型13.5.3营销管理决策13.5.5物流管理决策13.5.4人力资源管理决策2513.5.1最优化问题概述最优化问题是在给定条件下寻找最佳方案的问题。最佳的含义各种各样：成本最小、收益最大、利润最多、距离最短、时间最少、空间最小等，即在资源给定时寻找最好的目标或在目标确定下使用最少的资源。生产、经营和管理中几乎所有问题都可以认为是最优化问题，例如产品原材料组合问题、人员安排问题、运输问题、选址问题、资金管理问题等。最优化问题根据有无约束条件可以分为无约束条件最优化问题和有约束条件最优化问题。无约束条件最优化问题是有约束条件最优化问题的特例。实际问题一般都是在有约束条件下寻找最优方案。根据最优化问题中决策变量在目标函数与约束条件中出现的形式，可以分为线性规划问题和非线性规划问题。13.5.2Excel规划求解模型Excel中的“规划求解”是一组命令的组成部分，这些命令有时也称作假设分析工具。借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式的最优值。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格（也称为可变单元格）中的数值进行调整，最终在目标单元格公式中求得期望的结果。13.5.2Excel规划求解模型在“工具”菜单下选择“规划求解”，打开“规划求解参数”设置对话框，如图13-9所示。第一步操作步骤：13.5.2Excel规划求解模型4“设置目标单元格”是最优化求解的目标对象引用单元格区域，目标单元格是包含一些单元格引用和运算符号构成的计算公式。目标单元格可以设置取最大值、最小值或一个具体的数值。第二步第三步第四步操作步骤：“可变单元格”是与目标单元格数有关联的一组单元格，其值变化会影响到目标单元格的计算结果。单击“添加”按钮，打开一个“添加约束”对话框，如图13-10所示，添加规划求解的约束条件。例13-4某婴儿用品公司要求销售部经理制定一个新产品推介广告计划，计划费用为10万元，要求尽可能多的人能看到广告。广告的方式为电视、电台和报纸。各种广告形式下的单位费用，以及估计观看的人数如图13-11所示。同时，公司也对各广告形式的投放次数做了限制规定，其中电视广告的总次数至少2次，不超过4次；电台广告白天不超过5次，晚上不超过3次。分析：确定决策变量x

i为各广告形式下的投放次数。目标函数为：观看人数最多约束条件：总费用不超过10万元电视广告总次数限制白天电台广告次数限制晚上电台广告次数限制决策变量非负性13.5.3营销管理决策例13-4具体的操作步骤如下：第一步：设置决策变量（投放次数）存放单元格区域H2:H7；根据有关资料在目标单元格C9中输入公式“=SUMPRODUCT(G2:G7,H2:H7)”，计算观看人数；在单元格C8中输入公式“=SUMPRODUCT(C2:C7,H2:H7)”，计算广告总费用；在单元格H8中输入公式“=H2+H3”，计算电视广告投放次数。第二步：在“工具”菜单下选择“规划求解”，并在对话框中输入相关参数设置，如图13-12所示。例13-4第三步：单击“求解”的计算结果如图13-13所示。可以看到，下午电视广告播2次，半页报纸广告登10次，电台广告做足次数，广告总费用正好10万元。广告受众达57万人次。例13-5某公司下属有三个分厂，预计在未来半年中，有山东、云南、贵州和湖南四处的企业会大量订购本公司生产成品。其中山东企业是公司的重要客户，应完全满足其订单需求；云南和贵州企业是友好客户，所以营销经理认为应至少满足其订单的1/3；而湖南企业可以不作特殊考虑。由于各地运输成本上的差异，所以产品的单位利润不尽相同，有关单位利润资料、分厂生产能力资料如图13-14所示，问：应当如何安排销售计划，才能使公司利润最大？13.5.3营销管理决策例13-5分析：确定决策变量xij为第i个分厂销售给j地的产品数量，相应单位利润为aij，bi为各分厂实际产量。目标函数：利润最大约束条件：各分厂实际销售量等于产量山东客户采购量满足要求云南、贵州客户采购量满足限制要求湖南客户采购量不超过限制要求决策变量非负性操作步骤：例13-5第一步：决策变量设置于单元格区域B9:E11；在单元格B13中输入公式“=SUMPRODUCT(B3:E5,B9:E11)”，计算总利润；在单元格F9中输入公式“=SUM(B9:E9)”，并复制到单元格区域F10:F11，计算各分厂总销售量；在单元格B12中输入公式“=SUM(B9:B11)”，并复制到单元格区域C12:E12，计算各地客户实际采购量。第二步：在“工具”菜单下选择“规划求解”，并在对话框中输入相关参数设置，如图13-15所示。例13-5第三步：单击“求解”的计算结果如图13-16所示。可以看出，一分厂向山东销售2500件产品，向湖南销售6000件产品；二分厂向山东销售5500件产品；三分厂向云南销售4000件产品，向贵州销售3000件产品。公司利润最大为107.2万元。例13-6新开通地铁需要增加值班服务人数，一个班连续工作6个小时，不同排班，基本工资不同。各时段服务人数需求资料如图13-17所示。如何确定各班次人数，使得既能满足各时段服务人数需求，又使得人员总费用最低？13.5.4人力资源管理决策例13-6分析：确定决策变量xi为第i班排班人数。目标函数：人员总费用最少操作步骤：第一步：决策变量设置于单元格区域B13:G13。在单元格J3中输入公式“=SUMPRODUCT(B3:G3,$B$13:$G$13)”，并复制到单元格区域J4:J11，得各时段实际服务人数。在单元格B14中输入公式“=SUMPRODUCT(B12:G12,B13:G13)”，计算总费用。例13-6第二步：在“工具”菜单下选择“规划求解”，并在对话框中输入相关参数设置，如图13-18所示。第三步：单击“求解”，计算结果如图13-19所示。例13-7A公司有三个工程项目要启动，拟由三名项目工程师分别承担，每个项目工程师完成各项目的时间（单位：天）如图13-20所示。如何安排项目负责人，可以使项目尽快完成（项目总用时最少）？分析：确定0-1型决策变量xij为第i个工程师是否负责第j个项目。xij=1表示第i个工程师负责第j个项目，xij=0表示第i个工程师不负责第j个项目。各工程师完成各项目时间为cij。目标函数：总用时最少约束条件：一个工程师负责一个项目，一个项目由一个工程师负责13.5.4人力资源管理决策例13-7操作步骤：第一步：决策变量设置于单元格区域B7:D9。在单元格E7中输入公式“=SUM(B7:D7)”，并复制到单元格区域E8:E9，得每个工程师负责项目数。在单元格B10中输入公式“=SUM(B7:B9)”，并复制到单元格区域C10:D10，得负责每个项目的工程师人数。在单元格B11中输入公式“=SUMPRODUCT(B3:D5,B7:D9)”，计算总用时。第二步：在“工具”菜单下选择“规划求解”，并在对话框中输入相关参数设置，如图13-21所示。第三步：单击“求解”，计算结果如图13-22所示。例13-8某公司有一批长度为5m的钢管（数量充分），为满足制造零件的需要，将其截成长度为1400cm、950cm、650cm的短料，且三种管料要按2:4:1的比例配套生产。长管截成短管，一般要产生残料，图13-23列出了8种可能的截法，怎样截才能使截下来的短料既能配套，又能使残料最少？分析：确定决策变量xi为采用第i种截法的钢管数目，a为套数