Excel Goal Seek for Business Analysis

Hello and welcome, the Goal Seek function in Excel may save you a lot of time in reverse calculations and guess work, let us take a look!

In the first example, our finance department gave us the figures for the first three quarters. In this table we have the Quarters, gross sales, profit percentages and the net profit, in the first row is 26% of 54k.

We want to know the gross sales amount in the last quarter to finish the year with 65k.

While it is true that we can do a manual reverse calculation in Excel using formulas, the Goal Seek will take care of it in seconds.

Before we start make sure that this blank row has the Net profit calculation. If you left this cell blank, it will give you an error.

First, let’s open go to the Data Tab, right on the Forecast Group, pick the What If Analysis? >> Goal Seek

The next window opens, in the Goal Seek window we have the Set cell value, this is the cell with the ultimate goal or transformation we need which is the total of the Net Profits, cell E9 where the 41k and change is going to happen.

After we press Enter or the down arrow to continue type in the To value 65k, this is the transformation value or ultimate net profit that we are looking for

After we write 65000 let us go to select by changing cell. In this case let’s pick the Gross profit because, we need 26% out of this number that we don’t know yet to arrive at the Net profit of 65k.

Just click OK and let Excel do its magic.

The Gross sales we need are 93158.38 and look how everything is calculated for us. Let us do another example with a business loan.

Let us say that we are looking for a business loan and our target monthly payment needs to be 2500 or less.

The payment function is already entered for us, but we need to do a reverse calculation, how can we get a 2500 monthly payment by changing the loan amount?

Before we continue remember to like, share, and subscribe for weekly tips, check the comments so you can get free 26 Excel shortcuts in PDF

In this example let’s assume that we only know the rate at 7% with a 30 year loan. Let us get the target loan amount.

This time let’s use the shortcut for the Goal Seek, we can either click on any blank cell or the current payment which is D27, the cell we want to change. The shortcut is ALT+A+W+G, and we press one at a time like when we type, do not press them all at once like a piano.

The Excel Goal Seek window opens and in the first selection is correct, D27 needs to change to 2500, by changing the loan amount in D24, press OK and that is your target loan amount.

Let’s just copy and paste this loan amount in the cell below, because in real life you can try the Goal Seek with the interest rate or the loan term.

Let me know in the comments if you feel the Goal Seek could help you in your business, thank you for watching and see you next time!

goal seek mathematical formula
excel goal seek multiple cells
how to automate goal seek in excel
goal seek example
goal seek excel shortcut
use goal seek to calculate the changing value
use goal seek to calculate the changing value in cell d4
advantages of goal seek in excel
excel goal seek feature
excel goal seek examples
excel goal seek tutorial
excel goal seek shortcut
excel goal seek not working
excel seek goal
excel goal.seek
excel goal seek scenario manager
excel goal seek multiple cells
goal seek mathematical formula
how to automate goal seek in excel
goal seek example
goal seek excel shortcut
advantages of goal seek in excel
microsoft excel goal seek feature
you need to use the goal seek feature in excel. where is this located
explain goal seek feature in ms excel with the help of example
which statement best describes the goal seek feature in excel 2016
what is the use of goal seek feature in ms excel
excel goal seek settings
excel goal seek shortcut
excel goal seek analysis
excel goal seek command