# Templates for calculating depreciation

Depreciation is an expense allowance that accountants make for wear and tear on an asset over its useful life. In effect, depreciation allocates the cost of an asset over time. Depreciation calculations are often used in applications that include capital items or where accounting for the book value of assets is important.

When you create a model, PerformancePoint Planning Business Modeler automatically includes templates for business rules that calculate depreciation values for an asset. By substituting appropriate dimension member values for placeholders in one of these templates, you can quickly create business rules that perform these calculations.

## Find out what templates are available

The rules that you can create by using one of these templates calculate depreciation for an asset in a specified account. You can decide between several different standard methods for calculating depreciation.

 Template Description Straight-line depreciation template Calculates the depreciation of an asset uniformly across the life of the asset Declining balance depreciation template Calculates the depreciation of an asset by applying the rate of depreciation to the declining salvage Double declining balance depreciation template Calculates the depreciation of an asset in the same way as the declining balance, except by doubling the depreciation rate Sum-of-years-digits depreciation template Calculates the depreciation of an asset by multiplying the depreciable cost by a schedule of fractions

## Learn what substitutions are necessary in each template

To convert one of the depreciation templates into a business rule, you must substitute actual dimension values for the placeholders in the templates. For information about how to substitute values, see Filling a placeholder in a rule template in Planning Business Modeler.

The following table describes the placeholder substitutions that you must make to create a business rule from this template.

 Placeholder Description [Time].[Hierarchy].[Source Member] The time period in which the initial asset cost and salvage value are stored [Time].[Hierarchy].[Starting Period] The start of the depreciation period of the asset life [Time].[Hierarchy].[Ending Period] The end of the depreciation period of the asset life [Account].[Hierarchy].[Depreciation Account] The account where the depreciation results are stored [Account].[Hierarchy].[Cost Account] The account that stores the initial asset cost calculation [Account].[Hierarchy].[Salvage Account] The account that stores the salvage value [Scenario].[Hierarchy].[ TARGET_SCENARIO] Scenarios where depreciation calculations are performed. Select ACTUAL for testing. [Current Model] The name of the model in which the rule exists

## Learn about the depreciation methods

The depreciation templates in Planning Business Modeler represent four different methods of calculating depreciation: Straight-line, declining balance, double-declining balance, and sum-of-years’-digits. To learn about one or more of these methods, expand the following descriptions:

Straight-line method

The straight-line method is the most frequently used method. In this method, the company estimates the salvage value, or the value of the asset at the end of its useful life, and the length of the useful life of the asset. Then, depreciation for each year is calculated by this formula:

Depreciation = (Cost of asset – Salvage value)/(Useful life in years)

For example, suppose a computer that depreciates over 5 years is purchased for US \$2000, and will have a salvage value of US \$100. Depreciation for this computer is US \$ 380 per year: (\$2000-\$100)/5 = \$380.

Book value at the end of year becomes book value at the beginning of next year. The asset is depreciated until the book value equals scrap value.

The following table shows how this asset depreciates over time.

 Book Value - Beginning of Year Depreciation Expense Accumulated Depreciation Book Value - End of Year \$2000 (Original Cost) \$380 \$380 \$1620 \$1620 \$380 \$760 \$1240 \$1240 \$380 \$1140 \$860 \$860 \$380 \$1520 \$480 \$480 \$380 \$1900 \$100 (Salvage Value)

Declining Balance method and Double-declining Balance method

The declining balance method provides for a higher depreciation during the first year of the useful life of an asset, and then gradually decreases the amount of depreciation each year of the remaining useful life. To calculate depreciation by using this method, the book value of the asset is multiplied by a fixed rate of depreciation, as shown in this formula:

Annual Depreciation = Depreciation Rate * Book Value at Beginning of Year

To determine the rate of depreciation, accountants often use the rate of straight-line depreciation. In this way, an asset with a useful life of five years has a 20% depreciation rate.

The double declining balance simply doubles the rate of depreciation, so that an asset with a useful life of 5 years has a 2*20% = 40% rate of depreciation.

Book Value at the beginning of the first year of depreciation is the original cost of the asset. At any time, Book Value equals Book Value = Original Cost - Accumulated Depreciation

Book Value at the end of year becomes Book Value at the beginning of next year. The asset is depreciated until the Book Value equals Salvage Value.

