{"text_format":"1","workbook":"xlsx","n_sheets":15,"sheets":["Welcome","TW Inputs","OccupancyGraph","Scheme Type landing","Scheme Type WATER","Scheme Type FOUL","Scheme Type SURFACE","Scheme Type COMBINED","Parameters","BespokeOccupancy","Result WATER","Result FOUL","Result SURFACE","Result COMBINED","Full Calc"],"not_rendered":{"charts":2,"images":21,"macros":1,"external_links":0,"defined_names":60,"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":"Welcome","index":0,"n_rows":24,"n_cols":4,"rows_kept":24,"cut":false,"hidden":false,"merged":[[23,1,23,13],[9,3,10,10],[21,3,22,11]]}
[]
[]
[null,null,null,"Developer Services"]
[null,null,null,"Welcome to the Thames Water Discounted Aggregate Deficit Model"]
[]
[null,null,null,"This tool helps you to estimate the:"]
[null,null,null,"     - asset payment we’ll make if a self-lay provider lays a new water main"]
[null,null,null,"     - income offset we’ll apply if you ask us to lay a new water main, or a new sewer"]
[]
[null,null,null,{"$f":"'TW Inputs'!C120","v":"This version of the model is valid from 1st April 2019 to 31st March 2020 only. "}]
[]
[]
[null,null,null,"Full details of our charges for new connections services, any income offsets, and any asset payments that may apply"]
[null,null,null,{"$f":"\"can be found in our \"&charging_year&\" Charging Arrangements which is published on our website.\"","v":"can be found in our 2019/20 Charging Arrangements which is published on our website."}]
[]
[null,null,null,"Important : Before continuing, please ensure Excel macros are enabled."]
[null,null,null,"If you need help with enabling macros, visit Microsoft support (link opens external web page)"]
[]
[]
[]
[]
[null,null,null,"If you need assistance please contact the Thames Water Developer Services helpdesk\nEmail developer.services@thameswater.co.uk   Call: 0800 009 3921 (lines open 8am-5pm Mon-Fri excl. bank holidays)"]
[]
[null,{"$f":"\"       © Thames Water Utilities Limited \"&cal_year","v":"       © Thames Water Utilities Limited 2019"}]

{"sheet":"TW Inputs","index":1,"n_rows":142,"n_cols":15,"rows_kept":142,"cut":false,"hidden":true,"merged":[[56,11,56,13],[3,1,4,9],[57,2,57,7],[113,2,114,9],[125,2,126,9],[116,2,117,9],[119,2,120,9],[122,2,123,9],[141,1,142,1],[141,2,142,3],[141,4,142,5],[133,2,134,3],[133,1,134,1],[133,4,134,5]]}
[]
[null,"FILE MAINTENANCE - THAMES WATER USE ONLY",null,null,null,null,null,null,null,null,null,"Built by Darrel Flint for Thames Water"]
[null,null,null,null,null,null,null,null,null,null,null,"darrel.flint@flintfm.net"]
[null,"IMPORTANT: BEFORE PUBLISHING A NEW VERSION OF THIS MODEL, THE FILE MUST BE LOCKED DOWN AND PROTECTED BY \nPRESSING THE FOLLOWING BUTTON:"]
[]
[]
[]
[]
[]
[null,"Instructions for updating to the 2019/20 version"]
[]
[null,"1. Review the yellow input cells below and amend where necessary for the new year."]
[null,"2. It is anticipated only the following cells will need changing:"]
[null,"(i). The 3 average income figures;"]
[null,"(ii). The dates in the disclaimer text section;"]
[null,"(iii). The date in the Charging Arrangements Year box and the date of the calendar year box."]
[null,"(iv). If deemed necessary, the password.  Remember to change it twice (once in the file password store section below and once in VBA Properties)."]
[null,"3. When all changes are complete, press the lockdown button above and TEST thoroughly before publishing to the website."]
[]
[]
[]
[null,"FILE PASSWORD STORE"]
[]
[false,"Password:","Nugent1#","Note: The password is cAsE sEnSiTiVe."]
[]
[]
[null,"Important: VBA (macros) access has also been locked with the same password.  If the password above is changed, access VBA by pressing [ALT] F11 and"]
[null,"go to Tools - VBAProject Properties.  In the dialogue box that opens, click the Protection tab to change the password."]
[]
[]
[]
[]
[]
[null,"THAMES WATER VARIABLE INPUTS"]
[]
[null,"Update figures in the yellow shaded cells below only"]
[]
[null,"FINANCIAL RATES:"]
[]
[null,"OFWAT Borrowing Interest Rate:",null,null,2.75E-2]
[null,"OFWAT Deposit Interest Rate:",null,null,5.0000000000000001E-3]
[null,"NPV Discount Rate:",null,null,2.75E-2]
[null,"Long Term Annual Inflation:",null,null,0.03]
[]
[]
[]
[null,"AVERAGE INCOME PER PROPERTY:"]
[]
[null,"Foul water sewer income per property:",null,null,145.62,"Updated tariff for 1st April 2019 - current value provided by James Lancaster"]
[null,"Surface water sewer income per property:",null,null,26.4,"Updated tariff for 1st April 2019 - current value provided by James Lancaster"]
[null,"Water main income per Property:",null,null,196.44,"Updated tariff for 1st April 2019 - current value provided by James Lancaster"]
[null,"Combined sewer income per property:",null,null,{"$f":"E49+E50","v":172.02},"No update needed as a summation"]
[]
[]
[null,"OCCUPANCY RATES - STANDARD (before slowdown rates below applied)",null,null,null,null,null,null,null,null,null,"OCCUPANCY PROFILES (DROP DOWN):"]
[]
[]
[null,null,"!! IMPORTANT: The headings feed the drop down list on the right !!"]
[null,"Year","1 - 9 DWELLINGS ","10 - 100 DWELLINGS","101 - 500 DWELLINGS","501+ DWELLINGS","BESPOKE PROFILE",null,null,null,null,null,"Min","Max","Show graphic illustration?"]
[null,1,0.5,0.25,0.2,0.1,{"$f":"bespoke_Yr1","v":0},null,null,null,null,"<SELECT>",1,9.9999999999999904E+40,"No"]
[null,2,0.97499999999999998,0.5,0.4,0.2,{"$f":"bespoke_Yr2","v":0},null,null,null,null,{"$f":"C59","v":"1 - 9 DWELLINGS "},1,9,"Yes"]
[null,3,0.97499999999999998,0.97499999999999998,0.75,0.4,{"$f":"bespoke_Yr3","v":0},null,null,null,null,{"$f":"D59","v":"10 - 100 DWELLINGS"},10,100,"Yes"]
[null,4,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.6,{"$f":"bespoke_Yr4","v":0},null,null,null,null,{"$f":"E59","v":"101 - 500 DWELLINGS"},101,500,"Yes"]
[null,5,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.8,{"$f":"bespoke_Yr5","v":0},null,null,null,null,{"$f":"F59","v":"501+ DWELLINGS"},501,9.9999999999999904E+40,"Yes"]
[null,6,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr6","v":0},null,null,null,null,{"$f":"G59","v":"BESPOKE PROFILE"},1,9.9999999999999904E+40,"No"]
[null,7,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr7","v":0},null,null,null,null,"The orange border above"]
[null,8,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr8","v":0},null,null,null,null,"indicates the range named 'Occupancy_Profiles'."]
[null,9,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr9","v":0}]
[null,10,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr10","v":0}]
[null,11,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr11","v":0},null,null,null,null,"Selection made in dropdown list on 'Parameters' tab:",null,"<SELECT>","DO NOT DELETE!"]
[null,12,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,{"$f":"bespoke_Yr12","v":0},null,null,null,null,"The orange border to the right indicates the range named 'Occupancy'."]
[null,null,null,null,null,null,null,null,null,null,null,"This box is needed to check the dropdown list isn't keyed into and ensures only a selection"]
[null,null,null,null,null,null,null,null,null,null,null,"from the list above is made before the NEXT button appears on the Parameters sheet."]
[null,"OCCUPANCY RATES - SLOWDOWN RATES"]
[]
[null,"Year","Slowdown %",null,null,null,null,null,null,null,null,"Scheme type selected:",null,"Water main","DO NOT DELETE!"]
[null,1,0.5,null,null,null,null,null,null,null,null,"The orange border to the right indicates the range named 'scheme_selected'."]
[null,2,0.35]
[null,3,0.25,null,null,null,null,null,null,null,null,"Date of calc (populated by macro):",null,"2018-02-05T17:51:00","DO NOT DELETE!"]
[null,4,0.1]
[null,5,0]
[null,6,0]
[null,7,0]
[null,8,0]
[null,9,0]
[null,10,0]
[null,11,0]
[null,12,0]
[]
[]
[null,"OCCUPANCY RATES USED IN THE CALCULATIONS (slowdown rates applied)"]
[]
[null,null,"!! IMPORTANT: The orange border below indicates the range name \"Occupancy_table\".  Changing this or any of the headings will impact calculation formulae !!"]
[]
[null,"Year",{"$f":"C59","v":"1 - 9 DWELLINGS "},{"$f":"D59","v":"10 - 100 DWELLINGS"},{"$f":"E59","v":"101 - 500 DWELLINGS"},{"$f":"F59","v":"501+ DWELLINGS"},{"$f":"G59","v":"BESPOKE PROFILE"}]
[null,1,{"$f":"C60*(1-$C77)","v":0.25},0.125,0.1,0.05,{"$f":"G60","v":0}]
[null,2,0.63375000000000004,0.32500000000000001,0.26,0.13,{"$f":"G61","v":0}]
[null,3,0.73124999999999996,0.73124999999999996,0.5625,0.30000000000000004,0]
[null,4,0.87749999999999995,0.87749999999999995,0.87749999999999995,0.54,0]
[null,5,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.8,0]
[null,6,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[null,7,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[null,8,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[null,9,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[null,10,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[null,11,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[null,12,0.97499999999999998,0.97499999999999998,0.97499999999999998,0.97499999999999998,0]
[]
[]
[]
[null,"DISCLAIMER TEXT - POPULATES THE AUTOMATIC POP-UP MESSAGE ON FILE OPEN AND THE FINAL CALC SHEET"]
[]
[]
[null,null,"This file is the property of Thames Water Utilities Ltd (Thames Water) and is made available for the purposes of assisting customers of Thames Water in estimating the respective contributions to be made by Thames Water and the customer for a new water main or sewer."]
[]
[]
[null,null,"This model is an estimating tool only and the final charge will be as detailed on the quotation we provide on application."]
[]
[]
[null,"Update 1st April 2019 --->","This version of the model is valid from 1st April 2019 to 31st March 2020 only. "]
[]
[]
[null,null,"If you accept these terms of use please click Yes otherwise click No to close the file."]
[]
[]
[null,null,"Please note that if you have used your own bespoke occupancy profile in this model, Thames Water will need to review and agree your proposed occupancy rates."]
[]
[]
[]
[]
[null,"CHARGING ARRANGEMENTS YEAR - POPULATES THE WELCOME AND RESULT WATER SCREENS AND VARIOUS HELP POPUP BOXES"]
[]
[]
[null,"Updated 1st April 2019 --->","2019/20",null,"(Use the format 2019/20)"]
[]
[]
[]
[]
[null,"Calendar year - populates the copyright label at the bottom of each sheet"]
[]
[]
[null,"Updated 1st April 2019 --->",2019,null,"(Use the format 2019)"]

{"sheet":"OccupancyGraph","index":2,"n_rows":25,"n_cols":3,"rows_kept":25,"cut":false,"hidden":true,"merged":[[1,1,1,19],[2,1,2,19]]}
[]
[null,"WARNING - AVOID CHANGING THE ZOOM OF THIS PAGE FROM 65% AS IT WILL AFFECT THE ILLUSTRATION POP-UP IMAGE ON THE PARAMETERS SHEET"]
[null,"NOTE: THE LOCKDOWN MACRO AUTOMATICALLY CHANGES THE ZOOM TO 65% IF DIFFERENT"]
[null,"Occupancy profile selected - GRAPH"]
[]
[null,"Profile selected:"]
[null,{"$f":"LOWER(Occupancy)","v":"<select>"}]
[]
[null,"Show graphic illustration?"]
[null,{"$f":"IFERROR(VLOOKUP(B7,'TW Inputs'!$L$60:$O$65,4,0),\"No\")","v":"No"}]
[]
[]
[null,"Year"," Cumulative occupancy "]
[null,1,{"$f":"HLOOKUP(Occupancy,Occupancy_table,2,FALSE)","v":{"$error":"#N/A"}}]
[null,2,{"$f":"HLOOKUP(Occupancy,Occupancy_table,3,FALSE)","v":{"$error":"#N/A"}}]
[null,3,{"$f":"HLOOKUP(Occupancy,Occupancy_table,4,FALSE)","v":{"$error":"#N/A"}}]
[null,4,{"$f":"HLOOKUP(Occupancy,Occupancy_table,5,FALSE)","v":{"$error":"#N/A"}}]
[null,5,{"$f":"HLOOKUP(Occupancy,Occupancy_table,6,FALSE)","v":{"$error":"#N/A"}}]
[null,6,{"$f":"HLOOKUP(Occupancy,Occupancy_table,7,FALSE)","v":{"$error":"#N/A"}}]
[null,7,{"$f":"HLOOKUP(Occupancy,Occupancy_table,8,FALSE)","v":{"$error":"#N/A"}}]
[null,8,{"$f":"HLOOKUP(Occupancy,Occupancy_table,9,FALSE)","v":{"$error":"#N/A"}}]
[null,9,{"$f":"HLOOKUP(Occupancy,Occupancy_table,10,FALSE)","v":{"$error":"#N/A"}}]
[null,10,{"$f":"HLOOKUP(Occupancy,Occupancy_table,11,FALSE)","v":{"$error":"#N/A"}}]
[null,11,{"$f":"HLOOKUP(Occupancy,Occupancy_table,12,FALSE)","v":{"$error":"#N/A"}}]
[null,12,{"$f":"HLOOKUP(Occupancy,Occupancy_table,13,FALSE)","v":{"$error":"#N/A"}}]

{"sheet":"Scheme Type landing","index":3,"n_rows":32,"n_cols":12,"rows_kept":32,"cut":false,"hidden":false,"merged":[[21,3,21,4],[21,5,21,6],[21,7,21,8],[21,9,21,10],[22,3,22,4],[22,9,22,10],[31,1,31,12],[23,3,23,4],[22,5,22,6],[22,7,22,8],[23,5,27,6],[23,7,27,8],[23,9,27,10]]}
[]
[]
[]
[]
[]
[null,null,"Select the type of scheme you're developing",null,null,null,null,null,null,null,null,"Thames Water Discounted Aggregate Deficit Model"]
[]
[null,null,"Please select your scheme type by clicking on the appropriate button below and click next"]
[null,null,"If your scheme includes more than one type, you will need to calculate each one separately"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,"Water main",null,"Foul water sewer",null,"Surface water sewer",null,"Combined sewer "]
[]
[null,null,null,"A new water main.",null,"A new sewer collecting foul water\ndischarged from sources such as\ntoilets, baths, showers, sinks\nand washing machines.",null,"A new sewer collecting rain or melted\nsnow that falls on surfaces such as\nroofs, patios, driveways and roads.",null,"A new sewer collecting both\nfoul and surface water."]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Scheme Type WATER","index":4,"n_rows":32,"n_cols":12,"rows_kept":32,"cut":false,"hidden":false,"merged":[[31,1,31,12],[23,3,23,4],[21,3,21,4],[21,5,21,6],[21,7,21,8],[21,9,21,10],[23,5,27,6],[23,7,27,8],[23,9,27,10],[22,3,22,4],[22,6,22,7],[22,9,22,10]]}
[]
[]
[]
[]
[]
[null,null,"Select the type of scheme you're developing",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Please select your scheme type by clicking on the appropriate button below and click next"]
[null,null,"If your scheme includes more than one type, you will need to calculate each one separately"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D22","v":"Water main"},null,{"$f":"'Scheme Type landing'!F22","v":"Foul water sewer"},null,{"$f":"'Scheme Type landing'!H22","v":"Surface water sewer"},null,{"$f":"'Scheme Type landing'!J22","v":"Combined sewer "}]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D24:E24","v":"A new water main."},null,{"$f":"'Scheme Type landing'!F24","v":"A new sewer collecting foul water\ndischarged from sources such as\ntoilets, baths, showers, sinks\nand washing machines."},null,{"$f":"'Scheme Type landing'!H24","v":"A new sewer collecting rain or melted\nsnow that falls on surfaces such as\nroofs, patios, driveways and roads."},null,{"$f":"'Scheme Type landing'!J24","v":"A new sewer collecting both\nfoul and surface water."}]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Scheme Type FOUL","index":5,"n_rows":32,"n_cols":12,"rows_kept":32,"cut":false,"hidden":false,"merged":[[31,1,31,12],[21,3,21,4],[21,5,21,6],[21,7,21,8],[21,9,21,10],[23,3,23,4],[23,5,27,6],[23,7,27,8],[23,9,27,10],[22,3,22,4],[22,6,22,7],[22,9,22,10]]}
[]
[]
[]
[]
[]
[null,null,"Select the type of scheme you're developing",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Please select your scheme type by clicking on the appropriate button below and click next"]
[null,null,"If your scheme includes more than one type, you will need to calculate each one separately"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D22","v":"Water main"},null,{"$f":"'Scheme Type landing'!F22","v":"Foul water sewer"},null,{"$f":"'Scheme Type landing'!H22","v":"Surface water sewer"},null,{"$f":"'Scheme Type landing'!J22","v":"Combined sewer "}]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D24:E24","v":"A new water main."},null,{"$f":"'Scheme Type landing'!F24","v":"A new sewer collecting foul water\ndischarged from sources such as\ntoilets, baths, showers, sinks\nand washing machines."},null,{"$f":"'Scheme Type landing'!H24","v":"A new sewer collecting rain or melted\nsnow that falls on surfaces such as\nroofs, patios, driveways and roads."},null,{"$f":"'Scheme Type landing'!J24","v":"A new sewer collecting both\nfoul and surface water."}]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Scheme Type SURFACE","index":6,"n_rows":32,"n_cols":12,"rows_kept":32,"cut":false,"hidden":false,"merged":[[31,1,31,12],[21,3,21,4],[21,5,21,6],[21,7,21,8],[21,9,21,10],[23,3,23,4],[23,5,27,6],[23,7,27,8],[23,9,27,10],[22,3,22,4],[22,6,22,7],[22,9,22,10]]}
[]
[]
[]
[]
[]
[null,null,"Select the type of scheme you're developing",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Please select your scheme type by clicking on the appropriate button below and click next"]
[null,null,"If your scheme includes more than one type, you will need to calculate each one separately"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D22","v":"Water main"},null,{"$f":"'Scheme Type landing'!F22","v":"Foul water sewer"},null,{"$f":"'Scheme Type landing'!H22","v":"Surface water sewer"},null,{"$f":"'Scheme Type landing'!J22","v":"Combined sewer "}]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D24:E24","v":"A new water main."},null,{"$f":"'Scheme Type landing'!F24","v":"A new sewer collecting foul water\ndischarged from sources such as\ntoilets, baths, showers, sinks\nand washing machines."},null,{"$f":"'Scheme Type landing'!H24","v":"A new sewer collecting rain or melted\nsnow that falls on surfaces such as\nroofs, patios, driveways and roads."},null,{"$f":"'Scheme Type landing'!J24","v":"A new sewer collecting both\nfoul and surface water."}]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Scheme Type COMBINED","index":7,"n_rows":32,"n_cols":12,"rows_kept":32,"cut":false,"hidden":false,"merged":[[23,3,23,4],[23,5,27,6],[23,7,27,8],[23,9,27,10],[31,1,31,12],[21,3,21,4],[21,5,21,6],[21,7,21,8],[21,9,21,10],[22,3,22,4],[22,6,22,7],[22,9,22,10]]}
[]
[]
[]
[]
[]
[null,null,"Select the type of scheme you're developing",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Please select your scheme type by clicking on the appropriate button below and click next"]
[null,null,"If your scheme includes more than one type, you will need to calculate each one separately"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D22","v":"Water main"},null,{"$f":"'Scheme Type landing'!F22","v":"Foul water sewer"},null,{"$f":"'Scheme Type landing'!H22","v":"Surface water sewer"},null,{"$f":"'Scheme Type landing'!J22","v":"Combined sewer "}]
[]
[null,null,null,{"$f":"'Scheme Type landing'!D24:E24","v":"A new water main."},null,{"$f":"'Scheme Type landing'!F24","v":"A new sewer collecting foul water\ndischarged from sources such as\ntoilets, baths, showers, sinks\nand washing machines."},null,{"$f":"'Scheme Type landing'!H24","v":"A new sewer collecting rain or melted\nsnow that falls on surfaces such as\nroofs, patios, driveways and roads."},null,{"$f":"'Scheme Type landing'!J24","v":"A new sewer collecting both\nfoul and surface water."}]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Parameters","index":8,"n_rows":29,"n_cols":11,"rows_kept":29,"cut":false,"hidden":false,"merged":[[28,1,28,10]]}
[1]
[]
[]
[]
[]
[null,null,"Scheme parameters - complete all fields before clicking next",null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[]
[null,null,null,"Scheme type selected",null,{"$f":"scheme_selected","v":"Water main"}]
[]
[]
[null,null,null,"Customer name",null,null,"",null,null,null,{"$f":"IF(F12=\"\",1,0)","v":1}]
[]
[null,null,null,"Scheme title",null,null,"",null,null,null,{"$f":"IF(F14=\"\",1,0)","v":1}]
[]
[null,null,null,"Site address",null,null,"",null,null,null,{"$f":"IF(F16=\"\",1,0)","v":1}]
[]
[null,null,null,"Total scheme charges",null,null,"",null,null,null,{"$f":"IF(ISERROR(F18-F18)=TRUE,1,IF(F18<=0,1,0))","v":1}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(F20-F20)=TRUE,1,IF(F20<=0,1,0))","v":1}]
[null,null,null,"Number of properties",null,null,{"$f":"IFERROR(IF(OR(Properties=0,Occupancy=\"<Select>\"),\"\",IF(OR(Properties<VLOOKUP(Occupancy,'TW Inputs'!L60:N65,2,0),Properties>VLOOKUP(Occupancy,'TW Inputs'!L60:N65,3,0)),\"Error! Number of properties doesn't correspond to the occupancy profile selected\",\"\")),\"\")","v":""},null,null,null,{"$f":"IFERROR(IF(OR(Properties=0,Occupancy=\"<Select>\"),\"\",IF(OR(Properties<VLOOKUP(Occupancy,'TW Inputs'!L60:N66,2,0),Properties>VLOOKUP(Occupancy,'TW Inputs'!$L$60:$N$66,3,0)),1,0)),0)","v":""}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Occupancy=\"<Select>\",1,0)+I23","v":1}]
[null,null,null,"Occupancy profile",null,{"$f":"IF(Occupancy=\"BESPOKE PROFILE\",\"Please enter your bespoke occupancy profile on the next screen\",IFERROR(IF(Occupancy=VLOOKUP(Occupancy,Occupancy_Profiles,1,0),\"\",1),\"Invalid entry, please select from the dropdown list\"))","v":""}]
[null,null,null,null,null,null,null,null,{"$f":"IFERROR(IF(Occupancy=VLOOKUP(Occupancy,Occupancy_Profiles,1,0),0,1),1)","v":0},null,{"$f":"SUM(K12:K21)","v":6}]
[]
[]
[]
[]
[]
[null,{"$f":"\"        © Thames Water Utilities Limited \"&cal_year","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"BespokeOccupancy","index":9,"n_rows":30,"n_cols":18,"rows_kept":30,"cut":false,"hidden":false,"merged":[[29,1,29,17]]}
[]
[]
[]
[]
[]
[null,null,"Scheme parameters - complete all fields before clicking next",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,null,"Enter your bespoke 12 year occupancy profile *"]
[null,null,null,"Remember to make allowances for properties being purchased but remaining empty"]
[]
[]
[null,null,null,"Year 1:",null,null,"7:"]
[]
[null,null,null,"2:",null,null,"8:"]
[]
[null,null,null,"3:",null,null,"9:",null,null,null,null,null,null,null,null,null,null,{"$f":"COUNTA(bespoke_Yr1,bespoke_Yr2,bespoke_Yr3,bespoke_Yr4,bespoke_Yr5,bespoke_Yr6,bespoke_Yr7,bespoke_Yr8,bespoke_Yr9,bespoke_Yr10,bespoke_Yr11,bespoke_Yr12)","v":0}]
[]
[null,null,null,"4:",null,null,"10:",null,null,null,null,null,null,null,null,null,null,{"$f":"IF(R16<>12,0,IF(OR(bespoke_Yr2<bespoke_Yr1=TRUE,bespoke_Yr3<bespoke_Yr2=TRUE,bespoke_Yr4<bespoke_Yr3=TRUE,bespoke_Yr5<bespoke_Yr4=TRUE,bespoke_Yr6<bespoke_Yr5=TRUE,bespoke_Yr7<bespoke_Yr6=TRUE,bespoke_Yr8<bespoke_Yr7=TRUE,bespoke_Yr9<bespoke_Yr8=TRUE,bespoke_Yr10<bespoke_Yr9=TRUE,bespoke_Yr11<bespoke_Yr10=TRUE,bespoke_Yr12<bespoke_Yr11=TRUE),1,0))","v":0}]
[]
[null,null,null,"5:",null,null,"11:"]
[]
[null,null,null,"6:",null,null,"12:",null,null,{"$f":"IF(AND(R16=12,R18=1),\"Error! A year's occupancy % should be equal to or higher than the prior year's. Please correct the highlighted year(s).\",\"\")","v":""},null,null,null,null,null,null,null,{"$f":"IF(AND(bespoke_completion=12,R18=0)=TRUE,0,1)","v":1}]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Result WATER","index":10,"n_rows":31,"n_cols":12,"rows_kept":31,"cut":false,"hidden":false,"merged":[[30,1,30,12],[11,6,14,8],[11,9,14,10],[19,6,22,8],[19,9,22,10],[16,6,17,10],[20,3,21,4]]}
[]
[]
[]
[]
[]
[null,null,"The calculated result",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Based on your scheme type and entered parameters, the calculated result is below"]
[]
[]
[]
[null,null,null,null,null,null,"If self-laying, Thames Water will contribute: *",null,null,{"$f":"'Full Calc'!$F$82","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,null,null,null,null,null,"or"]
[]
[]
[null,null,null,null,null,null,"If Thames Water completes the work, you'll need to contribute:",null,null,{"$f":"'Full Calc'!$F$74","v":{"$error":"#N/A"}}]
[null,null,null,{"$f":"'Scheme Type landing'!D22","v":"Water main"}]
[]
[]
[]
[]
[null,null,null,null,null,null,{"$f":"\"   Charges for any works carried out by Thames Water will be deducted from this sum as set out in the \"&charging_year&\" Charging Arrangements\"","v":"   Charges for any works carried out by Thames Water will be deducted from this sum as set out in the 2019/20 Charging Arrangements"}]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Result FOUL","index":11,"n_rows":31,"n_cols":12,"rows_kept":31,"cut":false,"hidden":false,"merged":[[15,6,18,8],[15,9,18,10],[30,1,30,12],[21,6,21,10],[20,3,21,4]]}
[]
[]
[]
[]
[]
[null,null,"The calculated result",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Based on your scheme type and entered parameters, the calculated result is below"]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,"The total contribution you'll need to pay:",null,null,{"$f":"'Full Calc'!$F$74","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!F22","v":"Foul water sewer"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Result SURFACE","index":12,"n_rows":31,"n_cols":12,"rows_kept":31,"cut":false,"hidden":false,"merged":[[15,6,18,8],[15,9,18,10],[30,1,30,12],[21,6,21,10],[20,3,21,4]]}
[]
[]
[]
[]
[]
[null,null,"The calculated result",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Based on your scheme type and entered parameters, the calculated result is below"]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,"The total contribution you'll need to pay:",null,null,{"$f":"'Full Calc'!$F$74","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!H22","v":"Surface water sewer"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Result COMBINED","index":13,"n_rows":31,"n_cols":12,"rows_kept":31,"cut":false,"hidden":false,"merged":[[15,6,18,8],[15,9,18,10],[21,6,21,10],[30,1,30,12],[20,3,21,4]]}
[]
[]
[]
[]
[]
[null,null,"The calculated result",null,null,null,null,null,null,null,null,{"$f":"'Scheme Type landing'!$L$6","v":"Thames Water Discounted Aggregate Deficit Model"}]
[]
[null,null,"Based on your scheme type and entered parameters, the calculated result is below"]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,"The total contribution you'll need to pay:",null,null,{"$f":"'Full Calc'!$F$74","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,null,null,{"$f":"'Scheme Type landing'!J22","v":"Combined sewer "}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,{"$f":"Parameters!B29","v":"        © Thames Water Utilities Limited 2019"}]

{"sheet":"Full Calc","index":14,"n_rows":101,"n_cols":11,"rows_kept":101,"cut":false,"hidden":false,"merged":[[100,2,100,11],[15,10,15,11],[73,6,73,7],[91,2,91,11],[88,2,88,11],[87,2,87,11],[30,5,30,6],[15,5,15,6],[21,5,22,9],[92,2,92,11],[93,2,93,11]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Thames Water Discounted Aggregate Deficit Model"]
[]
[]
[null,null,null,"Type of scheme",null,{"$f":"scheme_type","v":"Water main"},null,null,"Date and time of calculation",null,{"$f":"Date_of_calc","v":"2018-02-05T17:51:00"}]
[]
[null,null,null,"Customer name",null,{"$f":"Parameters!F12","v":0}]
[]
[null,null,null,"Scheme title",null,{"$f":"Scheme_Title","v":0}]
[]
[null,null,null,"Site address",null,{"$f":"Parameters!F16","v":0}]
[]
[]
[]
[]
[]
[]
[null,null,null,"Scheme parameters"]
[]
[null,null,null,"Total scheme cost",null,{"$f":"Cost","v":0},null,null,"Occupancy profile",null,{"$f":"IF(Occupancy=\"Bespoke Profile\",\"Bespoke profile\",LOWER(Occupancy))","v":"<select>"}]
[]
[null,null,null,"Number of properties",null,{"$f":"Properties","v":0},null,null,"Applicable average income per property",null,{"$f":"IF(scheme_type=\"Water main\",$K$39,IF(scheme_type=\"Combined sewer\",$K$45,IF(scheme_type=\"Surface water sewer\",$K$43,IF(scheme_type=\"Foul water sewer\",$K$41,0))))","v":196.44}]
[]
[]
[]
[]
[]
[null,null,null,"Variables for calculation",null,null,null,null,"Water income per property",null,{"$f":"'TW Inputs'!E51","v":196.44}]
[]
[null,null,null,"Borrowing interest rate",null,2.75E-2,null,null,"Foul water income per property",null,{"$f":"'TW Inputs'!E49","v":145.62}]
[]
[null,null,null,"Net present value discount rate",null,2.75E-2,null,null,"Surface water income per property",null,{"$f":"'TW Inputs'!E50","v":26.4}]
[]
[null,null,null,"Long term annual inflation",null,0.03,null,null,"Combined sewer income per property",null,{"$f":"'TW Inputs'!E52","v":172.02}]
[]
[]
[]
[]
[]
[null,null,null,"Deficit calculation"]
[null,null,null,"Year","Cumulative\noccupancy","Cumulative\nproperties","Income\nper property","Projected\nfuture revenue","Annual\nfinancing cost","Deficit\nin the year","Present\nvalue"]
[null,null,null,1,{"$f":"HLOOKUP(Occupancy,Occupancy_table,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"Properties*E53","v":{"$error":"#N/A"}},{"$f":"Ave_Income*(1+Inflation_Rate)^($D53-1)","v":196.44},{"$f":"ROUND(G53*F53,0)","v":{"$error":"#N/A"}},{"$f":"ROUND(-PMT(Interest_Rate,Years,Total_Scheme_Cost),0)","v":0},{"$f":"IF(I53-H53>0,I53-H53,0)","v":{"$error":"#N/A"}},{"$f":"ROUND(J53*(1/(Discount_Rate+1)^D53),0)","v":{"$error":"#N/A"}}]
[null,null,null,2,{"$f":"HLOOKUP(Occupancy,Occupancy_table,3,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},202.33320000000001,{"$f":"ROUND(G54*F54,0)","v":{"$error":"#N/A"}},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,3,{"$f":"HLOOKUP(Occupancy,Occupancy_table,4,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},208.40319599999998,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,4,{"$f":"HLOOKUP(Occupancy,Occupancy_table,5,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},214.65529187999999,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,5,{"$f":"HLOOKUP(Occupancy,Occupancy_table,6,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},221.09495063639997,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,6,{"$f":"HLOOKUP(Occupancy,Occupancy_table,7,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},227.72779915549197,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,7,{"$f":"HLOOKUP(Occupancy,Occupancy_table,8,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},234.55963313015673,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,8,{"$f":"HLOOKUP(Occupancy,Occupancy_table,9,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},241.59642212406146,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,9,{"$f":"HLOOKUP(Occupancy,Occupancy_table,10,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},248.84431478778328,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,10,{"$f":"HLOOKUP(Occupancy,Occupancy_table,11,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},256.30964423141677,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,11,{"$f":"HLOOKUP(Occupancy,Occupancy_table,12,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},263.99893355835928,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,12,{"$f":"HLOOKUP(Occupancy,Occupancy_table,13,FALSE)","v":{"$error":"#N/A"}},{"$error":"#N/A"},271.91890156511005,{"$error":"#N/A"},0,{"$error":"#N/A"},{"$error":"#N/A"}]
[]
[null,null,null,null,null,null,null,null,null,"Total net present value:",{"$f":"SUM(K53:K64)","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[]
[null,null,null,"Discounted aggregate deficit"]
[]
[null,null,null,"Developer contribution",null,{"$f":"lumpsum","v":{"$error":"#N/A"}},{"$f":"F31-F74","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[]
[null,null,null,"Self lay asset payment"]
[]
[null,null,null,"Gross self lay asset payment",null,{"$f":"F31-F74","v":{"$error":"#N/A"}},"(Before deduction of charges for works carried out by Thames Water)"]
[]
[]
[]
[]
[]
[null,null,"IMPORTANT"]
[null,null,{"$f":"DisclaimerText2&CHAR(10)&CHAR(10)&DisclaimerText3&CHAR(10)&CHAR(10)&BespokeDisclaimer","v":"This model is an estimating tool only and the final charge will be as detailed on the quotation we provide on application.\n\nThis version of the model is valid from 1st April 2019 to 31st March 2020 only. \n\nPlease note that if you have used your own bespoke occupancy profile in this model, Thames Water will need to review and agree your proposed occupancy rates."}]
[]
[]
[null,null,"If you need assistance please contact the Thames Water Developer Services helpdesk:"]
[null,null,"Email developer.services@thameswater.co.uk"]
[null,null,"Call 0800 009 3921\n(lines open 8am-5pm Mon-Fri excl. bank holidays)"]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"\"© Thames Water Utilities Limited \"&cal_year","v":"© Thames Water Utilities Limited 2019"}]