Section 9.2. The Solver


9.2. The Solver

Goal Seek has some limitations. It does not have a way to keep results reasonable. You could get a solution that includes spending a negative amount of time on a project or sending half a person to St Louis. And sometimes one value is not enough.

For more complicated problems we use the Solver. The Solver is on the Tools menu . If it is not there, go to Add-Ins (also on the Tools menu) and be sure it is checked.

The biggest difference from Goal Seek is the Solver's ability to find more than one value.

9.2.1. Finding Two Values at Once

I can buy three apples and two oranges for $2.75. I can buy one apple and four oranges for $2.50. What are the unit prices of apples and oranges?

The model for this problem is in Figure 9-16.

Figure 9-16. The model for the two price problem


This sheet allows the costs and quantities to be changed. There is only one formula and it returns a zero when the prices of apples and oranges meet the requirements.

We now activate the Solver by selecting Tools Solver, and set the problem up as in Figure 9-17.

Figure 9-17. Using the Solver on the two price problem


This problem is solved similarly to using Goal Seek. We have a target cell and a target value. The difference is that we can tell Solver to change both prices at once. The result is shown in Figure 9-18.

Figure 9-18. The solution to the two price problem


Apples cost $0.60 and oranges $0.48.

9.2.2. Regression with the Solver

The Solver also lets you try different combinations easily. Suppose we need to build a model that predicts billable minutes on a network. We have several metrics that could be used to predict this value, but we don't know which ones give the best results. We could handle this as a typical regression problem, but we can also use the Solver to quickly try different combinations.

The data available is shown in Figure 9-19.

Figure 9-19. The data for the billable minutes problem


Column A contains the value we want to predict. We can use any combination of the other data items. There are 242 rows of data on the sheet.

A calculation area is in columns G-M of the same sheet. It is set up as in Figure 9-20.

Figure 9-20. The calculation area for the billable minutes problem


We are going to predict billable minutes by multiplying some or all of the metrics by a weight. Then we sum the products along with an intercept value, just like a normal regression problem.

The formula in G2 multiplies the first value in column B (Europe) by the weight in G1. This formula fills across to column K and down to row 243.

In L2 the products on row two are summed with the value in L1. This gives the prediction for the row. This is an estimate of the value in A2. This formula fills down to row 243.

The array formula in cell M2 is the objective function. It is the average difference between the predictions in column L and the actual values in column A. We use the ABS (absolute value) function because all error is equally bad, high or low.

With the calculation area built, we use the Solver to set the weights as in Figure 9-21.

Figure 9-21. Using the Solver on the billable minutes problem


The target cell is M2, our objective function. In this case we are not setting the target sell to a specific value. We just want the average error as low as possible, so we select minimum instead of specifying a value.

The Solver is allowed to change the weights and the intercept in the range G1:L1. The Solver comes up with the results shown in Figure 9-22.

The weights and intercept are set and the average error is 313,573. The weights in G1 and K1 are high. This means the model is depending heavily on these values. The weight in H1, in contrast, is low, so low that it is probably doing nothing useful. The Solver lets us test this premise easily. I set the weight in H1 to zero and re-run the Solver with the settings shown in Figure 9-23.

Everything is the same except the By Changing Cells box now contains G1,I1:L1. It skips cell H1. We re-run the Solver and get the result in Figure 9-24.

Figure 9-22. The results for the billable minutes problem


Figure 9-23. Eliminating one of the metrics


Figure 9-24. The new results


Column H and its supporting data in column C are no longer part of the model. Setting their weight to zero eliminates them. With less information, however, the model is doing a slightly better job. The average error is now 313,566. Using this technique we can quickly test any combination of metrics. You get the best results in this kind of problem if you can eliminate unnecessary metrics. They confuse the process and tend to make predictions less accurate.

9.2.3. A Problem with Constraints

Constraints are rules. They let you set conditions the Solver has to meet while finding a solution. They are common in business situations that have several possible courses of action.

A mixture problem is a classic example. We make oat bread mix. Our main ingredients are flour, oars, and raisins. We have 150 lbs of flour, 80 lbs of raisins, and 90 lbs of oats. We make two varieties of mix. Our standard oat bread mix contains no more than 30% flour and at least 30% raisins, while oat bread lite mix has up to 50% flour and at least 20% raisins. We sell standard for $3.49 a pound and lite for $2.99 a pound. We can sell the flour for $1.50 a pound and the raisins for $1.75 a pound.

We want to make as much money as possible. How much of each product should we make? Or should we sell some of the ingredients?

The model for this problem is shown in Figure 9-25.

Figure 9-25. The model for the mixture problem


For now all of the weights are zero. The Solver will adjust them later. In cell B7 the SUMPRODUCT function multiplies the selling prices times the pounds used for each possible use, and sums the products. This gives the total revenue and is the objective function.

