26/08/2013
Excel is a software package which allows you to juggle with numbers utilizing its different functions and is probably used almost by every professional though with different levels of expertise.
Let’s consider a simple case:
A family has its yearly income and expenses as Rs 10 lacs and Rs 8 lacs respectively. They invest their savings at 7% rate of return (after taxes) every year and want to achieve a target savings of Rs 1 Cr. Assume that family’s income rises by 10% while their expenses too go up by 8% per year.
They are looking to analyze the following:
1) How much time it will take them to reach their target number.
2) What should be the rate of return if they want to achieve the target within say 15 years or enhance the target number to Rs. 1.5 Cr.
3) Since it’s uncertain that what will be rate of return per year or what will be the % rise in income every year. They want to analyze the different scenarios (say, in a tabular form) to assess the impact of varying rates over the time period required to achieve the target.
(Assume rate of return as 5%, 6%, 7%, 8%, 9% and 10% while rate of rise in income can be taken as 2%, 4%, 6%, 8%, 10% and 12%).
4) They want to assess that which factor impacts their saving target the most. Is it % rise in income, % rise in expenses, initial absolute saving levels or % rate of return.
Ok, there are multiple ways to complete the analysis in Excel but one of quickest and simplest technique is “What-If Analysis” (MS ExcelDataData tools What-If analysis). It further has different functionalities (Goal-seek, Scenario manager, Data tables)
One can find a lot of interesting stuff on this in digital world. Explore, play and feel great as not many of us know about the efficacy of this.
Moreover do appreciate the importance of ‘Compounding of Money’ in this sample case as it’s one of founding pillar of Financial planning and Analysis