{"text_format":"1","workbook":"xlsx","n_sheets":24,"sheets":["Definitions","Definitions (Tutorial)","Data Collection Check List","Data Coll Check List (Tutorial)","Costs Check List","Costs Check List (Tutorial)","Rates Check List","Meter Error Calculation","Water Management Table","Rates Check List (Tutorial)","Meter Error Calc (Tutorial)","Water Mgmt Table (Tutorial)","Meter Error Table","Meter Error Table (Tutorial)","Cost to Produce Calculator","Cost to Produce Calc (Tutorial)","Retail Charge Calculator","Retail Charge Calc (Tutorial)","Water Management Chart","Notes","Water Mgmt Chart (Tutorial)","Notes (Tutorial)","Chart Data","Chart Data (Tutorial)"],"not_rendered":{"charts":4,"images":0,"macros":1,"external_links":0,"defined_names":5,"comments":10,"pivot_tables":0},"unopened":[{"sheet":"Water Management Chart","index":18,"reason":"not_a_worksheet"},{"sheet":"Water Mgmt Chart (Tutorial)","index":20,"reason":"not_a_worksheet"}],"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":"Definitions","index":0,"n_rows":66,"n_cols":2,"rows_kept":66,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["SMALL SYSTEM WATER AUDIT DEFINITIONS"]
[]
["WATER DELIVERED (Gal/Yr)"]
[" 1. Total Source Water Supply (Raw Water)","Total volume of water obtained from ground or surface water sources."]
["          Ground Water Sources","Water obtained from wells."]
["          Surface Water Sources","Water obtained from surface withdrawals."]
[" 2. Adjustments to Water Delivery","Total volume of water used at the plant that is not sent to the distribution system."]
["          Backwash water","Water used to clean out a clogged filter by reversing the flow of water."]
["         Other (e.g. tank maintenance, meter calibration, wash water, etc.)","Water that is used in operation, maintenance, and treatment processes at the plant."]
["3. Water Produced (as read from meter to distribution)","Finished Water that leaves the WTP and enters the water distribution system."]
["4. Purchase Water Sources","If your system purchases water, enter the gallon amount here."]
["Water Delivered to Distribution System  ","Water that enters the distribution system as registered by the meter."]
["WATER USED (Gal/Yr)"]
[" 5. Water Sold (Metered and Unmetered)"]
["         Residential","May include Boarding houses, Family care homes, Guest houses, Home occupations, Home professional offices, Manufactured Homes, Manufactured Home Parks, Multi-family dwellings, Planned unit developments, Residential accessory uses, Single-family dwellings, Vacation homes, Two-family dwellings (duplex), Three-family dwellings (triplex), etc."]
["         Commercial","May include Animal hospitals, Automobile repairs, Automobile sales, Automobile service stations, Banks, Barber/beauty shops, Business offices, Bus Stations, Butcher shops, Cab stands, Convenience stores, Dental clinics, Exterminators, Farms, Farm machinery showrooms, Fish markets, Funeral homes, Golf courses, Hardware stores, Hotels, Kennels, Laundry/dry cleaning storefronts, Medical clinics, Mobile home showrooms, Motorcycle showrooms, Nurseries, Paint/wallpaper showrooms, Parking lots, Pet shops, Print shops, Professional service agencies, Radio/TV repair shops, Recreational vehicle sales and service, Restaurants, Retail stores, Seed/feed stores, Shoe repair shops, Specialized service agencies, Studios (art, photo, design, etc.), Television or radio broadcast stations, Theaters, Train stations, etc."]
["         Industrial","May include Manufacturing Facilities, Processing Facilities, Utilities, etc."]
["         Institutional","May include Hospitals, Assisted living facilities, Correctional facilities, Schools, Colleges, Churches, Municipal buildings, Police stations, etc."]
["         Other Water Systems (Utilities)","Includes other entities outside the service area that the water system sold water to."]
["         Other","Includes metered situations not covered above."]
["Total Water Sold ","Total volume of water sold by the water system as registered by customer meters. Please note that all possible customers are listed. You may skip entities that do not pertain to your system."]
[" 6. Water Not Sold (Metered and Unmetered)"]
["       Residential","May include Boarding houses, Family care homes, Guest houses, Home occupations, Home professional offices, Manufactured Homes, Manufactured Home Parks, Multi-family dwellings, Planned unit developments, Residential accessory uses, Single-family dwellings, Vacation homes, Two-family dwellings (duplex), Three-family dwellings (triplex), etc."]
["       Commercial","May include Animal hospitals, Automobile repairs, Automobile sales, Automobile service stations, Banks, Barber/beauty shops, Business offices, Bus Stations, Butcher shops, Cab stands, Convenience stores, Dental clinics, Exterminators, Farms, Farm machinery showrooms, Fish markets, Funeral homes, Golf courses, Hardware stores, Hotels, Kennels, Laundry/dry cleaning storefronts, Medical clinics, Mobile home showrooms, Motorcycle showrooms, Nurseries, Paint/wallpaper showrooms, Parking lots, Pet shops, Print shops, Professional service agencies, Radio/TV repair shops, Recreational vehicle sales and service, Restaurants, Retail stores, Seed/feed stores, Shoe repair shops, Specialized service agencies, Studios (art, photo, design, etc.), Television or radio broadcast stations, Theaters, Train stations, etc."]
["       Industrial","May include Manufacturing Facilities, Processing Facilities, Utilities, etc."]
["       Institutional","May include Hospitals, Assisted living facilities, Correctional facilities, Schools, Colleges, Churches, Government buildings, Police stations, etc."]
["       Water Main Flushing","Water used for distribution system maintenance."]
["       Sewer/Storm Drain Flushing","Water used for distribution system maintenance."]
["       Parks/Playgrounds/Swimming Pools","Water used for irrigation, bathrooms, etc."]
["       Road Medians","Water used for maintenance or other."]
["       Schools","Water used for irrigation, bathrooms, maintenance, etc."]
["       Training/Fire Fighting","Water used for training, maintenance or other."]
["       Storage Tank Drainage (within distribution system)","Water used for maintenance or other."]
["       Sewer Plant Uses","Water used for maintenance or other."]
["       Municipal Buildings","Water used in buildings owned and used by the town or county."]
["       Golf Courses","Water used for irrigation, clubhouse, etc."]
["       Cemeteries","Water used for maintenance or other."]
["       Construction Use","Water used for development purposes."]
["       Other","Includes categories not covered above."]
["Total Water Not Sold","Total water consumption that is authorized but not billed."]
["WATER LOSSES (Gal/Yr)"]
[" 7. Apparent Water Losses"]
["        Water Meter Error","Water loss due to meter inaccuracy."]
["        Data Handling Error","Water loss due to meter reading or billing system error. "]
["        Theft","Loss due to water illegally taken from fire hydrants and other connections. Theft at hydrants can include unauthorized commercial connections. Theft at customer connections can include tampering with meters or meter reading equipment, and unauthorized connections. You may use the standard 0.25% of water delivered to the distribution system if you have a low level of confidence in your estimated volumes. "]
["        Other   ","Includes categories not covered above."]
["Total Apparent Losses","Total volume of water made up of non-physical losses resulting from customer meter inaccuracies, systematic data handling errors in customer billing systems and unauthorized consumption. In other words, this is water that is used but not properly metered, accounted for or billed for. "]
[" 8. Real Water Losses"]
["        Distribution System Leaks","Water loss due to pipeline or joint failures.  This value is automatically calculated by subtracting all WATER USED and other WATER LOSSES from water delivered to the distribution system."]
["        Storage Overflow","Water loss due to tank overflows."]
["        Other   ","Includes categories not covered above."]
["Total Real Losses","Total volume of water made up of physical losses resulting from distribution system leaks, tank overflows, and other quantifiable loss due to equipment operation or material failures."]
[" 9. Total  Non-Revenue Water","Total of Apparent Losses, Real Losses, and Water not sold."]
[" 10. Percentage  Water Loss","This is the percent of water delivered to the distribution system that was lost as apparent or real losses."]
[" 11. Annual Cost of Apparent Losses","Apparent losses is water supplied but not paid for. The cost is calculated by multiplying the number of gallons lost by the retail charge for water."]
[" 12. Annual Cost of Real Losses","Real losses includes water that has been extracted from a water resource, treated, energized, and transported a distance before being lost to the distribution system. The cost is calculated by multiplying the number of gallons lost by the cost of producing the next thousand gallons of water."]
[" 13. Cost to Produce Water (see tab: Cost to Produce Calculator)","This value represents the cost to the water system to produce the next thousand gallons of water. Include the price for chemicals, electricity and repair and maintenance costs."]
[" 14. Retail Charge for Water (see tab: Retail Charge Calculator)","This value represents the retail charge your customers pay for the water they use. You may use the average retail charge for each customer type or class in your system. The workbook calculates a  weighted retail charge based on the amount of water used by each customer class. The more water a customer class uses, the greater effect it will have on revenue loss and recovery."]
[" 15. Composite rate for water not sold ($/1,000 Gal.)","This value represents the retail rate for all customer classes (use types) that are not normally billed for water, and it is used in the calculation of Annual Cost of Apparent Losses. If the cell is blank, the retail charge for billed customers is applied to water not sold. If the cell contains a value, that value is applied to water not sold."]
[]
[]
[]
[]
[]
[]
["     "]

{"sheet":"Definitions (Tutorial)","index":1,"n_rows":66,"n_cols":2,"rows_kept":66,"cut":false,"hidden":true,"merged":[[0,0,0,1],[2,0,2,1],[12,0,12,1],[40,0,40,1]]}
["SMALL SYSTEM WATER AUDIT DEFINITIONS (TUTORIAL)"]
[]
["WATER DELIVERED (Gal/Yr)"]
[" 1. Total Source Water Supply (Raw Water)","Total volume of water obtained from ground or surface water sources."]
["          Ground Water Sources","Water obtained from wells."]
["          Surface Water Sources","Water obtained from surface withdrawals."]
[" 2. Adjustments to Water Delivery","Total volume of water used at the plant that is not sent to the distribution system."]
["          Backwash water","Water used to clean out a clogged filter by reversing the flow of water."]
["         Other (e.g. tank maintenance, meter calibration, wash water, etc.)","Water that is used in operation, maintenance, and treatment processes at the plant."]
["3. Water Produced (as read from meter to distribution)","Finished Water that leaves the WTP and enters the water distribution system."]
["4. Purchase Water Sources","If your system purchases water, enter the gallon amount here."]
["Water Delivered to Distribution System  ","Water that enters the distribution system as registered by the meter."]
["WATER USED (Gal/Yr)"]
[" 5. Water Sold (Metered and Unmetered)"]
["         Residential","May include Boarding houses, Family care homes, Guest houses, Home occupations, Home professional offices, Manufactured Homes, Manufactured Home Parks, Multi-family dwellings, Planned unit developments, Residential accessory uses, Single-family dwellings, Vacation homes, Two-family dwellings (duplex), Three-family dwellings (triplex), etc."]
["         Commercial","May include Animal hospitals, Automobile repairs, Automobile sales, Automobile service stations, Banks, Barber/beauty shops, Business offices, Bus Stations, Butcher shops, Cab stands, Convenience stores, Dental clinics, Exterminators, Farms, Farm machinery showrooms, Fish markets, Funeral homes, Golf courses, Hardware stores, Hotels, Kennels, Laundry/dry cleaning storefronts, Medical clinics, Mobile home showrooms, Motorcycle showrooms, Nurseries, Paint/wallpaper showrooms, Parking lots, Pet shops, Print shops, Professional service agencies, Radio/TV repair shops, Recreational vehicle sales and service, Restaurants, Retail stores, Seed/feed stores, Shoe repair shops, Specialized service agencies, Studios (art, photo, design, etc.), Television or radio broadcast stations, Theaters, Train stations, etc."]
["         Industrial","May include Manufacturing Facilities, Processing Facilities, Utilities, etc."]
["         Institutional","May include Hospitals, Assisted living facilities, Correctional facilities, Schools, Colleges, Churches, Municipal buildings, Police stations, etc."]
["         Other Water Systems (Utilities)","Includes other entities outside the service area that the water system sold water to."]
["         Other","Includes metered situations not covered above."]
["Total Water Sold ","Total volume of water sold by the water system as registered by customer meters. (Please note that all possible customers were listed. You may skip entities that do not pertain to your system."]
[" 6. Water Not Sold (Metered and Unmetered)"]
["       Residential","May include Boarding houses, Family care homes, Guest houses, Home occupations, Home professional offices, Manufactured Homes, Manufactured Home Parks, Multi-family dwellings, Planned unit developments, Residential accessory uses, Single-family dwellings, Vacation homes, Two-family dwellings (duplex), Three-family dwellings (triplex), etc."]
["       Commercial","May include Animal hospitals, Automobile repairs, Automobile sales, Automobile service stations, Banks, Barber/beauty shops, Business offices, Bus Stations, Butcher shops, Cab stands, Convenience stores, Dental clinics, Exterminators, Farms, Farm machinery showrooms, Fish markets, Funeral homes, Golf courses, Hardware stores, Hotels, Kennels, Laundry/dry cleaning storefronts, Medical clinics, Mobile home showrooms, Motorcycle showrooms, Nurseries, Paint/wallpaper showrooms, Parking lots, Pet shops, Print shops, Professional service agencies, Radio/TV repair shops, Recreational vehicle sales and service, Restaurants, Retail stores, Seed/feed stores, Shoe repair shops, Specialized service agencies, Studios (art, photo, design, etc.), Television or radio broadcast stations, Theaters, Train stations, etc."]
["       Industrial","May include Manufacturing Facilities, Processing Facilities, Utilities, etc."]
["       Institutional","May include Hospitals, Assisted living facilities, Correctional facilities, Schools, Colleges, Churches, Government buildings, Police stations, etc."]
["       Water Main Flushing","Water used for distribution system maintenance."]
["       Sewer/Storm Drain Flushing","Water used for distribution system maintenance."]
["       Parks/Playgrounds/Swimming Pools","Water used for irrigation, bathrooms, etc."]
["       Road Medians","Water used for maintenance or other."]
["       Schools","Water used for irrigation, bathrooms, maintenance, etc."]
["       Training/Fire Fighting","Water used for training, maintenance or other."]
["       Storage Tank Drainage (within distribution system)","Water used for maintenance or other."]
["       Sewer Plant Uses","Water used for maintenance or other."]
["       Municipal Buildings","Water used in buildings owned and used by the town or county."]
["       Golf Courses","Water used for irrigation, clubhouse, etc."]
["       Cemeteries","Water used for maintenance or other."]
["       Construction Use","Water used for development purposes."]
["       Other","Includes categories not covered above."]
["Total Water Not Sold","Total water consumption that is authorized but not billed."]
["WATER LOSSES (Gal/Yr)"]
[" 7. Apparent Water Losses"]
["        Water Meter Error (see tab: Meter Error Table)","Water loss due to meter inaccuracy."]
["        Data Handling Error","Water loss due to meter reading or billing system error. "]
["        Theft","Loss due to water illegally taken from fire hydrants and other connections. Theft at hydrants can include unauthorized commercial connections. Theft at customer connections can include tampering with meters or meter reading equipment, and unauthorized connections. You may use the standard 0.25% of water delivered to the distribution system if you have low levels of confidence in your estimated volumes. "]
["        Other   ","Includes categories not covered above."]
["Total Apparent Losses","Total volume of water made up of non-physical losses resulting from customer meter inaccuracies, systematic data handling errors in customer billing systems and unauthorized consumption. In other words, this is water that is used but not properly metered, accounted for or billed for. "]
[" 8. Real Water Losses"]
["        Distribution System Leaks","Water loss due to pipeline or joint failures.  This value is automatically calculated by subtracting all WATER USED and other WATER LOSSES from water delivered to the distribution system."]
["        Storage Overflow","Water loss due to tank overflows."]
["        Other   ","Includes categories not covered above."]
["Total Real Losses","Total volume of water made up of physical losses resulting from distribution system leaks, tank overflows, and other quantifiable loss due to equipment operation or material failures."]
[" 9. Total  Non-Revenue Water","Total of Apparent Losses, Real Losses, and Water not sold."]
[" 10. Percentage  Water Loss","This is the percent of water delivered to the distribution system that was lost as apparent or real losses."]
[" 11. Annual Cost of Apparent Losses","Apparent losses is water supplied but not paid for. This number is calculated by multiplying the number of gallons lost by the retail charge for water. The volume of authorized water use that is not sold is also included as part of the total volume used to calculate the annual cost of apparent losses."]
[" 12. Annual Cost of Real Losses","Real losses includes water that has been extracted from a water resource, treated, energized, and transported a distance before being lost to the distribution system. The cost is calculated by multiplying the number of gallons lost by the cost of producing the next thousand gallons of water."]
[" 13. Cost to Produce Water (see tab: Cost to Produce Calculator)","This value represents the cost to the water system to produce the next thousand gallons of water. Include the price for chemicals, electricity and repair and maintenance costs."]
[" 14. Retail Charge for Water (see tab: Retail Charge Calculator)","This value represents the retail charge your customers pay for the water they use. You may use the average retail charge for each customer type or class in your system. The workbook calculates a weighted retail charge based on the amount of water used by each customer class. The more water a customer class uses, the greater effect it will have on revenue loss and recovery."]
[" 15. Composite rate for water not sold ($/1,000 Gal.)","This value represents the retail rate for all customer classes (use types) that are not normally billed for water, and it is used in the calculation of Annual Cost of Apparent Losses. If the cell is blank, the retail charge for billed customers is applied to water not sold. If the cell contains a value, that value is applied to water not sold."]
[]
[]
[]
[]
[]
[]
["     "]

{"sheet":"Data Collection Check List","index":2,"n_rows":49,"n_cols":5,"rows_kept":49,"cut":false,"hidden":false,"merged":[[12,6,12,7],[43,1,43,3],[3,1,3,3],[0,0,0,4],[1,0,1,4],[11,1,11,3],[19,1,19,3],[38,1,38,3]]}
["WATER AUDIT DATA COLLECTION WORKSHEET"]
["(Units should be reported in gallons per year, GPY)"]
[null,"Data Year:"]
["WATER DELIVERED (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected the Following:","Yes","Metered","N/A"]
["Amount of raw ground water pumped"]
["Amount of raw surface water pumped"]
["Amount of finished water produced"]
["Amount of backwash water used at WTP"]
["Amount of other water used at WTP"]
["Amount of water purchased"]
["WATER USED (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Sold for:","Yes","Metered","N/A"]
["Residential Use"]
["Commercial Use"]
["Industrial Use"]
["Institutional Use"]
["Other Utilities Usage (water sold to other systems)"]
["Other"]
["WATER USED (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Not Sold for:","Yes","Metered","N/A"]
["Residential Use"]
["Commercial Use"]
["Industrial Use"]
["Institutional Use"]
["Water Main Flushing"]
["Sewer/Storm Drain Flushing"]
["Parks/Playgrounds/Swimming Pools Usage"]
["Road Median Usage"]
["School Usage"]
["Training/Fire Fighting"]
["Storage Tank Drainage (within distribution system)"]
["Sewer Plant Usage"]
["Municipal Buildings Usage"]
["Golf Course Usage"]
["Cemetery Usage"]
["Construction Use"]
["Other"]
["WATER LOSSES (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Loss from:","Yes","N/A"]
["Data Handling Error"]
["Theft"]
["Other"]
["WATER LOSSES (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Loss from:","Yes","N/A"]
["Storage Overflow"]
["Other (e.g. service line to meter leaks)"]
[]
["IF YOU DON'T HAVE AN ESTIMATE OF THE ACCURACY OF YOUR METERS, USE THE METER ERROR CALCULATION WORKSHEET."]

{"sheet":"Data Coll Check List (Tutorial)","index":3,"n_rows":49,"n_cols":5,"rows_kept":49,"cut":false,"hidden":true,"merged":[[12,6,12,7],[19,1,19,3],[38,1,38,3],[43,1,43,3],[0,0,0,4],[1,0,1,4],[3,1,3,3],[11,1,11,3]]}
["WATER AUDIT DATA COLLECTION WORKSHEET (TUTORIAL)"]
["(Units should be reported in gallons per year, GPY)"]
[null,"Data Year:",2021]
["WATER DELIVERED (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected the Following:","Yes","Metered","N/A"]
["Amount of raw ground water pumped",null,"a",null,22630000]
["Amount of raw surface water pumped",null,"a",null,54500000]
["Amount of finished water produced"]
["Amount of backwash water used at WTP",null,"a",null,5000000]
["Amount of other water used at WTP"]
["Amount of water purchased",null,"a",null,158045000]
["WATER USED (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Sold for:","Yes","Metered","N/A"]
["Residential Use",null,"a",null,151475000]
["Commercial Use",null,"a",null,15695000]
["Industrial Use",null,"a",null,2190000]
["Institutional Use",null,"a",null,8030000]
["Other Utilities Usage (water sold to other systems)",null,"a",null,7300000]
["Other"]
["WATER USED (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Not Sold for:","Yes","Metered","N/A"]
["Residential Use"]
["Commercial Use"]
["Industrial Use"]
["Institutional Use",null,"a",null,1000000]
["Water Main Flushing",null,null,null,10000000]
["Sewer/Storm Drain Flushing"]
["Parks/Playgrounds/Swimming Pools Usage"]
["Road Median Usage"]
["School Usage"]
["Training/Fire Fighting"]
["Storage Tank Drainage (within distribution system)"]
["Sewer Plant Usage"]
["Municipal Buildings Usage"]
["Golf Course Usage"]
["Cemetery Usage"]
["Construction Use"]
["Other"]
["WATER LOSSES (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Loss from:","Yes","N/A"]
["Data Handling Error","a",null,null,200000]
["Theft","a",null,null,575438]
["Other"]
["WATER LOSSES (Gal/Yr)","Check Appropriate Box",null,null,"Enter Quantities In This Column"]
["Have I Collected Data on Water Loss from:","Yes","N/A"]
["Storage Overflow"]
["Other (e.g. service line to meter leaks)"]
[]
["IF YOU DON'T HAVE AN ESTIMATE OF THE ACCURACY OF YOUR METERS, USE THE METER ERROR CALCULATION WORKSHEET."]

{"sheet":"Costs Check List","index":4,"n_rows":21,"n_cols":4,"rows_kept":21,"cut":false,"hidden":false,"merged":[[2,1,2,2],[0,0,0,3]]}
["COSTS DATA COLLECTION WORKSHEET"]
[null,"Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
["COST TO PRODUCE WATER (enter annual cost only)","Check Appropriate Column",null,"Enter Costs In This Column"]
["Have I Collected Data on Costs for Providing Safe Water:","Yes","N/A"]
["Electricity Cost",null,null,0]
["Chemical Cost",null,null,0]
["Chemical Cost",null,null,0]
["Chemical Analysis Cost",null,null,0]
["Purchased Water Cost",null,null,0]
["Maintenance Cost",null,null,0]
["Maintenance Cost",null,null,0]
["Other Cost (e.g. natural gas)",null,null,0]
["Other Cost ?",null,null,0]
["Other Cost ?",null,null,0]
["Other Cost ?",null,null,0]
["Salaries (water only)*",null,null,0]
["Employee Benefits (water only)*",null,null,0]
["Building Rent/Mortgage (water only)*",null,null,0]
["* Usually not included in determining the cost of providing the next thousand gallons of water. This cost should be taken"]
["into account when determining the rate structure and price points to cover the full cost of operating and maintaining"]
["your water system."]

{"sheet":"Costs Check List (Tutorial)","index":5,"n_rows":21,"n_cols":4,"rows_kept":21,"cut":false,"hidden":true,"merged":[[0,0,0,3],[2,1,2,2]]}
["COSTS DATA COLLECTION WORKSHEET (TUTORIAL)"]
[null,"Data Year:",2021]
["COST TO PRODUCE WATER (enter annual cost only)","Check Appropriate Column",null,"Enter Costs In This Column"]
["Have I Collected Data on Costs for Providing Safe Water:","Yes","N/A"]
["Electricity Cost - describe as needed","a",null,155200]
["Chemical Cost - describe as needed","a",null,40000]
["Chemical Cost - describe as needed",null,null,0]
["Chemical Analysis Cost - describe as needed",null,null,0]
["Purchased Water Cost - describe as needed","a",null,63492]
["Maintenance Cost - describe as needed","a",null,100000]
["Maintenance Cost - describe as needed",null,null,0]
["Other Cost - describe as needed",null,null,0]
["Other Cost - describe as needed",null,null,0]
["Other Cost - describe as needed",null,null,0]
["Other Cost - describe as needed",null,null,0]
["Salaries (water only)*",null,null,0]
["Employee Benefits (water only)*",null,null,0]
["Building Rent/Mortgage (water only)*",null,null,0]
["* Usually not included in determining the cost of providing the next thousand gallons of water. This cost should be taken"]
["into account when determining the rate structure and price points to cover the full cost of operating and maintaining"]
["your water system."]

{"sheet":"Rates Check List","index":6,"n_rows":12,"n_cols":5,"rows_kept":12,"cut":false,"hidden":false,"merged":[[6,0,6,1],[7,0,7,1],[8,0,8,1],[2,2,2,3],[0,0,0,4],[3,0,3,1],[2,0,2,1],[4,0,4,1],[5,0,5,1]]}
["WATER RATES DATA COLLECTION WORKSHEET"]
[null,null,"Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
["RETAIL CHARGE FOR WATER",null,"Check Appropriate Column",null,"Enter Rates In This Column"]
["Have I Collected Data on Water Rates for:",null,"Yes","N/A","$/1,000 Gal."]
["Residential Customers",null,null,null,0]
["Commercial Customers",null,null,null,0]
["Industrial Customers",null,null,null,0]
["Institutional Customers",null,null,null,0]
["Other Water Systems",null,null,null,0]
["Other",null,null,null,0]
["USE THE AVERAGE RETAIL CHARGE FOR EACH CUSTOMER TYPE."]
["IF YOU HAVE AN INCREASING BLOCK STRUCTURE, USE THE RATE THAT CAPTURES THE MAJORITY OF CUSTOMERS."]

{"sheet":"Meter Error Calculation","index":7,"n_rows":177,"n_cols":8,"rows_kept":177,"cut":false,"hidden":false,"merged":[[175,0,175,1],[176,0,176,1],[0,0,0,5],[158,5,158,7],[75,0,75,7],[109,0,109,7],[144,0,144,7],[40,0,40,6],[89,5,89,7],[123,5,123,7],[67,0,67,1],[68,0,68,1],[106,0,106,1],[107,0,107,1],[140,0,140,1],[141,0,141,1]]}
["METER ERROR CALCULATION WORKSHEET"]
[]
["Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
[]
["Month","Residential, Gal","Commercial, Gal","Industrial, Gal","Institutional, Gal","Total, Gal"]
["January",null,null,null,null,{"$f":"SUM(B6:E6)","v":0}]
["February",null,null,null,null,0]
["March",null,null,null,null,0]
["April",null,null,null,null,0]
["May",null,null,null,null,0]
["June",null,null,null,null,0]
["July",null,null,null,null,0]
["August",null,null,null,null,0]
["September",null,null,null,null,0]
["October",null,null,null,null,0]
["November",null,null,null,null,0]
["December",null,null,null,null,0]
["Annual Total, Gal",{"$f":"SUM(B6:B17)","v":0},{"$f":"SUM(C6:C17)","v":0},{"$f":"SUM(D6:D17)","v":0},{"$f":"SUM(E6:E17)","v":0},{"$f":"SUM(F6:F17)","v":0}]
["Daily Average, MGD",{"$f":"B18/(365*1000000)","v":0},{"$f":"C18/(365*1000000)","v":0},{"$f":"D18/(365*1000000)","v":0},{"$f":"E18/(365*1000000)","v":0},{"$f":"F18/(365*1000000)","v":0}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["RESIDENTIAL METER ERROR CALCULATION"]
[]
["Flow range volume percentages for residential meters.",null,null,null,"Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
[]
["Range, gpm","% Volume"]
["Low ( )",0.03]
["Medium ( )"]
["High ( )"]
["Total, must equal 100%",{"$f":"SUM(B46:B48)","v":0.03}]
["Range:  Identify low, medium, and high flow ranges for the recorded flow over a 24-hr period."]
["% Volume:  Ratio of volume used in each of the flow ranges to total volume used for the recording period (must total 100%)."]
[]
["Meter testing data for a random sample of residential meters."]
[]
["Test flow rates, gpm","Mean Registration, %"]
["Low ( )"]
["Medium ( )"]
["High ( )"]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of residential meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[{"$f":"B46","v":0.03},{"$f":"B18/1000000","v":0},{"$f":"A65*B65","v":0},{"$f":"B56","v":0},{"$f":"IF(D65>0,(C65/D65)-C65,0)","v":0}]
[{"$f":"B47","v":0},{"$f":"B18/1000000","v":0},{"$f":"A66*B66","v":0},{"$f":"B57","v":0},{"$f":"IF(D66>0,(C66/D66)-C66,0)","v":0}]
[{"$f":"B48","v":0},{"$f":"B18/1000000","v":0},{"$f":"A67*B67","v":0},{"$f":"B58","v":0},{"$f":"IF(D67>0,(C67/D67)-C67,0)","v":0}]
["Total residential meter error (MG)",null,null,null,{"$f":"SUM(E65:E67)","v":0}]
["Residential meter error (%)",null,null,null,{"$f":"IF(B65>0,E68/B65,0)","v":0},"Transfer Meter Error to Audit."]
[]
[]
[]
[]
[]
[]
["COMMERCIAL METER ERROR CALCULATION"]
[]
["Flow range volume percentages for commercial meters.",null,null,null,null,"Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
[]
["Range, gpm","% Volume"]
["Low ( )"]
["Medium ( )"]
["High ( )"]
["Total, must equal 100%",{"$f":"SUM(B81:B83)","v":0}]
["Range:  Identify low, medium, and high flow ranges for the recorded flow over a 24-hr period."]
["% Volume:  Ratio of volume used in each of the flow ranges to total volume used for the recording period (must total 100%)."]
[]
["Meter testing data for commercial meters."]
[]
["Meter ID Number","Meter Type &  Size, in.","Date of Installation","Manufacturer","Test Date","Test flow rates, gpm"]
[null,null,null,null,null,"Low ( )","Medium ( )","High ( )"]
[]
[]
[]
[]
[]
["Mean registration %",null,null,null,null,{"$f":"IF(F92>0,AVERAGE(F92:F96),0)","v":0},{"$f":"IF(G92>0,AVERAGE(G92:G96),0)","v":0},{"$f":"IF(H92>0,AVERAGE(H92:H96),0)","v":0}]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of commercial meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[{"$f":"B81","v":0},{"$f":"C18/1000000","v":0},{"$f":"A104*B104","v":0},{"$f":"F97","v":0},{"$f":"IF(D104>0,(C104/D104)-C104,0)","v":0}]
[{"$f":"B82","v":0},{"$f":"C18/1000000","v":0},{"$f":"A105*B105","v":0},{"$f":"G97","v":0},{"$f":"IF(D105>0,(C105/D105)-C105,0)","v":0}]
[{"$f":"B83","v":0},{"$f":"C18/1000000","v":0},{"$f":"A106*B106","v":0},{"$f":"H97","v":0},{"$f":"IF(D106>0,(C106/D106)-C106,0)","v":0}]
["Total commercial meter error (MG)",null,null,null,{"$f":"SUM(E104:E106)","v":0}]
["Commercial meter error (%)",null,null,null,{"$f":"IF(B104>0,E107/B104,0)","v":0},"Transfer Meter Error to Audit."]
[]
["INDUSTRIAL METER ERROR CALCULATION"]
[]
["Flow range percentages for industrial meters.",null,null,null,null,"Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
[]
["Range, gpm","% Volume"]
["Low ( )"]
["Medium ( )"]
["High ( )"]
["Total, must equal 100%",{"$f":"SUM(B115:B117)","v":0}]
["Range:  Identify low, medium, and high flow ranges for the recorded flow over a 24-hr period."]
["% Volume:  Ratio of volume used in each of the flow ranges to total volume used for the recording period (must total 100%)."]
[]
["Meter testing data for industrial meters."]
[]
["Meter ID Number","Meter Type &  Size, in.","Date of Installation","Manufacturer","Test Date","Test flow rates, gpm"]
[null,null,null,null,null,"Low ( )","Medium ( )","High ( )"]
[]
[]
[]
[]
[]
["Mean registration %",null,null,null,null,{"$f":"IF(F126>0,AVERAGE(F126:F130),0)","v":0},{"$f":"IF(G126>0,AVERAGE(G126:G130),0)","v":0},{"$f":"IF(H126>0,AVERAGE(H126:H130),0)","v":0}]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of industrial meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[{"$f":"B115","v":0},{"$f":"D18/1000000","v":0},{"$f":"A138*B138","v":0},{"$f":"F131","v":0},{"$f":"IF(D138>0,(C138/D138)-C138,0)","v":0}]
[{"$f":"B116","v":0},{"$f":"D18/1000000","v":0},{"$f":"A139*B139","v":0},{"$f":"G131","v":0},{"$f":"IF(D139>0,(C139/D139)-C139,0)","v":0}]
[{"$f":"B117","v":0},{"$f":"D18/1000000","v":0},{"$f":"A140*B140","v":0},{"$f":"H131","v":0},{"$f":"IF(D140>0,(C140/D140)-C140,0)","v":0}]
["Total industrial meter error (MG)",null,null,null,{"$f":"SUM(E138:E140)","v":0}]
["Industrial meter error (%)",null,null,null,{"$f":"IF(B138>0,E141/B138,0)","v":0},"Transfer Meter Error to Audit."]
[]
[]
["INSTITUTIONAL METER ERROR CALCULATION"]
[]
["Flow range percentages for institutional meters.",null,null,null,null,"Data Year:",{"$f":"IF('Data Collection Check List'!C3=\"\",\"No Data\",'Data Collection Check List'!C3)","v":"No Data"}]
[]
["Range, gpm","% Volume"]
["Low ( )"]
["Medium ( )"]
["High ( )"]
["Total, must equal 100%",{"$f":"SUM(B150:B152)","v":0}]
["Range:  Identify low, medium, and high flow ranges for the recorded flow over a 24-hr period."]
["% Volume:  Ratio of volume used in each of the flow ranges to total volume used for the recording period (must total 100%)."]
[]
["Meter testing data for institutional meters."]
[]
["Meter ID Number","Meter Type &  Size, in.","Date of Installation","Manufacturer","Test Date","Test flow rates, gpm"]
[null,null,null,null,null,"Low ( )","Medium ( )","High ( )"]
[]
[]
[]
[]
[]
["Mean registration %",null,null,null,null,{"$f":"IF(F161>0,AVERAGE(F161:F165),0)","v":0},{"$f":"IF(G161>0,AVERAGE(G161:G165),0)","v":0},{"$f":"IF(H161>0,AVERAGE(H161:H165),0)","v":0}]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of institutional meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[{"$f":"B150","v":0},{"$f":"E18/1000000","v":0},{"$f":"A173*B173","v":0},{"$f":"F166","v":0},{"$f":"IF(D173>0,(C173/D173)-C173,0)","v":0}]
[{"$f":"B151","v":0},{"$f":"E18/1000000","v":0},{"$f":"A174*B174","v":0},{"$f":"G166","v":0},{"$f":"IF(D174>0,(C174/D174)-C174,0)","v":0}]
[{"$f":"B152","v":0},{"$f":"E18/1000000","v":0},{"$f":"A175*B175","v":0},{"$f":"H166","v":0},{"$f":"IF(D175>0,(C175/D175)-C175,0)","v":0}]
["Total institutional meter error (MG)",null,null,null,{"$f":"SUM(E173:E175)","v":0}]
["Institutional meter error (%)",null,null,null,{"$f":"IF(B173>0,E176/B173,0)","v":0},"Transfer Meter Error to Audit."]

{"sheet":"Water Management Table","index":8,"n_rows":65,"n_cols":17,"rows_kept":65,"cut":false,"hidden":false,"merged":[[68,0,68,1],[6,14,6,16],[6,2,6,4],[6,11,6,13],[6,5,6,7],[6,8,6,10],[7,5,7,7],[7,2,7,4],[7,8,7,10],[7,11,7,13],[7,14,7,16],[13,0,13,1],[61,0,61,1],[62,0,62,1],[5,14,5,16],[5,0,5,1],[5,5,5,7],[5,8,5,10],[5,11,5,13]]}
["WATER SYSTEM AUDIT WORKSHEET"]
["(Units should be reported in gallons per year, gpy)"]
[]
["Enter values in non-shaded cells only."]
[]
["Cells with red triangles      contain information. Roll your pointer over the triangle to reveal the information."]
[null,null,2021,null,null,2022,null,null,2023,null,null,2024,null,null,2025]
["WATER DELIVERED (Gal/Yr)"]
[" 1. Total Source Water Supply (Raw Water)",null,"Metered","Unmetered",{"$f":"SUM(E10:E11)","v":0},"Metered","Unmetered",{"$f":"SUM(H10:H11)","v":0},"Metered","Unmetered",{"$f":"SUM(K10:K11)","v":0},"Metered","Unmetered",{"$f":"SUM(N10:N11)","v":0},"Metered","Unmetered",{"$f":"SUM(Q10:Q11)","v":0}]
["Ground Water Sources"]
["Surface Water Sources"]
[" 2. Adjustments to Water Delivery",null,null,null,{"$f":"SUM(E13:E14)","v":0},null,null,{"$f":"SUM(H13:H14)","v":0},null,null,{"$f":"SUM(K13:K14)","v":0},null,null,{"$f":"SUM(N13:N14)","v":0},null,null,{"$f":"SUM(Q13:Q14)","v":0}]
["Backwash water"]
["Other (e.g. tank maintenance, meter calibration, wash water, etc.)"]
["3.  Water Produced (as read from finished water meter)"]
["4. Purchase Water Sources"]
["Water Delivered to Distribution System",null,null,null,{"$f":"IF(E15>0,(E15+E16),E9-E12+E16)","v":0},null,null,{"$f":"IF(H15>0,(H15+H16),H9-H12+H16)","v":0},null,null,{"$f":"IF(K15>0,(K15+K16),K9-K12+K16)","v":0},null,null,{"$f":"IF(N15>0,(N15+N16),N9-N12+N16)","v":0},null,null,{"$f":"IF(Q15>0,(Q15+Q16),Q9-Q12+Q16)","v":0}]
["WATER USED (Gal/Yr)"]
[" 5. Water Sold",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered"]
["Residential"]
["Commercial"]
["Industrial"]
["Institutional"]
["Other Water Systems (Utilities)"]
["Other"]
["Total Water Sold",null,null,null,{"$f":"SUM(E20:E25)","v":0},null,null,{"$f":"SUM(H20:H25)","v":0},null,null,{"$f":"SUM(K20:K25)","v":0},null,null,{"$f":"SUM(N20:N25)","v":0},null,null,{"$f":"SUM(Q20:Q25)","v":0}]
[" 6. Water Not Sold (metered and unmetered)",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered"]
["Residential"]
["Commercial"]
["Industrial"]
["Institutional"]
["Water Main Flushing"," [Institutional]"]
["Sewer/Storm Drain Flushing"," [Institutional]"]
["Parks/Playgrounds/Swimming Pools"," [Institutional]"]
["Road Medians"," [Institutional]"]
["Schools"," [Institutional]"]
["Training/Fire Fighting"," [Institutional]"]
["Storage Tank Drainage (within distribution system)"," [Institutional]"]
["Sewer Plant Uses"," [Institutional]"]
["Municipal Buildings"," [Institutional]"]
["Golf Courses","[Commercial]"]
["Cemeteries","[Commercial]"]
["Construction Use","[Commercial]"]
["Other"]
["Total Water Not Sold",null,null,null,{"$f":"SUM(E28:E44)","v":0},null,null,{"$f":"SUM(H28:H44)","v":0},null,null,{"$f":"SUM(K28:K44)","v":0},null,null,{"$f":"SUM(N28:N44)","v":0},null,null,{"$f":"SUM(Q28:Q44)","v":0}]
["WATER LOSSES (Gal/Yr)"]
[" 7. Apparent Water Losses"]
["Water Meter Error (see tab: Meter Error Table)",null,null,null,{"$f":"IF(C20=\"m\",('Meter Error Table'!B4*E20),0)+IF(C21=\"m\",('Meter Error Table'!B5*E21),0)+IF(C22=\"m\",('Meter Error Table'!B6*E22),0)+IF(C23=\"m\",('Meter Error Table'!B7*E23),0)+IF(C24=\"m\",('Meter Error Table'!B8*E24),0)+IF(C25=\"m\",('Meter Error Table'!B9*E25),0)+IF(C28=\"m\",('Meter Error Table'!B4*E28),0)+IF(C29=\"m\",('Meter Error Table'!B5*E29),0)+IF(C30=\"m\",('Meter Error Table'!B6*E30),0)+IF(C31=\"m\",('Meter Error Table'!B7*E31),0)+IF(C44=\"m\",('Meter Error Table'!B9*E44),0)+IF(C32=\"m\",('Meter Error Table'!B7*E32),0)+IF(C33=\"m\",('Meter Error Table'!B7*E33),0)+IF(C34=\"m\",('Meter Error Table'!B7*E34),0)+IF(C35=\"m\",('Meter Error Table'!B7*E35),0)+IF(C36=\"m\",('Meter Error Table'!B7*E36),0)+IF(C37=\"m\",('Meter Error Table'!B7*E37),0)+IF(C38=\"m\",('Meter Error Table'!B7*E38),0)+IF(C39=\"m\",('Meter Error Table'!B7*E39),0)+IF(C40=\"m\",('Meter Error Table'!B7*E40),0)+IF(C41=\"m\",('Meter Error Table'!B5*E41),0)+IF(C42=\"m\",('Meter Error Table'!B5*E42),0)+IF(C43=\"m\",('Meter Error Table'!B5*E43),0)","v":0},null,null,{"$f":"IF(F20=\"m\",('Meter Error Table'!D4*H20),0)+IF(F21=\"m\",('Meter Error Table'!D5*H21),0)+IF(F22=\"m\",('Meter Error Table'!D6*H22),0)+IF(F23=\"m\",('Meter Error Table'!D7*H23),0)+IF(F24=\"m\",('Meter Error Table'!D8*H24),0)+IF(F25=\"m\",('Meter Error Table'!D9*H25),0)+IF(F28=\"m\",('Meter Error Table'!D4*H28),0)+IF(F29=\"m\",('Meter Error Table'!D5*H29),0)+IF(F30=\"m\",('Meter Error Table'!D6*H30),0)+IF(F31=\"m\",('Meter Error Table'!D7*H31),0)+IF(F44=\"m\",('Meter Error Table'!D9*H44),0)+IF(F32=\"m\",('Meter Error Table'!D7*H32),0)+IF(F33=\"m\",('Meter Error Table'!D7*H33),0)+IF(F34=\"m\",('Meter Error Table'!D7*H34),0)+IF(F35=\"m\",('Meter Error Table'!D7*H35),0)+IF(F36=\"m\",('Meter Error Table'!D7*H36),0)+IF(F37=\"m\",('Meter Error Table'!D7*H37),0)+IF(F38=\"m\",('Meter Error Table'!D7*H38),0)+IF(F39=\"m\",('Meter Error Table'!D7*H39),0)+IF(F40=\"m\",('Meter Error Table'!D7*H40),0)+IF(F41=\"m\",('Meter Error Table'!D5*H41),0)+IF(F42=\"m\",('Meter Error Table'!D5*H42),0)+IF(F43=\"m\",('Meter Error Table'!D5*H43),0)","v":0},null,null,{"$f":"IF(I20=\"m\",('Meter Error Table'!F4*K20),0)+IF(I21=\"m\",('Meter Error Table'!F5*K21),0)+IF(I22=\"m\",('Meter Error Table'!F6*K22),0)+IF(I23=\"m\",('Meter Error Table'!F7*K23),0)+IF(I24=\"m\",('Meter Error Table'!F8*K24),0)+IF(I25=\"m\",('Meter Error Table'!F9*K25),0)+IF(I28=\"m\",('Meter Error Table'!F4*K28),0)+IF(I29=\"m\",('Meter Error Table'!F5*K29),0)+IF(I30=\"m\",('Meter Error Table'!F6*K30),0)+IF(I31=\"m\",('Meter Error Table'!F7*K31),0)+IF(I44=\"m\",('Meter Error Table'!F9*K44),0)+IF(I32=\"m\",('Meter Error Table'!F7*K32),0)+IF(I33=\"m\",('Meter Error Table'!F7*K33),0)+IF(I34=\"m\",('Meter Error Table'!F7*K34),0)+IF(I35=\"m\",('Meter Error Table'!F7*K35),0)+IF(I36=\"m\",('Meter Error Table'!F7*K36),0)+IF(I37=\"m\",('Meter Error Table'!F7*K37),0)+IF(I38=\"m\",('Meter Error Table'!F7*K38),0)+IF(I39=\"m\",('Meter Error Table'!F7*K39),0)+IF(I40=\"m\",('Meter Error Table'!F7*K40),0)+IF(I41=\"m\",('Meter Error Table'!F5*K41),0)+IF(I42=\"m\",('Meter Error Table'!F5*K42),0)+IF(I43=\"m\",('Meter Error Table'!F5*K43),0)","v":0},null,null,{"$f":"IF(L20=\"m\",('Meter Error Table'!H4*N20),0)+IF(L21=\"m\",('Meter Error Table'!H5*N21),0)+IF(L22=\"m\",('Meter Error Table'!H6*N22),0)+IF(L23=\"m\",('Meter Error Table'!H7*N23),0)+IF(L24=\"m\",('Meter Error Table'!H8*N24),0)+IF(L25=\"m\",('Meter Error Table'!H9*N25),0)+IF(L28=\"m\",('Meter Error Table'!H4*N28),0)+IF(L29=\"m\",('Meter Error Table'!H5*N29),0)+IF(L30=\"m\",('Meter Error Table'!H6*N30),0)+IF(L31=\"m\",('Meter Error Table'!H7*N31),0)+IF(L44=\"m\",('Meter Error Table'!H9*N44),0)+IF(L32=\"m\",('Meter Error Table'!H7*N32),0)+IF(L33=\"m\",('Meter Error Table'!H7*N33),0)+IF(L34=\"m\",('Meter Error Table'!H7*N34),0)+IF(L35=\"m\",('Meter Error Table'!H7*N35),0)+IF(L36=\"m\",('Meter Error Table'!H7*N36),0)+IF(L37=\"m\",('Meter Error Table'!H7*N37),0)+IF(L38=\"m\",('Meter Error Table'!H7*N38),0)+IF(L39=\"m\",('Meter Error Table'!H7*N39),0)+IF(L40=\"m\",('Meter Error Table'!H7*N40),0)+IF(L41=\"m\",('Meter Error Table'!H5*N41),0)+IF(L42=\"m\",('Meter Error Table'!H5*N42),0)+IF(L43=\"m\",('Meter Error Table'!H5*N43),0)","v":0},null,null,{"$f":"IF(O20=\"m\",('Meter Error Table'!J4*Q20),0)+IF(O21=\"m\",('Meter Error Table'!J5*Q21),0)+IF(O22=\"m\",('Meter Error Table'!J6*Q22),0)+IF(O23=\"m\",('Meter Error Table'!J7*Q23),0)+IF(O24=\"m\",('Meter Error Table'!J8*Q24),0)+IF(O25=\"m\",('Meter Error Table'!J9*Q25),0)+IF(O28=\"m\",('Meter Error Table'!J4*Q28),0)+IF(O29=\"m\",('Meter Error Table'!J5*Q29),0)+IF(O30=\"m\",('Meter Error Table'!J6*Q30),0)+IF(O31=\"m\",('Meter Error Table'!J7*Q31),0)+IF(O44=\"m\",('Meter Error Table'!J9*Q44),0)+IF(O32=\"m\",('Meter Error Table'!J7*Q32),0)+IF(O33=\"m\",('Meter Error Table'!J7*Q33),0)+IF(O34=\"m\",('Meter Error Table'!J7*Q34),0)+IF(O35=\"m\",('Meter Error Table'!J7*Q35),0)+IF(O36=\"m\",('Meter Error Table'!J7*Q36),0)+IF(O37=\"m\",('Meter Error Table'!J7*Q37),0)+IF(O38=\"m\",('Meter Error Table'!J7*Q38),0)+IF(O39=\"m\",('Meter Error Table'!J7*Q39),0)+IF(O40=\"m\",('Meter Error Table'!J7*Q40),0)+IF(O41=\"m\",('Meter Error Table'!J5*Q41),0)+IF(O42=\"m\",('Meter Error Table'!J5*Q42),0)+IF(O43=\"m\",('Meter Error Table'!J5*Q43),0)","v":0}]
["Data Handling Error"]
["Theft"]
["Other"]
["Total Apparent Losses",null,null,null,{"$f":"SUM(E48:E51)","v":0},null,null,{"$f":"SUM(H48:H51)","v":0},null,null,{"$f":"SUM(K48:K51)","v":0},null,null,{"$f":"SUM(N48:N51)","v":0},null,null,{"$f":"SUM(Q48:Q51)","v":0}]
[" 8. Real Water Losses"]
["Distribution System Leaks",null,null,null,{"$f":"E17-E26-E45-E52-E55-E56","v":0},null,null,{"$f":"H17-H26-H45-H52-H55-H56","v":0},null,null,{"$f":"K17-K26-K45-K52-K55-K56","v":0},null,null,{"$f":"N17-N26-N45-N52-N55-N56","v":0},null,null,{"$f":"Q17-Q26-Q45-Q52-Q55-Q56","v":0}]
["Storage Overflow"]
["Other (e.g. service line to meter leaks)"]
["Total Real Losses",null,null,null,{"$f":"SUM(E54:E56)","v":0},null,null,{"$f":"SUM(H54:H56)","v":0},null,null,{"$f":"SUM(K54:K56)","v":0},null,null,{"$f":"SUM(N54:N56)","v":0},null,null,{"$f":"SUM(Q54:Q56)","v":0}]
[" 9. Total  Non-Revenue Water",null,null,null,{"$f":"E52+E57+E45","v":0},null,null,{"$f":"H52+H57+H45","v":0},null,null,{"$f":"K52+K57+K45","v":0},null,null,{"$f":"N52+N57+N45","v":0},null,null,{"$f":"Q52+Q57+Q45","v":0}]
[" 10. Percentage  Water Loss",null,null,null,{"$f":"IF(E17>0,IF(E57<0,\"ERROR\",(E52+E57)/E17),\"0\")","v":"0"},null,null,{"$f":"IF(H17>0,IF(H57<0,\"ERROR\",(H52+H57)/H17),\"0\")","v":"0"},null,null,{"$f":"IF(K17>0,IF(K57<0,\"ERROR\",(K52+K57)/K17),\"0\")","v":"0"},null,null,{"$f":"IF(N17>0,IF(N57<0,\"ERROR\",(N52+N57)/N17),\"0\")","v":"0"},null,null,{"$f":"IF(Q17>0,IF(Q57<0,\"ERROR\",(Q52+Q57)/Q17),\"0\")","v":"0"}]
[" 11. Annual Cost of Apparent Losses",null,null,null,{"$f":"IF(E64=\"\",(E63*((E45+E52)/1000)),(E64*(E45/1000)+E63*(E52/1000)))","v":0},null,null,{"$f":"IF(H64=\"\",(H63*((H45+H52)/1000)),(H64*(H45/1000)+H63*(H52/1000)))","v":0},null,null,{"$f":"IF(K64=\"\",(K63*((K45+K52)/1000)),(K64*(K45/1000)+K63*(K52/1000)))","v":0},null,null,{"$f":"IF(N64=\"\",(N63*((N45+N52)/1000)),(N64*(N45/1000)+N63*(N52/1000)))","v":0},null,null,{"$f":"IF(Q64=\"\",(Q63*((Q45+Q52)/1000)),(Q64*(Q45/1000)+Q63*(Q52/1000)))","v":0}]
[" 12. Annual Cost of Real Losses",null,null,null,{"$f":"(E62*(E57/1000))","v":0},null,null,{"$f":"(H62*(H57/1000))","v":0},null,null,{"$f":"(K62*(K57/1000))","v":0},null,null,{"$f":"(N62*(N57/1000))","v":0},null,null,{"$f":"(Q62*(Q57/1000))","v":0}]
[" 13. Cost to Produce Water* (see tab: Cost to Produce Calculator)",null,null,null,{"$f":"'Cost to Produce Calculator'!D17","v":0},null,null,{"$f":"'Cost to Produce Calculator'!H17","v":0},null,null,{"$f":"'Cost to Produce Calculator'!L17","v":0},null,null,{"$f":"'Cost to Produce Calculator'!P17","v":0},null,null,{"$f":"'Cost to Produce Calculator'!T17","v":0}]
[" 14. Retail Charge for Water* (see tab: Retail Charge Calculator)",null,null,null,{"$f":"'Retail Charge Calculator'!E11","v":0},null,null,{"$f":"'Retail Charge Calculator'!J11","v":0},null,null,{"$f":"'Retail Charge Calculator'!O11","v":0},null,null,{"$f":"'Retail Charge Calculator'!T11","v":0},null,null,{"$f":"'Retail Charge Calculator'!Y11","v":0}]
[" 15. Composite rate for water not sold ($/1,000 Gal.)"]
["*Value per 1,000 Gal."]

{"sheet":"Rates Check List (Tutorial)","index":9,"n_rows":13,"n_cols":5,"rows_kept":13,"cut":false,"hidden":true,"merged":[[10,0,10,1],[6,0,6,1],[7,0,7,1],[8,0,8,1],[9,0,9,1],[0,0,0,4],[3,2,3,3],[3,0,3,1],[4,0,4,1],[5,0,5,1]]}
["WATER RATES DATA COLLECTION WORKSHEET (TUTORIAL)"]
[]
[null,null,"Data Year:",2021]
["RETAIL CHARGE FOR WATER",null,"Check Appropriate Column",null,"Enter Rates In This Column"]
["Have I Collected Data on Water Rates for:",null,"Yes","N/A","$/1,000 Gal."]
["Residential Customers",null,"a",null,2.75]
["Commercial Customers",null,"a",null,3.75]
["Industrial Customers",null,"a",null,4.75]
["Institutional Customers",null,"a",null,3.75]
["Other Water Systems",null,"a",null,3.75]
["Other - describe as needed",null,null,null,0]
["USE THE AVERAGE RETAIL CHARGE FOR EACH CUSTOMER TYPE."]
["IF YOU HAVE AN INCREASING BLOCK STRUCTURE, USE THE RATE THAT CAPTURES THE MAJORITY OF CUSTOMERS."]

{"sheet":"Meter Error Calc (Tutorial)","index":10,"n_rows":177,"n_cols":8,"rows_kept":177,"cut":false,"hidden":true,"merged":[[175,0,175,1],[176,0,176,1],[144,0,144,7],[158,5,158,7],[0,0,0,5],[40,0,40,6],[75,0,75,7],[89,5,89,7],[109,0,109,7],[123,5,123,7],[67,0,67,1],[68,0,68,1],[106,0,106,1],[107,0,107,1],[140,0,140,1],[141,0,141,1]]}
["METER ERROR CALCULATION WORKSHEET (TUTORIAL)"]
[]
["Data Year:",2021]
[]
["Month","Residential, Gal","Commercial, Gal","Industrial, Gal","Institutional, Gal","Total, Gal"]
["January",11000000,1200000,182500,669000,13051500]
["February",11000000,1200000,182500,669000,13051500]
["March",11000000,1200000,182500,669000,13051500]
["April",12000000,1200000,182500,669000,14051500]
["May",13000000,1350000,182500,669000,15201500]
["June",14000000,1350000,182500,669000,16201500]
["July",17000000,1350000,182500,669000,19201500]
["August",15475000,1750000,182500,669000,18076500]
["September",15000000,1495000,182500,669000,17346500]
["October",11000000,1200000,182500,669000,13051500]
["November",11000000,1200000,182500,669000,13051500]
["December",10000000,1200000,182500,671000,12053500]
["Annual Total, Gal",151475000,15695000,2190000,8030000,177390000]
["Daily Average, MGD",0.42,0.04,0.01,0.02,0.48599999999999999]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["RESIDENTIAL METER ERROR CALCULATION"]
[]
["Flow range volume percentages for residential meters.",null,null,null,"Data Year:",2021]
[]
["Range, gpm","% Volume"]
["Low ( 0.5 - 1.0 )",0.8]
["Medium ( 1 - 10 )",0.15]
["High ( 10 - 15 )",0.05]
["Total, must equal 100%",1]
["Range:  Identify low, medium, and high flow ranges for recorded flow over a minimum 24-hr period."]
["% Volume:  Ratio of volume for a flow range to total volume for the recording period (must total 100%)."]
[]
["Meter testing data for a random sample of residential meters."]
[]
["Test flow rates, gpm","Mean Registration, %"]
["Low (1.0)",0.97499999999999998]
["Medium (5)",0.98]
["High (13)",0.97499999999999998]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of residential meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[0.8,151.47999999999999,121.18,0.98,3.11]
[0.15,151.47999999999999,22.72,0.98,0.46]
[0.05,151.47999999999999,7.57,0.98,0.19]
["Total residential meter error (MG)",null,null,null,3.77]
["Residential meter error (%)",null,null,null,2.4899999999999999E-2,"Transfer Meter Error to Audit."]
[]
[]
[]
[]
[]
[]
["COMMERCIAL METER ERROR CALCULATION"]
[]
["Flow range volume percentages for commercial meters.",null,null,null,null,"Data Year:",2021]
[]
["Range, gpm","% Volume"]
["Low ( 1 - 5 )",0.3]
["Medium ( 5 - 10 )",0.6]
["High ( 10 - 20 )",0.1]
["Total, must equal 100%",1]
["Range:  Identify low, medium, and high flow ranges for recorded flow over a minimum 24-hr period."]
["% Volume:  Ratio of volume for a flow range to total volume for the recording period (must total 100%)."]
[]
["Meter testing data for commercial meters."]
[]
["Meter ID Number","Meter Type &  Size, in.","Date of Installation","Manufacturer","Test Date","Test flow rates, gpm"]
[null,null,null,null,null,"Low (5)","Medium (10)","High (20)"]
[1,2,null,null,null,0.95,0.95,0.95]
[2,2,null,null,null,0.97,0.97,0.97]
[3,2,null,null,null,0.97499999999999998,0.98,0.98]
[]
[]
["Mean registration %",null,null,null,null,0.96499999999999997,0.9667,0.9667]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of commercial meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[0.3,15.7,4.71,0.97,0.17]
[0.6,15.7,9.42,0.97,0.32]
[0.1,15.7,1.57,0.97,0.05]
["Total commercial meter error (MG)",null,null,null,0.55000000000000004]
["Commercial meter error (%)",null,null,null,3.5000000000000003E-2,"Transfer Meter Error to Audit."]
[]
["INDUSTRIAL METER ERROR CALCULATION"]
[]
["Flow range percentages for industrial meters.",null,null,null,null,"Data Year:",2021]
[]
["Range, gpm","% Volume"]
["Low ( 5 - 10 )",0.3]
["Medium ( 10 - 30 )",0.35]
["High ( 30 - 50 )",0.35]
["Total, must equal 100%",1]
["Range:  Identify low, medium, and high flow ranges for recorded flow over a minimum 24-hr period."]
["% Volume:  Ratio of volume for a flow range to total volume for the recording period (must total 100%)."]
[]
["Meter testing data for industrial meters."]
[]
["Meter ID Number","Meter Type &  Size, in.","Date of Installation","Manufacturer","Test Date","Test flow rates, gpm"]
[null,null,null,null,null,"Low (10 )","Medium (25)","High (40)"]
[1,4,null,null,null,0.95,0.95499999999999996,0.95299999999999996]
[2,4,null,null,null,0.95,0.95499999999999996,0.95]
[]
[]
[]
["Mean registration %",null,null,null,null,0.95,0.95499999999999996,0.95150000000000001]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of industrial meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[0.3,2.19,0.66,0.95,0.03]
[0.35,2.19,0.77,0.96,0.04]
[0.35,2.19,0.77,0.95,0.04]
["Total industrial meter error (MG)",null,null,null,0.11]
["Industrial meter error (%)",null,null,null,5.0099999999999999E-2,"Transfer Meter Error to Audit."]
[]
[]
["INSTITUTIONAL METER ERROR CALCULATION"]
[]
["Flow range percentages for institutional meters.",null,null,null,null,"Data Year:",2021]
[]
["Range, gpm","% Volume"]
["Low ( 1 - 5 )",0.3]
["Medium ( 5 - 10 )",0.35]
["High ( 10 - 20 )",0.35]
["Total, must equal 100%",1]
["Range:  Identify low, medium, and high flow ranges for recorded flow over a minimum 24-hr period."]
["% Volume:  Ratio of volume for a flow range to total volume for the recording period (must total 100%)."]
[]
["Meter testing data for institutional meters."]
[]
["Meter ID Number","Meter Type &  Size, in.","Date of Installation","Manufacturer","Test Date","Test flow rates, gpm"]
[null,null,null,null,null,"Low (5)","Medium (10)","High (20)"]
[1,3,null,null,null,0.96,0.96599999999999997,0.97]
[2,3,null,null,null,0.96,0.97,0.97]
[]
[]
[]
["Mean registration %",null,null,null,null,0.96,0.96799999999999997,0.97]
["Test flow rates:  Choose a flow rate from each range identified above at which the meters will be tested."]
["Mean registration:  Average registration accuracy of meters tested at each flow rate."]
[]
["Calculation of institutional meter error."]
[]
["Volume","Total Annual Sales Volume (MG)","Adjusted Sales Volume (MG)","Meter Registration","Meter Error (MG)"]
[0.3,8.0299999999999994,2.41,0.96,0.1]
[0.35,8.0299999999999994,2.81,0.97,0.09]
[0.35,8.0299999999999994,2.81,0.97,0.09]
["Total institutional meter error (MG)",null,null,null,0.28000000000000003]
["Institutional meter error (%)",null,null,null,3.49E-2,"Transfer Meter Error to Audit."]

{"sheet":"Water Mgmt Table (Tutorial)","index":11,"n_rows":65,"n_cols":17,"rows_kept":65,"cut":false,"hidden":true,"merged":[[5,14,5,16],[5,0,5,1],[5,2,5,4],[5,5,5,7],[5,8,5,10],[5,11,5,13],[6,14,6,16],[7,2,7,4],[7,5,7,7],[7,8,7,10],[7,11,7,13],[7,14,7,16],[69,0,69,1],[6,2,6,4],[6,5,6,7],[6,8,6,10],[6,11,6,13],[61,0,61,1],[62,0,62,1],[13,0,13,1]]}
["WATER SYSTEM AUDIT WORKSHEET (TUTORIAL)"]
["(Units should be reported in gallons per year, gpy)"]
[]
["Enter values in non-shaded cells only."]
[]
["Cells with red triangles      contain information. Roll your pointer over the triangle to reveal the information."]
[null,null,2021,null,null,2022,null,null,2023,null,null,2024,null,null,2025]
["WATER DELIVERED (Gal/Yr)"]
[" 1. Total Source Water Supply (Raw Water)",null,"Metered","Unmetered",77130000,"Metered","Unmetered",0,"Metered","Unmetered",0,"Metered","Unmetered",0,"Metered","Unmetered",0]
["Ground Water Sources",null,"M",null,22630000]
["Surface Water Sources",null,"M",null,54500000]
[" 2. Adjustments to Water Delivery",null,null,null,5000000,null,null,0,null,null,0,null,null,0,null,null,0]
["Backwash water",null,"M",null,5000000]
["Other (e.g. tank maintenance, meter calibration, wash water, etc.)"]
["3.  Water Produced (as read from finished water meter)"]
["4. Purchase Water Sources",null,"M",null,158045000]
["Water Delivered to Distribution System  ",null,null,null,230175000,null,null,0,null,null,0,null,null,0,null,null,0]
["WATER USED (Gal/Yr)"]
[" 5. Water Sold",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered"]
["Residential",null,"M",null,151475000]
["Commercial",null,"M",null,15695000]
["Industrial",null,"M",null,2190000]
["Institutional",null,"M",null,8030000]
["Other Water Systems (Utilities)",null,"M",null,7300000]
["Other"]
["Total Water Sold ",null,null,null,184690000,null,null,0,null,null,0,null,null,0,null,null,0]
[" 6. Water Not Sold (metered and unmetered)",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered",null,"Metered","Unmetered"]
["Residential"]
["Commercial"]
["Industrial"]
["Institutional",null,"M",null,1000000]
["Water Main Flushing"," [Institutional]",null,null,10000000]
["Sewer/Storm Drain Flushing"," [Institutional]"]
["Parks/Playgrounds/Swimming Pools"," [Institutional]"]
["Road Medians"," [Institutional]"]
["Schools"," [Institutional]"]
["Training/Fire Fighting"," [Institutional]"]
["Storage Tank Drainage (within distribution system)"," [Institutional]"]
["Sewer Plant Uses"," [Institutional]"]
["Municipal Buildings"," [Institutional]"]
["Golf Courses","[Commercial]"]
["Cemeteries","[Commercial]"]
["Construction Use","[Commercial]"]
["Other"]
["Total Water Not Sold",null,null,null,11000000,null,null,0,null,null,0,null,null,0,null,null,0]
["WATER LOSSES (Gal/Yr)"]
[" 7. Apparent Water Losses"]
["Water Meter Error (see tab: Meter Error Table)",null,null,null,4958565.2860351121,null,null,0,null,null,0,null,null,0,null,null,0]
["Data Handling Error",null,null,null,200000]
["Theft",null,null,null,575438]
["Other"]
["Total Apparent Losses",null,null,null,5734003.2860351121,null,null,0,null,null,0,null,null,0,null,null,0]
[" 8. Real Water Losses"]
["Distribution System Leaks",null,null,null,28750996.713964887,null,null,0,null,null,0,null,null,0,null,null,0]
["Storage Overflow"]
["Other (e.g. service line to meter leaks)"]
["Total Real Losses",null,null,null,28750996.713964887,null,null,0,null,null,0,null,null,0,null,null,0]
[" 9. Total  Non-Revenue Water",null,null,null,45485000,null,null,0,null,null,0,null,null,0,null,null,0]
[" 10. Percentage  Water Loss",null,null,null,0.14982078853046596,null,null,0,null,null,0,null,null,0,null,null,0]
[" 11. Annual Cost of Apparent Losses",null,null,null,49226.410852298934,null,null,0,null,null,0,null,null,0,null,null,0]
[" 12. Annual Cost of Real Losses",null,null,null,44803.964432824992,null,null,0,null,null,0,null,null,0,null,null,0]
[" 13. Cost to Produce Water* (see tab: Cost to Produce Calculator)",null,null,null,1.5583447376995763,null,null,0,null,null,0,null,null,0,null,null,0]
[" 14. Retail Charge for Water* (see tab: Retail Charge Calculator)",null,null,null,2.9416996047430826,null,null,0,null,null,0,null,null,0,null,null,0]
[" 15. Composite rate for water not sold ($/1,000 Gal.)"]
["*Value per 1,000 Gal."]

{"sheet":"Meter Error Table","index":12,"n_rows":9,"n_cols":10,"rows_kept":9,"cut":false,"hidden":false,"merged":[[1,2,1,3],[1,4,1,5],[1,6,1,7],[1,8,1,9],[1,0,1,1]]}
[]
[2021,null,2022,null,2023,null,2024,null,2025]
["Meter Type","% Meter Error","Meter Type","% Meter Error","Meter Type","% Meter Error","Meter Type","% Meter Error","Meter Type","% Meter Error"]
["Residential",null,"Residential",null,"Residential",null,"Residential",null,"Residential"]
["Commercial",null,"Commercial",null,"Commercial",null,"Commercial",null,"Commercial"]
["Industrial",null,"Industrial",null,"Industrial",null,"Industrial",null,"Industrial"]
["Institutional",null,"Institutional",null,"Institutional",null,"Institutional",null,"Institutional"]
["Other Water Utilities",null,"Other Water Utilities",null,"Other Water Utilities",null,"Other Water Utilities",null,"Other Water Utilities"]
["Other",null,"Other",null,"Other",null,"Other",null,"Other"]

{"sheet":"Meter Error Table (Tutorial)","index":13,"n_rows":13,"n_cols":10,"rows_kept":13,"cut":false,"hidden":true,"merged":[[5,0,5,1],[5,2,5,3],[5,4,5,5],[5,6,5,7],[5,8,5,9]]}
[]
[]
[]
[]
["(TUTORIAL)"]
[2021,null,2022,null,2023,null,2024,null,2025]
["Meter Type","% Meter Error","Meter Type","% Meter Error","Meter Type","% Meter Error","Meter Type","% Meter Error","Meter Type","% Meter Error"]
["Residential",2.4856096284667756E-2,"Residential",null,"Residential",null,"Residential",null,"Residential"]
["Commercial",3.5018760050026874E-2,"Commercial",null,"Commercial",null,"Commercial",null,"Commercial"]
["Industrial",5.0121872514385025E-2,"Industrial",null,"Industrial",null,"Industrial",null,"Industrial"]
["Institutional",3.4894990201925588E-2,"Institutional",null,"Institutional",null,"Institutional",null,"Institutional"]
["Other Water Utilities",0.03,"Other Water Utilities",null,"Other Water Utilities",null,"Other Water Utilities",null,"Other Water Utilities"]
["Other",null,"Other",null,"Other",null,"Other",null,"Other"]

{"sheet":"Cost to Produce Calculator","index":14,"n_rows":17,"n_cols":20,"rows_kept":17,"cut":false,"hidden":false,"merged":[[1,5,1,7],[2,4,2,7],[2,0,2,3],[1,1,1,3],[1,17,1,19],[2,16,2,19],[1,9,1,11],[2,8,2,11],[1,13,1,15],[2,12,2,15]]}
[]
[null,2021,null,null,null,2022,null,null,null,2023,null,null,null,2024,null,null,null,2025]
["ANNUAL COST TO PRODUCE WATER",null,null,null,"ANNUAL COST TO PRODUCE WATER",null,null,null,"ANNUAL COST TO PRODUCE WATER",null,null,null,"ANNUAL COST TO PRODUCE WATER",null,null,null,"ANNUAL COST TO PRODUCE WATER"]
["Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal."]
[{"$f":"'Costs Check List'!A5","v":"Electricity Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C5/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A5","v":"Electricity Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G5/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A5","v":"Electricity Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K5/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A5","v":"Electricity Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O5/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A5","v":"Electricity Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S5/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A6","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C6/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A6","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G6/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A6","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K6/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A6","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O6/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A6","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S6/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A7","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C7/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A7","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G7/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A7","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K7/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A7","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O7/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A7","v":"Chemical Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S7/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A8","v":"Chemical Analysis Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C8/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A8","v":"Chemical Analysis Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G8/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A8","v":"Chemical Analysis Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K8/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A8","v":"Chemical Analysis Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O8/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A8","v":"Chemical Analysis Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S8/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A9","v":"Purchased Water Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C9/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A9","v":"Purchased Water Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G9/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A9","v":"Purchased Water Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K9/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A9","v":"Purchased Water Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O9/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A9","v":"Purchased Water Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S9/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A10","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C10/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A10","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G10/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A10","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K10/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A10","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O10/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A10","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S10/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A11","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C11/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A11","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G11/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A11","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K11/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A11","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O11/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A11","v":"Maintenance Cost"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S11/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A12","v":"Other Cost (e.g. natural gas)"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C12/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A12","v":"Other Cost (e.g. natural gas)"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G12/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A12","v":"Other Cost (e.g. natural gas)"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K12/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A12","v":"Other Cost (e.g. natural gas)"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O12/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A12","v":"Other Cost (e.g. natural gas)"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S12/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A13","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C13/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A13","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G13/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A13","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K13/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A13","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O13/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A13","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S13/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A14","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C14/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A14","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G14/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A14","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K14/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A14","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O14/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A14","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S14/(('Water Management Table'!Q$17)/1000),0)","v":0}]
[{"$f":"'Costs Check List'!A15","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!E$17>0,C15/(('Water Management Table'!E$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A15","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!H$17>0,G15/(('Water Management Table'!H$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A15","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!K$17>0,K15/(('Water Management Table'!K$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A15","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!N$17>0,O15/(('Water Management Table'!N$17)/1000),0)","v":0},{"$f":"'Costs Check List'!A15","v":"Other Cost ?"},null,0,{"$f":"IF('Water Management Table'!Q$17>0,S15/(('Water Management Table'!Q$17)/1000),0)","v":0}]
["Other Cost",null,0,{"$f":"IF('Water Management Table'!E$17>0,C16/(('Water Management Table'!E$17)/1000),0)","v":0},"Other Cost",null,0,{"$f":"IF('Water Management Table'!H$17>0,G16/(('Water Management Table'!H$17)/1000),0)","v":0},"Other Cost",null,0,{"$f":"IF('Water Management Table'!K$17>0,K16/(('Water Management Table'!K$17)/1000),0)","v":0},"Other Cost",null,0,{"$f":"IF('Water Management Table'!N$17>0,O16/(('Water Management Table'!N$17)/1000),0)","v":0},"Other Cost",null,0,{"$f":"IF('Water Management Table'!Q$17>0,S16/(('Water Management Table'!Q$17)/1000),0)","v":0}]
["Totals",null,{"$f":"SUM(C5:C16)","v":0},{"$f":"SUM(D5:D16)","v":0},"Totals",null,{"$f":"SUM(G5:G16)","v":0},{"$f":"SUM(H5:H16)","v":0},"Totals",null,{"$f":"SUM(K5:K16)","v":0},{"$f":"SUM(L5:L16)","v":0},"Totals",null,{"$f":"SUM(O5:O16)","v":0},{"$f":"SUM(P5:P16)","v":0},"Totals",null,{"$f":"SUM(S5:S16)","v":0},{"$f":"SUM(T5:T16)","v":0}]

{"sheet":"Cost to Produce Calc (Tutorial)","index":15,"n_rows":22,"n_cols":20,"rows_kept":22,"cut":false,"hidden":true,"merged":[[7,0,7,3],[7,4,7,7],[7,8,7,11],[7,12,7,15],[7,16,7,19],[6,1,6,3],[6,5,6,7],[6,9,6,11],[6,13,6,15],[6,17,6,19]]}
[]
[]
[]
[]
["(TUTORIAL)","Enter values in non-shaded cells only.",null,null,null,"Enter values in non-shaded cells only.",null,null,null,"Enter values in non-shaded cells only.",null,null,null,"Enter values in non-shaded cells only.",null,null,null,"Enter values in non-shaded cells only."]
[]
[null,2021,null,null,null,2022,null,null,null,2023,null,null,null,2024,null,null,null,2025]
["COST TO PRODUCE WATER (enter annual cost only)",null,null,null,"COST TO PRODUCE WATER (enter annual cost only)",null,null,null,"COST TO PRODUCE WATER (enter annual cost only)",null,null,null,"COST TO PRODUCE WATER (enter annual cost only)",null,null,null,"COST TO PRODUCE WATER (enter annual cost only)"]
["Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal.","Item:","Descriptor","Cost","Cost/1000 Gal."]
["Electricity Cost",null,155200,0.67426957749538396,"Electricity Cost",null,0,0,"Electricity Cost",null,0,0,"Electricity Cost",null,0,0,"Electricity Cost",null,0,0]
["Chemical Cost",null,40000,0.17378081894210926,"Chemical Cost",null,0,0,"Chemical Cost",null,0,0,"Chemical Cost",null,0,0,"Chemical Cost",null,0,0]
["Chemical Cost",null,0,0,"Chemical Cost",null,0,0,"Chemical Cost",null,0,0,"Chemical Cost",null,0,0,"Chemical Cost",null,0,0]
["Chemical Analysis Cost",null,0,0,"Purchased Water Cost",null,0,0,"Purchased Water Cost",null,0,0,"Purchased Water Cost",null,0,0,"Purchased Water Cost",null,0,0]
["Purchased Water Cost",null,63492,0.27584229390681003,"Purchased Water Cost",null,0,0,"Purchased Water Cost",null,0,0,"Purchased Water Cost",null,0,0,"Purchased Water Cost",null,0,0]
["Maintenance Cost",null,100000,0.43445204735527315,"Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0]
["Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0,"Maintenance Cost",null,0,0]
["Other Utilities Cost",null,0,0,"Other Utilities Cost",null,0,0,"Other Utilities Cost",null,0,0,"Other Utilities Cost",null,0,0,"Other Utilities Cost",null,0,0]
["Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0]
["Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0]
["Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0]
["Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0,"Other Cost",null,0,0]
["Totals",null,358692,1.5583447376995763,"Totals",null,0,0,"Totals",null,0,0,"Totals",null,0,0,"Totals",null,0,0]

{"sheet":"Retail Charge Calculator","index":16,"n_rows":11,"n_cols":25,"rows_kept":11,"cut":false,"hidden":false,"merged":[[10,20,10,21],[10,10,10,11],[2,10,2,14],[3,15,3,16],[1,2,1,4],[1,7,1,9],[10,15,10,16],[8,5,8,6],[9,5,9,6],[4,10,4,11],[5,10,5,11],[6,10,6,11],[7,10,7,11],[8,10,8,11],[9,10,9,11],[4,15,4,16],[3,0,3,1],[10,5,10,6],[2,5,2,9],[10,0,10,1],[2,0,2,4],[3,5,3,6],[4,0,4,1],[5,0,5,1],[6,0,6,1],[7,0,7,1],[8,0,8,1],[9,0,9,1],[4,5,4,6],[5,5,5,6],[6,5,6,6],[7,5,7,6],[1,22,1,24],[2,20,2,24],[3,20,3,21],[3,10,3,11],[1,17,1,19],[1,12,1,14],[2,15,2,19],[5,15,5,16],[6,15,6,16],[7,15,7,16],[8,15,8,16],[9,15,9,16],[9,20,9,21],[4,20,4,21],[5,20,5,21],[6,20,6,21],[7,20,7,21],[8,20,8,21]]}
[]
[null,null,2021,null,null,null,null,2022,null,null,null,null,2023,null,null,null,null,2024,null,null,null,null,2025]
["RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER"]
["Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal."]
["Residential",null,{"$f":"IF('Water Management Table'!E20>0,'Water Management Table'!E20/'Water Management Table'!$E$26,0)","v":0},0,{"$f":"C5*D5","v":0},"Residential",null,{"$f":"IF('Water Management Table'!H20>0,'Water Management Table'!H20/'Water Management Table'!$H$26,0)","v":0},0,{"$f":"H5*I5","v":0},"Residential",null,{"$f":"IF('Water Management Table'!K20>0,'Water Management Table'!K20/'Water Management Table'!$K$26,0)","v":0},0,{"$f":"M5*N5","v":0},"Residential",null,{"$f":"IF('Water Management Table'!N20>0,'Water Management Table'!N20/'Water Management Table'!$N$26,0)","v":0},0,{"$f":"R5*S5","v":0},"Residential",null,{"$f":"IF('Water Management Table'!Q20>0,'Water Management Table'!Q20/'Water Management Table'!$Q$26,0)","v":0},0,{"$f":"W5*X5","v":0}]
["Commercial",null,{"$f":"IF('Water Management Table'!E21>0,'Water Management Table'!E21/'Water Management Table'!$E$26,0)","v":0},0,0,"Commercial",null,{"$f":"IF('Water Management Table'!H21>0,'Water Management Table'!H21/'Water Management Table'!$H$26,0)","v":0},0,0,"Commercial",null,{"$f":"IF('Water Management Table'!K21>0,'Water Management Table'!K21/'Water Management Table'!$K$26,0)","v":0},0,0,"Commercial",null,{"$f":"IF('Water Management Table'!N21>0,'Water Management Table'!N21/'Water Management Table'!$N$26,0)","v":0},0,0,"Commercial",null,{"$f":"IF('Water Management Table'!Q21>0,'Water Management Table'!Q21/'Water Management Table'!$Q$26,0)","v":0},0,0]
["Industrial",null,{"$f":"IF('Water Management Table'!E22>0,'Water Management Table'!E22/'Water Management Table'!$E$26,0)","v":0},0,0,"Industrial",null,{"$f":"IF('Water Management Table'!H22>0,'Water Management Table'!H22/'Water Management Table'!$H$26,0)","v":0},0,0,"Industrial",null,{"$f":"IF('Water Management Table'!K22>0,'Water Management Table'!K22/'Water Management Table'!$K$26,0)","v":0},0,0,"Industrial",null,{"$f":"IF('Water Management Table'!N22>0,'Water Management Table'!N22/'Water Management Table'!$N$26,0)","v":0},0,0,"Industrial",null,{"$f":"IF('Water Management Table'!Q22>0,'Water Management Table'!Q22/'Water Management Table'!$Q$26,0)","v":0},0,0]
["Institutional",null,{"$f":"IF('Water Management Table'!E23>0,'Water Management Table'!E23/'Water Management Table'!$E$26,0)","v":0},0,0,"Institutional",null,{"$f":"IF('Water Management Table'!H23>0,'Water Management Table'!H23/'Water Management Table'!$H$26,0)","v":0},0,0,"Institutional",null,{"$f":"IF('Water Management Table'!K23>0,'Water Management Table'!K23/'Water Management Table'!$K$26,0)","v":0},0,0,"Institutional",null,{"$f":"IF('Water Management Table'!N23>0,'Water Management Table'!N23/'Water Management Table'!$N$26,0)","v":0},0,0,"Institutional",null,{"$f":"IF('Water Management Table'!Q23>0,'Water Management Table'!Q23/'Water Management Table'!$Q$26,0)","v":0},0,0]
["Other Water Systems",null,{"$f":"IF('Water Management Table'!E24>0,'Water Management Table'!E24/'Water Management Table'!$E$26,0)","v":0},0,0,"Other Water Systems",null,{"$f":"IF('Water Management Table'!H24>0,'Water Management Table'!H24/'Water Management Table'!$H$26,0)","v":0},0,0,"Other Water Systems",null,{"$f":"IF('Water Management Table'!K24>0,'Water Management Table'!K24/'Water Management Table'!$K$26,0)","v":0},0,0,"Other Water Systems",null,{"$f":"IF('Water Management Table'!N24>0,'Water Management Table'!N24/'Water Management Table'!$N$26,0)","v":0},0,0,"Other Water Systems",null,{"$f":"IF('Water Management Table'!Q24>0,'Water Management Table'!Q24/'Water Management Table'!$Q$26,0)","v":0},0,0]
[{"$f":"'Rates Check List'!A10","v":"Other"},null,{"$f":"IF('Water Management Table'!E25>0,'Water Management Table'!E25/'Water Management Table'!$E$26,0)","v":0},0,0,{"$f":"'Rates Check List'!A10","v":"Other"},null,{"$f":"IF('Water Management Table'!H25>0,'Water Management Table'!H25/'Water Management Table'!$H$26,0)","v":0},0,0,{"$f":"'Rates Check List'!A10","v":"Other"},null,{"$f":"IF('Water Management Table'!K25>0,'Water Management Table'!K25/'Water Management Table'!$K$26,0)","v":0},0,0,{"$f":"'Rates Check List'!A10","v":"Other"},null,{"$f":"IF('Water Management Table'!N25>0,'Water Management Table'!N25/'Water Management Table'!$N$26,0)","v":0},0,0,{"$f":"'Rates Check List'!A10","v":"Other"},null,{"$f":"IF('Water Management Table'!Q25>0,'Water Management Table'!Q25/'Water Management Table'!$Q$26,0)","v":0},0,0]
["Totals",null,{"$f":"SUM(C5:C10)","v":0},null,{"$f":"SUM(E5:E10)","v":0},"Totals",null,{"$f":"SUM(H5:H10)","v":0},null,{"$f":"SUM(J5:J10)","v":0},"Totals",null,{"$f":"SUM(M5:M10)","v":0},null,{"$f":"SUM(O5:O10)","v":0},"Totals",null,{"$f":"SUM(R5:R10)","v":0},null,{"$f":"SUM(T5:T10)","v":0},"Totals",null,{"$f":"SUM(W5:W10)","v":0},null,{"$f":"SUM(Y5:Y10)","v":0}]

{"sheet":"Retail Charge Calc (Tutorial)","index":17,"n_rows":16,"n_cols":25,"rows_kept":16,"cut":false,"hidden":true,"merged":[[14,20,14,21],[9,20,9,21],[10,20,10,21],[11,20,11,21],[12,20,12,21],[13,20,13,21],[14,10,14,11],[9,15,9,16],[10,15,10,16],[11,15,11,16],[12,15,12,16],[13,15,13,16],[14,15,14,16],[9,10,9,11],[10,10,10,11],[11,10,11,11],[12,10,12,11],[13,10,13,11],[14,0,14,1],[9,5,9,6],[10,5,10,6],[11,5,11,6],[12,5,12,6],[13,5,13,6],[14,5,14,6],[9,0,9,1],[10,0,10,1],[11,0,11,1],[12,0,12,1],[13,0,13,1],[7,0,7,4],[7,5,7,9],[7,10,7,14],[7,15,7,19],[7,20,7,24],[6,2,6,4],[6,7,6,9],[6,12,6,14],[6,17,6,19],[6,22,6,24],[15,0,15,1],[15,5,15,6],[15,10,15,11],[15,15,15,16],[15,20,15,21],[8,0,8,1],[8,5,8,6],[8,10,8,11],[8,15,8,16],[8,20,8,21]]}
[]
[]
[]
[]
["(TUTORIAL)",null,"Enter values in non-shaded cells only.",null,null,null,null,"Enter values in non-shaded cells only.",null,null,null,null,"Enter values in non-shaded cells only.",null,null,null,null,"Enter values in non-shaded cells only.",null,null,null,null,"Enter values in non-shaded cells only."]
[]
[null,null,2021,null,null,null,null,2022,null,null,null,null,2023,null,null,null,null,2024,null,null,null,null,2025]
["RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER",null,null,null,null,"RETAIL CHARGE FOR WATER"]
["Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal.","Water Sold (Metered)",null,"Category Weight","$/1,000 Gal.","WT$/1000 Gal."]
["Residential",null,0.82,2.75,2.2554347826086958,"Residential",null,0,0,0,"Residential",null,0,0,0,"Residential",null,0,0,0,"Residential",null,0,0,0]
["Commercial",null,0.08,3.75,0.31867588932806323,"Commercial",null,0,0,0,"Commercial",null,0,0,0,"Commercial",null,0,0,0,"Commercial",null,0,0,0]
["Industrial",null,0.01,4.75,5.6324110671936753E-2,"Industrial",null,0,0,0,"Industrial",null,0,0,0,"Industrial",null,0,0,0,"Industrial",null,0,0,0]
["Institutional",null,0.04,3.75,0.16304347826086957,"Institutional",null,0,0,0,"Institutional",null,0,0,0,"Institutional",null,0,0,0,"Institutional",null,0,0,0]
["Other Water Systems",null,0.04,3.75,0.14822134387351779,"Other Water Systems",null,0,0,0,"Other Water Systems",null,0,0,0,"Other Water Systems",null,0,0,0,"Other Water Systems",null,0,0,0]
["Other",null,0,0,0,"Other",null,0,0,0,"Other",null,0,0,0,"Other",null,0,0,0,"Other",null,0,0,0]
["Totals",null,1,null,2.9416996047430826,"Totals",null,0,null,0,"Totals",null,0,null,0,"Totals",null,0,null,0,"Totals",null,0,null,0]

{"sheet":"Notes","index":19,"n_rows":3,"n_cols":5,"rows_kept":3,"cut":false,"hidden":false,"merged":[]}
["This sheet can be used to take notes during the auditing process."]
[]
[2021,2022,2023,2024,2025]

{"sheet":"Notes (Tutorial)","index":21,"n_rows":3,"n_cols":5,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
["This sheet can be used to take notes during the auditing process."]
[]
[2021,2022,2023,2024,2025]

{"sheet":"Chart Data","index":22,"n_rows":4,"n_cols":8,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
[null,null,null,2021,2022,2023,2024,2025]
["Total  Non-Revenue Water (Gal)",null,null,{"$f":"'Water Management Table'!E58","v":0},{"$f":"'Water Management Table'!H58","v":0},{"$f":"'Water Management Table'!K58","v":0},{"$f":"'Water Management Table'!N58","v":0},{"$f":"'Water Management Table'!Q58","v":0}]
["Annual Cost of Apparent Losses",null,null,{"$f":"'Water Management Table'!E60","v":0},{"$f":"'Water Management Table'!H60","v":0},{"$f":"'Water Management Table'!K60","v":0},{"$f":"'Water Management Table'!N60","v":0},{"$f":"'Water Management Table'!Q60","v":0}]
["Annual Cost of Real Losses",null,null,{"$f":"'Water Management Table'!E61","v":0},{"$f":"'Water Management Table'!H61","v":0},{"$f":"'Water Management Table'!K61","v":0},{"$f":"'Water Management Table'!N61","v":0},{"$f":"'Water Management Table'!Q61","v":0}]

{"sheet":"Chart Data (Tutorial)","index":23,"n_rows":4,"n_cols":8,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
[null,null,null,2016,2017,2018,2019,2020]
["Total  Non-Revenue Water (Gal)",null,null,45485000,0,0,0,0]
["Annual Cost of Apparent Losses",null,null,49226.410852298934,0,0,0,0]
["Annual Cost of Real Losses",null,null,44803.964432824992,0,0,0,0]