Financial modelling excel

  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 / Financial modelling excel

Assumptions, facts, and format

By placing my cursor in cell E6, I’ve made clear that the number 462.30 was taken straight from the balance sheet and not generated by a calculation. To put it another way, this figure is hardwired into the spreadsheet.

In the financial modelling world –

The number we’ve hardcoded is accepted as a fact since it’s taken directly from the yearly report.

We can view the figure as an assumption, as we compute it using mathematics.

It is fundamental to be able to differentiate between the facts and assumptions in a financial model, so that the user of the model can comprehend which figures originate from AR and those which are calculated. Now, the errors can also be spotted easily.

To come up with the grand total, we need to add up all the non-current assets first. Here’s how it can be done:

I used the ‘=sum()’ function in excel to come up with the total for the non-current asset, however this is considered an assumption since I calculated it myself. To differentiate between assumptions and facts, I chose to colour code them.

To avoid repetition, one can easily colour-code this by pressing the function and F5 keys simultaneously. A pop-up window will now appear.

Hit ‘Special’ and pick out only constants and numbers, as illustrated below.

Once you select OK, Excel will identify and accentuate any numerical entries or factual values.

Without deselecting the numbers, you may choose whatever colour you’d like. Personally, I prefer light blue, but it’s entirely up to you.

Once you have picked the shade you desire, make sure to keep the entire sum of non-current resources in bold.

Once you have completed the initial step, then the rest of this process should be relatively easy. To get the needed data, simply pull out the figures from your balance sheet and profit & loss statement and insert them into a spreadsheet.

– Other things to note

You may be asking why it’s important to sum up the numbers and assign each one a specific colour. For instance, why not just copy the sum of non-current assets?

It’s important to calculate these figures ahead of time as they will then be used to project line items on the balance sheet. This ensures not only model accuracy but also consistency in our calculations.

Before we finish up this chapter, here are a few formatting pointers –

  • Ensure that all figures are aligned to the right and round off the answer to two digits.
  • Give all heavy numbers emphasis, usually this would be the subtotal and total figures.
  • Double-bordering cells can be used when it’s necessary.

I’ve finished entering the data into the balance sheet. Pay attention to a last few things.

I have tallied the total assets by summing up non-current and current assets. In a similar manner, I have done the same for liabilities.

To ensure my balance sheet stays balanced, I perform a ‘True’ and ‘False’ check. It’s imperative to remember that when assets equal liabilities, the balance sheet is in equilibrium.

My balance sheet is in equilibrium due to the fact that total liabilities match total assets. I won’t go over the specifics of how to extract P&L data, as it’s a similar process. If you come across any issues during the process, though, don’t hesitate to ask – I’d be more than happy to help out. Hopefully, your P&L will mirror this!

When attempting the P&L, you’ll notice I’ve deliberately expanded the ‘other expense’ in the expenses section to highlight that breaking down constituents of a hefty line item is beneficial. By modelling these lines at a more granular level, we can make sure our model is realistic.

Take note this is merely a helper model. It served as an illustration on how to go about transferring data from financial statements into excel. Now, we’ll be moving on to the main model, which will be covered in the following chapter.

Regarding financial modelling, ‘Historical data’ is usually the starting point. However, attaining this goal may involve a great number of individual steps. It’s best to bear in mind that this applies to all subsequent stages too.