Depreciation updating of values has errors

05-Oct-2019 12:05

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: One suggested structure for the layout of the depreciation assumptions for a typical vanilla project finance model is presented below: Some of the cells above can be coded using Data Validation which greatly simplifies the editing of assumptions from an end user’s perspective.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.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.Building a financial model that is cost efficient to audit is a lesson that many Analysts learn the hard way when they receive a quote exceeding their annual salary.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.

When you are copy-pasting sections of a formula or working with the Insert Function dialogs this makes life a lot easier.

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.

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.

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.

When you are copy-pasting sections of a formula or working with the Insert Function dialogs this makes life a lot easier.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.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.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.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.