Northeast Wyoming River Basin 2002 Water Plan Model Development Technical Memoranda

Northeast Wyoming River Basins Water Plan
Technical Memoranda

SUBJECT: Appendix L
Spreadsheet Model Development and Calibration

PREPARED BY: HKM Engineering Inc.

DATE: February 2002

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 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, 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 Northeast Wyoming River Basins Plan 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.

Twelve water availability spreadsheets, one for each of three hydrologic conditions and four distinct sub- basins were developed for the Northeast Wyoming River Basins Planning Area:

Belle Fourche River sub-basin
Redwater Creek sub-basin
Beaver Creek sub-basin
Cheyenne River sub-basin

The three workbooks for each sub-basin are yoked together with a menu-driven graphical user interface (GUI), effectively creating six sub-basin models.

This memorandum is intended to be both a user's guide and engineering documentation of the models and is organized as follows:

Spreadsheet Models
Model Overview
Model Development
Model Structure and Components
The Navigation Worksheets
The Central Navigation Worksheet
The Sub-Basin Map
The Results Navigator
The Input Worksheets
Master List of Nodes
Gage Data
Diversion Data
Import and Export Data
Options Tables
The Computation Worksheets
Irrigation Returns
Evaporative Losses
Basin Gain / Loss
The Reach/Node Worksheets
Belle Fourche River Notes
Redwater Creek Notes
Beaver Creek Notes
Cheyenne River Notes
The Results Worksheets
Outflows
Diversions
Programmers' Notes
Modification of the Northeast Wyoming River Basins Models
Graphical User's Interface (GUI)
Navigation Worksheets
Results Navigator
Diagram of the Basin
Master Node List
Diversion Data
Import and Export Data
Return Flow
Options Table
Basin Gain/Loss
Node Tables
Outflow Summary
Diversions Summary
Specific Instructions for Adding a Single Node to a Northeast Wyoming River
Basins Model
Appendix A - Calibration Summary

SPREADSHEET MODELS

Model Overview

The models developed for this plan are intended to simulate water use and availability under existing conditions. For each Northeast Wyoming River Basins 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 from the neighboring Powder/Tongue River Basin 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. For example, the Belle Fourche River Model consists of reaches with tributaries grouped together. Figures 1 through 4 present node diagrams of the sub-basin models developed for the Northeast Wyoming River Basins Plan.

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 Northeast Wyoming River Basins 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.

click to enlarge

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 Northeast Wyoming River Basins 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:

Navigation Worksheets: Graphical User Interfaces (GUIs) containing buttons used to move within the workbook;

Input Worksheets: Raw data entry worksheets (USGS Gage data or headwater inflow data, Diversion Data, etc.);

Computation Worksheets: Compute various components of the model (reservoir evaporation, irrigation return flows, etc.);

Reach/Node Worksheets: Calculate node by node computations of the water budget; and

Results Worksheets: Tabulate and present the model output.

In the following sections, each component of the Northeast Wyoming River Basins sub-basin models is discussed in greater detail. A general discussion of each component includes a brief overview of the function. The following notes are also included as appropriate:

Engineering Notes: Detailed discussion of methodologies, assumptions, and sources used in the development of that component;
Calibration Notes: Discussions of how this component is used for model calibration; and
User Notes: "How to" instructions for model Users.

Programmers' 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 Northeast Wyoming River Basins Plan.

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 Northeast Wyoming River Basins sub-basin model Excel file:

Belle Fourche Model.xls,
Redwater Model.xls,
Beaver Model.xls,
Cheyenne Model.xls

The GUI provides a brief tutorial and information regarding the current model version (see Figure 5). From the GUI, the User may select the dry, normal, or wet year model.

User Notes:
Upon opening the selected Northeast Wyoming River Basins sub-basin model file, the User is presented with several options:
Dry Year Model: Open the Dry Year Model workbook,
Normal Year Model: Open the Normal Year Model workbook,
Wet Year Model: Open the Wet Year Model workbook,

