Problem 1

Solve the LOGO Production problem (the base version with only two products — see attached Download Excel file) with Excel Solver and **produce the Sensitivity Report**. Using the sensitivity report without resolving the problem, predict whether the optimal solution changes if:

- The Selling Price for Santa’s Grotto increases from $55 to $80.
- The Selling Price for Advent Calendar decreases from $35 to $22.
- The Material Cost for Santa’s Grotto increases from $15 to $45.
- The Material Cost for Advent Calendar decreases from $8 to $20.

In each case where the optimal solution does __not__ change, also predict the new optimal profit without resolving the problem. Show your workings.

Problem 2

Refer to the Sensitivity Report of the LOGO Production problem. Without resolving the problem, predict the optimal profit (or indicate that we cannot predict without resolving) if:

- The maximum demand for Santa’s Grotto increases from 20000 to 40000.
- The maximum demand for Advent Calendar decreases from 60000 to 45000.
- The number of molding machine-hours available increases from 300 to 350.
- The number of packing machine-hours available increases from 300 to 350.

Show your workings.

Problem 3

Refer to the Worker Scheduling problem in Section 4.3 of the textbook. Implement the model in Excel following the textbook.

Then, try to add the following requirement to the model: Out of all workers, we want at least 20% starting their shifts in the weekend (Sat or Sun). For example, if we schedule 5 workers in each 5-day shift, we have 10 out of 35 workers (28.5%) starting their shifts in the weekend, which satisfies the requirement. Can such a requirement be formulated as a linear constraint?

Add this to the spreadsheet model and solve for the optimal solution. How much does adding this constraint change the optimal objective?

Problem 4

A bank is attempting to determine where its assets should be invested during the current year. At present, $500,000 is available for investment in bonds, home loans, auto loans, and personal loans. The annual rates of return on each type of investment are known to be the following: bonds, 10%; home loans, 16%; auto loans, 13%; and personal loans, 20%. To ensure that the bank’s portfolio is not too risky, the bank’s invest- ment manager has placed the following three restric- tions on the bank’s portfolio:

- The amount invested in personal loans cannot ex- ceed the amount invested in bonds.
- The amount invested in home loans cannot exceed the amount invested in auto loans.
- No more than 25% of the total amount invested can be in personal loans.

Help the bank maximize the annual return on its investment portfolio.

Problem 5

Young MBA Erica Cudahy can invest up to $20,000 in stocks and loans. Each dollar invested in stocks yields $0.08 profit, and each dollar invested in a loan yields $0.13 profit. At least 40% of all money invested must be in stocks, and at least $7000 must be in loans. Determine how Erica can maximize the profit earned on her investments.

## Answer Report 1

Microsoft Excel 16.0 Answer Report | ||||||

Worksheet: [LOGO Production.xlsx]Sheet1 | ||||||

Report Created: 11/18/2022 3:08:50 PM | ||||||

Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||

Solver Engine | ||||||

Engine: Simplex LP | ||||||

Solution Time: 0.016 Seconds. | ||||||

Iterations: 3 Subproblems: 0 | ||||||

Solver Options | ||||||

Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||

Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||

Objective Cell (Max) | ||||||

Cell | Name | Original Value | Final Value | |||

$B$22 | Profit Santa's Grotto | $ 2,001,000 | $ 2,001,000 | |||

Variable Cells | ||||||

Cell | Name | Original Value | Final Value | Integer | ||

$B$18 | Production Quantity Santa's Grotto | 18000 | 18000 | Contin | ||

$C$18 | Production Quantity Advent Calendar | 60000 | 60000 | Contin | ||

Constraints | ||||||

Cell | Name | Cell Value | Formula | Status | Slack | |

$B$27 | Production Quantity Santa's Grotto | 18000 | $B$27<=$B$28 | Not Binding | 2000 | |

$C$27 | Production Quantity Advent Calendar | 60000 | $C$27<=$C$28 | Binding | 0 | |

$B$31 | Molding Machine-hours used | 300 | $B$31<=$D$31 | Binding | 0 | |

$B$32 | Packing Machine-hours used | 270 | $B$32<=$D$32 | Not Binding | 30 |

## Sheet1

Input Parameters | |||

Santa's Grotto | Advent Calendar | ||

Maximum Demand | 20000 | 60000 | |

Selling Price | $55 | $35 | |

Material Cost | $15 | $8 | |

Molding machine-hours required | 0.01 | 0.002 | |

Packing machine-hours required | 0.005 | 0.003 | |

Machine-hours available | Cost per machine-hour | ||

Molding | 300 | $500 | |

Packing | 300 | $800 | |

Decisions | |||

Santa's Grotto | Advent Calendar | ||

Production Quantity | 18000 | 60000 | |

Objective | |||

Profit | $ 2,001,000 | ||

Constraints | |||

Santa's Grotto | Advent Calendar | ||

Production Quantity | 18000 | 60000 | <= |

Maximum Demand | 20000 | 60000 | |

Machine-hours used | Machine-hours available | ||

Molding | 300 | <= | 300 |

Packing | 270 | <= | 300 |

Calculations | |||

Santa's Grotto | Advent Calendar | ||

Selling Price | $55 | $35 | |

Material Cost | $15 | $8 | |

Assembly Machine-Hour Cost | $5.00 | $1 | |

Packing Machine-Hour Cost | $2.50 | $2.40 | |

Margin per Unit | $ 33 | $ 24 | |