Microsoft Excel Helps in Setting Goals

In a previous post, we know that Khawla has a goal: to accumulate AED100,000 in her bank account by the end of 2011. Her only source of income is her salary. Obviously, she can achieve this goal by limiting her monthly expenses (cash outflows).

There is a tool in Microsoft Excel that can help her set her goal. This tool is called Goal Seek which can be found under the Tools menu:

Microsoft Excel 2003: Tools Menu

Assuming that Khawla set her goal for the year 2011, and that her monthly salary is AED10,000, then in a year she can accumulate AED120,000 (10,000*12). But, since she incurs monthly expenses, she sets her goal to accumulate at least AED100,000. How much must her expenses in 2011 be if she wants to accumulate AED100,000 by the end of the year?

Using Microsoft Excel, let us draw the following table:

Microsoft Excel 2003: Inflows, Outflows, and Goal Table

In the table:

(1) Inflows = the accumulated monthly salary in 2011 = AED10,000*12 = AED120,000

– We can write in the empty cell next to “Inflows” the following: =10,000*12
Microsoft Excel 2003: Inflows, Outflows, and Goal Table
– We will call this cell “A“.
– Microsoft Excel will do the calculation. We hit Enter in the keyboard to read the answer.

(2) Outflows = total monthly expenses in year 2011

– Leave the cell to its right empty. Goal Seek will later write the answer for you.
– We will call this cell “X“.

(3) Goal = Inflows – Outflows = A-X = AED120,000-X = AED100,000

– We must define this equation in the cell to the right of “Goal”. Write =, then select the cell A and hit in your keyboard and then select X, and hit Enter.
Microsoft Excel 2003: Inflows, Outflows, and Goal Table

Without using Goal Seek, we can find X. It equals to AED20,000.

Now, in Microsoft Excel, click on Tools from the menus bar and select Goal Seek. You must see the following command window:

Microsoft Excel 2003: Goal Seek Command

Set cell: is our Goal cell. Select it from the Goal Seek command window by clicking on the icon next to Set cell:

Microsoft Excel 2003: Goal Seek Command

… and then select it and hit Enter:

Microsoft Excel 2003: Goal Seek Command

The goal is to accumulate AED100,000. We write 100,000 in the box next to To value:

Microsoft Excel 2003: Goal Seek Command

By changing cell: is cell X, the Outflows. We select it, hit Enter, and then clicking on OK. Microsoft Excel found the answer:

Microsoft Excel 2003: Goal Seek Command

Advertisements

Tags: , ,


%d bloggers like this: