Wyoming State Water Plan, Wyoming Water Development Office
Rafting on Snake River Lake Marie, Snowy Mountains Wyoming Wind River Range picture

Powder/Tongue River Basin 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 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:

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
Little Bighorn Notes
Tongue River Notes
Clear Creek Notes
Crazy Woman Creek Notes
Powder River Notes
Little Powder River Notes
The Results Worksheets
Outflows
Diversions
Programmers' Notes
Modification of the Powder/Tongue River Basin 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 Powder/Tongue River Basin 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 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.


click to enlarge


click to enlarge


click to enlarge


click to enlarge


Click to Enlarge


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 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:

In the following sections, each component of the Powder/Tongue River 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 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:

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

User Notes:

Upon opening the selected Powder/Tongue River 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 8 displays the Central Navigation Worksheet from the Clear Creek 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 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 6). 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 9) allows selection of any of the following output tabulations:

Table 1
Little Bighorn River Model Nodes

Node No.Node Name
Node 89.02East Pass Creek Near Parkman (06289800)
Node 89.04Jones Ditch Diversions
Node 89.06Diversions d/s of Jones Ditch
Node 89.08Return Flow from East Pass Creek Diversions
Node 90.02East Pass Creek Near Dayton (06289820)
Node 90.04East Pass Creek at Wyoming-Montana Stateline
Node 92.02Elkhorn Creek Above Fuller Ranch Ditch Near Parkman (06288975)
Node 92.04Fuller Ranch Ditch Diversions
Node 92.06Elkhorn Creek at Wyoming-Montana Stateline
Node 93.02Red Canyon Creek Near Parkman (06289100)
Node 93.04Diversions d/s of gage 06289100
Node 93.06Red Canyon Creek at Wyoming-Montana Stateline

Table 2
Tongue River Model Nodes

Node No.Node Name
Node 3.04Mead Coffeen Ditch Diversions (Transbasin)
Node 3.08Piney & Cruse Ditch Diversions (Transbasin)
Node 3.14Prairie Dog Ditch Diversions (Transbasin)
Node 65.02Prairie Dog Creek Headwaters
Node 65.04Diversions u/s of Ninemile Ditch
Node 65.06Ninemile Ditch Diversions
Node 65.08Prairie Dog #11 & #12 Ditch Diversions
Node 65.10Diversions d/s of Dutch Creek
Node 65.12Return Flow on Prairie Dog Creek
Node 66.02Prairie Dog Creek Near Acme (06306250)
Node 67.02Little Goose Creek in Canyon Near Big Horn (06303500)
Node 67.04Peralta Ditch Diversions
Node 67.06Last Chance Ditch Diversions
Node 67.08Red Hill Ditch Diversions
Node 67.10Diversions d/s of Red Hill Ditch
Node 67.12Colorado Colony Ditch Diversions
Node 67.14Diversions d/s of Colorado Colony Ditch
Node 67.16East Side Ditch Diversions
Node 67.18Gerdel Ditch Diversions
Node 67.20Burn Cleuch Ditch Diversions
Node 67.22Diversions d/s of Burn Cleuch Ditch
Node 68.02Beaver Creek Headwaters
Node 68.04Diversions on Beaver Creek
Node 69.02Rapid Creek Headwaters
Node 69.04Diversions u/s of Big Goose & Beaver #2 Ditch
Node 69.06Big Goose & Beaver #2 Ditch Diversions
Node 70.02Big Goose Creek Near Sheridan (06302000)
Node 70.04Sheridan City Intake Diversions
Node 70.06PK Ditch Diversions
Node 70.08Alliance Ditch Diversions
Node 70.10Diversions d/s of Alliance Ditch
Node 71.02Junction of Big Goose Creek & Rapid Creek
Node 71.04No. 9 Ditch Diversions
Node 71.06Diversions d/s of No. 9 Ditch
Node 73.02Junction of Big Goose Creek & Beaver Creek
Node 73.04Diversions d/s of Big Goose Cr & Beaver Cr Junction
Node 74.02Junction of Big Goose Creek & Little Goose Creek
Node 75.02Soldier Creek Headwaters
Node 75.04Diversions u/s of Soldier Creek Ditch
Node 75.06Soldier Creek Ditch Diversions
Node 75.08Return Flow on Soldier Creek
Node 76.02Goose Creek Below Sheridan (06305500)
Node 76.04Grinnell Ditch Diversions on Goose Creek
Node 76.06Diversions d/s of Grinnell Ditch on Goose Creek
Node 76.08Return Flow on Goose Creek
Node 77.02Goose Creek Near Acme (06305700)
Node 78.02Wolf Creek at Wolf (06299500)
Node 78.04Grinnell Ditch Diversions on Wolf Cr to irrigated lands
Node 78.05Grinnell Ditch Diversions on Wolf Cr to Soldier Creek
Node 78.06Old Reliable Ditch Diversions
Node 78.08Decker Ditch Diversions
Node 78.10Garrard Ditch Diversions
Node 78.12Dye Shield Ditch Diversions
Node 78.14Diversions d/s of Dye Shield Ditch
Node 78.16West Wolf Ditch Diversions
Node 78.18Diversions d/s of West Wolf Ditch
Node 79.02Little Tongue River Near Dayton (06298500)
Node 79.04Diversions d/s of gage 06298500
Node 80.02Tongue River Near Dayton (06298000)
Node 80.04Highline Ditch Diversions (06297500)
Node 80.06South Side Ditch Diversions
Node 80.08Tongue River #1 Ditch Diversions
Node 81.02Junction of Tongue River & Little Tongue River
Node 81.04OZ & K, Hanover & Mikado Ditch Diversions
Node 81.06York Ditch Diversions
Node 82.02Junction of Tongue River & Wolf Creek
Node 82.04Tongue River Ditch Diversions
Node 82.06Diversions d/s of Tongue River Ditch
Node 83.02Junction of Tongue River & Goose Creek
Node 83.04Diversions u/s of Interstate (Pennoyer) Ditch
Node 83.06Interstate (Pennoyer) Ditch Diversions
Node 83.08Diversions d/s of Interstate (Pennoyer) Ditch
Node 84.02Junction of Tongue River & Prairie Dog Creek
Node 84.04Tongue River at State Line Near Decker (06306300)

