excel - 混合模型: Oil Production

Question

油混合

一家石油公司生产三种品牌的油:普通油、多级油和最高。每个品牌的油由四种原油库存中的一种或多种组成，每种原油库存具有不同的润滑指数。原油库存相关数据如下。

+-------------+-------------------+------------------+--------------------------+
| Crude Stock | Lubrication Index | Cost (€/barrell) | Supply per day (barrels) |
+-------------+-------------------+------------------+--------------------------+
| 1           | 20                | 7,10             | 1000                     |
+-------------+-------------------+------------------+--------------------------+
| 2           | 40                | 8,50             | 1100                     |
+-------------+-------------------+------------------+--------------------------+
| 3           | 30                | 7,70             | 1200                     |
+-------------+-------------------+------------------+--------------------------+
| 4           | 55                | 9,00             | 1100                     |
+-------------+-------------------+------------------+--------------------------+  

每个品牌的油必须满足润滑指数的最低标准，并且每个品牌因此以不同的价格出售。三个牌号油的相关数据如下如下。

+------------+---------------------------+---------------+--------------+
| Brand      | Minimum Lubrication index | Selling price | Daily demand |
+------------+---------------------------+---------------+--------------+
| Regular    | 25                        | 8,50          | 2000         |
+------------+---------------------------+---------------+--------------+
| Multigrade | 35                        | 9,00          | 1500         |
+------------+---------------------------+---------------+--------------+
| Supreme    | 50                        | 10,00         | 750          |
+------------+---------------------------+---------------+--------------+   

确定一天的最佳产出计划，假设产量可以是以可忽略不计的成本出售或储存。每日需求数据可能有不同的解释。调查以下:
(a) 每日需求代表潜在销售额。换句话说，模型应该包含需求上限(上限)。最优利润是多少？
(b) 日常要求是严格的义务。换句话说，模型应该包含精确满足的需求约束。最优利润是多少？
(c) 每日需求代表最低销售 promise ，但所有产出都可以出售。换句话说，该模型应该允许产量超过每日 promise 。最优利润是多少？

问题

我已经能够在 Excel 中构建以下模型并通过 OpenSolver 求解它，但我只能集成常规油的混合。我正在尝试阅读 Kenneth R. Baker 所著的《Optimization Modeling with Spreadsheets》一书，但我一直坚持这个练习。虽然我可以从另一个混合问题转移逻辑，但我不确定如何同时构建多个混合的模型。我将该问题建模为不同原油库存成本的最小化问题。使用润滑指数数据，我将 R-Lub 指数的约束构建为线性约束。到目前为止，答案似乎对于普通油来说是正确的。然而，使用这种方法我不知道如何添加第二种多级油。

+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| Decision Variables |        |        |        |        |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
|                    | C1     | C2     | C3     | C4     |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| Inputs             | 1000   | 0      | 1000   | 0      |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
|                    |        |        |        |        |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| Objective Function |        |        |        |        |  | Total       |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| Cost               | 7,10 € | 8,50 € | 7,70 € | 9,00 € |  | 14.800,00 € |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
|                    |        |        |        |        |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| Constraints        |        |        |        |        |  | LHS         |    | RHS  |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| C1 supply          | 1      |        |        |        |  | 1000        | <= | 1000 |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| C2 supply          |        | 1      |        |        |  | 0           | <= | 1100 |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| C3 supply          |        |        | 1      |        |  | 1000        | <= | 1200 |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| C4 supply          |        |        |        | 1      |  | 0           | <= | 1100 |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| R- Lub Index       | -5     | 15     | 5      | 30     |  | 0           | >= | 0    |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| R- Output          | 1      | 1      | 1      | 1      |  | 2000        | =  | 2000 |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
|                    |        |        |        |        |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| Blending Data      |        |        |        |        |  |             |    |      |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+
| R- Lub             | 20     | 40     | 30     | 55     |  | 25          | >= | 25   |
+--------------------+--------+--------+--------+--------+--+-------------+----+------+  

这是带有 Excel 公式的模型:

