Question

You oversee a furniture manufacturing operation, which requires three processes: carpentry, finishing and upholstery. You can produce three products: chairs, sofas and loveseats. The below table shows the profit and labor hours required in each process: You are to decide how many chairs, sofas and loveseats to manufacture to maximize the profit of your company. (Partial units can be manufactured.) You are constrained by only having 96 hours of carpentry, 22 hours of finishing and 72 hours of upholstery available. You must produce at least one of every furniture type What is the profit? Use Solver to to optimize this related set of equations. This is a linear programming/optimization problem. Follow the pattern of solution shown in the video to solve this problem.

Process		Chairs	Sofas	Loveseats
Profit	$/unit	$55.00	$85.00	$75.00
Carpentry	hrs/unit	4.5	5.5	4.85
Finishing	hrs/unit	0.95	1.55	1.25
Upholstery	hrs/unit	3.15	5.25	4.7

Answer 1

HomeInsertPage Layout Formulas DataReview View Show DetailSolver Connections - Hide Detail Reapply operties Sr rvancedolumns Duplicates Validation From From From From Otherxisting Refresh Access Web Text SourcesConnections AllEdit Links Text to Remove Dt Consolidate What-If Group Ungroup Subtotal nalysis Get External Data Connections Sort & Filter Data Tools Analysis A B 0 Formula in G column ChairsSof Loveseats Max Profit Profit 85 75 D4 D5+E4 E5+F4 F5 Optimize values constrained 4.5 5.5 4.85 D6 D5+E6 E5+F6 F5 0.95 1.55 1.25 4.7 Upholstery 3.15 5.25 72 10 1.Set the problem as shown and use formulas in G column as shown 12. Click on the solver in the Data Tab 13 15 16

Solver Parameters Set Objective: ChairsSofasLoveseats Max Profit ariable Cells: 75 Profit Optimize values constrained 5.5 4.85 Finishing 0.95 1.55 Upholstery 3.15 5.25 72 4.7 3. Set objective cell G4 and select to Max 3 4. By changing variable cells: select D4 E4 F4 5. Add constraint by clicking on Add 6. then click on Solve Reset A Mase Unconstrained Variables Non-Negative 16 17 Slect a Solving Method: GRG Noninear Solving Method 19 Select the GRG Noninear engine for Solver Problems that are smooth noninear. Select the LP Simplex engine for lnear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth 20 23 HParetoGantt Chart Gantt and ES FSABC Analysis Simplex Drikta Sheeti Expon

A B Formula in G column 2 Chairs Sofas Loveseats Max Profit Profit D4 D5+E4 E5+F4 F5 4 1234.285709 Optimize values17.14286 3.42857 Carpentry constrained 4.85 D6 D5+E6 E5+F6 F5 5.5 95.99999981 < Finishing 0.95 1.55 1.25 21.5999999 < Upholste D8 D5+E8 E5+F8 F5 3.15 5.25 4.7 71.99999964 <- 72 Maximum profit is $ 1234.285 12 for Chairs 17.14286, Sofas 3.42857 and loveseats is 0 10 13 14 15 16