使用规划求解的资金预算

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

公司如何使用规划求解以确定它应有哪些项目?

每年,如黎 Lilly 公司需要确定其毒品开发;如 Microsoft,哪种软件程序开发; 公司Proctor 和场赌博,哪些新的消费者产品开发,如公司。Excel 中的规划求解功能有助于使这些决策的公司。

大多数公司希望执行参与的最大净现值 (NPV) 的项目支付有限的资源 (通常资金和劳工)。假设软件开发公司正在尝试确定哪些它应有 20 软件项目。(在数百万金额) NPV 贡献的每个项目,以及 (在数百万金额) 大写和下一步三年的每个过程中需要的编程人员数提供在文件 Capbudget.xlsx,这是基本模型工作表上在下一页上显示图 30-1。例如,项目 2 产生 908 万。它要求 $151 万期间 1 年、 $269 万期间 2 年和 3 年过程 $248 万。项目 2 所需期间 1 年、 86 编程期间 2 年和年份 3 过程 83 编程人员 139 编程人员。单元格 E4:G4 显示在三年的每个过程中可用 (在数百万金额) 大写和单元格 H4:J4 指示多少编程人员可用。例如,期间 1 年向上 $2.5 亿大写和 900 编程人员中都可用。

公司必须确定它是否应执行的每个项目。假设我们不能执行的软件项目; 分数如果我们分配 0.5 的值的所需的资源,例如,我们将获得的非工作的程序中会显示我们 $0 收入 !

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

书籍图像
图-30-1 数据,我们将使用规划求解以确定要执行的项目

使用此背景,我们就可以解决软件项目选择问题。始终为规划求解的模型,我们首先识别我们的目标单元格、 可变单元格和限制。

  • 目标单元格。我们最大化 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 和为 0 列 A 中的每个项目,此公式不拿起 NPV 的项目。因此,我们可以计算 NPV 的所有项目,并且它是由求和按照窗体的条款计算,我们的目标单元格是线性(更改相加。以类似的方式计算大写的用于每年,并通过从 E2 复制到 F2:J2 SUMPRODUCT(doit,E6:E25)的公式中使用每年的人工。

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

书籍图像
图-30-2 规划求解参数对话框框设置为项目选择模型

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

书籍图像
图-30-3 使用 Bin 选项在添加约束对话框中设置可变单元格的二进制数,将显示为 0 或 1 的单元格。

因为目标单元格计算为该窗体的术语的总和,我们模型是线性(更改相加并通过比较的总和来计算资源用量约束,因为(更改 cells)*(constants)为常量。

与填充规划求解参数对话框中,单击求解和更早版本图 30-1 中所示的结果。通过选择项目 2、 3、 6-10、 14-16、 19 和 20,公司可以获得最大的 NPV 为 9,293 万 $ (美元 9.293 亿)。

有时项目选择模型有其他限制。例如,假设我们选择项目 3,如果我们必须同时选择项目 4。我们当前的最佳解决方案选择项目 3,但不是项目 4,因为我们知道我们当前的解决方案无法保持最佳。若要解决此问题,只需添加项目 3 的二进制更改单元格小于或等于项目 4 的二进制更改单元格的约束。

您可以在文件中 Capbudget.xlsx,显示图 30-4如果 3 然后 4工作表上找到此示例。单元格 L9 是指与项目 3 和相关项目 4 的二进制值的单元格 L12 相关的二进制值。通过添加约束L9 < = L12,如果我们选择项目 3,L9 等于 1,我们限制强制 L12 (项目 4 二进制) 等于 1。我们约束还必须将二进制值留在项目 4 无限制如果我们不选择项目 3 可变单元格。如果我们不选择项目 3,L9 等于 0,然后我们约束允许项目 4 二进制等于 0 或 1,这是我们所需的信息。图 30-4 显示了新的最佳解决方案。

书籍图像
图-30-4 新最佳解决方案如果不是项目 3 然后项目 4

如果选择项目 3 意味着我们还必须选中项目 4,计算一个新的最佳解决方案。现在假设我们可以只有四个项目从项目 1 至 10。(请参阅在大多数 4 的 P1-P10工作表,显示图 30-5)。在单元格 L8,我们将计算与项目 1 通过使用公式SUM(A6:A15)10 二进制值的总和。然后我们添加约束L8 < = L10,这确保,最大 4 前 10 个项目的选择。新的最佳解决方案显示图 30-5。NPV 已删除到 $9.014 亿。

书籍图像
图-30-5 最佳解决方案时我们可以选择仅 4 的 10 个项目

线性规划求解模型中的部分或全部更改单元格都需要二进制或整数通常是更努力地以解决比在所有可变单元格允许为分数的线性模型。因此,我们通常满意二进制或整数的编程问题附近最佳解决方案。如果规划求解模型运行很长时间,您可能需要考虑调整规划求解选项对话框中的容错设置。(请参阅图 30-6)。例如,0.5%的容错设置表示规划求解将停止第一次找到 0.5%理论最佳目标单元格的值的范围内的可行解决方案 (理论最佳目标单元格的值是找到时的最佳目标值二进制和整数限制,则省略)。通常,我们都面对查找答案 10%最佳在 10 分钟之内,或在两周的计算机的时间中查找最佳解决方案之间进行选择 !默认容错值为 0.05%,这意味着规划求解停止时找到目标单元格值 0.05%理论最佳目标单元格的值的范围内。

书籍图像
图-30-6 调整容选项

  1. 1.公司具有正在考虑九个项目。添加的每个项目并在下一年中两个每个项目所需的大写字母 NPV 如下表所示。(位于数百万所有数字)。例如,项目 1 将 NPV 中添加 14 万,需要 $12 万期间 1 年和 $3 万个 2 年期间的支出。期间 1 年年大写 $50 亿是可用于项目,而 20 万可用期间 2 年。

NPV

第 1 年支出

第 2 年支出

项目 1

14

12

3

项目 2

17

54

7

项目 3

17

6

6

项目 4

15

6

2

项目 5

企业项目数字 40

30

35

项目 6

12

6

6

项目 7

14

48

4

项目 8

10

36

3

项目 9

12

18

3

  • 如果我们不能执行项目的分数,但必须采取的全部或无项目,如何充分发挥 NPV?

  • 假设如果实施项目 4,必须采取项目 5。我们如何最大化 NPV?

  • 发布公司尝试确定哪些 36 书籍它应发布本年度。文件 Pressdata.xlsx 提供了有关每本书以下信息:

    • 预计的收入和开发成本 (在数千个金额)

    • 每个簿中的页面

    • 书籍被面向 (由列 E 中的 1) 的软件开发人员的访问群体

      发布公司可以发布总额达 8500 页面本年度的书籍和必须发布至少四个书籍面向软件开发人员。公司如何最大化其利润?

本文是从Microsoft Office Excel 2007 数据分析和建模业务通过 Wayne l。 Winston 改动。

从演示文稿一系列由 Wayne Winston,众所周知 statistician 和从事创造性、 实用的 Excel 的应用程序的商学院教授开发本书课堂样式。

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

此信息是否有帮助?

谢谢您的反馈!

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

×