{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["calcs","Front Sheet","Licence data","Charge estimate","2. Use and purpose","3. Table of charges"],"not_rendered":{"charts":0,"images":6,"macros":1,"external_links":0,"defined_names":25,"comments":2,"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":"calcs","index":0,"n_rows":3,"n_cols":32,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
[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,26,27,28,29]
["Code","Name","Type","Abs vol","Tidal ?","Tidal factor2","Use","loss factor","loss","C Vol","CV code?","Baseline","Water Co","Water avail","WA?","Water avail2","Model","Applicant type","Mod charge","Spare1","Spare2","SS","SS code","SS charge","DiscountSpray","DiscountWinter","DiscountCT","Total fee","Summary","Vol from","Vol to","Abs final"]
["Blank","Blank","n",100000,"Y",{"$f":"IF(E3=\"y\",0.2,1)","v":0.2},"Heat Pump",{"$f":"VLOOKUP(Calcs[[#This Row],[Use]],'2. Use and purpose'!$A$2:$C$38,3,FALSE)","v":3.0000000000000001E-3},{"$f":"VLOOKUP(Calcs[[#This Row],[Use]],'2. Use and purpose'!$A$2:$C$38,2,FALSE)","v":"Low "},{"$f":"ROUNDUP(Calcs[[#This Row],[Abs vol]]*Calcs[[#This Row],[Tidal factor2]]*Calcs[[#This Row],[loss factor]]*0.001,0)","v":1},{"$f":"IF(AND(J3>=0,J3<15),\"0 to 15\",IF(AND(J3>=15,J3<50),\"15 to 50\",IF(AND(J3>=50,J3<85),\"50 to 85\",IF(AND(J3>=85,J3<120),\"85 to 120\",IF(AND(J3>=120,J3<220),\"120 to 220\",IF(AND(J3>=220,J3<400),\"220 to 400\",IF(AND(J3>=400,J3<750),\"400 to 750\",IF(AND(J3>=750,J3<1400),\"750 to 1400\",IF(AND(J3>=1400,J3<2200),\"1400 to 2200\",IF(AND(J3>=2200,J3<6500),\"2200 to 6500\",IF(AND(J3>=6500,J3<9000),\"6500 to 9000\",IF(AND(J3>=9000,J3<21500),\"9000 to 21500\",IF(AND(J3>=21500,J3<100000),\"21500 to 100000\",IF(J3>=100000,\"100000+\"))))))))))))))","v":"0 to 15"},{"$f":"VLOOKUP(Calcs[[#This Row],[CV code?]],TabCV[#All],2,FALSE)","v":97},{"$f":"IF(Calcs[[#This Row],[Type]]=\"Y\",VLOOKUP(Calcs[[#This Row],[CV code?]],TabCV[#All],3,FALSE),0)","v":0},"No water available",{"$f":"VLOOKUP(Calcs[Water avail],WaterAvTable[#All],2,FALSE)","v":"Y"},{"$f":"IF(Calcs[[#This Row],[WA?]]=\"Y\",VLOOKUP(Calcs[[#This Row],[CV code?]],TabCV[#All],4,FALSE),0)","v":18},"Tier 1"," ",{"$f":"IF(Calcs[[#This Row],[Model]]=\"Tier 1\",VLOOKUP(Calcs[[#This Row],[CV code?]],TabCV[#All],5,FALSE),IF(Calcs[[#This Row],[Model]]=\"Tier 2\",VLOOKUP(Calcs[[#This Row],[CV code?]],TabCV[#All],6,FALSE),0))","v":5},{"$f":"IF((Calcs[[#This Row],[DiscountSpray]]+Calcs[[#This Row],[DiscountWinter]]+Calcs[[#This Row],[DiscountCT]]<3),\" Discount for \" & (IF(Calcs[[#This Row],[DiscountSpray]]=0.5,\"2 part tariff, \",\"\")) & (IF(Calcs[[#This Row],[DiscountWinter]]=0.5,\"winter only, \",\"\"))& (IF(Calcs[[#This Row],[DiscountCT]]=0.5,\"CRT\",\"\")),\"\")","v":""},null,"Severn",{"$f":"IF(Calcs[[#This Row],[SS]]=\"None\",\" no \",\" \")","v":" "},{"$f":"INDEX(SSources[[0 to 15]:[100000+]],MATCH(Calcs[[#This Row],[SS]],SSources[Supported Source name],0),MATCH(Calcs[[#This Row],[CV code?]],SSources[[#Headers],[0 to 15]:[100000+]],0))","v":163},1,1,1,{"$f":"IF(Calcs[[#This Row],[SS charge]]=0.1,\"N/A\",(Calcs[[#This Row],[Baseline]]+Calcs[[#This Row],[Water Co]]+Calcs[[#This Row],[Water avail2]]+Calcs[[#This Row],[Mod charge]]+Calcs[[#This Row],[Spare2]]+Calcs[[#This Row],[SS charge]])*Calcs[DiscountSpray]*Calcs[DiscountWinter]*Calcs[DiscountCT])","v":283},{"$f":"IF(Calcs[[#This Row],[SS charge]]=0.1,\"We do not believe this volume of water would be available for abstraction from this source.\",(Calcs[[#This Row],[loss]]) & \" loss, \" & (IF(Calcs[Tidal ?]=\"y\",\"\",\" non \")) & \"tidal\" & \" abstraction of\" & Calcs[[#This Row],[Abs final]] & \",\" & (IF(Calcs[[#This Row],[WA?]]=\"N\",\" no\",\"\")) & \" water availability charge, \" & (Calcs[[#This Row],[Model]]) & \" modelling charge,\" & (Calcs[[#This Row],[SS code]]) & \"supported source charge\" & (IF(Calcs[[#This Row],[Type]]=\"Y\",\", Water Company charge\",\"\")) & \".\" &Calcs[[#This Row],[Spare1]])","v":"Low  loss, tidal abstraction of up to and including 25000 megalitres a year, water availability charge, Tier 1 modelling charge, supported source charge."},{"$f":"ROUNDDOWN((VLOOKUP(Calcs[[#This Row],[CV code?]],TabAbsVol[#All],2,FALSE))/Calcs[[#This Row],[Tidal factor2]]/Calcs[[#This Row],[loss factor]],0)","v":0},{"$f":"ROUNDDOWN((VLOOKUP(Calcs[[#This Row],[CV code?]],TabAbsVol[#All],3,FALSE)/Calcs[[#This Row],[Tidal factor2]]/Calcs[[#This Row],[loss factor]]),0)","v":25000},{"$f":"IF(Calcs[[#This Row],[Vol to]]<0,( \" greater than \" & ROUND(100000/Calcs[[#This Row],[Tidal factor2]]/Calcs[[#This Row],[loss factor]],0)),(IF(Calcs[[#This Row],[Vol from]]=0,\" up to and including \" & Calcs[[#This Row],[Vol to]],(\" greater than \" & Calcs[[#This Row],[Vol from]]) & \" up to and including \" & Calcs[[#This Row],[Vol to]]))) & \" megalitres a year\"","v":" up to and including 25000 megalitres a year"}]

{"sheet":"Front Sheet","index":1,"n_rows":34,"n_cols":3,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Water Resources charges scheme - annual charge indicator tool "]
[]
[null,"This tool helps to identify the annual charge for water resources abstractions."]
[null,"Source data - Draft of the Environment Agency (Environmental Permitting and Abstraction Licensing) (England) Charging Scheme 2022"]
[null,"Published on 18 August 2021"]
[null,"Use the maps linked from Citizen Space to identify if water availability, modelling and supported sources charges apply."]
[null,"Guidance to complete the licence data form","This is the input data that will be used to work out the annual charge"]
[null,"Name ","A name for your records "]
[null,"Is the water abstracted from a tidal river? ","Use 'y' for a tidal abstraction, 'n' for a non-tidal abstraction. This is the source information on your licence "]
[null,"Water availability - for abstractions from a groundwater body","Select Good (green), Poor (red) for groundwater abstractions in a groundwater body. Use the maps linked from Citizen Space to identify your water availability."]
[null,"Water availability - for abstractions not in a groundwater body","Select water available (Green), restricted water available (yellow) or no water available (red) for surface water and groundwater abstractions not in a groundwater body. Use the maps linked from Citizen Space to identify your water availability."]
[null,"Modelling ","Select no for an abstraction not in a model, Tier 1 or Tier 2 for groundwater or surface water abstraction in a modelling area. Use the maps linked from Citizen Space to identify whether you are in a modlling area."]
[null,"Supported source name ","Select none, or the supported source name where your abstraction is located on. Use the maps linked from Citizen Space to identify if your abstraction requires water from a supported source."]
[null,"Water company","Select only if you are a water undertaker (regulated by Ofwat)"]
[null,"Canal  & River  Trust","Select only if you are the Canal & River Trust"]
[null,"Other ","Default selection for all abstractions except water companies and Canal & River Trust. "]
[null,"Purpose ","Select the the purpose of your abstraction from the list. This is the purpose information on your licence. If you have more than one purpose with the same loss the annual volumes must be added together to get the right annual charge."]
[null,"Authorised annual quantity - m3/year","Enter the annual volume number you are authorised to abstraction in metres cubed per year (m3/year). This is information is on your licence as authorised volume."]
[null,"Spray irrigation 2 part tariff agreement","Select the box if you have a spray or trickle irrigation 2 part tariff agreement"]
[null,"Winter only abstraction","Select  the box if your abstraction is for winter only. This information is on your licence under season"]
[null,"Save / Calculate ","Press calculate to calculate your charge, you will be asked if you want to add another purpose and volume. "]
[null,"Do you want to add another purpose?","Yes if you have more than one purpose on your licence"]
[null,"Enter next purpose and abstraction volume","Enter the next purpose and annual volume from your licence"]
[null,"Print to Excel","To print out you data to excel "]
[null,"Clear all ","To clear all information from the form"]
[null,"Guidance for the charge estimate sheet","This is the table of charges. The information you enter into the Licence data form calculates the estimate from this information. "]
[null,"Date ","Date when the form was completed"]
[null,"Name ","Name from the licence data form"]
[null,"Total cost","Total cost of all the rows. This is the total annual charge"]
[null,"Company type ","Result from input form"]
[null,"Row number ","Row number for the different purposes added"]
[null,"Charge category description","Description of the charge category for  each purpose"]
[null,"Cost ","Charge category cost for each purpose and volume entered"]

{"sheet":"Licence data","index":2,"n_rows":26,"n_cols":11,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[null,{"$f":"TODAY()","v":"2021-09-16"},"Water Resources charges scheme - annual charge indicator tool "]
[null,null,null,null,null,null,null,"Validation","Formatting","Error count","Show error"]
[null,"Name",null,null,null,null,null,{"$f":"AND(LEN(D3)>3,LEN(D3)<41)","v":false},{"$f":"AND(NOT(H3),$K$3)","v":false},{"$f":"COUNTIF(H3:H22,FALSE)","v":7},0]
[]
[null,"Is the water abstracted from a tidal river?",null,null,null,null,null,{"$f":"OR((UPPER(D5))=\"Y\",(UPPER(D5))=\"N\")","v":false},{"$f":"AND(NOT(H5),$K$3)","v":false}]
[]
[null,"Water availability",null,null,null,null,null,{"$f":"LEN(D7)>1","v":false},{"$f":"AND(NOT(H7),$K$3)","v":false}]
[]
[null,"Modelling",null,null,null,null,null,{"$f":"LEN(D9)>1","v":false},{"$f":"AND(NOT(H9),$K$3)","v":false}]
[]
[null,"Supported sources",null,null,null,null,null,{"$f":"LEN(D11)>1","v":false},{"$f":"AND(NOT(H11),$K$3)","v":false}]
[]
[null,"Select type of applicant "]
[]
[]
[null,null,null," "]
[null,null,null,null,null,null,null,null,null,null,1]
[null,"Enter authorised abstraction volumes for each purpose on your licence"]
[]
[null,"Purpose",null,null,null,null,null,{"$f":"LEN(D20)>1","v":false},{"$f":"AND(NOT(H20),$K$3)","v":false}]
[]
[null,"Authorised annual quantity - m3 / year",null,null,null,null,null,{"$f":"AND(ISNUMBER(D22),(D22)>0.0009)","v":false},{"$f":"AND(NOT(H22),$K$3)","v":false}]
[]
[null,null,null,null,null,null,null,null,null,null,1]
[null,"Select if purpose qualifies for any of these agreements"]
[null,null,null,null,null,null,null,null,null,null,1]

{"sheet":"Charge estimate","index":3,"n_rows":10,"n_cols":6,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"Water Resources charges scheme - annual charge indicator tool "]
[]
[]
[]
[null,"Date","Name","Total Cost",null,"Company type"]
[null,null,null,{"$f":"SUM(D11:D42)","v":0}]
[]
[]
[null,"Row number","Charge category description","Cost"]

{"sheet":"2. Use and purpose","index":4,"n_rows":38,"n_cols":3,"rows_kept":38,"cut":false,"hidden":true,"merged":[]}
["OFFICIAL SENSITIVE "]
[]
["Use Description","Loss Factor Description","Loss Factor proportion"]
["Agricultural use (where not specified in other use categories)","Medium ",0.6]
["Amenity pools through flow.","Low ",3.0000000000000001E-3]
["Anti-frost spraying.","Medium ",0.6]
["Boiler feed.","Medium ",0.6]
["Commercial and industrial purposes  ","Medium ",0.6]
["Dewatering ","Low ",3.0000000000000001E-3]
["Dust suppression ","High ",1]
["Effluent / Slurry dilution ","Low ",3.0000000000000001E-3]
["Evaporative or general cooling ","High ",1]
["Fish farms.","Low ",3.0000000000000001E-3]
["Fish pass/ Canoe pass","Low ",3.0000000000000001E-3]
["Heat pump","Low ",3.0000000000000001E-3]
["Horticultural irrigation","High ",1]
["Hydraulic testing ","Low ",3.0000000000000001E-3]
["Hydropower of greater than 5 megawatts","Low ",3.0000000000000001E-3]
["Make-up or top-up water (no through flow).","High ",1]
["Mineral washing.","Low ",3.0000000000000001E-3]
["Non-evaporative cooling.","Low ",3.0000000000000001E-3]
["Pollution remediation ","Low ",3.0000000000000001E-3]
["Power production (electricity generation) of greater than 5 megawatts.","Low ",3.0000000000000001E-3]
["Private water supply","Medium ",0.6]
["Public water supply (including raw water supplies to industry) ","Medium ",0.6]
["Spray irrigation for agriculture or non-agriculture","High ",1]
["Trickle irrigation","High ",1]
["Vegetable washing.","Low ",3.0000000000000001E-3]
["Warping.","Low ",3.0000000000000001E-3]
["Water bottling and uses, which incorporate water in the product.","Medium ",0.6]
["Water meadows.","Low ",3.0000000000000001E-3]
["Water supply for a commercial purpose","Medium ",0.6]
["Water transfers (under a full licence)","Low ",3.0000000000000001E-3]
["Water transfers from any source of supply to water systems","Low ",3.0000000000000001E-3]
["Water used to convey material.","Medium ",0.6]
["Water wheels not for powerr","Low ",3.0000000000000001E-3]
["Watercress growing","Low ",3.0000000000000001E-3]
["Wet fencing.","Low ",3.0000000000000001E-3]

{"sheet":"3. Table of charges","index":5,"n_rows":80,"n_cols":15,"rows_kept":80,"cut":false,"hidden":true,"merged":[[19,1,19,2]]}
[null,"OFFICIAL SENSITIVE "]
[1,2,3,4,5,6]
["Consumptive volume categories (ML) ","Baseline charges ","Water Company","Water availability ","Tier 1","Tier 2"]
["0 to 15",97,8,18,5,13]
["15 to 50",513,41,96,25,75]
["50 to 85",1162,92,218,58,159]
["85 to 120",1783,141,334,88,244]
["120 to 220",2889,228,542,143,394]
["220 to 400",5258,415,986,261,718]
["400 to 750",9938,785,1863,493,1357]
["750 to 1400",18437,1456,3457,914,2518]
["1400 to 2200",31621,2498,5929,1568,4319]
["2200 to 6500",65451,5170,12272,3246,8939]
["6500 to 9000",139580,11025,26171,6923,19059.019]
["9000 to 21500",257486,20338,48279,12771,35165]
["21500 to 100000",657233,51913,123232,32597,89759]
["100000+",3365328,265816,631002,166911,459505.98300000001]
[]
[]
[null,"Conversion back to abstraction volume allowing for tidal and loss factors"]
["CV","From","To"]
["0 to 15",0,15]
["15 to 50",15.000999999999999,50]
["50 to 85",50.000999999999998,85]
["85 to 120",85.001000000000005,120]
["120 to 220",120.001,220]
["220 to 400",220.001,400]
["400 to 750",400.00099999999998,750]
["750 to 1400",750.00099999999998,1400]
["1400 to 2200",1400.001,2200]
["2200 to 6500",2200.0010000000002,6500]
["6500 to 9000",6500.0010000000002,9000]
["9000 to 21500",9000.0010000000002,21500]
["21500 to 100000",21500.001,100000]
["100000+",100000.001,-1]
[]
[]
["Lists for form"]
["Modelling"]
["no"]
["Tier 1"]
["Tier 2"]
[]
["Water availabilty","Charge"]
["Water available ","N"]
["Restricted water available","Y"]
["No water available","Y"]
["Groundwater good","N"]
["Groundwater poor","Y",null,"Updated 15 September 2021"]
[]
["Supported Source name","0 to 15","15 to 50","50 to 85","85 to 120","120 to 220","220 to 400","400 to 750","750 to 1400","1400 to 2200","2200 to 6500","6500 to 9000","9000 to 21500","21500 to 100000","100000+"]
["None"]
["Candover ",35,184,418,641,1038,1890,3752,6627,11366,23526,50171,92552,236238,1209644]
["Dee",74,391,886,1359,2201,4007,7573,14050,24096,49875,106363,196211,500827,2564460]
["Earl Soham - Deben",105,552,1251,1920,3109,5659,10696,19845,34035,70447,0.1,0.1,0.1,0.1]
["Glen Groundwater",54,285,645,990,1604,2919,5517,10236,17556,36338,77495,142956,0.1,0.1]
["Great East Anglian Groundwater",172,908,2058,3157,5114,9309,17594,32641,55982,115875,247115,455858,0.1,0.1]
["Great East Anglian Surface Water",157,829,1880,2882,4671,8505,16075,29823,51149,105871,225780,416503,1065203,5443658]
["Kielder",1575,8301,18816,28866,46754,85105,160850,298420,511811,1059372,2259211,4167622,10637839,54470525]
["Lodes Granta Groundwater",112,590,1338,2053,3325,6052,11438,21220,36393,75329,0.1,0.1,0.1,0.1]
["Rhee Groundwater",237,1248,2829,4340,7030,12796,24184,44868,76952,0.1,0.1,0.1,0.1,0.1]
["Severn",163,857,1943,2980,4827,8786,16606,30809,52839,109369,233240,430265,1098248,0.1]
["Thames",43,229,518,795,1288,2345,4430,8222,14096,29176,62221,114822,293082,1500709]
["Thet and Little Ouse Surface Water",707,3726,8445,12956,20852,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
["Waveney Groundwater",74,388,880,1349,2186,3978,7519,13950,23926,0.1,0.1,0.1,0.1,0.1]
["Waveney Surface Water",73,387,876,1345,2178,3964,7492,13900,23840,49346,0.1,0.1,0.1,0.1]
["Witham and Ancholme",543,2859,6481,9948,16103,29329,55421,102812,176345,365007,778411,1436250,0.1,0.1]
["Wye",0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
["Lower Yorkshire Derwent",23,119,271,415,673,1224,2314,4293,7363,15241,32502,59958,152123,0.1]
["Medway - Allington",9,49,111,171,276,503,950,1763,3024,6260,13350,24627,62860,0.1]
["Nene - Northampton",253479,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
["Nene - Water Newton",1192,6282,14239,21844,35381,64403,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
["Ouse -  Eaton Socon",186,983,2227,3417,5534,10074,19039,35323,60581,125394,0.1,0.1,0.1,0.1]
["Ouse - Hermitage",550,2897,6567,10075,16318,29703,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
["Ouse - Offord",531,2799,6345,9733,15765,28697,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
["Welland - Tinwell",534,2815,6381,9789,15855,28861,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
[]
[]
[null,null,"If updating table make sure £0 is entered as £0.1"]
[null,null,"A value of £0.1 in this table gives the message -  We do not believe this volume of water would be available for abstraction from this source."]