Locked lesson.

Upgrade

Quick reference

What-If Analysis: Goal Seek

Use the Goal Seek utility in Excel to find what the input value for a formula need to be when we know the formula result.

When to use

We can use Goal Seek in many scenarios to work out what value needs to change to achieve our result. 

Instructions

If you know the result that you want from a formula but are not sure what input value the formula needs to get that result, use the Goal Seek feature. Goal Seek is part of Excel's What-If Analysis tools. 

We are going to look at three examples. 

Use PMT with Goal Seek to Calculate Loan Payments

In the first example, we need to calculate the monthly repayments on a loan. We will use the PMT function to do this. 

The PMT Function

The PMT function has 5 arguments but only the first 3 are mandatory. 

rate - the interest rate. Needs to be calculated as a monthly % if listed as per annum. 

nper - number of periods. This is the number of months/years we are paying the loan back over. Needs to be calculated as months for monthly payments. 

pv - present value. The current loan amount. 

fv - future value (optional). We use this argument if we intend to make a balloon payment at the end. 

type - if we are paying at the beginning or end of the payment period. (optional). 

In our example, we need to divide the interest by 12 to get the monthly interest rate. 

The result will show as a negative value as Excel considers this to be a debited amount. If we prefer to work with positive values, we can simply edit the formula (F2) and add a minus symbol in front of PMT.

Goal Seek

Our monthly payments are currently $436.60. Maybe we have budgeted to spend $500 a month so we would like to see either how much more we could borrow or how much the term would be reduced by if we were to make the higher monthly payment. 

We can use Goal Seek to help with this. 

  • From the Data tab, in the Forecast group, click What-If Analysis and select Goal Seek. 

In our example, we are going to calculate how many months the loan term will be reduced by if we pay $500 a month. 

We set cell C11 to the value 500 by changing cell C9. 

  • Click OK. 

If we pay $500 a month the term is reduced from 60 months to 52 months. 

Use Goal Seek with AVERAGE

In the second example, we will use Goal Seek to help us calculate what mark Lucy needs to score for the last competency to achieve an average score of 80. 

  • Click in cell C15. 
  • Calculate the average of all scores. 

Currently, Lucy has an average of 78. We can use Goal Seek to calculate what score she needs to get for 'Productivity' to raise her average to 80. 

  • From the Data tab, in the Forecast group, click What-If Analysis and select Goal Seek. 

We can see that Lucy needs to score 90 for her last competency. 

Use Goal Seek with SUM

In the final example, we are going to use Goal Seek to calculate the % increase for all staff bonuses if we have an increased budget for salaries. 

  • Scroll to the bottom of the dataset. 
  • In cell I57, press ALT+= to calculate the total of all salaries. 

We have a budget of $2,500,000 for salaries so the % of increase needs to change. 

  • From the Data tab, in the Forecast group, click What-If Analysis and select Goal Seek. 

We need to set cell I57 (total) to 2,500,000 by changing cell L8. 

The increased amount is now 6.2%. 

