Defra - Department for Environment, Food and Rural Affairs.

Science Search

Science and Research Projects

Return to Science Search homepage   Return to Project List

Independent Quality Assurance of the Water Bills Projection Model - WT1575

This will be a small QA exercise and should not take more than a week. The model is fairly big but is transparent and has a simple structure with basic code - there is also a user handbook. The NERA project team will be available for an introductory session and for response to any queries during the QA period. Note that there are strong confidentiality agreements in place between Defra-EA-NERA and water companies which apply to this project.
Model audit
The following information gives an indication of the model size:
• 10 sheets of inputs with no formulas or a few very simple ones;
• 2 interface sheets with a few simple formulas;
• 7 input processing sheets with one unique non-trivial formula each;
• 8 calculations sheets where there are up to 500 rows for each company, with different formulas by row (repeated every year). These are mostly very simple lookups or sums, but occasionally contain non-trivial formulas;
• 8 output sheets which lookup results using mostly simple lookup formulas but occasionally contain non-trivial formulas.

Objectives will be to:

• Carry out a high level structural review by for example, using relevant software to produce “maps” of the Model and other relevant high level analysis reports on formulae and inputs.
• Identify unique formulae in the Model, a unique formula being one which is a not a copy of those in the column to the left or in the row above. For such identified unique formulae, inspect the formulaic code and document instances where it contains apparent mathematical inaccuracies or deviates from the intended logic (as implied by the model structure and construction or as explicitly stated in the cell label or, if available, cell comments or management’s documented understanding of the Model).
• Perform checks on whether assumptions and input data have in all material respects been consistently reflected in the Model.
• Test user interface and make recommendations to enhance usability, e.g. presentation, navigation through cells, worksheets, scenarios, drop down options etc.
• Assess available analytical tools and model outputs (there is a results supplement), recommending improvements to better meet user objectives and ensuring overall consistency of results at aggregate/ water company level with model inputs.
• Assess quality of user handbook in terms of clarity of guidelines, breadth of coverage and depth on specific matters of likely importance to end-user.
• The main deliverable would be a technical note outlining the steps taken to meet the objectives set out above, covering for example the QA approach/tests, log of any issues and potential solutions and recommendations made. An assessment of the overall robustness across the range of inputs and model outputs should be documented.

Project Documents
• FRP - Final Report : W1575 Quality Audit of draft Nera water bills model - Final Report   (394k)
Time-Scale and Cost
From: 2014

To: 2015

Cost: £5,820
Contractor / Funded Organisations
Vivid Economics Ltd