The Wyndor Glass Co estimates of the unit profits for teh two new products are $

Question

The Wyndor Glass Co estimates of the unit profits for teh two new products are $600 for teh doors and $300 for the windows. Formulate and solve the linear programming model for this problem on a spreadsheet.

The answer is in Chegg Textbook solutions but need the excel with formulas in a spreadsheet.

1 Wyndor Glass co. Product-Mix Problem Windows Doors $600 $300 Unit Profit Hours 6 Hours Used per Unit Produced used 7 plant 1 plant 2 plant 3 18 Windows Doors Unit Produced Hours Available 12 18 Total Profit $3,300

Explanation / Answer

Hi,

In order to solve this in Excel you need to have the Solver add-in installed. If you are using a Mac, then you will find it under Tools>Add-ins>Solver.Xlam. Once you have solver installed, then we can use the information in hand to maximize the profit. I am using the exact cell numbers in this spreadsheet in order to explain it you. You will need to change the cell numbers accordingly if you are working on a seperate spreadsheet.

You need to open Solver. You will find it under Tools. Once you have opened Solver, under 'Set Objective' you need to select cell G12 which shows the total profit. It should show you $G$12. You need to set it to Max. Under 'By Changing Variable Cells', you need to select cells C12 and D12. It should show you $C$12:$D$12.

Then under 'Subject to the Constraints', click on Add. Under 'Cell Reference', select E7. It should show $E$7. The sign should be set to <=. Under 'Constraint', select G7. It should show $G$7. Click on Add. You will need to do this 2 more times as there are two other plants as well. Under 'Cell Reference', select E8. It should show $E$8. The sign should be set to <=. Under 'Constraint', select G8. It should show $G$8. Click on Add. One last time, under 'Cell Reference', select E9. It should show $E$9. The sign should be set to <=. Under 'Constraint', select G9. It should show $G$9. Click on Add. Now all three constraints have been added. Click Cancel to go back to previous menu.

You will now see three constraints under the 'Subject to the Constraints' option. They will be $E$7<=$G$7, $E$8<=$G$8 and $E$9<=$G$9 respectively. Make sure that the 'Make Unconstrained Variables Non-Negative' option is checked. The solving method should be selected as GRG Nonlinear.

Once everything is in place, click on Solve. It will give you your answers; Doors 4 and Windows 3.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.