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.
Assumptions Make a Difference
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.
So how to go about organizing financial ratios in proforma sheets?
The best way to go about doing this is to divide the ratios into five broad categories and use these categories to classify the core category ratios within each of these classifications. The categories could be:
Liquidity Ratios – tells you if the company can pay off its debt and payments falling due within a year
Activity Ratios – important for manufacturing companies in order to analyze if the inventories, accounts receivables and account payables are handled efficiently; also tells about whether the asset utilization is appropriate.
Solvency Ratios – Debt repayment capacity of the company; could be critical for some of the companies
Profitability Ratios – margins earned and return on assets and equity
Valuation Ratios – how is the market valuing the company measured largely through relative valuation ratios
Once you have linked ratios sheet in place, you could look at the same from time to time to help you guide if you’re going in the right direction or not. In case, you’re unsure about how to check, always try to take a comparative time series analysis into account and check the performance over the years. In some cases, looking at more than one ratio together could tell loads about the company. For example, looking at the accounts receivable turnover, accounts payable turnover and inventory turnover in conjunction with each other could actually provide insights into the big picture of where the possible issues could be as far as the organizational value chain is concerned.
Most of the people who study or read about ratio analysis don’t realize how powerful it could actually get, especially when you apply it in dynamic context of financial modeling. It serves two clear purposes: firstly, it acts as a sanity check to the model’s sanctity. Sometimes, when financial modeling involves many linkages, some of the linkages are misapplied resulting in a situation where you don’t realize you have made a mistake. As difficult as it is to verify all linkages, it is impractical to keep doing that all the while the model is being constructed.
With financial ratios linked to the proforma forecasting sheets, one can easily make sure that the model is going in the right direction, and any wrong linkage would lead to one of the ratios being out of line with the others or out of line compared to other years. An example in this regard could be a situation where you have prepared the proforma income statements for 5 years, and coming down from the Profit before tax level to Net profit levels, accounting for taxation. If the company is projected to have a loss before taxation in one of the years projected and the formula is subtracting the taxes from profit before tax levels, it would create a mess in your spreadsheet, which could be overlooked.
If you had linked the ratios to the P&L previously, you would see some inconsistency on the net profit margin figure in that particular year compared to other years, and then you can go back to check if how you calculated the figure was correct.
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.
