259 Lecture 8 Spring 2013

1. 259 Lecture 8 Spring 2013 Optimization with the Excel Solver

2. Optimization Problems • Many problems involve optimization of a function by making an appropriate choice of inputs to get the best possible output. • Examples include: • Maximizing an area enclosed by a fence. • Minimizing error in an approximating function. • Maximizing office storage space.

3. Example 1 • A farmer has 2400 feet of fencing and wants to fence off a rectangular field that borders a straight river. Find the dimensions of the fence to maximize the area enclosed by the fence. y x River

4. Example 2 • Find a line of the form Y = a*x+b that minimizes the sum of the squares for error, i.e. minimizes the function (yi-Yi)2 for i = 1,2, … n.

5. Example 3 • An office manager needs to purchase new filing cabinets. At the local superstore Office Min, Ace cabinets cost $40 each, require 6 square feet of floor space, and hold 24 cubic feet of files. On the other hand, each Excello cabinet costs $80, requires 8 square feet of floor space, and holds 36 cubic feet. The manager’s budget permits spending no more than $560 on files, while the office has space for no more than 72 square feet of cabinets. The manager desires the greatest storage capacity within the limitations imposed by funds and space. How many of each cabinet should be purchased?

6. Excel’s Solver Add-In • One of the tools in Excel that can be used for optimization problems is the Solver. • Click the Microsoft Office Button, and then click Excel Options. • Click the Add-Ins category. • In the Manage box, click Excel Add-ins, and then click Go. • Check the Solver Add-in box and choose OK. • You may need to use you Microsoft Office installation disk for this step. • Once loaded, the Solver can be accessed from the Data tab’s Analysis group.

7. The Solver Parameters Dialog Box • Set Target Cell - Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. • This cell must contain a formula. • Equal to - Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. • If you want a specific value, type it in the box. • By Changing Cells - Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target. • The adjustable cells must be related directly or indirectly to the target cell. • Guess - Guesses all nonformula cells referred to by the formula in the Set Target Cell box, and places their references in the By Changing Cells box.

8. The Solver Parameters Dialog Box (cont.) • Subject to the Constraints - Lists the current restrictions on the problem. • Add - Displays the Add Constraint dialog box. • Change - Displays the Change Constraint dialog box. • Delete - Removes the selected constraint. • Solve - Starts the solution process for the defined problem. • Close - Closes the dialog box without solving the problem. • Retains any changes you made by using the Options, Add, Change, or Delete buttons. • Options - Displays the Solver Options dialog box, where you can load and save problem models and control advanced features of the solution process. • Reset All - Clears the current problem settings, and resets all settings to their original values.

9. The Solver Options Dialog Box • You can control advanced features of the solution process, load or save problem definitions, and define parameters for both linear and nonlinear problems. • Each option has a default setting that is appropriate for most problems.

10. The Solver Options Dialog Box • Max time - Limits the time taken by the solution process. • While you can enter a value as high as 32,767, the default value of 100 seconds is adequate for most small problems. • Iterations - Limits the time taken by the solution process by limiting the number of interim calculations. • While you can enter a value as high as 32,767, the default value of 100 is adequate for most small problems. • Precision - Controls the precision of solutions by using the number you enter to determine whether the value of a constraint cell meets a target or satisfies a lower or upper bound. • Precision must be indicated by a fractional number between 0 (zero) and 1. • Higher precision is indicated when the number you enter has more decimal places — for example, 0.0001 is higher precision than 0.01.

11. The Solver Options Dialog Box (cont.) • Tolerance - The percentage by which the target cell of a solution satisfying the integer constraints can differ from the true optimal value and still be considered acceptable. • This option applies only to problems with integer constraints. • A higher tolerance tends to speed up the solution process. • Convergence - When the relative change in the target cell value is less than the number in the Convergence box for the last five iterations, Solver stops. • Convergence applies only to nonlinear problems and must be indicated by a fractional number between 0 (zero) and 1. • A smaller convergence is indicated when the number you enter has more decimal places — for example, 0.0001 is less relative change than 0.01. • The smaller the convergence value, the more time Solver takes to reach a solution.

12. The Solver Options Dialog Box (cont.) • Assume Linear Model - Select to speed the solution process when all relationships in the model are linear and you want to solve a linear optimization problem. • Assume Non-Negative - Causes Solver to assume a lower limit of 0 (zero) for all adjustable cells for which you have not set a lower limit in the Constraint box in the Add Constraint dialog box. • Use Automatic Scaling - Select to use automatic scaling when inputs and outputs have large differences in magnitude — for example, when maximizing the percentage of profit based on million-dollar investments. • Show Iteration Results - Select to have Solver pause to show the results of each iteration.

13. The Solver Options Dialog Box (cont.) • Estimates - Specifies the approach used to obtain initial estimates of the basic variables in each one-dimensional search. • Tangent - Uses linear extrapolation from a tangent vector. • Quadratic - Uses quadratic extrapolation, which can improve the results on highly nonlinear problems. • Derivatives - Specifies the differencing used to estimate partial derivatives of the objective and constraint functions. • Forward - Use for most problems, in which the constraint values change relatively slowly. • Central - Use for problems in which the constraints change rapidly, especially near the limits. Although this option requires more calculations, it might help when Solver returns a message that it could not improve the solution.

14. The Solver Options Dialog Box (cont.) • Search - Specifies the algorithm used at each iteration to determine the direction to search. • Newton - Uses a quasi-Newton method that typically requires more memory but fewer iterations than the Conjugate gradient method. • Conjugate - Requires less memory than the Newton method but typically needs more iterations to reach a particular level of accuracy. Use this option when you have a large problem and memory usage is a concern, or when stepping through iterations reveals slow progress. • Load Model - Displays the Load Model dialog box, where you can specify the reference for the model you want to load. • Save Model - Displays the Save Model dialog box, where you can specify where to save the model. Click only when you want to save more than one model with a worksheet — the first model is automatically saved.

