How to Use Goal Seeking in Microsoft Excel?

Being one of the most potent calculators, Microsoft Excel includes a feature like goal-seeking that you can use when you know the final result you want to get by using a specific formula. Still, you are unaware of the values of the operands involved.

The Goal-Seek function of excel lets you find the correct input when you only know the output. In the process, Microsoft Excel changes the values in a specific cell when you search for the inputs until the formula provides you the result that you already had.

Along with Excel, there are other tools as well that will help you get the input via output if there is an involvement of only one input. This post will decipher the mystery behind goal-seeking and help you understand what goal-seeking is, how it works, and how you can use it adeptly.

So, without any further ado, let us get started-

What is Goal Seeking?

Goal seeking is a process by which we can determine the correct input value when only the output value is known to us. This function is built into various computer software programs, for example, Microsoft Excel.

In simpler words, this feature (goal-seek) performs backward calculation (behind the screen) and yields the desired input for the given/entered output.

These tools work when there is a single input value. It is used for finances, forecasting, sales, etc., and is particularly useful for performing sensitivity analysis in financial modeling.

Goal seeking can be termed as a process used for ‘what-if evaluation’ in computer programs. It is also understood as back solving.

All in all, you can use Goal Seek for finding the result that you want to adjust an input value.

Let us now understand what what-if Analysis involved in goal-seeking is-

What is ‘what-if Analysis’ on a Computer Program involved in Goal Seek?

The ‘what-if evaluation’ is a process in which we change the values in the class (say of Microsoft Excel) to find out how such changes in the values affect the formula outcomes.

We produce a cause and effect situation in goal-seeking by making a scenario (in a computer software program) by asking, ‘what if the value of the output was termed X?’

Goal-seeking provides accurate input for some given output.

How does Goal-seeking work in Microsoft Excel?

Let us understand the whole process via below given three examples below

Goal Seek Example 1

Suppose you want to find out the interest rate a borrower requires to pass for (input) when the borrower apprehends how much he/she can pay every month and how much time do they need to pay off the loan (output). You’ll have to do the following to find out the interest rate:

1. Open a fresh spreadsheet (open Microsoft excel. Select a new spreadsheet. Save that file.)
2. Label the columns properly for your ease of use and understanding. For this case, let’s say the columns are

• Loan amount (in cell A1).
• Terms in a 6month (in cell A2)
• Interest rate (in cell A3)
• Payment (in cell A4)

1. Enter the known values:

B1: The amount borrower wants to borrow.

B2: The number of months he/she needs to pay off the loan.

Note: you’ll have to specify the payment value at a later step.

1. Enter the formula to calculate what you need to know. The formula would assume the interest rate to be zero percent.
2. Now you can comprehend the rate of interest by using Goal Seeking purpose.

• Data Tab->Data Tools->what-if evaluation->Goal Seek. (This applies for all the versions of Microsoft Excel from 2007 onwards).

Goal Seeking Example 2

Now, let us take another example. Suppose you are given the number of units (say 500), retail price (say 25 rupees), selling discount (say 10 percent), and revenue (say 11250).

You have to find the number of units that need to be sold to achieve an income of 20,000. For doing so, you’ll have to follow the following steps:

1. Put the cursor on the cell that contains the output you want to change (revenue).
2. Select What-if-analysis on the data ribbon. Then click on Goal Seek. Shortcut from keyboard is: Alt + A + W + G or Alt + T + G.
3. When the dialogue box appears, make “Set cell” equal to the revenue cell.
4. Set “To value” equal to the output you want to achieve.
5. Set “By changing cell” equal to the value you want to solve for.
6. Press OK. You will get your required result, 889 units.

Goal Seeking Example 3

Suppose an election is taking place in one week. Voters can come and cast their vote at any time during this week. To win, the candidate will need to acquire 66.67 percent of the total votes (two-thirds).

The total number of voters is 200. Now let us assume that candidate ‘X’ has got 98 votes till now, that is 49 percent of the total votes. It would help if you found out how many votes X must get to win this election.

1. Open a spreadsheet and fill in the data like shown below:
2. Once you open the goal, seek box fill in the following:

Set cell: the formula to calculate the percentage of the present ‘Yes’ votes.

To value the percentage of votes needed to win (66.67% in this scenario).

By changing cell: The number of ‘Yes’ current ‘Yes votes (cell B2).

You can also look at some good YouTube tutorial videos on “How to use goal seek in Microsoft Excel.”

Limitations

Microsoft Excel works properly for only one input variable. This might be useful for many scenarios, but if you are dealing with some complex problems and need to juggle with more than one input variable, then you can use Excel Solver, which is an Add On.

Here you need to understand that goal seek is not a formula, so it is not going to reside in any selected cells or spreadsheet permanently.

That is why you are supposed to re-run goal Seek every time you are going to change the spreadsheet.

Most of the time, it can be acceptable, as you have made the model particularly for calculating specific parameters. Still, there can be a few occasions when you would not find it convenient and acceptable.

Final Thoughts!

Microsoft Excel has readily used computer software for goal seeking. Goal seeking is the process of finding the correct/requires input value when only the output is known.

You must have understood that it is a process in which we seek the right input number with some known output. It is known as goal-seeking because it finds the input needed to obtain a specific goal. It can do the backward calculation and find the input of a given output.

It is used to find answers to ‘what-if evaluation,’ that is, what the input must have been if the output is known, say X.

Various business and management departments use this as it is efficient and extremely useful. The only drawback is that it can only work for not more than one input. But there is a solution for it, Excel Solver.

One must learn the basics of Microsoft Excel to use its built-in goal-seek feature. Once you know the basics of Excel, there is not much left to learn to perform ‘what-if evaluation.’ You can go through the examples given above and daily understand how to use goal-seek.

There are plenty of helpful tutorial videos available online for the same. You can refer to some good YouTube videos in case of any doubt.

Have you ever tried the Microsoft Excel Goal Seeking function for finding an input? Share your experiences with us in the comment section below.