depreciation expense : Exploring Different Methods in Financial Modeling

  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 / depreciation expense : Exploring Different Methods in Financial Modeling

Let us utilize the proportion approach.

The company reported depreciation of 41.71Cr for the total asset worth of 538.76Cr.


= 7.74%

What is the depreciation for Year 6 when its gross block value is 588.77 Cr?

7.74% * 588.77

= 45.58Cr

This estimation suggests that depreciation for the next year will be 45.58Cr, which should be reflected in the Profit and Loss statement.

The depreciation value is fixed at 7.74%.

Rather than take the proportion used in the previous year and expect it to apply for the following one, try computing the depreciation to gross block ratio for all past years, then taking an average of those last five years to forecast future estimates.

It will look something like this –

Y6 = Average of Y1 to Y5

Y7 = Average of Y2 to Y6

Y8 = Average of Y3 to Y7


I’ll opt for the first approach since it seems to be the simplest.

If you possess a sound background in accounting, then exploring deeper into the net-block to get to the bottom of the depreciation figures may be an option.

By using the first method, I can quickly enter the formula in the P&L.

In cell J16, I divided the Depreciation expense stated in the P&L for Year 5 by the gross block stated in the Balance Sheet for the same year; thus generating the depreciation proportion. This figure is then used to calculate the Depreciation Expense for Year 6 by multiplying it with the projected gross block.

The resulting value is the depreciation amount for Year 6, which is projected. This same math can be applied to all future years in order to calculate the depreciation expense for each year.

We also took a small step forward with our financial modelling, since we created a P&L projection.

At this juncture, my profit and loss account appears like this –

Now that this is done, let’s review the asset list.

– Accumulated depreciation

Some of you may have already guessed what we will do next. It’s quite straightforward: we update the asset schedule sheet with the current year depreciation figure from the P&L and use the base rule to work out the total accumulated depreciation.

I have calculated the P&L numbers and used them as part of my computation.

The closing balance for Y5 is 223.68Cr and this serves as the opening balance for Y6. To work out the closing balance for Y6, add current year depreciation – although we don’t have any visibility on this line item so can only assume a value of zero for all future years.

I’ve employed the basic rule to work out the concluding balance of accrued depreciation. The net block is composed of the initial block minus the accrued depreciation, which I have approximated for coming years.

You can get the amount of accumulated depreciation from the asset schedule and then use that figure to do the net block calculation on the balance sheet.

We have now incorporated the Fixed assets section into the balance sheet.


    Get the App Now