How to build a financial model Step-by-Step Guide to Excel Sheet Setup?

Reimagine data presentation

As previously stated, the initial step in constructing a financial model is to refer to the annual report and obtain balance sheet and P&L information and input them into the Excel sheet.

This is no doubt a gruelling task, yet a crucial one since the figures you transcribe from the annual report provide the key component in the entire model. Therefore, it must be accomplished with due diligence. Do not succumb to the inclination to copy and paste data from external sources.

Different individuals have contrasting views on how many years of historical data ought to be taken into account. A popular choice is either the preceding seven or the preceding five years. If I had to choose something, I would opt for the latter. 

When I usually talk about the initial stage of financial modelling, one can imagine something like this – copying the last five-year balance sheet and income statement details from the annual report to an excel sheet.

It is indeed accurate to say that the data was copied from the annual report onto an Excel sheet, as demonstrated. However, this type of approach can be considered a ‘model suicide’.

The data is displayed in an untidy fashion. Therefore, instead of envisioning it one way, it is necessary to consider how the data can be structured in a model suitable format.

– Set up your excel sheet

To establish the necessary structure, it is important to set up the Excel worksheet in an organised fashion. All sheets within the workbook should follow the same format.

Prior to commencing the process of copying data from the annual report, there are several preliminary steps that should be taken into account, constituting a crucial part of the initial phase.

Begin a new Excel spreadsheet, add any title you desire.

My Excel sheet appears as such after shrinking Columns A, B, and D; Column C has been expanded. ‘Indexing’, in this context, simply means condensing a column into a more compact form.

I find it helpful to get rid of the gridlines in excel spreadsheets. I think this helps manage the model since there are often a lot of numbers and formulas used that can be quite overwhelming.

You should be familiar with removing gridlines and freezing panes, as these are fundamental excel skills. However, if you are having trouble, take a break to review yours excel knowledge before returning to this task.

We now go from E2 to I2, indicating the years of interest. This has resulted in my Excel sheet appearing as:

We now label this sheet as the P&L statement, seen in cell A1, as depicted below.

I like to present the ‘Profit and Loss statement’ in bold font size 14. Additionally, I’ve appended a line specifying that all the figures are in INR Crores, unless otherwise stated.

14.2 could indicate 14.2 Crores Rupees, hence I have italicised it and reduced the font size to create a better aesthetic.

Above is the basic skeleton of the model. To ensure consistency, it is best to create multiple sheets with a similar structure within the workbook. This should include Balance sheets, assumption sheets, and cash flow sheets. Here’s how you can do so in one go.

Press the Control key on your system, and select several sheets in a single action. This will cause whatever changes you make to one sheet to be replicated in all the others as well.

This is the state of my sheet prior to pressing control and selecting other sheets.

All the sheets except Sheet 1 have been chosen. Since Sheet 1 is already formatted, I didn’t want to disturb it.

On Sheet 2, I repeat the steps previously discussed but not including…

Freezing panes cannot be done when selecting multiple steps – or at least to my knowledge.

Label the sheet as a Profit and Loss statement, as each sheet of this type will have its own specific name.

After setting up sheet 2 –

Kindly be aware that all the sheets remain selected. I have completed every step, apart from the ones mentioned previously. Excel will unselect the chosen sheets when you press on a non-selected sheet. Therefore, feel free to click on Sheet 1 to deselect them.

Next, review Sheets 3, 4 and 5. They should be identical to Sheet2; select Cell D3 in each one and unfreeze the panes.

At the moment I’m unclear what I will do with Sheets 3, 4, and 5, but I have confirmed that Sheet 2 is for the Balance sheet. Therefore, I’ll label it as ‘Balance Sheet’ (cell A1).

I suggest renaming Sheet 1 to ‘Profit & Loss’ and Sheet 2 to ‘Balance sheet.’ To do this, simply keep your cursor on the sheet you wish to rename and right-click using your mouse.

Have a moment to reconsider your achievements thus far.

This is a key milestone in your financial modelling journey. You’ve made sure to construct Excel with an orderly system with five sheets, all of which have the same layout.

Having examined the model, I now understand that Column E indicates data from FY16, with F representing FY17 and so on through the entire model.

The Hygiene factor in a model is essential, and won’t be altered. My opinion on this matter is that it’s an extremely significant factor.

To round off this section, let’s transition to the next where we will transfer the data from the annual report into our excel sheet.