STEPL 2.2: Spreadsheet Tool for Estimating Pollutant Load

1. STEPL 2.2: Spreadsheet Tool for Estimating Pollutant Load US EPA: Romell Nandi (Nandi.Romell@epamail.epa.gov) Tetra Tech: Ting Dai (ting.dai@tetratech-ffx.com) and Henry Manguerra (henry.manguerra@tetratech-ffx.com)

2. What is STEPL? Calculates nutrient (N, P, and BOD pollutants) and sediment loads by land use type and aggregated by watershed Calculates load reductions as a result of implementing BMPs Data driven and highly empirical A customized MS Excel spreadsheet model Simple and easy to use Formulas and default parameter values can be modified by users (optional) with no programming required

3. STEPL Users? Basic understanding of hydrology, erosion, and pollutant loading processes Knowledge in the use and limitation of environmental data (e.g., land use, agricultural statistics, and BMP efficiencies) Familiarity with MS Excel and Excel Formulas

4. Process

5. Land use area Animal counts Septic system failure Precipitation Soil characteristics USLE or RUSLE parameters for soil erosion estimation Soil hydrologic group for runoff estimation; user�s input expected; default = B type Soil N, P, and BOD pollutant concentrations Step 1 � Source representation

6. Step 2 � System calculates annual load before BMP Runoff Curve Number Method (all land uses except urban area) Erosion and Sediment Delivered Erosion = Universal Soil Loss Equation (USLE = RKLSCP) Sediment = Erosion x Delivery Ratio Concentration Default (cropland, pasture, forest, feedlot) Load Export coefficient x Source area (urban) Dissolved concentration x Runoff volume Soil concentration x Sediment volume

7. Step 3 � Select BMP Select a BMP by land use Initial set of BMPs for each land use type is included in the spreadsheet Default N, P, BOD, Sediment removal efficiencies by BMP are included when available. User may change the default values. Specify a BMP train by land use Use BMP calculator to specify configuration of BMP train (multiple BMP implementations) and to calculate combined efficiencies

8. Step 4 � System calculates annual load after BMP Load reduction = Load before BMP x BMP efficiency Load after BMP = Load before BMP � Load reduction Loads are aggregated by watershed

9. Data Sources Spreadsheet Default Data Rainfall by county: NOAA Rainfall correction factors by station: NOAA USLE values by county: NRCS 1997 Natural Resources Inventory (NRI) Other: Literature values Default pollutant concentrations in runoff and soil BMP list and efficiencies Household wastewater characteristics, etc.

10. Data Sources, cont. On-line Data Server Land Use: NRCS 1997 NRI Database Note: Other = Federal land use, misc. minor land uses, and other farmland/CRP land. Animal Counts: USDA 1997 Census of Agriculture Note: D or N = data withhold or not available. Septic Systems: NESC 1992-1998 Summary of Onsite Waste Water Treatment System Note: If the failure rates are reported as 0% or not available (e.g. DE, DC, HI, OK, PA, and SD), we recommend to use the U.S. average rate of 1%. Soil hydrologic group: USDA STATSGO Soil Database

11. STEPL Web Site

12. STEPL Main Program Run STEPL executable program to create and customize spreadsheet dynamically

13. STEPL Spreadsheet

14. Input Worksheet

15. USLE Review For sheet and rill erosion (water): A = R K LS C P A = average annual soil loss (t/ac) due to sheet and rill erosion R = rainfall erosivity factor K = soil erodibility factor LS = slope length and steepness factor C = cover and management factor P = support practice factor

16. Input Worksheet, cont.

17. Input Worksheet, cont.

18. BMP Worksheet

19. Total Load Worksheet

20. Graph Worksheet

21. STEPL online input data server can be used to obtain preliminary values for STEPL input parameters Land uses (in acres) Animal types and numbers Septic system numbers and failure rate Soil hydrological group Data sources include national databases. User should use local data when available. STEPL Online Input Data Server

22. STEPL Online Input Data Server

