Operationalising project models

Why do it?

Once a project has been through its financing and construction phases, the project may want to operationalise the project model. This can have the following benefits for the project:

• The model can be simplified, making it more tractable to use, since much of the financing-related functionality is no longer necessary;
• The construction period can be removed, further simplifying the model;
• We can incorporate actual experience, making the stepping-off point for projections much more valid than under the original base case model;
• We can integrate the model into the project’s existing administration systems; and
• We can incorporate new line items which were not considered relevant at the financing stage – in particular, new opex items are common.

Operationalising a model may be more important for a project if the project’s reality has evolved in a fashion which is significantly different to that portrayed by the original model. Such an eventuality may necessitate frequent reconciliations between model and accounts, explanations of budgets and justification of covenant ratios.

Starting point

The inputs required to operationalise a model include the following, assuming the modeler has some familiarity with the project:

• The original model;
• At least two sets of financial statements and/or management accounts (or general ledger and mappings from the ledger to the fields in the model);
• An indication of what new functionality is desired, if any;
• An indication of how frequently the project would like to incorporate actuals into the model, in order to determine the period of the new model.

I recommend that the modeler start afresh or use a template for the new model, rather than trying to modify or simplify the existing model. This is because simplifying an existing model comes with any baggage associated with that model, and it can therefore be cumbersome. A simplified model may end up with inputs and calculations with no dependents, and may incorporate logic errors which were overlooked in the original model build.

Process

I recommend splitting the process into three main sections:

Stage 1: Replication and simplification

Replicate the existing model in a simplified manner. There will be much functionality which is no longer required, such as being able to select between different facility types (eg mortgage-style vs equal principal repayments). In this example the project has now fully determined which is applicable, so abandon the other. Another example may be excess facilities penciled in in case they were required, or construction period functionality.

Having the original model available is very helpful in understanding what the mechanics are for the transaction, and also to check numbers against. In particular, numbers which are point-in-time (such as revenue received in a period) as opposed to cumulative (such as cash balance) are ideal to check against the original model at this stage. I tend to check these as I work through the model build, and colour code my checked values to easily see which are exactly the same as the original model, which are similar, and which differ. Doing this will also help model users (lenders, for example) reconcile the new model back to the original model. This is helpful because it’s very likely that they made themselves comfortable with the original model, which was probably audited. It will also assist with model audit, if such is required.

Stage 2: Incorporating actuals

I like to incorporate a full new sheet which only contains actuals (i.e. figures that have actually occurred, and can therefore be found under existing sources of macroeconomic data (eg inflation) or in the project’s financial statements or general ledger). The benefits of this are:

• That the project / model users do not have to search through all your calculations to determine where to fill in their actuals;
• This in turn means that there is a reduced possibility of modifying the model’s calculations and thereby breaking it;
• Actuals are all in one place, and can be located with appropriate mappings so that new line items can be more easily incorporated into the model in the future. It should therefore also be evident if one or more actuals are omitted.

Actuals on a single sheet, incorporating mapping information

Depending on what the client wants, the actuals to include may be a full dump of the management accounts, general ledger or financial statements, or a subset of that information. Since the project will be updating this sheet periodically, it needs to be clear what each actuals line item incorporates, and how that information is to be sourced in subsequent periods.

If a subset of the management accounts is acceptable, it’s usually sufficient to use a combination of balance sheet and income statement items. Cash flow statement items can typically be implied from these.

As implied above, the actuals sheet may also include a series of mappings. This is because the model is likely to have less detail, aggregating several rows from the AFS or general ledger into fewer lines. The mappings, in conjunction with a SUMIF function, can effect this.

From the Actuals sheet, I typically import the aggregated/mapped rows straight into the top of my Working (i.e. main calculations) sheet. These then feed down into the model’s projection calculations using a formula similar to the following: =IF(actual <> “”, actual, projection). You may also need to infer certain revenue items or expenses from the balance sheet items which you import. This is necessary to minimize the size of any balancing items. I also tend to place conditional formatting in my worksheet to indicate where the figure observed has been pulled through from actuals, or is a projected figure.

Conditionally-formatted actuals pulling into the Working sheet

Finally, one or two balancing figures will probably be required. If you import actual cash balance at the end of the period, it is unlikely that the cash flow waterfall will balance, since some items will be projections/ calculated values and some will be actuals. A balancing cash flow is therefore required to reconcile the starting balance and sum of all cash flows in the period with the ending balance. This balancing cash flow should automatically be zero in periods which have no actuals being pulled through. Obviously this should be as small as possible. Another balancing figure may be required to reconcile starting balance sheet figures plus income statement items with the actual end-of-period balance sheet position. Again, this should be as small as possible. However, note that this balancing figure will persist (without changing, otherwise something is wrong with your model) into projected periods.

Stage 3: Importing actuals

Finally, you should import at least two periods’ worth of actuals into your model. This will ‘settle’ the figures, since the first period sets the base line, and the second period creates the first accurate set of the implied / calculated values which result from the previous accumulated position and the present period’s actuals.

As a corollary, many of the calculated figures in the first period may be incorrect / spurious. This is because the model is moving from one basis (projections) to another (actuals). Any accumulated values (such as cash balance) may differ significantly between the two bases.

A second advantage of incorporating multiple sets of actuals into the model is that it will provide a better base for model users to compare against when they first need to incorporate actuals without the benefit of having the modeler present to assist.

Amount of work required

Operationalising a model is significantly less work than building a project model in the first instance. This is not so much because there is less functionality (although that may be partially true if the new model is much simpler than the old model), but because there are fewer moving parts to the project itself, and because the old model provides as good a set of assumptions and inputs as a modeler is likely to receive.