Evaluating actual results compared to the financial model is addressed on this page. Issues include presenting actual and forecast results effectively; adjusting for actual results during the construction period; and using economic depreciation to gauge the performance of a project. A project finance model becomes obsolete the day after the plant begins operations. After you receive historic data, you should prepare new forecasts from the actual results. Updating forecasts of a project is more like corporate finance modelling and is the subject of this page. If you update capital expenditures and debt draws before the COD, the subjects are more complex and can give rise to difficult issues of circularity. After COD, the complex issues involves how to measure performance using economic depreciation rather than tax or accounting depreciation. You can download power point slides that describe modelling issues with incorporating actual data in project finance models.
Basic Example with Updating Forecasts after COD
The process of evaluating actual results involves presentation and involves efficiently putting data together. In addition there is a bit of forecasting involved. I have made a very simple to illustrate this process. In the screenshot below I use a small piece of a financial model to get started. Items in this model will be compared to actual data and the new forecast.
I suggest you put the actual data and perhaps the budget data in yet another sheet. If the titles are different you may have to do a little work putting a sheet that has a master title list. In this case I assumed that the titles are the same. The acutals are represented in the sheet below.
The next task is to compare the actuals and the forecast. The key to do this is to use the magic HISTORIC SWITCH. This allows you to compare actuals to forecast and it allows you to update a forecast in an automated manner. You can use the AVREAGEIF with the forecast switch and use the historic average to get the forecast. You can also apply all sorts of forecasts with regression, exponential smoothing etc. You can also apply conditional formatting to show what is the history and what is the forecast. To do this you should use the NEW RULE and USE FORMULA in the conditional formatting. This is illustrated in the video below. An illustration of the HISTORIC SWITCH and conditional formatting is shown in the screenshot below.
A video describing the process of presenting actuals and making new forecasts is presented below. This video is a lot about presentation of actuals compared to the financial model and compared to the forecast. You can download the file associated with the video using the button below.
Actuals in Financial Model During the Construction Period
The video and associated files demonstrate how to incorporate actual data in project finance. The two excel files below demonstrate how to construct models that contain actual balance sheet and/or income statement data. The actual data is inserted in a new sheet and a historic switch (flag) is used to make the model flexible where actual data can be automatically updated as new data arrives. Closing balance of accounts is computed with a formula that is different depending on historic switch. During the historic period, the closing balance is the actual amount. After the historic period, the closing balance is the sum of the opening balance and computed changes in the account.
One of the most important and difficult things to compute is the adjusted forecast that depends on the actual data. For the case where work in progress and the debt is computed from actual data, the forecast can be structured to meet contract amounts. The remaining amount to spend on construction is derived from the EPC and other contracts. The remaining amount of debt corresponds to the debt to capital ratio and the debt commitment. For using actual data in the construction period, tricky circular reference issues arise.
Construction with Actual and Forecast.xlsm
Actual and Forecast.xlsm
Actual and Forecast in Construction.pptx