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...