+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| Decision Variables |      |     |      |    |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
|                    | C1   | C2  | C3   | C4 |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| Inputs             | 1000 | 0   | 1000 | 0  |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
|                    |      |     |      |    |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| Objective Function |      |     |      |    |  | Total                                              |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| Cost               | 7,1  | 8,5 | 7,7  | 9  |  | =SUMMENPRODUKT(B5:E5;B8:E8)                        |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
|                    |      |     |      |    |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| Constraints        |      |     |      |    |  | LHS                                                |    | RHS  |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| C1 supply          | 1    |     |      |    |  | =SUMMENPRODUKT($B$5:$E$5;B11:E11)                  | <= | 1000 |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| C2 supply          |      | 1   |      |    |  | =SUMMENPRODUKT($B$5:$E$5;B12:E12)                  | <= | 1100 |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| C3 supply          |      |     | 1    |    |  | =SUMMENPRODUKT($B$5:$E$5;B13:E13)                  | <= | 1200 |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| C4 supply          |      |     |      | 1  |  | =SUMMENPRODUKT($B$5:$E$5;B14:E14)                  | <= | 1100 |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| R- Lub Index       | -5   | 15  | 5    | 30 |  | =SUMMENPRODUKT($B$5:$E$5;B15:E15)                  | >= | 0    |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| R- Output          | 1    | 1   | 1    | 1  |  | =SUMMENPRODUKT($B$5:$E$5;B16:E16)                  | =  | 2000 |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
|                    |      |     |      |    |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| Blending Data      |      |     |      |    |  |                                                    |    |      |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+
| R- Lub             | 20   | 40  | 30   | 55 |  | =SUMMENPRODUKT($B$5:$E$5;B19:E19)/SUMME($B$5:$E$5) | >= | 25   |
+--------------------+------+-----+------+----+--+----------------------------------------------------+----+------+  

朝着正确的方向插入将是一个巨大的帮助。

Answer 1

我认为您希望您的目标是利润，我将其定义为销售值(value)总和 - 成本总和。

要包括所有混合物，请计算每种混合物的生产量、润滑油指数、成本和值(value)。对使用的库存量、生产量和润滑油指数应用约束，并优化利润。

我将模型组合如下......

1. A 至 D 列是您提供的信息。
2. G2:J5 中的 10 是每次混合中使用的库存量的种子值。求解器将操纵这些。
3. K 列包含生产的总产品量。根据您的调查 (a)、(b) 和 (c)，这些将受到不同方式的限制。它是=SUM(G3:J3) 向下填充的。
4. L 列是产品的润滑油指数。正如您所指出的，它是线性混合 - 对于混合问题来说通常不是这样。这些值将在求解器中受到约束。它是 {=SUMPRODUCT(G3:J3,TRANSPOSE($B$2:$B$5))/$K3} 填充的。请注意，这是一个 Control-Shift-Enter (CSE) 公式，这是 TRANSPOSE 所必需的。
5. M 列是用于制造产品的库存成本。这用于利润计算。它是 {=SUMPRODUCT(G3:J3,TRANSPOSE($C$2:$C$5))}，已填充。这也是一个 CSE 公式。
6. N 列是所生产产品的值(value)。这用于利润计算。它是=K3*C8 向下填充的。
7. 第 7 行是用于生成所有混合物的总库存量。这些值将在求解器中受到约束。它是=SUM(G3:G5)，填充到右侧。
8. 利润计算方式为=SUM(N3:N5)-SUM(M3:M5)。

下面是求解器对话框的快照...

它执行以下操作...

1. 目标是利润最大化。
2. 它将通过操纵每种混合中的原料量来实现这一点。
3. 前四个约束($G$7 到 $J$7)确保不违反可用库存量。
4. 接下来的三个约束($K$3 到 $K$5)适用于情况 (a) - 生产的产品数量不得超过需求。
5. 最后三个约束($L$3 到 $L$5)确保润滑油指数满足最低规范。
6. 未显示 - 我选择了 GRG 非线性选项，并选择了“使用多重起点”并取消选择了“需要变量界限”。

下面是情况 (a) 的结果...

对于情况 (b)，将 K 列的约束更改为“=”而不是“<=”。下面是结果...

对于情况 (c)，将 K 列的约束更改为 ">=。下面是结果...