Module 1: Modelling in Excel

Environmental Flows to an Estuary

Objectives

Use scenario assessment to identify the impacts of water diversion on a coastal lake system. Become familiar with basic spreadsheet commands in Excel by conducting an environmental flow analysis and building a simple impact model.

What are environmental flows?

Lakes, rivers and estuaries are adapted to receive water based on the natural hydrological condition, but often water is required upstream for other purposes. An environmental flow is water that is provided to the environment to maintain its health and natural diversity

In simple terms: Provision of water to satisfy environmental requirements for ‘healthy’ (natural) function.


Environmental flows are a form of river/wetland restoration. They are sometimes referred to as “e-flows”.

In the Murray-Darling Basin (MDB), a water market was established that allows for irrigation entitlements to be “traded”; e-water must be bought from the “water holder” (in this case that is the Commonwealth Environmental Water Holder, CEWH).

Flow diversion case study: Robe Lakes

What happens if we divert freshwater away from a coastal lake system? How much flow diversion is “acceptable”?

In this exercise we use a real data-set from the Robe Lakes system in South Australia to explore this question.

Figure 1: Explore the case study area with this interactive map.

We can plan for e-water requirements to a highly valued system using scenario modelling, and the concept of a “flow-response model” (or flow-response relationship). In this case we look at an example in South Australia where an assessment for water diversion is being undertaken, and we seek to look at what impact this might have on the salinity of a coastal lake. In other words, if we change the flow regime, what will the response be. In this case, salinity is an environmental indicator of ecological requirements.

Specifically in this example, we assess the impact of diverting water from “Drain L”, thereby reducing water to the Robe Lakes. Before we assess our diversion scenario, we must first analyse the historical flow data, to understand the historical flow regime.

Figure 1: Drain L inflows (yellow arrow) will be diverted (blue arrow)

Module resources

Download the Excel spreadsheet for this module by clicking the download button in the tool bar .

Exercises

You are given a time-series of daily river flow (through the Drain) and need to assess how different diversion scenarios impact the freshwater flow delivery to a down stream estuary.

The steps below allow us to:

  • “Bin” (aka categorise) the daily flow into different categories (using an IF statement)
  • Summarise the daily flows into annual totals (using a PIVOT table)
  • Describe the historical flow variance in terms of an exceedance probability plot
  • Create to alternate “diversion scenarios”. These are “what-if” type scenarios where we explore what would have occurred if the diversion policy was in place.
  • Build a simple empirical (statistical) model linking drain flow rate and estuary response - we measure response in terms of salinity.
  • Use the empirical model to show what the salinity in the estuarine lakes would have looked like if the flow diversion policy was in place.

Now that we have an overview, lets get started! Follow closely the below steps from 1 to 9.

  1. Categorise the flow into different categories. For this flow time-series, calculate the volume of daily flow within the following bands 0-40; 40-80; 80-120; 120-200; 200-400; 400-1000; >1000
Use a nested IF() statement in adjacent columns to partition flows
  1. For this flow time-series, calculate the number of days of daily flow within the above bands and then work out annual average number of days. The latter is simply computed by dividing the total number of days by the number of years in the data-set.
Use COUNT() to get the number of entries in the column
  1. Calculate the annual amount of flow in total and in each flow band.

You may do the annual averaging manually (slow) or the pivot table method (fast). For the pivot table method: Insert a new column B, Label it Year. Type the year number into the first cell, make sure it is formatted as a number.

Click two times on the little square in the corner of the box. This will fill down the page. Repeat this at each year change and you will have the year only information which you can then use to summarise data. As the original date has day numbers and decimal time each one is unique so you end up with a record for each day. This way you get a record for each year.

Use YEAR() to speed this step up

Highlight all the data. It must have headings for each column or it won’t work. Go to “Insert” then “Pivot Table”. Click OK as you want the default settings. Then you just fiddle until you get what you want. The following is an example:

  1. Sort the years from highest to lowest total annual flow:
    1. Add a new column next to the flows indexing the years sequentially (i.e. 1, 2, 3…)
    2. Add a new column next to the indices calculating the probability of meeting the annual flow: \(P(Yri) = \frac{i}{\text{number of years}+1}\)
  1. Create a scatter plot of the flow (y-axis) vs probability (x-axis) to create an “exceedance” curve
Sum each year

Flow diversion scenarios

  1. Assess the amount of water available for flow diversion for two scenarios by adding a new time-series column for each option:
    1. Water below 50 ML/day and above 550 ML/day is NOT diverted
    2. Water below 150 ML/day and above 1000 ML/day is NOT diverted
Construct a nested IF() that solves the following:
  1. Add two new columns for each of the above to work out cumulative amount (running total) of diverted water
    1. \(\small{\text{Cumulated Volume} = \text{Yesterday's Total Diverted Discharge}+\text{Today's Diverted Discharge}}\)

Assessing impacts on lake salinity

You’ve now calculated the cumulative amount of water diverted under the two flow diversion scenarios. Next, let’s calculate how much water will remain in the Robe Lakes and the impact that this will have on salinity.

  1. Calculate the daily flow into the Robe Lakes for both diversion scenarios
Subtract the volume of today’s diverted water from today’s inflow

Look at sheet Estuary – Salinity. This records data collected at the site marked on the map with a +. You will use this data to build a simple statistical model that predicts the salinity from the drain’s daily discharge.

  1. Start by creating a scatter plot of the historical salinity (as EC) and daily discharge in the Estuary – Salinity sheet.
  2. Fit a trendline to the data and display equation and R2 – this is a statistical model. Look x/y axis, is your model predicting for salinity?
  3. In a new time-series column in the previous sheet, use the statistical model from above to predict a salinity time-series for the Robe Lakes under the two diversion scenarios compared to the base case.

Submission

In this exercise, we have explored the complexities of allocating water to the environment in a contested situation (i.e., where there are competing “customers” for the water resource). In particular, we can summarise environmental flows:

  • appreciate that there is a potential conflict between e-flow vs irrigation allocation
  • e-flow allocation is a river restoration and maintenance tool
  • the array of benefits from e-water need to quantified and maybe site specific
  • there is a need for identifying “Sustainable Diversion Limits” based on assessment of historical flow requirements.

Submit properly formatted graphs and tables of the following sections of the lab:

  • Q2: Table of the number of days daily flow within the above bands and annual average number of days
  • Q3: Table of the annual amount of flow in total and in each band
  • Q4: Table of annual flow by year with index and probability. Scatter plot of the flow vs probability curve
  • Q6: Table of the total cumulative amount of diverted water (i.e. the last value) for both diversion scenarios
  • Q7 & 8: Scatterplot of the EC vs. daily discharge from the drain, with trendline (hint: exponential), equation and R2 value
  • Q9: Plot of the annual average salinity timeseries of the two diversion scenarios compared to the base case
  • In less than 100 words, discuss which is the most appropriate scenario choice for management of the estuary

These are to be uploaded as per the formatting specified in the Style Guide. Marks will be deducted for incorrect formatting.

Professional presentation of your results is important. Explore the short tutorial in the Supporting Material section to improve your data viz skills with R’s tidyverse package.