{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Welcome ","Definitions","Applicant Info & Summary","1. Existing Conditions","2. WQ Buffer Impacts","3. Proposed On-Site Mitigation","4. Proposed Off-Site Mitigation","Form Lists (to be hidden sheet)"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":2,"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":"Welcome ","index":0,"n_rows":22,"n_cols":1,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["Chattanooga Buffer Tool Calculator"]
["Welcome to the Chattanooga Buffer Tool Calculator.  This calculator is available for developers and "]
["property owners proposing temporary or permanent land disturbance impacts within the regulated"]
["Water Quality (WQ) Buffer adjacent to streams, lakes or ponds with hydrologic connectivity, and"]
["wetlands.  This Buffer Tool is intended to assist applicants of land disturbing permits with calculating existing "]
["WQ Buffer requirements, proposed impacts, and resulting mitigation requirements for the WQ Buffer."]
[]
["Definitions for specific terms used in this Buffer Tool are available to review on the Definitions Tab."]
[]
["Users should first enter contact information and property data on the Applicant Info & Summary Tab and then follow Tabs 1-4 in order.  The Buffer Tool will update and calculate information as the applicant progresses through the Tabs."]
[]
["The only cells available to modify"]
["in the Buffer Tool are data entry"]
["cells for the applicant."]
[]
["Upon completion of entering"]
["data into the Buffer Tool, a"]
["summary of impacts to the"]
["WQ Buffer and the proposed"]
["mitigation is displayed on "]
["the Applicant Info &"]
["Summary Tab."]

{"sheet":"Definitions","index":1,"n_rows":18,"n_cols":1,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Definitions"]
["This tab includes relevant definitions for terms used in the Chattanooga Buffer Tool."]
[]
["Credit Ratio - The area of WQ Buffer credits generated for each mitigation practice selected."]
["Impact - Any disturbance within the designated WQ Buffer width in the project area.  See Permanent Impact, Temporary Impact."]
["Inner 30 Feet - The length of the WQ Buffer that is measured 0-30 FT perpendicular from the top of stream bank.  See Outer 30 Feet, WQ Buffer Width."]
["Mitigation Ratio - The area of buffer mitigation required per area of impacted WQ Buffer."]
["Outer 30 Feet - The length of the WQ Buffer that is measured 30-60 FT perpendicular from the top of stream bank.  This is only applicable where the required WQ Buffer Width is greater than 30 FT.  See Inner 30 Feet, WQ Buffer Width."]
["Permanent Impact - Any permanent clearing, grubbing, or disturbance to the WQ Buffer that will not be restored to native tree and shrub vegetation prior to project completion.  Permanent impacts include installation of one or more structures, pavement and impervious surfaces, stream/wetland loss, and non-native landscaping (e.g. lawn & garden)  See Impact, Temporary Impact."]
["Stream Bank Stabilization - Applying plant-based stream bank soil bioengineering to provide physical stability to eroding streambanks.  These mitigation sites typically include property acquisition; grading along the stream bank; bioengineering, engineered structures in urban watersheds, large streams, or banks exceeding 10-FT; planting native trees and shrubs in WQ Buffers; and site protection through covenants, conservation easements, and other legal deed restrictions sufficient to protect the stream bank stabilized and the adjacent WQ Buffer. These projects may require authorization from TDEC and/or USACE.  A minimum of 40 LF of Stream Bank Stabilization is required to generate WQ Buffer Mitigation Credits for the City of Chattanooga.  See Stream Restoration."]
["Stream Restoration - Returning or repairing the natural/historic functions of the site by improving natural geomorphic, hydraulic, physio-chemical, and biological functions and processes of a previously degraded stream such that the improved function is sustainable with little or no maintenance.  These mitigation sites typically include property acquisition, technical analyses of hydrology, hydraulics, sediment supply, physio-chemical parameters, physical habitat and other biological conditions; survey and engineering design; targeted ecological endpoints; physical construction and planting native trees and shrubs; and site protection through covenants, conservation easements, and other legal deed restrictions sufficient to protect the stream restoration area and the adjacent WQ Buffer.  These projects may require authorization from TDEC and/or USACE.  A minimum of 100 LF of Stream Restoration is required to generate WQ Buffer Mitigation Credits for the City of Chattanooga.  See Stream Bank Stabilization."]
["Temporary Impact - Any clearing, grubbing, or disturbance in the WQ Buffer area that will be replanted to current City standards with native tree and shrub vegetation prior to project completion.  See Impact, Permanent Impact."]
["Top of Bank - The uppermost limit of the active stream channel, typically indicated by a change in bank slope from steep to gentle.  TOB is defined in City code Sec 31-324.2.5 and allows for the modeled height of the 2 yr storm to be used to determine TOB if field indicators are not present.  TOB must be identified on the construction drawings and can be identified by a licensed Land Surveyor.  See WQ Buffer Width."]
["Water Resource - Lakes and ponds with hydrologic connectivity, wetlands, streams, or rivers."]
["Water Quality (WQ) Buffer Enhancement - Vegetation management activity within an existing WQ Buffer, such as removing invasive and nonnative vegetation and planting native trees and shrubs to improve water quality functions of the buffer.  These mitigation sites typically have some trees present but are not dominated by healthy trees and shrubs.  See WQ Buffer Restoration."]
["Water Quality (WQ) Buffer Restoration - Planting an existing stream buffer with native trees and shrubs where there are currently no trees or shrubs present.  The purpose is to restore water quality function of the WQ Buffer. These mitigation sites typically include installing trees and shrubs, monitoring, and long-term maintenance to ensure plant survival.  See WQ Buffer Enhancement."]
["Water Quality (WQ) Buffer Width - For streams: the distance measured perpendicular from the top of the stream bank. For lakes, ponds, and wetlands: the distance measured perpendicular from the normal pool elevation.  See Inner 30 Feet, Outer 30 Feet, Top of Stream Bank."]
["Water Quality (WQ) Buffer Area - The WQ Buffer Width multiplied by the length (streams) and/or perimeter (lakes, ponds, wetlands) of the water resource. See WQ Buffer Width."]

{"sheet":"Applicant Info & Summary","index":2,"n_rows":49,"n_cols":6,"rows_kept":49,"cut":false,"hidden":false,"merged":[[9,2,9,4],[48,1,48,3],[16,2,16,4],[21,2,21,4],[10,1,10,2],[33,1,33,3],[36,1,36,3],[34,1,34,3],[38,1,38,3],[46,1,46,3],[37,1,37,3],[42,1,42,3],[45,1,45,3],[43,1,43,3],[25,3,29,3]]}
["Project Summary"]
["Applicant should enter information into Steps 1-3 below.  "]
["Steps 4-5 will be populated by the Buffer Mitigation Tool as information is entered  on Tabs 1-4."]
[]
[null,null,"Cells for Applicant to enter data."]
[null,null,"Cells describing what data needs to be entered by Applicant."]
[null,null,"Cells performing calculations within the Buffer Mitigation Tool."]
[]
[1,"Project Name (as submitted to City of Chattanooga LDO)"]
[null,"Project Name:"]
[null,"Water Quality (WQ) Variance Request Number:"]
[]
[2,"Contact Information for Applicant and Property Owner"]
[null,"Applicant"]
[null,"First Name:",null,"Last Name:"]
[null,"Email:",null,"Phone Number:"]
[null,"Mailing Address:"]
[]
[null,"Property Owner"]
[null,"First Name:",null,"Last Name:"]
[null,"Email:",null,"Phone Number:"]
[null,"Mailing Address:"]
[]
[3,"Identify Location of Impacts to WQ Buffer in Project Area"]
[null,"Location of Primary Impacts",null,"Additional Adjacent Parcels with Impacts"]
[null,"Parcel ID:",null,"Additional Parcel IDs:"]
[null,"Street Address:"]
[null,"City:"]
[null,"State:"]
[null,"Zip:"]
[]
[4,"Summary of WQ Buffer Impacts"]
["Outputs below are populated by the Buffer Mitigation Tool."]
[null,"Required WQ Buffer Width (FT) for lakes, ponds, and wetlands = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E8","v":"Not Yet Calculated"}]
[null,"Required WQ Buffer Width (FT) for streams = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E9","v":"Not Yet Calculated"}]
[]
[null,"Existing WQ Buffer Area (SF) in the Project Area = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E10","v":"Not Yet Calculated"}]
[null,"Total WQ Buffer Impacts (SF) in the Project Area = ",null,null,{"$f":"Table38[[#Totals],[Column3]]","v":"Not Yet Calculated"}]
[null,"Percent of WQ Buffer Area impacted in the project area = ",null,null,{"$f":"IF(OR(E37=\"Not Yet Calculated\",E38=\"Not Yet Calculated\"),\"Not Yet Calculated\",IF(E38=0,0,E38/E37))","v":"Not Yet Calculated"}]
[]
[5,"Summary of Proposed Mitigation"]
["Outputs below are populated by the Buffer Mitigation Tool."]
[null,"Buffer Mitigation Requirement (SF) from Buffer Impacts = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E25","v":"Not Yet Calculated"}]
[null,"Required Off-Site Mitigation Area (SF) = ",null,null,{"$f":"'4. Proposed Off-Site Mitigation'!E27","v":"Not Yet Calculated"}]
[]
[null,"On-Site Mitigation Credits Proposed (SF) = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E34","v":"Not Yet Calculated"}]
[null,"Off-Site Mitigation Credits Proposed (SF) = ",null,null,{"$f":"'4. Proposed Off-Site Mitigation'!E36","v":"Not Yet Calculated"}]
[null,null,null,null,{"$f":"IF(AND(E46=\"Not Yet Calculated\",E47=\"Not Yet Calculated\"),\"\",(IF(F48=0,\"Success! The proposed mitigation credits satisfy WQ Buffer mitigation requirements.\",IF(F48>0,\"Attention! More mitigation credits are required.\",IF(F48<0,\"Proposed mitigation credits EXCEED mitigation requirements.  Do you want to proceed?\")))))","v":""},{"$f":"IF(E49=\"Not Yet Calculated\",\"\",SUBTOTAL(109,E44,E46)-SUBTOTAL(109,E47,E46))","v":""}]
[null,"EXCESS Mitigation Credits Proposed (SF) = ",null,null,{"$f":"IF(AND(E46=\"Not Yet Calculated\",E47=\"Not Yet Calculated\"),\"Not Yet Calculated\",SUBTOTAL(109,'3. Proposed On-Site Mitigation'!E37,'4. Proposed Off-Site Mitigation'!E38))","v":"Not Yet Calculated"}]

