Reservoir Dynamics

A spreadsheet containing the raw data is available for your use. It may be easiest to get the spreadsheet by using Internet Explorer.  Reservoir dynamics spreadsheet

  1. The majority of the flow into Cannonsville Reservoir enters through West Branch Delaware River at Walton NY. Find the station by searching the New York Gaging Stations. To find all the stations on the West Branch of the Delaware River search for "West Branch Delaware" in the station name. The drainage area that flows into the Cannonsville reservoir is 450 square miles. What fraction of the drainage area enters through the West Branch of the Delaware River at Walton NY?
  2. Your goal is to estimate the total inflow into Cannonsville Reservoir using the data from the Walton gaging station. You could retrieve the daily stream flow at the Walton NY gaging station from 06/01/1980 - 5/31/1990 from the USGS web site, but the web is sometimes slow and so we will start with the Reservoir dynamics spreadsheet. Right click on the link and save the file to your computer. You may assume that the stream flow coming from the other areas into the reservoir is proportional to the area of the watersheds. What do you multiply the stream flow by to get an estimate of the total flow (adjusted stream flow) into the reservoir?
  3. Convert the adjusted stream flow to million m3/day. Graph the stream flow vs. time using a semi-log line plot. What was the adjusted peak flow for the 10-year record, on what day did it occur, and what do you think caused the peak inflow?
  4. Create a spreadsheet model for the storage in the Cannonsville Reservoir. The Cannonsville Reservoir has a useable storage of 97 billion gallons. Initially you may assume a demand by NYC of 1.2 x106 m3/day and assume the reservoir is full at the beginning of the day on June 1, 1980. Use names to make your spreadsheet understandable. Your spreadsheet should have the following columns:

    adjusted inflow (m3x106/day)

    cumulative inflow
    (m3x106)

    cumulative demand (m3x106)

    cumulative River flow (m3x106)

    storage

    (m3x106)

    Reservoir percent of full
  1. Your goal is to find the demand that will cause the reservoir to reach 20% of full. Graph the reservoir percent of full as a function of time. Use Excel's MIN function to find the minimum reservoir level. Then use Excel's Goal Seek or Solver... capabilities (Tools Menu) to increase the demand by NYC until the reservoir reaches a minimum of 20% of full. On what date does the reservoir reach the minimum value? What is the demand (in m3x106/day) that causes the reservoir to reach the 20% of full value?
  2. Use Solver… to determine the demands that correspond to the reservoir reaching 0%, 10%, 20%, 30%, 40%, 50%, 60%, 70%, 80%, 90%, and 99%. Graph the minimum reservoir level (as a percentage of full) as a function of demand. (Note that you will need to Copy the demands and use Paste Special to paste the Value of the cell into a new location in order to save the calculated demand values. An alternate and more elegant way to obtain these values is to save each of the solver solutions as a scenario and then use the Scenarios… in the Tools menu and then select Summary… to have Excel summarize all the scenarios that you saved.)
    Note that as demand increases the minimum reservoir level decreases more rapidly.

Submit the following by email to the "homework TA": 

  1. Answers to each of the questions including graphs from Excel (pasted as pictures) as an attachment in Word.
  2. Your Excel Spreadsheet.