What is financial modelling Assumptions and Projections?

  1. Financial Modelling
    1. Financial Modelling Introduction
    2. Financial Modelling Tools & steps
    3. How to Make a Financial Model and choose the best Company and Excel Workbook Setup?
    4. How to build a financial model Step-by-Step Guide to Excel Sheet Setup?
    5. Financial Statements: A Step-by-Step Guide to Extracting Historical Data
    6. Financial modelling excel
    7. Learn financial modelling Balance Sheets, P&L, and Assumptions Know About
    8. What is financial modelling Assumptions and Projections?
    9. Financial modelling and valuation
    10. Investment decision calculation
    11. The balance sheet’s asset side reveals the company’s line items.
    12. Revenue Model & Growth Rate in in P&L Assumptions
    13. Basics of financial modelling CAPEX and Asset Schedule
    14. Financial Analysis: Gross Block and CAPEX
    15. Gross block & Capex: Constructing the Asset Schedule
    16. Depreciation : Connecting P&L and Balance Sheet for Accurate Asset Forecasting
    17. depreciation expense : Exploring Different Methods in Financial Modeling
    18. Debt Management: Connecting P&L and Balance Sheet for Accurate Liability Projection
    19. Interest Rate Calculation & Debt Schedule
    20. Share Capital & Reserves
    21. IPOs and Under subscription : Bata’s Share Capital Dynamics
    22. Reserves & Surplus understanding Bata schedule
    23. Reserves and surplus schedule How to Build on Excel
    24. Financial modelling projections
    25. Balance Sheet Projections and Completing Reserves Schedule
    26. Cash Flow Statements Analysing Operations, Investments, and Financing Activities
    27. What Is Valuation for Investor
    28. Free Cash Flow Key Components, Formulas and How to Calculate?
    29. FCFF and FCFE uses in Mastering Free Cash Flow Calculation
    30. WACC Weighted Average Cost of Capital Analysis
    31. Market Risk Premium analysis
    32. Tax Shield and its Impact on Equity Holder Returns
    33. Weighted Average Cost of Capital and Terminal Growth in Valuation
    34. Terminal Value Understanding Perpetual Cash Flow Projections in DCF Model
    35. Learn Financial Modelling
    36. Free Cash Flow to the Firm (FCFF) Calculation with examples
    37. Stock Valuation DCF Model & Stock Market Value
Marketopedia / Financial Modelling / 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.


    Get the App Now