Steps to create a Financial Model

Earlier units have curated the idea behind financial modeling. However, there are some essential steps to keep in mind while building a financial model of a company. So, let us begin.

“History doesn't repeat itself - at best it sometimes rhymes” said Mark Twain.

The first step in creating the model is to get the historical statements in place, because this is what will give you a sense of where the company stands as on date. Historical parameters need to be adjusted for expected developments. While patterns could be discerned from the past it is not necessary that the future would be a replica of the past.

This exercise of analysing the historical Profit & Loss, Balance Sheet and Cash flow will impart a sense of realism to the financial model and make us forecast most pragmatically. 

As you start to build models, you will be wearing three hats:

• You are the finance expert, working with the elements of the financial statements
• You should be able to understand business dynamics and to forecast business performance
• You are the MS Excel wizard, optimising the available functionality
• You are the virtual architect, structuring a model that is transparent (easy to follow), flexible (easy to change), and robust (hard to break down).

Steps Involved in preparing a model

Step 1

A good financial model is simply a translation of business plan wording into numbers. Since this model becomes the financial roadmap of the company, all endeavour should be to capture the complexities of the business and generate streamlined and accurate data flows. The first step is to gather information on key parameters affecting the business which could be broadly as follows:

Understand as to what the cost and revenue drivers for the company are and what are the macro and microeconomic indicators which could have a bearing on the same.

For a petrochemical company, revenues, in terms of Gross Refining Margins (GRM) would vary substantially in line with international crude prices and the administered petroleum prices. 

Similarly, the EBITDA (Earnings before Interest, Tax, Depreciation and Amortization) margins for a company in the aluminium space would vary proportionately with the international/domestic prices of Aluminium Ingots, Bauxite and Electricity. 

Factors such as Inflation could have a more evident bearing on the fortunes of FMCG or Consumer Durables companies because their business model is based on tapping the surplus liquidity in the hands of an individual customer. All forecasts are assumptions (some of the major ones would be price, sales growth and cost of goods sold). The trick is to be reasonable and defendable while making them.

Step 2

For internal analysis business, understand the Cost Behaviour Pattern in terms of whether it is committed or variable.

For example, a company which goes into branding the goods sourced from Contract Manufacturers or engages labour force on contract basis would have lesser a burden of fixed overheads as compared to a company with in-house manufacturing facilities and a substantial number of people permanently on its payroll. 

An assumption made today that online advertisements would drive a company’s business revenues to USD 10 million by the year 2024 would be solely impacted by the variable factor called Click- Through-Rates. If the same is 0.1 per cent, therefore a website, which receives 100,000 page visits, would probably convert roughly 100 potential sales leads per ad campaign.

Step 3

Model the relations among parameters. They are usually linear relations, e.g. manufacturing 1 shirt, priced at ₹ 250 would require 2 meters of cloth purchased @ ₹ 50 per metre and entail an overhead expense of ₹ 75, but in some cases one may observe non-linear relations as well. In either case, establishing the relationship in an integrated manufacturing is not an easy task and one has to rely on past data and subject it to different statistical tests.

Step 4

Models ought to be dynamic. Sometimes things might not work the way you thought and hence reasonable models should provide flexibility to adjust to reality. 

Incorporate sensitivity analysis into the model in order to answer What if questions. For example,

What if the selling price of shirt falls down to ₹ 205 in the above example or the raw material costs go down to ₹ 40 per meter. The user must have the flexibility to vary each model parameter, one at a time or even more than one simultaneously and observe change in outcome (profit, break-even level) and also calculate Model Elasticity (percent change in profit/percent change in parameter).

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.

Step 5

Since each model parameter could vary in a given range, the model should also have a Scenario Analysis feature where the user is able to generate outcomes under the Best, Worst, and Most-Likely cases. This would help one to evaluate risk under alternative scenarios by measuring effects of possible parameter changes. Thus, the up-side and down-side risks of adopting a particular strategy would be clearly foreseeable.

Scenarios are feasible combinations of parameters:

Best case – Highest sales prices + lowest costs (only one possibility)

Worst case – Lowest selling price + highest costs (only one possibility)

Most-likely case – Most likely combination of prices and costs

Let’s discuss how to forecast various elements – working capital, depreciation, amortization, etc. - that are linked to the financial statements in the end. We will discuss each element in our upcoming units.