{"sheet":"1. Existing Conditions","index":3,"n_rows":55,"n_cols":5,"rows_kept":55,"cut":false,"hidden":false,"merged":[[14,1,14,4],[8,1,8,4],[24,1,24,3],[23,1,23,4],[43,1,43,3],[17,1,17,3],[15,1,15,3],[42,1,42,3],[16,1,16,3],[53,1,53,3],[54,1,54,3],[18,1,18,3],[48,1,48,3],[52,1,52,3],[51,1,51,4],[25,1,25,4],[26,1,26,3],[47,1,47,4],[46,1,46,3],[45,1,45,4],[34,1,34,4],[36,1,36,4],[35,1,35,3],[31,1,31,2]]}
["Tab 1. Existing Conditions",null,null,"WQ Variance Request #",{"$f":"LDP","v":0}]
["This step defines existing conditions and determines dimensions of the existing Water Quality (WQ) Buffer."]
["Grey cells indicate a calculation performed by the Buffer Mitigation Tool."]
["Green cells indicate information needed for the Buffer Mitigation Tool."]
["White cells indicate where the applicant should enter project information corresponding to the green cells."]
[]
[1,"Identify Watershed and Type of Water Resource"]
[null,"Instructions:"]
[null,"1) Open the TDEC Division of Water Resources Public Data Viewer. Type this link into your web browser:"]
[null,"https://tdeconline.tn.gov/dwr"]
[null,"2) Navigate to the project area using the Search Bar or the Zoom tool in the Map Viewer."]
[null,"3) Zoom out from the project area to identify the correct water resource the project drains into."]
[null,"4) Click the tab at the bottom of the browser window (or the resource itself) to open the Attribute Tables."]
[null,"5) Locate the Water Quality Assessment ID, name of the water body, and the Attainment Description."]
[null,"If the total project area includes multiple watersheds, use this buffer tool separately for each watershed."]
[null,"Water Quality Assessment ID (e.g. TN06020001007_0300):"]
[null,"Name the water body where the project drains. (e.g. Mackey Branch)"]
[null,"What is the Attainment Description?"]
[null,"Is the water resource a stream, lake or pond with hydrologic connectivity, or a wetland?"]
[null,{"$f":"IF(E19=\"Stream\",\"For streams ONLY, skip Step 2 and continue to Step 3.\",IF(E19=\"Lake or pond with hydrologic connectivity or wetland\",\"For lakes, ponds, or wetlands ONLY, continue to Step 2 and skip Step 3.\",IF(E19=\"Both Stream and Lake/Pond/Wetland\",\"For BOTH streams and lakes/ponds/wetlands in the project area, continue to Step 2 AND Step 3.\", \"\")))","v":""}]
[]
[2,"Required WQ Buffer for Lakes, Ponds, and Wetlands"]
[null,"Applicable for Lakes, Ponds, and Wetlands"]
[null,"- For lakes and ponds with hydrologic connectivity and wetlands, the WQ Buffer Width is measured perpendicular from the normal pool elevation."]
[null,"Required WQ Buffer Width (FT) adjacent to lakes, ponds, and wetlands in project area =",null,null,{"$f":"IF(OR(E19=\"Lake or pond with hydrologic connectivity or wetland\",E19=\"Both Stream and Lake/Pond/Wetland\"),30,IF(E19=\"Stream\",\"Not Applicable\",\"Not Yet Determined\"))","v":"Not Yet Determined"}]
[null,"- The total existing WQ Buffer area (square feet) for a lake/pond/wetland is the required WQ Buffer width multiplied by the circumference or perimeter of the water resource in the project area, regardless of existing or proposed conditions in the buffer area."]
[null,"What is the TOTAL existing WQ Buffer Area (SF) adjacent to lakes, ponds, and wetlands in the project area?"]
[]
[3,"Required WQ Buffer for Streams"]
[null,"Instructions:"]
[null,"1) Enter the drainage area for the watershed using USGS Streamstats or similar process."]
[null,"https://streamstats.usgs.gov/ss/"]
[null,"2) View the project area in the TDEC Public Data Viewer at https://tdeconline.tn.gov/dwr from Step 1 above. "]
[null,"3) Open the Layer List and determine if the water resource is an Impaired/Not Supporting Waterbody. "]
[null,"4) Open the Exceptional Tennessee Waters Data Viewer."]
[null,"https://www.tn.gov/environment/about-tdec/tdec-dataviewers.html"]
[null,"5) Search for the Waterbody or Watershed Name to determine if the water resource in the project area is designated Exceptional Tennessee Waters."]
[]
[null,"Applicable for Streams Only"]
[null,"- The watershed drainage area (square miles) is measured from the lowest point of impact in the project area."]
[null,"What is the drainage area (Square Miles) for the watershed?"]
[null,"Is the drainage area for the identified watershed less than 1 mi2?",null,null,{"$f":"IF(E19=\"\",\"Not Yet Determined\",IF(E19=\"Lake or pond with hydrologic connectivity or wetland\",\"Not Applicable\",IF(AND(E41>0,E41<1),\"Yes\", IF(E41>=1,\"No\",\"Enter Drainage Area Above\"))))","v":"Not Yet Determined"}]
[null,"Is the adjacent stream designated as Impaired/Not Supporting?"]
[null,"Is the adjacent stream designated Exceptional Tennessee waters?"]
[null,"- All stream details must be entered above to calculate the Required WQ Buffer width."]
[null,"- For streams, the WQ Buffer Width is measured perpendicular from the top of bank.  The top of bank must be identified on the construction drawings and can be identified by a licensed Land Surveyor."]
[null,"Required WQ Buffer width (FT) adjacent to streams in project area =",null,null,{"$f":"IF(E19=\"\",\"Not Yet Determined\",IF(E19=\"Lake or pond with hydrologic connectivity or wetland\",\"Not Applicable\",IF(E41<>0,(IF(E42=\"No\",60,(IF(OR(E43=\"Yes\",E44=\"Yes\"),60,30)))),\"Enter stream info above to calculate buffer width\")))","v":"Not Yet Determined"}]
[null,"- The total existing WQ Buffer Area (square feet) for streams is the required WQ Buffer Width on both sides of the stream multiplied by the full length of the water resource in the project area, regardless of existing or proposed conditions in the Buffer area."]
[null,"What is the TOTAL existing WQ Buffer Area (SF) adjacent to streams in the project area?"]
[]
[4,"Total Existing WQ Buffer Area"]
[null,"- The total existing WQ Buffer Area (square feet) is the sum of the required WQ Buffer Areas adjacent to water resources in the project area, regardless of existing or proposed conditions in the buffer area."]
[null,"Existing WQ Buffer Area (SF) for lakes, ponds and wetlands = ",null,null,{"$f":"IF(E25=\"Not Applicable\",\"N/A in Project Area\",IF(E27=\"\", \"Not Calculated\", E27))","v":"Not Calculated"}]
[null,"Existing WQ Buffer Area (SF) for streams = ",null,null,{"$f":"IF(E47=\"Not Applicable\",\"N/A in Project Area\",IF(E49=\"\", \"Not Calculated\", E49))","v":"Not Calculated"}]
[null,"Total Existing WQ Buffer Area (SF) = ",null,null,{"$f":"IF(AND(E53=\"Not Calculated\",E54=\"Not Calculated\"),\"Not Yet Calculated\",SUBTOTAL(109,E53:E54))","v":"Not Yet Calculated"}]

