What is financial modelling Assumptions and Projections?

I previously noted that financial modelling requires both an artful and scientific approach.

Now the art begins!

The concept behind a financial model is to use the historical financial statements in order to make predictions. Typically, this is done over three to five years. In our model, we will be concentrating on a five-year forecast.

To gain an initial insight into this, I will publish a set of questions and their answers –

– What strategies can you use to forecast the financial statements for coming years?

– You can forecast the financial statements by creating a series of assumptions.

– What steps can you take to create the calculations necessary for forecasting?

– We can assume upcoming developments based on past patterns.

– How will you measure historical trends?

– We track historical trends by considering individual line items in the balance sheet and P&L, usually by taking a ratio of one item to another. In certain circumstances, we may also factor in year-on-year growth rate.

We will delve into this further later on in this chapter.

– Once you’ve gauged the past pattern, how can you forecast what lies ahead?

– To predict future trends, one can look to past records or take an educated guess.

Have a read of what I’ve written above and bear it in mind. There may be some areas you can make sense of easily, while other parts may not quite click yet – however, I trust that by the time you reach the end of this section, your comprehension of the subject should be clear.

We need to think about our first assumption for the financial model, so let’s create an assumption sheet.

To begin, open a new sheet in the workbook and rename it ‘Assumption’ at the base.

Now, as usual, let’s go ahead and…

Index column A and B

Expand column C

Index column D

Cells E2 to I2 will represent the first year to the fifth year.

Cells J2 to N2 will be Year 5P to tear 10P

I’ve followed the same process, and this is what my Excel sheet looks like now.

The idea of the assumption sheet is to list the financial statements’ line items and project them based on our assumptions. Let us begin with the Balance sheet; we should examine these two line items in the current liabilities section of the balance sheet, i.e. liabilities and provisions –

Remember this part from the Q&A session we held earlier –

To measure historical trends, we use the ‘Gross Block’ ratio on the balance sheet. This is due to it being one of the most sizable items, and since it absorbs the company’s CAPEX.

If we examine ‘Year 2’, we can observe the percentage of liabilities to the Gross block.

Liabilities as a % of Gross Block (Y2) = 102.74/310.58

= 33.08%

Here’s how it can be done on Excel directly. 

I’m focusing on the Year 2 data, so I calculate F6 divided by F34 in the balance sheet.

One may query why it is only Year 2 that has been accounted for and not Year 1. This is because, in various areas within this module, the need arises to calculate the year-on-year growth rate, making Year 2 our baseline figure. Consequently, there is no need to attend to Year 1.

Once we have worked out the Liabilities as a % of Gross Block, let’s apply the formula across Y3, Y4, and Y5.

We can observe that liabilities as a proportion of gross income usually remain within 27-35%. Therefore, if I need to calculate the figure for Year 6, I can take the average of observed values to get an indication.

Let me do the same –

Well done! We have now produced the first item in our financial statement. Consider these few possibilities: 

In this case, I have utilised the simple average function.

The average for Year 6 is derived from the data of Years 2 to 5.

For Year 7, the average falls somewhere between that of Year 3 and 6.

To determine the rolling average, we take into account the most recent four years of data.

The average we computed lies within the predetermined range of 27% to 35%, so all is acceptable.

When performing calculations regarding these ratios, an ideal outcome is a narrow variance range. Through this, the average calculation becomes more reliable and the model much tighter.

I’m dissatisfied with the range of 27% to 35%. Exploring other ratios, such as liabilities compared to total assets or net block, could be beneficial.

Pause for a moment. What should you take into account? The liabilities in comparison to the gross block, net block, or total assets?

This is where the artistic touch comes in. There’s no definite way to approach this; I’m just using one I’m familiar with, which is considering the denominator as a gross block.

The goal is to keep the numbers calculated and reliable. Relax — this is simply a financial model built in Excel. We have the ability to make adjustments as we move ahead.

Let’s move to the next line, i.e. the Provisions under the current liabilities. Again, I’ll calculate provisions as a percentage of the gross block.

Let’s return to the balance sheet for a moment.

Under the liabilities side of the balance sheet, we have projected Provisions and Liabilities. After that are shareholders’ funds and non-current liabilities – big-ticket items that should be handled in their own dedicated space within the financial model called a ‘Schedule’. We will discuss Schedules in greater detail later in this module, but for now just consider that they are a part of the financial model.

All of the items listed are taken into account in the schedule, from which we can also generate future projections. The only item remaining on the liabilities side of the balance sheet is deferred tax liabilities.

To evaluate the deferred tax liabilities, I’ll compare Y1 and Y2 figures of 13.61 Cr and 16.95 Cr, respectively. This is to calculate the year-on-year growth rate –

(16.95/13.61) – 1

= 25.55%

Please take note that this represents the growth rate for Year 2.

Of course, you can now drag the cells for up to Year 5, and begin calculating the rolling average from Year 6.

Let us now shift our focus to the asset side of the balance sheet. It shall be discussed further in the following section.

This chapters excel sheet can be downloaded from here, including the raw data. I recommend that you make use of the raw data and construct your own P&L and Balance Sheet by hand.