Financial modeling basics: Laying the foundation right

Are you a beginner and planning to make a company’s financial model? Before jumping to the spreadsheet right away, there are a few points to ponder. A poorly thought out model requires lot of duplicate efforts later on. Hence it is always recommended to have a well thought out structure in mind before actually starting to build a model .  In this blog, I share a few points that might help you to make a more effective model in a productive way.

Start with a basic structure

Begin with the study of the company’s financial reporting structure (both yearly and quarterly) for the last couple of years/quarters, identify its key competitors and also briefly look at their financial reporting structure to understand the industry norm.

Some companies report financials in multiple formats or under multiple accounting standards. For example, TCS (Tata Consultancy Services) reports financials in IFRS (International Financial Reporting Standards) as well as Indian GAAP (Indian accounting standards). One should prefer the financials that are widely discussed by the management in its results presentations (IFRS in case of TCS). The common norm in the industry would also be an important factor to consider. For example, if the management uses IFRS numbers extensively in discussions but most of the peers in the industry are using Indian GAAP numbers, it would be prudent to maintain both the numbers in the model for an easy comparison of the historical financials across the sector.

Other key factors would be to differentiate between standalone and consolidated financials (i.e. financials including subsidiaries and associates), studying the flow and frequency of data (for example, automobile companies in India report monthly sales numbers) and the specific ratios/metrics relevant for the company.

Identify the KPIs (key performance indicators)

The next step would be to identify the key performance indicators (KPIs) for revenues and costs. Segment wise analysis (geographical and/or industry/product) can always be helpful in understanding the revenue dynamics. Certain other metrics (or key performance indicators) can be useful such as the capacity metrics for a manufacturing company, number of users for an IT software company with subscription based revenue and order book for a project based company. If sufficient industry data is available, one should also track the industry size (and demand) and relative market share of the company. 

For a manufacturing company, the key cost drivers would be the raw materials, employee cost and capacity utilization. Economies of scale can also provide cost benefits – it would be greater for companies with standardized output. For a services company, the key cost driver could be employee cost.

For a reasonably well-followed company by the analyst community, one of the ways to indentify the key drivers could be to read the last few earnings call transcripts and the ‘management discussion and analysis’ section of the annual report. Observation of the most frequently asked questions by the analysts could be useful. For a not so well covered company, one could follow the same procedure for its larger peers.

Work backwards

The companies report the current year and comparable previous year financials in the annual report. Similarly, for the quarterly financials, generally, the companies report financials for the current quarter, the immediately preceding quarter, and the same quarter last year. Sometimes, the companies restate previous year/quarter’s numbers in the next year’s annual results or next years or quarter’s quarterly financials. To make sure that the historical numbers are adjusted for reclassifications, one should use the most recent numbers. For instance, financials for the quarters ending Dec-13 and Sep-14 should be updated from the quarterly report of Dec-14. The same procedure should be followed for non-financial metrics.

The devil is in the detail – keep an eye on the ‘notes to accounts’

There are several hidden adjustments in the ‘notes to accounts’ section that sometimes go unobserved. For example, a change in revenue accounting policy may distort the growth comparison, a change in estimate of useful life of an asset could impact the depreciation expense and hence the profits. There could also be some one-off (non-recurring) expense or revenue items that could make the comparison with the historical period and the trend analysis useless.

Thoroughly go through the ‘notes to accounts’ section and the schedules in the annual and quarterly reports to keep track of such events and adjustments. Also, strip-off the one-off items into a separate ‘exceptional items’ line item in the P&L, so that numbers can be easily compared across period.

Apply checks and balances at several levels

Generally, the financial models have a large volume of data comprising several years and quarters of financials and a bunch of key performance indicators. A slight punching error may lead to erroneous data, which could have some material implications on the historical trend analysis. If you are preparing the model with strict timelines, the chances of an error increase even further.

One way to avoid the errors is to keep some basic checks and balances in the model.  For example, while feeding-in the segmental revenue details, ensure that sum of the segments is equal to the reported revenues. Similarly, calculated profit (or any other number) can be checked against the reported profit (for this purpose, you should hardcode the reported profit). Also, preparing common size P&L and balance sheet (everything as % of revenues for P&L and everything as % of total assets for balance sheet) can help in identifying the key deviations. The deviations, if large, can be probed further to find any errors.

Aim for a simple yet comprehensive model

The eventual model should be comprehensive enough to include all the key drivers, should contain historical data going back as much as possible (this could be helpful in trend analysis) and include the details of material subsidiaries, associates and joint ventures. However, the format of the model should be kept simple so that it is easy to understand the flow of the data. The font and cell coding should be pre-defined – for example, blue font may be used to represent all the historical inputs, red font may be used to represent all the assumptions and all the resulting forecasts may be represented in black font. Also, clearly differentiate between the historical numbers and forecasts.

There should be a separate sheet for all the key assumptions and it should be formatted in such a way that you can see the changes in the key line items (such as EPS, cash flows and the fair value) in the same sheet as you make the changes in these assumptions. In case of a complex company with multiple lines of the business, there can be multiple assumption sheets. Finally, the model should be summarized briefly in a summary tab that has key assumptions, financials, ratios, valuation parameters and the fair value.

Keep watching this space for more advanced discussion on financial modeling!

EduPristine: Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.