{"sheet":"2. WQ Buffer Impacts","index":4,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[[7,1,7,3],[36,3,36,5],[8,1,8,3],[24,1,24,5],[15,1,15,5],[16,1,16,5],[14,1,14,5],[12,1,12,5],[13,1,13,5],[9,1,9,3],[19,1,19,5],[21,1,21,5],[25,1,25,5],[20,1,20,5]]}
["Tab 2. WQ Buffer Impacts",null,null,null,"WQ Variance Request #",{"$f":"LDP","v":0}]
["This step determines impacts into the existing Water Quality (WQ) Buffer."]
["Grey cells indicate a calculation performed by the Buffer Mitigation Tool."]
["Green cells indicate information needed for the Buffer Mitigation Tool."]
["White cells indicate where the applicant should enter project information corresponding to the green cells."]
[]
[1,"Summary of Existing Conditions in the Project Area"]
[null,"Required WQ Buffer Width (FT) for lakes, ponds, and wetlands = ",null,null,{"$f":"IF('1. Existing Conditions'!E19=\"\",\"Not Yet Calculated\",IF('1. Existing Conditions'!E25=30,30,IF('1. Existing Conditions'!E25<>30,\"N/A in Project Area\",\"\")))","v":"Not Yet Calculated"}]
[null,"Required WQ Buffer Width (FT) for streams = ",null,null,{"$f":"IF('1. Existing Conditions'!E19=\"\",\"Not Yet Calculated\",IF('1. Existing Conditions'!E47=60,60,IF('1. Existing Conditions'!E47=30,30,\"N/A in project area\")))","v":"Not Yet Calculated"}]
[null,"Existing WQ Buffer Area (SF) = ",null,null,{"$f":"'1. Existing Conditions'!E55","v":"Not Yet Calculated"}]
[]
[2,"Individual Buffer Impacts & Required Buffer Mitigation"]
[null,"- An impact is any disturbance within the designated WQ  Buffer Area in the project area."]
[null,"- A temporary impact is any clearing, grubbing, or disturbance in the WQ Buffer Width that will be replanted with native tree and shrub vegetation prior to project completion."]
[null,"- A permanent impact includes permanent clearing, grubbing, or disturbance to the WQ Buffer that will not be restored to native tree and shrub vegetation prior to project completion.  Permanent impacts include installation of one or more structures, pavement and impervious surfaces, and stream piping."]
[null,"- The inner 30 feet of the WQ Buffer is defined as 0-30 FT from the surveyed top of stream bank.  Top of stream bank must be identified by a licensed Land Surveyor."]
[null,"- The outer 30 feet of the WQ Buffer is defined as 30-60 FT from the surveyed top of stream bank.  Top of stream bank must be identified by a licensed Land Surveyor."]
[]
[null,"Instructions:"]
[null,"1) On the construction drawings, include a Table of Buffer Impacts listing the individual areas (SF) of buffer impacts for each type of impact and location.  Include a subtotal row, summing the total for each of the four combinations."]
[null,"2) Allowable uses as defined in City Code Section 31-324.2.10 may not require mitigation, but use of this WQ Buffer Tool is still a required part of submitting for City review, noting the specifics of the allowable use/s."]
[null,"3) In the Buffer Impact Table below, enter the total area of impacted buffer for each impact and location combination.  Be sure the total area for each impact and location matches the Buffer Impact Table in the construction drawings."]
[null,"4) Where no impacts are proposed, enter zero in the corresponding cell.  "]
[null,"5) Outer 30 Feet is only applicable in 60-FT WQ Buffers."]
[null,"6) Buffer Impact Table will calculate the total Impacted Buffer Area (SF) and corresponding Buffer Mitigation Requirements (SF).  Permanent impacts and impacts within 0-30 FT from the top of stream bank have higher mitigation ratios."]
[null,"NOTE: Mitigation for < 5,000 SF of temporary impacts is not required if total impacts are less than 5,000 SF.  However, if total impacts exceed 5,000 SF all temporary impacts must be mitigated, even if temporary impacts are less than 5,000 SF."]
[]
[null,"Buffer Impact Table"]
[null,"Type of WQ Buffer Impact","Location of Impacted Buffer Area","Impacted Buffer Area Subtotal (SF)*","Mitigation Ratio","Buffer Mitigation Requirement (SF)"]
[null,"Temporary Impact","Inner 30 Feet",null,{"$f":"IF(AND(B30=\"Temporary Impact\",C30=\"Inner 30 Feet\"),\"2:1\",IF(AND(B30=\"Temporary Impact\",C30=\"Outer 30 Feet\"),\"1:1\",IF(AND(B30=\"Permanent Impact\",C30=\"Inner 30 Feet\"),\"4:1\",IF(AND(B30=\"Permanent Impact\",C30=\"Outer 30 Feet\"),\"3:1\", \"\"))))","v":"2:1"},{"$f":"IF(Table38[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(Table38[[#Totals],[Column3]]<=5000,\"Not Required\",IF(E30=\"1:1\",D30*1, IF(E30=\"2:1\", D30*2,IF(E30=\"3:1\",D30*3, IF(E30=\"4:1\",D30*4, \"\"))))))","v":"Not Calculated"}]
[null,"Temporary Impact","Outer 30 Feet",null,{"$f":"IF(AND(B31=\"Temporary Impact\",C31=\"Inner 30 Feet\"),\"2:1\",IF(AND(B31=\"Temporary Impact\",C31=\"Outer 30 Feet\"),\"1:1\",IF(AND(B31=\"Permanent Impact\",C31=\"Inner 30 Feet\"),\"4:1\",IF(AND(B31=\"Permanent Impact\",C31=\"Outer 30 Feet\"),\"3:1\", \"\"))))","v":"1:1"},{"$f":"IF(Table38[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(Table38[[#Totals],[Column3]]<=5000,\"Not Required\",IF(E31=\"1:1\",D31*1, IF(E31=\"2:1\", D31*2,IF(E31=\"3:1\",D31*3, IF(E31=\"4:1\",D31*4, \"\"))))))","v":"Not Calculated"}]
[null,"Permanent Impact","Inner 30 Feet",null,{"$f":"IF(AND(B32=\"Temporary Impact\",C32=\"Inner 30 Feet\"),\"2:1\",IF(AND(B32=\"Temporary Impact\",C32=\"Outer 30 Feet\"),\"1:1\",IF(AND(B32=\"Permanent Impact\",C32=\"Inner 30 Feet\"),\"4:1\",IF(AND(B32=\"Permanent Impact\",C32=\"Outer 30 Feet\"),\"3:1\", \"\"))))","v":"4:1"},{"$f":"IF(Table38[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(Table38[[#This Row],[Column3]]=0,\"Not Required\",IF(E32=\"1:1\",D32*1, IF(E32=\"2:1\", D32*2,IF(E32=\"3:1\",D32*3, IF(E32=\"4:1\",D32*4, \"\"))))))","v":"Not Calculated"}]
[null,"Permanent Impact","Outer 30 Feet",null,{"$f":"IF(AND(B33=\"Temporary Impact\",C33=\"Inner 30 Feet\"),\"2:1\",IF(AND(B33=\"Temporary Impact\",C33=\"Outer 30 Feet\"),\"1:1\",IF(AND(B33=\"Permanent Impact\",C33=\"Inner 30 Feet\"),\"4:1\",IF(AND(B33=\"Permanent Impact\",C33=\"Outer 30 Feet\"),\"3:1\", \"\"))))","v":"3:1"},{"$f":"IF(Table38[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(Table38[[#This Row],[Column3]]=0,\"Not Required\",IF(E33=\"1:1\",D33*1, IF(E33=\"2:1\", D33*2,IF(E33=\"3:1\",D33*3, IF(E33=\"4:1\",D33*4, \"\"))))))","v":"Not Calculated"}]
[null,null,"Total Area (SF) = ",{"$f":"IF(AND(D30=\"\",D31=\"\",D32=\"\",D33=\"\"),\"Not Yet Calculated\",SUM(Table38[Column3]))","v":"Not Yet Calculated"},null,{"$f":"IF(AND(D30=\"\",D31=\"\",D32=\"\",D33=\"\"),\"Not Yet Calculated\",IF(Table38[[#Totals],[Column3]]>0, SUBTOTAL(109,Table38[Column5]),IF(Table38[[#Totals],[Column3]]=0,\"Not Required\",\"\")))","v":"Not Yet Calculated"}]
[null,"*Enter the sum totals for each combination of WQ Buffer Impact and Location of Impact"]
[]
[null,null,{"$f":"IF(OR(E10=\"Not Yet Calculated\",Table38[[#Totals],[Column3]]=0,Table38[[#Totals],[Column3]]=\"Not Yet Calculated\"),\"\",Table38[[#Totals],[Column3]]-E10)","v":""},{"$f":"IF(Table38[[#Totals],[Column3]]=\"Not Yet Calculated\",\"\",IF(Table38[[#Totals],[Column3]]>E10, \"Math Check! The sum of the impacted buffer areas should not exceed the Existing WQ Buffer Area.\",\"\"))","v":""}]

