Understanding the Process of Financial Model Development

Financial Model Development

Today, I am a Strategy professional with experience in Management Consulting, but I am an Engineer at heart; and one who likes to think like an engineer, someone who loves number crunching. However, for becoming a good financial analyst, it is important not to purely read the standalone numbers but to interpret them in the context of the surrounding environment and business situations. The reversal of this concept needs to be applied while making financial models i.e. when a financial model is being built, the basic understanding of the context, the purpose, the business model and the industry are extremely important apart from the knowledge of software tools such as Microsoft Excel which act more as a ‘medium of representation’ than anything else.

From my experience, I have found a significant number of similarities between software development and financial model development. Let’s first go through a very generic and simplistic software development cycle shown below. Practically, the parts of the process depicted below do not necessarily follow the same order and may also occur in parallel OR in various loops.

Similarly while developing a financial model the analyst needs to follow these steps and consider the listed aspects:

1. Gather Requirements

This is a very critical stage and the analyst should obtain the answers to following critical questions before moving on to the actually developing the model.

a.  What is the purpose of this financial model? – e.g. Is it for credit rating assessment or for project performance forecast OR acquisition of business etc.

b.  Who is the target audience that would consume this model? e.g. Is it for the clients or the regulatory bodies or for financial institutions etc.

c.  What kind of decisions would be taken based on this model? E.g. Go/ No-go decision for projects OR deciding upon budget allocation OR setting targets etc.

d.  How much time is available for developing the model? While at times, there are no exigencies, in some cases time is an extremely important factor. Knowledge of this factor would enable the analyst to decide upon the trade-off between accuracy and speed with which the financial model is developed.

2. Apply the industry knowledge

Applying the knowledge of the industry and the business model is extremely important as it will have a certain impact on the logic used for deriving various output numbers. For instance, projecting the prices of a commodity (say potato) which is used as a raw material for manufacturing the end product (say potato chips) by a company, would need a good understanding of the demand-supply dynamics for that commodity and an in depth analysis of the emerging trends and the underlying factors which can affect the demand and supply and in turn the prices of that commodity.

3. Define the scope and execution plan

It’s important to work backwards from the final delivery deadline to ensure smooth development process. Clarifications such as those listed below would be helpful in defining the structure (architecture) of the financial model

a.  How many different P&L statements would be a part of the model?

b.  For how many years do we need to project the financial statements?

c.  Should the statements be projected on a yearly basis or on a quarterly half yearly basis?

4. Develop the structure (architecture) of the model

Similar to any software, every financial model has three important elements:

a.  Input

b.  Processing/ Logic

c.  Output

The structure of the model should be such that it clearly segregates the inputs, processing (calculations) and the output. The financial model should also be broken down into various re-usable logic modules which would save the developer from unnecessary re-work and save a lot of time. However, one should be careful in re-using modules because in such cases, errors or bugs also tend to percolate along with the logic.

5. Develop and Test the modules

The first element of any financial model should be the output template, typically with time periods on the horizontal axis and various outputs as rows. This would make it easier for the analyst to keep targeting the row elements and keep working on them till all of the row elements are estimated by the financial model.

Defining the logic of deriving the outputs and the corresponding required inputs (assumptions) should be the next step. There can be various iterations at this stage and gathering the suitable inputs and development of appropriate logic for estimating the outputs may be a time taking and complex activity. At this stage, it is critical that the analyst who is developing the model stays focused and does not get carried away by the complexity and must not lose track of time or the real purpose behind development of the model.

Testing is an extremely critical element of the financial model as even a small error can create havoc in the final stages and may lead to incorrect output, which if gone un-noticed may lead to incorrect decision making.

6. Final Testing

After all the outputs have been estimated, the financial model should be stress tested with variety of inputs, especially the boundary conditions where there are higher chances of errors creeping in.

At this stage, the financial analyst must be completely well versed with the financial model and be aware of how the model should behave in various scenarios. This would enable the analyst to test out the model thoroughly.

7. Sensitivity Analysis

Sensitivity analysis is widely used for understanding the key risks and dependence of key output parameters on various input parameters or assumptions. E.g. Variation in Net Present Value (NPV) of a project due to changes in raw material costs.
Conducting and presenting the sensitivity analysis on key output parameters is an essential step towards completion of the financial model, almost comparable with icing on a cake .

Each of the stages of development described above is important and should not be missed. If these steps are followed diligently, any financial analyst is highly likely to successfully develop financial models which are fit for the purpose, with minimum rework and maximum efficiency.

Quick Summary

A financial model is very similar to a software program with inputs (assumptions), logic (calculations) and output (financial statements, key parameters). Similarly developing a financial model demands significant amount of planning and homework before the actual development process starts. The first few stages – from requirement gathering till the development of the structure – are equally important, if not more, to the actual development of the model. Following them diligently, with an open mind would ensure that the financial model meets the requirements as well as the deadlines.

Happy Financial Modeling!