About the (Sub-basin) Model: Obtain information pertaining to the current version of the
model,
Tutorial: Open a brief tutorial that describes the general structure of a spreadsheet workbook
Close the (Sub-basin) Model: Close all open workbooks.

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 6 displays the Central Navigation Worksheet from the Belle Fourche River Sub-Basin Wet Year Model.

User Notes:

This is the first worksheet the User sees upon selecting a hydrologic condition from the GUI. Using the gray buttons, the User can move to

The sub-basin diagram (View a Diagram of the Model Nodes),
Any of the Reach/Node worksheets (Go to this Reach),
The Input Worksheets and Computation Worksheets (View List of All Nodes, Gage Data / Inflow Data, Estimated Actual Diversion Data, Full Supply Diversions Data, Imports & Exports, Evaporative Losses, Options Tables, Estimated Actual Return Flows, Basin Gain/Loss Iteration 1, Model Simulated Return Flows, Basin Gain/Loss Iteration 2), and
The Results Navigator (Results Summary) which leads in turn to several summaries of output. The User specifies the reach he wants to go to by selecting it from the pull-down menu. When a reach is selected, the table to the right lists all the nodes in that reach by number and name.

The Sub-Basin Map

User Notes:
The Sub-Basin Map Worksheet provides a navigable schematic diagram of the sub-basin (see Figures 1 through 4). This interactive screen allows the User to visually select a reach. To move to the water budget calculations for a reach, simply click on the desired reach arrow or its name.

The Results Navigator

User Notes:
The Results Navigator (Figure 7) allows selection of any of the following output tabulations:

Outflows summarized by node
Outflows summarized by reach
Diversions summarized by node
Diversions summarized by reach
Model Simulated versus Full Supply and Estimated Actual Diversions

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 Northeast Wyoming River Basins sub-basins, or reservoirs. Tables 1 through 4 list the nodes for the four sub-basin models.

Engineering Notes:
The decision of how best to represent a river basin by reaches and nodes is a key element of river basin modeling. The choice of nodes must consider the objectives of the study and the available data. It also must represent all the key water resources features that govern the operation of the basin.
The following is a summary of the number of reaches and nodes used to represent each sub-basin:

Belle Fourche River Model: 28 reaches, 51 nodes
Redwater Creek Model: 5 reaches, 12 nodes
Beaver Creek Model: 14 reaches, 29 nodes
Cheyenne River Model: 28 reaches, 49 nodes
User Notes:
This worksheet presents a master list of all nodes included in the Northeast Wyoming River Basins sub-basin models. The list allows the User to view a simple, comprehensive listing of all nodes within the model, organized by reach and node number. This master list governs naming and numbering conventions on many worksheets, so changes to the list must be done with great care. Many of the calculations within the spreadsheet are dependent on the proper correlation of node names and numbers.

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 8). 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.

Table 1
Belle Fourche River Model Nodes

Node No. Node Name

Node 49.02 Belle Fourche River above Dry Creek near Piney (06425780)

Node 50.02 Miscellaneous Tributaries above Gage 06426500

Node 50.04 Diversions on Misc. Tributaries above Gage 06426500

Node 51.02 Caballo Creek near Piney (06425900)

Node 52.02 Dry Cr, Yellow Hammer Cr, Whitetail Cr, & Four Horse Cr Headwaters

Node 53.02 Junction of Belle Fourche River, Four Horse Cr & Others

Node 53.04 Diversions on Belle Fourche River below Caballo Cr

Node 54.02 Raven Creek near Moorcroft (06425950)

Node 55.02 Timber Creek & Buffalo Creek Headwaters

Node 55.04 Diversions on Timber Cr & Buffalo Cr

Node 56.02 Junction of Belle Fourche River, Buffalo Cr & Others

Node 56.04 Diversions on Belle Fourche River below Buffalo Cr

Node 57.02 Donkey Creek Headwaters

Node 57.04 Diversions on Donkey Creek

Node 58.02 Trail Cr, Dry Cr, & Robinson Cr Headwaters

Node 58.04 Diversions on Trail Cr, Dry Cr, & Robinson Cr

