Preparing Fixed Asset (CapEx) forecast model in Excel – Depreciation & NBV Calculations

4
14410

In any financial model (forecasts or variance analysis) the idea is to derive expectations where business will end up if particular set of assumptions (scenario) prevails. These are basically calculations that help the decision making process.

Excel provides the necessary flexibility in designing such models. As calculations are based on assumptions, it is much easier for us to understand the effect of change in assumption and how the business and its resources will react.

Today we are looking at a simple example to understand how Excel can help us make Capital Expenditure forecasts and calculating net book value (NBV) each year using Excel formulas.

Scenario

We are required to make calculations with following information provided:

The forecast capital expenditures are as follows:

Years Capex
2015 81
2016 55
2017 95
2018 13
2019 67
2020 18
2021 54
2022 58
2023 22
2024 86
2025 58
2026 97
2027 83
2028 21
2029 59
2030 71

Inflation rate of 5% per annum is expected. The useful life of asset is 5 years and thus fixed assets are depreciated on straight line basis over 5 years.

Clearing the approach

We need to do the following:

  1. Adjust the capital expenditure amounts for inflation
  2. Make up a formula to calculate depreciation of assets
  3. Calculate net book value of assets each year

A pretty simple thing to do right? But we have one problem here. As we are using straight line basis of depreciation, one might think of adding all the assets up and then dividing it over the useful life cycle to get the depreciation for each year.

This is true but only for the first five years. Once we enter the sixth year, the asset purchased in the first year will be disposed off and no longer with us, therefore shouldn’t be added in the total cost of the asset. To get around this problem we can:

  1. either use helper columns/rows
  2. or make up a formula with clever combination of functions

Making the forecast calculations

Step 1: Download the file and open it. You will see preliminary information already available arranged in row format. It looks like this:

fixed asset base
Click to expand

Step 2: First we need inflation multiplier so that we can calculate adjusted CAPEX for each year. To calculate inflation multiplier the formula is:

(1+r)^n

where r is rate of interest and n is the number of years. So the base year will be n = 0 and with every next year “n” will increase by 1. To do the calculations in Excel we can use the formula.

Go to cell C5 and put this formula:

=(1+$B$5)^(COLUMN()-3)

Drag the fill handle to cell R5.

B4 contains the percentage value and COLUMN() function help increase the value as we drag the fill handle. “-3” is appended at the end as we are starting from column 3 therefore to make the resultant equal to 0, a weight is added.

In cell C6 put this formula and drag the fill handle to R5: =C4*C5

Step 3: We assume the opening balance of fixed asset is zero, so enter 0 in cell C9. Row 10 is about additions (acquisition of fixed assets). It will be adjusted capital expenditure figures. Therefore, in cell C10 put the formula: =C6 and drag the fill handle to R9.

Step 4: If we have the depreciation figures, we can calculate the closing balance by adding opening balance and additions during the year and deducting the depreciation of the year amount. To calculate the closing balance put the following formula in cell C12 and drag the fill handle to cell R12:

=C9+C10-C11

Step 4: To calculate the depreciation go to cell C10 and put the following formula:

=(SUMPRODUCT($C$6:$R$6,--($C$7:$R$7<=(COLUMN()-2)))-IF(C7>$B$7,SUMPRODUCT($C$6:$R$6,--($C$7:$R$7<=(COLUMN()-($B$7+2))))))/$B$7

This formula might look daunting and before we dissect it, lets understand the situation the easy way. Remember we are using straight line basis for depreciation and useful life is 5 years for each asset. Therefore, every 5 years the oldest asset will complete useful life and will be disposed off, if we make the schedule for each asset over the years with total of assets and depreciation each year, it will look like this:

 Year  Asset Cost Dep = Cost/5
2015 81 81 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 81 16.2
2016 57.75 81 57.75 0 0 0 0 0 0 0 0 0 0 0 0 0 0 138.75 27.75
2017 104.7375 81 57.75 104.7375 0 0 0 0 0 0 0 0 0 0 0 0 0 243.4875 48.6975
2018 15.04913 81 57.75 104.7375 15.04913 0 0 0 0 0 0 0 0 0 0 0 0 258.5366 51.707325
2019 81.43892 81 57.75 104.7375 15.04913 81.43892 0 0 0 0 0 0 0 0 0 0 0 339.9755 67.99510875
2020 22.97307 0 57.75 104.7375 15.04913 81.43892 22.97307 0 0 0 0 0 0 0 0 0 0 281.9486 56.38972238
2021 72.36516 0 0 104.7375 15.04913 81.43892 22.97307 72.36516 0 0 0 0 0 0 0 0 0 296.5638 59.31275529
2022 81.61182 0 0 0 15.04913 81.43892 22.97307 72.36516 81.61182 0 0 0 0 0 0 0 0 273.4381 54.6876202
2023 32.50402 0 0 0 0 81.43892 22.97307 72.36516 81.61182 32.50402 0 0 0 0 0 0 0 290.893 58.17859915
2024 133.4142 0 0 0 0 0 22.97307 72.36516 81.61182 32.50402 133.4142 0 0 0 0 0 0 342.8683 68.57366071
2025 94.47589 0 0 0 0 0 0 72.36516 81.61182 32.50402 133.4142 94.47589 0 0 0 0 0 414.3711 82.87422476
2026 165.9029 0 0 0 0 0 0 0 81.61182 32.50402 133.4142 94.47589 165.9029 0 0 0 0 507.9089 101.5817754
2027 149.0561 0 0 0 0 0 0 0 0 32.50402 133.4142 94.47589 165.9029 149.0561 0 0 0 575.3531 115.0706255
2028 39.59863 0 0 0 0 0 0 0 0 0 133.4142 94.47589 165.9029 149.0561 39.59863 0 0 582.4477 116.4895479
2029 116.816 0 0 0 0 0 0 0 0 0 0 94.47589 165.9029 149.0561 39.59863 116.816 0 565.8495 113.1698955
2030 147.6039 0 0 0 0 0 0 0 0 0 0 0 165.9029 149.0561 39.59863 116.816 147.6039 618.9775 123.795498

Use scroll bar to go through the whole data. To the right you can see the sum for each year and the depreciation calculation as well. This is exactly we have done in the formula above but it has saved us all of this work! Amazing isn’t it 🙂

Though I recommend everyone to go through an easier tutorial on SUMPRODUCT to understand how it works and also the use of “double dash” by reading: Conditional SUM in Excel with SUMPRODUCT function.

Note
There are other possible ways to do get the depreciation calculation done. For example using OFFSET function to cater the problem of useful life of asset. I will be covering the use of this function to do conditional sum in my future articles.

So once we have the depreciation calculation in place we have the forecast complete. And as formulas are based on cell values (for inflation, useful years) you can easily change them and model will update instantly.

The completed file looks like this:

fixed asset complete
Click to expand

You can download the fully worked file to play and learn. Enjoy!

Check out more articles to learn awesome use of conditional sum functions

  1. Create Edgy To-Do or Check Lists with Check boxes in Excel + Shortfall calculator
  2. Birth Dates Heat Map in Excel – How To
  3. Conditional SUM in Excel with SUMPRODUCT function

4 COMMENTS

LEAVE A REPLY