{"text_format":"1","workbook":"xlsx","n_sheets":15,"sheets":["Front Sheet","PE EST","PE SITE SUM","PE SOW","PE SOW SUM","New PE EST","New PE EST (2)","New PE EST (3)","New PE SUM","PE Rates","BARR EST","BARR EST (2)","BARR EST (3)","BARR SUM","Rates"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":0,"comments":0,"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":"Front Sheet","index":0,"n_rows":42,"n_cols":20,"rows_kept":42,"cut":false,"hidden":false,"merged":[[20,2,21,20],[1,1,5,21],[17,2,18,20],[16,2,16,20],[19,2,19,21]]}
[]
[null,"Calculate your estimated charges for mains requisition work\nEstimation based on Severn Trent new connection charges 2020/21\nLast updated:  February 2020\n"]
[]
[]
[]
[]
[]
[null,"Before you get started there are a few things you'll need to understand…"]
[]
[null,"l","The estimated charges are calculated using Severn Trent's 2020/21 new connections charges."]
[null,"l","The estimated charges relate to mains requisitions work completed by Severn Trent."]
[null,"l","This estimator can be used to estimate the cost of the onsite mains scheme that needs to be paid based on the site information you provide."]
[null,"l","The estimated charges should not be taken as a final quote, agreed terms or an offer to carry out works at an agreed price."]
[null,"l","The estimated charges can be calculated for PE (polyethylene) sites and protective pipe sites. "]
[null,"l","PE sites refer to standard pipework used when the ground is not contaminated and our estimation is based on a bespoke build up of a schedule of rates."]
[null,"l","The estimated costs for onsite ground is unmade."]
[null,"l","Barrier sites refer to sites where protective pipework is required because the ground is contaminated and our estimation is based on a bespoke build up of a schedule of rates."]
[null,"l","Figures shown are 'gross' values ie this figure represents the total onsite cost of construction required for this site. Any work that Severn Trent will be required to carry out \n(whether contestable or non-contestable) has not been deducted, deferred or 'netted' off this figure."]
[]
[null,"l","The estimated charges assumes temporary two way traffic lights within the SOW (source of water) cost however in reality some schemes may require more expensive traffic management (eg road closures)."]
[null,"l","To provide a simple estimate of likely charges we have had to make a number of assumptions about your site, particularly for the estimation of protective pipe charges. A formal quote, based on information provided by you about your site, may therefore differ."]
[]
[null,"l","The protective pipe & PE pipe estimated charges allows you to select onsite pipework with three lengths of different pipe diameters. Offsite pipework can only be selected as a single length of one diameter."]
[null,"l","The protective pipe & PE pipe estimated charges assumes temporary two way traffic lights within each scheme however in reality some schemes may require more expensive traffic management (eg road closures)."]
[null,"l","The protective pipe & PE pipe estimated charges assumes the diameters of the valves, hydrants and wash outs based on the information you provide under question 1 of your protective pipe charges."]
[null,"l","The protective pipe & PE pipe estimated charges assumes that one pressure test per 100m of onsite mains is required."]
[null,"l","The protective pipework estimated charges do not assume that there are any PRV requirements - quotes including PRVs are likely to be higher than the estimates shown in this tool"]
[null,"l","This estimate does not include any mains extension needed to serve the site."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"PE EST","index":1,"n_rows":42,"n_cols":29,"rows_kept":42,"cut":false,"hidden":true,"merged":[[118,6,120,6],[1,1,3,21],[4,4,4,5],[4,2,4,3],[36,6,36,9],[96,5,96,8],[97,5,97,8],[98,5,98,8],[99,5,99,8],[100,5,100,8],[33,10,33,20],[34,10,34,20],[35,10,35,20],[36,10,37,20],[35,28,35,31],[36,28,36,31],[9,2,9,4],[13,2,13,4],[19,2,19,4],[33,2,33,4],[33,6,33,9],[34,6,34,9],[35,6,35,9]]}
[]
[null,"Calculate your estimated PE charges\nThis estimation should only be used for standard PE schemes where sites have between 5 and 250 plots.\n"]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"Question 1",null,"How many plots do you have on your site?"]
[null,null,"* Our standard and fixed charges only apply to schemes with between 5 and 250 plots. If your scheme is outside of this you will require a bespoke quote for your site."]
[]
[null,"Answer 1",null,null,null,"Plots"]
[]
[null,"Question 2",null,"Do you require us to simply lay your new main (lay only) or excavate the trench and lay the main (open cut)?"]
[]
[null,"Answer 2","Open cut"]
[]
[null,"Question 3",null,"What is the estimated length of main from the edge of your development site to our existing network? (ie distance to the SOW connection)"]
[null,null,"* If you're unsure of the length at this stage enter the width of the adjacent road"]
[null,null,"* The length we are referring to here is shown in the diagram on the right"]
[]
[null,"Answer 3",null,null,null,"m"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Question 4",null,"What are the excavation requirements for the connection pipework?",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Question 5","What are the excavation requirements for the connection pipework?"]
[null,null,"* These excavation requirements are for the length of main shown in Question 3",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"* These excavation requirements are for the length of main shown in Question 4"]
[]
[null,"Answer 4","Excavation in the highway",null,null,null,"Options",null,null,null,"Descriptions",null,null,null,null,null,null,null,null,null,null,null,null,"Answer 5","Excavation in the highway",null,null,"Options","Descriptions"]
[null,null,null,null,null,null,"No excavation",null,null,null,"You do not require Severn Trent or a Self Lay Provider to excavate the trench for the pipework",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"No excavation","You do not require Severn Trent or a Self Lay Provider to excavate the trench for the pipework"]
[null,null,null,null,null,null,"Excavation in unmade ground",null,null,null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Excavation in unmade ground","You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)"]
[null,null,null,null,null,null,"Excavation in the highway",null,null,null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in the highway. This option covers footpaths and types 3/4 and 1/2 carriageway.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Excavation in the highway","You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in the highway. This option covers footpaths and types 3/4 and 1/2 carriageway."]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"PE SITE SUM","index":2,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":true,"merged":[[1,1,3,21],[4,2,4,3],[4,4,4,5],[6,1,7,21],[72,4,72,8],[9,4,10,6],[15,4,16,6],[21,4,22,6],[25,4,26,6],[9,1,9,3],[15,1,15,3],[21,1,21,3],[25,1,25,3],[73,4,73,8],[74,4,74,8],[92,5,94,5],[70,4,70,8],[71,4,71,8]]}
[]
[null,"Summary of your total site estimated charges\nThe total cost estimate for your scheme is based on the information you have provided\n"]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"This is shown as a single figure because our simplified PE 'per plot' charges include the cost of all contributing elements (eg pipework, connection, fittings, traffic management etc) "]
[]
[]
[null,"Total cost",null,null,{"$f":"(IF('PE EST'!C14=\"Lay only\",U15,U16)*'PE EST'!C10)+((VLOOKUP('PE EST'!C34,Q19:U21,5,FALSE))*MAX(0,'PE EST'!C20-22))","v":0}]
[]
[]
[]
[null,"If you are choosing a self lay provider to lay your new main the total asset payment is below:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Per plot charges (PE)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Lay only",null,null,null,620.58000000000004]
[null,"Asset payment",null,null,{"$f":"E10*0.9","v":0},null,null,null,null,null,null,null,null,null,null,null,"Open cut",null,null,null,896.3]
[null,null,null,null,null,null,null,"(equivalent to 90% of total construction costs)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Per metre charges >22m (PE)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"No excavation",null,null,null,24.27]
[null,"If you are choosing Severn Trent to lay your new main the income offset and developer contribution are below:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Excavation in unmade ground",null,null,null,48.67]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Excavation in the highway",null,null,null,122.46]
[null,"Income offset",null,null,{"$f":"E16","v":0}]
[null,null,null,null,null,null,null,"(equivalent to 90% of total construction costs)"]
[]
[]
[null,"Developer Contribution",null,null,{"$f":"E10*0.1","v":0},null,null,"(equivalent to 10% of total construction costs)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"PE SOW","index":3,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":true,"merged":[[36,2,36,4],[20,8,21,19],[1,1,3,21],[4,2,4,3],[4,4,4,5],[19,2,19,4],[30,2,30,4],[30,7,30,9],[22,8,22,19],[23,8,23,19],[24,8,24,19],[19,6,19,7],[19,8,19,19],[20,6,21,7]]}
[]
[null,"Calculate your source of water charges\nIf you would like to understand the specific cost of the connection between the new main and the existing main (otherwise known as the source of water connection) we just need a little more information"]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"The SOW estimate covers the assets shown in the diagram"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Question 1",null,"What are the excavation requirements offsite?"]
[null,null,"* If you are unsure what your excavation requirements will be select the worst case scenario. This is likely to be 'type 1/2 carriageway' for self lay providers (highest charge)"]
[]
[null,"Answer 1","Type 1/2 CW",null,null,null,"Option",null,"Description"]
[null,null,null,null,null,null,"Unmade",null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)"]
[]
[null,null,null,null,null,null,"Footpath",null,"You require excavation of the trench in a footpath by either Severn Trent or a Self Lay Provider"]
[null,null,null,null,null,null,"Type 3/4 CW",null,"You require excavation of the trench in a type 3/4 carriageway by either Severn Trent or a Self Lay Provider"]
[null,null,null,null,null,null,"Type 1/2 CW",null,"You require excavation of the trench in a type 1/2 carriageway by either Severn Trent or a Self Lay Provider"]
[]
[null,"Question 2",null,"What length and diameter of main do you require Severn Trent to lay into the SOW connection?"]
[null,null,"* If you are unsure of your pipework diameter at this stage please select 126 to 180mm"]
[]
[null,null,"Length",null,null,null,null,"Diameter"]
[null,"Answer 2",null,null,null,"m",null,"126 to 180",null,null,"mm"]
[]
[null,"Question 3",null,"What diameter connection do you need between the new mains and our existing mains (also known as SOW connection)?"]
[null,null,"* If you're unsure of the length at this stage enter the width of the adjacent road"]
[null,null,"* The length we are referring to here is shown in the diagram above"]
[]
[null,"Answer 3","150-200",null,null,"mm"]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"PE SOW SUM","index":4,"n_rows":42,"n_cols":21,"rows_kept":42,"cut":false,"hidden":true,"merged":[[17,8,19,20],[22,8,24,20],[23,0,23,3],[18,0,18,3],[70,4,70,8],[17,1,17,3],[17,4,18,6],[22,1,22,3],[22,4,23,6],[71,4,71,8],[72,4,72,8],[73,4,73,8],[74,4,74,8],[92,5,94,5],[1,1,3,21],[4,2,4,3],[4,4,4,5],[9,1,9,3],[9,4,10,6],[9,8,11,20]]}
[]
[null,"Summary of source of water estimated charges\nThe total cost estimate for your scheme is based on the information you have provided\n"]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV"]
[]
[]
[null,null,null,null,null,null,null,null,"Specific assumptions"]
[]
[null,"Total SOW cost",null,null,{"$f":"SUM(E18+E23)","v":6383.6213490186601},null,null,null,"The SOW cost includes an allowance for valves, wash outs and trial holes. This SOW cost does include the length of main connecting into the connection itself based on the answer to Question 2. The SOW cost assumes temporary two way traffic lights are required."]
[]
[]
[]
[]
[]
[null,"This total SOW cost is split by contestable and non-contestable elements below:"]
[]
[null,"Off-site pipework cost",null,null,{"$f":"(Rates!C340+Rates!C348+Rates!C349)*0.1","v":102.04622938752003},null,null,null,"This pipework cost is solely the cost of the length of pipework specified in response to Question 2. It does not include any valves or other fittings."]
[" (laying of main into SOW)"]
[null,null,null,null,null,null,"Contestable"]
[]
[]
[null,"Physical connection",null,null,{"$f":"SUM(Rates!C337+Rates!C341+Rates!C342+Rates!C343+Rates!C350)*1.05","v":6281.5751196311403},null,null,null,"This connection cost includes the physical connection, traffic management and fittings."]
[" includes valves and traffic management"]
[null,null,null,null,null,null,"Non contestable"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"New PE EST","index":5,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":false,"merged":[[88,5,88,8],[89,5,89,8],[90,5,90,8],[108,6,110,6],[27,2,27,4],[27,6,27,8],[34,2,34,4],[86,5,86,8],[87,5,87,8],[34,8,34,19],[35,8,36,19],[28,2,28,4],[28,6,28,8],[1,1,3,21],[4,2,4,3],[4,4,4,5],[6,1,6,20],[7,1,9,7],[25,2,25,4],[25,6,25,8],[25,10,27,19],[26,2,26,4],[26,6,26,8]]}
[]
[null,"Calculate your estimated protective pipe charges\nThis estimate should be used for PE pipework schemes. "]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[]
[null,"The diagram to the right shows a typical mains site and highlights which assets are being referenced in each question."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"ASSETS WITHIN YOUR SITE BOUNDARY"]
[]
[null,"Question 1",null,"What is the estimated length of main on your development site (within the boundaries of your site)?"]
[null,null,"Often development sites require mains pipework of two or three different diameters, if you require more than one diameter of mains please specify the lengths of each below."]
[null,null,"* If you only require one diameter of on-site pipework within this estimate simply enter the total length & relevant diameter in the top row of three below"]
[null,null,"* If you are unsure of your pipework diameter at this stage please select 126 to 180mm and enter the full length of mains within your development site alongside this diameter"]
[]
[null,null,"Diameter of pipe",null,null,null,"Length of each diameter"]
[null,"Answer 1","64 to 90",null,null,"mm",100,null,null,"m","Please ensure at least one of the three pairs of answer cells have been populated. If a diameter has been selected but a length hasn't been entered or vice versa the 'on-site mains laying cost' shown in your estimate will be zero."]
[null,null,"up to 63",null,null,"mm",119,null,null,"m"]
[null,null,null,null,null,"mm",null,null,null,"m"]
[null,null,null,null,null,"mm",null,null,null,"m"]
[]
[null,"Question 2",null,"What are the excavation requirements onsite?"]
[null,null,"* This is solely for the length of mains within your development site"]
[]
[null,null,null,null,null,null,"Option",null,"Description"]
[null,"Answer 2","Unmade",null,null,null,"No Excavation",null,"You do not require Severn Trent or a Self Lay Provider to excavate the trench for the pipework"]
[null,null,null,null,null,null,"Unmade",null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)"]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"New PE EST (2)","index":6,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":false,"merged":[[27,9,27,20],[28,9,28,20],[23,2,23,4],[23,7,23,8],[23,9,23,20],[24,7,25,8],[24,9,25,20],[26,9,26,20],[20,2,21,18],[1,1,3,21],[4,2,4,3],[4,4,4,5],[11,2,11,4],[16,2,16,4]]}
[]
[null,"Calculate your estimated protective pipe charges\nThis estimate should be used for protective pipe schemes. "]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"OFF-STE MAINS 'CONNECTION' LENGTH"]
[]
[null,"Question 1",3,"What is the estimated length of offsite main (from the edge of your development site to our existing network? ie distance to the SOW connection)"]
[null,null,"* If you're unsure of the length at this stage enter the width of the adjacent road"]
[]
[null,"Answer 3",3,null,null,"m"]
[]
[null,"Question 2",4,"What diameter of mains pipework do you require off-site?"]
[null,null,"* If you're unsure of the diameter at this stage please select 126 to 180mm"]
[]
[null,"Answer 4","126 to 180",null,null,"mm"]
[]
[null,"Question 2",5,"What are the excavation requirements off-site?"]
[null,null,"* This is solely for the length of mains between the edge of your development site and our existing network"]
[null,null,"* If you are unsure what your excavation requirements will be select the worst case scenario. This is likely to 'type 1/2 carriageway' (highest charge)"]
[]
[]
[null,"Answer 5","Type 3/4 CW",null,null,null,null,"Option",null,"Description"]
[null,null,null,null,null,null,null,"Unmade",null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)"]
[]
[null,null,null,null,null,null,null,"Footpath",null,"You require excavation of the trench in a footpath by either Severn Trent or a Self Lay Provider"]
[null,null,null,null,null,null,null,"Type 3/4 CW",null,"You require excavation of the trench in a type 3/4 carriageway by either Severn Trent or a Self Lay Provider"]
[null,null,null,null,null,null,null,"Type 1/2 CW",null,"You require excavation of the trench in a type 1/2 carriageway by either Severn Trent or a Self Lay Provider"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"New PE EST (3)","index":7,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":false,"merged":[[1,1,3,21],[4,2,4,3],[4,4,4,5],[11,2,11,4]]}
[]
[null,"Calculate your estimated protective pipe charges\nThis estimate should be used for protective pipe schemes. "]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"PHYSICAL SOW CONNECTION"]
[]
[null,"Question 1",6,"What diameter connection do you need between the new mains and our existing mains (also known as SOW connection)?"]
[null,null,"* If you are unsure of your pipework diameter at this stage please select 150-200mm"]
[]
[null,"Answer 6","150-200",null,null,"mm"]
[]
[]
[]
[null,"EXISTING MAINS"]
[]
[null,"Question 2",7,"Are there any existing mains on site which need to be abandoned? (We refer to this as 'capping off')."]
[null,null,"* Please enter the number of each type of asset below"]
[]
[null,"Answer 7",null,"Asset to be capped off",null,null,null,null,null,"Quantity"]
[null,null,null,"Cap off Abandoned main up to 4\" in Tarmac/Footway"]
[null,null,null,"Cap off Abandoned main 4-6\" in Unmade"]
[null,null,null,"Cap off Abandoned main up to 4-6\" in Tarmac/Footway"]
[null,null,null,"Cap off Abandoned main up to 6-9\" in Tarmac/Footway"]
[null,null,null,"Cap off Abandoned main 6-9\" in Verge/Footway"]
[null,null,null,"Cap off Abandoned main 9-12\" in Tarmac/Footway"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"New PE SUM","index":8,"n_rows":42,"n_cols":21,"rows_kept":42,"cut":false,"hidden":false,"merged":[[30,3,30,4],[31,5,32,7],[33,5,34,7],[33,9,35,20],[34,3,34,4],[25,5,26,7],[25,9,27,20],[27,5,28,7],[146,6,148,6],[29,5,30,7],[29,9,31,20],[124,5,124,8],[125,5,125,8],[126,5,126,8],[127,5,127,8],[128,5,128,8],[21,1,23,7],[23,9,24,11],[1,1,3,21],[4,2,4,3],[4,4,4,5],[8,5,9,7],[7,9,9,20]]}
[]
[null,"Summary of protective pipe estimated charges\nThe total estimated cost for your scheme based on the information you've entered is shown below."]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV"]
[]
[]
[null,null,null,null,null,null,null,null,{"$f":"SUM('New PE EST'!G26+'New PE EST'!G27+'New PE EST'!G28+'New PE EST (2)'!C12)","v":222}]
[null,"Total cost (including SOW)",null,null,null,{"$f":"'PE Rates'!B239+'PE Rates'!B240","v":27934.744435827848}]
[]
[]
[]
[]
[null,"The SOW estimate covers the assets shown in the diagram:"]
[]
[]
[]
[]
[]
[]
[]
[null,"This total SOW cost is split by contestable and non-contestable elements below:"]
[]
[null,null,null,null,null,null,null,null,null,"Specific assumptions"]
[]
[null,"Total SOW cost",null,null,null,{"$f":"F30+F34","v":9501.6738655398603},null,null,null,"The SOW cost includes an allowance for valves, wash outs and trial holes. This SOW cost does include the length of main connecting into the connection itself based on the answer to Question 3. The SOW cost assumes temporary two way traffic lights are required."]
[]
[]
[]
[null,"Offsite pipework cost",null,null,null,{"$f":"'PE Rates'!C224+'PE Rates'!C228+'PE Rates'!C231","v":1550.7946559100606},null,null,null,"This pipework cost includes the cost of the length of pipework specified in response to Question 3 as well as the cost of sampling and a pressure test. It does not include any valves or other fittings. When Question 3 has been answered with zero, this cost assumes 2m of pipework is required."]
[null,null,null,"Contestable"]
[]
[]
[null,"Physical connection cost",null,null,null,{"$f":"'PE Rates'!C225+'PE Rates'!C232","v":7950.8792096298002},null,null,null,"This connection cost includes the physical connection, traffic management and fittings. This connection assumes you are connecting into an existing barrier main which is more expensive than connecting into a PE main, to understand the connection costs of connecting into a PE main please use the PE estimator."]
[null,null,null,"Non-contestable"]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"PE Rates","index":9,"n_rows":240,"n_cols":8,"rows_kept":240,"cut":false,"hidden":true,"merged":[[224,4,224,6],[225,4,225,6],[227,4,227,6],[228,4,228,6]]}
["Full description of required works",null,null,null,"CONCATENATE","ST charge (£)","Diameter","Excavation requirement"]
["Mains laying rates"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,{"$f":"CONCATENATE(G3,H3)","v":"No excavation"},0,null,"No excavation"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Unmade",0,null,"Unmade"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Footpath",0,null,"Footpath"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Type 3/4 CW",0,null,"Type 3/4 CW"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Type 1/2 CW",0,null,"Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 63mm NB No excavation",null,null,null,"up to 63No excavation",15.333640971000003,"up to 63","No excavation"]
["PE main laying, n.e 2m depth, up to 63mm NB in Unmade",null,null,null,"up to 63Unmade",38.170015494600008,"up to 63","Unmade"]
["PE main laying, n.e 2m depth, up to 63mm NB in Footpath",null,null,null,"up to 63Footpath",89.874373868400014,"up to 63","Footpath"]
["PE main laying, n.e 2m depth, up to 63mm NB in Type 3/4 CW",null,null,null,"up to 63Type 3/4 CW",118.31233679100001,"up to 63","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 63mm NB in Type 1/2 CW",null,null,null,"up to 63Type 1/2 CW",137.0748288426,"up to 63","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 90mm NB No excavation",null,null,null,"64 to 90No excavation",16.816081537200002,"64 to 90","No excavation"]
["PE main laying, n.e 2m depth, up to 90mm NB in Unmade",null,null,null,"64 to 90Unmade",39.663772401000003,"64 to 90","Unmade"]
["PE main laying, n.e 2m depth, up to 90mm NB in Footpath",null,null,null,"64 to 90Footpath",91.436028816000018,"64 to 90","Footpath"]
["PE main laying, n.e 2m depth, up to 90mm NB in Type 3/4 CW",null,null,null,"64 to 90Type 3/4 CW",119.94188977980002,"64 to 90","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 90mm NB in Type 1/2 CW",null,null,null,"64 to 90Type 1/2 CW",138.68174915100002,"64 to 90","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 125mm NB No excavation",null,null,null,"91 to 125No excavation",19.973340453000002,"91 to 125","No excavation"]
["PE main laying, n.e 2m depth, up to 125mm NB in Unmade",null,null,null,"91 to 125Unmade",42.798398636400002,"91 to 125","Unmade"]
["PE main laying, n.e 2m depth, up to 125mm NB in Footpath",null,null,null,"91 to 125Footpath",94.661185773000014,"91 to 125","Footpath"]
["PE main laying, n.e 2m depth, up to 125mm NB in Type 3/4 CW",null,null,null,{"$f":"CONCATENATE(G21,H21)","v":"91 to 125Type 3/4 CW"},123.22362843780002,"91 to 125","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 125mm NB in Type 1/2 CW",null,null,null,"91 to 125Type 1/2 CW",141.96348780900001,"91 to 125","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 180mm NB No excavation",null,null,null,"126 to 180No excavation",29.728025705400004,"126 to 180","No excavation"]
["PE main laying, n.e 2m depth, up to 180mm NB in Unmade",null,null,null,"126 to 180Unmade",57.47569187580001,"126 to 180","Unmade"]
["PE main laying, n.e 2m depth, up to 180mm NB in Footpath",null,null,null,"126 to 180Footpath",111.47726731020002,"126 to 180","Footpath"]
["PE main laying, n.e 2m depth, up to 180mm NB in Type 3/4 CW",null,null,null,"126 to 180Type 3/4 CW",145.21127744640003,"126 to 180","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 180mm NB in Type 1/2 CW",null,null,null,"126 to 180Type 1/2 CW",149.62465012440001,"126 to 180","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 200 mm NB No excavation",null,null,null,"181 to 200No excavation",37.932372350400016,"181 to 200","No excavation"]
["PE main laying, n.e 2m depth, up to 200mm NB in Unmade",null,null,null,"181 to 200Unmade",69.516277848599998,"181 to 200","Unmade"]
["PE main laying, n.e 2m depth, up to 200mm NB in Footpath",null,null,null,"181 to 200Footpath",123.18967941720003,"181 to 200","Footpath"]
["PE main laying, n.e 2m depth, up to 200mm NB in Type 3/4 CW",null,null,null,"181 to 200Type 3/4 CW",165.33173032200003,"181 to 200","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 200mm NB in Type 1/2 CW",null,null,null,"181 to 200Type 1/2 CW",176.18410057380004,"181 to 200","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 250 mm NB No excavation",null,null,null,"201 to 250No excavation",47.607843221400003,"201 to 250","No excavation"]
["PE main laying, n.e 2m depth, up to 250mm NB in Unmade",null,null,null,"201 to 250Unmade",81.387118718400018,"201 to 250","Unmade"]
["PE main laying, n.e 2m depth, up to 250mm NB in Footpath",null,null,null,"201 to 250Footpath",135.25289807040002,"201 to 250","Footpath"]
["PE main laying, n.e 2m depth, up to 250mm NB in Type 3/4 CW",null,null,null,"201 to 250Type 3/4 CW",184.95426422880001,"201 to 250","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 250mm NB in Type 1/2 CW",null,null,null,"201 to 250Type 1/2 CW",196.73457437700003,"201 to 250","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 355mm NB No excavation",null,null,null,"251 to 355No excavation",98.46347608020001,"251 to 355","No excavation"]
["PE main laying, n.e 2m depth, up to 355mm NB in Unmade",null,null,null,"251 to 355Unmade",129.44761554780001,"251 to 355","Unmade"]
["PE main laying, n.e 2m depth, up to 355mm NB in Footpath",null,null,null,"251 to 355Footpath",221.05338946680004,"251 to 355","Footpath"]
["PE main laying, n.e 2m depth, up to 355mm NB in Type 3/4 CW",null,null,null,"251 to 355Type 3/4 CW",262.78805212440005,"251 to 355","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 355mm NB in Type 1/2 CW",null,null,null,"251 to 355Type 1/2 CW",334.80524115720004,"251 to 355","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 450mm NB No excavation",null,null,null,"356 to 450No excavation",146.6824016724,"356 to 450","No excavation"]
["PE main laying, n.e 2m depth, up to 450mm NB in Unmade",null,null,null,"356 to 450Unmade",185.15795835240002,"356 to 450","Unmade"]
["PE main laying, n.e 2m depth, up to 450mm NB in Footpath",null,null,null,"356 to 450Footpath",293.00268045840005,"356 to 450","Footpath"]
["PE main laying, n.e 2m depth, up to 450mm NB in Type 3/4 CW",null,null,null,"356 to 450Type 3/4 CW",348.26036965500009,"356 to 450","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 450mm NB in Type 1/2 CW",null,null,null,"356 to 450Type 1/2 CW",432.71421656760003,"356 to 450","Type 1/2 CW"]
["Under pressure connections"]
["Under pressure connection. Body not exceeding 100mm NB.  No Excavation",null,null,null,{"$f":"CONCATENATE(G49,H49)","v":"up to 100No excavation"},955.95915473520006,"up to 100","No excavation"]
["Under pressure connection body not exceeding 100mm NB.  Unmade",null,null,null,"up to 100Unmade",1278.4409014146001,"up to 100","Unmade"]
["Under pressure connection. Body not exceeding 100mm NB.  Footpath",null,null,null,"up to 100Footpath",1630.4922450366003,"up to 100","Footpath"]
["Under pressure connection Body not exceeding 100mm NB.  Type 3/4 carriageway",null,null,null,"up to 100Type 3/4 CW",2062.5614302128001,"up to 100","Type 3/4 CW"]
["Under pressure connection Body not exceeding 100mm NB.  Type 1/2 carriageway",null,null,null,"up to 100Type 1/2 CW",2150.4328118658004,"up to 100","Type 1/2 CW"]
["Under pressure connection Body 150-200mm NB.  No Excavation",null,null,null,"150-200No excavation",1394.1731126400002,"150-200","No excavation"]
["Under pressure connection Body 150-200mm NB.  Unmade",null,null,null,"150-200Unmade",1813.4322007098003,"150-200","Unmade"]
["Under pressure connection Body 150-200mm NB.  Footpath",null,null,null,"150-200Footpath",2333.7688394460006,"150-200","Footpath"]
["Under pressure connection Body 150-200mm NB.  Type 3/4 carriageway",null,null,null,"150-200Type 3/4 CW",2722.1003697492006,"150-200","Type 3/4 CW"]
["Under pressure connection Body 150-200mm NB.  Type 1/2 carriageway",null,null,null,"150-200Type 1/2 CW",2827.5460277328002,"150-200","Type 1/2 CW"]
["Under pressure connection Body 225-300 NB.  No Excavation",null,null,null,"225-300No excavation",2388.6870384366002,"225-300","No excavation"]
["Under pressure connection Body 225-300 NB.  Unmade",null,null,null,"225-300Unmade",2990.7163370766002,"225-300","Unmade"]
["Under pressure connection Body 225-300 NB.  Footpath",null,null,null,"225-300Footpath",3544.1759035782002,"225-300","Footpath"]
["Under pressure connection Body 225-300 NB.  Type 3/4 carriageway",null,null,null,"225-300Type 3/4 CW",4137.4010895426009,"225-300","Type 3/4 CW"]
["Under pressure connection Body 225-300 NB.  Type 1/2 carriageway",null,null,null,"225-300Type 1/2 CW",4292.5028483238002,"225-300","Type 1/2 CW"]
["Under pressure connection Body 375-450 NB.  No Excavation",null,null,null,"375-450No excavation",6088.7342119296,"375-450","No excavation"]
["Under pressure connection Body 375-450 NB.  Unmade",null,null,null,"375-450Unmade",7234.4910244992006,"375-450","Unmade"]
["Under pressure connection Body 375-450 NB.  Footpath",null,null,null,"375-450Footpath",8530.8909578112016,"375-450","Footpath"]
["Under pressure connection Body 375-450 NB.  Type 3/4 carriageway",null,null,null,"375-450Type 3/4 CW",10626.2924072844,"375-450","Type 3/4 CW"]
["Under pressure connection Body 375-450 NB.  Type 1/2 carriageway",null,null,null,"375-450Type 1/2 CW",10903.8709160502,"375-450","Type 1/2 CW"]
["Valves, hydrants, wash outs"]
["Install SV/FH/WO/Tee up to 4\" No Excavation'",null,null,null,{"$f":"CONCATENATE(G70,H70)","v":"up to 63No excavation"},265.8434639784,"up to 63","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Unmade",null,null,null,"up to 63Unmade",371.22122392080013,"up to 63","Unmade"]
["Install SV/FH/WO/Tee up to 4\" in Footpath",null,null,null,"up to 63Footpath",454.43027341140009,"up to 63","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway",null,null,null,"up to 63Type 3/4 CW",480.88787679900008,"up to 63","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway",null,null,null,"up to 63Type 1/2 CW",505.53486575460005,"up to 63","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 4\" No Excavation'",null,null,null,"64 to 90No excavation",265.8434639784,"64 to 90","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Unmade",null,null,null,"64 to 90Unmade",371.22122392080013,"64 to 90","Unmade"]
["Install SV/FH/WO/Tee up to 4\" in Footpath",null,null,null,"64 to 90Footpath",454.43027341140009,"64 to 90","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway",null,null,null,"64 to 90Type 3/4 CW",480.88787679900008,"64 to 90","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway",null,null,null,"64 to 90Type 1/2 CW",505.53486575460005,"64 to 90","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 4\" No Excavation'",null,null,null,"91 to 125No excavation",265.8434639784,"91 to 125","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Unmade",null,null,null,"91 to 125Unmade",371.22122392080013,"91 to 125","Unmade"]
["Install SV/FH/WO/Tee up to 4\" in Footpath",null,null,null,"91 to 125Footpath",454.43027341140009,"91 to 125","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway",null,null,null,"91 to 125Type 3/4 CW",480.88787679900008,"91 to 125","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway",null,null,null,"91 to 125Type 1/2 CW",505.53486575460005,"91 to 125","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 6\" No Excavation",null,null,null,"126 to 180No excavation",435.21512775180003,"126 to 180","No excavation"]
["Install SV/FH/WO/Tee up to 6\" in Unmade",null,null,null,"126 to 180Unmade",410.64735317760005,"126 to 180","Unmade"]
["Install SV/FH/WO/Tee up to 6\" in Footpath",null,null,null,"126 to 180Footpath",495.21436349220011,"126 to 180","Footpath"]
["Install SV/FH/WO/Tee up to 6\" in Type 3/4 carriageway",null,null,null,"126 to 180Type 3/4 CW",525.20266502220011,"126 to 180","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 6\" in Type 1/2 carriageway",null,null,null,"126 to 180Type 1/2 CW",551.08313505960007,"126 to 180","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 9\" No Excavation",null,null,null,"181 to 200No excavation",668.10540906780011,"181 to 200","No excavation"]
["Install SV/FH/WO/Tee up to 9\" in Unmade",null,null,null,"181 to 200Unmade",629.58458702700011,"181 to 200","Unmade"]
["Install SV/FH/WO/Tee up to 9\" in Footpath",null,null,null,"181 to 200Footpath",721.40537140980018,"181 to 200","Footpath"]
["Install SV/FH/WO/Tee up to 9\" in Type 3/4 carriageway",null,null,null,"181 to 200Type 3/4 CW",756.61050577200012,"181 to 200","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 9\" in Type 1/2 carriageway",null,null,null,"181 to 200Type 1/2 CW",783.95078369520002,"181 to 200","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 9\" No Excavation",null,null,null,"201 to 250No excavation",668.10540906780011,"201 to 250","No excavation"]
["Install SV/FH/WO/Tee up to 9\" in Unmade",null,null,null,"201 to 250Unmade",629.58458702700011,"201 to 250","Unmade"]
["Install SV/FH/WO/Tee up to 9\" in Footpath",null,null,null,"201 to 250Footpath",721.40537140980018,"201 to 250","Footpath"]
["Install SV/FH/WO/Tee up to 9\" in Type 3/4 carriageway",null,null,null,"201 to 250Type 3/4 CW",756.61050577200012,"201 to 250","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 9\" in Type 1/2 carriageway",null,null,null,"201 to 250Type 1/2 CW",783.95078369520002,"201 to 250","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 12\" No Excavation",null,null,null,"251 to 355No excavation",1299.0253242384001,"251 to 355","No excavation"]
["Install SV/FH/WO/Tee up to 12\" in Unmade",null,null,null,"251 to 355Unmade",1428.9595424148004,"251 to 355","Unmade"]
["Install SV/FH/WO/Tee up to 12\" in Footpath",null,null,null,"251 to 355Footpath",1539.1467469422,"251 to 355","Footpath"]
["Install SV/FH/WO/Tee up to 12\" in Type 3/4 carriageway",null,null,null,"251 to 355Type 3/4 CW",1589.5610425332002,"251 to 355","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 12\" in Type 1/2 carriageway",null,null,null,"251 to 355Type 1/2 CW",1619.7077728260003,"251 to 355","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 18\" No Excavation",null,null,null,"356 to 450No excavation",4911.3708611814009,"356 to 450","No excavation"]
["Install SV/FH/WO/Tee up to 18\" in Unmade",null,null,null,"356 to 450Unmade",5288.0352447384012,"356 to 450","Unmade"]
["Install SV/FH/WO/Tee up to 18\" in Footpath",null,null,null,"356 to 450Footpath",5577.0206244258006,"356 to 450","Footpath"]
["Install SV/FH/WO/Tee up to 18\" in Type 3/4 carriageway",null,null,null,"356 to 450Type 3/4 CW",5641.648243308,"356 to 450","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 18\" in Type 1/2 carriageway",null,null,null,"356 to 450Type 1/2 CW",7215.9661755918014,"356 to 450","Type 1/2 CW"]
["Cap offs"]
["Cap off Abandoned main up to 4\" in Tarmac/Footway",null,null,null,null,454.91687604000003,"4\"","Tarmac/footway"]
["Cap off Abandoned main 4-6\" in Unmade",null,null,null,null,349.79939192220007,"4 - 6\"","Unmade"]
["Cap off Abandoned main up to 4-6\" in Tarmac/Footway",null,null,null,null,517.65466610880003,"4 - 6\"","Tarmac/footway"]
["Cap off Abandoned main up to 6-9\" in Tarmac/Footway",null,null,null,null,678.93514663920018,"6 - 9\"","Tarmac/footway"]
["Cap off Abandoned main 6-9\" in Verge/Footway",null,null,null,null,620.29387172280008,"6 - 9\"","Verge/footway"]
["Cap off Abandoned main 9-12\" in Tarmac/Footway",null,null,null,null,877.29927400500003,"9 - 12\"","Tarmac/footway"]
["Pressure Test  & Chlorination"]
["Pressure Test  & Chlorination",null,null,null,null,566.2583472678001]
["Trial hole"]
["Trial hole in unmade",null,null,null,"Unmade",93.461653711800011,null,"Unmade"]
["Trial hole in Footpath",null,null,null,"Footpath",174.11321031720004,null,"Footpath"]
["Trial hole in type 3/4",null,null,null,"Type 3/4 CW",254.00657212920004,null,"Type 3/4 CW"]
["Trial hole in type 2/1",null,null,null,"Type 1/2 CW",371.68519386900005,null,"Type 1/2 CW"]
["Shut off"]
["Shut off (Turnkey attendance)",null,null,null,null,399.62523782280005]
["B2B"]
["Back to back connection; includes removal of end hydrants, maximum connection distance 2m; not exceeding 125mm diameter",null,null,null,null,576.22804298400001]
["Back to back connection; includes removal of end hydrants, maximum connection distance 2m; not exceeding 180mm diameter",null,null,null,null,601.16925678480004]
["Sampling"]
["Sampling; where not included within a turnkey operation - applies to separate site visit solely for the purpose of sampling, as instructed",null,null,null,null,154.95464635860003]
["Traffic lights"]
["2 way temporary traffic lights",null,null,null,null,541.27186810620015]
["3 way temporary traffic lights",null,null,null,null,618.01928734260002]
["4 way temporary traffic lights",null,null,null,null,685.41940957380018]
[]
[]
["Pipe diameter drop down options"]
[]
["up to 63"]
["64 to 90"]
["91 to 125"]
["126 to 180"]
["181 to 200"]
["201 to 250"]
["251 to 355"]
["356 to 450"]
[]
["Excavation drop down options"]
[]
["No excavation"]
["Unmade"]
["Footpath"]
["Type 3/4 CW"]
["Type 1/2 CW"]
[]
["Traffic management drop down options"]
["2 way temporary traffic lights"]
["3 way temporary traffic lights"]
["4 way temporary traffic lights"]
[]
["PE lay type drop down options"]
["Lay only"]
["Open cut"]
[]
["PE offsite excavation drop down options"]
["No excavation"]
["Excavation in unmade ground"]
["Excavation in the highway"]
[]
["Pop up for PE <5 or >250 plots"]
["You have selected a number of plots which is less than 5 or over 250, this estimator tool is not applicable for this number of plots so you will require a bespoke quote for your site."]
[]
["Concatenate for PE onsite mains laying rate 1"]
[{"$f":"CONCATENATE('New PE EST'!C26,'New PE EST'!C35)","v":"64 to 90Unmade"},null,"Cost of onsite barrier length 1",null,{"$f":"(VLOOKUP(A174,E3:F47,2,FALSE))*'New PE EST'!G26","v":3966.3772401000001}]
[]
["Concatenate for PE onsite mains laying rate 2"]
[{"$f":"CONCATENATE('New PE EST'!C27,'New PE EST'!C35)","v":"up to 63Unmade"},null,"Cost of onsite barrier length 2",null,{"$f":"(VLOOKUP(A177,E3:F47,2,FALSE))*'New PE EST'!G27","v":4542.2318438574011}]
[]
["Concatenate for PE onsite mains laying rate 3"]
[{"$f":"CONCATENATE('New PE EST'!C28,'New PE EST'!C35)","v":"Unmade"},null,"Cost of onsite barrier length 3",null,{"$f":"(VLOOKUP(A180,E3:F47,2,FALSE))*'New PE EST'!G28","v":0}]
[]
["Concatenate for PE onsite mains laying rate 4"]
[{"$f":"CONCATENATE('New PE EST'!C29,'New PE EST'!C35)","v":"Unmade"},null,"Cost of onsite barrier length 3",null,{"$f":"(VLOOKUP(A183,E3:F47,2,FALSE))*'New PE EST'!G29","v":0}]
["Concatenate for PE offsite mains laying rate"]
[{"$f":"CONCATENATE('New PE EST (2)'!C17,'New PE EST (2)'!C24)","v":"126 to 180Type 3/4 CW"}]
[]
["Concatenate for PE under pressure connections"]
[{"$f":"CONCATENATE('New PE EST (3)'!C12,'New PE EST (2)'!C24)","v":"150-200Type 3/4 CW"}]
[]
["Valves and uplift at SOW barrier (adjustment post audit and testing)"]
["Concatenate for offsite valve"]
[{"$f":"CONCATENATE('New PE EST (2)'!C17,'New PE EST (2)'!C24)","v":"126 to 180Type 3/4 CW"}]
["SOW valve charge (per unit)",{"$f":"VLOOKUP(A192,E70:F109,2,FALSE)","v":525.20266502220011}]
["SOW valve charge for 3 (assume 3 at SOW)",{"$f":"B193*3","v":1575.6079950666003}]
[]
["Flat uplift as per audit/sample averages (16.77% increase)",1.1677]
[]
["SOW cost for simply UPT",{"$f":"(VLOOKUP('PE Rates'!A188,'PE Rates'!E49:F68,2,FALSE))","v":2722.1003697492006}]
["Traffic mgt",{"$f":"F132","v":541.27186810620015}]
["Adding valves",{"$f":"B198+B199+B194","v":4838.9802329220011}]
[]
["Concatenate for Barrier under pressure connections"]
["up to 100"]
["150-200",null,"Number of pressure tests - ensuring no less than 0",null,null,{"$f":"MAX(1,('New PE EST'!G26+'New PE EST'!G27+'New PE EST'!G28+'New PE EST'!G29)/100)","v":2.19}]
["225-300",null,"Ensure rounding to a single figure",null,null,{"$f":"ROUND(F204,0)","v":2}]
["375-450"]
[]
["Concatenate for valves, hydrants and wash outs",null,"Length",null,"Divided by 100m",null,"Rounding to a single figure","Add two"]
[{"$f":"CONCATENATE('New PE EST'!C26,'New PE EST'!C35)","v":"64 to 90Unmade"},{"$f":"IF(ISNA(VLOOKUP('PE Rates'!A209,'PE Rates'!$E$70:$F$109,2,FALSE)*H209),\"0.00\",VLOOKUP('PE Rates'!A209,'PE Rates'!$E$70:$F$109,2,FALSE)*H209)","v":2227.3273435248007},{"$f":"'New PE EST'!G26","v":100},"m",{"$f":"C209/25","v":4},null,{"$f":"ROUND(E209,0)","v":4},{"$f":"G209+2","v":6}]
[{"$f":"CONCATENATE('New PE EST'!C27,'New PE EST'!C35)","v":"up to 63Unmade"},{"$f":"IF(ISNA(VLOOKUP('PE Rates'!A210,'PE Rates'!$E$70:$F$109,2,FALSE)*H210),\"0.00\",VLOOKUP('PE Rates'!A210,'PE Rates'!$E$70:$F$109,2,FALSE)*H210)","v":2598.5485674456008},{"$f":"'New PE EST'!G27","v":119},"m",{"$f":"C210/25","v":4.76},null,{"$f":"ROUND(E210,0)","v":5},{"$f":"G210+2","v":7}]
[{"$f":"CONCATENATE('New PE EST'!C28,'New PE EST'!C35)","v":"Unmade"},{"$f":"IF(ISNA(VLOOKUP('PE Rates'!A211,'PE Rates'!$E$70:$F$109,2,FALSE)*H211),\"0.00\",VLOOKUP('PE Rates'!A211,'PE Rates'!$E$70:$F$109,2,FALSE)*H211)","v":"0.00"},{"$f":"'New PE EST'!G28","v":0},"m",0,null,0,2]
[{"$f":"CONCATENATE('New PE EST'!C29,'New PE EST'!C35)","v":"Unmade"},{"$f":"IF(ISNA(VLOOKUP('PE Rates'!A212,'PE Rates'!$E$70:$F$109,2,FALSE)*H212),\"0.00\",VLOOKUP('PE Rates'!A212,'PE Rates'!$E$70:$F$109,2,FALSE)*H212)","v":"0.00"},{"$f":"'New PE EST'!G29","v":0},"m",0,null,0,2]
[null,null,{"$f":"SUM(C209:C212)","v":219},"m",{"$f":"SUM(E209:E212)","v":8.76},null,{"$f":"SUM(G209:G212)","v":9},11]
[]
["How many required (1 per 100m + 2)"]
[]
[]
[]
[null,{"$f":"B236+B237","v":31038.604928697605},null,"Includes SOW costs"]
[]
["Onsite mains laying cost",{"$f":"'PE Rates'!E174+'PE Rates'!E177+'PE Rates'!E180+E183","v":8508.6090839574008},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B221*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B221*1))","v":7657.7481755616609}]
["Offsite mains laying cost - if above 2m",{"$f":"(VLOOKUP('PE Rates'!A185,'PE Rates'!E8:F47,2,FALSE))*'New PE EST (2)'!C12","v":435.63383233920013},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B222*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B222*1))","v":392.07044910528015}]
["Offsite mains laying cost - if below 2m",{"$f":"(VLOOKUP('PE Rates'!A185,'PE Rates'!E8:F47,2,FALSE))*2","v":290.42255489280006},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B223*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B223*1))","v":261.38029940352004}]
["Offsite mains laying cost - pull through cell",{"$f":"IF('New PE EST (2)'!C12<2,'PE Rates'!B223,'PE Rates'!B222)","v":435.63383233920013},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B224*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B224*1))","v":392.07044910528015}]
["SOW connection cost + TM + valves",{"$f":"B200","v":4838.9802329220011},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B225*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B225*1))","v":4355.0822096298007},null,"This does not include any length of main connecting into the connection itself (this would shown within the total 'offsite mains laying cost'). This does include the physical connection, traffic mgt and valves at the SOW connection. This estimator assumes a SOW cost in each estimate rather than a 'back-to-back'."]
["Valves, hydrants, wash outs along the length of the mains",{"$f":"SUM(B209:B212)*1.5","v":7238.8138664556027},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B226*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B226*1))","v":6514.9324798100424},null,"This estimator assumes 1.5 valve for every 100m of onsite mains plus two extra at each end of the total mains length"]
["Cap offs",{"$f":"('New PE EST (3)'!J22*'PE Rates'!F111)+('New PE EST (3)'!J23*'PE Rates'!F112)+('New PE EST (3)'!J24*'PE Rates'!F113)+('New PE EST (3)'!J25*'PE Rates'!F114)+('New PE EST (3)'!J26*'PE Rates'!F115)+('New PE EST (3)'!J27*'PE Rates'!F116)","v":0},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B227*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B227*1))","v":0}]
["Pressure test",{"$f":"'PE Rates'!F118*'PE Rates'!F205","v":1132.5166945356002},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B228*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B228*1))","v":1019.2650250820402},null,"This estimator assumes 1 pressure test per 100m of onsite mains"]
["Trial holes",{"$f":"VLOOKUP('New PE EST (2)'!C24,'PE Rates'!E120:F123,2,FALSE)","v":254.00657212920004},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B229*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B229*1))","v":228.60591491628003},null,"This estimator assumes 1 trial hole within each estimate"]
["B2B average",588.70000000000005,{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B230*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B230*1))","v":529.83000000000004}]
["Sampling",154.95464635860003,{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B231*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B231*1))","v":139.45918172274003}]
["UPT Average",3995.33,{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B232*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B232*1))","v":3595.797}]
["Connection valves Average",{"$f":"1297.02*3","v":3891.06},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B233*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B233*1))","v":3501.9540000000002}]
[null,null,{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B234*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B234*1))","v":0}]
["SOW totals",null,{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B235*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B235*1))","v":0}]
["Physical connection total",{"$f":"B221+B225+B226+B229+B230","v":21429.109755464207},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B236*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B236*1))","v":19286.198779917788}]
["Offsite mains total",{"$f":"B224+B228+B231+B232+B233","v":9609.4951732333993},{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B237*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B237*1))","v":8648.5456559100603}]
[]
["Physical connection total V1",{"$f":"IF(SUM('New PE EST'!$G$26:$I$29)<500,B236*0.9,IF(SUM('New PE EST'!$G$26:$I$29)>=500,B236*1))","v":19286.198779917788},null,null,"10% reduction made for schemes <500"]
["Offsite mains total V1",{"$f":"IF(SUM('New PE EST'!G26:I29)<500,B237*0.9,IF(SUM('New PE EST'!G26:I29)>=500,B237*1))","v":8648.5456559100603},null,null,"10% reduction made for schemes <500"]