Node 59.02 Junction of Belle Fourche River, Robinson Cr & Others

Node 59.04 Diversions on Belle Fourche River below Donkey Cr

Node 59.06 Return Flow above Gage 06426500

Node 60.02 Belle Fourche River below Moorcroft (06426500)

Node 61.02 Miscellaneous Tributaries to Keyhole Reservoir

Node 61.04 Diversions on Misc. Tributaries to Keyhole Reservoir

Node 62.02 Miller Cr, Lone Tree Cr, Deer Cr, & Eggie Cr Headwaters

Node 62.04 Diversions on Miller Cr & Deer Cr

Node 63.02 Duck Cr, Smoke Cr, Berger Cr, Wind Cr, Mule Cr, & Cottonwood Cr Headwaters

Node 63.04 Diversions on Wind Cr & Mule Cr

Node 64.02 Keyhole Reservoir

Node 65.02 Belle Fourche River below Keyhole Reservoir (06427500)

Node 66.02 Miscellaneous Tributaries above Gage 06428200

Node 66.04 Diversions on Misc. Tributaries above Gage 06428200

Node 67.02 Arch Cr, Inyan Kara Cr, Cabin Cr, & Miller Cr Headwaters

Node 67.04 Diversions on Arch Cr, Inyan Kara Cr, Cabin Cr, & Miller Cr

Node 68.02 Junction of Belle Fourche River, Miller Cr & Others

Node 68.04 Diversions on Belle Fourche River below Gage 06427500

Node 69.02 Lytle Cr, Whitetail Cr, Blacktail Cr, & Beaver Cr Headwaters

Node 69.04 Diversions on Lytle Cr, Whitetail Cr, Blacktail Cr, & Beaver Cr

Node 70.02 Junction of Belle Fourche River, Beaver Cr & Others

Node 70.04 Diversion on Belle Fourche River above Gage 06428200

Node 70.06 Return Flow above Gage 06428200

Node 71.02 Belle Fourche River near Alva (06428200)

Node 72.02 Miscellaneous Tributaries above Gage 06428500

Node 72.04 Diversions on Misc. Tributaries above Gage 06428500

Node 73.02 East Creek & Arnold Creek Headwaters

Node 74.02 Junction of Belle Fourche River, East Cr, & Arnold Cr

Node 74.04 Diversions on Belle Fourche River below Gage 06428200

Node 75.02 Horse Cr, Pine Cr, Kilpatrick Cr, Kruger Cr, & Oak Cr Headwaters

Node 75.04 Diversions on Pine Cr, Kilpatrick Cr, Kruger Cr, & Oak Cr

Node 76.02 Junction of Belle Fourche River, Oak Cr & Others

Node 76.04 Diversions on Belle Fourche River above Gage 06428500

Node 76.06 Return Flow above Gage 06428500

Node 76.08 Belle Fourche River at WY-SD Stateline (06428500)

Table 2
Redwater Creek Model Nodes

Node No. Node Name

Node 1.02 Sand Creek near Ranch A near Beulah (06429905)

Node 1.04 Diversions on Sand Cr below Gage 06429905

Node 2.02 South Redwater Creek Headwaters

Node 2.04 Diversions on South Redwater Cr

Node 3.02 Junction of South Redwater Cr & Sand Cr

Node 4.02 Redwater Creek Headwaters

Node 4.04 Diversions on Redwater Cr

Node 5.02 Junction of Redwater Cr & South Redwater Cr

Node 5.04 Murray Ditch Diversions to lands in WY

Node 5.06 Murray Ditch Diversions to lands in SD

Node 5.08 Murray Ditch Returns Flows above Gage 06430500

Node 5.10 Redwater Creek at WY-SD Stateline (06428500)

Table 3
Beaver Creek Model Nodes

Node No. Node Name

Node 6.02 Beaver Creek Headwaters

Node 6.04 Diversions on Beaver Cr

Node 6.06 Diversions on Beaver Cr above Oil Cr

Node 7.02 Oil Creek Headwaters

