使用规划求解进行资本预算

大多数公司希望承担 (NPV) 贡献最大净现值的项目，但资源有限， (通常为资本和劳动力) 。 假设一家软件开发公司正在尝试确定应执行 20 个软件项目中哪一个。 每个项目贡献的 NPV (数百万美元) ，以及) 数百万美元的资本 (，以及未来三年每一年所需的程序员数量，位于文件 Capbudget.xlsx 的“基本模型 ”工作表上，下一页如图 30-1 所示。 例如，项目 2 产生 9.08 亿美元。 第一年需要1.51亿美元，第二年需要2.69亿美元，第三年需要2.48亿美元。 项目 2 在第 1 年需要 139 名程序员，第 2 年需要 86 名程序员，第 3 年需要 83 名程序员。 单元格 E4：G4 显示三年内每) 数百万美元的资本 (，单元格 H4：J4 指示有多少程序员可用。 例如，在第 1 年，最多有 25 亿美元的资本和 900 名程序员可用。

公司必须决定是否应承担每个项目。 假设我们不能承担软件项目的一小部分;例如，如果我们分配了 0.5 个所需资源，我们将有一个非工作计划，这将给我们带来 0 美元的收入！

在执行或不执行某些操作的情况下进行建模的技巧是使用 二进制更改单元格。 二进制更改单元格始终等于 0 或 1。 当对应于项目的二进制更改单元格等于 1 时，我们将执行该项目。 如果对应于项目的二进制更改单元格等于 0，则不执行该项目。 通过添加约束，将规划求解设置为使用一系列二进制更改单元格-选择要使用的更改单元格，然后从“添加约束”对话框中的列表中选择“Bin”。

在此背景下，我们已准备好解决软件项目选择问题。 与规划求解模型一样，我们首先标识目标单元格、变化单元格和约束。

• 目标单元格。 我们将所选项目生成的 NPV 最大化。

• 更改单元格。 我们为每个项目寻找一个 0 或 1 二进制更改单元格。 我在 A6：A25 (区域找到这些单元格，并将区域 doit 命名为) 。 例如，单元格 A6 中的 1 表示我们执行项目 1;单元格 C6 中的 0 表示我们不执行项目 1。

• 约束。 我们需要确保对于每个年份 t (t=1， 2， 3) ，使用年份 t 资本小于或等于可用的 t 资本年，并且使用的年 t 劳动力小于或等于可用的 年 t 劳动力。

如你所看到的，我们的工作表必须计算任何选择的项目 NPV、每年使用的资金以及每年使用的程序员。 在单元格 B2 中，我使用 公式 SUMPRODUCT (doit，NPV) 来计算所选项目生成的 NPV 总数。 (范围名称 NPV 是指范围 C6：C25.) 对于列 A 中具有 1 的每个项目，此公式将选取项目的 NPV，对于列 A 中具有 0 的每个项目，此公式不会选取项目的 NPV。 因此，我们能够计算所有项目的 NPV，并且我们的目标单元格是线性的，因为它是通过对单元格 ) * (常量) (形式求和计算的。 以类似的方式，我将从 E2 复制到 F2：J2 公式 SUMPRODUCT (doit，E6：E25) 来计算每年使用的资本和每年使用的劳动力。

现在，我填写“规划求解参数”对话框，如图 30-2 所示。

我们的目标是将所选项目的 NPV 最大化， (单元 B2) 。 名为 doit) 的区域 (更改单元格是每个项目的二进制更改单元格。 约束 E2：J2<=E4：J4 可确保每年使用的资本和劳动力小于或等于可用的资本和劳动力。 若要添加使更改单元格成为二进制文件的约束，请在“规划求解参数”对话框中单击“添加”，然后从对话框中间的列表中选择“Bin”。 “添加约束”对话框应如图 30-3 所示。

我们的模型是线性的，因为目标单元格计算为具有 (更改单元格) * (常量) 形式的术语之和，并且资源使用约束是通过比较 ) 常量) * (常量 (变化单元格 的总和来计算的。

填写“规划求解参数”对话框后，单击“求解”，结果如图 30-1 所示。 公司可以选择项目 2、3、6-10、14-16、19 和 20， (92.93 亿美元) 的最大 NPV 为 92.93 亿美元。

有时，项目选择模型具有其他约束。 例如，假设如果选择“项目 3”，还必须选择“项目 4”。 由于我们当前的最佳解决方案选择项目 3，但未选择项目 4，因此我们知道当前解决方案无法保持最佳状态。 若要解决此问题，只需添加约束，即 Project 3 的二进制更改单元格小于或等于 Project 4 的二进制更改单元格。

可以在文件 Capbudget.xlsx 的 If 3 then 4 工作表上找到此示例，如图 30-4 所示。 单元格 L9 是指与项目 3 相关的二进制值，单元格 L12 是指与项目 4 相关的二进制值。 通过添加约束 L9<=L12，如果选择“项目 3”，则 L9 等于 1，约束将强制 L12 (Project 4 二进制) 等于 1。 如果我们未选择“项目 3”，约束还必须将二进制值保留在 Project 4 的更改单元格中不受限制。 如果未选择“项目 3”，则 L9 等于 0，并且我们的约束允许 Project 4 二进制文件等于 0 或 1，这就是我们想要的。 图 30-4 显示了新的最佳解决方案。

如果选择“项目 3”意味着我们还必须选择“项目 4”，则会计算新的最佳解决方案。 现在假设在项目 1 到 10 之间只能执行四个项目。 (请参阅最多 4 个 P1–P10 工作表，如图 30-5.) 在单元格 L8 中，我们使用 公式 SUM (A6：A15) 计算与项目 1 到 10 关联的二进制值之和。 然后添加约束 L8<=L10，这可确保在前 10 个项目中最多选择 4 个。 图 30-5 显示了新的最佳解决方案。 NPV已降至90.14亿美元。

线性规划求解器模型（其中某些或所有更改单元格需要为二进制或整数）通常比线性模型更难求解，其中所有更改单元格都允许为分数。 因此，我们通常对二进制或整数编程问题的近乎最佳解决方案感到满意。 如果规划求解模型长时间运行，则可能需要考虑调整“规划求解选项”对话框中的“容差”设置。 (见图 30-6.) 例如，“容差”设置为 0.5% 意味着规划求解首次找到的可行解决方案时将停止该解决方案，该解决方案位于理论最佳目标单元格值的 0.5% 以内 (理论最佳目标单元格值是当省略二进制和整数约束) 时找到的最佳目标值。 通常，我们面临着在 10 分钟内找到最佳答案的 10% 或两周计算机时间内找到最佳解决方案之间的选择！ 默认容差值为 0.05%，这意味着当规划求解发现目标单元格值在理论最佳目标单元格值的 0.05% 以内时，它将停止。

1. 一家公司有九个项目正在考虑中。 下表显示了每个项目在未来两年内添加的 NPV 和每个项目所需的资本。 (所有数字都以百万为单位。) 例如，项目 1 将增加 1400 万美元的 NPV，并要求在第一年增加 1 200 万美元，在第二年需要 300 万美元。 在第 1 年，5000 万美元可用于项目，2000 万美元可用于第 2 年。

	NPV	第一年支出	第 2 年支出
项目 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
项目 8	10	36	3
项目 9	1.2	18	3

• 如果我们不能承担项目的一小部分，但必须承担全部或全部项目，我们如何最大化 NPV？

• 假设如果执行了项目 4，则必须执行项目 5。 如何最大化 NPV？

• 一家出版公司正试图确定今年应该出版的36本书中的哪一本。 Pressdata.xlsx 文件提供有关每本书的以下信息：

  • 预计收入和开发成本 (数千美元)

  • 每本书中的页面

  • 该书是否面向软件开发人员的受众 (E 列中的 1 表示)

    一家出版公司今年可以出版总计达 8500 页的书籍，并且必须至少出版四本面向软件开发人员的书籍。 公司如何实现利润最大化？

本文改编自 Microsoft Office Excel 2007 数据分析和业务建模 by Wayne L. Winston。

这本课堂式的书是由韦恩·温斯顿（Wayne Winston）编写的，他是一位著名的统计学家和商业教授，专门研究 Excel 的创造性、实际应用。