Depreciation Expense

We already projected our depreciation expense as a percentage of sales in Step 2, so why revisit it now? Because we want to (a) confirm that our depreciation expense and capital expenditure assumptions make sense, and (b) to introduce you to depreciation expense as computed for tax, rather than accounting, purposes.

 

Create Financial Models 10x Faster with Macabacus

Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking and productivity tools to help you accelerate financial modeling and presentations.

Start your Free Trial
 

Book Depreciation Expense

In the first section of our new depreciation schedule, we bifurcate the total book depreciation expense computed in Step 2 into (a) depreciation of new fixed assets (e.g. PP&E) generated by capital expenditures, and (b) depreciation of existing fixed assets (i.e. those fixed assets on the balance sheet at the most recent historical period). Assuming straight-line depreciation of new fixed assets and the total depreciation expense already projected as a percentage of sales, the depreciation of existing fixed assets can be computed as a plug between the two.

These calculations serve as a reality check. If the depreciation of existing fixed assets implied by the depreciation of new fixed assets and the total depreciation expense becomes negative at any point over the projection period, we may need to refine our assumptions. Specifically, our assumed useful life may be too low, or our assumed capital expenditures over the projection period may be too low. Try setting the useful life assumption to three years and observe that the depreciation of existing fixed assets is negative in the two final years of the projection period. Now, try increasing projected capex as a percentage of sales to 5% over the projection period; it should exacerbate the problem.

Problems with our depreciation and capex assumptions may not manifest themselves until after the five-year period we are projecting. In practice, you would want to extend the projections out a few more years and look for problems. If your assumptions are reasonable, you should observe that the depreciation of existing fixed assets ultimately goes to zero, such that the depreciation of new fixed assets equals the total depreciation expense.

Tax Depreciation Expense

In financial modeling, we often ignore that depreciation schedules for fixed assets are different for accounting and tax purposes. While we have assumed straight-line depreciation for accounting, or book, purposes, tax depreciation often occurs on an accelerated schedule in practice. Accelerated depreciation has an important benefit—the present value of the tax shield from tax-deductible depreciation is larger when the depreciation is accelerated. In the United States, companies can use the Modified Accelerated Cost Recovery System (“MACRS”) schedule to depreciate fixed assets for tax purposes. The MACRS schedule is promulgated by the IRS, and replicated here in our model.

We select a “property class” class available in the MACRS schedule that most closely ties to the useful life we assumed in calculating book depreciation, and compute tax depreciation expense using HLOOKUPs on the MACRS schedule. Next, we add a toggle that allows us to select either book (i.e. straight-line) depreciation or MACRS depreciation as our tax depreciation of new fixed assets. Note that any difference between how we depreciate fixed assets for book and tax purposes will give rise to deferred taxes. To avoid obfuscating the deferred tax impact of other features in our operating model (e.g. undistributed earnings from equity investments), we will set tax depreciation of new fixed assets equal to the book depreciation of such assets for now. You should try toggling on MACRS depreciation after we complete our tax schedule in Step 16 and observe how the net deferred tax liability changes.

Recall from above that our projected book depreciation of existing fixed assets is a plug. Without further diligence or a handful of other assumptions, we have no better way to estimate these figures. Similarly, we have no visibility into the depreciation of existing fixed assets for tax purposes. So, for simplicity, we set the tax depreciation of existing fixed assets equal to the book depreciation of such assets. At this point, our total tax depreciation equals our total book depreciation, and there is no deferred tax impact related to depreciation.

Discover more topics

Build an operating model
In this tutorial, we will walk through how to build a general industry business operating model.
Read more
Build an M&A model
In this section, we demonstrate how to model a merger of two public companies in Excel.
Read more
Build an LBO model
In this tutorial, we will walk you through building an LBO model in Excel.
Read more
Asset and Stock Deals
The first step in purchase price allocation, or PPA, is to determine the purchase price.
Read more