Good Modelling Practices
Start by understanding the examination question
NPV models and NPV risk models can serve different purposes at different points in the project. These might include:
- What are the key sources of risk that the project needs to manage?
- How can the project's benefits be optimised?
- Should the project be authorised to proceed beyond the concept phase?
- Which option or combination of options should be adopted for the project?
- Should the project be authorised to proceed to full implementation?
To make good judgements about what is relevant to the model and the level of detail that is appropriate, the modeller needs to understand the decisions to be supported.
Expect to develop a model iteratively starting with a simple early model
Models tend to require more detail as we go down the above list of modelling purposes. Increasing detail is thus a natural process as different decisions are taken during the course of a project. In addition, starting with a simple model helps to:
- develop a coherent structure and
- identify a record assumptions, starting with those that are the most fundamentally important.
Develop a Project Impact Breakdown Structure
Project managers are familiar with developing work breakdown structures (WBS) and cost breakdown structures (CBS). These structures are used to ensure that everything required by the project scope is included, but that nothing is duplicated.
The project impact breakdown structure (IBS) extends this principle to a project's impact on all relevant costs and benefits, noting that these may include:
- costs not managed by the project manager e.g. tax liabilities or through life operational costs.
- all benefits including those realised after project handover.
- disbenefits e.g. lost opportunity costs or negative environmental impact.
An IBS helps to both structure a model and help differentiate between relevant and irrelevant costs and benefits.
A general plan for development of NPV models
The following general model development plan combines the above principles with the steps to building a financial model described by Danielle Stein Fairhurst in her book Using Excel for Business Analysis (2012):
- identify the decision(s) to be supported by the model;
- identify and structure the relevant costs and benefits;
- identify the users of the model;
- assign modelling tasks;
- build inputs and record associated assumptions;
- build calculation and workings and record associated assumptions;
- build outputs: summaries, charts and reports;
- peer review of the draft model;
- update the model in response to peer review;
- formal model quality assurance (QA) check;
- take relevant decision(s) and review other insights identified by modelling;
- identify decisions (if any) to be supported by the next iteration of modelling;
- repass the process from point 2 or archive the model as appropriate.
Good spreadsheet design practice
As a model is being built, there are a number of practices that can help to improve its transparency and avoid errors. Again Using Excel for Business Analysis (2012) provides sound advice on this subject. The list below summarises some of its recommendations:
- document the model's design, assumptions, ownership and version control;
- colour code inputs, calculations and results, and separate where possible;
- use each column for the same purpose;
- only enter data once e.g. avoid duplicating numbers by copying formulae;
- make the model easy to comprehend from left to right and top to bottom;
- include errors checks, ideally with conditional formatting to highlight errors.