Learn financial modelling Balance Sheets, P&L, and Assumptions Know About

Assumptions Part 1

– Model integrity

I want to begin this chapter by stressing a pivotal concept. I may have mentioned it earlier, but I would like to elaborate on it further with visuals to emphasise its significance.

Previously, we created a balance sheet and P&L for the helper model. This is an overview –

P&L –

And the balance sheet –

The model design ensures that the numbers are identified quickly, and links between cells are accurate by assigning FY16 data to column E, FY17 to column F and so on.

For example, if I’m trying to determine the ratio of Property, plant and equipment to Total revenue for FY18, crisscrossing between sheets is necessary. However, it’s very easy to mistakenly link up incorrect cells without even realising it.

Let us go ahead and do this. I can use the cells of Column G in the formula bar to easily calculate the result.

Examine a case wherein you mis-linked the years when calculating the ratio. It is effortless to identify the error –

In this instance, I am aware that column G in the balance sheet should be correlated with column G in the P&L. When I observe the amalgamation of columns G and F, I realise something isn’t right.

This example may seem basic, yet as the model becomes increasingly intricate, you will recognise the importance of preserving its accuracy.

– Main model

It’s time to introduce you to the main concept. Undoubtedly, many of you would anticipate me to disclose the organisation we’re focusing on and name the timeframe we’re evaluating. But I have different ideas.

I am consciously avoiding mentioning a specific company to avoid potential biases. This way, it is clear that the model template can be applied to any organisation (except banking and NBFCs) without singling out any sector in particular.

It is expected that by omitting the date references, this module will remain relevant for readers looking to undertake a financial modelling exercise regardless of when they decide to learn about it. The core principles and structure would still be applicable even many years down the line.

In this illustration, we will take into account the records of a basic manufacturing company.

I followed all the instructions in the preceding chapter and got Balance Sheet and P&L data together. Below is a screenshot of it –

Balance sheet –

I’ve reduced the size of my Excel sheet to 70%, so I can get a complete picture of the balance sheet, with numbers and formatting that may look a bit different.

This is a snapshot of the P&L.

A couple of things here –

For the purposes of this study, Year 1 up to Year 5 are being taken into account. This captures the most recent five years’ worth of information, so the data will still be relevant in the future no matter how far removed from this present moment.

The data presented in the Annual Report reflects the financial position as of the fiscal year’s conclusion, which is March 31st.

Year 1A and 6P signify the actual and anticipated data of Year 1 and 6, respectively. The projected information is based on March 31st. In other words, this allows us to contemplate what the financial statement might appear like in future annual reports.

At the conclusion of this chapter, you can download an excel sheet that features the raw P&L and Balance sheet data. I believe it’d be beneficial for you to utilise this information and create a format as we discussed. It’ll serve as a great way for you to practice.