1、第11章 经典实例：销售决策分析, 11.1 要点分析 11.2 通过建立决策模型进行营销决策 11.2.1 产品定价决策问题描述与解决的基本思路 11.2.2 构建销售利润最大化的产品定价决策模型 11.2.3 产品定价决策模型中有关变量的图表分析 11.3 数据表在净利润敏感度分析中的应用 11.3.1 产品销售净利润值敏感度分析, 11.3.2 价格调整引起的利润变化 11.3.3 价格与数量同时变动引起的利润变化 11.4 单变量求解在销售利润目标确定中的应用 11.4.1 确定目标利润对应的销售收入 11.4.2 确定新产品的保本销售量 11.5 使用“规划求解”工具分析营销决策,
2、11.5.1 规划问题的特点以及“规划求解”工具的组成 11.5.2 利用“规划求解”工具解决规划求解问题的流程 11.5.3 利用“规划求解”工具确定商品运输方案 11.5.4 利用“规划求解”工具分析设置快捷酒店网点布局, 11.2 通过建立决策模型进行营销决策,营销决策是指对有关产品市场经营和销售活动的目标、方针、策略等重大问题进行选择和决断的过程。营销决策是企业市场营销中的核心问题，它必须建立在充分的市场调查和市场预测的基础之上。, 11.2.1 产品定价决策问题描述与解决的基本思路,企业市场营销中的一个重要决策问题就是产品定价。而销售利润则是企业管理追求的一个重要目标，但是销售利润的
3、大小与产品的定价具有重要关联关系。 如果产品价格定得过高，那么单位产品的销售利润相应的也会增加，但是总的销售量可能会减少，从而会影响总的产品销售利润；反之产品价格定的过低，虽然销售数量可能会大幅的增长，但是因为单位产品的销售利润不在，也会影响到总的产品销售利润。 下面是一个产品定价决策的问题描述，并给出相应的问题解决的基本思路。 如表11-1所示，为某公司根据市场分析，对其生产的某产品在不同价格水平下的预测销售量。假设某公司生产该产品的全年固定成本为每年150万无，全年的生产能为55万 套，每套产品的变动成为20元。请问：某公司应该如何对该产品定价？, 11.1 要点分析,问题分析：这里的产品
4、定价，应该就是以销售利润最大化为目标，因此需要构造销售利润的计算公式。然后根据相关变量之间的关系，这里的销售利润计算的公式如下。 L=S-C=PQ-(FVQ)(P-V)QF 其中： L为全年销售利润。 S为全年销售额。 C为全年销售成本。 P为销售价格。 F为全年固定成本。 V为单位变动成本。 Q为预测的销售量。 通过上面公式，就可以计算出来不同价格水平下，对应不同预测销售量时的销售利润；然后可以根据销售利润最大化的原则，即可将其对应价格作为最佳的决策价格。, 11.2.2 构建销售利润最大化的产品定价决策模型,下面就可以得用Excel建立模型，来进行以上的销售利润辅助计算，操作步骤如下。 步
5、骤1 根据已知数据以及销售利润计算公式，制作如图11-1所示的表格。 步骤2 选取C9单元格，输入公式“C4”，获取第一个价格水平，然后向右拖动填充柄，一直复制公式到I9单元格获取其他的价格水平，如图11-2所示。,表11-1 不同价格水平下的预测销售量,步骤3 选取C10单元格，输入公式“C5”，获取第一个预测销售量，然后向右拖动填充柄，一直复制公式到I10单元格，获取其他预测销售量，如图11-3所示。 步骤4 选取C11单元格，输入公式“=C9*C10”，获取第一个不同销售量销售收入，然后向右拖动填充柄，一直复制公式到I11单元格，获取其他不同销售量销售收入，如图11-4所示。,图11-1
6、 销售利润最大化的产品定价所用表格,图11-2 获取其他价格水平,图11-3 计算出其他预测销售量,图11-4 计算出不同销售量销售收入,步骤5 选取C12单元格，输入公式“=C5*$I$6”，获取第一个总变动成本，然后向右拖动填充柄，一直复制公式到I12单元格，获取其他总变动成本，如图11-5所示。 步骤6 选取C13单元格，输入公式“=$D$6”，计算出固定成本，如图11-6所示。,图11-5 计算出其他总变动成本,图11-6 计算出固定成本,步骤7 选取C14单元格，输入公式“=C12$C$13”，获取第一个总成本，然后向右拖动填充柄，一直复制公式到I14单元格，获取其他的总成本，如图1
7、1-7所示。 步骤8 选取C15单元格，输入公式“=C11-C14”，计算出第一个价格水平下的销售利润然后向右拖动填充柄，一直复制公式到I15单元格，计算出其他价格水平下的销售利润。通过以上的操作，计算出各种不同价格水平下的销售利润，效果娟秀图11-8所示。 步骤9 选取C17单元格，输入公式“=“本产品售价应定为“&INDEX(C10:I10,MATCH (MAX(C15:I15),C15:I15,0)&“元,此时对应销售量为“&INDEX(C4:I4,MATCH(MAX(C15:I15), C15:I15,0)&“万套，预测销售利润为“&MAX(C15:I15)&“万元，得出销售利润最大化
8、的产品定价决策模型的分析结果，如图11-9所示。,图11-7 计算出其他的总成本,图11-8 计算出不同价格水平下的销售利润,图11-9 销售利润最大化的产品定价决策模型的分析结果, 11.2.3 产品定价决策模型中有关变量的图表分析,下面就来根据产品定价决策模型制作数据图表，操作步骤如下。 步骤1 选取B14:I14单元格区域，按下Ctrl键，依次选取B12:I12单元格区域、B14:I14单元格区域和B15:I15单元格区域。,步骤2 在“插入”选项卡下的“图表”组中单击“折线图”按钮，在展开的下拉列表中单击“带数据标记的折线图”图标，如图11-10所示。 步骤3 如图11-11所示，经过
9、上述操作，制作出的带数据标记的折线图的初始效果。,图11-11 带数据标记的折线图的初始效果,步骤4 适当调整图表放置位置，将其移动到适合位置。 步骤5 右击图表水平坐标轴，从快捷菜单中选择“删除”命令，如图11-12所示，删除纵坐标轴。 步骤6 右击图例，从快捷菜单中选择“设置图例格式”命令，如图11-13所示。 步骤7 弹出“设置图例格式”对话框，在“图例选项”选项卡的右侧窗格中单击图例位置“底部”单选按钮，在“边框颜色”选项卡的右侧窗格中单击“实线”单选按钮，并选择颜色“黑色”，如图11-14所示。 步骤8 在图表中，右击“价格水平（元）”折线条，从快捷菜单中选择“添加数据标签”命令，如
10、图11-15所示，为数据添加标签。,图11-10 单击“带数据标记的折线图”图标,图11-13 选择“设置图例格式”命令,图11-12 选择“删除”命令,图11-14 “设置图例格式”对话框,图11-15 选择“添加数据标签”命令,步骤9 右击添加的数据标签，从快捷菜单中选择“设置数据系列格式”命令，如图11-16所示。 步骤10 弹出“设置数据系列格式”对话框，设置“系列选项”系列绘制在“次坐标轴”，如图11-17所示，从面建立双轴图表。,图11-17 设置数据系列格式,图11-16 选择“设置数据系列格式”命令,步骤11 右击图表的绘图区，从快捷菜单中选择“设置绘图区格式”命令，如图11-
11、18所示。 步骤12 弹出“设置绘图区格式”对话框，设置填充色为“纯色填充”，填充颜色 为“浅橙色”，如图11-19所示。,图11-19 设置绘图区格式,图11-18 选择“设置绘图区格式”命令,步骤13 添加图表标题“销售利润最大情况下产品定价决策的图表分析”，并进行一定的文字格式效果设置。,步骤14 适当调整图表的大小，然后设置A1:S1,A18:S18,A2:A18,S2:S18单元格区域的填充色、调整宽度，做出模型周围的边框效果，构造出基于Excel的销售利润最大化的产品定价决策模型，如图11-20所示。,图11-20 销售利润最大化的产品定价决策模型最终制作效果, 11.3 数据表在
12、净利润敏感度分析中的应用,敏感度分析又称“What-if分析”，其用途是测定当一个参数发生变化时，由该参数所带的中间变量是如何变化，以及由中间变量引起的最终结果会发生什么样的变化。, 11.3.1 产品销售净利润值敏感度分析,在Sheet2工作表中新建如图11-21所示的表格，左边部分给出了某公司本月产品的简易损益表，而右边部分给出了从基本数据输入开始，到最终的净利润输出为止，中间的数据计算规则。,从图中可以看出，本月该产品的净利润约为840万元。现在假设一下，下个月想实现净利润达到870万元，请在只能改变销售价格和销售数量的情况下，对净利润值的敏感度进行分析。通过改变销售价格和销售数量，可以
13、进行利润值敏感度的分析，引起利润值的变化，更加方便的选择达到即定值的数据。下面在Excel 中利用“模拟运算表”工具分析解决该问题。,图11-21 某公司某产品本月的简易损益表, 11.3.2 价格调整引起的利润变化,下面就以在上述的产品损益表中，根据销售价格的变化，来模拟净利润的变化为 例，说明利用“模拟运算表”工具进行单因素敏感度分析的操作。具体操作步骤如下。 步骤1 将Sheet3工作表重命名为“单因素敏感度分析”，然后将某公司某产品本月的简易损益表复制到该工作表中，并在其右侧建立单变量数据表的初始结构，如图11-22所示。,步骤2 在D5单元格和D6单元格分别输入75和76，然后选取D
14、5:D6单元格区域，向下拖动填充柄一直到D20单元格，在E4单元格中输入“B21”，结果如图11-23所示。,图11-23 利用公式和复制功能填入数据,图11-22 单变量数据表的初始结构,步骤3 给要进行数据敏感度分析的数据表范围设置填充色。首先选取D4:E20单元格区域，在“数据”选项卡下的“数据工具”组中单击“模拟分析”按钮，从弹出的菜单中选择“模拟运算表”命令，如图11-24所示。 步骤4 本例是在列上模拟销售价格的变化，销售价格的数据在B5单元格中存储，所以在弹出的“模拟运算表”对话框中在“输入引用列的单元格”文本框中输入“$B$5”，如图11-25所示。 步骤5 单击“确定”按钮，
15、单变量数据表制作完成，效果如图11-26所示。在每一个价格数字的后面，已经计算出在其他变量都保持不变的情况下，对应净利润的金额。从图中可以看出，在保持其他变量都不变的情况下，要想实现净利润达到870万，只要将价格调整到81元，也就是上涨1元即可。此时对应的净利润为8761406元。,图11-24 选择“模拟运算表”命令,图11-25 “模拟运算表”对话框,图11-26单因素利润敏感度分析的结果, 11.3.3 价格与数量同时变动引起的利润变化,进行单变量敏感度分析，只是分析改变一个输入变量对公式计算结果的影响。如果想要分析两个变量对公式计算的影响，就需要进行双变量敏感度分析，这可以通过使用双变
16、量数据来实现。,图11-27 创建双变量数据表的框架,例如，前面通过改变销售价格一个参数，分析单变量的利润敏感度，其结果不一定完全准确。因为当价格变化时也可能会引起销售数量也发生一定的变化，所以有必要一起去考虑。下面同时考虑价格和数量两个因素，进行双变量利润敏感度分析，具体操作步骤如下。 步骤1 插入一个工作表，并重命为“双因素敏感度分析”。 步骤2 将某公司某产品本月的简易损益表复制到“双因素敏感度分析”工作表中，并在其右侧建立双变量数据表的初始结构，接着在F2:L2单元格区域中输入销售数量，并在E2单元格输入“B21”，结果如图11-27所示。,步骤3 选取E2:L18单元格区域，在“数据
17、”选项卡下的“数据工具”组中单击“模拟分析”按钮，在展开的下拉列表中选择“模拟运算表”命令，如图11-28所示。,图11-28 选择“模拟运算表”命令,步骤4 弹出“模拟运算表”对话框，在“输入引用行的单元格”文本框中输 入“$B$6”，在“输入引用列的单元格”文本框中输入“$B$5”，如图11-29所示。 步骤5 单击“确定”按钮，结果如图11-30所示。,图11-29 “模拟运算表”对话框,图11-30 按照价格和数量得到的双变量数据表结果,图11-31 单击“介于”按钮,步骤6 为了将结果为870万元的数字用特殊效果显示，可以为模拟结果设置条件格式，选取E3:L18单元格区域，在“开始”
18、选项下的“样式”组中单击“条件格式”按钮，在展开的列表中选择“突出显示单元格规则”选项，然后单击“介于”按钮，如图11-31所示。 步骤7 弹出“介于”对话框，设置条件格式，如图11-32所示，最后单击“确定”按钮。,图11-32 “介于”对话框,步骤8 返回工作表，在图11-33中可以看到，净利润大于870万元的“价格/数量”组合，都分布到整个区域对角线的右下部分。根据价格与数量之间的关系，其中只有一部分的组合具有较大的可能性。例如F15、H9、K3等单元格，都是已经达到了870万元的净利润，并且也是比较可行的几种方案。以F15单元格为例，意思就是“价格如果涨到87元，销售只要达到40万个，
19、就可以实现净利润870万元”。而H9单元格则意味着“价格如果保持81元不动，销售量只要达到50万个，也可以实现净利润达到870万元”。,图11-33 对双变量敏感度分析的结果进行条件格式化设置, 11.4 单变量求解在销售利润目标确定中的应用,在“营销决策.xlsx”工作簿中新插入一张工作表，在工作表中制作某公司编制的本月损益简表，如图11-35所示。在右边部分是各变量之间的相互关系及计算方法，左边部分“本月数字”一列中带有填充色的单元格是利用D列中的计算公式进行计算得出的结果。 问题分析：从如图11-34可以看出，本月净利润为78万元左右。假如公司确定下个月净利润要达到80万元，其他原始数据
20、和计算模型中各个参数都保持不变。那么，销售收入应该增长到多少，才能实现净利润达到80万元的目标呢？,图11-34 某公司编制的本月损益简表,下面将介绍如何运用“单变量求解”工具来解决上述问题。, 11.4.1 确定目标利润对应的销售收入,下面采用单变量求解的方法确定目标利润对应的销售收入，具体操作步骤如下。 步骤1 选定要确定目标利润对应的销售收入的单元格，也就是净利润数据所在的B16单元格。 步骤2 在“数据”选项卡下的“数据工具”组中单击“模拟分析”按钮，在展开的下拉列表中选择“单变量求解”命令，如图11-35所示。 步骤3 弹出“单变量求解”对话框，在“目标值”文本框中输入“800000
21、”， 在“可变单元格”文本框中，输入这里需要确定的可变量销售收入所在的“$B$2”单元格，如图11-36所示。 步骤4 单击“确定”按钮，弹出如图11-37所示的“单变量求解状态”对话框，说明已经找到解，并且与目标值完全一致。,图11-35 选择“单变量求解”命令,图11-36 “单变量求解”对话框,步骤5 单击“确定”按钮，得到单变量求解的结果，如图11-38所示，要实现净利润增长到800000.00元，销售收入需要增加到11217548.57元。,图11-37 “单变量求解状态”对话框,图11-38 单变量求解的结果, 11.4.2 确定新产品的保本销售量,新插入一张工作表，然后在工作表中
22、制作“新开发产品的保本点计算”表格，如图11-39所示。其中，B4:C14单元格区域中是某公司为一种新产品进行销售利润测算，按照目前设定的数据，计算出来的销售利润为负数。下面利用“单变量求解”工具确定新产品保本销售。 假设现在其他数据都不能改变，而唯一能够做出改进的是设法增大产品的销售数量。请问：销售数量必须增长到多少时，才能实现本产品的销售能够“保本”？下面就来介绍如何利用单变量求解解决这个问题，具体操作步骤如下。,图11-39 “新开发产品的保本点计算”表格,步骤1 将B4:C14单元格内容复制到B17:C27单元格区域。 步骤2 选定C27单元格，也就是销售利润所在的单元格，在“数据”选
23、项卡下 的“数据工具”组中单击“模拟分析”按钮，在展开的下拉列表中选择“单变量求解”命令，如图11-40所示。 步骤3 弹出“单变量求解”对话框，在“目标值”文本框中输入数字“0”， 在“可变单元格”文本框中，输入这里需要确定的可变量销售收入所在的“$C$19”单元格，如图11-41所示。,图11-40 选择“单变量求解”命令,图11-41 “单变量求解”对话框,步骤4 单击“确定”按钮，弹出如图11-42所示的“单变量求解状态”对话框，说明已经找到一个解，并且与目标值是完全一样的。 步骤5 单击“确定”按钮，得到单变量求解的结果，如图11-43所示，可以看 出“保本点”销售为90000，也就
24、是说如果其他各量都不变，必须销售90000个以上的本产品，才能实现赢利。,图11-42 “单变量求解状态”对话框,图11-43 利用单变量求解确定保本销售量, 11.5 使用“规划求解”工具分析营销决策,在实际的销售管理中，要完成一项目标任务的时候，就会经常会存在着多个因素，这些因素都具有一定的约束条件。下面将介绍如何在约束条件下进行最优确定。, 11.5.1 规划问题的特点以及“规划求解”工具的组成,对于规划求解问题的解决，在Excel中专门提供了“规划求解”加载宏工具。 1规划问题的特点 在计划管理当中，往往会遇到各种各样的规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生
25、产材料的搭配、采购批次的确定等。这类问题有一个共同的要求，那就是：如果合理的利用各种约束资源从而达到最佳的经济效益，也就是达到产量最高、利润最大、成本最低、费用最省等目标。 一般来说，这类规划问题都具有如下三个特点。 （1）所求的问题都有一个单一的目标，如求生产的最低成本、求运输的最佳的路线、求产品的最大盈利、求产品周期的最短时间以及求其他目标函数的最优值等。 （2）总是有明确的不等式约束的条件。比如库存不能低于一定的数量，否则就会造成原料短缺或产品缺货的情况；生产的产品不能超过一定的额度，否则会造成商品积压等。 （3）问题都有直接或间接影响约束条件的一组输入值。 2Excel“规划求解”工具
26、的组成 规划求解就是在满足约束条件的前提下，调整决策变量，实现目标函数最优。因此，,可以说规划求解工具包括决策变量、目标函数和约束条件三部分。 （1）决策变量：是指在实际的问题当有有一些待解决的未知因素，一个规划问题中可能会有一个决策变量，也有可能会有多个决策变量。一组决策变量代表一个规划求解的方案。在Excel的规划求解模型中，决策变量通常利用可变单元格进行表示。 （2）目标函数：用来表示规划求解要达到的最终目标，如求最短路径、最大利润、最小成本、最佳产品组合等。在规划模型中，目标函数应该是决策变量的函数。也就是说，在Excel中目标函数与可变单元格有着直接或间接的联系，它可以进线性函数（对
27、应规划问题称为“线性规划”），也可以是非线性函数（对应规划问题称为“非线性规划”）。 （3）约束条件：是指实现目标的限制条件，规划求解是否有解，与约束条件有着密切的关系，它对可变单元格中的值起着直接限制的作用。约束的条件可以是等式，也可以不是等式。, 11.5.2 利用“规划求解”工具解决规划求解问题的流程,某电脑销售公司主要销售“戴尔”和“华硕”两种笔记本。因担心进货积压，公司规定“戴尔”笔记本一次性最多进货200台，“华硕”笔记本一次性最多进货200台。而受公司与资金的限制，两种笔记本的单次进货量不得超过300台。其中“戴尔”笔记本的平均利润为400/台，若“戴尔”笔记本每多进10台，单台
28、“戴尔”笔记本的利润会增长5元。“华硕”笔记本的平均利润为350元/台，如果“华硕”每台笔记本多进10台，单台“华硕”笔记本的利润会上涨8元。那么该公司如何确定两种笔记本的单次进货量，才能使效益最大化呢？ 下面介绍如何使用“规划求解”工具来解决问题，具体操作步骤如下。 步骤1 在“开发工具”选项卡下的“加载项”组中单击“加载项”按钮，如图11-45所示。,步骤2 弹出“加载宏”对话框，在“可用加载宏”列表中选中“规划求解加载项”复选框，最后单击“确定”按钮，如图11-46所示。,步骤3 这时在“数据”选项卡下的“分析”组中会增加一个“规划求解”命令，说明加载成功，然后在Sheet5工作表中建立
29、如图11-47所示的规划模型 步骤4 在B4单元格中输入公式“SUM(B2B3)”，按Enter键计算总进货量，接着在B6单元格中输入公式“=(400B2/10*5)*B2(350B3/100*8)*B3”，按Enter键计算总利润，如图11-48所示。 步骤5 单击“数据”选项，在“分析”组中选择“规划求解”命令，如图11-49所 示。 步骤6 弹出“规划求解参数”对话框，在“设置目标”文本框中输入“$B$6”，然后在“通过更改可变单元格”文本框中输入“$B$2:$B$3”，接着选中“最大值”单选按钮，如图11-50所示，最后单击“添加”按钮。,图11-45 单击“加载项”按钮,图11-46
30、 “加载宏”对话框,步骤7 弹出“添加约束”对话框，在“单元格引用位置”文本框中输入“$B$2”，选择“=”约束条件，在“约束值”文本框中输入“200”，如图11-51所示，再单击“添加”按钮。,图11-47 规划模型框架,图11-48 输入相关公式,图11-49 选择“规划求解”命令,图11-50 “规划求解参数”对话框,步骤8 在“添加约束”对话框中为B3单元格添加约束条件，如图11-52所示，再单击“添加”按钮。,图11-51 为B2单元格添加约束条件,图11-53 为B4单元格添加约束条件,图11-54 参数设置后的结果,图11-52 为B3单元格添加约束条件,步骤9 在“添加约束”对
31、话框中继续为B4单元格添加约束条件，如图11-53所示，再单击“添加”按钮。 步骤10返回“规划求解参数”对话框，单击“求解”按钮，如图11-54所示。,步骤11弹出“规划求解结果”对话框，选中“保存求解结果”单选按钮，单击“确定”按钮，如图11-55所示。 步骤12此时，在工作表中即可看到求解结果，如图11-56所示。当“戴尔”笔记本单笔进货200台，“华硕”笔记本单笔进货100台时，获得的利润最大。,图11-56 规划求解的计算结果, 11.5.3 利用“规划求解”工具确定商品运输方案,某公司需将存储在三个物流中心L1、L2、L3的同一种货物，分别运输到C1、C2、C3三个城市。L1、L2
32、、L3的发货数量分别为300吨、200吨、300吨，C1、C2、C3的货物需求量分别为270吨、230吨、300吨。每个物流中心到各个城市的价格如表11-2所示。下面要通过计算来设计运输方案，才能使公司成本最低。,图11-55 “规划求解结果”对话框,图表11-2 每个物流中心到各个城市的价格,因为各个物流中心到各个城市的运费是不同的，而各个物流中心的可发货数量，以及各个城市的可收货数量也都是有限制的，要想确定运输成本的最小值，就必须要求设计的方案同时能满足这些条件。 步骤1 按照如图11-58所示的样式建立规划求解的模型。其中，B2:B4单元格区域为不同物流中心向各个城市的发货的数量，作为将
33、来规划求解中的可变单元格；E2:E4单元格区域是各物流中心的实际发货数量，各单元格使用的公式分别为“SUM(B2:D2)”、“SUM(B3:D3)”、“SUM(B4:D4)”；B5:D5单元格区域是各物流中心的实际发货数量，各单元格使用的公式分别为“SUM(B2:B4)”、“SUM(C2:C4)”、“SUM(D2:D4)”；E2:G4单元格区域以及B5:D7单元格区域是本问题的六个约束控制条件；D11:D13单元格区域是根据题目中的条件输入的各物流中心到各城市的运输成本价格；F5单元格是用来计算运输成本，属于规划求解中的目标单元格，公式为“=SUMPRODUCT(B2:D4,B11:D13)”
34、。 步骤2 在“数据”选项卡下的“分析”组中单击“规划求解”按钮，弹出“规划求解参数”对话框，在“设置目标”文本框中输入“$F$5”，然后在“通过更改可变单元格”文本框中输入“$B$2:$D$4”，接着选中“最小值”单选按钮，最后单击“添加”按钮，如图11-59所示。 步骤3 弹出“添加约束”对话框，在“单元格引用位置”文本框中输入“$B$2:$D$4”，选择“=”约束条件，在“约束值”文本框中输入“0”，再单击“添加”按钮，添加第一个约束条件，如图11-60所示。 步骤4 在“单元格引用位置”文本框中输入“$B$5:$D$5”，选择“=”约束条件，在“约束值”文本框中输入“$B$7:$D$7
35、”，再单击“添加”按钮，添加第二个约束条件，如图11-61所示。,图11-58 运输成本最小问题的规划求解模型,图11-59 “规划求解参数”对话框,图11-60 设置第一个约束条件,图11-61 设置第二个约束条件,步骤5 在“单元格引用位置”文本框中输入“$E$2:$E$4”，选择“=”约束条件，在“约束值”文本框中输入“$G$2:$G$4”，如图11-62所示。 步骤6 单击“确定”按钮，返回“规划求解参数”对话框，单击“求解”按钮，如图11-63所示。 步骤7 弹出“规划求解结果”对话框，选中“保留规划求解的解”单选按钮，单 击“确定”按钮，如图11-64所示。 步骤8 此时，在工作表
36、中即可看到求解结果，如图11-65所示，可知最佳运输方案为:物流中心的L1的300吨货物全部运输到C3城市；物流中心的L2的200吨货物全部运输到,C1城市；物流中心的L3的300吨货物运输到C1城市70吨和C2城市230吨。这样各个城市的需求量都得到了满足，各个物流中心也没有超过发货数量，总运输成本为57150元。,图11-62 设置最后一个约束条件,图11-63 参数设置后的结果,图11-64 “规划求解结果”对话框,图11-65 规划求解的计算结果,表11-3 各区域中每一个网点的初始投资额及其年利润的预测值, 11.5.4 利用“规划求解”工具分析设置快捷酒店网点布局,M快捷酒店计划进
37、入T市进行网点布局，根据该市的整体状况，该公司营销将其分成了核心主城区、经济开发区、周边郊县区和高新技术区四个区域，并从这四个不同区域中拟订了12个网点设置Li(i=1，2，3，12)。根据市场调查，这12个网点位置的初始投资额和每年的利润预测值，如表11-3所示（单位:万元）。,目前，公司总部提出了如下的网点布局指导原则。 在该市核心主城区，至少要从L1、L2、L3、L4中选择开发两个网点。 在该市经济开发区，至少要从L5、L6中选择开发一个网点。 在该市周边郊县区，至多要从L7、L8、L9中选择开发两个网点。 在该市高新技术区，至多要从L10、L11、L12中选择开发两个网点。 请问:在该
38、市的总投资额度不能超过1000万元的前提下，该饭店的营销部应该选择哪几个地点来开发和建设快捷连锁酒店，才能使总的年利润最大？ 快捷连锁酒店经营地点的设置问题，是一种连锁企业经常遇到的网点布局问题，类似的还有超市的网点布局、银行储蓄所的网点布局等。对于每一个网点，都存在开发与不开发两种情况，所以这个问题一般用01整数规划问题来解决。,根据题目中的要求，要先建立网点布局问题的规划求解模型。设Xi为01的变量（其中i1，2，3，12），也说是说Xi=0或者是Xi=1。其中结果为0表示Li网点没有被选中，而结果为1则表示Li网点被选中。本问题的目标就是使总利润最大，因此，目标函数为：MAXz=32X1
39、45X230X328X464X576X696X725X842X932X1078X1150X12。 约束条件有以下几条。 （1）全部投资总预算不大于1000万，也就是说：100X1180X2116X3100X4210X5194X6242X788X8109X981X10178X11124X121000。 （2）在核心主城区，至少选择两个，则有X1X2X3X42。 （3）在经济开发区，至少选择一个，则有X5X61。 （4）在周边郊县区，至多选择两个，则有X7X8X92。 （5）在高新技术区，至多选择两个，则有X10X11X122。 步骤1 根据上述分析，建立规划求解问题的模型表格，并进行相关项目的格
40、式设置和公式输入，如图11-66所示。其中：C4:N6单元格区域是已经知道数据所在的单元格区域；C10:N10单元格区域是决策变量（可变参数）所在的单元格区域；G13:K17单元格区域是约束条件所在的单元格区域；M15单元格（合并了M15:N17单元格区域后的单元格）为目标函数所在的单元格；选取G13单元格，输入公式“=SUMPRODUCT(C5:N5,C10:N10)”；选取G14单元格，输入公式“=SUM(C10:F10)”；在G15单元格中输入公式“=SUM (G10:H10)”；在G16单元格中输入公式“=SUM(I10:K10)”；在G17单元格中输入公式“=SUM(L10:N10)
41、”；在M15单元格中输入公式“=SUMPRODUCT(C10:N10,C6:N6)”。 步骤2 在“数据”选项卡下的“分析”组中单击“规划求解”按钮，弹出“规划求解参数”对话框，在“设置目标”文本框中输入“$M$15”，然后在“通过更改可变单元格”文本框中输入“$C$10:$N$10”，接着选中“最大值”单选按钮，最后单击“添加按钮”，如图11-67所示。,图11-66 快捷酒店网点布局决策分析的规划求解模型,图11-67 “规划求解参数”对话框,图11-68 添加第一个约束条件,图11-69 添加第二个约束条件,步骤3 弹出“添加约束”对话框，在“单元格引用位置”文本框中输 入“$C$10:
42、$N$10”，选择“bin”约束条件，在“约束值”文本框中输入“二进制”。然后单击“添加”按钮，如图11-68所示 步骤4 在“单元格引用位置”文本框中输入“$C$10:$N$10”，选择“=”约束条件，在“约束值”文本框中输入“0”。然后单击“添加”按钮，如图11-69所示。,步骤5 在“单元格引用位置”文本框中输入“$G$13”，选择“=”约束条件， 在“约束值”文本框中输入“$K$13”。然后单击“添加”按钮，如图11-70所示。,图11-70 添加第三个约束条件,图11-71 添加第四个约束条件,图11-72 添加最后一个约束条件,图11-73 设置好参数的“规划求解”对话框,步骤7
43、在“单元格引用位置”文本框中输入“$G$16:$G$17”，选择“=”约束条件，在“约束值”文本框中输入“$K$16:$K$17”，如图11-72所示。 步骤8 单击“确定”按钮返回“规划求解”对话框，单击“求解”按钮，如图11-73所示。,步骤6 在“单元格引用位置”文本框中输入“$G$14:$G$15”，选择“=”约束条件，在“约束值”文本框中输入“$K$14:$K$15”。然后单击“添加”按钮，如图11-71所示。,图11-74 “规划求解结果”对话框,图11-75 规划求解的计算结果,步骤9 弹出“规划求解结果”对话框，选中“保存规划求解结果”单选按钮，单击“确定”按钮，如图11-74所示。 步骤10 此时，工作表中即可看到求解结果，如图11-75所示，最佳网点布局方案为:在核心主城区的L1、L2、L3和L4进行网点开发；在经济开发区的L5和L6进行网点开发；在高新技术区的L11进行网点开发。,