Node 7.04 Diversions on Oil Cr

Node 8.02 Junction of Beaver Cr & Oil Cr

Node 8.04 Diversions on Beaver Cr below Oil Cr

Node 9.02 Blacktail Creek Headwaters

Node 9.04 Diversions on Blacktail Cr

Node 10.02 Junction of Beaver Cr & Blacktail Cr

Node 10.04 Diversions on Beaver Cr below Blacktail Cr

Node 11.02 Dry Beaver Creek Headwaters

Node 12.02 Beaver Creek at Mallo Camp near Four Corners (06392900)

Node 13.02 Miscellaneous Tributaries above Gage 06392950

Node 13.04 Diversions on Stockade Beaver Cr Tributaries

Node 14.02 Junction of Stockade Beaver Cr, Dry Beaver Cr, & Beaver Cr

Node 14.04 Diversions on Stockade Beaver Cr above Gage 06392950

Node 14.06 Return Flow above Gage 06392950

Node 15.02 Stockade Beaver Creek near Newcastle (06392950)

Node 15.04 Spencer Reservoir & Diversions

Node 15.06 Diversions d/s of Spencer Reservoir

Node 16.02 Junction of Beaver Cr & Stockade Beaver Cr

Node 17.02 Beaver Creek near Newcastle (06394000)

Node 17.04 Diversions on Beaver Cr below Gage 06394000

Node 17.06 Beaver Creek at WY-SD Stateline

Node 46.02 Miscellaneous Tributaries above Gage 06394000

Node 46.04 Diversions on Misc. Tributaries above Gage 06394000

Node 47.02 Miscellaneous Tributaries above Stockade Beaver Creek Mouth

Node 47.04 Diversions on Misc. Tribs above Stockade Beaver Cr Mouth

Table 4
Cheyenne River Model Nodes

Node No. Node Name

Node 18.02 Dry Fork Cheyenne River near Bill (06365300)

Node 18.04 Diversions on Dry Fork Cheyenne River

Node 19.02 Miscellaneous Tributaries above Gage 06365900

Node 19.04 Diversions on Misc. Tributaries above Gage 06365900

Node 20.02 Willow Creek Headwaters

Node 21.02 Woody Creek Headwaters

Node 21.04 Diversions on Woody Cr

Node 22.02 Lake Creek Headwaters

Node 23.02 Junction of Dry Fork Cheyenne River, Willow Cr, Woody Cr, & Lake Cr

Node 24.02 Antelope Creek near Teckla (06364700)

Node 25.02 Junction of Dry Fork Cheyenne River & Antelope Cr

Node 26.02 Cheyenne River near Dull Center (06365900)

Node 27.02 Miscellaneous Tributaries above Gage 06386500

Node 27.04 Diversions on Misc. Tributaries above Gage 06386500

Node 28.02 Sheep Creek Headwaters

Node 28.04 Diversions on Sheep Cr

Node 29.02 Wagonhound Creek Headwaters

Node 29.04 Diversions on Wagonhound Cr

Node 30.02 Junction of Cheyenne River, Sheep Cr, & Wagonhound Cr

Node 30.04 Diversions below Wagonhound Cr

Node 30.06 Diversions on Cheyenne River above Black Thunder Cr

Node 31.02 Black Thunder Creek near Hampshire (06376300)

Node 31.04 Diversions on Black Thunder Cr

Node 32.02 Junction of Cheyenne River & Black Thunder Cr

Node 33.02 Lodgepole Creek near Hampshire (06378300)

Node 33.04 Diversions on Lodgepole Cr

Node 34.02 Junction of Cheyenne River & Lodgepole Cr

Node 34.04 Diversions on Cheyenne River above Snyder Cr

Node 35.02 Snyder Creek Headwaters

Node 35.04 Diversions on Snyder Cr

Node 36.02 Junction of Cheyenne River & Snyder Cr

Node 37.02 Boggy Creek Headwaters

Node 37.04 Diversions on Boggy Cr

Node 38.02 Junction of Cheyenne River & Boggy Cr

