Start the new decade with Goal Seek in #Excel - KING OF EXCEL

Monday, January 13, 2020

Start the new decade with Goal Seek in #Excel

Start the new decade with Goal Seek in #Excel

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!

The situation:

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:

Excel tip - Goal Seek Sample 1

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.

What if…

  • 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 manuallyExcel tip - Goal Seek demo
2) or using Goal SeekExcel tip - Goal Seek demo1

Well, I believe the choice is obvious!  Isn’t it?

So the question is, where is “Goal Seek”?

It’s under
  1. Data Tab
  2. What-if Analysis
  3. Goal Seek
Excel tip - Goal Seek Sample 2
There we go!
Then input the parameters correctly:
  1. Set (target) cell – reference input
  2. To value (your gold) – numeric input
  3. By changing cell – reference input
  4. OK
Excel tip - Goal Seek Sample 3
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.
Excel tip - Goal Seek Sample 4
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.
From now on, will you rely Excel on finding your goals? 
#evba #etipfree #kingexcel
📤You download App installed directly on the latest phone here :

No comments:

Post a Comment