Another week has passed by and it’s time to get the hang of another feature in Excel. ‘It takes time to save time’ and When we invest time to learn Excel, we are actually saving our time.
♦ Background:
You need to borrow some money – – You know how much you want (the amount of borrowings), how long you want to pay off the loan (the time period), and how much you can afford to pay each month (the EMI). But you are confused, face to face with the banker, as to what rate of interest to bargain for, so that your “loan goal” is met.
You take a piece of paper and a pen, recall your knowledge of Algebra that you learnt during your High-School and start solving it by assuming the rate of interest to be “X” …. Yes! 5 minutes down, you are delighted after arriving at the desired rate of interest. That’s when the banker tells you that the duration of the loan that you had in mind is too long for him to approve and everything else is okay. You take the pen and the paper again, start solving and Boom! This time you took just 4.5 minutes to arrive at the rate of interest.
To your astonishment, the Banker, sitting right in front of you, does the calculation in a jiffy. All he did was to feed some data into Excel and the solution was there with a click of the button. The difference between you and the banker? He knew one Excel feature more than you – the Goal Seek feature!
♦ So.. what is it about?
Excel comes with Three kinds of What-If Analysis: Scenarios, Goal Seek, and Data Tables. What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Goal Seek works differently from the others, it takes a result and determines possible input values that produce that result. Put simply, Goal Seek comes handy when we know the result that we want from a formula, but we are not sure what input value the formula needs, to get that result.
♦ Goal Seek and a CA:
The above situation might look a little redundant from a Chartered Accountant’s view point. So, here’s something that will interest you.
Your client consults you, with the following information, as to the amount of investments he shall make in schemes eligible for deduction under Chapter VI-A so that the advance tax takes care of his entire tax liability.
| Gross Total Income: | Rs. 7,75,000/- |
| Tax Rate: | 30% (Flat) |
| Education Cess: | 4% on Tax |
| Advance Tax Paid: | Rs. 2,00,000/- |
Ignore Surcharge and Interest.
To make it lucid, here is how the situation looks like:
The task is to determine the contribution to be made to LIC, so that the Balance Tax Payable is Nil.
♦ Procedure:
1. On the ‘Data tab’, click ‘What-If Analysis’, and then click ‘Goal Seek’
2. In the ‘Set cell’box, enter the reference for the cell that contains the formula to be resolved. In the example, this reference is cell E19 (amount of balance tax payable).
Note: The ‘Set cell’ must contain a formula. In our case, it is “=E17–E18” (Total tax minus Advance Tax paid)
3. In the ‘To value’box, type the formula result that you want in the Set cell. In our example, this is 0 (since we desire the Balance tax payable to be Nil).
Note: The ‘value’ must be feasible. i.e., it cannot be lower than -2,00,000 in our case.
4. In the ‘By changing cell’ box, refer to the cell that contains the value that you want to adjust. In the example, this reference is cell E12 (deduction u/s 80C).
Note: The ‘changing cell’ must directly or indirectly be a precedent to the ‘set cell’. i.e., ‘set cell’ should be able to change with a change in ‘changing cell’.
5. Click “OK”.
♦ Result:
We now know that Rs. 1,33,974/- has to be contributed to the LIC so that the objective is achieved.
In addition to the above, Goal seek can be used in areas such as budgeting and valuation.
Until next time, SEEYA!
If you have still not gone through the first Chapter, rest easy, here is the link: taxguru.in/chartered-accountant/how-consolidating-data-excel-worksheets.html or https://studycafe.in/2019/05/excel-with-excel.html
Author Bio
