Creative Constructs, Inc. makes three types of doll furniture from high-quality recycled lumber (from demolished ivarns, houses and other buildings). They sell directly to retailers who in turn sell to the final customer. Demand for their products has been increasing in recent months. Each piece of furniture made must be processed through two departments (1) cutting and (2) finishing before it is sold. The sales prices and costs of inputs can be found in the table below. In addition to the information iselow, Creative Constructs has only been able to obtain 500 board feet of high-quality, recycled lumber per week. In addition, the labor used in both the cutting and assembly departments is highly-skilled and difficult to hire and train, which limits the hours available. Currently, there is a maximum of 200 labor hours available per week in cutting and 250 labor hours available in finishing No overtime is authorized by the owners of the company to make sure all employees are well rested and less stressed so they can produce quality products. OBJECTIVE: Determine which mix of products maximizes weekly profits (short-term solution) and discuss options to alleviate the constraints in the long-term. INSTRUCTIONS 1. Input the following data into an Excel Spreadsheet: CHAIRS $24.00 6.00 BEDS $29.00 7.00 SOFAS $37.00 9.00 Selling price DM per unit (lumler) Conversion cost per unit Contribution Margin 11.32 12.40 14.96 6.68 2.60 13.04 1.5 2 3 0.8 DM per unit in board feet Labor hrs - Cutting Labor hrs- Assembly & Finishing DEMAND 0.5 1.2 0.6 1.3 1.6 No more than 150 per week No more than 100 per week No more than 75 per week 2. Using Excel Solver find the optimal product mix Creative Constructs should produce in the coming period. Make sure all dollar figures have two decimals and quantities are founded to the nearest whole number. Copy the Excel worksheet into a Word Document. 3. In the document identify which constraint is the biggest problem and address the question of what options Creative Constructs might consider to alleviate the constraints in the long-term