Node 39.02 Sevenmile Creek Headwaters

Node 39.04 Diversions on Sevenmile Cr

Node 40.02 Lance Creek near Riverview (06386000)

Node 41.02 Mule Creek Headwaters

Node 41.04 Diversions on Mule Cr

Node 42.02 Junction of Cheyenne River, Lance Cr, Sevenmile Cr, & Mule Cr

Node 42.04 Diversions on Cheyenne River below Mule Cr

Node 43.02 Robbers' Roost Creek Headwaters

Node 43.04 Diversions on Robbers' Roost Cr

Node 44.02 Junction of Cheyenne River & Robbers' Roost Cr

Node 44.04 Diversions on Cheyenne River below Robbers' Roost Cr

Node 44.06 Return Flow above Gage 06386500

Node 45.02 Cheyenne River near Spencer (06386500)

Node 45.04 Diversions on Cheyenne River below Gage 06386500

Node 45.06 Cheyenne River at WY-SD Stateline

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). The minor tributaries were then grouped together by reach to simplify the model computations. In these instances, the grouped tributaries typically have very little irrigation and lie within close proximity to one another. For example, Miller Creek, Lone Tree Creek, Dear Creek, and Eggie Creek were grouped together as tributaries entering Keyhole Reservoir from the west (Belle Fourche River Model Reach 14).

User Notes:
The Gage Data Table presents the average historical monthly gage data for each hydrologic condition used in the model. Only the data pertaining to the hydrologic condition being modeled are included in each respective model.

Diversion Data

Diversions in the Northeast Wyoming River Basins sub-basin models are associated with agricultural use. In the absence of diversion records for the Northeast Wyoming River Basins Plan, the historical diversion records from the neighboring Powder/Tongue River Basin Plan were used as a basis for estimating Actual Diversions and Full Supply Diversion Requirements. This is discussed in detail 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 contains the information designating the node used in the model.

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:
Collection of agricultural diversion data is discussed in the Irrigation Diversion Operation and Description memorandum (HKM, 2002).
The estimated consumptive irrigation requirement (CIR), duration of irrigation, actual historic diversions and full supply diversions are the result of a great deal of analysis outside of the spreadsheet as described in the Agricultural Use memorandum (HKM, 2002).
There were no industrial and municipal diversions of surface water significant enough to be modeled.
User Notes:
The diversion data worksheets contain only input data for each node for an average dry, normal, or wet year. Note that all nodes are listed in the tables, even if no diversions occur at them. At the top of the worksheets are buttons that will take the User to the table summarizing the total monthly diversions in each reach. With the exception of these summary tables, no computations occur within these worksheets.

Import and Export Data

Engineering Notes:
The only imports or exports modeled in the Northeast Wyoming River Basins models occur in the Belle Fourche River model. Municipal wastewater effluent from Gillette, Moorcroft, Hulett, and Wright are generated from a groundwater source and thus are treated as imports to the surface water model. The effluent volumes were taken from the Municipal Use memorandum (HKM, 2002). No attempts were made to develop dry, normal and wet year municipal diversions.
User Notes:
The Imports / Exports Table summarizes the monthly imports to or exports from other basins or return flows from groundwater uses. As noted above, only the Gillette, Moorcroft, Hulette, and Wright effluent imports were modeled as such. However, the node water balance tables in the Reach/Node Worksheets are set up to incorporate imports to or exports from any node.

Options Tables

Two tables are included in the Options Tables worksheet (see Figure 9):

Irrigation Return Pattern . a percentage representing the amount of the diversion that returns to the river (inefficiencies), and
Irrigation Return Lags . a percentage representing the amount of the return flow from a given month that reaches the river in the month of the diversion and in subsequent months.

