CHAPTER 5
Capital Expenditures, Depreciation, Intangibles, and Amortization
A number of items on the balance sheet are of critical importance to a company or have complex interactions with other concepts. In such instances it is best to create separate sheets for these concepts so details can be properly covered and implemented in Excel. Capital expenditure is one of these concepts. These are necessary investments a company makes in order to keep the business operational and to further expansion. As technology develops, more and more companies operate with fewer fixed assets than traditional capital expenditures purchase. Instead, their products are intangible items such as intellectual property, patents, and licenses. Intangible is another concept that we will want to understand and track in detail separately.
Regardless of whether money is invested in a fixed asset or an intangible, both items lose value over time. This complexity, known as depreciation for fixed assets and amortization for intangibles, necessitates rigorous technical methods to insure the concepts are properly implemented. Given that many businesses rely on fixed assets and/or intangibles to continue operations, we should look at each of these concepts carefully.
CAPITAL EXPENDITURES
As mentioned earlier, capital expenditures are investments in fixed assets that contribute to corporate operations. Typically, capital expenditures will be made to purchase property, plant, and equipment. Such purchases can be the entire focus of a model since capital expenditures can be significant outlays for a company. The best way to capture capital expenditure costs is by projecting future capital expenditures exactly as they are planned during the forecast period. To accomplish this, we will create detailed capital expenditure schedules that list the amount of capital expenditure on the date that it is expected to take place.
DEPRECIATION
Immediately after a capital expenditure takes place the asset begins to lose value due to normal wear and tear. This depreciation of the asset should be tracked with equal specificity as the original capital expenditure. To understand the depreciation of an asset, one needs to know the book value or cost, the useful life of the asset, and how much (if any) the asset is worth at the end of the useful life. Also, depending on the type of asset and the accounting regime the company follows, the depreciation calculation could be fairly complicated.
IFRS’s standard accounting method of choice for calculating depreciation in a given period is known as straight-line depreciation. This simple method takes the cost of the asset, less the value at the end of the useful life, otherwise known as the salvage value, and divides that amount by the useful life of the asset. As a simple example, take a laptop computer used for a financial modeling training company. If the computer cost $2,000, had a useful life of three years, and was worth $500 at the end of the third year, the periodic depreciation would be $500 (($2,000 - $500)/3). Figure 5.1 depicts the straight-line formula.
Some argue that certain assets do not lose equal amounts of value over time, but rather lose more value in earlier years than later years. This concept is known as accelerated depreciation and is used by companies with assets that lose a lot of up-front value, such as vehicles. There are multiple methods of accelerated depreciation depending on how accelerated the depreciation is expected to be. The most basic type of accelerated depreciation is fixed declining balance depreciation, which can be accelerated by factors to increase the speed of the depreciation. See Figure 5.2 for the fixed declining balance depreciation formula. Less used types include sum of the year’s digits and government depreciation systems such as modified accelerated cost recovery system.
Keep in mind that there are many more types of depreciation that exist. Some of these have been detailed in the Toolbox at the end of this chapter. Also there are some types of depreciation that are asset specific. For example, in project finance transactions one can encounter equipment that depreciates depending on use. Such forms of depreciation require the analyst to thoroughly investigate specific characteristics of the asset.
FIGURE 5.1 The straight-line depreciation method equally spaces the depreciable amount of the asset.
FIGURE 5.2 Fixed declining balance depreciation creates a rate based on the salvage value, cost, and usefulness and applies this to each period’s remaining asset value.
A BALANCE SHEET OR AN INCOME STATEMENT ITEM?
The answer to the question of whether to classify capital expenditures and depreciation as a balance sheet item or an income statement item is both; the effects of capital expenditures and depreciation are found on both the balance sheet and the income statement. However, we must be very careful about the figures that show up on each. On the income statement, depreciation is first seen at the top, prior to EBIT. This is the depreciation for the current period. However, some income statements consolidate depreciation into operating expenses and the actual amounts must be found in footnotes.
Capital expenditures are not directly found on the income statement. However, effects of capital expenditures, such as the disposal of an asset originally created through capital expenditure, could show up on the income statement if there was a gain or loss on the eventual sale.
From a balance sheet perspective, capital expenditure adds to gross PP&E. As each capital expenditure takes place, the gross PP&E number grows. Only when assets are disposed of do their amounts get removed from the gross PP&E figure. However, gross PP&E is not used as part of the total assets calculation. Instead, we must net out all of the depreciation that accumulates for the assets. This means that each asset has periodic depreciation amounts, which are aggregated on the balance sheet under accumulated depreciation. Gross PP&E minus depreciation is equal to net PP&E, which is the value counted toward total assets. Refer to Figure 5.3 for more details on capital expenditures, intangibles, depreciation, and amortization on the income statement and balance sheet.
CONCEPT STATUS
Prior to implementing the first Model Builder that focuses on capital expenditures and depreciation, we should understand a common challenge that will begin to occur. Managing the current status of a concept and the effects of that status in a projection model is a difficulty that financial model builders face. For instance, capital expenditures are usually provided by amount and the expected date of the expenditure. Prior to that date there is no capital expenditure, on the capital expenditure date there is the capital expenditure, and after that date depreciation begins. Further, depreciation will continue until the useful life of the asset is achieved or the asset is disposed of or impaired, or the accumulated depreciation of the asset is equal to the cost minus the salvage value. As we can see from these examples, both capital expenditures and depreciation have concept states that change over time. Our projection model must be flexible and implemented in such a way that it can adapt to changing characteristics of each concept. Figure 5.4 depicts the thoughts that should go into the concept status.
FIGURE 5.3 Capital expenditures, intangibles, depreciation, and amortization appear on both the income statement and balance sheet.
FIGURE 5.4 Many concepts in financial modeling change status over time.
MODEL BUILDER 5.1: CAPITAL EXPENDITURE SCHEDULES SETUP
1. Insert a sheet and name it Capex. There are many standard items to each sheet that we should create, such as the dates and timing and labels.
2. Enter the text Capex in cell A1. Then enter the following formulas in their corresponding cell references:
D2: =Vectors!D9
D3: =Vectors!D10
Copy and paste these formulas over to column Z for both rows.
3. The assumptions for capital expenditures and depreciation will be controlled from the Assumptions sheet. We should jump to that sheet and set up assumptions so we can see the formulas work on the Capex sheet. Go to the Assumptions sheet and enter the following text in the corresponding cell references:
B15: Capex Assumptions
B17: Capex 1
B18: Capex 2
B19: Capex 3
B20: Capex 4
C16: Depreciation Method
D16: Amt
E16: Capex Date
F16: Useful Life (years)
G16: Salvage Value
4. We should put proxy values in to create a few capital expenditures that will make the building process easier to implement. Enter the following values in the associated cell references:
D17: 85
D18: 25
D19: 10
D20: 0
E17: 12/31/2008
E18: 12/31/2008
E19: 12/31/2010
E20: 0
F17: 4
F18: 5
F19: 5
F20: 0
G17: 25
G18: 5
FIGURE 5.5 The primary assumptions for the capital expenditures are stored and controlled on the Assumptions sheet.
G19: 2
G20: 0
Thus far the Assumptions sheet should look like Figure 5.5.
5. Now go back to the Capex sheet and enter the text Capital Expenditure in cell B4. Also enter the following cell references in the corresponding cells on the Capex sheet to create labels for the capital expenditure schedules:
B5: =Assumptions!B17
B6: =Assumptions!B18
B7: =Assumptions!B19 B8: =Assumptions!B20
6. We are now ready to enter the main capital expenditure schedule formula in cell E5:
=IF(E$3=Assumptions!$E17,Assumptions!$D17,0)
This single formula can be copied and pasted over the range E5:J8. Notice that this formula uses an IF function to test the current date of each column and compares that date against the possible capital expenditure dates. This is what is meant by concept status. Depending on the current period and the capital expenditure date, the status of the capital expenditure concept could be either an amount greater than zero or zero. If you are having trouble understanding the dollar signs in the formula, refer to this chapter’s Toolbox for more details.
7. We should summarize the capital expenditures. Enter the text Total Capex in cell B10. Then enter the following formula in cell E10:
=SUM(E5:E8)
Copy and paste this formula over the range E10:J10. The Capex sheet should look like Figure 5.6.
FIGURE 5.6 The upper part of the Capex sheet calculates the amount of capital expenditure on projection dates that the user assumes.
More on Capital Expenditure Schedules
We just implemented the capital expenditure schedule system, and some new financial modelers are asking the question: “What if we have multiple stages of capital expenditure for the same project?” For instance, what if the capital expenditure was a large plant that was to be created in multiple periods? In such cases, my preference is to create separate capital expenditure schedules for each phase. It is easier to work into the current formula and can be better if each phase has its own depreciation schedule.
As an example, take a two-stage project where a generator is built and then an assembly machine. Perhaps the generator is built first and used as soon as it is done to assist with the second capital expenditure. This would mean that the generator begins depreciating prior to the completion of the assembly machine. It would be ideal to separate out the capital expenditures so that the depreciation schedules can be easily distinguished. Otherwise, the two capital expenditures will have blended depreciation schedules, making the formula implementation complicated and the presentation difficult to dissect.
MODEL BUILDER 5.2: DEPRECIATION SCHEDULES SETUP
1. The depreciation schedules will be built directly underneath the capital expenditure schedules. On the Capex sheet, insert the text Depreciation in cell B12 and the cell references below to establish labels:
B13: =Assumptions!B17
B14: =Assumptions!B18
B15: =Assumptions!B19 B16: =Assumptions!B20
2. The example model gives the user the option of selecting one of two of the most common forms of depreciation: straight-line or fixed declining balance. To give the user a choice between these two, we will set up a data validation list on the Assumptions sheet. Prior to that we should create the list contents 
on the Hidden sheet. Go to the Hidden sheet and enter the following text in the corresponding cells:
FIGURE 5.7 The user has the option to select different depreciation methods for each asset.
A14: lst_Depreciation
A15: Straight Line
A16: Fixed Declining
Name the range A15:A16 lst_Depreciation.
3. Go to the Assumptions sheet and create data validations lists in range C17:C20 using the named range lst_Depreciation as the reference. The completed section is displayed in Figure 5.7.
4. The next formula, which determines the depreciation amount, is complicated. To tackle it we will work on it in sections. Prior to the actual formula instruction we should take a moment to think about the concept status. This is particularly important for depreciation. If we think about the different states for depreciation, we can break it down into three: (1) prior to any capital expenditure, there is no depreciation; (2) the period after a capital expenditure there is depreciation; and (3) once the asset is depreciated to its cost-minus-salvage value, then depreciation ceases. To handle the first part, we should implement the first part of the formula on the Capex sheet in cell E13 (note that the following formula is not complete):
=IF(SUM($D5:D5)=0,0
The first part of the formula is an IF function. It references row 5, which is the corresponding capital expenditure schedule for the depreciation schedule we are creating. The IF function checks all of the prior periods for a capital expenditure by adding up the row from the historic period up to the prior period. If there is no capital expenditure in the prior periods, then the SUM function will return a zero, which is equal to zero. The IF will take this TRUE statement and return a zero value for the depreciation. If the use of the dollar sign is confusing, refer to the Toolbox section at the end of this chapter.
If the IF function is FALSE, then we need to continue on through the formula:
=IF(SUM($D5:D5)=0,0,IF((Assumptions!$D17-Assumptions!$G17) <=SUM($D13:D13),0
The next part of the formula is another IF function. This IF function tests to see whether the asset created by the capital expenditure has already been completely depreciated. It does so by taking the capital expenditure cost and subtracting the salvage value from the Assumptions sheet. If the amount produced from this subtraction is less than or equal to the depreciation taken on the asset thus far, then there should be no more depreciation and a zero is returned. Once again, the use of the dollar sign within the SUM function is integral and the Toolbox section at the end of this chapter should be referenced if this is unclear. The formula then continues with:
=IF(SUM($D5:D5)=0,0,IF((Assumptions!$D17-Assumptions!$G17)<=SUM ($D13:D13),0,IF(inputs_Capex1Dep=“Straight Line”,SLN (Assumptions! $D17,Assumptions!$G17, Assumptions!$F17)
The logic with this next section is that if the capital expenditure existed in the prior period and all of the depreciation on the asset has not been taken yet, the asset should be depreciated. However, we need one more test since we gave the user the option to depreciate using a straight-line method or a fixed declining balance method. To do this, we use another IF function that checks the named range on the Assumptions sheet where the user selected the depreciation type (inputs_Capex1Dep). If that cell has the value Straight Line, then Excel’s built-in straight-line depreciation function, SLN, is used. This function is described in more detail in the Toolbox at the end of this chapter. If the value is different, it means that the user wants to use a fixed declining balance method. We do not need another IF function since the fixed declining balance method is the only other type of method and there can be no more possibilities for the entire formula’s return. Prior to implementing the remainder of this formula, we need to create additional functionality for the fixed declining balance calculation.
5. The need for additional functionality for the fixed declining method stems from the calculation’s need to know which period of depreciation the asset is experiencing. The mathematical formula uses a compounding calculation to calculate the accelerated figures, which requires us to provide an integer-based compounding number. To do this, we will create a separate section below the depreciation schedules that tracks each asset’s depreciation period in integer format. Let’s first create labels to guide us. Enter the text Current Dep Pd in cell B21 and the following formulas in the corresponding cells:
B22: =B13
B23: =B14
B24: =B15
B25: =B16
FIGURE 5.8 In order to implement accelerated depreciation schedules we need to track each asset’s depreciation period.
6. The formula to determine which time period of depreciation the asset is in needs to check when the asset was created, in order to determine when to start depreciation. The formula also needs to know when to stop counting, which takes place when the asset has taken its full depreciation. Interestingly, we already created this functionality in the first two IF functions of the partial equation in step 7. All we must do is skip the IF function that determines the depreciation method and then finish off the final result if both conditions of the partial equation are false. These additions are shown in the complete formula we should enter in cell E22:
=IF(SUM($D5:D5)=0,0,IF((Assumptions!$D17-Assumptions!$G17)<=SUM
($D13:D13),0,D22+1))
Notice that this formula is essentially a counter. It creates zeroes when the asset is in its first year or earlier and when the asset has taken its full depreciation. However, when those conditions are not met, the formula adds a one to the prior period. This has the effect of creating a cumulative counter. This counter is a cumulative count of the asset’s depreciation period. Copy and paste this formula over the range E22:J25. The counter should look like Figure 5.8.
7. We can now finish off the depreciation calculation in cell E13. Go back to that cell and complete the partial equation so that it is the following formula:
=IF(SUM($D5:D5)=0,0,IF((Assumptions!$D17-Assumptions!$G17)<=SUM
($D13:D13),0,IF(inputs_Capex1Dep=“Straight Line”,SLN(Assumptions! $D17, Assumptions! $G17,Assumptions! $F17),DB(Assumptions! $D17, Assumptions!$G17,Assumptions!$F17,E22))))
The last part of the equation adds in Excel’s built-in fixed declining balance function (DB). This function requires all of the information for straight-line depreciation, plus the current depreciation period that we determined in step 6. For more information on the DB function, refer to the Toolbox section of this chapter. Also, make sure to copy and paste the formula created in this step over the range E13:J16.
8. Finally, enter the text Total Depreciation in cell B18. Then enter the following formula in cell E18:
=SUM(E13:E16)
FIGURE 5.9 The depreciation associated with each capital expenditure should calculate automatically.
Copy and paste this formula over the range E18:J18. Figure 5.9 shows what this section should look like.
INTANGIBLES
The concept of an intangible is tough to define considering the word itself describes something that cannot be seen or felt. In finance, intangibles have similar characteristics. Many intangibles derive their value from intellectual creations such as patents, licenses, and trademarks. These assets are legal claims to intellectual property that can add significant value to companies. Some industries such as pharmaceuticals, information technology firms, and film companies build their fortunes around intangibles.
One particular type of intangible is known as goodwill, which is the difference between book value and market value. Goodwill captures the concept that an entity might be worth more than just the book value of its assets based on its name recognition, reputation, and branding. For instance, Sony charges a premium over competitors for its products and converts its brand and reputation into real value. The market perceives value in the Sony name, which adds to the company’s value.
AMORTIZATION
Just as capital expenditures lose value over time, so do intangible assets. Patents can eventually expire, competitors can catch up technologically, and proprietary processes can become outdated or irrelevant. This reduction in value is known as intangible amortization. Be very careful with the term amortization, since the word draws its roots from the ending of a life or erosion, which can be used with other concepts in finance such as debt. In the case of intangible amortization, we reduce the value of the intangible each year for a specific number of years.
In fact, most accounting methodologies amortize intangibles in an identical manner as straight-line depreciation. Instead the cost is the cost of the intangible, the salvage value is the value, if any, at the end of the useful life, and the useful life is the perceived duration of intangible value. Typically, accelerated forms of amortization are not allowed by accounting boards. IFRS specifically uses straight-line amortization for intangibles.
One exception to intangible amortization is goodwill. Prior to 2002, IFRS amortized goodwill like any other intangible. That practice was stopped and subsequently replaced with the concept that goodwill must be examined periodically for impairment.
MODEL BUILDER 5.3: INTANGIBLES AND AMORTIZATION SCHEDULES
1. We need to set up some assumptions on the Assumptions sheet. Go to that sheet and enter the following text in the corresponding cells:
I15: Intangible Assumptions
J16: Amortization Method
K16: Amt
L16: Intangible Date
M16: Life (years)
I17: Intangible 1
I18: Intangible 2
I19: Intangible 3
I20: Intangible 4
2. We should also enter some initial assumptions that we can work with. Enter the following values in the corresponding cells:
J17: Straight Line
J18: Straight Line
J19: Straight Line
J20: Straight Line
K17: 10
K18: 5
K19: 3
K20: 0
L17: 12/31/2008
L18: 12/31/2009
L19: 12/31/2010
M17: 3
M18: 2
M19: 2 M20: 0
Figure 5.10 shows what the assumptions area should look like.
FIGURE 5.10 The assumptions for intangibles are created in a similar manner as capital expenditures.
3. Insert a new sheet after the Capex sheet and name it Intangibles.
4. On the Intangibles sheet, set up labels for rows by entering the following text and formula references in the corresponding cells:
A1: Intangibles
B4: Intangibles
B5: =Assumptions!I17
B6: =Assumptions!I18
B7: =Assumptions!I19
B8: =Assumptions!I20 B10: Total Intangibles
B12: Amortization
B13: =Assumptions!I17
B14: =Assumptions!I18
B15: =Assumptions!I19
B16: =Assumptions!I20
B18: Total Amortization
5. Create the dates and timing for this sheet by doing the same Vectors sheet reference in the following cells:
D2: =Vectors!D9
D3: =Vectors!D10
Copy and paste each of these cells across their respective rows to column Z.
6. The next step is to determine the correct amount of amortization in the right time period. This is done with virtually the same formula used for the capital expenditure schedules:
E5: =IF(E$3=Assumptions! $L17,Assumptions! $K17,0)
Copy and paste this formula over the range E5:J8. If you are having difficulty with the dollar signs, refer to the Toolbox at the end of this chapter.
FIGURE 5.11 The schedule of intangibles takes the data from the Assumptions sheet and lays it out over the projection period.
7. Total each period’s intangible amounts by entering the following formula in cell E10:
=SUM(E5:E8)
Copy and paste this formula over the range E10:J10. These steps should make the Intangibles sheet look like Figure 5.11.
8. The related amortization schedules present the same concept status issues as depreciation. We need to know when the intangible was created or purchased and when the total expected amortization has been taken. This is done using the following formula in cell E13:
=IF(SUM($D5:D5)=0,0,IF(Assumptions!$K17-(SUM($D13:D13))=0,0,SLN (Assumptions!$K17,0,Assumptions!$M17)))
Notice that this formula does not use the named cells, but instead uses relative referencing so the same formula can be used for multiple cells. Also note that the example model has no salvage value for the intangibles. Copy and paste this formula over the range E13:J16.
9. Total up the amortization for each period by entering the following formula in cell E18:
=SUM(E13:E16)
Copy and paste this formula over the range E18:J18. The final part of the Intangibles sheet is depicted in Figure 5.12.
INCOME STATEMENT AND BALANCE SHEET EFFECTS
With schedules created for capital expenditures, depreciation, intangibles, and amortization, we should now integrate these figures into our modeling process. The income statement is the first section in which we encounter any of these items. Depreciation and amortization are non-cash items that reduce the earnings of a firm. This is due to the idea that when these assets are eventually disposed of the accrued depreciation and amortization must be already accounted for or taken at one single time. Instead, companies are allowed the benefit of spreading this charge over time, which makes sense since items usually lose value over time, not just in one instance. The reduction in earnings due to depreciation and amortization also provides a tax benefit to companies since the amounts are removed prior to paying tax.
FIGURE 5.12 The intangible amortization works similar to depreciation; however, for the most part it is calculated using a straight-line method.
On the balance sheet we take a more comprehensive view. Gross fixed assets are tracked each year, which are increased by capital expenditures and decreased by fixed asset disposals. Depreciation for each fixed asset is tracked and added together to form accumulated depreciation. The difference between gross fixed assets and accumulated depreciation is known as the net fixed assets of the firm. Similarly, gross intangibles are increased by the intangibles each period and reduced by their disposal. Accumulated amortization is tracked and increased by the amortization each period. The difference between gross intangibles and accumulated amortization produces the net intangible figure. Both net numbers contribute to the total assets of the firm.
MODEL BUILDER 5.4: INTEGRATING CAPITAL EXPENDITURES, DEPRECIATION, INTANGIBLES, AND AMORTIZATION
1. Go back to the Income Statement sheet. Enter the text Depreciation in cell B15 and Amortization in cell B16.
2. In cell D15, enter the value 5, and in cell D16, enter the value 2. These will be our historical assumptions for depreciation.
3. In cell E15 on the Income Statement, enter the following formula:
=Capex!E18
Copy and paste this formula over the range E15:J15.
4. In cell E16, enter the following formula:
=Intangibles!E18
FIGURE 5.13 Depreciation and amortization are non-cash items that reduce net income on the income statement.
Copy and paste this formula over the range E16:J16. The updated income statement is shown in Figure 5.13.
5. Go to the Balance Sheet sheet and enter the following text in the corresponding cells to create labels:
B17: Gross Fixed Assets
B18: Accumulated Depreciation
B19: Net Fixed Assets
B21: Gross Intangibles
B22: Accumulated Amortization
B23: Net Intangibles
6. Enter the following values to insert historical assumptions:
D17: 85
D18: 17
D21: 25
D22: 5
7. Let’s complete the capital expenditure and depreciation formulas first. These are cumulative figures that use the prior period’s value plus the current depreciation or amortization. Insert the following formulas in their corresponding cells:
E17: =D17+Capex!E10
E18: =D18+Capex!E18
FIGURE 5.14 Gross fixed assets are increased by periodic capital expenditures, while accumulated depreciation is increased by periodic depreciation. The difference between gross fixed assets and accumulated depreciation is net fixed assets.
Copy and paste these formulas over to the J column for each of their respective rows.
8. Net fixed assets are the difference between gross fixed assets and accumulated depreciation. Enter the following formula in cell D19:
=D17-D18
Copy and paste this formula over the range D19:J19. Refer to Figure 5.14 for details on how the balance sheet should be developing.
9. Next we will work on the intangibles and amortization. Enter the following formulas in the corresponding cells:
E21: =D21+Intangibles!E10
E22: =D22+Intangibles!E18
Copy and paste these formulas over to the J column for each of their respective rows.
10. Finally we need to subtract the amortization from the intangibles to get the net figure. Enter the following formula in cell D23:
=D21-D22
Copy and paste this formula over the range D23:J23. Similar to Figure 5.14, Figure 5.15 shows the interaction for intangibles.
TOOLBOX
Understanding Dollar Signs
Seeing dollar signs in formulas can be confusing to new financial modelers if they are unfamiliar with Excel conventions. The technical utility of a dollar sign is to change a reference from a relative reference to an absolute reference. In normal-speak, a dollar sign locks a cell reference so the reference does not change when the formula the reference is created in is dragged. As an example, imagine the following formula in cell C5 of any sheet:
=A1+B1
FIGURE 5.15 Gross intangibles are reduced by accumulated amortization to calculate net intangibles.
If C5 is dragged or copied to the right one cell, then the formula’s references will change to B1+C1. However, if we put dollar signs in front of the rows and columns, such as in the case of $A$1+$B$1, we can lock the reference. Now when cell C5 is dragged or copied to the right one cell the formula’s references remain $A$1+$B$1. Figure 5.16 shows the difference between these two methods.
In the previous example we did not actually have to put dollar signs in front of both the row and column references. Since we were dragging the cell to the right, we were moving only across columns. We could have locked the reference by entering $A1+$B1. However, if we dragged cell C5 down one cell, the row reference would change and the formula in cell C6 would be $A2+$B2. We could do the opposite and put dollar signs only in front of the row references, such as A$1+B$1. Now when cell C5 is dragged down the references will not change, but when cell C5 is dragged, say, one cell to the right, the formula will change to B$1+C$1. Figure 5.17 shows this dragging example.
FIGURE 5.16 Using dollar signs changes a reference from relative to absolute.
FIGURE 5.17 Dollar signing in front of the rows will prevent the row numbers from changing when the reference is dragged down, but will have no effect on the columns when dragged across.
Dollar signs are used in this chapter because we want to use one formula for multiple capital expenditure and intangible items on the Assumptions sheet. Each capital expenditure or intangible is organized with different types of information going across columns, with each capital expenditure or intangible having its own row. Therefore, if we want to reference different characteristics of the capital expenditure or intangible, such as amount or date, we would want to have a dollar sign in front of the column, but not row. Conversely, we want to reference the dates on either the Capex or Intangibles sheet in the schedule formulas, but as we drag those formulas down rows we do not want the row reference to change. Therefore, we put dollar signs in front of the rows for this reference, but not in front of the columns since we want to reference a new date each period. Figure 5.18 shows the organization of the assumptions that need to be worked around with dollar signs.
Dollar Signs and Arrays
Dollar signs can also be used with functions that accept arrays. First, what is an array? An array is more than one cell of data, which can be a single column list or a matrix of data. For the most part, Excel functions that work with arrays work only with single-column or -row arrays. Many of us are already familiar with functions that work with arrays, such as the SUM or AVERAGE function. All it means is that the function can accept and return a value with multiple continuous cells of data as inputs.
The typical reference for an array is to use the starting cell reference, a colon symbol, and then the ending cell reference. For instance, range C5:G5 would reference cells C5, D5, E5, F5, and G5. Or it could be interpreted as saying “for each cell in the range of cells C5 to G5.”
FIGURE 5.18 Dollar signs are required to reference data correctly between the Assumptions and Capex sheets.
As with any cell reference, array references are relative and change as they are dragged. So imagine the following formula in cell G10:
=SUM(C5:G5)
If cell G10 was dragged one column across to cell H10, the formula in cell H10 would read:
=SUM(D5:H5)
Now, many people have guessed that you could write the following to lock down the reference:
=SUM($C$5:$G$5)
This previous formula would be locked down, but what is interesting is that we have the option of locking down only one of the two cell references in the range. For example, in cell G10 we could enter:
=SUM($C$5:G5)
When the previous formula is dragged one column to the right to cell H10, the reference will change to:
=SUM($C$5:H5)
FIGURE 5.19 The formula in cell G10 returns a sum of 43. When cell G10 is dragged to cell H10, the initial location of the reference is locked and picks up the additional 7, to return a total of 50.
Notice that the first part of the reference is locked, while the second part is not and changes. This will increase the array reference and sum up anything in cell H5, but not move the original starting point. In finance, this method is frequently used to calculate cumulative figures. Figure 5.19 shows this example with numbers to assist in the explanation.
Depreciation Functions: SLN, DB, DDB, SYD
SLN (Straight Line Depreciation) There are numerous prebuilt depreciation functions in Excel. In this section, we will cover four of the most commonly used ones. The first is the SLN function, which calculates straight-line depreciation. Earlier we provided the mathematical formula for straight-line depreciation. The SLN function’s entry parameters are as follows:
=SLN(Cost, Salvage Value, Useful Life)
Always keep in mind the problem absolute referencing causes with this function. The function on its own does not know when to turn off depreciation as it is dragged across time periods. This problem is mitigated by using the formula created in step 4 of Model Builder 5.2.
DB (Fixed Declining Balance Depreciation) Given that some assets do not depreciate in equal amounts each period, accelerated depreciation calculations try to create an organized method for determining unbalanced depreciation. A common method is the fixed declining balance depreciation method. The entry parameters for this function include:
=DB(Cost, Salvage Value, Useful Life, Current Depreciation Period, Month)
Most of the entry parameters are the same as the SLN function except for the last two. The current depreciation period is the period of depreciation for the asset, not the current period of the model. The optional month parameter is if the asset begins and ends depreciation on a partial-year basis. You can select the month numerically if this is the case.
There is a minor problem with the DB function that can become a major problem in financial modeling. The DB function rounds results to the third decimal place. This can cause too much depreciation to be taken. If too much depreciation is taken, then the cost minus the salvage value, less the depreciation, will be a negative number. This can cause errors to propagate throughout a model. The suggested fix for this issue is to recreate the DB function mathematically and not use rounding. This can be done by entering the following formula rather than the DB function:
=(Cost-SUM(Prior Depreciation Amounts))*(1-((Salvage Value/Cost)^
(1/Useful Life)))
DDB (Double Declining Balance Depreciation) In some cases the fixed declining balance method of accelerated depreciation does not accurately capture the expected depreciation of the asset. To account for this Excel has a host of other depreciation functions. A common alteration to the formula is to accelerate the rate of depreciation by a factor of two, which is known as the double declining balance method. The DDB function calculates this amount using the following entry parameters:
=DDB(Cost, Salvage Value, Useful Life, Current Depreciation Period, Factor)
The only new element in this function is the Factor, which is two in the case of double declining or three for triple declining, and so on. I have rarely used this formula and have never had to alter the factor to anything greater than 2. For those who follow U.S. GAAP accounting, you should be mindful that GAAP does not allow accelerated depreciation to dip below the equivalent straight-line depreciation. One could account for this using a MAX function or implement the VDB function, which takes care of this problem.
SYD (Sum of the Years’ Digits Depreciation) The final depreciation method we will discuss is sum of the years’ digits. This is an accelerated form of depreciation that is faster than straight line, slower than a fixed declining method in the early periods, but faster in later periods. Conceptually it can be thought of as a more smoothed version between straight-line depreciation and fixed declining balance methods. Sum of the years’ digits is officially calculated using the following formula:
.
(2 * (Useful Life - Current Depreciation Period + 1) * (Cost-Salvage Value))/
(Useful Life * (Useful Life + 1))
Alternatively, we could use the SYD function in Excel, which is much easier. The entry parameters for this function include:
=SYD(Cost, Salvage Value, Useful Life, Current Depreciation Period)
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.