Updates to STEPL Version 4.4 Input Worksheet. Weather Station Updates: Spatial and temporal coverage of precipitation stations has been updated to increase the number of weather stations in STEPL from 493 to 4,998. Data from US EPA's BASINS system's meteorological database were used to make the updates. The BASINS 4.0 meteorological database provides a national database that is quality controlled and corrected for missing and accumulated data. Specifically the database contains data from NOAA - NCDC's three data sources which are - Summary of the Day (SOD), Hourly Precipitation Data (HPD), and Integrated Surface Hourly (ISH). For inclusion into STEPL, the BASINS data were filtered to limit the number of weather stations to those with at least 30 years of data, to provide a long- period to calculate the average annual precipitation and average annual number of rain days at each of the stations. Figure U1 and Figure U2 show the spatial locations of the weather stations included in STEPL model for the contiguous U.S. and for Alaska, Puerto Rico, and Hawaii respectively. Figure Ul. NCDC Precipitation Stations included in STEPL - Contiguous United States. 1 ------- Figure U2. NCDC Precipitation Stations included in STEPL - Alaska, Puerto Rico, and Hawaii. Manure Application on Pastureland: In previous versions of STEPL, manure application was only available for cropland. In version 4.4, manure application can now be included on pastureland. This function is available in Table 2 in the Input worksheet. The updated Table 2 is shown in Figure U3. 2. Input agricultural animals # of months # of months manure manure applied on applied on Watershed Beef Cattle Dairy Cattle Swine (Hog) Sheep Horse Chicken Turkey Duck Cropland Pastureland W1 100 100 100 100 100 100 100 100 9 6 W2 0 0 0 0 0 0 0 0 0 0 W3 0 0 0 0 0 0 0 0 0 0 W4 0 0 0 0 0 0 0 0 0 0 W5 0 0 0 0 0 0 0 0 0 0 W6 0 0 0 0 0 0 0 0 0 0 W7 0 0 0 0 0 0 0 0 0 0 W8 0 0 0 0 0 0 0 0 0 0 W9 0 0 0 0 0 0 0 0 0 0 W10 0 0 0 0 0 0 0 0 0 0 Total 100 100 100 100 100 100 100 100 Figure U3. Updated Table 2 in the Input worksheet where the user can include manure application on pastureland. Average Number of Months for Manure Application: A new worksheet ManureApplication has been added to calculate the average number of months for manure application per year with varying application frequency across the watershed. This 2 ------- worksheet (hidden by default) is accessed via the Manure Application button above Table 1 in the Input worksheet, adjacent to the weather station inputs, as shown in Figure U4, below. State County Weather Station ^ | ^^^^^^^^^^^^^^^^^^^^^]^Calcu|ateJVlanure Application Months: Manure Application | Figure U4. Manure Application button on the Input worksheet to access the ManureApplication worksheet. The ManureApplication worksheet allows the user to specify treatment subareas within a watershed, each with a specific number of months where manure is applied. The worksheet will automatically calculate the area-weighted number of months when manure is applied across the watershed, as shown in Figure U5. This value is then entered into Table 2 in the Input worksheet. The results from the ManureApplication worksheet are manually entered by the user to allow the worksheet to be reused for multiple watersheds. The worksheet can be used for both cropland and pastureland calculations of months of manure application. Estimate an area-weighted frequency of application based on varying manure application across a watershed Enter total land use area I 100.001acres Enter the subarea and its corresponding number of months of manure application below (upto 20 varying frequency of treatment allowed) Treatment Area (ac) # of Months Manure Applied in a Year 1 50.00 9 2 20.00 7 3 30.00 0 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Land Use Area 100.00| 6 <— Enter the calculated value in Table 2. located in "Input" tab, under the appropriate watershed and landuse (cropland Tota IAreacheck:OK or pastureland under columns K or L) Figure U5. ManureApplication worksheet where the user can calculate area-weighted frequency of manure application across a watershed. A new table, Table 10 on the Input worksheet, shown in Figure U6, provides EMCs for pastureland based on six groups of livestock density: ¦ Low Live Stock Density with manure ¦ Low Live Stock Density without manure ¦ Medium Live Stock Density with manure ¦ Medium Live Stock Density without manure ¦ High Live Stock Density with manure ¦ High Live Stock Density without manure 3 ------- 10. Pastureland Nutrient concentration in runoff (mg/l) and E. coli (MPN/100ml Land use N p BOD E. coli 1. L-Pasture I 0.3 13 0 1a. w/ manure 4 0.3 13 0 2. M-Pasture I 0.3 13 0 2a. w/ manure 4 0.3 13 0 3. H-Pasture 4 0.3 13 0 3a. w/ manure 4 0.3 13 0 Figure U6. Default values for pastureland nutrient concentrations (mg/L). The default values in Table 10 are the average EMC taken from the previous version 4.3 of STEPL model. The EMC values for all six categories need to be updated based on the published literature and after review/approval of EPA. Export input/output data: An Export Data button has been added to the Input worksheet to allow the user to export input and output data and associated summary tables and plots into a Word document. The button, shown in Figure U7, is located at the top of the Input worksheet, just below the instructions. Export input/output data: Export Data Figure U7. The export data button on the Input worksheet to allow the user to export data, summary tables and plots into a Word document. The Export Data provides the following four options to generate a summary report. 1. Output summary tables only 2. Output summary tables with graphs 3. Input and Output summary tables only 4. Input and Output summary tables with graphs BMPs Worksheet. New Agricultural BMPs and Updated Pollutant Efficiencies for Nutrients: New BMPs for cropland and pastureland have been added to STEPL version 4.4. The cropland BMP list has been expanded from 6 BMPs to 17, and 17 pastureland BMPs were added, see Table Ul. Previous version of STEPL did not have pastureland BMPs. In addition, the pollutant efficiency numbers for some existing cropland BMPs have been updated. The efficiencies are summarized in the BMPList worksheet in STEPL. 4 ------- Table Ul. BMPs available in STEPL version 4.4 for cropland and pastureland. Land Use BMP Cropland Bioreactor Cropland Buffer - Forest (100ft wide) Cropland Buffer - Grass (35ft wide) Cropland Combined BMPs-Caicuiated Cropland Conservation Tillage 1 (30-59% Residue) Cropland Conservation Tillage 2 (equal or more than 60% Residue) Cropland Contour Farming Cropland Controlled Drainage Cropland Cover Crop 1 (Group A Commodity) (High Till only for Sediment) Cropland Cover Crop 2 (Group A Traditional Normal Planting Time) (High Till only for TP and Sediment) Cropland Cover Crop 3 (Group A Traditional Early Planting Time) (High Till only for TP and Sediment) Cropland Land Retirement Cropland Nutrient Management 1 (Determined Rate) Cropland Nutrient Management 2 (Determined Rate Plus Additional Considerations) Cropland Streambank Stabilization and Fencing Cropland Terrace Cropland Two-Stage Ditch Pastureland 30m Buffer with Optimal Grazing Pastureland Alternative Water Supply Pastureland Combined BMPs-Calculated Pastureland Critical Area Planting Pastureland Forest Buffer (minimum 35 feet wide) Pastureland Grass Buffer (minimum 35 feet wide) Pastureland Grazing Land Management (rotational grazing with fenced areas) Pastureland Heavy Use Area Protection Pastureland Litter Storage and Management Pastureland Livestock Exclusion Fencing Pastureland Multiple Practices Pastureland Pasture and Hayland Planting (also called Forage Planting) Pastureland Prescribed Grazing Pastureland Streambank Protection w/o Fencing Pastureland Streambank Stabilization and Fencing Pastureland Use Exclusion Pastureland Winter Feeding Facility Flow Volume Reductions for Urban LID and Infiltration Practices: STEPL can now estimate flow volume reductions for urban LID and infiltration BMP practices. This is represented as gallons/year by urban land use type in each watershed. These results are shown in Table 5 in the Urban worksheet, as seen in Figure U8 below. The BMPList worksheet contains pre-populated design storage depths for each of the available infiltration BMPs. The user enters the design runoff captured depth and percent imperviousness of the BMP drainage areas. The urban practices in STEPL that provide the flow volume reduction are listed in Table U2. 5 ------- Landuse Commerci Industrial Institution. Transports Multi-Fam Single-Far Urban-Cul Vacant (dc Open Spa< W1 1435371.9 0 0 0 0 49855.14 0 0 0 W2 0 0 0 0 0 0 0 0 0 W3 0 0 0 0 0 0 0 0 0 W4 0 0 0 0 0 0 0 0 0 W5 0 0 0 0 0 0 0 0 0 W6 0 0 0 0 0 0 0 0 0 W7 0 0 0 0 0 0 0 0 0 W8 0 0 0 0 0 0 0 0 0 W9 0 0 0 0 0 0 0 0 0 W10 0 0 0 0 0 0 0 0 0 Figure U8. Table 5 in Urban worksheet showing volume reductions from urban infiltration BMPs. Table U2. Urban LID and infiltration practices in STEPL version 4.4 with flow volume reductions. Land Use BMP Urban Infiltration Basin Urban Infiltration Devices Urban Infiltration Trench Urban LID*/Cistern Urban LID*/Cistern+Rain Barrel Urban LID*/Rain Barrel Urban LID/Bioretention Urban LID/Dry Well Urban LI D/Fi Iter/Buffer Strip Urban LID/lnfiltration Swale Urban LID/lnfiltration Trench Urban LIDA/egetated Swale Urban LID/Wet Swale Urban Oil/Grit Separator Urban Porous Pavement Urban Sand Filter/Infiltration Basin Combined BMP Efficiency Worksheet: The combined BMP efficiency functionality has been updated. The CombinedBMPEfficiency worksheet (hidden by default) can be accessed from the BMPs worksheet by clicking on the Calculate Combined BMP Efficiency button, see Figure U9. This tool calculates a combined BMP efficiency from multiple types of parallel management practices on the same landuse category across the watershed. Figure U9. The Calculated Combined BMP Efficiency button on the BMPs worksheet, used to access the CombinedBMPEfficiency worksheet. In the BMP pull-down list boxes, there are items called "Combined BMPs-Calculated". Select "Combined BMPs-Calculated" if there are multiple BMPs on the same land use type in a given subwatershed. If "Combined BMPs-Calculated" is selected, the CombinedBMPEfficiency 6 ------- worksheet (Figure U10) can be used to obtain the watershed-wide combined BMP efficiencies for calculating pollutant reductions. Note that for nested BMPs that route to each other, BMP calculator can be used to estimate the combined BMP efficiency. A B C D E F G H Estimate an area-weighted cor ibined efficiency of multiple BMPs (in parallel) across a watershed J Enter total land use area (acre) 200.00 Cropland ¦iy.iTcl"tclB.rvl PjL'istl Copy to Log Enter the subarea treated by each selected BMP type (upto 20 varying frequency of treatment allowed) Treatment Area (ac) Select a BMP Type N P BOD Sediment E. coli 1 50.00 Conservation Tillaqe 1 (30-59% Residue) 0.150 0.356 0.000 0.403 0.000 2 20.00 Conservation Tillaqe 1 (30-59% Residue) 0.150 0.356 0.000 0.403 0.000 3 30.00 Nutrient Manaqement 1 (Determined Rate) 0.154 0.450 0.000 0.000 0.000 4 100.00 Two-Staqe Ditch 0.120 0.280 0.000 0.000 0.000 5 0 No BMP 0.000 0.000 0.000 0.000 0.000 6 0 No BMP 0.000 0.000 0.000 0.000 0.000 7 0 No BMP 0.000 0.000 0.000 0.000 0.000 8 0 No BMP 0.000 0.000 0.000 0.000 0.000 9 0 No BMP 0.000 0.000 0.000 0.000 0.000 10 0 No BMP 0.000 0.000 0.000 0.000 0.000 11 0 No BMP 0.000 0.000 0.000 0.000 0.000 12 0 No BMP 0.000 0.000 0.000 0.000 0.000 13 0 No BMP 0.000 0.000 0.000 0.000 0.000 14 0 No BMP 0.000 0.000 0.000 0.000 0.000 15 0 No BMP 0.000 0.000 0.000 0.000 0.000 16 0 No BMP 0.000 0.000 0.000 0.000 0.000 17 0 No BMP 0.000 0.000 0.000 0.000 0.000 18 0 No BMP 0.000 0.000 0.000 0.000 0.000 19 0 No BMP 0.000 0.000 0.000 0.000 0.000 20 0 No BMP 0.000 0.000 0.000 0.000 0.000 Total Land Use Area 200.00 Enter the calculated value in Table 7. located in "BMPs" tab, under the appropriate watershed 0.136 0.332 0.000 0.141 0.000 261 27 Total Area check: OK 28] Figure U10. The CombinedBMPEfficiency worksheet where area-weighted combined efficiency of multiple parallel BMPs can be calculated. Combined BMP efficiencies can be calculated individually for cropland and pastureland in each watershed. The user should select the total treated area by land use and then selected BMP types and acreage of treatment for each area in the watershed. The practices are assumed to be functioning in parallel. BMP efficiencies are automatically populated based on the BMP selected. When all BMP treatment areas have been added, the resulting area-weighted values in blue color should be added to Table 7 in the BMPs worksheet. This process can be repeated for agricultural land use in each watershed. The Copy to Log button allows the user to keep a record of various combinations of combined BMP efficiencies by creating a Word document in the same location where the STEPL workbook is saved. The same Word document is updated/appended every time the user clicks on the Copy To Log button. The date/time are included in the document, so the user can keep track of the various iterations. E.coli place holder for next release: An E. coli place holder has been added to STEPL in preparation for the next release of the tool. Currently, E. coli is not calculated by STEPL. Place holders for E. coli appear in Tables 5, 7, 7a, and 10 on the Input worksheet, in Tables 1-7 on the BMPs worksheet, in Tables 1 and 4 on the Urban worksheet, and Tables 1 and 2 on the Total Load worksheet. None of these tables are populated with loading or reduction data in STEPL version 4.4. Similarly, the E. coli graphs and figures in the Graphs worksheet are blank. 7 ------- New STEPL Customized Version (Microsoft Excel 2016). Customized spreadsheet model: In addition to the executable file that creates a STEPL model workbook, a customized spreadsheet model with 10 watersheds (STEPLlOws) was added. This model allows the user to start working in STEPL without using the executable file, and provides the user the ability to populate the model with up to 10 watersheds. Up to 100 gullies and banks also come with the customized STEPL spreadsheet. Optimization algorithm: A simple optimization algorithm (Run Solver) is now available in the customized spreadsheet model (STEPLlOws), which allows the user to identify the extent of treatment areas to meet a load reduction target from the user selected BMP types. The optimization is not available in the original STEPL executable version. The optimization algorithm only solves for non-urban BMPs, and the user must enter a selection of BMPs for the optimizer to use in the solution. The optimizer is intended to maximize the load reduction and minimize the treated land use area based on the suite of BMPs provided by the user. The user inputs required to run the optimization tool are: • Pollutant - select a pollutant of concern that will define the load reduction target (Figure Ull). • Target - define the numeric load reduction target for the selected pollutant. The target should be for all land uses and watersheds in the spreadsheet (Figure U11). • BMPs - suitable BMP types for each land use and watershed combination (Figure U12). • Constraints - define the minimum and maximum percent treated area for each land use by watershed for the selected BMPs (Figure U12). ^Target Load Reduction Total Load Reduction (Ib/yr) 625.00 Select a Pollutant: N 625.00 Figure Ull. Run Solver button to run optimization scenario and the inputs for target load reduction and pollutant of concern. Prior to running an optimization scenario, the user should set the maximum area constraint as high as possible, taking into consideration any land in the watershed that may not be available/suitable for the BMP to determine the maximum load reduction possible with the selected BMPs. The target load reduction should be lower than the total load reduction determined at the maximum area constraint settings, since a larger reduction target will not be possible with the BMPs and constraints as defined. The minimum area constraint can be set as zero or as pre-identified treatment area for the selected BMP type in any given watershed. 8 ------- 1. BMPs and efficiencies for different pollutants on CROPLAND, ND=No Data Watershed Cropland CONSTRAINT N p BOD Sediment E. coli BMPS % Area BMP Applied Min Area (%) Max Area (%) W1 0.24 0.23 ND 0.29 ND O Buffer - Forest (10011 wide) -H 50.00 0 50 W2 0.03 0.08 ND 0.09 ND O Conservation Tillage 1 (30-59% Residue) 2225 0 50 W3 0.00 0.00 ND 0.00 ND O Contour Farming -J 0.00 0 50 W4 0.00 0 00 0.00 0.00 0.00 O 0 No BMP 0.00 0 0 W5 0.00 0 00 0.00 0.00 0.00 O 0 No BMP 0.00 0 0 W6 0.00 0.00 0.00 0.00 0.00 O 0 No BMP • 0.00 0 0 W7 0.00 0.00 0.00 0.00 0.00 O 0 No BMP 0.00 0 0 W8 0 00 0.00 0.00 0.00 0.00 O 0 NO BMP -1 0 00 0 0 W9 0.00 0.00 0.00 0.00 0.00 O 0 No BMP - 0.00 0 0 W10 0.00 0.00 0.00 0.00 0.00 O 0 No BMP 0.00 0 0 2. BMPs and efficiencies for different pollutants on PASTURELAND, ND=No Data Watershed Pastureland CONSTRAINT N p BOD Sediment E. coli BMPs % Area BMP Applied Min Area (%) Max Area (%) W1 0.20 0.30 ND 0.62 ND O Livestock Exclusion Fencing -M 100.00 0 100 W2 0.00 0.00 ND 0.00 ND O Alternative Water Supply - T 0.00 0 100 W3 0.00 0.00 ND 0.00 ND O Critical Area Planting ID ' 0 00 0 100 W4 0.00 0.00 0.00 0.00 0.00 O 0 No BMP \±2 - 0.00 0 0 W5 0.00 0.00 0.00 0.00 0.00 O 0 No BMP T 0.00 0 0 W6 0.00 0.00 0.00 0.00 0.00 O 0 No BMP ±] ' 0.00 0 0 W7 0.00 0.00 0.00 0.00 0.00 O 0 No BMP ±3 • i 0.00 0 0 W8 0.00 0 00 0.00 0.00 0.00 O 0 No BMP ±] - 0 00 0 0 W9 0.00 0.00 0.00 0.00 0.00 O 0 No BMP ±3 0.00 0 0 W10 0.00 0.00 0.00 0.00 0.00 O 0 No BMP ±j 0.00 0 0 Figure U12. Example land uses with BMPs selected and constraints set. ------- |