Assignment -45 |Optimization Modeling

Excepted timing : 10 minutes

Sample Data 

| Product | Material A (kg/unit) | Material B (kg/unit) | Profit ($) |

|---------|----------------------|----------------------|------------|

| A       | 2                    | 1                    |            |

| B       | 3                    | 2                    |            |

| C       | 1                    | 3                    |            |


Task :
  1. Use Solver Add-in to maximize profit by adjusting quantities of Product A, B, and C.
  2. Set constraints to ensure total material usage does not exceed available quantities.
  3. Calculate the shadow price for Material A and Material B using Solver results.
  4. Use Scenario Manager to compare profits under different production constraints.
  5. Build a sensitivity analysis table to analyze the impact of changing profit margins on product quantities.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

 

Step To Solve:

1.Solver Setup:

o    Go to Data tab > Solver (if not available, install the Solver Add-in from Excel Add-ins).

o    Set Objective: Select the cell where you want the Solver to maximize profit (Profit cell).

o    Set Objective to: Max (since we are maximizing profit).

o    By Changing Variable Cells: Select the cells where quantities of Product A, B, and C are located.

o    Add Constraints:

o    Click Solve to let Solver find the optimal solution.

2. Interpreting Results

·         After Solver finds a solution, it will adjust the quantities of Product A, B, and C to maximize profit while respecting the constraints.

·         Note the values of Product quantities and the calculated profit.

3. Further Analysis

·         Shadow Prices: Calculate the shadow prices for Material A and Material B to understand their impact on profit when their availability changes.

·         Scenario Manager: Use Excel's Scenario Manager to compare profits under different production constraints or changes in material availability.

·         Sensitivity Analysis: Build a sensitivity analysis table to analyze how changes in profit margins or material costs affect the optimal quantities and overall profit.