- Date:29 Jun 2020
- Views:11
- Downloads:0
- Pages:10
- Size:751.14 KB

Transcription:

Introduction to Excel Solver 1 of 2, Excel has the capability to solve linear and often nonlinear programming. problems with the SOLVER tool which, May be used to solve linear and nonlinear optimization problems. Allows integer or binary restrictions to be placed on decision variables. Can be used to solve problems with up to 200 decision variables. SOLVER is an Add In program that you will need to load in Excel. Microsoft users, 1 Click the Microsoft Office Button and then click Excel Options. 2 Click Add Ins and then in the Manage box select Excel Add ins and click Go. 3 In the Add Ins available box select the Solver Add in check box and then click OK. If Solver Add in is not listed in the Add Ins available box click Browse to locate it. If you get prompted that Solver is not currently installed click Yes to install it. 4 After you load Solver the Solver command is available in the Analysis group on the Data tab. 1 Open Excel for Mac 2011 and begin by clicking on the Tools menu. 2 Click Add Ins and then in the Add Ins box check Solver xlam and then click OK. 3 Restart Excel for Mac 2011 fully quit the program select the Data tab then select Solver to. Introduction to Excel Solver 2 of 2, There are 4 steps on how to use SOLVER to solve an LP. The key to solving an LP on a spreadsheet is, Set up a spreadsheet that tracks everything of interest e g costs profits.

resource usage, Identify the cell that contains the value of your objective function as the. Target Cell, Identify the decision variables that can be varied called Changing. Variable Cells, Identify the constraints and enter them into the program to tell SOLVER how. to solve the problem, At this point the optimal solution to our problem will be placed on the. spreadsheet with its value in the target cell,Diet Problem Set Up 1 of 7.

Problem Statement, Consider the problem of diet optimization based on cost and different. nutritional factors, There are four different types of food Brownies Ice Cream Cola and. Cheese Cake with nutrition values and cost per unit as follows. Brownies Ice Cream Cola Cheese Cake,Calories 400 200 150 500. Chocolate 3 2 0 0,Sugar 2 2 4 4,Fat 2 4 1 5,Cost 0 50 0 20 0 30 0 80. Find a minimum cost diet that contains,at least 500 calories.

at least 6 grams of chocolate,at least 10 grams of sugar. at least 8 grams of fat,Diet Problem Set Up 2 of 7. First we must format our spreadsheet correctly to be entered into SOLVER. Identify the decision variables changing cells, To begin we enter heading for each type of food in B2 E2. In the range B3 E3 we input random trial values for the amount of each food. eaten any values will work but at least one should be positive. In the example shown below we indicate that we are considering eating 3. brownies 0 scoops of chocolate ice cream 1 bottle of cola and 7 pieces of. pineapple cheesecake,1 DECISION VARIABLES,2 Brownies Ice Cream Cola Cheese Cake. 3 Eaten 3 0 1 7,Diet Problem Set Up 3 of 7,Write and enter objective function target cell.

To see if the diet is optimal we must determine its cost as well as the calories. chocolate sugar and fat it provides, In the range B7 E7 we reference the number of units and in B8 E8 we input the. per unit cost for each available food, We compute the cost of the diet in cell B10 with the formula. B7 B8 C7 C8 D7 D8 E7 E8,But it is usually easier to enter the formula. SUMPRODUCT B7 E7 B8 E8, And this is much easier to understand for anyone reading the spreadsheet. The SUMPRODUCT function requires two ranges as inputs. The first cell in range 1 is multiplied by the first cell in range 2 then the. second cell in range 1 is multiplied by the second cell in range 2 and so on. All of these products are then added, Thus in cell B10 the SUMPRODUCT function computes total cost as.

3 50 0 20 1 30 7 80 740 cents,Diet Problem Set Up 4 of 7. Now the spreadsheet should look like,1 DECISION VARIABLES. 2 Brownies Ice Cream Cola Cheese Cake,3 Eaten 3 0 1 7. 5 OBJECTIVE FUNCTION,6 Brownies Ice Cream Cola Cheese Cake. 7 Eaten B3 C3 D3 E3,8 Cost 50 20 30 80,10 Total 740 SUMPRODUCT B7 E7 B8 E8.