23. STEPL Online Input Data Server

24. STEPL Online Input Data Server: Basic Report

25. STEPL Online Input Data Server: Custom Report

26. STEPL Online Input Data Server: Custom Report

27. STEPL BMP Calculator

28. STEPL BMP Calculator

29. STEPL BMP Calculator

30. STEPL BMP Calculator

31. STEPL BMP Calculator

32. Summary

33. STEPL Additional Reference Slides

34. STEPL Components

35. Customized Menu

36. Hydrology Algorithm Summary Estimate average rainfall/event using corrected rainfall and number of rain days (days that rain amount > 5 mm or 0.2 inch) Use NRCS� runoff formula with Curve Numbers R is rainfall (cm), Q is daily runoff (cm), and CN is curve number The initial rainfall abstraction coefficient could be much less than 0.2 Calculate total runoff for different land uses

37. Nutrient Algorithm Summary Simple loading functions (concentration x runoff) Agricultural nutrient concentrations adjusted by animal density Population impact relates to septic tank failure rate.

38. Sediment Algorithm Summary USLE for rural land uses USLE parameters by counties Lookup tables for urban land uses Area-based sediment delivery functions Sediment reduction BMP efficiencies

39. Data Source: Nutrient Concentration in Runoff Modify default nutrient concentration in runoff

40. Data Source: Nutrient Concentration in Runoff

41. BMP List

42. Add New Data to BMP List In STEPL customized menu, click �View/Edit BMP List� BMPList worksheet is shown, add or delete BMPs

43. STEPL: Add New Data to BMP List Click �Update BMP Data� button to update the BMP selections in the BMPs worksheet Click �Save Updates� to save changes to text files (comma delimited) C:or D:\Stepl\Support\AllBMPstepl.csv C: or D:\Stepl\Support\AllBMP.csv

45. STEPL BMP Calculator - Steps

47. Sample Problem Exercises Exercise #1 Estimate total annual load for a specific HUCO area, and total load reduction resulting to implementation of a (single) BMP on croplands, and urban area Use STEPL On-line Data Server Basic Report and STEPL Spreadsheet Exercise #2 Similar to Exercise #1 but for a specific 8-digit watershed Use STEPL On-line Data Server Custom Report and STEPL Spreadsheet Exercise #3 Similar to Exercise #1 but for a smaller watershed that spans across two HUCO areas, and BMP trains implemented on croplands, and urban areas Use STEPL On-line Data Server Custom Report, BMP Calculator, and STEPL Spreadsheet

48. Sample Problem Exercise #1 Estimate total annual load for a HUCO which is in Lycoming County and HUC=02050205 (Pine Watershed) in Pennsylvania Get data using on-line data server Using Internet Explorer, go to http://it.tetratech-ffx.com/stepl/ Click hyperlink �Access to STEPL database for Input Data� to display on-line data server Follow steps outlined in the on-line data server Select state = Pennsylvania Select county = Lycoming or select HUC = 02050205: Pine (try both and check what happens) If you are familiar with the area, you will probably know which polygon it is. If not, click the identify tool to activate it, and click one of the selected polygons in red to query which county and HUC it is located (i.e., results shown in the table shown near the bottom of the window. Note that the table also shows the HUCO polygon ID You may skip step #3 shown in the interface if you do not want to manually include (or select) additional HUCO polygons Click Basic report button to generate tables showing data for the selected HUCO polygons Just in case you are interested in knowing where the rest of the HUCO polygons are located in the map, you may use the Identify tool to identify the other selected HUCO polygons in the map, or you may use the zoom-in tool just to zoom enough to display the HUCO Ids in the map. Do not close the windows since we will be using them later on.