Cells B8 and B9 simply add up the pounds of the ingredients in each selling possibility. Cells B12 to B14 contain total use constraints. The Solver needs to know how much of each ingredient is available. These cells keep up with the total flour, raisins, and oats used. Cells B15 to B18 contain rules. The upper limit for flour in the lite mix is 50%. So flour cannot be more than 50% of the weight of this product. The total weight is the sum of the weights of all three ingredients. This, then, is the rule:

 Flour <= .5 x (Flour + Raisins + Oats)

This form will not work with the Solver. We have to express one side of the equation as a number. Therefore, multiply the .5 through the right side::

 Flour <= .5Flour + .5Raisins + .5Oats)

Next we subtract Flour from both sides, resulting in:

 0 <= -.5Flour + .5Raisins + .5Oats)

The weight of flour used in the lite mix is in cell D4, raisins in E4, and oats in F4. The Excel version of the equation is:

 0 <= (-.5*D4) + (.5 * E4) + (.5 * F4)

The entry in cell B15 is:

 =(-.5*D4) + (.5 * E4) + (.5 * F4)

We tell the Solver this value must be less than or equal to zero.

The other rules (in cells B16 - B18) work the same way.

There will also have to be a constraint on each cell in the range B4:I4. These are the pounds used and we need to tell the Solver that these values cannot be negative.

Now we are ready to use the Solver. We start as in Figure 9-26.

Figure 9-26. The Solver in the mixture problem


The setup starts like the other problems. The objective is to maximize the target cell (B7) by changing cells B4:I4. Next, we enter the constraints by clicking the Add button. This displays the dialog shown in Figure 9-27.

Figure 9-27. Adding the constraints


The first constraint is on the total pounds of flour used in cell B12. This must be less than or equal to 150 lbs, since that's all we have. We click the Add button rather than OK to go on to the next constraint and make all the entries in Table 9-1.

Table 9-1. Constraints for the mixture problem

B12

<=

150

B13

<=

80

B14

<=

90

B15

<=

0

B16

<=

0

B17

<=

0

B18

<=

0

B4

>=

0

C4

>=

0

D4

>=

0

E4

>=

0

F4

>=

0

G4

>=

0

H4

>=

0

I4

>=

0


When the last constraint is entered we press OK, bringing up the Solver display. Next we click the Options button, which displays the dialog in Figure 9-28.

Figure 9-28. The options dialog


This is a linear problem. The objective function is the sum of products. Nothing is raised to a power, and there are no IF functions or other nonlinear calculations. So, we check Assume Linear Model. This lets the Solver run a little faster on this problem, but it is not required. If you are not sure about a model, it is best to leave this alone. We also click on Save Model. We entered a lot of constraints and we want them saved. The Save Model dialog will ask for a place to put the model. I entered K1 and then closed the Options dialog. This brings the main Solver dialog back. Then I clicked Solve, resulting in Figure 9-29.

Figure 9-29. Solver results for the mixture problem


The model is in cells K1:K18, and I can reload it from the options dialog if I need to. The Solver has set the values in cells B4:I4 to a combination that results in revenue of $981.80. We will use all of the ingredients, making 270 pounds of lite mix and 50 pounds of standard.

The mixture for the lite product is: flour 135 lbs, raisins 65 lbs, and oats 70 lbs. The standard mixture is: flour 15 lbs, raisins 15 lbs, and oats 20 lbs.

9.2.4. Zero/One Problem

You can tell the Solver what kind of answer you want. Some problems can only be answered by an integer. You cannot schedule half of an airplane on a route, even if it would save money. Sometimes the answer needs to be yes or no, and this is called a zero/one problem .

We are making a weekly schedule for a salesman who has seven customers. All the sales calls require travel and the customers are different distances away. Travel costs $0.90 per mile. Travel speed is 50 miles per hour, and we can only schedule 40 hours of travel time per week. We know how much each customer will spend if the salesman calls on them. We need to come up with the schedule that will make the most money.

This is a zero/one problem because for each customer it is a yes or no question. We model the problem as in Figure 9-30.

Figure 9-30. The model for the zero/one problem


The hours for the potential calls are calculated based on half the travel speed because it is a round trip. Call cost is just the miles multiplied by the cost per mile.

Cells F3:F9 are all 0. This means no sales calls are scheduled yet. When a call is scheduled, the 0 in that row is changed to 1. These are the cells the Solver will be allowed to change.

The values in the range H3:J9 are the sales potential, cost, and hours all multiplied by the yes/no value for the row. So, if the call is not scheduled they are 0. The profit is the sum of the sales minus the costs for the scheduled calls. This is the objective function for the problem. The Solver setup starts as shown in Figure 9-31.

The target cell H3 is to be set to a maximum value by changing the values in cells F3:F9.

Figure 9-31. The Solver setup for the salesman problem


Next we enter the constraints. There is only one rule. Hours cannot be more than 40. We click Add and enter the rule as in Figure 9-32.

Figure 9-32. Adding the hours rule


Cell J11 contains the total hours for the scheduled calls, and its value must be less than or equal to 40.

We also need to enter a constraint for each cell in the range F3:F9. These are yes/no values and can only be 0 or 1. These constraints are entered as shown in Figure 9-33.