Diet Problem Set Up 5 of 7, Finally we must set up the given problem constraints for calories. chocolate sugar and fat, To begin we recreate the table in Excel that defines how many calories and units. of chocolate sugar and fat are in each type of dessert. We can use this information to calculate total amounts based on the. quantities of different decision variables, Next take the SUMPRODUCT of the number of items with the calories in each. to calculate total calories in our dessert selection. SUMPRODUCT B7 E7 B14 E14, Finally indicate the limitations highlighted in the problem. Add a or to identify maximum versus minimum constraints in Column. G and use Column H to indicate those limits,Diet Problem Set Up 6 of 7.

The formulas will look like,A B C D E F G H, 13 Brownies Ice Cream Cola Cheese Cake Totals Required. 14 Calories 400 200 150 500 SUMPRODUCT B 7 E 7 B14 E14 500. 15 Chocolate 3 2 0 0 SUMPRODUCT B 7 E 7 B15 E15 6,16 Sugar 2 2 4 4 SUMPRODUCT B 7 E 7 B16 E16 10. 17 Fat 2 4 1 5 SUMPRODUCT B 7 E 7 B17 E17 8, The constraint values that will show up on your screen look like. A B C D E F G H, 13 Brownies Ice Cream Cola Cheese Cake Totals Required. 14 Calories 400 200 150 500 4850 500,15 Chocolate 3 2 0 0 9 6.

16 Sugar 2 2 4 4 38 10,17 Fat 2 4 1 5 42 8,Diet Problem Set Up 7 of 7. The complete LP to be entered into SOLVER now looks like. A B C D E F G H,1 DECISION VARIABLES,2 Brownies Ice Cream Cola Cheese Cake. 3 Eaten 3 0 1 7,5 OBJECTIVE FUNCTION,6 Brownies Ice Cream Cola Cheese Cake. 7 Eaten 3 0 1 7,8 Cost 50 20 30 80,10 Total 740,12 CONSTRAINTS. 13 Brownies Ice Cream Cola Cheese Cake Totals Required. 14 Calories 400 200 150 500 4850 500,15 Chocolate 3 2 0 0 9 6.

16 Sugar 2 2 4 4 38 10,17 Fat 2 4 1 5 42 8,Diet Problem Dialog Box 1 of 6. Now we need to enter the LP into SOLVER,click on Data Solver to get this box. We need to fill in each of the components of,the Parameters Dialog Box. 1 Identify the cell that contains the value of,your objective function as the Target Cell. Fill in the Set Objective box by clicking on the cell in our spreadsheet that. calculates our objective function in this case B10. Use the buttons to identify the type of problem we are solving a Max or. Min here we want to minimize total cost so select Min. Diet Problem Dialog Box 2 of 6, 2 Identify the decision variables that can be varied called Changing Cells or.

Variable Cells,Click into the By Changing Variable Cells box. Select the decision variable cells of our LP which are B3 E3. SOLVER now knows that it can change the number of brownies scoops of ice. cream sodas and pieces of cheese cake to reach an optimal solution. Diet Problem Dialog Box 3 of 6, 3 Identify the constraints and enter them into the program. Click on the Add a constraint button and a box will appear that allows us to add. our constraints, We can use the Cell Reference box to input the totals for each constraint that. we calculated, Using Calories as an example we would click on Cell F14 which computed. the total calories from all our desserts, There are several options for constraint type int integer bin binary.

or dif all different, After adjusting the constraint type to be greater than or equal to click. on the cell referencing the minimum quantity permitted Cell H14. Note Instead of a reference we can also enter a specific number. The complete constraint looks as follows,Diet Problem Dialog Box 4 of 6. The Add button will allow us to include all the,other constraints to SOLVER. Instead of entering each constraint individually,you can add them all at once. In the Cell Reference box and Constraint box you can also specify an array of. cell references if both the Cell Reference and Constraint are specified using an. array of cell references the length of the arrays must match and Solver treats. this constraint as n individual constraints where n is the length of each array. We have now created four constraints, SOLVER will ensure that the changing cells are chosen so F14 H14.

F15 H15 F16 H16 and F17 H17, The Change button allows you to modify a constraint already entered and. Delete allows you to delete a previously entered constraint. Diet Problem Dialog Box 5 of 6,The final SOLVER Parameters Dialog Box. Note the checked box titled Make Unconstrained,Variables Non Negative allows us to capture non. negativity constraints all variables will be,constrained to be 0. Additionally you should change the Select a,Solving Method to SIMPLEX LP when you are.