Login to download
  • 00:03 In this section of the course,
  • 00:05 we're going to delve into some of Excel's What-If Analysis tools.
  • 00:10 And if you've never used these before,
  • 00:13 they are super useful in many different scenarios.
  • 00:16 Now, you'll find the What-If analysis tools on the Data tab.
  • 00:20 All the way over in the Forecast group, we have them just here.
  • 00:23 And we're going to take a look at all of these over the next three lessons.
  • 00:27 And we're going to start out in this lesson by taking a look at Goal Seek.
  • 00:31 So, what exactly is Goal Seek, and how can it be helpful to us?
  • 00:36 Well, what Goal Seek allows us to do is effectively work backwards in
  • 00:40 a calculation.
  • 00:41 For example, I can start with a number, and
  • 00:44 I can get Goal Seek to work out which cells,
  • 00:46 which values need to be changed In order to achieve that result or that number.
  • 00:52 Now, that's pretty hard to visualize from my explanation just there.
  • 00:56 So the best way to understand how Goal Seek works is to dive straight into
  • 01:00 an example.
  • 01:00 So, if you take a look at the worksheet,
  • 01:03 you can see here I have a calculation that I need to work out.
  • 01:06 I need to work out what the monthly payments are going to be on a loan.
  • 01:11 So, maybe I've borrowed $24,000 to buy a new car.
  • 01:17 Now, the borrowing rate, the interest rate is 3.5% per annum,
  • 01:22 and I'm borrowing that money over a term of 60 months.
  • 01:26 So what I'm going to do here first of all is I'm going to use a function in Excel
  • 01:31 called PMT which stands for payment to work out how much my payments are going
  • 01:36 to be each month with these values just here.
  • 01:38 So let's type in =PMT.
  • 01:41 Now, we have five arguments here, the last two are optional.
  • 01:44 We are only going to use those first three mandatory arguments.
  • 01:47 So the first argument is the rate.
  • 01:49 So that refers to the interest rate.
  • 01:51 So the rate I can see here is 3.5%.
  • 01:55 Now, one thing to note here is that we're working out monthly payments.
  • 02:00 So, all of our calculations need to be in monthly installments.
  • 02:04 Now the rate is currently 3.5% per annum.
  • 02:08 So we need to divide it by 12 to get the monthly interest rate.
  • 02:13 NPER, that stands for number of periods or effectively,
  • 02:17 the term, how many months are you paying this over.
  • 02:20 Now, I'm paying this over 60 months.
  • 02:23 If I had five years in there, I would need to multiply 5 by 12 to get 60 months.
  • 02:30 We have 60 months, so we're fine.
  • 02:32 And then the final argument we're going to use is PV which stands for present value.
  • 02:36 So that is the present loan amount, which is this value just here.
  • 02:41 Let's close the bracket, hit Enter, and you can see that for
  • 02:46 a loan amount of 24,000, with a rate of 3.5% per annum over
  • 02:51 a term of 60 months, I'm going to be paying $436.60.
  • 02:56 Now, notice that this is displaying as a negative number because Excel sees this as
  • 03:00 money that's been debited or coming out of your account.
  • 03:03 It's a negative value to get.
  • 03:06 If you want this to show as a positive value,
  • 03:08 you can pretty much edit the formula by double-clicking, and
  • 03:12 you could just put a minus on the front here, and
  • 03:14 that's going to switch that to a positive value, so whichever works for you.
  • 03:19 So now that we have this payment calculation,
  • 03:21 let's get on to looking at how Goal Seek can help us out.
  • 03:24 So, maybe I look at this monthly payment and I think to myself,
  • 03:27 I've actually budgeted $500 a month to spend on these repayments.
  • 03:32 So I could either borrow more money or
  • 03:34 maybe pay this loan back a little bit quicker if I'm paying $500 a month.
  • 03:38 So, let's say that I want to decrease the number of months that I'm paying this loan
  • 03:43 over because I'm paying a little bit more each month.
  • 03:46 So let's jump up to the Data tab, What-If Analysis, Goal Seek.
  • 03:50 So the first thing we need to provide here is set cell.
  • 03:54 So you're basically saying set this cell and then the next one is to value.
  • 03:58 So I want to say set cell C11 to value 500.
  • 04:03 That's how much I want to pay.
  • 04:05 And then we can specify which cell we want to change.
  • 04:08 So, if I wanted to borrow more money, I could change the loan amount.
  • 04:12 If I want to pay over less time, I could change the term in months.
  • 04:16 And that's what we're going to do.
  • 04:18 Let's click on OK.
  • 04:20 And you can see it calculates it, and if I'm paying $500,
  • 04:25 it takes my term amount down to 51 or it looks like 52 days.
  • 04:30 Let's do some rounding in here.
  • 04:31 Let's take these decimal places down.
  • 04:34 So, now I only have to pay this loan over 52 months as opposed to 60 months.
  • 04:40 So, that is how Goal Seek works.
  • 04:43 Let's take a look at it in another scenario.
  • 04:45 This time, we have an employee, Lucy Oliver, and
  • 04:48 these are her competency skills from her review.
  • 04:51 So, you can see, she has a mark out of 100 for each of these skills.
  • 04:56 Now, let's say in order to get a bonus, her average score has to be 80.
  • 05:01 So, I want to work out what she needs to score for
  • 05:05 productivity in order to achieve an average of 80.
  • 05:09 So, the first thing I'm going to do here is I'm going to work out what the average
  • 05:13 is of the current scores.
  • 05:15 So, let's select this cell range, close the bracket.
  • 05:19 I can see currently she's sitting at 78.
  • 05:23 So, now we can use this value to work out what she needs to score in productivity so
  • 05:28 that this average comes up to 80.
  • 05:31 So once again, let's go to the Data tab > What-If Analysis > Goal Seek.
  • 05:37 We want to set this cell to 80 by changing this cell.
  • 05:45 Click on OK.
  • 05:50 And there we go.
  • 05:50 I can see that in order to achieve an average result of 80,
  • 05:54 she needs to score 90 on Productivity.
  • 05:56 Let's take a look at one final example.
  • 05:59 Now here I have a list of employees, and we can see their salaries, and
  • 06:04 every employee has got a salary increase of 2.7%.
  • 06:08 And we can see their new salary in column I.
  • 06:11 Now, if we go all the way to the bottom Ctrl+down arrow,
  • 06:14 let's add the total of all of these salaries.
  • 06:16 I'm going to do Alt = and Enter.
  • 06:19 So the total here is 2,418,042.
  • 06:23 Now, if we jump to the top of the list,
  • 06:26 you can see here that I have a budget of $2.5 million.
  • 06:30 So, it means that I can increase everybody's salaries by slightly more in
  • 06:34 order to match this budget.
  • 06:36 So, let's go to What-If Analysis, Goal Seek.
  • 06:39 This time, the cell that I want to set is going to be this total.
  • 06:43 So we're going to say set this cell to 2500000 by changing,
  • 06:51 and then we're going to change the percentage increase.
  • 06:57 Let's click on OK, and OK again.
  • 07:00 And because these formulas use this cell over here,
  • 07:05 it's automatically updated everybody's salary,
  • 07:10 and we should find that if we go to the bottom,
  • 07:14 we now have a total of exactly 2.5 million.

Lesson notes are only available for subscribers.

Gift this course