Example 4 4 Blending Models Background Information n
Example 4. 4 Blending Models
Background Information n Chandler Oil has 5000 barrels of crude oil 1 and 10, 000 barrels of crude oil 2 available. n Chandler sells gasoline and heating oil. n These products are produced by blending together the two crude oils. n Each barrel of crude oil 1 has a “quality level” of 10 and each barrel of crude oil 2 has a quality level of 5. Gasoline must have an average quality level of at least 8, while heating oil must have an average level of at least 6. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Background Information – continued n Gasoline sells for $25 per barrel and heating oil sells for $20 per barrel. n The advertising cost to sell one barrel of gasoline is $0. 20 and the advertising cost to sell one barrel of heating oil is $0. 10. n We assume that demand for heating oil and gasoline is unlimited, so that all of Chandler’s production can be sold. n Chandler wants to maximize profits. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Solution n To model Chandler’s problem, we must keep track of the following: – The number of barrels of gasoline and heating oil produced (the outputs) – The number of barrels of each crude oil (the inputs) used to produce each output – The quality levels of the inputs used to make the outputs – The total profit earned 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
BLENDING. XLS n This file shows the spreadsheet model for this problem. n The spreadsheet figure on the next slide shows the model. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Developing the Model n To develop this model, proceed as follows. – Monetary and quality inputs. Enter the unit profit contributions and advertising costs in the Selling. Prices and Unit. Ad. Costs ranges. Enter the quality levels for crude oils in the Quality. Levels range and the quality standards for the outputs in the range B 13: C 13. Enter the barrels available in the Available range. – Inputs blended into each output. Although it may not be immediately apparent, the quantities Chandler must choose to specify any solution are the barrels of each input used to produce each output. Therefore, enter any trial values for these values in the Blend. Plan range. The Blend. Plan range will be the changing cell range. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Developing the Model – continued – Inputs used and outputs sold. We need to calculate the row sums and column sums of the Blend. Plan range. – Quality achieved. Keeping track of the quality level of gasoline and heating oil in the Quality. Obtained range is tricky. Begin by calculating for each output the number of quality points (QP) in the inputs used to produce this output: QP in gasoline = 10 * (Oil 1 in gasoline) + 5 * (Oil 2 in gasoline) QP in heating oil = 10 * (Oil 1 in heating oil) + 5 * (Oil 2 in heating oil) For the gasoline produced to have a quality level of at least 8, we must have QP in gasoline 8 * Gasoline sold For the heating oil produced to have a quality level of at least 6, we must have QP in heating oil 6 * Heating Oil sold 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Developing the Model – continued – To implement the Inequalities, calculate the QP for gasoline in cell B 23 with the formula =SUMPRODUCT(B 17: B 18, Quality. Levels) then copy this formula to cell C 23 to generate the QP for heating oil. – Quality required. Calculate the required quality points for gasoline and heating oil in the Quality. Reqd range. Specifically, in cell B 25 determine the required quality points for gasoline with the formula =B 13*B 19. Then copy this formula to cell C 25 to calculate the required quality points for heating oil. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Developing the Model – continued – Revenue, cost and profit. Calculate the total revenue, total advertising cost, and total profit in the Revenue, Ad. Cost, and Profit cells with the formulas =SUMPRODUCT(Selling. Prices, Sold), =SUMPRODUCT(Unit. Ad. Costs, Sold) and =Revenue. Ad. Cost. n Using Solver: To solve Chandler’s problem with Solver, proceed as follows. – Objective. Maximize profit by choosing the Profit cell as the target cell. – Changing cells. Select the Blend. Plan range as the changing cells. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Developing the Model – continued – Crude oil availability constraint. Enter the constraint Used <=Available. This endures that no more of any oil is used than is available. – Quality constraint. Enter the constraint Quality. Obtained>=Quality. Reqd. This ensures that the gasoline and heating oil produced are acceptable quality. – Specify nonnegativity and optimize. Under Solver. Options, check the nonnegativity box, and use the LP algorithm to obtain the optimal solution shown. n The Solver dialog should appear as shown on the next slide. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Solution n The solution implies that Chandler should make 5000 barrels of gasoline with 3000 barrels of crude oil 1 and 2000 barrels of crude oil 2. n It should also make 10, 000 barrels of heating oil with 2000 barrels of crude oil 1 and 8000 barrels of crude oil 2. n With this blend Chandler will earn a profit of $323, 000. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Solution – continued n As stated earlier, we believe this problem is sufficiently complex to defy intuition. n Clearly, gasoline is more profitable per barrel than heating oil, but given the crude availability and the quality constraints, it turns out that Chandler should sell twice as much heating oil as gasoline. n This would have been very difficult to guess ahead of time. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Sensitivity Analysis n We perform two typical sensitivity analyses on the Chandler blending model. n In each, we see how profit and the amounts of the outputs produced vary. n In the first analysis, we use the unit selling price of gasoline as the input and let it vary from $20 to $80 in increments of $5. n The Solver. Table results appear in the figure on the next slide. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Sensitivity Analysis – continued n Two things are of interest. – First, as the price of gasoline increases, Chandler produces more gasoline and less heating oil, exactly as we would expect. – Second, the profit never decreases, as the changes in column E indicate. n In the second sensitivity analysis, we vary the availability of crude 1 from 2000 barrels to 20, 000 barrels in increments of 1000 barrels. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Sensitivity Analysis – continued n The resulting Solver. Table output appears here. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Sensitivity Analysis – continued n These results make sense if we analyze them carefully. – First, the profit increases, but at a decreasing rate, as more crude 1 is available. This is a common occurrence in LP models. As more of a resource is made available, profit can only increase, but each extra unit of the resource produces less profit than the previous unit. – Second, the amount of gasoline produced increases while the amount of heating oil produced decreases. Why? Crude 1 has higher quality than crude 2, and gasoline requires higher quality. Gasoline also sells for a higher price. Therefore, as more crude 1 is available, Chandler can produce more gasoline, receive more profit, and still meet quality standards. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Modeling Issues 1. We used the inequality as the Quality constraint for gasoline. It might appear more natural to write this in terms of average quality points by dividing through by Gasoline sold: (QP in gasoline)/(Gasoline sold) 8 While this is logically correct, we prefer the Inequality for two reasons. – First, it is conceivable that no gasoline should be sold. – Second, is that the second inequality is technically nonlinear, as the Solver will inform you if you try to use the LP algorithm. The lesson, therefore, is to “clear denominators” in blending problem constraints. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Modeling Issues – continued 2. We have assumed that the quality level of a mixture is a linear function of the fraction of each input used in the mixture. For example, we have assumed that if gasoline is made with a fraction 3/5 of crude oil 1 and 2/5 of crude oil 2, then Quality Level of Gasoline = 3/5 * Quality Level of Oil 1 + 2/5 * Quality Level of Oil 2 If the quality level of the output is not a linear function of the fraction of each input used in the mixture, then we no longer have a linear model; we have a nonlinear model. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
Modeling Issues – continued 3. In reality, a company using a blending model would run the model periodically and set production on the basis of the current inventory of inputs and the current demand forecasts. Then the forecasts and the input levels would be updated, and the model would run again to determine the next day’s production. 4. 1 | 4. 2 | 4. 3 | 4. 5 | 4. 6 | 4. 7
- Slides: 22
