{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Calculator","Custom Amend Rate Tables"],"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,"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":"Calculator","index":0,"n_rows":25,"n_cols":28,"rows_kept":25,"cut":false,"hidden":false,"merged":[[22,0,22,11],[23,0,23,11],[24,0,24,11],[2,0,2,11],[3,0,3,11],[4,0,4,11],[5,0,5,11],[6,0,6,11]]}
["Preserving and Restoring Healthy Soil: Best Practices for Urban Construction"]
["Appendix A3:  Soil Amendment Calculator",null,null,null,"Version 2.0","2017-07-21"]
["This tool will calculate the application depths and volumes of compost and base topsoil needed to achieve project specifications for Planting Soil organic matter content and depth, based on user inputs of information regarding materials to be used (compost and base topsoil dry bulk density,  % organic matter and unit cost) and project specifications for Planting Soil organic matter and depth. "]
["This tool will also calculate the volumes of imported Planting Soil and mulch needed and material costs where applicable, based on user inputs of information regarding project specifications for Planting Soil and mulch depth and unit costs for each type of material."]
["Add additional rows below the example row for each planting area type and BMP option combination on the site and copy the appropriate values and formulas from Columns B to AB depending on the BMP option and planting area type to be constructed. "]
["Users should input materials and project specific values in the green cells or use default values where information is not available."]
["For more information, refer to Preserving and Restoring Healthy Soil: Best Practices for Urban Construction (TRCA, 2012)."]
[null,null,null,null,"TBD","TOM","POM","CBD","COM","CID","PSD","CAD"]
["Planting Area and BMP Option Combination Identification Code","BMP Option","Planting Area Type","Area (m2)","Base Topsoil Bulk Density (g/cm3 dry weight)","Base Topsoil or Subsoil Organic Matter (%)*","Planting Soil Organic Matter target (%)","Compost Bulk Density (g/cm3 dry weight)*","Compost Organic Matter (%)*","Compost Incorporation Depth (cm)","Total Planting Soil Depth (cm, unconsolidated)**","Compost Application Depth (cm)","Base Topsoil Application Depth (cm)","Imported Planting Soil Application Depth (cm)","Mulch Application Depth (cm)","Volume of Compost Required (m3)","Volume of Base Topsoil Required (m3)","Volume of Imported Planting Soil Required (m3)","Volume of Mulch Required (m3)","Unit Cost of Compost ($/m3)","Unit Cost of Base Topsoil ($/m3)","Unit Cost of Imported Planting Soil ($/m3)","Unit Cost of Mulch ($/m3)","Total Cost of Compost ($)","Total Cost of Base Topsoil ($)","Total Cost of Imported Planting Soil ($)","Total Cost of Mulch ($)","Planting Area/BMP Option Total Materials Cost ($)"]
["Example: 001-2C","Option 2 - Stockpile, replace and amend site or base topsoil","Lawn",1000,1.42,3.5,6,0.4,50,20,30,{"$f":"(E10*(F10-G10)/(E10*(F10-G10)-H10*(I10-G10)))*J10","v":3.3569739952718667},{"$f":"K10-L10","v":26.643026004728135},"N/A","N/A",{"$f":"L10/100*D10","v":33.56973995271867},{"$f":"M10/100*D10","v":266.43026004728137},"N/A","N/A",20,0,"N/A","N/A",{"$f":"P10*T10","v":671.39479905437338},{"$f":"Q10*U10","v":0},"N/A","N/A",{"$f":"SUM(X10:AA10)","v":671.39479905437338}]
[]
[null,"Option 1 - Protect native vegetation and soil","Natural Heritage Feature Buffer",1,"N/A","N/A","N/A","N/A","N/A","N/A","N/A",3,"N/A","N/A","N/A",{"$f":"L12/100*D12","v":0.03},"N/A","N/A","N/A",20,"N/A","N/A","N/A",{"$f":"P12*T12","v":0.6},"N/A","N/A","N/A",{"$f":"SUM(X12:AA12)","v":0.6}]
[null,"Option 2 - Harvest, store, replace and amend site or base topsoil","Lawn",1,1.42,2,6,0.4,50,20,30,{"$f":"(E13*(F13-G13)/(E13*(F13-G13)-H13*(I13-G13)))*J13","v":4.8797250859106525},{"$f":"K13-L13","v":25.120274914089347},"N/A","N/A",4.8797250859106522E-2,{"$f":"M13/100*D13","v":0.25120274914089347},"N/A","N/A",20,0,"N/A","N/A",0.97594501718213045,{"$f":"Q13*U13","v":0},"N/A","N/A",{"$f":"SUM(X13:AA13)","v":0.97594501718213045}]
[null,"Option 2 - Harvest, store, replace and amend site or base topsoil","Planting bed",1,1.42,2,11,0.4,50,20,30,{"$f":"(E14*(F14-G14)/(E14*(F14-G14)-H14*(I14-G14)))*J14","v":9.0063424947145858},20.993657505285412,"N/A",8,9.0063424947145865E-2,{"$f":"M14/100*D14","v":0.20993657505285412},"N/A",{"$f":"(O14/100)*D14","v":0.08},20,0,"N/A",42,1.8012684989429173,{"$f":"Q14*U14","v":0},"N/A",{"$f":"S14*W14","v":3.36},5.1612684989429169]
[null,"Option 2 - Harvest, store, replace and amend site or base topsoil","Tree pit",1,1.42,2,11,0.4,50,20,30,9.0063424947145858,20.993657505285412,"N/A",8,9.0063424947145865E-2,{"$f":"M15/100*D15","v":0.20993657505285412},"N/A",{"$f":"(O15/100)*D15","v":0.08},20,0,"N/A",42,1.8012684989429173,{"$f":"Q15*U15","v":0},"N/A",{"$f":"S15*W15","v":3.36},5.1612684989429169]
[null,"Option 3 - Amend site subsoil","Lawn",1,1.42,0,6,0.4,50,20,{"$f":"J16","v":20},6.523736600306278,"N/A","N/A","N/A",6.5237366003062777E-2,"N/A","N/A","N/A",20,"N/A","N/A","N/A",1.3047473200612556,"N/A","N/A","N/A",1.3047473200612556]
[null,"Option 3 - Amend site subsoil","Planting bed",1,1.42,0,11,0.4,50,20,{"$f":"J17","v":20},10.006406149903908,"N/A","N/A",8,0.10006406149903908,"N/A","N/A",{"$f":"(O17/100)*D17","v":0.08},20,"N/A","N/A",42,2.0012812299807816,"N/A","N/A",{"$f":"S17*W17","v":3.36},5.361281229980781]
[null,"Option 3 - Amend site subsoil","Tree pit",1,1.42,0,11,0.4,50,20,20,10.006406149903908,"N/A","N/A",8,0.10006406149903908,"N/A","N/A",{"$f":"(O18/100)*D18","v":0.08},20,"N/A","N/A",42,2.0012812299807816,"N/A","N/A",{"$f":"S18*W18","v":3.36},5.361281229980781]
[null,"Option 4 - Import planting soil mix (or planting soil mix and base topsoil)","Lawn",1,1.42,"N/A",6,"N/A","N/A","N/A",30,"N/A",{"$f":"K19-N19","v":0},30,"N/A","N/A",{"$f":"M19/100*D19","v":0},{"$f":"N19/100*D19","v":0.3},"N/A","N/A",10,50,"N/A","N/A",{"$f":"Q19*U19","v":0},{"$f":"R19*V19","v":15},"N/A",15]
[null,"Option 4 - Import planting soil mix (or planting soil mix and base topsoil)","Planting bed",1,1.42,"N/A",11,"N/A","N/A","N/A",30,"N/A",0,30,8,"N/A",{"$f":"M20/100*D20","v":0},{"$f":"N20/100*D20","v":0.3},{"$f":"O20/100*D20","v":0.08},"N/A",10,50,42,"N/A",{"$f":"Q20*U20","v":0},{"$f":"R20*V20","v":15},{"$f":"S20*W20","v":3.36},18.36]
[null,"Option 4 - Import planting soil mix (or planting soil mix and base topsoil)","Tree pit",1,1.42,"N/A",11,"N/A","N/A","N/A",30,"N/A",0,30,8,"N/A",0,0.3,{"$f":"O21/100*D21","v":0.08},"N/A",10,50,42,"N/A",0,15,{"$f":"S21*W21","v":3.36},18.36]
["Grand Total","N/A","N/A",{"$f":"SUM(D10:D11)","v":1000},"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A",{"$f":"SUM(P10:P11)","v":33.56973995271867},{"$f":"SUM(Q10:Q11)","v":266.43026004728137},{"$f":"SUM(R10:R11)","v":0},{"$f":"SUM(S10:S11)","v":0},"N/A","N/A","N/A",{"$f":"SUM(W10:W11)","v":0},671.39479905437338,0,0,0,671.39479905437338]
["* All bulk density values must be based on samples compacted to between 75 and 85% maximum dry density.  To convert bulk density \"as is\" (i.e., moist weight) to dry weight, multiply by the total solids content (e.g., if moisture content is 25%, solids content is 75% so multiply by 0.75."]
["** All organic matter measurements must be based on Loss-On-Ignition (LOI) test method."]
["*** Total Planting Soil depth includes compost incorporation and base topsoil horizons and should restore pre-construction topsoil (i.e., 'A' horizon) depth at a minimum, or 20 to 30 cm after consolidation and settling."]

