COMP4041-LDO Workshop 4 Post-optimality Analysis on Product-Mix Optimization
Purpose: Develop and solve LP models for some product-mix problems using both the Excel solver and LP-Solve, then interpret the post-optimality analysis reports.
The RESEARCH INSTITUTE problem description, algebraic LP model and spreadsheet LP model were given in the brief for workshop 0 (taster workshop). You might have already developed an Excel model with a different layout but also correct. In the Excel model shown there, the calculation for cells E14 to I14 are incorrect, but since those cells are only for visualisation and not involved in other calculations, the model still produces a correct optimal solution.
A screenshot of a modified model with the correct calculations for cells E14 to I14 and some additional visualisation of constraints, is shown below. Please replicate this Excel LP model exactly as shown so that the post-optimality analysis reports produced are exactly as expected (some questions in the test will be referring to those reports). Solve the problem and examine the three post-optimality reports produced by the Excel solver. Aim to understand all the parts in the reports according to what it was explained in lecture 4.
Implement the LP-Solve model for the above RESEARCH INSTITUTE problem. Then examine the post-optimality analysis reports produced by LP-Solve (some questions in the test will be referring to those reports). Make sure to write the algebraic model and label it so that the post-optimality analysis reports are complete and clear. Also make sure that you relate the post-optimality reports produced by LP-Solve to the ones produced by Excel in Step 1.
Develop the optimization model in Excel and LP-Solve to solve the following problem. Also write the algebraic model in compact notation using the sigma (summation) operator. Examine the post-optimality analysis reports for the optimal solution obtained.
MACHINE PLANNING Problem
A company produces two products A and B using two machines X and Y. The company needs the machine planning for the production over three months. Each product can be produced in any of the machines. The machine-hours required for production, the production capacity (in hours) and the estimated product demand are given in the table. The cost for using one machine hour is $30 and the cost of inventory-holding one unit of each product per month is $10. For example, producing 60 units of product A in machine X and none in machine Y, during month 1, has a cost of $3600. Since only 50 units are needed in month 1, the extra 10 units can be hold in inventory for month 2 at a cost of $100. The company wants to decide the production plan in order to meet exactly the estimated demand in each month at the minimum overall cost of machine-hours and inventory-holding. Meeting the demand exactly means being able to provide the required amount of product in each month but the production might be higher or lower depending on the inventory that can be used. It must be assumed that there is no inventory before month 1 and no inventory needed after month 3.
Hours on machine X Hours on machine Y
Product A Product B Month Capacity Capacity Demand Demand machine X machine Y product A product B
2.0 1.5 1 140 250 50 30 2.5 2.0 2 60 80 100 60 3 150 100 50 50
In addition to developing the correct optimization model, aim at developing the spreadsheet model following the principles and guidelines given in lecture 4. Produce and examine the post-optimality analysis reports both in Excel and LP-Solve.