Engineering Notes:
The unused, or inefficiency portion of diversions is returned to the river over the course of one or more months either by direct surface runoff, or through the alluvial aquifer. For modeling purposes, an estimate must be made of amount, location, and timing of returns. The Options Table addresses amount and timing of return flows. The points of diversion (service area) GIS theme contains the information designating the Return Pattern and Return Lags for each model node.
The Irrigation Return Pattern table provides the monthly return fractions (inefficiencies) for every diversion in the model. One pattern is characterized by zeros in all months, which is available for future use with municipal or industrial diversions. Monthly efficiencies for irrigation diversions were developed by comparing historical diversion records (from the neighboring Powder/Tongue River Basin) to the theoretical maximum diversion requirement (based on CIR) as discussed in the Agricultural Use memorandum (HKM, 2002). The return flow fraction is defined as (1.0 . Efficiency).
Lags for irrigation diversions were patterned after similar previous projects and adjusted based on the type of irrigation system defined in the irrigated lands mapping (i.e. conventional irrigation systems as opposed to spreader dikes or intermittent diversions from ephemeral streams).
Calibration Notes:
The efficiencies and return lags initially selected were further calibrated to fit the conditions of the Northeast Wyoming River Basins sub-basins using the magnitude and monthly pattern of the Ungaged Basin Gain/Loss term as a reasonableness check.
User Notes:
The Options Tables incorporate the information used in the computation of irrigation return flow quantities and their timing. The data in the first table, "Irrigation Return Patterns," consist of the percentages of water diverted which eventually will return to the river and be made available to downstream users.
The second worksheet table, "Irrigation Return Lags", controls the timing of these returns. Flows diverted in any month can be lagged up to six months beyond the month in which they are diverted. An example pattern is:
Month		0	1	2	3	4	5	6
Percent		30	21	14	11	9	8	7
By way of example, for a diversion occurring in July, 30 percent of the Total Irrigation Returns (i.e., that portion not lost to consumptive use, evaporation, etc.) will return in July, 21 percent in August, 14 percent in September, 11 percent in October, 9 percent in November, 8 percent in December, and the remaining 7 percent will return in January.

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:

Figure 10 shows a typical irrigation return flow calculation for the Murray Ditch Diversions.
Efficiency Pattern: The value entered here is used to look up the Irrigation Return Pattern found in the Options Table.
Total Diversions: On the Estimated Actual Return Flows worksheet, these values are referenced from the Estimated Actual Diversion Data input worksheet. On the Model Simulated Return Flows worksheet, these values are referenced from the "Summary of Diversion Calculations: By Reach" table on the Diversion Summary worksheet.
Total Irrigation Returns: These data are computed by multiplying the Total Diversions by the selected Irrigation Return Pattern for the month. For example, for a month with Total Diversions of 1000 acre-feet and an irrigation return fraction of 80%, the Total Irrigation Returns from that diversion for that month will be 800 acre-feet.
Return Pattern: The value entered here is used to look up the Irrigation Return Lag found in the Options Table.
To and Percent: This feature allows the User to define the node(s) in the model where irrigation returns will return and in what percentages. Total Irrigation Returns are distributed according to the node numbers entered in the "To" box, their corresponding percentages of the Total Irrigation Returns, and the Irrigation Return Lag pattern in the Options Table. The percentages entered at each node must total either 0 or 100% or a warning message will appear.
The location of the irrigated acreage in relation to natural topographic features was used in determining return flow locations and percentages.
Irrigation Returns: Node Totals Table: This table lists all of the irrigation returns that have been directed to each Node and provides their sum.
Irrigation Returns: Reach Totals Table: This table lists all of the irrigation returns that have been directed to each Reach and provides their sum.
User Notes:
This worksheet computes the return flows from irrigation diversions. The User should modify only those cells highlighted in yellow.
Buttons at the top of the worksheet take the User directly to each of the three tables in the Irrigation Return Worksheet. "View Individual Nodes" takes the User to the first table, which calculates return flows from each node and distributes them in time and place. "View 'Node Totals' Summary Table" takes the User to the second table, the Node Totals Table. "View 'Reach Totals' Summary Table" takes the User to the Reach Totals Table.

Evaporative Losses