{"sheet":"BARR EST","index":10,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":false,"merged":[[34,8,35,19],[1,1,3,21],[4,2,4,3],[4,4,4,5],[26,2,26,4],[32,2,32,4],[28,6,28,8],[6,1,7,20],[8,1,10,7],[26,10,28,19],[27,2,27,4],[28,2,28,4],[26,6,26,8],[27,6,27,8],[88,5,88,8],[89,5,89,8],[90,5,90,8],[108,6,110,6],[86,5,86,8],[87,5,87,8]]}
[]
[null,"Calculate your estimated protective pipe charges\nThis estimate should be used for protective pipe schemes. "]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"Our charging structure for protective pipe schemes is bespoke and therefore we ask you a few more questions here than we do in the PE estimator so we can put together an estimate based on our more granular protective pipe charges."]
[]
[null,"The diagram to the right shows a typical mains site and highlights which assets are being referenced in each question."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"ASSETS WITHIN YOUR SITE BOUNDARY"]
[]
[null,"Question 1",null,"What is the estimated length of main on your development site (within the boundaries of your site)?"]
[null,null,"Often development sites require mains pipework of two or three different diameters, if you require more than one diameter of mains please specify the lengths of each below."]
[null,null,"* If you only require one diameter of on-site pipework within this estimate simply enter the total length & relevant diameter in the top row of three below"]
[null,null,"* If you are unsure of your pipework diameter at this stage please select 126 to 180mm and enter the full length of mains within your development site alongside this diameter"]
[]
[null,null,"Diameter of pipe",null,null,null,"Length of each diameter"]
[null,"Answer 1","126 to 180",null,null,"mm",1000,null,null,"m","Please ensure at least one of the three pairs of answer cells have been populated. If a diameter has been selected but a length hasn't been entered or vice versa the 'on-site mains laying cost' shown in your estimate will be zero."]
[null,null,null,null,null,"mm",null,null,null,"m"]
[null,null,null,null,null,"mm",null,null,null,"m"]
[]
[null,"Question 2",null,"Do you require us to simply lay your new main (lay only) or excavate the trench and lay the main (open cut)?"]
[]
[null,"Answer 2","Unmade",null,null,null,"Option",null,"Description"]
[null,null,null,null,null,null,"No Excavation",null,"You do not require Severn Trent or a Self Lay Provider to excavate the trench for the pipework"]
[null,null,null,null,null,null,"Unmade",null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)"]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"BARR EST (2)","index":11,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":false,"merged":[[1,1,3,21],[4,2,4,3],[4,4,4,5],[24,9,25,20],[26,9,26,20],[27,9,27,20],[28,9,28,20],[11,2,11,4],[23,2,23,4],[20,2,21,18],[23,7,23,8],[23,9,23,20],[24,7,25,8],[16,2,16,4]]}
[]
[null,"Calculate your estimated protective pipe charges\nThis estimate should be used for protective pipe schemes. "]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"OFF-STE MAINS 'CONNECTION' LENGTH"]
[]
[null,"Question 1",3,"What is the estimated length of offsite main (from the edge of your development site to our existing network? ie distance to the SOW connection)"]
[null,null,"* If you're unsure of the length at this stage enter the width of the adjacent road"]
[]
[null,"Answer 3",16,null,null,"m"]
[]
[null,"Question 2",4,"What diameter of mains pipework do you require off-site?"]
[null,null,"* If you're unsure of the diameter at this stage please select 126 to 180mm"]
[]
[null,"Answer 4","126 to 180",null,null,"mm"]
[]
[null,"Question 2",5,"What are the excavation requirements off-site?"]
[null,null,"* This is solely for the length of mains between the edge of your development site and our existing network"]
[null,null,"* If you are unsure what your excavation requirements will be select the worst case scenario. This is likely to be 'type 1/2 carriageway' for developers (highest charge)"]
[]
[]
[null,"Answer 5","Type 3/4 CW",null,null,null,null,"Option",null,"Description"]
[null,null,null,null,null,null,null,"Unmade",null,"You require Severn Trent or a Self Lay Provider to excavate the trench for the pipework in unmade ground. Unmade ground typically covers areas like grass verges where soft digs are required (ie there is no tarmac on the surface)"]
[]
[null,null,null,null,null,null,null,"Footpath",null,"You require excavation of the trench in a footpath by either Severn Trent or a Self Lay Provider"]
[null,null,null,null,null,null,null,"Type 3/4 CW",null,"You require excavation of the trench in a type 3/4 carriageway by either Severn Trent or a Self Lay Provider"]
[null,null,null,null,null,null,null,"Type 1/2 CW",null,"You require excavation of the trench in a type 1/2 carriageway by either Severn Trent or a Self Lay Provider"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"BARR EST (3)","index":12,"n_rows":42,"n_cols":22,"rows_kept":42,"cut":false,"hidden":false,"merged":[[1,1,3,21],[4,2,4,3],[4,4,4,5],[11,2,11,4]]}
[]
[null,"Calculate your estimated protective pipe charges\nThis estimate should be used for protective pipe schemes. "]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV","NEXT>>"]
[]
[null,"PHYSICAL SOW CONNECTION"]
[]
[null,"Question 1",6,"What diameter connection do you need between the new mains and our existing mains (also known as SOW connection)?"]
[null,null,"* If you are unsure of your pipework diameter at this stage please select 150-200mm"]
[]
[null,"Answer 6","150-200",null,null,"mm"]
[]
[]
[]
[null,"EXISTING MAINS"]
[]
[null,"Question 2",7,"Are there any existing mains on site which need to be abandoned? (We refer to this as 'capping off')."]
[null,null,"* Please enter the number of each type of asset below"]
[]
[null,"Answer 7",null,"Asset to be capped off",null,null,null,null,null,"Quantity"]
[null,null,null,"Cap off Abandoned main up to 4\" in Tarmac/Footway"]
[null,null,null,"Cap off Abandoned main 4-6\" in Unmade"]
[null,null,null,"Cap off Abandoned main up to 4-6\" in Tarmac/Footway"]
[null,null,null,"Cap off Abandoned main up to 6-9\" in Tarmac/Footway"]
[null,null,null,"Cap off Abandoned main 6-9\" in Verge/Footway"]
[null,null,null,"Cap off Abandoned main 9-12\" in Tarmac/Footway"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"BARR SUM","index":13,"n_rows":42,"n_cols":21,"rows_kept":42,"cut":false,"hidden":false,"merged":[[1,1,3,21],[4,2,4,3],[4,4,4,5],[23,9,23,11],[7,5,8,7],[7,9,9,20],[128,5,128,8],[146,6,148,6],[124,5,124,8],[125,5,125,8],[126,5,126,8],[127,5,127,8],[27,5,28,7],[31,5,32,7],[30,3,30,4],[34,3,34,4],[25,9,27,20],[29,9,31,20],[33,9,35,20],[25,5,26,7],[29,5,30,7],[33,5,34,7]]}
[]
[null,"Summary of protective pipe estimated charges\nThe total estimated cost for your scheme based on the information you've entered is shown below."]
[]
[]
[null,"HOME","PE CHARGES",null,"PROTECTIVE PIPE CHARGES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<<PREV"]
[]
[]
[null,"Total cost (including SOW)",null,null,null,{"$f":"IF(I8=0,\"£0.00\",SUM(Rates!C194+SUM(Rates!C197:C204))*1.1)","v":127070.03019259278},null,null,{"$f":"SUM('BARR EST'!G27+'BARR EST'!G28+'BARR EST'!G29+'BARR EST (2)'!C12)","v":1016}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["This total SOW cost is split by contestable and non-contestable elements below:",null,null,null,null,null,null,null,null,"Specific assumptions"]
[]
[null,"Total SOW cost",null,null,null,{"$f":"F30+F34","v":8021.3760752382223},null,null,null,"The SOW cost includes an allowance for valves, wash outs and trial holes. This SOW cost does include the length of main connecting into the connection itself based on the answer to Question 3. The SOW cost assumes temporary two way traffic lights are required."]
[]
[]
[]
[null,"Offsite pipework cost",null,null,null,{"$f":"IF(F8=\"£0.00\",\"£0.00\",Rates!C210)*0.1","v":1651.4725963273404},null,null,null,"This pipework cost includes the cost of the length of pipework specified in response to Question 3 as well as the cost of sampling and a pressure test. It does not include any valves or other fittings. When Question 3 has been answered with zero, this cost assumes 2m of pipework is required."]
[null,null,null,"Contestable"]
[]
[]
[null,"Physical connection cost",null,null,null,{"$f":"IF(F8=\"£0.00\",\"£0.00\",Rates!C209)*0.9","v":6369.9034789108819},null,null,null,"This connection cost includes the physical connection, traffic management and fittings. This connection assumes you are connecting into an existing barrier main which is more expensive than connecting into a PE main, to understand the connection costs of connecting into a PE main please use the PE estimator."]
[null,null,null,"Non-contestable"]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"© Severn Trent Water 2019"]

