5 Spreadsheet Models for Assessing Run-of-River Reliability
5.1 Introduction
Spreadsheet models for assessing the reliability of supply from tributaries to run-of-river users have been developed for a number of gauged tributaries. These models, which are based on the full record of historical daily flows, may be used to evaluate various environmental flow-sharing regimes and allocation quantities and patterns. There is a model for each of the following sites (in alphabetical order):
- Ahuriri River at South Diadem
- Awamoko Stream at Georgetown
- Forks River at Balmoral
- Hakataramea River at Above Main Highway Bridge (Environment Canterbury's naturalised flow record used)
- Irishman Creek at Windy Ridge
- Mary Burn at Mt MacDonald
- Maerewhenua River at Kellys Gully
- Omarama Stream at Above Tara Hills (Environment Canterbury's naturalised flow record used)
- Otekaieke River at Stockbridge/Gorge/Weir
- Otematata River at Pumphouse
- Twizel River at Lake Poaka.
In addition, a similar spreadsheet model for the lower Waitaki River based on the historical daily flows recorded at Kurow (Site No. 71104) from mid-1979 has also been developed to assess river flows and resource availability to potential users within the main stem of the river under various environmental flow-sharing regimes.
Sample screen views of a typical spreadsheet model are shown on Figure 5.1 overleaf.
Figure 5.1: Sample screen views of spreadsheet model
5.2 Spreadsheet user's guide
Below is a brief user's guide to the spreadsheet models:
The variables to be defined by the user are:
- Residual Flow Regime - there are two three-stage flow-sharing regimes (Pattern 1 or Pattern 2 residual) allowed for in the spreadsheet, and either one can apply each month of the year
- Scaling factor for recorded flows - a simple scaling factor has been included for modifying tributary inflows. It is possible to programme in a full correlation relationship for a virtual site, either upstream or downstream of the actual recorder location.
- Demand or Take - three types of take are permissible, i.e. a constant take and two user-defined monthly take patterns.
How to use the spreadsheet:
1. General
Only values in bold red font are meant to be modified by the user. These user-modifiable cells are in the "Main" and "Demand" worksheets. Other cells should be left unmodified.
The "Raw" worksheet contains the raw data extracted from the TIDEDA database, and cells must not be modified.
The "Main" worksheet contains the computation of the residual flow, the available take and the shortfall amount on a daily basis. The main variables to be modified by the user are also on this worksheet, including the residual flow parameters, the peak irrigation and other takes, and the flow scaling factor.
The "Demand" worksheet contains the pattern of take by month-of-year, expressed as a percentage of the peak take rate. Two such take patterns have been allowed for.
2. Residual flow regime
The three-stage flow regime works as follows:
(i) for inflows less than x, residual = lesser of the actual inflow or x multiplied by the flow sharing ratio r1, i.e. residual = r1.inflow
(ii) for inflows between x and y, a proportion r2 of the inflow between x and y is added to the bypass, i.e. residual = r1x + r2(inflow - x)
(iii) for inflows greater than y, a proportion r3 of the inflow above y is added to the bypass, i.e. residual = r1x + r2(y - x) + r3(inflow - y).
Two sets of residual flow parameters have been allowed for, and each month of the year can either have one pattern or the other. The user will have to set the parameters x, y and r1, r2 and r3. If only one regime is to be used, then it is not necessary to define the Pattern 2 parameters. In this case, ensure that under Pattern 1, and under "On or Off" all values in column I from rows 10 to 21 are set to 1. If some months are to have Pattern 2 residual, then for these months the corresponding values under Pattern 1 (between I10 and I21) should be set to 0. There is no need to change the values under "On or Off" in Pattern 2 as these change automatically.
The graphs to the right of the residual flow parameters illustrate the flow-sharing regime specified by the parameters. The data for plotting these graphs are tabulated to the right of these graphs.
3. Demand pattern
Three separate take patterns are provided for in the spreadsheet model, comprising a constant take and two by-month take patterns. The two take patterns are specified in the "Demand" worksheet, and are expressed as a percentage of the peak take. The peak take rates are specified in the "Main" worksheet in cells E18 and E19. The base constant take rate is specified in cell E20 in the "Main" worksheet. The total take applied is the sum of the three takes.
However, we note that, if an allocation is granted, the consent owner can theoretically take the full allocated amount (but within the applicable residual flow rules) at any time of the year. Thus, there is a case for setting all the percentage values at 100%, unless the consent conditions specify a lower limit outside the irrigation season.
4. Shortfall computation
Conceptually, the total take is assumed to be lumped at the flow site, and the shortfall = total take less the scaled inflow. The scaling factor (cell E17), which is applied to the recorded mean daily inflows, allows for the situation where some of the take occurs below the flow recording station, and compliance with the environmental flow regime is monitored at some virtual downstream flow site. The actual situation can be significantly more complicated because some rivers have less surface flow at downstream locations because of flow loss to the river bed and aquifers.
The number of shortfall days on a year-by-year basis is summarised in the table below the flow-sharing graphs. The shortfall criteria can be changed by the user by changing the values in cells Q47 to W47. For example, ">10%" will return the number of days with shortfall greater than 10% of the total demand, and so on.
Seven classes of shortfall criteria (Q47 to W47) have been allowed for to enable plotting of the average shortfall days per year versus shortfall severity. This graph appears on the left below "Take Shortfalls" on the "Main" worksheet. The "Rank" (column X to the right of the table of year-by-year shortfall days) gives an indication of the dry years in the record, with rank 1 being the driest (based on the criteria in R47).