solving a linear program,Finally click Solve for your solution. The Parameters Dialog Box will close and decision,variables will change to the optimal solution. A B C D E F G H,1 DECISION VARIABLES,Cheese Cake,Note because we referenced. 5 OBJECTIVE FUNCTION,these cells in all our, 6 Brownies Ice Cream Cola Cheese Cake calculations the objective. 7 Eaten 0 3 1 0,8 Cost 50 20 30 80 function and constraints will.

10 Total 90 also change,12 CONSTRAINTS, 13 Brownies Ice Cream Cola Cheese Cake Totals Required. 14 Calories 400 200 150 500 750 500,15 Chocolate 3 2 0 0 6 6. 16 Sugar 2 2 4 4 10 10 16,17 Fat 2 4 1 5 13 8,Diet Problem Dialog Box 6 of 6. The Parameters Dialog Box also has a number of options on how to. calculate solutions,Constraint Precision is the degree of. accuracy of the Solver algorithm for example,how close does the value of the LHS of a.

constraint have to be before it is considered,equal to the RHS. Max Time allows you to set the number of,seconds before Solver will stop. Iterations similar to Max Time allows you to,specify the maximum number of steps of the. Solver algorithm takes before stopping,If you want to learn about other options in. SOLVER please reference the SOLVER,www solver com,Food Start Up Problem 1 of 2.

Problem Statement, You create a start up company that caters food directly to customers You want. to allocate production capabilities to devise a feasible daily production plan that. maximizes your profit, There are three kinds of food that you order at this early stage of the company. Hummus H a Moussaka M and a Tabouleh T Each meal has to be cooked. packaged and delivered you estimate that total available cooking hours is 4. packaging hours 2 and delivery hours 2, Hummus for 10 portions requires 1 hour of time packaging is done at the rate of 20. portions per hour and delivery at the rate of 30 per hour Ingredients for 1 portion cost. 1 and each packaged portion can be sold for 7, In 1 hour the food cooking team can prepare 5 portions of Moussaka packaging is. done at the rate of 15 per hour and 15 portions can be delivered in 1 hour. Ingredients for 1 portion cost 2 and it can be sold for 12. Finally Tabouleh can be prepared at the rate of 15 portions per hour packaged at 25. portions per hour and delivered at 30 per hour one portion only costs 0 5 in raw. ingredients and can be sold for 5, Customers expressed interest in having the following products delivered every day 20.

Hummus meals 10 Moussaka meals and 30 Tabouleh meals. Solve this in Excel on your own,Food Start Up Problem 2 of 2. The solution to this problem is,A B C D F G H,1 DECISION VARIABLES. 2 Hummus Moussaka Tabouleh,3 Orders 8 6 30,5 COST AND OR PROFIT DATA. 6 Hummus Moussaka Tabouleh,7 Orders 8 6 30,8 Pro t 6 10 4 5. 9 OBJECTIVE FUNCTION,10 Total 243,12 CONSTRAINTS,13 Hummus Moussaka Tabouleh Totals Maximum.

14 Cooking 0 100 0 200 0 067 4 000 4,15 Packaging 0 050 0 067 0 040 2 000 2. 16 Delivery 0 033 0 067 0 033 1 667 2,17 Demand H 8 20. Excel Solver 1 Table of Contents Introduction to Excel Solver slides 3 4 Example 1 Diet Problem Set Up slides 5 11 Example 1 Diet Problem Dialog Box slides 12 17 Example 2 Food Start Up Problem slides 18 19 2 Note that there is an Excel file that accompanies this tutorial each worksheet tab in the Excel corresponds to each example problem 10 3 17 2 Introduction to Excel

Recent Views:

- Chapter 2 introduction to middleware technologies ppt
- Marque pioneer reference bcs 727 codic 3611531
- 2013 14
- Ramp test set nav 402ap 3 avionteq
- Plasma tv service manual espec
- Grade 5 mississippi department of education
- Year 9 course and assessment information 2018
- Ta60 diebold nixdorf
- Of engineering publications 2019
- Biblical adobe