6 David, Diana and Lydia are the sole partners and workers in a company that produces fine clocks David and Diana 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 Diana (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 below Task Time required Grandfather clock Wall clock Assemble clock mechanism Carve wood casing Ship finished clock 6 hours 8 hours 3 hours 4 hours 4 hours 3 hours 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 The Answer and Sensitivity Reports on the following page may be of use when answering some or all of these questions (a) Write an equation for the objective function of this model (b) Using the Solver output below, list the constraints contributing to the optimal solution (c) Using the Solver output below, describe the linear programming solution for this situation (d) Grandfather clocks have become very popular and the partners feel that a unit profit per grandfather clock of $375 may be possible Would the solution shown above still be optimal Which of the EXCEL reports helps you answer this question Justify your answer carefully How would the total profit change, if at all (e) If Lydia becomes available to work 25 hours per week, what impact, if any, will this have on the weekly profit Will the optimal solution change 2 Answer Report Target Cell (Max) Name Original Value Final Value Profit$1,666 67$1,666 67 Adjustable Cells Name Original Value Final Value G3 3333333333 333333333 W3 3333333333 333333333 Constraints Name Cell Value Formula Status Slack JB Assembly LHS33 33333333$B$17 $D$20Not Binding3 3333 JB Non negativityx2 LH3S 333333333$B$21 $D$21Not Binding3 3333 Sensitivity Report Adjustable Cells Final Reduced Objective Allowable Allowable Name Value Cost Coefficient Increase Decrease G3 33330300100100 W3 3333020010050 Constraints Final Shadow Constraint Allowable Allowable Name Value Price R H Side Increase Decrease JB Assembly LHS33 3330401 00E 306 66666667 JB Carving LHS40254013 3333313 3333333 JB Shipping LHS2033 333320105 JB Non negativity x1 LHS3 3333003 3333331 00E 30 JB Non negativity x2 LHS3 3333003 3333331 00E 30

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 06, 2024

6. David, Diana and Lydia are the sole partners and workers in a company that produces fine clocks. David and Diana are each available to

6. David, Diana and Lydia are the sole partners and workers in a company that produces fine clocks. David and Diana 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 Diana (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 below.

TaskTime requiredGrandfather clockWall clock

Assemble clock mechanism Carve wood casing Ship finished clock

6 hours

8 hours 3 hours

4 hours

4 hours 3 hours

image text in transcribed

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.

The Answer and Sensitivity Reports on the following page may be of use when answering some or all of these questions.

(a) Write an equation for the objective function of this model.

(b) Using the Solver output below, list the constraints contributing to the optimal solution.

(d) Grandfather clocks have become very popular and the partners feel that a unit profit per grandfather clock of $375 may be possible. Would the solution shown above still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the total profit change, if at all?

(e) If Lydia becomes available to work 25 hours per week, what impact, if any, will this have on the weekly profit? Will the optimal solution change?

Answer Report

Target Cell (Max) NameOriginal ValueFinal Value Profit$1,666.67$1,666.67 Adjustable Cells NameOriginal ValueFinal Value G3.3333333333.333333333 W3.3333333333.333333333 Constraints NameCell ValueFormulaStatusSlack JB_Assembly LHS33.33333333$B$17=$D$20Not Binding3.3333 JB_Non-negativityx2 LH3S.333333333$B$21>=$D$21Not Binding3.3333

Sensitivity Report

Adjustable Cells FinalReducedObjectiveAllowableAllowable NameValueCostCoefficientIncreaseDecrease G3.33330300100100 W3.3333020010050 Constraints FinalShadowConstraintAllowableAllowable NameValuePriceR.H. SideIncreaseDecrease JB_Assembly LHS33.3330401.00E+306.66666667 JB_Carving LHS40254013.3333313.3333333 JB_Shipping LHS2033.333320105 JB_Non-negativity x1 LHS3.3333003.3333331.00E+30 JB_Non-negativity x2 LHS3.3333003.3333331.00E+30