Financial Modelling Tools & steps

What tools are needed?

To be able to build and benefit from a financial model, you need some previous knowledge of specific topics. This includes understanding the fundamentals of finance, being aware of the concepts of financial planning, and having the capacity to use various software programs for modelling.

• How to read an annual report?
•  Understanding how to analyse the financial statements of a company, including the Balance Sheet, Profit and Loss (P&L) statement, and Cash Flow statement.

It would be beneficial to have experience with MS Excel or a comparable program.

You can acquire an understanding of annual reports, balance sheets, profit and loss statements and cash flow as part of the fundamental analysis module.

When I suggest that one needs to know how to read financial statements, I’m only implying that it’s from a user’s point of view. It is not necessary to be an expert; just having a basic understanding is adequate.

Excel is useful if you have a sound understanding of basic functions and formats.

Financial modelling is a way to approach complex problems in market finance, whether it is investing or derivatives trading. Often referred to as ‘Design thinking’, it requires structured thought and careful analysis.

These upcoming chapters will hone in on the basics and financial modelling for investments.

– What are the steps involved in understanding financial modelling? 

I’d now like to present a basic outline of the process for developing an integrated financial model. I won’t go into too much detail at this stage, but we will explore each of these steps in more depth as we move forward.

Following are the steps for building a financial model –

– Setting up a layout

Arranging our layout is essential when creating a financial model. I’ll be emphasising this point repeatedly during the chapters, so please stay with me. A regular financial model will involve multiple spreadsheets within one workbook. We need to make sure that our Excel workbook is indexed correctly and formatted correctly and that the format stays the same throughout the entire model.

In the case of column ‘E’, it should always contain, let’s say 2018 data; meanwhile, columns A and B can be condensed to make indexing across each sheet easier.

At this juncture, these sentences may seem like a collection of general comments, yet as we move forward, you will gain an understanding of their importance.

– Historical data

We need to obtain and enter the consolidated balance sheet and P&L data from the Annual report of the company we are working with. Ideally, acquiring information from the last five years would be optimal. In this instance, downloading the report is essential.

The annual report should be used as the primary data source, with other 3rd party vendors being ignored.

-Assumption sheet

Remember when I said that financial modelling is an art form and not a science? Well, to help us stay close to reality, we create an assumption sheet and put all our assumptions on one sheet. A good example of this approach is the need to make sure our model’s accuracy is not affected by any distortion caused by making assumptions that are too far removed from reality.

A company’s revenue that has been increasing at 7% annually for the past five years will probably experience a similar rate of growth in the 6th year. Unless something drastic changes, we can reasonably anticipate growth in line with previous years; any higher or lower figure will not accurately reflect true performance.

– Asset and other schedules

In the model, we create something known as a ‘schedule’. With line items of larger-than-usual size, the asset schedule deals with plants, machinery, and all fixed assets belonging to the company. We arrange these numbers in an orderly fashion and take into account details such as gross block number, depreciation, net block and even CAPEX figures.

By looking at a single timetable, we can gain insight into various aspects of the company.

Apart from the asset schedule, we also create other types of schedules, such as reserves and debt schedules.

-Projections

Creating assumptions and schedules forms an essential part of the modelling process, as it allows us to project a balance sheet and P&L for either three or five years in advance.

– Cash flow derivation

Constructing the cash flow statement using the indirect method is a crucial aspect of financial modelling. Instead of relying on historical annual report data, it involves deriving the cash flow statement from the P&L and Balance sheet data. However, this process can be complex and may not always produce the intended outcome.

Therefore, we will also explore different options.

-Ratios

Once the necessary information is compiled, we can quickly create ratios and graphs for our model. The ratio document would contain essential items such as liquidity, solvency and profitability ratios.

-Valuations

In the valuation sheet, we employ the discounted cash flow approach and develop a final value for the business. This step involves incorporating one model inside another. To ensure that we don’t lose focus, we have placed precautionary measures in place; if that occurs, the sensitivity tables which we develop should aid us to get back on course.

Let’s get started now