{"text_format":"1","workbook":"xlsx","n_sheets":20,"sheets":["Title","ii) Table of Contents","iii) Description","iv) Instructions","v) Assumptions","vi) Start","1.1) Community Data","1.2) Existing System","1.3) Existing Expenditures","1.4) Existing Revenue","1.5) Results","2.1) Start","2.2) Design Parameters","2.3) Treatment Upgrades","2.4) Pumping Upgrades","2.5) Distribution Upgrades","2.6) Cost Curve Predictions","2.7) Financing Results","Calculations","Table Inputs"],"not_rendered":{"charts":6,"images":2,"macros":1,"external_links":0,"defined_names":24,"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":"Title","index":0,"n_rows":20,"n_cols":17,"rows_kept":20,"cut":false,"hidden":false,"merged":[[1,1,1,19],[19,8,19,10],[19,11,19,13],[19,14,19,15],[19,16,19,17],[13,8,13,17]]}
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Province of Newfoundland & Labrador"]
[null,null,"Full Cost Accounting Assessment Tool"]
[null,null,"To Achieve Complete Cost Recovery of Drinking Water"]
[null,null,"Supply System Operation & Maintenance"]
[]
[null,null,"Community:"]
[null,null,"Geographic Region:"]
[]
[]
[]
[]
[null,null,"Date:",null,null,null,null,null,null,null,null,null,null,null,"Year:",null,2017]

{"sheet":"ii) Table of Contents","index":1,"n_rows":27,"n_cols":15,"rows_kept":27,"cut":false,"hidden":false,"merged":[[6,14,6,17],[8,14,8,17],[1,1,1,19],[2,14,2,17],[4,14,4,17],[5,14,5,17],[2,4,2,13],[26,9,26,10],[21,14,21,17],[20,14,20,17],[7,14,7,17],[10,14,10,17],[11,14,11,17],[12,14,12,17],[9,14,9,17],[13,14,13,17],[14,14,14,17],[16,14,16,17],[17,14,17,17],[18,14,18,17],[19,14,19,17],[15,14,15,17]]}
[]
[null,"Table of Contents"]
[null,null,"Page #",null,"Title",null,null,null,null,null,null,null,null,null,"Tab Details"]
[]
[null,null,"i",null,"Title Page",null,null,null,null,null,null,null,null,null,"Title Slide"]
[null,null,"ii",null,"Table of Contents",null,null,null,null,null,null,null,null,null,"Information"]
[null,null,"iii",null,"Description of Cost Accounting Tool",null,null,null,null,null,null,null,null,null,"Information"]
[null,null,"iv",null,"Instructions",null,null,null,null,null,null,null,null,null,"Information"]
[null,null,"v",null,"Assumptions",null,null,null,null,null,null,null,null,null,"Information"]
[null,null,"vi",null,"Start",null,null,null,null,null,null,null,null,null,"Information"]
[null,null,1.1000000000000001,null,"Community Data",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,1.2,null,"Existing System",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,1.3,null,"Existing Expenditures",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,1.4,null,"Existing Revenue",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,1.5,null,"Part 1 Results - Full Cost Accounting",null,null,null,null,null,null,null,null,null,"Results"]
[null,null,2.1,null,"Part 2 - Start",null,null,null,null,null,null,null,null,null,"Information"]
[null,null,2.2000000000000002,null,"Design Parameters for Upgrades",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,2.2999999999999998,null,"Description of Proposed Treatment System",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,2.4,null,"Description of Pumping System Upgrades",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,2.5,null,"Description of Distribution System Upgrades",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,2.6,null,"Cost Curve Predictions",null,null,null,null,null,null,null,null,null,"Data Entry"]
[null,null,2.7,null,"Part 2 Results - Financing and Water Taxes",null,null,null,null,null,null,null,null,null,"Results"]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"ii"]

{"sheet":"iii) Description","index":2,"n_rows":10,"n_cols":10,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,19],[4,3,4,17],[9,9,9,10]]}
[]
[null,"Description of Full Cost Accounting Assessment Tool"]
[]
[]
[null,null,null,"The purpose of the Full Cost Accounting Assessment Tool is to help owners of public drinking water systems in the Province of Newfoundland and Labrador to predict the costs of operating their systems to establish water taxes that will adequately fund proper operation. The second part of the Full Cost Accounting Assessment Tool is for communities that are considering upgrades to their treatment system. The Full Cost Accounting Assessment Tool will generate a water tax based on predicted capital and annual operation and maintenance costs. The sole purpose of the Full Cost Accounting Assessment Tool is to provide information for establishing water taxes. It should not be used to estimate capital budgets. Non-Operating costs such as depreciation and debt servicing, which are typically costs included in full-cost accounting for water systems, have not been included in the calculations. Provincial or Federal sales or services taxes have also not been included. The Full Cost Accounting Assessment Tool is set-up to determine the amount of revenue that is needed to cover the operating costs by detailing the typical operating costs for a water system on a break even basis."]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"iii"]

{"sheet":"iv) Instructions","index":3,"n_rows":16,"n_cols":10,"rows_kept":16,"cut":false,"hidden":false,"merged":[[15,9,15,10],[9,3,9,17],[1,1,1,19],[4,3,4,17],[5,3,5,17],[6,3,6,17],[7,3,7,17]]}
[]
[null,"Instructions"]
[]
[]
[null,null,"-","Complete the Full Cost Accounting Assessment Tool by filling out data in each of the pages. The Full Cost Accounting Assessment Tool includes two parts: Part 1 is an analysis of the existing operation and maintenance costs and water taxes, Part 2 is an analysis of future upgrade costs for communities that are considering upgrades to their water treatment system(s).  "]
[null,null,"-","The colour of each tab (bottom of screen) indicates the following: grey tabs introduction section, blue tabs are for Part 1 of the Tool, and green tabs are for Part 2 of the Tool. "]
[null,null,"-","Buttons have been placed throughout the Full Cost Accounting Assessment Tool to assist with navigation from one tab to the next, as well as to access pages containing additional information."]
[null,null,"-","Data entry cells are white with a black outline. This tool also uses drop-down menus and selector options buttons."]
[null,null,"-","Where prompted, enter the most recent available information. "]
[null,null,"-","For communities with more than one water supply system, the Full Cost Accounting Assessment Tool may be completed separately for each system, or it can be completed for all systems at once. However, if completing the tool for more than one system, make sure to add the values for all systems before entering in the data entry cells."]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"iv"]

{"sheet":"v) Assumptions","index":4,"n_rows":16,"n_cols":10,"rows_kept":16,"cut":false,"hidden":false,"merged":[[15,9,15,10],[10,3,10,17],[1,1,1,19],[9,3,9,17],[4,3,4,17],[5,3,5,17],[8,3,8,17],[7,3,7,17],[6,3,6,17]]}
[]
[null,"Limitations"]
[null,null,"Limitations"]
[]
[null,null,"-","This program relies on conceptual cost curves to predict capital and operation and maintenance costs based on user entered data. The cost data is based on 2017 costs and is inflated using 3% inflation rate for subsequent years."]
[null,null,"-","The cost curves were developed based on data from water supply systems constructed in Newfoundland and Labrador and information supplied by various vendors. The curves were calculated based on many assumptions and allowances. The program is intended only to help communities establish water taxes. "]
[null,null,"-","Cost predictions generated by this program do not include depreciation. The tool does not consider existing non-operating expenses such as debt servicing. "]
[null,null,"-","All cost predictions are provided in Canadian dollars"]
[null,null,"-","Cost predictions DO NOT include applicable taxes"]
[null,null,"-","The cost predictions generated through this tool do not include special costs for land acquisition, permit costs, legal fees, or site specific costs."]
[null,null,"-","Actual capital costs and operation and maintenance costs for system upgrades may vary substantially from those calculated by this program. It is recommended that a site specific engineering study should be completed to establish costs used for capital budgeting."]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"v"]

{"sheet":"vi) Start","index":5,"n_rows":14,"n_cols":10,"rows_kept":14,"cut":false,"hidden":false,"merged":[[5,6,5,17],[8,6,8,17],[1,1,1,19],[13,9,13,10]]}
[]
[null,"Section Selection"]
[]
[]
[null,null,null,null,null,null,"SECTION 1 - ANALYSIS OF EXISTING COSTS"]
[null,null,null,null,null,null,"In this section, users will enter existing operation and maintenance costs and revenue data. The tool will evaluate the existing operation and maintenance costs and will provide a recommended water tax to adequately fund the operation of the water supply system."]
[]
[null,null,null,null,null,null,"SECTION 2 - ANALYSIS OF UPGRADE COSTS"]
[null,null,null,null,null,null,"This section is for communities that are evaluating capital upgrade options. The community can select a type of treatment system to construct, and the tool will provide a range of potential capital and operation and maintenance costs. The tool will then generate a water tax to fund the capital and operation and maintnenace costs of the proposed system. "]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"vi"]

{"sheet":"1.1) Community Data","index":6,"n_rows":26,"n_cols":18,"rows_kept":26,"cut":false,"hidden":false,"merged":[[18,11,18,12],[19,11,19,12],[18,15,18,16],[25,9,25,10],[1,1,1,19],[2,1,2,19],[6,8,6,17],[5,8,5,17],[10,16,10,17],[14,2,14,18],[19,8,19,9],[17,8,17,9],[18,8,18,9],[16,15,16,18],[16,8,16,9],[17,15,17,16],[17,11,17,12]]}
[]
[null,"PART 1"]
[null,"Community Data"]
[null,null,"General"]
[]
[null,null,"System:"]
[null,null,"Type of Water Supply:"]
[]
[null,null,"Population"]
[]
[null,null,"Enter the permanent service population for the water system being evaluated:"]
[]
[null,null,"Existing System Flows"]
[]
[null,null,"Enter flow data below and select the correct units."]
[null,null,"Note: If flow data is not entered, the Cost Accounting Tool will estimate flows based on population data."]
[null,null,null,null,null,null,null,"Value","Units",null,null,null,null,null,null,{"$f":"IF(OR(H18=0,I18=\"<Units>\"),\"\",\"Per Capita Consumption\")","v":""}]
[null,null,"Average Flow/Water Use:",null,null,null,null,null,"<Units>",null,{"$f":"IF(H18>0,Calculations!G51,\"\")","v":""},{"$f":"IF(H18=0,\"\",\"m3/d\")","v":""},null,null,null,{"$f":"IF(OR(H18=0,I18=\"<Units>\"),\"\",ROUND(Calculations!D62,0))","v":""},null,{"$f":"IF(OR(H18=0,I18=\"<Units>\"),\"\",\"L/p/d\")","v":""}]
[null,null,"Maximum Day Flow:",null,null,null,null,null,"<Units>",null,{"$f":"IF(I19=\"<Units>\",\"\",IF(H19>0,Calculations!G52,\"\"))","v":""},{"$f":"IF(H19=0,\"\",\"m3/d\")","v":""},null,null,null,{"$f":"IF(OR(H19=0,I19=\"<Units>\"),\"\",ROUND(Calculations!D63,0))","v":""},null,{"$f":"IF(OR(H19=0,I19=\"<Units>\"),\"\",\"L/p/d\")","v":""}]
[null,null,"Peak Hour Flow:",null,null,null,null,null,"<Units>",null,{"$f":"IF(I20=\"<Units>\",\"\",IF(H20>0,Calculations!G53,\"\"))","v":""},{"$f":"IF(H20=0,\"\",\"m3/d\")","v":""}]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,1.1000000000000001]

{"sheet":"1.2) Existing System","index":7,"n_rows":46,"n_cols":15,"rows_kept":46,"cut":false,"hidden":false,"merged":[[45,9,45,10],[1,1,1,19],[2,1,2,19],[28,2,30,13],[39,3,40,14],[23,2,26,11]]}
[]
[null,"PART 1"]
[null,"Description of Existing Water Supply System"]
[null,null,"Existing Treatment & Disinfection Systems"]
[]
[null,null,"Select the disinfection system(s) operated at the facility:"]
[null,null,null,"Sodium Hypochlorite (liquid chlorine)"]
[null,null,null,"Chlorine Gas"]
[null,null,null,"Onsite Sodium Hypochlorite Generation (Mixed Oxidants)"]
[null,null,null,"Calcium Hypochlorite (chlorine powder)"]
[null,null,null,"UV Disinfection"]
[]
[]
[null,null,"Existing Treatment & Disinfection Systems"]
[]
[null,null,"If the treatment process includes pH adjustment, select the chemical that is used:"]
[null,null,null,"None",null,null,"Soda Ash",null,null,null,"Lime",null,null,null,"Caustic Soda"]
[]
[null,null,"If applicable, select the treatment processes that are provided at the existing facility:"]
[null,null,null,"Inline Filtration (for particle removal)",null,null,null,null,null,null,null,"Membrane Treatment"]
[null,null,null,"Iron / Manganese Removal",null,null,null,null,null,null,null,"Conventional WTP"]
[null,null,null,"Arsenic Removal",null,null,null,null,null,null,null,"PWDU"]
[]
[null,null,"Does the existing water distribution system include chlorine booster stations?"]
[]
[]
[]
[]
[null,null,"If chlorine boosting is included, what fraction of the distribution system receives water that has been boosted with chlorine?"]
[]
[]
[]
[null,null,"Existing Pumping Systems"]
[]
[null,null,"Does the existing water supply system include any of the following pumping systems:"]
[null,null,null,"Ground water pumping"]
[null,null,null,"Low lift pumping (pumping from surface water source to treatment building)"]
[null,null,null,"High lift pumping (pumping from treatment building to distribution system)"]
[null,null,null,"Distribution System Pressure Booster Pumping"]
[null,null,null,"If distribution system booster pumping is provided, approximately what fraction of the distribution system is fed from the booster pumping station?"]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,1.2]