Table 3
Clear Creek Model Nodes

Node No.Node Name
Node 1.02North Piney Creek Near Story (06321500)
Node 2.02South Piney Creek Near Story (06321000)
Node 3.02Junction of North Piney Creek & South Piney Creek
Node 3.04Mead Coffeen Ditch Diversions (Transbasin)
Node 3.06Diversions d/s of Mead Coffeen Ditch
Node 3.08Piney & Cruse Ditch Diversions (Transbasin)
Node 3.10Piney Divide Ditch Diversions to Little Piney Creek
Node 3.12Piney Divide Ditch Diversions to irrigated lands
Node 3.14Prairie Dog Ditch Diversions (Transbasin)
Node 3.16Diversions d/s of Prairie Dog Ditch
Node 3.18Return Flow u/s of gage 06323000
Node 4.02Piney Creek at Kearney (06323000)
Node 4.04Leiter Ditch Diversions
Node 4.06High Line Ditch Diversions
Node 4.08Lake DeSmet Intake Tunnel Diversions
Node 4.10Return Flow d/s of Lake DeSmet Intake Tunnel
Node 5.02Piney Creek Below Lake DeSmet Tunnel Intake Near Story (SEO gage)
Node 6.02Little Piney Creek Headwaters
Node 6.04Diversions u/s of Little Piney Divide Ditch
Node 6.06Little Piney Divide Ditch Diversions
Node 6.08Diversions d/s of Little Piney Divide Ditch
Node 6.10Return Flow on Little Piney Creek
Node 7.02Little Piney Creek (SEO gage)
Node 7.04Little Piney Diversions to Piney Creek
Node 8.02Junction of Piney Creek & Little Piney Creek
Node 8.04Upper Flying E, Lower Flying E & Senff Ditch Diversions
Node 8.06Maverick, Sturdevent & WJD Ditch Diversions
Node 8.08Anthorpe Rogers Ditch Diversions
Node 8.10Dunlap Ditch Diversions
Node 9.02Shell Creek Headwaters
Node 9.04Diversions on Shell Creek
Node 9.06Lake DeSmet Reservoir
Node 11.02Junction of Piney Creek & Box Elder Creek
Node 11.04Pratt & Ferris #1 Ditch Diversions
Node 11.06Return Flows from Dunlap Ditch
Node 12.02Piney Creek at Ucross (06323500)
Node 13.02Rock Creek Near Buffalo (06320000)
Node 13.04Mowry Basin Ditch Diversions
Node 13.06Diversions d/s of Mowry Basin Ditch
Node 13.08Hallie Ditch Diversions
Node 13.10Diversions d/s of Hallie Ditch
Node 13.12Lake DeSmet (M&M) Ditch Diversions to irrigated lands
Node 13.14Lake DeSmet (M&M) Ditch Diversions to Lake DeSmet
Node 14.02Johnson Creek Headwaters
Node 14.04Penrose Johnson Ditch Diversions
Node 14.06Diversions u/s Penrose Johnson Ditch Return Flows
Node 14.08Diversions d/s Penrose Johnson Ditch Return Flows
Node 15.02Junction of Rock Creek & Johnson Creek
Node 15.04Diversions d/s of Sand Creek
Node 15.06Prince Albert Ditch Diversions
Node 15.08Return Flows from Prince Albert Ditch
Node 16.02Rock Creek at Mouth Near Buffalo (SEO gage)
Node 18.02French Creek Headwaters
Node 18.04Penrose Ditch Diversions to Johnson Creek
Node 18.06Diversions d/s of Penrose Ditch
Node 18.08Hopkins Ditch Diversions
Node 18.10Diversions d/s of Hopkins Ditch
Node 19.02Clear Creek Near Buffalo (06318500)
Node 19.04Diversions d/s of gage 06318500
Node 19.06Buffalo City Municipal Diversions
Node 19.07Diversions u/s of Snider Ditch
Node 19.08Snider Ditch Diversions
Node 19.10Johnson Holt Ditch Diversions
Node 19.12Six Mile Ditch Diversions
Node 19.14Crown Ditch Diversions
Node 19.16Diversions d/s of Crown Ditch
Node 19.18Clear Creek Land & Co Ditch Diversions
Node 19.20Clear Creek at Buffalo
Node 20.02Clear Creek in Buffalo City Park (SEO gage)
Node 21.02Junction of Clear Creek & French Creek
Node 21.04Diversions d/s of Clear Cr & French Cr Junction
Node 22.02Junction of Clear Creek & Rock Creek
Node 22.04Return Flow d/s of Clear Cr & Rock Cr Junction
Node 23.02Clear Creek Below Rock Creek Near Buffalo (06320200)
Node 23.04Redman Ditch Diversions
Node 23.06Diversions d/s of Redman Ditch
Node 23.08Hillyer & Onslow Ditch Diversions
Node 23.10Diversions to Healy Reservoir
Node 23.12Clear Creek d/s of Hillyer & Onslow Ditch
Node 24.02Healy Reservoir
Node 25.02Clear Creek Below Healy Reservoir Near Buffalo (SEO gage)
Node 25.04Frank G Hopkins Ditch Diversions
Node 25.06Des Moines Ditch Diversions
Node 25.08Diversions d/s Des Moines Ditch Diversions
Node 25.10Big Bonanza Ditch Diversions
Node 26.02Junction of Clear Creek & Piney Creek
Node 26.04Diversions u/s of Roberts Ditch
Node 26.06Roberts Ditch Diversions
Node 26.08Diversions d/s of Roberts Ditch
Node 26.10Return Flows u/s of Double Crossing gage
Node 27.02Clear Creek at Double Crossing Near Clearmont (SEO gage)
Node 27.04Diversions u/s of Pratt & Ferris #2 Ditch
Node 27.06Pratt & Ferris #2 Ditch Diversions
Node 27.08Diversions d/s of Pratt & Ferris #2 Ditch
Node 27.10Pratt & Ferris #3 Ditch Diversions
Node 27.12Clear Creek above SEO Gage
Node 28.02Clear Creek Below P&F#3 Ditch Near Clearmont (SEO gage)
Node 28.04Diversions d/s of Pratt & Ferris #3 Ditch
Node 28.06Diversions u/s of Kendrick Ditch
Node 28.08Kendrick Ditch Diversions
Node 28.10Return Flows from Kendrick Ditch
Node 29.02Clear Creek Near Arvada (06324000)
Node 62.02Powder River at Arvada (06317000)
Node 63.02Junction of Powder River & Clear Creek
Node 63.04Diversions d/s of Powder River & Clear Creek Junction
Node 63.06Return Flow from Diversions d/s of Powder R & Clear Cr Junction
Node 63.08Powder River at Moorhead (06324500)

