Building a CAPEX schedule and Depreciation waterfall in a financial model

Projecting the CAPEX and D&A schedule is a key component in a financial model. For a detailed model, it is a good practice to build the full schedules rather than just projecting them as a % of sales or taking historical numbers.

In the attached excel file, I have built two CAPEX schedules: 

a) Apple - For basic understanding of the concept

b) Hindustan Unilever - More detailed with breakdowns of PPE

Excel Model Link

There are a couple of key formulas as well that should be kept in mind-

1) Closing PPE (Net block) = Opening PPE + New CAPEX - D&A

• Closing PPE and Opening PPE : Balance sheet items
• CAPEX : CFI section 
• D&A : CFO section

2) Net Block = Gross Block - Accumulated D&A

• Only Net Block (ie closing PPE) is shown on the balance sheet
• For Gross block and accumulated depreciation, you need to go to the notes section

Treatment of Land in PPE 

Lets suppose a company does a fresh round of CAPEX. It will have to buy some land and then build a factory on that. Suppose the company invests the following amounts in the CAPEX-

a) Land - Rs. 10cr

b) Plant & Machinery - Rs. 2cr

Now think for a minute. When a new factory/machine is set up, what is the average age for which the company can utilize it? Could be 10 yrs, 20 yrs etc.

But what about the land? Well, there is no such finite 'useful life' for it. It is due to this very reason that land is not depreciated while PPE is depreciated over its assumed useful life. 

In order to correctly project the D&A, it is important to deduct the land component from the net PPE. Then the remaining PPE can be depreciated over time.

Why is that important? If you look at the current PPE balance of a company, it could be a summation of land as well as machinery. When we are building the CAPEX schedule, we need to deduct this land so that we don't depreciate it. 

For ex: Attached below is a snip of Apple's latest 10K filing. 

The company reports $42,117mm on its balance sheet as the PPE balance. Now that PPE is divided into the following sections-

a) Land and buildings

b) Machinery, equipment

c) Leasehold improvements 

Out of the three segments, we can assume that Land and buildings are non-depreciable (assuming buildings too for simplicity). The remaining segments should be counted as depreciable.

Apple Inc. PPE schedule stepwise process-

Step 1: Project the CAPEX

CAPEX can be projected in multiple ways-

• As a % of sales
• Based on management commentary

For simplicity, I have assumed CAPEX projections as a % of sales in model-

Step 2: Depreciation waterfall

Here, we need to depreciate the existing PPE as well as the new PPE that the company is installing. Hence, we must create a waterfall schedule to estimate the D&A over the projection years. 

For how many years should we depreciate the old and new CAPEX? Sometimes you can get the estimate from the company's filings (check in the attached excel for HUL. Otherwise we can get the answer by simply calculating the average life of the assets over the years.

• Formula = Gross PPE (non-land)/Annual depreciation (check Row 17)
• Note: To get the non-land PPE, simply deduct the land and building balance from the net PPE (cell D22)

For the upcoming PPE, we can take the average life of the assets over the past years. I have considered 5 years for the old PPE and 7 years for the new CAPEX. Depreciation is calculated using the double declining method.

Step 3: Link the D&A, CAPEX and complete the PPE schedule

In this final step, simply link the values from the first two steps, and use the below formulas-

1) Closing PPE = Opening PPE + New CAPEX - D&A

2) Net Block = Gross Block - Accumulated D&A

 Link Final PPE projections: 

• Closing PPE (Row46) - Add in the balance sheet projections of the full model
• D&A (Row45) - Add in the CFO section projections of the full model

Note that the PPE schedule is only a part of the full financial model. There are various other projections and schedules that may need to be built.