Goal-Seek in Excel | Do you ever wonder what would happen if a detail was changed? “What-if”? With Goal-seek in Excel, you can actually do this!.
When do you use Goal-Seek?
For example, let’s say you are about to buy a new car. You have of course done very accurate calculations in Excel (what else do you do on Saturdays?) for your new purchase. There are many parameters to consider! In the sample file in this blog post, I have included the blue-marked values below that you can choose to change.
Based on these values, I have prepared a table showing how the costs change per month over 2 years. How much is the car worth after a certain amount of time, how is it related to the loan and what is my total monthly expenditure?
We can easily change the parameters that are blue-marked and see what’s happening in the table, but it’s not exactly the same as goal-seek.
For example, we can see if we change to a payback of 3,000 kr per month instead of 2,000 kr, we will at best get around 57,000 kr after 2 years if we sell the car, compared to about 22,000 kr.
Goal-seek does the same thing, but the other way around!
If we instead want to know how much we would need to amortize for the car to be worth exactly 75,000 kr more than the loan after 2 years, we use goal-seek.
Mark the cell you wish to set a new value for.
Then go under the Data tab. A bit to the right is “what-if analysis” and there you can select “Goal-seek”.
The first cell refers to the one we want to change. Because we were already in the correct cell, we do not need to change it.
In the middle row, fill in the desired value as we want it to become.
At the bottom we enter the value to be changed.
In this case, we say that Excel may change the amortization value in order for the car value – loan amount after 2 years will be exactly 75,000 kr.
After a second’s magic, we have received the fairly specific value required for monthly repayment to be able to expect to lose 75,000 kr after 2 years.
The powerful thing is that we can change any of the values in the calculation and see the outcome by means of goal management!
What would gasoline costs need to be for us to get a monthly cost of the first month of 11,000 kr instead of 12,333 kr?
Just go back with Ctrl + Z so that the amortization goes back to 3,000 kr a month and select Total Expense.
Alacazam! We know that if only the gasoline had cost 7 kr per liter instead of 15, we would have saved 1,333 kr the first month. It may seem unreasonable so you can of course adjust the amount of loan or the number of miles you drive a year to see if you make your calculations correct.
Goal-seek can be used in many different areas! If you want to lose weight, you can either eat less or exercise more. Goal-seek lets you figure out how many extra steps you would need to take if you eat half a chip bag more a week, for example.
Do you have a procurement at work or budget work can you make assumptions and see what impact different outcomes would have, and above all – if you want to achieve a certain outcome, what impact is required?
If you want to test yourself you can download the sample file used in the blog post here!