公司如何使用 Solver 来确定它应该承担哪些项目？
每年，像礼来公司这样的公司都需要确定开发哪些药物;像 Microsoft 这样的公司，开发哪些软件程序;像 Proctor & Gamble 这样的公司，开发哪些新的消费品。 Excel 中的规划求解功能可帮助公司做出这些决策。
公司如何使用 Solver 来确定它应该承担哪些项目？
大多数公司都希望开展对 NPV) 贡献最大净现值 (项目，但受制于资源有限 (通常是资本和劳动力) 。 假设一家软件开发公司正试图确定它应该承担 20 个软件项目中的哪一个。 每个项目) 贡献的净现值 (数百万美元，以及) 以百万美元为单位的资本 (以及未来三年每年所需的程序员数量，在文件 Capbudget.xlsx 中的基本 模型 工作表中给出了，如下一页的图 30-1 所示。 例如，项目 2 的收益为 9.08 亿美元。 第一年需要 1.51 亿美元，第二年需要 2.69 亿美元，第三年需要 2.48 亿美元。 项目 2 在第一年需要 139 名程序员，在第二年需要 86 名程序员，在第三年需要 83 名程序员。 单元格 E4：G4 显示三年中每年可用的资本 (（以百万) 美元为单位），单元格 H4：J4 表示有多少程序员可用。 例如，在第一年，有高达 25 亿美元的资本和 900 名程序员可用。
公司必须决定是否应该承担每个项目。 假设我们不能承担软件项目的一小部分;例如，如果我们分配 0.5 的所需资源，我们将有一个无效的程序，该程序将为我们带来 0 美元的收入！
在对您做某事或不做某事的情况进行建模时，诀窍是使用 二进制变化单元格。 二进制更改单元格始终等于 0 或 1。 当对应于项目的二进制更改单元格等于 1 时，我们执行该项目。 如果与项目对应的二进制更改单元格等于 0，则不执行该项目。 通过添加约束来将规划求解设置为使用一系列二进制变化单元格 - 选择要使用的变化单元格，然后从“添加约束”对话框的列表中选择箱。
有了这个背景，我们已经准备好解决软件项目选择问题了。 与求解器模型一样，我们首先确定目标单元格、变化单元格和约束。
- 目标单元格。 我们将选定项目产生的净现值最大化。
- 正在更改单元格。 我们为每个项目寻找一个 0 或 1 个二进制更改单元格。 我在 A6：A25 (范围内找到了这些单元格，并将该区域命名为 doit) 。 例如，单元格 A6 中的 1 表示我们进行项目 1;单元格 C6 中的 0 表示我们不进行项目 1。
- 约束。 我们需要确保每年 t (t=1、2、3) ，每年 t 使用的资本小于或等于 Year t 可用资本，并且 Year t 使用的劳动力小于或等于年份 t 的可用劳动力。
如您所见，我们的工作表必须为任何选择的项目计算 NPV、每年使用的资本和每年使用的程序员。 在单元格 B2 中，我使用公式 SUMPRODUCT (doit，NPV) 来计算所选项目生成的总 NPV。 (范围名称 NPV 是指范围 C6：C25.) 对于 A 列中为 1 的每个项目，此公式将获取项目的 NPV，而对于 A 列中为 0 的每个项目，此公式不会获取项目的 NPV。 因此，我们能够计算所有项目的 NPV，并且我们的目标单元格是线性的，因为它是通过对遵循 变化单元格) * (constant) 的形式 求和 (项来计算的。 以类似的方式，我通过将公式 SUMPRODUCT (doit，E6：E25) 从 E2 复制到 F2：J2 来计算每年使用的资本和每年使用的劳动力。
现在，我填写求解器参数对话框，如图 30-2 所示。
我们的目标是最大化单元格 B2) (选定项目的 NPV。 我们的更改单元格 (名为 doit) 的区域是每个项目的二进制更改单元格。 约束 E2：J2<=E4：J4 确保每年使用的资本和劳动力小于或等于可用的资本和劳动力。 要添加使更改的单元格成为二进制的约束，我单击“求解器参数”对话框中的“添加”，然后从对话框中间的列表中选择箱。 “添加约束”对话框应出现，如图 30-3 所示。
我们的模型是线性的，因为目标单元格计算为具有 (变化单元格) * (常量) 形式的项的和，并且资源使用约束是通过比较 ) 常量 (变化单元格) * (常量 的总和来计算的。
在填写求解器参数对话框后，单击求解，结果如图 30-1 前面所示。 通过选择项目 2、3、6-10、14-16、19 和 20，该公司可以获得 92.93 亿美元 (92.93 亿美元) 的最大净现值。
处理其他约束
有时，项目选择模型具有其他约束。 例如，假设如果我们选择项目 3，则还必须选择项目 4。 由于当前的最佳解决方案选择了项目 3 而不是项目 4，因此我们知道当前解决方案无法保持最佳状态。 若要解决此问题，只需添加约束，即项目 3 的二进制更改单元格小于或等于项目 4 的二进制更改单元格。
你可以在文件 Capbudget.xlsx 的 If 3 then 4 工作表中找到此示例，如图 30-4 所示。 单元格 L9 是指与项目 3 相关的二进制值，单元格 L12 是指与项目 4 相关的二进制值。 通过添加约束 L9<=L12，如果我们选择项目 3，则 L9 等于 1，并且我们的约束强制 L12 (项目 4 二进制) 等于 1。 如果我们不选择项目 3，我们的约束还必须使项目 4 的更改单元格中的二进制值不受限制。 如果我们不选择项目 3，则 L9 等于 0，并且我们的约束允许项目 4 二进制文件等于 0 或 1，这正是我们想要的。 新的最优解如图 30-4 所示。
如果选择项目 3 意味着我们也必须选择项目 4，则计算一个新最优解。 现在假设我们只能执行项目 1 到 10 中的四个项目。 (请参阅“ 最多 P1-P10 的 4 ”工作表，如图 30-5 所示。) 在单元格 L8 中，我们使用公式 SUM (A6：A15) 计算与项目 1 到 10 关联的二进制值之和。 然后我们添加约束 L8<=L10，以确保最多选择前 10 个项目中的 4 个。 新的最优解如图 30-5 所示。 净现值已降至 90.14 亿美元。
解决二进制和整数规划问题
要求部分或所有变化单元是二进制或整数的线性求解器模型通常比允许所有变化单元都是分数的线性模型更难求解。 出于这个原因，我们通常对二元或整数规划问题的近乎最优的解感到满意。 如果求解器模型运行时间较长，则可能需要考虑调整求解器选项对话框中的容差设置。 (参见图 30-6.) 例如，容差设置为 0.5% 意味着求解器在第一次找到理论最佳目标像元值 0.5% 以内的可行解时停止 (理论最佳目标像元值是省略二进制和整数约束时找到的最佳目标值) 。 我们经常面临一个选择，要么在 10 分钟内找到 10% 内最优的答案，要么在两周的计算机时间内找到最佳解决方案！ 默认容差值为 0.05%，这意味着当求解在理论最佳目标像元值的 0.05% 范围内找到目标像元值时停止。
问题
- 一家公司有 9 个项目正在考虑中。 每个项目新增的净现值和每个项目未来两年所需的资本如下表所示。 (所有数字均以百万为单位。) 例如，项目 1 将增加 1400 万美元的 NPV，并要求在第 1 年支出 1200 万美元，在第 2 年支出 300 万美元。 在第一年，有 5000 万美元的资金可用于项目，在第二年有 2000 万美元的资金。
| NPV | 第一年支出 | 第二年支出 | |
|---|---|---|---|
| 项目 1 | 14 | 1.2 | 3 |
| 项目 2 | 17 | 54 | 7 |
| 项目 3 | 17 | 6 | 6 |
| 项目 4 | 15 | 6 | 2 |
| 项目 5 | 40 | 30 | 35 |
| 项目 6 | 1.2 | 6 | 6 |
| 项目 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| 项目 9 | 1.2 | 18 | 3 |
- 如果我们不能承担一个项目的一小部分，但必须承担一个项目的全部或不承担，我们如何才能最大化 NPV？
- 假设如果进行了项目 4，则必须进行项目 5。 我们如何最大化 NPV？
一家出版公司正试图确定今年应该出版 36 本书中的哪本书。 文件 Pressdata.xlsx 提供了有关每本书的以下信息：
- 预计收入和开发成本 (数千美元)
- 每本书中的页面
- 这本书是否面向软件开发人员的受众 (E 列中的 1 表示)
一家出版公司今年可以出版总计 8500 页的书籍，并且必须出版至少四本面向软件开发人员的书籍。 公司如何实现利润最大化？
文章内容
本文改编自 Wayne L. Winston 所著的《 Microsoft Office Excel 2007 数据分析和业务建模 》。
这本课堂式书籍是根据韦恩·温斯顿 （Wayne Winston） 的一系列演讲编写而成的，韦恩·温斯顿 （Wayne Winston） 是一位著名的统计学家和商学教授，专门研究 Excel 的创造性和实际应用。