49. Sample Problem Exercise #1 Generate a new custom spreadsheet. Note that you may reuse a spreadsheet you created previously for a different project. Click Start button (e.g., normally located at the Windows bottom left corner), then Program, STEPL, and STEPL to run the STEPL main executable program (stepl.exe in /STEPL folder) and display main interface Select options. For Exercise #1, specify the following: Specify number of watershed = 1 Check all landuses except for User-defined (default selection) Check all animal types Click ok to create new spreadsheet When the new spreadsheet is opened, click Ok button to enable stored formulas/equations in the spreadsheet Save the spreadsheet using a new file name For this example, you may save it to exercise1.xls

50. Sample Problem Exercise #1 Enter data in the Input Worksheet (numbers in red in spreadsheet) By default, optional tables are not shown. Click yes to show the optional tables (Table 5-8) with their default values. Click no to hide them. Select state = Pennsylvania, and county = Lycoming. Notice that initial values for Annual Rainfall and Number of Rain Days are specified in Table 1 as you select a state or county. Select a weather station = NY New York Central Part. Notice that correction factors change with the selected weather station. In Table 1, enter the land use areas for your watershed by referring back to the reported values by the on-line data server. Note that you are assuming your entire HUCO (e.g. 5153) = watershed. You may cut and paste values from one window to another. Also in Table 1, Select the feedlot percent paved assuming feedlot area is not zero. Default value = 0-24%.

51. Sample Problem Exercise #1 Enter data in the Input Worksheet (numbers in red in spreadsheet), cont�d. In Table 2, enter the animal counts for your watershed by referring back to the reported values by the on-line data server. (Watch for missing data, all the data entered must be numeric values!) Also in Table 2, select the average # of months manure is applied in croplands. In Table 3, enter septic system data by referring back to the reported values by the on-line data server. (If septic failure rate is zero, enter 1 (percent) as the national average. In Table 4, examine the initial USLE parameter values for each land use type which were automatically specified as you selected the state and county.

52. Sample Problem Exercise #1 Examine estimated load in Total Load and Graph worksheets and enter the results below: Total Annual N Load (lb): ________________ Total Annual P Load (lb): ________________ Total Annual Sediment Load (ton): _____________ Source with highest annual load contribution: N load (lb): _________ What source: __________ P load (lb): _________ What source: __________ Sediment load (lb): _________ What source: __________

53. Sample Problem Exercise #1 For the same HUCO area, estimate total annual load reduction assuming reduced tillage is practiced in cropland areas, and porous pavement is used in urban areas Enter BMP data in BMPs worksheet In Table 1 which is for cropland areas, select Reduced Tillage System under BMP column. Note that initial values of BMP efficiencies are automatically specified with the selected BMP. In Table 6 which is for urban areas, select Porous Pavement under BMP.

54. Sample Problem Exercise #1 Examine estimated load reduction in Total Load and Graph worksheets and enter the results below: Total Annual N Load Reduction (lb): ________________ Total Annual P Load Reduction (lb): ________________ Total Annual Sediment Load Reduction (ton): _____________ Source with highest annual load contribution: N load (lb): _________ What source: __________ P load (lb): _________ What source: __________ Sediment load (lb): _________ What source: __________

55. Sample Problem Exercise #2 Estimate total annual load for the entire 8-digit watershed, HUC =02050205 (Pine Watershed) in Pennsylvania Get data using on-line data server Instead of basic report, you will be using custom report since you want to get the data summed up for the entire 8-digit watershed Hint: Entire area of each HUCO is within the 8-digit watershed. Create a spreadsheet for this project or exercise. Instead of generating a new custom spreadsheet using the STEPL main executable program, you will be using the spreadsheet in the previous exercise. Save the spreadsheet used for Exercise #1 to save recent changes. Save this spreadsheet with a new name (exercise2.xls, be sure to save the file as *.xls type). This new spreadsheet will be used for Exercise #2.

56. Sample Problem Exercise #2 Enter new data in the Input Worksheet Note that some of the data entered in the previous spreadsheet are still valid (e.g., state, county, etc.) In contrast to Exercise #1, you will be examining the data in the Optional tables so click Yes button to show the optional tables Note that the on-line data server provides the average soil hydrologic group. Enter this value in Table 5. You may change the rest of the default data especially if local data is available

