Trial and error is time consuming. Goal Seek fixes that.
Goal Seek is a simple tool in Excel that lets you go straight to the answer and set it to anything you want.
Excel then works backwards and evaluates what input value is required in order for the answer to be achieved.
Example 1: Using Goal Seek To Forecast Revenue
Let’s start with a really simple example - a classic sales calculation.
Now, let’s say we want to reach a revenue of $300.00,
Assuming the same quantity of widgets are ordered, what would we need to increase the price to?
OR … assuming we sell widgets at the same price, what quantity would the customer need to order?
Here’s the spreadsheet:
Figure 01: Revenue Calculator
Here’s how we would use Goal Seek to calculate the new PRICE.
1. Select cell B3.
2. Select Data | What If Analysis | Goal Seek.
Figure 02: Where to find the Goal Seek tool
3. Change Value to 300.
4. Set By Changing Cell to B1.
Figure 03: A completed Goal Seek dialog
5. Click OK to see the results.
Figure 04: The new price projected by Goal Seek
This tells us that we would need to increase the price from $0.13 to $0.15 to achieve our revenue target of $300.00
At this point you can click OK to keep the new price or Cancel to revert back to the original value.
Let’s use Goal Seek again to calculate the new QUANTITY.
1. Select cell B3.
2. Select Data | What If Analysis | Goal Seek.
3. Change ‘To Value’ to 300.
4. Set ‘By Changing Cell’ to B2.
5. Click OK.
Figure 05: The new Quantity projected by Goal Seek
This tells us that we would need to increase the quantity to 2,308 (rounding up the decimals) to achieve our revenue target of $300.00
Example 2: Using Goal Seek To Calculate Monthly Mortgage Repayments
A mortgage repayment has 3 contributing factors
Here’s the spreadsheet, already populated.
Figure 06: The PMT calculation
This is a quick breakdown of the PMT function.
Notice how all the figures were adjusted to work on a common MONTHLY basis (i.e. B2/12 and B3*12).
There is a minus sign in front of the Present Value (PV). This makes it negative, which makes the final repayment figure positive.
Anyway back to Goal Seek!
The current monthly repayment based on these figures is $2,347.
Let’s say you get a pay rise and you can now afford to pay $2,600 instead of $2,347.
Using Goal Seek you can discover 3 things:
Let’s answer those questions.
1. Select cell B4.
2. Select Data | What If Analysis | Goal Seek.
3. Change ‘To Value’ to 2600.
4. Set ‘By Changing Cell’ to B1.
5. Click OK.
The amount you can borrow has increased from $450,000 to $498,421.
And setting ‘By Changing Cell’ to B2 … The interest rate could rise from 4.75% to 5.66%.
And setting ‘By Changing Cell’ to B3 … The term reduces from 30 years to 24.37 years.
What next?
So that’s Goal Seek. A simple tool that provides incredible functionality and purpose.
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
