David, LaDeana, and Lydia are the sole partners and workers in a company that produces fine clocks. David and LaDeana are each available to work a maximum of 40 hours per week at the company, while Lydia is available to work a maximum of 20 hours per week. The company makes two different types of clocks: a grandfather clock and a wall clock. To make a clock, David (a mechanical engineer) assembles the inside mechanical parts of the clock while LaDeana (a woodworker) produces the hand-carved wood casings. Lydia is responsible for taking orders and shipping the clocks. The amount of time required for each of these tasks is shown next Unit Profit Assembly (David) Carving (Labeana) Shipping (Lydia) Grandfather Clock $300 Wall Clock $200 Available (in hours). 40 Time Required (in hours) Click here for the Excel Data File 40 20 Each grandfather clock built and shipped yields a profit of $300, while each wall clock yields a profit of $200 The three partners now want to determine how many clocks of each type should be produced per week to maximize the total profit. a. Formulate and solve this model on a spreadsheet, and then generate a sensitivity report to fill in the information below. Note: If an allowable increase/decrease is 10,000 or larger, simply enter 10,000. Round all decimal values to 3 decimal places. View previous atter Name Final Value Reduced Cost Objective Coefficient Allowable Increase Allowable Decrease Assembly (David) used 0.000 200 Carving (LaDeana) used 0.000 300 Constraint Final Shadow Name Right-Hand Value Price Allowable Increase Allowable Decrease Side Resource 1 Used 40 Resource 2 Used 40 3 at 1 Navi a. Formulate and solve this model on a spreadsheet, and then generate a sensitivity report to fill in the information below. Note: If an allowable increase/decrease is 10,000 or larger, simply enter 10,000. Round all decimal values to 3 decimal places. Name Final Value Reduced Cost Objective Coefficient Allowable Increase Allowable Decrease Assembly (David) used 0.000 200 Carving (LaDeana) used 0.000 300 Constraint Final Shadow Name Right-Hand Value Price Allowable Increase Allowable Decrease Side Resource 1 Used 40 Resource 2 Used 40 Shipping (Lydia) used 20 b. Determine the total profit. Total profit To increase the total profit, the three partners have agreed that one of them will slightly increase the maximum number of hours available to work per week. The choice of which one will be based on which one would increase the total profit the most. Use the sensitivity report to make this choice. (Assume no change in the original estimates of the unit profits.) c. Who should increase their hours? Who should increase their hours? Lydia Paste A1 AT LB AA A B C D E F G H BI UV xfx 12 Grandfather Wall Clock Clock 34 Unit Profit $300 $200 168557 Time Required Assembly (David) 6 4 Hours: Available 40 7 Carving (LaDeana) 8 4 40 Shipping (Lydia) 3 3 20 9 11 22 12