{"text_format":"1","workbook":"xlsx","n_sheets":18,"sheets":["Title Page","Index","INPUT","OUTPUT","Critical Design Assumptions","O&M Assumptions","Indirect Assumptions","Well Design","Pumps Pipe Structure","Instrumentation and Control","O&M","Indirect","Standard Inputs","Cost Equations","Cost Coefficients","Cost Data","Engineering Data","Lookup Tables"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":386,"comments":10,"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 Page","index":0,"n_rows":15,"n_cols":1,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["U.S. EPA Office of Ground Water and Drinking Water"]
["Work Breakdown Structure (WBS) Model for NON-TREATMENT Treatment"]
["Release Date: March 2023"]
["· The primary purpose of this model is to assist EPA in estimating national compliance costs."]
["· As such, the costs are intended to be representative of the central tendency of typical facilities."]
["· The default settings, however, do not account for all of the site-specific factors that could affect the cost of any given individual facility or project."]
["· Still, other uses of the model might be reasonable if sufficient care is taken by the user to account for site- or project-specific factors."]
["· The model results are point estimates shown to the nearest $1, but this precision is not meant to imply that the results are accurate to $1."]
["· Instead, users should review all documentation, modify inputs/assumptions as appropriate to their specific purpose, and form their own informed opinions about the accuracy and suitability of the results."]
["· Use of this model requires Excel 2007 or later."]
["· The model should be opened as Read-only, so that any changes you make are saved under a new file name."]
["· To function properly, macros (\"active content\") must be enabled. If a security warning appears, click \"options\" and \"enable this content.\""]
["· For further information see the following document distributed with this file:"]
["USEPA. 2023. Work Breakdown Structure-Based Cost Model for NON-TREATMENT Drinking Water Treatment. Office of Water."]
["· To begin using the model, go to the INPUT sheet."]

{"sheet":"Index","index":1,"n_rows":93,"n_cols":2,"rows_kept":93,"cut":false,"hidden":false,"merged":[]}
["INDEX"]
["This page provides an index to all user-adjustable inputs and assumptions.  Click on a variable name to go directly to the adjustable cells."]
[]
["User-adjustable Input or Assumption","Variable Name and Link"]
["Design Flow ","design_flow_I"]
["Average Flow ","average_flow_I"]
["Distance to Water Supply","pipe_length_I"]
["Pressure at Supply Source","buyer_pressure_I"]
["Elevation Difference from Supply Source","elevation_diff_I"]
["Cost of Purchased Water","water_cost_I"]
["Maxiumum Flow per Well","wells_I"]
["Well Depth","well_depth_I"]
["Distance from Well to Distribution System","well_distance_I"]
["Component level","component_level_I"]
["System automation","manual_I"]
["Include buildings?","include_buildings_I"]
["Include land?","include_land_I"]
["Access space for pumps and PRVs","space_pumps"]
["Asphalt thickness","asphalt_thickness"]
["Cost estimating method","curve"]
["Depth of grout seal","seal_depth"]
["Fire Buffer for Buildings","fire_buffer"]
["Flow meters for each well","fm_well"]
["Flow meters for interconnect line","fm_interconnect"]
["Footprint per well","well_footprint"]
["Frost depth","frost"]
["Include asphalt patch","asphalt_patch_include"]
["Include well pump soft start?","soft_start"]
["Interior hight of vault","vault_interior_height"]
["Minimum average flow as percentage of design","min_avg_flow"]
["Minimum number of PRVs","min_num_PRVs"]
["Minimum number of valves  (isolation PRV, street)","min_valves"]
["Minimum pipe size for interconnection","min_pipe_size_interconnect"]
["Minimum pipe size for new wells","min_pipe_size"]
["Minimum trench depth to top of pipe","min_trench_depth"]
["Minimum trench width on each side of pipe","min_trench_width"]
["Minimum vault cover","vault_min_cover"]
["Non fire Buffer for buildings","non_fire_buffer"]
["Number of redundant booster pumps","NRD_booster_pumps"]
["Number of redundant PRVs","NRD_PRVs"]
["Safety factor for pumps","pump_safety_factor"]
["Screen length to casing length ratio","screen_ration"]
["Spacing for valves","valve_spacing"]
["Thickness of gravel pack","gravel_thickness"]
["Thickness of grout seal","grout_thickness"]
["Valves per PRV","valve_per_PRV"]
["Valves per pump (booster and well)","valve_per_pump"]
["Vault concrete thickness","vault_concrete_thickness"]
["water pressure at top of well (for distribution)","supply_head"]
["I/O port table","port_table"]
["City Index for Total Capital Cost","City_Index"]
["City Index for Total O&M Cost","OM_City_Index"]
["Mangerial LOE as a percent of average technical labor ","Manager_percent"]
["Administrative LOE as a percent of average technical labor ","Clerical_percent"]
["Minutes per valve per week","uloe_valve"]
["Minutes per pump per day","uloe_pump"]
["Minutes per 100 sf per day","uloe_fp"]
["For valves","uloe_valve_maint"]
["For pumps","uloe_pump_maint"]
["Pump efficiency for booster pumps","booster_pump_efficiency"]
["Cost for parts & maintenance for booster pumps","booster_pump_maintenance"]
["Cost for parts & maintenance for well pumps","well_pump_maintenance"]
["Builder's risk insurance percentage","br_ins_pct"]
["Technology complexity factor","complexity"]
["Electrical percentage","elect_pct"]
["Engineering percentage for large systems","eng_pct_large"]
["Engineering percentage for medium systems","eng_pct_medium"]
["Engineering percentage for small systems","eng_pct_small"]
["Financing percentage","finance_pct"]
["Financing percentage for small systems","finance_pct_small"]
["Instrumentation and control percentage","i_c_pct"]
["Include architectural fee?","include_arch"]
["Include architectural fee for small systems?","include_arch_small"]
["Include performance bond cost?","include_bond"]
["Include construction management cost?","include_cm"]
["Include contingency costs for high cost systems?","include_conting_high"]
["Include contingency costs for low cost systems?","include_conting_low"]
["Include contingency costs for medium cost systems?","include_conting_medium"]
["Include geotechnical costs for high cost systems?","include_geo_high"]
["Include geotechnical costs for low cost systems?","include_geo_low"]
["Include geotechnical costs for medium cost systems?","include_geo_medium"]
["Include sitework costs?","include_sitework"]
["Include standby power costs?","include_standby"]
["Include standby power costs for small systems?","include_standby_small"]
["Installation, Transportatation, and O&P percentage","inst_pct"]
["Legal percentage","legal_pct"]
["Minimum standby power generator size","min_standby"]
["Miscellaneous allowance percentage","misc_pct"]
["Mobilization percentage for large systems","mob_pct_large"]
["Mobilization percentage for medium systems","mob_pct_medium"]
["Mobilization percentage for small systems","mob_pct_small"]
["Design safety factor for standby power","std_safety_factor"]
["Sales tax percentage","tax_pct"]

{"sheet":"INPUT","index":2,"n_rows":53,"n_cols":9,"rows_kept":53,"cut":false,"hidden":false,"merged":[[13,6,14,7]]}
["NON-TREATMENT SYSTEM DESIGN AND COST INPUT"]
[]
[null,null,null,null,"STEP 1"]
[null,null,null,null,"Select Design Type"]
[]
[]
[null,null,null,null,"STEP 2:",null,null,{"$f":"IF(OR('Standard Inputs'!S9,'Standard Inputs'!S14),\"<- Using this design\",IF(OR('Standard Inputs'!T9,'Standard Inputs'!T14,),\"<- You modified this design\",\"\"))","v":"<- Using this design"},"The standard design buttons are for EPA's use in estimating national costs, but other users might find them useful as a starting point. See documentation for more information"]
[null,null,null,null,"Select one of the eight standard designs at right OR select \"CLEAR FOR MANUAL ENTRY\"",null,null,{"$f":"IF(OR('Standard Inputs'!S10,'Standard Inputs'!S15),\"<- Using this design\",IF(OR('Standard Inputs'!T10,'Standard Inputs'!T15,),\"<- You modified this design\",\"\"))","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(OR('Standard Inputs'!S11,'Standard Inputs'!S16),\"<- Using this design\",IF(OR('Standard Inputs'!T11,'Standard Inputs'!T16,),\"<- You modified this design\",\"\"))","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(OR('Standard Inputs'!S12,'Standard Inputs'!S17),\"<- Using this design\",IF(OR('Standard Inputs'!T12,'Standard Inputs'!T17,),\"<- You modified this design\",\"\"))","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(OR('Standard Inputs'!S13,'Standard Inputs'!S18),\"<- Using this design\",IF(OR('Standard Inputs'!T13,'Standard Inputs'!T18,),\"<- You modified this design\",\"\"))","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(OR('Standard Inputs'!S9:T18),\"\",\"Using a manual design\")","v":""}]
[null,null,null,null,"STEP 3:"]
[null,null,null,null,"(Optional for standard designs)",null,{"$f":"IF(C15,IF('Standard Inputs'!S21,\"Input Complete -- Results Ready\",\"Input Complete--Press 'Generate Results'\"),\"Input Incomplete--Check for Error Messages Below\")","v":"Input Complete -- Results Ready"}]
[null,null,{"$f":"AND(C28,C36,C43,C44,C53)","v":true},null,"Enter or change values in the gold and blue cells below, under \"Manual Inputs\""]
[]
[null,null,null,null,"STEP 4:",null,null,null,{"$f":"\"Resulting Costs (in year \"&TEXT(current_year_cl,0)&\" dollars, see OUTPUT sheet for details)\"","v":"Resulting Costs (in year 2021 dollars, see OUTPUT sheet for details)"}]
[null,null,null,null,{"$f":"IF(G14=\"input complete -- results ready\",\"Results are ready (no need to click button)\",\"Click here to generate results -->\")","v":"Results are ready (no need to click button)"},null,null,null,{"$f":"\"Direct Capital Cost: \" & IF(input_status<>\"input complete -- results ready\",input_status,IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Cost buildup error!\",IF(ISERROR(direct_cost),\"Calculation error!\",TEXT(direct_cost,\"$0,0\")))))","v":"Direct Capital Cost: $304,232"}]
[null,null,null,null,null,null,null,null,{"$f":"\"Total Capital Cost: \" & IF(input_status<>\"input complete -- results ready\",input_status,IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Cost buildup error!\",IF(ISERROR(system_cost),\"Calculation error!\",TEXT(system_cost,\"$0,0\")))))","v":"Total Capital Cost: $443,171"}]
[null,null,null,null,null,null,null,null,{"$f":"\"Annualized Capital Cost: \"&IF(input_status<>\"Input Complete -- Results Ready\",input_status,IF(Missing_Cost,\"Missing Price!\",IF(cost_buildup_error,\"Cost buildup error!\",IF(ISERROR(Annualized_Capital_Cost),\"Calculation error!\",TEXT(Annualized_Capital_Cost,\"$0,0\")&\" per year\"&\" (over \"&useful_life&\" years at \"&Discount_rate*100&\"%)\"))))","v":"Annualized Capital Cost: $45,392 per year (over 17 years at 7%)"}]
[null,null,null,null,null,null,null,null,{"$f":"\"Annual O&M Cost: \"&IF(input_status<>\"input complete -- results ready\",input_status,IF(ISERROR(om_cost),\"Calculation error!\",TEXT(om_cost,\"$0,0\")&\" per year\"))","v":"Annual O&M Cost: $8,641 per year"}]
[null,null,null,null,null,null,null,null,{"$f":"\"Total Annualized Cost: \"&IF(input_status<>\"input complete -- results ready\",input_status,IF(Missing_Cost,\"Missing Price!\",IF(cost_buildup_error,\"Cost buildup error!\",IF(ISERROR(Annualized_Cost),\"Calculation error!\",TEXT(Annualized_Cost,\"$0,0\")&\" per year (\"&TEXT(TAC_pct_cap,\"0%\")&\" capital, \"&TEXT(TAC_pct_OM,\"0%\")&\" O&M)\"))))","v":"Total Annualized Cost: $54,032 per year (84% capital, 16% O&M)"}]
[null,null,null,null,"MANUAL INPUTS",null,null,null,{"$f":"IF(Missing_Cost,\"A price is not available for at least one required component. See Use? column on OUTPUT sheet.\",IF(cost_buildup_error,\"There is an Excel error in the price for at least one component. Please see the Use? column on OUTPUT sheet.\",\"\"))","v":""}]
[null,null,null,null,"Cells in gold are required; cells in blue are optional"]
[null,"Used?","Valid input?","Invalid reason?",null,null,null,"Select units"]
[null,null,null,null,"Design Flow ","design_flow_I",0.03,"MGD"]
[null,null,null,null,"Average Flow ","average_flow_I",7.0000000000000001E-3,"MGD"]
[null,null,{"$f":"IF(OR(df_units=\"\",af_units=\"\"),FALSE,IF(average_flow>design_flow,FALSE,IF(OR(design_flow_I=0,average_flow_I=0),FALSE,IF(average_flow<design_flow*min_avg_flow,FALSE,TRUE))))","v":true},{"$f":"IF(OR(df_units=\"\",af_units=\"\"),\"Choose units for design and average flow\",IF(average_flow>design_flow,\"The average flow should not be greater than the design flow\",IF(OR(design_flow_I=0,average_flow_I=0),\"Input incomplete\",IF(average_flow<design_flow*min_avg_flow,\"The average flow must be at least \" & min_avg_flow & \" times design flow\",\"OK\"))))","v":"OK"},null,null,null,{"$f":"IF(C28,\"System size inputs OK\",D28)","v":"System size inputs OK"}]
[]
[null,{"$f":"IF(design=1,TRUE,FALSE)","v":true},null,null,"Interconnection"]
[null,null,{"$f":"IF(B30=TRUE,IF(pipe_length_I=\"\",FALSE,TRUE),TRUE)","v":true},{"$f":"IF(B30=TRUE,IF(pipe_length_I=\"\",\"Interconnection Inputs Incomplete\",\"\"),\"\")","v":""},"Distance to Water Supply","pipe_length_I",10000,"feet"]
[null,null,{"$f":"IF(B31=TRUE,IF(buyer_pressure_I=\"\",FALSE,TRUE),TRUE)","v":true},{"$f":"IF(B31=TRUE,IF(buyer_pressure_I=\"\",\"Interconnection Inputs Incomplete\",\"\"),\"\")","v":""},"Pressure at Supply Source","buyer_pressure_I","correct pressure","<-- select one"]
[null,{"$f":"IF(AND(B30,buyer_pressure_I=\"pressure less than required\"),TRUE,FALSE)","v":false},{"$f":"IF(AND(B30=TRUE,B33=TRUE),IF(elevation_diff_I=\"\",FALSE,IF(booster_psi>500,FALSE,TRUE)),TRUE)","v":true},{"$f":"IF(AND(B30=TRUE,B33=TRUE),IF(elevation_diff_I=\"\",\"Interconnection Inputs Incomplete\",IF(booster_psi>500,\"Elevation difference exceeds maximum available pump rating\",\"\")),\"\")","v":""},"Elevation Difference from Supply Source","elevation_diff_I",0,{"$f":"IF(B33,\"feet\",\"\")","v":""},{"$f":"IF(AND(B30=TRUE,B33=TRUE),\"Guidance: The elevation difference is used to size the booster pump.\",\"\")","v":""}]
[null,null,true,null,"Cost of Purchased Water","water_cost_I",null,"$/1000 gal",{"$f":"IF(B30=TRUE,\"The cost of purchased water appears as an O&M cost item. Leave blank to accept default cost; enter 0 to exclude\",\"\")","v":"The cost of purchased water appears as an O&M cost item. Leave blank to accept default cost; enter 0 to exclude"}]
[null,null,null,null,"For information: Cost of purchased water",null,{"$f":"IF(B30=TRUE,IF(ISERROR(water_cost),\"\",water_cost),\"\")","v":3.0595290784661366}]
[null,null,{"$f":"AND(C31:C34)","v":true},{"$f":"IF(C36,\"OK\",IF(C31=FALSE,D31,IF(C32=FALSE,D32,IF(C33=FALSE,D33,IF(C34=FALSE,D34)))))","v":"OK"},null,null,null,{"$f":"IF(B30=TRUE,IF(C36,\"Interconnection Inputs OK\",D36),\"\")","v":"Interconnection Inputs OK"}]
[]
[null,{"$f":"IF(design=2,TRUE,FALSE)","v":false},null,null,"New Well Construction"]
[null,null,{"$f":"IF(B38=TRUE,IF(wells_I=\"\",FALSE,TRUE),TRUE)","v":true},{"$f":"IF(B38=TRUE,IF(wells_I=\"\",\"Well Inputs Incomplete\",\"\"),\"\")","v":""},"Maxiumum Flow per Well","wells_I",null,"gpm","Guidance: Larger systems are typically made up of many smaller wells"]
[null,null,{"$f":"IF(B39=TRUE,IF(wells_depth_I=\"\",FALSE,TRUE),TRUE)","v":true},{"$f":"IF(B38=TRUE,IF(well_depth_I=\"\",\"Well Inputs Incomplete\",\"\"),\"\")","v":""},"Well Depth","well_depth_I",null,"feet"]
[null,null,{"$f":"IF(B38=TRUE,IF(well_distance_I=\"\",FALSE,TRUE),TRUE)","v":true},{"$f":"IF(B38=TRUE,IF(well_distance_I=\"\",\"Well Inputs Incomplete\",\"\"),\"\")","v":""},"Distance from Well to Distribution System","well_distance_I",null,"feet"]
[null,null,null,null,"For information: Number of wells",null,{"$f":"IF(ISERROR(wells),\"\",wells)","v":0}]
[null,null,{"$f":"IF(B38=TRUE,IF(ISERROR(well_pump_hp),FALSE,IF(well_pump_hp>well_pump_hp_max,FALSE,TRUE)),TRUE)","v":true},{"$f":"IF(B38=TRUE,IF(ISERROR(well_pump_hp),\"Well Inputs Incomplete\",IF(well_pump_hp>well_pump_hp_max,\"design exceeds maximum available pump horsepower- reduce well depth\",\"\")),\"\")","v":""},"Well pump horsepower",null,{"$f":"IF(OR(B38=FALSE,ISERROR(well_pump_hp)),\"\",well_pump_hp)","v":""},"horsepower"]
[null,null,{"$f":"AND(C39:C43)","v":true},{"$f":"IF(C44,\"OK\",IF(C39=FALSE,D39,IF(C40=FALSE,D40,IF(C41=FALSE,D41,IF(C43=FALSE,D43)))))","v":"OK"},null,null,null,{"$f":"IF(B38=TRUE,IF(AND(C43,C44),\"Well Inputs OK\",D44),\"\")","v":""}]
[]
[]
[null,null,null,null,"Component level","component_level_I",null,"<-- pick or leave blank","Leave blank to use low cost components"]
[null,null,null,null,"System automation","manual_I","fully automated","<-- pick or leave blank","Leave blank to allow model to pick based on component level"]
[null,null,null,null,"Include buildings?","include_buildings_I",null,"<-- pick or leave blank",{"$f":"IF(design=2,\"Leave blank to include buildings\",\"Interconnection designs never include buildings\")","v":"Interconnection designs never include buildings"}]
[null,null,null,null,"Include land?","include_land_I",null,"<-- pick or leave blank",{"$f":"IF(design=2,\"Leave blank to include land\",\"Interconnection designs never include land\")","v":"Interconnection designs never include land"}]
[null,null,null,null,"For information: Component level",null,{"$f":"IF(component_level=2,\"high cost\",IF(component_level=1,\"mid cost\",\"low cost\"))","v":"low cost"}]
[null,null,null,null,"Automation",null,{"$f":"IF(manual=1,\"manual\",IF(manual=2,\"semi-automated\",IF(manual=3,\"fully automated\")))","v":"fully automated"}]
[null,null,{"$f":"NOT(ISERROR(manual))","v":true},{"$f":"IF(ISERROR(manual),\"Invalid system automation input\",\"OK\")","v":"OK"},null,null,null,{"$f":"IF(C53,\"System Automation Inputs OK\",D53)","v":"System Automation Inputs OK"}]

{"sheet":"OUTPUT","index":3,"n_rows":204,"n_cols":25,"rows_kept":204,"cut":false,"hidden":false,"merged":[[153,8,153,11],[149,2,149,3],[149,4,149,5],[23,0,25,0],[23,1,25,1],[23,2,25,3],[23,4,25,5],[23,6,25,7],[23,8,25,8],[23,9,25,9],[23,10,25,10],[23,11,25,11],[23,12,25,12],[23,13,23,21],[23,22,24,23],[24,13,24,15],[24,16,24,18],[24,19,24,21]]}
["OUTPUT SUMMARY"]
[null,"Parameter","Value","Units"]
[null,"OUTPUT SUMMARY"]
[null,"Technology",{"$f":"\"Non-Treatment, \"& IF(design=1,\"interconection\", \"new well\")","v":"Non-Treatment, interconection"}]
[null,"System Size Category",{"$f":"IF(design_flow<1,\"small\",\"large\")","v":"small"}]
[null,"Design Flow",{"$f":"design_flow_I","v":0.03},{"$f":"df_units","v":"MGD"}]
[null,"Average Flow",{"$f":"average_flow_I","v":7.0000000000000001E-3},{"$f":"af_units","v":"MGD"}]
[null,{"$f":"\"Distance \"&IF(design=1,\"to interconnected supply\",\"to distribution system\")","v":"Distance to interconnected supply"},{"$f":"IF(design=1,pipe_length_I,well_distance_I)","v":10000},"feet"]
[null,{"$f":"IF(design=1,\"Pressure at supply source\",\"Well depth\")","v":"Pressure at supply source"},{"$f":"IF(design=1,buyer_pressure_I,well_depth_I)","v":"correct pressure"},{"$f":"IF(design=1,\"\",\"feet\")","v":""}]
[null,"Component level",{"$f":"IF(component_level_I=\"\",\"low\", component_level_I)","v":"low"}]
[null,"System automation",{"$f":"IF(manual=1,\"manual\",IF(manual=2,\"semi-automated\",\"fully automated\"))","v":"fully automated"}]
[null,{"$f":"\"Resulting Costs (in year \"&TEXT(current_year_cl,0)&\" dollars)\"","v":"Resulting Costs (in year 2021 dollars)"}]
[null,"Direct Capital Cost",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Error in buildup!\",direct_cost)))","v":304231.95761581301},"Details",{"$f":"IF(input_status<>\"input complete -- results ready\",IF(input_complete,\"Press Generate Results\",\"Check INPUT sheet for errors\"),IF(Missing_Cost,\"A price is not available for at least one required component. See Use? column below.\",IF(cost_buildup_error,\"There is an Excel error in the price for at least one component. See Use? column below.\",\"\")))","v":""}]
[null,"Add-on Cost",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",addon_cost)","v":0},"Details"]
[null,"Indirect Capital Cost",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",indirect_cost)","v":138938.76958300962},"Details"]
[null,"Total Capital Cost",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Error in buildup!\",system_cost)))","v":443170.72719882266},"Details"]
[null,{"$f":"\"Annualized Capital Cost \"&IF(OR(input_status<>\"input complete -- results ready\",Missing_Cost,cost_buildup_error),\"\",\"(per year over \"&useful_life&\" years at \"&Discount_rate*100&\"%)\")","v":"Annualized Capital Cost (per year over 17 years at 7%)"},{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",Annualized_Capital_Cost)","v":45391.847292618142},"Details"]
[null,"Annual O&M Cost (per year)",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",om_cost)","v":8640.5329610584886},"Details"]
[null,{"$f":"\"Total Annualized Cost (per year\"&IF(OR(input_status<>\"input complete -- results ready\",ISERROR(OR(TAC_pct_cap,TAC_pct_OM))),\"\",\", \"&TEXT(TAC_pct_cap,\"0%\")&\" capital, \"&TEXT(TAC_pct_OM,\"0%\")&\" O&M\")&\")\"","v":"Total Annualized Cost (per year, 84% capital, 16% O&M)"},{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Error in buildup!\",Annualized_Cost)))","v":54032.380253676631},"Details"]
[null,"Annualized cost per 1,000 gallons average flow",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Error in buildup!\",C178)))","v":21.147702643317665}]
[null,"Annualized cost per household per year",{"$f":"IF(input_status<>\"input complete -- results ready\",\"Action required\",IF(Missing_Cost,\"Missing cost!\",IF(cost_buildup_error,\"Error in buildup!\",C179)))","v":2131.0249312431934}]
[null,null,null,null,"errornote"]
["Direct Capital Cost Details (items in italics, without a 1 in the \"Use?\" column, represent alternate costs and are excluded from total)"]
["WBS #","Item","Design Quanitity",null,"Design Size",null,"Size used in estimate",null,"Unit Cost","Total Cost","Useful Life","Use?","Full Line Item Name","Priority selection",null,null,null,null,null,null,null,null,"Useful Life"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Small (<1 mgd)",null,null,"Medium (1-10 mgd)",null,null,"Large (>10 mgd)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Low","Mid","High","Low","Mid","High","Low","Mid","High","Count?","Depreciation","Major component useful life"]
[1,"Well Items"]
["1.1","Well Casing"]
["1.1.1","PVC",{"$f":"wells*casing_len","v":0},"lf",{"$f":"IF(C29>0,casing_diam,\"NA\")","v":"NA"},{"$f":" IF(C29=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C29=0,\"NA\",IF(curve=1,casing_diam,VLOOKUP(casing_diam,casing_pvc_cost_cl,2)))","v":"NA"},{"$f":" IF(E29=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C29=0,\"NA\",IF(curve=1,casing_pvc_eq,VLOOKUP(casing_diam,casing_pvc_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I29=\"contact vendor\",I29=\"NA\"),0,C29*I29)","v":0},{"$f":"IF(I29=\"NA\",\"N/A\",VLOOKUP(casing_diam,casing_pvc_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$28&\" - \"&B29","v":"Well Items - Well Casing - PVC"},1,2,2,1,2,2,1,2,2,{"$f":"IF(ISERROR(K29*L29),0,IF(K29<1,0,L29))","v":0},{"$f":"IF(W29,J29/K29,0)","v":0},{"$f":"IF(W29=1,K29,0)","v":0}]
["1.1.1","Stainless Steel",{"$f":"wells*casing_len","v":0},"lf",{"$f":"IF(C30>0,casing_diam,\"NA\")","v":"NA"},{"$f":" IF(C30=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C30=0,\"NA\",IF(curve=1,casing_diam,VLOOKUP(casing_diam,casing_ss_cost_cl,2)))","v":"NA"},{"$f":" IF(E30=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C30=0,\"NA\",IF(curve=1,casing_ss_eq,VLOOKUP(casing_diam,casing_ss_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I30=\"contact vendor\",I30=\"NA\"),0,C30*I30)","v":0},{"$f":"IF(I30=\"NA\",\"N/A\",VLOOKUP(casing_diam,casing_ss_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$28&\" - \"&B30","v":"Well Items - Well Casing - Stainless Steel"},2,1,1,2,1,1,2,1,1,{"$f":"IF(ISERROR(K30*L30),0,IF(K30<1,0,L30))","v":0},{"$f":"IF(W30,J30/K30,0)","v":0},{"$f":"IF(W30=1,K30,0)","v":0}]
["1.2","Well screen",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(K31*L31),0,IF(K31<1,0,L31))","v":0},{"$f":"IF(W31,J31/K31,0)","v":0}]
["1.2.1","PVC Schedule 40",{"$f":"wells*screen_len","v":0},"lf",{"$f":" IF( C32>0,screen_diam,\"NA\")","v":"NA"},{"$f":" IF(C32=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C32=0,\"NA\",IF(curve=1,screen_diam,VLOOKUP(screen_diam,screen_pvc40_cost_cl,2)))","v":"NA"},{"$f":" IF(E32=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C32=0,\"NA\",IF(curve=1,screen_pvc40_eq,VLOOKUP(screen_diam,screen_pvc40_cost_cl,4)))","v":"NA"},{"$f":"IF(OR(I32=\"contact vendor\",I32=\"NA\"),0,C32*I32)","v":0},{"$f":"IF(I32=\"NA\",\"N/A\",VLOOKUP(screen_diam,screen_pvc40_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$31&\" - \"&B32","v":"Well Items - Well screen - PVC Schedule 40"},1,2,2,1,2,2,1,2,2,{"$f":"IF(ISERROR(K32*L32),0,IF(K32<1,0,L32))","v":0},{"$f":"IF(W32,J32/K32,0)","v":0},{"$f":"IF(W32=1,K32,0)","v":0}]
["1.2.1","PVC Schedule 80",{"$f":"wells*screen_len","v":0},"lf",{"$f":" IF( C33>0,screen_diam,\"NA\")","v":"NA"},{"$f":" IF(C33=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C33=0,\"NA\",IF(curve=1,screen_diam,VLOOKUP(screen_diam,screen_pvc80_cost_cl,2)))","v":"NA"},{"$f":" IF(E33=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C33=0,\"NA\",IF(curve=1,screen_pvc80_eq,VLOOKUP(screen_diam,screen_pvc80_cost_cl,4)))","v":"NA"},{"$f":"IF(OR(I33=\"contact vendor\",I33=\"NA\"),0,C33*I33)","v":0},{"$f":"IF(I33=\"NA\",\"N/A\",VLOOKUP(screen_diam,screen_pvc80_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$31&\" - \"&B33","v":"Well Items - Well screen - PVC Schedule 80"},2,1,3,2,1,3,2,1,3,{"$f":"IF(ISERROR(K34*L34),0,IF(K34<1,0,L34))","v":0},{"$f":"IF(W33,J34/K34,0)","v":0},{"$f":"IF(W33=1,K33,0)","v":0}]
["1.2.1","Stainless Steel",{"$f":"wells*screen_len","v":0},"lf",{"$f":" IF( C34>0,screen_diam,\"NA\")","v":"NA"},{"$f":" IF(C34=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C34=0,\"NA\",IF(curve=1,screen_diam,VLOOKUP(screen_diam,screen_ss_cost_cl,2)))","v":"NA"},{"$f":" IF(E34=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C34=0,\"NA\",IF(curve=1,screen_ss_eq,VLOOKUP(screen_diam,screen_ss_cost_cl,4)))","v":"NA"},{"$f":"IF(OR(I34=\"contact vendor\",I34=\"NA\"),0,C34*I34)","v":0},{"$f":"IF(I34=\"NA\",\"N/A\",VLOOKUP(screen_diam,screen_ss_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$31&\" - \"&B34","v":"Well Items - Well screen - Stainless Steel"},3,3,1,3,3,1,3,3,1,{"$f":"IF(ISERROR(K35*L35),0,IF(K35<1,0,L35))","v":0},{"$f":"IF(W34,J35/K35,0)","v":0},{"$f":"IF(W34=1,K34,0)","v":0}]
["1.3","Plugs",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(K35*L35),0,IF(K35<1,0,L35))","v":0},{"$f":"IF(W35,J35/K35,0)","v":0}]
["1.3.1","PVC",{"$f":" IF(design=2,wells,0)","v":0},"units",{"$f":" IF( C36>0,plug_diam,\"NA\")","v":"NA"},"",{"$f":"IF(C36=0,\"NA\",IF(curve=1,plug_diam,VLOOKUP(plug_diam,plug_pvc_cost_cl,2)))","v":"NA"},{"$f":" IF(E36=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C36=0,\"NA\",IF(curve=1,plug_pvc_eq,VLOOKUP(plug_diam,plug_pvc_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I36=\"contact vendor\",I36=\"NA\"),0,C36*I36)","v":0},{"$f":"IF(I36=\"NA\",\"N/A\",VLOOKUP(plug_diam,plug_pvc_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$35&\" - \"&B36","v":"Well Items - Plugs - PVC"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K36*L36),0,IF(K36<1,0,L36))","v":0},{"$f":"IF(W36,J36/K36,0)","v":0},{"$f":"IF(W36=1,K36,0)","v":0}]
["1.3.1","Stainless Steel",{"$f":" IF(design=2,wells,0)","v":0},"units",{"$f":" IF( C37>0,plug_diam,\"NA\")","v":"NA"},"",{"$f":"IF(C37=0,\"NA\",IF(curve=1,plug_diam,VLOOKUP(plug_diam,plug_ss_cost_cl,2)))","v":"NA"},{"$f":" IF(E37=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C37=0,\"NA\",IF(curve=1,plug_ss_eq,VLOOKUP(plug_diam,plug_ss_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I37=\"NA\",\"N/A\",VLOOKUP(plug_diam,plug_ss_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&$B$35&\" - \"&B37","v":"Well Items - Plugs - Stainless Steel"},2,2,2,2,2,2,2,2,2,{"$f":"IF(ISERROR(K37*L37),0,IF(K37<1,0,L37))","v":0},{"$f":"IF(W37,J37/K37,0)","v":0},{"$f":"IF(W37=1,K37,0)","v":0}]
[1.4,"Well Drilling",{"$f":"IF(design=2,well_depth*wells,0)","v":0},"lf",{"$f":"IF(C38>0,borehole_diam,\"NA\")","v":"NA"},"",{"$f":"IF(C38>0,borehole_diam,\"NA\")","v":"NA"},{"$f":" IF(E38=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C38=0,\"NA\",well_drill_cost)","v":"NA"},0,{"$f":"IF(I38=\"NA\",\"N/A\",VLOOKUP(0,drill_12_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&B38","v":"Well Items - Well Drilling"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K38*L38),0,IF(K38<1,0,L38))","v":0},{"$f":"IF(W38,J38/K38,0)","v":0}]
[1.5,"Gravel Pack",{"$f":"wells*gravel_len","v":0},"lf",{"$f":"IF(C39>0,casing_diam,\"NA\")","v":"NA"},"",{"$f":"IF(C39=0,\"NA\",IF(curve=1,casing_diam,VLOOKUP(casing_diam,gravel_pack_cost_cl,2)))","v":"NA"},{"$f":" IF(E39=\"NA\",\"\",\"in. diam\")","v":""},{"$f":"IF(C39=0,\"NA\",IF(curve=1,gravel_pack_eq,VLOOKUP(casing_diam,gravel_pack_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I39=\"NA\",\"N/A\",VLOOKUP(casing_diam,gravel_pack_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&B39","v":"Well Items - Gravel Pack"},1,1,1,1,1,1,1,1,1]
[1.6,"Well Development",{"$f":"IF(design=2,wells,0)","v":0},"units","NA",null,"NA",null,{"$f":"IF(C40=0,\"NA\",VLOOKUP(0,develop_well_cost_cl,3))","v":"NA"},0,{"$f":"IF(I40=\"NA\",\"N/A\",VLOOKUP(0,develop_well_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$27&\" - \"&B40","v":"Well Items - Well Development"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K40*L40),0,IF(K40<1,0,L40))","v":0},{"$f":"IF(W40,J40/K40,0)","v":0}]
[1.7,"Grout Seal",{"$f":"IF(design=2,grout_vol,0)","v":0},"cf","NA",null,"NA",null,{"$f":"IF(C41=0,\"NA\",VLOOKUP(0,grout_cost_cl,3))","v":"NA"},0,{"$f":"IF(I41=\"NA\",\"N/A\",K38)","v":"N/A"},null,{"$f":"$B$27&\" - \"&B41","v":"Well Items - Grout Seal"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K41*L41),0,IF(K41<1,0,L41))","v":0},{"$f":"IF(W41,J41/K41,0)","v":0}]
["2","Vaults",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(K42*L42),0,IF(K42<1,0,L42))","v":0},{"$f":"IF(W42,J42/K42,0)","v":0}]
[2.1,"Booster Pump"]
["2.1.1","Concrete",{"$f":"pump_vault_total_conc_vol","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C44=0,\"NA\",VLOOKUP(0,conc_basin_cost_cl,3))","v":"NA"},{"$f":"IF(OR(I44=\"contact vendor\",I44=\"NA\"),0,C44*I44)","v":0},{"$f":"IF(I44=\"NA\",\"N/A\",VLOOKUP(0,conc_basin_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$42&\" - \"&$B43&\" - \"&B44","v":"Vaults - Booster Pump - Concrete"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K44*L44),0,IF(K44<1,0,L44))","v":0},{"$f":"IF(W44,J44/K44,0)","v":0}]
["2.1.2","Excavation",{"$f":"pump_vault_exc_volume","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C45=0,\"NA\",VLOOKUP(0,excavate_cost_cl,3))","v":"NA"},0,{"$f":"IF(I45=\"NA\",\"N/A\",VLOOKUP(0,conc_basin_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$42&\" - \"&$B$43&\" - \"&B45","v":"Vaults - Booster Pump - Excavation"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K45*L45),0,IF(K45<1,0,L45))","v":0},{"$f":"IF(W45,J45/K45,0)","v":0}]
["2.1.3","Backfill and Compaction",{"$f":"pump_vault_backfill_volume","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C46=0,\"NA\",VLOOKUP(0,backfill_cost_cl,3))","v":"NA"},0,{"$f":"IF(I46=\"NA\",\"N/A\",VLOOKUP(0,conc_basin_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$42&\" - \"&$B$43&\" - \"&B46","v":"Vaults - Booster Pump - Backfill and Compaction"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K46*L46),0,IF(K46<1,0,L46))","v":0},{"$f":"IF(W46,J46/K46,0)","v":0}]
[2.2000000000000002,"Pressure Reducing Valve"]
["2.2.1","Concrete",{"$f":"prv_vault_total_conc_vol","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C48=0,\"NA\",VLOOKUP(0,conc_basin_cost_cl,3))","v":"NA"},{"$f":"IF(OR(I48=\"contact vendor\",I48=\"NA\"),0,C48*I48)","v":0},{"$f":"IF(I48=\"NA\",\"N/A\",VLOOKUP(0,conc_basin_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$42&\" - \"&$B$47&\" - \"&B48","v":"Vaults - Pressure Reducing Valve - Concrete"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K48*L48),0,IF(K48<1,0,L48))","v":0},{"$f":"IF(W48,J48/K48,0)","v":0}]
["2.2.2","Excavation",{"$f":"prv_vault_exc_volume","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C49=0,\"NA\",VLOOKUP(0,excavate_cost_cl,3))","v":"NA"},{"$f":"IF(OR(I49=\"contact vendor\",I49=\"NA\"),0,C49*I49)","v":0},{"$f":"IF(I49=\"NA\",\"N/A\",VLOOKUP(0,conc_basin_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$42&\" - \"&$B$47&\" - \"&B49","v":"Vaults - Pressure Reducing Valve - Excavation"},1,1,1,1,1,1,1,1,1,0,0]
["2.2.3","Backfill and Compaction",{"$f":"prv_vault_backfill_volume","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C50=0,\"NA\",VLOOKUP(0,backfill_cost_cl,3))","v":"NA"},{"$f":"IF(OR(I50=\"contact vendor\",I50=\"NA\"),0,C50*I50)","v":0},{"$f":"IF(I50=\"NA\",\"N/A\",VLOOKUP(0,conc_basin_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$42&\" - \"&$B$47&\" - \"&B50","v":"Vaults - Pressure Reducing Valve - Backfill and Compaction"},1,1,1,1,1,1,1,1,1,0,0]
["3","Piping",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
[3.1,"Interconnect ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["3.1.1","Ductile Iron",{"$f":"interconnect_pipe_length","v":10000},"lf",{"$f":"IF(C53=0,\"NA\",interconnect_pipe_diameter)","v":4},{"$f":" IF(C53=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C53=0,\"NA\",IF(curve=1,interconnect_pipe_diameter,VLOOKUP(interconnect_pipe_diameter,di_pipe_cost_cl,2)))","v":4},{"$f":" IF(C53=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C53=0,\"NA\",IF(curve=1,di_pipe_eq_interconnect,VLOOKUP(interconnect_pipe_diameter,di_pipe_cost_cl,3)))","v":74.282787567},{"$f":"IF(OR(I53=\"contact vendor\",I53=\"NA\"),0,C53*I53)","v":742827.87566999998},{"$f":"IF(I53=\"NA\",\"N/A\",VLOOKUP(interconnect_pipe_diameter,di_pipe_cost_cl,IF(ss_cat=\"large\",4,5)))","v":35},0,{"$f":"$B$51&\" - \"&$B$52&\" - \"&B53","v":"Piping - Interconnect  - Ductile Iron"},2,2,1,2,2,1,2,2,1,0,0,{"$f":"IF(W53=1,K53,0)","v":0}]
["3.1.1","PVC",{"$f":"interconnect_pipe_length","v":10000},"lf",{"$f":"IF(C54=0,\"NA\",interconnect_pipe_diameter)","v":4},{"$f":" IF(C54=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C54=0,\"NA\",IF(curve=1,interconnect_pipe_diameter,VLOOKUP(interconnect_pipe_diameter,pvc_pipe_cost_cl,2)))","v":4},{"$f":" IF(C54=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C54=0,\"NA\",IF(curve=1,pvc_pipe_eq_interconnect,VLOOKUP(interconnect_pipe_diameter,pvc_pipe_cost_cl,3)))","v":6.1853408730000004},{"$f":"IF(OR(I54=\"contact vendor\",I54=\"NA\"),0,C54*I54)","v":61853.408730000003},{"$f":"IF(I54=\"NA\",\"N/A\",VLOOKUP(interconnect_pipe_diameter,pvc_pipe_cost_cl,IF(ss_cat=\"large\",4,5)))","v":17},1,{"$f":"$B$51&\" - \"&$B$52&\" - \"&B54","v":"Piping - Interconnect  - PVC"},1,1,2,1,1,2,1,1,2,1,3638.4358076470589,{"$f":"IF(W54=1,K54,0)","v":17}]
["3.1.2","Excavation",{"$f":"interconnect_trench_vol","v":2736.6255144032921},"cy","NA",null,"NA",null,{"$f":"IF(C55=0,\"NA\",VLOOKUP(0,excavate_cost_cl,3))","v":30.879999999999995},{"$f":"IF(OR(I55=\"contact vendor\",I55=\"NA\"),0,C55*I55)","v":84506.995884773642},{"$f":"IF(I55=\"NA\",\"N/A\",IF(L53=1,K53,IF(L54=1,K54,\"N/A\")))","v":17},1,{"$f":"$B$51&\" - \"&$B$52&\" - \"&B55","v":"Piping - Interconnect  - Excavation"},1,1,1,1,1,1,1,1,1,1,4970.9997579278615]
["3.1.3","Backfill and Compaction",{"$f":"interconnect_trench_vol","v":2736.6255144032921},"cy","NA",null,"NA",null,{"$f":"IF(C56=0,\"NA\",VLOOKUP(0,backfill_cost_cl,3))","v":18.649999999999999},{"$f":"IF(OR(I56=\"contact vendor\",I56=\"NA\"),0,C56*I56)","v":51038.065843621393},{"$f":"IF(I56=\"NA\",\"N/A\",IF(L53=1,K53,IF(L54=1,K54,\"N/A\")))","v":17},1,{"$f":"$B$51&\" - \"&$B$52&\" - \"&B56","v":"Piping - Interconnect  - Backfill and Compaction"},1,1,1,1,1,1,1,1,1,1,3002.2391672718468]
["3.1.4","Asphalt Patch",{"$f":"asphalt_patch_area","v":864.19753086419757},"sy",{"$f":"IF(C57=0,\"NA\",asphalt_thickness)","v":4},{"$f":" IF(C57=0,\"\",\"in. thick\")","v":"in. thick"},{"$f":"IF(C57=0,\"NA\",VLOOKUP(asphalt_thickness,asphalt_patch_cost_cl,2))","v":4},{"$f":" IF(C57=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C57=0,\"NA\",VLOOKUP(asphalt_thickness,asphalt_patch_cost_cl,3))","v":101},{"$f":"IF(OR(I57=\"contact vendor\",I57=\"NA\"),0,C57*I57)","v":87283.950617283961},{"$f":"IF(I57=\"NA\",\"N/A\",IF(L53=1,K53,IF(L54=1,K54,\"N/A\")))","v":17},1,{"$f":"$B$51&\" - \"&$B$52&\" - \"&B57","v":"Piping - Interconnect  - Asphalt Patch"},1,1,1,1,1,1,1,1,1,1,5134.3500363108215]
[3.2,"Well",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["3.2.1","Ductile Iron",{"$f":"total_well_pipe_length","v":0},"lf",{"$f":"IF(C59=0,\"NA\",well_pipe_diameter)","v":"NA"},{"$f":" IF(C59=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C59=0,\"NA\",IF(curve=1,well_pipe_diameter,VLOOKUP(well_pipe_diameter,di_pipe_cost_cl,2)))","v":"NA"},{"$f":" IF(C59=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C59=0,\"NA\",IF(curve=1,di_pipe_eq_well,VLOOKUP(well_pipe_diameter,di_pipe_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I59=\"contact vendor\",I59=\"NA\"),0,C59*I59)","v":0},{"$f":"IF(I59=\"NA\",\"N/A\",VLOOKUP(well_pipe_diameter,di_pipe_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$51&\" - \"&$B$58&\" - \"&B59","v":"Piping - Well - Ductile Iron"},2,2,1,2,2,1,2,2,1,0,0]
["3.2.1","PVC",{"$f":"total_well_pipe_length","v":0},"lf",{"$f":"IF(C60=0,\"NA\",well_pipe_diameter)","v":"NA"},{"$f":" IF(C60=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C60=0,\"NA\",IF(curve=1,well_pipe_diameter,VLOOKUP(well_pipe_diameter,pvc_pipe_cost_cl,2)))","v":"NA"},{"$f":" IF(C60=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C60=0,\"NA\",IF(curve=1,pvc_pipe_eq_well,VLOOKUP(well_pipe_diameter,pvc_pipe_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I60=\"NA\",\"N/A\",VLOOKUP(well_pipe_diameter,pvc_pipe_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$51&\" - \"&$B$58&\" - \"&B60","v":"Piping - Well - PVC"},1,1,2,1,1,2,1,1,2,0,0]
["3.2.2","Excavation",{"$f":"well_trench_vol","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C61=0,\"NA\",VLOOKUP(0,excavate_cost_cl,3))","v":"NA"},0,{"$f":"IF(I61=\"NA\",\"N/A\",IF(L59=1,K59,IF(L60=1,K60,\"N/A\")))","v":"N/A"},null,{"$f":"$B$51&\" - \"&$B$58&\" - \"&B61","v":"Piping - Well - Excavation"},1,1,1,1,1,1,1,1,1,0,0]
["3.2.3","Backfill and Compaction",{"$f":"well_trench_vol","v":0},"cy","NA",null,"NA",null,{"$f":"IF(C62=0,\"NA\",VLOOKUP(0,backfill_cost_cl,3))","v":"NA"},0,{"$f":"IF(I62=\"NA\",\"N/A\",IF(L59=1,K59,IF(L60=1,K60,\"N/A\")))","v":"N/A"},null,{"$f":"$B$51&\" - \"&$B$58&\" - \"&B62","v":"Piping - Well - Backfill and Compaction"},1,1,1,1,1,1,1,1,1,0,0]
["4","Valves",null,null,null,null,null,null,null,null,null,null,null,1,1,1,1,1,1,1,1,1,0,0]
[4.0999999999999996,"Isolation and Street",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["4.1.1","Ductile Iron",{"$f":"num_valves","v":11},{"$f":"IF( C65=0,\"\", \"units\")","v":"units"},{"$f":"IF(C65=0,\"NA\",valve_size)","v":4},{"$f":" IF(C65=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C65=0,\"NA\",IF(curve=1,valve_size,VLOOKUP(valve_size,street_valve_cost_cl,2)))","v":4},{"$f":" IF(C65=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C65=0,\"NA\",IF(curve=1,street_valve_eq,VLOOKUP(valve_size,street_valve_cost_cl,3)))","v":1339.4691080929997},{"$f":"IF(OR(I65=\"contact vendor\",I65=\"NA\"),0,C65*I65)","v":14734.160189022998},{"$f":"IF(I65=\"NA\",\"N/A\",VLOOKUP(valve_size,street_valve_cost_cl,IF(ss_cat=\"large\",4,5)))","v":20},1,{"$f":"$B$63&\" - \"&$B$64&\" - \"&B65","v":"Valves - Isolation and Street - Ductile Iron"},1,1,1,1,1,1,1,1,1,1,736.70800945114991]
[4.2,"Motor/Air Operated (on/off)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["4.2.1","Booster Pump - Polypropylene/PVC",{"$f":"total_booster_MOV","v":0},{"$f":"IF( C67=0,\"\", \"units\")","v":""},{"$f":" IF( C67=0,\"NA\",valve_size)","v":"NA"},{"$f":" IF(C67=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C67=0,\"NA\",IF(curve=1,E67,VLOOKUP(E67,pp_mov_cost_cl,2)))","v":"NA"},{"$f":" IF(C67=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C67=0,\"NA\",IF(curve=1,pp_mov_eq_booster_pump,VLOOKUP(G67,pp_mov_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I67=\"contact vendor\",I67=\"NA\"),0,C67*I67)","v":0},{"$f":"IF(I67=\"NA\",\"N/A\",VLOOKUP(G67,pp_mov_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$63&\" - \"&$B$66&\" - \"&B67","v":"Valves - Motor/Air Operated (on/off) - Booster Pump - Polypropylene/PVC"},1,2,3,1,2,3,1,2,3,0,0]
["4.2.1","Booster Pump - Stainless Steel",{"$f":"total_booster_MOV","v":0},"","NA","",{"$f":"IF(C68=0,\"NA\",IF(curve=1,E68,VLOOKUP(E68,ss_mov_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C68=0,\"NA\",IF(curve=1,ss_mov_eq_booster_pump,VLOOKUP(G68,ss_mov_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I68=\"NA\",\"N/A\",VLOOKUP(G68,ss_mov_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Motor/Air Operated (on/off) - Booster Pump - Stainless Steel",3,3,1,3,3,1,3,3,1,0,0]
["4.2.1","Booster Pump - Cast Iron",{"$f":"total_booster_MOV","v":0},"","NA","",{"$f":"IF(C69=0,\"NA\",IF(curve=1,E69,VLOOKUP(E69,ci_mov_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C69=0,\"NA\",IF(curve=1,ci_mov_eq_booster_pump,VLOOKUP(G69,ci_mov_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I69=\"NA\",\"N/A\",VLOOKUP(G69,ci_mov_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Motor/Air Operated (on/off) - Booster Pump - Cast Iron",2,1,2,2,1,2,2,1,2,0,0]
["4.2.2","Well Pump - Polypropylene/PVC",{"$f":"total_well_MOV","v":0},"","NA","",{"$f":"IF(C70=0,\"NA\",IF(curve=1,E70,VLOOKUP(E70,pp_mov_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C70=0,\"NA\",IF(curve=1,pp_mov_eq_well_pump,VLOOKUP(G70,pp_mov_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I70=\"NA\",\"N/A\",VLOOKUP(G70,pp_mov_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Motor/Air Operated (on/off) - Well Pump - Polypropylene/PVC",1,2,3,1,2,3,1,2,3,0,0]
["4.2.2","Well Pump - Stainless Steel",{"$f":"total_well_MOV","v":0},"","NA","",{"$f":"IF(C71=0,\"NA\",IF(curve=1,E71,VLOOKUP(E71,ss_mov_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C71=0,\"NA\",IF(curve=1,ss_mov_eq_well_pump,VLOOKUP(G71,ss_mov_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I71=\"NA\",\"N/A\",VLOOKUP(G71,ss_mov_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Motor/Air Operated (on/off) - Well Pump - Stainless Steel",3,3,1,3,3,1,3,3,1,0,0]
["4.2.2","Well Pump - Cast Iron",{"$f":"total_well_MOV","v":0},"","NA","",{"$f":"IF(C72=0,\"NA\",IF(curve=1,E72,VLOOKUP(E72,ci_mov_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C72=0,\"NA\",IF(curve=1,ci_mov_eq_well_pump,VLOOKUP(G72,ci_mov_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I72=\"NA\",\"N/A\",VLOOKUP(G72,ci_mov_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Motor/Air Operated (on/off) - Well Pump - Cast Iron",2,1,2,2,1,2,2,1,2,0,0]
[4.3,"Manual",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["4.3.1","Booster Pump - Polypropylene/PVC",{"$f":"total_booster_man","v":0},{"$f":"IF( C74=0,\"\", \"units\")","v":""},{"$f":" IF( C74=0,\"NA\",valve_size)","v":"NA"},{"$f":" IF(C74=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C74=0,\"NA\",IF(curve=1,E74,VLOOKUP(E74,pp_man_cost_cl,2)))","v":"NA"},{"$f":" IF(C74=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C74=0,\"NA\",IF(curve=1,pp_man_eq_booster_pump,VLOOKUP(G74,pp_man_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I74=\"contact vendor\",I74=\"NA\"),0,C74*I74)","v":0},{"$f":"IF(I74=\"NA\",\"N/A\",VLOOKUP(G74,pp_man_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$63&\" - \"&$B$73&\" - \"&B74","v":"Valves - Manual - Booster Pump - Polypropylene/PVC"},1,2,3,1,2,3,1,2,3,0,0]
["4.3.1","Booster Pump - Stainless Steel",{"$f":"total_booster_man","v":0},"","NA","",{"$f":"IF(C75=0,\"NA\",IF(curve=1,E75,VLOOKUP(E75,ss_man_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C75=0,\"NA\",IF(curve=1,ss_man_eq_booster_pump,VLOOKUP(G75,ss_man_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I75=\"NA\",\"N/A\",VLOOKUP(G75,ss_man_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Manual - Booster Pump - Stainless Steel",3,3,1,3,3,1,3,3,1,0,0]
["4.3.1","Booster Pump - Cast Iron",{"$f":"total_booster_man","v":0},"","NA","",{"$f":"IF(C76=0,\"NA\",IF(curve=1,E76,VLOOKUP(E76,ci_bfv_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C76=0,\"NA\",IF(curve=1,ci_man_eq_booster_pump,VLOOKUP(G76,ci_bfv_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I76=\"NA\",\"N/A\",VLOOKUP(G76,ci_bfv_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Manual - Booster Pump - Cast Iron",2,1,2,2,1,2,2,1,2,0,0]
["4.3.2","Well Pump - Polypropylene/PVC",{"$f":"total_well_man","v":0},"","NA","",{"$f":"IF(C77=0,\"NA\",IF(curve=1,E77,VLOOKUP(E77,pp_man_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C77=0,\"NA\",IF(curve=1,pp_man_eq_well_pump,VLOOKUP(G77,pp_man_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I77=\"NA\",\"N/A\",VLOOKUP(G77,pp_man_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Manual - Well Pump - Polypropylene/PVC",1,2,3,1,2,3,1,2,3,0,0]
["4.3.2","Well Pump - Stainless Steel",{"$f":"total_well_man","v":0},"","NA","",{"$f":"IF(C78=0,\"NA\",IF(curve=1,E78,VLOOKUP(E78,ss_man_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C78=0,\"NA\",IF(curve=1,ss_man_eq_well_pump,VLOOKUP(G78,ss_man_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I78=\"NA\",\"N/A\",VLOOKUP(G78,ss_man_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Manual - Well Pump - Stainless Steel",3,3,1,3,3,1,3,3,1,0,0]
["4.3.2","Well Pump - Cast Iron",{"$f":"total_well_man","v":0},"","NA","",{"$f":"IF(C79=0,\"NA\",IF(curve=1,E79,VLOOKUP(E79,ci_bfv_cost_cl,2)))","v":"NA"},"",{"$f":"IF(C79=0,\"NA\",IF(curve=1,ci_man_eq_well_pump,VLOOKUP(G79,ci_bfv_cost_cl,3)))","v":"NA"},0,{"$f":"IF(I79=\"NA\",\"N/A\",VLOOKUP(G79,ci_bfv_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,"Valves - Manual - Well Pump - Cast Iron",2,1,2,2,1,2,2,1,2,0,0]
[4.4000000000000004,"Control",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["4.4.1","Pressure Reducing",{"$f":"num_PRVs","v":0},"units",{"$f":"IF(C81=0,\"NA\",PRV_size)","v":"NA"},{"$f":" IF(C81=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C81=0,\"NA\",IF(curve=1,PRV_size,VLOOKUP(PRV_size,PRV_cost_cl,2)))","v":"NA"},{"$f":" IF(C81=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C81=0,\"NA\",IF(curve=1,prv_eq,VLOOKUP(PRV_size,PRV_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I81=\"contact vendor\",I81=\"NA\"),0,C81*I81)","v":0},{"$f":"IF(I81=\"NA\",\"N/A\",VLOOKUP(PRV_size,PRV_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$63&\" - \"&$B$80&\" - \"&B81","v":"Valves - Control - Pressure Reducing"},1,1,1,1,1,1,1,1,1,0,0]
["5","Pumps",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
[5.0999999999999996,"Interconnect Booster Pump",{"$f":"total_booster_pumps","v":0},"units",{"$f":"IF(C83=0,\"NA\",booster_pump_rating)","v":"NA"},{"$f":" IF(C83=0,\"\",\"gpm\")","v":""},{"$f":"IF(C83=0,\"NA\",IF(curve=1,booster_pump_rating,booster_pump_size_used))","v":"NA"},{"$f":" IF(C83=0,\"\",\"gpm\")","v":""},{"$f":"IF(C83=0,\"NA\",IF(booster_pump_rating=0,\"-\",IF(curve=1,int_booster_pump_eq,booster_pump_cost)))","v":"NA"},{"$f":"IF(OR(I83=\"contact vendor\",I83=\"NA\"),0,C83*I83)","v":0},{"$f":"IF(C83=0,\"N/A\",VLOOKUP(booster_pump_rating,pump_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$82&\" - \"&B83","v":"Pumps - Interconnect Booster Pump"},1,1,1,1,1,1,1,1,1,0,0]
[5.2,"Well Pump",{"$f":"well_pumps","v":0},"units",{"$f":"IF(C84=0,\"NA\",well_pump_hp)","v":"NA"},{"$f":" IF(C84=0,\"\",\"hp\")","v":""},{"$f":"IF(C84=0,\"NA\",VLOOKUP(E84,well_pump_cost_cl,2))","v":"NA"},{"$f":" IF(C84=0,\"\",\"hp\")","v":""},{"$f":"IF(C84=0,\"NA\",VLOOKUP(G84,well_pump_cost_cl,3))","v":"NA"},0,{"$f":"IF(I84=\"NA\",\"N/A\",VLOOKUP(G84,well_pump_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$82&\" - \"&B84","v":"Pumps - Well Pump"},1,1,1,1,1,1,1,1,1,0,0]
["6","Instrumentation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
[6.1,"Flow Meters - Interconnect",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["6.1.1","Orifice Plate",{"$f":"IF( interconnect_meter_size=0,0,1)","v":1},"units",{"$f":"IF(C87=0,\"NA\",interconnect_meter_size)","v":4},{"$f":" IF(C87=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(curve=1,E87,VLOOKUP(E87,op_fm_cost_cl,2))","v":4},{"$f":" IF(C87=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C87=0,\"NA\", IF(curve=1,op_fm_eq_innterconnect,VLOOKUP(G87,op_fm_cost_cl,3)))","v":2640.9899335509999},{"$f":"IF(OR(I87=\"contact vendor\",I87=\"NA\"),0,C87*I87)","v":2640.9899335509999},{"$f":"IF( I87=\"NA\",\"NA\",VLOOKUP(G87,op_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":14},0,{"$f":"$B$85&\" - \"&$B$86&\" - \"&B87","v":"Instrumentation - Flow Meters - Interconnect - Orifice Plate"},3,4,4,3,4,4,3,3,4,0,0]
["6.1.1","Propeller",{"$f":"IF( interconnect_meter_size=0,0,1)","v":1},"units",{"$f":"IF(C88=0,\"NA\",interconnect_meter_size)","v":4},{"$f":" IF(C88=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(curve=1,E88,VLOOKUP(E88,prop_fm_cost_cl,2))","v":4},{"$f":" IF(C88=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C88=0,\"NA\", IF(curve=1,prop_fm_eq_innterconnect,VLOOKUP(G88,prop_fm_cost_cl,3)))","v":3600.3763511109996},{"$f":"IF(OR(I88=\"contact vendor\",I88=\"NA\"),0,C88*I88)","v":3600.3763511109996},{"$f":" IF(I88=\"NA\",\"NA\",VLOOKUP(G88,prop_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":14},1,{"$f":"$B$85&\" - \"&$B$86&\" - \"&B88","v":"Instrumentation - Flow Meters - Interconnect - Propeller"},1,2,3,1,2,3,4,4,2,1,257.16973936507139]
["6.1.1","Venturi",{"$f":"IF( interconnect_meter_size=0,0,1)","v":1},"units",{"$f":"IF(C89=0,\"NA\",interconnect_meter_size)","v":4},{"$f":" IF(C89=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(curve=1,E89,VLOOKUP(E89,ven_fm_cost_cl,2))","v":4},{"$f":" IF(C89=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C89=0,\"NA\", IF(curve=1,ven_fm_eq_innterconnect,VLOOKUP(G89,ven_fm_cost_cl,3)))","v":2664.4617081420001},{"$f":"IF(OR(I89=\"contact vendor\",I89=\"NA\"),0,C89*I89)","v":2664.4617081420001},{"$f":" IF(I89=\"NA\",\"NA\",VLOOKUP(G89,ven_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":14},0,{"$f":"$B$85&\" - \"&$B$86&\" - \"&B89","v":"Instrumentation - Flow Meters - Interconnect - Venturi"},2,1,2,2,1,2,1,2,3,0,0]
["6.1.1","Magnetic",{"$f":"IF( interconnect_meter_size=0,0,1)","v":1},"units",{"$f":"IF(C90=0,\"NA\",interconnect_meter_size)","v":4},{"$f":" IF(C90=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(curve=1,E90,VLOOKUP(E90,mag_fm_cost_cl,2))","v":4},{"$f":" IF(C90=0,\"\",\"in. diam\")","v":"in. diam"},{"$f":"IF(C90=0,\"NA\", IF(curve=1,mag_fm_eq_innterconnect,VLOOKUP(G90,mag_fm_cost_cl,3)))","v":5121.6611304090002},{"$f":"IF(OR(I90=\"contact vendor\",I90=\"NA\"),0,C90*I90)","v":5121.6611304090002},{"$f":" IF(I90=\"NA\",\"NA\",VLOOKUP(G90,mag_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":14},0,{"$f":"$B$85&\" - \"&$B$86&\" - \"&B90","v":"Instrumentation - Flow Meters - Interconnect - Magnetic"},4,3,1,4,3,1,2,1,1,0,0]
[6.2,"Flow Meters - Well"]
["6.2.1","Orifice Plate",{"$f":"IF( well_meter_size=0,0,1)","v":0},"units",{"$f":"IF(C92=0,\"NA\",well_meter_size)","v":"NA"},{"$f":" IF(C92=0,\"\",\"in. diam\")","v":""},{"$f":"IF(curve=1,E92,VLOOKUP(E92,op_fm_cost_cl,2))","v":"NA"},{"$f":" IF(C92=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C92=0,\"NA\", IF(curve=1,op_fm_eq_well,VLOOKUP(G92,op_fm_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I92=\"contact vendor\",I92=\"NA\"),0,C92*I92)","v":0},{"$f":"IF( I92=\"NA\",\"NA\",VLOOKUP(G92,op_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"NA"},null,{"$f":"$B$85&\" - \"&$B$91&\" - \"&B92","v":"Instrumentation - Flow Meters - Well - Orifice Plate"},3,4,4,3,4,4,3,3,4,{"$f":"IF(ISERROR(K92*L92),0,IF(K92<1,0,L92))","v":0},{"$f":"IF(W92,J92/K92,0)","v":0}]
["6.2.1","Propeller",{"$f":"IF( well_meter_size=0,0,1)","v":0},"units",{"$f":"IF(C93=0,\"NA\",well_meter_size)","v":"NA"},{"$f":" IF(C93=0,\"\",\"in. diam\")","v":""},{"$f":"IF(curve=1,E93,VLOOKUP(E93,prop_fm_cost_cl,2))","v":"NA"},{"$f":" IF(C93=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C93=0,\"NA\", IF(curve=1,prop_fm_eq_well,VLOOKUP(G93,prop_fm_cost_cl,3)))","v":"NA"},0,{"$f":" IF(I93=\"NA\",\"NA\",VLOOKUP(G93,prop_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"NA"},null,{"$f":"$B$85&\" - \"&$B$91&\" - \"&B93","v":"Instrumentation - Flow Meters - Well - Propeller"},1,2,3,1,2,3,4,4,2,0,0]
["6.2.1","Venturi",{"$f":"IF( well_meter_size=0,0,1)","v":0},"units",{"$f":"IF(C94=0,\"NA\",well_meter_size)","v":"NA"},{"$f":" IF(C94=0,\"\",\"in. diam\")","v":""},{"$f":"IF(curve=1,E94,VLOOKUP(E94,ven_fm_cost_cl,2))","v":"NA"},{"$f":" IF(C94=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C94=0,\"NA\", IF(curve=1,ven_fm_eq_well,VLOOKUP(G94,ven_fm_cost_cl,3)))","v":"NA"},0,{"$f":" IF(I94=\"NA\",\"NA\",VLOOKUP(G94,ven_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"NA"},null,{"$f":"$B$85&\" - \"&$B$91&\" - \"&B94","v":"Instrumentation - Flow Meters - Well - Venturi"},2,1,2,2,1,2,1,2,3,0,0]
["6.2.1","Magnetic",{"$f":"IF( well_meter_size=0,0,1)","v":0},"units",{"$f":"IF(C95=0,\"NA\",well_meter_size)","v":"NA"},{"$f":" IF(C95=0,\"\",\"in. diam\")","v":""},{"$f":"IF(curve=1,E95,VLOOKUP(E95,mag_fm_cost_cl,2))","v":"NA"},{"$f":" IF(C95=0,\"\",\"in. diam\")","v":""},{"$f":"IF(C95=0,\"NA\", IF(curve=1,mag_fm_eq_well,VLOOKUP(G95,mag_fm_cost_cl,3)))","v":"NA"},0,{"$f":" IF(I95=\"NA\",\"NA\",VLOOKUP(G95,mag_fm_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"NA"},null,{"$f":"$B$85&\" - \"&$B$91&\" - \"&B95","v":"Instrumentation - Flow Meters - Well - Magnetic"},4,3,1,4,3,1,2,1,1,0,0]
["7","System Controls ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
[7.1,"Remote Telemetry Units (RTUs)",{"$f":"total_RTU","v":1},"units","NA",null,"NA",null,{"$f":"IF(C97=0,\"NA\",VLOOKUP(0,RTU_cost_cl,3))","v":1215},{"$f":"IF(OR(I97=\"contact vendor\",I97=\"NA\"),0,C97*I97)","v":1215},{"$f":"IF(I97=\"NA\",\"N/A\",VLOOKUP(0,RTU_cost_cl,IF(ss_cat=\"large\",4,5)))","v":8},1,{"$f":"$B$96&\" - \"&B97","v":"System Controls  - Remote Telemetry Units (RTUs)"},1,1,1,1,1,1,1,1,1,1,151.875]
[7.2,"Well Pump - Soft Start Control",{"$f":"tot_soft_start","v":0},"units","NA",null,"NA",null,{"$f":"IF(C98=0,\"NA\",VLOOKUP(0,soft_start_cost_cl,3))","v":"NA"},0,{"$f":"IF(I98=\"NA\",\"N/A\",VLOOKUP(0,soft_start_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$96&\" - \"&B98","v":"System Controls  - Well Pump - Soft Start Control"},1,1,1,1,1,1,1,1,1,{"$f":"IF(ISERROR(K98*L98),0,IF(K98<1,0,L98))","v":0},{"$f":"IF(W98,J98/K98,0)","v":0}]
["8","Building Structures",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
[8.1,"Building 1",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0,0]
["8.1.1","Small Low Cost Shed",{"$f":"IF(AND(build1_fp>0,build1_fp<500),1,0)","v":0},"units",{"$f":"IF(C101=0,\"NA\",build1_fp)","v":"NA"},{"$f":"IF(C101=0,\"\",\"sf\")","v":""},{"$f":"IF(C101=0,\"NA\",build1_fp)","v":"NA"},{"$f":"IF(C101=0,\"\",\"sf\")","v":""},{"$f":"IF(C101=0,\"NA\",VLOOKUP(build1_fp,bldg_shed_cost_cl,3))","v":"NA"},{"$f":"IF(OR(I101=\"contact vendor\",I101=\"NA\"),0,G101*I101)","v":0},{"$f":"IF($C101=0,\"N/A\",VLOOKUP(build1_fp,bldg_shed_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$99&\" - \"&$B$100&\" - \"&B101","v":"Building Structures - Building 1 - Small Low Cost Shed"},{"$f":"IF(include_buildings=1,1,0)","v":0},0,0,0,0,0,0,0,0,0,0,{"$f":"IF(W101,K101,0)","v":0}]
["8.1.1","Low Quality",{"$f":"IF(build1_fp>=500,1,0)","v":0},"units",{"$f":"build1_fp","v":0},"sf",{"$f":"build1_fp","v":0},"sf",{"$f":"IF(C102=0,\"NA\",IF(curve=1,bldg_lo_eq_1,VLOOKUP(build1_fp, bldg_lo_cost_cl,3)))","v":"NA"},{"$f":"IF(OR(I102=\"NA\",I102=\"contact vendor\"),0,G102*I102)","v":0},{"$f":"IF($C102=0,\"N/A\",VLOOKUP(build1_fp,bldg_lo_cost_cl,IF(ss_cat=\"large\",4,5)))","v":"N/A"},null,{"$f":"$B$99&\" - \"&$B$100&\" - \"&B102","v":"Building Structures - Building 1 - Low Quality"},0,0,0,0,0,0,0,0,0,0,0,{"$f":"IF(W102,K102,0)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"direct_for_UL","total_direct_depreciation","build_UL"]
["Total of direct line items above:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUMPRODUCT(W29:W102,J29:J102)","v":304231.95761581301},{"$f":"SUM(X29:X102)","v":17891.777517973813},{"$f":"MAX(Y36:Y37,Y53:Y54,Y32:Y34,Y29:Y30)","v":17}]
[null,"Direct cost category","Total","Guidance"]
[null,"Process costs",{"$f":"SUMPRODUCT(J28:J97,L28:L97)","v":304231.95761581301},"Excludes components in italics (which are alternate costs for comparison).  Includes installation, transportation, and O&P"]
[null,"Building costs",{"$f":"SUMPRODUCT(J101:J102,L101:L102)","v":0},"See indirect assumptions sheet to exclude buildings"]
[null,"Total direct capital cost",{"$f":"process_cost+building_cost","v":304231.95761581301},"Total of process costs"]
[]
["Add-on Cost Details (See indirect assumptions sheet to exclude individual add-on items)"]
[null,"Add-on Line Item","Total","Guidance"]
[null,"Permits",{"$f":"permit_cost","v":0},"For building permits"]
[null,"Pilot Study",0,"No pilot study needed for this technology"]
[null,"Land Cost",{"$f":"include_land*land_required*land_cost","v":0},{"$f":"\"For \"&TEXT(land_required,\"#0.000\")&\" acres\"","v":"For 0.000 acres"}]
[null,"Total add-on costs",{"$f":"SUM(C112:C114)","v":0}]
[]
["Indirect Capital Cost Details (See indirect assumptions sheet to exclude individual indirect items)"]
[null,"Indirect Line Item","Total","Percent","Guidance"]
[null,"Mobilization and Demobilization",{"$f":"(direct_cost+C121+C122+C123+C124+C125+C126+C127+C130)*D119","v":16732.757668869715},{"$f":"CHOOSE(ss_cat2,mob_pct_small,mob_pct_medium,mob_pct_large)","v":0.05}]
[null,"Architectural Fees for Treatment Building",{"$f":"IF(ss_cat2=1,include_arch_small,include_arch)*D120*building_cost","v":0},{"$f":"VLOOKUP(building_cost,architectural_table_cl,3)","v":0.09},"Excluded by default for small systems"]
[null,"Installation, Transportation, and O&P",{"$f":"direct_cost*D121","v":0},{"$f":"inst_pct","v":0}]
[null,"Site Work  ",{"$f":"include_sitework*sitework_cost","v":0},null,"Calculated based on system requirements."]
[null,"Yard Piping",{"$f":"0","v":0},null,"Not included in this technology"]
[null,"Geotechnical",{"$f":"IF(component_level=\"\",include_geo_low,IF(component_level=1,include_geo_medium,include_geo_high)) *geotech_cost","v":0},null,"Calculated based on system requirements.  "]
[null,"Standby Power",{"$f":"IF(ss_cat2=1,include_standby_small,include_standby)*std_power_cost","v":0},null,"Calculated based on system requirements.  Excluded by default for small systems"]
[null,"Electrical (including yard wiring)",{"$f":"IF(OR(C83>0,C84>0),process_cost*D126,0)","v":0},{"$f":"elect_pct","v":0.1},"Percentage is applied only to process cost. Building direct costs include electrical components."]
[null,"Instrumentation and Control",{"$f":"direct_cost*D127","v":0},{"$f":"i_c_pct","v":0}]
[null,"Contingency",{"$f":"IF(component_level=\"\",include_conting_low,IF(component_level=1,include_conting_medium,include_conting_high))*(direct_cost+addon_cost+SUM(C119:C127,C129:C134))*D128","v":0},{"$f":"IF(component_level=\"\",include_conting_low,IF(component_level=1,include_conting_medium,include_conting_high))*VLOOKUP(direct_cost,contingency_table_cl,3)*complexity","v":0},"Included for high cost systems only."]
[null,"Process Engineering",{"$f":"direct_cost*D129","v":60846.391523162602},{"$f":"CHOOSE(ss_cat2,eng_pct_small,eng_pct_medium,eng_pct_large)","v":0.2}]
[null,"Miscellaneous Allowance",{"$f":"direct_cost*D130","v":30423.195761581301},{"$f":"misc_pct","v":0.1},{"$f":"IF(design=1, \"Includes interconnection pipe fittings\",\"\")","v":"Includes interconnection pipe fittings"}]
[null,"Legal, Fiscal, and Administrative",{"$f":"direct_cost*D131","v":6084.6391523162602},{"$f":"legal_pct","v":0.02}]
[null,"Sales Tax",{"$f":"direct_cost*D132","v":0},{"$f":"tax_pct","v":0}]
[null,"Financing during Construction",{"$f":"direct_cost*D133","v":0},{"$f":"IF(ss_cat2=1,finance_pct_small,finance_pct)","v":0}]
[null,"Construction Management and GC Overhead",{"$f":"cm_cost","v":24851.785477079742}]
[null,"Total indirect capital cost",{"$f":"indirect_cost_mult*direct_cost+indirect_cost_add","v":138938.76958300962},null,"Total of indirect line items"]
[]
["TOTAL CAPITAL COST DETAILS"]
[null,"Assumption","Value","Guidance"]
[null,"City Index for Total Capital Cost",1,"User adjustable.  Can be used to adjust estimated costs to be more reflective of a specific geographical location"]
[]
[null,"Total Direct Capital Cost (from above)",{"$f":"direct_cost","v":304231.95761581301}]
[null,"Total Add-on Cost (from above)",{"$f":"addon_cost","v":0}]
[null,"Total Indirect Cost (from above)",{"$f":"C135","v":138938.76958300962}]
[null,"Grand total capital cost (incorporating city index)",{"$f":"(direct_cost*(1+SUM(cul_indir_mult_table))+SUM(cul_indir_add_table))*city_index","v":443170.72719882266}]
[]
["Annual Operating and Maintenance Cost Details (items in italics, without a 1 in the \"Use?\" column, represent alternate costs and are excluded from total)"]
[null,"Assumption","Value","Guidance"]
[null,"City Index for Total O&M Cost",1,"User adjustable. Can be used to adjust estimated costs to be more reflective of a specific geographical location"]
[]
[null,"Item","Quantity",null,"Unit Cost",null,"Total Cost","Use?","Guidance"]
[null,"Labor"]
[null,"Manager",{"$f":"Manager_LOE","v":9.1666666666666674E-2},"hrs/yr",{"$f":"VLOOKUP(design_flow,labor_table_cl,5)","v":50.648663101604278},"/hr",{"$f":"E152*C152","v":4.6427941176470595},{"$f":"IF(C152=0,0,1)","v":1}]
[null,"Administrative",{"$f":"Clerical_LOE","v":9.1666666666666674E-2},"hrs/yr",{"$f":"VLOOKUP(design_flow,labor_table_cl,4)","v":34.161577540106947},"/hr",{"$f":"E153*C153","v":3.1314779411764704},{"$f":"IF(C153=0,0,1)","v":1}]
[null,"Operator",{"$f":"Operator_LOE","v":0.91666666666666663},"hrs/yr",{"$f":"VLOOKUP(design_flow,labor_table_cl,6)","v":32.90061497326203},"/hr",{"$f":"E154*C154","v":30.158897058823527},{"$f":"IF(C154=0,0,1)","v":1},"Includes labor for regular operations, maintenance, regeneration, etc."]
[null,"Materials"]
[null,"Building maintenance (materials and labor)",{"$f":"total_fp","v":0},"sf",{"$f":"IF(C156=0,0,G156/C156)","v":0},"/sf/yr",{"$f":"bldg_maint_cost","v":0},{"$f":"IF(G156=0,0,1)","v":0}]
[null,"Purchased Water",{"$f":"IF(design=1, average_flow,0)* day_per_year *1000","v":2555},"K gal",{"$f":" water_cost","v":3.0595290784661366},"K gal",{"$f":"C157 * E157","v":7817.0967954809794},{"$f":"IF(C157>0,1,0)","v":1}]
[null,"Booster pump","calculated as a percentage of capital",null,null,null,{"$f":"pump_materials","v":0},{"$f":"IF(C158>0,1,0)","v":1}]
[null,"Well pump","calculated as a percentage of capital",null,null,null,{"$f":"well_pump_materials","v":0},{"$f":"IF(C159>0,1,0)","v":1}]
[null,"Energy"]
[null,"Energy for booster pumps",{"$f":"booster_pump_energy","v":0},"Mwh/yr",{"$f":"energy_cost_cl","v":0.11001750924784216},"/kwh",{"$f":"E161*C161*1000","v":0},{"$f":"IF(C161=0,0,1)","v":0}]
[null,"Energy for well pumps",{"$f":"well_pump_energy","v":0},"Mwh/yr",{"$f":"energy_cost_cl","v":0.11001750924784216},"/kwh",{"$f":"E162*C162*1000","v":0},{"$f":"IF(C162=0,0,1)","v":0}]
[null,"Miscellaneous Allowance",{"$f":"misc_pct","v":0.1},null,null,null,{"$f":"SUMPRODUCT(G152:G162,H152:H162)*C163","v":785.50299645986263},1]
[null,"Total Annual O&M Cost (incorporating city index)",null,null,null,null,{"$f":"SUMPRODUCT(G152:G163,H152:H163)*OM_City_Index","v":8640.5329610584886}]
[]
["ANNUALIZED COSTS"]
[null,"Assumption","Value","Guidance"]
[null,"Discount rate",7.0000000000000007E-2,"User adjustable"]
[null,"Calculate households based on:","ground water","User adjustable"]
[null,"Household size (people per household)",2.5299999999999998,"User adjustable"]
[]
[null,"Calculation","Value","Units"]
[null,"Useful Life (Reciprocal Weighted Average)",{"$f":"ROUND((direct_for_UL+IF(build_UL=0,0,addon_cost+indirect_cost))/(total_direct_depreciation+IF(build_UL=0,0,(addon_cost+indirect_cost)/build_UL)),1)","v":17},"years"]
[null,"Number of households served",{"$f":"IF(source_select=\"ground water\",(average_flow*1000/0.08558)^(1/1.0584),(average_flow*1000/0.14004)^(1/0.99703))/hh_size","v":25.355114086889319},"households"]
[null,"Annualized Capital Cost",{"$f":"-PMT(Discount_rate,useful_life,system_cost)","v":45391.847292618142},"/year"]
[null,"Annual O&M Cost",{"$f":"om_cost","v":8640.5329610584886},"/year"]
[null,"Total Annualized Cost",{"$f":"C175+C176","v":54032.380253676631},"/year",{"$f":"IF(ISERROR(OR(TAC_pct_cap,TAC_pct_OM)),\"\",TEXT(TAC_pct_cap,\"0%\")&\" annualized capital, \"&TEXT(TAC_pct_OM,\"0%\")&\" annual O&M\")","v":"84% annualized capital, 16% annual O&M"}]
[null,"Annualized cost per 1,000 gallons average flow",{"$f":"Annualized_Cost/(average_flow*365*1000)","v":21.147702643317665},"/kgal"]
[null,"Annualized cost per household",{"$f":"Annualized_Cost/households","v":2131.0249312431934},"/household/year"]
[]
["Internal use"]
[null,"Mobilization and Demobilization",{"$f":"C119","v":16732.757668869715}]
[null,"Architectural Fees for Treatment Building",{"$f":"C120","v":0}]
[null,"Installation, Transportation, and O&P",{"$f":"D121","v":0}]
[null,"Site Work",{"$f":"C122","v":0}]
[null,"Yard Piping",{"$f":"C123","v":0}]
[null,"Geotechnical",{"$f":"C124","v":0}]
[null,"Standby Power",{"$f":"C125","v":0}]
[null,"Electrical (including yard wiring)",{"$f":"C126","v":0}]
[null,"Instrumentation and Control",{"$f":"D127","v":0}]
[null,"Contingency",{"$f":"C128","v":0}]
[null,"Process Engineering",{"$f":"C129","v":60846.391523162602}]
[null,"Miscellaneous Allowance",{"$f":"D130","v":0.1}]
[null,"Legal, Fiscal, and Administrative",{"$f":"D131","v":0.02}]
[null,"Sales Tax",{"$f":"D132","v":0}]
[null,"Financing during Construction",{"$f":"D133","v":0}]
[null,"Construction Management and GC Overhead",{"$f":"C134","v":24851.785477079742}]
[null,"Indirect cost multiplier",{"$f":"SUM(cul_indir_mult_table)","v":0.12000000000000001}]
[null,"Indirect costs added",{"$f":"SUM(cul_indir_add_table)-addon_cost","v":102430.93466911206}]
[null,"Subtotal indirect and add-on cost",{"$f":"C135+addon_cost","v":138938.76958300962}]
[]
[null,"Validity Checks",{"$f":"INPUT!$C$28","v":true},{"$f":"INPUT!$C$36","v":true},{"$f":"INPUT!$C$43","v":true},{"$f":"INPUT!$C$44","v":true},{"$f":"INPUT!$C$53","v":true}]
[null,"Missing Cost?:",{"$f":"IF(COUNTIF(use_column,\"x\")>0,TRUE,FALSE)","v":false}]
[null,"Buildup Error?",{"$f":"IF(COUNTIF(use_column,\"ec\")+COUNTIF(use_column,\"ep\")>0,TRUE,FALSE)","v":false}]

{"sheet":"Critical Design Assumptions","index":4,"n_rows":58,"n_cols":7,"rows_kept":58,"cut":false,"hidden":false,"merged":[]}
["Default Critical Design Assumptions"]
["Users may change any of the cells in the Value column; cells in gold are most important and may vary based on contaminant treated and site-specific conditions"]
[]
["Assumption","Range Name","Value","Units","Guidance"]
["Cost estimating method","curve",1,"<-- enter 1 to use cost equations, leave blank to use lookup tables for component costs","Keeping this assumption with a value of 1 is strongly recommended; the cost equations method is more appropriate for national cost estimating and will be more accurate for nearly all user-specified designs"]
[]
["Minimum pipe size for new wells","min_pipe_size",2,"in. diam"]
["Minimum pipe size for interconnection","min_pipe_size_interconnect",4,"in. diam"]
["Minimum average flow as percentage of design","min_avg_flow",0.1,null,"The minimum ratio of average flow to design flow. If the ratio falls below this value, it will trigger a warning on the INPUT sheet. If this value is zero, no minimum is enforced."]
[]
["Minimum number of valves  (isolation PRV, street)","min_valves",2,"each"]
["Spacing for valves","valve_spacing",1000,"ft"]
["Valves per pump (booster and well)","valve_per_pump",2,"each"]
["Valves per PRV","valve_per_PRV",2,"each"]
[]
["Safety factor for pumps","pump_safety_factor",0.25]
["Number of redundant booster pumps","NRD_booster_pumps",0]
["Access space for pumps and PRVs","space_pumps",4,"feet","Provide a minimum of 3 ft (1 m) of clear space around each item of equipment (American Water Works Association and American Society of Civil Engineers. Water Treatment Plant Design. Third Edition. New York: McGraw Hill, 1998. Pp. 695)."]
["Number of redundant PRVs","NRD_PRVs",0]
["Minimum number of PRVs","min_num_PRVs",1,null,"If pressure reduction is required"]
["Include well pump soft start?","soft_start",1,null,"1 for yes, 0 for no"]
[]
["Well Design"]
["Thickness of gravel pack","gravel_thickness",3,"inches "]
["Thickness of grout seal","grout_thickness",4,"inches "]
["Depth of grout seal","seal_depth",30,"feet","typically 30-50 ft depending on local regulations"]
["Screen length to casing length ratio","screen_ration",0.5,null,"accounts for small sections of blank casing below and above well screen"]
["water pressure at top of well (for distribution)","supply_head",69,"feet","2.31 ft of water per 1 psi"]
["Footprint per well","well_footprint",200,"sqft"]
[]
["Interconnection Trenching "]
["Frost depth","frost",38,"inches"]
["Minimum trench depth to top of pipe","min_trench_depth",36,"inches"]
["Minimum trench width on each side of pipe","min_trench_width",12,"inches"]
["Asphalt thickness","asphalt_thickness",4,"inches"]
["Include asphalt patch","asphalt_patch_include",1,null,"1 for yes, 0 for no"]
[]
["Flow meters for interconnect line","fm_interconnect",1,"units"]
["Flow meters for each well","fm_well",1,"units"]
[]
["Non fire Buffer for buildings","non_fire_buffer",100,"feet","100 feet to account for sanitary control area around wellhead"]
["Fire Buffer for Buildings","fire_buffer",100,"feet","100 feet to account for sanitary control area around wellhead"]
[]
["Vault "]
["Vault concrete thickness","vault_concrete_thickness",6,"inches"]
["Interior hight of vault","vault_interior_height",8,"ft"]
["Minimum vault cover","vault_min_cover",1,"ft"]
[]
["I/O Port Table"]
["Full automation includes all inputs and outputs, semi-automated uses inputs only, manual control uses neither"]
["To change the degree of automation, the user may replace the numbers in each of the blue columns."]
["I/O Ports needed per….","Lookup name","Discrete Inputs","Discrete Outputs","Analog Inputs","Analog Outputs"]
["Magnetic flow meter","flow_mag",0,0,1,0]
["Orifice plate flow meter","flow_op",0,0,2,1,"Additional analog input because flow meters include pressure gauges"]
["Propeller flow meter","flow_prop",0,0,1,0]
["Venturi flow meter","flow_ven",0,0,2,1,"Additional analog input because flow meters include pressure gauges"]
["Drive Controllers (to operate pump motors, mixers, valves, etc)","switch",3,1,0,0]
["Motor/air operated valve","valve",0,0,1,1]

{"sheet":"O&M Assumptions","index":5,"n_rows":21,"n_cols":5,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Default O&M Assumptions"]
["Users may change any of the cells in the Value column"]
[]
["Assumption","Range Name","Value","Units","Guidance"]
["Labor"]
["Mangerial LOE as a percent of average technical labor ","Manager_percent",0.1,"fraction"]
["Administrative LOE as a percent of average technical labor ","Clerical_percent",0.1,"fraction"]
[]
["Operating LOE assumptions"]
["Minutes per valve per week","uloe_valve",5,"min/week","For manual and semiautomated systems"]
["Minutes per pump per day","uloe_pump",5,"min/day","For manual and semiautomated systems"]
["Minutes per 100 sf per day","uloe_fp",1,"min/100 sf/day"]
[]
["Maintenance LOE assumptions"]
["For valves","uloe_valve_maint",5,"min/year","Applied to main process valves only"]
["For pumps","uloe_pump_maint",30.25,"hours/year","Based on vendor recommended maintenance activities; applied to large, frequently operated pumps only"]
[]
["Pumping"]
["Pump efficiency for booster pumps","booster_pump_efficiency",0.75]
["Cost for parts & maintenance for booster pumps","booster_pump_maintenance",0.01,"of installed cost","For calculating cost of actual maintenance materials; not to be confused with depreciation.  Initial proposed estimate of 5% seems high  (Richard E. Hubel, Peter Keenan, American Water Works Service Company, Inc)"]
["Cost for parts & maintenance for well pumps","well_pump_maintenance",0.01,"of installed cost"]

{"sheet":"Indirect Assumptions","index":6,"n_rows":64,"n_cols":5,"rows_kept":64,"cut":false,"hidden":false,"merged":[]}
["Default Assumptions for Indirect and Add-On Costs"]
["Users may change any of the cells in the Value column"]
[]
["Assumption","Range Name","Value","Units","Guidance"]
["Mobilization and Demobilization"]
["Mobilization percentage for small systems","mob_pct_small",0.05,"of direct capital cost","Typically 2-5% of the estimated construction cost.  Enter 0% to exclude."]
["Mobilization percentage for medium systems","mob_pct_medium",0.04,"of direct capital cost"]
["Mobilization percentage for large systems","mob_pct_large",0.02,"of direct capital cost"]
[]
["Architectural Fees for Treatment Building"]
["Include architectural fee?","include_arch",1,"<--- enter 1 for yes, 0 for no","Must be added to building cost. Change for special considerations. Includes structural, mechanical, and electrical engineering for building only."]
["Include architectural fee for small systems?","include_arch_small",0,"<--- enter 1 for yes, 0 for no"]
[]
["Installation, Transportatation, and O&P"]
["Installation, Transportatation, and O&P percentage","inst_pct",0,"of direct capital cost","Included in line item unit costs.  Add a percentage for special considerations such as a remote site or high cost location."]
[]
["Sitework"]
["Include sitework costs?","include_sitework",1,"<--- enter 1 for yes, 0 for no"]
[]
["Electrical (including yard wiring)"]
["Electrical percentage","elect_pct",0.1,"of direct cost"]
[]
["Geotechnical"]
["Include geotechnical costs for low cost systems?","include_geo_low",1,"<--- enter 1 for yes, 0 for no"]
["Include geotechnical costs for medium cost systems?","include_geo_medium",1,"<--- enter 1 for yes, 0 for no"]
["Include geotechnical costs for high cost systems?","include_geo_high",1,"<--- enter 1 for yes, 0 for no"]
[]
["Instrumentation and Control"]
["Instrumentation and control percentage","i_c_pct",0,"of direct cost","Included as a direct cost line item."]
[]
["Contingency"]
["Include contingency costs for low cost systems?","include_conting_low",0,"<--- enter 1 for yes, 0 for no"]
["Include contingency costs for medium cost systems?","include_conting_medium",0,"<--- enter 1 for yes, 0 for no"]
["Include contingency costs for high cost systems?","include_conting_high",1,"<--- enter 1 for yes, 0 for no"]
["Technology complexity factor","complexity",0.5,null,"Technology-specific factor.  See Appendices for details.  Low complexity = 0.5; Average complexity = 1.0; High complexity = 1.5; Very high complexity = 2.0."]
[]
["Process Engineering"]
["Engineering percentage for small systems","eng_pct_small",0.2,"of direct capital cost","Enter 0% to exclude."]
["Engineering percentage for medium systems","eng_pct_medium",0.12,"of direct capital cost"]
["Engineering percentage for large systems","eng_pct_large",0.08,"of direct capital cost"]
[]
["Standby Power"]
["Include standby power costs?","include_standby",1,"<--- enter 1 for yes, 0 for no"]
["Include standby power costs for small systems?","include_standby_small",0,"<--- enter 1 for yes, 0 for no"]
["Design safety factor for standby power","std_safety_factor",0,null,"Default 0%.  Apply a percentage to increase standby power requirements."]
["Minimum standby power generator size","min_standby",1.5,"kW","Minimum size for full standby generators"]
[]
["Miscellaneous Allowance"]
["Miscellaneous allowance percentage","misc_pct",0.1,"of direct capital cost","Enter 0% to exclude."]
[]
["Legal, Fiscal, and Administrative"]
["Legal percentage","legal_pct",0.02,"of direct capital cost","Enter 0% to exclude.  Highly site-specific.  Can range from 2-5% of equipment, installation, construction, electrical, and standby power.  To estimate, add 5% to total estimated construction cost."]
[]
["Sales Tax"]
["Sales tax percentage","tax_pct",0,"of direct capital cost","May be exempt if constructed with public funds.  May be as high as 7.25% of equipment and construction, with national average 4.75%."]
[]
["Financing during Construction"]
["Financing percentage","finance_pct",0.05,"of direct capital cost","Enter 0% to exclude.  Typically assumed to be one year's interest on capital cost."]
["Financing percentage for small systems","finance_pct_small",0,"of direct capital cost","Small system construction assumed to take less than one year"]
[]
["Construction Management and General Contractor Overhead"]
["Builder's risk insurance percentage","br_ins_pct",3.3999999999999998E-3,null,"Enter 0% to exclude. 0.34% is national average rate. RSMeans Building Construction Cost Data 2009, reference section R01100-070"]
["Include performance bond cost?","include_bond",1,"<--- enter 1 for yes, 0 for no"]
["Include construction management cost?","include_cm",1,"<--- enter 1 for yes, 0 for no"]

{"sheet":"Well Design","index":7,"n_rows":31,"n_cols":5,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["Well Design Module"]
[]
["Calculation","Range Name","Value","Units","Guidance"]
["From Inputs"]
["Design Type","design",1,"1 for interconnection, 2 for new well construction "]
["Design Flow","design_flow",{"$f":"IF(df_units=\"gpm\",design_flow_I*24*60/1000000,design_flow_I)","v":0.03},"MGD"]
["Average Flow","average_flow",{"$f":"IF(af_units=\"gpm\",average_flow_I*24*60/1000000,IF(af_units=\"fraction of design flow\",average_flow_I*design_flow,average_flow_I))","v":7.0000000000000001E-3},"MGD"]
["Design Flow Rate","design_flow_rate",{"$f":"design_flow*1000000/(24*60)","v":20.833333333333332},"gpm","Design flow expressed in gpm"]
["Average Flow Rate","average_flow_rate",{"$f":"average_flow*1000000/(24*60)","v":4.8611111111111107},"gpm","Average flow expressed in gpm"]
[]
["Number of wells","wells",{"$f":"IF(design=1,0,ROUNDUP(design_flow_rate/wells_I,0))","v":0}]
["Well Depth","well_depth",{"$f":"well_depth_I","v":0},"feet"]
["Maxiumum well pump horsepower","well_pump_hp_max",{"$f":"MAX('Lookup Tables'!B7:B9)","v":250},"hp"]
[]
["Well Design"]
["Casing Diameter","casing_diam",{"$f":"IF(design=1,0,pump_diam+3)","v":0},"inches","Casing diameter 3 inches larger than largest pump componenent"]
["Casing Length","casing_len",{"$f":"well_depth","v":0},"feet"]
["Gravel Pack Length","gravel_len",{"$f":"IF(design=1,0,casing_len-seal_depth)","v":0},"feet","Casing length minus grout seal length"]
["Grout Seal Cross Sectional Area","grout_area",{"$f":"IF(design=1,0,(PI()*(casing_diam/2+grout_thickness)^2)-(PI()*(casing_diam/2)^2))","v":0},"square inches"]
["Grout Seal Volume per Well","grout_vol_well",{"$f":"grout_area/144*seal_depth","v":0},"cubic feet"]
["Total Grout Seal Volume","grout_vol",{"$f":"grout_vol_well*wells","v":0},"cubic feet"]
["Screen Diamater","screen_diam",{"$f":"casing_diam","v":0},"inches"]
["Screen Length","screen_len",{"$f":"casing_len*screen_ration","v":0},"feet"]
["Borehole Diameter","borehole_diam",{"$f":"IF(design=1,0,2*pump_diam+gravel_thickness)","v":0},"inches","2*pump diameter diameter plus gravel pack thickness"]
["Plug diameter","plug_diam",{"$f":"screen_diam","v":0},"inches"]
[]
["Well drilling unit cost, 8\" borehole diameter",null,{"$f":"VLOOKUP(well_depth,drill_8_cost_cl,3)","v":122.0818644470868},"per linear foot"]
["Well drilling unit cost, 10\" borehole diameter",null,{"$f":"VLOOKUP(well_depth,drill_10_cost_cl,3)","v":166.64412841854934},"per linear foot"]
["Well drilling unit cost, 12\" borehole diameter",null,{"$f":"VLOOKUP(well_depth,drill_12_cost_cl,3)","v":193.78256361474436},"per linear foot"]
["Well drilling unit cost, 15\" borehole diameter",null,{"$f":"VLOOKUP(well_depth,drill_15_cost_cl,3)","v":263.54362901307968},"per linear foot"]
["Well drilling unit cost","well_drill_cost",{"$f":"IF(design=1,\"na\",IF(borehole_diam<=8,C27,IF(borehole_diam<=10,C28,IF(borehole_diam<=12,C29,C30))))","v":"na"},"per linear foot"]

{"sheet":"Pumps Pipe Structure","index":8,"n_rows":104,"n_cols":5,"rows_kept":104,"cut":false,"hidden":false,"merged":[]}
["Pumps, Piping, and Structure Calculations"]
[]
["Calculation","Range Name","Value","Units","Guidance"]
["Interconnection Booster Pump Design"]
["Pressure at Supply Source (from inputs)","buyer_pressure",{"$f":"IF(buyer_pressure_I=\"correct pressure\",0,IF(buyer_pressure_I=\"pressure greater than required\",1,2))","v":0},null,"0 for correct pressure, 1 for excess pressure, 2 for insufficient pressure"]
["Elevation Difference from Supply Source","elevation_head",{"$f":"elevation_diff_I","v":0},"head in feet","From Inputs, Booster pump pressure must account for this difference"]
["Hazen-Williams friction loss","HW_friction_loss",{"$f":"IF(design=2,\"\",0.2083*(100/IF(OUTPUT!L54=1,130,140))^1.852*design_flow_rate^1.852/interconnect_pipe_diameter^4.8655)","v":4.1752695868709139E-2},"head loss in feet per 100 ft pipe"]
["Total pressure needed to overcome friction losses","friction_head",{"$f":"IF(design=2,\"\",interconnect_pipe_length/100*HW_friction_loss)","v":4.1752695868709138},"head loss in feet"]
["Total head needed for booster pump","booster_additional_head",{"$f":"IF(design=2,\"\",elevation_head+friction_head)","v":4.1752695868709138},"head in feet"]
["Booster pump pressure rating","booster_psi",{"$f":"IF(design=2,0,0.434*booster_additional_head)","v":1.8120670007019766},"psi","0.434 converts head in feet to psi"]
[]
["Maximum pump size, 25 psi",null,{"$f":"VLOOKUP(10^6,pump_cost_cl,1)-1","v":10000},"gpm"]
["Maximum pump size, 75 psi",null,{"$f":"VLOOKUP(10^6,dis75_pump_cost_cl,1)-1","v":10000},"gpm"]
["Maximum pump size, 150 psi",null,{"$f":"VLOOKUP(10^6,ro150_pump_cost_cl,1)-0.1","v":2000},"gpm"]
["Maximum pump size, 200 psi",null,{"$f":"VLOOKUP(10^6,ro200_pump_cost_cl,1)-0.1","v":2000},"gpm"]
["Maximum pump size, 250 psi",null,{"$f":"VLOOKUP(10^6,ro250_pump_cost_cl,1)-0.1","v":2000},"gpm"]
["Maximum pump size, 300 psi",null,{"$f":"VLOOKUP(10^6,ro300_pump_cost_cl,1)-0.1","v":250},"gpm"]
["Maximum pump size, 350 psi",null,{"$f":"VLOOKUP(10^6,ro350_pump_cost_cl,1)-0.1","v":225},"gpm"]
["Maximum pump size, 500 psi",null,{"$f":"VLOOKUP(10^6,ro500_pump_cost_cl,1)-0.1","v":75},"gpm"]
["Maximum pump size","max_pump_size",{"$f":"IF(booster_psi<=25,C12,IF(booster_psi<=75,C13,IF(booster_psi<=150,C14,IF(booster_psi<=200,C15,IF(booster_psi<=250,C16,IF(booster_psi<=300,C17,IF(booster_psi<=350,C18,C19)))))))","v":10000},"gpm","The largest pump size for which costs are available."]
["Booster pump rating","booster_pump_rating",{"$f":" IF( OR( design<> 1,buyer_pressure<>2),0,MAX(design_flow_rate*(1+pump_safety_factor),max_pump_size))","v":0},"gpm"]
["Number of booster pumps","booster_pumps",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, IF(booster_pump_rating=0,0,ROUNDUP(MIN(design_flow_rate/max_pump_size,(design_flow_rate)*(1+pump_safety_factor)/booster_pump_rating ),0)))","v":0},null,"Pumps from seller to buyer"]
["Total number of booster pumps","total_booster_pumps",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0,(booster_pumps +NRD_booster_pumps))","v":0},"units"]
[]
["Pump length and width, 25 and 75 psi",null,{"$f":"VLOOKUP(booster_pump_rating,pump_dim_table_cl,4)","v":2.5},"feet"]
["Pump length and width, high pressure",null,{"$f":"SQRT(VLOOKUP(booster_pump_rating*60*24/10^6,inf_dim_table_cl,3))","v":6},"feet"]
["Pump length and width","booster_pump_L&W",{"$f":"IF(booster_psi<=75,C25,C26)","v":2.5},"feet"]
[]
["Booster pump cost, 25 psi",null,{"$f":"VLOOKUP(booster_pump_rating,pump_cost_cl,3)","v":3765.081740826402}]
["Booster pump cost, 75 psi",null,{"$f":"VLOOKUP(booster_pump_rating,dis75_pump_cost_cl,3)","v":3653.3939778012004}," "]
["Booster pump cost, 150 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro150_pump_cost_cl,3)","v":4107.7838744247119}]
["Booster pump cost, 200 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro200_pump_cost_cl,3)","v":4463.8258470658184}]
["Booster pump cost, 250 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro250_pump_cost_cl,3)","v":4985.0841587509367}]
["Booster pump cost, 300 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro300_pump_cost_cl,3)","v":5200.6544702091669}]
["Booster pump cost, 350 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro350_pump_cost_cl,3)","v":5200.6544702091669}]
["Booster pump cost, 500 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro500_pump_cost_cl,3)","v":6603.2988529312988}]
["Booster pump cost","booster_pump_cost",{"$f":"IF(booster_psi<=25,C29,IF(booster_psi<=75,C30,IF(booster_psi<=150,C31,IF(booster_psi<=200,C32,IF(booster_psi<=250,C33,IF(booster_psi<=300,C34,IF(booster_psi<=350,C35,C36)))))))","v":3765.081740826402},null,"This value used with the lookup table cost estimating method (i.e., when the critical design assumption curve <> 1). See the cost equations page when curve = 1"]
[]
["Booster pump size used in estimate, 25 psi",null,{"$f":"VLOOKUP(booster_pump_rating,pump_cost_cl,2)","v":10}]
["Booster pump size used in estimate, 75 psi",null,{"$f":"VLOOKUP(booster_pump_rating,dis75_pump_cost_cl,2)","v":4}," "]
["Booster pump size used in estimate, 150 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro150_pump_cost_cl,2)","v":5}]
["Booster pump size used in estimate, 200 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro200_pump_cost_cl,2)","v":5}]
["Booster pump size used in estimate, 250 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro250_pump_cost_cl,2)","v":5}]
["Booster pump size used in estimate, 300 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro300_pump_cost_cl,2)","v":5}]
["Booster pump size used in estimate, 350 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro350_pump_cost_cl,2)","v":5}]
["Booster pump size used in estimate, 500 psi",null,{"$f":"VLOOKUP(booster_pump_rating,ro500_pump_cost_cl,2)","v":5}]
["Booster pump size used in estimate","booster_pump_size_used",{"$f":"IF(booster_psi<=25,C39,IF(booster_psi<=75,C40,IF(booster_psi<=150,C41,IF(booster_psi<=200,C42,IF(booster_psi<=250,C43,IF(booster_psi<=300,C44,IF(booster_psi<=350,C45,C46)))))))","v":10},null,"This value used with the lookup table cost estimating method (i.e., when the critical design assumption curve <> 1). The cost equations method uses exact size"]
[]
["Well Pump Design"]
["Number of well pumps","well_pumps",{"$f":"IF(design=1,0,wells)","v":0},"units"]
["Well pump rating","well_pump_rating",{"$f":"IF(design=1,0,design_flow_rate/well_pumps)","v":0},"gpm"]
["Well pump pressure requirements","well_pump_head",{"$f":"IF(design=1,0,well_depth+supply_head)","v":0},"feet"]
["Well pump horsepower","well_pump_hp",{"$f":"IF(design<>2,\"NA\",well_pump_head*well_pump_rating*(1+pump_safety_factor)/3960)","v":"NA"},"hp "]
["Well pump diameter","pump_diam",{"$f":"IF(design<>2,0,VLOOKUP(well_pump_hp,pump_hp_table,3))","v":0},"inches"]
[]
["Interconnection piping"]
["Interconnect pipe length","interconnect_pipe_length",{"$f":"IF(design=1,pipe_length_I,0)","v":10000},"ft"]
["Interconnect pipe diameter","interconnect_pipe_diameter",{"$f":"IF( OR( design_flow_rate=0, design<>1 ),0, MAX(min_pipe_size_interconnect, VLOOKUP((design_flow_rate),pipe_size_table_cl,2)))","v":4},"in"]
[]
["InterconnectionTrench Calculations"]
["Interconnect pipe trench depth to top of pipe","interconnect_trn_depth",{"$f":"IF(design=2,0,MAX(frost,min_trench_depth))","v":38},"inches"]
["Interconnect pipe trench cross-section area","interconnect_trench_area",{"$f":"IF(interconnect_pipe_diameter=0,0,(interconnect_pipe_diameter/12+2*min_trench_width/12)*interconnect_trn_depth/12)","v":7.3888888888888893},"square ft"]
["Interconnect pipe trench volume total","interconnect_trench_vol",{"$f":"interconnect_trench_area*interconnect_pipe_length/27","v":2736.6255144032921},"cy"]
["Asphalt patch area","asphalt_patch_area",{"$f":" IF( asphalt_patch_include=1,( interconnect_pipe_length * (interconnect_pipe_diameter/12 + 2*min_trench_width/12))/sqft_per_sqyd,0)","v":864.19753086419757},"square yards"]
[]
["Well piping"]
["Single well pipe length","well_pipe_length",{"$f":"IF( design=2,well_distance_I,0)","v":0},"ft","Pipe to connect new well to existing distribution system"]
["Well pipe diameter","well pipe diameter",{"$f":"IF( OR( design_flow_rate=0,design=1  ),0, MAX( min_pipe_size, VLOOKUP((design_flow_rate/wells),pipe_size_table_cl,2)))","v":0},"in"]
["Total well piping length","total_well_pipe_length",{"$f":"well_pipe_length*wells","v":0},"ft"]
[]
["Well to Distribution Trench Calculations"]
["Well pipe trench depth to top of pipe","well_trn_depth",{"$f":"MAX(frost,min_trench_depth)","v":38},"inches"]
["Well pipe trench cross-section area","well_trench_area",{"$f":"IF(well_pipe_diameter=0,0,(well_pipe_diameter/12+2*min_trench_width/12)*well_trn_depth/12)","v":0},"square ft"]
["Well pipe trench volume total","well_trench_vol",{"$f":"well_trench_area*total_well_pipe_length/27","v":0},"cy"]
[]
["Pump Vault Design"]
["Interior length","pump_vault_int_length",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, 2 * space_pumps + booster_pump_L_W)","v":0},"ft","."]
["Interior width","pump_vault_int_width",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, 2 * space_pumps + booster_pump_L_W + (  (total_booster_pumps-1) * space_pumps )  )","v":0},"ft"]
["Pump vault length concrete volume","pump_vault_length_conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0,( (pump_vault_int_length + 2 * vault_concrete_thickness / in_per_ft)* (vault_interior_height + 2 * vault_concrete_thickness / in_per_ft  ))  * vault_concrete_thickness / in_per_ft     /cuft_per_cuyd )","v":0},"cy"]
["Pump vault floor concrete volume","pump_vault_floor conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, (pump_vault_int_width * pump_vault_int_length* vault_concrete_thickness / in_per_ft )/cuft_per_cuyd )","v":0},"cy"]
["Pump vault width concrete volume","pump_vault_width_conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0,  pump_vault_int_width * (vault_interior_height + 2 * vault_concrete_thickness / in_per_ft ) *  vault_concrete_thickness / in_per_ft  / cuft_per_cuyd )","v":0},"cy"]
["Pump vault total concrete volume","pump_vault_total_conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, SUM(C79:C81) )","v":0},"cy"]
["Pump vault excavation depth","pump_vault_exc_depth",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, vault_concrete_thickness * 2 /in_per_ft + vault_interior_height + vault_min_cover )","v":0},"ft"]
["Pump vault excavation volume","pump_vault_exc_volume",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0,( ( (pump_vault_int_width  + vault_concrete_thickness/ in_per_ft * 2  + pump_vault_exc_depth) * (pump_vault_int_length + pump_vault_exc_depth + vault_concrete_thickness/ in_per_ft ) * (pump_vault_exc_depth  ) )/cuft_per_cuyd ) )","v":0},"cy"]
["Pump vault backfill volume","pump_vault_backfill_volume",{"$f":" IF( OR( design<> 1,buyer_pressure<>2), 0, (pump_vault_exc_volume  -  (  ( (pump_vault_int_width + 2 * vault_concrete_thickness/ in_per_ft ) * (pump_vault_int_length + 2 * vault_concrete_thickness/ in_per_ft) *(pump_vault_exc_depth +vault_concrete_thickness/ in_per_ft) ) / cuft_per_cuyd )))","v":0},"cy"]
[]
["Vault for PRVs"]
["Interior length","prv_vault_int_length",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0, 2 * space_pumps +PRV_length / in_per_ft)","v":0},"ft"]
["Interior width","prv_vault_int_width",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0, 2 * space_pumps + PRV_width / in_per_ft + (  (num_PRVs-1) * space_pumps )  )","v":0},"ft"]
["PRV vault length concrete volume","prv_vault_length_conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0,( (prv_vault_int_length + 2 * vault_concrete_thickness / in_per_ft)* (vault_interior_height + 2 * vault_concrete_thickness / in_per_ft  ))  * vault_concrete_thickness / in_per_ft     /cuft_per_cuyd )","v":0},"cy"]
["PRV vault floor concrete volume","prv_vault_floor conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0, (prv_vault_int_width * prv_vault_int_length* vault_concrete_thickness / in_per_ft )/cuft_per_cuyd )","v":0},"cy"]
["PRV vault width concrete volume","prv_vault_width_conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0,  prv_vault_int_width * (vault_interior_height + 2 * vault_concrete_thickness / in_per_ft ) *  vault_concrete_thickness / in_per_ft  / cuft_per_cuyd )","v":0},"cy"]
["PRV vault total concrete volume","prv_vault_total_conc_vol",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0, SUM(C90:C92) )","v":0},"cy"]
["PRV vault excavation depth","prv_vault_exc_depth",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0, vault_concrete_thickness * 2 /in_per_ft + vault_interior_height + vault_min_cover )","v":0},"ft"]
["PRV vault excavation volume","prv_vault_exc_volume",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0,( ( (prv_vault_int_width  + vault_concrete_thickness/ in_per_ft * 2  + prv_vault_exc_depth) * (prv_vault_int_length + prv_vault_exc_depth + vault_concrete_thickness/ in_per_ft ) * (prv_vault_exc_depth  ) )/cuft_per_cuyd ) )","v":0},"cy"]
["PRV vault backfill volume","prv_vault_backfill_volume",{"$f":" IF( OR( design<> 1,buyer_pressure<>1), 0, (prv_vault_exc_volume  -  (  ( (prv_vault_int_width + 2 * vault_concrete_thickness/ in_per_ft ) * (prv_vault_int_length + 2 * vault_concrete_thickness/ in_per_ft) *(prv_vault_exc_depth +vault_concrete_thickness/ in_per_ft) ) / cuft_per_cuyd )))","v":0},"cy"]
[]
["Footprint"]
["Well footprint","well_fp",{"$f":"IF(design=1,0,wells*well_footprint)","v":0},"square feet"]
["Footprint of building 1","build1_fp",{"$f":" IF(design<>2,0,well_fp)","v":0},"square feet"]
["Total footprint","total_fp",{"$f":"build1_fp","v":0},"square feet"]
[]
["Land acquisition required","land_required",{"$f":" IF(design<>2,0, ((build1_fp)^0.5+(2*non_fire_buffer))*((build1_fp)^0.5 + (non_fire_buffer+fire_buffer)) /43560)","v":0},"acres"]
["Land acquisition cost","land_cost",{"$f":"VLOOKUP(design_flow,land_cost_cl,3)","v":21165.713250209614},"/acre"]

{"sheet":"Instrumentation and Control","index":9,"n_rows":28,"n_cols":5,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["Instrumentation and Control Calculations"]
[]
["Calculation","Range Name","Value","Units","Guidance"]
["From Inputs"]
["Component level","component_level",{"$f":"IF(component_level_I=\"high cost\",2,IF(component_level_I=\"mid cost\",1,\"\"))","v":""},"blank = low cost, 1 = mid cost, 2 = high cost"]
["Manual system controls","manual",{"$f":"IF(manual_I=\"manual\",1,IF(manual_I=\"semi-automated\",2,IF(manual_I=\"fully automated\",3,IF(component_level=\"\",1,IF(component_level=2,3,CHOOSE(ss_cat2,1,3,3))))))","v":3},"1 = fully manual system, 2 = semi-automated system, 3 = fully automated system.","Based on system size and component level (see cell comment), unless user overrides on input sheet"]
[]
["Valves and Instrumentation"]
["Valve size","valve_size",{"$f":"IF(  design_flow_rate=0, 0, IF( design=2, well_pipe_diameter,interconnect_pipe_diameter))","v":4},"in"]
["Number of valves (isolation, street)","num_valves",{"$f":"IF(design= 2, 0, min_valves+  num_PRVs* valve_per_PRV  + ROUND(interconnect_pipe_length/valve_spacing,0)-1)","v":11},"units"]
[]
["Number of MOVs for isolating booster pumps","total_booster_MOV",{"$f":" IF( manual =3,  total_booster_pumps * valve_per_pump, 0)","v":0},"units"]
["Number of MOVs for well pump","total_well_MOV",{"$f":" IF( manual =3,  well_pumps * valve_per_pump, 0)","v":0},"units"]
[]
["Number of manual valves for isolating booster pumps","total_booster_man",{"$f":" IF( manual <> 3,  total_booster_pumps * valve_per_pump, 0)","v":0},"units"]
["Number of manual valves for well pump","total_well_man",{"$f":" IF( manual <> 3,  well_pumps * valve_per_pump, 0)","v":0},"units"]
[]
["Pressure Reducing Valve size","PRV_size",{"$f":"IF( OR( design_flow_rate=0, design<>1, buyer_pressure<>1 ),0,VLOOKUP((interconnect_pipe_diameter),prv_table,2))","v":0},"in"]
["Number of Pressure Reducing Valves","num_PRVs",{"$f":"IF( OR( design_flow_rate=0, design<>1, buyer_pressure<>1 ),0,min_num_PRVs + NRD_PRVs)","v":0},"units"]
["Length of PRV","PRV_length",{"$f":"IF( OR( design_flow_rate=0, design<>1, buyer_pressure<>1 ),0, VLOOKUP((interconnect_pipe_diameter),prv_table,3))","v":0},"in"]
["Width of PRV","PRV_width",{"$f":"IF( OR( design_flow_rate=0, design<>1, buyer_pressure<>1 ),0,VLOOKUP((interconnect_pipe_diameter),prv_table,4) )","v":0},"in"]
[]
["Total flow meters for interconnect line","tot_fm_interconnect",{"$f":"IF( design=1,fm_interconnect,0)","v":1},"units"]
["Flow meters for each well","tot_fm_well",{"$f":"IF( design=2,fm_well*wells,0)","v":0},"units"]
["Size of flow meters interconnect","interconnect_meter_size",{"$f":"IF( design=1, interconnect_pipe_diameter,0)","v":4},"in"]
["Size of flow meters well","well_meter_size",{"$f":"IF( design=2,well_pipe_diameter,0)","v":0},"in"]
["Number of remote telemetry units (RTUs)","total_RTU",{"$f":"tot_fm_interconnect+tot_fm_well","v":1},"units"]
["Number of well pump soft start controls","tot_soft_start",{"$f":"soft_start*wells","v":0},"units"]

{"sheet":"O&M","index":10,"n_rows":51,"n_cols":5,"rows_kept":51,"cut":false,"hidden":false,"merged":[]}
["Operation and Maintenance Calculations"]
[]
["Calculation","Range Name","Value","Units","Guidance"]
["Labor and Materials"]
["Cost of Purchased Water","water_cost",{"$f":"IF(water_cost_I=\"\",wholesale_water_cost_cl,water_cost_I)","v":3.0595290784661366},"$/1000 gal"]
[]
["Maintenance Materials"]
["Materials for booster pumps","pump_materials",{"$f":" IF( ISNUMBER(OUTPUT!J83), booster_pump_maintenance * OUTPUT!J83,0)","v":0},"/yr"]
["Materials for well pumps","well_pump_materials",{"$f":" IF( ISNUMBER(OUTPUT!J84), well_pump_maintenance * OUTPUT!J84,)","v":0}]
["Materials for PRVs"]
[]
["General Labor"]
["Number of booster pumps for operation","tot_pumps",{"$f":"booster_pumps","v":0},"units","Pumps requiring daily observation/adjustment"]
["Total number of booster pumps for maintenance","tot_pumps_maint",{"$f":"booster_pumps","v":0},"units","Large, frequently operated pumps require preventative maintenance"]
["Number of well pumps for operation","tot_well_pumps",{"$f":"well_pumps","v":0},"units","Pumps requiring daily observation/adjustment"]
["Total number of well pumps for maintenance","tot_well_pumps_maint",{"$f":"well_pumps","v":0},"units","Large, frequently operated pumps require preventative maintenance"]
["Number of valves","tot_valves",{"$f":"num_valves + total_booster_MOV + total_well_MOV+ total_booster_man","v":11},"units","Main process valves require frequent operation and preventative maintenance"]
["Number of PRVs","tot_PRVs",{"$f":"num_PRVs","v":0},"units"]
[]
["Verify/adjust booster pump operating parameters",null,{"$f":"IF(OR(manual=1,manual=2),tot_pumps*uloe_pump*365/60,0)","v":0},"hr/yr"]
["Preventative maintenance of booster pumps",null,{"$f":"tot_pumps_maint*uloe_pump_maint","v":0},"hr/yr"]
["Verify/adjust valve positions",null,{"$f":"IF(OR(manual=1,manual=2),tot_valves*uloe_valve*52/60,0)","v":0},"hr/yr"]
["Preventative maintenance and inspection of valves",null,{"$f":"tot_valves*uloe_valve_maint/60","v":0.91666666666666663},"hr/yr"]
["Visual inspection of facility",null,{"$f":"total_fp/100*uloe_fp*365/60","v":0},"hr/yr"]
["Preventative maintenance and inspection of PRVs",null,{"$f":"tot_PRVs*uloe_valve_maint/60","v":0},"hr/yr"]
["Verify/adjust well pump operating parameters",null,{"$f":"IF(OR(manual=1,manual=2),tot_well_pumps*uloe_pump*365/60,0)","v":0},"hr/yr"]
["Preventative maintenance of well pumps",null,{"$f":"tot_well_pumps_maint*uloe_pump_maint","v":0},"hr/yr"]
[]
["General Operator Labor","General_oper_labor",{"$f":"SUM(C20:C27)","v":0.91666666666666663},"hr/yr"]
[]
["Annual Operator LOE","Operator_LOE",{"$f":"General_oper_labor","v":0.91666666666666663},"hr/yr"]
["Annual Manager LOE","Manager_LOE",{"$f":"Operator_LOE*Manager_percent","v":9.1666666666666674E-2},"hr/yr"]
["Annual Administrative LOE","Clerical_LOE",{"$f":"Operator_LOE*Clerical_percent","v":9.1666666666666674E-2},"hr/yr"]
[]
[]
["Building Maintenance"]
["Cost for all buildings",null,{"$f":"IF(include_buildings=0,0,building_matl_rate_cl*total_fp)","v":0},"/yr"]
["Annual maintenance for all buildings","bldg_maint_cost",{"$f":"C37","v":0},"/yr"]
[]
["Energy"]
["Booster pumps"]
["Pump operating horsepower",null,{"$f":"IF(booster_pumps=0,0,average_flow_rate*booster_psi/booster_pumps/booster_pump_efficiency/14.7*33.9*8.34/550/60)","v":0},"hp"]
["Energy per pump",null,{"$f":"C42*0.7457/1000*24*day_per_year","v":0},"MWh/yr"]
["Energy for all active booster pumps","booster_pump_energy",{"$f":"C43*booster_pumps","v":0},"MWh/yr"]
[]
["Well pumps"]
["Pump operating horsepower",null,{"$f":"IF(design<>2,0,well_pump_head*average_flow_rate/well_pumps*(1+pump_safety_factor)/3960)","v":0},"hp"]
["Energy per pump",null,{"$f":"C47*0.7457/1000*24*day_per_year","v":0},"MWh/yr"]
["Energy for all active well pumps","well_pump_energy",{"$f":"C48*tot_well_pumps","v":0},"MWh/yr"]
[]
["System Size","ss_cat2",{"$f":"IF(design_flow<1,1,IF(design_flow<10,2,3))","v":1},"1 indicates small system, 2 indicates medium, 3 indicates large"]

{"sheet":"Indirect","index":11,"n_rows":40,"n_cols":5,"rows_kept":40,"cut":false,"hidden":false,"merged":[]}
["Indirect and Add-on Cost Calculations"]
[]
["Calculation","Range Name","Value","Units","Guidance"]
["Buildings & Land"]
["Include buildings in total capital cost?","include_buildings",{"$f":"IF(OR(design=1,include_buildings_I=\"no\"),0,1)","v":0},"1 = yes, 0 = no"]
["Include land costs?","include_land",{"$f":"IF(OR(design=1,include_land_I=\"no\"),0,1)","v":0},"1 = yes, 0 = no"]
[]
["Standby Power"]
["Booster pumps",null,{"$f":"booster_pump_energy*design_flow/average_flow/day_per_year/24*1000","v":0},"kW"]
["Well pumps",null,{"$f":"well_pump_energy*design_flow/average_flow/day_per_year/24*1000","v":0},"kW"]
["Total base standby power requirement","std_base",{"$f":"IF((C9+C10)=0,0,ROUNDUP(2*MAX(min_standby,(C9+C10)*(1+std_safety_factor)),0)/2)","v":0},"kW"]
["Total standby power cost","std_power_cost",{"$f":"VLOOKUP(std_base,std_gen_cost_cl,3)*std_base","v":0}]
[]
["Geotechnical & Sitework"]
["Total sitework cost","sitework_cost",{"$f":"total_fp*sitework_sf_cl","v":0}]
[]
["Construction Management and General Contractor Job Overhead"]
["Builder's risk insurance percentage",null,{"$f":"br_ins_pct*direct_cost","v":1034.3886558937643}]
["Performance bond cost",null,{"$f":"include_bond*(VLOOKUP(direct_cost,performance_bond_table_cl,3)+VLOOKUP(direct_cost,performance_bond_table_cl,4)*(direct_cost-VLOOKUP(direct_cost,performance_bond_table_cl,5)))","v":5563.4793642371951},null,"Bracket determined from direct capital cost"]
["Construction management percentage",null,{"$f":"VLOOKUP(direct_cost,construction_mgt_table_cl,3)","v":0.06},null,"Looked up from direct capital cost"]
["Construction management fee",null,{"$f":"include_cm*C20*direct_cost","v":18253.91745694878}]
["Total construction management/job overhead cost","cm_cost",{"$f":"SUM(C18:C19,C21)","v":24851.785477079742}]
[]
["Borings, initial field stake out & determination of elevations",null,{"$f":"wells*VLOOKUP(0,stake_out_cost_cl,3)","v":0}]
["Borings, drawings showing boring details",null,{"$f":"wells*VLOOKUP(0,bore_draw_cost_cl,3)","v":0}]
["Borings, report and recommendations from P.E.",null,{"$f":"wells*VLOOKUP(0,bore_report_cost_cl,3)","v":0}]
["Borings, mobilization and demobilization, minimum",null,{"$f":"wells*VLOOKUP(0,bore_mob_cost_cl,3)","v":0}]
["Borings, drill rig and crew with truck mounted auger",null,{"$f":"wells*VLOOKUP(0,bore_rig_cost_cl,3)","v":0}]
["Borings, cased borings in earth, w/samples, 2.5\" dia",null,{"$f":"wells*well_depth*VLOOKUP(0,bore_cost_cl,3)","v":0}]
["Total geotechnical cost","geotech_cost",{"$f":"SUM(C24:C29)","v":0}]
[]
[]
["Permits"]
["Building #1 value",null,{"$f":"IF(OUTPUT!C102>0,SUMPRODUCT(OUTPUT!J102:J102,OUTPUT!L102:L102),0)","v":0},null,"Determined using Use? Column of output sheet"]
["Building permit cost",null,{"$f":"VLOOKUP(design_flow,UBC_mult_table_cl,3)*(BPCost_UBC97(C34))","v":0},null,"Uses custom function to apply formula and multiplier documented in Abt Associates, 2020, Permitting Requirement Costs for Drinking Water Treatment Cost Models. Prepared for USEPA. October."]
["Total permit cost","permit_cost",{"$f":"C35","v":0}]
[]
["Percent Contributions to Annualized Cost"]
["Total annualized cost percent capital","TAC_pct_cap",{"$f":"Annualized_Capital_Cost/Annualized_Cost","v":0.84008602026244172}]
["Total annualized cost percent O&M","TAC_pct_OM",{"$f":"om_cost/Annualized_Cost","v":0.15991397973755828}]

{"sheet":"Standard Inputs","index":12,"n_rows":21,"n_cols":20,"rows_kept":21,"cut":false,"hidden":false,"merged":[[5,0,5,1],[6,18,6,19],[5,2,5,5],[5,13,5,16],[5,6,5,9],[5,11,5,12]]}
["Standard Designs"]
["Default inputs represent typical options used to generate national average compliance costs."]
[]
[]
[]
["Design Indices",null,"System Size",null,null,null,"Interconnection",null,null,null,null,"Well Inputs",null,"Optional"]
["Design Type","Design #","Design flow","Design Flow Units","Average Flow","Average flow units","Distance to Water Supply","Pressure at Supply Source","Elevation Difference from Supply Source","Cost of Purchased Water","Flow per Well","Well Depth","Distance to Distribution System","Component level","System Automation","Include Buildings?","Include Land?",null,"Internal Use"]
[null,null,"design_flow_I","df_units","average_flow_I","af_units","pipe_length_I","buyer_pressure_I","elevation_diff_I","water_cost_I","wells_I","well_depth_I","well_distance_I","component_level_I","manual_I","include_buildings_I","include_land_I"]
[1,1,0.03,"MGD",7.0000000000000001E-3,"MGD",10000,"correct pressure",0,null,null,null,null,null,"fully automated",null,null,null,{"$f":"AND(C9:Q9=inp_used,A9=inp_used_usersel)","v":true},{"$f":"AND(C9:F9=inp_used_minimal,A9=inp_used_usersel)","v":true}]
[1,2,0.124,"MGD",3.5000000000000003E-2,"MGD",10000,"correct pressure",0,null,null,null,null,null,"fully automated",null,null,null,{"$f":"AND(C10:Q10=inp_used,A10=inp_used_usersel)","v":false},{"$f":"AND(C10:F10=inp_used_minimal,A10=inp_used_usersel)","v":false}]
[1,3,0.30499999999999999,"MGD",9.4E-2,"MGD",10000,"correct pressure",0,null,null,null,null,null,"fully automated",null,null,null,{"$f":"AND(C11:Q11=inp_used,A11=inp_used_usersel)","v":false},{"$f":"AND(C11:F11=inp_used_minimal,A11=inp_used_usersel)","v":false}]
[1,4,0.74,"MGD",0.251,"MGD",10000,"correct pressure",0,null,null,null,null,null,"fully automated",null,null,null,{"$f":"AND(C12:Q12=inp_used,A12=inp_used_usersel)","v":false},{"$f":"AND(C12:F12=inp_used_minimal,A12=inp_used_usersel)","v":false}]
[1,5,2.1520000000000001,"MGD",0.81899999999999995,"MGD",10000,"correct pressure",0,null,null,null,null,null,"fully automated",null,null,null,{"$f":"AND(C13:Q13=inp_used,A13=inp_used_usersel)","v":false},{"$f":"AND(C13:F13=inp_used_minimal,A13=inp_used_usersel)","v":false}]
[2,1,0.03,"MGD",7.0000000000000001E-3,"MGD",null,null,null,null,500,250,500,null,"fully automated",null,null,null,{"$f":"AND(C14:Q14=inp_used,A14=inp_used_usersel)","v":false},{"$f":"AND(C14:F14=inp_used_minimal,A14=inp_used_usersel)","v":false}]
[2,2,0.124,"MGD",3.5000000000000003E-2,"MGD",null,null,null,null,500,250,500,null,"fully automated",null,null,null,{"$f":"AND(C15:Q15=inp_used,A15=inp_used_usersel)","v":false},{"$f":"AND(C15:F15=inp_used_minimal,A15=inp_used_usersel)","v":false}]
[2,3,0.30499999999999999,"MGD",9.4E-2,"MGD",null,null,null,null,500,250,500,null,"fully automated",null,null,null,{"$f":"AND(C16:Q16=inp_used,A16=inp_used_usersel)","v":false},{"$f":"AND(C16:F16=inp_used_minimal,A16=inp_used_usersel)","v":false}]
[2,4,0.74,"MGD",0.251,"MGD",null,null,null,null,500,250,500,null,"fully automated",null,null,null,{"$f":"AND(C17:Q17=inp_used,A17=inp_used_usersel)","v":false},{"$f":"AND(C17:F17=inp_used_minimal,A17=inp_used_usersel)","v":false}]
[2,5,2.1520000000000001,"MGD",0.81899999999999995,"MGD",null,null,null,null,500,250,500,null,"fully automated",null,null,null,{"$f":"AND(C18:Q18=inp_used,A18=inp_used_usersel)","v":false},{"$f":"AND(C18:F18=inp_used_minimal,A18=inp_used_usersel)","v":false}]
[]
[{"$f":"IF(LEN(design)=0,\"\",design)","v":1},null,{"$f":"IF(LEN(design_flow_I)=0,\"\",design_flow_I)","v":0.03},{"$f":"IF(LEN(df_units)=0,\"\",df_units)","v":"MGD"},{"$f":"IF(LEN(average_flow_I)=0,\"\",average_flow_I)","v":7.0000000000000001E-3},{"$f":"IF(LEN(af_units)=0,\"\",af_units)","v":"MGD"},{"$f":"IF(LEN(pipe_length_I)=0,\"\",pipe_length_I)","v":10000},{"$f":"IF(LEN(buyer_pressure_I)=0,\"\",buyer_pressure_I)","v":"correct pressure"},{"$f":"IF(LEN(elevation_diff_I)=0,\"\",elevation_diff_I)","v":0},{"$f":"IF(LEN(water_cost_I)=0,\"\",water_cost_I)","v":""},{"$f":"IF(LEN(wells_I)=0,\"\",wells_I)","v":""},{"$f":"IF(LEN(well_depth_I)=0,\"\",well_depth_I)","v":""},{"$f":"IF(LEN(well_distance_I)=0,\"\",well_distance_I)","v":""},{"$f":"IF(LEN(component_level_I)=0,\"\",component_level_I)","v":""},{"$f":"IF(LEN(manual_I)=0,\"\",manual_I)","v":"fully automated"},{"$f":"IF(LEN(include_buildings_I)=0,\"\",include_buildings_I)","v":""},{"$f":"IF(LEN(include_land_I)=0,\"\",include_land_I)","v":""},"<--current inputs"]
[null,null,0.03,"MGD",7.0000000000000001E-3,"MGD",10000,"correct pressure",0,null,null,null,null,null,"fully automated",null,null,"<--inputs used at last \"Generate Results\"",{"$f":"AND(C21:Q21=inp_used)","v":true}]

{"sheet":"Cost Equations","index":13,"n_rows":80,"n_cols":17,"rows_kept":80,"cut":false,"hidden":false,"merged":[]}
["Cost Equations"]
["This sheet is used internally to calculate component costs using the cost equations method; see model documentation for more details on the development of cost equations"]
[]
["Component","Equation Name","Result","Variable","Equation Lookup Name",2,3,4,5,6,7,8,9,10,11,16,17]
["Well Items"]
["Well Casing"]
["PVC","casing_pvc_eq",{"$f":"IF(D7=0,0,IF(OR(D7>P7,D7<Q7),\"contact vendor\",F7*D7^G7+H7*LN(D7)+I7+J7*EXP(K7*D7)+L7*D7^3+M7*D7^2+N7*D7+O7))","v":0},{"$f":"casing_diam","v":0},"casing_pvc_eq",{"$f":"VLOOKUP($E7,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,-4.6884322999999999E-2,1.202903939,-2.5370973120000002,13.474255165000001,12.00001,0]
["Stainless Steel","casing_ss_eq",0,{"$f":"casing_diam","v":0},"casing_ss_eq",13.982077743,1.7609326249999999,0,0,0,0,0,0,0,0,12.00001,2]
["Well screen"]
["PVC Schedule 40","screen_pvc40_eq",{"$f":"IF(D10=0,0,IF(OR(D10>P10,D10<Q10),\"contact vendor\",F10*D10^G10+H10*LN(D10)+I10+J10*EXP(K10*D10)+L10*D10^3+M10*D10^2+N10*D10+O10))","v":0},{"$f":"screen_diam","v":0},"screen_pvc40_eq",{"$f":"VLOOKUP($E10,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,-3.8992711999999999E-2,0.46173353499999997,8.5790048849999998,15.696100854999999,12.00001,0]
["PVC Schedule 80","screen_pvc80_eq",0,{"$f":"screen_diam","v":0},"screen_pvc80_eq",0,0,0,0,0,0,-0.18676874099999999,3.452799739,-5.9343944000000004,27.119046914999998,12.00001,0]
["Stainless Steel","screen_ss_eq",0,{"$f":"screen_diam","v":0},"screen_ss_eq",0,0,0,0,0,0,0.40461480999999999,-7.8955133340000003,76.806647092000006,-0.75316684700000003,12.00001,2]
["Plugs"]
["PVC","plug_pvc_eq",{"$f":"IF(D14=0,0,IF(OR(D14>P14,D14<Q14),\"contact vendor\",F14*D14^G14+H14*LN(D14)+I14+J14*EXP(K14*D14)+L14*D14^3+M14*D14^2+N14*D14+O14))","v":0},{"$f":"plug_diam","v":0},"plug_pvc_eq",{"$f":"VLOOKUP($E14,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,-0.25465370599999998,4.833758123,6.2534816659999999,14.260592057,12.00001,0]
["Stainless Steel","plug_ss_eq",{"$f":"IF(D15=0,0,IF(OR(D15>P15,D15<Q15),\"contact vendor\",F15*D15^G15+H15*LN(D15)+I15+J15*EXP(K15*D15)+L15*D15^3+M15*D15^2+N15*D15+O15))","v":0},{"$f":"plug_diam","v":0},"plug_ss_eq",0,0,0,0,0,0,0.42194984000000002,2.6692835760000002,4.4695669049999998,121.69001632600001,10.00001,0]
["Well Drilling","no equation"]
["Gravel Pack","gravel_pack_eq",{"$f":"IF(D17=0,0,IF(OR(D17>P17,D17<Q17),\"contact vendor\",F17*D17^G17+H17*LN(D17)+I17+J17*EXP(K17*D17)+L17*D17^3+M17*D17^2+N17*D17+O17))","v":0},{"$f":"casing_diam","v":0},"gravel_pack_eq",{"$f":"VLOOKUP($E17,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,-2.456785E-3,0.123224508,6.046649607,-16.044484240999999,36.000999999999998,0]
["Well Development","no equation"]
["Grout Seal","no equation"]
["Vaults","no equations"]
["Piping"]
["Interconnect "]
["Ductile Iron","di_pipe_eq_interconnect",{"$f":"IF(D23=0,0,IF(OR(D23>P23,D23<Q23),\"contact vendor\",F23*D23^G23+H23*LN(D23)+I23+J23*EXP(K23*D23)+L23*D23^3+M23*D23^2+N23*D23+O23))","v":74.282787567},{"$f":"interconnect_pipe_diameter","v":4},"di_pipe_eq",{"$f":"VLOOKUP($E23,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,1.3009082E-2,-0.63853567099999997,18.152542467,11.056607186999999,48.1,4]
["PVC","pvc_pipe_eq_interconnect",6.1853408730000004,{"$f":"interconnect_pipe_diameter","v":4},"pvc_pipe_eq",0,0,0,0,0,0,0,9.7106987000000006E-2,0.800977414,1.427719425,48.1,0]
["Excavation","no equation"]
["Backfill and Compaction","no equation"]
["Asphalt Patch"]
["Well"]
["Ductile Iron","di_pipe_eq_well",{"$f":"IF(D29=0,0,IF(OR(D29>P29,D29<Q29),\"contact vendor\",F29*D29^G29+H29*LN(D29)+I29+J29*EXP(K29*D29)+L29*D29^3+M29*D29^2+N29*D29+O29))","v":0},{"$f":"well_pipe_diameter","v":0},"di_pipe_eq",{"$f":"VLOOKUP($E29,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,1.3009082E-2,-0.63853567099999997,18.152542467,11.056607186999999,48.1,4]
["PVC","pvc_pipe_eq_well",0,{"$f":"well_pipe_diameter","v":0},"pvc_pipe_eq",0,0,0,0,0,0,0,9.7106987000000006E-2,0.800977414,1.427719425,48.1,0]
["Excavation","no equation"]
["Backfill and Compaction","no equation"]
["Valves"]
["Isolation and Street"]
["Ductile Iron","street_valve_eq",{"$f":"IF(D35=0,0,IF(OR(D35>P35,D35<Q35),\"contact vendor\",F35*D35^G35+H35*LN(D35)+I35+J35*EXP(K35*D35)+L35*D35^3+M35*D35^2+N35*D35+O35))","v":1339.4691080929997},{"$f":"valve_size","v":4},"street_valve_eq",{"$f":"VLOOKUP($E35,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,9.6425160640000005,-109.600720857,728.40911852299996,-437.67686038300002,12.1,0]
["Motor/Air Operated (on/off)"]
["Booster Pump - Polypropylene/PVC","pp_mov_eq_booster_pump",487.11354229300002,{"$f":"valve_size","v":4},{"$f":"IF(D37>12,\"pp_man_eq_large\",\"pp_man_eq_small\")","v":"pp_man_eq_small"},{"$f":"VLOOKUP($E37,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,0,12.117823524,45.057729000000002,112.997449909,12,0]
["Booster Pump - Stainless Steel","ss_mov_eq_booster_pump",2066.2008893709999,4,"ss_mov_eq",0,0,0,0,0,0,1.4813451259999999,44.575825872999999,-42.850924452000001,1429.5852851469999,48.1,0]
["Booster Pump - Cast Iron","ci_mov_eq_booster_pump",4053.3997653390002,4,"ci_mov_eq",0,0,0,0,0,0,0,0,1304.6120832720001,-1165.0485677490001,48.1,1]
["Well Pump - Polypropylene/PVC","pp_mov_eq_well_pump",487.11354229300002,4,{"$f":"IF(D40>12,\"pp_man_eq_large\",\"pp_man_eq_small\")","v":"pp_man_eq_small"},0,0,0,0,0,0,0,12.117823524,45.057729000000002,112.997449909,12,0]
["Well Pump - Stainless Steel","ss_mov_eq_well_pump",2066.2008893709999,4,"ss_mov_eq",0,0,0,0,0,0,1.4813451259999999,44.575825872999999,-42.850924452000001,1429.5852851469999,48.1,0]
["Well Pump - Cast Iron","ci_mov_eq_well_pump",4053.3997653390002,4,"ci_mov_eq",0,0,0,0,0,0,0,0,1304.6120832720001,-1165.0485677490001,48.1,1]
["Manual"]
["Booster Pump - Polypropylene/PVC","pp_man_eq_booster_pump",{"$f":"IF(D44=0,0,IF(OR(D44>P44,D44<Q44),\"contact vendor\",F44*D44^G44+H44*LN(D44)+I44+J44*EXP(K44*D44)+L44*D44^3+M44*D44^2+N44*D44+O44))","v":487.11354229300002},{"$f":"valve_size","v":4},{"$f":"IF(D44>12,\"pp_man_eq_large\",\"pp_man_eq_small\")","v":"pp_man_eq_small"},{"$f":"VLOOKUP($E44,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,0,12.117823524,45.057729000000002,112.997449909,12,0]
["Booster Pump - Stainless Steel","ss_man_eq_booster_pump",981.56625867975288,4,"ss_man_eq",271.98316458599999,0.92578415199999997,0,0,0,0,0,0,0,0,12.1,0]
["Booster Pump - Cast Iron","ci_man_eq_booster_pump",946.00563627706163,4,{"$f":"IF(D46>12,\"ci_bfv_eq_large\",\"ci_bfv_eq_small\")","v":"ci_bfv_eq_small"},221.77653215199999,1.0463710340000001,0,0,0,0,0,0,0,0,12,1]
["Well Pump - Polypropylene/PVC","pp_man_eq_well_pump",487.11354229300002,4,{"$f":"IF(D47>12,\"pp_man_eq_large\",\"pp_man_eq_small\")","v":"pp_man_eq_small"},0,0,0,0,0,0,0,12.117823524,45.057729000000002,112.997449909,12,0]
["Well Pump - Stainless Steel","ss_man_eq_well_pump",981.56625867975288,4,"ss_man_eq",271.98316458599999,0.92578415199999997,0,0,0,0,0,0,0,0,12.1,0]
["Well Pump - Cast Iron","ci_man_eq_well_pump",946.00563627706163,4,{"$f":"IF(D49>12,\"ci_bfv_eq_large\",\"ci_bfv_eq_small\")","v":"ci_bfv_eq_small"},221.77653215199999,1.0463710340000001,0,0,0,0,0,0,0,0,12,1]
["Control"]
["Pressure Reducing","prv_eq",{"$f":"IF(D51=0,0,IF(OR(D51>P51,D51<Q51),\"contact vendor\",F51*D51^G51+H51*LN(D51)+I51+J51*EXP(K51*D51)+L51*D51^3+M51*D51^2+N51*D51+O51))","v":0},{"$f":"PRV_size","v":0},"prv_eq",{"$f":"VLOOKUP($E51,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,9.0431154370000009,42.908741155999998,617.31836156300005,1458.208772277,12.1,0]
["Pumps"]
["Booster pump cost, 25 psi",null,{"$f":"IF(D53=0,0,IF(OR(D53>P53,D53<Q53),\"contact vendor\",F53*D53^G53+H53*LN(D53)+I53+J53*EXP(K53*D53)+L53*D53^3+M53*D53^2+N53*D53+O53))","v":0},{"$f":"booster_pump_rating","v":0},"pump_eq",{"$f":"VLOOKUP($E53,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,0,-6.7002999999999995E-4,14.809014980000001,4093.494684836,10001,0]
["Booster pump cost, 75 psi",null,0,0,"dis75_pump_eq",0,0,0,0,0,0,0,-9.45834E-4,21.707653629999999,4216.6581915959996,10001,0]
["Booster pump cost, 150 psi",null,0,0,"ro150_pump_eq",1976.6355754589999,0.41723543800000001,0,0,0,0,0,0,0,0,2000.1,0]
["Booster pump cost, 200 psi",null,0,0,"ro200_pump_eq",0,0,0,0,0,0,0,0,72.107578773,4668.9350805410004,2000.1,0]
["Booster pump cost, 250 psi",null,0,0,"ro250_pump_eq",0,0,0,0,0,0,0,-2.8111870000000001E-2,138.497637586,3440.5033408099998,2000.1,0]
["Booster pump cost, 300 psi",null,0,0,"ro300_pump_eq",0,0,0,0,0,0,0,-0.32797716599999999,232.563202193,3389.3015626460001,250.1,0]
["Booster pump cost, 350 psi",null,0,0,"ro350_pump_eq",1526.907357395,0.61024054000000005,0,0,0,0,0,0,0,0,225.1,0]
["Booster pump cost, 500 psi",null,0,0,"ro500_pump_eq",3021.4372028819998,0.51326787299999999,0,0,0,0,0,0,0,0,185.1,0]
["Interconnect Booster Pump","int_booster_pump_eq",{"$f":"IF(booster_psi<=25,C53,IF(booster_psi<=75,C54,IF(booster_psi<=150,C55,IF(booster_psi<=200,C56,IF(booster_psi<=250,C57,IF(booster_psi<=300,C58,IF(booster_psi<=350,C59,C60)))))))","v":0}]
["Well Pump","no equation"]
["Instrumentation"]
["Flow meters Interconnect"]
["Orifice Plate","op_fm_eq_innterconnect",{"$f":"IF(D65=0,0,IF(OR(D65>P65,D65<Q65),\"contact vendor\",F65*D65^G65+H65*LN(D65)+I65+J65*EXP(K65*D65)+L65*D65^3+M65*D65^2+N65*D65+O65))","v":2640.9899335509999},{"$f":"OUTPUT!G87","v":4},"op_fm_eq",{"$f":"VLOOKUP($E65,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,0.290528801,-20.141018838000001,567.62280550399998,674.16116967899995,60.1,0]
["Propeller","prop_fm_eq_innterconnect",{"$f":"IF(D66=0,0,IF(OR(D66>P66,D66<Q66),\"contact vendor\",F66*D66^G66+H66*LN(D66)+I66+J66*EXP(K66*D66)+L66*D66^3+M66*D66^2+N66*D66+O66))","v":3600.3763511109996},{"$f":"OUTPUT!G88","v":4},"prop_fm_eq",0,0,0,0,0,0,0.48667437299999999,-19.177375645000001,593.88035160699997,1500.545795131,36.1,0]
["Venturi","ven_fm_eq_innterconnect",{"$f":"IF(D67=0,0,IF(OR(D67>P67,D67<Q67),\"contact vendor\",F67*D67^G67+H67*LN(D67)+I67+J67*EXP(K67*D67)+L67*D67^3+M67*D67^2+N67*D67+O67))","v":2664.4617081420001},{"$f":"OUTPUT!G89","v":4},"ven_fm_eq",0,0,0,0,0,0,-8.9659137E-2,7.3814192910000003,135.40877886999999,2010.462068774,48.1,0]
["Magnetic","mag_fm_eq_innterconnect",{"$f":"IF(D68=0,0,IF(OR(D68>P68,D68<Q68),\"contact vendor\",F68*D68^G68+H68*LN(D68)+I68+J68*EXP(K68*D68)+L68*D68^3+M68*D68^2+N68*D68+O68))","v":5121.6611304090002},{"$f":"OUTPUT!G90","v":4},"mag_fm_eq",0,0,0,0,0,0,8.7416773000000003E-2,3.727367106,488.19409208000002,3103.652214921,36.1,0]
["Flow meters well"]
["Orifice Plate","op_fm_eq_well",{"$f":"IF(D70=0,0,IF(OR(D70>P70,D70<Q70),\"contact vendor\",F70*D70^G70+H70*LN(D70)+I70+J70*EXP(K70*D70)+L70*D70^3+M70*D70^2+N70*D70+O70))","v":"contact vendor"},{"$f":"OUTPUT!G92","v":"NA"},"op_fm_eq",{"$f":"VLOOKUP($E70,eq_table_cl,F$4,FALSE)","v":0},0,0,0,0,0,0.290528801,-20.141018838000001,567.62280550399998,674.16116967899995,60.1,0]
["Propeller","prop_fm_eq_well",{"$f":"IF(D71=0,0,IF(OR(D71>P71,D71<Q71),\"contact vendor\",F71*D71^G71+H71*LN(D71)+I71+J71*EXP(K71*D71)+L71*D71^3+M71*D71^2+N71*D71+O71))","v":"contact vendor"},{"$f":"OUTPUT!G93","v":"NA"},"prop_fm_eq",0,0,0,0,0,0,0.48667437299999999,-19.177375645000001,593.88035160699997,1500.545795131,36.1,0]
["Venturi","ven_fm_eq_well",{"$f":"IF(D72=0,0,IF(OR(D72>P72,D72<Q72),\"contact vendor\",F72*D72^G72+H72*LN(D72)+I72+J72*EXP(K72*D72)+L72*D72^3+M72*D72^2+N72*D72+O72))","v":"contact vendor"},{"$f":"OUTPUT!G94","v":"NA"},"ven_fm_eq",0,0,0,0,0,0,-8.9659137E-2,7.3814192910000003,135.40877886999999,2010.462068774,48.1,0]
["Magnetic","mag_fm_eq_well",{"$f":"IF(D73=0,0,IF(OR(D73>P73,D73<Q73),\"contact vendor\",F73*D73^G73+H73*LN(D73)+I73+J73*EXP(K73*D73)+L73*D73^3+M73*D73^2+N73*D73+O73))","v":"contact vendor"},{"$f":"OUTPUT!G95","v":"NA"},"mag_fm_eq",0,0,0,0,0,0,8.7416773000000003E-2,3.727367106,488.19409208000002,3103.652214921,36.1,0]
["System Controls "]
["RTU Units","no equation"]
["Well Pump - Soft Start Control","no equation"]
["Building Structures"]
["Building 1"]
["Small Low Cost Shed","no equation"]
["Low Quality","bldg_lo_eq_1",{"$f":"IF(LEN($E80)=0,\"\",IF(OR(D80>P80,D80<=Q80),\"contact vendor\",F80*D80^G80+H80*LN(D80)+I80+J80*EXP(K80*D80)+L80*D80^3+M80*D80^2+N80*D80+O80))","v":"contact vendor"},{"$f":"build1_fp","v":0},"bldg_lo_eq",{"$f":"VLOOKUP($E80,eq_table_cl,F$4,FALSE)","v":163.72087260199999},-9.5204622000000003E-2,0,0,0,0,0,0,0,0,200000.1,0]

{"sheet":"Cost Coefficients","index":14,"n_rows":37,"n_cols":17,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Coefficients for Component Cost Equations"]
["This sheet is used internally to calculate component costs using the cost equations method; see model documentation for more details on the development of cost equations"]
[]
[null,"Power: C1*x^C2",null,"Logarithmic: C3*Ln(x) + C4",null,"Exponential: C5*e^(C6*x)",null,"Polynomial: C7*x^3 + C8*x^2 + C9*x + C10",null,null,null,"NOTES"]
["Equation Lookup Name","C1","C2","C3","C4","C5","C6","C7","C8","C9","C10","Pct deviation of fit equation","Modified by","Last modified","Rsq of fit equation","max size used to gen curve","min size for which curve fits are valid"]
["bldg_lo_eq",163.72087260199999,-9.5204622000000003E-2,0,0,0,0,0,0,0,0,2.4871309850656418E-2,null," 1/25/2023 5:10:26 PM",0.96700830313985342,200000.1,0]
["casing_pvc_eq",0,0,0,0,0,0,-4.6884322999999999E-2,1.202903939,-2.5370973120000002,13.474255165000001,2.9389604228194265E-2,null," 1/25/2023 4:47:05 PM",0.99579651998027963,12.00001,0]
["casing_ss_eq",13.982077743,1.7609326249999999,0,0,0,0,0,0,0,0,0.41539212874008652,null," 1/25/2023 4:47:24 PM",0.78887425427086011,12.00001,2]
["ci_bfv_eq_large",0,0,0,0,0,0,1.500986001,-137.11144950400001,4591.2435827480003,-36246.272177972001,0.10118956633106969,null," 1/17/2023 10:13:27 AM",0.94922030750420328,48.1,14]
["ci_bfv_eq_small",221.77653215199999,1.0463710340000001,0,0,0,0,0,0,0,0,0.19940134150122094,null," 1/17/2023 11:28:24 AM",0.94954224445418201,12,1]
["ci_mov_eq",0,0,0,0,0,0,0,0,1304.6120832720001,-1165.0485677490001,0.34948104502507477,null," 1/17/2023 11:27:00 AM",0.94241839597621901,48.1,1]
["di_pipe_eq",0,0,0,0,0,0,1.3009082E-2,-0.63853567099999997,18.152542467,11.056607186999999,8.2838949266919329E-2,null," 1/13/2023 3:31:40 PM",0.99257213725669535,48.1,4]
["dis75_pump_eq",0,0,0,0,0,0,0,-9.45834E-4,21.707653629999999,4216.6581915959996,0.24383290802571739,null," 1/19/2023 2:31:04 PM",0.9431124087196926,10001,0]
["gravel_pack_eq",0,0,0,0,0,0,-2.456785E-3,0.123224508,6.046649607,-16.044484240999999,2.8187468542961572E-2,null," 1/25/2023 4:52:26 PM",0.99706122426697208,36.000999999999998,0]
["mag_fm_eq",0,0,0,0,0,0,8.7416773000000003E-2,3.727367106,488.19409208000002,3103.652214921,0.15420855763774652,null," 1/23/2023 12:59:50 PM",0.95974019025607282,36.1,0]
["op_fm_eq",0,0,0,0,0,0,0.290528801,-20.141018838000001,567.62280550399998,674.16116967899995,8.7926571657932578E-2,null," 1/23/2023 12:59:03 PM",0.99588591793663983,60.1,0]
["plug_pvc_eq",0,0,0,0,0,0,-0.25465370599999998,4.833758123,6.2534816659999999,14.260592057,7.8407526447544312E-2,null," 1/25/2023 4:51:45 PM",0.97396209694622005,12.00001,0]
["plug_ss_eq",0,0,0,0,0,0,0.42194984000000002,2.6692835760000002,4.4695669049999998,121.69001632600001,2.4708829255172621E-2,null," 1/25/2023 4:51:56 PM",0.99793336626974938,10.00001,0]
["pp_man_eq_large",0,0,0,0,0,0,0,0,2553.3443597519999,-25690.347540136001,0.1288535088310907,null," 1/17/2023 10:02:52 AM",0.81137657813383668,24.1,14]
["pp_man_eq_small",0,0,0,0,0,0,0,12.117823524,45.057729000000002,112.997449909,0.25566288048231928,null," 1/17/2023 9:54:09 AM",0.86317684069618528,12,0]
["prop_fm_eq",0,0,0,0,0,0,0.48667437299999999,-19.177375645000001,593.88035160699997,1500.545795131,4.521517984937741E-2,null," 1/23/2023 12:59:37 PM",0.99578400826432001,36.1,0]
["PRV_eq",0,0,0,0,0,0,9.0431154370000009,42.908741155999998,617.31836156300005,1458.208772277,6.3614921123102519E-2,null," 1/25/2023 4:56:55 PM",0.99875913342202416,12.1,0]
["pump_eq",0,0,0,0,0,0,0,-6.7002999999999995E-4,14.809014980000001,4093.494684836,0.16787720556525074,null," 1/17/2023 11:35:59 AM",0.95812431183124169,10001,0]
["pvc_pipe_eq",0,0,0,0,0,0,0,9.7106987000000006E-2,0.800977414,1.427719425,0.19469475172443332,null," 1/13/2023 3:33:26 PM",0.99750218963449888,48.1,0]
["ro150_pump_eq",1976.6355754589999,0.41723543800000001,0,0,0,0,0,0,0,0,0.2393085697913106,null," 1/19/2023 2:31:34 PM",0.77313218418984353,2000.1,0]
["ro200_pump_eq",0,0,0,0,0,0,0,0,72.107578773,4668.9350805410004,0.27286347493220853,null," 1/19/2023 2:31:45 PM",0.97095008362194879,2000.1,0]
["ro250_pump_eq",0,0,0,0,0,0,0,-2.8111870000000001E-2,138.497637586,3440.5033408099998,0.21937416786598979,null," 1/19/2023 2:32:00 PM",0.98966246282997439,2000.1,0]
["ro300_pump_eq",0,0,0,0,0,0,0,-0.32797716599999999,232.563202193,3389.3015626460001,0.21117776627626691,null," 1/19/2023 2:32:15 PM",0.85818710033282541,250.1,0]
["ro350_pump_eq",1526.907357395,0.61024054000000005,0,0,0,0,0,0,0,0,0.29913452404767105,null," 1/19/2023 2:32:32 PM",0.90891963403729581,225.1,0]
["ro500_pump_eq",3021.4372028819998,0.51326787299999999,0,0,0,0,0,0,0,0,0.10317276383790089,null," 1/19/2023 2:32:46 PM",0.91793425116209904,185.1,0]
["screen_pvc40_eq",0,0,0,0,0,0,-3.8992711999999999E-2,0.46173353499999997,8.5790048849999998,15.696100854999999,2.9008960283719142E-2,null," 1/25/2023 4:51:08 PM",0.99468109635689661,12.00001,0]
["screen_pvc80_eq",0,0,0,0,0,0,-0.18676874099999999,3.452799739,-5.9343944000000004,27.119046914999998,1.6474872286404572E-2,null," 1/25/2023 4:50:26 PM",0.9982594296445878,12.00001,0]
["screen_ss_eq",0,0,0,0,0,0,0.40461480999999999,-7.8955133340000003,76.806647092000006,-0.75316684700000003,5.4373181192911769E-2,null," 1/25/2023 4:51:24 PM",0.98212918630079737,12.00001,2]
["ss_man_eq",271.98316458599999,0.92578415199999997,0,0,0,0,0,0,0,0,0.19376804452462798,null," 1/17/2023 10:07:57 AM",0.8807162518733741,12.1,0]
["ss_mov_eq",0,0,0,0,0,0,1.4813451259999999,44.575825872999999,-42.850924452000001,1429.5852851469999,0.27548354444948392,null," 1/17/2023 9:46:38 AM",0.93859465845858792,48.1,0]
["street_valve_eq",0,0,0,0,0,0,9.6425160640000005,-109.600720857,728.40911852299996,-437.67686038300002,5.1187018130507661E-2,null," 1/25/2023 4:56:45 PM",0.99809258509899801,12.1,0]
["ven_fm_eq",0,0,0,0,0,0,-8.9659137E-2,7.3814192910000003,135.40877886999999,2010.462068774,7.9589033678493251E-2,null," 1/23/2023 12:59:16 PM",0.98202299755210409,48.1,0]

{"sheet":"Cost Data","index":15,"n_rows":709,"n_cols":7,"rows_kept":709,"cut":false,"hidden":false,"merged":[]}
["Cost Data"]
["This sheet is used internally and contains component cost data extracted from the central cost database"]
["To maintain vendor confidentiality, the central cost database itself is not provided here"]
[]
["BCI","Asphalt Patch"]
[3,"Size needed (in thick)","Size selected (in thick)","Avg. Unit Cost (per sy)","Useful Life (large)","Useful Life (small)"]
["asphalt_patch_cost_cl",0,2,74,40,37]
[null,2.1,4,101,40,37]
[null,4.0999999999999996,6,134,40,37]
[null,6.1,"contact vendor","contact vendor",40,37]
[]
[]
["BCI","Backfill and Compaction"]
[3,null,null,"Avg. Unit Cost (per cy)","Useful Life (large)","Useful Life (small)"]
["backfill_cost_cl",0,0,18.649999999999999,"N/A","N/A"]
[]
[]
["bore_rig_cost",0,0,2650,"N/A","N/A"]
[null,"Borings, cased borings in earth, w/samples, 2.5\" dia",null,"Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["bore_cost_cl",0,0,64,"N/A","N/A"]
[]
[]
["stake_out_cost",0,0,1475,"N/A","N/A"]
[null,"Borings, drawings showing boring details",null,"Avg. Unit Cost (per job)","Useful Life (large)","Useful Life (small)"]
["bore_draw_cost_cl",0,0,425,"N/A","N/A"]
[]
[]
["bore_report_cost",0,0,970,"N/A","N/A"]
[null,"Borings, mobilization and demobilization, minimum",null,"Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["bore_mob_cost_cl",0,0,660,"N/A","N/A"]
[]
[]
["bore_draw_cost",0,0,425,"N/A","N/A"]
[null,"Borings, report and recommendations from P.E.",null,"Avg. Unit Cost (per job)","Useful Life (large)","Useful Life (small)"]
["bore_report_cost_cl",0,0,970,"N/A","N/A"]
[]
[]
["bore_mob_cost",0,0,660,"N/A","N/A"]
[null,"Borings, drill rig and crew with truck mounted auger",null,"Avg. Unit Cost (per day)","Useful Life (large)","Useful Life (small)"]
["bore_rig_cost_cl",0,0,2650,"N/A","N/A"]
[]
[]
["BCI","PVC Well Casing"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["casing_pvc_cost_cl",0,2,12.85,30,27]
[null,2.0000100000000001,4,19.399999999999999,30,27]
[null,4.0000099999999996,6,32.5,30,27]
[null,6.0000099999999996,8,44,30,27]
[null,8.0000099999999996,10,64,30,27]
[null,10.00001,12,74.5,30,27]
[null,12.00001,"contact vendor","contact vendor",30,27]
[]
[]
["BCI","Stainless Steel Well Casing"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["casing_ss_cost_cl",0,2,54.786435196195001,30,27]
[null,2.0000100000000001,4,86.33014030915578,30,27]
[null,4.0000099999999996,6,506.98410463733654,30,27]
[null,6.0000099999999996,8,730.50224019024972,30,27]
[null,8.0000099999999996,10,810.10941260404275,30,27]
[null,10.00001,12,858.156442330559,30,27]
[null,12.00001,"contact vendor","contact vendor",30,27]
[]
[]
["bls1149","Cast Iron Manual Valves"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["ci_bfv_cost_cl",0,2,382.59443243243237,25,20]
[null,2.1,2.5,465.81371999999999,25,20]
[null,2.6,3,985.99555274261604,25,20]
[null,3.1,4,1175,25,20]
[null,4.0999999999999996,8,1725,25,20]
[null,8.1,12,2875,25,20]
["ci_bfv_cost_large",12.1,14,5400,25,20]
[null,14.1,18,9700,25,20]
[null,18.100000000000001,20,12700,25,20]
[null,20.100000000000001,24,20900,25,20]
[null,24.1,30,17100,25,20]
[null,30.1,36,21100,25,20]
[null,36.1,42,26600,25,20]
[null,42.1,48,34100,25,20]
[null,48.1,"contact vendor","contact vendor",25,20]
[]
[]
["bls1149","Cast Iron Motor/Air Operated Valves (on/off)"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["ci_mov_cost_cl",0,1,1344.7174074420614,25,20]
[null,1.1000000000000001,2,863.90978021978026,25,20]
[null,2.1,3,3473.2001649484537,25,20]
[null,3.1,4,4185.087896551724,25,20]
[null,4.0999999999999996,6,6000.7343666666675,25,20]
[null,6.1,8,11061.839595959596,25,20]
[null,8.1,10,14396.050612244897,25,20]
[null,10.1,12,16659.506451612902,25,20]
[null,12.1,14,19254.500240963855,25,20]
[null,14.1,16,26647.512289156628,25,20]
[null,16.100000000000001,18,33531.189219858155,25,20]
[null,18.100000000000001,30,42084.270140109402,25,20]
[null,30.1,48,59227.779900671587,25,20]
[null,48.1,"contact vendor","contact vendor",25,20]
[]
[]
["BCI","Concrete for Basins"]
[3,null,null,"Avg. Unit Cost (per cy)","Useful Life (large)","Useful Life (small)"]
["conc_basin_cost_cl",0,0,739.60558874747949,40,37]
[]
[]
[]
["Current Year","current_year_cl",2021]
[]
[]
["ECI","Well Development"]
[4,null,null,"Avg. Unit Cost (per well)","Useful Life (large)","Useful Life (small)"]
["develop_well_cost_cl",0,0,770,30,27]
[]
[]
["bls10150211","Ductile Iron Piping"]
[19,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost ($/lf)","Useful Life (large)","Useful Life (small)"]
["di_pipe_cost_cl",0,4,75,40,35]
[null,4.0999999999999996,6,92.5,40,35]
[null,6.1,8,126,40,35]
[null,8.1,10,161,40,35]
[null,10.1,12,197,40,35]
[null,12.1,14,157,40,35]
[null,14.1,16,169,40,35]
[null,16.100000000000001,18,216,40,35]
[null,18.100000000000001,20,225,40,35]
[null,20.100000000000001,24,254,40,35]
[null,24.1,30,345.13777777777773,40,35]
[null,30.1,36,451.23555555555549,40,35]
[null,36.1,42,628.76444444444439,40,35]
[null,42.1,48,816.64,40,35]
[null,48.1,"contact vendor","contact vendor",40,35]
[]
[]
["bls1141","75 PSI Pumps for Pumping to Distribution"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["dis75_pump_cost_cl",0,4,3653.3939778012004,20,17]
[null,5,10,3845.6248086268724,20,17]
[null,11,30,4241.77177139372,20,17]
[null,31,75,7747.3609833662886,20,17]
[null,76,100,11871.374146900242,20,17]
[null,101,250,15159.917819620669,20,17]
[null,251,300,17216.566262211822,20,17]
[null,301,500,17834.59696777261,20,17]
[null,501,750,21330.03263077603,20,17]
[null,751,1000,39578.375705660153,20,17]
[null,1001,1500,32412.708918784188,20,17]
[null,1501,2000,37368.823232398398,20,17]
[null,2001,3000,47995.955970614443,20,17]
[null,3001,3500,60559.980079296962,20,17]
[null,3501,4000,62611.588621061441,20,17]
[null,4001,6000,118254.40440369825,20,17]
[null,6001,10000,146237.08136191388,20,17]
[null,10001,"Contact Vendor","contact vendor",20,17]
[]
[]
["BCI","Well Drilling, 10\" borehole diameter"]
[3,"Depth needed (feet)","Depth selected (feet)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["drill_10_cost_cl",0,"Up to 100",166.64412841854934,30,27]
[null,100,"100 to 500",153.08312960760998,30,27]
[null,500.1,"Deeper",162.78129845422117,30,27]
[]
[]
["BCI","Well Drilling, 12\" borehole diameter"]
[3,"Depth needed (feet)","Depth selected (feet)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["drill_12_cost_cl",0,"Up to 100",193.78256361474436,30,27]
[null,100,"100 to 500",151.14349583828775,30,27]
[null,500.1,"Deeper",162.78129845422117,30,27]
[]
[]
["BCI","Well Drilling, 15\" borehole diameter"]
[3,"Depth needed (feet)","Depth selected (feet)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["drill_15_cost_cl",0,"Up to 100",263.54362901307968,30,27]
[null,100,"100 to 500",255.78509393579074,30,27]
[null,500.1,"Deeper",267.40645897740785,30,27]
[]
[]
["BCI","Well Drilling, 8\" borehole diameter"]
[3,"Depth needed (feet)","Depth selected (feet)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["drill_8_cost_cl",0,"Up to 100",122.0818644470868,30,27]
[null,100,"100 to 500",106.58123186682522,30,27]
[null,500.1,"Deeper",114.32332936979786,30,27]
[]
[]
["BCI","Excavation"]
[3,null,null,"Avg. Unit Cost (per cy)","Useful Life (large)","Useful Life (small)"]
["excavate_cost_cl",0,0,30.879999999999995,"N/A","N/A"]
[]
[]
["BCI","Gravel Pack"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["gravel_pack_cost_cl",0,6,24.5,30,27]
[null,6.0010000000000003,8,37.5,30,27]
[null,8.0009999999999994,10,55,30,27]
[null,10.000999999999999,12,71.5,30,27]
[null,12.000999999999999,14,83,30,27]
[null,14.000999999999999,16,104,30,27]
[null,16.001000000000001,18,120,30,27]
[null,18.001000000000001,20,142,30,27]
[null,20.001000000000001,24,164,30,27]
[null,24.001000000000001,26,173,30,27]
[null,26.001000000000001,30,210,30,27]
[null,30.001000000000001,36,250,30,27]
[null,36.000999999999998,"contact vendor","contact vendor",30,27]
[]
[]
["BCI","Grouting"]
[3,null,null,"Avg. Unit Cost (per cf)","Useful Life (large)","Useful Life (small)"]
["grout_cost_cl",0,0,90.25,40,37]
[]
[]
["bls118","Magnetic Flow Meters"]
[43,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["mag_fm_cost_cl",0,0.75,5046.2119844357976,15,14]
[null,0.76,1,3719.3050000000003,15,14]
[null,1.1000000000000001,1.5,3762.3734647887327,15,14]
[null,1.6,2,3899.756109589041,15,14]
[null,2.1,2.5,3894.0035399999997,15,14]
[null,2.6,3,4108.8077378640774,15,14]
[null,3.1,4,4281.420210752688,15,14]
[null,4.0999999999999996,6,6813.2262666666675,15,14]
[null,6.1,8,9089.8655287356323,15,14]
[null,8.1,10,12176.345693430656,15,14]
[null,10.1,12,12145.340729927009,15,14]
[null,12.1,14,10284.360875912409,15,14]
[null,14.1,16,12564.606131386863,15,14]
[null,16.100000000000001,18,10867.900729927009,15,14]
[null,18.100000000000001,20,14743.154452554745,15,14]
[null,20.100000000000001,24,19627.052846715331,15,14]
[null,24.1,30,22949.66189781022,15,14]
[null,30.1,36,30908.188029197081,15,14]
[null,36.1,"contact vendor","contact vendor",15,14]
[]
[]
["bls118","Orifice Plate Flow Meters"]
[43,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["op_fm_cost_cl",0,2,1917.9132555761435,15,14]
[null,2.1,3,2012.2775307939435,15,14]
[null,3.1,6,3229.2703536375971,15,14]
[null,6.1,12,5131.3008277058216,15,14]
[null,12.1,16,6932.5703263810165,15,14]
[null,16.100000000000001,24,6272.4431261963609,15,14]
[null,48.1,60,25150.900552878997,15,14]
[null,60.1,"contact vendor","contact vendor",15,14]
[]
[]
["BCI","PVC Well Plug"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per well)","Useful Life (large)","Useful Life (small)"]
["plug_pvc_cost_cl",0,2,44.480076099881096,30,27]
[null,2.0000100000000001,4,94.022925089179552,30,27]
[null,4.0000099999999996,6,201.2287847800238,30,27]
[null,6.0000099999999996,8,238.73932936979787,30,27]
[null,8.0000099999999996,10,281.95371224732463,30,27]
[null,10.00001,12,361.88963614744353,30,27]
[null,12.00001,"contact vendor","contact vendor",30,27]
[]
[]
["BCI","Stainless Steel Well Plug"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per well)","Useful Life (large)","Useful Life (small)"]
["plug_ss_cost_cl",0,2,145.04515576694411,30,27]
[null,2.0000100000000001,4,206.34087039239003,30,27]
[null,4.0000099999999996,6,348.3121997621879,30,27]
[null,6.0000099999999996,8,525.26268727705121,30,27]
[null,8.0000099999999996,10,868.28198810939364,30,27]
[null,10.00001,"contact vendor","contact vendor",30,27]
[]
[]
["bls1149","Polypropylene/PVC Manual Valves"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["pp_man_cost_cl",0,0.5,128.83954852512139,25,20]
[null,0.6,0.75,147.50199593286607,25,20]
[null,0.76,1,165.45892277617779,25,20]
[null,1.1000000000000001,1.5,234.30425345942169,25,20]
[null,1.6,2,297.00740796003362,25,20]
[null,2.1,3,500.39976319112048,25,20]
[null,3.1,4,636.40287930604438,25,20]
[null,4.0999999999999996,6,509.65011818181824,25,20]
[null,6.1,8,1509.803581818182,25,20]
[null,8.1,10,2218.7854000000002,25,20]
[null,10.1,12,3493.0035818181823,25,20]
["pp_man_cost_large",12.1,14,9497.3730363636369,25,20]
[null,14.1,16,15175.099218181818,25,20]
[null,16.100000000000001,18,26336.568309090911,25,20]
[null,18.100000000000001,20,27037.973036363637,25,20]
[null,20.100000000000001,24,30928.845763636364,25,20]
[null,24.1,"contact vendor","contact vendor",25,20]
[]
[]
["bls1149","Polypropylene/PVC Motor/Air Operated Valves (on/off)"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["pp_mov_cost_cl",0,0.5,740.26144396551717,25,20]
[null,0.6,0.75,756.44980603448266,25,20]
[null,0.76,1,762.05928879310341,25,20]
[null,1.1000000000000001,1.5,832.34657327586206,25,20]
[null,1.6,2,819.5775532116046,25,20]
[null,2.1,2.5,754.23778894515874,25,20]
[null,2.6,3,976.71146485581494,25,20]
[null,3.1,4,1166.5479564973525,25,20]
[null,4.0999999999999996,6,1648.4738331376229,25,20]
[null,6.1,8,2631.2493192641541,25,20]
[null,8.1,10,4317.4601627104021,25,20]
[null,10.1,12,5117.5928035714278,25,20]
["pp_mov_cost_large",12.1,14,11974.494936134073,25,20]
[null,14.1,16,17391.798762050355,25,20]
[null,16.100000000000001,18,27381.034667861262,25,20]
[null,18.100000000000001,20,29109.944789041583,25,20]
[null,20.100000000000001,24,32873.996547028371,25,20]
[null,24.1,"contact vendor","contact vendor",25,20]
[]
[]
["bls118","Propeller Flow Meters"]
[43,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["prop_fm_cost_cl",0,2,2762.2015890410958,15,14]
[null,2.1,3,2952.1338543689321,15,14]
[null,3.1,4,3374.8769849462369,15,14]
[null,4.0999999999999996,6,5052.9042666666664,15,14]
[null,6.1,8,5332.266333333333,15,14]
[null,8.1,10,5899.4732846715342,15,14]
[null,10.1,12,6787.882043795621,15,14]
[null,12.1,14,7395.5316788321179,15,14]
[null,14.1,16,8116.5827737226282,15,14]
[null,16.100000000000001,18,8534.2178102189791,15,14]
[null,18.100000000000001,20,9377.896642335767,15,14]
[null,20.100000000000001,24,11593.604671532848,15,14]
[null,24.1,30,15914.305401459857,15,14]
[null,30.1,36,20239.210510948906,15,14]
[null,36.1,"contact vendor","contact vendor",15,14]
[]
[]
["bls1149","Pressure Reducing Valves"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["PRV_cost_cl",0,1.5,2627.8917297297298,25,20]
[null,1.6,2,2680.1079459459461,25,20]
[null,2.1,2.5,3874.2537199999997,25,20]
[null,2.6,3,3653.8759746835444,25,20]
[null,3.1,4,5498.9983146067407,25,20]
[null,4.0999999999999996,6,8789.1013114754096,25,20]
[null,6.1,8,13309.212405063292,25,20]
[null,8.1,10,20857.633636363636,25,20]
[null,10.1,12,31181.063606557378,25,20]
[null,12.1,"contact vendor","contact vendor",25,20]
[]
[]
["bls1141","Centrifugal Pumps (Booster)"]
[30,"Size needed (gpm)","Size selected (gpm)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["pump_cost_cl",0,10,3765.081740826402,20,17]
[null,11,30,4254.6995836907727,20,17]
[null,31,200,12125.919025502953,20,17]
[null,301,500,13969.613526038316,20,17]
[null,501,750,17759.918572183145,20,17]
[null,751,1000,18367.928438860366,20,17]
[null,1001,1500,23207.19064302601,20,17]
[null,1501,2000,33543.358376538781,20,17]
[null,2001,3000,38246.128569817702,20,17]
[null,3001,3500,41894.187769881028,20,17]
[null,3501,4000,44289.002142711717,20,17]
[null,4001,6000,82333.048086229319,20,17]
[null,6001,10000,89174.66776026864,20,17]
[null,10001,"contact vendor","contact vendor",20,17]
[]
[]
["bls072106033","PVC Piping"]
[15,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost ($/lf)","Useful Life (large)","Useful Life (small)"]
["pvc_pipe_cost_cl",0,0.5,1.9312269788838421,22,17]
[null,0.6,0.75,2.5438388800988618,22,17]
[null,0.76,1,3.7370578810951129,22,17]
[null,1.1000000000000001,1.5,2.3199999999999998,22,17]
[null,1.6,2,2.4700000000000002,22,17]
[null,2.1,2.5,3.46,22,17]
[null,2.6,3,6.45,22,17]
[null,3.1,4,9.6,22,17]
[null,3.1,6,12.6,22,17]
[null,6.1,8,17.850000000000001,22,17]
[null,8.1,10,22.5,22,17]
[null,10.1,12,29,22,17]
[null,12.1,14,28,22,17]
[null,14.1,16,35,22,17]
[null,16.100000000000001,18,42.5,22,17]
[null,18.100000000000001,20,52.5,22,17]
[null,20.100000000000001,24,73.5,22,17]
[null,24.1,30,115,22,17]
[null,30.1,36,160,22,17]
[null,36.1,42,217,22,17]
[null,42.1,48,265,22,17]
[null,48.1,"contact vendor","contact vendor",22,17]
[]
[]
["bls1141","150 PSI Feed Water Pumps for Nanofiltration and Reverse Osmosis"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["ro150_pump_cost_cl",0,5,4107.7838744247119,20,17]
[null,5.0999999999999996,10,4637.9187341605675,20,17]
[null,10.1,30,5715.8811701857849,20,17]
[null,30.1,75,13047.168512850063,20,17]
[null,75.099999999999994,100,17717.44959733792,20,17]
[null,100.1,250,23275.929653752206,20,17]
[null,250.1,500,32326.990849518384,20,17]
[null,500.1,2000,34349.312001263977,20,17]
[null,2000.1,"Contact Vendor","contact vendor",20,17]
[]
[]
["bls1141","200 PSI Feed Water Pumps for Nanofiltration and Reverse Osmosis"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["ro200_pump_cost_cl",0,5,4463.8258470658184,20,17]
[null,5.0999999999999996,30,6261.686988742731,20,17]
[null,30.1,75,17674.344305873863,20,17]
[null,75.099999999999994,100,21918.741047273157,20,17]
[null,100.1,250,30537.918600792258,20,17]
[null,250.1,500,36841.166405487958,20,17]
[null,500.1,600,36716.22773111095,20,17]
[null,600.1,2000,161772.35560671645,20,17]
[null,2000.1,"Contact Vendor","contact vendor",20,17]
[]
[]
["bls1141","250 PSI Feed Water Pumps for Reverse Osmosis"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["ro250_pump_cost_cl",0,5,4985.0841587509367,20,17]
[null,5.0999999999999996,30,6261.686988742731,20,17]
[null,30.1,55,8769.5885127242764,20,17]
[null,55.1,75,17674.344305873863,20,17]
[null,75.099999999999994,100,30095.115151079353,20,17]
[null,100.1,250,39168.909902165833,20,17]
[null,250.1,300,40188.37101092298,20,17]
[null,300.10000000000002,2000,167352.98939583654,20,17]
[null,2000.1,"Contact Vendor","contact vendor",20,17]
[]
[]
["bls1141","300 PSI Feed Water Pumps for Reverse Osmosis"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["ro300_pump_cost_cl",0,5,5200.6544702091669,20,17]
[null,5.0999999999999996,10,5748.6192336628683,20,17]
[null,10.1,30,7418.0102317974943,20,17]
[null,30.1,55,19781.90503761113,20,17]
[null,55.1,80,31322.852459671165,20,17]
[null,80.099999999999994,90,19006.707082636389,20,17]
[null,90.1,100,30011.358689853409,20,17]
[null,100.1,225,37732.489451610621,20,17]
[null,225.1,250,40188.37101092298,20,17]
[null,250.1,"Contact Vendor","contact vendor",20,17]
[]
[]
["bls1141","350 PSI Feed Water Pumps for Reverse Osmosis"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["ro350_pump_cost_cl",0,5,5200.6544702091669,20,17]
[null,5.0999999999999996,10,5748.6192336628683,20,17]
[null,10.1,30,7418.0102317974943,20,17]
[null,30.1,71,21906.094524922431,20,17]
[null,71.099999999999994,100,34361.533719321917,20,17]
[null,100.1,225,40188.37101092298,20,17]
[null,225.1,"Contact Vendor","contact vendor",20,17]
[]
[]
["bls1141","500 PSI Feed Water Pumps for Reverse Osmosis"]
[30,"Size needed in (gpm)","Size Selected (gpm)","Avg. Unit Cost","Useful Life(large)","Useful Life (small)"]
["ro500_pump_cost_cl",0,5,6603.2988529312988,20,17]
[null,5.0999999999999996,33,21037.207314600906,20,17]
[null,33.1,75,25030.16136959842,20,17]
[null,75.099999999999994,"Contact Vendor","contact vendor",20,17]
[]
[]
["bls117507","Remote Telemetry Unit (RTU)"]
[42,null,null,"Avg. Unit Cost (per well)","Useful Life (large)","Useful Life (small)"]
["RTU_cost_cl",0,0,1215,10,8]
[]
[]
["BCI","PVC Schedule 40 Well Screen"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["screen_pvc40_cost_cl",0,2,34.190154577883476,30,27]
[null,2.0000100000000001,4,56.890444708680143,30,27]
[null,4.0000099999999996,6,71.519885850178355,30,27]
[null,6.0000099999999996,8,96.504998810939355,30,27]
[null,8.0000099999999996,10,109.85230915576695,30,27]
[null,10.00001,12,116.83827824019025,30,27]
[null,12.00001,"contact vendor","contact vendor",30,27]
[]
[]
["BCI","PVC Schedule 80 Well Screen"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["screen_pvc80_cost_cl",0,2,27.648,30,27]
[null,2.0000100000000001,4,45.943020214030916,30,27]
[null,4.0000099999999996,6,77.437412604042805,30,27]
[null,6.0000099999999996,8,105.39772651605233,30,27]
[null,8.0000099999999996,10,123.24893222354341,30,27]
[null,10.00001,12,131.69784542211653,30,27]
[null,12.00001,"contact vendor","contact vendor",30,27]
[]
[]
["BCI","Stainless Steel Well Screen"]
[3,"Size needed (in. diam.)","Size selected (in. diam.)","Avg. Unit Cost (per lf)","Useful Life (large)","Useful Life (small)"]
["screen_ss_cost_cl",0,2,123,30,27]
[null,2.0000100000000001,4,223,30,27]
[null,4.0000099999999996,6,242,30,27]
[null,6.0000099999999996,8,320,30,27]
[null,8.0000099999999996,10,405,30,27]
[null,10.00001,12,470,30,27]
[null,12.00001,"contact vendor","contact vendor",30,27]
[]
[]
[3,null,null,"Avg. Unit Cost (per sf)","Useful Life (large)"]
["sitework_sf_cl","Sitework unit cost","sitework_sf_cl",15.191208791208791,"N/A"]
[]
[]
["bls117507","Well Pump - Soft Start Control"]
[42,null,null,"Avg. Unit Cost (per well)","Useful Life (large)","Useful Life (small)"]
["soft_start_cost_cl",0,0,417.5,10,8]
[]
[]
["bls1149","Stainless Steel Manual Valves"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["ss_man_cost_cl",0,1,317.83100975609756,25,20]
[null,1.1000000000000001,2,468.17123628865829,25,20]
[null,2.1,3,761.47524388103591,25,20]
[null,3.1,4,1023.1879828009146,25,20]
[null,4.0999999999999996,6,1045.1288577751598,25,20]
[null,6.1,8,1499.216706700558,25,20]
[null,8.1,10,2730.5081374852439,25,20]
[null,10.1,12,3467.076863583452,25,20]
[null,12.1,"contact vendor","contact vendor",25,20]
[]
[]
["bls1149","Stainless Steel Motor/Air Operated Valves (on/off)"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["ss_mov_cost_cl",0,1,1207.272967032967,25,20]
[null,1.1000000000000001,1.5,1723.1306593406593,25,20]
[null,1.6,2,1414.6933333692275,25,20]
[null,2.1,3,2482.1433022573333,25,20]
[null,3.1,4,4004.9512980917061,25,20]
[null,4.0999999999999996,6,2714.3535021394746,25,20]
[null,6.1,8,4334.6072933153191,25,20]
[null,8.1,10,5587.5543049125572,25,20]
[null,10.1,12,7631.5667775126039,25,20]
[null,12.1,14,24748.502523442836,25,20]
[null,14.1,16,28530.258869128291,25,20]
[null,16.100000000000001,20,46615.65881913164,25,20]
[null,20.100000000000001,24,65149.016724246409,25,20]
[null,24.1,30,80302.681490252042,25,20]
[null,30.1,48,220985.84305316926,25,20]
[null,48.1,"contact vendor","contact vendor",25,20]
[]
[]
["ECI","Geotechnical costs"]
[4,"Borings, initial field stake out & determination of elevations",null,"Avg. Unit Cost (per day)","Useful Life (large)","Useful Life (small)"]
["stake_out_cost_cl",0,0,1475,"N/A","N/A"]
[]
[]
["bls1173","Standby Generators for Standby Power Calculations"]
[40,"Size needed (kW)","Size selected (kW)","Avg. Unit Cost ($/kW)","Useful Life (large)","Useful Life (small)"]
["std_gen_cost_cl",0,30,660]
[null,31,50,482]
[null,51,60,525]
[null,61,75,477.33333333333331]
[null,76,100,412]
[null,101,125,304]
[null,126,150,356.66666666666669]
[null,151,175,262.85714285714283]
[null,176,200,357.5]
[null,201,250,240]
[null,251,275,301.81818181818181]
[null,276,300,221.66666666666666]
[null,301,350,230]
[null,351,400,237.5]
[null,401,500,214]
[null,501,600,250]
[null,601,650,346.92307692307691]
[null,651,750,281.33333333333331]
[null,751,800,260]
[null,801,900,287.22222222222223]
[null,901,1000,243]
[]
[]
["bls1149","Isolation/Street Valves"]
[35,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["street_valve_cost_cl",0,2,674.75659459459462,25,20]
[null,2.1,3,944.60593248945145,25,20]
[null,3.1,4,1426.2455056179774,25,20]
[null,4.0999999999999996,6,2185.1942076502733,25,20]
[null,6.1,8,3113.4212658227848,25,20]
[null,8.1,10,5539.6336363636365,25,20]
[null,10.1,12,9399.260327868853,25,20]
[null,12.1,"contact vendor","contact vendor",25,20]
[]
[]
["bls118","Venturi Flow Meters"]
[43,"Size needed (in. diam)","Size selected (in. diam)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["ven_fm_cost_cl",0,1,2193.0706249999998,15,14]
[null,1.1000000000000001,2,2310.6700136986301,15,14]
[null,2.1,3,2401.0457718446605,15,14]
[null,3.1,4,2419.8302129032259,15,14]
[null,4.0999999999999996,6,3590.5771888888885,15,14]
[null,6.1,8,3965.3893275862065,15,14]
[null,8.1,10,4011.5260729927013,15,14]
[null,10.1,12,4677.219503649635,15,14]
[null,12.1,14,5013.5698686131391,15,14]
[null,14.1,16,5181.7450510948911,15,14]
[null,16.100000000000001,18,5924.5187737226279,15,14]
[null,18.100000000000001,20,6330.9421313868606,15,14]
[null,20.100000000000001,24,9988.7523503649645,15,14]
[null,24.1,30,11109.920233576642,15,14]
[null,30.1,36,12511.380087591242,15,14]
[null,36.1,42,13492.40198540146,15,14]
[null,42.1,48,15594.591766423357,15,14]
[null,48.1,"contact vendor","contact vendor",15,14]
[]
[]
["bls1141","Well Pumps"]
[30,"Size needed (hp)","Size selected (gpm)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["well_pump_cost_cl",0,0.5,1930.9028445559698,20,17]
[null,0.6,0.75,2055.6069090479305,20,17]
[null,0.85,1,2147.4288072808172,20,17]
[null,1.1000000000000001,1.5,1798.1333430570662,20,17]
[null,1.6,2,2555.6640034783804,20,17]
[null,2.1,3,3417.4249267586465,20,17]
[null,3.1,5,4450.42128187862,20,17]
[null,5.0999999999999996,7.5,6608.8563085827582,20,17]
[null,7.6,10,7255.7291732688172,20,17]
[null,10.1,15,7897.2416244364167,20,17]
[null,15.1,20,11557.709189125813,20,17]
[null,20.100000000000001,25,12536.729158122402,20,17]
[null,25.1,30,13545.529202221547,20,17]
[null,30.1,40,17251.907716027388,20,17]
[null,40.1,50,19682.70634627367,20,17]
[null,50.1,60,26847.184406082401,20,17]
[null,60.1,75,29529.872838237814,20,17]
[null,75.099999999999994,100,42819.851770985537,20,17]
[null,100.1,125,48532.042426594904,20,17]
[null,125.1,150,54788.96028928856,20,17]
[null,150.1,200,58978.024187048359,20,17]
[null,200.1,250,65692.190286212543,20,17]
[null,250.1,"contact vendor","contact vendor",20,17]
[]
[]
["BCI","Low Quality, No Heating or Cooling"]
[3,"Size needed (sf)","Size selected (sf)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["bldg_lo_cost_cl",0,500,96.812325106916731,40,37]
[null,500.1,1000,85.695132889157776,40,37]
[null,1000.1,2000,79.902726370939661,40,37]
[null,2000.1,3000,77.562022463174657,40,37]
[null,3000.1,4000,73.888442053430978,40,37]
[null,4000.1,5000,71.210649833158655,40,37]
[null,5000.1000000000004,7500,68.754095256191349,40,37]
[null,7500.1,8000,68.342791320755438,40,37]
[null,8000.1,10000,65.677166848285296,40,37]
[null,10000.1,12000,66.225667162447152,40,37]
[null,12000.1,15000,63.962022393975595,40,37]
[null,15000.1,18000,63.774970360904831,40,37]
[null,18000.099999999999,20000,61.506399713827875,40,37]
[null,20000.099999999999,24000,62.533501422015092,40,37]
[null,24000.1,25000,61.704060174752492,40,37]
[null,25000.1,30000,60.168928848565692,40,37]
[null,30000.1,36000,60.263829094701443,40,37]
[null,36000.1,42000,59.739422513108003,40,37]
[null,42000.1,48000,59.54501465752233,40,37]
[null,48000.1,50000,58.026379657538449,40,37]
[null,50000.1,54000,58.038523930206111,40,37]
[null,54000.1,60000,58.21329937756736,40,37]
[null,60000.1,100000,56.179268642501221,40,37]
[null,100000.1,200000,54.640406783156237,40,37]
[null,200000.1,"contact vendor"]
[]
[]
["BCI","Small Low Cost Shed"]
[3,"Size needed (sf)","Size selected (sf)","Avg. Unit Cost","Useful Life (large)","Useful Life (small)"]
["bldg_shed_cost_cl",0,500,50.742289444355997,25,20]
[null,500.1,"contact vendor","contact vendor"]
[]
[]
[null,"Building maintenance (labor and materials)","building_matl_rate_cl",6.4790285359180011,"/sf/yr"]
[]
[]
[null,"Energy cost","energy_cost_cl",0.11001750924784216,"/kwh"]
[]
[]
["ECI","Labor Costs",null,null,"Inf. Adj. Rates"]
[4,"Design Flow Needed (mgd)","Design Flow (mgd)","Average Water Treatment Plant Operators3","Technical Rate (Loaded)","Managerial Rate (Loaded)","Clerical Rate (Loaded)"]
["labor_table_cl",0,0.03,1,34.161577540106947,50.648663101604278,32.90061497326203]
[null,3.1E-2,0.124,1,34.161577540106947,50.648663101604278,32.90061497326203]
[null,0.1241,0.30499999999999999,1.2,34.161577540106947,50.648663101604278,32.90061497326203]
[null,0.30509999999999998,0.74,1.2,34.161577540106947,50.648663101604278,32.90061497326203]
[null,0.74099999999999999,2.1520000000000001,1.6,36.431310160427806,57.930721925133689,32.90061497326203]
[null,2.1520999999999999,7.3650000000000002,2.8,38.459358288770055,64.529759358288771,42.483930481283423]
[null,7.3651,22.614000000000001,3.8,40.151149732620318,75.300481283422457,42.483930481283423]
[null,22.614100000000001,75.069999999999993,7.8,46.928823529411758,80.449411764705886,42.483930481283423]
[]
[]
[null,"Land Costs"]
["CPI","Land Costs"]
[5,"System Size  greater than (MGD)","Assumed Population","Unit Cost ($/acre)"]
["land_cost_cl",0,"25-100",21165.713250209614]
[null,3.1E-2,"101-500",20660.02273280502]
[null,0.1241,"501-1,000",16458.498827329597]
[null,0.30509999999999998,"1,001-3,300",19327.219360846331]
[null,0.74099999999999999,"3,301-10,000",31219.845835377939]
[null,2.1520999999999999,"10,001-50,000",68950.849699201353]
[null,7.3651,"50,001-100,000",129311.24247230604]
[null,22.614100000000001,"100,001-1,000,000",262816.68000200921]
[]
[]
[null,"Building Permit Cost Multipliers"]
[null,"System Size  greater than (MGD)","Assumed Population","Multiplier"]
["UBC_mult_table_cl",0,"25-100",2.6]
[null,3.1E-2,"101-500",2.2999999999999998]
[null,0.1241,"501-1,000",2.2000000000000002]
[null,0.30509999999999998,"1,001-3,300",1.8]
[null,0.74099999999999999,"3,301-10,000",1.6]
[null,2.1520999999999999,"10,001-50,000",1.5]
[null,7.3651,"50,001-100,000",1.5]
[null,22.614100000000001,"100,001-1,000,000",1.4]
[]
[]
["CPI","Wholesale Water Purchases","wholesale_water_cost_cl",3.0595290784661366,"/1,000 gallons"]

{"sheet":"Engineering Data","index":16,"n_rows":89,"n_cols":5,"rows_kept":89,"cut":false,"hidden":false,"merged":[]}
["Engineering Data"]
["This sheet is used internally and contains engineering reference and indirect cost data extracted from the central cost database"]
[]
["Architectural Fee"]
["Building cost lookup","Corresponds to the range","Architectural fee"]
[0,"<$250,000",0.09]
[250000,"$250,000 to $500,000",0.08]
[500000,"$500,000 to $1,000,000",7.0000000000000007E-2]
[1000000,"$1,000,000 to $5,000,000",6.2E-2]
[5000000,"$5,000,000 to $10,000,000",5.2999999999999999E-2]
[10000000,"$10,000,000 to $50,000,000",4.9000000000000002E-2]
[50000000,">$50,000,000",4.4999999999999998E-2]
[]
[]
["Construction Management Fee"]
["Building cost lookup","Corresponds to the range","Construction management fee"]
[0,"<$100,000",0.1]
[100000,"$100,000 to $250,000",0.09]
[250000,"$250,000 to $1,000,000",0.06]
[1000000,"$1,000,000 to $5,000,000",0.05]
[5000000,"$5,000,000 to $10,000,000",0.04]
[10000000,">$10,000,000",3.2000000000000001E-2]
[]
[]
["Contingency Factors"]
["Project direct cost","Corresponds to the range","Default contingency (prior to application of technology complexity factor)"]
[0,"<$500,000",6.7000000000000004E-2]
[500000,"$500,000 to $3,000,000",5.8000000000000003E-2]
[3000000,"$3,000,000 to $15,000,000",4.9000000000000002E-2]
[15000000,"$15,000,000 to $50,000,000",4.1000000000000002E-2]
[50000000,"$50,000,000 to $100,000,000",3.2000000000000001E-2]
[100000000,">$100,000,000",5.8000000000000003E-2]
[]
[]
["Feed Water Pump Dimensions (for high pressure pumps for reverse osmosis and nanofiltration)"]
["Pump Rating Needed (mgd)","Pump Rating Selected (mgd)","Area w/ piping (sf)","Foundation Depth (ft)"]
[0,0.05,36,1]
[0.06,0.1,45,1.1666666666666667]
[0.11,0.2,54,1.1666666666666667]
[0.21,0.5,65,1.1666666666666667]
[0.51,1,76,1.3333333333333333]
[1.01,2,88,1.3333333333333333]
[2.0099999999999998,3,95,1.3333333333333333]
[3.01,5,101,1.5]
[5.01,10,144,2]
[]
[]
["Performance Bond"]
["Direct cost lookup","Corresponds to the range","Base cost","Plus fraction","Of amount over"]
[0,"<$100,000",0,2.5000000000000001E-2,0]
[100000,"$100,000 to $500,000",2500,1.4999999999999999E-2,100000]
[500000,"$500,000 to $2,500,000",8500,0.01,500000]
[2500000,"$2,500,000 to $5,000,000",28500,7.4999999999999997E-3,2500000]
[5000000,"$5,000,000 to $7,500,000",47250,7.0000000000000001E-3,5000000]
[7500000,">$7,500,000",64750,6.0000000000000001E-3,7500000]
[]
[]
["Pipe Size Selection"]
["Pipe Flow to Pipe Diameter"]
["Flow (GPM)","Diameter (inch)"]
[0,0.5]
[2.1,0.75]
[4.0999999999999996,1]
[7.1,1.5]
[21.1,2]
[41.1,2.5]
[66.099999999999994,3]
[116.1,4]
[238.1,6]
[697.1,8]
[1435.1,10]
[2608.1,12]
[4132.1000000000004,14]
[5299.1,16]
[7528.1,18]
[10265.1,20]
[13643.1,24]
[22174.1,30]
[50561.1,36]
[81777.100000000006,42]
[122025.1,48]
[]
[]
["Pump Dimensions (for centrifugal pumps)"]
["Pump Rating Needed (gpm)","Pump Rating Selected (gpm)","Length (ft)","Width (ft)"]
[0,350,2.5,2.5]
[351,1740,3.75,3.75]
[1741,7000,5,5]
[7001,35000,7.083333333333333,7.083333333333333]

{"sheet":"Lookup Tables","index":17,"n_rows":56,"n_cols":4,"rows_kept":56,"cut":false,"hidden":false,"merged":[]}
["Lookup Tables and Input Validation"]
["This sheet is used internally to perform certain reference calculations and support input sheet data validation"]
[]
["Well Pump Size Lookup"]
["pump_hp_table"]
["Horsepower Needed","Horsepower Selected","Pump Diameter (inches)"]
[0,7.5,4]
[7.6,50,6]
[51,250,8]
[]
[]
["Pressure Reducing Valve Sizes"]
["prv_table"]
["Diameter Needed (inches)","Diameter Selected (inches)","Length (inches)","Width (inches)"]
[0,2,9.3800000000000008,18]
[2.1,2.5,11,20]
[2.6,3,12,22]
[3.1,4,15,24]
[4.0999999999999996,6,20,40]
[6.1,8,25.5,77]
[8.1,10,29.75,48]
[10.1,12,34,52]
[12.1,14,39,58]
[14.1,16,41.4,60]
[]
["Input Validation"]
["design_type"]
["Interconnection"]
["New Well Construction"]
[]
["comp_level_valid"]
["low cost"]
["mid cost"]
["high cost"]
[]
["system_automation_valid"]
["manual"]
["semi-automated"]
["fully automated"]
[]
["yes_no_valid"]
[{"$f":"IF(design=2,\"yes\",\"\")","v":""}]
[{"$f":"IF(design=2,\"no\",\"\")","v":""}]
[]
[]
["Constants"]
["Minutes in a day","min_per_day",1440,"min/day"]
["Gallons per cubic foot","gal_per_cuft",7.48,"gal/ft^3"]
["Gallons per million gallons","gal_per_mil_gal",1000000,"gal/mil gal"]
["Seconds per minites","sec_per_min",60,"sec/min"]
["Cubic feet per cubic yard","cuft_per_cuyd",27,"ft^3/Yd^3"]
["Pounds per gal","lbs_per_gal",8.34,"lbs/gal"]
["Pounds of water per cubic foot","lbs_per_cuft",62.4,"lbs/ft^3"]
["Days per year","day_per_year",365,"days/year"]
["Inches per foot","in_per_ft",12,"in/ft"]
["Square feet per square yard","sqft_per_sqyd",27,"ft^2/yd^2"]