PROBLEM 5: Multi-Period Planning

Recall the production and inventory planning problem of Upton Manufacturing formulated as follows:

Minimise 240P1 + 250P2 + 265P3 + 285P4 + 280P5 + 260P6 + 3.6(B1+B2)/2 + 3.75(B2+B3)/2 + 3.98(B3+B4)/2 + 4.28(B4+B5)/2 + 4.20(B5+ B6)/2 + 3.9(B6+B7)/2

s.t. 2,000 ≤ P1 ≤ 4,000

1,750 ≤ P2 ≤ 3,500

2,000 ≤ P3 ≤ 4,000

2,250 ≤ P4 ≤ 4,500

2,000 ≤ P5 ≤ 4,000

1,750 ≤ P6 ≤ 3,500

1,500 < B1 + P1 - 1,000 < 6,000

1,500 < B2 + P2 - 4,500 < 6,000

1,500 < B3 + P3 - 6,000 < 6,000

1,500 < B4 + P4 - 5,500 < 6,000

1,500 < B5 + P5 - 3,500 < 6,000

1,500 < B6 + P6 - 4,000 < 6,000

B1 = 2750

B2 = B1 + P1 - 1,000

B3 = B2 + P2 - 4,500

B4 = B3 + P3 - 6,000

B5 = B4 + P4 - 5,500

B6 = B5 + P5 - 3,500

B7 = B6 + P6 - 4,000

Question 1:

From the Excel implementation, it is possible to generate a sensitivity report. Based on this, what is the impact for Upton’s minimised cost from increasing the minimum inventory level at the end of month 6 to 1,501 from 1,500? Verify your answer by changing the relevant parameter in the Excel implementation and re-executing Solver to find the new optimal solution. Provide a screenshot of the changes that you made and the new minimised cost that you found.

Question 2:

Consider the monthly maximum production constraints in the formulation above. From the right-hand sides of the first six constraints in the formulation, it seems that the maximum total production that Upton is allowed over the entire six months is 23,500. Instead of having monthly maximum production limits, how would you reformulate the problem such that there were only a single constraint on total production of 23,500 over these six months?

Question 3:

Implement the formulation from Question 2 in Excel. Provide a screenshot of the new optimal solution and Solver settings. What is the minimised cost?