AGWRAP – Water Needs Assessment Tool

Introduction

This spreadsheet- based tool is designed to aid in the determination of water needs (demands) of irrigated cropland and livestock consumption. It also predicts monthly runoff and by cumulating supply (runoff) and demand estimates the required capacity of a farm pond to supply the estimated demands.

The tool uses Excel and Visual Basic for Applications (VBA) for user input forms, output screens, and to access help files.


Main Page

The main page is used to help the user navigate to the desired input screens and to view output. Buttons on the main page that are light green are those that require user input (“Input Location, Crop, and Irrigation System Information”, “Input Watershed Information”, and “Input Livestock Information”. Clicking on these buttons opens the respective input form. Model output can be seen by clicking on the light blue buttons (“Summary Output”, “Plots”, “Pond Operation”, and “Irrigation Requirements Only”). Help documentation (like this page) is activated by clicking the orange “Help” button.

The three cells with white background at the upper middle of the page are meant for input of project name, location, and last saved date, from top to bottom, respectively. If no information for project name and location has been previously entered, there will be a prompt in the cell "Enter Project Name Here", and "Enter Project Location Here" in the upper two cells, and the current date will be displayed. The entered project name and location will be subsequently displayed in the user input forms. The Project Name and Project location should be entered from the main page - if nothing has been entered for those on the main page, the prompt to enter those will be displayed subsequently in the user input forms, but will not be saved from those forms.

Clear Data

This button will clear all data associated with cropland, watershed, and livestock, and project information (project name and location). It is not necessary to clear data prior to the evaluation, however if not cleared, previous information may be retained if data has been saved from any of the 3 user input forms (see below) in a previous session.

Input Location, Crop and Irrigation System Information

Click this button to move to the data entry form for these items. From this form, location crop and irrigation system data can be entered and saved, and the user form may be printed.

Input Watershed Information

Click this button to navigate to the data entry form to enter watershed information such as soils, cover, and area. The user will select between a “simple method” and a curve number approach to estimating watershed runoff. From this form, watershed data can be entered and saved, and the user form may be printed.

Input Livestock Information

Click this button to move to the workbook page to enter livestock type and head numbers. This information will be used to estimate monthly livestock water demand. From this form, livestock data can be entered and saved, and the user form may be printed.

Summary Output

Click this button to view a table of monthly output. Information includes crop and livestock water demand, cumulative demand and supply (runoff) and required pond capacity.

Plots

Monthly rainfall, runoff and cumulative supply and demand curves will be shown when this button is clicked.

Pond Operation

Click this button after reviewing the Summary Output page to operate the pond system. This allows the user to check the “best estimate” of required pond storage in the summary output, by actually “operating” the planned pond. The user can change the pond capacity and starting (initial) volume to see the effect on water supply adequacy. The user should only input information in blue colored cells above the table body. Blue cells in the body of the table contain calculations and should not be changed. Clicking on the cell below “Operation Mode” allows the user to select between two modes.

Irrigation Requirements Only

Click this button to view monthly reference ET for the selected site; and monthly crop coefficients, effective precipitation, net irrigation requirements, and gross irrigation requirements for the crops selected.

Help

Clicking on this button accesses the help files (such as this).


Livestock Information

If livestock water requirements are to be considered, the assumed source is the farm pond and grazing animals – but the requirements should be valid for any water source. Enter livestock type and head data on this input form.

Up to 4 livestock types and associated numbers (head) can be entered. The user should uncheck types that will not be used for cases of fewer than 4 livestock types. Note that when a check box is checked, the list boxes below are visible and enabled; if the box is unchecked, the values in the lists below are “greyed out” and nothing can be entered in the associated livestock information boxes.

Save

Clicking on the “Save” button will save the entered data. This data will appear later if the form is loaded again from the main page during the current session, or will be maintained for later sessions if the spreadsheet is saved. If the “Save” button is inactivated, it is either because no data has yet been entered, or there are no new or changed data from the previously saved version.

Print Form

Clicking on the “Print Form” button sends a printout of the data entry form to the currently selected printer.

Exit

Clicking on the “Exit” button closes the data entry form, and returns control back to the main page.

Home

Clicking on the “Home” button returns control to the main page.

Help

Help files (such as this one) can be accessed by clicking on the help button from this or any input form or output page.


Location, Crop and Irrigation System Input Screen

County

Enter the county for which you will be doing the assessment. This will be used to access the appropriate weather station for rainfall and temperature.

Crop Information

The user can specify up to 3 crops that would be grown in a 12-month period. Click the check boxes above crop1, crop2 or crop3 to enter data or to omit data (uncheck) if fewer than 3 crops are to be evaluated. Note that when a check box is checked, the list boxes below are visible and enabled; if the box is unchecked, the values in the lists below are “greyed out” and nothing can be entered in the associated crop and irrigation system information boxes. Select the crop from the list box using the scroll bar if necessary. For any crops that are selected, the associated acreage is entered in the box below the crop selection box and a planting date is chosen using the list box below the acreage data. Click on the desired entry to highlight and select it.

A double crop option if given for the first two crops. If double-cropping is planned, click the far left double crop check box and enter the acreage to be double-cropped. The double-crop check box and acreage for crop 2 will automatically be checked and filled with the same acreage as crop 1.

Irrigation System Information
For each crop selected, choose the associated irrigation system. Selection of a particular irrigation system will determine the irrigation efficiency for all types except “custom”. If “custom” is selected, the user can enter the desired irrigation efficiency.

Save

Clicking on the “Save” button will save the entered data. This data will appear later if the form is loaded again from the main page during the current session, or will be maintained for later sessions if the spreadsheet is saved. If the “Save” button is inactivated, it is either because no data has yet been entered, or there are no new or changed data from the previously saved version.

Print Form

Clicking on the “Print Form” button sends a printout of the data entry form to the currently selected printer.

Exit

Clicking on the “Exit” button closes the data entry form, and returns control back to the main page.

Home

Clicking on the “Home” button returns control to the main page.

Help

Help files (such as this one) can be accessed by clicking on the help button from this or any input form or output page.


Watershed input

This input form is used to enter the information required to estimate monthly runoff.

Estimation methodology

Select either the “use simple method” or “use cover type” to select the method used in the NRCS document “Ponds-Planning, Design and Construction” or the NRCS curve number method to estimate runoff into a proposed farm pond.

Simple-Method

The method used a “acres required to produce one acre-foot annually” map found in the NRCS Pond Planning publication. Runoff is distributed monthly using the proportion of monthly to annual runoff from the rainfall station associated with the respective county. The user must enter the total number of acres for the watershed that would drain to the proposed pond. This method tends to be the more conservative method (predict less runoff than the curve number method) for soils in hydrologic groups C and D and for good land cover. If watershed cover and soils are unknown, this is the preferred method.

Cover-Type

If land cover and associated acreage are known, the SCS curve number method can be used. This method computes excess precipitation (runoff) from monthly precipitation, the average number of rainfall events of 0.5 in. or more, and cover type. The user can select up to three different watershed cover types and associated acreage for each cover type. If selecting fewer than three cover types, be sure to “uncheck” the other entries. The user must also select the predominant soil in the watershed. This will determine the hydrologic soil group used to estimate the curve number for the cover types.

For agricultural practices, the following key applies for interpretation of the input form:

CR = crop residue

SR = straight row

C = Contoured

T = Terraced

Save

Clicking on the “Save” button will save the entered data. This data will appear later if the form is loaded again from the main page during the current session, or will be maintained for later sessions if the spreadsheet is saved. If the “Save” button is inactivated, it is either because no data has yet been entered, or there are no new or changed data from the previously saved version.

Print Form

Clicking on the “Print Form” button sends a printout of the data entry form to the currently selected printer.

Exit

Clicking on the “Exit” button closes the data entry form, and returns control back to the main page.

Home

Clicking on the “Home” button returns control to the main page.

Help

Help files (such as this one) can be accessed by clicking on the help button from this or any input form or output page.


Summary Output

This page provides a monthly output table of water demands and water supply. The “deficit” column is greater than 0 when demand exceeds the supply for the month. The font will turn to red to alert the user of the condition. If annual supply exceeds annual demand, pond storage can satisfy the months with deficits. The second-last and last columns of the green shaded table list cumulative supply (runoff) and total demand (irrigation and livestock). The first column just outside the green-shaded portion of the table shows the difference (cumulative runoff – cumulative demand) by month. Determination of required pond storage considers multiple conditions. First, the largest deficit (negative value) in the first column outside the shaded table is considered. If no negative value of cumulative runoff - cumulative demand exists, then the summation of monthly deficits is considered. If no month has a deficit (negative value of supply - demand), the required storage is then selected as the maximum monthly total demand in the spreadsheet. This reflects the condition in which monthly runoff always exceeds demand, but there must be storage provided to satisfy the demand. If the total annual runoff is less than the total annual demand, then no amount of pond capacity will be able to fully supply water needs in a normal year.

The required pond storage is shown beneath the table along with targeted error messages.


Pond Operation

This page is used to operate a pond, usually starting with a pond of about the same capacity recommended at the bottom of the summary output table. The user can change pond capacity and beginning pond storage (blue cells above the table) to see the effect on water supply adequacy. The blue areas of the summary output table are output cells that depend on the blue input cells, i.e., pond capacity and beginning storage, and the selected operation mode.

Operation Mode

Click on the cell below the label “operation mode” to select the mode which determines the order of operation in the pond operation model. Two options are available; “inflow first” or “demand first”. Selecting “inflow first” means that monthly inflow will be placed in the pond first, subject to pond capacity. This is the more conservative of operation order as it potentially leaves less water for water demand and results in more “spill”. Selecting “demand first” assumes that all demand will be satisfied first from the beginning month pond volume allowing “space” for inflow.

The monthly “spill” and “deficit” columns are subject to the operation mode.

The “notes” below the table provide guidance to meet total water demand by either reducing acreage or by increasing storage, based upon runoff, and operational spills and deficits.