Table 4
Crazy Woman Creek Model Nodes

Node No.Node Name
Node 30.02Kelly Creek Headwaters
Node 31.02Little North Fork Crazy Woman Creek Headwaters
Node 31.04Diversions on Little North Fk Crazy Woman Cr
Node 32.02North Fork Crazy Woman Creek Below Spring Draw Near Buffalo (06314500)
Node 32.04Thompson & Matthews Ditch Diversions to irrigated lands
Node 33.02Junction of North Fk Crazy Woman Cr, Little North Fk Crazy Woman Cr, & Kelly Cr
Node 33.04Cook Ditch Diversions
Node 33.06Diversions d/s of Cook Ditch
Node 33.08North Fork Ditch Diversions
Node 33.10Kennedy Ditch Diversions
Node 33.12Diversions d/s of Kennedy Ditch
Node 33.14Diversions u/s of North Fk Crazy Woman Cr, Muddy Cr, & Billy Cr Junction
Node 34.02Muddy Creek Headwaters
Node 34.06Diversions u/s of Return Flows from Thompson & Matthews Ditch
Node 34.08Diversions d/s of Return Flows from Thompson & Matthews Ditch
Node 34.10Thompson Brothers Ditch Diversions
Node 34.12PX Ditch Diversions
Node 35.02Billy Creek Headwaters
Node 35.06Diversions in O'Malley Draw
Node 35.08Diversions at Mouth of Billy Creek
Node 36.02Junction of North Fork Crazy Woman Creek, Muddy Creek, & Billy Creek
Node 37.02Middle Fork Crazy Woman Creek Near Greub (06315500)
Node 37.04Diversions u/s of Moreton Ditch
Node 37.06Moreton Ditch Diversions
Node 37.08Teddy Miller Ditch Diversions
Node 37.10Diversions d/s of Teddy Miller Ditch
Node 37.12Devoe #1 Ditch Diversions
Node 38.02Junction of North Fork & Middle Fork Crazy Woman Creek
Node 38.04Mitchell & Long Ditch Diversions to irrigated lands
Node 38.10John R Smith Ditch Diversions
Node 40.02Junction of Crazy Woman Creek & South Fk Crazy Woman Creek
Node 40.04Return Flows u/s of Trabing Bridge
Node 41.02Crazy Woman Creek at Trabing Bridge Near Arvada (SEO gage)
Node 41.04Diversions d/s of Trabing Bridge
Node 41.06Return Flows u/s of Upper Station
Node 42.02Crazy Woman Creek at Upper Station Near Arvada (06316400)

