Using Excel Solver to Solve Linear Programming Models BSTA 320 / 325 Chapter 2.8 Solving with Excel Solver Let be the number of necklaces to make, and Let be the number of bracelets to make. Maximize: = 300 + 400y Subject to: 3 + 2 18 2 + 4 20 0, 0 ( amount of gold available) ( amount of platinum available) (non-negativity) Excel Solver Solution We begin by entering the following into Excel. Notice we don't input the non-negativity constraints. We need these empty cells here. Type these formulas in Cells B3 and C3 (empty right now) are where Excel Solver will put the optimal number of necklaces () and bracelets () after it has solved the model. SUMPRODUCT multiplies cells together and adds them up. This equation will end up multiplying 3 + 2. In other words, it will calculate the amount of gold used. Using Excel Solver Now we're ready to solve. Click on the Data tab, and click Solver. If you don't see Excel Solver on your computer, do this to add it: Click File, Options, Add-Ins You'll see this near the bottom: Click Go Check off Excel Solver and press OK After starting Solver, you'll see this pop up If you have an older version of Excel, this might look slightly different. Please see textbook pages 55-57 for how to deal with the older version. Set the objective function In Solver: Beside \"Set Objective\BSTA 320 Section N1A Assignment 2 Winter 2020 Assignment 2 - Version 1: Linear Programming (Chapters 2, 3) Total marks: 15 marks Weight: 5% Due Date: Friday, March 27, 2020 Computer Tool: Excel Solver Add-In. Submit through Blackboard assignment submission. Assignment can be done individually or in groups of 2 or 3. The Okatoks Feed Company makes a feed mix from four ingredients: oats, corn, soybeans, and a vitamin supplement. The company has 150 pounds of oats, 400 pounds of corn, 200 pounds of soybeans, and 100 pounds of vitamin supplement available for the mix. The company has the following specifications for the mix. No more than 30% of the mix can be soybeans At least 20% of the mix must be the vitamin supplement The ratio of corn to oats cannot exceed 2 to 1 The amount of oats cannot exceed the amount of soybeans The mix must be at least 500 pounds A pound of oats costs $0.50; a pound of corn, $0.90; a pound of soybeans, $1.30; and a pound of vitamin supplement, $2.00. The feed company wants to know the number of pounds of each ingredient to put into the mix in order to minimize the cost. 1. Develop the appropriate linear programming (LP) model. Write the model out and include decision variables, objective function and constraints. (5 marks) 2. Solve the LP model using Excel Solver. Submit your Excel file, and show your Excel solution and the Answer Report. (8 marks) 3. Make a written recommendation to Okatoks of the amount of each ingredient that should be produced to make up the mix, and also indicate the cost of the mix. (2 marks) Microsoft Excel 16.0 Answer Report Worksheet: [Excel solver examples - completed.xlsx]Neck and brac Report Created: 3/12/2020 10:22:22 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.015 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name $D$4 Profit Original Value Final Value 2400 2400 Variable Cells Cell Name $B$3 Number of units Necklaces $C$3 Number of units Bracelets Original Value Final Value Integer 4 4 Contin 3 3 Contin Constraints Cell Name $D$6 Gold Avail $D$7 Platinum Available Cell Value Formula Status Slack 18 $D$6=$G$8 0 $E$9= 0