Assumptions Assumptions…

[JP-Facebook-Like]

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.

Using ratio analysis in financial forecasting – II

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.

Using ratio analysis in financial forecasting – I

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.