|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
INTRODUCTION The Wyoming Water Development Commission (WWDC) has undertaken statewide water basin planning efforts in selected river basins. The purpose of the statewide planning process is to provide decision- makers with current, defensible data to allow them to manage water resources for the benefit of all the state's citizens. Under Task 3B, spreadsheet models are developed to determine average monthly streamflow in the basin during normal, wet, and dry years. The purpose of these models is to simulate existing basin uses, assist in the determining timing and location of water available for future development, and help to assess future water supply alternatives. The WWDC dictated that the models developed for the various river basins across the state be consistent and that the models be developed using software available to the average citizen. The Bear River Basin Plan was the first to be performed. Anderson Consulting, the model developer for this plan, selected Excel as the software to be used for model development. The spreadsheet model developed for the Bear River Basin Plan then defined the software and the modeling approach to be used for all subsequent Basin Plans. The Bear River model was passed onto Boyle Engineering, the model developer for the Green River Basin Plan, to be used as a template for model development in that basin. The Green River Basin models were subsequently passed on to HKM to be used as a template for the Powder/Tongue River Basin models. It should be recognized that the models are quite general in nature and although they provide a reasonable indication of water availability on any given stream, caution should be exercised in drawing conclusions from the results about individual diversions or water uses. Eighteen water availability spreadsheets, one for each of three hydrologic conditions and six distinct sub- basins were developed for the Powder/Tongue River Basin Planning Area:
This memorandum is intended to be both a user's guide and engineering documentation of the models and is organized as follows: Spreadsheet ModelsSPREADSHEET MODELSModel OverviewThe Navigation Worksheets Model Overview The models developed for this plan are intended to simulate water use and availability under existing conditions. For each Powder/Tongue River sub-basin, three models were developed, reflecting each of three hydrologic conditions: dry, normal, and wet year water supply. The spreadsheets each represent one calendar year of flows, on a monthly time step. The modelers relied on historical gage data from 1970 to 1999 to identify the hydrologic conditions for each year in the study period, as discussed in the Surface Water Hydrology memorandum (HKM, 2002). Streamflow, estimated actual diversions, full supply diversions, irrigation returns, and reservoir conditions are the basic input data to the models. For all of these data, average values drawn from the dry, normal, or wet subset of the study period were computed for use in the spreadsheets. The models do not explicitly account for water rights, appropriations, or compact allocations nor is the model operated based on these legal constraints. Further, the model does not associate supplemental reservoir releases to the appropriate water users. However, by calibrating the models to historical streamflows at gaged locations, the models can be used to generally represent existing operations. Theoretical Maximum Diversion Requirements were calculated using the mapped acreage of irrigated lands (see the Irrigated Lands Mapping and Water Rights Data memorandum [HKM, 2002]) and the consumptive irrigation requirements (CIR) provided by Consumptive Use and Consumptive Irrigation Requirements – Wyoming (Pochop et al., 1992). The historical diversion records were then compared to the Theoretical Maximum Diversions yielding mathematical relationships used to calculate the Estimated Actual Diversions and the Full Supply Diversion Requirements for all modeled irrigated lands. A more detailed discussion of this process is offered in the Agricultural Use memorandum (HKM, 2002). The estimated actual diversions and diversion demands as well as irrigation efficiencies, duration of irrigation, and irrigation return flows were then adjusted as appropriate until the models were reasonably well calibrated. The parameters used in the calibration process as well as a summary of the calibration results will be discussed in subsequent sections. To mathematically represent each sub-basin, the river system was divided into reaches based primarily upon the location of major tributary confluences. Each reach was then sub-divided by identifying a series of individual nodes representing diversions, reservoirs, tributary confluences, gages, or other significant water resources features. The resulting network is a simplified representation of actual conditions. Figures 1 through 6 present node diagrams of the sub-basin models developed for the Powder/Tongue River. Historical or virgin flow for each month is supplied to the model at the uppermost node. Where available, upper basin gages were selected as the uppermost model nodes; in their absence, flow at the ungaged headwater point was estimated outside the spreadsheet. A complete discussion of the surface water hydrology work is provided in the Surface Water Hydrology memo (HKM, 2002). For each reach, incremental stream gains (e.g., ungaged tributaries, groundwater inflow, and inflow resulting from man- induced but unmodeled processes) and losses (e.g. seepage, evaporation, and unspecified diversions) are computed by the spreadsheet. These are calculated by adding the net modeled effects (diversions and increases in storage less return flows and decreases in storage) within the basin back into the difference between the upstream and downstream historical gage flows. Stream gains are input at the top of a basin to be available for diversion throughout the basin and losses are subtracted at the bottom of each basin. At each node, a water budget computation is completed to determine the amount of water that bypasses the node. At non-storage nodes, the difference between inflow, including upstream inflows, return flows, imports and basin gains, and outflows, including diversions, basin losses and exports, is the amount of flow available to the next node downstream. For storage nodes, an additional loss calculation for evaporation and the change in storage is evaluated. Also at storage nodes, any uncontrolled spill that occurs is added to the scheduled release to determine total outflow. Diverted amounts at diversion nodes are the minimum of demand (the full supply diversion at the structure) and physically available streamflow. The mass balance, or water budget calculations, is performed for all nodes in a reach. Model output includes the full-supply diversion demand and model simulated diversions at each of the diversion points, and streamflow at each of the Powder/Tongue River sub-basin model nodes. Estimates of impacts associated with various water projects can be analyzed by changing input data, as decreases in available streamflow or as changes to diversions occur. New storage projects that alter the timing of streamflows or shortages may also be evaluated.
Model Development The model was developed using Microsoft® Excel 97. The workbooks contain macros written in the Microsoft® Visual Basic for Applications programming language. The primary function of the macros is to facilitate navigation within the workbook. The models are recalculated and updated automatically whenever a change is made to any of the input data. The model was developed with the novice Excel user in mind and it is assumed that the user has a basic level of proficiency in spreadsheet usage and programming. Every effort has been taken to lead the User through the model with interactive buttons and mouse-driven options. This memorandum will not provide instructions in the use of the Excel program. Model Structure and Components Each of the Powder/Tongue River sub-basin models is a workbook consisting of numerous individual pages (worksheets). Each worksheet is a component of the model and completes a specific task required for execution of the model. There are five basic types of worksheets:
Engineering Notes: Detailed discussion of methodologies, assumptions, and sources used inProgrammers' Notes, which are instructions and suggestions for programmers modifying the model, are included as the final section. These will assist state staff with any modifications of this model to analyze changed conditions or other applications in the Powder/Tongue River Basin. THE NAVIGATION WORKSHEETS A GUI was developed to assist the User in navigating the sub-basin workbooks. The top-level navigation sheet initializes on opening the appropriate Powder/Tongue River sub-basin model Excel file:
User Notes:The Dry, Normal, and Wet Year models each have two main navigation worksheets to view other portions of the workbook. A third sheet contains a diagram of the basin to orient the user, and which provides a link to the Reach/Node worksheets. For Users experienced with Excel spreadsheets, all conventional spreadsheet navigation commands are still operative (e.g., page down, GOTO, etc.). The Central Navigation Worksheet The Central Navigation Worksheet is the "heart" of the model. From here, the User is provided with links to any worksheet in the model. Figure 8 displays the Central Navigation Worksheet from the Clear Creek Sub-Basin Wet Year Model.
User Notes:The Sub-Basin Map User Notes:The Results Navigator User Notes:THE INPUT WORKSHEETS Master List of Nodes The model is structured around nodes at which mass balance calculations are performed and reaches that connect the nodes. Nodes are points on the river that represent such water resources features as gage locations, diversion headgates, major tributary confluences within the Powder/Tongue River sub-basins, or reservoirs. Tables 1 through 6 list the nodes for the six sub-basin models. Engineering Notes:Gage Data Historic monthly stream gage data were obtained from the USGS or the Wyoming Water Resources Data System (WRDS) for each of the stream gages used in the model (Figure 10). Linear regression techniques were used to estimate missing values for the many gages that had incomplete records. Once the gages were filled in for the study period, monthly values for Dry, Normal, and Wet conditions were averaged from the Dry, Normal, or Wet years of the study period.
Headwater inflow at several ungaged locations is also included on the Gage Data worksheet. Different approaches to estimating the flow were used, depending on the complexity of the stream system and availability of data. For a more detailed discussion of the development of flow estimates at ungaged locations as well as missing data a gaged locations, see the Surface Water Hydrology memorandum (HKM, 2002). User Notes:Diversion Data Diversions in the Powder/Tongue River sub-basin models are associated either to municipal use, or agricultural use. The spreadsheets model both the entire diversion and the consumptive portion of all municipal diversions. Agricultural diversion nodes fall into two categories: those for which historical diversion records are available and those for which estimates of actual diversions had to be made. The ditches with historical diversion records served as indicators of irrigation practices throughout the Powder/Tongue River Basin. Their historical diversion records were used to determine Estimated Actual Diversions and Full Supply Diversion Requirements as discussed in the Agricultural Use memorandum (HKM, 2002). Estimated actual diversions are made outside of the spreadsheet model in order to make an initial determination of unaccounted for gains and losses. The points of diversion (service area) GIS theme contain s the information designating the node used in the models. Two Diversion Data worksheets are used: Estimated Actual Diversion Data and Full Supply Diversion Data. Data on the Estimated Actual Diversion Data sheet are used in calculating Estimated Actual Return Flows and initial Ungaged Basin Gains and Losses. Data on the Full Supply Diversion Data sheet are used as the Diversion Demand in the Reach/Node worksheets and are used in determining diversion shortages. The Full Supply Diversion Data are not directly used to calculate return flows, though the modeled return flow calculations are limited by the minimum of the full supply diversion data or by the available inflow to a particular node. Engineering Notes:Import and Export Data Engineering Notes:Options Tables Two tables are included in the Options Tables worksheet (see Figure 11):
Engineering Notes:THE COMPUTATION WORKSHEETS The Computation Worksheets are calculators for parameters required by the Reach/Node water balance computations. They use data supplied in the Input Worksheets. Irrigation returns, ungaged basin gains and losses, and evaporative losses are calculated in the Computation Worksheets. Irrigation Returns The unused portion of a headgate diversion either returns to the river as surface runoff during the month it is diverted, or "deep percolates" into the alluvial aquifer. The deep percolation portion returns to the river through the aquifer but generally lags the time of diversion by several months. The location of the return flow's re-entry to the stream is an important factor in modeling the basin, and depends on the specific topography and layout of the irrigation system. The location of irrigation return flows were determined through the irrigated lands mapping task and are specified as a GIS attribute for each irrigated service area.
There are two Irrigation Return worksheets: the Estimated Actual Return Flows and the Model Simulated Return Flows. Each of these Irrigation Return worksheets has three tables. The first calculates the amount of return flow resulting from each month's diversion at each node, and distributes it in time and place according to the information in the Options Table. The second table then effectively "collects" all the incoming return flows for each month at each node, from the various sources. This table produces the return flow component of inflow at each node. The third table summarizes return flows by reach. Engineering Notes:Evaporative Losses Two reservoirs are explicitly modeled in the Powder/Tongue River basin models: Lake DeSmet and Healy Reservoir in the Clear Creek Model. Other reservoirs, such as Muddy Guard #1 & #2, Patch Reservoir, Zezas Reservoir, and Dull Knife Reservoir, were not explicitly modeled because there is insufficient historical data or operational information to model their operation. The effects of these relatively minor reservoirs are accounted for in the gain/loss terms for these reaches. Several reservoirs are also located upstream of the limits of the models (i.e. Dome Lake and Dull Knife Reservoir). Although these reservoirs are not explicitly modeled, their operations are reflected in the historical records of streamflow gages representing the uppermost limits of the models. The model calculates evaporation losses included in the mass balance calculations at each modeled reservoir node and in the ungaged gain/loss determination. Engineering Notes:Reach Gain/Loss The Powder/Tongue River sub-basin models simulate the major diversions and features of the sub-basins, but many water resources features, such as small tributaries and diversions on those tributaries, are not explicitly included in the computer representation of the physical system. These less-significant water supplies and water uses are lumped together between measured flow points in the river by a modeling construct called ungaged reach gains and losses. These ungaged gains and losses account for all water in the budget that is not explicitly accounted for and includes ungaged tributaries, groundwater/surface water interactions, or any other process not explicitly or perfectly modeled. Engineering Notes:Reach/Node Tables Each non-storage node is represented in the spreadsheet by an inflow section, which includes inflow from the upstream node, irrigation returns, ungaged gains, and imports, if applicable; and an outflow section, which includes ungaged losses and diversions, if applicable. The algebraic sum of these flows is then the net outflow from the node. In the case of storage nodes, evaporation is included as a loss and flow can either go to or come from storage. Again, the water balance is done for the node and outflow is calculated (Figure 14). Engineering Notes:The nodes must be organized in an upstream-to-downstream order within each reach. Diversion demands at each node are referenced from the Full Supply Diversion Data worksheet. Model simulated diversions are the lesser of full supply diversion requirements and available flow. In the event that the full supply demand cannot be met, a warning is provided to inform the User that the diversion has been shorted.
The following subsections contain miscellaneous notes about specific nuances within the Reach/Node tables in the six sub-basin models. See the "Model Node Map" and Node list within each model for the locations of the reaches and nodes discussed below. Little Bighorn River Model Reach 1 Node 89.04 — Jones Ditch Diversions: A portion of the return flows from this ditch enters the Tongue River Basin. Reaches 2, 3, & 4 These reaches are not included in the Ungaged Basin Gain/Loss calculations as they are not bounded by downstream gages. Tongue River Model Reach 4 Nodes 78.04 & 78.05 — Grinnell Ditch Diversions: This ditch on Wolf Creek has historically diverted water for use by the Grinnell Ditch on Goose Creek. The diversion amount was therefore divided between the two nodes with the return flow from 78.04 reentering Wolf Creek and the return flow from 78.05 going to Soldier Creek. Reach 6 Node 70.04 — Sheridan City Intake Diversions: The historical diversion data was averaged for wet, normal, and dry conditions for use in the models. The full diversion amount was modeled with the unconsumed portion returning to Soldier Creek in the same month it was diverted. The consumptive use amounts (the average value over the entire study period) are found in the Municipal Use memorandum (HKM, 2002). Reach 7 Node 69.04 — Diversions u/s of Big Goose & Beaver #2 Ditch: The imports entering the model at this node are the historical diversions from Big Goose & Beaver #1 Ditch for use by Big Goose & Beaver #2. Reach 16 Node 83.06 — Interstate (Pennoyer) Ditch Diversions: This ditch also diverts water for users in Montana. However, as the historical diversion records were insufficient to allow for estimating the Montana portion, only the Wyoming portion is modeled. Reach 20 This reach is used only to account for the imports from North & South Piney Creeks to allow for the return flows to be appropriately calculated and apportioned (Nodes 3.04, 3.08, & 3.14). Clear Creek Model Reach 3 Nodes 3.04, 3.08, & 3.14 — Mead Coffeen, Piney & Cruse, & Prairie Dog Ditches: These ditches export water for use in the Tongue River Basin. Nodes 3.10 & 3.12 — Piney Divide Ditch: This ditch diverts for irrigated lands lying along the ditch as well as for use by other ditches on Little Piney Creek. The diversion amount for Node 3.10 uses the Estimated Actual Diversion and the Full Supply Diversion based on the acreage of lands served directly by the Piney Divide Ditch as indicated by the irrigated lands and service area GIS themes. The diversion amount for Node 3.12 is the remaining portion of the historical diversion data. Node 4.08 — Lake DeSmet Intake Tunnel Diversions: The data for this diversion is taken directly from data provided on the operation of Lake DeSmet and Healy Reservoir by the SEO. Reach 7 Node 7.04 — Little Piney Diversions to Piney Creek: The data for this diversion is taken from data provided on the operation of Lake DeSmet and Healy Reservoir by the SEO. Reach 9 Node 9.06 — Lake DeSmet Reservoir: The releases from this node enter Piney Creek below the junction with Little Piney Creek (Node 8.04). The diversions from the reservoir are the releases to Box Elder Creek and in the model enter Piney Creek at Node 11.02. Reach 12 Nodes 13.12 & 13.14: Lake DeSmet (M&M) Ditch Diversions: This ditch diverts for irrigated lands lying along the ditch as well as for Lake DeSmet. The diversion amount for Node 13.12 uses the Estimated Actual Diversion and the Full Supply Diversion based on the acreage of lands served directly by the ditch as indicated by the irrigated lands and service area GIS themes. The diversion amount for node 13.14 is the remaining portion of the historical diversion data. Reach 16 Node 18.04 — Penrose Ditch Diversions to Johnson Creek: The historical diversion data for Penrose Ditch on French Creek are used for this intra-basin diversion. The import data is the historical data from the Four Lakes & French Creek Diversion to French Creek. Reach 17 Node 19.06 — Buffalo City Municipal Diversions: A constant diversion of 6 cubic feet per second (cfs) was used. The consumptive use amount was used to determine the remainder (returns to Clear Creek) and is found in the Municipal Use memorandum (HKM, 2002). Reaches 21 & 22 Nodes 23.08, 23.10, & 24.02 (Node 23.10 is upstream of Node 23.08 on Clear Creek) — Healy Reservoir: The diversions to Healy Reservoir leave Clear Creek at Node 23.10 and the reservoir releases enter Clear Creek at Node 23.08. The reservoir data is from data provided on the operation of Lake DeSmet and Healy Reservoir by the SEO. Crazy Woman Creek Model Reaches 1, 2, & 3 North Fork Crazy Woman Creek, Little North Fork Crazy Woman Creek, and Kelly Creek are modeled as joining upstream of Cook Ditch (Node 33.04) as the ditch receives water from all three streams. Node 32.04 — Thompson & Matthew Ditch Diversions: This ditch also diverts to Muddy Guard #2 Reservoir. However, only the portion diverted to the irrigated lands is modeled due to lack of reservoir operation data. Reach 5 Muddy Guard #1 & Patch Reservoirs are not modeled due to lack of operation data. However nodes could be added in the future to explicitly model them. Reach 9 Node 38.04 — Mitchell & Long Ditch Diversions: This ditch also diverts to Zezas Reservoir. However, only the portion diverted to the irrigated lands is modeled due to lack of reservoir operation data. Powder River Model See the Agricultural Use memorandum for the methodology used to determine the estimated actual diversions and the full supply diversions (HKM, 2002). Little Powder River Model Reach 3 This reach is not included in the Ungaged Basin Gain/Loss calculations as it is not bounded by downstream gages. THE RESULTS WORKSHEETS Several forms of model output can be accessed from the Summary Options worksheet. These include river outflow data (by node or by reach), and diversion data (by node, by reach, or model simulated compared to full supply and estimated actual). Outflows This worksheet summarizes the flows at all nodes in the model. The "Outflow Calculations: By Node" table summarizes the net outflow for all nodes. The nodes are grouped by reach. The "Outflow Calculations: By Reach" table presents the net outflow for each reach. A primary purpose for developing the spreadsheet models was to determine surface water availability under baseline conditions. The Outflow by Reach table provided the basis for determination of baseline surface water availability, as described in the Available Surface Water Determination memorandum (HKM, 2002). Diversions This worksheet summarizes the diversions at all nodes in the model. The "Summary of Diversion Calculations: By Node" tables summarizes the computed diversions which are made at each node. The nodes are grouped by reach. The "Summary of Diversion Calculations: By Reach" table presents the total diversions taken within each reach. The "Comparison of Model Simulated Diversions vs. Full Supply Diversions (Shortage) and vs. Estimated Actual Diversions (Calibration Difference)" table presents the estimated shortages and a measure of calibration of modeled diversions (Figure 15).
Shortage is defined as the difference between Full Supply Diversions and Model Simulated Diversions. The Calibration Difference is the term used to determine how close the model simulates historical diversions. As the model does not explicitly take into account water right priorities and other legal constraints nor does it explicitly associate supplemental reservoir releases to the appropriate diversions, values within 35 percent are considered reasonable. Calibration values greater than 35 percent are primarily due to lack of information concerning irrigation practices (efficiencies, return lags, and historical diversion records) for those lands served by spreader dikes and intermittent diversions on ephemeral streams and in some cases also reflect inadequate surface water hydrology data. CALIBRATION SUMMARY The Basin Gain/Loss Charts along with the "Comparison of Model Simulated Diversions vs. Full Supply Diversions (Shortage) and vs. Estimated Actual Diversions (Calibration Difference)" tables provide the basis for assessing how well calibrated the models are. This information is provided in Appendix A. PROGRAMMERS' NOTES Modification of the Powder/Tongue River Models The Powder/Tongue River Spreadsheet Models were written assuming that they may be modified for use in future investigations of other Wyoming river basins. Instructions are incorporated throughout this document providing hints and suggestions to the Programmer. Some overall suggestions are included here for consideration of the Programmer.
For various sections of the Excel spreadsheet model, programmers' notes have been prepared to assist or guide modifications in future modeling efforts. Graphical User Interface (GUI) The GUI was developed using Visual Basic for Applications within Microsoft® Excel. Modification of the GUI requires an understanding of the Visual Basic programming language. When the User opens the Powder/Tongue River sub-basin model files - the GUI - the model is informed where on the User's computer the file is located. All files must be located in the same folder for the model to operate properly. Once the GUI is initialized, the model will look in the same location for any additional files. Future revisions of the Powder/Tongue River sub-basin models will require the following minor modifications to the GUI:
Excel programmers modifying the spreadsheet model will need to modify the Reach/Node Description table located to the right of the visible screen (Figure 16), for the Navigation Worksheet to work properly. If new reaches must be entered, INSERT columns and renumber the header accordingly. This will cause formulas referencing this table to change accordingly. Also, if the table must be expanded vertically (i.e., more nodes must be added than the table currently accommodates), the same practice should be followed. That is, always INSERT rows, columns, or cells within the existing table. This allows the Programmer to avoid modification of formulas influenced by the table.
The Programmer must also modify the macro associated with the pull-down menu to include all reaches in a new model if more than 30 reaches are needed. Begin by naming a cell in the upper left of any new Reach worksheets (e.g., name cell A3 "Reach31"). Then modify the Visual Basic (VB) code to include a "GoTo" reference for that worksheet. Following is the VB code associated with the subroutine named "Reach". New reaches can be incorporated in this macro by copying one "else if" statement and renaming the appropriate range number. Sub Reach () Results Navigator This portion of the worksheet must be customized to correlate with any future versions of this model. Different river basins will have different compact allocation computations and formats. When incorporated into this model, the Summary Navigator worksheet should be modified to allow the User to "jump" directly to the new tables. Diagram of the Basin The model node diagrams are dynamically linked to the Reach/Node worksheets. It is also included as a visual reference for orientation to the basin, helping the user understand locations of nodes and connectivity of reaches. The six Powder/Tongue River sub-basin diagrams were created in Excel using autoshapes with the appropriate navigational macros assigned to the reach arrows, node circles, and text descriptions so the user may "jump" directly to the desired Reach/Node worksheet. Master Node List This list is referenced throughout the workbook by "lookup" functions. The "lookup" functions primarily associate the name of a node with the node number when it is entered at certain locations. This eases input of information in tables such as the Node Tables, Return Flow Tables, etc. In those tables, the Programmer can simply enter the Node number and the Name is filled in automatically. Therefore, whenever a Node is added to a Reach, it must be inserted in this table. Because the model frequently uses "lookup" functions, it is highly recommended that the Programmer use Excel's "INSERT ROWS" command whenever adding information to this or other data tables. When information is added this way, formulas referencing the table automatically update to refer to the newly expanded range. If rows are added to the bottom of a listing, the referenced formula will not "find" the new data. It is not required to sort this list in any particular order; all formulas referencing the table will retrieve the correct information regardless of order. However, for ease of reading, it is recommended that it be sorted either by node number or by node name. If the User must add nodes between existing nodes, they do not necessarily need to be numbered in sequential order. The node numbers are used as unique identifiers only. The correct node sequencing within each reach is defined with the "Reach/Node Description Table" on the Navigation worksheet. Diversion Data This table is referenced by several other worksheets in the Powder/Tongue River sub-basin models via "lookup" functions.
It is important to note that ALL nodes are included in this table, even if no diversions occur at that node (e.g. gaging station nodes). This simplifies the spreadsheet logic used in the Node Tables. By including all nodes in this table, the Node Tables are all identical and can generally be copied as many times as are needed without modification (see User and Programmer Notes pertaining to the node/reach worksheets for exceptions to this rule). Therefore, if no diversions occur at a node, simply leave the data columns blank or insert zeros. Because the model uses "lookup" functions to retrieve data from this table, it is highly recommended that the Programmer use Excel's "INSERT ROWS" command whenever adding information to this or other data tables. When information is added this way, formulas referencing the table automatically update to refer to the newly expanded range. If rows are added to the bottom of a listing, the referring formula will not "find" the new data. After rows are inserted, the Programmer can copy the formulas in the "Name" column to retrieve gage names automatically. The Programmer can also copy the formulas in the "Reach" column to retrieve the reach number automatically from the "Reach/Node Description Table" on the Navigation worksheet. Import and Export Data This table is referenced by several other worksheets in the Powder/Tongue River sub-basin models via "lookup" functions. Any imports or exports must be entered here. No computations are conducted within this worksheet. It is important to note that ALL nodes are included in this table, even if no imports or exports occur there (e.g. gaging station nodes). This simplifies the spreadsheet logic used in the Node Tables. By including all nodes in this table, the Node Tables are identical and can be copied as many times as are needed without modification. Therefore, if no diversions occur at a node, simply leave the data columns blank or insert zeros. Because the model uses "lookup" functions to retrieve data from this table, it is highly recommended that the Programmer use Excel's "INSERT ROWS" command whenever adding information to this or other data tables. Return Flow All nodes where diversions occur must be included in the Return Flows worksheet. If nodes are added, the Programmer should follow the same precautions outlined in the discussion of previous worksheets and use Excel's "INSERT ROWS" commands. This simplifies modifications because formulas referencing this worksheet via "lookup" functions will be modified automatically. Once rows are inserted for new nodes, the Programmer can copy an existing "Node Evaluation" table as many times as needed. When the Programmer changes the Node Number, the Node Name and Total Diversions will update automatically with a "lookup" to the Master Node List and the Diversions Data worksheets, respectively. The Programmer must then modify the "Efficiency Pattern", "Return Pattern", "TO" and "Percent" features to represent conditions associated with the diversions from the new node. To update the "Irrigation Returns: Node Totals Table", the Programmer must first be certain that all nodes are included in the list of nodes. For simplicity, the Programmer can copy the Node Number column from the Master Node List and paste it here. Then the Programmer can copy the remaining portion of a row including Name, Monthly Summation, and Reach number as many times as needed. The Programmer should be cautioned to verify that the ranges referenced in the monthly summation columns span the entire range of Node Evaluation tables following addition of nodes. The Programmer should also be sure to INSERT new rows within the table when they are needed rather than adding rows to the end of the table. To update the "Irrigation Returns: Reach Totals Table" the Programmer must enter all reach numbers in the appropriate columns and then copy the formulas in the January through December columns. Verify that the range referenced in the monthly summation cells span the entire range of the "Irrigation Returns: Node Totals Table" after it was modified. Options Table Incorporation of a "Irrigation Return Pattern" or "Irrigation Return Lag" relationship which differs from those included in this model can be done by either over-writing one of the existing lines or by inserting a new line within the existing table. If irrigation returns are determined to require longer than six months before returning to the river system, a column may be inserted in the Irrigation Return Lags table. However, it is important to note that the formulas of the Irrigation Returns worksheet will need modification to reflect any additional months. Basin Gain/Loss Ungaged Basin Gain/Losses must be computed on a Basin-by-Basin basis in a manner as shown in the "Gain/Loss" worksheet (Figure 18). To do this, the Programmer must reference the appropriate gage data, diversion data (iteration 1: estimated actual; iteration 2: simulated diversions on diversion summary sheet), return flow data (iteration 1: estimated actual; iteration 2: model simulated), and reservoir data; building a budget as shown in the worksheet. Each Basin requires construction of an individual table with that Reach's specific conditions incorporated. At the bottom of each computation table, the Programmer must enter a Basin Name corresponding to the Basin(s) for which the Gains/Losses will be applied. New Basin Gain/Loss tables may be created by copying another table and entering the new node numbers and basin name. The Basin Names must then be entered into the Summary Table and the tables will automatically update. The Programmer should verify that the lookup formulas in the Basin Gain Table span the entire Basin Gain/Loss Calculation Iteration One tables. The Basin Loss Table is updated by subtracting the downstream gage data from the total inflow to the most downstream basin node. New Basins should be added to the Summary tables by INSERTING new rows as needed.
Ungaged Reach gains are added to the upstream end of a Reach to make them available to diversions within the Reach. Ungaged Reach losses are subtracted at the downstream end. To facilitate this feature, the Programmer must enter the Reach Name in the Reach Gains line at the upstream node of a reach and the Node Table will automatically update. The Reach Name must also be entered in Ungaged Losses line of the Reach's downstream node and the Node Table will automatically update. By incorporating Ungaged Gains and Losses, the spreadsheet model is calibrated to match historic gaging data at each gage node. Node Tables Adaptation of the Powder/Tongue River sub-basin models for other river basins will require reconstruction of the Reach/Node worksheets on a node-by-node basis. Because all values in the Node tables are obtained via "lookup" functions, this is a relatively easy task. The Node Inflow to any Node Table is referenced in one of three ways: 1. If the node is the upstream end of the model, or upstream node of a modeled tributary, the inflow is retrieved from the Gage Data worksheet using a "vlookup" function. Refer to the Tongue River Model Node 80.02 for an example of this method. 2. If the node is located at the upstream end of any other reach, the Node Inflow is referenced as the NET Flow from the Reach that feeds it. In this case, cell references must be manually modified. Refer to the Tongue River Model Node 81.02 for an example of this method. 3. If the node is located at any midpoint within a Reach, the Node Inflow is simply the NET Flow from the Node upstream of it. Refer to the Tongue River Model Node 80.06 for an example of this method. Most nodes will be built using the third method described above. In this case, once the Node Inflow cells have been modified as in the example (i.e., Node 80.06), the Node Table may be copied as many times as needed and the Reach can be constructed in a sequential manner. The Programmer must enter the Node Number in the cell at the top of each Node Table cell and the worksheet will return the Node Name and all corresponding data from the worksheets referenced. Outflow Summary The "Outflow Calculations: By Node" tables were generated using lookup functions which reference the corresponding Reach worksheets. The values in the "Node" column were entered manually and the lookup tables constructed accordingly. The "Outflow Calculations: By Reach" table simply references the downstream limit of each "Outflow Calculations: By Node" table. Diversions Summary The "Summary of Diversion Calculations: By Node" tables were generated using lookup functions which reference the corresponding Reach worksheets. The values in the "Node" column were entered manually and the lookup tables constructed accordingly. The "Summary of Diversion Calculations: By Reach" table references the "Summary of Diversion Calculations: By Node" tables using SUMIF functions. The "Comparison of Model Simulated Diversions vs. Full Supply Diversions (Shortage) and vs. Estimated Actual Diversions (Calibration Check)" table looks up Estimated Actual Diversions and Full Supply Diversions for each node from the Estimated Actual Diversions Data and the Full Supply Diversions Data worksheets. It also looks up the Model Simulated Diversions from the "Summary of Diversion Calculations: By Node" tables and computes the shortage and the calibration check. Specific Instructions for Adding a Single Node to a Powder/Tongue River Model The Powder/Tongue River sub-basin models have been constructed such that new nodes, representing a new point of diversion, a reservoir, a streamflow gage, an instream flow segment or any other point at which the user needs to evaluate, can be added. The process for adding a new node is described below. Worksheets need to be modified in the order given here. 1. General The workbooks have been provided with the "protection mode" enabled for each worksheet. No password has been used, therefore the user must turn the protection feature off to make changes (Tools / Protection / Unprotect Worksheet). The user may also find it helpful to turn on the row and column headers and the sheet tabs on each worksheet to be modified (Tools / Options / View). It is recommended that the user make any modifications to the model in the order that is presented below. 2. Master List of Nodes Worksheet There are two ways of modifying the Master List of Nodes:
3. The Central Navigation Worksheet The Reach/Node Description table located to the right of the visible screen must be modified. Go to the column containing the reach that you wish to modify. Type in the node number that you wish to add. If this is not the last node in the reach, it is simplest to retype the subsequent nodes in the rows below rather than inserting a cell. 4. Gage Data / Inflow Data Worksheet If the new node to be added represents a gage or an inflow point to the model, the Gage Data / Inflow Data worksheet must be modified. As with the Master List of Nodes, the user can add the new node and relevant data in the next available unused row in the table (as defined by the borders and shading). Alternatively, the user can INSERT a row in the appropriate location to maintain the reach/node sequence, then add the new node and data. 5. Diversion Data Worksheet All nodes MUST be included in this table even if no diversion occurs at the node. The user may simply enter the new node and relevant data in the next available unused row in the table (as defined by the borders and shading) or the user can INSERT a row in the appropriate location to maintain the reach/node sequence, then add the new node and data. Both the Estimated Actual Diversion Data and the Full Supply Diversion Data worksheets must be updated. 6. Import and Export Data Worksheet All nodes MUST be included in this table even if no import or export occurs at the node. The user may simply enter the new node and relevant data in the next available unused row in the table (as defined by the borders and shading) or the user can INSERT a row in the appropriate location to maintain the reach/node sequence, then add the new node and data. 7. Return Flows Worksheet All nodes where diversions occur MUST be included in the Return Flows worksheets (both the Estimated Actual Return Flows and the Model Simulated Return Flows must be updated). Select an entire Irrigation Return table. COPY the selected cells, INSERT COPIED CELLS and select SHIFT CELLS DOWN. Update the node number in the yellow shaded cell. The node name will automatically update. The user must then update the "Efficiency Pattern", "Return Pattern", "TO" and "Percent" cells (shaded yellow) to represent conditions associated with the diversions from the new node. To update the "Irrigation Returns: Node Totals Table", the user must first be certain that all nodes are included in the list of nodes. For simplicity, the user can copy the Node Number column from the Master Node List and paste it here. Be sure that the Master Node List does not extend past the yellow shaded area. Then the user can copy the Monthly Summation and Reach number equations as many times as needed. The user should be cautioned to verify that the ranges referenced in the monthly summation columns span the entire range of Node Evaluation tables following addition of nodes. As currently constructed, the "Irrigation Returns: Reach Totals Table" requires no modification for the simple addition of a node. 8. Options Table Worksheet Incorporation of a "Irrigation Return Pattern" or "Irrigation Return Lag" relationship which differs from those included in this model can be done by either over-writing one of the existing lines or by inserting a new line within the existing table. If irrigation returns are determined to require longer than six months before returning to the river system, a column may be inserted in the Irrigation Return Lags table. However, it is important to note that the formulas of the Irrigation Returns worksheet will need modification to reflect any additional months. 9. Evaporative Losses Worksheet If the new node is a storage node, COPY the rows containing the "Mean Monthly Evaporation (inches)", "Historical End-of-Month Contents (acre-feet)" and "Surface Area (acres)" tables and insert the rows above the "Mean Monthly Evaporation (acre-feet)" table. Update the node number and the node name will automatically update. Enter in gross evaporation and precipitation for the new node. Enter the historical end-of-month contents for the reservoir. If the gross evaporation and precipitation are the same for the new node as for any existing nodes, only the "Historical End-of-Month Contents" and "Surface Area" tables need to be copied (or rows inserted in the existing tables). Select and COPY the rows containing the existing area-capacity table, then paste the rows below the existing table. Update the node number and area-capacity information. The reservoir surface area is calculated by looking up historical end-of-month content and interpolating the surface area from the area-capacity table. The "vlookup' portion of the equation must be updated to correspond to the new area-capacity table. Enter a new node number and the equation to calculate the mean monthly evaporation within the "Mean Monthly Evaporation (acre-feet)" table. If there is no room available in the table, INSERT a row within the table and add the necessary information. 10. Basin Gain/Loss Worksheet Determine the ungaged basin gain/loss table that the new node is within. Locate the corresponding table in the Basin Gain/Loss Worksheet. Select the row in the diversion portion of the table either above or below where the new node needs to be inserted. COPY the selected cells, then INSERT COPIED CELLS in the appropriate location. Update the node number in the yellow shaded cell. The node name and diversions associated with the new node will automatically update. Repeat these steps in the return flow portion of the table. This should be done to both the Iteration One and the Iteration Two worksheets. 11. Reach/Node Worksheet Select the rows containing an entire Node table. COPY the selected rows, then move to the new location in the workbook and select INSERT COPIED CELLS. Update the node number in the yellow shaded cell. The node name, diversions, irrigation returns, ungaged gains/losses and import/exports will automatically update if all the above steps have been completed. The Node Inflow to any Node Table references one of three sources:
If the Reach/Node table represents a reservoir, the user will need to manually update the cells shaded yellow. As a precautionary measure, it is best to check the Node Inflow in the Reach/Node table below where the new node has been inserted to ensure that the appropriate cells are referenced. REFERENCES Daly, Chris and George Taylor, April 1998. Wyoming Average Monthly or Annual Precipitation, 1961- 1990. Water and Climate Center of the Natural Resources Conservation Service. Portland, Oregon HKM Engineering Inc., 2002. Available Surface Water Determination, Technical Memorandum, Powder/Tongue River Basin Plan. Billings, Montana HKM Engineering Inc., 2002. Municipal Use, Technical Memorandum, Powder/Tongue River Basin Plan. Billings, Montana HKM Engineering Inc., 2002. Irrigation Diversion Operation and Description, Technical Memorandum, Powder/Tongue River Basin Plan. Billings, Montana HKM Engineering Inc., 2002. Storage Operation and Description, Technical Memorandum, Powder/Tongue River Basin Plan. Billings, Montana HKM Engineering Inc., 2002. Surface Water Hydrology, Technical Memorandum, Powder/Tongue River Basin Plan. Billings, Montana Pochop, Larry, Travis Teegarden, Greg Kerr, Ronald Delaney and Victor Hasfurther, October 1992. Consumptive Use and Consumptive Irrigation Requirements – Wyoming, WWRC Publication #92-06. Laramie, Wyoming
Appendix A
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Privacy Policy | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
