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

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的英文版本以便参考。

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

每年, 黎国 Lilly 公司都需要确定要开发的毒品;Microsoft 等公司, 要开发哪些软件程序;一家公司, 如 Proctor & Gamble, 是要开发的新消费者产品。 Excel 中的 "规划求解" 功能可帮助公司做出这些决策。

大多数企业希望执行可提供最大净现值 (NPV) 的项目, 这些项目受有限资源 (通常是资本和劳动) 的影响。 假设软件开发公司正在尝试确定应采取哪种软件项目。 在文件 Capbudget 中的基本模型工作表上提供了每个项目的 NPV (以百万为单位), 以及每个未来三年所需的程序员数量。在文件中的基本模型工作表中, 这是下一页面上的图30-1 所示。 例如, 项目2生成 $908000000。 在第2年和第3年的 $269000000 中, 需要 $151000000。 Project 2 在第1年、86程序员和第3年的中需要139程序员, 并在第3年中83程序员。 单元格 E4: G4 显示三年的每一种可用的资本 (以百万为单位), 单元格 h4 为: J4 指明有多少程序员可用。 例如, 在第1年中, 在资本和900程序员的 $2500000000 年中提供。

公司必须决定是否应承担每个项目。 假设我们不能取得软件项目的一小部分;例如, 如果我们分配0.5 所需的资源, 我们将有一个非工作程序, 这会给我们带来 $0 收入!

在建模情况中, 执行或不执行任何操作的技巧是使用二进制可变单元格。 二进制可变单元格始终等于0或1。 当与某个项目对应的二进制更改单元格等于1时, 我们执行该项目。 如果与某个项目对应的二进制可变单元格等于 0, 则不会执行该项目。 通过添加约束将规划求解设置为使用二进制可变单元格区域, 请选择要使用的可变单元格, 然后从 "添加约束" 对话框的列表中选择 "Bin"。

书籍图像

在此背景中, 我们准备好解决软件项目选择问题。 对于始终使用规划求解模型, 我们首先确定目标单元格、可变单元格和约束。

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

  • 更改单元格。我们为每个项目查找一个0或1个二进制可变单元格。 我已在 "A6: A25" 区域 (命名区域doit) 中找到这些单元格。 例如, 单元格 A6 中的1表示我们执行的是 Project 1;单元格 C6 中的0表示我们不会执行项目1。

  • 各种.我们需要确保每年 t 的t (t = 1, 2, 3), 使用的年数资本小于或等于年末资本, 而使用的年数小于或等于 year 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 所示。

书籍图像

我们的目标是最大程度地提高所选项目 (单元格 B2) 的 NPV。 我们的可变单元格 (名为doit的区域) 是每个项目的二进制可变单元格。 约束E2: J2< = E4: J4确保每年的资本和人工使用小于或等于资本和劳动。 若要添加使更改的单元格成为二进制的约束, 我单击 "规划求解参数" 对话框中的 "添加", 然后从对话框中间的列表中选择 "Bin"。 将显示 "添加约束" 对话框, 如图30-3 所示。

书籍图像

我们的模型是线性的, 因为目标单元格计算为具有窗体(可变单元格) * (常量)的术语总和, 并且通过将(可变单元格) * (常量)的总和与常量进行比较来计算资源使用限制。

在已填写 "规划求解参数" 对话框中, 单击 "求解", 我们将在图30-1 中显示前面所示的结果。 通过选择项目2、3、6-10、14–16、19和 20, 公司可以获得最大的 NPV $9293000000 ($9293000000)。

有时, 项目选择模型具有其他限制。 例如, 假设我们选择 "项目 3", 我们还必须选择 "项目 4"。 由于我们的当前最佳解决方案选择项目 3, 而不是 project 4, 因此我们知道当前的解决方案无法保持最佳状态。 若要解决此问题, 只需添加项目3的二进制更改单元格小于或等于项目4的二进制可变单元格的限制。

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

书籍图像

如果选择项目3意味着我们还必须选择 "项目 4", 将计算新的最佳解决方案。 现在假设我们只能从项目1到10中执行四个项目。 (请参阅图30-5 所示的最多4个 P1 – P10工作表。) 在单元格 L8 中, 使用公式sum (A6: A15)计算与项目1到10相关联的二进制值的总和。 然后, 我们添加约束L8< = L10, 从而确保选择了前10个项目中的最大4个项目。 新的最佳解决方案如图30-5 所示。 NPV 已降为 $9014000000。

书籍图像

线性规划求解模型, 在这种模型中, 某些或所有可变单元格必须是二进制或整数, 通常比线性模型更难解决, 因为所有可变单元格都允许分分式。 出于此原因, 我们经常会对二进制或整数编程问题的接近最佳的解决方案感到满意。 如果您的规划求解模型长时间运行, 可能需要考虑调整 "规划求解选项" 对话框中的 "容差" 设置。 (请参阅图30-6。) 例如, 0.5% 的容差设置意味着, 在第一次发现的可行解决方案位于理论最佳目标单元格值的 0.5% 范围内时, 它将会停止 (理论最佳目标单元格值是最佳目标值省略二进制和整数约束。 通常, 我们面临的选择是在10分钟内获得 10% 的答案, 或在计算机时间的两周内找到最佳解决方案! 默认公差值为 0.05%, 这意味着当它在理论最佳目标单元格值的 0.05% 范围内找到目标单元格值时, 将停止规划求解。

书籍图像

  1. 1. 公司有九个项目在考虑。 下表显示了每个项目所添加的 NPV 以及下两年中每个项目所需的资本。 (所有数字均以百万为单位。) 例如, Project 1 将在 NPV 中添加 $14000000, 并且在第1年和第2年的第2年中需要 $12000000 的支出。 在第1年中, $50000000 的资本可用于项目, 而 $20000000 在第2年提供。

假定

第1年支出

第2年支出

项目1

14

1.2

3

项目2

54

7

项目3

6

6

项目4

15

6

2

项目5

40

大约

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 提供了有关每本书的以下信息:

    • 预计收入和开发成本 (以千位美元为单位)

    • 每本书中的页面

    • 书籍是否适合于软件开发人员的受众 (用列 E 中的1表示)

      发布公司可以将书籍的总计最高发布到8500页, 并且必须至少发布四个与软件开发人员相对应的书籍。 公司如何最大限度地提高利润?

本文通过堡 Winston 与Microsoft Office Excel 2007 数据分析和业务建模进行了改编。

这种课堂风格的书籍是从一系列演示文稿开发的, 由堡 Winston, 这是众所周知的 statistician 和商业教授, 专门从事富有创意的、实用的 Excel 应用。

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×