IDSB Banner

Using Indirect() with Capex and Depreciation Model


Forecasting capital expenditures and depreciation is an important and sometimes tricky part of the planning process.  Depreciation as a P&L expense is a non-cash charge against earnings. Therefore it is important not to simply project depreciation as a percent of revenue, as may be the case with many other expense lines. Different asset types have varying depreciable lives. To accurately project the expense, one must break assets into groups. Also, while cash flow is impacted at the time a fixed asset is acquired, it is also very important to plan for the future impact of asset replacement on the organization's cash flow and expense. Quantrix has a function called "Indirect()" that is well suited to integrate depreciation into forward looking financial models. This Modeler note demonstrates how to use the indirect() function to model the organizations depreciation expense for assets with varying depreciable lives and to plan for the future replacement of these assets.

Calculation of Depreciation
Depreciation reflects the fact that a balance sheet asset declines in value as time marches on. Since depreciation can be recorded as an expense item on the income statement, it can have major tax implications to the organization and needs to be accounted for accurately in forward looking financial models.

In this model the calculation of depreciation is derived from the depreciable balance for a particular asset divided by the asset's depreciable life. The depreciable balance is the cumulative value of assets purchased which have not reached the end of their depreciable lives. As assets are purchased, they are added to the balance. As assets reach the end of their depreciable lives, they are removed from the balance. Building formulas to perform this calculation can be potentially numerous and complex. However, Quantrix simplifies this by utilizing a function called Indirect(). The Indirect() function returns a string which may be partially or entirely based on a variable set elsewhere in the model. In the case of the depreciable balance, Indirect() is used to deduct the value of assets purchased X years ago based on a depreciable life X set in a Capex Assumptions table. Specifically, Indirect() is used to insert the depreciable life into a recurrence formula so that, in the case of a three-year asset, the formula will subtract “Total Purchases:Month[THIS]:Year:[THIS-3]” from the balance.

Calculation of Replacements
Replacements are also calculated using the Indirect() function. In this case, the formula refers to a “useful life” set in the Capex Assumptions table. This allows one to plan for the practical timing of replacement of assets which may not coincide with the end of the accounting depreciable life. In the attached example, PC Workstations have a depreciable life of three years and a useful life of two, and you will note that the plan begins capturing replacements two years after the first PC is purchased. Replacements along with new purchases comprise the total capital expenditure for a period which is added to the depreciable balance.

Integration of Capex and Depreciation into the Financial Statements
As long as the categories for time (e.g., months, quarters, years) are linked across the financial statements and the capex and depreciation schedules, simple formulas can be used to pull total capex figures into the Cash Flow, and depreciation into the Income Statement. From that point forward, new asset types can be added and all that is needed is to enter the depreciable and useful lives and to input or calculate (based on other factors such as number of employees) the planned additions to assets.

Downloads:

>> Quantrix Capital Expenditure Model




idbs_banner_960x41
About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -