{"text_format":"2","workbook":"xlsx","n_sheets":5,"sheets":["Instructions","Inputs & Cost Comparison","Conventional Project Cost","GOPHURRS Project Cost","CAPEX Amortization"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","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":"Instructions","index":0,"n_rows":21,"n_cols":2,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Instructions"]
[null,"Required only for Category 1 and Category 2"]
[1,"The purpose of this template is to provide a starting point for estimating cost of an underground power distribution project.  This template can be used to compare costs for a project with and without introduction of advanced technology resulting from the GOPHURRS program. "]
[{"$f":"+A3+1","v":2},"The spreadsheet has been created to serve as a starting point and example for level of sophistication of the analysis.  Modification is encouraged, as needed."]
[3,"This worksheet is intended to provide guidance on the per km cost metric.  The input values are for example only.  "]
[4,"Color coding is provided with blue for key input parameter, tan for text and copied parameters, green for intermediate calculations, and rust for key calculated values. See color code chart below. "]
[5,"This tool assumes all equipment will be rented.  If needed, a page has been added to the spreadsheet for amortization of capital expense.  The amortized amount can be added to the base model for a total project cost."]
[6,"Overhead costs should be included in the total cost.  Provisions have been made to include an overhead rate for each of the four cost categories and calculate the fully burdened cost."]
[7,"A line for instrumentation cost has been included in the materials and material transport section.  GOPHURRS is projected to develop instrumentation that will need to be accounted for."]
[8,"Cost comparisons and percent reduction are shown on the next tab."]
[" "]
[" ","Key input parameters"]
[" ","Text and copied parameters"]
[" ","Intermediate calculations"]
[" ","Key calculated values"]
[" "]
[" "]
[" "]
[" "]
[" "]
[" "]

{"sheet":"Inputs & Cost Comparison","index":1,"n_rows":19,"n_cols":4,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Input Parameters","Amount","Units"]
[null,"Installed Distance",3000,"m "]
[null,"Overhead Rate:  Consultant & Contractor",0.1]
[null,"Overhead Rate:  Utility Labor",0.5]
[null,"Overhead Rate:  Material & Material Transport",0.2]
[null,"Overhead Rate:  Other costs",0.2]
[null,"Project Duration",24,"months"]
[]
[]
[null,"Cost Category","Conventional Project Cost","GOPHURRS Project Cost"]
[null,"Consultant & Contractor Cost",{"$f":"+'Conventional Project Cost'!G42","v":727100},{"$f":"+'GOPHURRS Project Cost'!G42","v":381150}]
[null,"Utility Labor Cost",{"$f":"+'Conventional Project Cost'!G50","v":157500},{"$f":"+'GOPHURRS Project Cost'!G50","v":45000}]
[null,"Material & Material Transport Cost",{"$f":"+'Conventional Project Cost'!G66","v":873600},{"$f":"+'GOPHURRS Project Cost'!G66","v":783600}]
[null,"Other Costs",{"$f":"+'Conventional Project Cost'!G74","v":102000},{"$f":"+'GOPHURRS Project Cost'!G74","v":78000}]
[null,"Total Cost",{"$f":"SUM(C12:C15)","v":1860200},{"$f":"SUM(D12:D15)","v":1287750}]
[null,"Cost per km ($/km)",{"$f":"+C16*1000/$C$3","v":620066.66666666663},{"$f":"+D16*1000/$C$3","v":429250}]
[null,"Cost per mile ($/mile)",{"$f":"+C17/0.621","v":998497.04777240998},{"$f":"+D17/0.621","v":691223.83252818033}]
[null,"Percent Cost Reduction",null,{"$f":"+(C16-D16)/C16","v":0.30773572734114613}]

{"sheet":"Conventional Project Cost","index":2,"n_rows":74,"n_cols":8,"rows_kept":74,"cut":false,"hidden":false,"merged":[[1,1,1,6]]}
[]
[null,"Conventional Project Cost"]
[]
[null,"Input Parameters","Amount","Units",null,"Results Summary","Units","Value"]
[null,"Installed Distance",{"$f":"+'Inputs & Cost Comparison'!C3","v":3000},"m ",null,"Project Cost","($)",{"$f":"+G42+G50+G66+G74","v":1860200}]
[null,"Overhead Rate:  Consultant & Contractor",{"$f":"+'Inputs & Cost Comparison'!C4","v":0.1},null,null,"Cost per km","($/km)",{"$f":"+H5*1000/(C5)","v":620066.66666666663}]
[null,"Overhead Rate:  Utility Labor",{"$f":"+'Inputs & Cost Comparison'!C5","v":0.5},null,null,"Cost per mile","($/mile)",{"$f":"+H6/0.621","v":998497.04777240998}]
[null,"Overhead Rate:  Material & Material Transport",{"$f":"+'Inputs & Cost Comparison'!C6","v":0.2}]
[null,"Overhead Rate:  Other costs",{"$f":"+'Inputs & Cost Comparison'!C7","v":0.2}]
[null,"Project Duration",{"$f":"+'Inputs & Cost Comparison'!C8","v":24},"months"]
[]
[null,"1.  Consultant & Contractor Costs","Quantity","Units","Cost per Unit","Units","Value","Comment"]
[null,"Project Management",300,"Hours",100,"($/Hour)",{"$f":"+C13*E13","v":30000}]
[null,"Site preparation (vegetation control)",{"$f":"+C$5","v":3000},"m",5,"($/m)",{"$f":"+E14*C14","v":15000}]
[null,"Excavation & Backfilling"," "]
[null,"  a. Duct bank and direct conduit burial",{"$f":"+C$5","v":3000},"m",30,"($/m)",{"$f":"+E16*C16","v":90000},"Based upon depth"]
[null,"  b. Junction boxes",100,"Each",10,"($)",2000,"Based upon quantity and box dimensions"]
[null,"  c. Vaults",10,"Each",10,"($)",2000,"Based upon quantity and vault dimensions"]
[null,"  d. Access shafts",20,"Each",50,"($)",2000,"If needed for tunneling"]
[null," e. Spoil and waste management",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E20*C20","v":30000},"Based upon depth"]
[null,"Utility day-lighting (pot holing, underground survey/mapping)",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E21*C21","v":30000}]
[null,"Repaving",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E22*C22","v":30000}]
[null,"Restoring landscapes (vegetation)",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E23*C23","v":30000}]
[null,"Install duct banks and conduits",{"$f":"+C$5","v":3000},"m",5,"($/m)",{"$f":"+E24*C24","v":15000},"Based upon quantity and unit cost"]
[null,"Install junction boxes",{"$f":"+C17","v":100},"Each",10,"($)",2000,"Based upon quantity and unit cost"]
[null,"Install vaults",{"$f":"+C18","v":10},"Each",20,"($)",2000,"Based upon quantity and unit cost"]
[null,"Install access shafts",20,"Each",50,"($)",2000,"If needed for tunneling"]
[null,"Install pads for pad-mount equipment",30,"Each",10,"($)",2000,"Based upon quantity and unit cost"]
[null,"Install cables",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E29*C29","v":30000},"Based upon depth and quantity"]
[null,"Install joints (splice, termination, elbow)",100,"Each",10,"($)",2000,"Based upon quantity and unit cost"]
[null,"Transportation","Line Item",null," ",null,25000,"Quantify amount"]
[null,"Instrumentation (if needed)","Line Item",null," ",null,0,"Quantify amount"]
[null,"Permitting","Line Item",null," ",null,50000,"Quantify amount"]
[null,"Access roads and matting","Line Item",null," ",null,50000,"Quantify amount"]
[null,"Environmental Health & Safety","Line Item",null," ",null,75000,"Quantify amount"]
[null,"Temporary facilities","Line Item",null," ",null,20000,"Quantify amount"]
[null,"Equipment rental","Line Item",null," ",null,100000,"Total equipment rental cost"]
[null,"Damages and repairs","Line Item",null," ",null,25000,"Estimate amount"]
[null,"Direct Cost:  Consultant & Contractor","($)",null,null,null,{"$f":"SUM(G13:G38)","v":661000}]
[null,"Overhead Rate:  Consultant & Contractor"," ",null,null,null,{"$f":"+C6","v":0.1}]
[null,"Overhead:  Consultant & Contractor Cost"," ",null,null,null,{"$f":"+G39*G40","v":66100}]
[null,"Total Consultant & Contractor Cost","($)",null,null,null,{"$f":"+G41+G39","v":727100}]
[]
[null,"2.  Utility Labor Costs","Quantity","Units","Cost per Unit","Units","Value","Comment"]
[null,"Project Management",1000,"Hours",100,"($/Hour)",{"$f":"+C45*E45","v":100000},"Utility project management"]
[null,"Utility installations","Line Item",null," ",null,5000]
[null,"Direct Cost:  Utility Labor ","($)",null,null,null,{"$f":"SUM(G45:G46)","v":105000}]
[null,"Overhead Rate:  Utility Labor",{"$nulls":4},{"$f":"+C7","v":0.5}]
[null,"Overhead:  Utility Labor","($)",null,null,null,{"$f":"+G48*G47","v":52500}]
[null,"Total Utility Labor Cost","($/km)",null,null,null,{"$f":"+G49+G47","v":157500}]
[]
[null,"3.  Material & Material Transport Costs","Quantity","Units","Cost per Unit","Units","Value","Comment"]
[null,"Cable",{"$f":"+C$5","v":3000},"m",100,"($/m)",{"$f":"+E53*C53","v":300000},"Material Cost"]
[null,"Conduit",{"$f":"+C$5","v":3000},"m",50,"($/m)",{"$f":"+E54*C54","v":150000},"Material Cost"]
[null,"Duct Banks (if needed)",{"$f":"+C$5","v":3000},"m",20,"($/m)",{"$f":"+E55*C55","v":60000},"Material Cost"]
[null,"Access shafts",{"$f":"+C19","v":20},"Each",100,"($)",2000,"Material Cost"]
[null,"Junction boxes",{"$f":"+C19","v":20},"Each",10,"($)",2000,"Material Cost"]
[null,"Vaults",{"$f":"+C18","v":10},"Each",100,"($)",2000,"Material Cost"]
[null,"Equipment mounting pads",{"$f":"+C28","v":30},"Each",500,"($)",2000,"Material Cost"]
[null,"Terminations",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E60*C60","v":30000},"Material Cost"]
[null,"Materials Transport & Handling",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E61*C61","v":30000},"Transportation Cost based on $/m"]
[null,"Consumables",3000,"m",50,"($/m)",{"$f":"+E62*C62","v":150000},"Material Cost"]
[null,"Direct Cost:  Material & Material Transport","($)",null,null,null,{"$f":"SUM(G53:G62)","v":728000}]
[null,"Overhead Rate:  Material & Material Transport",{"$nulls":4},{"$f":"+$C$8","v":0.2}]
[null,"Overhead:  Material & Material Transport","($)",null,null,null,{"$f":"+G64*G63","v":145600}]
[null,"Total Material & Material Transport Cost","($/m)",null,null,null,{"$f":"+G65+G63","v":873600}]
[]
[null,"4. Other Costs"," "]
[null,"AFUDC & Interest","Line Item",null," ",null,50000,"Quantify Amount"]
[null,"Insurance","Line Item",null,null,null,35000,"Quantify Amount"]
[null,"Direct Other Costs","($)",null,null,null,{"$f":"+G70+G69","v":85000}]
[null,"Overhead Rate:  Other Costs",{"$f":"+C9","v":0.2},null,null,null,{"$f":"+C9","v":0.2}]
[null,"Overhead:  Other Costs","($)",null,null,null,{"$f":"+G71*G72","v":17000}]
[null,"Total Other Costs","($/m)",null,null,null,{"$f":"+G73+G71","v":102000}]

{"sheet":"GOPHURRS Project Cost","index":3,"n_rows":74,"n_cols":8,"rows_kept":74,"cut":false,"hidden":false,"merged":[[1,1,1,6]]}
[]
[null,"GOPHURRS Project Cost"]
[]
[null,"Input Parameters","Amount","Units",null,"Results Summary","Units","Value"]
[null,"Installed Distance",{"$f":"+'Inputs & Cost Comparison'!C3","v":3000},"m ",null,"Project Cost","($)",{"$f":"+G42+G50+G66+G74","v":1287750}]
[null,"Overhead Rate:  Consultant & Contractor",{"$f":"+'Inputs & Cost Comparison'!C4","v":0.1},null,null,"Cost per km","($/km)",{"$f":"+H5*1000/(C5)","v":429250}]
[null,"Overhead Rate:  Utility Labor",{"$f":"+'Inputs & Cost Comparison'!C5","v":0.5},null,null,"Cost per mile","($/mile)",{"$f":"+H6/0.621","v":691223.83252818033}]
[null,"Overhead Rate:  Material & Material Transport",{"$f":"+'Inputs & Cost Comparison'!C6","v":0.2}]
[null,"Overhead Rate:  Other costs",{"$f":"+'Inputs & Cost Comparison'!C7","v":0.2}]
[null,"Project Duration",{"$f":"+'Inputs & Cost Comparison'!C8","v":24},"months"]
[]
[null,"1.  Consultant & Contractor Costs","Quantity","Units","Cost per Unit","Units","Value","Comment"]
[null,"Project Management",250,"Hours",100,"($/Hour)",{"$f":"+C13*E13","v":25000}]
[null,"Site preparation (vegetation control)",{"$f":"+C$5","v":3000},"m",1,"($/m)",{"$f":"+E14*C14","v":3000}]
[null,"Excavation & Backfilling"," "]
[null,"  a. Duct bank and direct conduit burial",{"$f":"+C$5","v":3000},"m",1,"($/m)",{"$f":"+E16*C16","v":3000},"Based upon depth"]
[null,"  b. Junction boxes",50,"Each",1,"($)",2000,"Based upon quantity and box dimensions"]
[null,"  c. Vaults",5,"Each",1,"($)",2000,"Based upon quantity and vault dimensions"]
[null,"  d. Access shafts",5,"Each",25,"($)",2000,"If needed for tunneling"]
[null," e. Spoil and waste management",{"$f":"+C$5","v":3000},"m",1,"($/m)",{"$f":"+E20*C20","v":3000},"Based upon depth"]
[null,"Utility day-lighting (pot holing, underground survey/mapping)",{"$f":"+C$5","v":3000},"m",2.5,"($/m)",{"$f":"+E21*C21","v":7500}]
[null,"Repaving",{"$f":"+C$5","v":3000},"m",1,"($/m)",{"$f":"+E22*C22","v":3000}]
[null,"Restoring landscapes (vegetation)",{"$f":"+C$5","v":3000},"m",1,"($/m)",{"$f":"+E23*C23","v":3000}]
[null,"Install duct banks and conduits",{"$f":"+C$5","v":3000},"m",1,"($/m)",{"$f":"+E24*C24","v":3000},"Based upon quantity and unit cost"]
[null,"Install junction boxes",{"$f":"+C17","v":50},"Each",1,"($)",2000,"Based upon quantity and unit cost"]
[null,"Install vaults",{"$f":"+C18","v":5},"Each",10,"($)",2000,"Based upon quantity and unit cost"]
[null,"Install access shafts",5,"Each",50,"($)",2000,"If needed for tunneling"]
[null,"Install pads for pad-mount equipment",15,"Each",10,"($)",2000,"Based upon quantity and unit cost"]
[null,"Install cables",{"$f":"+C$5","v":3000},"m",5,"($/m)",{"$f":"+E29*C29","v":15000},"Based upon depth and quantity"]
[null,"Install joints (splice, termination, elbow)",75,"Each",5,"($)",2000,"Based upon quantity and unit cost"]
[null,"Transportation","Line Item",null," ",null,25000,"Quantify amount"]
[null,"Instrumentation (if needed)","Line Item",null," ",null,100000,"Quantify amount"]
[null,"Permitting","Line Item",null," ",null,40000,"Quantify amount"]
[null,"Access roads and matting","Line Item",null," ",null,10000,"Quantify amount"]
[null,"Environmental Health & Safety","Line Item",null," ",null,25000,"Quantify amount"]
[null,"Temporary facilities","Line Item",null," ",null,10000,"Quantify amount"]
[null,"Equipment rental","Line Item",null," ",null,50000,"Total equipment rental cost"]
[null,"Damages and repairs","Line Item",null," ",null,5000,"Estimate amount"]
[null,"Direct Cost:  Consultant & Contractor","($)",null,null,null,{"$f":"SUM(G13:G38)","v":346500}]
[null,"Overhead Rate:  Consultant & Contractor"," ",null,null,null,{"$f":"+C6","v":0.1}]
[null,"Overhead:  Consultant & Contractor Cost"," ",null,null,null,{"$f":"+G39*G40","v":34650}]
[null,"Total Consultant & Contractor Cost","($)",null,null,null,{"$f":"+G41+G39","v":381150}]
[]
[null,"2.  Utility Labor Costs","Quantity","Units","Cost per Unit","Units","Value","Comment"]
[null,"Project Management",250,"Hours",100,"($/Hour)",{"$f":"+C45*E45","v":25000},"Utility project management"]
[null,"Utility installations","Line Item",null," ",null,5000]
[null,"Direct Cost:  Utility Labor ","($)",null,null,null,{"$f":"SUM(G45:G46)","v":30000}]
[null,"Overhead Rate:  Utility Labor",{"$nulls":4},{"$f":"+C7","v":0.5}]
[null,"Overhead:  Utility Labor","($)",null,null,null,{"$f":"+G48*G47","v":15000}]
[null,"Total Utility Labor Cost","($/km)",null,null,null,{"$f":"+G49+G47","v":45000}]
[]
[null,"3.  Material & Material Transport Costs","Quantity","Units","Cost per Unit","Units","Value","Comment"]
[null,"Cable",{"$f":"+C$5","v":3000},"m",100,"($/m)",{"$f":"+E53*C53","v":300000},"Material Cost"]
[null,"Conduit",{"$f":"+C$5","v":3000},"m",50,"($/m)",{"$f":"+E54*C54","v":150000},"Material Cost"]
[null,"Duct Banks (if needed)",{"$f":"+C$5","v":3000},"m",20,"($/m)",{"$f":"+E55*C55","v":60000},"Material Cost"]
[null,"Access shafts",{"$f":"+C19","v":5},"Each",100,"($)",2000,"Material Cost"]
[null,"Junction boxes",{"$f":"+C19","v":5},"Each",10,"($)",2000,"Material Cost"]
[null,"Vaults",{"$f":"+C18","v":5},"Each",100,"($)",2000,"Material Cost"]
[null,"Equipment mounting pads",{"$f":"+C28","v":15},"Each",500,"($)",2000,"Material Cost"]
[null,"Terminations",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E60*C60","v":30000},"Material Cost"]
[null,"Materials Transport & Handling",{"$f":"+C$5","v":3000},"m",10,"($/m)",{"$f":"+E61*C61","v":30000},"Transportation Cost based on $/m"]
[null,"Consumables",3000,"m",25,"($/m)",{"$f":"+E62*C62","v":75000},"Material Cost"]
[null,"Direct Cost:  Material & Material Transport","($)",null,null,null,{"$f":"SUM(G53:G62)","v":653000}]
[null,"Overhead Rate:  Material & Material Transport",{"$nulls":4},{"$f":"+$C$8","v":0.2}]
[null,"Overhead:  Material & Material Transport","($)",null,null,null,{"$f":"+G64*G63","v":130600}]
[null,"Total Material & Material Transport Cost","($/m)",null,null,null,{"$f":"+G65+G63","v":783600}]
[]
[null,"4. Other Costs"," "]
[null,"AFUDC & Interest","Line Item",null," ",null,40000,"Quantify Amount"]
[null,"Insurance","Line Item",null,null,null,25000,"Quantify Amount"]
[null,"Direct Other Costs","($)",null,null,null,{"$f":"+G70+G69","v":65000}]
[null,"Overhead Rate:  Other Costs",{"$f":"+C9","v":0.2},null,null,null,{"$f":"+C9","v":0.2}]
[null,"Overhead:  Other Costs","($)",null,null,null,{"$f":"+G71*G72","v":13000}]
[null,"Total Other Costs","($/m)",null,null,null,{"$f":"+G73+G71","v":78000}]

{"sheet":"CAPEX Amortization","index":4,"n_rows":21,"n_cols":8,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[null,"Input Parameters","Units","Value","Comment"]
[null,"Overhead Rate:  CAPEX",null,1.1000000000000001]
[null,"Project Duration","months",24]
[]
[]
[null,"Capital Equipment Costs"," "]
[null,null,"Units","Capital Equipment #1:  Mapping & Survey","Capital Equipment #2:  Drilling","Capital Equipment  #3: Restoration Service","Capital Equipment  #4: (As needed)","Cost Modeling Tool can be expanded for additional capital equipment items"]
[null,"CAPEX ","($)",50000,75000,0,0]
[null,"Annual return on CAPEX ","(%)",0.08,0.08,0.08,0.08]
[null,"Useful life ","(yrs)",3,5,5,5]
[null,"Annual amortized cost recovery for tool CAPEX, including Annual return on CAPEX","($/year)",{"$f":"-PMT(D9,D10,D8)","v":19401.675702316414},{"$f":"-PMT(E9,E10,E8)","v":18784.234092512743},{"$f":"-PMT(F9,F10,F8)","v":0},{"$f":"-PMT(G9,G10,G8)","v":0}]
[null,"Monthly amortized cost recovery for tool CAPEX, including Annual return on CAPEX","($/month)",{"$f":"+D11/12","v":1616.8063085263677},{"$f":"+E11/12","v":1565.3528410427286},{"$f":"+F11/12","v":0},{"$f":"+G11/12","v":0}]
[null,"Capital Equipment Maintenance","% of CAPEX",0.1,0.1]
[null,"Annual Capital Equipment Maintenance Cost","($/year)",{"$f":"+D13*D8","v":5000},{"$f":"+E13*E8","v":7500},{"$f":"+F13*F8","v":0},{"$f":"+G13*G8","v":0}]
[null,"Monthly Capital Equipment Maintenance Cost","($/month)",{"$f":"+D14/12","v":416.66666666666669},{"$f":"+E14/12","v":625},{"$f":"+F14/12","v":0},{"$f":"+G14/12","v":0}]
[null,"Monthly Direct CAPEX","($/month)",{"$f":"+D15+D12","v":2033.4729751930345},{"$f":"+E15+E12","v":2190.3528410427289},{"$f":"+F15+F12","v":0},{"$f":"+G15+G12","v":0}]
[null,"Overhead Rate:  CAPEX",null,{"$f":"+$D2","v":1.1000000000000001},{"$f":"+$D2","v":1.1000000000000001},{"$f":"+$D2","v":1.1000000000000001},{"$f":"+$D2","v":1.1000000000000001}]
[null,"Overhead:  CAPEX","($)",{"$f":"+D16*D17","v":2236.820272712338},{"$f":"+E16*E17","v":2409.388125147002},{"$f":"+F16*F17","v":0},{"$f":"+G16*G17","v":0}]
[null,"Monthly Project CAPEX (amortized cost recovery + capital equipment maintenance)","($/month)",{"$f":"+D16+D18","v":4270.2932479053725},{"$f":"+E16+E18","v":4599.7409661897309},{"$f":"+F16+F18","v":0},{"$f":"+G16+G18","v":0}]
[null,"Term of Project CAPEX","($)",{"$f":"+D19*$D3","v":102487.03794972894},{"$f":"+E19*$D3","v":110393.78318855354},0,0]
[null,"Total CAPEX (Categories # 1 - 4)","($)",{"$f":"SUM(D20:G20)","v":212880.82113828248}]