CFP|Financial Planning India

In India, organisations do not often have the necessary skill sets in conceptualising and developing modelling solutions that can facilitate an accurate evaluation of critical complex business decisions.

Financial models are a key element in most major business decisions. A financial model is prepared whenever any organisation is considering project finance, bidding for a project, evaluating acquisition target, carrying out monthly financial planning, conducting capital structure studies, etc.

They are useful tools that allow business options and risks to be evaluated in a cost-effective manner against a range of assumptions, identify optimal solutions in evaluating financial returns and understand the impact of resource constraints to make the most effective business decisions.

Indian companies are becoming increasingly integrated with the global economy by establishing/acquiring operations overseas, increasing the export/import intensity of their businesses, entering into global alliance, raising funds from overseas market, etc. Even within India, companies can no longer afford to assume fixed capacity and a ready market scenario.

In view of these factors, they require robust financial models which can help them in carrying out the analysis of the complexities of each country on their operations, consider multiple currencies in their models, evaluate varying capacity as well as capacity utilisations to find out the optimal capacity under varying industry demand-supply scenarios and similar other cases.

In India, organisations do not often have the necessary skill sets in conceptualising and developing modelling solutions that can facilitate an accurate evaluation of critical complex business decisions. Their financial models are often not based on best practices (as described later) and a comprehensive, independent review of the financial model by experts is seldom done.

A best practice model is:

easy to understand — by using a transparent design;

reliable — by using control checks so that an error is automatically flashed;

easy to use — so that one can be more productive in using the model for analysis rather than struggling just to produce simple results from a badly designed model;

focussed on the important issues — so that one does not waste too much time in development of immaterial items;

These benefits can be achieved by using the generally accepted principles and techniques while developing a financial model. These are explained under the following stages:

Planning

Before one starts to build a financial model, it is essential to plan the design and structure of the model. The planning stage of the model is very important and should not be overlooked.

This is particularly true with a large, complex model that has a number of separate worksheets and where a number of sensitivities are required to be operated on them. Some of the key points to keep in mind at the planning stage are:

Have a clear understanding of the aims and objectives of the model so that the model can be designed to meet these objectives. The objective of the model could be to generate profit and loss account, balance sheet and cash flows statement or to compute the price to be paid for the target or to compute the target sales volume so as to achieve the desired profit level.

Set up modular structure so that it is easy to build the model initially and easy to maintain and update the model later. For example, the entire model can be broken into various modules such as production volume, sales value, working capital, tax computation, depreciation computation, etc.

Make the structure as flexible as possible so that one can change an individual module later on without changing the rest of the model.

For example, the key output of the production module which is being taken to other sheets can be summarised at one place within the production module.

Thus, if required at a later stage, one can introduce the capacity expansion in the production module without having to change the rest of the model.

Ensure the model has adequate documentation so that it can be easily modified later. The documentation would help in understanding the design and structure of the model and would be particularly useful when the model is required to be operated by a person other than the person who has developed it.

Set up cell logic (for example, the maximum and minimum limits of debt-equity ratios, the maximum capacity utilisation, funding constraints, etc.) of the modules so as to minimise errors when building the model and make it easier to find and correct errors.

At this stage, one should also consider the inputs or assumptions that will drive the model. Often, people assume that the sales would increase by 20 per cent and the operating profit margin would improve by 2 per cent without giving sufficient thought about how the same would be achieved.

It is recommended that one should give sufficient attention to the underlying factors so that the sales and profits figures can be supported by explicitly stated underlying assumptions and calculations.

Another added advantage of this approach is that it forces the person to question the validity of the inputs and, thus, ensures that a well-developed model is not producing garbage results due to garbage inputs being used.

Every financial model would have multiple worksheets and calculations.

To help users understand the model, one can put a schematic diagram on the front sheet of the model for describing the various sheets and indicating how they interact with each other. One can also use hyperlinks to the relevant modules, thereby making the first sheet as a navigational tool, helping users to find their way around (see diagram).

Build

At this stage, the actual coding of the model takes place. There are many ways to perform any one task in Excel. The best practices are those which are more flexible and robust.

The recommended principles and technique that should be followed at this stage are:

Separate inputs, calculations and outputs: It helps in easily locating all inputs in the model and ensures that there is only one cell for one input — for example, tax rate. Thus, sensitivity can be operated easily with higher level of confidence about accurate operation. Colour coding to identify inputs, external links, etc. should also be used.

Maintain a logical flow: Ensure that the flow of the calculations in the model are from left to right, top to bottom and from front to back (in terms of module, for example, the sales module should come after production module).

Ensure consistency between sheets: The same time-period should be in the same column in all sheets.

Use consistent formulae: Use the same formula across the whole row. Also, actively look for the possibility of using the same formula in adjacent down rows. They are easier to change, simpler to test and more likely to work

Avoid the tendency of embedding inputs into formula: This makes it harder to change the inputs or formula and reduce the flexibility and transparency of the model.

Do not carry out many computations in one formula: It increases the probability of using incorrect logics and also makes it difficult to understand the logic of the formula.

Avoid circular references: This reduces the credibility of the model and, as far as possible, avoid them.

Delete redundant data: Do not hide redundant data by hiding the rows and/or columns. They may affect other computations and lead to errors, especially when a model is amended subsequently.

Maintain sign convention within a particular group of items: Use (+) numbers to indicate items which need to be added and (-) to indicate items that need to be subtracted. For example, in income side of P&L account, excise duty, sales return can be shown as negative numbers while sales, other income can be shown as positive numbers. It helps in avoiding wrong summation of these items by mistake.

Build control check: Last, but not the least, use as many control checks (for example, total assets = total liabilities, sum of product mix = 100 per cent, opening stock + production/purchases – sales – closing stock = 0, etc.) as possible to ensure that there is no conceivable error in the model. Also, they should be built in such a way that they automatically flash whenever an error occurs in the model.

It would also be a good idea to familiarise oneself with the advanced features of Excel and frequently using them, for example, the data validation feature avoids the accidental selection of incorrect value for an input cell.

Test

Under this stage, a cell by cell detailed review of the model is undertaken so as to check the mathematical accuracy and logical integrity of the model. To test a model is to root out errors and inconsistencies and to increase confidence in the results produced by the model. Testing boosts the confidence that the model is producing reliable results.

The review and test phase of a financial model is the most critical part of the entire model making process, but is often the most neglected one. The level of testing would depend upon the risk associated with the errors. If a financial model is developed to make multi-million dollar investment decision, one has to ensure that it should be tested exhaustively. One may also consider taking the help of independent expert for carrying out the testing of the model.

Use

The model is now ready to be put to use for the purpose it was developed. With the passage of time and with the changes in environment, it may be essential to amend the model. While smaller to medium changes may be carried out in the same model, one may need to build a new model if the extent of changes requires substantial amendment of the model.

Financial modelling is an art and like any other form of art, one needs constant practice and commitment to develop expertise in this area.

Financial models are a key element in most major business decisions. A financial model is prepared whenever any organisation is considering project finance, bidding for a project, evaluating acquisition target, carrying out monthly financial planning, conducting capital structure studies, etc.

They are useful tools that allow business options and risks to be evaluated in a cost-effective manner against a range of assumptions, identify optimal solutions in evaluating financial returns and understand the impact of resource constraints to make the most effective business decisions.

Indian companies are becoming increasingly integrated with the global economy by establishing/acquiring operations overseas, increasing the export/import intensity of their businesses, entering into global alliance, raising funds from overseas market, etc. Even within India, companies can no longer afford to assume fixed capacity and a ready market scenario.

In view of these factors, they require robust financial models which can help them in carrying out the analysis of the complexities of each country on their operations, consider multiple currencies in their models, evaluate varying capacity as well as capacity utilisations to find out the optimal capacity under varying industry demand-supply scenarios and similar other cases.

In India, organisations do not often have the necessary skill sets in conceptualising and developing modelling solutions that can facilitate an accurate evaluation of critical complex business decisions. Their financial models are often not based on best practices (as described later) and a comprehensive, independent review of the financial model by experts is seldom done.

For more information on Financial Modeling program please visit NSE India( National Stock Exchange website http://www.nseindia.com/content/ncfm/ncfm_ibam_fmpros.pdf

Forum for finance certifications query