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

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.