Although most financial instruments offer a certain rate for a fixed period of time, there are certain instruments that offer a certain rate forever. Such instruments are called ‘Perpetuity’.

Two most common calculations pertain to calculating the value of an annuity that offers the same rate throughout and one that increases payments at a particular rate throughout.

Some people think that since the payments keep coming in forever, it is impossible to value an annuity. Well, there is a way to do it, and good news is that’s very simple!

Check out the attached Microsoft Excel Sheet “Annuities in Action” to find out how!

In many investment choices, people often have to select projects to invest in and choosing the best one has to base on some common sense measure that is understood widely. Net Present Value, or NPV is basically all cash flows (negative = cash outflow/investment, positive= cash inflow) taken at the current time period.

With MS Excel, it’s increasingly easy to calculate this measure, but many people mess it up the first time they use MS Excel NPV formula. Contrary to what one would expect, NPV formula doesn’t take into account the negative cash investment at the beginning of timeperiod. So for the NPV formula to work out well, you need to simply add up the current cash outflow/investment needed figure to the NPV result.

Have a look at the attached Excel sheet for your reference (Just click the picture below to access the MS Excel sheet).

In today’s post, I would be writing about two very interesting and helpful functions of Excel that could be exploited to improve efficiency when working on MS Excel:
Show All Formulas: Sometimes, when working on complex models, one needs to give a quick run-through of the entire model to check for any inconsistencies that might have been unintentionally left out. Use ‘CTRL’ + ‘~’ to see the magic. Clicking these two together changes all the functions in a way so as to make them all visible. This is very helpful when checking the financial models or understanding the models linkages quickly.

Converting Text to Numbers: Oftentimes, when people are working on information sheets with information that is contained in a set of cells, the information extraction process can be overwhelmingly high. For instance, there could be addresses of people residing in a particular city as a text, where you want to separate their postal codes/ zip codes. Considering that the postal codes are 5-letter figures, you could use this formula ‘=VALUE(RIGHT(A10, 5)).’

Using VALUE lets you extract numbers within text and convert the same to numbers in a separate cell and RIGHT (alternatively, LEFT) is used to identify the number of characters that would be extracted from a particular cell from the right or left side, for instance, in this case, it’s 5 characters from the right of cell A10.

MS Excel is a fairly powerful tool that is very commonly used by professionals ranging from data entry staff, to analysts, to corporate executives right up to the C-level. It is ironic, however, that most of those who use MS Excel in one way or another quite regularly don’t know the power of the language. According to an estimate, only around 20% of the users actually realize the functionality and power of this software.

In proforma financial models, where linkages are spread all across the various sheets in order to make sense of the numbers in the end, one of the most important things to check first are: Assumptions.

Sometimes, these are integrated within different sheets in the model in a hap-hazard fashion, however, most properly-built models have a separate tab for Assumptions. I have seen people commit three major blunders as far as model assumptions are concerned:

Ignoring or undermining the importance of assumptions and trying to concentrate on the numbers instead: I have seen executives that just want the numbers in place. These are the kind that are often lost in the numbers, forgetting the logic behind them. They feel that by looking at the numbers hard enough, they can realize how the situation would be like in the coming years and when they want some number changed, for instance sales, they just ask to change it without realizing that if they don’t look into and change the assumptions, someone else under them would.

Being too involved in some assumptions and not caring about most: These executives often have a hard time figuring out which areas to concentrate on. Pressed for time, they just want to look into some assumptions and leave the rest. The problem, however, comes up when they get too much involved in one particular assumption to the extent that they lose sight of the big picture. For example, the amount of Capital Expenditure (CapEx) to be done in the fifth year of projection to be increased by 20% rather than 15% YoY basis. This is problematic, as all this does is to lose the connection with the overall big picture of the model.

Not carefully checking the model linkages to the assumptions: As simple as it sounds, few analysts, bankers and evaluators forget to check model assumptions’ linkages to the actual model. I have often encountered cases where certain assumptions listed in the model are not actually linked anywhere in the financial model. For analysts, that is where they need to remain careful and make sure there is no such error because in certain cases, the implications for such a loophole could mean a disaster in decision making.

So, make sure you don’t fall prey to these basic flaws when you’re handling proforma models.

This is a valuation model of a leading petroleum company operating in Pakistan, Pakistan Petroleum Limited. You can clearly see how this comprehensive model entails working on each individual component of the business, for instance, production and sales separately eventually building up within the model.

Although many businesses are based on simpler models, and are handled very differently from this one, some analysts focus on the complexity and predicting each individual tiny component so much that they lose sight of the big picture. I will be talking about how to go about modeling in some of the future posts where you will get to learn the to-dos and not-to-dos of the modeling world. It is always best to keep things simple, which can make life easier for everyone and increase utility of the financial model many folds.

In the world of finance, handling MS Excel is expected to be an individual’s second-nature. When I see a cross-section of new and old graduates in finance, business administration and accounting, it becomes clear that this is one edge to the newer graduates which differentiates them, increases their productivity and improves their decision-making support.

MS Excel might have just grown out a simple spreadsheet system of the 80s, or 90s, but it is not just that anymore. Financial analysis, reporting, critical business operation assessment and financial modeling utilizing debt and equity modeling using proforma statements and financial forecasting, all start and end on Excel.

I’ll try to write, post videos and talk about various tools, approaches and ideas that one could utilize to effectively use Excel to prove their Excellence. So hang on and keep visiting!