EXCEL 在会计和财务管理中运用 资料.ppt

表11-3 各区域中每一个网点的初始投资额及其年利润的预测值 ● 11.5.4 利用“规划求解”工具分析设置快捷酒店网点布局 M快捷酒店计划进入T市进行网点布局，根据该市的整体状况，该公司营销将其分成了核心主城区、经济开发区、周边郊县区和高新技术区四个区域，并从这四个不同区域中拟订了12个网点设置Li(i=1，2，3……，12)。根据市场调查，这12个网点位置的初始投资额和每年的利润预测值，如表11-3所示（单位:万元）。 核心主城区 经济开发区 周边郊县区 高新技术区 L1 L2 L3 L4 L5 L6 L7 L8 L9 L10 L11 L12 所需投资额 100 180 116 100 210 194 242 88 109 81 178 124 预测年利润 32 45 30 28 64 70 96 25 42 32 78 50 科目 区域 目前，公司总部提出了如下的网点布局指导原则。 在该市核心主城区，至少要从L1、L2、L3、L4中选择开发两个网点。 在该市经济开发区，至少要从L5、L6中选择开发一个网点。 在该市周边郊县区，至多要从L7、L8、L9中选择开发两个网点。 在该市高新技术区，至多要从L10、L11、L12中选择开发两个网点。 请问:在该市的总投资额度不能超过1000万元的前提下，该饭店的营销部应该选择哪几个地点来开发和建设快捷连锁酒店，才能使总的年利润最大？ 快捷连锁酒店经营地点的设置问题，是一种连锁企业经常遇到的网点布局问题，类似的还有超市的网点布局、银行储蓄所的网点布局等。对于每一个网点，都存在开发与不开发两种情况，所以这个问题一般用0～1整数规划问题来解决。 根据题目中的要求，要先建立网点布局问题的规划求解模型。设Xi为0～1的变量（其中i＝1，2，3…，12），也说是说Xi=0或者是Xi=1。其中结果为0表示Li网点没有被选中，而结果为1则表示Li网点被选中。本问题的目标就是使总利润最大，因此，目标函数为：MAXz=32X1＋45X2＋30X3＋28X4＋64X5＋76X6＋96X7＋25X8＋42X9＋32X10＋78X11＋50X12。 约束条件有以下几条。 （1）全部投资总预算不大于1000万，也就是说：100X1＋180X2＋116X3＋100X4＋210X5＋194X6＋242X7＋88X8＋109X9＋81X10＋178X11＋124X12≤1000。 （2）在核心主城区，至少选择两个，则有X1＋X2＋X3＋X4≥2。 （3）在经济开发区，至少选择一个，则有X5＋X6≥1。 （4）在周边郊县区，至多选择两个，则有X7＋X8＋X9≤2。 （5）在高新技术区，至多选择两个，则有X10＋X11＋X12≤2。 步骤1　根据上述分析，建立规划求解问题的模型表格，并进行相关项目的格式设置和公式输入，如图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)”；在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:$N$10”，选择“bin”约束条件，在“约束值”文本框中输入“二进制”。然后单击“添加”按钮，如图11-68所示 步骤4　在“单元格引用位置”文本框中输入“$C$10:$N$10”，选择“=”约束条件，在“约束值”文本框中输入“0”。然后单击“添加”按钮，如图11-69所示。 步骤5　在“单元格引用位置”文本框中输入“$G$13”，选择“=”约束条件，在“约束值”文本框中输入