Happy New Year of 2020! Wish you all an Excellent
year decade ahead! 😉
Have you made your new year resolutions?
Or you are relying on Excel to set your goals of the year?
Did you know that Excel has a feature called Goal Seek?
Let’s start the year with a simple example to demonstrate how to use Goal Seek in Excel!
Let’s say I want to set a saving plan for the year. My saving plan is simple: On week 1, I started my saving with $10. Following each week, I will save just 1% more than the previous week’s saving.
With Excel, I can calculate the total savings at the end of week 52 easily.
You may download a Sample File to follow along.
The Set up:
B2 = Input value: the weekly increment percentage that I am committed to B5 = Input value: my saving on week 1 B6 = B5*(1+$B$2) B4 = SUM(B5:B56)
Pretty easy and straight-forward… 😁
By starting with $10, and save just 1% more every week, I am expected to save $677.69 in total by the end of the year.
- my goal is to save $1000 in total by the end of the year?
- by changing my initiate saving amount ($X)
- with the weekly incremental percentage remains constant
To solve the equation, I can either
1) Trial and Error by changing the value in B5 manually
Well, I believe the choice is obvious! Isn’t it?
So the question is, where is “Goal Seek”?
- Data Tab
- What-if Analysis
- Goal Seek…
There we go!
Then input the parameters correctly:
- Set (target) cell – reference input
- To value (your gold) – numeric input
- By changing cell – reference input
The target cell set in step 1 should always be a formula; its result is dependent (directly / indirectly) on the changing cell set in step 3
If you don’t care to see the weekly figures, you can use FV function to obtain the result – total savings in the end of the year.
The formula is simple:
= - FV(B2, 52, B5) 'where B2 is the interest rate of each period 52 is the number of period B5 is the amount of first investment (savings)
You can visit the following page for more details on this function. https://support.office.com/en-us/article/fv-function-2eef9f44-a084-4c61-bdd8-4fe4bb1b71b3
From now on, will you rely Excel on finding your goals? 😁