Table 5
Powder River Model Nodes

Node No.Node Name
Node 42.02Crazy Woman Creek at Upper Station Near Arvada (06316400)
Node 42.04Diversions d/s of gage 06316400
Node 43.02Middle Fork Powder River Near Barnum (06309200)
Node 44.02North Fork Powder River Below Pass Cr Near Mayoworth (06311400)
Node 44.04Diversions d/s of gage 06311400
Node 44.06Morgareidge & Frances Ditch Diversions
Node 44.08Diversions d/s of Morgareidge & Frances Ditch
Node 44.10Strickler Rinker, Dry Bob Brock & Roseberry Ditch Diversions
Node 44.12Jim Blaine, Judd Ritter & Broughton Ditch Diversions
Node 44.14Affalter & other Ditch Diversions
Node 45.02Red Fork Powder River Headwaters
Node 45.04Red Wall Ditch Diversions
Node 45.06Diversions d/s of Red Wall Ditch
Node 45.08Comstock Ditch Diversions
Node 45.10Big Four Ditch Diversions
Node 46.02Beaver Creek Above White Panther Ditch Near Barnum (06309460)
Node 46.04Diversions d/s of gage 06309460
Node 48.02Junction of Beaver Creek & Blue Creek
Node 48.04Barnum Freeman Ditch Diversions
Node 49.02Buffalo Creek Near Arminto (06309260 & 06309270)
Node 49.04Diversions on Buffalo Creek
Node 50.02Junction of Middle Fk Powder River & Buffalo Creek
Node 50.04Diversions d/s of Middle Fk Powder River & Buffalo Cr Junction
Node 51.02Junction of Middle Fk Powder River & Beaver Creek
Node 51.04A. M. Smith Ditch Diversions
Node 51.06Middle Fork Powder River above gage 06309500
Node 52.02Middle Fork Powder River Above Kaycee (06309500)
Node 53.02Junction of Red Fk Powder River & Middle Fk Powder River
Node 53.04Diversions d/s of Red Fk & Middle Fk Powder River Junction
Node 53.06Diversions d/s of Big Four Ditch Return Flow
Node 53.08Vruwink Ditch Diversions
Node 53.10Diversions d/s of Vruwink Ditch Diversions
Node 53.12Diversions d/s of Vruwink Ditch Return Flow
Node 54.02Junction of North Fk Powder River & Middle Fk Powder River
Node 54.04Saraha Ditch Diversions
Node 54.06Powder River above gage 06312500
Node 55.02Powder River Near Kaycee (06312500)
Node 58.02South Fork Powder River Near Kaycee (06313000)
Node 59.02Junction of Powder River & South Fk Powder River
Node 59.04Return Flow from Sahara Ditch
Node 60.02Powder River at Sussex (06313500)
Node 60.04Diversions d/s of gage 06313500
Node 61.02Junction of Powder River & Crazy Woman Creek
Node 61.04Return Flow from Crazy Woman Cr Diversion
Node 62.02Powder River at Arvada (06317000)
Node 97.02Salt Creek Near Sussex (06313400)
Node 98.02Junction of Powder River & Salt Creek

