Data Driven Decision Making/Business Decision Modelling

Data Driven Decision Making/Business Decision Modelling. TB2 Assignment 1

Practical Exercise – Linear Programming

A petroleum company produces three grades of motor oil – super, premium and extra – from three components. The company wants to determine the optimal mix of the three components in each grade of motor oil that will maximise profit. The maximum quantities available of each component and their cost per barrel are as follows:

Table 1

To ensure the appropriate blend, each grade has certain general specifications. Each grade must have a minimum amount of component 1 plus a combination of other components, as follows:

Table 2

The company wants to produce at least 3,000 barrels of each grade of motor oil.

TASK 1 (35 marks)

1. Identify the decision variables for this problem, using clear notation.

2. Use these decision variables to formulate the firm’s objective function, clearly stating what the objective is.

3. Use the information in Tables 1 and 2 above to identify the constraints for this problem.

4. Use what you have identified in 1-3 above to formulate and present the complete linear programming model for this problem.

TASK 2 (25 Marks)

5. Enter the linear programming model you have derived in 4 above into Solver and obtain the optimal solution to the problem

6. Extract the solution from Solver and state the value of the objective function and the values of the decision variables and summarise the results.

TASK 3 (20 marks)

7. Using Solver extract a Sensitivity Report for the problem.

8. Extract the slack and surplus variables from the Sensitivity Report and interpret their meaning.

9. Using the results of the sensitivity report indicate whether the optimal solution of the problem is unique or if there are alternative optimal solutions? Give reasons for your answer.

10. Present a brief interpretation of the ranging information on the objective function coefficients presented in the Sensitivity Report for this problem.

TASK 4 (20 marks)

11. Identify the shadow prices from the sensitivity report and interpret their meaning.

12. Using the shadow price on component 1 indicate the impact on the objective function of increasing its availability by 1 barrel. What is the upper limit of the availability of component 1 for which this shadow price is valid?

13. What would be the impact of increasing the availability of component 3 by 1 unit? Give the reasons for your answer.

Instructions

,

1. Upload your spreadsheet with Solver model and Sensitivity Report to Canvas.
2. Upload a Word document containing your answers to questions 1 to 13 above.
3. The piece of work is individual.