15. Example 1 • A farmer has 2400 feet of fencing and wants to fence off a rectangular field that borders a straight river. Find the dimensions of the fence to maximize the area enclosed by the fence. y x River

16. Example 1(cont.) • We wish to maximize the area A of the rectangle with width y and height x, i.e. we want to maximize the function A = xy. • Since we have 2400 ft of fence, we know that 2x + y = 2400. • Solving this constraint on amount of fence for y, we find that y = 2400 – 2x. • Substituting for y in our original area equation yields a function of just x alone to be maximized: A = x(2400 - 2x) = 2400x - 2x2. • Note that 0 ≤ x ≤ 1200. • Plot y = A(x) to get an idea of what x – value maximizes the area! y x River

17. Example 1 (cont.) • Sketching the graph of A(x) = 2400x-2x2, we see that the maximum of A(x) occurs near x = 500. • Using the Solver, we can start with an initial guess of x = 500 and try to find the choice of x to maximize the area!

18. Example 1 (cont.) • Put the initial guess 500 in cell F18 and calculate A(500) with the formula for A(x) in cell G18. • From the Data tab’s Analysis group, choose the Solver. • F18 will be our Changing Cell and G18 will be our Target Cell in the Solver! • Click on Solve.

19. Example 1 (cont.) • Solver indicates it has found a solution. • Choose “Keep Solver Solution”. • The “Answer” Option will put a report on the solution into a new worksheet. • Notice that the cells F18 and G18 have changed to the optimal solution values! • Take x=600 ft and y= 2400 – 2*600 = 1200 ft

20. Example 2 • Find a line of the form Y = a*x+b that minimizes the sum of the squares for error, i.e. minimizes the function (yi-Yi)2 for i = 1,2, … n.

21. Example 2 (cont.) • Put the given table of data into Excel and add a column labeled Y = a*x+b. • For initial guesses for a and b, use the points (1939,32800) and (1974,584000) to construct the point-slope form of the line through these points. • Thus, we can take: • a = (584000-32800)/(1974-1939) • b = 32800 - a*1939 • Using these values for a and b, fill in the column for the best-fit line Y = a*x+b and plot the actual values along with the best-fit line values. • In the cell just below the best-fit line column, use the function SUMXMY2 to compute the sums of the squares of the differences between elements in the y – column and the Y – column.

22. Example 2 (cont.) • Choose the sum of the squares for error as the Target Cell, the cells containing a and b as the Changing Cells, and Minimum in the Solver! • Excel finds that the optimum choices are • a = 13568.094 and • b = -26340456.1 • Compare to the numbers found with Excel’s Trendline!

23. Example 2 (cont.)

24. Example 2 (cont.) • Repeat example 2, but use a function of the form Y = A*e^(k*t), where t is the number of years after 1939. • Change the input values accordingly. • Choose initial values of A = 32800 and k = 0.07. • If there is not convergence to a solution, increase the number of iterations and choose Automatic Scaling!

25. Example 2 (cont.)

26. Example 3 • An office manager needs to purchase new filing cabinets. At the local superstore Office Min, Ace cabinets cost $40 each, require 6 square feet of floor space, and hold 24 cubic feet of files. On the other hand, each Excello cabinet costs $80, requires 8 square feet of floor space, and holds 36 cubic feet. The manager’s budget permits spending no more than $560 on files, while the office has space for no more than 72 square feet of cabinets. The manager desires the greatest storage capacity within the limitations imposed by funds and space. How many of each cabinet should be purchased?

27. Example 3 (cont.) • We can formulate this situation as a linear programming problem. • Let x1= the number of Ace cabinets to be bought. • Let x2 = the number of Excello cabinets to be bought. • Let Z = the total storage capacity of cabinets purchased. • Summarize the given information in a table:

28. Example 3 (cont.)

29. Example 3 (cont.) • We call x1 and x2decision variables for this model. • From the bottom row of the table, we get the objective function: • Z = 24 x1 + 36 x2 (1) • The objective function (1) gives the amount of storage space in cubic feet for a choice of x1 and x2. • In this case, the objective is to maximize Z.

30. Example 3 (cont.) • From rows 1 and 2 of the table, we get restrictions on our choices of x1 and x2 due to a limit on what we can spend and the size of the office. 40 x1 + 80 x2≤ 560 (2) 6 x1 + 8 x2≤72 (3) • We also want x1≥ 0 (4) x2≥ 0 (5) • The last two restrictions on x1 and x2 make sense physically. • We call equations (2) - (5) constraint equations.

31. Example 3 (cont.) • Our model for deciding how to allocate file cabinets is as follows: • Maximize: Z = 24 x1 + 36 x2 Subject to the restrictions: 40 x1 + 80 x2≤ 560 (cost) 6 x1 + 8 x2≤ 72 (space) and x1≥ 0; x2≥ 0.

32. Example 3 (cont.) • From the Defined Names group of the Formulas tab, use Define Name or the Name Manager to assign names to cells we will use in formulas. • This can also be done by right-clicking on a range of cells and choosing Name a Range.

33. Example 3 (cont.)

34. Example 3 (cont.)

35. Example 3 (cont.)

36. References • Calculus with Early Transcendentals (5th ed) by James Stewart • Finite Mathematics and Calculus with Applications (4th ed) by Margaret Lial, Charles Miller, and Raymond Greenwell • Introduction to Operations Research (8th ed) by Frederick Hillier and Gerald Leiberman