{"sheet":"Custom Amend Rate Tables","index":1,"n_rows":52,"n_cols":11,"rows_kept":52,"cut":false,"hidden":false,"merged":[[51,1,51,10],[8,0,13,0],[14,0,19,0],[25,0,35,0],[36,0,46,0],[48,1,48,10],[50,1,50,10],[49,1,49,10]]}
["Preserving and Restoring Healthy Soil: Best Practices  for Urban Construction"]
["Appendix A4:  Table of custom compost application rates by soil type"]
[]
["Examples comparing custom compost application depths for lawns and trees and planting beds with typical sandy  and clayey textures, calculated using this Topsoil and Soil Amendment Calculator spreadsheet tool."]
[]
["LAWNS"]
[null,"TBD","TOM","POM","CBD","COM","CID","CAD","PSD","TAD","CAV"]
["Soil Texture","Base Topsoil Bulk Density (g/cm3 dry weight)*","Base Topsoil Organic Matter Content (%)**","Target Planting Soil Organic Matter Content (%)**","Compost Bulk Density (g/cm3 dry weight)*","Compost Organic Matter Content (%)**","Compost Incorporated Depth (cm)","Custom Compost Application Depth (cm, unconsolidated)","Planting Soil Depth (cm, unconsolidated)***","Custom Base Topsoil Application Depth (cm, unconsolidated)","Custom Compost Amendment - Volumetric Mixing (% compost incorporation)"]
["Sandy",1.5,0,6,0.4,50,20,{"$f":"B9*(C9-D9)/(B9*(C9-D9)-E9*(F9-D9))*G9","v":6.7669172932330826},30,{"$f":"I9-H9","v":23.233082706766918},{"$f":"H9/G9*100","v":33.834586466165412}]
[null,1.5,1,6,0.4,50,20,5.9760956175298796,30,{"$f":"I10-H10","v":24.023904382470121},{"$f":"H10/G10*100","v":29.880478087649397}]
[null,1.5,2,6,0.4,50,20,5.0847457627118642,30,{"$f":"I11-H11","v":24.915254237288135},25.423728813559322]
[null,1.5,3,6,0.4,50,20,4.0723981900452486,30,{"$f":"I12-H12","v":25.927601809954751},20.361990950226243]
[null,1.5,4,6,0.4,50,20,2.9126213592233006,30,27.087378640776699,14.563106796116504]
[null,1.5,4.4000000000000004,6,0.4,50,20,2.3999999999999995,30,27.6,11.999999999999996]
["Fine Silt and Clay",1.35,0,6,0.4,50,20,6.3035019455252925,30,{"$f":"I15-H15","v":23.696498054474709},31.517509727626464]
[null,1.35,1,6,0.4,50,20,5.5441478439425049,30,{"$f":"I16-H16","v":24.455852156057496},27.720739219712527]
[null,1.35,2,6,0.4,50,20,4.6956521739130439,30,{"$f":"I17-H17","v":25.304347826086957},23.478260869565222]
[null,1.35,3,6,0.4,50,20,3.7413394919168597,30,{"$f":"I18-H18","v":26.258660508083139},18.706697459584298]
[null,1.35,4,6,0.4,50,20,2.6600985221674875,30,27.339901477832512,13.300492610837439]
[null,1.35,4.4000000000000004,6,0.4,50,20,2.1862348178137649,30,27.813765182186234,10.931174089068826]
[]
[]
["PLANTING BEDS AND TREE PITS"]
[null,"TBD","TOM","POM","CBD","COM*","CID","CAD","PSD","TAD","CAV"]
["Soil Type","Base Topsoil Bulk Density (g/cm3 dry weight)*","Base Topsoil Organic Matter Content (%)**","Target Planting Soil Organic Matter Content (%)**","Compost Bulk Density (g/cm3 dry weight)*","Compost Organic Matter Content (%)**","Compost Incorporation Depth (cm, unconsolidated)","Custom Compost Application Depth (cm, unconsolidated)","Planting Soil Depth (cm, unconsolidated)***","Custom Base Topsoil Application Depth (cm, unconsolidated)","Custom Compost Amendment - Volumetric Mixing (% compost incorporation)"]
["Sandy Soil",1.5,0,11,0.4,50,20,{"$f":"B26*(C26-D26)/(B26*(C26-D26)-E26*(F26-D26))*G26","v":10.2803738317757},50,{"$f":"I26-H26","v":39.719626168224302},{"$f":"H26/G26*100","v":51.401869158878498}]
[null,1.5,1,11,0.4,50,20,9.8039215686274499,50,{"$f":"I27-H27","v":40.196078431372548},{"$f":"H27/G27*100","v":49.019607843137244}]
[null,1.5,2,11,0.4,50,20,9.2783505154639165,50,{"$f":"I28-H28","v":40.721649484536087},46.391752577319586]
[null,1.5,3,11,0.4,50,20,8.695652173913043,50,{"$f":"I29-H29","v":41.304347826086953},43.478260869565219]
[null,1.5,4,11,0.4,50,20,8.0459770114942515,50,41.954022988505749,40.229885057471257]
[null,1.5,5,11,0.4,50,20,7.3170731707317067,50,{"$f":"I31-H31","v":42.682926829268297},{"$f":"H31/G31*100","v":36.585365853658537}]
[null,1.5,6,11,0.4,50,20,6.4935064935064934,50,43.506493506493506,{"$f":"H32/G32*100","v":32.467532467532465}]
[null,1.5,7,11,0.4,50,20,5.5555555555555545,50,44.444444444444443,27.777777777777775]
[null,1.5,8,11,0.4,50,20,4.4776119402985071,50,45.522388059701491,22.388059701492537]
[null,1.5,9,11,0.4,50,20,3.225806451612903,50,46.774193548387096,16.129032258064516]
[null,1.5,9.4,11,0.4,50,20,2.6666666666666661,50,47.333333333333336,13.33333333333333]
["Fine Silt and Clay Soils",1.35,0,11,0.4,50,20,9.7536945812807883,50,{"$f":"I37-H37","v":40.24630541871921},{"$f":"H37/G37*100","v":48.768472906403943}]
[null,1.35,1,11,0.4,50,20,9.2783505154639165,50,{"$f":"I38-H38","v":40.721649484536087},{"$f":"H38/G38*100","v":46.391752577319586}]
[null,1.35,2,11,0.4,50,20,8.7567567567567579,50,{"$f":"I39-H39","v":41.243243243243242},43.78378378378379]
[null,1.35,3,11,0.4,50,20,8.1818181818181817,50,{"$f":"I40-H40","v":41.81818181818182},40.909090909090907]
[null,1.35,4,11,0.4,50,20,7.5449101796407181,50,{"$f":"I41-H41","v":42.455089820359284},37.724550898203589]
[null,1.35,5,11,0.4,50,20,6.8354430379746844,50,43.164556962025316,{"$f":"H42/G42*100","v":34.177215189873422}]
[null,1.35,6,11,0.4,50,20,6.0402684563758395,50,43.959731543624159,{"$f":"H43/G43*100","v":30.201342281879196}]
[null,1.35,7,11,0.4,50,20,5.1428571428571432,50,44.857142857142854,25.714285714285719]
[null,1.35,8,11,0.4,50,20,4.1221374045801529,50,45.877862595419849,20.610687022900763]
[null,1.35,9,11,0.4,50,20,2.9508196721311473,50,47.049180327868854,14.754098360655737]
[null,1.35,9.4,11,0.4,50,20,2.432432432432432,50,47.567567567567565,12.16216216216216]
[]
[null,"If concentration of any of the 11 regulated metals in the compost to be used are near the maximum allowable limits in Ontario and calculated compost application depth is >7 cm, calculation of the maximum allowable compost application rate that would still meet CFIA Trade Memorandum T-4-93 limits for metal loads using Ontario Compost Quality Standards Appendix 7 needs to be performed to determine if the compost is suitable for amendment at that depth."]
["*","All bulk density values must be based on samples compacted to between 75 and 85% maximum dry density.  To convert bulk density \"as is\" (i.e., moist weight) to dry weight, multiply by the total solids content (e.g., if moisture content is 30%, solids content is 70% so multiply by 0.7)."]
["**","All organic matter values must be based on Loss-On-Ignition test (LOI)."]
["***","Total Planting Soil depth includes compost incorportion and base topsoil horizons and should restore pre-construction depth at a minimum or 20 to 30 cm after consolidation and settling."]