“Goal Seek” – a function everyone involved in financial modeling must know - DataPartner Software

News & Events

Press to learn more about Invest for Excel software!

News

“Goal Seek” – a function everyone involved in financial modeling must know

June 15, 2016

In this article we will take a look at a very useful Excel feature - Goal Seek function. It can bring great value for those involved in financial modeling and analysis, yet many people are not aware of it.

The Goal Seek function is part of Excel’s What-If analysis toolset. Theoretically, it answers the question: what input variable you should have in order to reach the desired result. Practically, it adjusts one cell, to achieve a fixed desired result in another cell.

Let’s take a look at one of the most common usage scenarios: Say, we have a complex forecasting model with many interlinked variables. We are curious to know what the selling price should be so that our project achieves 60% IRR.

For that, we need to do the following:

1. Go to Excel menu > Data > What-If Analysis > Goal Seek

2. Define fields in popped-up window:

  • "Set Cell”: set the cell with the formula you want to resolve (IRR).
  • “To Value”: set the desired value you want the formula to return (60 %).
  • “By changing cell”: set the cell with variable to be changed (selling price).

3. Press OK. As a result, we can see that our project IRR will reach 60%, when the selling price is 403,64 EUR.

In a similar way, Goal Seek function can be applied to almost any parameter of your model. It is commonly used for analysing following profitability indicators: NPV, IRR, MIRR, RONA, Profitability Index, Payback time and others.

Note: When using Goal Seek function remember that "by changing cell” field must not include a formula!

Let’s take a look at another calculation scenario. Say, that we want to know what the selling price should be so that Net Income in each period equals 0.

For that, we need to do the following:

1. Go to Excel menu > Data > What-If Analysis > Goal Seek

2. Define fields in popped up window:

  • "Set Cell”: set the cell with the formula you want to resolve (Net Income for the period).
  • “To Value”: set the desired value you want the formula to return (0).
  • “By changing cell”: set the cell with variable to be changed (selling price).

3. Press OK. As a result, we can see that Net Income for the period will be equal to 0, when the selling price is 210.76 EUR.

4. Repeat the same steps for the following periods to achieve 0 Net Income for the period throughout all calculation terms.

Goal Seek animation. Please wait for image to load.

Calculation displayed above is quite common for nonprofit organizations, when it is necessary to avoid making a profit - by keeping prices for products or services at just enough level to cover the operating costs.

These examples were made using Invest for Excel software that is based on Microsoft Excel. Click here to learn more about Invest for Excel

Back