I address incorporation of actual results and performance evaluation on this page. Issues include how should you structure your model for computing and presenting actual and forecast results effectively; adjusting for actual results during the operation, construction or development period; using economic depreciation to gauge the performance of a project and adjusting for actual debt financing in a model. When you put actual data into a project finance model, you are really making your model into a corporate model with historic switches and flexible forecasts. I emphasize in the discussion below that you can use a whole bunch of different methods to make your forecasts into actual data. Once a project is operational or under construction and after you receive actual data, you should either: (1) prepare new forecasts from the actual results; (2) incorporate the actual data (e.g. development costs) in your model; or (3) compare the new forecasts with your original model. If you update capital expenditures and debt draws before the COD, the modelling tasks 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 and Use of Actual or Historic Switch
The process of evaluating actual results involves presentation and involves efficiently putting data together. In addition there may be a bit of forecasting involved. I have made a very simple to illustrate this process where you receive actual data on the operation of the project such as revenues and expenses. 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 actual results 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 actual data and making new forecasts is presented below. This video is a lot about presentation of actual data compared to the financial model and compared to the forecast. You can download the file associated with the video using the button below.
Difference Between Time Series Variables and Scalar (Constant) Variables
Computing Actual Returns using Economic Depreciation
Measuring the performance of a project is a very difficult issue in project finance. You cannot evaluate returns in a normal manner using return on invested capital. I have suggested reconciling IRR and ROIC, but to really evaluate ROIC you can go further. You can compute economic depreciation by evaluating the change in the value of a project. Using economic depreciation the ROIC remains constant if your original projections are met. But if you don’t meet your projections you can evaluate your performance. Then you don’t have to waste a lot of time computing the new IRR over the entire lifetime of the project.
Implementing Actual Data in a 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
