使用 Microsoft 登录
登录或创建帐户。
你好,
使用其他帐户。
你有多个帐户
选择要登录的帐户。

公司如何使用规划求解来确定应执行哪些项目?

每年,像Eli Lilly这样的公司都需要确定开发哪些药物:像 Microsoft 这样的公司,要开发哪些软件程序;像 Proctor & Gamble 这样的公司,要开发新的消费品。 Excel 中的规划求解功能可帮助公司做出这些决策。

大多数公司希望承担 (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 页的书籍,并且必须至少出版四本面向软件开发人员的书籍。 公司如何实现利润最大化?

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

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

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。

此信息是否有帮助?

你对语言质量的满意程度如何?
哪些因素影响了你的体验?
按“提交”即表示你的反馈将用于改进 Microsoft 产品和服务。 你的 IT 管理员将能够收集此数据。 隐私声明。

谢谢您的反馈!

×