Two reservoirs are explicitly modeled in the Northeast Wyoming River Basins models: Keyhole Reservoir in the Belle Fourche Model and Spencer (Lak) Reservoir in the Beaver Creek Model. Other reservoirs, such as Kilpatrick Reservoir and the many stock reservoirs, 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. 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:
Monthly gross evaporation and area-capacity data for each of the modeled reservoirs was obtained from the Storage Operation and Description memorandum (HKM, 2002). Precipitation was obtained from the Wyoming Average Monthly or Annual Precipitation, 1961-1990 GIS theme (Daly and Taylor, 1998). Historical end-of-month reservoir contents, diversions, and releases were obtained from the USBR for Keyhole Reservoir. Operational data for Spencer (Lak) Reservoir was unobtainable, so the end-of-month contents and releases were calibrated to minimize the Ungaged Basin Gain/Loss term. Dry, normal and wet year end-of-month contents were determined for each reservoir for modeling the respective hydrologic conditions.
User Notes:
Monthly gross evaporation (inches) and total precipitation (inches) data are included in the table. The net evaporation in inches is then calculated within the worksheet. The end-of month surface area is calculated from the area-capacity table and used to determine the mean monthly evaporative loss in acre-feet. As with other tables in the model spreadsheet, cells that require an entry are highlighted in yellow.

Reach Gain/Loss

The Northeast Wyoming River Basins 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:
Ungaged gains and losses are computed between gages using a water budget approach, as:
{Q downstream . Q upstream } + Σ Diversions within Reach - S Return flows to Reach +/- Δ Storage
All terms are supplied from the Input Worksheets, the Computation Worksheets, or the Summary Worksheets.
Calibration Notes:
Two computational iterations are performed in establishing the ungaged gain/loss. The first iteration uses the Estimated Actual Diversion Data and Return Flows developed outside of the model to estimate ungaged gain/loss, while the second iteration uses the Model Simulated Diversions and Return Flows. The second iteration accounts for reductions in return flows resulting from diversion shortages and is necessary to achieve closure in the water balance calculations. Basin gains are equated to positive values, while basin losses are equated to negative values.
The basin gain/loss charts are used to visually verify the reasonableness of the gain/loss pattern and magnitude. Model assumptions, input data, and schematic representations of the physical system were adjusted as necessary through a trial-and-error process until the magnitude and monthly distribution of the gain/loss term appeared reasonable given the inherent limitations of the model and data deficiencies.
User Notes:
The worksheet uses positive values from iteration one as Basin Gains and negative values from iteration two as Basin Losses. Mathematical closure in the water balance calculations is accomplished through adjustments made in the second iteration. The two Basin Summary Tables (Basin Gains, Basin Losses) are viewed by selecting the "Basin Summary" button. The Basin Charts are similarly viewed by selecting the appropriate "View Basin __ Chart" button.

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.

Engineering Notes:
This is the heart of the spreadsheet model where water budget calculations are performed for each node represented in the model. Water balance is maintained in each river reach through the use of the Ungaged Basin Gain/Loss term.
User Notes:
The Node Tables compute the flow available to downstream users (NET flow) using a water budget approach.
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.

Belle Fourche River Basin Model

Reach 9

Node 57.04 . Diversions on Donkey Creek: Imports from Gillette, Wright, and Moorcroft wastewater effluent enter the model here.

Reaches 18

Node 66.04 . Diversions on Misc. Tributaries above Gage 06428200: Imports from Hulett wastewater effluent enter the model here.

Redwater Creek Basin Model

Reach 5

Nodes 5.04 & 5.06. Murray Ditch Diversions: This ditch diverts for irrigated lands lying within Wyoming and South Dakota. The diversion amount for Node 5.04 uses the Estimated Actual Diversion and the Full Supply Diversion based on the acreage of land mapped in the irrigated lands GIS theme. The diversion amount for Node 5.06 is the remaining portion of the historical diversion data reported by the USGS.

Beaver Creek Basin Model

Reach 10

Node 15.04 . Spencer (Lak) Reservoir: Data for the operation of this reservoir was unobtainable so the model uses the best estimate of end-of-month storage and releases.

Reach 14

