[Solved] Modeling Analytics

Assignment Prompt & Instructions

Problem 1 Solve the LOGO Production problem (the base version with only two products — see attached [Download Excel file](https://georgetown.instructure.com/courses/187127/files/12542251/download?download_frd=1)) 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. \ LOGOProductionComplete.xlsx](https://www.sweetstudy.com/files/logoproductioncomplete-xlsx "LOGOProductionComplete.xlsx")