Table 6
Little Powder River Model Nodes

Node No.Node Name
Node 94.02Little Powder River Below Corral Cr Near Weston (06324890)
Node 94.04Diversions d/s of gage 06324890
Node 94.06Return Flow from Diversions d/s of gage 06324890
Node 95.02Little Powder River Near Weston (06324925)
Node 95.04Diversions d/s of gage 06324925
Node 95.06Diversions d/s of Wildcat Creek
Node 95.08Return Flow from Diversions d/s of Wildcat Creek
Node 96.02Little Powder River Above Dry Cr Near Weston (06324970)
Node 96.04Diversions d/s of gage 06324970
Node 96.06Little Powder River at Wyoming-Montana Stateline

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:

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:

User Notes:

This worksheet presents a master list of all nodes included in the Powder/Tongue River 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 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:

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 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:

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

Municipal diversions for Buffalo and Sheridan were taken from the Municipal Use memorandum (HKM, 2002). Values reported in this memorandum represent the consumptive use portion as well as the entire historical diversion amount of the municipal diversions. No attempts were made to develop dry, normal and wet year municipal diversions. There were no industrial uses 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 Powder/Tongue River basin models occur in the Tongue River and Clear Creek models. Historical records were obtained as described in the Irrigation Diversion Operation and Description memorandum (HKM, 2002). Monthly exports were averaged for the Dry, Normal and Wet years of the study period, as indicated in the Surface Water Hydrology memorandum (HKM, 2002).

The Tongue River model includes the Mead Coffeen, Piney & Cruse, and Prairie Dog Ditch diversions from North and South Piney Creeks. These diversions, while imports, are handled as regular diversions so that estimated return flows can be modeled. The Big Goose & Beaver No. 1 Ditch diversion import to Rapid Creek on Big Goose Creek is strictly modeled as an import.

The Clear Creek model includes the Four Lakes & French Creek Ditch diversion imports to French Creek. The exports to the Tongue River model from North and South Piney Creek (the Mead Coffeen, Piney & Cruse, and Prairie Dog Ditches) are handled as regular diversions without flow returning to the Clear Creek model.

User Notes:

The Imports / Exports Table summarizes the monthly imports to or exports from other basins. As noted above, only the Big Goose & Beaver No. 1 and the Four Lake & French Creek imports were explicitly 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 11):

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 applicable to all intra-basin diversion nodes (e.g., Lake DeSmet Tunnel Intake diversions from Piney Creek). Monthly efficiencies for irrigation diversions were developed by comparing historical diversion records 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). Irrigation Return Lags for municipal nodes were set to 100 percent during the month of diversion.

Calibration Notes:

The efficiencies and return lags initially selected were further calibrated to fit the conditions of the Powder/Tongue River 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 12 shows a typical irrigation return flow calculation for the Pratt & Ferris #1 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 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:

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 State Engineer's Office for Lake DeSmet and Healy Reservoir. 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 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:

Ungaged gains and losses are computed between gages using a water budget approach, as:

{Q downstream . Q upstream } + ∑ Diversions within Reach - ∑ 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 (Figure 14).

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.

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.

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 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:

  1. The names of the Powder/Tongue River files must be replaced with future file names in the programming code associated with each of the three model selection buttons.

  2. 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 (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 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:

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

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:

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, 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
Calibration Summary

Click here to view Appendix A (3.13 MB)