Depreciation & PP&E.  Many financial modelers ignore depreciation because it “doesn’t matter” from a cash standpoint.  Remember that deprecation does have a cash impact if you are not explicitly modeling tax separately, as most model formats will calculate tax post depreciation (effectively giving a tax deduction for depreciation).  Depreciation is important from a couple of standpoints.  It helps reflect a GAAP set of numbers.  The accountants and CFOs using your model will feel uncomfortable with strange numbers coming out of depreciation.  Depreciation affects tax, net income, and long term assets (which in turn can affect many financial ratios).  Also, depreciation is informative, especially when accounting lives approximate physical or economic lives.  In these cases, watching the net PP&E of a company can tell you if they are spending enough capital to replace the assets that run the business.  When PP&E is continually declining (meaning capital expenditures are less than depreciation) it is a general indication that the business is not maintaining itself over the long run.  For all of these reasons, it is good practice to ensure that depreciation in your financial model reflects reality as much as possible.

  1. Please download the free depreciation example Excel spreadsheet here: Depreciation.  As with other examples in the advanced modeling section, this is a stand-alone example that does not fully follow proper formatting (such has having inputs directly on this sheet) for ease of discussion.
  2. You will see quickly that property plant and equipment (long term assets) are calculated alongside depreciation.  Since depreciation affects net PP&E, it is convenient to handle all PP&E and depreciation calculations together.
  3. Section 1, Gross PP&E.  The simple model examples calculate PP&E directly in the three statements.  As a general rule, a model (especially a complex one) is easier to understand and use when calculations are shown in their broadest form in detail.  This table could have been “coded” in one formula, but is much easier to see, follow, and de-bug when shown broadly like it is here.  This table starts with historic data for reasons discussed later.  The gross property, plant, and equipment shown on the end of year balance sheet is the prior year gross PP&E, plus purchases, less items sold.  Capital expenditures are broken into two popular categories, growth and maintenance.  These categories may help the modeler determine revenue.  Here, they are just both contributors to the increase in assets.
  4. Section 2, Net PP&E.  As depreciation accumulates, it reduces the net PP&E.  While this exact calculation is often shown on a balance sheet, a modeler may choose to show only net PP&E.  This section’s calculation is more for reference than typical use.
  5. Section 3.  Depreciation.  This is a “wedge” calculation. 
    1. Capital expenditures.  Capex is an input to the depreciation schedule.  Elsewhere in the model is some “engine” that determines the year to year capital expenditures.  In this example, they are simply grown year to year to provide an input.
    2. Life (Cell B22).  This schedule allows for only one life for all assets.  Often, it is possible to determine an average life for assets in service.  If there are large changes in the asset mix in the model, then you must create a separate wedge for each asset class.  An example might be a company that spends on maintenance (say 5 year life) and new machinery (say 15 year life).  If that company, in the forecast period, spends heavily on machinery in the first three years and suddenly stops (all the while spending steadily on maintenance), the weighted average life of the new assets will change dramatically.  In such cases, a single life input will not work correctly.  The formulas in the wedge use straight line depreciation, but you could use other methods as well.
    3. The wedge (Row 24-32).  Here, each year’s spending is depreciated over its life.  A separate line is required for each year to correctly begin and end the appropriate depreciation.  Again, a skilled Excel coder could likely embed this result in a long formula, but debugging and reviewing would be very hard.  Each row refers to the appropriate year’s capex, and divides it by the life.  Each row stops depreciating, by displaying a zero, when the assets purchased in that year are fully depreciated.  Without this feature, you would over-depreciate your assets (gross PP&E).  When the life is changed, the depreciation schedule changes automatically.  Many models either accumulate depreciation based on capex or divide gross assets by an average life.  Both approaches can generate large errors, especially with short lived assets (like in a tech company) or with large time periods or swings in capital expenditures.  By changing the capital expenditure growth rate and the depreciable life, you can see how a new company (or one embarking on a growth plan) can quickly become swamped with depreciation as it builds up over time.
  6. Section 4.  Prior period depreciation.  When modeling an existing company, you often know what was spent in the last few years on capex and what you are expecting to spend in the future.  However, constructing a wedge from this information will miss the depreciation associated with assets purchased in the more distant past.  In high capital industries, this is often the dominant portion of the depreciation.  You could always dig through historical data and add in the depreciation and life for all relevant years, but this is often impractical or too time consuming.  As such, you must include something for this prior period depreciation, or your depreciation will drop dramatically when you start your projections.  Here is a simple example of estimating what the “long-run” depreciation could have been by comparing your wedge (which contains some historical data) to the total historical depreciation.  Row 49, cells B, C, and D are the result of adding an estimate of prior-period depreciation to the wedge.  It compares well with the total depreciation in row 51.  As such, this constant number is carried across and added to the results from the wedge.  You may already be thinking that this is not really the case, and you are right.  These older assets will eventually be fully depreciated, resulting in some decay near the average useful life.  Said differently, that total number was created from some earlier wedge (that you don’t have).  You can see that the wedge made up of 2008-2010 depreciation (shown totaled on row 47) declines after a steady state period.  The $10,000 of pre-2008 depreciation will behave in a similar manner at some point.
  7. Section 5.  Depreciation for the model.  This adds up the continuing / prior period depreciation with the new depreciation from the wedge.
  8. Section 6.  Accumulated depreciation.  This result feeds the net PP&E calculation above.  Here the depreciation is rolled forward by tracking the changes inside of the year.  This includes the disposition of assets.  When an asset is sold, its net book value is removed from Net PP&E.  What happens behind the scenes is that the asset is removed from both the gross PP&E and accumulated depreciation ledgers.
  9. Section 7.  Asset dispositions.  This is an input to the depreciation sheet.  It is shown here to add clarity to the source of asset disposition calculations in gross PP&E and accumulated depreciation.  The assumption is that this company sells some amount of assets that are 90% depreciated for 50% of book value.  Here, a $5,000 gross asset with $4,500 (90%) of accumulated depreciation is sold for $250.  You have already walked through the affect on gross PP&E and accumulated depreciation (and therefore net PP&E).  The balance sheet is reduced by $500 in this case.  However, the company only received $250 in cash.  The difference is loss in other income.  This will reduce retained earnings by $250.  In this manner, the balance sheet remains balanced as the transaction is properly reflected (ignoring tax for the moment, cash +250, PP&E down 500 (assets down $250 in total), shareholder’s equity down $250).  Asset dispositions are less frequently modeled, but are often important in understanding historical depreciation and asset information.