This reach is not included in the Ungaged Basin Gain/Loss calculations as it is not bounded by downstream gages.

Cheyenne River Model

Reach 28

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.

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 Northeast Wyoming River Basins Models

The Northeast Wyoming River Basins 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.

As a general rule, whenever rows need to be added to any table (e.g. adding a node within an existing Reach), the Programmer should create a template for the new information using existing cells/tables. Existing worksheet rows containing the type of information to be inserted should be copied and "inserted" where needed instead of adding them to the bottom of the table. The Northeast Wyoming River Basins sub-basin models use "lookup" functions extensively. By "inserting" rows, Excel will automatically modify the formulas in cells referencing a table. If rows are added to a table without insertion, the lookup functions will not "find" the new information.
The workbooks have been provided with the "protection mode" enabled for each worksheet. No password has been used, therefore, the Programmer may turn the protection feature off if changes are required (Tools - Protection - Unprotect Worksheet).
When entering data such as historic diversions, historic USGS gage data, etc., the Programmer should use a "paste special" command to protect the existing format and formulas by inserting only data values (Edit - Paste Special - Values).
When copying existing formulas or tables, the Programmer should copy and paste entire rows of existing tables (or entire tables where appropriate), in lieu of copying a single cell formula and using it to fill a table. The reason for this is that many of the "lookup" formulas may not copy to adjacent columns correctly.
Whenever copying and pasting anything in the workbook, the Programmer should ensure that Excel has been set to copy in the "relative address" mode.

Additional detailed information has been provided to the Programmer in this document with the discussion of each worksheet.

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 Northeast Wyoming River Basins 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 Northeast Wyoming River Basins sub-basin models will require the following minor modifications to the GUI:

The names of the Northeast Wyoming River Basins files must be replaced with future file names in the programming code associated with each of the three model selection buttons.
Text in the forms presented in the GUI must be modified to reflect the future version.

Navigation Worksheets

Excel programmers modifying the spreadsheet model will need to modify the Reach/Node Description table located to the right of the visible screen (see 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()
If Range("S18") = 1 Then
Application.Goto Reference:="Reach1"
ElseIf Range("S18") = 2 Then
Application.Goto Reference:="Reach2"
ElseIf Range("S18") = 3 Then
.
.
.
ElseIf Range("S18") = 30 Then
Application.Goto Reference:="Reach30"
End If
End Sub

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 four Northeast Wyoming River Basins 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 Northeast Wyoming River Basins 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 Northeast Wyoming River Basins 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.

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. 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 Northeast Wyoming River Basins 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:

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 Belle Fourche River Model Node 51.02 for an example of this method.
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 Belle Fourche River Model Node 53.02 for an example of this method.
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 Belle Fourche River Model Node 53.04 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 53.04), 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 Northeast Wyoming River Basins Model

The Northeast Wyoming River Basins 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:

Enter the node number and name immediately below the last node in the table and above the line labeled "Insert new nodes above".
INSERT a row at the location where you want to add a new node, then type in the node number and name.

It is recommended that the user use the second approach so that the list remains in numerical sequence.

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

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 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.
If the node is located at the upstream end of any other reach, the Node Inflow references the NET Flow from the Reach(s) that feed it. In this case, cell references must be manually modified.
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.

Most nodes will be built using the third method described above.

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, Northeast Wyoming River Basins Plan. Billings, Montana

HKM Engineering Inc., 2002. Municipal Use, Technical Memorandum, Northeast Wyoming River Basins Plan. Billings, Montana

HKM Engineering Inc., 2002. Irrigation Diversion Operation and Description, Technical Memorandum, Northeast Wyoming River Basins Plan. Billings, Montana

HKM Engineering Inc., 2002. Storage Operation and Description, Technical Memorandum, Northeast Wyoming River Basins Plan. Billings, Montana

HKM Engineering Inc., 2002. Surface Water Hydrology, Technical Memorandum, Northeast Wyoming River Basins 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
CALIBRATION SUMMARY

BELLE FOURCHE RIVER MODEL

Belle Fourche River Model:
Wet Year Condition