Using Goal Seek and Solver in MS Excel

For an example using goal seek, say you have a budget of $1000 per month on a certain project. And your budget is being governed by a specific formula, say Budget=5x + y (I am just making up this example to relate it to reality). Say x is the food budget and y is the expenses for transportation.

Thus you might want to compute the expenses for transportation, given the total food expenses to be $100. With problems like this, you need goal seek for faster computation and to avoid algebraic manipulation of the variables, which can be very time consuming to do on an Excel worksheet.

Now suppose you do not know the food and transportation expenses, but you are given the following constraints: food expenses should not exceed $400, yet not be less than $100, while transportation expenses should not exceed $600 yet not be less than $200. Given these criteria, you are asked to solve for the optimal food and travel expenses, given that the overall budget does not exceed $1000.

This type of problem can be solved by the MS Excel Solver add-on. Note that Solver is an add-on, and should be installed to be used, unlike goal seek which is a built-in function of Excel.

If you do not have Solver installed under the Tools section of your MS Excel, try reading this Microsoft tutorial on Installing and Running Solver.

I ill give you real examples of how to apply goal seek in MS Excel. This tutorial is tested to work starting with MS Excel 2002. MS Excel might be evolving; I have seen many advanced features, particularly in Excel 2007. But this tutorial is simple, and the basic principles are still covered.

Using Goal Seek

FIRST STEP: Goal seek requires one variable to be unknown in a set of equations. Remember our example on the previous page:

Budget = 5x + y

If the budget is known ($1000), and the food cost is known ($100), we can solve for the transportation cost.

Therefore the first step in a goal seek process is to define your equations or relationships between the variables.

SECOND STEP: The next step after defining the equations is to define the given part of the equations.

THIRD STEP: After defining both the given variables and the equations, open your Excel spreadsheet. Define the equations in one cell ONLY. Note that defining the equations in Excel differs a lot from the mathematical world. For example, the above equation can be written as:

Budget = 5*x + y

The multiplication sign should be written as “*”. Other complicated mathematical expressions such as:

can be expressed as Z= (5*x*y*4*log((5^x), 10)/(y)

To implement our example in the spreadsheet, copy and paste this formula in cell A1:

=5*A2+A3

With this formula, cell A2 will be used for “Food expenses” while cell A3 will be used for “Travel Expenses.” Shade A2 and A3 with yellow so that you will know they are variables.

If you are asked to solve for the travel expenses given that the food expenses equal $100 and the total monthly budget is $1000, place $100 in cell A2, then go to “Tools” and click on “Goal Seek.” Enter the following data:

Set Cell: $A$1

To value: 1000

By Changing Cell: $A$3

Then click OK. After that, Excel analyzes the given conditions, and then goal seek returns a result like the one below:

If you have some formulas yourself, try solving them using goal seek. It is a much faster approach than any other methods.

Using Solver

Assuming you have successfully installed Solver in your MS Excel by following the procedure from the Microsoft website, we will illustrate how to solve a typical problem using Solver.

Let us use our previous example, summed up below:

Total monthly given budget: $1000

Constraints:

Variable X (food expenses) = $400

Variable X (food expenses) = $100

Variable Y (travel expenses) = $600

Variable Y (travel expenses) = $200

Problem: Solve the optimal food and travel expenses that meet the required monthly budget and given constraints.

Solution: Now this is a more interesting problem. You have two unknowns here (food and travel expenses), as opposed to just one in the goal seek example.

Step 1: Set up the conditions above in an Excel spreadsheet.

Ideally, use the same worksheet/cell setups as you used with goal seek on the previous page. To avoid confusion, summarize the variables with their respective cell locations. see below:

$A$1 = The “Overall monthly budget required”

$A$2 = Food expenses

$A$3 = Travel expenses

Step 2: Click Tools, and then “Solver.” Set the following data carefully in the “Solver Dialog” Box:

Set Target Cell: $A$1

Equal to: (please check “value of:”) 1000

By changing cells (select cell A2 and A3): $A$2:$A$3

Subject to the constraints:

$A$2 <= 400

$A$2 >= 100

$A$3 <= 600

$A$3 >= 200

To add these specific constraints, click “Add.” Next, click on the cell reference (whether it is A2 or A3 in this example), set <= or >= , and then enter the value as the constraint. Finally, click OK to add the constraint. Do the same for the other conditions; you can only add one constraint at a time. If you have correctly set up the constraints and the other stuff, it should look like this:

Solving with Solver

Step 3: When all the values are configured correctly in Step 2, click “Solve.” A “Solver Results” Dialog Box will then appear. It will tell you whether or not Solver has found a solution. Then click OK to close this dialog box.

Step 4: When Solver has found a solution, it will be displayed in the yellow box. In other words, Solver will automatically fill in those blank fields (yellow shaded cells for x and y variables).

Below is what the Excel worksheet will look like with Solver filling in the optimal values for X and Y, which are:

Optimal food expenses (X variable) = $158

Optimal travel expenses (Y variable) = $212

Using Goal Seek and Solver in Real World Applications

Of course, the examples illustrated in this article are fictitious, but you can use Solver and goal seek to solve complicated problems. Common applications can be found in the engineering/manufacturing sector, the financial sector, and in academic sectors.

Scientific experiments, financial research and academic studies can be solved using the Solver and goal seek features in MS Excel.