Goal Seek

Goal Seek is used to get a particular result when you’re not too sure of the starting value. For example, if the answer is 56, and the first number is 8, what is the second number? Is it 8 multiplied by 7, or 8 multiplied by 6? You can use Goal Seek to find out.

 

Create the following Excel spreadsheet

data table

In the spreadsheet above, we are calculating savings based on family income and expense. Here, total expense is the summation of all expense items, My income is the summation of all income sources and My savings is the difference between My Income and Total Expense.

Now I want to save more for my future plan. Suppose, I want to save 70,000 tk for the above family budget. So, I need to expense less, that’s for sure. I want to reduce my miscellaneous expense and added the amount in my saving. The question is “How much I deduct from miscellaneous cost? By using goal seek, I can find out easily and magically.

 

To use Goal Seek to get the answer, try the following:

From the Excel menu bar, click on Data Tab. Locate the Data Tools panel and the What if Analysis item. From the What if Analysis menu, select Goal Seek

The following dialogue box appears:

goal seek popup

The first thing Excel is looking for is “Set cell”. This is not very well named. It means “Which cell contains the Formula that you want Excel to use”. For us, this is cell E6. We have the following formula in E6:
E6 = E5 – E4

Set Cell: So enter E6 into the “Set cell” box, if it’s not already in there.
To Value: The “To value” box means “What answer are you looking for”? For us, this is 70,000. So just type 70000 into the “To value” box
By changing cell: The “By Changing Cell” is the part you’re not sure of. Excel will be changing this part. For us, it was cell B12. So type B12 into the box or just select the cell.

You Goal Seek dialogue box should look like ours below:

setting data

Click OK and Excel will tell you if it has found a solution:

result

Click OK again, because Excel has found the answer. Your new spreadsheet will look like this one:

final result

As you can see, Excel has changed cell E6 and replace 70,000 which I exactly looking for.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment