There are certain situations when our modeling of property, plant, and equipment require more detailed calculations.

This is particularly the case if we're modeling startup businesses, project finance models, or when we're modeling a company which is undergoing significant amounts of CapEx.

This latter situation is particularly relevant for very capital intensive sectors, such as telecoms and utilities, where CapEx is typically incurred in waves rather than being constant or grown gradually from one year to the next.

For all these situations, we should consider forecasting depreciation separately for each CapEx year.

To do this, we start by forecasting the CapEx, which is expected for each year.

Here we have CapEx forecast for years one to five.

Although these forecasts will be laid out horizontally in our model, it's usually easier to build our calculations vertically.

We can use the transpose function in Excel to pick up the horizontal CapEx forecasts and lay them out vertically.

Once we have the CapEx for each year laid out vertically, we can then build a depreciation calculation for each year of CapEx.

As this will involve lots of calculations, it's helpful to automate the calculation, and we usually do this using the count A function and an if statement.

So how does this work? Well, first, the count A function counts the number of cells in a range which have a value.

So for example, applying the count A function to the values in row one from years one to four would result in a four.

Now, if we want expansion CapEx to be depreciated over 20 years, we can then use an IF formula to allow depreciation only in years where the count A results in a value less than 20.

If the count A results in a value of more than 20, then no depreciation is required as the initial CapEx has been fully depreciated.

One further thing to note is that the calculation shown here is just for expansion CapEx.

We can of course, do the same for maintenance CapEx using the same calculations when building these calculations.

We should also remember that depreciation is only recognized on an asset once it's fully operational within the business.

If the expansion CapEx relates to a large piece of plant, which is being constructed over a three year period, and which will become operational only at the end of year three, then depreciation on that CapEx would only commence in year four once that plant becomes operational.

No depreciation is recognized during years one to three.