Start the new decade with Goal Seek in #Excel

January 13, 2020 Excel tips,

Start the new decade with Goal Seek in #Excel

by MF

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:

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.

Wait…

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 manually

2) or using Goal Seek

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

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

It’s under

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
OK

Note:

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

Tip:

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?

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1