The following table shows how an asset with an original cost of \$1000 depreciates over five years.

 Book Value - Beginning of Year Depreciation Rate Depreciation Expense Accumulated Depreciation \$1,000 (Original Cost) 40% \$400 \$400 \$600 40% \$240 \$640 \$360 40% \$144 \$784 \$216 40% \$86.40 \$870.40 \$129.60 \$129.60 - \$100 \$29.60 \$900

Sum-of-years’-digits method

Sum-of-Years' Digits is a depreciation method that results in a more accelerated write-off than straight line, but less than declining-balance method. Under this method, annual depreciation is determined by multiplying the Depreciable Cost by a schedule of fractions.

Depreciable Cost = Original Cost - Salvage Value

Book Value = Original Cost - Accumulated Depreciation

For example, if an asset has original cost \$1000, a useful life of 3 years and a salvage value of \$100, you can compute its depreciation schedule as follows.

1. First, determine Years' digits. Since the asset has useful life of 3 years, the Years' digits are 3, 2, and 1.

2. Next, calculate the sum of the digits: 3+2+1=6

3. Set the Depreciation Rate per year: 3/6 for the 1st year, 2/6 for the 2nd year, and 1/6 for the 3rd year.

4. Determine Depreciable Cost: \$1000 - \$100 = \$900.

5. Determine Depreciation Expense = Depreciable Cost* Depreciation Rate

The following table shows how the asset in this example depreciates:

 Book Value - Beginning of Year Total Depreciable Cost Depreciation Rate Depreciation Expense Accumulated Depreciation Book Value - End of Year \$1000(original cost) \$1000 - \$100 = \$900 3/6 (\$900*3/6)=\$450 \$450 \$550 \$550 \$900 2/6 \$300 \$750 \$250 \$250 \$900 1/6 \$150 \$900 \$100 (Salvage)

## Create a rule by using a depreciation template

To create a rule by using one of the templates listed in this article, you must first create a rule set and rule, and then fill the template placeholders with appropriate values. To see the steps in the following procedures, expand each procedure:

Before you create a rule by using a depreciation template, complete these tasks:

1. Verify that Microsoft Office Excel is installed on the computer that will run the rule. The rule uses a function from Excel.

2. Create an account (a dimension member in the Account dimension) that the rule can use to hold computed values. For example, you might create an account named DepreciationAccount. For more information, see Add, remove, or delete a dimension member.

2. Create a new rule set

When you create a rule by using a depreciation template, the rule is a procedural rule with Native MDX Script implementation. Therefore, you must create a Procedural rule set to contain any rules that you create by using these templates.

To learn how to create a rule set, see Create or remove a rule set.

Note: NativeMDXScript implementations run automatically at query time. To learn more, see Native code implementation. Native MDX Scripts must be saved as inactive in the model, and then enabled by a Performance Point Planning Global Administrator. For more information, see Security requirements for Native SQL and MDX rules.

3. Create a new rule

Take these steps:

1. In the table of business rules, right-click the name of the rule set that you just created. Then, on the shortcut menu, select Create a New Rule.

2. In the New Rule dialog box, type a name, label, and description for the rule that you want to create by using one of the depreciation templates. For information about limitations and character restrictions, see About names and labels.

3. Check the Use template or copy from an existing rule box.

4. Select Template, and then click the ellipsis button [...] next to the Template list to open the Select Rule Template dialog box.

5. From the Rule Template table, select the name of the rule template that you want to use. Your selection must be one of the following values: Declining Balance Depreciation, Double Declining Balance Depreciation, Straight Line Depreciation, or Sum of Year digit Depreciation.

7. Click OK to open the Rule expression pane that contains the text of the rule template.

4. Fill placeholders in the rule template

Take these steps:

1. In the Rule expression pane, right-click to show the shortcut menu, and select Fill Template Placeholders.

2. In the Fill Template Placeholders dialog box, for each placeholder in the Placeholders grid, select or type a value. For more information about the required values, see Learn what substitutions are necessary in each template.

3. When you finish selecting values for all placeholders, click OK.

4. Save the new rule as inactive, and then contact a PerformancePoint Server Global Administrator to enable the rule.

Important: Because rules that you create by using these templates have Native MDX implementation, they act directly on the application database. Therefore, make sure that you limit the scope of dimensions. This helps make sure that the amount of information that the query returns is not too large.

Share