{"sheet":"3. Proposed On-Site Mitigation","index":5,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[[8,1,8,3],[11,1,11,3],[10,1,10,3],[9,1,9,3],[36,1,36,3],[21,1,21,5],[35,1,35,4],[24,1,24,3],[33,1,33,3],[34,1,34,3]]}
["Tab 3. Proposed On-Site Mitigation",null,null,null,"WQ Variance Request #",{"$f":"LDP","v":0}]
["This step identifies proposed on-site mitigation for the impacts to the WQ Buffer.  "]
["On-Site Mitigation shall be considered any site within the impacted buffer's City defined watershed."]
["Grey cells indicate a calculation performed by the Buffer Mitigation Tool."]
["Green cells indicate information needed for the Buffer Mitigation Tool."]
["White cells indicate where the applicant should enter project information corresponding to the green cells."]
[]
[1,"Summary of Existing Conditions and WQ Buffer Impacts"]
[null,"Required WQ Buffer Width (FT) for lakes, ponds, and wetlands = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E8","v":"Not Yet Calculated"}]
[null,"Required WQ Buffer Width (FT) for streams = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E9","v":"Not Yet Calculated"}]
[null,"Existing WQ Buffer Area (SF) = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E10","v":"Not Yet Calculated"}]
[null,"Total Area of WQ Buffer Impacts (SF) = ",null,null,{"$f":"Table38[[#Totals],[Column3]]","v":"Not Yet Calculated"}]
[]
[2,"Proposed On-Site Mitigation"]
[null,"Table Instructions:"]
[null,"1) In a separate document, calculate the total area for each type of mitigation proposed for the project."]
[null,"2) Enter the total area for each type of proposed mitigation into the On-Site Mitigation Table below."]
[null,"3) If using stream bank stabilization, then a minimum of 40 LF is required to receive Buffer Mitigation Credits."]
[null,"4) If using stream restoration, then a minimum of 100 LF is required to receive Buffer Mitigation Credits."]
[null,"5) If no mitigation is proposed, leave blank or right click and select \"Clear Contents\" to restart."]
[null,"6) Table will calculate the Buffer Mitigation Credits (SF) generated by the proposed mitigation."]
[null,"7) Table will calculate the any Required Off-Site Mitigation Area (SF).  Off-Site Mitigation is calculated as 1.5 times the remaining area not met through On-Site Mitigation."]
[]
[null,"On-Site Mitigation Table"]
[null,"Total Required Mitigation Area (SF) from Buffer Impacts = ",null,null,{"$f":"Table38[[#Totals],[Column5]]","v":"Not Yet Calculated"}]
[null,"Type of Buffer Mitigation","Credit Ratio","Area of Proposed Buffer Mitigation (SF)","Buffer Mitigation Credit (SF)"]
[null,"WQ Buffer Enhancement",{"$f":"IF(B27=\"WQ Buffer Enhancement\",\"1:1\",IF(B27=\"WQ Buffer Restoration\",\"1:2\",IF(B27=\"Stream Bank Stabilization\",\"1 LF:150 SF\",IF(B27=\"Stream Restoration\",\"1 LF:500 SF\", \"\"))))","v":"1:1"},null,{"$f":"IF(Table36[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(C27=\"1:1\",D27*1, IF(C27=\"1:2\", D27*2,IF(C27=\"1 LF:150 SF\",D27*150, IF(C27=\"1 LF:500 SF\",D27*500, \"\")))))","v":"Not Calculated"}]
[null,"WQ Buffer Restoration","1:2",null,{"$f":"IF(Table36[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(C28=\"1:1\",D28*1, IF(C28=\"1:2\", D28*2,IF(C28=\"1 LF:150 SF\",D28*150, IF(C28=\"1 LF:500 SF\",D28*500, \"\")))))","v":"Not Calculated"}]
[null,null,"Subtotal =",{"$f":"IF(AND(D27=\"\",D28=\"\"),\"Not Calculated\",SUBTOTAL(109,Table36[Column3]))","v":"Not Calculated"},{"$f":"IF(AND(D27=\"\",D28=\"\"),\"Not Calculated\",SUBTOTAL(109,Table36[Column4]))","v":"Not Calculated"}]
[null,"Type of Stream Mitigation","Credit Ratio","Length of Proposed Stream Mitigation (LF)","Buffer Mitigation Credit (SF)"]
[null,"Stream Bank Stabilization",{"$f":"IF(B31=\"WQ Buffer Enhancement\",\"1:1\",IF(B31=\"WQ Buffer Restoration\",\"1:2\",IF(B31=\"Stream Bank Stabilization\",\"1 LF:150 SF\",IF(B31=\"Stream Restoration\",\"1 LF:500 SF\", \"\"))))","v":"1 LF:150 SF"},null,{"$f":"IF(Table367[[#This Row],[Column3]]=\"\", \"Not Calculated\",IF(C31=\"1:1\",D31*1, IF(C31=\"1:2\", D31*2,IF(C31=\"1 LF:150 SF\",D31*150, IF(C31=\"1 LF:500 SF\",D31*500, \"\")))))","v":"Not Calculated"}]
[null,"Stream Restoration",{"$f":"IF(B32=\"WQ Buffer Enhancement\",\"1:1\",IF(B32=\"WQ Buffer Restoration\",\"1:2\",IF(B32=\"Stream Bank Stabilization\",\"1 LF:150 SF\",IF(B32=\"Stream Restoration\",\"1 LF:500 SF\", \"\"))))","v":"1 LF:500 SF"},null,{"$f":"IF(Table367[[#This Row],[Column3]]=\"\", \"Not Calculated\",IF(C32=\"1:1\",D32*1, IF(C32=\"1:2\", D32*2,IF(C32=\"1 LF:150 SF\",D32*150, IF(C32=\"1 LF:500 SF\",D32*500, \"\")))))","v":"Not Calculated"}]
[null,null,"Subtotal = ",{"$f":"IF(AND(D31=\"\",D32=\"\"),\"Not Calculated\",SUBTOTAL(109,Table367[Column3]))","v":"Not Calculated"},{"$f":"IF(AND(D31=\"\",D32=\"\"),\"Not Calculated\",SUBTOTAL(109,Table367[Column4]))","v":"Not Calculated"}]
[null,"Total On-Site Mitigation Credits Proposed (SF) = ",null,null,{"$f":"IF(AND(Table36[[#Totals],[Column3]]=\"Not Calculated\",Table367[[#Totals],[Column3]]=\"Not Calculated\"),\"Not Yet Calculated\",SUBTOTAL(109,Table36[Column4],Table367[Column4]))","v":"Not Yet Calculated"}]
[null,"Required Off-Site Mitigation Area (SF) = ",null,null,{"$f":"IF(OR(E25=\"Not Yet Calculated\",E34=\"Not Yet Calculated\"),\"Not Yet Calculated\",IF(AND(E25=\"Not Required\",E34=0),0,IF($E$34>=$E$25,0,IF($E$34<$E$25,($E$25-$E$34)*1.5,\"\"))))","v":"Not Yet Calculated"}]
[null,{"$f":"IF(E35=\"Not Yet Calculated\",\"\",IF(E25=0,\"\",IF(E25>E34,\"Attention! Additional off-site mitigation is REQUIRED. Continue to Tab 4.\",IF(E25=E34,\"Success!  Mitigation requirements have been met by proposed mitigation credits.\",IF(E25<E34,\"Proposed mitigation credits EXCEED mitigation requirements.  Do you want to proceed?\",\"\")))))","v":""},null,null,null,{"$f":"IF(E35=\"Not Yet Calculated\",\"\",IF(AND(E25=\"Not Required\", E34=0,E35=0),\"\",IF($E$25=0,\"\",IF($E$34>=$E$25,$E25-$E34, IF($E$34<$E$25,($E$25-$E$34)*1.5,\"\")))))","v":""}]
[null,"Excess On-Site Mitigation Credits Generated (SF) = ",null,null,{"$f":"IF(OR(E25=\"Not Yet Calculated\",E34=\"Not Yet Calculated\"),\"Not Yet Calculated\",IF(E34-E25<0,0,IF(E34=\"Not Yet Calculated\",\"Not Yet Calculated\",E34-E25)))","v":"Not Yet Calculated"}]