57. Sample Problem Exercise #2 Examine estimated load in Total Load and Graph worksheets and enter the results below: Total Annual N Load (lb): ________________ Total Annual P Load (lb): ________________ Total Annual Sediment Load (ton): _____________ Source with highest annual load contribution: (You may unselect the BMPs to obtain source load before the implementation of the BMPs) N load (lb): _________ What source: __________ P load (lb): _________ What source: __________ Sediment load (lb): _________ What source: __________

58. Sample Problem Exercise #2 Examine estimated load reduction in Total Load and Graph worksheets and enter the results below: Total Annual N Load Reduction (lb): ________________ Total Annual P Load Reduction (lb): ________________ Total Annual Sediment Load Reduction (ton): _____________ Source with highest annual load contribution: N load (lb): _________ What source: __________ P load (lb): _________ What source: __________ Sediment load (lb): _________ What source: __________

59. Sample Problem Exercise #3 Estimate total annual load and load reduction for a watershed that lies across two HUCOS within HUC=02050205 and where all croplands are practicing reduced tillage and filter strips (shown below): Get data using on-line data server Use custom report to get numbers for the entire watershed. Hint: Compute the percent area of the HUCO that is within the watershed.

60. Sample Problem Exercise #3 Create a spreadsheet for this project or exercise. Save the spreadsheet used in Exercise #2 to exercise3.xls. Enter new data in the Input Worksheet Enter BMP data in BMP worksheet In Table 1, which is for cropland areas, select �Combined-BMP calculated� under BMP column to indicate that we have a �Reduced Tillage-Filter Strip� BMP train in croplands. Note that the N, P, BOD, and Sediment BMP efficiencies remained zero. If you have the combined efficiency values for this particular BMP train, enter them in Table 7 (number in red). These values will be reflected in Table 1 and in other tables (i.e., if the same BMP train is implemented for other land uses). If you do not have the values, you may use the BMP calculator (next step)

61. Sample Problem Exercise #3 Use BMP Calculator to estimate combined efficiencies of the BMP train Run the BMP Calculator by selecting the STEPL/BMP Calculator menu of the STEPL spreadsheet. If the system cannot find the BMP Calculator program, navigate to /STEPL folder and select BMPCalculator.exe Using the BMP Calculator interface, do the following (refer back to slide 33 for steps in using BMP Calculator): Add two BMP boxes (one each for Reduced Tillage, and Filter Strip Enter BMP information (type, area, etc.) for each BMP box by double-clicking the box (Question: What is the area associated with the filter strip) Specify the connection between the two BMPs (Question: Which BMP should be upstream). You may move the boxes to make them more readable Calculate the combined efficiencies for N, P, BOD, and Sediment. Enter the combined efficiencies in Table 7 of STEPL spreadsheet. Note the efficiencies are reflected in Table 1.

62. Sample Problem Exercise #3 Examine estimated load in Total Load and Graph worksheets and enter the results below: Total Annual N Load (lb): ________________ Total Annual P Load (lb): ________________ Total Annual Sediment Load (ton): _____________ Source with highest annual load contribution: N load (lb): _________ What source: __________ P load (lb): _________ What source: __________ Sediment load (lb): _________ What source: __________

63. Sample Problem Exercise #3 Examine estimated load reduction in Total Load and Graph worksheets and enter the results below: Total Annual N Load Reduction (lb): ________________ Total Annual P Load Reduction (lb): ________________ Total Annual Sediment Reduction (ton): _____________ Source with highest annual load contribution: N load (lb): _________ What source: __________ P load (lb): _________ What source: __________ Sediment load (lb): _________ What source: __________

64. More Exercises for BMP Calculator Try different BMP trains in the BMP Calculator. Note that you may define as many trains as you want and calculate each BMP train�s combined efficiency at the same time in the same window. You don�t need to open a separate BMP window for each BMP train (see illustration below).