Chapter 12
IN THIS CHAPTER
Calculating the cash required for budgeted asset purchases
Determining the depreciation on budgeted capital expenditure
Figuring out the written-down value of assets for inclusion on the balance sheet
The last two chapters deal with building financial models at quite a high level. In this chapter, you delve into more detail in one part of a financial model: the capital expenditure (CapEx).
In Chapter 10, I explain the process of purchasing assets and calculating their depreciation. For example, you purchased a coffee machine, as well as fixtures and fittings. These purchases were reflected in your cash flow statement, but you also needed to calculate their depreciation based on their useful life. You used this amount in the Income Statement and showed it on the Balance Sheet in order to show the current value of fixed assets. The way this was calculated was fairly simple.
In this chapter, I explain in more detail how to model depreciation. Here, you take a list of existing and budgeted CapEx items, convert them into a cash flow schedule, calculate the depreciation, and use the depreciated amounts to calculate the written-down value on the Balance Sheet. You also see what happens when an asset is fully depreciated and how to model this.
In this case study, you’re charged with assembling the CapEx component for the coming year’s IT budget. You’ve managed to pull from the fixed assets register a list of assets, the purchase price, and the purchase date. You also have a wish list of items your department wants included in the next budget. You use the information you have to model the cash flow and the depreciation.
You can download the blank template, File 1201.xlsx, at www.dummies.com/go/financialmodelinginexcelfd
. The Calculations tab contains the data you already know and is designed for changes to be made by the user (a colleague or client who is not necessarily expected to understand how the model works) or yourself at a later date. The Assumptions tab contains assumptions you know; anything else you think of during the model-building process can be documented here as well.
The first thing you need to do is enter the time frames. Almost every model has an element of time series data, and it’s important to get this right from the start. You need to decide at what level of time series detail the model should be built. For more information on modeling time frames, see the section in Chapter 3. Most budget models, like this one, will be monthly. Many models are annual, such as the DCF model built in Chapter 11. Some are modeled weekly and many, such as cash flow models, are calculated on a daily basis.
In the following sections, you set up the model template with a variable time series so that the model will be reusable in the future. You also set up the titles, which also calculate dynamically as time goes by.
When building the time series for this model, you can easily type in Jan-19, Feb-19, and so on into the Calculations tab, but by doing this, the model can be used only once in its current form. If you want to use it again, the following year, you’d need to update each and every date manually.
A much better idea is to enter the start date as an assumption, using a named range, and each time you use it in the model, refer to the named range. For more information about creating, using, and deleting named ranges, see Chapter 6.
Follow these steps to set up the time series for the budget template:
Type Start in the name box in the upper-left corner, as shown in Figure 12-1.
Now you can use this named range in your formulas as a starting point to model the time series.
Select the Calculations tab and in cell H2, type =Start.
The value Jan-19 appears. Note that the day of the month does not show because of the way cell H2 has been formatted.
In the Font section on the Home tab, change the font color to white, as shown in Figure 12-2.
In cell I2, you now need to add the rest of the dates. Instead of typing it in manually, you should link it to the start date now in cell H2. There are 31 days in January, so the formula =H2+31 would do the trick but copying it across would make your dates slightly inaccurate and cause problems later on. Instead, you can use an EDATE function.
Select cell I2, and enter the formula =EDATE(H2,1).
By entering the 1 in the second part of the formula, you’re given a date that is exactly one calendar month from the start date (H2). The value in cell I2 is Feb-19.
Copy cell I2 all the way across the row to S2, so that you have all months entered from Jan to Dec.
Just for fun, go back to the Start date assumption on the Assumptions page, and enter the formula =TODAY(). It will show the month and year of today’s date. Go back to the Calculations page, and you’ll see that the budget starts from this month and runs for 12 months into the future. Use the Ctrl+Z shortcut to undo and change the start date back to Jan-19.
While you’re here, you should also add in the dates for the depreciation calculations from column Y onward.
Select cell Y2.
Now, you could simply link this cell back to cell H2, but that would be a case of spaghetti links (see Chapter 14). It will work, but it’s not good modeling practice.
Copy all the cells in the range H2 to S2 across to Y2 to AJ2.
This will link back to the source. Now you’re ready to build the budget.
Before you get into the modeling, take a moment to update the titles in red. It’ll be helpful to show the year in the title, but you don’t want to hard-code the year, because that means the template can’t be reused. Instead, use the YEAR() formula coupled with the ampersand (&) to make a dynamic title:
These two formulas in cell A1 and A11 will pull out the year of the start date only, and will automatically change when the start date changes. For another example of using the ampersand in dynamic text, see the example of linked dynamic text in Figure 4-4 (see Chapter 4).
Now that you’ve set up the layout of the model, you can start to model the numbers. The Calculations page contains all the information you need to create a cash flow schedule and calculate the depreciation. The top section you’d obtained from the fixed assets register, and the second part is the budget for the coming year.
The first thing you need to do is calculate the total line item amounts in column E. Follow these steps:
Select cell E25 and press Ctrl+=.
This inserts a total.
Check your totals against those in Figure 12-3.
Now, you can use the dates to populate the cash flow schedule. Don’t worry about the prior period for now — just focus on comparing the dates in the time series to the spend date in order to populate the cash flow schedule.
In cell H4, enter =IF($F4=H$2,$E4,0).
The calculated value in cell H4 is zero because the spend in row 4 occurred in the prior year, not Jan-19.
Be sure to use your mixed cell referencing (using the dollar signs) correctly. For a refresher, see Chapter 6.
Copy this formula all the way across and down the block of data.
By doing this, you’re following good modeling practice by having consistent formulas in blocks of data wherever you can.
Compare your totals to Figure 12-4.
If the referencing has been done correctly, most of the values should be zero, with a few values showing.
You’ll notice that no values are showing for the prior period in column G. You need to add a different formula here, which shows the spend value only if the spend occurred prior to the start date of the model. Again, refer to the named range you created earlier because the start date is an arbitrary value that will change.
Select cell G4 and enter =IF(F4<=Start,E4,0).
The calculated value in cell G4 is $5,250.
Take a moment to understand what this sheet is now telling you. You’ve taken the total spend for each item in column E and spread it out over the full year. The totals of each of the columns G through S should be equal to the total of column E. If they aren’t, you need to figure out why.
Follow these steps:
Select cell E26 and enter the formula =E25-SUM(G25:S25).
The calculated value will be zero because the totals are identical.
If you want, use conditional formatting to color the entire cell red if the error check has been triggered.
If you choose to do this, select Conditional Formatting on the Home tab of the Ribbon, click Highlight Cells Rules, select More Rules, and create a new rule under Format Only Cells That Do Not Contain a Zero. Change the formatting to red fill.
Now let’s test the error check to see if it works.
Select cell F12 and change the value to Oct-20 instead of Oct-19.
The error check in cell E26 is triggered because that date is outside the range of the budget schedule and is not being picked up.
Select cell F12 again, and type in 10/15/19 (or 15/10/19, depending on your regional settings) to enter Oct 15, 2019.
Even though the date is within the budget schedule range, the cost in row 12 still isn’t being picked up! This is because the dates in row 2 are actually the first of each month (even though it has been formatted to show only the month and the year) so it won’t pick up 15th of the month because the formula is only looking for the 1st. This kind of error is really easy to make, and it’s a good example of how an error check can identify an error that’s been made by a user after the model is complete.
Now that you’ve identified this error, you need to correct it and ensure that the same mistake doesn’t happen again going forward. There are a couple of different ways of handling this:
My preferred solution for this problem is to stop the user from entering an incorrect value in the first place, using data validations. Follow these steps:
Select the range F4 to F24 and then select Data Validation from the Data Tools section of the Data tab.
The Data Validation dialog box appears.
After you’ve added the data validation, you need to document what you’ve done to show users that they can’t simply enter any old data. If they try to enter an invalid date, they’ll get a confusing and frustrating error message unless you explain why.
To prevent user frustration, you can add an input message to the cell so that users know what sort of data they can enter. This will also serve to document this model. For more information on using data validation input messages to document assumptions, turn to Chapter 4.
Follow these steps:
Select the range F4 to F24 and then select Data Validation from the Data Tools section of the Data tab.
The Data Validation dialog box appears.
Click one of the cells.
The drop-down box, as well as the input message, appears.
Try entering an invalid date, such as October 15, or any gobbledygook.
You get an error message.
You can customize this error message on the third tab of the dialog box if you want.
Now that the spend has been spread out over the year for the cash flow, you can turn your attention to calculating depreciation. In order to do this, you need a few pieces of information:
The useful life can be worked out from the class of the asset, which has already been entered on the Assumptions page. You need to refer to the table using a VLOOKUP function. To review how to use this function, refer to Chapter 7.
Follow these steps to calculate the useful life:
Return to the Calculations page and, in cell U4, enter the formula =VLOOKUP(A4,AssetLife,2,0).
The calculated value is 6. This is the number of years the asset will last, but you need to show it in months for your depreciation calculation.
Add *12 to the end of the formula in cell U4 to convert the number of years into months.
The entire formula is now =VLOOKUP(A4,AssetLife,2,0)*12, and the calculated value is 72.
Getting units of time mixed up in a situation like this is very easy. Be sure to label carefully. On the Assumptions page, the useful life is shown as Years in the label in cell B4 and on the Assumptions page in cell U2 the label is clearly labeled to show that the time period has been converted to months.
Copy the formula down the column of data.
An #N/A error appears in cell U11. This happens because of the title in cell A11. You have two options here:
Now you know when the asset was purchased (or is supposed to be purchased) and you also know how long the asset is expected to last. With these two pieces of information, you can go ahead and calculate the date at which the asset is expected to be fully depreciated. You can use the EDATE function again just as we did earlier in this chapter to calculate the exact date at which the asset will be fully depreciated.
Follow these steps:
Select cell V4 and enter the formula =EDATE(F4,U4).
The calculated value is Feb-24.
Copy the formula down the range.
Cell V11 returns an incorrect value of Jan-00 again because there is no date value in cell F11 because of the title row. Your options are to:
Leave the Months Elapsed Since Purchase and Depn in Prior Period calculations aside for now, and start scheduling out the depreciation from column Y onward. What you need to do here is to show the depreciation only if the schedule date is between the spend date and the written-down date. Or in other words, only if the schedule date is greater than the spend date and less than the written-down date.
This formula is going to be more complex than what we have done so far, as it will contain a nested IF statement. Start by building the first part of the formula; show the depreciation only if the schedule is greater than the spend date. Follow these steps:
Select cell Y4 and enter the formula =IF(Y$2>$F4,$E4/$U4,0).
You can either type the formula directly into the cell or use the Function Arguments dialog box, as shown in Figure 12-8. The calculated value will be $73.
Notice that columns B, C, and D are missing from Figure 12-8. This is because Freeze Panes has been added to this document already. Be careful when using sheets with Freeze Panes added — it’s very easy to miss cells in a range when they aren’t showing on the sheet.
This formula takes the cost of the item and divides it by the useful life to calculate the depreciation — but only if the asset has already been purchased. Take special care with the referencing. Be sure to put the dollar signs before the row or the column you want to fix so that we can then copy the exact same formula all the way down and across the block of data.
Copy the formula across the range Y4:AJ24, and sense-check to make sure that it looks correct.
The depreciation should show only after the spend date has already elapsed on the schedule.
Again, row 11 is causing problems, but you can deal with this later, after you’ve finished this formula.
Now it’s time to add in the written-down date, which is the second part of the formula, so it should show only if the spend date has elapsed and the written-down date has not elapsed.
Go back to cell Y4 and edit the formula to =IF(AND(Y$2>$F4,Y$2<$V4),$E4/$U4,0).
The calculated value is still $73.
Ensure that the mixed referencing is correct by inserting the dollar signs in the correct places in the formula, and copy the formula across and down the block of data in the range Y4:AJ24.
Again, you can remove the #N/A errors in row 11 by clearing them or adding the IFERROR function around the formula.
Now that you’ve calculated the depreciation amounts for the budget year, you can turn your attention to prior years. You need to do this for your Balance Sheet because you need to show the assets at their original purchase price, and then deduct the depreciation to arrive at the current written-down value.
First, you need to work out how many months have elapsed since asset purchase at the beginning of the budget year so that you can figure out how much depreciation to carry forward. You can do this by deducting the budget start date (January 1, 2019) from the asset’s date of purchase (February 1, 2018, for the first asset). The formula =Start-F4 in cell W4 will return the value 334, which is the number of days between the two dates. To convert this number of days to months, you need to divide it by, say, 30 with the formula =(Start-F4)/30. This is probably close enough for our purposes in this model, but it would be more accurate to use a function such as DATEDIF, which calculates the exact number of calendar months between the two dates.
Follow these steps to calculate the amount of time that has elapsed since the asset was purchased:
Select cell W4 and enter the formula =DATEDIF(F4,Start,”m”).
The first field contains the beginning date, the second field contains the ending date of the period, and the third field contains the unit of measurement in which to show the resulting value. “d” denotes day, “m” denotes month, and “y” denotes year.
Copy this formula down the range W4:W10.
Note that this formula is only relevant for assets purchased in the past, so the bottom half of the schedule can be left blank.
The DATEIF function, although first introduced in Excel 2010, is strangely not found in the Function Arguments dialog box. To use the function, you need to type it manually into the cell. It’s rather a mystery why this function did not make it into the Function Arguments dialog box, but it’s a rather handy secret to know!
You can now calculate the depreciation for prior years in column X by dividing the original purchase amount by the useful life to arrive at the monthly depreciation amount. This amount is then multiplied by the number of elapsed months to work out how much depreciation has been incurred in prior years.
Copy this formula down the range X4:X10.
Again, note that this formula is only relevant for assets purchased in the past, so the bottom half of the schedule can be left blank.
For the balance sheet, you need to know how much the asset was originally purchased for, and how much has been depreciated to arrive at the written-down value. You already have all the pieces of information that you need to calculate this, and a place to enter it in row 27 of this model. Note that these assets will be called property, plant, and equipment (PP&E) on the balance sheet. For more information on how to incorporate these calculations in a full working financial model, see Chapter 10.
Follow these steps:
In cell G27, enter =G25+F27 to calculate the total cost of the assets.
This row is a cumulative total that will keep adding more assets as they’re purchased. Note that although there is no value in cell F27, for the sake of formula consistency, you’re still including it so that the formula can be copied across the range consistently.
In cell G28, enter =-X25+F28.
Again, you include cell F28 for consistency, even though it does not contain a value. The calculated value is –$7,708.
Add these cells together in cell G29 with the formula =SUM(G27:G28).
The calculated value is $60,242
Now that you’ve completed this model, it can be used as a stand-alone model, or each of the completed outputs can be used as inputs for an integrated financial statements model.
You can download the completed version of this model, called File 1202.xlsx, at www.dummies.com/go/financialmodelinginexcelfd
.