{"sheet":"4. Proposed Off-Site Mitigation","index":6,"n_rows":38,"n_cols":6,"rows_kept":38,"cut":false,"hidden":false,"merged":[[37,1,37,3],[10,1,10,3],[11,1,11,3],[12,1,12,3],[8,1,8,3],[9,1,9,3],[13,1,13,3],[14,1,14,3],[36,1,36,4],[26,1,26,3],[35,1,35,3]]}
["Tab 4. Proposed Off-Site Mitigation",null,null,null,"WQ Variance Request #",{"$f":"LDP","v":0}]
["This step identifies proposed off-site mitigation for the impacts to the WQ Buffer."]
["Off-Site Mitigation shall be considered any site outside of the impacted buffer's City defined watershed."]
["Grey cells indicate a calculation performed by the Buffer Mitigation Tool."]
["Green cells indicate information needed for the Buffer Mitigation Tool."]
["White cells indicate where the applicant should enter project information corresponding to the green cells."]
[]
[1,"Summary of Existing Conditions, WQ Buffer Impacts, and On-Site Mitigation"]
[null,"Required WQ Buffer Width (FT) for lakes, ponds, and wetlands = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E9","v":"Not Yet Calculated"}]
[null,"Required WQ Buffer Width (FT) for streams = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E10","v":"Not Yet Calculated"}]
[null,"Existing WQ Buffer Area (SF) = ",null,null,{"$f":"'2. WQ Buffer Impacts'!E10","v":"Not Yet Calculated"}]
[null,"Total Area of WQ Buffer Impacts (SF) = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E12","v":"Not Yet Calculated"}]
[null,"Total Required Buffer Mitigation (SF) from Buffer Impacts = ",null,null,{"$f":"Table38[[#Totals],[Column5]]","v":"Not Yet Calculated"}]
[null,"Total On-Site Mitigation Credits Proposed (SF) = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E34","v":"Not Yet Calculated"}]
[null,"Required Off-Site Mitigation Area (SF) = ",null,null,{"$f":"'3. Proposed On-Site Mitigation'!E35","v":"Not Yet Calculated"}]
[]
[2,"Proposed Off-Site Mitigation"]
[null,"Table Instructions:"]
[null,"1) In a separate document, calculate the total area for each type of mitigation proposed for the project."]
[null,"2) Enter the total area for each type of proposed mitigation into Off-Site Mitigation Table."]
[null,"3)  If using stream bank stabilization, then a minimum of 40 LF is required to receive Buffer Mitigation Credits."]
[null,"4) If using stream restoration, then a minimum of 100 LF is required to receive Buffer Mitigation Credits."]
[null,"5) If no mitigation is proposed, leave blank or right click and select \"Clear Contents\" to restart."]
[null,"6) Table will calculate the Buffer Mitigation Credits (SF) generated by the proposed mitigation."]
[]
[null,"Off-Site Mitigation Table"]
[null,"Required Off-Site Mitigation Area (SF) = ",null,null,{"$f":"E15","v":"Not Yet Calculated"}]
[null,"Type of Buffer Mitigation","Credit Ratio","Area of Proposed Buffer Mitigation (SF)","Buffer Mitigation Credit (SF)"]
[null,"WQ Buffer Enhancement",{"$f":"IF(B29=\"WQ Buffer Enhancement\",\"1:1\",IF(B29=\"WQ Buffer Restoration\",\"1:2\",IF(B29=\"Stream Bank Stabilization\",\"1 LF:150 SF\",IF(B29=\"Stream Restoration\",\"1 LF:500 SF\", \"\"))))","v":"1:1"},null,{"$f":"IF(Table3610[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(C29=\"1:1\",D29*1, IF(C29=\"1:2\", D29*2,IF(C29=\"1 LF:150 SF\",D29*150, IF(C29=\"1 LF:500 SF\",D29*500, \"\")))))","v":"Not Calculated"}]
[null,"WQ Buffer Restoration","1:2",null,{"$f":"IF(Table3610[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(C30=\"1:1\",D30*1, IF(C30=\"1:2\", D30*2,IF(C30=\"1 LF:150 SF\",D30*150, IF(C30=\"1 LF:500 SF\",D30*500, \"\")))))","v":"Not Calculated"}]
[null,null,"Subtotal =",{"$f":"IF(AND(D29=\"\",D30=\"\"),\"Not Calculated\",SUBTOTAL(109,Table3610[Column3]))","v":"Not Calculated"},{"$f":"IF(AND(D29=\"\",D30=\"\"),\"Not Calculated\",SUBTOTAL(109,Table3610[Column4]))","v":"Not Calculated"}]
[null,"Type of Stream Mitigation","Credit Ratio","Length of Proposed Stream Mitigation (LF)","Buffer Mitigation Credit (SF)"]
[null,"Stream Bank Stabilization",{"$f":"IF(B33=\"WQ Buffer Enhancement\",\"1:1\",IF(B33=\"WQ Buffer Restoration\",\"1:2\",IF(B33=\"Stream Bank Stabilization\",\"1 LF:150 SF\",IF(B33=\"Stream Restoration\",\"1 LF:500 SF\", \"\"))))","v":"1 LF:150 SF"},null,{"$f":"IF(Table36711[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(C33=\"1:1\",D33*1, IF(C33=\"1:2\", D33*2,IF(C33=\"1 LF:150 SF\",D33*150, IF(C33=\"1 LF:500 SF\",D33*500, \"\")))))","v":"Not Calculated"}]
[null,"Stream Restoration",{"$f":"IF(B34=\"WQ Buffer Enhancement\",\"1:1\",IF(B34=\"WQ Buffer Restoration\",\"1:2\",IF(B34=\"Stream Bank Stabilization\",\"1 LF:150 SF\",IF(B34=\"Stream Restoration\",\"1 LF:500 SF\", \"\"))))","v":"1 LF:500 SF"},null,{"$f":"IF(Table36711[[#This Row],[Column3]]=\"\",\"Not Calculated\",IF(C34=\"1:1\",D34*1, IF(C34=\"1:2\", D34*2,IF(C34=\"1 LF:150 SF\",D34*150, IF(C34=\"1 LF:500 SF\",D34*500, \"\")))))","v":"Not Calculated"}]
[null,null,"Subtotal = ",{"$f":"IF(AND(D33=\"\",D34=\"\"),\"Not Calculated\",SUBTOTAL(109,Table36711[Column3]))","v":"Not Calculated"},{"$f":"IF(AND(D33=\"\",D34=\"\"),\"Not Calculated\",SUBTOTAL(109,Table36711[Column4]))","v":"Not Calculated"}]
[null,"Total Off-Site Mitigation Credits Proposed (SF) = ",null,null,{"$f":"IF(AND(Table3610[[#Totals],[Column4]]=\"Not Calculated\",Table36711[[#Totals],[Column4]]=\"Not Calculated\"),\"Not Yet Calculated\",SUBTOTAL(109,Table3610[Column4],Table36711[Column4]))","v":"Not Yet Calculated"}]
[null,{"$f":"IF(OR(E27=\"Not Yet Calculated\",E36=\"Not Yet Calculated\"),\"\",IF(E36<E27,\"Attention! More off-site mitigation credits are REQUIRED.\",IF(E27=E36,\"Success!  Mitigation requirements have been met by proposed mitigation credits.\",IF(E36>E27,\"Proposed mitigation credits EXCEED mitigation requirements.  Do you want to proceed?\",\"\"))))","v":""},null,null,null,{"$f":"IF(B37=\"\",\"\",IF(E27>=E36,E27-E36,IF(E36<E27,(E36-E27),\"\")))","v":""}]
[null,"Excess Off-Site Mitigation Credits Generated (SF) = ",null,null,{"$f":"IF(OR(E27=\"Not Yet Calculated\",E36=\"Not Yet Calculated\"),\"Not Yet Calculated\",IF(E36-E27<0,0,E36-E27))","v":"Not Yet Calculated"}]

{"sheet":"Form Lists (to be hidden sheet)","index":7,"n_rows":6,"n_cols":7,"rows_kept":6,"cut":false,"hidden":true,"merged":[]}
["List 1","List 2","List 3","List 4","List 5","List 6","List 7"]
["Yes/No List","Attainment Description","Type of Buffer Impact","Location of Buffer Impact","Type of Water Resource","Type of Buffer Mitigation Practice"]
["Yes","Fully Supporting","Temporary Impact","Inner 30 Feet","Stream","WQ Buffer Enhancement"]
["No","Not Supporting","Permanent Impact","Outer 30 Feet","Lake or pond with hydrologic connectivity or wetland","WQ Buffer Restoration"]
[null,"Not Assessed",null,null,"Both Stream and Lake/Pond/Wetland","Stream Bank Stabilization"]
[null,null,null,null,null,"Stream Restoration"]