Home

Financial model user guide

1. T m dp Final Allowed Revenues 206 239 248 266 288 295 306 286 283 282 1247 1451 2 697 112 113 m dp Wastewater 114 fmOdp PAYG 10 Ti Ti 12 12 13 13 13 14 14 57 67 124 115 fmOdp Equity Issuance Cost 116 fmOdp Return on Capital Tr 163 158 145 123 tT 33 74 55 36 778 370 1 147 Tt m dp Depreciation 38 53 81 104 123 144 150 154 153 163 410 770 1 181 118 fmOdp Tax 4 13 22 33 23 23 27 40 42 43 100 182 282 119 m dp Allowed Revenues 229 252 272 294 299 298 282 283 270 256 1 346 1 389 2 735 120 m dp Revenue solving adjustment 1055 1243 1440 1 637 1747 1815 1737 1871 1871 1858 7 128 9 222 16 350 121 m dp Allowed Revenues post revenue solvi 1284 1501 1 712 1 930 2 045 2 113 2 080 2 153 2 141 2 124 8 473 10 611 19 085 122 123 m dp Bankable allowed input 127 158 183 213 235 247 246 257 258 258 928 1 265 2 193 124 m dp Post financeability adjustments 47 10 10 10 5 0 11 1 9 125 m dp Allowed Revenues pre profiling 80 168 133 223 241 247 246 257 258 258 318 1 266 2 184 126 Em Ddp Profiling adjustment 127 fmOdp Profiled Allowed Revenues 80 168 199 229 241 247 246 257 258 258 318 1 266 2 184 128 fmOdp Capital contributions from connection charge 1 1 tt 123 m dp Final Allowed Revenues 73 168 133 223 241 247 246 257 258 258 317 1 266 2 183 130 131 End 132 60 d Setting price controls for 2015 2
2. and click on 3 Target Rate of Return Home Insert Page Layout Formulas Data Review View PR14 Model ead 4 Reset Allowed Revenues 4 Adjust Dividends T Adjust Allowed Revenues e SN 2 Adjust Gearing D Adjust PAYG and Run Off B Overwrite Bankable Profiles FX Set Net Review Size Reports Margin Headroom 3 Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues columns Retail _ Price Control Solving Data 3 59 The relevant dialogue box appears which gives the user several options The solving process will select the blended rate of return on capital to apply equal to whichever of the options the user selects taking into account the model inputs for example sourced from a company s BPTs Select Return on Capital co mmm Wholesale t Annual blended cost of capital f Average blended cost of capital 5 years t Average blended cost of capital 1 4 5 By service Water By service Wastewater e Annual blended cost of capital default f Annual blended cost of capital default f Average blended cost of capital 5 years e Average blended cost of capital 5 years f Average blended cost of capital 1 4 5 Average blended cost of capital 1 4 5 3 60 The options are as follows e Wholesale level the user can set target return on capital at wholesale level to one of the following options annual blended cost of capi
3. Reset Allowed Revenues Adjust gearing 24 2 66 2 6 7 2 68 2 69 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Target rate of return Adjust dividends Adjust PAYG and run off rates Apply equity injection Adjust Allowed Revenues Overwrite bankable profiles Reprofile Allowed Revenues d EAR E These steps may be accessed through the PR14 price control solving ribbon For retail household costs and non household charges are subject to the application of a net margin which in turn determines the retail price control By design the retail price controls must cover their underlying costs including tax and interest costs The primary lever to refine the retail price controls is the net margin assumption specified in the retail inputs section of the input sheets A headroom check is reported for the retail controls This helps to inform whether the net margin specified by the user is sufficient to cover tax and interest This is a manual check the user must make and can be reviewed on the Headroom check sheet detailed later in this document Post solving the wholesale price control the retail headroom check must be re checked for acceptability See section 3 80 Upon changing the retail net margin assumptions or input costs the wholesale price control must be re solved The model will notify the user and lead them through this This is to help ensure that the whole
4. Profit amp Loss Revenue Opex Depreciation Interest receivedi cost Profit before tax Current tax charge Profit after tax Dividends Retained earnings Balance Sheet Fixed assets Total receivables Measured income accrual Advance receipts unmeasured Advance receipts measured Wholesale creditor Capex creditor Tax creditor Dividend creditor Cash overdraft Total Accumulated profit reserve Cashflow Opening balance Accounting revenue Movement in debtors Movement in MIA Movement in advance receipts Total cash received from customers Wholesale charge Capital expenditure Movement in trade creditor Movement in Capex Creditor Retail service opex Closing balance before payinglreceiving interest and tax Interest receivedl cost for Receivables and Retail Service Opes Corporation tax Dividends Closing balance Price control solving J Household Financial Statements 2014 15 eru 2016 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 1 2 3 4 SES 7 8 api 1103 741 1268 364 1 331653 1376 876 1 334 567 1332 510 1347 056 1403 806 1538 730 1685 030 1 089 682 1253 669 13156 001 1 353 917 1375 344 1313 086 1327 286 1383 13 1 517 977 1 663 703 0 963 2 063 2 604 3 728 5 607 5 607 5 607 5 607 5 607 5 607 1 429 3 512 4 387 4 702 5 430 5 591 4 881 4 094 3 451 2 916 11 661 9 720 8 666 8 528 8 186 8 226 3 282
5. e Phase 2 PAYG and run off rates e Phase 3 gearing adjustment e Phase 4 target rates of return e Phase 5 dividend adjustment e Phase 6 equity injection e Phase 7 Allowed Revenue adjustment e Phase 8 bankable allowed revenue profile e Phase 9 reprofiled allowed revenues 4 17 The following screen shot shows the Allowed Revenue section of the dashboard 53 2024 25 E Return on Capital Employed RoCE 3 2 7 8 6 3 1 6 3 8 6 7 TOM 8 37 3 1 9 3 Return on Regulatory Equity RoRE 10 67 9 3 8 0 114 3 6 6 8 TY 8 1 6 8 8 8 Cash Interest Cover 15 955 13 220 12 707 11 831 12 834 15 361 18 376 17 882 16 891 15 815 Adjusted cash interest cover ratio 7 610 4 882 4 132 3 302 3 198 3 927 5 006 4 952 4 579 4 166 Funds from operationsidebt 0 490 0 570 0 407 0 424 0 688 1 126 1 690 2 522 3 860 6 022 Retained cash flowldebt 0 253 0 043 0 451 0 196 0 347 0 351 0 294 0 438 0 498 0 534 Gearing 11 5 10 5 16 9 18 3 12 5 8 27 5 6 3 9 2 6 17 3 i Dividend Cover 2 125 0 558 0 635 0 722 0 887 0 331 0 335 0 332 0 331 0 926 3 i Regulatory equitylretained earnings for the regulatory company 6 359 4 223 5 088 256 418 221450 195 285 185 684 170 022 155 409 141 895 RCVIEBITDA 17 047 13 342 11845 10 325 3 262 8 710 8 554 8 350 8 167 8 002 RCF to Capex 5 962 2 373 6 959 0 314 3 080 6 863 4 145 3 367 2 813 2 309 Current Final Indicators Return on Capita
6. tmOd gt Exqpatylequance Con n nig Peter on Capra s9 5 xe m n os ns K 53 KI su 721 1224 m mog Depeeouten 3 45 SN sa 7 95 Lu na 3 Ha KA 555 856 ni onda Tax z3 n da 25 l 3 6 t 33 Ri mob Allowed Revenues kaalt an so 543 505 S21 546 511 601 651 2 561 2065 5450 we m dp Reverse scheet z z v Gnd Adored Revemers ust rerum sulviery adjustment HU su SU J us Si AL n un eu 21 213 543 vM Us Cp fankable allowed input vs mOdp Pon finance ab n aipcmener d e e e as es A es wT mOdp Allowed Revenue pre penting ws m Odp Profiling adputment Lj 433 dia dre ss 425 EK 443 455 deg 2241 2223 454 va Cm Ot Profded Allowed Revenues TE ces DE DZ 453 425 4X 443 455 dh 2261 0203 amp 464 Do OnOdp Capea conmibunong fom conmecton changer m Om 04 Final Allowed D Ka 433 zi 466 483 425 43 443 455 deg 2261 2223 KE ns eno Wastewater Ka mp PAYG Ki HI uw Mi LX M v3 VJ TW w 52 GIA LEA Uus timQip Lqutylssuarme Cost e LS Em Qdp Fatum on Capas OU Zi Si To ma SI ko Kal NH 331 dap mi GU mOdp Depeecumon n za m z3 n Gi at 1 ss e im 75 na m dp Ta AU n 2 _ e 3 DH D ES 73 m 51 EI mOdp Allowed Revenues nn Za NI NBC TED HIN 781 Sem mU 55 isi Tano ESRI Moi m Pewersse soling ad Wi m r Allowed Revenues post revenue solving adjustment Ze 231 Z i NI BD Kn EI 275 Ku 259 imi 1370 2 551 x2 i LE On Od Bankable alowed input Ma on Odp Post Inarce abi
7. 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 Hold allowed revenue E E E E E D be n Bankable allowed revenues NPV 2015 2020 962 334m NPV 2020 2025 1 091 652m Cancel 3 0 If Allowed Revenue is changed without changing any other relevant factors in the model then K will remain constant when Reprofile is clicked and only Allowed Revenue will be changed 3 71 If the user forces an Allowed Revenue by entering specific values in particular years clicking reprofile will calculate K such that the NPV of the profile including any forced adjustments is equal to the original profile 3 2 Itis possible to exclude specific years from the solving process using checkboxes against each year If the check boxes at the top of the dialogue box are ticked next to each year shown then revenue will be held constant in the solving process for those years 3 3 Allowed Revenue for the first year may be input to the model if a different value to the bankable Allowed Revenue after applying post financeability adjustments is required 47 3 74 3 75 3 76 3 3 78 3 79 Setting price controls for 2015 20 PR14 financial model user guide September 2014 The year 1 Allowed Revenue either as input or as pre populated from bankable Allowed Revenue after post financeability adjustments is held constant as part of the reprofiling stage for AMP 1 Year 1 Allow
8. 1975 611 34 fmOdp Tax Charge 28 44 60 77 83 92 32 95 38 101 297 473 776 zz m dp Deferred Tax Charge 138 143 158 88 35 2 3 3 2 2 562 6 568 36 m dp Profit after Tax 438 399 348 7 34 223 279 306 340 347 1 098 1501 403 37 Odp Effective Tax Rate 5 9 13 445 60 29 247 247 23 23 22 24 127 38 39 Financial Indicators 40 41 Appointee Target financial indicators used in determining Price Controls 42 43 3 Nr 3dp Cash Interest Cover 3 730 3 961 4 389 4 920 5 688 5 032 6 651 6 746 6 712 6 636 4 570 6 566 5 572 3 dd 4 Nr 3dp Adjusted cash interest cover ratio 0 171 0 055 0 109 0 207 0 318 0 352 0 542 0 623 0 614 0 587 0 174 0 543 0 359 45 5 Nr 3dp Funds from operations debt 0 173 0 206 0 202 0 298 0 478 0 804 1104 1448 1851 2 272 0 256 1 332 0 506 46 6 Nr 3dp Retained cash flow debt 0 170 0 047 0 231 0 150 0 313 0 428 0 206 0 260 0 246 0 202 0 025 0 290 0 048 47 7 Odp Gearing 24 23 28 23 16 10 8 6 5 47 23 TA 15 48 8 Nr 3dp Dividend Cover 2 736 0 805 0 328 0 326 0 325 0 325 0 313 1 676 0 924 0 177 49 9 Nr 3dp Regulatory equitulretained earnings for the re 5 283 3 583 3 137 459 435 38 432 218 950 184 641 153 472 154 785 141234 9 384 153 017 19 869 50 10 Nr 3dp RCVIEBITDA 17 336 14 562 12 320 10 657 3 483 8 905 8 736 8 521 8 331 8 159 12 262 8 521 10 006 51 52 Wholesale Additional financial indicators 53 54 Wholesale 55 m dp
9. 4 1 AR di 324i TERTIA SALI WEI TERT ERE cr aria pas manico ed Cai metta care Hti KEE zn A7 M ia ar au ait nw LET UH E Lieu DLE BET LARE AR 17 418 Ta War ETSE Tm vga VE Moz LE HE a 44 Lei 1a L ARCA STATES BVT i Te ST ARD TAX F a T X Se DAA aaa EE 5 K om Sesh telai eee terrena Wer Gaz TIRI TESE Em DA aan zer uma mt a 18 es ON im Appeton scien thig for HH rectal Si SO 41341 Te eT rine Dar 7 Tonal esae reg meee perde beeches mls ed Sa praciece Ve 1 048 LEM r3 TM mu GE HE ELE SS DR AS HH Bereta E posdiort a H L i WE Em SES finitis ia ON Ge Gan ee ee Ti ee rm ee L i im rz EAE CG cea dei denari uM SITA Re TA 741133 HOIA THETIS 7812231 I EHA t4 Le sii Spreconi ome iiim 5521 WC H r z d 128 Lu Lalli poer pgs BBE days i TRIS 700497 T3082 TD T ES Li Oo as BR 30 LIE a GET E ia aka capital Phra qne quS y qui r4 Gig Gsm Gm og ost ndum sof 28 Pair be Tanri Prete z 3 3 z 3 n WE 90 tm akua fen Eu Charge Cass E x WE im MEASURED marcio red BCEE raf 7348 BER igih 1053 Ou Wii LE us 3355 KEN n La Ers UNE SLD Heke eed rel E r cower Lai EES ann RL A A H So arts axe ame s L S E ANE c uid mox 12 AREAL STATEMENT CAPITAL EDXPLBDITURI a oT jM E Aieka Bic scitis I wn iaia mm 07MM e WE Wr dp red pret Frast ena 1550 d SO mu 1555 TH IH Te 1550 15M ae e AT WEI ke angles Total Fpord Amata ai cani tom tes ka page depnto
10. 836 63 031 68 086 42 292 14 511 19 919 20 584 27 458 7 480 23 016 19 27A 0 2 8 ka 22 78 18 233 33 136 29 042 15 580 43 862 33 172 27 245 18 453 8 618 11 206 9 341 3 703 4 580 12 851 1 N75 2 2 1 4 14 A 20 80 22 512 62 952 150 923 46 822 286 852 184 108 195 383 116 847 43 671 58 700 57 307 74 904 22 479 71 815 GR d 3 4 1 5 13 214 79 40 744 102 088 173 365 62 401 330 714 217 279 222 628 135 300 52 290 69 906 66 648 84 613 27 060 84 666 RR ATR gt Il Setting price controls for 2015 20 PR14 financial model user guide HH Fin Stats NHH Fin Stats September 2014 2 62 These sheets calculate the retail income statement balance sheet and cash flow for household and non household respectively They have exactly the same layout other than for Advance Receipts Yr Yr Yr 1 5 6 10 1 10 3 4 Data Chk 5 6 7 Unit 8 3 m 3dp 10 m3dp Ti m 3dp 12 m 3dp 13 m 3dp 14 m 3dp 15 m 3dp 16 m 3dp 17 m 3dp 18 iz 20 21 m 3dp 22 m 3dp 23 m 3dp 24 m 3dp 29 m 3dp 26 m 3dp 2 Em 3dp 28 m 3dp 23 m 3dp 3 m 3dp 31 m 3dp 32 ao m 3dp 35 36 37 38 m 3dp 39 40 m 3dp 41 m 3dp 42 Em 3dp 43 m 3dp 44 m 3dp 45 46 m 3dp 47 Em 3dp 48 m 3dp 49 m 3dp 50 m 3dp 51 m 3dp 52 53 m 3dp 54 Em 3dp 55 m 3dp 56 m 3dp E Calc Chk Year
11. 9 35 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Stage 1 reset Allowed Revenues 3 28 This step removes all user inputs relating to wholesale price control solving that is it sets these inputs to zero Each time a user elects to begin a new version of the model based on a previous version which has solved the price controls this step must be applied to clear old inputs 3 29 This is a semi automated stage the user must manually run this stage while the model automatically calculates the initial Allowed Revenues 3 30 This step is implied at the Calculate Initial Allowed Revenue stage as shown at the solving process diagram in the previous sub section 3 31 To perform this step go to the PR14 Model tab in the ribbon and click the button 1 Reset Allowed Revenues Home Insert Page Layout Formulas Data Review View PR14 Model m 7 Reset Allowed Revenues BA Adjust Dividends 7 Adjust Allowed Revenues LES 2 Adjust Gearing B Adjust PAYG and Run Off B Overwrite Bankable Profiles Set Net Review Margin Headroom d Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues Retail Price Control Solving X3 Gk Size Reports Columns Data 3 32 If you wish to revert to an earlier stage between 2 and 9 in the solving process a warning box will appear to ask 7 you ivan to reset the input values and outputs for the current model scenar
12. before running the gearing adjustment below The notionalisation process is used to move the capital structure used in the model from the company s actual capital structure to a notional capital structure The company s actual capital structure uses its actual debt instruments actual cost of debt and proposed level of dividends as presented in the business plan tables This is input into the model through the various feeder models and F Inputs The notionalisation process converts it to a notional capital structure based on a notional level of gearing and including a notional cost of debt and notional level of dividends which are consistent with the notional appointee WACC For companies which are geared above the notional level of gearing the tax calculations take into account the company s actual level of gearing and the cost of debt embedded within the notional WACC when calculating the interest shield available to offset against the tax charge For companies which are below the notional level of gearing the interest used in the tax computation is based on the both the notional gearing level and the cost of debt embedded within the notional WACC Applying the notional cost of debt cost of capital 1 Set the cost of capital and real cost of debt in line with Ofwat s view of the notional structure 2 Set the household and non household retail margins in line with Ofwat s view of the notional structure 3 Set the cash overdraft intere
13. e Key defines the main colour key used throughout the model e RPI calculates the RPI indices used throughout the model using assumptions provided via the F_Inputs sheet e Error Checks summarises the model s error checks e Index linked debt macro a sheet that calculates tax adjustments that result from overriding the opening index linked debt balance The following sections outline the above sheets in further detail The initial price controls are calculated automatically from data in the input sheets The wholesale price control solving function is controlled by the price control solving menu bar that takes users through the solving process This is given a brief overview at section 2 63 and a detailed explanation in section 3 later in this document For retail household costs and non household costs are subject to a charge for net margin which covers tax and interest costs which in turn determines the retail price control Retail net margin is specified in the retail section of the F_Inputs sheet and can be overridden in the Inout Nominal worksheet Setting price controls for 2015 20 PR14 financial model user guide September 2014 Input sheets 2 2 There are four input sheets for the model e F Inputs inputs for the wholesale and retail sections e Adjustments adjustments to the F_Inputs data which can be used by Ofwat to adjust data submitted by companies This is used primarily to a
14. model user guide September 2014 Where Show annual values is de selected the Start year and End year fields are disabled and become greyed out If a user selects Change rounding settings the inputs in the Format section of the form are activated Here the user can change the level of precision displayed for each of the following number fields e Values in millions between 0 and 3 decimal places e Values in s between 0 and 2 decimal places e Indicators ages between 0 and 2 decimal places If the Export to PDF option is selected the form will save PDFs of the reports selected to the same folder path as the model On clicking OK the reports are displayed according to the form inputs and the user shown the Reports summary sheet On clicking Cancel the format and visibility of the reports are not altered and the user moved to the Reports summary sheet Dashboard 4 12 This contains the model outputs for water wastewater and appointee financial indicators The following sub sections outline the main outputs available in the Dashboard worksheet from top to bottom Financial indicators initial and current 4 13 The following financial indicators are reported both for the initial values set before solving and the final values set as a result of solving e Return on Capital Employed ROCE e Return on Regulatory Equity RORE e Cash Interest Cover e Adjust
15. revenue adjustments in phases 8 and 9 which differ in format to the above adjustments and are therefore explained separately in the following sub sections 3 MU Year 2012 13 2013 14 2014 15 2015 16 2046 47 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 99 100 Initial Revised 101 r 102 r a 103 J 104 Change in allowed revenue 105 Real m 3dp Water 7 0 000 0 000 0 000 0 000 0 000 0 000 0 000 106 Real m 3dp Wastewater 0 000 0 000 0 000 0 000 0 000 0 000 0 000 0 000 0 000 0 000 107 Real m 3dp Appointee 0 000 Um 1 Nominal dp Return on Capital Employed RoCE 3 2 7 8 6 3 1 6 3 8 6 7 TTA 8 34 3 1 3 3 ma Nominal dp Return on Regulatory Equity RoRE 10 6 3 3 8 0 114 3 6 6 8 TOY 8 1 3 8 8 8 113 Nominal Mr 3dp Cash Interest Cover 15 355 13 220 12 690 11 833 12 835 15 371 18 373 17 883 16 891 15 816 114 Nominal Nr 3dp Adjusted cash interest cover ratio 7 610 4 883 4 114 3 303 3 138 3 338 5 002 4 352 4 573 4 166 115 Nominal Nr 3dp Funds from operationside 0 490 0 570 0 406 0 424 0 687 1 126 1 688 2 519 3 854 6 007 116 Nomina Nr 3dp Retained cash flow debt 0 253 0 043 0 451 0 196 0 346 0 351 0 294 0 437 0 497 0 532 Tt Nominal ddp earing T15z 10 5 16 9 18 3 12 5 8 27 5 6 3 9 2 6 17 118 Nomina Nr 3dp Dividend Cover 2 125 0 658 0 696 0 722 0 887 0 932 0 934 0 932 0 93
16. shares set formula to Debt Float Bf Input Shares Pref 2015 Input Debt Fixed Bf Input Set the opening floating rate debt balance to zero oet the movements in fixed and floating rate debt to zero and remove any apportionments oet the interest receivable in line with the above capital structure e g to Zero oet the opening preference shares balance and the movements in preference shares to zero Preparing notional dividends 11 12 13 14 15 Set the preference share dividends to zero oet the inputs for ordinary dividend and special ordinary dividend paid per share to zero Set the percentage of dividends issued as scrip shares and the interim dividends to zero Set the dividend yield and real dividend growth figures for all years in line with Ofwat s view of the notional structure Set the share issues in each year to zero 3 47 Applying index linked debt 16 Set the indexation rate for index linked debt in line with Ofwat s view of RPI set formula to Inflation Yearly Average The model will recalculate the indexation of index lined debt rather than using input values Note that using Ofwat RPI figures is part of moving to the Ofwat view of costs and is done for consistency across all companies rather than being part of notionalisation 17 Set the gearing in phases 1 and 2 of the model for the purpose of calculating dividends under the growth method using the Gearing Adjustments on the Adjust
17. 0 PR14 financial model user guide September 2014 This user guide is a nota policy statement and users are referred to Ofwat s methodology statement and associated documents for further information 5 Index linked debt macro Overriding index linked debt opening position 5 1 5 2 5 3 5 4 5 9 5 6 The model includes functionality to override the opening index linked debt balance via the Input Nominal sheet If the opening index linked debt balance is overridden the model then provides the user with an option to hold the tax charge constant The index linked debt macro sheet supports the calculation of the tax adjustment required to keep the tax consistent with the tax charge before the index linked debt override The override to the index linked debt opening balance can be entered to the Input Nominal sheet in the override cell in section 6 4 There is a button on the index linked debt macro sheet which takes you to the cell where the adjustment should be made The model will not allow the opening index linked debt position to be over ridden without removing any existing tax adjustments Upon overriding the index linked debt opening balance OR removing an existing override the model will automatically prompt the user with the option to remove existing tax adjustments and then to make a tax adjustment The tax adjustment offered allows tax charge to be set consistent with the level of tax prior to adjusting the index linke
18. 1 0 926 119 Nomina Nr3dp Regulatory equitulretained earnings for the regulatory company 6 359 4 223 5 084 256 394 221438 201 613 185 062 170 026 155 408 141835 120 Nominal Nr 3dp RCVIEBITDA 17 047 13 341 11 860 10 325 9 262 T04 8 555 8 350 8 167 8 002 121 Nominal Nr 3dp RCF to Capex 5 962 2 374 6 974 0 314 3 080 6 873 4 143 3 367 2 813 2 309 Gearing 5 t e 5 Year BSA 261 015 Override bankable allowed revenues and post financeability revenue adjustments 4 23 After determining Allowed Revenues that are considered financeable the model allows post financeability revenue adjustments to be applied They include e service incentive mechanism SIM adjustment or e opex incentive allowance only e CIS adjustment or e revenue correction mechanism RCM adjustment or e equity injection claw back only e tax refinancing benefit claw back only e outcome delivery incentive or and e totex menu additional income or 95 Setting price controls for 2015 20 PR14 financial model user guide September 2014 4 24 These are entered at the bottom of the wholesale section of the Inputs Real sheet in absolute terms ve entry as a reward ve entry as a penalty per annum and by service water and wastewater 4 25 The following screen shot shows the post financeability section of the dashboard H
19. 10 486 11 695 12 805 2 332 1 944 1 733 1 706 1 637 1 645 1 856 2 097 2 339 2 561 9 329 7 776 6 933 6 823 6 549 6 581 7 426 8 383 3 356 10 244 4 665 3 888 3 467 3 411 3 274 6 581 7 426 8 383 3 356 10 244 ez 8 721 17 598 20 404 27 316 41 099 35 492 29 885 24 278 18 671 13 064 I 112 000 115 675 133 413 140 775 145 510 146 386 133 635 140 468 145 185 157 532 170 451 116 311 133 332 152 303 162 534 170 560 168 632 176 367 183 327 214 355 242 872 34 451 39 121 39 153 38 601 37 353 35 175 34 462 34 773 36 866 33 006 74 979 30 536 38 674 106 285 111 177 109 597 114 133 122 563 138 242 155 667 82 803 36 713 102 131 105 883 107 063 101 614 102 536 106 368 117 903 129 796 0 956 1 073 0 534 1 103 1 853 ajz 2 332 1 944 1 733 1 706 1 637 1 645 1 856 2 037 2 333 2 561 4 665 3 868 3 467 3 411 3 274 35 852 50 108 55 712 53 535 76 382 70 703 60 956 51 889 45 351 38 668 4 665 8 553 12 013 15 430 19 705 25 286 32 711 41 101 50 457 60 700 4 665 8 553 12 013 15 430 18 705 25 286 32 711 41 101 50 457 60 700 35 852 50 108 55 712 63 535 76 382 70 703 60 956 51 883 45 351 1103 741 1 268 364 1331659 1376 576 1394567 1332510 1347 056 1403 506 1538 730 1685 030 3 676 17 743 7 357 4 735 1 476 7 150 0 633 4 717 12 347 12 318 3 205 23 620 12 372 10 230
20. 2 6 651 6 746 6 712 6 636 4 570 6 566 5 572 44 4 Nr 3dp Adjusted cash interest cover ratio 0 171 0 055 0 109 0 207 0 318 0 352 0 542 0 623 0 614 0 587 0 174 0 543 0 359 45 5 Nr 3dp Funds from operationsidebt 0 173 0 206 0 202 0 298 0 478 0 804 1 104 1 448 1 851 2 272 0 256 1 332 0 506 46 6 Nr 3dp Retained cash flow debt 0 170 0 047 0 231 0 150 0 313 0 428 0 206 0 260 0 246 0 202 0 025 0 290 0 048 4 Odp Gearing 247 23 28 23 16 10 8 6 5 47 23 TA 15 48 8 Nr 3dp Dividend Cover 2 736 0 805 0 928 0 926 0 925 0 925 0 313 1 676 0 924 0 177 49 9 Nr 3dp Regulatory equitylretained earnings for the re 5 283 3 583 3137 453 435 38 432 218 950 184 641 163 472 154 785 141234 3 384 153 017 13 853 50 10 Nr 3dp RCVIEBITDA 17 336 14 562 12 320 10 657 9 489 6 905 8 736 8 521 8 331 8 153 12 262 8 521 10 006 51 52 Wholesale Additional financial indicators DA 54 Wholesale 55 m dp Closing year end RCV 4 071 4 199 4 301 4 374 4 414 4 435 4 453 4 467 4 479 4 489 4 414 4 489 4 489 56 7 m dp Gearing 0 0 0 0 0 0 0 0 0 0 0 0 0 57 m dp Year average RCV 4 045 4 18 4 303 4 33 4 44 4 48 4 49 4 51 4 52 4 54 4 16 4 45 4 20 58 Odp Year average RCV Growth YoY 4 3 ZA ta ta 0x DA on 0x 2 on LA 59 Ddp Pre tas return on DCH 12 10 9 DA 5 9 10 10 TUA T 5 10 60 Odp Post tax return on REW 9 8 6 2 4 TA dA 8 EK gx 3 ax 61 1 Odp Return on Capital Employed RoC
21. 23 6 Real m 3dp Tax refinancing benefit clawback only 0 020 0 020 0 020 0 020 0 020 0 020 0 020 0 020 0 020 0 020 E 324 Real m 3dp Outcome delivery incentive or 0 200 0 200 0 200 0 200 0 200 0 200 0 200 0 200 0 200 0 200 325 8 Real m3dp otex m dditional income or 326 Real m 3dp Water total post financeability revenue adjustments 0 320 12 320 2 320 2 320 2 320 2 320 12 320 2 320 0 320 0 320 Abst Finan 327 328 Wastewater 323 Real m 3dp SIM adjustment or 0 250 0 250 0 250 0 250 0 250 0 250 0 250 0 250 0 250 0 250 330 Real m 3dp Opes incentive allow ance only 2 000 331 3 Real m 3dp CIS adjustment or 000 5 000 5 000 5 000 332 4 Real m 3dp RCM adjustment or 55 570 333 5 Real m 3dp Equity injection clawback only 5 000 5 000 5 000 5 000 5 000 334 B Real m 3dp Tas refinancing benefit clawback only 0 400 LS 335 Real m3dp Outcome delivery incentive or 3 000 336 8 Real m 3dp Totes menu additional income or 337 Real m 3dp Wastewater total post financeability revenue adjustments 46 720 10 250 10 250 10 250 5 250 0 250 0 250 0 250 0 250 0 250 Abst Amar 338 395 Allowed revenue after post financeability revenue adjustments d Reprofile Allowed Revenue 4 26 After determining Allowed Revenues and applying a post financeability revenue adjustment s the user may elect
22. 23 712 31 791 48 089 77 423 105 836 43 852 286 852 330 714 34 dp NHH net margin tariff band 6 2 131 3 472 5 546 8 603 13 413 17 834 23 363 32 741 46 479 63 031 33 172 184 108 217 279 35 dp NHH net margin tariff band 7 2 120 2 560 3 366 5 881 TL 718 17 871 24 906 36 215 48 304 68 086 27 245 195 393 222 628 36 Odp NHH net margin tariff band 8 1 164 1321 3 140 4 820 7 409 3 863 13 248 20 155 31288 42 232 18 453 116 847 135 300 37 Odp NHH net margin tariff band 3 547 914 1 464 2 245 3 448 4 484 5 882 7 822 10 973 14 511 8 616 43 671 52 290 e 38 Odp NHH net margin tariff band 10 732 1 206 1 895 2 907 4 465 5 738 7 182 10 406 14 794 19 919 11 206 58 700 69 906 s 33 dp NHH net margin tariff band 11 570 365 1535 2 448 3 763 4 954 6 593 3 837 15 338 20 584 3 341 57 307 66 648 40 dp MHH net margin tariff band 12 576 385 1 610 2 566 4 073 5 804 7 885 13 081 20 676 27 458 3 703 74 904 84 613 41 Odp NHH net margin tariff band 13 454 476 752 1147 1152 2 230 3 018 4 040 5 651 7 480 4 580 22 479 27 060 42 dp NHH net margin tariff band 14 TOT 1335 2 132 3 365 5 172 6 783 306 13 644 21 466 29 016 12 851 71 815 84 666 d3 dy BIEL netmarointariff hand 15 IA 17 nan 2 209 d 918 n2731 ZB 22132 fd 434 131216 17075 56 403 PR d In Wholesale price control solving 3 15 Once the initial wholesale price control has been calculated the user may elect to use optional functionality that will help determine price controls that are finance
23. 3 238 321 247 843 265 506 288 403 235 287 305 541 285 610 282 734 281 539 Real m 3dp Final allowed revenue 205 913 238 921 247 843 265 506 288 403 235 287 305 541 285 610 282 734 281 539 2dp Initial k Water 44 08 37 14 26 78 21 02 18 50 10 68 5 387 3 50 5 51 4 89 x 2dp Final k Water 2 56 16 03 3 74 7 13 3 63 2 39 3 48 6 52 1 00 0 42 Wastewater Real m 3dp Initial allowed revenue 231 071 242 858 251 477 258 358 252 464 240 438 217 407 206 879 187 843 168 992 Real m 3dp PAYG amp run off rates 0 000 0 000 0 000 Real m 3dp Gearing adjustment 4 299 4 882 5 486 5 188 2 057 2 131 1627 3 343 3 264 3 185 Real m 3dp Target rates of return 100 211 73 775 57 374 33 936 14 750 8 321 29 922 53 304 73 115 92 040 Real m 3dp Dividend adjustments Real m 3dp Equity injection Real m 3dp Allowed revenue adjustment Real m 3dp Allowed revenue before post financeability revenue adjustments 126 562 158 201 188 616 219 234 235 656 245 588 245 702 255 841 257 694 257 846 Real m3dp Create bankable allowed input Real m 3dp Allowed revenue after post financeability revenue adjustments 79 842 168 451 198 866 229 484 240 906 246 938 245 952 257 091 257 944 258 096 Real m 3dp Re profiled allowed revenue 79 842 168 451 198 866 223 484 240 306 245 338 245 952 257 091 257 944 258 036 Real m 3dp Final allowed revenue 225 000 78
24. 33 Monrad At 258 096 lone 533 635 247 843 198 866 446 709 265 506 229 484 434 330 288 403 240 306 523 303 200 000 205 913 238 321 225 000 168 451 407 372 78 842 284 755 2 96 16 03 3 74 7 13 8 63 2 39 3 48 6 52 1 00 0 42 64 96 113 66 18 06 5 40 4 98 2 514 0 40 4 53 0 34 0 06 4 29 The Executive Summary report sets out the following information resulting from the price control solving process which is outlined in detail in section 3 Price Control Allowed Revenues 4 30 Information is provided as follows e Wholesale Allowed Revenues e Wholesale K factors e K e Broken down by water and wastewater e Retail Allowed Revenues by 4 31 household and non household based on average allowed revenue per customer by tariff band Executive Summary The following screen shot shows the Allowed Revenue section of the 57 Setting price controls for 2015 20 PR14 financial model user guide September 2014 il 2 H fa St GAN 2016 16 2016 17 2018 19 2019 20 m2 m 2324 292425 DI On dp Non heuneheld 1 1 1 T i E LC Lie Ma edocet percentage of rst margani m x Odp Househokd 6627 1 2th 2394ec DSS Ra Tox Geo 253k Ggzti 2306501 m Odp Norhwrasebuohd ur ue ur Wi VS Gt US ves Vis VS T TH Wholesale Adoved Revenue breakdown TM ts mdp Ware n mOdp PASO 250 3T 331 345 376 288 295 308 so 29 1550 15 Im y
25. 8 027 1 868 7 675 13 560 25 029 27 917 103 430 19 226 3 163 7 053 4 244 4 358 3 888 8 674 17 774 19 564 1 212 701 1246 828 1321100 1368 970 1 383 303 1337 171 1342 637 1394 204 1519 128 1663 759 993 702 1 160 625 1 226 293 1 270 594 1284 762 1219 365 1 230 427 1283 620 1 414 841 1557 429 9 690 10 340 5 410 11 240 18 730 148 708 13 310 5 472 3 692 1 181 5 450 0 922 4 433 10 935 11 882 0 956 0 123 0 545 0 575 0 745 1 853 ia i 95 380 33 044 89 708 83 323 90 582 33 721 96 859 39 997 X 103 136 106 274 34 423 33 599 45 493 53 633 66 435 60 200 54 430 45 938 39 802 33 413 1 429 3 512 4 387 4 702 5 430 5 591 4 881 4 034 3 451 2 916 2 332 1 344 1 733 1 706 1 637 1 645 1 656 2 037 2 333 4 665 3 888 3 467 3 411 3 274 1 35 852 50 108 55 712 63 535 76 982 70 703 60 956 51 883 45 351 36 668 2 63 The financial model provides users with optional functionality in order to help determine the wholesale price controls 2 64 This sub section provides a brief overview of the key steps in the price control solving process The stages available in the price control solving process are described in more detail in section 3 later in the user guide 2 65 The following steps are available to the user in determining the wholesale price controls 1 2
26. 842 168 451 198 866 223 484 240 306 245 338 245 352 257 091 257 944 258 036 2dp Initial k Wastewater 2 70 5 10 3 55 2 74 2 28 4 74 9 60 4 84 3 20 10 03 2dp Final k Wastewater 64 96 113 66 18 06 15 40 4 387 2 514 0 40 4 53 0 347 0 06 Price control solving adjustments 4 18 4 19 4 20 The next sections of the dashboard set out adjustments made in the solving process for the following areas e PAYG and run off rates e Gearing adjustment e Target rates of return blended e Dividend adjustments e Equity injection e Allowed Revenue adjustment These adjustments are broken down as follows where applicable e Water e Wastewater The corresponding effects of the adjustments alongside the adjusted driver are shown through the following sections e Allowed Revenues e Financial indicators initial and current 54 Il Setting price controls for 2015 20 PR14 financial model user guide September 2014 4 21 The following screen shot shows an example section of the dashboard for the PAYG and run off rate adjustments The calculations for the other adjustments outlined above follow the same broad format albeit with differences to reflect the individual adjustments which were explained in detail earlier in chapter 3 on price control solving 4 22 Note that there are calculations for two further types of adjustment reprofiling bankable revenues and post financeability
27. A ka A A A A A A A A 15 Odp Headroom percentage of net margin 666 692 1931 2127 2251 21654 20257 195074 158474 1795 1827 1942 1905 16 kel 17 HEADROOM CHECK NHH 18 B m Odp NHH aggregate net margin 0 0 0 0 0 0 0 0 1 1 0 2 3 s 20 m Odp NHH working capital interest 0 0 0 0 0 0 0 0 1 1 2 2 4 i ea m Odp NHH tas charge 0 0 0 0 0 0 0 0 0 0 0 t n ou DE m Odp Headroom 0 0 0 0 0 0 1 1 1 2 4 5 23 24 Odp NHH aggregate net margin percentage BA TA TA 8x 10 Wx 127 13 15 Tz dA 147 13 ves x Odp NHH working capital interest percentage of net margin 4 4 A A lt A x s 26 x Odp NHH tax charge percentage of net margin 20 2 227 23 227 21 20 20 20 20 22 20 2 ET x Odp Headroom percentage of net margin 807 79 T8 TUA 78 79 80 80 80 807 Ton 807 79 s 28 SIE dp MHH net margin tariff band 1 3 340 3 483 3 642 3 822 3 946 4 121 4 279 4 479 4 705 4 928 18 233 22 512 40 744 30 Odp NHH net margin tariff band 2 5 583 7 104 7 733 8 456 9 254 10 063 10 971 12 316 13 997 15 600 39 136 62 952 102 088 2 ec dp MHH net margin tariff band 3 1 665 3 117 4 733 7 556 11 904 15 192 20 858 26 163 36 959 51 752 29 042 150 923 179 965 32 dp NHH net margin tariff band 4 946 1774 2 297 3 338 6 625 7 969 11 433 8 103 6 493 12 624 15 580 46 822 62 401 33 Odp NHH net margin tariff band 5 2 565 4 423 7 425 11 517 17 927
28. BI A B D E J K L M N O P a R Dashboard z 3 UM Year 2012 13 2013 14 2014 15 2015 16 2016 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 300 301 AN ____Phase8 Bankable all me 302 303 Create bankable allowed input 304 305 Create bankable allowed input 306 Real m 3dp Water 205 533 226 601 245 523 263 186 286 083 292 967 293 221 283 290 282 414 281 219 307 Real m 3dp Wastewater 126 562 158 201 188 616 219 234 235 656 246 688 245 702 256 841 257 694 257 846 308 Real m 3dp Appointee 332 155 384 802 434 133 482 420 521 739 539 655 538 923 540 131 540 108 539 065 309 310 Final allowed revenue before post financeability revenue adjustments 311 Real m 3dp Water 205 533 226 601 245 523 263 186 286 083 292 967 293 221 283 290 282 414 281 219 312 Real m 3dp Wastewater 126 562 158 201 188 616 219 234 235 656 246 688 245 702 256 841 257 694 257 846 313 Real m 3dp Appointee 332 155 384 802 434 139 482 420 521 739 539 655 538 923 540 131 540 108 539 065 314 315 Post financeability revenue adjustments 316 31 ater 318 Real m3dp SIM adjustment or 10 000 10 000 319 Real m 3dp Opes incentive allow ance only 0 100 0 100 0 100 0 100 0 100 0 100 0 100 0 100 0 100 0 100 320 3 Real m 3dp CIS adjustment or 2 000 2 000 2 000 2 000 2 000 2 000 2 000 321 4 Real m 3dp RCM adjustment or 7 322 5 Real m3dp Equity injection clawback only 3
29. Closing year end RCV 4 071 4 199 4 301 4 374 4 414 4 435 4 453 4 467 4 479 4 483 4 414 4 489 4 483 56 7 m dp Gearing 0 0 0 0 0 0 0 0 5 m dp Year average DCH 4 045 4 186 4 303 4 391 4 449 4 48 4 499 4 516 4 529 4 540 4 167 4 452 4 204 58 7 Odp Year average RCY Growth vor da DA 2 T TA DA DA DA DA 2 DA 1x 59 Odp Pre tas return on RCV 12 10 9 LA 5 3x 10 10 Tt Wx 5 10 E 60 Odp Post tax return on DCH 9 8 6 24 da TA 8 8 EK gx 3 8 Bi 1 Odp Return on Capital Employed RoCE 9 8 6 24 47 TA da 87 EA EK 3 8 3 B2 z Odp Return on Regulatory Equity RoRE 13 12 10 0x dA 6 TA Ta 8x 8x 6 TA 2 63 f Nr 3dp RCF to Capex 4 333 1 848 5 207 3 347 10 523 3 374 4 700 3 905 3 449 3 011 4 268 4 819 4 561 64 65 Mater 66 m dp Closing year end RCV 2 409 2 456 2 491 2 515 2 525 2 528 2 530 2 530 2 529 2 526 2 525 2 526 2 526 67 fmOdp Year average RCV 2 410 2 462 2 504 2 534 2 551 2 558 2 561 2 562 2 561 2 559 2 439 2 526 2 439 Financial indicators 4 36 The financial indicators outlined earlier are provided at the appointee level e Wholesale water and wastewater e Retail household and non household al Executive Summary 2020 21 2021 22 2022 23 2023 24 2024 25 2015 20 2020 25 2015 25 39 Financial Indicators 40 41 Appointee Target financial indicators used in determining Price Controls 42 43 3 Nr 3dp Cash Interest Cover 3 730 3 361 4 383 4 320 5 688 6 09
30. E sta Om Sie Curare Te I i ra Au ws DI TERI eet Cumt Cie LE im Xn Deiengd Fun ae ee EE EE iH imp Vena oralio ihr e aT ca RATS a on ac pacil ins DER ge Acht hanhi Si gr Dh Bei sgoel pein his fe men RER LL ARENA PRATI AIR t Seegen H E PUW fast Balance Thasi 4 ERE Lag Horecuneni bire as cat eim x oc es T ai em eee ere 3 im Xin oats enis renna c m ie amies pp EE Ee Ee D 7 nad m T m 1000 o a o i nO i e ID MS SE seg aia f 2 ge Ak Presidi epee ie tt ete ee es LNW Ve d N T Tey T8 De tre bip Taak ara i arh qaad PETE al 45 im Wip Toad I sta l ap Linea RSA ie S Tide Crisi I pm Gist CAGES HT Fe MS 33 Oe 73 BT mn y ih ip Citti Copia I EZ 14453 3 EMI DP ama LEZE pum 11 dE des Sc Capes crasdinor eh MM 1 D 2 bas eaderel copciece RRE im Dose aaae a a aaa rer 7 Ve bie Vrersiareza Shi I LI fmi Carrer ran adr I via in Xp Vigcraemara bereit cii unc E Ve dal af E t Ak Dus Pn teh Ta HE P m Calc sheets HH and non HH 2 57 These sheets calculate the household non household inputs to the retail financial statements z i 3 AMID 2501114 240415 PR eC ee MARI DARI E e KZ 1 CALCULATIONS FOR HH BETA l oi Te Hs 1A APPLICA TH OU pg KIT T E n See I j l 1 j P l pe LJ E Ade ci Ger tmbirbunted Wi coe 11 38 ines nm xn Xi Hx 12 58 Xu NOM mes zU E Liu ara ein GN MAT au
31. E 3 8 6 2A 47 TA dA 8 EK 3 3 5x 3 62 2 Odp Return on Regulatory Equity RoRE 13 12 10 0x dA 6x TA TA 8 8A 6 TA 2 83 11 Nr 3dp RCF to Capex 4 333 1 848 5 207 9 347 10 523 3 374 4 700 3 305 3 443 3 011 4 268 4 819 4 561 64 65 Water 66 m dp Closing year end RCV 2 409 2 456 2 491 2 515 2 525 2 528 2 530 2 530 2 529 2 526 2 525 2 526 2 526 67 m dp Year average RCV 2 410 2 46 2 504 2 53 2 55 2 558 2 56 2 56 2 561 2 553 2 43 2 52 2 43 68 Odp Year average RCV Growth YoY 2 2 V LA ox 0x DA 0 0 ta 07 TA 53 Odp Pre tas return on RCV 10 9 8 47 6 gx LA Wx 12 12 3 Tt 4 70 Odp Post tax return on REW 8 74 62 47 5x TA a 9 EK 10 2 a 71 LI 72 Wastewater 73 m dp Closing year end RCV 1 662 1744 1810 1853 1883 1307 1323 1337 1350 1363 1883 1363 1363 74 fmOdp Year average RCV 1635 1724 1733 1857 1838 1322 1333 1354 1368 1381 1723 1326 1765 75 Odp Year average HCH Growth YoY 5x da 3 24 Ww TA TA L TA 3x ta ZA 76 Odp Pre tax return on RCV 10 8 7 2 2 6 6 TA 87 8 7 EK 77 Odp Post tax return on REW 11 9 7 1 2A 6 6 TA EK a 5 TA 78 72 Messi A D rr al lin ee wm Bel Se i eee E 59 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Wholesale Allowed Revenue 4 38 A detailed breakdown of wholesale Allowed Revenue is also provided by the cost elements and adjustments outlined earlier
32. Headroom check sheet Note The net margin calculated at the tariff level are not expressed as EBIT margins they are expressed as margin as a percentage of retail revenue only for the tariff only i e excluding the wholesale charge If the headroom check is insufficient to cover interest and tax the user can consider revisiting the inputs until they are satisfied that headroom is acceptable 3 14 The screenshot below shows the Headroom check sheet in the model Headroom check This sheet calculates the headroom as a percentage of net margin for retail 31 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Alzi ABC D E I J K B M N Ej T Lu V Wi Net margin headroom check 2 3 2015 16 2016 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 2015 20 2020 25 2015 25 4 E 5 Unit HEADROOM CHECK HH 6 oak m Odp HH net margin 1 1 1 1 1 1 1 1 2 2 4 7 Ti 8 m Odp HH working capital interest 4 m 14 18 22 24 26 30 33 37 69 151 213 3 m Odp HH tax charge 10 m Odp Headroom 4 10 13 17 21 23 25 28 32 35 65 143 208 Ti 12 Odp HH net margin percentage 5 5x 5x 5x 5 5x 5 5 5x 5 5x 5x 5x 13 7 Odp HH working capital interest percentage of net margin 766 EES 20314 2227 23514 2285 2126 2060 13847 18357 13277 20427 20057 14 Odp HH tax charge percentage of net margin A A A
33. September 2014 Water today water tomorrow setting price controls for 2015 20 PR14 financial model user guide www ofwat gov uk 1 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Ido 3 BACK MOWING DEER 3 Stuc ornis user Manu dl esere ieri rette 4 FAP VGA TT OC BEE 6 BE 6 TECAAIGAL e Pes MINI T m T UU Tm 6 Oe WO Te WO NP 6 Operating the model iii 7 Structure of the financial model nennen nennen nennen nnn nnns nn 8 mici E Ii sessionen TU TTE 11 NV PIOUS Sle SIG SS urinario du tdv bui ria rara sara eis 11 COMENCE SINS NT 12 output re por EE 13 Jer ebe Tr 13 MOU SNE EEN 14 Navigating the inputs real ebeet i 14 E D 19 Calculation sheets overview nennen nennen nnn nnns n ninh nnns nna ann nna nnns nnn 19 Wholesale calculation sheets sessi nnn nnn nnns 19 Retail calculation sheets nennen nnne nnne nnn nnn nnns nnns nnn nsns nnns 21 Price control eolvimng nenen 24 Standard reports model QUtputSs 27 LEE RR 28 Price e ele Ee silicati 29 Juttel ONT ME NEMO ITem 29 Retail price control net margin headroom check 30 Wholesale price control song 32 Stage 1 reset allowed revenues nnns sina nnn rnnt 36 Sele a eec al SSA ne REPRE NERCRT o 37 Stage 3 target rate of return iii 43 D Setting price controls for 2015 20 PR14 finan
34. This is shown at the screen shot below Note these are shown in 2012 13 prices A B C D E I J K E M N a E Q R E T u V Ww Executive Summary 2 3 E 2015 16 2 17 2017 18 2018 1 2019 2 2020 24 2021 22 2022 23 2023 24 2024 25 2015 20 2020 25 2015 25 33 Wholesale Allowed Revenue breakdown 34 35 m dp Water 96 fmOdp PAYG 14 15 15 15 16 16 T T T 18 75 85 153 37 m dp Equity Issuance Cost 38 fmOdp Return on Capital 43 53 35 122 148 174 200 225 251 276 477 1 127 1604 33 m dp Depreciation 53 72 86 100 115 124 128 131 134 137 431 654 1085 100 fmOdp Tax 24 31 37 44 59 63 65 55 56 58 196 297 493 101 m dp Allowed Revenues 140 186 233 281 338 377 409 428 459 489 1179 2 162 3 341 102 m dp Revenue solving adjustment 1138 1354 1563 17893 2 047 2 207 2 318 2 357 2 461 2 565 7 837 11 908 13 805 103 m dp Allowed Revenues post revenue solvi 1278 1540 1803 2 070 2 385 2 584 2 727 2 785 2 920 3 054 3 076 14 063 23 146 104 105 fmOdp Bankable allowed input 206 227 246 263 286 293 293 283 282 281 1227 1433 2 550 106 m dp Post financeability adjustments 0 12 2 2 2 2 12 2 0 0 20 18 37 107 m dp Allowed Revenues pre profiling 206 233 248 255 288 235 306 285 283 282 1247 1451 2 697 108 Em Ddp Profiling adjustment 103 fmOdp Profiled Allowed Revenues 206 239 248 266 288 295 306 286 283 282 1247 1451 2 697 110 fmOdp Capital contributions from connection charge
35. a single sheet holding all nominal terms inputs to the financial model including user specified inputs Note this contains information of retail and wholesale elements of the model Appointee Nominal contains calculations relating to wholesale and retail combined to derive the appointee financeability metrics in nominal prices Exec Summary contains price control outputs and specific information on Allowed Revenue bills income statement and financial indicators Note this contains information of retail and wholesale elements of the model Dashboard contains the calculations and outputs generated in solving the wholesale price control It shows appointee nominal financial indicators F Outputs a single output sheet which formats key model outputs so they can be uploaded to Fountain Setting price controls for 2015 20 PR14 financial model user guide September 2014 Output report sheets 2 22 Of the above sheets the following contain output information Reports are generated through the Reports function on the ribbon e HH Price limits e NHH Price limits e Net margin headroom check Headroom check e Appointee Financial Statements Appointee Fin Stats e Executive Summary e Wholesale Tax Reconciliation Tax Reconciliation Other sheets 2 23 2 24 2 29 2 26 In addition the following sheets also exist e Version Control manual capture of version control and associated change log
36. able 3 16 The optional wholesale price control solving functionality can be accessed through the PR14 Model ribbon menu File Home Insert Page Layout Formulas Data Review View PR14 Model bZ Be Reset Allowed Revenues 4 Adjust Dividends 7 Adjust Allowed Revenues mmm 1 x 2 Adjust Gearing B Adjust PAYG and Run Off B Overwrite Bankable Profiles Set Net Review Size Reports Margin Headroom 3 Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues f columns Retail Price Control Solving Data IL Il 3 17 The above interface guides the user through the following steps in the wholesale price control solving process Not all steps are mandatory and some can be skipped by the user For example the user can skip to reprofile allowed revenues e Reset Allowed Revenues e Adjust gearing e arget rate of return e Adjust dividends e Adjust PAYG and run off rates e Apply equity injection 32 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Adjust Allowed Revenues e Overwrite bankable profiles e Reprofile Allowed Revenues 3 18 The solving functions are controlled through the user interface The following diagram and subsequent paragraphs outline the process Overview of the wholesale price control solving process Stage 1 P m Stage 3 m m Stage 5 Reset Allowed mom TargetRate of PES Adjust PAYG and Revenues 8 Return Run Of
37. atan ant H H fa bei k L k a Le Per pared Caen eren ih mira Pally Sapi icase e e fin dii 21 007 mam z a Um P nz Canile Expenses KE 104203 ings HIE IR 138 m ja i ce veer eid ond addet At eat St gt i00 arl EI cm Vili d implied ep Ate Ai cca 15421 18423 haga d r k k pa p o Yir 3 pian Gre aaen AL coat eM ua IDEA E 2 a 4 Higi aj rm Vir d phj oi aa Ap coat s H HA XM Ki LIT DT TL r uj E nh F Price limits sheets HH and non HH 2 58 These sheets show the retail price control limits 22 Setting price controls for 2015 20 PR14 financial model user guide September 2014 LI HH Price Limits Severn Trent Wase Final Determination LI d B Foibe ali STE 17 FEE PLSD riali NEEN AE rasi SITI Puri M 2015 20 SOA i A Und 1 ALLOVAE de NUT LL D mdp Tou alegra Fab creta men i2 te DEN pang E EK sam irm sala EI tana laf dmn HIE E 3 CHANGE TACTORE 10 ti 248 Abee cold perio ENEE prs mani Viale csi LE ar e rn sm FEI EA GE zt na 338 188 3T GEI ET n E24 TT a 3158 Mar DAI Ir T Ap GEO HES Tt bd 7B 3B Ho 43 XN MI KOR t3 iig Bosi el parece PAREA pH ep ered Dal perce a 3 KE SEA atm Sai ar ar hat re bi 54 dd rad 3 1b ata TJ 14 E ide Ad ta Nit MT Eee D I OD Geier I UE Lhe HE ZI E UE Hm ny Th H mra ITF Headroom check 2 59 This sheet calculates the headroom as a percentage of net margin for
38. bles 3 Working capital e Current assets and liabilities 4 Other income and cash flow items e Other income e Other cash flow items 5 Tax e Corporation tax rate and opening creditor e Water e Wastewater e Deferred tax and other adjustments 6 Debt e Cash interest rates e Floating rate debt Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Fixed rate debt e Index linked debt e Preference shares e Interest receivable other 7 Equity e Equity e Dividends 8 Adjustment inputs e Stage counter e Run off rates and PAYG ratio e Gearing adjustment e Return on capital e Dividend adjustment e Equity injection e Allowed revenue adjustment e Bankable allowed input 9 Post financeability indicators e Water post financeability adjustments e Wastewater post financeability adjustments 10 Reprofiled Allowed Revenue grants and contributions e Reprofiled revenue e Capital Grants and contributions e 2014 allowed revenue for calculation of 1st period K 11 Customer projections to be used on Executive Summary sheet e Number of customers 12 Model parameters e Tolerance for solving algorithms e Iteration limits for solving algorithms e Default financial indicators for solving e Default financial indicator tolerances for solving Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Default financial
39. cial model user guide September 2014 SEE c 46 Stage 9 reprofile allowed revenues nnne nnne 46 Wiele unenee WEE 48 lee Ee Ree e 50 Lf E 50 SACRO eege 50 FRC OMS e ER EE UU E E A E EE E E E E 51 DSD ege EN 52 ze EUN E Um 57 Iyel incipere EE 61 Setting price controls for 2015 20 PR14 financial model user guide September 2014 All screenshots in this document are included for illustrative purposes only The numbers displayed are the result of test data and should not be relied upon They do not reflect and should not act as to a guide of any assumptions that Ofwat may make as part of setting price controls for 2015 20 This user guide is a nota policy statement and users are referred to Ofwat s Setting price controls for 2015 20 final methodology and expectations for companies business plans the methodology statement and associated documents for further information 1 Introduction Background 1 1 1 2 1 3 1 4 1 5 The PR14 financial model has three purposes e To determine the wholesale price controls e o calculate the revenues arising from the retail price controls e To help assess financeability of the appointee with the ability to refine the wholesale price control limits until target financeability indicators are satisfied For wholesale the financial model uses the costs of running the wholesale business to derive the revenue
40. custome Ls post Wa Ka D Ma Mi Wal PR kd x wa Ka ko E dip Aya age era bill stica 1520 pe ga EH ci ma m Tan Ta KI 24 Dip Bug Ae vu I rund ball peer WE gT Sere re ihe be eee 358 Tax ra 1 prx Enel 3T 38 CDen Pea deg i nane ger candy Ell TH Th Bn RH RET BS dba del art 35 Ze Die Bang vg aer cacy Ell arceri aE Sir ner p Bh 552 E quu Sex Eil 53 1 545c an ai cosa Blei dei ere Lig LI 1535 155 LSG aT La LHi Lo 140 az Kup BeH bg cambr bill gessi th res Ed ge gTa gna Shu Wo ge SEI Dei 5a 2x 43 ores ell ae bse ie Tuas i ai imde Sigle Pad nene H E LS Ki n 25 Fr 3a 5 a5 timido Ae Fri irc e L e e ab timido Torna Fetal eiTe H E E CA x z1 Fe za H x e Summary income statement for the appointee 4 34 The income statement for the appointee is provided in the Executive Summary report 4 35 The following screen shot shows the income statement section of the Executive Summary 58 Setting price controls for 2015 20 PR14 financial model user guide September 2014 E R Executive Summary 2 E a 2020 24 2021 22 2022 23 2023 24 2024 25 2015 20 2020 25 2015 25 Fas Income Statement Appointee 3 31 m dp Turnover 375 445 514 585 550 532 711 735 758 781 2 570 3 676 6 246 32 m dp Operating Profit 486 430 375 56 216 388 444 457 503 515 1 018 2 307 1283 33 m dp Profit before Tax 548 498 447 17 146 320 380 393 437 446 1 363
41. d Tax e F16 Initial Wholesale Price Controls Calculation e F17 Solving Wholesale Price Controls e F18 Dividends e F19 Financial Statement and Indicators 2 48 The calculations under each heading build up the financial projections for a company based on the assumptions in the input sheet for example from RCV Allowed Revenue working capital financing depreciation to tax 20 2 49 2 50 2 91 Setting price controls for 2015 20 PR14 financial model user guide September 2014 The service level calculation sheets water and wastewater calculate the service specific revenue requirement The initial revenue requirement is calculated under Phase 1 Subsequent phases overlay to facilitate additional calculations used in solving the price controls for each service e Phase 1 financial statements and indicators e Phase 2 run off rates and PAYG ratio e Phase 3 gearing adjustment e Phase 4 target rate of return e Phase 5 dividend adjustments e Phase 6 equity injection e Phase 7 Allowed Revenue adjustment e Phase 8 post financeability revenue adjustments e Phase 9 reprofile Allowed Revenue In the wholesale sheet the inputs and phases are built up cumulatively giving the financial projections for the overall wholesale business Retail calculation sheets 2 02 2 53 2 04 2 59 The following calculation sheets relate to the retail aspects of the model t
42. d debt If the user opts to make the tax adjustment any previous tax adjustments input in the Input nominal sheet sections 5 2 and 5 3 are overwritten The resulting tax charge will remain consistent with the pre adjustment tax charge Upon successfully adjusting the tax the user will be notified If the user does not opt to remove the existing tax adjustments then the index linked debt override is not accepted and the model reverts to its pre adjustment state 61 AT Ofwat Centre City Tower 7 Hill Street Birmingham B5 4UA Phone 0121 644 7500 Fax 0121 644 7533 Website www ofwat gov uk Email mailbox ofwat gsi gov uk September 2014 Crown copyright 2014 You may reuse this information excluding logos free of charge in any format or medium under the terms of the Open Government Licence v2 0 To view this licence visit http www nationalarchives gov uk doc open government licence version 2 or email psi nationalarchives gsi gov uk Where we have identified any third party copyright information you will need to obtain permission from the copyright holders concerned Any enquiries regarding this publication should be sent to us at mailbox ofwat gsi gov uk This document is also available from our website at www ofwat gov uk OGL
43. ded for company use in preparing business plans 3 66 For completeness the additional stages include e adjust dividends this contains the functionality to adjust dividends in the model to seek a set of financial indicators e adjust PAYG and run off rates this contains the functionality to override the base data PAYG ratios and RCV run off rates e apply equity injection this contains the functionality to apply an equity injection in any given year in the model e adjust Allowed Revenues this contains the functionality to adjust allowed revenues in the model to seek a set of financial indicators and e overwrite bankable profiles this contains the functionality to override the bankable allowed revenues determined during earlier price control solving stages Stage 9 reprofile Allowed Revenues 3 67 This step allows the user to reprofile Allowed Revenues after the application of post financeability adjustments by smoothing them such that their net present value NPV remains the same as the original profile 3 68 To do this click on the PR14 Model tab on the ribbon and click 9 Reprofile Allowed Revenues Home Insert Page Layout Formulas Data Review View PR14 Model B 4 age 1 Reset Allowed Revenues 4 Adjust Dividends T Adjust Allowed Revenues SC La aal SEN 2 Adjust Gearing D Adjust PAYG and Run Off B Overwrite Bankable Profiles FX Set Net Review Size Reports Margin Headroom 3 Targ
44. djust for inconsistent input data Companies wishing to override their non household default tariffs with total non household cost to serve and margin should enter this information on the adjustment sheet If the non household default tariffs are overridden the blended debtor days should also be entered e Inputs Real linked directly to the F_Inputs sheet and used to drive real calculations e Inputs Nominal inflated from the Inputs Real sheet and used to drive nominal calculations Navigating the Inputs Real sheet Overview of input sheet collapsed level 2 28 2 29 The Inputs Real sheet is divided into several sections according to the source of the data and the calculations that use it These are e regulated capital value RCV e fixed assets e working capital e other income and cash flow items e tax e debt e equity e adjustment inputs e post financeability adjustments e reprofiled Allowed Revenue grants and contributions e customer projections to be used on Executive Summary sheet and e model parameters Note that there is also a separate input sheet F Inputs The F Inputs sheet is where the base data should be populated Manual user inputs and inputs Setting price controls for 2015 20 PR14 financial model user guide September 2014 for the price control solving process will be in the input sheet not the F_Inputs sheet 2 30 In general different input items ne
45. e means the indicator will not be targeted e Default for financial indicator inversion for solving These inputs set the default of whether the financial indicator is to be inverted for the purpose of solving This is required for those indicators which may approach infinite values as they observe an asymptotic relationship when the denominator passes through zero e Default limits for constraining the financial indicators These inputs set the defaults range within which the financial indicators can be specified by the user during the price control solving user interface Standard reports model outputs 2 1 The output worksheets standard reports in the model are 2 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Dashboard e HH Price limits e NHH Price limits e Headroom check on retail net margins e Executive Summary and e Tax Reconciliation 2 2 The standard reports produced by the model have their own dedicated section later in this user guide section 4 F_Output sheet 2 3 The F Output captures specific model outputs and other line items so that they can be uploaded to Fountain 2 4 It also contains user inputs which have been specified in the financial model for example introduced as part of the price control solving process 2 5 Integration with Fountain is not a feature found within the financial model Fountain integration relies on a separat
46. e tool developed by Ofwat 28 Setting price controls for 2015 20 PR14 financial model user guide September 2014 This user guide is a nota policy statement and users are referred to Ofwat s methodology statement and associated documents for further information 3 Price control solving Introduction 3 1 3 2 3 3 3 4 This chapter outlines the steps that may be taken in the user interface to solve the wholesale price control Note that there is a separate process to set the price control for retail via the net margin assumption described later in this document The retail price controls should be set first before the wholesale price controls If the retail price control is set after the solving of the wholesale price control then the wholesale price control must be re solved Where inputs are altered after the wholesale price control solving has been started the model will notify the user with the dialogue box below The user has a choice of reversing the changes made to inputs or restarting the price control solving process Changes to Inputs Identified em dT Changes have been detected to the Input Nominal sheet during the Price Control Solving process This will reset the Wholesale Price Control Solving to Stage 1 Are you sure you wish to do this By selecting No these changes will be reversed 29 3 9 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Chan
47. ed Revenue is not reprofiled by the model The user can combine exclusion from the solving process with overwriting values for revenue by entering alternative revenue amounts in the input boxes for particular years If the user does this the relevant check boxes will be automatically ticked and revenue will be held constant for those years This functionality is not available in the dividend solving process due to the interrelationship between dividends across the years If the profile is forced in such a way that the NPV cannot be held constant the model will report that reprofiling revenues has not been completed underneath the Reprofile button The user can click Apply to save and apply the reprofiled Allowed Revenue profile to the model Clicking Cancel will exit the dialogue box and the model will remain unchanged Headroom check revisited 3 80 3 81 3 82 The headroom check is intended to apply to an efficient retailer in order to inform understanding of financeability of retail level margins As noted in section 2 68 after solving the wholesale price control the user must perform a manual Headroom check at the Headroom check sheet in the model This checks that the retail net margin specified by the user is sufficient to cover tax and interest Note that it is important to conduct this stage at whatever stage the Price Control Solving is terminated This may not be stage 9 depending on
48. ed cash interest cover ratio e Funds from operations debt e Retained cash flow debt e Gearing e Dividend cover e Regulatory equity retained earnings for the regulatory company 52 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e RCV Earnings Before Interest Tax Depreciation and Amortisation EBITDA e Retained Cash Flow RCF to capex 4 14 The following screen shot shows the financial indicators section of the dashboard 2012 13 2013 14 Cash Interest Cover Gearing Dividend Cover RCVIEBI RCF to Capex Allowed Revenue Adjusted cash interest cover ratio Funds from operations debt Retained cash flowldebt Regulatory equitylretained earnings for the regulatory company 2014 15 2015 16 2016 17 2017 18 2018 19 2019 20 2020 21 j npe I TT SO E AREE 35 9 J 3 730 111 833 153 359 4 383 3 137 12 32 5 550 194 426 453 435 657 8 330 235 294 A ANAM 38 432 3 4 10 031 278 807 ro ano 328 218 350 8 305 8 810 308 553 2021 22 2022 23 926 184 641 8 736 4 076 330 088 925 169 472 8 521 3 224 341 621 2023 24 925 154 785 8 331 2 703 360 439 4 15 Allowed Revenue breakdowns are provided by water and wastewater 4 16 The impact of the various optional user adjustments made as part of the solving process are set out by calculation phase as follows
49. ed debt had been made 6 Option B Set the movements in index linked debt to zero using the override lines 7 Option B Re run the gearing adjustment to reset the gearing to the required level keeping the inputs the same as in step 4 above 3 49 To adjust gearing click on the ribbon tab called PR14 Model and click on 2 Adjust Gearing Home Insert Page Layout Formulas Data Review View PR14 Model M E 4 Reset Allowed Revenues 4 Adjust Dividends 7 Adjust Allowed Revenues EEE mmm bz ical L i SEN 2 Adjust Gearing D Adjust PAYG and Run Off B Overwrite Bankable Profiles SIX Set Met Review Size Reports Margin Headroom 3 Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues Columns Retail i Price Control Solving Data 3 50 The relevant dialogue box appears showing the current and target gearing and allows the user to do the following e Input the target for gearing e Choose the target gearing approach at the drop down menu at the top of the box 41 3 51 Setting price controls for 2015 20 PR14 financial model user guide September 2014 using the base year gearing Is set such that the first year is set to equal to the target gearing or a five year average gearing is set such that average gearing for five years equals target gearing e Select to apply one of the following tax treatments allow interest movement to flow to financial statements while h
50. ed to be populated for different time periods The range of years that need to be populated for each item is indicated in the columns to the right of the input titles in the input worksheets 2 31 The following screenshot shows the input sheet collapsed at the overview level 2 32 The and buttons can be used to expand or collapse the input sheet from an overview of the section headings to the detailed input lines Indexation Units Actual Year 2015 2016 Real 2017 2018 2019 2020 2021 2022 2023 2024 2025 Model Parameters at Company Name at Company Type t S io N Final Determination cenario Name amp o0 00 4 0 On 4 5 fo EEEI II mt o 0 Retail ulig oar 183 184 238 239 258 259 285 286 348 349 422 423 462 463 580 581 606 607 627 628 638 33 eH E E HH WH DH H HH RH HH H HH Detailed view of input sheet expanded level 2 33 The following screenshot shows the Inputs Real sheet expanded at the detailed level 19 1213 A B c D E E G H N D P D R CH Input Data Real 2020 21 2021 22 2022 23 2023 24 2024 25 000 40 Ob GA ro le ale ale ale ale il r Ke ale EE E pos THE Ch P no P F 40 O wo Elli IR ro no ew ouo Indexation Units Actual Year INAK AB C D E F G H N P e R A CR Input Data Real Setting price controls for 2015 20 PR14 fi
51. et Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues columns n Retail Price Control Solving Data 3 69 The relevant dialogue box appears which allows the user to 46 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e view the current Real Bankable Allowed Revenues in the model greyed out in m e check boxes enable the user to select which periods to reprofile by AMP 2015 19 or 2020 24 e reprofile smooth Real Allowed Revenues such that their NPV remains the same as the original profile e include or exclude specific years form the solving process e enter or force specific Allowed Revenues in m and e reprofile Allowed Revenue by service Reprofile Allowed Revenues es Sn AMP s to Reprofile Service s to Reprofile Gamer Note the following profiles incorporate Bankable Revenues as 2015 2020 Water TUER input and any applicable Post Financeability adjustments 2020 2025 Waste Reprofiling Not Yet Complete Water 2015 16 2016 17 2017 18 2018 19 2019 70 202021 2021 77 2022 233 2023 24 2024 75 Hold allowed revenue iv n E E E B E m n Bankable allowed revenues i 280 046 290 926 295 028 298 198 299 113 299 800 300 230 Reprofiled allowed revenues 165 293 243 341 262 373 280 046 298 198 299 113 299 800 300 230 NPV 2015 2020 1 112 506m NPV 2020 2025 1 134 348m Wastewater 2015 16 2016
52. et margin NHH working capital interest NHH tax charge Headroom NHH aggregate net margin percentage NHH working capital interest percentage of net margin NHH tax charge percentage of net margin Headroom percentage of net margin NHH net margin tariff band 1 NHH net margin tariff band 2 NHH net margin tariff band 3 NHH net margin tariff band 4 NHH net margin tariff band 5 NHH net margin tariff band 6 NHH net margin tariff band 7 NHH net margin tariff band 8 MHH net margin tariff band 3 NHH net margin tariff band 10 NHH net margin tariff band 11 NHH net margin tariff band 12 NHH net margin tariff band 13 MHH net margin tariff band 14 MHH nat marain tariff hand 15 0 0 0 W TA A 227 78 3 642 7 733 4 799 2 297 7 425 5 546 3 966 3 140 1464 1835 1535 1 610 2 132 nan 23 0 0 10 A 22 78 3 946 9 254 11 904 6 625 17 927 13 413 11 718 7 409 3 448 4 465 3 763 4 073 1752 5 172 d oi 0 1 12x A 20 80 4 279 10 371 20 858 11 433 31 791 23 963 24 906 13 248 5 882 7 782 6 593 7 885 3 018 7 RN 13 kd 20 80 4 479 12 316 26 163 8 103 48 089 32 741 36 215 20 155 7 822 10 406 3 837 13 081 4 040 13 644 4222 0 1 15 A 20 80 4 705 13 997 36 959 5 433 77 423 46 479 48 304 31 288 10 973 14 794 15 338 20 676 5 651 21 466 1d dad 0 1 TA kd 20 80 4 928 15 600 51 752 12 824 105
53. ey calculation sheets in the model which relate to the wholesale aspects of the model these are listed in section 2 20 Setting price controls for 2015 20 PR14 financial model user guide September 2014 2 42 The calculation sheets contain calculation cells only and are protected 2 43 No user inputs are expected on these sheets 2 44 The water and wastewater sheets contain identical calculations using their respective inputs Water wastewater and appointee calculation sheets 2 45 The wholesale calculation sheets have a common structure as outlined in more detail in the sub sections below 2 46 Each of the wholesale calculation sheets outlined in section 2 20 align to the structure of the PR14 Rulebook where main headings correspond to the flowcharts in the rulebook 2 47 Note that any PR14 Rulebook headings missing from the list below relate to illustrative diagrams in the rulebook These do not correspond directly to a discrete area of model functionality thus not referenced in the model e F3 Retail Household Allowed Revenues e F4 Retail Non Household Allowed Revenue e F6 Wholesale Revenue Requirement e F7 Wholesale Regulatory Capital Value RCV e F8 Wholesale Notional Depreciation e F9 Wholesale Accounting Depreciation e F10 Wholesale Return on Capital e F11 Interest Financing Costs and Debt e F12 Working Capital e Fi3 Wholesale Taxation e F14 Capital Allowances e F15 Deferre
54. f Stage 6 Stage 7 Stage 8 Stage 9 Completion Financial Apply Equity Adjust Allowed Overwrite Bankable Reprofile Allowed Injection j Revenues Profiles Revenues Statements 33 3 19 3 20 Setting price controls for 2015 20 PR14 financial model user guide September 2014 The user workflow occurs in stages whereby a discrete function sits in each stage Note that the order of the calculations outlined in section 2 50 for each function may differ to workflow that the user experiences To help distinguish this difference the user workflow refers to stages and calculations are referred to by phases The table below explains the mapping of the workflow stages to the calculation phases in the model Workflow Calculation stage phase Reset Allowed Revenues Set initial revenues 1 1 Adjust gearing 2 3 Target rate of return 3 4 Adjust dividends 4 5 Adjust PAYG and run off rates 5 ER Apply equity injection 6 6 Adjust allowed revenues 7 7 Overwrite bankable profiles 8 8 Reprofile allowed revenues 9 9 Adjusting PAYG and run off rates is stage 5 in the model workflow This does not preclude the user from running other solving stages first However the user must skip or confirm required assumptions in earlier price control solving stages For calculation purposes any adjustments to the RCV for example via adjustment of the PAYG or run off rates must occur prior to performing a gearing adjustment as the gearing adjustment relie
55. ffects of subsequent stages are removed until they are returned to and applied If the user exits the form changes applied up to that point are retained At stage 5 the user is required to confirm the PAYG and run off rates for water and wastewater Note that if these rates differ to those used in previous stages then the user must return to stages 2 3 and 4 This is because changing PAYG or run off rates has implications for gearing rate of return and in turn dividends With reference to the diagram earlier on the price control solving process the following sub sections outline how the model s user interface guides the user through the steps available to help determine the wholesale price controls Note that the user workflow is described in stages whereas the model calculations are applied at the phases outlined at section 2 50 This reflects that there is a difference between the calculation flow and the user experience At some stages in the solving process there is the option to set model inputs by service water and wastewater If producing model outputs for a water only company WoC the user should select options to specify inputs at Water service level wherever available As will be described later if the input labelled Company type in the Input sheet at section 12 Model Parameters is set to WoC then the options for wholesale and or wastewater will be disabled in the dialogue boxes for stages 3 5 6 8 and
56. financial Target gearing statements while holding tax for allowed revenue constant Solving Not Yet Complete Do not hold tax for allowed revenue constant Blended interest rate on change in borrowings 9 2015 16 2016 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 3 52 Clicking Apply will use the selected gearing approach and target in the model with corresponding impact on the revenue requirements and price controls 3 53 Note Apply must be clicked if changing inputs to move to a notional structure in order for these changes to be implemented 3 54 Clicking Skip will skip this stage in the process and remove the effects of any changes made in the model 3 55 Clicking Cancel will exit the dialogue box and the model will remain unchanged Stage 3 target rate of return 3 56 This step allows the user to set the desired blended target rate of return for the weighted average cost of capital WACC applied on the RCV in determining the revenue requirement and price controls 3 57 This is a semi automated stage in that the user must choose the basis on which the target rate of return should be calculated The model then calculates the appropriate target rates of return by blending the input WACCs in line with the user specified choice 43 Setting price controls for 2015 20 PR14 financial model user guide September 2014 3 58 Todo this click on the ribbon tab called PR14 Model
57. ges to Inputs Identified Changes have been detected to the Input Nominal sheet during the WE Price Control Solving process This will reset the Wholesale Price Control Solving to Stage 1 Note Adjustments to PAYG and RCV Run Off Rates will NOT be reset this time As tax related to the index linked debt change needs to account for the adjusted PAYG Run Off Rates Reset to Stage 1 By selecting No these changes will be reversed The initial price controls for retail and wholesale are calculated automatically from data in F Inputs and user inputs Retail price control net margin headroom check 3 6 3 3 8 3 9 For retail Ofwat will assess retail margins proposed by companies on a pre tax basis EBIT In the business plans we have asked companies to submit margins based on their Retail revenues which is the same as total revenues This assessment is performed outside of the financial model The financial model uses household costs and non household costs and applies a net margin that is derived from the overall EBIT margin which covers tax and interest costs which in turn determines the retail price control Note that the wholesale and retail elements are combined in the appointee financial statements for the purpose of assessing financeability and determining price controls Retail net margin is specified at the retail inputs section of the F_Inputs sheet Should the user wish to overr
58. he model uses an event macro to detect when inputs to it are changed This check is performed when the user navigates away from deactivating the input sheets Because of this trying to edit the input sheets using a split screen or second view is not supported by the financial model The model has a button see below on the PR14 model ribbon which allows the user at any point in time or stage in the wholesale price control solving to review the retail headroom checks in the model Setting price controls for 2015 20 PR14 financial model user guide September 2014 Home Insert Page Layout Formulas Data Review View PR14 Model 4 Reset Allowed Revenues 4 Adjust Dividends 7 Adjust Allowed Revenues 2 Adjust Gearing D Adjust PAYG and Run Off B Overwrite Bankable Profiles SIA Size Reports 3 Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues Columns Price Control Solving Data Structure of the financial model 2 14 The model has four components e Retail e Wholesale e Combined sheets e Report sheets A schematic of the model can be found on page 10 2 15 The following sub sections outline these areas in more detail 2 16 Each sheet is colour coded to help the user distinguish groupings Note that these are different to the cell colour coding 2 1 Each sheet contains cells corresponding to the tab colour to help indicate the user location Other cells are coloured white for calculations or pe
59. hese are listed In section 2 19 The calculation sheets contain calculation cells only and are protected No user inputs are expected on these sheets The screen shots on the following pages relate to the retail calculation sheets outlined in section 2 19 Note that several of the sheets follow a similar structure so are not all shown below for example Calc HH and Calc NHH and Price Limits HH and NHH Retail Financial Stats Nominal sheet 2 06 This sheet calculates the nominal financial statements for retail 21 Setting price controls for 2015 20 PR14 financial model user guide September 2014 nET Ware Retail Financial Statements NT Ha 2 3 DHdda 29041 SE badi Mifid Ai SEN wenn niis Aiia Bee LA 5 PLD Pet Meta amp Low Arome 8 T im ep Vera Ra 243 ntn nao Tix Im mise id tia T TH laa Wa TI eva ene DEN PEE Ze Zb Those men ea a ia Tn ee To verte DO pen LEE Zeie on sa LB Ze dela rt ten spari al opal zen irta GE EE MEET Sn UR ges del pd ao Tie ad i E dre dp Dum aun Pri I 1 sr H im Deh Deet te ari PFs L TEX Ti Z i Am n a CN DB Ve At Dherbs TT DE REECH DECH a R i ke Dapp CC omg f EH eten tab a ae LE de Scho eee sepepnaan acl Ever di Eie k e kez X PT a TG d p LEAST 20 im Tdp edene cb redenta vient Bn Ee e Ier CT m dp Fro n batorg rte LE LO TET smo pea 24TH
60. i RH L i Ms mob Afowed Revenues pro profiting RH D D D E D Ma EmO Profiling ekbatrerd m El A 207 zh ER JH P4 P6 zw We 10 2070 i MI GnOdp Piofded Abov ed Revenues m e Mx a To YA a P4 eM lt w ws LD 207 i M mOdp Capra coneibunona from conneoton charges Ss LN i e es mc EU A DRM i mr m Od Final Allowed Hovenues E Zem 207 TB TUN Z2 zu zw zm IT LD LOTO SO ti End i L Average household bills 4 32 Information on average household bills is provided as follows e Average water only bill e Average wastewater only bill e Average combined bill e Respective growth rates 4 33 The following screen shot shows the household bills section of the Executive Summary Executive Summary FUE NEMPE NO 000 202122 Fer Fare 2022 24 21 dara ae Householhd Hill d Sa co Melee aliu tb aou dra patr art Kc Ed es 941 gon 751 TE ea GA E3 d e Ode k nnt lr dts B i curser ecw es Bis Ka I Ek 9h Wu BEN Bac 251 Gal Se ES 25 t Op wieder gie w ipii He Biere Gi Igor app pari CO ot ena 669 Gre 551 53b did T sl 350 3x ST x Lu ILI IE i Lid Ir Ia axe Tp E rs Dedi Mie Ska 33x Etras fic mai i2 zB zu tsp Hei al aloe eel eevee pam Camione tele inem ar ah 43 J ETI b ET rd m Cu X D de Fir biro ed einer feet fer buairte rioni Hi Y BEE REG var Rm KEE XS BERG ips Fi IISI n d i Ode Feis adir ed bite D ugitt pari Pv E sj p 33 va dp Feil alos ed SASA Pet
61. ide the base retail net margin this may be achieved in the Inout Nominal sheet Note that if the inputs are changed for the retail model for example costs revenue or margin then the wholesale price control solving process needs to 30 3 10 3 11 3 12 3 13 Setting price controls for 2015 20 PR14 financial model user guide September 2014 be updated by running the desired steps again The wholesale price control solving process is described from section 3 15 onwards Conversely if the wholesale price control is updated the headroom check needs to be carried out and net margin may require revisiting The user should visit the Headroom check sheet in order to perform a manual check that the selected level of nominal net margin is sufficient for example to cover tax and interest At the Headroom check sheet the following information is available for household and non household e Retail net margin calculated as a percentage of retail revenue inclusive of the wholesale charge e Retail interest is calculated as a percentage of net margin e Retail tax as a percentage of net margin plus interest receivable e Headroom is calculated as retail net margin less retail tax and interest on working capital e Headroom is then also calculated as a percentage of net margin plus interest receivable In addition to the above tariff level information for non household is available at the
62. iles Retail Price Control Solving 50 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Reports form 4 3 To display and change the format of the report sheets described above the user clicks the Reports button on the PR14 Model ribbon Home Insert Page Layout Formulas Data Review View PR14 Model 4 Reset Allowed Revenues 4 Adjust Dividends 7 Adjust Allowed Revenues 2 Adjust Gearing B Adjust PAYG and Run Off B Overwrite Bankable Profiles b Set Met Review Margin Headroom d Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues Retail Price Control Solving 4 4 This displays the following form Output Reports Cl 1 HH Price Limits 2 NHH Price Limits 3 Net margin headroom check 4 Appointee Financial Statements Nominal 5 Wholesale Tax Reconciliation Nominal Display End year 2024 25 v 5 year totals Rounding Values in s and total St KE Iw Show annual values Rounding Indicators Sages File Name Reports 20130924 1408 4 5 Auser should select all reports they wish to display from the Select Report list 4 6 The user can chose the period for the report and which values to display through the Display section of the form at least one of these options should be selected 91 d 4 8 4 9 4 10 4 11 Setting price controls for 2015 20 PR14 financial
63. in real terms Wastewater Nominal contains calculations relating to the wholesale wastewater aspects of a water company in nominal terms Wastewater Real AR contains calculations relating to the wholesale wastewater allowed revenue aspects of a water company in real terms Setting price controls for 2015 20 PR14 financial model user guide September 2014 Wholesale Nominal contains calculations relating to the combined wholesale services covers both water and wastewater services where applicable to a particular company in nominal terms Error Checks contains calculations to check the integrity of wholesale financial statements Tax reconciliation contains calculations to reconcile tax at wholesale and service levels Combined sheets 2 21 The following sheets contain information relating to both retail and wholesale as denoted by the following descriptions F Inputs a single input sheet for all model base data inputs Note this contains information of retail and wholesale elements of the model Adjustments a single input sheet that enables base data inputs to be overridden Note this worksheet is intended for Ofwat use only and if used should be populated prior to carrying out price solving stages Input Real a single sheet holding all real terms inputs to the financial model including user specified inputs Note this contains information of retail and wholesale elements of the model Input Nominal
64. indicator solving status e Default for financial indicator inversion for solving e Default limits for constraining the financial indicators F_Inputs 2 35 The inputs of the financial model will be sourced from separate Ofwat internal 2 36 2 3 2 38 models and the business plan templates BPTs Additional user specified assumptions are also required These are entered on the Input sheet or via the user interface Each input line which comes from a separate Ofwat internal model or the BPTs has a unique Fountain code to identify it User specified inputs will be entered in the input sheets nominal real not F_Inputs To help ensure these assumptions are captured they will be fed through to the F_Output sheet Note there is a separate client application developed by Ofwat to download data required for the F_Inputs sheet and upload information from the FE Output sheet to and from Fountain Calculation sheets overview Overview 2 39 2 40 This section of the user manual describes the structure of the calculation sheets only For further information on calculation methodology the reader is referred to the PR14 Rulebook which describes the basis on which the model has been developed As far as possible the calculation sheets follow the structure of the PR14 Rulebook where main headings cross reference to the flowcharts in the rulebook Wholesale calculation sheets 2 41 There are several k
65. io Confirm Backtrack of Scenano You are about to revert the scenario to a previous stage di This operation cannot be undone and any changes made during the intervening stages will be lost Are you sure you wish to continue 3 33 36 Setting price controls for 2015 20 PR14 financial model user guide September 2014 3 34 If you click No the dialogue box closes and the model remains unchanged Confirm Backtrack of Scenario m You are about to revert the scenario to a previous stage You are resetting Allowed Revenues This will include a reset of adjustments to PAYG Run Off Rates Are you sure you wish to continue Stage 2 adjust gearing 3 38 This step allows the user to choose the target for gearing net debt RCV The user may target the opening gearing in the base year or the average over the first five years 3 39 This is a semi automated stage in that the user must determine the target gearing and the gearing approach desired The model then calculates the appropriate movements in net debt and reserves that will satisfy the user chosen gearing level 3 3 40 3 41 3 42 3 43 3 44 Setting price controls for 2015 20 PR14 financial model user guide September 2014 If the user is intending to put the company onto a fully notional basis which reflects Ofwat s view of both gearing for an efficient company and the cost of debt the following steps need to be carried out
66. l Employed RoCE 3 2 7 8 6 3 1 6 3 8 6 7 TO 8 37 3 1 3 3 Return on Regulatory Equity RoRE 13 7 12 3 10 8 0 1 2 7 5 9 6 8 7 3 3 0 3 0 378 055 E iO ANDOL O Setting price controls for 2015 20 PR14 financial model user guide September 2014 C D E E G H J K E M N O P Q H Dasbboard Year 2012 13 2013 14 2014 15 2015 16 2016 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 Initial Allowed Revenue CE a ig Water Real m 3dp Initial allowed revenue 111 833 153 359 194 426 235 294 278 807 308 559 330 088 341 621 360 439 378 055 Real m 3dp PAYG amp run off rates 0 000 0 000 0 000 Real m 3dp Gearing adjustment 6 451 6 533 6 278 6 044 7 474 7 272 6 944 5 027 4 910 4 796 Real m 3dp Target rates of return 100 211 79 775 57 374 33 936 14 750 8 321 29 922 53 304 73 115 92 040 Real m 3dp Dividend adjustments Real m 3dp Equity injection Real m 3dp Allowed revenue adjustment Real m 3dp Allowed revenue before post financeability revenue adjustments 205 593 226 601 245 523 263 186 266 083 292 967 293 221 293 290 282 414 281 213 Real m3dp Create bankable allowed input Real m 3dp Allowed revenue after post financeability revenue adjustments 205 313 238 321 247 843 265 506 298 403 295 287 305 541 285 510 282 134 281533 Real m 3dp Re profiled allowed revenue 205 31
67. ments sheet In most cases the adjustment should be equal to the opening gearing value in the actual 39 Setting price controls for 2015 20 PR14 financial model user guide September 2014 3 48 The modeller should follow the appropriate steps depending on whether the company s actual gearing is above or below the notional level to ensure that the model calculates the tax value to be included in the allowed revenue correctly If the company s actual gearing Appointee Nominal Phase 1 Financial Indicators is below the notional level then follow steps 1 to 3 Option A If the company s actual gearing is above the notional gearing then follow steps 4 to 6 Option B Option A Actual gearing is below the notional level 1 Option A Set the movements in index linked debt to zero and the apportionments to blank using the override lines Run the Gearing IBS Teo on the PR14 Toolbar to set the Sculls to EES The t earing approa opted i rE Ene that you d the jan Do not hold tax for allowed revenue constant 2 Option A If you wish to set index linked debt as a specific proportion of opening net debt then the following additional steps now need to be followed Having run the Gearing adjustment above identify the opening net debt figure Appointee Nominal Phase 3 Financial Indicators and calculate the target value for opening index linked debt Notional 9e x opening net debt Enter this am
68. model uses an event macro to detect when inputs to it are changed as this triggers the recalculation of the retail elements of the model and to assess whether the changes necessitate the user to revisit any price control solving stages The events are triggered on navigating away from deactivating the input sheets Operating the model 2 2 8 2 9 2 10 2 11 2 12 2 13 The user may wish to determine wholesale price controls through reviewing their impact on the appointee financial ratios Any changes to the retail inputs will affect the retail price controls and the appointee financial ratios It is expected that the retail price controls are calculated prior to solving for the wholesale price controls Once the retail net margins are set the user should then proceed to set the wholesale price controls Setting retail price controls after setting the wholesale price controls could lead to wholesale price controls that do not reflect the effects of the retail price controls on the appointee business Any changes to the retail price controls in the model will result in the user being notified and forced to re run any wholesale price control solving that has taken place Any changes to the inputs to the model will result in the user being notified the retail calculations will then be automatically re run and the user will be forced to re run any wholesale price control solving that has taken place To facilitate this t
69. nancial model user guide September 2014 2015 2016 Real 2017 2018 2019 2020 2021 2022 2023 2024 2025 Model Parameters Text Company Name Test Company Type Test Scenario Name Final Determination 0 Retail 0 RETAIL INPUTS 0 1 WHOLESALE CHARGES 0 2 PAYMENT TERMS 0 3 NHH ADYANCE RECEIPTS WEIGHTING 0 4 FIXED ASSETS 0 5 RETAIL NET MARGIN 3 1 Current assets and liabilities The sub headings in the expanded view shown above are listed below Note that all of the following inputs relate to the wholesale aspects of the model with the exception of the retail specific inputs shown at the top of this sheet Wholesale inputs 0 Retail e Wholesale charge e Household apportionment of wholesale charge e Retail household creditor payment terms e Retail non household creditor payment terms e Proportion of debtors to revenue NHH advance receipts weighting e Unmeasured e Measured Fixed assets e Asset Life whole years Retail Net Margin Override e Household net margin 16 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Non household net margin by tariff band Retail Dividend e Percentage distributed 1 RCV e Totex e RCV depreciation e Cost of capital water e Cost of capital wastewater e Discount rate e Other adjustments to RCV e Other adjustments to revenue requirement 2 Fixed assets e Water e Wastewater e Investments and intangi
70. net margin to derive the retail HH price control Calc NHH this sheet uses non household costs and applies a net margin to derive the retail NHH price control Price limits HH this sheet reports the HH price controls Price limits NHH this sheet reports the NHH price controls Headroom check this sheet enables the user to carry out a manual check that the applied net margin is sufficient to cover interest and tax for the control HH Fin Stats this sheet provides the retail income statement balance sheet and cash flow for household including calculation of the retail debtor wholesale creditor measured income accrual advance receipts and capex creditor together with their corresponding cashflows NHH Fin Stats this sheet is the same as the HH Fin Stats sheet but for non household Retail Fin Stats this is a consolidation of HH Fin Stats and NHH Fin otats using a layout consistent with that used at the Appointee level Retail Phase Outputs contains retail information by Phase phases are overlays of financial information to facilitate calculations used in solving the price controls for each service discussed later Wholesale sheets 2 20 The following sheets relate to wholesale only Water Nominal contains calculations relating to the wholesale water aspects of a water company in nominal terms Water Real AR contains calculations relating to the wholesale water allowed revenue aspects of a water company
71. olding tax for Allowed Revenue constant note this is an actual structure with interest This is the default option do not allow interest movement to flow to financial statements while holding tax for Allowed Revenue constant note this is an actual structure without interest or do not hold tax for Allowed Revenue constant if selecting this option the tax structure would be fully notional that is not based on an actual company s position Note that neither tax nor interest are held constant in this option e Input the percentage interest rate to be applied to the additional borrowing as part of the gearing adjustment The above tax and interest options are available because gearing adjustments move gearing and financing away from an actual structure based on company business plans to a notional structure This implies that the new corresponding structures are also notional The user can therefore elect to apply one of the above options to hold tax constant and allow or prevent interest movements flowing to financial statements 42 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Gearing and Dividend Adjustment Lo mes Interest A Tax Options Target gearing approach Allow interest movement to flow to financial statements while holding tax for allowed revenue Current gearing 96 constant default e 55 760 Do not allow interest movement to flow to
72. orts outputs available in the model Setting price controls for 2015 20 PR14 financial model user guide September 2014 This user guide is a nota policy statement and users are referred to Ofwat s methodology statement and associated documents for further information 2 Financial model Users 2 1 The model has been built for and is intended to be used primarily by Ofwat staff and management It is therefore assumed that the users of the model are familiar with the reporting format being used have a good understanding of this document and understand the assumptions inherent in the model with reference to the PR14 Rulebook Technical requirements 2 2 The model is designed to run on a Windows 7 operating system and is written in the English language version of Microsoft Excel 2010 It makes use of the standard add ins Analysis Toolpak and Analysis Toolpak VBA 2 3 The model is built in 32 bit Excel 2010 It is not guaranteed that the model will work in other versions of Excel Windows or other operating systems 2 4 In order to run the model properly we anticipate that the minimum technical requirements will be e Intel dual core 2 5 GHz processor or better e 4GB RAM or better and e 1 GB of free hard disk space Opening the model 2 5 Open the model file in a stand alone session of Excel and ensure macros are enabled 2 6 Setting price controls for 2015 20 PR14 financial model user guide September 2014 The
73. ount in the override for opening net debt Press Yes when asked to clear the Other Tax Adjustments Press No when asked to hold the tax constant 3 Option A Re run the gearing adjustment to reset the gearing to the required level keeping the inputs the same as in step 1 above 40 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Option B Actual gearing is above the notional level 4 Option B Run the Gearing Adjustment on the PR14 Toolbar to set the gearing to the notional level The target gearing approach adopted is base year and the blended interest rate on change in borrowings should reflect the notional cost of debt nominal Ensure that you select the default option Allow interest movement to flow to financial statements while holding the tax for allowed revenue constant 5 Option B If you wish to set index linked debt as a specific proportion of opening net debt then the following additional steps now need to be followed Having run the Gearing adjustment above identify the opening net debt figure Appointee Nominal Phase 3 Financial Indicators and calculate the target value for opening index linked debt Notional x opening net debt Enter this amount in the override for opening net debt Press Yes when asked to clear the Other Tax Adjustments Press Yes when asked to hold the tax constant This will hold the tax position as if no adjustment to index link
74. r cash adjustment of gearing e Tolerance on dividends e Tolerance on allowed revenue e Tolerance on NPV lf the algorithm suggests an adjustment of less than the tolerances specified here then the adjustment is considered too small an adjustment and the algorithm concludes solving e Newton Raphson iteration limit 26 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Newton Raphson iteration limit for revenue reprofiling These limits control how many iterations the Newton Raphson algorithm can perform Greater iterations when coupled with smaller variable tolerances will lead to solutions which are technically more accurate Increasing the number of iterations may slow down the performance of the model It is entirely possible that the model may reach a solution within the specified iteration limits in which case not all iterations will be performed e Default financial indicators for solving These inputs contain the default values for each financial indicator that the modelling is seeking to solve to e Default financial indicator tolerances for solving These inputs contain the default tolerances for each value for each financial indicator that the modelling is seeking to solve within e Default financial indicator solving status These inputs set the default for whether each financial indicator is to be targeted True means that the indicator is to be targeted while fals
75. r the key as shown in the model 2 18 The model key is as follows Cell style Definition Manual user input may be also be populated by form Input not possible Unique formula Unique input for modeller use only Error check failed UN Setting price controls for 2015 20 PR14 financial model user guide Sheet tab colour Wd September 2014 Error check passed Definition Manual user input may be also be populated by form Separating sheet Report Error check sheet Retail calculations Retail outputs Wholesale water calculations Wholesale wastewater calculations Wholesale calculations Appointee calculations Setting price controls for 2015 20 PR14 financial model user guide September 2014 The model diagram below shows the main data flows throughout the PR14 financial model It is available in the model to guide navigation np Index linked debt D macro Calc HH Calc NHH y LC Wastewater Real AR HH Fin Stats NHH Fin Stats Retail Fin Stats Tax Reconciliation Retail Phase Outputs S Price Limits HH Price Limite NIE 10 F Outputs Error Checks Dashboard Appointee Fin Stats Setting price controls for 2015 20 PR14 financial model user guide September 2014 Retail sheets 2 19 The following worksheets relate specifically to the retail components of the model Calc HH this sheet uses household costs and applies a
76. requirement and consequently expected turnover in each year The wholesale revenue requirement identifies the total amount of revenue that needs to be raised from customers in each year in order for the company to finance its functions For retail the financial model applies net margin on expenditure to cover tax and interest costs to determine the retail revenue requirement The financial model reflects the methodology for the 2014 review as set out in Setting price controls for 2015 20 final methodology and expectations for companies business plans the methodology statement The financial model for the 2014 price review has changed since the 2009 price review to reflect the following key changes although the list is not exhaustive 1 6 1 7 1 8 1 9 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Separation of the single price control into four price controls across wholesale and retail e PR14 incentive mechanisms e Retirement of the PROY capital expenditure capex incentive scheme CIS mechanism e Discontinuation of the Construction Output Price Index COPI for cost recovery e Simplified tax approach e Depreciation using a run off rate schedule e Move to International Financial Reporting Standards IFRS from UK Generally Accepted Accounting Principles GAAP and harmonisation with IFRS e Removal of the June return reporting requirement The model use
77. retail 2 60 The headroom check non household calculates the net margin by tariff band using a margin on retail service expenditure approach This is a known limitation of the model and may be addressed in future to reflect an overall EBIT margin 2 61 The aggregate non household net margin is calculated as an overall EBIT margin DODO ABC E Net margin headroom check 2015 16 2016 17 2017 18 2018 19 2019 20 2020 21 2021 22 2022 23 2023 24 2024 25 2015 20 2020 25 2015 25 Unit HEADROOM CHECK HH m Odp HH net margin 1 1 1 1 1 1 1 1 2 2 4 7 11 m Odp HH working capital interest 4 11 14 18 22 24 26 30 33 37 68 151 213 m Ddp HH tax charge m Odp Headroom 4 10 13 17 21 23 25 29 32 35 65 143 208 Odp HH net margin percentage 5x 5x 5x 5x 5x 5x 5x 5x 5x 5x 5x 5x 5x 7 Odp HH working capital interest percentage of net margin 766 1792 2031 22214 23514 22854 21267 20507 13847 18357 19277 20427 20057 7 Odp HH tas charge percentage of net margin ka ka ka A ka A bd ka A ka ka A Ddp Headroom percentage of net margin 666 692 13314 21272 22514 21854 2026 19602 18847 1795 1827 1942 1905 HEADROOM CHECK NHH m Odp m Odp m Odp m Odp x Odp 7 Odp 7 Odp Odp Odp dp Odp Odp dp dp Odp dp Odp Odp dp Odp Odp Odp Nda NHH aggregate n
78. s VBA macros for price control solving and a user interface to guide the user Note that the running of a VBA macro cannot be reversed via the Excel Undo function The inputs of the financial model will be sourced in part from separate Ofwat internal models and the business plan templates BPTs input directly by the user into the financial model and in part from user specified assumptions The key outputs of the financial model will be uploaded to Fountain via the F_Outputs worksheet in the model The calculations in the financial model are applicable to all companies The financial model has been developed in line with the PR14 Financial Model Rulebook PR14 Rulebook which in turn has been developed in line with the methodology statement Structure of this user manual 1 11 This user manual provides guidance on the macro enabled version of the Excel financial model It covers Fountain is an internal Ofwat system that facilitates the integration of the business plan templates and models in the Ofwat modelling suite Setting price controls for 2015 20 PR14 financial model user guide September 2014 an introduction to this user guide covering the background and structure to the financial model an overview of the financial model and its inputs calculations and outputs a dedicated section covering the price control solving process used to inform price controls and a dedicated section covering the standard rep
79. s on the calculation of RCV This is why the PAYG and run off rate adjustments occur at calculation phase 2 There is a user interface for each stage in the price control On each user interface e the Apply button applies the user input parameters as shown on the solving user interface e the Cancel button reverts the user back to the model and reverts they change you made e if you press ESC or click the form close button X it does the same as the Cancel button 34 3 21 3 22 3 23 3 24 3 25 3 26 3 2 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e the Skip Remove Effects button allows you to continue to the next stage it removes the impact of the skipped stage and e you cannot skip a phase and retain the effects of that stage unless you click Apply The user begins at stage 1 and progresses through each stage sequentially as per the guidelines detailed in later sub sections of this document Subsequent stages will be locked or greyed out in the user form unless the previous stage has been addressed It is possible to skip stages 2 4 6 and 7 by clicking the Skip button which means the relevant step will not be applied To complete the solving process other stages must be addressed by either accepting or changing the values currently used in the model which are shown in the user form If the user reverts to an earlier stage the e
80. sale charge takes into account any change in retail net margin which impacts appointee financeability The user will be prompted for this as per the screenshot below Changes to Inputs Identified LA a Changes have been detected to the Input Nominal sheet during the SOF Price Control Solving process This will reset the Wholesale Price Control Solving to Stage 1 Are you sure you wish to do this By selecting No these changes will be reversed 29 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Note Changes to the Adjustment sheet do not automatically force the user to re solve the price controls However should an adjustment be made the user should manually re solve the price controls by Resetting Allowed Revenues using the button on the price control solving ribbon Changes to Inputs Identified Va Changes have been detected to the Input Nominal sheet during the WS Price Control Solving process This will reset the Wholesale Price Control Solving to Stage 1 Note Adjustments to PAYG and RCV Run Off Rates will NOT be reset this time As tax related to the index linked debt change needs to account for the adjusted PAYG Run Off Rates Reset to Stage 1 By selecting No these changes will be reversed 2 0 The default parameters for the price control solving are set in the Input Nominal sheet They include Tolerance for solving algorithms e Tolerance fo
81. ser will enter data into the By service Water section only The disabling described above in fact applies to all stages in the price control solving process where service level options and data entry are available that is stages 3 5 6 8 and 9 If the company type is set to WoC then it will only be possible to enter data at the water service level and anything that applies to waste and or wholesale will be disabled and greyed out An example of this is shown below but is not shown in this guide for every relevant stage in the price control solving process Select Return on Capital c lies Wholesale By service Water By service Wastewater e Annual blended cost of capital default A f Average blended cost of capital 5 years E f Average blended cost of capital 1 4 5 5 45 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Stages 4 8 3 65 In addition to the functionality to adjust gearing to a notional capital structure to select a target rate of return WACC approach and to reprofile allowed revenues the financial model includes additional functionality stages that may be used at Ofwat s discretion as part of setting price limits for PR14 The additional functionality does not set or imply an approach to policy Ofwat s approach to financeability is set out in the methodology statement This user guide does not discuss these areas of functionality as they are not inten
82. st rate equal to Ofwat s view of the notional cost of debt nominal This input will also override the retail working capital financing rates for both household and non household 4 Set the interest rates for existing and new fixed rate debt so that they equal Ofwat s view of the notional cost of debt nominal and the interest rates for existing and new index linked debt so that it is in equal to Ofwat s view of the notional cost of debt real Under the notional capital structure it is assumed that all debt including any bank balances bears interest at a rate that is consistent with the cost of debt embedded in the notional WACC To enable this to flow through the model correctly it is assumed that all debt including preference shares is either categorised as fixed rate debt on which a nominal interest rate is changed or 38 3 45 3 46 Setting price controls for 2015 20 PR14 financial model user guide September 2014 index linked debt on which a real interest rate is charged and where the capital element increases annually by inflation Floating rate debt and the associated interest is set to zero as the model uses an input value for the interest on the debt rather than recalculating it from first principles Applying the notional capital structure di 6 10 Set the floating rate debt interest to zero Override the opening fixed rate debt to the sum of the opening balances for fixed and floating rate debt and preference
83. stewater and appointee financial indicators It tracks the determination of the wholesale price controls from initial to bankable HH Price limits this report displays the retail HH price controls NHH Price limits NHH this report displays the retail NHH price controls Net margin headroom check this report enables the user to carry out a manual check that the applied retail net margin is sufficient to cover interest and tax for the control Exec Summary contains price control outputs and specific information for wholesale and retail for example on Allowed Revenue bills income statement and financial indicators Wholesale Tax reconciliation this report contains calculations to reconcile tax at wholesale and service levels F_Outputs a single output sheet that formats key model outputs so they can be uploaded to Fountain and Appointee Fin Stats a full set of nominal financial statements at the appointee level Size columns 4 2 Set Met Review Margin Headroom d Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues To autosize the columns on the active sheet to fit the content of the cells the user can click the size column button This action may not be undone Home Insert Page Layout Formulas Data Review View PR14 Model b 4 WW Reset Allowed Revenues 4 Adjust Dividends T Adjust Allowed Revenues LA SS vi 2 Adjust Gearing 5 Adjust PAYG and Run Off B Overwrite Bankable Prof
84. tal this will set target return on capital to equal the average across both services for all years that is 2015 16 to 2024 25 average blended cost of capital five years this will set target return on capital using five year average across both services and average blended cost of capital 1 4 5 o this will set return on capital in the first year to equal that of the first year the average across both services for the next four years and o the average across both services for the next five years thereafter en 3 61 3 62 3 63 3 64 Setting price controls for 2015 20 PR14 financial model user guide September 2014 e Service level the same options as wholesale can also be specified for each service that is setting different target returns on capital for water and wastewater e lf the user selects options in the service level water and wastewater sections of the dialogue box then assumptions will be applied at service level Once the desired option s has been selected click Apply to apply this choice in the model Clicking Cancel will exit the dialogue box and the model will remain unchanged As noted earlier if the input labelled Company type in the Input Nominal sheet at section 12 Model Parameters is set to WoC water only company then the options at wholesale and wastewater level will be disabled in the dialogue box as shown at the diagram below In this case the u
85. the user preferences There is a button on the PR14 ribbon which allows the user to go to the headroom checks which is the Review Headroom button 48 Setting price controls for 2015 20 PR14 financial model user guide September 2014 Insert Page Layout Formulas Data Review View PR14 Model 4 Reset Allowed Revenues 4 Adjust Dividends T Adjust Allowed Revenues 2 Adjust Gearing D Adjust PAYG and Run Off B Overwrite Bankable Profiles FX Size Reports 3 Target Rate of Return 6 Apply Equity Injection 9 Reprofile Allowed Revenues Columns Price Control Solving Data 3 83 f the user is not satisfied with the headroom check there is a button on the PR14 ribbon which takes the user to the Input Nominal sheet whereby the net margin assumptions can be altered 3 84 Ifthe retail assumptions are changed on the input sheet the wholesale price controls must be re solved The user will be alerted to this by a message box and asked to reset the wholesale initial allowed revenues 49 Setting price controls for 2015 20 PR14 financial model user guide September 2014 This user guide is a nota policy statement and users are referred to Ofwat s methodology statement and associated documents for further information 4 Standard reports Introduction 4 1 This chapter outlines the main outputs available in the model The main output worksheets in the model are Dashboard contains the model outputs for water wa
86. to reprofile the Allowed Revenue and calculated K factors by service 4 2 This step smooths revenues over a user defined time period such that the NPV of the revenue stream is maintained 4 28 The following screen shot shows the reprofiling section of the dashboard 56 Setting price controls for 2015 20 PR14 financial model user guide September 2014 348 Real m 3dp 349 Real m 3dp 350 351 352 Real m 3dp 353 Real m 3dp 354 Real m 3dp 355 356 Real m 3dp 357 Real m 3dp eal m 3dp m 3dp eal m 3dp co e Co mmm m v 5 year NPY before re profiling 2nd 5 year NPY before re profiling Phase 3 Re profiled allowed revenue ater 5 year NPY after re profiling 2nd 5 year NPY after re profiling 368 Final allowed revenue 369 Real m 3dp Water 370 Real m 3dp Wastewater 371 Real m 3dp Appointee 372 373 k 374 x 2dp Wat 1375 x 2dp Wastew at Executive Summary 2012 13 2013 14 2014 15 2015 16 2016 17 2018 19 2020 21 2021 22 2022 23 2023 24 2024 25 1 936 303 2 065 972 205 913 238 321 247 843 265 506 288 403 295 287 305 541 285 610 282 734 281 539 73 842 168 451 198 866 223 484 240 306 245 338 245 352 257 091 257 944 258 096 285 755 407 372 446 703 434 330 529 303 542 225 551 493 542 701 540 678 539 635 1 936 303 2 065 972 1 000 1 000 235 287 305 541 285 610 246 938 245 952 257 091 542 225 551 493 542 701 282 734 251 344 540 678 2815

Financial model user guide

Contents

Download Pdf Manuals

Related Search

Related Contents