In this workout, we are going to build a complex pp and e depreciation schedule for a model.

We've been given some assumptions already, so let's take a look at those first.

The first assumption is for revenue.

Notice that no revenue is generated during the first three years.

That's during the project set up.

Revenue is only generated once the project becomes operational, and that's important for our depreciation forecasts because depreciation only commences once assets are brought into use in a business or project.

Next, we have our expansion CapEx assumptions with expansion CapEx occurring during forecast years two to five, this expansion CapEx will be depreciated over 20 years.

Then we have our maintenance CapEx assumptions with maintenance CapEx commencing in the sixth forecast year.

This maintenance CapEx will be depreciated over five years.

Let's start by building a calculation to show gross PP and E.

That's the cumulative purchase price of all CapEx.

We start by making the opening balance in the second forecast year equal to the prior year ending balance of zero.

We then add the expansion and maintenance CapEx.

This can then be sum to show the ending balance.

We then copy this calculation to the right for our remaining forecast years.

Now let's look at the depreciation profile, which is where things start to get interesting.

Firstly, we're going to start our calculations only once depreciation commences.

That's in the first operating year, which we'll refer to as year one.

Also, we are going to want to build our calculations vertically with one row for each CapEx year.

The cumulative expansion CapEx in year one is the sum of all the CapEx from year minus two to year one, and so we're gonna sum these by summing cells C six to F six in subsequent years.

We just want to take the explicit assumption for each subsequent year, but we're gonna lay them out vertically and we can do this using the transpose function.

Now, this is an array function in Excel, and we start by selecting the array that we want to transpose to, and that sells D 18 to D 21.

We then enter the transpose function and select the horizontal array that we want to transpose from, and that sells G six to J six.

Those are the CapEx assumptions for each year. Then Instead of hitting enter, we hit control shift and enter together, and this allows Excel to recognize that we're using an array function and it will populate all the cells in the array as required.

The next step is to calculate the depreciation for each year.

And when doing this, we always aim to calculate our formulae only once and then reuse them for efficiency.

We're going to use an IF function and the count A function here to prevent over depreciation of the CapEx in each year.

I start with the IF function, and then I'm going to use the count A function.

Now, this function counts the number of cells in an array that contains values, and I'm gonna count the number of depreciation cells to the left of this cell, which contain values.

Now, the first year's calculation will reference just cell E 17 in the array, but I'm going to lock the column reference at the start of the array so I can then reuse this formula in subsequent years.

I then want to check whether the number of non blank depreciation cells is less than the depreciation years for expansion CapEx, and I'm gonna lock the cell reference to the number of depreciation years.

Now, when the condition in this if statement is satisfied, I do need to continue depreciating the CapEx in the current year.

So I'll divide the initial CapEx and I'm gonna lock the column reference here so I can copy the formula to the right by the number of depreciation years, and again, lock the full cell reference here.

Now, when the if statement is not satisfied, we don't want to continue depreciating the assets, so we just have a value of zero.

Instead. Now that we've completed this formula, I'm gonna copy it over to the right to the end of my forecasts, and then in each row I'm gonna copy down the formula for one year later through to the end of my forecasts, and again for the next year, and so on at the bottom, I can then sum the total depreciation that needs to be recognized in each year.

So those are the calculations for expansion CapEx.

I'll repeat this process for the maintenance CapEx, but as it's exactly the same process as before, I'm gonna accelerate the next section of this video.

I suggest you pause the video, have a go at completing the calculations on your own, and then check your results.

Now that we've completed our depreciation calculation, we can finish our analysis.

We need to calculate the cumulative amount of depreciation recognized in our forecasts, and that's just a base calculation.

So we start with zero, becoming the beginning balance for our accumulated depreciation, and then we add the depreciation on both the expansion and maintenance CapEx each year, and then we add this together to give the ending balance.

Then copy your formulas to the right.

The final step is to calculate net pp and E.

That's the amount displayed on the balance sheet.

We start with zero, and we then add the total CapEx from our gross PP and E calculations.

We then subtract depreciation from our accumulated depreciation calculations, and we can then calculate the ending balance.

Don't forget, we need to subtract the accumulated depreciation here, and we now have the ending balance on net PP and E.

Now copy to the right and we've completed our forecasts.