Input structure for depreciation rates and asset lives

There is a setting in the Options -> Edit -> Setting section called ‘Edit directly in cell.

The ‘Edit directly in cell’ related to the way Excel shows formulas in the cell and in the toolbar when you are in Edit mode (which is what you get when you click F2).

Edit directly in cell –activated
When Edit directly in cell is activated the formula in the toolbar does not get colour-coded but the formula in the cell does.

Edit directly in cell – activated
If you choose to uncheck the Edit directly in cell option then you will get the formula in the toolbar colour-coded but the formula in the cell is not.

Which setting is better?
I find it more efficient to work with the ‘Edit directly in cell’ option unchecked. There are two main advantages
1. If you are editing a formula that is longer than the cell (which is almost always the case) then you can see the whole colour-coded formula in the standard Excel toolbar.
2. It is easier for the eye to focus on the same area of the spreadsheet (the toolbar) every time you are editing a formula. When you are copy-pasting sections of a formula or working with the Insert Function dialogs this makes life a lot easier.

This is a functionality that only takes seconds to implement so try both options and find out for yourself which option you prefer.
On of the most common mistakes in project finance modelling is incorrect coding of delay scenarios. The main reason for this is that it is not implemented as part of the core functionality of the model when it is first constructed, cheap but rather squeezed in by a bank analyst as part of the credit analysis.

Since the person coding the delay functionality is often different from the person who originally constructed the financial model it can be challenging to understand all the implications of changing the model logic in something as complicated as a delay scenario.

Example of delay scenarios

  • Delay of start date of construction
  • Delay of completion (i.e. construction takes longer than planned)
  • Delay of divestment date
  • Delay of acquisition date
  • Delay of capital raising
  • Delay of debt refinancing

How can we quickly estimate the accuracy of a delay scenario?

One quick method of getting an estimate of the accuracy of a delay scenario is to prepare a data table (manual or dynamic using the Excel Data Table functionality) with cashflows from a number of scenarios. If these cashflows are plotted then it is often possible to visually identify big picture problems.

This method can save you hours of work if you are on performing your own credit analysis but are not responsible for the modelling on the transaction. You can simply prepare the outputs as illustrated below and send this back to the modelling bank or the sponsor for clarification.

Preparing a table of cashflows for chosen scenarios

In many cases it makes sense to choose the scenarios that you will be including in the credit or investment analysis. In the example below I have illustrated the concept with CFADS (Cashflow Available for Debt Service) but in your specific case it may be more useful with another metric or a combination of several metrics.

Suggested cashflow metrics

  • EBITDA
  • Distributions
  • Revenue
  • Operational Expenses
  • Capex
  • Debt Repayments
  • Cash account movements
Image 1: Cashflow Available for Debt Service for four scenarios

Image 1: Cashflow Available for Debt Service for four scenarios

Image 2: Graph of cashflows from the investigated scenarios

Image 2: Graph of cashflows from the investigated scenarios

Identify problems in financial modelling of scenarios

Generally speaking cashflow movements in scenarios or sensitivities should be smooth and without spikes. In the example in Image 2 there is a big jump in CFADS in periods Ops 1 and Ops 2 which would be a reason to further investigate the correctness of this scenario.

Of course there could still be problems in all the other scenarios too and this method is a top down approach to finding BIG mistakes first (while you can still get someone else to fix them for you!).
On of the most common mistakes in project finance modelling is incorrect coding of delay scenarios. The main reason for this is that it is not implemented as part of the core functionality of the model when it is first constructed, ampoule
but rather squeezed in by a bank analyst as part of the credit analysis.

Since the person coding the delay functionality is often different from the person who originally constructed the financial model it can be challenging to understand all the implications of changing the model logic in something as complicated as a delay scenario.

Example of delay scenarios
• Delay of start date of construction
• Delay of completion (i.e. construction takes longer than planned)
• Delay of divestment date
• Delay of acquisition date
• Delay of capital raising
• Delay of debt refinancing

How can we quickly estimate the accuracy of a delay scenario?

One quick method of getting an estimate of the accuracy of a delay scenario is to prepare a data table (manual or dynamic using the Excel Data Table functionality) with cashflows from a number of scenarios. If these cashflows are plotted then it is often possible to visually identify big picture problems.

This method can save you hours of work if you are on performing your own credit analysis but are not responsible for the modelling on the transaction. You can simply prepare the outputs as illustrated below and send this back to the modelling bank or the sponsor for clarification.

Preparing a table of cashflows for chosen scenarios
In many cases it makes sense to choose the scenarios that you will be including in the credit or investment analysis. In the example below I have illustrated the concept with CFADS (Cashflow Available for Debt Service) but in your specific case it may be more useful with another metric or a combination of several metrics.

Suggested cashflow metrics
• EBITDA
• Distributions
• Revenue
• Operational Expenses
• Capex
• Debt Repayments
• Cash account movements

Image 1: Cashflow Available for Debt Service for four scenarios

Image 2: Graph of cashflows from the investigated scenarios

Identify problems in financial modelling of scenarios
Generally speaking cashflow movements in scenarios or sensitivities should be smooth and without spikes. In the example in Image 2 there is a big jump in CFADS in periods Ops 1 and Ops 2 which would be a reason to further investigate the correctness of this scenario.

Of course there could still be problems in all the other scenarios too and this method is a top down approach to finding BIG mistakes first (while you can still get someone else to fix them for you!).
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:

depreciation-1

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.

depreciation-2

A user can simply point and click to choose between Straight Line Depreciation of Reducing Balance.

To set up the Data Validation, visit this site
click Data – > Data Validation and select Allow: List and set the Source to “SL, physician
RB”.

depreciation-3

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:

depreciation-4

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.

Recent posts by Rickard Wärnelid

Tags: ,

Comments for “Input structure for depreciation rates and asset lives”

  1. Chris Mishler says:

    Wonderful stuff. I was just talking to a tax colleague this morning who was complaining about the treatment of depreciation lives in some of the software he uses to compute various tax provisions. I will encourage him to look up this blog.

  2. Chris,

    I am happy you like it!