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 | |