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

  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 / 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.


    Get the App Now