Tracking of actual data

How to transform an existing “Financial Close” model into a user friendly “Updatable for Actuals” model? This Tutorial and the accompanied workbook set out an approach for conversion of a typical project finance model and how to code-in the mechanics in a transparent way to facilitate reporting and analysis tool.

As the project moves ahead, the actual results will vary from the initial forecasts as of financial close of the project. For example there will certainly be some interest rate movements post financial close and certain variations in the construction schedule or operating expenditures.

Such variations might or might not be materially affecting the key results/outputs of the financial model. However, the project contract or financial arrangement might require that historic performance is updated within the model on a regular basis as the project moves from financial close. Such updates might also be required to facilitate internal budgeting and management reporting.

Basic approach to capturing actual data

The basic approaches to tracking financial and operational actual data are set out in bullet points below.

• The actual data will override the historical figures in the model during the actual period (or historical period).
• Refer to Screenshot 1. End of actual period can be selected from the drop down cell in the Inputs sheet (cell E14).
• Refer to Screenshot 2. IFS_Actuals tab is a dedicated input sheet for the actual data.
• A flag to indicate the actual period is built as shown in Screenshot 1 (row 7), which means the forecast period will be picked up from the end of the actual period.
• The inputs for actual data capture most of the items in the Cashflow Statement. The Income Statement and Balance sheet items are calculated automatically when the actual data for the Cashflow are entered.

Screenshot 1: Input cell for end of actual period

Screenshot 2: Input sheet for the actual data

The screenshot above shows an example of the actual period up to 31-Mar-09. The inputs that can be updated for the actual are styled in a pink colour (style name: Actuals_Input) and are conditionally formatted to guide the users where to enter the actual data. Basic coding to update the actual data is:

= IF (Actuals flag =1, Actuals Input, Calculated)

General cashflow items

This is the simplest method and is applicable for general cashflow items that do not have balances, such as Revenue, OpEx, and Interest revenue. The cashflow statement (in IFS sheet) is adjusted using the basic coding as demonstrated in Screenshot 4.

Screenshot 3: Inputs for general cashflow items

Screenshot 4: Actuals coding for general cashflow items

Construction cost items

This is applicable to construction capital expenditures and other costs during construction such as capitalised financing costs. The actual data is linked as such to ensure that the net book value of the fixed assets and debt balance (in the Balance Sheet) going forward are correctly updated.

Screenshot 5: Inputs for construction cost items

Screenshot 6: Actuals coding for construction capex

Tracking of actual debt payments

The actuals for the Principal are linked to the debt account in the Finance sheet, i.e. where the debt balance is calculated.

This is to ensure that the debt balance is correctly updated and the forecast calculations going forward remain correct (for example the forecast interest is calculated as % of the debt balance).

Screenshot 7: Inputs for debt services

Screenshot 8: Actuals coding for the debt

Reserve accounts and tax payable

• Reserve Accounts: The movements of the reserve accounts (the additions and releases) need to be updated for the actual data. 
• Tax: The Tax Payable (Income Statement) and Tax Paid (Cashflow) need to be updated for the actual data. The Tax Creditor in the Balance Sheet will be automatically updated.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...