{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Trenching Calculations - New","Example Trenching Calculations ","Trenching Calculations - OLD"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Trenching Calculations - New","index":0,"n_rows":70,"n_cols":11,"rows_kept":70,"cut":false,"hidden":false,"merged":[[0,0,0,9],[1,0,1,9],[2,0,2,9],[68,0,68,1]]}
[]
["1. Determine the total number of pipes in each trench segment (including spares) and the length of the segment and input into the yellow cell below."]
["2. SPI Line Item 15-1.a, 151.b, 15.1.c, and 15.1.d will be calculated automatically and displayed in at the bottom of the table. This calculation is based upon the total number of pipes in each trench segment. For trench segments greater than 30 pipes, the table will automatically take the number of pipes in the trench segment, subtract 30 pipes, calculate the number of 10 pipe bundles, and find the equivalent length of the trench based upon the 10 pipe bundles. \n                                      Calculation = Length of Trench (feet) x ROUNDED UP[(Number of Pipes in Trench - 30 Pipes) / 10 Pipe Bundles]"]
[]
[]
["Trench Segment","Number of Pipes in Trench","Length of Trench Segment (feet)","SPI Item 15-1.a. Trenching Installation of trench containing  1-10 Plumbing Lines (linear foot of trench)","SPI Item 15-1.b. Trenching Installation of trench containing  11-20 Plumbing Lines (linear foot of trench)","SPI Item 15-1.c. Trenching Installation of trench containing  21-30 Plumbing Lines (linear foot of trench)","Additional pipes >30","ADDITIONAL bundle of 1-10 lines","SPI Item 15-1.d. Trenching Installation of additional bundle of 1-10 lines greater than 30 lines in trench (linear foot of trench)","Notes",true]
["A",null,null,{"$f":"IF($B7<=10,$C7,0)","v":0},{"$f":"IF(($B7>10)*AND($B7<=20),$C7,0)","v":0},{"$f":"IF(($B7>20),$C7,0)","v":0},{"$f":"IF(B7>30,(B7-30),0)","v":0},{"$f":"ROUNDUP($G7/10,0)","v":0},{"$f":"C7*H7","v":0},null,{"$f":"IF(OR(B7<>\"\",C7<>\"\"),TRUE,FALSE)","v":false}]
["B",null,null,0,0,0,0,0,0,null,false]
["C",null,null,0,0,0,0,0,0,null,false]
["D",null,null,0,0,0,0,0,0,null,false]
["E",null,null,0,0,0,0,0,0,null,false]
["F",null,null,0,0,0,0,0,0,null,false]
["G",null,null,0,0,0,0,0,0,null,false]
["H",null,null,0,0,0,0,0,0,null,false]
["I",null,null,0,0,0,0,0,0,null,false]
["J",null,null,0,0,0,0,0,0,null,false]
["K",null,null,0,0,0,0,0,0,null,false]
["L",null,null,0,0,0,0,0,0,null,false]
["M",null,null,0,0,0,0,0,0,null,false]
["N",null,null,0,0,0,0,0,0,null,false]
["O",null,null,0,0,0,0,0,0,null,false]
["P",null,null,0,0,0,0,0,0,null,false]
["Q",null,null,0,0,0,0,0,0,null,false]
["R",null,null,0,0,0,0,0,0,null,false]
["S",null,null,0,0,0,0,0,0,null,false]
["T",null,null,0,0,0,0,0,0,null,false]
["U",null,null,0,0,0,0,0,0,null,false]
["V",null,null,0,0,0,{"$f":"IF(B28>30,(B28-30),0)","v":0},0,{"$f":"C28*H28","v":0},null,{"$f":"IF(OR(B28<>\"\",C28<>\"\"),TRUE,FALSE)","v":false}]
["W",null,null,0,0,0,0,0,0,null,false]
["X",null,null,0,0,0,0,0,0,null,false]
["Y",null,null,0,0,0,0,0,0,null,false]
["Z",null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
[null,null,null,0,0,0,0,0,0,null,false]
["Actual Footage of Trench",null,{"$f":"SUM(C7:C68)","v":0},{"$f":"SUM(D7:D68)","v":0},{"$f":"SUM(E7:E68)","v":0},{"$f":"SUM(F7:F68)","v":0},null,null,{"$f":"SUM(I7:I68)","v":0},null,true]
[null,null,null,"SPI Item 15-1.a.","SPI Item 15-1.b.","SPI Item 15-1.c.",null,null,"SPI Item 15-1.d.",null,true]

{"sheet":"Example Trenching Calculations ","index":1,"n_rows":32,"n_cols":10,"rows_kept":32,"cut":false,"hidden":false,"merged":[[29,0,29,1],[30,0,30,1],[28,0,28,1],[1,0,1,9],[0,0,0,9],[2,0,2,9]]}
[]
["1. Determine the total number of pipes in each trench segment (including spares) and the length of the segment and input into the yellow cell below."]
["2. SPI Line Item 15-1.a, 151.b, 15.1.c, and 15.1.d will be calculated automatically and displayed in at the bottom of the table. This calculation is based upon the total number of pipes in each trench segment. For trench segments greater than 30 pipes, the table will automatically take the number of pipes in the trench segment, subtract 30 pipes, calculate the number of 10 pipe bundles, and find the equivalent length of the trench based upon the 10 pipe bundles. \n                                      Calculation = Length of Trench (feet) x ROUNDED UP[(Number of Pipes in Trench - 30 Pipes) / 10 Pipe Bundles]"]
[]
[]
["Trench Segment","Number of Pipes in Trench","Length of Trench Segment (feet)","SPI Item 15-1.a. Trenching Installation of trench containing  1-10 Plumbing Lines (linear foot of trench)","SPI Item 15-1.b. Trenching Installation of trench containing  11-20 Plumbing Lines (linear foot of trench)","SPI Item 15-1.c. Trenching Installation of trench containing  21-30 Plumbing Lines (linear foot of trench)","Additional pipes >30","ADDITIONAL bundle of 1-10 lines","SPI Item 15-1.d. Trenching Installation of additional bundle of 1-10 lines greater than 30 lines in trench (linear foot of trench)","Notes"]
["A",26,48,{"$f":"IF($B7<=10,$C7,0)","v":0},{"$f":"IF(($B7>10)*AND($B7<=20),$C7,0)","v":0},{"$f":"IF(($B7>20),$C7,0)","v":48},{"$f":"IF(B7>30,(B7-30),0)","v":0},{"$f":"ROUNDUP($G7/10,0)","v":0},{"$f":"C7*H7","v":0},"Leaving Equipment Compound"]
["B",1,28,28,0,0,0,0,0,"Trench to MPX-1"]
["C",25,52,0,0,52,0,0,0,"All pipes not including MPX-1"]
["D",2,52,52,0,0,0,0,0,"MPX-6 and Effluent Discharge"]
["E",23,16,0,0,16,0,0,0]
["F",1,8,8,0,0,0,0,0,"Trench to AS-10"]
["G",22,10,0,0,10,0,0,0]
["H",7,118,118,0,0,0,0,0,"Trench including AS-1, AS-9, AS-7, AS-8, AS-16, AS-17, and MPX-5"]
["I",15,24,0,24,0,0,0,0]
["J",3,40,40,0,0,0,0,0,"Trench including AS-11, AS-2, and MPX-2"]
["K",12,18,0,18,0,0,0,0]
["L",8,146,146,0,0,0,0,0,"Trench including AS-18, AS-14, AS-4, AS-15, AS-5, AS-6, MPX-4, and MPX-7"]
["M",4,56,56,0,0,0,0,0,"Trench including AS-12, AS-13, AS-3, and MPX-3"]
["N",null,0,0,0,0,0,0,0]
["O",null,0,0,0,0,0,0,0]
["P",50,100,0,0,100,20,2,200,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["Q",30,100,0,0,100,0,0,0,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["R",32,100,0,0,100,2,1,100,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["S",39,100,0,0,100,9,1,100,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["T",42,100,0,0,100,12,2,200,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["U",57,100,0,0,100,27,3,300,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["V",60,100,0,0,100,30,3,300,"MISC TEST CALCULATIONS NOT IN FIGURE"]
["Actual Footage of Trench",null,{"$f":"SUM(C7:C28)","v":1316},{"$f":"SUM(D7:D28)","v":448},{"$f":"SUM(E7:E28)","v":42},{"$f":"SUM(F7:F28)","v":826},null,null,{"$f":"SUM(I7:I28)","v":1200}]
["10% Contingent (rounded)",null,{"$f":"ROUND((C29*0.1),0)","v":132},{"$f":"ROUND((D29*0.1),0)","v":45},{"$f":"ROUND((E29*0.1),0)","v":4},{"$f":"ROUND((F29*0.1),0)","v":83},null,null,{"$f":"ROUND((I29*0.1),0)","v":120}]
["Total Scoped Units ",null,{"$f":"SUM(C29:C30)","v":1448},{"$f":"SUM(D29:D30)","v":493},{"$f":"SUM(E29:E30)","v":46},{"$f":"SUM(F29:F30)","v":909},null,null,{"$f":"SUM(I29:I30)","v":1320}]
[null,null,null,"SPI Item 15-1.a.","SPI Item 15-1.b.","SPI Item 15-1.c.",null,null,"SPI Item 15-1.d."]

{"sheet":"Trenching Calculations - OLD","index":2,"n_rows":77,"n_cols":7,"rows_kept":77,"cut":false,"hidden":false,"merged":[[1,0,1,5],[76,0,76,2],[3,0,3,5],[2,0,2,5],[8,0,8,5],[7,0,7,5],[6,0,6,5],[5,0,5,5],[4,0,4,5]]}
[]
["CALCULATIONS FOR OLD CONTRACT.  ONLY USE IF THE PREVIOUS CONTRACT RATES ARE IN EFFECT.  USE NEW TRENCHING CALCULATIONS TAB FOR CURRENT WORK SCOPES"]
["1. Determine scale based on one inch from Construction Drawing "]
["2. Input the number of feet into the yellow cell for the Construction Drawing scale below."]
["3. Determine the total number of pipes (including spares) that exit the equipment compound and input into the yellow cell below"]
["4. Determine the total number of segments and total number of pipes in each trench segment and input into the yellow cell below."]
["5. Measure the length of each trench segment and place in the yellow cell below. "]
["6. SPI Line Item 15-1 will be calculated automatically and displayed in blue at the bottom of the table. This calculation is based upon the total length of the trench (independent of pipes)."]
["7. SPI Line Item 15-2 will be calculated automatically and displayed in green  at the bottom of the table. This calculation is based upon the total number of pipes in each trench segment. For trench segments less than 8 pipes, a value of 0 will be displayed.  For trench segments greater than 8 pipes, the table will automatically take the number of pipes in the trench segment, subtract 8 pipes, calculate the number of pipe pairs, and find the equivalent length of the trench based upon the pipe pairs. \n\n                                      Calculation = Length of Trench (feet) x (Number of Pipes in Trench - 8 Pipes) / 2 Pipes per Pair"]
[]
[null,"Scale: ","1 inch = ",20,"feet"]
[]
[]
["Trench Segment","Number of Pipes in Trench","Measured Length of Trench Segment (in)","Length of Trench Segment (feet)","Length of Trench Segment >8 pipes  (feet)","Notes",true]
["A",26,2.4,{"$f":"$D$11*C15","v":48},{"$f":"IF(B15>8,D15*(B15-8)*0.5,0)","v":432},"Leaving Equipment Compound",{"$f":"IF(OR(B15<>\"\",C15<>\"\"),TRUE,FALSE)","v":true}]
["B",1,1.4,28,0,"Trench to MPX-1",{"$f":"IF(OR(B16<>\"\",C16<>\"\"),TRUE,FALSE)","v":true}]
["C",25,2.6,52,442,"All pipes not including MPX-1",true]
["D",2,2.6,52,0,"MPX-6 and Effluent Discharge",true]
["E",23,0.8,16,120,null,true]
["F",1,0.4,8,0,"Trench to AS-10",true]
["G",22,0.5,10,70,null,true]
["H",7,5.9,118,0,"Trench including AS-1, AS-9, AS-7, AS-8, AS-16, AS-17, and MPX-5",true]
["I",15,1.2,24,84,null,true]
["J",3,2,40,0,"Trench including AS-11, AS-2, and MPX-2",true]
["K",12,0.9,18,36,null,true]
["L",8,7.3,146,0,"Trench including AS-18, AS-14, AS-4, AS-15, AS-5, AS-6, MPX-4, and MPX-7",true]
["M",4,2.8,56,0,"Trench including AS-12, AS-13, AS-3, and MPX-3",true]
["N",null,null,0,0,null,false]
["O",null,null,0,0,null,false]
["P",null,null,0,0,null,false]
["Q",null,null,0,0,null,false]
["R",null,null,0,0,null,false]
["S",null,null,0,0,null,false]
["T",null,null,0,0,null,false]
["U",null,null,0,0,null,false]
["V",null,null,null,null,null,false]
["W",null,null,null,null,null,false]
["X",null,null,null,null,null,false]
["Y",null,null,null,null,null,false]
["Z",null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
[null,null,null,null,null,null,false]
["SPI Line Item 15-1. Trenching and Installation of Plumbing (and \n           Electrical) Lines in Trench - up to 8 plumbing lines [Includes an extra 20%]",null,null,{"$f":"SUM(D15:D76)*1.2","v":739.19999999999993},{"$f":"SUM(E15:E76)*1.2","v":1420.8},"SPI Line Item 15-2. Trenching and Installation - Additional 2 plumbing lines [Includes an extra 20%]",true]