Goal seeking a solution in Excel

The Goal Seek function is an invaluable tool in seeing how to get a model to get to a solution you want. The easiest way to explain it is with an example.

To follow along go to 15.5

In the image below we have a simple income statement and it is showing a gross profit of 209.68 when we use a sales price of 10.75. We want to know what the sales price needs to be in order to get a gross profit of 250.

goal-seekingYou could of course manually change the number in cell B5 until you get the answer you want, but a quicker way is to use the Goal Seek tool. You will find the Goal Seek button in the Data Ribbon under What If Analysis

goal-seeking

In our example, when you click the Goal Seek button you see the dialogue box as shown below. Excel needs to know

  • what cell you are looking for the answer in (‘Set Cell’),
  • what number do you want it to become (‘To Value’) and
  • which cell it can change (‘By changing cell’) to get there.

goal-seeking

When you click OK, you will notice that Excel tries lots of numbers until it finds the input (Sales Price in this case) that results in the output (gross profit) of 250. Notice that the actual number has lots of decimals. This is the level that Excel goes to to find the correct number.

goal-seekingIf Excel cannot find a solution, it will tell you. The reasons for not finding a solution may be:

  • It is not possible- what you are asking cannot be achieved with the calculations you have set up
  • There is no link between the input and the output cell- no matter what Excel does to the input cell, the output cell won’t be affected- you need to correct your spreadsheet

Some notable points about the Goal Seek tool are:

  • The input cell (‘By changing cell’) must be a number in the spreadsheet. It mustn’t contain an = sign even if it is just adding 2 numbers together
  • The input and output can be on different sheets.
  • The number you want to achieve (‘To Value’) must be typed in, you can’t link it to a cell.
  • You can only do one variable at a time. If you want to do sensitivity analysis with two variables you will need to look at the other tools covered in this course.
Back to: Online Financial Modelling, Budgeting and Forecasting Course > Running What Ifs