When "bin" is selected in the drop-down list, the constraint is entered. If we wanted an integer value we would select "int."

After the constraints are added we save the model. We do this as before, by going to the Options dialog and clicking Save Model. The address for the model is N1.

Figure 9-33. Adding a zero/one constraint


We return to the Solver dialog and click on Solve. The Solver returns results shown in Figure 9-34.

Figure 9-34. Solver results for the zero/one problem


Our most profitable schedule is to call on customers three and four. This results in a 34.48 hour travel week and a profit of $3824.20.

9.2.5. Running the Solver with a Macro

If this problem needs to be solved every week, it would be helpful to build a macro to run the Solver. Start by going to the Visual Basic Editor, either by way of the Tools Macro Visual Basic Editor menu or by pressing Alt-F11. Then enter the following code:

 Sub RunTheSolver(  )     SolverLoad LoadArea:="$N$1:$N$11"     SolverSolve UserFinish:=True End Sub 

The macro is named RunTheSolver. We made things easy by saving the model on the worksheet. We don't have to tell the Solver what to do, just where the model is. The model is saved in the range N1:N11, which is the parameter for the SolverLoad statement.

The SolverSolve statement uses the UserFinish:=True option. This prevents the end dialog from displaying when the Solver finishes, making the process completely automatic.

We return to the worksheet, and add a button from the Forms toolbar as shown in Figure 9-35.

Figure 9-35. Adding a button


If this toolbar is not visible, go to the View Toolbars and be sure the Forms toolbar is checked. Drag and draw a button and the Assign Macro dialog will display. Click on the RunTheSolver macro. The button will still be selected and you can highlight the button and change its caption to "Solve.

Suppose the assumptions have changed. With the rising price of gas we are now paying $1.75 per mile. To help offset this cost increase we are now assuming a travel speed of 60 miles per hour instead of 50.

We change the Cost per mile and Travel Speed in cells B12 and B13 and click our Solve button, resulting in the display in Figure 9-36.

Figure 9-36. Running the Solver with a macro


There are no dialogs. The new schedule just appears. With the new assumptions we now schedule three customers (numbers 2, 3 and 6) instead of two, and the profit goes up slightly.

9.2.6. Common Problems with the Solver

Solver problems are often complex and sometimes the Solver will fail. In most cases you can fix the problem if you understand what's wrong.

The Solver changes values on the sheet while trying to find the best solution. If these values can cause an error in a calculation anywhere on the sheet, you may get the message in Figure 9-37.

Figure 9-37. A calculation error with the Solver


This could mean the Solver has tried a value of zero that is used in a formula as a divisor. This will cause a divide by zero error . Another possibility is the Solver has tried a negative number for a value that cannot be negative.

If you get this error, review your calculations. It may be necessary to change the model or the Solver options.

Another possible problem is shown in Figure 9-38.

Figure 9-38. The Solver fails to find an answer


In this case the problem is just too hard and the Solver can't find the answer. If this happens rewrite the objective function. That lets the Solver try a different approach.

In a problem like the regression model in Figure 9-20, you may get this error if there are a large number of columns. The objective function in that problem is:

 {=AVERAGE(ABS(A2:A243-L2:L243))}

This formula calculates the average error and is what we want to minimize. Another way of looking at the problem would be to maximize the correlation between the actuals and the predicted with this formula:

 =CORREL(A2:A243,L2:L243)

If the first formula is in cell A1 and the second is in cell A2 you can combine these two approaches with this:

 {=(1- A2) * A1

This is a more complicated function and it adds no value in theory. But it gives the Solver a different approach to the problem and often works in this situation.

Another error is shown in Figure 9-39.

Figure 9-39. An impossible problem


Here we have mutually exclusive constraints. In the problem modeled in Figure 9-25, if I enter a new constraint telling the Solver that cell B4 must be greater than 200, I will get this error. This new constraint tells the Solver that the solution must include the sale of 200 lbs of flour while other constraints tell it that there are only 150 lbs of flour available.

This is impossible and there is no solution that meets all the constraints. If you get this error, go back and review the constraints. One has been entered wrong, the problem has not been modeled correctly, or it is just impossible to do what you are trying to do.

Another common, but less serious, situation occurs when the Solver reaches its time or iteration limit. The iteration limit dialog is shown in Figure 9-40.

The Solver looks for a solution by making small changes in the cells. If a change improves the solution, the Solver makes another small change in that direction. Each cycle of changes is called an iteration. The maximum number of iterations allowed is set on the Options dialog shown in Figure 9-28. If you get this error or the similar Max Time error dialog, it means the problem is difficult and it is taking the Solver a long time to find a solution. Usually you can just click continue, but if it keeps happening you may need to adjust the model.

Figure 9-40. The iteration limit


9.2.7. Applications

The Solver and Goal Seek can be used in Excel applications. None of the applications in the book do this, but you can include either of these tools on a Workarea sheet. The macros in this chapter show how to run the tools. You would have to decide how and when to trigger the macros. They could be run from a button or tied to a change on a worksheet as we did in this chapter, or perhaps run when the workbook opens.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net