Filetype Excel XLSX | Posted on 07 Jul 2022 | 3 years ago
Authentication
digital resources
239x Filetype XLSX File size 0.35 MB Source: www.ofwat.gov.uk
Sheet 1: Front sheet
| Front sheet | ||||||||
| Workbook name: | BPT-FM-Mapping-tool-v8.1.xlsx | |||||||
| Version number: | 8.1 | |||||||
| Filename: | https://ofwat.sharepoint.com/sites/rms/pr-pr19/CrossPhase/Data and modelling/Live models/Financial models/Live/BPT-FM-Mapping-tool-v8.1.xlsx | |||||||
| Date: | Tuesday, March 12, 2019 | |||||||
| Author: | Robert Thorp | |||||||
| Author contact information: | Robert.Thorp@ofwat.gov.uk | |||||||
| Sponsor: | Andy Duff | |||||||
| Sponsor contact information: | Andy.Duff@ofwat.gov.uk | |||||||
| Summary of workbook: | The purpose of this spreadsheet is to aid companys in linking data from the business plan tables (BPTs) into the PR19 financial model (FM). | |||||||
| Instructions | See guidance sheet | |||||||
| Guidance | ||||||||
| Change Log | ||||||||
| Below are details of changes to the mapping tool from the previously published version (31 Jan 2019) due to identified errors and changes to the BPTs/Financial model. | ||||||||
| Issue | Details | Link | ||||||
| RoRE scenarios updated | The RoRE scenarios have been amended to link to the pre tax values from app26. | Financing - RoRE adjustment to net profits - Upside case - WR | ||||||
| Correction to links | The previous version of the mapping tool did not align to the financial model for 'Brought forward capital allowance pool ~ structures and buildings 2%' and 'New capital expenditure - Proportion of new capital expenditure qualifying for the structures and buildings (2%) pool ~ Wastewater network plus'. This has been corrected | Brought forward capital allowance pool ~ structures and buildings 2% - Brought forward capital allowance 2% ~ Wastewater network plus | ||||||
| New capital expenditure - Proportion of new capital expenditure qualifying for the structures and buildings (2%) pool ~ Wastewater network plus | ||||||||
| Correction to links | The links to the WACC tables (wr5 etc) were linked to the notional company structure instead of the actual company structure. This has bee amended. | Cost of debt (used in WACC) ~ water resources | ||||||
| Cost of equity (used in WACC) ~ water resources | ||||||||
| Gearing (used in WACC) ~ water resources | ||||||||
| New lines added | Links to new lines have been added to align with the latest version of the financial model (version 17z). | Total residential retail costs (opex plus depreciation, excluding third party services) - Water only - Residential unmeasured | ||||||
| Total residential retail costs (opex plus depreciation, excluding third party services) - Wastewater only - Residential unmeasured | ||||||||
| Total residential retail costs (opex plus depreciation, excluding third party services) - Water and wastewater - Residential unmeasured | ||||||||
| Total residential retail costs (opex plus depreciation, excluding third party services) - Water only - Residential measured | ||||||||
| Total residential retail costs (opex plus depreciation, excluding third party services) - Wastewater only - Residential measured | ||||||||
| Total residential retail costs (opex plus depreciation, excluding third party services) - Water and wastewater - Residential measured | ||||||||
| Total residential retail outcome delivery incentives (ODI) impact ~ High RoRE case (pre tax adjustment) | ||||||||
| Total residential retail outcome delivery incentives (ODI) impact ~ Low RoRE case (pre tax adjustment) |
| ToC | ||||||
| ASSUMPTIONS | MODEL INPUTS | MODEL OUTPUTS | ||||
| Front sheet | F_Inputs Mapping | BPT Extracts | ||||
| Model documentation sheet | Copy of FM F_Inputs with links to BPT | Extracts of BPT to aid population of data tables | ||||
| ToC | ||||||
| Table of contents | ||||||
| Formatting | ||||||
| Explanation of different formatting types | ||||||
| Guidance | ||||||
| Guidance on using the mapping tool | ||||||
| END |
| Formatting | ||||||||
| CELL / ROW / COLUMN COLOUR | ||||||||
| Font Colour | ||||||||
| Green Italics (no shade) | Documentation | |||||||
| F_Inputs Mapping | ||||||||
| Linked to data tables | ||||||||
| Expected to be populated from feeder models | ||||||||
| Not available in data tables | ||||||||
| WORKSHEET TAB COLOUR CODING | ||||||||
| Light Grey | Documentation Sheets | |||||||
| Dark Grey | Workbook break sheets | |||||||
| Turquoise | F_Inputs mapping sheet | |||||||
| Green | BPT Extracts sheet | |||||||
| END |
