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.
[iframe width=”694″ height=”407″ frameborder=”0″ scrolling=”no” src=”https://r.office.microsoft.com/r/rlidExcelEmbed?su=-1668697156039599496&Fi=SDE8D798C7188E1A78!126&ak=t%3d0%26s%3d0%26v%3d!AMmKlXKXd7np6UE&kip=1&wdAllowInteractivity=False&AllowTyping=True&Item=’CapEx’!B6%3AN24&wdHideGridlines=True&wdDownloadButton=True”]
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.
[iframe width=”694″ height=”228″ frameborder=”0″ scrolling=”no” src=”https://r.office.microsoft.com/r/rlidExcelEmbed?su=-1668697156039599496&Fi=SDE8D798C7188E1A78!126&ak=t%3d0%26s%3d0%26v%3d!AMmKlXKXd7np6UE&kip=1&wdAllowInteractivity=False&AllowTyping=True&Item=’CapEx’!B28%3AN36&wdHideGridlines=True&wdDownloadButton=True”]
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.