Capital Expenditures aka CapEx is the spending of money to buy or fix assets. CapEx is typically related to buildings, property, equipment. Many financial models are built to help determine growth and expansion plans that require spending money on equipment and other assets. Understanding the relationship between CapEx, deprecation, and the financial statement is a very important aspect of financial modeling.

In the current sample financial model, deprecation and CapEx are not forecasted to change. However as the business grows, additional equipment is needed.
In the new model, we CapEx spending in Years 5,6 and 8.

CapEx affect on financial statements

This will have implications for all three financial statements.

Income Statement: Depreciation expense will increase due to additional assets to depreciate
Balance Sheet: Long Term Assets and related line items will increase because of the purchase of assets
Cash Flow Statement: Cash Flow From Investing will change to accounting for the cash used to purchase the assets

While all of these changes will have trickle down effects that you need to be area of
Income statement: Increased depreciation expense will affect net income
Balance Sheet: Decreased cash because cash out the door to purchase

With CapEx purchases come depreciation. There are many plays on depreciation that we won’t get into. We’ll keep it simple with straight line depreciation for all assets.

The CapEx numbers for Years 1-4 are fixed because those are historical, so don’t modify those or it won’t match the other versions of the model.

The downloadable excel model below is the depreciation schedule for the CapEx purchases above. You can change the timing and amount of CapEx and see how that changes depreciation. You can also change the useful life of each purchase.

We have some pretty complex calculations to build out the depreciation schedule. You can keep yours very simple but just using formulas without all the if/then statement.

Integrate CapEx and Depreciation schedule into financial model

The overall formula for capex and depreciation in relation to assets is: Beginning value + CapEx – depreciation = Ending Value

These old values and formulas in the main financial model need to be replaced by links to these new line items. The only two that need to be linked are PPE, Gross and Depreciation Expense. The other three line items, CapEx, Acc. Dep, and PPE, Net, are already linked properly in the main financial model and don’t need to be modified.

Remember to check your balance sheet to make sure it balances.

** Interview Tip: This concept is a very common investment banking interview question. I’ve heard it phrased in many different ways, but the concept is the same: Talk me through the financial statement effects of an asset purchase.

rhoro

Since you are applying a full year depreciation in year 1, that would imply the asset was booked to the balance sheet on the first day of the first month in that year. A 5-year life for the asset means it should be fully depreciated on Dec.31 of the 4th year, yet you show a full year depreciation in year 5.

It would be more correct to assume the asset was booked at the mid-point of year 1, and then you would have 1/2 year’s depreciation in both the first and last year of the asset’s life — across a total of 6 years.
- Larry M
  
  You are absolutely correct.
  I’ve simplified for this model to keep the numbers clean, but you’re right to assume 1/2 year depreciation.
  In most of my real models, I do it on a quarterly basis and just sum up to get to the annual numbers.
Pietro

Wonderfull! Really helped me out!