{"sheet":"Rates","index":14,"n_rows":363,"n_cols":8,"rows_kept":363,"cut":false,"hidden":true,"merged":[[197,4,197,6],[198,4,198,6],[200,4,200,6],[201,4,201,6]]}
["Full description of required works","Code","Unit of measure",null,"CONCATENATE","ST charge (£)","Diameter","Excavation requirement"]
["Mains laying rates"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,{"$f":"CONCATENATE(G3,H3)","v":"No excavation"},0,null,"No excavation"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Unmade",0,null,"Unmade"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Footpath",0,null,"Footpath"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Type 3/4 CW",0,null,"Type 3/4 CW"]
["Blank dummy lines to enable formula for onsite mains cost to work",null,null,null,"Type 1/2 CW",0,null,"Type 1/2 CW"]
["Puriton main laying, n.e 2m depth, up to 125mm NB No excavation","DR051","m",null,"up to 125No excavation",55.0653114132,"up to 125","No excavation"]
["Puriton main laying, n.e 2m depth, up to 125mm NB in Unmade","DR052","m",null,"up to 125Unmade",71.417423002199996,"up to 125","Unmade"]
["Puriton main laying, n.e 2m depth, up to 125mm NB in Footpath","DR053","m",null,"up to 125Footpath",123.50653694280003,"up to 125","Footpath"]
["Puriton main laying, n.e 2m depth, up to 125mm NB in Type 3/4 CW","DR054","m",null,"up to 125Type 3/4 CW",140.82053744880002,"up to 125","Type 3/4 CW"]
["Puriton main laying, n.e 2m depth, up to 125mm NB in Type 1/2 CW","DR055","m",null,"up to 125Type 1/2 CW",181.49146412760004,"up to 125","Type 1/2 CW"]
["Puriton main laying, n.e 2m depth, up to 180mm NB No excavation","DR056","m",null,"126 to 180No excavation",71.971923672000017,"126 to 180","No excavation"]
["Puriton main laying, n.e 2m depth, up to 180mm NB in Unmade","DR057","m",null,"126 to 180Unmade",92.420550413400022,"126 to 180","Unmade"]
["Puriton main laying, n.e 2m depth, up to 180mm NB in Footpath","DR058","m",null,"126 to 180Footpath",154.75095223500003,"126 to 180","Footpath"]
["Puriton main laying, n.e 2m depth, up to 180mm NB in Type 3/4 CW","DR059","m",null,"126 to 180Type 3/4 CW",175.67486526480005,"126 to 180","Type 3/4 CW"]
["Puriton main laying, n.e 2m depth, up to 180mm NB in Type 1/2 CW","DR060","m",null,"126 to 180Type 1/2 CW",225.42149678400003,"126 to 180","Type 1/2 CW"]
["Puriton main laying, n.e 2m depth, up to 200 mm NB No excavation","DR061","m",null,"181 to 200No excavation",102.88816509840002,"181 to 200","No excavation"]
["Puriton main laying, n.e 2m depth, up to 200 mm NB in Unmade","DR062","m",null,"181 to 200Unmade",127.42199065200001,"181 to 200","Unmade"]
["Puriton main laying, n.e 2m depth, up to 200mm NB in Footpath","DR063","m",null,"181 to 200Footpath",218.065875654,"181 to 200","Footpath"]
["Puriton main laying, n.e 2m depth, up to 200mm NB in Type 3/4 CW","DR064","m",null,"181 to 200Type 3/4 CW",250.43060862600007,"181 to 200","Type 3/4 CW"]
["Puriton main laying, n.e 2m depth, up to 200mm NB in Type 1/2 CW","DR065","m",null,"181 to 200Type 1/2 CW",321.14641853580008,"181 to 200","Type 1/2 CW"]
["Puriton main laying, n.e 2m depth, up to 250 mm NB No excavation","DR066","m",null,"201 to 250No excavation",133.17069147360002,"201 to 250","No excavation"]
["Puriton main laying, n.e 2m depth, up to 250 mm NB in Unmade","DR067","m",null,"201 to 250Unmade",163.20425836440003,"201 to 250","Unmade"]
["Puriton main laying, n.e 2m depth, up to 250mm NB in Footpath","DR068","m",null,"201 to 250Footpath",256.59801403500006,"201 to 250","Footpath"]
["Puriton main laying, n.e 2m depth, up to 250mm NB in Type 3/4 CW","DR069","m",null,"201 to 250Type 3/4 CW",292.98004777799997,"201 to 250","Type 3/4 CW"]
["Puriton main laying, n.e 2m depth, up to 250mm NB in Type 1/2 CW","DR070","m",null,"201 to 250Type 1/2 CW",351.07813836480005,"201 to 250","Type 1/2 CW"]
["Puriton main laying, n.e 2m depth, up to 355mm NB No excavation","DR071","m",null,"251 to 355No excavation",179.94112552020002,"251 to 355","No excavation"]
["Puriton main laying, n.e 2m depth, up to 355mm NB in Unmade","DR072","m",null,"251 to 355Unmade",217.12661941740004,"251 to 355","Unmade"]
["Puriton main laying, n.e 2m depth, up to 355mm NB in Footpath","DR073","m",null,"251 to 355Footpath",347.28716439780004,"251 to 355","Footpath"]
["Puriton main laying, n.e 2m depth, up to 355mm NB in Type 3/4 CW","DR074","m",null,"251 to 355Type 3/4 CW",408.27092173559998,"251 to 355","Type 3/4 CW"]
["Puriton main laying, n.e 2m depth, up to 355mm NB in Type 1/2 CW","DR075","m",null,"251 to 355Type 1/2 CW",511.22698487520006,"251 to 355","Type 1/2 CW"]
["Puriton main laying, n.e 2m depth, up to 450mm NB No excavation","DR076","m",null,"356 to 450No excavation",263.03701160880001,"356 to 450","No excavation"]
["Puriton main laying, n.e 2m depth, up to 450mm NB in Unmade","DR077","m",null,"356 to 450Unmade",309.19636328460007,"356 to 450","Unmade"]
["Puriton main laying, n.e 2m depth, up to 450mm NB in Footpath","DR078","m",null,"356 to 450Footpath",441.41648218140006,"356 to 450","Footpath"]
["Puriton main laying, n.e 2m depth, up to 450mm NB in Type 3/4 CW","DR079","m",null,"356 to 450Type 3/4 CW",521.35510935420007,"356 to 450","Type 3/4 CW"]
["Puriton main laying, n.e 2m depth, up to 450mm NB in Type 1/2 CW","DR080","m",null,"356 to 450Type 1/2 CW",643.33394037000005,"356 to 450","Type 1/2 CW"]
["Under pressure connections"]
["Under pressure connection. Body not exceeding 100mm NB.  No Excavation","DR086","nr",null,{"$f":"CONCATENATE(G39,H39)","v":"up to 100No excavation"},1419.9291029352003,"up to 100","No excavation"]
["Under pressure connection body not exceeding 100mm NB.  Unmade","DR087","nr",null,"up to 100Unmade",1742.4108496146002,"up to 100","Unmade"]
["Under pressure connection. Body not exceeding 100mm NB.  Footpath","DR088","nr",null,"up to 100Footpath",2094.4621932366003,"up to 100","Footpath"]
["Under pressure connection Body not exceeding 100mm NB.  Type 3/4 carriageway","DR089","nr",null,"up to 100Type 3/4 CW",2526.5313784128007,"up to 100","Type 3/4 CW"]
["Under pressure connection Body not exceeding 100mm NB.  Type 1/2 carriageway","DR090","nr",null,"up to 100Type 1/2 CW",2614.4027600658001,"up to 100","Type 1/2 CW"]
["Under pressure connection Body 150-200mm NB.  No Excavation","DR092","nr",null,"150-200No excavation",2790.1568397120004,"150-200","No excavation"]
["Under pressure connection Body 150-200mm NB.  Unmade","DR093","nr",null,"150-200Unmade",3209.4159277818007,"150-200","Unmade"]
["Under pressure connection Body 150-200mm NB.  Footpath","DR094","nr",null,"150-200Footpath",3729.7525665180005,"150-200","Footpath"]
["Under pressure connection Body 150-200mm NB.  Type 3/4 carriageway","DR095","nr",null,"150-200Type 3/4 CW",4118.0840968212005,"150-200","Type 3/4 CW"]
["Under pressure connection Body 150-200mm NB.  Type 1/2 carriageway","DR096","nr",null,"150-200Type 1/2 CW",4223.5297548048002,"150-200","Type 1/2 CW"]
["Under pressure connection Body 225-300 NB.  No Excavation","DR098","nr",null,"225-300No excavation",11087.5464338364,"225-300","No excavation"]
["Under pressure connection Body 225-300 NB.  Unmade","DR099","nr",null,"225-300Unmade",11689.5757324764,"225-300","Unmade"]
["Under pressure connection Body 225-300 NB.  Footpath","DR100","nr",null,"225-300Footpath",12243.035298978,"225-300","Footpath"]
["Under pressure connection Body 225-300 NB.  Type 3/4 carriageway","DR101","nr",null,"225-300Type 3/4 CW",12836.260484942401,"225-300","Type 3/4 CW"]
["Under pressure connection Body 225-300 NB.  Type 1/2 carriageway","DR102","nr",null,"225-300Type 1/2 CW",12991.362243723603,"225-300","Type 1/2 CW"]
["Under pressure connection Body 375-450 NB.  No Excavation","DR104","nr",null,"375-450No excavation",23402.089686538202,"375-450","No excavation"]
["Under pressure connection Body 375-450 NB.  Unmade","DR105","nr",null,"375-450Unmade",24547.846499107804,"375-450","Unmade"]
["Under pressure connection Body 375-450 NB.  Footpath","DR106","nr",null,"375-450Footpath",25844.246432419804,"375-450","Footpath"]
["Under pressure connection Body 375-450 NB.  Type 3/4 carriageway","DR107","nr",null,"375-450Type 3/4 CW",27939.647881893005,"375-450","Type 3/4 CW"]
["Under pressure connection Body 375-450 NB.  Type 1/2 carriageway","DR108","nr",null,"375-450Type 1/2 CW",28217.226390658809,"375-450","Type 1/2 CW"]
["Valves, hydrants, wash outs"]
["Install SV/FH/WO/Tee up to 4\" No Excavation","DR110","nr",null,"up to 125No excavation",265.8434639784,"up to 125","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Unmade","DR111","nr",null,"up to 125Unmade",371.22122392080013,"up to 125","Unmade"]
["Install SV/FH/WO/Tee up to 4\" in Footpath","DR112","nr",null,"up to 125Footpath",454.43027341140009,"up to 125","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway","DR113","nr",null,"up to 125Type 3/4 CW",480.88787679900008,"up to 125","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway","DR114","nr",null,"up to 125Type 1/2 CW",505.53486575460005,"up to 125","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 6\" No Excavation","DR115","nr",null,"126 to 180No excavation",435.21512775180003,"126 to 180","No excavation"]
["Install SV/FH/WO/Tee up to 6\" in Unmade","DR116","nr",null,"126 to 180Unmade",410.64735317760005,"126 to 180","Unmade"]
["Install SV/FH/WO/Tee up to 6\" in Footpath","DR117","nr",null,"126 to 180Footpath",495.21436349220011,"126 to 180","Footpath"]
["Install SV/FH/WO/Tee up to 6\" in Type 3/4 carriageway","DR118","nr",null,"126 to 180Type 3/4 CW",525.20266502220011,"126 to 180","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 6\" in Type 1/2 carriageway","DR119","nr",null,"126 to 180Type 1/2 CW",551.08313505960007,"126 to 180","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 9\" No Excavation","DR120","nr",null,"181 to 200No excavation",668.10540906780011,"181 to 200","No excavation"]
["Install SV/FH/WO/Tee up to 9\" in Unmade","DR121","nr",null,"181 to 200Unmade",629.58458702700011,"181 to 200","Unmade"]
["Install SV/FH/WO/Tee up to 9\" in Footpath","DR122","nr",null,"181 to 200Footpath",721.40537140980018,"181 to 200","Footpath"]
["Install SV/FH/WO/Tee up to 9\" in Type 3/4 carriageway","DR123","nr",null,"181 to 200Type 3/4 CW",756.61050577200012,"181 to 200","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 9\" in Type 1/2 carriageway","DR124","nr",null,"181 to 200Type 1/2 CW",783.95078369520002,"181 to 200","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 9\" No Excavation","DR120","nr",null,"201 to 250No excavation",668.10540906780011,"201 to 250","No excavation"]
["Install SV/FH/WO/Tee up to 9\" in Unmade","DR121","nr",null,"201 to 250Unmade",629.58458702700011,"201 to 250","Unmade"]
["Install SV/FH/WO/Tee up to 9\" in Footpath","DR122","nr",null,"201 to 250Footpath",721.40537140980018,"201 to 250","Footpath"]
["Install SV/FH/WO/Tee up to 9\" in Type 3/4 carriageway","DR123","nr",null,"201 to 250Type 3/4 CW",756.61050577200012,"201 to 250","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 9\" in Type 1/2 carriageway","DR124","nr",null,"201 to 250Type 1/2 CW",783.95078369520002,"201 to 250","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 12\" No Excavation","DR125","nr",null,"251 to 355No excavation",1299.0253242384001,"251 to 355","No excavation"]
["Install SV/FH/WO/Tee up to 12\" in Unmade","DR126","nr",null,"251 to 355Unmade",1428.9595424148004,"251 to 355","Unmade"]
["Install SV/FH/WO/Tee up to 12\" in Footpath","DR127","nr",null,"251 to 355Footpath",1539.1467469422,"251 to 355","Footpath"]
["Install SV/FH/WO/Tee up to 12\" in Type 3/4 carriageway","DR128","nr",null,"251 to 355Type 3/4 CW",1589.5610425332002,"251 to 355","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 12\" in Type 1/2 carriageway","DR129","nr",null,"251 to 355Type 1/2 CW",1619.7077728260003,"251 to 355","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 18\" No Excavation","DR130","nr",null,"356 to 450No excavation",4911.3708611814009,"356 to 450","No excavation"]
["Install SV/FH/WO/Tee up to 18\" in Unmade","DR131","nr",null,"356 to 450Unmade",5288.0352447384012,"356 to 450","Unmade"]
["Install SV/FH/WO/Tee up to 18\" in Footpath","DR132","nr",null,"356 to 450Footpath",5577.0206244258006,"356 to 450","Footpath"]
["Install SV/FH/WO/Tee up to 18\" in Type 3/4 carriageway","DR133","nr",null,"356 to 450Type 3/4 CW",5641.648243308,"356 to 450","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 18\" in Type 1/2 carriageway","DR134","nr",null,"356 to 450Type 1/2 CW",7215.9661755918014,"356 to 450","Type 1/2 CW"]
["Cap offs"]
["Cap off Abandoned main up to 4\" in Tarmac/Footway","DR135","nr",null,null,454.91687604000003,"4\"","Tarmac/footway"]
["Cap off Abandoned main 4-6\" in Unmade","DR140","nr",null,null,349.79939192220007,"4 - 6\"","Unmade"]
["Cap off Abandoned main up to 4-6\" in Tarmac/Footway","DR136","nr",null,null,517.65466610880003,"4 - 6\"","Tarmac/footway"]
["Cap off Abandoned main up to 6-9\" in Tarmac/Footway","DR137","nr",null,null,678.93514663920018,"6 - 9\"","Tarmac/footway"]
["Cap off Abandoned main 6-9\" in Verge/Footway","DR138","nr",null,null,620.29387172280008,"6 - 9\"","Verge/footway"]
["Cap off Abandoned main 9-12\" in Tarmac/Footway","DR139","nr",null,null,877.29927400500003,"9 - 12\"","Tarmac/footway"]
["Pressure Test  & Chlorination"]
["Pressure Test  & Chlorination","DR141","nr",null,null,566.2583472678001]
["Trial hole"]
["Trial hole in unmade","DR142","m3",null,"Unmade",93.461653711800011,null,"Unmade"]
["Trial hole in Footpath","DR143","m3",null,"Footpath",174.11321031720004,null,"Footpath"]
["Trial hole in type 3/4","DR144","m3",null,"Type 3/4 CW",254.00657212920004,null,"Type 3/4 CW"]
["Trial hole in type 2/1","DR145","m3",null,"Type 1/2 CW",371.68519386900005,null,"Type 1/2 CW"]
["Shut off"]
["Shut off (Turnkey attendance)","DR146","day",null,null,399.62523782280005]
["B2B"]
["Back to back connection; includes removal of end hydrants, maximum connection distance 2m; not exceeding 125mm diameter","DR149","sum",null,null,576.22804298400001]
["Back to back connection; includes removal of end hydrants, maximum connection distance 2m; not exceeding 180mm diameter","DR150","sum",null,null,601.16925678480004]
["Sampling"]
["Sampling; where not included within a turnkey operation - applies to separate site visit solely for the purpose of sampling, as instructed","DR151","sum",null,null,154.95464635860003]
["Traffic lights"]
["2 way temporary traffic lights","TL2","sum",null,null,541.27186810620015]
["3 way temporary traffic lights","TL3","sum",null,null,618.01928734260002]
["4 way temporary traffic lights","TL4","sum",null,null,685.41940957380018]
[]
[]
["Pipe diameter drop down options"]
[]
["up to 125","up to 63"]
["126 to 180","64 to 90"]
["181 to 200","91 to 125"]
["201 to 250","126 to 180"]
["251 to 355","181 to 200"]
["356 to 450","201 to 250"]
[null,"251 to 355"]
[null,"356 to 450"]
[]
["Excavation drop down options"]
["No excavation"]
["Unmade"]
["Footpath"]
["Type 3/4 CW"]
["Type 1/2 CW"]
[]
["Traffic management drop down options"]
["2 way temporary traffic lights"]
["3 way temporary traffic lights"]
["4 way temporary traffic lights"]
[]
["PE lay type drop down options"]
["Lay only"]
["Open cut"]
[]
["PE offsite excavation drop down options"]
["No excavation"]
["Excavation in unmade ground"]
["Excavation in the highway"]
[]
["Pop up for PE <5 or >250 plots"]
["You have selected a number of plots which is less than 5 or over 250, this estimator tool is not applicable for this number of plots so you will require a bespoke quote for your site."]
[]
["Concatenate for Barrier onsite mains laying rate 1"]
[{"$f":"CONCATENATE('BARR EST'!C27,'BARR EST'!C33)","v":"126 to 180Unmade"},null,"Cost of onsite barrier length 1",null,{"$f":"(VLOOKUP(A153,E3:F37,2,FALSE))*'BARR EST'!G27","v":92420.550413400022}]
[]
["Concatenate for Barrier onsite mains laying rate 2"]
[{"$f":"CONCATENATE('BARR EST'!C28,'BARR EST'!C33)","v":"Unmade"},null,"Cost of onsite barrier length 2",null,{"$f":"(VLOOKUP(A156,E3:F37,2,FALSE))*'BARR EST'!G28","v":0}]
[]
["Concatenate for Barrier onsite mains laying rate 1"]
[{"$f":"CONCATENATE('BARR EST'!C29,'BARR EST'!C33)","v":"Unmade"},null,"Cost of onsite barrier length 3",null,{"$f":"(VLOOKUP(A159,E3:F37,2,FALSE))*'BARR EST'!G29","v":0}]
[]
["Concatenate for Barrier offsite mains laying rate"]
[{"$f":"CONCATENATE('BARR EST (2)'!C17,'BARR EST (2)'!C24)","v":"126 to 180Type 3/4 CW"}]
[]
["Concatenate for Barrier under pressure connections"]
[{"$f":"CONCATENATE('BARR EST (3)'!C12,'BARR EST (2)'!C24)","v":"150-200Type 3/4 CW"}]
[]
["Valves and uplift at SOW barrier (adjustment post audit and testing)"]
["Concatenate for offsite valve"]
[{"$f":"CONCATENATE('BARR EST (2)'!C17,'BARR EST (2)'!C24)","v":"126 to 180Type 3/4 CW"}]
["SOW valve charge (per unit)",{"$f":"VLOOKUP(A169,E60:F89,2,FALSE)","v":525.20266502220011}]
["SOW valve charge for 3 (assume 3 at SOW)",{"$f":"B170*3","v":1575.6079950666003}]
[]
["Flat uplift as per audit/sample averages (16.77% increase)",1.1677]
[]
["SOW cost for simply UPT",{"$f":"VLOOKUP(A165,E39:F58,2,FALSE)","v":4118.0840968212005}]
["Traffic mgt",{"$f":"F112","v":541.27186810620015}]
["Adding valves",{"$f":"B175+B176+B171","v":6234.9639599940019}]
[]
["Concatenate for Barrier under pressure connections"]
["up to 100"]
["150-200",null,"Number of pressure tests - ensuring no less than 0",null,null,{"$f":"MAX(1,('BARR EST'!G27+'BARR EST'!G28+'BARR EST'!G29)/100)","v":10}]
["225-300",null,"Ensure rounding to a single figure",null,null,{"$f":"ROUND(F181,0)","v":10}]
["375-450"]
[]
["Concatenate for valves, hydrants and wash outs"]
[{"$f":"CONCATENATE('BARR EST'!C27,'BARR EST'!C33)","v":"126 to 180Unmade"}]
[null,null,"Length",null,"Divided by 100m",null,"Rounding to a single figure"]
["How many required (1 per 100m + 2)",null,{"$f":"'BARR EST'!G27+'BARR EST'!G28+'BARR EST'!G29","v":1000},"m",{"$f":"C188/100","v":10},null,{"$f":"ROUND(E188,0)","v":10}]
[]
[null,null,null,null,null,null,"Add two"]
[null,null,null,null,null,null,{"$f":"G188+2","v":12}]
[]
[]
["Onsite mains laying cost",{"$f":"E153+E156+E159","v":92420.550413400022},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B194*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B194*1))","v":92420.550413400022}]
["Offsite mains laying cost - if above 2m",{"$f":"VLOOKUP(A162,E8:F37,2,FALSE)*'BARR EST (2)'!C12","v":2810.7978442368008},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B195*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B195*1))","v":2810.7978442368008}]
["Offsite mains laying cost - if below 2m",{"$f":"VLOOKUP(A162,E8:F37,2,FALSE)*2","v":351.34973052960009},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B196*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B196*1))","v":351.34973052960009}]
["Offsite mains laying cost - pull through cell",{"$f":"IF('BARR EST (2)'!C12<2,B196,B195)","v":2810.7978442368008},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B197*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B197*1))","v":2810.7978442368008}]
["SOW connection cost + TM + valves",{"$f":"B177","v":6234.9639599940019},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B198*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B198*1))","v":6234.9639599940019},null,"This does not include any length of main connecting into the connection itself (this would shown within the total 'offsite mains laying cost'). This does include the physical connection, traffic mgt and valves at the SOW connection. This estimator assumes a SOW cost in each estimate rather than a 'back-to-back'."]
["Valves, hydrants, wash outs along the length of the mains",{"$f":"_xlfn.IFNA((VLOOKUP(A186,E60:F89,2,FALSE)*G191),\"Please select a diameter\")*1.5","v":7391.6523571968009},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B199*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B199*1))","v":7391.6523571968009},null,"This estimator assumes 1.5 valve for every 100m of onsite mains plus two extra at each end of the total mains length"]
["Cap offs",{"$f":"('BARR EST (3)'!J22*F91)+('BARR EST (3)'!J23*F92)+('BARR EST (3)'!J24*F93)+('BARR EST (3)'!J25*F94)+('BARR EST (3)'!J26*F95)+('BARR EST (3)'!J27*F96)","v":0},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B200*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B200*1))","v":0}]
["Pressure test",{"$f":"F98*F182","v":5662.5834726780013},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B201*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B201*1))","v":5662.5834726780013},null,"This estimator assumes 1 pressure test per 100m of onsite mains"]
["Trial holes",{"$f":"VLOOKUP('BARR EST (2)'!C24,E100:F103,2,FALSE)","v":254.00657212920004},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B202*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B202*1))","v":254.00657212920004},null,"This estimator assumes 1 trial hole within each estimate"]
["B2B average",588.70000000000005,{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B203*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B203*1))","v":588.70000000000005}]
["Sampling",154.95464635860003,{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B204*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B204*1))","v":154.95464635860003}]
["UPT Average",3995.33,{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B205*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B205*1))","v":3995.33}]
["Connection valves Average",{"$f":"1297.02*3","v":3891.06},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B206*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B206*1))","v":3891.06}]
[null,null,{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B207*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B207*1))","v":0}]
["SOW totals",null,{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B208*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B208*1))","v":0}]
["Physical connection total",{"$f":"B198+B202+B203","v":7077.6705321232021},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B209*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B209*1))","v":7077.6705321232021}]
["Offsite mains total",{"$f":"B197+B201+B204+B205+B206","v":16514.725963273402},{"$f":"IF(SUM('BARR EST'!$G$27:$I$29)<500,B210*0.9,IF(SUM('BARR EST'!$G$27:$I$29)>=500,B210*1))","v":16514.725963273402}]
[]
["Physical connection total V1",{"$f":"IF(SUM('BARR EST'!G27:I29)<200,B209*0.9,IF(SUM('BARR EST'!G27:I29)>200,B209*1))","v":7077.6705321232021},null,null,"10% reduction made for schemes <500"]
["Offsite mains total V1",{"$f":"IF(SUM('BARR EST'!G27:I29)<200,B210*0.9,IF(SUM('BARR EST'!G27:I29)>200,B210*1))","v":16514.725963273402},null,null,"10% reduction made for schemes <500"]
[]
[null,null,"Unit of measure","Amey rate (£)","CONCATENATE","ST charge (£)","Diameter","Excavation requirement"]
["PE rates for PE SOW calculation"]
["Full description of required works","Code","m",null,{"$f":"CONCATENATE(G217,H217)","v":"up to 63No excavation"},15.333640971000003,"up to 63","No excavation"]
["Mains laying rates",null,"m",null,{"$f":"CONCATENATE(G218,H218)","v":"up to 63Unmade"},38.170015494600008,"up to 63","Unmade"]
["PE main laying, n.e 2m depth, up to 63mm NB No excavation","DR001","m",null,"up to 63Footpath",89.874373868400014,"up to 63","Footpath"]
["PE main laying, n.e 2m depth, up to 63mm NB in Unmade","DR002","m",null,"up to 63Type 3/4 CW",118.31233679100001,"up to 63","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 63mm NB in Footpath","DR003","m",null,"up to 63Type 1/2 CW",137.0748288426,"up to 63","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 63mm NB in Type 3/4 CW","DR004","m",null,"64 to 90No excavation",16.816081537200002,"64 to 90","No excavation"]
["PE main laying, n.e 2m depth, up to 63mm NB in Type 1/2 CW","DR005","m",null,"64 to 90Unmade",39.663772401000003,"64 to 90","Unmade"]
["PE main laying, n.e 2m depth, up to 90mm NB No excavation","DR006","m",null,"64 to 90Footpath",91.436028816000018,"64 to 90","Footpath"]
["PE main laying, n.e 2m depth, up to 90mm NB in Unmade","DR007","m",null,"64 to 90Type 3/4 CW",119.94188977980002,"64 to 90","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 90mm NB in Footpath","DR008","m",null,"64 to 90Type 1/2 CW",138.68174915100002,"64 to 90","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 90mm NB in Type 3/4 CW","DR009","m",null,"91 to 125No excavation",19.973340453000002,"91 to 125","No excavation"]
["PE main laying, n.e 2m depth, up to 90mm NB in Type 1/2 CW","DR010","m",null,"91 to 125Unmade",42.798398636400002,"91 to 125","Unmade"]
["PE main laying, n.e 2m depth, up to 125mm NB No excavation","DR011","m",null,"91 to 125Footpath",94.661185773000014,"91 to 125","Footpath"]
["PE main laying, n.e 2m depth, up to 125mm NB in Unmade","DR012","m",null,"91 to 125Type 3/4 CW",123.22362843780002,"91 to 125","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 125mm NB in Footpath","DR013","m",null,"91 to 125Type 1/2 CW",141.96348780900001,"91 to 125","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 125mm NB in Type 3/4 CW","DR014","m",null,"126 to 180No excavation",29.728025705400004,"126 to 180","No excavation"]
["PE main laying, n.e 2m depth, up to 125mm NB in Type 1/2 CW","DR015","m",null,"126 to 180Unmade",57.47569187580001,"126 to 180","Unmade"]
["PE main laying, n.e 2m depth, up to 180mm NB No excavation","DR016","m",null,"126 to 180Footpath",111.47726731020002,"126 to 180","Footpath"]
["PE main laying, n.e 2m depth, up to 180mm NB in Unmade","DR017","m",null,"126 to 180Type 3/4 CW",145.21127744640003,"126 to 180","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 180mm NB in Footpath","DR018","m",null,"126 to 180Type 1/2 CW",149.62465012440001,"126 to 180","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 180mm NB in Type 3/4 CW","DR019","m",null,"181 to 200No excavation",37.932372350400016,"181 to 200","No excavation"]
["PE main laying, n.e 2m depth, up to 180mm NB in Type 1/2 CW","DR020","m",null,"181 to 200Unmade",69.516277848599998,"181 to 200","Unmade"]
["PE main laying, n.e 2m depth, up to 200 mm NB No excavation","DR021","m",null,"181 to 200Footpath",123.18967941720003,"181 to 200","Footpath"]
["PE main laying, n.e 2m depth, up to 200mm NB in Unmade","DR022","m",null,"181 to 200Type 3/4 CW",165.33173032200003,"181 to 200","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 200mm NB in Footpath","DR023","m",null,"181 to 200Type 1/2 CW",176.18410057380004,"181 to 200","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 200mm NB in Type 3/4 CW","DR024","m",null,"201 to 250No excavation",47.607843221400003,"201 to 250","No excavation"]
["PE main laying, n.e 2m depth, up to 200mm NB in Type 1/2 CW","DR025","m",null,"201 to 250Unmade",81.387118718400018,"201 to 250","Unmade"]
["PE main laying, n.e 2m depth, up to 250 mm NB No excavation","DR026","m",null,"201 to 250Footpath",135.25289807040002,"201 to 250","Footpath"]
["PE main laying, n.e 2m depth, up to 250mm NB in Unmade","DR027","m",null,"201 to 250Type 3/4 CW",184.95426422880001,"201 to 250","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 250mm NB in Footpath","DR028","m",null,"201 to 250Type 1/2 CW",196.73457437700003,"201 to 250","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 250mm NB in Type 3/4 CW","DR029","m",null,"251 to 355No excavation",98.46347608020001,"251 to 355","No excavation"]
["PE main laying, n.e 2m depth, up to 250mm NB in Type 1/2 CW","DR030","m",null,"251 to 355Unmade",129.44761554780001,"251 to 355","Unmade"]
["PE main laying, n.e 2m depth, up to 355mm NB No excavation","DR031","m",null,"251 to 355Footpath",221.05338946680004,"251 to 355","Footpath"]
["PE main laying, n.e 2m depth, up to 355mm NB in Unmade","DR032","m",null,"251 to 355Type 3/4 CW",262.78805212440005,"251 to 355","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 355mm NB in Footpath","DR033","m",null,"251 to 355Type 1/2 CW",334.80524115720004,"251 to 355","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 355mm NB in Type 3/4 CW","DR034","m",null,"356 to 450No excavation",146.6824016724,"356 to 450","No excavation"]
["PE main laying, n.e 2m depth, up to 355mm NB in Type 1/2 CW","DR035","m",null,"356 to 450Unmade",185.15795835240002,"356 to 450","Unmade"]
["PE main laying, n.e 2m depth, up to 450mm NB No excavation","DR036","m",null,"356 to 450Footpath",293.00268045840005,"356 to 450","Footpath"]
["PE main laying, n.e 2m depth, up to 450mm NB in Unmade","DR037","m",null,"356 to 450Type 3/4 CW",348.26036965500009,"356 to 450","Type 3/4 CW"]
["PE main laying, n.e 2m depth, up to 450mm NB in Footpath","DR038","m",null,"356 to 450Type 1/2 CW",432.71421656760003,"356 to 450","Type 1/2 CW"]
["PE main laying, n.e 2m depth, up to 450mm NB in Type 3/4 CW","DR039"]
["PE main laying, n.e 2m depth, up to 450mm NB in Type 1/2 CW","DR040","nr",null,"up to 100No excavation",955.95915473520006,"up to 100","No excavation"]
["UPT rates",null,"nr",null,"up to 100Unmade",1278.4409014146001,"up to 100","Unmade"]
["Under pressure connection. Body not exceeding 100mm NB.  No Excavation","DR086","nr",null,"up to 100Footpath",1630.4922450366003,"up to 100","Footpath"]
["Under pressure connection body not exceeding 100mm NB.  Unmade","DR087","nr",null,"up to 100Type 3/4 CW",2062.5614302128001,"up to 100","Type 3/4 CW"]
["Under pressure connection. Body not exceeding 100mm NB.  Footpath","DR088","nr",null,"up to 100Type 1/2 CW",2150.4328118658004,"up to 100","Type 1/2 CW"]
["Under pressure connection Body not exceeding 100mm NB.  Type 3/4 carriageway","DR089","nr",null,"150-200No excavation",1394.1731126400002,"150-200","No excavation"]
["Under pressure connection Body not exceeding 100mm NB.  Type 1/2 carriageway","DR090","nr",null,"150-200Unmade",1813.4322007098003,"150-200","Unmade"]
["Under pressure connection Body 150-200mm NB.  No Excavation","DR092","nr",null,"150-200Footpath",2333.7688394460006,"150-200","Footpath"]
["Under pressure connection Body 150-200mm NB.  Unmade","DR093","nr",null,"150-200Type 3/4 CW",2722.1003697492006,"150-200","Type 3/4 CW"]
["Under pressure connection Body 150-200mm NB.  Footpath","DR094","nr",null,"150-200Type 1/2 CW",2827.5460277328002,"150-200","Type 1/2 CW"]
["Under pressure connection Body 150-200mm NB.  Type 3/4 carriageway","DR095","nr",null,"225-300No excavation",2388.6870384366002,"225-300","No excavation"]
["Under pressure connection Body 150-200mm NB.  Type 1/2 carriageway","DR096","nr",null,"225-300Unmade",2990.7163370766002,"225-300","Unmade"]
["Under pressure connection Body 225-300 NB.  No Excavation","DR098","nr",null,"225-300Footpath",3544.1759035782002,"225-300","Footpath"]
["Under pressure connection Body 225-300 NB.  Unmade","DR099","nr",null,"225-300Type 3/4 CW",4137.4010895426009,"225-300","Type 3/4 CW"]
["Under pressure connection Body 225-300 NB.  Footpath","DR100","nr",null,"225-300Type 1/2 CW",4292.5028483238002,"225-300","Type 1/2 CW"]
["Under pressure connection Body 225-300 NB.  Type 3/4 carriageway","DR101","nr",null,"375-450No excavation",6088.7342119296,"375-450","No excavation"]
["Under pressure connection Body 225-300 NB.  Type 1/2 carriageway","DR102","nr",null,"375-450Unmade",7234.4910244992006,"375-450","Unmade"]
["Under pressure connection Body 375-450 NB.  No Excavation","DR104","nr",null,"375-450Footpath",8530.8909578112016,"375-450","Footpath"]
["Under pressure connection Body 375-450 NB.  Unmade","DR105","nr",null,"375-450Type 3/4 CW",10626.2924072844,"375-450","Type 3/4 CW"]
["Under pressure connection Body 375-450 NB.  Footpath","DR106","nr",null,"375-450Type 1/2 CW",10903.8709160502,"375-450","Type 1/2 CW"]
["Under pressure connection Body 375-450 NB.  Type 3/4 carriageway","DR107"]
["Under pressure connection Body 375-450 NB.  Type 1/2 carriageway","DR108","nr",null,{"$f":"CONCATENATE(G279,H279)","v":"up to 63No excavation"},265.8434639784,"up to 63","No excavation"]
["Valves, hydrants and wash outs",null,"nr",null,"up to 63Unmade",371.22122392080013,"up to 63","Unmade"]
["Install SV/FH/WO/Tee up to 4\" No Excavation'","DR110","nr",null,"up to 63Footpath",454.43027341140009,"up to 63","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Unmade","DR111","nr",null,"up to 63Type 3/4 CW",480.88787679900008,"up to 63","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Footpath","DR112","nr",null,"up to 63Type 1/2 CW",505.53486575460005,"up to 63","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway","DR113","nr",null,"64 to 90No excavation",265.8434639784,"64 to 90","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway","DR114","nr",null,"64 to 90Unmade",371.22122392080013,"64 to 90","Unmade"]
["Install SV/FH/WO/Tee up to 4\" No Excavation'","DR110","nr",null,"64 to 90Footpath",454.43027341140009,"64 to 90","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Unmade","DR111","nr",null,"64 to 90Type 3/4 CW",480.88787679900008,"64 to 90","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Footpath","DR112","nr",null,"64 to 90Type 1/2 CW",505.53486575460005,"64 to 90","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway","DR113","nr",null,"91 to 125No excavation",265.8434639784,"91 to 125","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway","DR114","nr",null,"91 to 125Unmade",371.22122392080013,"91 to 125","Unmade"]
["Install SV/FH/WO/Tee up to 4\" No Excavation'","DR110","nr",null,"91 to 125Footpath",454.43027341140009,"91 to 125","Footpath"]
["Install SV/FH/WO/Tee up to 4\" in Unmade","DR111","nr",null,{"$f":"CONCATENATE(G292,H292)","v":"91 to 125Type 3/4 CW"},480.88787679900008,"91 to 125","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 4\" in Footpath","DR112","nr",null,"91 to 125Type 1/2 CW",505.53486575460005,"91 to 125","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 4\" in Type 3/4 carriageway","DR113","nr",null,"126 to 180No excavation",435.21512775180003,"126 to 180","No excavation"]
["Install SV/FH/WO/Tee up to 4\" in Type 1/2 carriageway","DR114","nr",null,"126 to 180Unmade",410.64735317760005,"126 to 180","Unmade"]
["Install SV/FH/WO/Tee up to 6\" No Excavation","DR115","nr",null,"126 to 180Footpath",495.21436349220011,"126 to 180","Footpath"]
["Install SV/FH/WO/Tee up to 6\" in Unmade","DR116","nr",null,"126 to 180Type 3/4 CW",525.20266502220011,"126 to 180","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 6\" in Footpath","DR117","nr",null,"126 to 180Type 1/2 CW",551.08313505960007,"126 to 180","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 6\" in Type 3/4 carriageway","DR118","nr",null,"181 to 200No excavation",668.10540906780011,"181 to 200","No excavation"]
["Install SV/FH/WO/Tee up to 6\" in Type 1/2 carriageway","DR119","nr",null,"181 to 200Unmade",629.58458702700011,"181 to 200","Unmade"]
["Install SV/FH/WO/Tee up to 9\" No Excavation","DR120","nr",null,"181 to 200Footpath",721.40537140980018,"181 to 200","Footpath"]
["Install SV/FH/WO/Tee up to 9\" in Unmade","DR121","nr",null,"181 to 200Type 3/4 CW",756.61050577200012,"181 to 200","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 9\" in Footpath","DR122","nr",null,"181 to 200Type 1/2 CW",783.95078369520002,"181 to 200","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 9\" in Type 3/4 carriageway","DR123","nr",null,"201 to 250No excavation",668.10540906780011,"201 to 250","No excavation"]
["Install SV/FH/WO/Tee up to 9\" in Type 1/2 carriageway","DR124","nr",null,"201 to 250Unmade",629.58458702700011,"201 to 250","Unmade"]
["Install SV/FH/WO/Tee up to 9\" No Excavation","DR120","nr",null,"201 to 250Footpath",721.40537140980018,"201 to 250","Footpath"]
["Install SV/FH/WO/Tee up to 9\" in Unmade","DR121","nr",null,"201 to 250Type 3/4 CW",756.61050577200012,"201 to 250","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 9\" in Footpath","DR122","nr",null,"201 to 250Type 1/2 CW",783.95078369520002,"201 to 250","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 9\" in Type 3/4 carriageway","DR123","nr",null,"251 to 355No excavation",1299.0253242384001,"251 to 355","No excavation"]
["Install SV/FH/WO/Tee up to 9\" in Type 1/2 carriageway","DR124","nr",null,"251 to 355Unmade",1428.9595424148004,"251 to 355","Unmade"]
["Install SV/FH/WO/Tee up to 12\" No Excavation","DR125","nr",null,"251 to 355Footpath",1539.1467469422,"251 to 355","Footpath"]
["Install SV/FH/WO/Tee up to 12\" in Unmade","DR126","nr",null,"251 to 355Type 3/4 CW",1589.5610425332002,"251 to 355","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 12\" in Footpath","DR127","nr",null,"251 to 355Type 1/2 CW",1619.7077728260003,"251 to 355","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 12\" in Type 3/4 carriageway","DR128","nr",null,"356 to 450No excavation",4911.3708611814009,"356 to 450","No excavation"]
["Install SV/FH/WO/Tee up to 12\" in Type 1/2 carriageway","DR129","nr",null,"356 to 450Unmade",5288.0352447384012,"356 to 450","Unmade"]
["Install SV/FH/WO/Tee up to 18\" No Excavation","DR130","nr",null,"356 to 450Footpath",5577.0206244258006,"356 to 450","Footpath"]
["Install SV/FH/WO/Tee up to 18\" in Unmade","DR131","nr",null,"356 to 450Type 3/4 CW",5641.648243308,"356 to 450","Type 3/4 CW"]
["Install SV/FH/WO/Tee up to 18\" in Footpath","DR132","nr",null,"356 to 450Type 1/2 CW",7215.9661755918014,"356 to 450","Type 1/2 CW"]
["Install SV/FH/WO/Tee up to 18\" in Type 3/4 carriageway","DR133"]
["Install SV/FH/WO/Tee up to 18\" in Type 1/2 carriageway","DR134","nr",null,null,566.2583472678001]
["Pressure Test  & Chlorination"]
["Pressure Test  & Chlorination","DR141","m3",null,"Unmade",93.461653711800011,null,"Unmade"]
["Trial hole",null,"m3",null,"Footpath",174.11321031720004,null,"Footpath"]
["Trial hole in unmade","DR142","m3",null,"Type 3/4 CW",254.00657212920004,null,"Type 3/4 CW"]
["Trial hole in Footpath","DR143","m3",null,"Type 1/2 CW",371.68519386900005,null,"Type 1/2 CW"]
["Trial hole in type 3/4","DR144"]
["Trial hole in type 2/1","DR145","sum",null,null,541.27186810620015]
["Traffic lights"]
["2 way temporary traffic lights","TL2"]
[]
["Concatenate for PE SOW connection"]
[]
[{"$f":"CONCATENATE('PE SOW'!C37,'PE SOW'!C20)","v":"150-200Type 1/2 CW"}]
[]
["Concatenate for PE SOW mains length"]
[]
[{"$f":"CONCATENATE('PE SOW'!H31,'PE SOW'!C20)","v":"126 to 180Type 1/2 CW"},null,{"$f":"VLOOKUP(A333,E258:F277,2,FALSE)","v":2827.5460277328002}]
[null,null,{"$f":"(VLOOKUP(A337,E217:F256,2,FALSE))*'PE SOW'!C31","v":0}]
["UPT cost",null,{"$f":"(VLOOKUP(A337,E217:F256,2,FALSE))*2","v":299.24930024880001}]
["Mains length cost if above 2m",null,{"$f":"IF(C338<2,C339,C338)","v":299.24930024880001}]
["Mains length cost if below 2m",null,{"$f":"F327","v":541.27186810620015}]
["Mains length cost - pull through cell",null,{"$f":"VLOOKUP(A337,E279:F318,2,FALSE)*3","v":1653.2494051788003},null,"(3 assumed)"]
["Traffic management cost",null,{"$f":"VLOOKUP('PE SOW'!C20,E322:F325,2,FALSE)","v":371.68519386900005}]
["Valve/wash out cost"]
["Trial hole cost"]
[]
["B2B, pressure test and sampling uplift (adjustment post audit and testing)",null,{"$f":"C337+SUM(C340:C343)","v":5693.0017951356003}]
[null,null,566.2583472678001]
["Total of charges above",null,154.95464635860003]
["Pressure test charge",null,588.70000000000005]
["Sampling charge",null,{"$f":"C347+C348+C349+C350","v":7002.9147887620002}]
["B2B average"]
["Total"]
[]
["Pipe diameter drop down options"]
["up to 63"]
["64 to 90"]
["91 to 125"]
["126 to 180"]
["181 to 200"]
["201 to 250"]
["251 to 355"]
["356 to 450"]