Problem 1 The Hickory Cabinet and Furniture Company produces sofas, tables, and chairs. The plant uses three main resources to make furniture - wood, upholstery, and labor. The resource requirements for each piece of furniture and the total resources available weekly are as follows. Resource Requirements Wood (lb) Upholstery (yd) Labor (hr) Sofa 7 12 6 Table NA 9 Chair 7 5 Total Available Resources 2250 1000 240 The furniture is produced on a weekly basis and stored in the warehouse until the end of the week, when it is shipped out. The warehouse has a total capacity of 650 pieces of furniture. Each sofa earns $300 in profit, each table $200, and each chair $150. The company wants to know how many pieces of each type of furniture to make per week to maximize profit. The worksheet named "Hickory 1" contains a template for solving this LP problem. a. (6 Points) In cells B4:D7, enter the resource requirement for each of the four constraints. b. (4 Points) In cells G4:G7, enter the appropriate resource limitation values. c. (4 Points) In cells E4:E7, use the "SUMPRODUCT" function to write a formula for the amount of each resource used. Be sure to reference the appropriate cells. d. (4 Points) In cells F4:F7, write the type of inequality associated with each constraint using either "=", or "=". e. (3 Points) In cells B10:D10, enter the profit coefficients for the objective function. f. (3 Point) In cell B13, use the Excel's built-in function named "SUMPRODUCT" to write a formula for the objective function. Be sure to reference the appropriate cells. g. (7 points) Setup Excel Solver to use it to solve the model for the maximum profit. Keep the optimal solution returned by Solver in the model.B3 X1 Sofa A B C D E F G H Problem 1 Constraint Amount Resource 3 Coefficients X1 Sofa X2 Table X3 Chair Used Limitation Wood Upholstery Labor Warehouse Capacity 8 9 X1 Sofa X2 Table X3 Chair 10 Profit Coefficients 11 Decision Variables 12 13 Max Profit 14 15 16 17 > Hickory 1 Hickory 2 Investment 1 | Investment 2 +29-0-2023 10.5$ - Excel Shivam Kumar SK X File Home Insert Page Layout Formulas Data Review View Help Fuzzy Lookup Power Pivot Tell me what you want to do AutoSum ~ & Cut EX Calibri - 11 - A A = al Wrap Text Accounting AY O Fill Copy Sort & Find & Solver Paste BIU HA- Merge & Center $ % " Conditional Format as Cell Insert Delete Format Formatting " Table Styles ~ Clear Format Painter Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Solver F8 X V fx 650 A B C D E F G H K L - Solver X 1 N XI Sofa X2 Table X3 Chair Amount Used Resource Limitation 5 Wood 7.00 5.0 4.00 280.00 2,250.00 Objective (Maximize $B$14 ) 6 Upholdstry 12.00 7.0 480.00 1,000.00 7 Labor 6.00 9.00 5.0 240.00 240.00 Variables ($B$11:$D$11) Warehouse Capacity 1.00 1.00 1.00 40.00 650.00 9 10 Profit Co-efficient 300.00 200.00 150.00 Constraints ($E$5