Depreciation is a key component of a financial model to calculate the tax payable. Of course it is often critical to calculate the Profit and Loss but in project finance many would argue that this has a minor importance when compared to the Cashflow Waterfall which drives all credit analysis.
Clear presentation of the depreciation inputs
Having a standard structure for the assumptions regarding depreciation can save a lot of time. The assumptions can vary slightly depending on the applications but generally you will need a combination of the following assumptions:
- Asset Category
- Method of depreciation for Tax
- Method of depreciation for Accounting
- Asset life
- Multiplier (for accelerated Reducing Balance method)
One suggested structure for the layout of the depreciation assumptions for a typical vanilla project finance model is presented below:
Use Data Validation to facilitate easy updating
Some of the cells above can be coded using Data Validation which greatly simplifies the editing of assumptions from an end user’s perspective.
A user can simply point and click to choose between Straight Line Depreciation of Reducing Balance.
To set up the Data Validation, click Data – > Data Validation and select Allow: List and set the Source to “SL, RB”.
Linking the Depreciation assumptions to the financial model
This is a bigger topic that will be covered in a separate article but the screenshot below will give you an indication of the flow of information in the Depreciation calculations worksheet of the model:
Both methods (Straight Line AND Reducing Balance) are calculated for all asset categories but only the selected depreciation method flows through to the outputs. In the illustrated case above only the Straight Line values for the Depreciation Charge flows through to the Profit and Loss.
This structure would be repeated for all categories and for Tax and Accounting.