{"text_format":"1","workbook":"xlsx","n_sheets":16,"sheets":["Guidance","Process Map","Wholesale Tariff","Water Avoidable Costs","Sewerage Avoidable Costs","Long Run Avoidable Costs","Asset lives","York tariffs","Bidding Costs","Wholesale charges","Scientific services","Metering costs","Water costs","Leakage costs","Wastewater costs","Business overhead"],"not_rendered":{"charts":1,"images":28,"macros":1,"external_links":0,"defined_names":1,"comments":6,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Guidance","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Process Map","index":1,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Wholesale Tariff","index":2,"n_rows":87,"n_cols":16,"rows_kept":87,"cut":false,"hidden":false,"merged":[[86,6,86,7],[86,3,86,5],[85,3,85,5],[67,6,67,7],[76,6,76,7],[68,6,68,7],[77,6,77,7],[69,6,69,7],[78,6,78,7],[78,3,78,5],[67,3,67,5],[68,3,68,5],[69,3,69,5],[76,3,76,5],[77,3,77,5],[1,1,1,13],[85,6,85,7],[70,3,70,5],[71,3,71,5],[72,3,72,5],[70,6,70,7],[71,6,71,7],[72,6,72,7],[79,3,79,5],[80,3,80,5],[81,3,81,5],[79,6,79,7],[80,6,80,7],[81,6,81,7]]}
[]
[null,"Household and NonHousehold Wholesale Tariff"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Colour codes:","user input values"]
[null,1,"NAV applicant details",null,null,null,null,null,null,null,null,null,null,null,null,"calculated values"]
[null,null,"Contact Name"]
[null,null,"Company"]
[null,null,"Address"]
[]
[]
[null,null,"Postcode"]
[null,null,"Telephone Number"]
[null,null,"Email"]
[]
[]
[null,2,"Development location details"]
[null,null,"Site address"]
[null,null,"Postcode"]
[]
[]
[null,null,"Is the site within the York area?*","No"]
[null,null,"Application reference number (if known)"]
[null,null,"* Please see tab 'York Tariffs' for maps and  list of postcodes"]
[]
[null,3,"Development Characteristics"]
[null,null,"Bulk Water Supply:","Yes"]
[null,null,"Bulk Wastewater Supply:","Yes"]
[]
[null,null,"Total mains network:",null,"km"]
[null,null,"Total sewers network:",null,"km"]
[]
[null,null,"Number of HH properties",null,"nr"]
[null,null,"Number of NHH properties",null,"nr"]
[null,null,"Total Number of points of connection required",{"$f":"IFERROR(SUM($D$31:$D$32), \"ERROR\")","v":0},"nr"]
[]
[]
[null,null,"Bulk Water Supply"]
[null,null,"Annual HH water demand",130,"m3/prop/yr"]
[null,null,"Daily HH water demand",{"$f":"$D$37/365","v":0.35616438356164382},"m3/prop/d"]
[]
[null,null,"Annual NHH water demand",null,"m3/prop/yr"]
[null,null,"Daily NHH water demand",{"$f":"D40/365","v":0},"m3/prop/d"]
[]
[null,null,"Bulk Wastewater Discharge",null," *95% sewerage rate applied or not"]
[null,null,"Annual HH wastewater discharge",null,"m3/prop/yr"]
[null,null,"Daily HH wastewater discharge",{"$f":"IFERROR(D44/365,\"\")","v":0},"m3/prop/d"]
[]
[null,null,"Annual NHH wastewater discharge",0,"m3/prop/yr"]
[null,null,"Daily NHH wastewater discharge",{"$f":"IFERROR(D47/365,\"\")","v":0},"m3/prop/d"]
[]
[null,null,"Starting HH Tariffs 2020/21"]
[null,null,"Fixed water charge",{"$f":"IF($D$25=\"Yes\",IF($D$20=\"Yes\",'Wholesale charges'!$D$28,'Wholesale charges'!$D$13),\"\")","v":9.89},"£","York Tariff"]
[null,null,"Fixed wastewater charge",{"$f":"IF($D$26=\"Yes\",'Wholesale charges'!E13,\"\")","v":0},"£"]
[null,null,"Volumetric water charge",{"$f":"IF($D$25=\"Yes\",IF($D$20=\"Yes\",'Wholesale charges'!$D$29,'Wholesale charges'!$D$14),\"\")","v":138.85},"p/m3"]
[null,null,"Volumetric wastewater charge",{"$f":"IF($D$26=\"Yes\",'Wholesale charges'!E14,\"\")","v":179.32},"p/m3"]
[]
[null,null,"Starting NHH Tariffs 2020/21",{"$f":"CONVERT($D$40,\"m^3\",\"l\")*10^-6","v":0}]
[null,null,"Volumetric water charge",{"$f":"IF($D$25=\"Yes\",IF($D$20=\"Yes\",VLOOKUP($D$56,'Wholesale charges'!$C$35:$E$37,3,1),VLOOKUP($D$56,'Wholesale charges'!$C$35:$E$37,2,1)),\"\")","v":136.21},"p/m3","York Tariff"]
[null,null,"Volumetric wastewater charge",{"$f":"IFERROR(IF($D$26=\"Yes\", IF(CONVERT($D$47,\"m^3\",\"l\")*10^-6=0,0,IF(CONVERT($D$47,\"m^3\",\"l\")*10^-6<50,'Wholesale charges'!$E$5,IF(CONVERT($D$47,\"m^3\",\"l\")*10^-6<=250,'Wholesale charges'!$E$6,'Wholesale charges'!$E$7))),\"\"),\"\")","v":0},"p/m3"]
[]
[]
[]
[null,null,"NAV Density Rates"]
[null,null,"Meters of mains per property",{"$f":"IFERROR(($D$28*1000)/$D$33,\"\")","v":""},"m/prop"]
[null,null,"Meters of sewers per property",{"$f":"IFERROR(($D$29*1000)/$D$33,\"\")","v":""},"m/prop"]
[]
[null,4,"Wholesale volumetric tariff "]
[]
[null,null,"Bulk Water Supply Tariff","Starting weighted water rate (p/m3)",null,null,{"$f":"IFERROR(IF(D25=\"Yes\",(($D$51*100*D31)/(D31*D37+D32*D40))+(D31*D37*$D$53+D32*D40*D57)/(D31*D37+D32*D40),\"\"),\"\")","v":""}]
[null,null,null,"Total water avoidable costs (£/prop)",null,null,{"$f":"IFERROR(IF($D$25=\"Yes\",'Water Avoidable Costs'!E24,\"\"),\"\")","v":2.311918919939592}]
[null,null,null,"Starting weighted water price for site (£)",null,null,{"$f":"IFERROR(((D31*D37+D32*D40)*G68)/100,\"\")","v":""}]
[null,null,null,"Total water avoidable costs for site (£)",null,null,{"$f":"IFERROR(G69*(D31+D32),\"\")","v":0}]
[null,null,null,"Water tariff for site(£)",null,null,{"$f":"IFERROR(G70-G71,\"\")","v":""}]
[null,null,null,"Water tariff (p/m3)",null,null,{"$f":"IFERROR(G72/(D31*D37+D32*D40)*100,\"\")","v":""}]
[]
[]
[]
[null,null,"Bulk Wastewater Discharge Tariff","Starting weighted wastewater rate (p/m3)",null,null,{"$f":"IFERROR(IF(D26=\"Yes\",((D52*100*D31)/(D31*D44+D32*D47)+(D31*D44*$D$54+D32*D47*D58)/(D31*D44+D32*D47)),\"\"),\"\")","v":""}]
[null,null,null,"Total wastewater avoidable costs (£/prop)",null,null,{"$f":"IFERROR(IF(D26=\"Yes\",'Sewerage Avoidable Costs'!E20,\"\"),\"\")","v":0}]
[null,null,null,"Starting weighted wastewater price for site (£)",null,null,{"$f":"IFERROR(((D31*D44+D32*D47)*G77)/100,\"\")","v":""}]
[null,null,null,"Total wastewater avoidable costs for site (£)",null,null,{"$f":"IFERROR(G78*(D31+D32),\"\")","v":0}]
[null,null,null,"Wastewater tariff for site (£)",null,null,{"$f":"IFERROR(G79-G80,\"\")","v":""}]
[null,null,null,"Wastewater tariff (p/m3)",null,null,{"$f":"IFERROR(G81/(D31*D37+D32*D40)*100,\"\")","v":""}]
[]
[null,null,null,"*Leakage discount (%) is applied after the avoidable costs have been deducted. "]
[null,null,null,"*After careful consideration, the discount is capped to maximum 12% discount."]
[null,null,null,"Final water supply tariff (p/m3)",null,null,{"$f":"IFERROR($G$73*(1-'Water Avoidable Costs'!$E$20),\"\")","v":""}]
[null,null,null,"Final wastewater discharge tariff (p/m3)",null,null,{"$f":"IFERROR($G$82*(1-'Sewerage Avoidable Costs'!$E$16),\"\")","v":""}]

{"sheet":"Water Avoidable Costs","index":3,"n_rows":24,"n_cols":19,"rows_kept":24,"cut":false,"hidden":false,"merged":[[22,5,22,15],[19,5,19,15],[0,1,0,16],[11,5,11,15],[12,5,12,15],[13,5,13,15],[5,5,5,15],[2,5,2,15],[16,5,16,15],[4,5,4,15],[9,5,9,15],[10,5,10,15],[3,5,3,15]]}
[null,"Avoidable Costs - Water supply"]
[null,"A","Treated Water Distribution Ongoing On-site Costs",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Colour codes:","user input values"]
[null,null,"Nr","Activity description","Cost £ /prop /year","Approach",null,null,null,null,null,null,null,null,null,null,null,null,"calculated values"]
[null,null,1,"Scientific services (sampling costs)",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Scientific services'!$F$12,\"\"),\"\")","v":0.41430673001728191},"Use of financial year 2018/19 expenditure values and total length of mains from APR 2018/19 to create a simple £ / m that can be multiplied with the estimate NAV rate m / prop."]
[null,null,2,"Water operational costs",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Water costs'!$F$25,\"\"),\"\")","v":""},"Use of year 2018/19 expenditure values and total length of mains from APR 2018/19 to create a simple £ / m that can be multiplied with the estimate NAV rate m / prop."]
[null,null,3,"Leakage management costs",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Leakage costs'!H14,\"\"),\"\")","v":""},"Use of new builds leakage expenditure  (£/property) for repair and detection for the last decade."]
[]
[]
[null,"B","Treated Water Distribution Replacement of On-site Assets"]
[null,null,"Nr","Activity description","Cost £ /prop /year","Approach"]
[null,null,1,"Communication pipes & stop taps replacement/renewal",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Long Run Avoidable Costs'!$G$17,\"\"),\"\")","v":""},"Use of replacement cost £ / property / year for 2018/19, asset life and discount rate at 3.89% to create a future annual value."]
[null,null,2,"Meter and meter chamber replacement/renewal",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",\n                    'Long Run Avoidable Costs'!$G$8,\"\"),\"\")","v":1.6678675645390912},"Use of replacement cost £ / property / year for 2018/19, asset life and discount rate at 3.89% to create a future annual value."]
[null,null,3,"Street furniture replacement/renewal",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Long Run Avoidable Costs'!$J$37,\"\"),\"\")","v":""},"Use of replacement cost £ / property / year for 2018/19, asset life and discount rate at 3.89% to create a future annual value."]
[null,null,4,"Water mains replacement/renewal",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Long Run Avoidable Costs'!$G$26,\"\"),\"\")","v":""},"Use of replacement cost £ / property / year for 2018/19, asset life and discount rate at 3.89% to create a future annual value."]
[]
[]
[null,"C",1,"Business overhead and profit",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",SUM(E4:E6,E11:E14,E23)*(1-(1-'Business overhead'!C9)), \"\"),\"\")","v":0.22974462538321866},"Estimated  based on Retail HH, Loop recharge and YKS charge values. (% is partially updated to current values)"]
[]
[]
[null,"D",1,"Leakage allowance - water (%)",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Leakage costs'!E30,\"\"),\"\")","v":""},"Estimated  based on Natural Rate of Rise for new DMAs in our region and the NAV characteristics."]
[]
[]
[null,"E",1,"Additional costs",{"$f":"IFERROR(IF('Wholesale Tariff'!D25=\"Yes\",'Water costs'!H67+'Bidding Costs'!C21,\"\"),\"\")","v":""},"Include emergency and bidding costs. Estimated using the number of properties."]
[null,null,null,"TOTAL",{"$f":"SUM(E4:E6,E11:E14,E17,E23)","v":2.311918919939592}]

{"sheet":"Sewerage Avoidable Costs","index":4,"n_rows":20,"n_cols":19,"rows_kept":20,"cut":false,"hidden":false,"merged":[[18,5,18,15],[15,5,15,15],[0,1,0,16],[8,5,8,15],[7,5,7,15],[12,5,12,15],[2,5,2,15],[3,5,3,15],[9,5,9,15]]}
[null,"Avoidable Costs -  Sewerage discharge"]
[null,"A","Sewer Discharge Avoidable Costs",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Colour codes:","user input values"]
[null,null,"Nr","Activity description","Cost £ /prop /year","Approach",null,null,null,null,null,null,null,null,null,null,null,null,"calculated values"]
[null,null,1,"Sewerage operational costs (combined sewers)",{"$f":"IFERROR(IF('Wholesale Tariff'!D26=\"Yes\",SUM('Wastewater costs'!$O$28:$P$28),\"\"),\"\")","v":""},"Use of year 2018/19 expenditure values and total length of combined sewers from APR 2018/19 to create a simple £ / m cost that can be multiplied with the NAV unit meter of sewers / property."]
[]
[]
[null,"B","Sewer Discharge Replacement of On-site Assets"]
[null,null,"Nr","Activity description","Cost £ /prop /year","Approach"]
[null,null,1,"Sewers replacement/renewal",{"$f":"IFERROR(IF('Wholesale Tariff'!D26=\"Yes\",'Long Run Avoidable Costs'!G43,\"\"),\"\")","v":""},"Use of replacement cost £ / property / year for 2018/19, asset life and discount rate at 3.89% to create a future annual value."]
[null,null,2,"Street furniture replacement/renewal",{"$f":"IFERROR(IF('Wholesale Tariff'!D26=\"Yes\",'Long Run Avoidable Costs'!$J$38,\"\"),\"\")","v":""},"Use of replacement cost £ / property / year for 2018/19, asset life and discount rate at 3.89% to create a future annual value."]
[]
[]
[null,"C",1,"Business overhead and profit",{"$f":"IFERROR(IF('Wholesale Tariff'!D26=\"Yes\",SUM(E4,E9:E10)*(1-(1-'Business overhead'!C9)),\"\"),\"\")","v":0},"Estimate  based on Retail HH, Loop recharge and YKS charge values. (% is partially updated to current values)"]
[]
[]
[null,"D",1,"Leakage allowance - sewerage (%)",{"$f":"IFERROR(IF('Wholesale Tariff'!D26=\"Yes\",'Leakage costs'!E30,\"\"),\"\")","v":""},"Estimated  based on Natural Rate of Rise for new DMAs in our region and the NAV characteristics."]
[]
[]
[null,"E",1,"Additional costs",{"$f":"IFERROR(IF('Wholesale Tariff'!D26=\"Yes\",'Bidding Costs'!C21,\"\"),\"\")","v":""},"Estimated based on bidding costs and the number of properties."]
[null,null,null,"TOTAL",{"$f":"SUM(E4,E9:E10,E13,E19)","v":0}]

{"sheet":"Long Run Avoidable Costs","index":5,"n_rows":46,"n_cols":14,"rows_kept":46,"cut":false,"hidden":false,"merged":[[6,4,6,5],[6,6,6,7],[7,6,7,7],[15,6,15,7],[15,4,15,5],[0,1,0,11],[42,4,42,5],[42,6,42,7],[32,4,32,5],[32,6,32,7],[33,4,33,5],[33,6,33,7],[41,4,41,5],[41,6,41,7],[16,6,16,7],[24,4,24,5],[24,6,24,7],[25,4,25,5],[25,6,25,7],[16,4,16,5],[7,4,7,5]]}
[null,"Long Run Avoidable Costs"]
[null,null,null,null,null,"Rate",null,null,null,null,null,null,"Colour codes:","user input values"]
[null,null,null,1,"Discount Rate - WACC (as Ofwat's guidance)",3.8899999999999997E-2,null,null,null,null,null,null,null,"calculated values"]
[]
[]
[]
[null,null,null,null,"Asset Class",null,"TOTAL"]
[null,null,null,"A","Meter and Meter chamber",null,{"$f":"IFERROR(IF(AND('Wholesale Tariff'!D26=\"No\", 'Wholesale Tariff'!D25=\"No\"),\"\",SUM(J11:J12)),\"\")","v":1.6678675645390912}]
[]
[null,null,null,"Yrs","Asset","£/property","Asset Life","Rate","Annuity","Final Cost"]
[null,null,null,1,"meters only activities",{"$f":"'Metering costs'!G37","v":0.54760939533105379},{"$f":"'Asset lives'!G3","v":15},{"$f":"F3","v":3.8899999999999997E-2},{"$f":"-1*FV(H11,G11,1)","v":19.860705017658123},{"$f":"(('Metering costs'!$B$9/'Metering costs'!$E$28)*F11)/I11","v":1.1775792185796492}]
[null,null,null,2,"meters/chambers",{"$f":"'Metering costs'!G38","v":1.0630064732896927},{"$f":"'Asset lives'!G4","v":40},{"$f":"F3","v":3.8899999999999997E-2},{"$f":"-1*FV(H12,G12,1)","v":92.59728264708707},{"$f":"(('Metering costs'!$B$9/'Metering costs'!$E$28)*F12)/I12","v":0.4902883459594421}]
[]
[]
[]
[null,null,null,null,"Asset Class",null,"TOTAL","Annuity"]
[null,null,null,"B","Communication pipes & stop taps",null,{"$f":"IFERROR(SUM(J20:J21),\"\")","v":""}]
[]
[null,null,null,"Yrs","Asset","£/property","Asset Life","Rate","Annuity","Final Cost"]
[null,null,null,1,"Stop Taps",{"$f":"'Water costs'!$C$61","v":{"$error":"#VALUE!"}},{"$f":"'Asset lives'!$G$10","v":15},{"$f":"$F$3","v":3.8899999999999997E-2},{"$f":"-1*FV(H20,G20,1)","v":19.860705017658123},{"$f":"(G20*F20)/I20","v":{"$error":"#VALUE!"}}]
[null,null,null,2,"Comm pipes",{"$f":"'Water costs'!$C$59","v":{"$error":"#VALUE!"}},{"$f":"'Asset lives'!$G$6","v":70},{"$f":"$F$3","v":3.8899999999999997E-2},{"$f":"-1*FV(H21,G21,1)","v":346.01024234396135},{"$f":"(G21*F21)/I21","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[null,null,null,null,"Asset Class",null,"TOTAL"]
[null,null,null,"C","Water mains",null,{"$f":"IFERROR(J29,\"\")","v":""}]
[]
[null,null,null,"Yrs","Asset","£/property","Asset Life","Rate","Annuity","Final Cost"]
[null,null,null,1,"Water mains",{"$f":"'Water costs'!E36","v":{"$error":"#VALUE!"}},70,{"$f":"F3","v":3.8899999999999997E-2},{"$f":"-1*FV(H29,G29,1)","v":346.01024234396135},{"$f":"(('Water costs'!$I$13/'Water costs'!$I$15)*F29)/I29","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[null,null,null,null,"Asset Class",null,"TOTAL"]
[null,null,null,"D","Furniture replacement/renewal",null,{"$f":"IFERROR(SUM(J37:J38),\"\")","v":""}]
[]
[null,null,null,"Yrs","Asset","£/property","Asset Life","Rate","Annuity","Final Cost"]
[null,null,null,1,"Furniture Water",{"$f":"'Water costs'!C45","v":{"$error":"#VALUE!"}},25,{"$f":"$F$3","v":3.8899999999999997E-2},{"$f":"-1*FV(H37,G37,1)","v":41.034265955799242},{"$f":"(G37*F37)/I37","v":{"$error":"#VALUE!"}}]
[null,null,null,2,"Furniture Wastewater (Manhole covers)",{"$f":"'Wastewater costs'!F46","v":{"$error":"#VALUE!"}},25,{"$f":"$F$3","v":3.8899999999999997E-2},{"$f":"-1*FV(H38,G38,1)","v":41.034265955799242},{"$f":"(G38*F38)/I38","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[null,null,null,null,"Asset Class",null,"TOTAL"]
[null,null,null,"E","Sewers",null,{"$f":"IFERROR(J46,\"\")","v":""}]
[]
[null,null,null,"Yrs","Asset","£/property","Asset Life","Rate","Annuity","Final Cost"]
[null,null,null,1,"Sewers",{"$f":"'Wastewater costs'!F41","v":{"$error":"#VALUE!"}},{"$f":"'Asset lives'!G9","v":100},{"$f":"$F$3","v":3.8899999999999997E-2},{"$f":"-1*FV(H46,G46,1)","v":1142.2451422132722},{"$f":"('Wastewater costs'!K36*F46)/I46","v":{"$error":"#VALUE!"}}]

{"sheet":"Asset lives","index":6,"n_rows":80,"n_cols":7,"rows_kept":80,"cut":false,"hidden":true,"merged":[[0,1,1,2],[2,1,3,1],[2,2,3,2]]}
[null,"Asset Classes taken from models in UCD 21/02/2017 (PR19 Proforma)"]
[null,null,null,null,null,"Asset Categories","Asset Life (Yrs)"]
[null,"Asset Life","Years",null,1,"AMR Meters - 15 years",15]
[null,null,null,null,2,{"$f":"B34","v":"Meter Chamber - 40 Years"},{"$f":"VLOOKUP(F4,$B$5:$C$80,2,0)","v":40}]
[null,"AMR Meters - 15 years",15,null,3,"Process Plant - E&M Components - 25 Years",25]
[null,"Boreholes - 60 Years",60,null,4,{"$f":"B75","v":"Water Mains (Comm Pipes) - 70 Years"},70]
[null,"Bridges - 60 Years",60,null,5,"Water Mains (All PVC) - 70 Years",70]
[null,"Catchwaters - 100 Years",100,null,6,"Sewers (All PVC) - 40 Years",40]
[null,"CSOs - 100 Years",100,null,7,"Sewers (300 to 1000mm) - 100 Years",100]
[null,"Detention Tank - 100 Years",100,null,8,"Equip eg ICA - equip & valves - 15 Years",15]
[null,"Earthbank/Concrete Dams - 200 Years",200,null,9,"Water Mains (300 to 600mm) - 100 Years",100]
[null,"Equip eg H'ware/S'ware/S'ware Dev - 5 Years",5]
[null,"Equip eg ICA - equip & valves - 15 Years",15]
[null,"Equip eg ICA - equip only - 7 Years",7]
[null,"Equip eg Modelling & D'bases - 7 Years",7]
[null,"Equip eg Outstat's & Inst - 10 Years",10]
[null,"Equip eg Tel/Comms/Cabling/Elec - 20 Years",20]
[null,"Equip eg Telemetry at one Site - 15 Years",15]
[null,"Equipment / Computers - 3 Years",3]
[null,"Equipment eg Water Meters - 12 years",12]
[null,"Fencing - 15 Years",15]
[null,"Filter Media  (Brimac, GAC & Memb) - 5 Years",5]
[null,"Filter Media (RGF) - 10 Years",10]
[null,"Filter Media (Slag, Plastic etc) - 40 Years",40]
[null,"Furniture & Fittings - 7 Years",7]
[null,"Inter-Process Pipework (Sewage) - 60 Years",60]
[null,"Inter-Process Pipework (Water) - 60 Years",60]
[null,"Lagoons - 80 Years",80]
[null,"Land (Freehold) - 0 Years",0]
[null,"Land (Short Lease) - 0 Years",0]
[null,"Landscaping (Amenity) - 0 Years",0]
[null,"Landscaping (Protective) - 0 Years",0]
[null,"Lighting - 10 Years",10]
[null,"Meter Chamber - 40 Years",40]
[null,"Non Oper'l Buildings - 60 Years",60]
[null,"O'head Gantry Cranes - 40 Years",40]
[null,"Op Buildings - Pumping - 60 Years",60]
[null,"Operational Structures - 60 Years",60]
[null,"Operational Structures - Metalwork - 30 Years",30]
[null,"Pipe Bridge - 60 Years",60]
[null,"Process Plant - E&M Components - 10 Years",10]
[null,"Process Plant - E&M Components - 15 Years",15]
[null,"Process Plant - E&M Components - 25 Years",25]
[null,"Process Plant - E&M Components - 3 Years",3]
[null,"Process Plant - E&M Components - 40 Years",40]
[null,"Process Plant - E&M Components - 5 Years",5]
[null,"Process Plant - E&M Components - 7 Years",7]
[null,"Process Plant - E&M Major Items - 20 Years",20]
[null,"Process Plant - E&M Major Items - 30 Years",30]
[null,"Pumps - Pumping Stations & Inter Process - 20 Years",20]
[null,"River Prot Works eg Piling - 40 Years",40]
[null,"Roads, Car Parks, Etc. - 60 Years",60]
[null,"RW Storage Res - 80 Years",80]
[null,"Sea Outfalls (Piped) - 60 Years",60]
[null,"Service Reservoirs - 80 Years",80]
[null,"Sewer Relining - 50 years",50]
[null,"Sewers (< 300mm) - 80 Years",80]
[null,"Sewers (>1000mm) - 125 Years",125]
[null,"Sewers (300 to 1000mm) - 100 Years",100]
[null,"Sewers (All PVC) - 40 Years",40]
[null,"Spillways - 30 Years",30]
[null,"Steel Water Towers - 40 Years",40]
[null,"V&P 10 Year Life (Need Scrap Value)",10]
[null,"V&P 3 Year Life (Need Scrap Value)",3]
[null,"V&P 4 Year Life (Need Scrap Value)",4]
[null,"V&P 5 Year Life (Need Scrap Value)",5]
[null,"V&P 6 Year Life (Need Scrap Value)",6]
[null,"V&P 8 Year Life (Need Scrap Value)",8]
[null,"Water Mains - Cleaning - 3 Years",3]
[null,"Water Mains - Cleaning - 6 Years",6]
[null,"Water Mains (<300mm) - 70 Years",70]
[null,"Water Mains (>600mm) - 125 Years",125]
[null,"Water Mains (300 to 600mm) - 100 Years",100]
[null,"Water Mains (All PVC) - 70 Years",70]
[null,"Water Mains (Comm Pipes) - 70 Years",70]
[null,"Water Mains (Leakage Control) - 5 Years",5]
[null,"Water Mains (Scrape & Line) - 40 Years",40]
[null,"Water Mains (Tunnels) - 150 Years",150]
[null,"Water Storage Res (Earth Banked) - 80 Years",80]
[null,"Water Towers - Concrete - 60 Years",60]

{"sheet":"York tariffs","index":7,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Bidding Costs","index":8,"n_rows":21,"n_cols":28,"rows_kept":21,"cut":false,"hidden":true,"merged":[]}
["Date:",null,"2020-03-26"]
["Name:",null,"Dimitra K"]
[]
["Annuities represent a loan or investment which offer monthly fixed payments until the account is deplted or paid off."]
[null,"2017/18",null,"2019/20"]
["Interest Rate",4.7699999999999999E-2,null,3.8899999999999997E-2]
["Payment Periods",25,null,25,"years"]
["Bidding Cost",-15000,null,{"$f":"B8*0.176+B8","v":-17640},"£"]
["Nr of properties",null,null,{"$f":"'Wholesale Tariff'!$D$33","v":0}]
[]
[null,null,"Years",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25]
[null,null,"Initial Payment",{"$f":"D8","v":-17640}]
[null,null,"Payments",{"$f":"PMT($D$6,$D$7,$D$8)","v":1116.0806242065387},{"$f":"PMT($D$6,$D$7,$D$8)","v":1116.0806242065387},1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387]
[]
[null,null,"Final payment",{"$f":"SUM(D12:D13)","v":-16523.919375793463},{"$f":"SUM(E12:E13)","v":1116.0806242065387},1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387,1116.0806242065387]
[null,null,null,{"$f":"PV(D6,D7,D13:AB13)","v":-17639.999999999971},"<<calculate original investment amount"]
[]
[]
["NPV",null,{"$f":"NPV(D6,D15:AB15)","v":660.50245451921808}]
[]
["NAV discount (incl WACC)",null,{"$f":"IFERROR(-(D12/D7/D9),\"\")","v":""},"£/prop"]

{"sheet":"Wholesale charges","index":9,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":true,"merged":[]}
[1,"Yorkshire Water Measured NH Water and Wastewater Charges (excl. York Waterworks)"]
[null,"Description","Units","2020/21 Wholesale water NH charges","2020/21 Wholesale wastewater NH charges","Tariff Report Name"]
[null,"Standard Tariff 0-20","p/m3",136.21,171.61,"Metered Volumetric Charges"]
[null,"Standard Tariff 20-50","p/m3",136.21,171.61,"Metered Volumetric Charges"]
[null,"Banded Tariff 50-100","p/m3",86.98,171.61,"Metered Volumetric Charges"]
[null,"Banded Tariff  100-250","p/m3",86.98,138.71,"Metered Volumetric Charges"]
[null,"Banded Tariff >250 Mla","p/m3",73.41,126.61,"Metered Volumetric Charges"]
[]
[]
[]
[2,"Yorkshire Water Measured HH Water and Wastewater Charges (excl. York Waterworks)"]
[null,"Description","Units","2020/21 Wholesale water HH charges","2020/21 Wholesale wastewater HH charges"]
[null,"Fixed Charge","£",9.89,0]
[null,"Volumetric Charge","p/m3",138.85,179.32]
[]
[]
[null,"*If a NAV applies for a site within the defined geographical area of York Waterworks, then the relevant water tariff should be applied. Wastewater tariffs are the same in both cases."]
[3,"York Waterworks Measured NH Water and Wastewater Charges "]
[null,"Description","Units","2020/21 Wholesale water NH charges","Tariff Report Name"]
[null,"Standard Tariff 0-5","p/m3",75.47,"Metered Volumetric Charges"]
[null,"Standard Tariff 5-50","p/m3",75.47,"Metered Volumetric Charges"]
[null,"Banded Tariff <50 Mla","p/m3",75.47,"Metered Volumetric Charges"]
[null,"Banded Tariff >50 & <=250 Mla","p/m3",66.3,"Metered Volumetric Charges"]
[null,"Banded Tariff >250 Mla","p/m3",66.3,"Metered Volumetric Charges"]
[]
[4,"York Waterworks Measured HH Water and Wastewater Charges"]
[null,"Description","Units","2020/21 Wholesale water HH charges"]
[null,"Fixed Charge","£",9.89]
[null,"Volumetric Charge","p/m3",78.650000000000006]
[]
[]
[]
[null,"Source of Data:","vlookup table for banded NH tariffs"]
[null,null,"Bands","NH Yorkshire WATER charges","NH York WATER charges"]
[null,null,0,{"$f":"D3","v":136.21},{"$f":"D20","v":75.47}]
[null,null,50,{"$f":"D5","v":86.98},{"$f":"D23","v":66.3}]
[null,null,250,{"$f":"D7","v":73.41},{"$f":"D24","v":66.3}]

{"sheet":"Scientific services","index":10,"n_rows":88,"n_cols":7,"rows_kept":88,"cut":false,"hidden":true,"merged":[[14,0,14,9],[76,0,87,0],[16,0,27,0],[28,0,39,0],[40,0,51,0],[52,0,63,0],[64,0,75,0]]}
["YW Source:","Sampling and Analysis Manager, Clare Beasant, August 2018, 'WSZ Sample Costs 2015'"]
["YW Definition:","‘Any costs YW takes over for the collection and analysis of samples for scientific purposes per year per property’"]
["DWI guidance:","DWI - Guidance on the Regulatory Responsibilities of NAVs in relation to Public Water Supplies"]
[]
["Source data: ","For the estimation of the sampling cost we use the financial year 2017/18 and the nr of properties from APR 2017/18"]
["Properties 2018/19",2299162]
[]
[null,"Year","Annual Total Cost","Aveg Cost per year","£ / property / year","3yr average"]
[null,"2015/16",{"$f":"SUM(D20:D31)","v":1021269.9700000059},{"$f":"C9/12","v":85105.830833333821},{"$f":"C9/$B$6","v":0.44419226222423908}]
[null,"2016/17",{"$f":"SUM(D32:D43)","v":1068594.1099999831},{"$f":"C10/12","v":89049.509166665259},{"$f":"C10/$B$6","v":0.46477547471643282}]
[null,"2017/18",{"$f":"SUM(D44:D55)","v":957061.99000000185},79755.165833333493,{"$f":"C11/$B$6","v":0.41626557415267035}]
[null,"2018/19",{"$f":"SUM(D56:D67)","v":952558.28999999387},79379.857499999489,{"$f":"C12/$B$6","v":0.41430673001728191},{"$f":"AVERAGE(E12)","v":0.41430673001728191},"<< Average sampling cost for 3 years"]
[null,"2019/20",{"$f":"SUM(D68:D79)","v":798001.35999999079},{"$f":"C13/12","v":66500.113333332571},{"$f":"C13/$B$6","v":0.34708357218847163}]
[]
["Sampling Costs 2015 - 2018"]
["YEAR","Month","Regulatory Cost","Total Cost"]
[{"$f":"YEAR(B17)","v":2015},"2015-01-01"]
[null,"2015-02-01"]
[null,"2015-03-01"]
[null,"2015-04-01",39299.339999999997,75701.659999999989]
[null,"2015-05-01",42018.460000000399,77707.280000000377]
[null,"2015-06-01",42710.140000000603,82110.280000000625]
[null,"2015-07-01",44637.300000001,93438.680000000983]
[null,"2015-08-01",40692.250000000102,90307.190000000017]
[null,"2015-09-01",44549.800000000701,90814.010000000737]
[null,"2015-10-01",43179.510000000802,93467.460000000792]
[null,"2015-11-01",41677.220000000299,93677.480000000287]
[null,"2015-12-01",41804.200000000499,77107.98000000052]
[{"$f":"YEAR(B29)","v":2016},"2016-01-01",42416.560000000303,84097.560000000347]
[null,"2016-02-01",38893.500000000502,76017.750000000495]
[null,"2016-03-01",44361.990000000696,86822.640000000727]
[null,"2016-04-01",43673.750000000502,94106.630000000558]
[null,"2016-05-01",39332.549999998097,89090.259999997914]
[null,"2016-06-01",45324.679999998203,102887.05999999792]
[null,"2016-07-01",41695.239999998601,109465.89999999836]
[null,"2016-08-01",43598.449999998003,109899.82999999786]
[null,"2016-09-01",44140.559999998099,97327.299999998068]
[null,"2016-10-01",43406.669999998499,93136.699999998469]
[null,"2016-11-01",42557.729999997602,87594.999999997526]
[null,"2016-12-01",42109.609999998203,78295.169999998237]
[{"$f":"YEAR(B41)","v":2017},"2017-01-01",31402.149999999401,66700.899999999456]
[null,"2017-02-01",27639.299999999301,65336.889999999199]
[null,"2017-03-01",33874.189999999602,74752.469999999579]
[null,"2017-04-01",28935.479999999199,65464.869999999159]
[null,"2017-05-01",38063.989999999503,76261.509999999471]
[null,"2017-06-01",40786.249999999098,89979.899999999121]
[null,"2017-07-01",37701.130000000601,97094.990000000776]
[null,"2017-08-01",35964.0700000008,93071.030000000887]
[null,"2017-09-01",37693.010000000701,87285.940000000686]
[null,"2017-10-01",43156.9000000003,92753.860000000204]
[null,"2017-11-01",40155.670000000602,84769.010000000577]
[null,"2017-12-01",29890.7400000009,62365.610000000837]
[{"$f":"YEAR(B53)","v":2018},"2018-01-01",35234.410000000898,76462.390000000858]
[null,"2018-02-01",29207.069999999701,62527.809999999728]
[null,"2018-03-01",38212.1099999996,69025.069999999556]
[null,"2018-04-01",31685.939999999799,68840.109999999768]
[null,"2018-05-01",34204.759999999602,68301.829999999565]
[null,"2018-06-01",36405.959999999199,79020.439999999115]
[null,"2018-07-01",33553.809999999597,82545.389999999476]
[null,"2018-08-01",33832.149999999398,84063.359999999331]
[null,"2018-09-01",36239.609999999302,87475.379999999292]
[null,"2018-10-01",35237.269999999597,86662.569999999687]
[null,"2018-11-01",35477.929999999302,79342.809999999343]
[null,"2018-12-01",34074.779999999802,70691.489999999831]
[2019,"2019-01-01",38343.769999999298,77884.199999999328]
[null,"2019-02-01",37008.139999999097,76797.059999999154]
[null,"2019-03-01",43530.1899999999,90933.649999999878]
[null,"2019-04-01",37222.199999999102,71106.309999999139]
[null,"2019-05-01",42400.369999998802,86387.589999998876]
[null,"2019-06-01",42072.789999998902,89130.74999999901]
[null,"2019-07-01",40072.699999998898,89958.269999999015]
[null,"2019-08-01",40556.759999998903,94917.379999999102]
[null,"2019-09-01",41952.159999998803,91515.789999998946]
[null,"2019-10-01",42730.729999998803,99697.63999999901]
[null,"2019-11-01",41814.269999998702,101062.71999999876]
[null,"2019-12-01",39792.889999998799,74224.909999998825]
[2020,"2020-01-01"]
[null,"2020-02-01"]
[null,"2020-03-01"]
[null,"2020-04-01"]
[null,"2020-05-01"]
[null,"2020-06-01"]
[null,"2020-07-01"]
[null,"2020-08-01"]
[null,"2020-09-01"]
[null,"2020-10-01"]
[null,"2020-11-01"]
[null,"2020-12-01"]

{"sheet":"Metering costs","index":11,"n_rows":38,"n_cols":10,"rows_kept":38,"cut":false,"hidden":true,"merged":[[7,1,7,9]]}
["Source:","Billing solutions Team Leader, Kelly Lloyd, August 2018 'MEX data 13.08.2018' &"]
[null,"Capital Scheme Project Manager, Chris Daw, October 2018"]
["Definition:","Meter and Chamber replacement/renewal include costs for asset material, fuel, team plant, support plant,"]
[null,"consumable materials, management fee, non asset materials, non productive labour, productive labour, site overhead, sub contract invoice"]
["Important comment: When the NAV application is fo WATER ONLY then the meter cost is 100% water avoidable cost, when the application is for water and wastewater is 50/50 between water and wastewater,"]
[null,null,"when is for wastewater only the meter costs are 0"]
[]
[null,"Meter and meter chamber Renewal/Replacement Costs"]
["Metered Properties 2018/19",{"$f":"1145.569*1000","v":1145569},"<< APR Datashare 2018 -Table 4Q"]
["Properties 2018/19",{"$f":"2319.165*1000","v":2319165},"<< APR Datashare 2018 -Table 4Q"]
[]
[]
[null,"Years","Nr of HH meters renewed","Nr of NHH meters renewed","Total nr of meters renewed"]
[1,"2010-11",76920,9377,{"$f":"SUM(C14:D14)","v":86297}]
[2,"2011-12",46467,10964,{"$f":"SUM(C15:D15)","v":57431}]
[3,"2012-13",134405,20854,155259]
[4,"2013-14",48378,12982,61360]
[5,"2014-15",4107,1650,5757]
[6,"2015-16",27453,5347,32800]
[7,"2016-17",33359,4264,37623]
[8,"2017-18",24957,1866,26823]
[9,"2018-19",3716,1625,5341]
[]
[]
[null,"Years","Scheme","Expenditure","Activities","UnitCost","£ / metered property / year","£ / property / year","3yr average"]
[1,"2015-16","S/3601",5691194,32800,{"$f":"D26/E26","v":173.51201219512194},{"$f":"D26/$B$9","v":4.9680062920697052}]
[2,"2016-17","S/4106",6680791,37623,{"$f":"D27/E27","v":177.57199053770299},{"$f":"D27/$B$9","v":5.8318538647606557}]
[3,"2017-18","S/4393",6044960,26823,225.36479886664429,{"$f":"D28/$B$9","v":5.276818768664306},{"$f":"D28/$B$10","v":2.6065243309553221}]
[4,"2018/19","S/4818",1845071.6099999999,5341,345.45433626661674,{"$f":"D29/$B$9","v":1.6106158686207463},{"$f":"D29/$B$10","v":0.79557582578212416},{"$f":"AVERAGE(G29)","v":1.6106158686207463},"<< Meter replacement/renewal cost for 2018/19 is used for the model"]
[]
[]
[]
[null,null,"External","Internal"," << Exercise to identify the % of activities that are meter only  or meter /chamber"]
[null,"count",13,11,"It is assumed that when an activity is external is for meter/chamber,"]
[null,"% of total",0.54200000000000004,0.45800000000000002,"and when is internal is for meter only"]
[null,"average cost",411.10526447497966,238.25394631864046]
[null,"sum cost",56064.558421082074,28932.123771971659,null,"meter",{"$f":"I29*D38","v":0.54760939533105379}]
[null,"% of cost",0.66,0.34,null,"meter/chamber",{"$f":"I29*C38","v":1.0630064732896927}]

{"sheet":"Water costs","index":12,"n_rows":67,"n_cols":14,"rows_kept":67,"cut":false,"hidden":true,"merged":[[6,1,6,9],[31,1,31,9],[24,1,25,1],[24,3,25,3],[24,4,25,4],[23,5,23,6],[24,5,25,6],[34,4,34,5],[35,4,35,5],[44,1,45,1],[44,2,45,2],[63,1,63,9],[58,1,59,1],[58,2,59,2],[60,1,61,1],[60,2,61,2]]}
[0,"Liam Saddler, Strategic Asset Manager - Clean Water Networks, October 2018"]
["1. definition:","The costs cover Flushing and Linning (current cleaning methods). Both cleaning techniques are predominantly done  on YW's distribution network rather than trunk main network."]
[]
["2,3 source:","Martin Hopkins, Finance Business Partner, October 2018"]
["2,3 definition:","R&M expenditure on street furniture renewal, Communication pipes (flow/pressure), stop tap installations/renewals, CS&N stop tap adaptors"]
[]
["1.","Water Operational Costs"]
["Properties 2018/19",{"$f":"2319.165*1000","v":2319165},"<< APR Datashare 2018 -Table 4Q"]
["Nr of DMA:",2500,"<< Provided by Katy Walker"]
[]
[null,"CCC Table 11 - Water services",null,null,null,null,null,null,null,null,"APR Datashare 2018/19"]
[null,null,"2011/12","2012/13","2013/14","2014/15","2015/16","2016/17","2017/18",null,"Parameter","km","meters","meters/property"]
[null,"Total length of potable mains as at 31 March (km)",31270.3,31274.080000000002,31363.38,31404.9,31531.96,31604.71,31693.4,null,"Total Length of Mains 2018/19",31790.1,{"$f":"L13*1000","v":31790100},{"$f":"M13/B8","v":13.707562851284838}]
[null,"Total length of mains relined",19.05,6.61,13,0.2,7,3.0139999999999998,15.41]
[null,"Total length of mains renewed (km)",125.19,112.87,75.3,6.27,29.2,43.64,25.78]
[null,"Mains cleaned (km)",241.29,147.33000000000001,22.6,13.54,20.54,26.34]
[]
[null,"Rate mains cleaned",{"$f":"C16/$C$13","v":7.7162675126237999E-3},{"$f":"D16/$D$13","v":4.7109299458209485E-3},{"$f":"E16/$E$13","v":7.2058560014896355E-4},{"$f":"F16/$F$13","v":4.3114291081964911E-4},{"$f":"G16/$G$13","v":6.5140257694098309E-4},{"$f":"H16/H13","v":8.334200820067642E-4}]
[null,"Rate of relining",{"$f":"C14/C13","v":6.0920426091211149E-4},2.11357136644787E-4,4.1449614167860731E-4,6.3684329515457777E-6,2.2199698337813445E-4,9.5365532542459653E-5,4.8622110597159029E-4]
[null,"Rate renewed",{"$f":"C15/C13","v":4.0034793398208528E-3},{"$f":"D15/D13","v":3.6090590034942673E-3},2.4008891898768562E-3,1.9965037303096013E-4,9.2604455923450365E-4,1.3808068480932114E-3,8.1341856664163519E-4]
[]
[]
[null,"Provided by Liam Saddler"]
[null,"Activity 2017/18","Cost (£) and Nr of DMAs","Total expenditure (£)","£/ meter for YW","Cost of flushing  for NAV  (£/property/year) "]
[null,"Flushing",5000,{"$f":"C25*C26","v":4500000},{"$f":"D25/(L13*1000)","v":0.141553502505497},{"$f":"E25*'Wholesale Tariff'!D63","v":{"$error":"#VALUE!"}}]
[null,null,900]
[]
[]
[]
[]
[]
["2.","Replacement Costs - Water"]
[null,"1. Water mains"]
[null,"Provided by Liam Saddler"]
[null,"Cost of replacement (£/metre of mains)","Total expenditure (£/metre)","£/ meter for YW","Cost of renewal for NAV (£/property/year) "]
[null,{"$f":"250","v":250},{"$f":"B36*(I15*1000)","v":6445000},{"$f":"C36/M13","v":0.2027360719217618},{"$f":"D36*'Wholesale Tariff'!D63","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[null,"2. Street furniture"]
[null,"Provided by Martin Hopkins"]
[null,"Years","2017/18","2018/19"]
[null,"Street furnit cost of replacement",2015233.6399999983,3512575.0900000553]
[]
[null,"Cost of street furniture for NAV (£/property/year) ",{"$f":"(AVERAGE(C43:D43)/M13)*'Wholesale Tariff'!D63","v":{"$error":"#VALUE!"}}]
[]
[]
[]
[]
[null,"3. Communication pipes. Stop taps"]
[null,"Provided by Martin Hopkins"]
[null,"Years","2017/18","2018/19"]
[null,"Comm pipes cost of replacement",3210025.55,5550586.3500000294]
[]
[null,"Years","2017/18","2018/19"]
[null,"Stop taps cost of replacement",3689378.7400000016,5665121.9099999797]
[]
[]
[null,"Cost of comm pipes for NAV (£/property/year) ",{"$f":"(AVERAGE(C53:D53)/M13)*'Wholesale Tariff'!D63","v":{"$error":"#VALUE!"}}]
[]
[null,"Cost of stop taps for NAV (£/property/year) ",{"$f":"(AVERAGE(C56:D56)/M13)*'Wholesale Tariff'!$D$63","v":{"$error":"#VALUE!"}}]
[]
[]
["3.","Emergency Costs"]
[null,"CAPEX",3.6,"m"]
[null,"OPEX",1,"m"]
[null,"Total cost per year",{"$f":"C66+C65/5","v":1.72},"m",null,null,"Cost per prop",{"$f":"C67/2","v":0.86},"£/prop/year for 2,000,000 properties for emergency services"]

{"sheet":"Leakage costs","index":13,"n_rows":30,"n_cols":9,"rows_kept":30,"cut":false,"hidden":true,"merged":[[4,1,4,8]]}
["Source:","Martin Hopkins, Finance Business Partner, April 2019"]
["Definition:","Annual leakage Find & Fix costs - Reactive R&M and Proactive leakage"]
[null," "]
[]
[null,"Leakage Management Costs & Leakage Allowance"]
["Properties 2018/19",{"$f":"2319.165*1000","v":2319165},"<< APR Datashare 2018 -Table 4Q"]
["Total length of mains (km)",31790.1]
[]
[null,"1. Leakage Management Cost"]
[]
[null,null," 2015/16 "," 2016/17 "," 2017/18 "," 2018/19"]
[null," Reactive R&M ",8921428,11940917,14224491,18824339]
[null," Proactive Leakage ",10451975,9552427,16342953,33208227,null,"4yrs average"]
[null,"Total",{"$f":"SUM(C12:C13)","v":19373403},{"$f":"SUM(D12:D13)","v":21493344},{"$f":"SUM(E12:E13)","v":30567444},{"$f":"SUM(F12:F13)","v":52032566},null,{"$f":"AVERAGE(C16:F16)","v":{"$error":"#VALUE!"}},"<< leakage management cost for YW"]
[null,"£/meter for YW",{"$f":"C14/($B$7*10^3)","v":0.6094162333556673},{"$f":"D14/($B$7*10^3)","v":0.67610180527900199},0.96153972463125315,{"$f":"F14/($B$7*10^3)","v":1.6367537692552083}]
[null,"£/prop NAV",{"$f":"C15*'Wholesale Tariff'!$D$63","v":{"$error":"#VALUE!"}},{"$f":"D15*'Wholesale Tariff'!$D$63","v":{"$error":"#VALUE!"}},{"$f":"E15*'Wholesale Tariff'!$D$63","v":{"$error":"#VALUE!"}},{"$f":"F15*'Wholesale Tariff'!$D$63","v":{"$error":"#VALUE!"}}]
[]
[]
[null,"2. Leakage Allowance"]
[null,"Method 1 - APR Datashare 2018 - Table 4P (line 74)"]
[null,"Company","Length mains (m)","Distribution losses (l/d)","m3/d","m3/metre/annum"]
[null,"2018/19",{"$f":"B7*1000","v":31790100},{"$f":"212.04*1000000","v":212040000},{"$f":"CONVERT(D22,\"l\",\"m^3\")","v":212040},{"$f":"(E22/C22)*365","v":2.4345503788915419}]
[null,"2017/18",{"$f":"31693.4*10^3","v":31693400},219210000,{"$f":"CONVERT(D23,\"l\",\"m^3\")","v":219210},{"$f":"(E23/C23)*365","v":2.5245524304744835}]
[]
[null,"Method 2 - Natural Rate of Rise"]
[null,"Average Total Leakage Losses for the al age groups (m3/m/yr)"]
[null,1.599270863656584,"<< Water lost per metre of main per annum 2018/19"]
[]
[null,null,"Annual Losses (m3/year)","Total  input water for the site","Leakage Allowance","Uncapped leakage allowance"]
[null,"Water",{"$f":"IF(ISBLANK('Wholesale Tariff'!$D$28),('Wholesale Tariff'!$D$29*1000)*'Leakage costs'!$B$27,('Wholesale Tariff'!$D$28*1000)*'Leakage costs'!$B$27)","v":0},{"$f":"('Wholesale Tariff'!D31*'Wholesale Tariff'!D37+'Wholesale Tariff'!D32*'Wholesale Tariff'!D40+'Leakage costs'!C30)","v":0},{"$f":"IF(ROUNDUP((C30/D30),3)>0.12,0.12,ROUNDUP((C30/D30),3))","v":{"$error":"#DIV/0!"}},{"$f":"ROUNDUP((C30/D30),3)","v":{"$error":"#DIV/0!"}}]

{"sheet":"Wastewater costs","index":14,"n_rows":46,"n_cols":16,"rows_kept":46,"cut":false,"hidden":true,"merged":[[29,1,29,19],[4,1,4,19],[7,2,7,3],[7,4,7,5],[7,6,7,7],[7,8,7,9],[7,10,7,11],[20,12,20,13],[20,14,20,15]]}
["Source:","Peter Bennett, Finance Business Partner (Waste Water), October 2018"]
[null,"Mark Thompson, Capital Process Manager, December 2018"]
["Definition:","Total expenditure for Jetting, Vactor, CCTV and Densilts"]
[]
["1.","Sewerage Operational Costs"]
["Properties 2018/19",{"$f":"2299.162*1000","v":2299162}]
[null,null,"Provided by Peter Bennett"]
[null,null,"2014-15",null,"2015-16",null,"2016-17",null,"2017-18",null,"2018-19"]
[null,"Activities (£m)","OPEX","CAPEX","OPEX","CAPEX","OPEX","CAPEX","OPEX","CAPEX","OPEX","CAPEX"]
[1,"Jetting",4827550.17,1469977.83,4555664.17,1101725.6000000001,4348373,983054,4728000,1286000,7017104,647759]
[2,"Vactor",2219526.46,88830.54,2388440.87,87390.59,3852008.83,29410,3673000,87000,3265399,129429]
[3,"CCTV",1006218.06,163509.12,744499.96,120050.9,1371729,118278.17,746000,85000,657350,22762]
[4,"Desilts",1271632.4300000002,17679.57,1124842.25,40357.01,959572,29410,712000,6000,612337,3037]
[null,"TOTAL",{"$f":"SUM(C10:C13)","v":9324927.1199999992},{"$f":"SUM(D10:D13)","v":1739997.0600000003},8813447.25,1349524.1,10531682.83,1160152.17,9859000,1464000,{"$f":"SUM(K10:K13)","v":11552190},802987]
[]
[null,"APR Datashare 2018/19","Length (km)","Length (meter)"]
[null,"Total length of combined public sewers 2017/18",16265.903433973994,{"$f":"C17*1000","v":16265903.433973994}]
[]
[]
[]
[null,null,"2014-15",null,"2015-16",null,"2016-17",null,"2017-18",null,"2017-18",null,"2018-19",null,"2018-19"]
[null,"Activities (£k)","OPEX","CAPEX","OPEX","CAPEX","OPEX","CAPEX","OPEX for YW","CAPEX for YW","OPEX for NAV (£/prop)","CAPEX for NAV (£/prop)","OPEX for YW","CAPEX for YW","OPEX for NAV (£/prop)","CAPEX for NAV (£/prop)"]
[1,"Jetting",{"$f":"C10/$D$17","v":0.29678955058326939},{"$f":"D10/$D$17","v":9.037172979459053E-2},0.28007446303196121,6.7732210785099486E-2,0.26733055545612755,6.043648322334997E-2,0.29066937592441378,7.9061086598730151E-2,{"$f":"I23*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"J23*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"K10/$D$17","v":0.43139958554921903},{"$f":"L10/$D$17","v":3.9823118502415891E-2},{"$f":"M23*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"N23*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}}]
[2,"Vactor",{"$f":"C11/$D$17","v":0.13645270113703961},5.4611500898537804E-3,0.1468372709634653,5.3726244198321312E-3,0.23681493288312847,1.8080766383115501E-3,{"$f":"I11/$D$17","v":0.22580977533214294},5.3486116128223351E-3,{"$f":"I24*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"J24*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"K11/$D$17","v":0.20075116105630389},7.9570741659308278E-3,{"$f":"M24*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"N24*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}}]
[3,"CCTV",{"$f":"C12/$D$17","v":6.1860570123535186E-2},1.0052261816486904E-2,4.5770587721859352E-2,7.3805245732157798E-3,8.4331559299369752E-2,7.2715401563836126E-3,4.5862807622591518E-2,5.225655024021821E-3,{"$f":"I25*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"J25*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"K12/$D$17","v":4.0412756824008758E-2},1.3993689371386436E-3,{"$f":"M25*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"N25*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}}]
[4,"Desilts",{"$f":"C13/$D$17","v":7.817779290045386E-2},1.0869098093299466E-3,6.9153382999347174E-2,2.4810801418941046E-3,5.8992849914243146E-2,1.8080766383115501E-3,4.3772545612982786E-2,3.6886976640154034E-4,{"$f":"I26*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"J26*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"K13/$D$17","v":3.7645434358169999E-2},1.8670958009357966E-4,{"$f":"M26*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}},{"$f":"N26*'Wholesale Tariff'!$D$64","v":{"$error":"#VALUE!"}}]
[null,"TOTAL",{"$f":"SUM(C23:C26)","v":0.57328061474429803},{"$f":"SUM(D23:D26)","v":0.10697205151026115},{"$f":"SUM(E23:E26)","v":0.541835704716633},{"$f":"SUM(F23:F26)","v":8.2966439920041504E-2},{"$f":"SUM(G23:G26)","v":0.64746989755286888},7.132417665635668E-2,{"$f":"SUM(I23:I26)","v":0.60611450449213111},9.0004223001975858E-2,{"$error":"#VALUE!"},{"$error":"#VALUE!"},0.71020893778770178,4.9366271185578937E-2,{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"AVERAGE(O27)","v":{"$error":"#VALUE!"}},{"$f":"AVERAGE(P27)","v":{"$error":"#VALUE!"}}]
[]
["2. ","Replacement Costs"]
[]
[null,"1. Sewers"]
[null,"CCC table 16","2011-12","2012-13","2013-14","2014-15","2015-16","2016-17","2017-18","2018-19"]
[null,"Critical sewers - replaced (km)",0.39,0.79820000000000002,-0.2319,0.94430000000000014,0.70703000000000005,0.18860000000000002,0.68,0.68]
[null,"Non-critical sewers replaced (km)",5.48,5.0919800000000004,20.71,15.184719999999999,12.749560000000002,12.490950000000002,14.96,14.96]
[null,"Total length of sewers (km)",30388.11,30508.681,30532.906843502835,30524.799999999999,30563.275980000002,30620.55,30669.599999999999,30732.399027838088,{"$f":"(J36/(J34+J35))","v":1964.9871501175248}]
[null,"Rate sewers replaced",{"$f":"SUM(C34:C35)/C36","v":1.9316765669204173E-4},{"$f":"SUM(D34:D35)/D36","v":1.9306570480710067E-4},6.706894992003548E-4,5.2839068560645757E-4,4.4028624447214773E-4,4.1408629172238906E-4,5.0995122205702069E-4,{"$f":"SUM(J34:J35)/J36","v":5.0890918036801945E-4}]
[]
[null,"Provided by Mark Thompson"]
[null,"Investment driver","2017/18 Actuals ","2018/19 Actuals ","Cost per meter for YW (£/m)","Cost per property for NAV (£/prop)"]
[null,"TOTAL",6502323.4700000249,7524789.6099997638,{"$f":"AVERAGE(D41)/D17","v":0.46261123094355844},{"$f":"E41*'Wholesale Tariff'!D64","v":{"$error":"#VALUE!"}}]
[]
[null,"1. Furniture (Manhole and Manhole covers)"]
[null,"Provided by Mark Thompson"]
[null,"Investment driver","2017/18 Actuals ","2018/19 Actuals ","Cost per meter for YW (£/m)","Cost per property for NAV (£/prop)"]
[null,"Manhole and Manhole covers",1171042.8700000001,1547678.1800000004,{"$f":"AVERAGE(D46)/D17","v":9.5148614786893543E-2},{"$f":"E46*'Wholesale Tariff'!D64","v":{"$error":"#VALUE!"}}]

{"sheet":"Business overhead","index":15,"n_rows":17,"n_cols":4,"rows_kept":17,"cut":false,"hidden":true,"merged":[[0,1,0,6]]}
[null,"Business Overhead"]
[]
[null,null,"£m"]
[1,"HH Retail Opex",60.991,"<<APR datashare Table 2C 2018/19"]
[2,"Loop Recharge",{"$f":"4.765693+4.765693*0.176","v":5.6044549679999998},"<<Provided by Naomi Scholey"]
[3,"YKS Charge",{"$f":"0.956819+0.956819*0.176","v":1.1252191439999999}]
[]
[null,"TOTAL",{"$f":"SUM(C5:C6)","v":6.7296741119999997}]
[null,"% Overhead",{"$f":"C8/C4","v":0.11033880592218523}]
[]
[]
["Source: ''Section 3 Wholesale Minus\", David Musco"]
["Business overhead calculation is based on the idea"]
["that the incumbnent's HH retail arm will notionally"]
["extend its business activities to embrace the local"]
["network management/operation on these same"]
["new developments."]