![[logo] US EPA](http://www.epa.gov/epafiles/images/logo_epaseal.gif){kind=logo}
Details about the Demonstration Model
Two files are needed to run the demonstration model: allo_dem.xls and allo_dem.opt.
Allo_dem.xls Spreadsheet
Allo_dem.xls is an MS-Excel workbook containing the spreadsheets that calculate loads, concentrations, and costs associated with different management strategies for achieving fecal coliform bacteria criteria in a small rural Idaho watershed. Spreadsheets beginning with "OPT" contain variables that are used by the optimization (OptQuest) and risk analysis (Crystal Ball) addins. The spreadsheets are organized as follows:
| OPT-Decision spreadsheet contains management DECISION variables (shaded in yellow) used to specify management activity levels. |
| OPT-Concentrations spreadsheet calculates load and concentration distributions for each month, for each stakeholder source, using probability distributions for impact coefficients. These distributions are referred to as ASSUMPTIONS (shaded in green) and are specifed using the histogram data provided in OPT-Concentrations. |
| OPT-Costs spreadsheet estimates the costs to each stakeholder. |
| OPT-Results spreadsheet contains all FORECAST variables (shaded in blue) , including outcome variable such as total annual cost, monthly concentrations, as well as total and individual stakeholder loads. FORECAST variables are used by OptQuest to determine if criteria and allocation objectives are being met. |
| Spreadsheets beginning with "FC" estimate fecal coliform loads and fecal coliform accumulation rates on land surface from nonpoint sources. These spreadsheets are part of EPA's Pathogen Loading Estimation Tool. |
| Spreadsheets beginning with "LOAD" and "STORAGE" contain additional calculations and load functions (e.g., stimate point source loads and determine manure management requirements) that are not part of the Pathogen Loading Estimation Tool. |
Costs, aquatic loads, and concentrations are all classified as FORECAST variables (blue shading) in the spreadsheet OPT-Results. Optquest can evaluate allocation objectives and criteria requirements using only those variables that have been defined as FORECAST variables.
For each iteration in the optimization process, the following steps occur:
- Optquest selects new values for the DECISION variables;
- DECISION variables are used to estimate costs and loads;
- loads and impact coefficient ASSUMPTIONs are used to FORECAST concentration distributions; and
- Optquest compares concentrations to criteria REQUIREMENTS and determines the degree to which the allocation OBJECTIVE is satisfied.
It should also be noted that this spreadsheet model differentiates between environmental loads, aquatic loads, and activity levels as follows:
- Environmental loads refer to
- fecal coliform accumulation on land (e.g., cfu/acre cropland) from nonpoint sources and
- fecal coliform wasteloads from point source discharge locations.
- Aquatic loads are estimated by multiplying incremental concentrations by average flow. Note that impact coefficients are shaded in green in OPT-Concentrations. Monte Carlo trials will randomly select a sample of impact coefficients from each distribution for each optimization iteration.
- Activity levels refer to the technology levels and BMP levels that are needed to implement the management decision. Activity levels are used to estimate costs in OPT-Costs.
For more information about this modeling process, see Modeling Framework.
allo_dem.opt
allo_dem.opt is the optimization file that enables OptQuest to run simulations. This file allows the user to specify the following:
- Allocation Objectives
- Water quality criteria Requirements
- Stakeholder Constraints
- Model simulation run Preferences
To edit this file, you must use the menu options in Optquest (see the instruction for running the model). There are four possible allocation objectives based on the following forecast variables that have been defined in OPT-Decisions:
- Total Annual Cost - Objective: Minimize Total Annual Cost
- Equal Loads - Objective: Minimize sum square differences between stakeholder loads
- Equal Load Red - Objective: Minimize sum square differences between stakeholder load reductions
- Equal % Load Red - Objective: Minimize sum square differences between stakeholder percent load reductions.
Any type of requirement (criteria) can be placed on the monthly concentrations (e.g., average limit, Percent upper-bound limit, maximum limit, etc.). Any type of constraint can be placed on the management decision variables (e.g., wastewater treatment plant effluent concentration < 10 cfu/100ml). The user also specifies the run time for simulations, solution criteria, and run mode (e.g., stochastic or deterministic). Run preferences for the Crystal Ball simulations are specified in the crystal ball menu options in the excel sheet screen (see the instructions for running the model).