{"sheet":"1.3) Existing Expenditures","index":8,"n_rows":151,"n_cols":17,"rows_kept":151,"cut":false,"hidden":false,"merged":[[55,3,55,18],[59,3,59,18],[2,1,2,19],[57,2,57,18],[58,16,58,18],[44,2,44,18],[36,3,36,18],[37,3,37,18],[38,3,38,18],[39,3,39,18],[40,3,40,18],[41,3,41,18],[45,2,45,18],[52,3,52,18],[53,3,53,18],[26,3,26,18],[27,3,27,18],[30,2,30,18],[31,3,31,18],[29,2,29,18],[32,3,32,18],[33,3,33,18],[34,3,34,18],[3,2,3,18],[6,16,6,18],[13,2,13,18],[8,16,8,18],[9,16,9,18],[20,2,20,18],[7,16,7,18],[11,16,11,18],[10,16,10,18],[5,2,5,18],[10,2,10,15],[90,2,90,19],[91,2,91,7],[92,2,92,7],[93,2,93,7],[94,2,94,7],[95,2,95,7],[96,2,96,7],[97,2,97,7],[79,2,79,19],[81,2,81,7],[82,2,82,7],[83,2,83,7],[84,2,84,7],[85,2,85,7],[86,2,86,7],[81,8,81,19],[82,8,82,19],[83,8,83,19],[84,8,84,19],[85,8,85,19],[86,8,86,19],[80,2,80,7],[80,8,80,19],[98,2,98,7],[91,8,91,19],[92,8,92,19],[93,8,93,19],[94,8,94,19],[95,8,95,19],[96,8,96,19],[97,8,97,19],[98,8,98,19],[102,2,102,19],[103,2,103,7],[104,2,104,7],[105,2,105,7],[106,2,106,7],[107,2,107,7],[108,2,108,7],[109,2,109,7],[110,2,110,7],[111,2,111,7],[112,2,112,7],[113,2,113,7],[114,2,114,7],[115,2,115,7],[116,2,116,7],[117,2,117,7],[103,8,103,19],[104,8,104,19],[105,8,105,19],[106,8,106,19],[107,8,107,19],[108,8,108,19],[109,8,109,19],[110,8,110,19],[111,8,111,19],[112,8,112,19],[113,8,113,19],[114,8,114,19],[115,8,115,19],[116,8,116,19],[117,8,117,19],[122,2,122,19],[123,2,123,7],[124,2,124,7],[125,2,125,7],[126,2,126,7],[127,2,127,7],[128,2,128,7],[129,2,129,7],[130,2,130,7],[132,2,132,7],[133,2,133,7],[134,2,134,7],[135,2,135,7],[123,8,123,19],[124,8,124,19],[125,8,125,19],[126,8,126,19],[127,8,127,19],[128,8,128,19],[129,8,129,19],[130,8,130,19],[131,8,131,19],[132,8,132,19],[133,8,133,19],[134,8,134,19],[135,8,135,19],[35,3,35,18],[54,3,54,18],[149,2,149,7],[150,2,150,7],[141,8,141,19],[142,8,142,19],[143,8,143,19],[144,8,144,19],[145,8,145,19],[146,8,146,19],[147,8,147,19],[148,8,148,19],[149,8,149,19],[150,8,150,19],[140,2,140,19],[141,2,141,7],[142,2,142,7],[143,2,143,7],[144,2,144,7],[145,2,145,7],[146,2,146,7],[147,2,147,7],[148,2,148,7],[131,2,131,7],[65,9,65,10],[1,1,1,19],[58,2,58,15],[46,3,46,18],[47,3,47,18],[48,3,48,18],[49,3,49,18],[50,3,50,18],[51,3,51,18],[6,2,6,15],[7,2,7,15],[8,2,8,15],[9,2,9,15],[11,2,11,15],[21,2,21,15],[15,3,15,17],[16,3,16,17],[17,3,17,17],[18,3,18,17],[21,16,21,18],[22,3,22,18],[23,3,23,18],[24,3,24,18],[25,3,25,18]]}
[]
[null,"PART 1"]
[null,"Existing Expenditures"]
[null,null,"Enter the actual expenditures from the most recent year. Items that would be included within each category are listed below, and a detailed description of each item can be reviewed by clicking the \"Detailed Descriptions\" buttons. "]
[]
[null,null,"Expenditures"]
[null,null,"Expeniture Category",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D177&\" Actual Expenditures\"","v":"2016 Actual Expenditures"}]
[null,null,"Source of Supply"]
[null,null,"Pumping & Power"]
[null,null,"Water Disinfection & Treatment"]
[null,null,"Transmission & Distribution "]
[null,null,"Administration"]
[]
[null,null,"Source of Supply"]
[null,null,"These costs may include the following items:"]
[null,null,"-","Wages and Benefits "]
[null,null,"-","Maintenance of Source of Supply - Surface Water Watersheds incl. travel"]
[null,null,"-","Maintenance of Source of Supply - Groundwater Recharge Areas incl. travel"]
[null,null,"-","Professional Services - Consulting & Engineering"]
[]
[null,null,"Power & Pumping"]
[null,null,"These costs may include the following items:"]
[null,null,"-","Wages and Benefits "]
[null,null,"-","Maintenance of Pump Station Structures incl. travel"]
[null,null,"-","Maintenance of Pumps and Controls incl. travel"]
[null,null,"-","Electricity"]
[null,null,"-","Fuel for Generators and for Building Heat"]
[null,null,"-","Professional Services"]
[]
[null,null,"Water Disinfection and Treatment"]
[null,null,"These costs may include the following items:"]
[null,null,"-","Wages and Benefits "]
[null,null,"-","Electricity and Fuel"]
[null,null,"-","Chemicals"]
[null,null,"-","Maintenance of Treatment Plant Equipment"]
[null,null,"-","Maintenance and Repair of Buildings and Structures"]
[null,null,"-","Maintenance of Computer Controls and SCADA"]
[null,null,"-","Materials and Supplies"]
[null,null,"-","Sludge Handling and Disposal"]
[null,null,"-","Water Testing"]
[null,null,"-","Vehicle Expense Including Vehicles and Operating Costs"]
[null,null,"-","Professional Services"]
[]
[]
[null,null,"Transmission & Distribution "]
[null,null,"These costs may include the following items:"]
[null,null,"-","Wages and Benefits "]
[null,null,"-","Maintenance of Water Mains Including Valves"]
[null,null,"-","Maintenance of Service Laterals to the Property Boundary"]
[null,null,"-","Maintenance of Water Reservoirs and Storage Tanks"]
[null,null,"-","Maintenance of Hydrants Including Hydrant Valves"]
[null,null,"-","Cell Phones and Dedicated Communication Lines"]
[null,null,"-","Supplies/Materials/Tools/Equipment"]
[null,null,"-","Leak Detection"]
[null,null,"-","Vehicle Expense Including Vehicles and Operating Costs"]
[null,null,"-","Professional Services"]
[]
[null,null,"Administration"]
[null,null,"These costs may include the following items:"]
[null,null,"-","Administration costs allocated to drinking water service"]
[null,null,"*Note: Administration costs are typically in the range of 10% of all other operation and maintenance costs"]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,1.3]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Source of Supply"]
[null,null,"Item",null,null,null,null,null,"Description"]
[null,null,"Source of Supply",null,null,null,null,null,"The Source of Supply expenses relate to the maintenance of the surface watershed for surface water supplies and the recharge area for groundwater supplies. "]
[null,null,"Wages and Benefits",null,null,null,null,null,"This expense item is the estimated cost for staff to maintain the watershed or groundwater recharge area. Staff time could relate to cleaning of intake screens for surface water, tree cutting, garbage collection, placement and maintenance of signage, fencing and responding to public concerns to name a few items. Benefits relate to overheads including matching contributions for income tax, CPP, EI, health plans and vacations are included.  "]
[null,null,"Maintenance of Source of Supply Surface Watersheds including Travel",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the surface watershed.  This item might include replacing intake screen, materials for maintenance of the intake screens, gravel, stone or concrete for dam and/or intake maintenance, signage, gravel for roads and travel to the undertake work to name a few. "]
[null,null,"Maintenance of Source of Supply Groundwater Recharge Areas including Travel",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the groundwater recharge area.  This item might include drilling new supply and/or monitoring wells, cleaning screens on existing wells, wellhead maintenance, signage, gravel for roads, fencing and travel to undertake work to name a few.  "]
[null,null,"Professional Services",null,null,null,null,null,"This expense item relates to the hiring of outside consultants to undertake works that require professional input relating to the Source of Supply."]
[null,null,"Other ",null,null,null,null,null,"This category is included such that the Operator/Supervisor can add other items that are unique to the location."]
[]
[]
[]
[null,null,"Power and Pumping"]
[null,null,"Item",null,null,null,null,null,"Description"]
[null,null,"Power and Pumping",null,null,null,null,null,"The Power and Pumping expenses relate to the operation and maintenance of all pumping equipment associated with the water system including supply pumps for surface water, supply/well pumps for groundwater and booster pumps to boost water pressure in the distribution network. "]
[null,null,"Wages and Benefits ",null,null,null,null,null,"This expense item is the estimated cost for staff to operate and maintain all the pumping equipment in the system. Staff time could relate to routine checking of pump operation, routine cleaning and lubrication, and pressure checks to ensure the pumps are operating as per the requirements of the system. Benefits relate to overheads including matching contributions for income tax, CPP, EI, health plans and vacations are included.  "]
[null,null,"Maintenance of Pumps and Controls including Travel",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the pumping equipment including pump controls.  This item might include routine maintenance of pumps, monitoring of electrical power consumption, monitoring of pump outputs if flow meters are installed, inspection of pump control panels and travel to undertake work to name a few. "]
[null,null,"Electricity",null,null,null,null,null,"This expense is to cover the cost of supplying electricity to the pumping equipment including electricity for space heating if required."]
[null,null,"Fuel for Generators and Building Heat ",null,null,null,null,null,"This expense is to cover the cost of fuel for generators, diesel/gasoline powered backup pumps and heating oil/propane for building heat."]
[null,null,"Professional Services",null,null,null,null,null,"This expense item relates to the hiring of outside consultants to undertake works that require professional input relating to pumping requirements in the system."]
[null,null,"Other",null,null,null,null,null,"This category is included such that the Operator/Supervisor can add other items that are unique to the location"]
[]
[]
[]
[null,null,"Water Disinfection & Treatment"]
[null,null,"Item",null,null,null,null,null,"Description"]
[null,null,"Water Treatment",null,null,null,null,null,"The Water Treatment expenses relate to the operation and maintenance of the water treatment equipment associated with water system including operation and maintenance of water treatment plants and chlorination systems. "]
[null,null,"Wages and Benefits ",null,null,null,null,null,"This expense item is the estimated cost for staff to operate and maintain the water treatment equipment. Staff time could relate to regular checking of treatment plant operation, adjustment of the treatment processes including chemical feed rates, routine testing to verify treatment requirements and performance, and routine chlorine residual testing as per the requirements of the regulators. Benefits relate to overheads including matching contributions for income tax, CPP, EI, health plans and vacations are included.  "]
[null,null,"Maintenance of Pump Station Structures including Travel ",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the pump station structures.  This item might include routine maintenance on the structures including repair of damage caused by vandalism and/or storms and travel to undertake required works to name a few.  "]
[null,null,"Electricity and Fuel ",null,null,null,null,null,"This expense is to cover the cost of supplying electricity to the water treatment system including electricity and/or heating oil/propane for space heating if required."]
[null,null,"Chemicals",null,null,null,null,null,"This expense is to cover the cost of all chemicals used in the water treatment process."]
[null,null,"Maintenance of Treatment Plant Equipment ",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the water treatment equipment.  This item might include routine maintenance/replacment of chemical feed pumps, monitoring of electrical power consumption, monitoring of treatment plant production and backwash volumes, monitoring of turbidity and chlorine residual values to name a few. "]
[null,null,"Maintenance of Pump Station Structures",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the water treatment plant structures.  This item might include routine maintenance on the structures including repair of damage cause by vandalism and/or storms and travel to undertake work to name a few.  "]
[null,null,"Maintenance of Computer Controls and SCADA",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the computer systems in the water treatment plant including SCADA systems."]
[null,null,"Materials and Supplies",null,null,null,null,null,"This expense is to cover the cost of routine materials and supplies that are required for the operation of the Water Treatment Plant."]
[null,null,"Sludge Handling and Disposal",null,null,null,null,null,"This expense is to cover the cost of sludge handling at the Water Treatment Plant including short term storage, transportation (if required) and long term disposal."]
[null,null,"Water Testing ",null,null,null,null,null,"This expense is to cover the cost of routine water testing as required for the operation by the Regulators of the Water Treatment Plant."]
[null,null,"Vehicle Expense Including Vehicles and Operating Costs ",null,null,null,null,null,"This expense is to cover the cost of providing vehicles including operating costs that are required for the operation of the Water Treatment Plant. If dedicated vehicles are not required this expense item should be used for any travel expenses associated with the Water Treatment Plant."]
[null,null,"Professional Services ",null,null,null,null,null,"This expense item relates to the hiring of outside consultants to undertake works that require professional input relating to pumping requirements in the system."]
[null,null,"Other",null,null,null,null,null,"This category is included such that the Operator/Supervisor can add other items that are unique to the location"]
[]
[]
[]
[]
[null,null,"Transmission and Distribution"]
[null,null,"Item",null,null,null,null,null,"Description"]
[null,null,"Transmission and Distribution",null,null,null,null,null,"The Transmission and Distribution expenses relate to the operation and maintenance of all the water transmission and distribution system including the operation and maintenance of reservoirs/storage tanks, customer services/laterals and fire hydrants (if installed)."]
[null,null,"Wages and Benefits",null,null,null,null,null,"This expense item is the estimated cost for staff to maintain and operate the water distribution network. Staff time could relate to locating and repairing leaks in distribution pipes including service laterals, routine maintenance of pressure reducing valves, maintenance and repair of reservoirs/storage tanks and any hydrants that may be installed in the water system. Benefits relate to overheads including matching contributions for income tax, CPP, EI, health plans and vacations are included.  "]
[null,null,"Maintenance of Water Mains including Valves",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining the water transmission and distribution pipes including valves.  This item might include routine maintenance such as valve exercising and watermain flushing, location and repair of leaks, any outside contractors who provide backhoes/excavators and/or other equipment to name a few.  "]
[null,null,"Maintenance of Service Laterals to the Property Boundary",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining and repairing service laterals to customer properties including location of shut off valves on water mains and curb shut off valves at the property boundary and any outside contractors who provide backhoes/excavators and/or other equipment."]
[null,null,"Maintenance of Water Reservoirs and Storage Tanks",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining and repairing all water reservoirs and storage tanks in the water distribution system. This item might include routine inspection for leaks, repairs to structures, and maintenance of altitude valves and any outside contractors who provide backhoes/excavators and/or other equipment to name a few.  "]
[null,null,"Maintenance of Hydrants including Hydrant Valves ",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to maintaining hydrants (if installed) in the distribution system.  This item might include routine maintenance such as painting, leak repairs, exercising hydrant valves and any outside contractors who provide backhoes/excavators and/or other equipment to name a few.  "]
[null,null,"Cell Phones and Dedicated Communication Lines ",null,null,null,null,null,"This expense is to cover the cost of providing cell phones and any dedicated communication lines that may exist in the water distribution system."]
[null,null,"Supplies/Materials/Tools and Equipment ",null,null,null,null,null,"This expense is to cover the cost of routine supplies, materials, tools and equipment that are required for the operation of the water distribution system."]
[null,null,"Leak Detection ",null,null,null,null,null,"This expense is to cover the cost of leak detection services provided by outside service providers."]
[null,null,"Vehicle Expense Including Vehicles and Operating Costs ",null,null,null,null,null,"This expense is to cover the cost of providing vehicles including operating costs that are required for the operation and maintenance of the distribution system. If dedicated vehicles are not required this expense item should be used for any travel expenses associated with the water distribution system."]
[null,null,"Professional Services ",null,null,null,null,null,"This expense item relates to the hiring of outside consultants to undertake works that require professional input relating to pumping requirements in the system."]
[null,null,"Other",null,null,null,null,null,"This category is included such that the Operator/Supervisor can add other items that are unique to the location"]
[]
[]
[]
[]
[null,null,"Administration and General"]
[null,null,"Item",null,null,null,null,null,"Description"]
[null,null,"Administration and General",null,null,null,null,null,"The Administration and General expenses relate to the overall administration of the water system including customer service, billing, annual audits and the provision of insurance. "]
[null,null,"Wages and Benefits ",null,null,null,null,null,"This expense item is the estimated cost for staff to administer the water system. Staff time could relate to receiving and directing phone calls, preparation of bills for the water service, collection of bills, annual audit and provision of insurance. Benefits relate to overheads including matching contributions for income tax, CPP, EI, health plans and vacations are included.  "]
[null,null,"Administrative Office/Phones/Billing/Supplies ",null,null,null,null,null,"This expense item relates to expenses, other than Wages and Benefits, that pertain to operating the administration office.  This item might include office space, office supplies, postage, computer allowance including software, phones, billing and miscellaneous office supplies to name a few.  "]
[null,null,"Allowance for Doubtful Accounts ",null,null,null,null,null,"This expense is to cover the cost of doubtful accounts or bills that have been issued and payment is doubtful."]
[null,null,"Rents/Building Operation/Maintenance ",null,null,null,null,null,"This expense is to cover the cost of any building, or part of a building that are provided for the operation of the water system."]
[null,null,"Liability and Facility Insurance ",null,null,null,null,null,"This expense is to cover the cost of any insurance required for the operation of the water system."]
[null,null,"Regulatory/Legal/Auditor ",null,null,null,null,null,"This expense is to cover the cost of any regulatory requirements such as water rights, legal advice and audit services required by the water system."]
[null,null,"Professional Services ",null,null,null,null,null,"This expense item relates to the hiring of outside consultants to undertake works that require professional input relating to pumping requirements in the system."]
[null,null,"Other",null,null,null,null,null,"This category is included such that the Operator/Supervisor can add other items that are unique to the location."]

{"sheet":"1.4) Existing Revenue","index":9,"n_rows":32,"n_cols":17,"rows_kept":32,"cut":false,"hidden":false,"merged":[[31,9,31,10],[1,1,1,19],[25,16,25,17],[6,16,6,17],[2,1,2,19],[7,16,7,17],[19,16,19,17],[18,16,18,17],[21,16,21,17],[11,16,11,17],[12,16,12,17],[13,16,13,17]]}
[]
[null,"PART 1"]
[null,"Existing Revenue"]
[null,null,"Revenue"]
[]
[null,null,"Water Taxes"]
[null,null,"Enter the number of customers that pay Water Taxes:"]
[null,null,"Enter the annual water rate/water tax charged to each customer:"]
[]
[]
[null,null,"Water and Sewer Taxes"]
[null,null,"Enter the number of customers that pay Water & Sewer Taxes:"]
[null,null,"Enter the annual Water & Sewer Tax rate charged to each customer:"]
[null,null,"Select the approximate percentage of the tax that funds sewer operations:"]
[]
[]
[null,null,"Other Sources of Revenue"]
[null,null,"If industrial/commercial customers are charged a different rate: "]
[null,null,null,"- Enter the number of commercial/industrial customers charged at this rate"]
[null,null,null,"- Enter the rate charged to each industrial/commercial customer"]
[]
[null,null,"Enter the total revenue generated from any other sources:"]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Revenue",null,{"$f":"IF(Calculations!D217>0,Calculations!D217,\"\")","v":""}]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,1.4]

{"sheet":"1.5) Results","index":10,"n_rows":46,"n_cols":24,"rows_kept":46,"cut":false,"hidden":false,"merged":[[27,2,28,18],[15,15,15,17],[18,15,18,17],[19,15,19,17],[21,15,21,17],[22,15,22,17],[25,3,26,17],[6,15,6,17],[9,15,9,17],[11,3,11,17],[23,11,23,13],[23,15,23,17],[18,11,18,13],[19,11,19,13],[20,11,20,13],[21,11,21,13],[22,11,22,13],[20,15,20,17],[1,1,1,19],[45,8,45,9],[38,3,40,17],[34,15,34,17],[33,15,33,17],[32,15,32,17],[34,11,34,13],[32,11,32,13],[33,11,33,13],[35,15,35,17],[36,15,36,17],[36,11,36,13],[35,11,35,13],[2,1,2,19],[16,11,16,13],[5,15,5,17]]}
[]
[null,"PART 1"]
[null,"Results"]
[null,null,"Current Balance"]
[]
[null,null,null,"Total Revenue",null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D217","v":0}]
[null,null,null,"Total Expenditures",null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D228","v":0}]
[]
[]
[null,null,null,"Balance",null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D229","v":0},null,null,null,null,null,null,{"$f":"P6","v":0},"Revenue"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"P7","v":0},"Expenses"]
[null,null,null,{"$f":"Calculations!D231","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(P10<0,P10*-1,0)","v":0}]
[]
[null,null,"Evaluation of Existing Costs"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Recommended "]
[null,null,null,"Item",null,null,null,null,null,null,null,{"$f":"\"Actual \"&Calculations!D177","v":"Actual 2016"},null,null,null,null,"Annual Investment1"]
[]
[null,null,null,"Source of Supply",null,null,null,null,null,null,null,{"$f":"IF(Calculations!D181=0,\"\",Calculations!D181)","v":""},null,null,null,{"$f":"Calculations!E162","v":0}]
[null,null,null,"Pumping",null,null,null,null,null,null,null,{"$f":"IF(Calculations!D182=0,\"\",Calculations!D182)","v":""},null,null,null,{"$f":"Calculations!E163","v":0}]
[null,null,null,"Treatment & Disinfection",null,null,null,null,null,null,null,{"$f":"IF(Calculations!D183=0,\"\",Calculations!D183)","v":""},null,null,null,{"$f":"Calculations!E164","v":0}]
[null,null,null,"Transmission & Distribution",null,null,null,null,null,null,null,{"$f":"IF(Calculations!D184=0,\"\",Calculations!D184)","v":""},null,null,null,{"$f":"Calculations!E165","v":0}]
[null,null,null,"Administration",null,null,null,null,null,null,null,{"$f":"IF(Calculations!D185=0,\"\",Calculations!D185)","v":""},null,null,null,{"$f":"Calculations!E166","v":0}]
[null,null,null,"Total",null,null,null,null,null,null,null,{"$f":"IF(Calculations!D186=0,\"\",Calculations!D186)","v":""},null,null,null,{"$f":"Calculations!E167","v":0}]
[]
[null,null,null,{"$f":"Calculations!C244","v":""}]
[]
[null,null,"1Recommended investments were estimated based on the size of the community and characteristics of the water supply system. These values were estimated assuming best practices are followed for items such as montoring, preventive maintenance, etc. While every water supply system is unique, large variations between actual and typical expenditures  may indicate that actual budgets should be re-evaluated."]
[]
[]
[null,null,"Gap Analysis"]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Annual Water Tax ",null,null,null,"Gap From"]
[null,null,null,null,null,null,null,null,null,null,null,"per Customer",null,null,null,"Current"]
[null,null,null,"Current",null,null,null,null,null,null,null,{"$f":"Calculations!E199","v":0},null,null,null,"-",null,null,null,null,null,null,"Current Tax"]
[null,null,null,"Minimum to Fund Existing Operations",null,null,null,null,null,null,null,{"$f":"Calculations!D252","v":{"$error":"#DIV/0!"}},null,null,null,{"$f":"Calculations!E252","v":{"$error":"#DIV/0!"}},null,null,null,null,null,null,"To Fund Current Operations"]
[null,null,null,"Minimum to Fund Recommended Investment",null,null,null,null,null,null,null,{"$f":"Calculations!D253","v":{"$error":"#DIV/0!"}},null,null,null,{"$f":"Calculations!E253","v":{"$error":"#DIV/0!"}},null,null,null,null,null,null,"To Fund Best Practices Operations"]
[]
[null,null,null,{"$f":"IF(Calculations!D255=TRUE,Calculations!C256,Calculations!C257)","v":{"$error":"#DIV/0!"}}]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,1.5]

{"sheet":"2.1) Start","index":11,"n_rows":27,"n_cols":10,"rows_kept":27,"cut":false,"hidden":false,"merged":[[1,1,1,19],[26,9,26,10],[5,2,9,18],[11,2,13,18],[18,2,21,18],[2,1,2,19],[15,2,16,18]]}
[]
[null,"Part 2"]
[null,"Analysis of Proposed Upgrades"]
[]
[]
[null,null,"This section of the Full Cost Accounting Assessment Tool is for communities that are considering upgrades to their  water supply system. Enter information to describe the proposed upgrades as well as the existing components that will remain in operation. From the information, the Tool will use cost ratings curves to generate an opinion of capital and operation and maintenance costs. The Full Cost Accounting Assessment Tool will then calculate a water tax that would allow the community to achieve full cost recovery. "]
[]
[]
[]
[]
[]
[null,null,"If a cost estimate for an upgrade has already been developed by an engineer or other qualified professional, this value should be entered in the Upgrade Costspage, as such costs are considered to be more accurate than the costs predicted by the tool. "]
[]
[]
[]
[null,null,"Costs predicted by the Full Cost Accounting Assessment Tool do not include: Provincial or Federal sales or service taxes, depreciation, or existing non-operating expenses such as debt servicing. "]
[]
[]
[null,null,"If a particular type of upgrade is not included in the tool, the user should enter the value manually on the upgrade costs page. Operation and maintenance costs for items entered manually will not be captured by the Full Cost Accounting Assessment Tool, and therefore, the user should keep this in mind when evaluating the recommended future water tax result. "]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.1]

{"sheet":"2.2) Design Parameters","index":12,"n_rows":46,"n_cols":19,"rows_kept":46,"cut":false,"hidden":false,"merged":[[1,1,1,19],[33,8,33,9],[26,3,27,17],[34,4,34,5],[34,8,34,9],[33,16,33,17],[2,1,2,19],[31,12,31,13],[32,12,32,13],[33,12,33,13],[34,12,34,13],[31,16,31,17],[32,16,32,17],[31,4,31,5],[32,4,32,5],[33,4,33,5],[40,5,40,6],[37,12,37,13],[36,6,36,10],[36,12,36,17],[39,8,39,9],[38,8,38,9],[37,5,37,6],[38,5,38,6],[39,5,39,6],[38,12,38,13],[40,12,40,13],[5,2,7,18],[13,8,13,9],[9,8,9,9],[9,10,9,11],[11,10,11,11],[12,10,12,11],[10,8,10,9],[11,8,11,9],[12,8,12,9],[9,13,9,14],[12,13,12,14],[13,13,13,14],[8,16,9,18],[11,16,11,18],[12,16,12,18],[45,9,45,10],[39,12,39,13],[38,15,38,16],[39,15,39,16],[34,16,34,17],[31,8,31,9],[32,8,32,9],[13,10,13,11],[11,13,11,14]]}
[]
[null,"Part 2"]
[null,"Design Parameters for Upgrades"]
[null,null,"Design Parameters for Proposed Facility"]
[]
[null,null,"Enter the number of people who will be serviced by the new system. If flow data is available, enter it in the fields below and select the correct flow units. If flows are not known, leave the fields blank and flows will be estimated by assuming per capita consumption of 340 L/p/d."]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Per Capita Use (L/p/d)"]
[null,null,null,null,null,null,null,null,"Value",null,"Units",null,null,"Flow (m3/d)"]
[null,null,null,"Population Serviced"]
[null,null,null,"Average Flow ",null,null,null,null,null,null,"<Units>",null,null,{"$f":"IF(AND(I12>0,NOT(OR(K12=\"\",K12=\"<Units>\"))),Calculations!G267,\"\")","v":""},null,null,{"$f":"IF(AND(I12>0,NOT(OR(K12=\"\",K12=\"<Units>\"))),Calculations!H267,\"\")","v":""}]
[null,null,null,"Maximum Day Flow",null,null,null,null,null,null,"<Units>",null,null,{"$f":"IF(AND(I13>0,NOT(OR(K13=\"\",K13=\"<Units>\"))),Calculations!G268,\"\")","v":""},null,null,{"$f":"IF(AND(I13>0,NOT(OR(K13=\"\",K13=\"<Units>\"))),Calculations!H268,\"\")","v":""}]
[null,null,null,"Peak Flow",null,null,null,null,null,null,"<Units>",null,null,{"$f":"IF(AND(I14>0,NOT(OR(K14=\"\",K14=\"<Units>\"))),Calculations!G269,\"\")","v":""}]
[]
[]
[null,null,"Select the design life (in years) for the new facility:",null,null,null,null,null,null,null,null,null,15,null,20,null,25,null,30]
[]
[null,null,"The new treatment facility will need to have capacity to service the existing population, as well as the"]
[null,null,"population at the end of the system's service life. Enter population data from the most recent three"]
[null,null,"censuses below in order to determine population growth rate:"]
[null,null,null,null,null,null,null,null,"Census Year",null,null,"Population"]
[null,null,null,null,null,null,null,null,{"$f":"IF(Title!Q20=\"\",\"\",Calculations!C289)","v":2016}]
[null,null,null,null,null,null,null,null,{"$f":"IF(I23=\"\",\"\",Calculations!C290)","v":2011}]
[null,null,null,null,null,null,null,null,{"$f":"IF(I24=\"\",\"\",Calculations!C291)","v":2006}]
[]
[null,null,null,{"$f":"IF(L23*L24>0,Calculations!C294,\"\")","v":""}]
[]
[]
[null,null,"Select an Annual Population Growth Rate (%) for the system's service life:"]
[]
[]
[]
[]
[]
[null,null,"Based on the data above, the design parameters are as follows:"]
[null,null,null,null,null,null,"Current",null,null,null,null,null,{"$f":"\"Year \"&Title!Q20+Calculations!D285","v":"Year 2042"}]
[null,null,"Population:",null,null,{"$f":"Calculations!D266","v":0},null,null,null,null,null,null,{"$f":"ROUND(Calculations!D324,0)","v":0}]
[null,null,"Avg. Day Flow:",null,null,{"$f":"Calculations!D273","v":0},null,"m3/d",{"$f":"Calculations!E273","v":0},null,{"$f":"Calculations!E328","v":"L/p/d"},null,{"$f":"Calculations!D333","v":0},null,{"$f":"Calculations!E333","v":"m3/d"},{"$f":"Calculations!D328","v":0},null,{"$f":"Calculations!E328","v":"L/p/d"}]
[null,null,"Max. Day Flow:",null,null,{"$f":"Calculations!D274","v":0},null,"m3/d",{"$f":"Calculations!E274","v":0},null,{"$f":"Calculations!E329","v":"L/p/d"},null,{"$f":"Calculations!D334","v":0},null,{"$f":"Calculations!E334","v":"m3/d"},{"$f":"Calculations!D329","v":0},null,{"$f":"Calculations!E329","v":"L/p/d"}]
[null,null,"Peak Hour Flow:",null,null,{"$f":"Calculations!D275","v":0},null,"m3/d",null,null,null,null,{"$f":"Calculations!D335","v":0},null,{"$f":"Calculations!E335","v":"m3/d"}]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.2000000000000002]

{"sheet":"2.3) Treatment Upgrades","index":13,"n_rows":201,"n_cols":19,"rows_kept":201,"cut":false,"hidden":false,"merged":[[1,1,1,19],[47,2,48,18],[2,1,2,19],[6,14,6,18],[12,14,12,18],[19,2,19,5],[19,14,19,18],[19,6,19,13],[50,15,50,17],[57,2,58,18],[54,15,54,17],[50,2,52,13],[54,2,56,13],[137,2,137,7],[137,8,137,19],[134,2,134,19],[135,2,135,7],[135,8,135,19],[136,2,136,7],[136,8,136,19],[138,2,138,7],[138,8,138,19],[139,2,139,7],[139,8,139,19],[140,2,140,7],[140,8,140,19],[155,2,155,19],[156,2,156,7],[156,8,156,19],[157,2,157,7],[157,8,157,19],[158,2,158,7],[158,8,158,19],[159,2,159,7],[159,8,159,19],[160,2,160,7],[160,8,160,19],[63,9,63,10],[199,2,199,7],[199,8,199,19],[200,2,200,7],[200,8,200,19],[196,2,196,19],[197,2,197,7],[197,8,197,19],[198,2,198,7],[198,8,198,19],[161,2,161,7],[161,8,161,19],[162,2,162,7],[162,8,162,19],[163,2,163,7],[163,8,163,19]]}
[]
[null,"Part 2"]
[null,"Upgrades: Description of Proposed Treatment"]
[null,null,"Proposed Disinfection System"]
[]
[null,null,"What type of chemical disinfection system will be used?"]
[null,null,null,"Sodium Hypochlorite (liquid chlorine)"]
[null,null,null,"Chlorine Gas"]
[null,null,null,"Onsite Generated Sodium Hypochlorite (Mixed oxidants)"]
[null,null,null,"Calcium Hypochlorite "]
[]
[null,null,"Will existing disinfection equipment be used or will new equipment be purchased?"]
[null,null,null,"New"]
[null,null,null,"Existing"]
[]
[null,null,"Proposed Treatment Process"]
[]
[null,null,"Specify the processes that will be included, whether the equipment will be new or existing."]
[null,null,"Note: Only select the processes that are included."]
[null,null,"Treatment Process",null,null,null,"Equipment"]
[null,null,"UV Disinfection System",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"pH Adjustment System",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"Inline Pressure Filtration",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"Iron / Manganese Removal",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"Arsenic Removal",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"Membrane Treatment",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"Conventional WTP",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[null,null,"PWDU",null,null,null,null,"Not included",null,null,"New",null,"Existing"]
[]
[null,null,"New Treatment Plant Building"]
[]
[null,null,"Will a new building be required? ",null,null,null,null,null,null,null,null,null,null,null,"Yes",null,null,"No"]
[]
[null,null,"If a new building is required, select the processes that will need to be housed in the new building?"]
[null,null,"In this list below, do not select processes that will be housed in an existing building. "]
[null,null,null,null,null,"Chlorine Disinfection System (Sodium hypochlorite, chlorine gas, other)"]
[null,null,null,null,null,"UV Disinfection System"]
[null,null,null,null,null,"pH Adjustment System"]
[null,null,null,null,null,"Inline Pressure Filtration"]
[null,null,null,null,null,"Fe and/or Mn Removal"]
[null,null,null,null,null,"As Removal"]
[null,null,null,null,null,"Membrane Treatment"]
[null,null,null,null,null,"Conventional WTP"]
[null,null,null,null,null,"PWDU"]
[]
[null,null,"Yard Piping"]
[]
[null,null,"Yard piping is defined as the in-ground piping constructed at the treatment facility site. Do not include distribution system upgrades here, as there is a separate page for those upgrades."]
[]
[]
[null,null,"Enter the length of piping connecting the new water WTP to the nearest point in the distribution system. If unknown, the program assumes 20 m. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"m"]
[]
[]
[]
[null,null,"If the treatment plant will require a connection to the wastewater collection system, enter the distance to the nearest point in the collection system.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"m"]
[]
[]
[null,null,"Note: if a conventional water treatment process is being used, a connection to the collection system is required. This program assumes that the facility can be connected to the sewer system using a gravity collection system. "]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.2999999999999998]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Disinfection Chemicals"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"Sodium Hypochlorite",null,null,null,null,null,"Sodium hypochlorite is liquid chlorine. Generally it is a preferred disinfectant for smaller systems, because it is relatively easy to handle compared to chlorine gas. The product used in the water treatment industry typically has a trade strength of 12% available chlorine (10.2% sodium hypochlorite)."]
[null,null,"Gas Chlorination",null,null,null,null,null,"Chlorine gas is typically delivered in 120 lb cylinders or tonne containers. The product is 100% available chlorine, so it is typically used in larger installations where large tanks would otherwise be required for storing liquid chlorine. The gas must be stored in a separate room from other process equipment. Facilities using chlorine gas must have chlorine gas detectors and alarms. Operators handling chlorine gas must be trained for using self-contained breathing apparatus. A bystander must also be present when changing chlorine containers. "]
[null,null,"Onsite Sodium Hypochlorite Generation",null,null,null,null,null,"Onsite sodium hypochlorite generation systems use high purity salt to create a dilute sodium hypochlorite product (0.8%). The process requires a power input to create sodium hypochlorite and has a high capital cost. The chemicals are easier to handle and the shelf life of the salt is substantially greater than that of 12% sodium hypochlorite. "]
[null,null,"Calcium Hypochlorite",null,null,null,null,null,"Calcium hypochlorite is a solid form of chlorine, which is typically supplied in powdered form containing 60% available chlorine. It has a longer shelf life than sodium hypochlorite and has slightly lower cost per kg of available chlorine. It requires a separate tanks for batching and dosing. Batches are prepared by dissolving the solid in a batch tank for 24 hours. THe batch tank must be fit with a motorized mixer. "]
[null,null,"UV Disinfection",null,null,null,null,null,"UV disinfection is a method of disinfection that does not use chemicals. The light inactivates microorganisms through destruciton of DNA. It requires a power input and efficacy is dependent on clarity of the water. It is effective at inactivating pathogens that are chlorine resistant, such as Crypto and Giardia. It does not, however, produce a residual, so a chlorine based chemical must also be included in order to provide secondary disinfection (free chlorine residual throughout distribution system)."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Treatment Processes"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"pH Adjustment",null,null,null,null,null,"In this process, chemicals are added to increase pH in the distribution system. This process is generally included to reduce corrosion of distribution system and premise plumbing pipes and components. This helps protect distribution system pipes and reduces concentrations of metals such as iron, copper, and lead.  "]
[null,null,"Inline Pressure Filtration",null,null,null,null,null,"Inline pressure filtration uses media installed in fibreglass or steel vessels to reduce turbidity and remove particles. These systems must be pressurized, either through gravity or by using pumps. "]
[null,null,"Iron and/or Manganese Removal",null,null,null,null,null,"Iron and manganese removal is used where iron concentrations exceed 0.3 mg/L or manganese concentrations exceed 0.05 mg/L. Iron and manganese are aesthetic parameters, as they can contribute to the water's colour and cause staining of sinks, toilets, and clothing. Iron and manganese systems generally use a special media, which may be installed in gravity or pressure filters. Water is pre-treated with an oxidant such as chlorine or potassium permanganate."]
[null,null,"Arsenic Removal",null,null,null,null,null,"Arsenic is a health based parameter, and arsenic treatment is required where arsenic concentrations in the source water exceed 0.01 mg/L. Arsenic removal systems often use a coated media installed in fibreglass or steel tanks. The media requires periodic replacement. "]
[null,null,"Membrane Filtration",null,null,null,null,null,"Membrane filtration is a physical treatment process where particles are physically removed by exclusion, as water is passed through a membrane with an effective pore size. The process is often used to remove particles and organics (colour).  Membranes are classified based on their pore sizes. Depending on the type of membrane process used, the systems may or may not require the use of pre-treatment chemicals to achieve organics (colour) removal. They require periodic cleaning, which is typically accomplished using citric acid. As a result, come means of handling the process waste must be included. "]
[null,null,"Conventional WTP",null,null,null,null,null,"Conventional water treatment plants use chemical pre-treatment to create particles that can be removed through a clarification step and/or filtration. Examples include: dissolved air flotation, conventional filtration, and direct filtration. The processes require chemical coagulation and pH adjustment. A significant amount of process residuals, which include chemicals such as aluminum, are generated through filter backwashing. As a result, these facilities must be connected to the community's wastewater collection system or they require some method of residuals treatment. "]
[null,null,"Potable Water Dispensing Units",null,null,null,null,null,"Potable water dispensing units are suitable alternatives for smaller communities. The units includes treatment, disinfection, and dispensing systems. Members from the community collect water for potable use from the units, and transport the water using containers. The units are designed to meet the potable consumption demands of the community. Other non-potable uses are met through traditional distribution systems."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Pumping Systems"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"Low Lift Pumping",null,null,null,null,null,"Low lift pumps move water from the source to the treatment facility. They operate at a relatively low discharge pressure and are required where the source water is not at an elevation that is insufficiently above the elevation of the treatment facility."]
[null,null,"High Lift Pumping",null,null,null,null,null,"High lift pumps move water from the treatment plant to the distribution system. In cases where water is pumped from the source (groundwater or surface), through treatment, and directly to the distribution system, it may be considered high lift pumping. "]
[null,null,"Distribution System Pumping",null,null,null,null,null,"Distribution system pumps increase pressure within the distribution system. These are also often referred to as booster pumps. They are required distribution systems with substantial elevation differences. "]

{"sheet":"2.4) Pumping Upgrades","index":14,"n_rows":57,"n_cols":18,"rows_kept":57,"cut":false,"hidden":false,"merged":[[1,1,1,19],[2,1,2,19],[55,2,55,7],[55,8,55,19],[56,2,56,7],[56,8,56,19],[52,2,52,19],[53,2,53,7],[53,8,53,19],[54,2,54,7],[54,8,54,19],[26,9,26,10]]}
[]
[null,"Part 2"]
[null,"Description of Proposed Pumping"]
[null,null,"Describe the types of pumps that will be included in the proposed system"]
[]
[null,null,"Indicate if pumping equipment will be included, whether it will be new or existing,"]
[null,null,"and if the equipment will be installed in a new or existing building. "]
[null,null,"Note: you only need to select the processes that are included. Those that are not included can be left as is."]
[]
[null,null,null,"Pumping Process"]
[null,null,null,"Well Pump",null,null,null,null,null,"Not Included",null,null,null,"New",null,null,null,"Existing"]
[]
[null,null,null,"Low Lift Pump",null,null,null,null,null,"Not Included",null,null,null,"New",null,null,null,"Existing"]
[]
[null,null,null,"High Lift Pump",null,null,null,null,null,"Not Included",null,null,null,"New",null,null,null,"Existing"]
[]
[null,null,"Indicate below if a new building is being constructed to house the pumping systems."]
[null,null,null,"Low Lift Pump"]
[null,null,null,"High Lift Pump"]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.4]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Pumping Systems"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"Low Lift Pumping",null,null,null,null,null,"Low lift pumps move water from the source to the treatment facility. They operate at a relatively low discharge pressure and are required where the source water is not at an elevation that is insufficiently above the elevation of the treatment facility."]
[null,null,"High Lift Pumping",null,null,null,null,null,"High lift pumps move water from the treatment plant to the distribution system. In cases where water is pumped from the source (groundwater or surface), through treatment, and directly to the distribution system, it may be considered high lift pumping. "]
[null,null,"Distribution System Pumping",null,null,null,null,null,"Distribution system pumps increase pressure within the distribution system. These are also often referred to as booster pumps. They are required distribution systems with substantial elevation differences. "]

{"sheet":"2.5) Distribution Upgrades","index":15,"n_rows":149,"n_cols":18,"rows_kept":149,"cut":false,"hidden":false,"merged":[[1,1,1,19],[146,2,146,7],[146,8,146,19],[147,2,147,7],[147,8,147,19],[106,2,106,7],[106,8,106,19],[79,2,79,7],[79,8,79,19],[80,2,80,7],[80,8,80,19],[81,2,81,7],[81,8,81,19],[103,2,103,19],[104,2,104,7],[104,8,104,19],[148,2,148,7],[148,8,148,19],[145,2,145,7],[145,8,145,19],[107,2,107,7],[107,8,107,19],[108,2,108,7],[108,8,108,19],[109,2,109,7],[109,8,109,19],[110,2,110,7],[110,8,110,19],[111,2,111,7],[111,8,111,19],[144,2,144,19],[105,2,105,7],[105,8,105,19],[76,2,76,7],[76,8,76,19],[77,2,77,7],[77,8,77,19],[78,2,78,7],[78,8,78,19],[26,14,26,16],[75,2,75,19],[10,2,11,11],[11,14,11,16],[13,2,14,11],[14,14,14,16],[16,2,16,18],[22,14,22,16],[31,9,31,10],[6,14,6,16],[8,14,8,16],[2,1,2,19],[20,14,20,16],[24,14,24,16]]}
[]
[null,"Part 2"]
[null,"Description of Distribution Upgrades"]
[null,null,"Treated Water Storage"]
[]
[null,null,"Do the proposed upgrades include the construction of a new "]
[null,null,"treated water storage tanks? ",null,null,null,null,null,null,null,null,null,null,null,"<Yes/No>"]
[]
[null,null,"Will storage tank include a fire storage component?",null,null,null,null,null,null,null,null,null,null,null,"<Yes/No>"]
[]
[null,null,"The default fire storage value for this program is 2 hours. If a different value should be used, enter it here:"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<Fire Storage>",null,null,"minutes"]
[]
[null,null,"The default fire flowrate is 1250 gpm. If a different value should be used, enter it here: "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"<Fire flow>",null,null,"gpm"]
[]
[null,null,"*NOTE: The level of fire protection is the responsibility of the municipality. Fire flow requirements are typically established by the appropriate Insurance Advisory Organization, and should be satisfied where fire protection is provided. "]
[]
[null,null,"New Distribution System"]
[]
[null,null,"Is the community planning to expand the distribution system? ",null,null,null,null,null,null,null,null,null,null,null,"<Yes/No>"]
[]
[null,null,"Enter the length of new distribution system that is being installed",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"m"]
[]
[null,null,"Enter the number of valves that will be installed",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"m"]
[]
[null,null,"Enter the number of hydrants that will be installed",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"m"]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.5]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Disinfection Chemicals"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"Sodium Hypochlorite",null,null,null,null,null,"Sodium hypochlorite is liquid chlorine. Generally it is a preferred disinfectant for smaller systems, because it is relatively easy to handle compared to chlorine gas. The product used in the water treatment industry typically has a trade strength of 12% available chlorine (10.2% sodium hypochlorite)."]
[null,null,"Gas Chlorination",null,null,null,null,null,"Chlorine gas is typically delivered in 120 lb cylinders or tonne containers. The product is 100% available chlorine, so it is typically used in larger installations where large tanks would otherwise be required for storing liquid chlorine. The gas must be stored in a separate room from other process equipment. Facilities using chlorine gas must have chlorine gas detectors and alarms. Operators handling chlorine gas must be trained for using self-contained breathing apparatus. A bystander must also be present when changing chlorine containers. "]
[null,null,"Onsite Sodium Hypochlorite Generation",null,null,null,null,null,"Onsite sodium hypochlorite generation systems use high purity salt to create a dilute sodium hypochlorite product (0.8%). The process requires a power input to create sodium hypochlorite and has a high capital cost. The chemicals are easier to handle and the shelf life of the salt is substantially greater than that of 12% sodium hypochlorite. "]
[null,null,"Calcium Hypochlorite",null,null,null,null,null,"Calcium hypochlorite is a solid form of chlorine, which is typically supplied in powdered form containing 60% available chlorine. It has a longer shelf life than sodium hypochlorite and has slightly lower cost per kg of available chlorine. It requires a separate tanks for batching and dosing. Batches are prepared by dissolving the solid in a batch tank for 24 hours. THe batch tank must be fit with a motorized mixer. "]
[null,null,"UV Disinfection",null,null,null,null,null,"UV disinfection is a method of disinfection that does not use chemicals. The light inactivates microorganisms through destruciton of DNA. It requires a power input and efficacy is dependent on clarity of the water. It is effective at inactivating pathogens that are chlorine resistant, such as Crypto and Giardia. It does not, however, produce a residual, so a chlorine based chemical must also be included in order to provide secondary disinfection (free chlorine residual throughout distribution system)."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Treatment Processes"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"pH Adjustment",null,null,null,null,null,"In this process, chemicals are added to increase pH in the distribution system. This process is generally included to reduce corrosion of distribution system and premise plumbing pipes and components. This helps protect distribution system pipes and reduces concentrations of metals such as iron, copper, and lead.  "]
[null,null,"Inline Pressure Filtration",null,null,null,null,null,"Inline pressure filtration uses media installed in fibreglass or steel vessels to reduce turbidity and remove particles. These systems must be pressurized, either through gravity or by using pumps. "]
[null,null,"Iron and/or Manganese Removal",null,null,null,null,null,"Iron and manganese removal is used where iron concentrations exceed 0.3 mg/L or manganese concentrations exceed 0.05 mg/L. Iron and manganese are aesthetic parameters, as they can contribute to the water's colour and cause staining of sinks, toilets, and clothing. Iron and manganese systems generally use a special media, which may be installed in gravity or pressure filters. Water is pre-treated with an oxidant such as chlorine or potassium permanganate."]
[null,null,"Arsenic Removal",null,null,null,null,null,"Arsenic is a health based parameter, and arsenic treatment is required where arsenic concentrations in the source water exceed 0.01 mg/L. Arsenic removal systems often use a coated media installed in fibreglass or steel tanks. The media requires periodic replacement. "]
[null,null,"Membrane Filtration",null,null,null,null,null,"Membrane filtration is a physical treatment process where particles are physically removed by exclusion, as water is passed through a membrane with an effective pore size. The process is often used to remove particles and organics (colour).  Membranes are classified based on their pore sizes. Depending on the type of membrane process used, the systems may or may not require the use of pre-treatment chemicals to achieve organics (colour) removal. They require periodic cleaning, which is typically accomplished using citric acid. As a result, come means of handling the process waste must be included. "]
[null,null,"Conventional WTP",null,null,null,null,null,"Conventional water treatment plants use chemical pre-treatment to create particles that can be removed through a clarification step and/or filtration. Examples include: dissolved air flotation, conventional filtration, and direct filtration. The processes require chemical coagulation and pH adjustment. A significant amount of process residuals, which include chemicals such as aluminum, are generated through filter backwashing. As a result, these facilities must be connected to the community's wastewater collection system or they require some method of residuals treatment. "]
[null,null,"Potable Water Dispensing Units",null,null,null,null,null,"Potable water dispensing units are suitable alternatives for smaller communities. The units includes treatment, disinfection, and dispensing systems. Members from the community collect water for potable use from the units, and transport the water using containers. The units are designed to meet the potable consumption demands of the community. Other non-potable uses are met through traditional distribution systems."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Description of Pumping Systems"]
[null,null,"Disinfection Chemical",null,null,null,null,null,"Description"]
[null,null,"Low Lift Pumping",null,null,null,null,null,"Low lift pumps move water from the source to the treatment facility. They operate at a relatively low discharge pressure and are required where the source water is not at an elevation that is insufficiently above the elevation of the treatment facility."]
[null,null,"High Lift Pumping",null,null,null,null,null,"High lift pumps move water from the treatment plant to the distribution system. In cases where water is pumped from the source (groundwater or surface), through treatment, and directly to the distribution system, it may be considered high lift pumping. "]
[null,null,"Distribution System Pumping",null,null,null,null,null,"Distribution system pumps increase pressure within the distribution system. These are also often referred to as booster pumps. They are required distribution systems with substantial elevation differences. "]

{"sheet":"2.6) Cost Curve Predictions","index":16,"n_rows":54,"n_cols":17,"rows_kept":54,"cut":false,"hidden":false,"merged":[[1,1,1,19],[12,2,13,18],[10,8,10,10],[2,1,2,19],[15,16,15,18],[5,16,5,18],[19,16,20,18],[19,2,20,9],[19,13,20,15],[19,10,20,12],[23,13,23,15],[23,10,23,12],[21,16,21,18],[22,16,22,18],[23,16,23,18],[24,16,24,18],[21,2,21,9],[22,2,22,9],[23,2,23,9],[21,13,21,15],[22,13,22,15],[21,10,21,12],[22,10,22,12],[24,10,24,12],[24,13,24,15],[24,2,24,9],[26,10,26,12],[26,13,26,15],[26,16,26,18],[40,13,40,15],[47,2,48,18],[45,13,45,15],[43,13,43,15],[44,13,44,15],[41,13,41,15],[42,13,42,15],[53,9,53,10],[30,2,31,18],[6,16,6,18],[16,2,17,18],[32,2,35,18],[39,13,39,15],[38,12,38,16],[25,16,25,18],[25,13,25,15],[25,10,25,12],[27,10,27,12],[27,13,27,15],[27,16,27,18],[28,10,28,12],[28,13,28,15],[28,16,28,18]]}
[]
[null,"Part 2"]
[null,"Cost Curve Predictions"]
[null,null,"Predicted Capital Costs for Upgrades"]
[]
[null,null,"Enter the number of customers on the water supply system:"]
[null,null,"Enter the current Water Tax:"]
[]
[null,null,"Has a cost estimate for the proposed upgrades been developed through an engineering study?"]
[]
[null,null,null,null,"Yes, the estimate is:"]
[null,null,null,null,"No, have the tool calculate an approximate value based on historical data."]
[null,null,"Note: Estimates obtained through an engineering study are considered to be more reliable than the values generated by this program, which are based on cost data from previous projects in the Province."]
[]
[]
[null,null,"Enter the fraction of the capital cost will be covered by government funding:",null,null,null,null,null,null,null,null,null,null,null,null,null,"<Funding>"]
[null,null,"Note: Project funding is reduced by the amout of GST rebate due to the community if the full project project costs are expended. The community is responsible for applying for the appropriate rebates and interim financing of the rebates. "]
[]
[]
[null,null,"Upgrade Component",null,null,null,null,null,null,null,"Capital Cost",null,null,"Government Contributions",null,null,"Balance Covered by Community"]
[]
[null,null,{"$f":"Calculations!C540","v":"Water Treatment Facility"},null,null,null,null,null,null,null,{"$f":"IF(Calculations!D549>0,Calculations!D549,\"\")","v":""},null,null,{"$f":"IF(Calculations!E549>0,Calculations!E549,\"\")","v":""},null,null,{"$f":"IF(Calculations!F549>0,Calculations!F549,\"\")","v":""}]
[null,null,{"$f":"Calculations!C541","v":"Pumping Systems"},null,null,null,null,null,null,null,{"$f":"IF(Calculations!D550>0,Calculations!D550,\"\")","v":""},null,null,{"$f":"IF(Calculations!E541>0,Calculations!E541,\"\")","v":""},null,null,{"$f":"IF(Calculations!F550>0,Calculations!F550,\"\")","v":""}]
[null,null,{"$f":"Calculations!C542","v":"Distribution "},null,null,null,null,null,null,null,{"$f":"IF(Calculations!D551>0,Calculations!D551,\"\")","v":""},null,null,{"$f":"IF(Calculations!E542>0,Calculations!E542,\"\")","v":""},null,null,{"$f":"IF(Calculations!F551>0,Calculations!F551,\"\")","v":""}]
[null,null,{"$f":"Calculations!C543","v":"Storage"},null,null,null,null,null,null,null,{"$f":"IF(Calculations!D552>0,Calculations!D552,\"\")","v":""},null,null,{"$f":"IF(Calculations!E543>0,Calculations!E543,\"\")","v":""},null,null,{"$f":"IF(Calculations!F552>0,Calculations!F552,\"\")","v":""}]
[null,null,"Range of Probable Cost"]
[null,null,null,"-30%",null,null,null,null,null,null,{"$f":"IF(Calculations!D554>0,Calculations!D554,\"\")","v":""},null,null,{"$f":"IF(Calculations!E554>0,Calculations!E554,\"\")","v":""},null,null,{"$f":"IF(Calculations!F554>0,Calculations!F554,\"\")","v":""}]
[null,null,null,"Median",null,null,null,null,null,null,{"$f":"IF(Calculations!D553>0,Calculations!D553,\"\")","v":""},null,null,{"$f":"IF(Calculations!E553>0,Calculations!E553,\"\")","v":""},null,null,{"$f":"IF(Calculations!F553>0,Calculations!F553,\"\")","v":""}]
[null,null,null,"+30%",null,null,null,null,null,null,{"$f":"IF(Calculations!D555>0,Calculations!D555,\"\")","v":""},null,null,{"$f":"IF(Calculations!E555>0,Calculations!E555,\"\")","v":""},null,null,{"$f":"IF(Calculations!F555>0,Calculations!F555,\"\")","v":""}]
[null,null,"Notes:"]
[null,null,"1. The values above do not include taxes or take into account any site specific feastures, and were calculated based on historical costs from other projects."]
[]
[null,null,"2.The values presented in the table above were calculated to demonstrate the potential impacts that proposed upgrades may have on the annual costs of operating the water supply system. For capital budgeting, an assessment should be conducted by an engineer or qualified person."]
[]
[]
[]
[null,null,"Predicted Operation and Maintenance Costs"]
[]
[null,null,"Item",null,null,null,null,null,null,null,null,null,"Predicted"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"O&M Costs1"]
[null,null,"Source of Supply",null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D652","v":0}]
[null,null,"Pumping",null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D653","v":0}]
[null,null,"Treatment & Disinfection",null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D654","v":0}]
[null,null,"Transmission & Distribution",null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D655","v":0}]
[null,null,"Administration",null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D656","v":0}]
[null,null,"Total",null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D657","v":0}]
[]
[null,null,"1Predicted O&M expenditures have been calculated based on the size of the community, characteristics of the water supply system, and the type of systems included in the proposed upgrade. These values were estimated assuming best practices are followed for items such as montoring, preventive maintenance, etc."]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.6]

{"sheet":"2.7) Financing Results","index":17,"n_rows":74,"n_cols":16,"rows_kept":74,"cut":false,"hidden":false,"merged":[[31,9,31,10],[24,2,25,18],[22,15,22,17],[19,15,19,17],[21,15,21,17],[20,15,20,17],[1,1,1,19],[73,2,73,7],[73,8,73,19],[70,2,70,19],[71,2,71,7],[71,8,71,19],[72,2,72,7],[72,8,72,19],[2,1,2,19],[5,15,5,17],[7,15,7,17],[8,15,8,17],[10,2,11,13]]}
[]
[null,"Part 2"]
[null,"Financing and Water Taxes"]
[null,null,"Financing"]
[]
[null,null,"If reserve funds will be used to reduce the loan amount, enter the amount:"]
[]
[null,null,"Enter the term length, in years, for the loan. Must not be more than 30 years."]
[null,null,"Enter the anticipated interest rate on any borrowed funds:",null,null,null,null,null,null,null,null,null,null,null,null,"<Loan Interest Rate>"]
[]
[null,null,"Select whether loan payments will be serial or blended. If this is not known, leave blank, and the program will use serial payments by default",null,null,null,null,null,null,null,null,null,null,null,null,"Blended payments"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Serial payments"]
[]
[]
[]
[]
[]
[null,null,"For Recommended Operations:"]
[]
[null,null,"Current Annual Water Tax per Household:",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D663","v":0}]
[null,null,"Current Number of Customers:",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D662","v":0}]
[null,null,"Recommended Annual Water Tax (Year 1 following Upgrade)",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Calculations!D665","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"\"$\"&ROUNDUP(P22/12,0)&\"/month\"","v":"$0/month"}]
[]
[null,null,{"$f":"IF(P20>P22,\"\",\"Annual Water Tax would need to be increased by $\"&ROUND((P22-P20)/12,0)&\" per month in order to completely fund the upgrades.\")","v":"Annual Water Tax would need to be increased by $0 per month in order to completely fund the upgrades."}]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,2.7]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Source of Supply"]
[null,null,"Item",null,null,null,null,null,"Description"]
[null,null,"Blended Payment",null,null,null,null,null,"Blended payment is a loan repayment method where the loan is repaid in equal instalment amounts over the period of the loan. "]
[null,null,"Serial Payment",null,null,null,null,null,"Serial payment is a loan repayment method where the principal is paid in equal amounts over the period of the loan. Interest payments decrease each period, meaning the highest annual payment is in the first year of the loan. "]

{"sheet":"Calculations","index":18,"n_rows":666,"n_cols":20,"rows_kept":666,"cut":false,"hidden":true,"merged":[[638,14,638,16],[638,17,638,19],[150,17,150,19],[150,14,150,16],[638,5,638,13],[150,5,150,13],[568,3,568,6],[568,7,568,10],[409,5,409,7]]}
[]
[null,"Legend:"]
[null,"X","Indicates cells that can be modified by Government of Newfoundland"]
[null,null,"Indicates cells that are linked from other spreadsheets (provided separately); note, links have been removed in this version of the file."]
[]
[]
[null,"EXISTING OPERATIONS:"]
[]
[]
[null,null,"PAGE 1 - Community Data"]
[]
[]
[null,null,"Community:",{"$f":"Title!I14","v":0}]
[null,null,"Water Supply System:",{"$f":"'1.1) Community Data'!I6","v":0}]
[null,null,"Month:",{"$f":"Title!I20","v":"00:00:00"}]
[null,null,"Day:",{"$f":"Title!L20","v":0}]
[null,null,"Year:",{"$f":"Title!Q20","v":2017}]
[]
[null,null,"Type of Water Supply:",{"$f":"'1.1) Community Data'!I7","v":0}]
[null,null,"Annual O&M Inflation Rate:",1.4999999999999999E-2]
[null,null,"Annual Capital Inflation Rate:",0.03]
[null,null,"Base Year for O&M & Capital Cost Data:",2017]
[null,null,"O&M Inflation Factor:",{"$f":"(1+D20)^(D17-D22)","v":1}]
[null,null,"Capital Inflation Factor:",{"$f":"(1+D21)^(D17-D22)","v":1}]
[]
[]
[null,null,"Geographic Region Option #","Geographic Region","Capital Cost Factor","O&M Cost Factor"]
[null,null,1,null,1,1]
[null,null,2,"Avalon (St. John's)",1,1]
[null,null,3,"Eastern (Clarenville)",1.05,1.05]
[null,null,4,"Central (Gander)",1.05,1.05]
[null,null,5,"Central West (Corner Brook)",1.05,1.05]
[null,null,6,"North West (St. Anthony)",1.1000000000000001,1.1000000000000001]
[null,null,7,"South West (Port Aux Basques)",1.3,1.3]
[null,null,8,"Labrador Central (Happy Valley-Goose Bay)",1.7,1.7]
[null,null,9,"Labrador West (Wabush)",1.7,1.7]
[null,null,10,"Labrador (North)",2,2]
[]
[null,null,"Geographic Region Option #",1]
[null,null,"Geographic Region:",{"$f":"VLOOKUP(D39,C28:F37,2,FALSE)","v":0}]
[null,null,"Geographic Region Capital Cost Factor:",{"$f":"VLOOKUP(D39,C28:F37,3,FALSE)","v":1}]
[null,null,"Geographic Region O&M Cost Factor:",{"$f":"VLOOKUP(D39,C28:F37,4,FALSE)","v":1}]
[]
[]
[null,null,"Existing System Flows:"]
[]
[null,null,"Water System Population:",{"$f":"'1.1) Community Data'!Q11","v":0}]
[]
[null,null,"User Entered Flow Data (if Available):"]
[null,null,"Parameter","User Entered Flow Value","User Entered Flow Unit","Conversion Factor","Flow By User (m3/d)"]
[null,null,"User Entered Average Flow - Season 1",{"$f":"'1.1) Community Data'!H18","v":0},{"$f":"'1.1) Community Data'!I18","v":"<Units>"},{"$f":"IF(E51=\"<Units>\",0,VLOOKUP(E51,'Table Inputs'!$T$4:$U$6,2,FALSE))","v":0},{"$f":"D51*F51","v":0}]
[null,null,"User Entered Maximum Day Flow - Season 1",{"$f":"'1.1) Community Data'!H19","v":0},{"$f":"'1.1) Community Data'!I19","v":"<Units>"},{"$f":"IF(E52=\"<Units>\",0,VLOOKUP(E52,'Table Inputs'!$T$4:$U$6,2,FALSE))","v":0},{"$f":"D52*F52","v":0}]
[null,null,"User Entered Peak Hour Flow - Season 1",{"$f":"'1.1) Community Data'!H20","v":0},{"$f":"'1.1) Community Data'!I20","v":"<Units>"},{"$f":"IF(E53=\"<Units>\",0,VLOOKUP(E53,'Table Inputs'!$T$4:$U$6,2,FALSE))","v":0},{"$f":"D53*F53","v":0}]
[]
[]
[null,null,"Theoretical Per Capita Demand",340,"L/p/d"]
[null,null,"Average Day Flow",{"$f":"IF(G51>0,G51,D47*D56/1000)","v":0},"m3/d",{"$f":"D57*1000/3.785/1440","v":0},"gpm"]
[null,null,"Maximum Day Flow",{"$f":"IF(G52>0,G52,D57*VLOOKUP(D47,'Table Inputs'!B4:E12,3,TRUE))","v":0},"m3/d",{"$f":"D58*1000/3.785/1440","v":0},"gpm"]
[null,null,"Peak Hour Flow",{"$f":"IF(G53>0,G53,D57*VLOOKUP(D47,'Table Inputs'!B4:E12,4,TRUE))","v":0},"m3/d",{"$f":"D59*1000/3.785/1440","v":0},"gpm"]
[]
[null,null,"Per Capita Values - Primary Season"]
[null,null,"Average",{"$f":"D57/D47*1000","v":{"$error":"#DIV/0!"}},"L/p/d"]
[null,null,"MDD",{"$f":"D58/D47*1000","v":{"$error":"#DIV/0!"}},"L/p/d"]
[null,null,"Peak Hour",{"$f":"D59/D47*1000","v":{"$error":"#DIV/0!"}},"L/p/d"]
[]
[]
[]
[]
[]
[null,null,"PAGE 2 - Existing System Operation"]
[]
[null,null,"Disinfection:"]
[null,null,"Disinfection Type #","Chemical","System","$/kg (Available Chlorine)","Predicted Dose (mg/L)"]
[null,null,1,"Sodium Hypochlorite","Liquid Chlorine",14,{"$f":"IF($D$19=\"Surface Water\",5,2.5)","v":2.5}]
[null,null,2,"Chlorine Gas","Chlorine Gas",6,{"$f":"IF($D$19=\"Surface Water\",5,2.5)","v":2.5}]
[null,null,3,"Salt","Mixed Oxidants",0.9,{"$f":"IF($D$19=\"Surface Water\",5*3,2.5*3)","v":7.5}]
[null,null,4,"Calcium Hypochlorite","Powdered chlorine",9,{"$f":"IF($D$19=\"Surface Water\",5,2.5)","v":2.5}]
[null,null,"Disinfection type (option #):",1]
[null,null,"Disinfectant Type:",{"$f":"VLOOKUP(D78,C74:E77,3,FALSE)","v":"Liquid Chlorine"}]
[null,null,"Disinfection Chemical:",{"$f":"VLOOKUP(D78,C74:D77,2,FALSE)","v":"Sodium Hypochlorite"}]
[null,null,"UV disinfection included:",false]
[]
[]
[null,null,"Treatment Chemicals"]
[null,null,"pH Adjustment Chemical #","Chemical","$/kg","Predicted Dose (mg/L)"]
[null,null,1,"None",0,0]
[null,null,2,"Soda Ash",1.55,30]
[null,null,3,"Lime",1.1000000000000001,11]
[null,null,4,"Caustic Soda",1.5,12]
[null,null,"pH adjustment chemical Option #:",1]
[null,null,"pH adjustment chemical selected:",{"$f":"VLOOKUP(D90,C86:D89,2,FALSE)","v":"None"}]
[]
[]
[null,null,"Treatment Process","Included?"]
[null,null,"None",false]
[null,null,"Inline Filtration",false]
[null,null,"Iron / Manganese Removal",false]
[null,null,"Arsenic Removal",false]
[null,null,"Membrane Treatment ",false]
[null,null,"Conventional WTP",false]
[null,null,"PWDU",false]
[]
[]
[]
[null,null,"O&M Costs for Treatment Items:"]
[null,null,"Treatment Type","Selected?","Slope","Intercept","Cost"]
[null,null,"Disinfection with Sodium Hypochlorite",{"$f":"IF(D78=1,TRUE,FALSE)","v":true},24.560345823878983,6752.3394616801743,{"$f":"IF(D57<0.00001,0,D107*(F107+E107*$D$57))","v":0}]
[null,null,"Disinfection with Chlorine Gas",{"$f":"IF(D78=2,TRUE,FALSE)","v":false},17.892213890256492,8457.6522398196412,{"$f":"IF(D57<0.00001,0,D108*(F108+E108*$D$57))","v":0}]
[null,null,"Disinfection with OSHG",{"$f":"IF(D78=3,TRUE,FALSE)","v":false},11.303012941653165,20743.298208654778,{"$f":"IF(D57<0.00001,0,D109*(F109+E109*$D$57))","v":0}]
[null,null,"Disinfection with Calcium Hypochlorite",{"$f":"IF(D78=4,TRUE,FALSE)","v":false},19.840191853052399,5723.0039673527845,{"$f":"IF(D57<0.00001,0,D110*(F110+E110*$D$57))","v":0}]
[null,null,"UV Disinfection",{"$f":"IF(D81=TRUE,TRUE,FALSE)","v":false},9.1560742618854682,1851.9836921258775,{"$f":"IF(D57<0.00001,0,D111*(F111+E111*$D$57))","v":0}]
[null,null,"+ pH adjustment with Soda Ash",{"$f":"IF(D90=2,TRUE,FALSE)","v":false},22.201576274230145,2735.9515733225289,{"$f":"D112*(F112+E112*$D$57)","v":0}]
[null,null,"+ pH adjustment with Lime",{"$f":"IF(D90=3,TRUE,FALSE)","v":false},11.8637934541329,3814.3826916369526,0]
[null,null,"+ pH adjustment with Caustic Soda",{"$f":"IF(D90=4, TRUE,FALSE)","v":false},11.086855202593194,1871.6616782820092,0]
[null,null,"Inline Filtration",{"$f":"D96","v":false},15.309744675227705,4857.663103727713,0]
[null,null,"Iron / Manganes Removal",false,20.864488565017009,2922.3305267423038,0]
[null,null,"Arsenic Removal",false,20.855287624984591,3185.7408995137739,0]
[null,null,"Membrane Treatment",false,59.808470355596754,61775.286250292476,{"$f":"D118*((F118+E118*$D$57)-SUM(G107:G110,G112:G114))","v":0}]
[null,null,"Engineered Filtration",false,78.419303609637012,64425.358469978099,{"$f":"D119*((F119+E119*$D$57)-SUM(G107:G110,G112:G114))","v":0}]
[null,null,"PWDU",false,52.316238430578061,1968.8933522420339,{"$f":"D120*(F120+E120*$D$57)","v":0}]
[null,null,null,null,null,{"$f":"\"Total Treatment Cost (\"&$D$22&\"$)\"","v":"Total Treatment Cost (2017$)"},{"$f":"ROUND(SUM(G107:G120,D136),-2)","v":0}]
[null,null,null,null,null,{"$f":"\"Total Cost (\"&$D$17&\"$)\"","v":"Total Cost (2017$)"},{"$f":"G121*$D$23","v":0}]
[]
[null,null,"Distribution System Chlorine Boosting:",null,1,0]
[null,null,"Boosting Option Value",1,2,0.1]
[null,null,"No",null,3,0.2]
[null,null,"Yes, with sodium hypochlorite",null,4,0.3]
[null,null,"Yes, with chlorine gas",null,5,0.4]
[null,null,"Rule",{"$f":"IF(OR(D125=2,D125=3),TRUE,FALSE)","v":false},6,0.5]
[null,null,"Dist. Percent Boosted Option #",1,7,0.6]
[null,null,null,null,8,0.7]
[null,null,null,null,9,0.8]
[null,null,"Percent Serviced",{"$f":"VLOOKUP(D130,E124:F134,2,FALSE)","v":0},10,0.9]
[null,null,"Dose as Chlorine (mg/L)",1.5,11,1]
[null,null,"Unit Cost of Chemical",{"$f":"IF(D125=2,F74,IF(D125=3,F75,0))","v":0}]
[null,null,"Annual Cost",{"$f":"$D$57*1000*365*D134/1000/1000*D135*D133","v":0}]
[]
[]
[null,null,"O&M Costs for non-treatment Related Items:"]
[null,null,"Item","Source Type","Slope","Intercept",{"$f":"\"Cost (\"&$D$22&\"$)\"","v":"Cost (2017$)"},{"$f":"\"Cost (\"&$D$17&\"$)\"","v":"Cost (2017$)"}]
[null,null,"Source",{"$f":"D19","v":0},10.572432665682996,5306.2727576844245,{"$f":"IF(D57<0.00001,0,ROUND(F141+E141*$D$57,-2))","v":0},{"$f":"G141*$D$23","v":0}]
[null,null,"Distribution",{"$f":"D19","v":0},80,10000,{"$f":"IF(D57<0.00001,0,IF(D47<150,15000,ROUNDUP(F142+E142*$D$57,-2)))","v":0},{"$f":"G142*$D$23","v":0}]
[null,null,"Administration",{"$f":"D19","v":0}]
[null,null,null,null,null,"Total ",{"$f":"SUM(G141:G143)","v":0},{"$f":"SUM(H141:H143)","v":0}]
[]
[null,null,"O&M Costs for Pumping:"]
[null,null,"Dist. Syst. Pumping Option #",1]
[]
[]
[null,null,"Fraction dist. syst. served by booster pumps",{"$f":"VLOOKUP(D147,E124:F134,2,FALSE)","v":0}]
[null,null,null,null,null,"Power Costs",null,null,null,null,null,null,null,null,"Labour Costs",null,null,"Miscellaneou Replacements"]
[null,null,"Item","Option Button Value","Flow (m3/d)","Fixed Costs","Fixed Costs Applied (yes/no)","Power Rate per Pump (kW/m3/d)","Pump Power (kW)","Demand Rate","Consumption Rate ($/kWh)","Demand Cost","Consumption Cost","Total Power Cost","Slope","Intercept","Costs","Slope","Intercept","Costs"]
[null,null,"Ground water pumping",false,{"$f":"D153*$D$57","v":0},1649.925,{"$f":"F153*D153","v":0},1.4881423050242464E-2,{"$f":"H153*E153","v":0},7.63,0.09,{"$f":"J153*I153*12","v":0},{"$f":"I153*24*365*K153","v":0},{"$f":"L153+M153+G153","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E153=0,0,P153+O153*E153)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E153=0,0,S153+R153*E153)","v":0}]
[null,null,"Low Lift Pumping",false,{"$f":"D154*$D$57","v":0},1649.925,{"$f":"F154*D154","v":0},6.7206426678514354E-3,{"$f":"H154*E154","v":0},7.63,0.09,{"$f":"J154*I154*12","v":0},{"$f":"I154*24*365*K154","v":0},{"$f":"L154+M154+G154","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E154=0,0,P154+O154*E154)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E154=0,0,S154+R154*E154)","v":0}]
[null,null,"High Lift Pumping",false,{"$f":"D155*$D$57","v":0},1649.925,{"$f":"F155*D155","v":0},1.3441285335702871E-2,{"$f":"H155*E155","v":0},7.63,0.09,{"$f":"J155*I155*12","v":0},{"$f":"I155*24*365*K155","v":0},{"$f":"L155+M155+G155","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E155=0,0,P155+O155*E155)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E155=0,0,S155+R155*E155)","v":0}]
[null,null,"Distribution System Pumping",false,{"$f":"IF(D150=\"<Percentage>\",0,D156*$D$57*$D$150)","v":0},1649.925,{"$f":"F156*D156","v":0},6.7206426678514354E-3,{"$f":"H156*E156","v":0},7.63,0.09,{"$f":"J156*I156*12","v":0},{"$f":"I156*24*365*K156","v":0},{"$f":"L156+M156+G156","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E156=0,0,P156+O156*E156)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E156=0,0,S156+R156*E156)","v":0}]
[null,null,{"$f":"\"Total Pumping Cost (\"&$D$22&\"$)\"","v":"Total Pumping Cost (2017$)"},{"$f":"ROUND(SUM(N153:N156,Q153:Q156,T153:T156),-2)","v":0}]
[null,null,{"$f":"\"Total Cost (\"&$D$17&\"$)\"","v":"Total Cost (2017$)"},{"$f":"D157*$D$23","v":0}]
[]
[null,null,"Summary of Theoretical Existing O&M Costs"]
[null,null,"Item","Base Year (2017) Theoretical Costs",{"$f":"\"Inflated Theoretical Costs (\"&D17&\"$)\"","v":"Inflated Theoretical Costs (2017$)"}]
[null,null,"Source",{"$f":"ROUND(H141,0)","v":0},{"$f":"D162*$D$23","v":0}]
[null,null,"Pumping",{"$f":"ROUND(D158,0)","v":0},{"$f":"D163*$D$23","v":0}]
[null,null,"Treatment",{"$f":"ROUND(G122,0)","v":0},{"$f":"D164*$D$23","v":0}]
[null,null,"Transmission & Distribution",{"$f":"ROUND(H142,0)","v":0},{"$f":"D165*$D$23","v":0}]
[null,null,"Administration",{"$f":"ROUND(SUM(D162:D165)*0.1,0)","v":0},{"$f":"D166*$D$23","v":0}]
[null,null,"Total",{"$f":"SUM(D162:D166)","v":0},{"$f":"SUM(E162:E166)","v":0}]
[null,null,"Annual Cost per Population",{"$f":"D167/D47","v":{"$error":"#DIV/0!"}}]
[null,null,"Monthly Cost per Person",{"$f":"D168/12","v":{"$error":"#DIV/0!"}}]
[]
[]
[]
[]
[null,null,"PAGE 3 - Existing Expenditures"]
[]
[null,null,"ACTUAL O&M COSTS"]
[null,null,"Previous Year Actual Expenditures:",{"$f":"IF(D17>2016,D17-1,\"\")","v":2016}]
[]
[]
[null,null,"Item"]
[null,null,"Source of Supply",{"$f":"'1.3) Existing Expenditures'!Q8","v":0}]
[null,null,"Pumping",{"$f":"'1.3) Existing Expenditures'!Q9","v":0}]
[null,null,"Water Treatment",{"$f":"'1.3) Existing Expenditures'!Q10","v":0}]
[null,null,"Transmission & Distribution",{"$f":"'1.3) Existing Expenditures'!Q11","v":0}]
[null,null,"Administration",{"$f":"'1.3) Existing Expenditures'!Q12","v":0}]
[null,null,"Total",{"$f":"SUM(D181:D185)","v":0}]
[]
[]
[]
[]
[]
[null,null,"PAGE 4 - Existing Revenue"]
[]
[null,null,"Water Tax Revenue:"]
[null,null,"Enter the number of customers that pay Water Taxes:",null,{"$f":"'1.4) Existing Revenue'!Q7","v":0}]
[null,null,"Number people per household:",null,2.2999999999999998]
[null,null,"Population:",null,{"$f":"D47","v":0}]
[null,null,"Number of customers:",null,{"$f":"IF(E195=0,E197/E196,E195)","v":0}]
[null,null,"Water Tax:",null,{"$f":"'1.4) Existing Revenue'!Q8","v":0}]
[]
[null,null,"Water & Sewer Tax Revenue:"]
[null,null,"Enter the number of customers that pay Water & Sewer Tax:",null,{"$f":"'1.4) Existing Revenue'!Q12","v":0}]
[null,null,"Enter the annual Water & Sewer Tax rate charged to each customer:",null,{"$f":"'1.4) Existing Revenue'!Q13","v":0}]
[null,null,"%age applied to Sewer",null,{"$f":"'1.4) Existing Revenue'!Q14","v":0}]
[null,null,"Percentage applied to water services",null,{"$f":"E203*(1-E204)","v":0}]
[]
[null,null,"Other Revenue:"]
[null,null,"Number of Industrial Customers:",null,{"$f":"'1.4) Existing Revenue'!Q19","v":0}]
[null,null,"Industrial Rate:",null,{"$f":"'1.4) Existing Revenue'!Q20","v":0}]
[null,null,"Other Revenue:",null,{"$f":"'1.4) Existing Revenue'!Q22","v":0}]
[]
[null,null,"Customer Type","Revenue"]
[null,null,"Water Tax",{"$f":"E198*E199","v":0}]
[null,null,"Water & Sewer Tax",{"$f":"E205*E202","v":0}]
[null,null,"Industrial/Commercial Customers",{"$f":"E208*E209","v":0}]
[null,null,"Other",{"$f":"E210","v":0}]
[null,null,"Total",{"$f":"SUM(D213:D216)","v":0}]
[]
[null,null,"Total Number of Household Customers",{"$f":"SUM(E198,E202)","v":0}]
[]
[]
[]
[]
[]
[null,null,"PAGE 5 - Gap Analysis"]
[]
[null,null,"Analysis of Existing Budget:"]
[null,null,"Actual Total Expenditures:",{"$f":"D186","v":0}]
[null,null,"Current Balance:",{"$f":"D217-D228","v":0}]
[null,null,"Conditional Formatting Rule",{"$f":"IF(D229<0,TRUE,FALSE)","v":false}]
[null,null,"Statement on Current Budget Balance:",{"$f":"IF(D230=TRUE,\"This indicates that existing revenues are not sufficient to cover the costs of operation.\",\"\")","v":""}]
[]
[]
[null,null,"Evaluation of Existing Costs"]
[null,null,"Item",{"$f":"E161","v":"Inflated Theoretical Costs (2017$)"},"Actual Cost","Delta"]
[null,null,"Source",0,{"$f":"IF($D$186=0,E181,D181)","v":0},{"$f":"E236-D162","v":0}]
[null,null,"Pumping",0,0,0]
[null,null,"Treatment",0,0,0]
[null,null,"Transmission & Distribution",0,0,0]
[null,null,"Administration",0,0,0]
[null,null,"Total",0,0,0]
[]
[null,null,"Statement on Existing Costs:"]
[null,null,{"$f":"IF(F241<0,\"This result indicates that the community should consider increasing the budget for operation and maintenance of the water supply system.\",\"\")","v":""}]
[null,null,"Conditional Formatting Rule:",{"$f":"IF(F241<0,TRUE,FALSE)","v":false}]
[]
[null,null,"Gap Analysis:"]
[]
[null,null,"Annual Water Tax",null,{"$f":"IF(D249=0,\"\",IF(D249<0,\"This indicates that existing revenues are not sufficient to cover the costs of operation. \",\"Revenue is sufficient to fund existing operations\"))","v":""}]
[null,null,"Funding Goal","Annual Tax","Gap"]
[null,null,"Current",{"$f":"E199","v":0},0]
[null,null,"Minimum to fund existing operations",{"$f":"(D228-SUM(D215:D216))/D219","v":{"$error":"#DIV/0!"}},{"$f":"D252-D251","v":{"$error":"#DIV/0!"}}]
[null,null,"Minimum to fund Best Practices",{"$f":"E167/E198","v":{"$error":"#DIV/0!"}},{"$f":"D253-D251","v":{"$error":"#DIV/0!"}}]
[]
[null,null,"Conditional Formating Rule",{"$f":"IF(E252>0,TRUE,FALSE)","v":{"$error":"#DIV/0!"}}]
[null,null,{"$f":"\"Water taxes should be increased to a minimum of $\"&ROUNDUP(D252,0)&\" per household to fund existing operations. If water system operation budgets are increased, taxes should be further increased.\"","v":{"$error":"#DIV/0!"}}]
[null,null,"Water taxes are sufficient to fund existing operations. If operations costs are increased, the tax rate should be re-evaluated."]
[]
[]
[]
[]
[]
[null,null,"UPGRADE CONDITIONS:"]
[]
[null,null,"Parameter","Value","Units","Conversion Factor","Flow by User (m3/d)","Per Capita Consumption (L/p/d)"]
[null,null,{"$f":"'2.2) Design Parameters'!D11","v":"Population Serviced"},{"$f":"'2.2) Design Parameters'!I11","v":0}]
[null,null,{"$f":"'2.2) Design Parameters'!D12","v":"Average Flow "},{"$f":"'2.2) Design Parameters'!I12","v":0},{"$f":"'2.2) Design Parameters'!K12","v":"<Units>"},{"$f":"IF(OR(E267=\"<Units>\",E267=0,E267=\"\"),0,VLOOKUP(E267,'Table Inputs'!$T$4:$U$6,2,FALSE))","v":0},{"$f":"D267*F267","v":0},{"$f":"G267*1000/$D$266","v":{"$error":"#DIV/0!"}}]
[null,null,{"$f":"'2.2) Design Parameters'!D13","v":"Maximum Day Flow"},{"$f":"'2.2) Design Parameters'!I13","v":0},{"$f":"'2.2) Design Parameters'!K13","v":"<Units>"},{"$f":"IF(OR(E268=\"<Units>\",E268=0,E268=\"\"),0,VLOOKUP(E268,'Table Inputs'!$T$4:$U$6,2,FALSE))","v":0},{"$f":"D268*F268","v":0},{"$f":"G268*1000/$D$266","v":{"$error":"#DIV/0!"}}]
[null,null,{"$f":"'2.2) Design Parameters'!D14","v":"Peak Flow"},{"$f":"'2.2) Design Parameters'!I14","v":0},{"$f":"'2.2) Design Parameters'!K14","v":"<Units>"},{"$f":"IF(OR(E269=\"<Units>\",E269=0,E269=\"\"),0,VLOOKUP(E269,'Table Inputs'!$T$4:$U$6,2,FALSE))","v":0},{"$f":"D269*F269","v":0}]
[]
[null,null,"Design Flows:","Value","Per Capita"]
[null,null,"Theoretical Per Capita Avg Flow",340]
[null,null,"Average Flow (m3/d)",{"$f":"MAX(G267,D266*D272/1000)","v":0},{"$f":"IF(D266=0,0,D273*1000/D266)","v":0}]
[null,null,"Maximum Day Flow (m3/d)",{"$f":"IF(G268>0,G268,D273*VLOOKUP($D$266,'Table Inputs'!$B$4:$E$12,3,TRUE))","v":0},{"$f":"IF(D266=0,0,D274*1000/D266)","v":0}]
[null,null,"Peak Flow (m3/d)",{"$f":"IF(G269>0,G269,D273*VLOOKUP($D$266,'Table Inputs'!$B$4:$E$12,3,TRUE))","v":0},{"$f":"IF(D266=0,0,D275*1000/D266)","v":0}]
[]
[]
[null,null,"Design Life for Facility Option #",3]
[null,null,"Design Life Option #","Design Life (years)"]
[null,null,1,15]
[null,null,2,20]
[null,null,3,25]
[null,null,4,30]
[]
[null,null,"Chosen Design Life:",{"$f":"VLOOKUP(D278,C280:D283,2,FALSE)","v":25}]
[]
[null,null,"Population Change:"]
[null,null,"Year","Population","Annual Percentage Change"]
[null,null,{"$f":"VLOOKUP(D17,'Table Inputs'!G64:H83,2,TRUE)","v":2016},{"$f":"'2.2) Design Parameters'!L23","v":0}]
[null,null,{"$f":"C289-5","v":2011},{"$f":"'2.2) Design Parameters'!L24","v":0},{"$f":"(D289-D290)/D290/(C289-C290)","v":{"$error":"#DIV/0!"}}]
[null,null,{"$f":"C290-5","v":2006},{"$f":"'2.2) Design Parameters'!L25","v":0},{"$f":"IF(AND(D291>0,D290>0),(D290-D291)/D291/(C290-C291),\"\")","v":""}]
[null,null,null,"Average",{"$f":"IF(AND(E290>0,E291>0),AVERAGE(E290:E291),E290)","v":{"$error":"#DIV/0!"}}]
[]
[null,null,{"$f":"IF(E292>0, \"The census data indicates that the population of the community has increased by approximately \"&ROUND(E292*100,1)&\"% per year\",\"The census data indicates that the population of the community has declined by approximately \"&ROUND(E292*100,1)&\"% per year.\")","v":{"$error":"#DIV/0!"}}]
[]
[null,null,"Population Growth Option #",9]
[null,null,"Population Growth Option #","Growth Value"]
[null,null,1,-0.02]
[null,null,2,-1.7500000000000002E-2]
[null,null,3,-1.4999999999999999E-2]
[null,null,4,-1.2500000000000001E-2]
[null,null,5,-0.01]
[null,null,6,-7.4999999999999997E-3]
[null,null,7,-5.0000000000000001E-3]
[null,null,8,-2.5000000000000001E-3]
[null,null,9,0]
[null,null,10,2.5000000000000001E-3]
[null,null,11,5.0000000000000001E-3]
[null,null,12,7.4999999999999997E-3]
[null,null,13,0.01]
[null,null,14,1.2500000000000001E-2]
[null,null,15,1.4999999999999999E-2]
[null,null,16,1.7500000000000002E-2]
[null,null,17,0.02]
[null,null,18,2.2499999999999999E-2]
[null,null,19,2.5000000000000001E-2]
[null,null,20,2.75E-2]
[]
[null,null,"Selected Annual Population Growth Rate",{"$f":"VLOOKUP(D296,C298:D317,2,FALSE)","v":0}]
[]
[null,null,"Design Life",{"$f":"D285","v":25}]
[null,null,"Selected Growth Rate",{"$f":"D319","v":0}]
[null,null,"Population Change Factor",{"$f":"(1+D322)^D321","v":1}]
[null,null,"Future Design Population",{"$f":"D266*D323","v":0}]
[]
[]
[null,null,"Per Capita Consumption (design values):"]
[null,null,"Average Per Capita Flow",{"$f":"E273","v":0},"L/p/d"]
[null,null,"MDD per capita flow",{"$f":"E274","v":0},"L/p/d"]
[null,null,"Peak Hour Flow",{"$f":"E275","v":0},"L/p/d"]
[]
[null,null,"Design Flows:"]
[null,null,"Design Average Day Flow",{"$f":"MAX(D324,D266)*D328/1000","v":0},"m3/d",{"$f":"D333*1000/3.785/1440","v":0},"usgpm"]
[null,null,"Design Maximum Day Flow",{"$f":"MAX(D324,D266)*D329/1000","v":0},"m3/d",{"$f":"D334*1000/3.785/1440","v":0},"usgpm"]
[null,null,"Design Peak Hour Flow",{"$f":"D330*MAX(D324,D266)/1000","v":0},"m3/d",{"$f":"D335*1000/3.785/1440","v":0},"usgpm"]
[]
[]
[null,null,"Capital"]
[]
[null,null,"Wells"]
[]
[null,null,"Are the upgrades for the community or just specified system?"]
[]
[]
[]
[null,null,"Disinfection System Type"]
[null,null,"Disinfection Option #",1]
[null,null,"Option #","System"]
[null,null,1,"Liquid Chlorine"]
[null,null,2,"Chlorine Gas"]
[null,null,3,"OSHG"]
[null,null,4,"Calcium Hypochlorite"]
[]
[null,null,"New or Existing Equipment Option #",2]
[null,null,"Option #","New/Existing"]
[null,null,1,"New"]
[null,null,2,"Existing"]
[]
[null,null,"UV Yes or No Option #",1]
[null,null,"Option #","New/Existing",null,null,1]
[null,null,1,"Not included"]
[null,null,2,"New"]
[null,null,3,"Existing"]
[]
[null,null,"pH Equipment Option #",1]
[null,null,"Option #","Equipment"]
[null,null,1,"Not included"]
[null,null,2,"New"]
[null,null,3,"Existing"]
[]
[null,null,"Inline Filter Option #",1]
[null,null,"Option #","Equipment"]
[null,null,1,"Not included"]
[null,null,2,"New"]
[null,null,3,"Existing"]
[]
[null,null,"Fe/Mn Removal Option #",1]
[null,null,"Option #","Equipment"]
[null,null,1,"Not included"]
[null,null,2,"New"]
[null,null,3,"Existing"]
[]
[null,null,"As Removal Option #",1]
[null,null,"Option #","Equipment"]
[null,null,1,"Not included"]
[null,null,2,"New"]
[null,null,3,"Existing"]
[]
[null,null,"Membrane Option #",1]
[null,null,"Option #","Equipment"]
[null,null,1,"Not included"]
[null,null,2,"New"]
[null,null,3,"Existing",null,"New Building #",2]
[null,null,null,null,null,"Will New building be required?",{"$f":"IF(G393=1,\"yes\",\"no\")","v":"no"}]
[null,null,"WTP Option #",1]
[null,null,"Option #","Equipment",null,"Will Any of the following requires a new building?"]
[null,null,1,"Not included",null,"Disinfection System",false]
[null,null,2,"New",null,"UV Disinfection System",false]
[null,null,3,"Existing",null,"pH Adjustment System",false]
[null,null,null,null,null,"Inline Filter Building Option #",false]
[null,null,"PWDU Option #",1,null,"Fe/Mn Building Option #",false]
[null,null,"Option #","Equipment",null,"As Building Option #",false]
[null,null,1,"Not included",null,"Membrane  Building Option #",false]
[null,null,2,"New",null,"WTP Building Option #",false]
[null,null,3,"Existing",null,"PWDU Building Option #",false]
[]
[]
[]
[]
[null,null,"Treatment Processes",null,null,"Equipment Cost",null,null,"Building Cost"]
[null,null,null,"Inflation Factor (from 2017$)","Flow Rate (input)","Slope","Intercept","Value","Slope","Intercept","Flow Rate (input)","Value"]
[null,null,"Sodium Hypochlorite",{"$f":"$D$24","v":1},{"$f":"IF(AND($D$347=1,$D$354=1),$D$333,0)","v":0},11.984576982381203,57322.315514959955,{"$f":"IF(E412=0,0,ROUNDUP((F412*E412+G412)*D412,-3))","v":0},78.658979784437079,73175.591125817868,{"$f":"IF(AND($D$347=1,$G$397=TRUE),$D$333,0)","v":0},{"$f":"IF(K412=0,0,ROUNDUP((I412*K412+J412)*D412,-3))","v":0}]
[null,null,"Gas Chlorination",1,{"$f":"IF(AND($D$347=2,$D$354=1),$D$333,0)","v":0},85.774431600000028,62424.725220000008,0,70.254869466122429,280346.26224269392,{"$f":"IF(AND($D$347=2,$G$397=TRUE),$D$333,0)","v":0},0]
[null,null,"Onsite Sodium Hypochlorite Generation System",1,{"$f":"IF(AND($D$347=3,$D$354=1),$D$333,0)","v":0},46.093200606138147,526954.80759916885,0,83.289027724130932,258083.40004449565,{"$f":"IF(AND($D$347=3,$G$397=TRUE),$D$333,0)","v":0},0]
[null,null,"Calcium Hypochlorite",1,{"$f":"IF(AND($D$347=4,$D$354=1),$D$333,0)","v":0},11.984576982381203,57322.315514959955,0,78.658979784437079,73175.591125817868,{"$f":"IF(AND($D$347=4,$G$397=TRUE),$D$333,0)","v":0},0]
[null,null,"UV Disinfection System",1,{"$f":"IF(D359=2,$D$333,0)","v":0},166.20651105882345,239960.65034117649,0,42.97979958204705,68862.163016032951,{"$f":"IF(AND(G398=TRUE,OR(D359=2,D359=3)),$D$333,0)","v":0},0]
[null,null,"pH Adjustment System",1,{"$f":"IF(D365=2,$D$333,0)","v":0},95.883016498842537,21470.296354921928,0,72.915515129557988,83660.507768022551,{"$f":"IF(AND(G399=TRUE,OR(D365=2,D365=3)),$D$333,0)","v":0},0]
[null,null,"Inline Pressure Filtration System",1,{"$f":"IF(D371=2,$D$333,0)","v":0},764.88310799999988,76814.139599999995,0,244.63661904000003,85693.967567999993,{"$f":"IF(AND(G400=TRUE,OR(D371=2,D371=3)),$D$333,0)","v":0},0]
[null,null,"Iron and/or Manganese Removal",1,{"$f":"IF(D377=2,$D$333,0)","v":0},912.62483701244821,334237.3124190871,0,176.90636308929464,91384.900572585073,{"$f":"IF(AND(G401=TRUE,OR(D377=2,D377=3)),$D$333,0)","v":0},0]
[null,null,"Arsenic Removal",1,{"$f":"IF(D383=2,$D$333,0)","v":0},2554.8126685714283,261287.65928571438,0,174.08539391990047,132551.75803804732,{"$f":"IF(AND(G402=TRUE,OR(D383=2,D383=3)),$D$333,0)","v":0},0]
[null,null,"Membrane Filtration",1,{"$f":"IF(D389=2,$D$333,0)","v":0},1916.0176309587571,958903.9913280129,0,901.9075772034281,614925.70902161975,{"$f":"IF(AND(G403=TRUE,OR(D389=2,D389=3)),$D$333,0)","v":0},0]
[null,null,"Conventional WTP",1,{"$f":"IF(D395=2,$D$333,0)","v":0},1001.9744061151077,1210860.8901906474,0,983.61213913346751,650243.07310840592,{"$f":"IF(AND(G404=TRUE,OR(D395=2,D395=3)),$D$333,0)","v":0},0]
[null,null,"PWDU",1,{"$f":"IF(D401=2,$D$333,0)","v":0},1,206725,0,1,190925,{"$f":"IF(AND(G405=TRUE,OR(D401=2,D401=3)),$D$333,0)","v":0},0]
[null,null,"Total Cost",null,{"$f":"ROUNDUP(SUM(L412:L423,H412:H423),-3)","v":0}]
[]
[null,null,"Other Capital Items"]
[null,null,"Yard Pipe",null,"Flow (m3/d)","Pipe Diameter","Cost ($/m)","Length of Yard Pipe (m) (input)","Cost"]
[null,null,"Watermain from WTP","MDD",{"$f":"D334","v":0},{"$f":"VLOOKUP(E428,'Table Inputs'!I4:K21,3,TRUE)","v":4},{"$f":"VLOOKUP(F428,'Table Inputs'!M4:R11,6,FALSE)","v":203.83679999999998},{"$f":"IF(AND('2.3) Treatment Upgrades'!P51=\"\",SUM(L412:L423)>0),20,'2.3) Treatment Upgrades'!P51)","v":0},{"$f":"IF(H428*G428>0,H428*G428,0)","v":0}]
[null,null,"Sewer main (assume 8\" PVC)","N/A","N/A",8,{"$f":"'Table Inputs'!R18","v":208.71359999999999},{"$f":"IF('2.3) Treatment Upgrades'!P55=\"\",0,'2.3) Treatment Upgrades'!P55)","v":0},{"$f":"IF(H429*G429>0,H429*G429,0)","v":0}]
[null,null,"Sub-Total",null,{"$f":"SUM(I428:I429)","v":0}]
[null,null,"Inflation Factor",{"$f":"$D$24","v":1},{"$f":"E430*D431","v":0}]
[null,null,"Design Development & Construction",0.5,{"$f":"E431*D432","v":0}]
[null,null,"Location Factor",0.05,{"$f":"SUM(E431:E432)*D433","v":0}]
[null,null,"Engineering",0.15,{"$f":"SUM(E431:E433)*D434","v":0}]
[null,null,"Yard Pipe Costs",null,{"$f":"ROUNDUP(SUM(E431:E434),-3)","v":0}]
[]
[null,null,"Pumping Included?"]
[null,null,1,"Not Included"]
[null,null,2,"New"]
[null,null,3,"Existing"]
[]
[null,null,"Pumps Included?",null,"Included?","New Building for Pumps?"]
[null,null,"Well Pump",1,{"$f":"VLOOKUP(D443,$C$438:$D$440,2,FALSE)","v":"Not Included"}]
[null,null,"Low Lift Pump",1,{"$f":"VLOOKUP(D444,$C$438:$D$440,2,FALSE)","v":"Not Included"},false]
[null,null,"High Lift Pump",1,{"$f":"VLOOKUP(D445,$C$438:$D$440,2,FALSE)","v":"Not Included"},false]
[]
[]
[]
[]
[null,null,"Default Pump Head (ft):"]
[null,null,"Well Pump",310]
[null,null,"Low Lift Pump",140]
[null,null,"High Lift Pump",280]
[null,null,"Booster Pumps in Distribution system",140]
[null,null,null,null,null,null,null,null,null,"Building Cost"]
[null,null,"Pumps:","Flow Rate (input)","Head (ft)","HP Required","Slope","Intercept","Cost Per Pump","Slope","Intercept","Building Cost"]
[null,null,"Well Pump",{"$f":"$D$334","v":0},{"$f":"D451","v":310},{"$f":"MROUND(D457*1000/3.785/1440*E457/3960/0.78/0.98,0.5)","v":0},258.702685363125,3678.7037348924969,{"$f":"IF(D443=2,G457*F457+H457,0)","v":0}]
[null,null,"Low Lift Pump",{"$f":"$D$334","v":0},{"$f":"D452","v":140},{"$f":"MROUND(D458*1000/3.785/1440*E458/3960/0.78/0.98,0.5)","v":0},819.45457258064516,2874.9712499999951,{"$f":"IF(D444=2,G458*F458+H458,0)","v":0},42.97979958204705,68862.163016032951,{"$f":"IF(F444=TRUE,ROUNDUP(J458*D458+K458,-3),0)","v":0},"<--- used same as UV building addition"]
[null,null,"High Lift Pump",{"$f":"$D$334","v":0},{"$f":"D453","v":280},{"$f":"MROUND(D459*1000/3.785/1440*E459/3960/0.78/0.98,0.5)","v":0},819.45457258064516,2874.9712499999951,{"$f":"IF(D445=2,G459*F459+H459,0)","v":0},42.97979958204705,68862.163016032951,{"$f":"IF(F445=TRUE,ROUNDUP(J459*D459+K459,-3),0)","v":0},"<--- used same as UV building addition"]
[null,null,"Total Pump Costs",null,{"$f":"SUM(I457:I459)","v":0}]
[null,null,"Inflation Factor",{"$f":"$D$24","v":1},{"$f":"E460*D461","v":0}]
[null,null,"Mechanical Pipes & Valves",0.2,{"$f":"$E$461*D462","v":0}]
[null,null,"Mechanical Installation",0.25,{"$f":"$E$461*D463","v":0}]
[null,null,"Process Electrical",0.25,{"$f":"$E$461*D464","v":0}]
[null,null,"Sub-Total",null,{"$f":"SUM(E461:E464)","v":0}]
[null,null,"Design Development & Construction",0.3,{"$f":"E465*D466","v":0}]
[null,null,"Location Factor",0.05,{"$f":"SUM(E465:E466)*D467","v":0}]
[null,null,"Engineering",0.1,{"$f":"SUM(E465:E467)*D468","v":0}]
[null,null,"Total Pump Costs",null,{"$f":"ROUNDUP(SUM(E465:E468),-3)","v":0}]
[]
[null,null,"Pumping Related Building Costs:",{"$f":"SUM(L458:L459)*D461","v":0},"<-- this number is too big; will need to re-evaluate building costs"]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Treated Water Storage Volume:"]
[null,null,"Treated Water Storage","With Fire Storage","Without Fire Storage"]
[null,null,"Fire Storage, A (m3)",{"$f":"D483*D484*3.785/1000","v":567.75},0]
[null,null,"Fire Storage Duration (min)",{"$f":"IF('2.5) Distribution Upgrades'!O12=\"<Fire Storage>\",120,'2.5) Distribution Upgrades'!O12)","v":120}]
[null,null,"Fire Flow Rate (gpm)",{"$f":"IF('2.5) Distribution Upgrades'!O15=\"<Fire Flow>\",1250,'2.5) Distribution Upgrades'!O15)","v":1250}]
[null,null,"Peak Balancing Storage, B",{"$f":"0.25*D334","v":0},{"$f":"0.25*D334","v":0}]
[null,null,"Emergency Storage, C",{"$f":"SUM(D482,D485)*0.25","v":141.9375},{"$f":"0.4*D333","v":0}]
[null,null,"Total Storage",{"$f":"D482+D485+D486","v":709.6875},{"$f":"E482+E485+E486","v":0}]
[null,null,"Required Storage Volume (if storage is provided)",{"$f":"IF('2.5) Distribution Upgrades'!O9=\"yes\",D487,E487)","v":0}]
[]
[null,null,"Storage Cost Curve Values:"]
[null,null,"Cost of Storage ($/m3)"]
[null,null,"Base",50461]
[null,null,"Exponent",0.4425]
[]
[]
[null,null,"Total Storage Cost",null,{"$f":"IF('2.5) Distribution Upgrades'!O7=\"Yes\",D492*D488^D493)","v":false}]
[null,null,"Design Development & Construction",0.3,{"$f":"E496*D497","v":0}]
[null,null,"Location Factor",0.05,{"$f":"SUM(E496:E497)*D498","v":0}]
[null,null,"Engineering",0.1,{"$f":"SUM(E496:E498)*D499","v":0}]
[null,null,"Total Storage Costs",null,{"$f":"ROUNDUP(SUM(E496:E499),-3)","v":0}]
[]
[]
[]
[null,null,"New Distribution System"]
[null,null,"Is the community planning to expand dist. Syst.",{"$f":"'2.5) Distribution Upgrades'!O21","v":"<Yes/No>"}]
[]
[null,null,"Components","Length","Quantity","Unit","Unit Price","Total"]
[null,null,"Mobilization",null,null,"allow",null,{"$f":"SUM(H509:H523)*0.1","v":1150}]
[null,null,"Flagperson",null,null,"allow",null,5000]
[null,null,"Project Sign",null,null,"allow",null,1500]
[null,null,"Topsoil",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D511*1.5","v":0},"m2",10,{"$f":"G511*E511","v":0}]
[null,null,"Sod",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D512*1.5","v":0},"m2",10,{"$f":"G512*E512","v":0}]
[null,null,"Removal of Water lines",null,null,"allow",null,5000]
[null,null,"Trench Excavation",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D514*3*3/2","v":0},"m3",20,{"$f":"G514*E514","v":0}]
[null,null,"Pipe Bedding",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D515*0.8*0.8","v":0},"m3",45,0]
[null,null,"Imported Backfill",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D516*1*2","v":0},"m3",30,0]
[null,null,"Marking & Plastic Tape",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D517","v":0},"m",10,0]
[null,null,"Removal of Asphalt",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D518*2","v":0},"m2",10,0]
[null,null,"Patching of Asphalt",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D519*2","v":0},"m2",80,0]
[null,null,"Watermain (assume 200 mm)",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D520","v":0},"m",{"$f":"'Table Inputs'!N7","v":275},{"$f":"G520*E520","v":0}]
[null,null,"Valves including Valve Boxes",{"$f":"'2.5) Distribution Upgrades'!O25","v":0},{"$f":"D521","v":0},"Ea",5000,0]
[null,null,"Hydrants",{"$f":"'2.5) Distribution Upgrades'!O27","v":0},{"$f":"D522","v":0},"Ea",7500,0]
[null,null,"Restraints, Thrust Blocks, & Fittings Allowance",null,null,"allow",null,{"$f":"0.1*SUM(H520:H522)","v":0}]
[null,null,"Watermain Disinfection",{"$f":"'2.5) Distribution Upgrades'!$O$23","v":0},{"$f":"D524","v":0},"m",2,0]
[null,null,null,null,null,null,"Subtotal",{"$f":"SUM(H508:H524)","v":12650}]
[null,null,"Prime Contractor OH&Profits",null,0.1,null,null,{"$f":"H525*E526","v":1265}]
[null,null,"Design Development & Contingency",null,0.3,null,null,{"$f":"(H525+H526)*E527","v":4174.5}]
[null,null,"Engineering",null,0.15,null,null,{"$f":"(H525+H526+H527)*E528","v":2713.4249999999997}]
[null,null,null,null,null,null,"Total",{"$f":"IF(D505=\"Yes\",SUM(H525:H528),0)","v":0}]
[]
[]
[null,null,"Engineering Study Completed Value:",1]
[null,null,"Yes",1]
[null,null,"No",2]
[]
[null,null,"Cost from Engineering Study",{"$f":"IF(D532=1,'2.6) Cost Curve Predictions'!I11,0)","v":0}]
[]
[null,null,"Upgrade Cost Table - Calculated By Tool"]
[null,null,"Component","Capital Budget Estimate","Government Contributions","Net Funding Required"]
[null,null,"Water Treatment Facility",{"$f":"SUM(H412:H423,L412:L423,E435)","v":0},{"$f":"IF($D$559=\"<Funding>\",\"\",D540*$D$559)","v":""},{"$f":"IF($D$559=\"<Funding>\",D540,D540-E540)","v":0}]
[null,null,"Pumping Systems",{"$f":"SUM(E469,D471)","v":0},{"$f":"IF($D$559=\"<Funding>\",\"\",D541*$D$559)","v":""},{"$f":"IF($D$559=\"<Funding>\",D541,D541-E541)","v":0}]
[null,null,"Distribution ",{"$f":"H529","v":0},{"$f":"IF($D$559=\"<Funding>\",\"\",D542*$D$559)","v":""},{"$f":"IF($D$559=\"<Funding>\",D542,D542-E542)","v":0}]
[null,null,"Storage",{"$f":"E500","v":0},{"$f":"IF($D$559=\"<Funding>\",\"\",D543*$D$559)","v":""},{"$f":"IF($D$559=\"<Funding>\",D543,D543-E543)","v":0}]
[null,null,"Total",{"$f":"SUM(D540:D543)","v":0},{"$f":"SUM(E540:E543)","v":0},{"$f":"SUM(F540:F543)","v":0}]
[]
[]
[null,null,"Cost Table Shown To User"]
[null,null,"Component","Capital Budget Estimate","Government Contributions","Net Funding Required"]
[null,null,"Water Treatment Facility",{"$f":"IF($D$532=1,\"\",D540)","v":""},{"$f":"IF($D$532=1,\"\",E540)","v":""},{"$f":"IF($D$532=1,\"\",F540)","v":""}]
[null,null,"Pumping Systems",{"$f":"IF($D$532=1,\"\",D541)","v":""},"",{"$f":"IF($D$532=1,\"\",F541)","v":""}]
[null,null,"Distribution ","","",""]
[null,null,"Storage","","",""]
[null,null,"Total",{"$f":"IF(D532=1,D536,SUM(D549:D552))","v":0},{"$f":"IF(D532=1,IF(D559=\"<Funding>\",0,D553*D559),E544)","v":0},{"$f":"IF(D559=\"<Funding>\",D553,D553-E553)","v":0}]
[null,null,"-30%",{"$f":"D553*0.7","v":0},0,0]
[null,null,"+30%",{"$f":"D553*1.3","v":0},0,0]
[]
[null,null,"Page X - Financing of Proposed Upgrades"]
[]
[null,null,"Fraction Funded by Government",{"$f":"'2.6) Cost Curve Predictions'!Q16","v":"<Funding>"}]
[null,null,"Term of Loan",{"$f":"'2.7) Financing Results'!P8","v":0}]
[null,null,"Term of Loan (in months)",{"$f":"D560*12","v":0}]
[null,null,"Cash Set Aside for Project By Community",{"$f":"'2.7) Financing Results'!P6","v":0}]
[null,null,"Annual Interest Rate",{"$f":"IF('2.7) Financing Results'!P9=\"<Loan Interest Rate>\",0,'2.7) Financing Results'!P9)","v":0}]
[null,null,"Monthly Interest Rate",{"$f":"D563/12","v":0}]
[]
[null,null,"Principal",{"$f":"F544-D562","v":0}]
[null,null,"Payment Type Option #",1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Loan Repayment","Total Annual Payment","Required Water Tax","Current Water Tax"]
[null,null,null,"Blended Payment Option",null,null,null,"Serial Payment Option",null,null,null,"Selected Type:",null,{"$f":"IF(D567=2,\"Serial Payment\",\"Blended Payment\")","v":"Blended Payment"}]
[null,null,"Year ","Principal","Interest","Total","Balance","Principal","Interest","Annual Payment","Principal Balance","Year","Calendar Year","Annual Payment","Annual Payment"]
[null,null,1,{"$f":"-PPMT($D$563,C571,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"-IPMT($D$563,C571,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"SUM(D571:E571)","v":{"$error":"#NUM!"}},{"$f":"D566-D571","v":{"$error":"#NUM!"}},{"$f":"$D$566/$D$560","v":{"$error":"#DIV/0!"}},{"$f":"$D$563*D566","v":0},{"$f":"H571+I571","v":{"$error":"#DIV/0!"}},{"$f":"D566-H571","v":{"$error":"#DIV/0!"}},1,{"$f":"L571+$D$17","v":2018},{"$f":"IF(L571<($D$560+1),ROUND(VLOOKUP(L571,$C$571:$K$600,IF($D$567=2,8,4),FALSE),0),0)","v":0},{"$f":"N571+$D$657","v":0},{"$f":"O571/$D$662","v":{"$error":"#DIV/0!"}},{"$f":"$E$199","v":0}]
[null,null,2,{"$f":"-PPMT($D$563,C572,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"-IPMT($D$563,C572,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"SUM(D572:E572)","v":{"$error":"#NUM!"}},{"$f":"G571-D572","v":{"$error":"#NUM!"}},{"$error":"#DIV/0!"},{"$f":"$D$563*K571","v":{"$error":"#DIV/0!"}},{"$f":"H572+I572","v":{"$error":"#DIV/0!"}},{"$f":"K571-H572","v":{"$error":"#DIV/0!"}},2,2019,0,0,{"$error":"#DIV/0!"},0]
[null,null,3,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$f":"G572-D573","v":{"$error":"#NUM!"}},{"$error":"#DIV/0!"},{"$f":"$D$563*K572","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$f":"K572-H573","v":{"$error":"#DIV/0!"}},3,2020,0,0,{"$error":"#DIV/0!"},0]
[null,null,4,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},4,2021,0,0,{"$error":"#DIV/0!"},0]
[null,null,5,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},5,2022,0,0,{"$error":"#DIV/0!"},0]
[null,null,6,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},6,2023,0,0,{"$error":"#DIV/0!"},0]
[null,null,7,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},7,2024,0,0,{"$error":"#DIV/0!"},0]
[null,null,8,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},8,2025,0,0,{"$error":"#DIV/0!"},0]
[null,null,9,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},9,2026,0,0,{"$error":"#DIV/0!"},0]
[null,null,10,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},10,2027,0,0,{"$error":"#DIV/0!"},0]
[null,null,11,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},11,2028,0,0,{"$error":"#DIV/0!"},0]
[null,null,12,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},12,2029,0,0,{"$error":"#DIV/0!"},0]
[null,null,13,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},13,2030,0,0,{"$error":"#DIV/0!"},0]
[null,null,14,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},14,2031,0,0,{"$error":"#DIV/0!"},0]
[null,null,15,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},15,2032,0,0,{"$error":"#DIV/0!"},0]
[null,null,16,{"$f":"-PPMT($D$563,C586,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},16,2033,0,0,{"$error":"#DIV/0!"},0]
[null,null,17,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},17,2034,0,0,{"$error":"#DIV/0!"},0]
[null,null,18,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},18,2035,0,0,{"$error":"#DIV/0!"},0]
[null,null,19,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},19,2036,0,0,{"$error":"#DIV/0!"},0]
[null,null,20,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},20,2037,0,0,{"$error":"#DIV/0!"},0]
[null,null,21,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},21,2038,0,0,{"$error":"#DIV/0!"},0]
[null,null,22,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},22,2039,0,0,{"$error":"#DIV/0!"},0]
[null,null,23,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},23,2040,0,0,{"$error":"#DIV/0!"},0]
[null,null,24,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},24,2041,0,0,{"$error":"#DIV/0!"},0]
[null,null,25,{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$f":"$D$563*K594","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},25,2042,0,0,{"$error":"#DIV/0!"},0]
[null,null,26,{"$f":"-PPMT($D$563,C596,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},26,2043,0,0,{"$error":"#DIV/0!"},0]
[null,null,27,{"$f":"-PPMT($D$563,C597,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"-IPMT($D$563,C597,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"SUM(D597:E597)","v":{"$error":"#NUM!"}},{"$f":"G596-D597","v":{"$error":"#NUM!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},27,2044,0,0,{"$error":"#DIV/0!"},0]
[null,null,28,{"$f":"-PPMT($D$563,C598,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"-IPMT($D$563,C598,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"SUM(D598:E598)","v":{"$error":"#NUM!"}},{"$f":"G597-D598","v":{"$error":"#NUM!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},28,2045,0,0,{"$error":"#DIV/0!"},0]
[null,null,29,{"$f":"-PPMT($D$563,C599,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"-IPMT($D$563,C599,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"SUM(D599:E599)","v":{"$error":"#NUM!"}},{"$f":"G598-D599","v":{"$error":"#NUM!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},29,2046,0,0,{"$error":"#DIV/0!"},0]
[null,null,30,{"$f":"-PPMT($D$563,C600,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"-IPMT($D$563,C600,$D$560,$D$566)","v":{"$error":"#NUM!"}},{"$f":"SUM(D600:E600)","v":{"$error":"#NUM!"}},{"$f":"G599-D600","v":{"$error":"#NUM!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},30,2047,0,0,{"$error":"#DIV/0!"},0]
[]
[]
[]
[null,null,"Loan Calculations:"]
[null,null,"Principal:",{"$f":"F544-D562","v":0}]
[null,null,"Monthly Payment",{"$f":"D605*D564/(1-(1+D564)^-D561)","v":{"$error":"#DIV/0!"}}]
[null,null,"Annual Payment",{"$f":"D606*12","v":{"$error":"#DIV/0!"}}]
[null,null,"Total Cost",{"$f":"D606*D561","v":{"$error":"#DIV/0!"}}]
[null,null,"Total Interest Paid",{"$f":"D608-D605","v":{"$error":"#DIV/0!"}}]
[]
[]
[null,null,"Operation and Maintenance Costs:"]
[]
[null,null,"Treatment Type","Selected?","Slope","Intercept","Cost (2017$)",{"$f":"\"Cost (\"&D17&\"$)\"","v":"Cost (2017$)"}]
[null,null,"Disinfection with Sodium Hypochlorite",{"$f":"IF(D347=1,TRUE,FALSE)","v":true},27.719978454889247,5265.0828436358715,{"$f":"D615*(F615+E615*$D$333)","v":5265.0828436358715},{"$f":"G615*$D$23","v":5265.0828436358715}]
[null,null,"Disinfection with Chlorine Gas",{"$f":"IF(D347=2,TRUE,FALSE)","v":false},19.571907463793249,7178.6863988317164,0,0]
[null,null,"Disinfection with OSHG",{"$f":"IF(D347=3,TRUE,FALSE)","v":false},18.047497004321993,10532.577517136679,0,0]
[null,null,"Disinfection with CaOCl",{"$f":"IF(D347=4,TRUE,FALSE)","v":false},23.21091038357644,4439.9029408806073,0]
[null,null,"UV Disinfection",{"$f":"IF(OR(D359=2,D359=3),TRUE,FALSE)","v":false},18.525712295380874,3088.5984949351714,0,0]
[null,null,"+ pH adjustment with Soda Ash",{"$f":"IF(OR(D365=2,D365=3),TRUE,FALSE)","v":false},23.675763200216096,3338.3027348568376,0,0]
[null,null,"Inline Filtration",{"$f":"IF(OR(D371=2,D371=3),TRUE,FALSE)","v":false},13.195412129558767,6031.0288519719088,0,0]
[null,null,"Iron / Manganes Removal",{"$f":"IF(OR(D377=2,D377=3),TRUE,FALSE)","v":false},23.825482622283349,5200.2719638033486,0,0]
[null,null,"Arsenic Removal",{"$f":"IF(OR(D383=2,D383=3),TRUE,FALSE)","v":false},82.462614026967472,7020.7176427112645,0,0]
[null,null,"Membrane Treatment",{"$f":"IF(OR(D389=2,D389=3),TRUE,FALSE)","v":false},88.99687833936602,24906.501725810333,0,0]
[null,null,"Conventional Treatment",{"$f":"IF(OR(D395=2,D395=3),TRUE,FALSE)","v":false},121.71175458729932,32130.81330145456,{"$f":"D625*((F625+E625*$D$333)-SUM(G615:G617,G620))","v":0},0]
[null,null,"PWDU",{"$f":"IF(OR(D401=2,D401=3),TRUE,FALSE)","v":false},1.7929942962687266,13409.481348460295,{"$f":"D626*(F626+E626*$D$333)","v":0},0]
[null,null,null,null,null,"Total Treatment Cost",{"$f":"IF($D$333>0,ROUND(SUM(G615:G626),-2),0)","v":0},{"$f":"IF($D$333>0,ROUND(SUM(H615:H626),-2),0)","v":0}]
[]
[]
[null,null,"O&M Costs for non-treatment Related Items:"]
[null,null,"Item","Slope","Intercept","Cost (2017$)",{"$f":"\"Cost (\"&D17&\"$)\"","v":"Cost (2017$)"}]
[null,null,"Source",10.572432665682996,5306.2727576844245,{"$f":"IF($D$333>0,ROUND(E632+D632*$D$333,-2),0)","v":0},{"$f":"F632*$D$23","v":0}]
[null,null,"Transmission & Distribution",80.314649137267224,41859.268083156603,{"$f":"IF($D$333>0,IF(D266<150,15000,ROUND(E633+D633*$D$333,-2)),0)","v":0},{"$f":"F633*$D$23","v":0}]
[null,null,"Administration"]
[null,null,null,null,"Total",{"$f":"SUM(F632:F634)","v":0},{"$f":"SUM(G632:G634)","v":0}]
[]
[null,null,"O&M Costs for Pumping:"]
[null,null,"Fraction dist. syst. served by booster pumps",{"$f":"D150","v":0}]
[null,null,null,null,null,"Power Costs",null,null,null,null,null,null,null,null,"Labour Costs",null,null,"Miscellaneou Replacements"]
[null,null,"Item","Option Button Value","Flow (m3/d)","Fixed Costs Value","Fixed Cost (Applied/Not Applied)","Pump Power Rate (kW/m3/d)","Pump Power (kW)","Demand Rate","Consumption Rate ($/kWh)","Demand Cost","Consumption Cost","Total Power Cost","Slope","Intercept","Costs","Slope","Intercept","Costs"]
[null,null,"Ground water pumping",{"$f":"IF(OR(D443=2,D443=3),TRUE,FALSE)","v":false},{"$f":"D641*$D$333","v":0},1649.925,{"$f":"F641*D641","v":0},1.4881423050242464E-2,{"$f":"H641*E641","v":0},7.63,0.09,{"$f":"J641*I641*12","v":0},{"$f":"I641*24*365*K641","v":0},{"$f":"L641+M641+G641","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E641=0,0,P641+O641*E641)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E641=0,0,S641+R641*E641)","v":0}]
[null,null,"Low Lift Pumping",{"$f":"IF(OR(D444=2,D444=3),TRUE,FALSE)","v":false},{"$f":"D642*$D$333","v":0},1649.925,{"$f":"F642*D642","v":0},6.7206426678514354E-3,{"$f":"H642*E642","v":0},7.63,0.09,{"$f":"J642*I642*12","v":0},{"$f":"I642*24*365*K642","v":0},{"$f":"L642+M642+G642","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E642=0,0,P642+O642*E642)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E642=0,0,S642+R642*E642)","v":0}]
[null,null,"High Lift Pumping",{"$f":"IF(OR(D445=2,D445=3),TRUE,FALSE)","v":false},{"$f":"D643*$D$333","v":0},1649.925,{"$f":"F643*D643","v":0},1.3441285335702871E-2,{"$f":"H643*E643","v":0},7.63,0.09,{"$f":"J643*I643*12","v":0},{"$f":"I643*24*365*K643","v":0},{"$f":"L643+M643+G643","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E643=0,0,P643+O643*E643)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E643=0,0,S643+R643*E643)","v":0}]
[null,null,"Distribution System Pumping",{"$f":"D156","v":false},{"$f":"IF(D638=\"<Percentage>\",0,D644*D333*D638)","v":0},1649.925,{"$f":"F644*D644","v":0},6.7206426678514354E-3,{"$f":"H644*E644","v":0},7.63,0.09,{"$f":"J644*I644*12","v":0},{"$f":"I644*24*365*K644","v":0},{"$f":"L644+M644+G644","v":0},2.4625352112676056,1881.0422535211269,{"$f":"IF(E644=0,0,P644+O644*E644)","v":0},1.4587525150905432,786.21730382293765,{"$f":"IF(E644=0,0,S644+R644*E644)","v":0}]
[null,null,{"$f":"\"Total Cost (\"&D22&\"$)\"","v":"Total Cost (2017$)"},{"$f":"ROUND(SUM(N641:N644,Q641:Q644,T641:T644),-2)","v":0}]
[null,null,{"$f":"\"Total Cost (\"&D17&\"$)\"","v":"Total Cost (2017$)"},{"$f":"D645*$D$23","v":0}]
[]
[]
[null,null,"TOTAL O&M COSTS For Upgraded System"]
[]
[null,null,"Item",{"$f":"\"Theoretical Cost (\"&D17&\"$)\"","v":"Theoretical Cost (2017$)"}]
[null,null,"Source",{"$f":"G632","v":0}]
[null,null,"Pumping",{"$f":"D646","v":0}]
[null,null,"Treatment",{"$f":"H627","v":0}]
[null,null,"Transmission & Distribution",{"$f":"G633","v":0}]
[null,null,"Administration",{"$f":"SUM(D652:D655)*0.1","v":0}]
[null,null,"Total",{"$f":"SUM(D652:D656)","v":0}]
[null,null,"Annual Cost per Population",{"$f":"D657/D266","v":{"$error":"#DIV/0!"}}]
[null,null,"Monthly Cost per Person",{"$f":"D658/12","v":{"$error":"#DIV/0!"}}]
[]
[null,null,"Number of Customers Entered:",{"$f":"'2.6) Cost Curve Predictions'!Q6","v":0}]
[null,null,"Number of Customers (at year 1)",{"$f":"IF(D661>0,D661,ROUND(D266/2.31,0))","v":0}]
[null,null,"Current Water Tax",{"$f":"'2.6) Cost Curve Predictions'!Q7","v":0}]
[null,null,"Total Annual Costs (Year 1)",{"$f":"O571","v":0}]
[null,null,"Recommended Water Tax",{"$f":"IF(D333=0,0,D664/D662)","v":0}]
[null,null,"Water Tax Statement Conditional Formatting",{"$f":"IF('2.7) Financing Results'!P22>'2.7) Financing Results'!P20,TRUE,FALSE)","v":false}]

{"sheet":"Table Inputs","index":19,"n_rows":106,"n_cols":22,"rows_kept":106,"cut":false,"hidden":true,"merged":[]}
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Watermain Pipe (based on Ductile Iron)"]
[null,"Population",null,"MDF","PDF",null,"Existing or New?",null,"Peak Hour Flow (m3/d)","Pipe Size (inches)","Recommended Nominal Pipe (inches)",null,"Pipe Diameter (inch)","Pipe & Installation Cost ($/m)","Excavation Volume (m3/m)","Volume Rock Excavated (/m)","Rock Allowance ($80/m3/m)","Total Installation Cost ($/m)",null,"Given Unit","Conversion Factor"]
[null,0,1000,2.75,4.13,null,"<Building?>",null,0,0,4,null,2,100,{"$f":"(M4*25.4/1000+0.7)*2+0.8^2","v":2.1415999999999999},{"$f":"O4*0.3","v":0.64247999999999994},{"$f":"P4*80","v":51.398399999999995},{"$f":"N4+Q4","v":151.39839999999998},null,"m3/d",1,"m3/d"]
[null,{"$f":"C4+1","v":1001},2000,2.5,3.75,null,"New",null,4.13,0,4,null,4,150,{"$f":"(M5*25.4/1000+0.7)*2+0.8^2","v":2.2431999999999999},{"$f":"O5*0.3","v":0.67295999999999989},{"$f":"P5*80","v":53.83679999999999},{"$f":"N5+Q5","v":203.83679999999998},null,"usgpm",{"$f":"3.785/1000*1440","v":5.4504000000000001},"m3/d"]
[null,2001,3000,2.25,3.38,null,"Existing",null,854.91,4,4,null,6,200,2.3448000000000002,0.70344000000000007,56.275200000000005,256.27519999999998,null,"igpm",{"$f":"4.55/1000*1440","v":6.5520000000000005},"m3/d"]
[null,3001,10000,2,3,null,null,null,859.04,5,6,null,8,275,2.4464000000000001,0.73392000000000002,58.7136,333.71359999999999]
[null,10001,25000,1.9,2.85,null,"<Fire Storage>",null,1404.2,6,6,null,10,300,2.548,0.76439999999999997,61.152000000000001,361.15199999999999]
[null,25001,50000,1.8,2.7,null,30,null,1788.75,6,6,null,12,325,2.6496,0.79487999999999992,63.590399999999995,388.59039999999999]
[null,50001,75000,1.75,2.62,null,60,null,1792.5,7,8,null,14,400,2.7511999999999999,0.82535999999999998,66.028800000000004,466.02879999999999]
[null,75001,150000,1.65,2.42,null,90,null,2550,8,8,null,16,450,2.8527999999999998,0.85583999999999993,68.467199999999991,518.46720000000005]
[null,150001,null,1.5,2.25,null,120,null,3062.2799999999997,8,8,null,"Assume 2 m deep, 30% excavation is rock"]
[null,null,null,null,null,null,150,null,3065.66,9,10,null,"Total Excavation volume per m"]
[null,null,null,null,null,null,180,null,3447.6,9,10]
[null,"<Units>",null,"<Design Life>",null,null,null,null,4674,10,10]
[null,"usgpm",null,20,null,null,null,null,4676.8500000000004,11,12,null,"Wastewater Pipe (assume gravity flow)"]
[null,"igpm",null,25,null,null,"<Fire flow>",null,6626.25,12,12,null,"Pipe Diameter (inch)","Pipe & Installation","Excavation Volume (m3/m)","Volume Rock Excavated (/m)","Rock Allowance ($80/m3/m)","Total Installation Cost ($/m)"]
[null,"m3/d",null,30,null,null,250,null,6629.1,13,14,null,8,150,{"$f":"(M18*25.4/1000+0.7)*2+0.8^2","v":2.4464000000000001},{"$f":"O18*0.3","v":0.73392000000000002},{"$f":"P18*80","v":58.7136},{"$f":"N18+Q18","v":208.71359999999999}]
[null,null,null,null,null,null,500,null,8914.8000000000011,14,14,null,"Assume 2 m deep, 30% excavation is rock"]
[null,null,null,null,null,null,750,null,8917.65,15,16,null,"Total Excavation volume per m"]
[null,"<Yes/No>",null,"<Geographic Region>",null,null,1000,null,11970,17,18]
[null,"No",null,"St. John's/Avalon",null,null,1250]
[null,"Yes",null,"Eastern",null,null,1500,null,null,null,null,null,"Disinfection System Type","Chemical Used","Quantity Units"]
[null,null,null,"Central",null,null,1750,null,null,null,null,null,"Sodium Hypochlorite","Sodium Hypochlorite","L/year"]
[null,null,null,"Western",null,null,2000,null,null,null,null,null,"Gas Chlorination","Chlorine Gas","kg/year"]
[null,null,null,"Labrador",null,null,null,null,null,null,null,null,"Onsite Hypo Generation","Salt","kg/year"]
[null,null,null,null,null,null,null,null,null,null,"Chemicals"]
[null,null,null,null,null,null,"pH adjustment Chemicals",null,null,null,"<pH Chemical>"]
[null,null,null,"<Type of Supply>",null,null,null,null,null,null,"Soda Ash"]
[null,null,null,"Surface Water",null,null,"Soda Ash",null,null,null,"Sodium Hydroxide"]
[null,null,null,"Groundwater ",null,null,"Sodium Hydroxide",null,null,null,"Lime"]
[null,null,null,null,null,null,"Lime"]
[null,null,null,"<Gravity or Pumped>",null,null,"Carbon Dioxide"]
[null,null,null,"Gravity",null,null,"Sulfuric Acid"]
[null,null,null,"Pumped",null,null,"Hydrochloric Acid"]
[null,null,null,null,null,null,null,null,null,null,"Treatment Chemicals"]
[null,null,null,"<Loan Interest Rate>",null,null,null,null,null,null,"<Other Chemicals>"]
[null,null,null,1.4999999999999999E-2,null,null,"Disinfection System Type",null,"Disinfection System Upgrade",null,"Polymer"]
[null,null,null,0.02,null,null,"<Disinfection System Type>",null,"<Existing/New?>",null,"Potassium Permanganate"]
[null,null,null,2.5000000000000001E-2,null,null,"Sodium Hypochlorite",null,"Existing"]
[null,null,null,0.03,null,null,"Gas Chlorination",null,"New"]
[null,null,null,3.5000000000000003E-2,null,null,"Onsite Hypo Generation"]
[null,null,null,0.04]
[null,null,null,4.4999999999999998E-2,null,null,"Treatment Processes"]
[null,null,null,0.05,null,null,"<Treatment Processes>"]
[null,null,null,5.5E-2,null,null,"pH Adjustment System"]
[null,null,null,0.06,null,null,"Pressure Filtration System"]
[null,null,null,6.5000000000000002E-2,null,null,"Membrane Filtration",null,null,null,"Other Chemicals"]
[null,"With VFD?",null,null,null,null,"As Removal",null,null,null,"<Other Chemicals>"]
[null,"Yes",null,null,null,null,"Fe and/or Mn Removal",null,null,null,"Citric Acid"]
[null,"No",null,null,null,null,"Conventional WTP",null,null,null,"Corrosion Inhibitor"]
[null,null,null,null,null,null,"PWDU"]
[]
[null,"Dates",null,null,null,null,"<USER GENERATED OR ESTIMATED BASED ON POPULATION>"]
[null,"Month","Day ","<Year>",null,null,"User Generated"]
[null,"January",1,2017,null,null,"Using Population Estimate"]
[null,"February",2,2018]
[null,"March",3,2019,null,null,"<USER GENERATED OR PEAKING FACTOR>"]
[null,"April",4,2020,null,null,"User Generated"]
[null,"May",5,2021,null,null,"Using Peaking Factor"]
[null,"June",6,2022]
[null,"July",7,2023]
[null,"August",8,2024,null,null,"Current Date","Census Year",null,null,"<Equipment>"]
[null,"September",9,2025,null,null,2017,2016,null,null,"New"]
[null,"October",10,2026,null,null,{"$f":"G64+5","v":2022},{"$f":"H64+5","v":2021},null,null,"Existing"]
[null,"November",11,2027,null,null,2027,2026]
[null,"December",12,2028,null,null,2032,2031]
[null,null,13,2029,null,null,2037,2036,null,null,"<Funding>"]
[null,null,14,2030,null,null,2042,2041,null,null,0]
[null,null,15,2031,null,null,2047,2046,null,null,0.1]
[null,null,16,2032,null,null,2052,2051,null,null,0.2]
[null,null,17,2033,null,null,2057,2056,null,null,0.3]
[null,null,18,2034,null,null,2062,2061,null,null,0.4]
[null,null,19,2035,null,null,2067,2066,null,null,0.5]
[null,null,20,2036,null,null,2072,2071,null,null,0.6]
[null,null,21,2037,null,null,2077,2076,null,null,0.7]
[null,null,22,2038,null,null,2082,2081,null,null,0.8]
[null,null,23,2039,null,null,{"$f":"G77+5","v":2087},{"$f":"H77+5","v":2086},null,null,0.9]
[null,null,24,2040,null,null,2092,2091,null,null,1]
[null,null,25,null,null,null,2097,2096]
[null,null,26,null,null,null,2102,2101]
[null,null,27,null,null,null,2107,2106]
[null,null,28,null,null,null,2112,2111]
[null,null,29]
[null,null,30]
[null,null,31,null,null,null,"<Percentage>","Booster Pump Sets"]
[null,null,null,null,null,null,0,0]
[null,null,null,null,null,null,0.1,1]
[null,null,null,null,null,null,0.2,2]
[null,"Primary Seasonal Flows",null,null,null,null,0.3,3]
[null,"Flows do not fluctuate seasonally",null,null,null,null,0.4]
[null,6,null,null,null,null,0.5]
[null,7,null,null,null,null,0.6]
[null,8,null,null,null,null,0.7]
[null,9,null,null,null,null,0.8]
[null,10,null,null,null,null,0.9]
[null,11,null,null,null,null,1]
[null,12]
[]
[null,"Secondary Seasonal Flows"]
[null,"Flows do not fluctuate seasonally "]
[null,1]
[null,2]
[null,3]
[null,4]
[null,5]