Project Grade 10 Science
Ostlin's Drought Plan: A Spreadsheet Water Budget
A five-session grade 10 project where teams build a monthly spreadsheet water budget for a growing town, stress-test it, and choose a 15-year drought plan.
Objectives
- Students will build a monthly water budget that tracks reservoir storage as river supply and town demand change through the year.
- Students will project demand forward with a growth rate and find the first year the reservoir falls below its drought reserve.
- Students will choose measures that fit a stated budget and protect the drought reserve under dry-year and high-growth tests.
Vocabulary
- Water budget
- A water budget is a way of tracking all the water coming into an area, like rain and snowmelt, against all the water leaving it, like evaporation and streamflow. Scientists use water budgets to see whether a watershed or aquifer is gaining or losing water over time.
- Acre-foot
- The amount of water it takes to cover one acre of land, about the size of a football field, one foot deep. Western states use acre-feet to measure large amounts of water.
Also: drought reserve, peak demand, reclaimed water
Teacher overview
A growing town will eventually ask for more water than its river and reservoir can deliver through a dry summer, and its planners have to work out which year that happens and what to build first. Over five sessions, teams build a month-by-month spreadsheet water budget for the fictional town of Ostlin, project it fifteen years forward, and test it against two dry years and faster growth. They then choose a package of conservation and supply measures that fits a fixed capital budget, and the final deliverable is a written plan and a short presentation to a fictional water board, which votes on the packages.
Project Brief
Driving question: How should the fictional town of Ostlin keep Marrow Reservoir above its drought reserve for the next fifteen years, including two dry years in a row, without spending more than its capital budget?
Scenario: The fictional Ostlin water board has asked student teams to write the town's first drought plan. Ostlin draws from the Stannifer River and stores water in Marrow Reservoir. In winter and spring the river carries more than the town uses, and the extra refills the reservoir. From June through September the town uses more than its permit allows it to take from the river, and the reservoir makes up the difference. Every figure in this brief is invented for the project.
Baseline data table (Year 0, the current year):
| Month | Days | Town demand (MGD) | Permitted river withdrawal (MGD) |
|---|---|---|---|
| January | 31 | 2.6 | 7.0 |
| February | 28 | 2.6 | 7.0 |
| March | 31 | 2.7 | 7.0 |
| April | 30 | 2.9 | 7.0 |
| May | 31 | 3.4 | 4.4 |
| June | 30 | 4.2 | 2.8 |
| July | 31 | 4.8 | 2.0 |
| August | 31 | 5.0 | 2.0 |
| September | 30 | 4.3 | 2.0 |
| October | 31 | 3.3 | 4.2 |
| November | 30 | 2.8 | 7.0 |
| December | 31 | 2.6 | 7.0 |
MGD means million gallons per day. Average demand across Year 0 is about 3.4 MGD, and August is the month of peak demand. The permitted withdrawal is the most the state allows Ostlin to take from the river in each month. It does not grow over time.
Model rules:
- Marrow Reservoir holds 1,400 acre-feet of usable storage and is full (1,400 acre-feet) on January 1 of Year 0.
- The board requires that 300 acre-feet stay in the reservoir at all times as a drought reserve.
- One acre-foot equals 325,851 gallons, so 1 million gallons equals 1,000,000 ÷ 325,851 acre-feet.
- Each month, change in storage (acre-feet) = (permitted river withdrawal − demand) × days in the month × 1,000,000 ÷ 325,851. A negative change is a draw on the reservoir.
- End-of-month storage = previous end-of-month storage + change in storage, except that it can never be more than the reservoir's full capacity. When the river could supply more than the reservoir can hold, the extra is left in the river.
- Population and demand grow 1.2 percent per year in every month. Demand in Year n = Year 0 demand × 1.012^n, for Years 1 through 15.
- Storage carries over from December of one year to January of the next, so Year 1 begins from the Year 0 December storage.
Measure price sheet (all figures invented for this project):
| Measure | Capital cost | Effect in June through September |
|---|---|---|
| Tiered summer rate structure | $120,000 | Reduces demand by 0.30 MGD |
| Irrigation timer and rain barrel rebates | $150,000 | Reduces demand by 0.05 MGD |
| Turf replacement rebates for 400 homes | $600,000 | Reduces demand by 0.20 MGD |
| Leak detection and repair program | $850,000 | Reduces demand by 0.25 MGD |
| Reclaimed water line to the ballfields and cemetery | $2,400,000 | Reduces demand by 0.45 MGD |
| New well in the Tarnfield Sand aquifer | $3,100,000 | Adds 0.90 MGD of supply |
| Dredging to restore reservoir storage | $1,800,000 | Raises full capacity from 1,400 to 1,580 acre-feet |
Each measure may be chosen once. Assume every chosen measure is in place by January 1 of Year 1. Apply each demand reduction or added supply only in June, July, August, and September, the months when the town draws on the reservoir in a normal year, and leave the other eight months unchanged. Dredging raises the full capacity in every month from Year 1 on, and the larger reservoir fills from the January surplus. The drought reserve stays at 300 acre-feet.
Constraints:
- Total capital spending must stay at or below $6,000,000.
- The reservoir must never fall below the 300 acre-foot drought reserve in any modeled month of Years 1 through 15.
- The plan must still meet the reserve when Years 14 and 15 are both dry years. In a dry year, the permitted river withdrawal in every month is 75 percent of the table value. Years 14 and 15 are used because demand is highest at the end of the plan.
- At least one measure must reduce demand rather than add supply or storage.
Final deliverable: A spreadsheet model, a two-page written plan addressed to the fictional Ostlin water board, and a five-minute presentation. The written plan must name the chosen measures, state the total cost, state the first year the reservoir falls below the reserve without any action, and show the reservoir's lowest storage under the plan in normal years, in the two dry years, and with 2.4 percent growth.
Milestones
- Session 1, build the baseline: Teams enter the Year 0 baseline data table and write formulas for each month's change in storage and end-of-month storage, starting from a full reservoir on January 1. They convert the August shortfall from million gallons per day into acre-feet for the month. Output: a twelve-month Year 0 table showing the change in storage and end-of-month storage for every month, with the August draw and the lowest storage of the year stated in acre-feet.
- Session 2, project fifteen years: Teams add the growth factor 1.012^n to the demand column and extend the model through Year 15, carrying storage from each December into the next January. They identify the first year in which the reservoir falls below the 300 acre-foot reserve with no action, and they graph each year's lowest storage against year with both axes labeled and scaled and a horizontal line at 300 acre-feet. Output: a fifteen-year projection, the storage graph, and a stated shortfall year.
- Session 3, choose a package: Teams select measures from the price sheet, add a June-through-September adjustment to their demand or supply columns, and confirm the total cost stays at or below $6,000,000. Output: an itemized package with total cost, the combined June-through-September effect in MGD, and the lowest storage in any month of Years 1 through 15.
- Session 4, stress test and revise: Teams rerun the model twice, once with Years 14 and 15 as dry years at 75 percent of the permitted river withdrawal, and once with growth at 2.4 percent per year instead of 1.2 percent. For each run they record the lowest storage, the month and year it occurs, and whether the drought reserve holds. If it fails, they revise the package and confirm the revised cost still fits the budget. Output: two stress-test results and a revised package with its new total cost.
- Session 5, write and present: Teams write the two-page plan and present it to the class acting as the fictional Ostlin water board, which asks one question of each team and votes. Output: the written plan and the completed presentation.
From Water for Schools (waterforschools.org). This material is in the public domain (CC0) and may be copied, edited, and shared freely.
Rubric
| Criterion | Emerging | Developing | Proficient | Advanced |
|---|---|---|---|---|
| Model construction | Table is incomplete or the supply and demand columns do not connect to storage | Model balances one month but formulas are entered by hand rather than linked, or storage is allowed to rise above full capacity | Model links demand, river supply, and reservoir storage across all twelve months and fifteen years, caps storage at full capacity, and matches the Year 0 values in the teacher reference | Model is fully linked, clearly labeled, and lets the growth rate, dry years, or any measure be changed without rebuilding the sheet |
| Use of constraints | Package ignores the budget cap or the drought reserve | Package meets one constraint but not the other | Package meets the budget cap and keeps the reservoir at or above 300 acre-feet in every modeled month, including the two dry years | Package meets both constraints with a stated margin and the plan explains the trade-off behind each measure chosen |
| Stress testing | No stress test attempted | One stress test run but results not interpreted | Both stress tests run and the failing constraint, month, and year identified | Both stress tests run, the failing constraint identified, and the package revised with a stated reason and a cost comparison |
| Written plan and presentation | Plan omits the cost or the shortfall year | Plan reports results without explaining the reasoning | Plan names measures, cost, the no-action shortfall year, and the reservoir's lowest storage under the plan clearly | Plan does all of that and answers the board's question with evidence from the model |
Teacher reference
Use these figures to check team models. Values are rounded to one decimal place at the end of each calculation, so a team that rounds monthly values first may differ by a few tenths of an acre-foot. One million gallons is 1,000,000 ÷ 325,851 = 3.0689 acre-feet.
Year 0 baseline (Session 1).
| Month | Change in storage (MG) | Change in storage (acre-feet) | End-of-month storage (acre-feet) |
|---|---|---|---|
| January | +136.4 | +418.6 | 1,400.0 (full) |
| February | +123.2 | +378.1 | 1,400.0 (full) |
| March | +133.3 | +409.1 | 1,400.0 (full) |
| April | +123.0 | +377.5 | 1,400.0 (full) |
| May | +31.0 | +95.1 | 1,400.0 (full) |
| June | −42.0 | −128.9 | 1,271.1 |
| July | −86.8 | −266.4 | 1,004.7 |
| August | −93.0 | −285.4 | 719.3 |
| September | −69.0 | −211.8 | 507.6 |
| October | +27.9 | +85.6 | 593.2 |
| November | +126.0 | +386.7 | 979.9 |
| December | +136.4 | +418.6 | 1,398.5 |
The August draw is (5.0 − 2.0) × 31 = 93.0 million gallons, and 93,000,000 ÷ 325,851 = 285.4 acre-feet.
Answer: 285.4 acre-feet drawn from the reservoir in August of Year 0.
The June-through-September draw is 42.0 + 86.8 + 93.0 + 69.0 = 290.8 million gallons, which is 892.4 acre-feet, so storage bottoms out at 1,400 − 892.4 = 507.6 acre-feet at the end of September. The reservoir refills in January.
Answer: 507.6 acre-feet, the lowest storage of Year 0, at the end of September.
Fifteen-year projection with no action (Session 2). The January-through-May surplus refills the reservoir to 1,400 acre-feet every normal year, so each year's lowest point is the end of September. Because the river permit does not grow, the June-through-September draw in Year n is (558.8 × 1.012^n − 268.0) million gallons, where 558.8 is the four-month Year 0 demand total and 268.0 is the four-month river total.
| Year | Growth factor | Lowest storage (acre-feet) | Year | Growth factor | Lowest storage (acre-feet) |
|---|---|---|---|---|---|
| 1 | 1.0120 | 487.0 | 9 | 1.1133 | 313.2 |
| 2 | 1.0241 | 466.2 | 10 | 1.1267 | 290.3 |
| 3 | 1.0364 | 445.1 | 11 | 1.1402 | 267.1 |
| 4 | 1.0489 | 423.8 | 12 | 1.1539 | 243.7 |
| 5 | 1.0615 | 402.2 | 13 | 1.1677 | 219.9 |
| 6 | 1.0742 | 380.3 | 14 | 1.1818 | 195.9 |
| 7 | 1.0871 | 358.2 | 15 | 1.1959 | 171.6 |
| 8 | 1.1001 | 335.9 |
Answer: Year 10, when end-of-September storage falls to 290.3 acre-feet, below the 300 acre-foot reserve.
In Year 15 the August draw alone is (5.0 × 1.1959 − 2.0) × 31 = 123.4 million gallons, or 378.6 acre-feet. With no action, the dry-year test empties the reservoir in October of Year 14 (the formula gives −149.4 acre-feet, which means the reservoir would run dry), and 2.4 percent growth moves the first shortfall to Year 5, at 291.7 acre-feet.
Measure effects (Session 3). Over June through September (122 days), each 1.00 MGD of reduced demand or added supply saves 122 million gallons, or 374.4 acre-feet. The tiered rates save 112.3 acre-feet, the timer rebates 18.7, the turf rebates 74.9, leak repair 93.6, reclaimed water 168.5, and the new well 337.0. These savings add only while every one of the four months is still a draw month, which holds for any package whose combined effect is 1.40 MGD or less. A larger package can turn June into a surplus month, and because the reservoir is full on June 1, that surplus is left in the river.
Sample package A (passes the normal and dry-year tests, fails the growth test). Tiered rates, turf rebates, and the new well cost $120,000 + $600,000 + $3,100,000 = $3,820,000 and have a combined effect of 0.30 + 0.20 + 0.90 = 1.40 MGD.
Answer: $3,820,000 total capital cost for package A.
Answer: 695.7 acre-feet, package A's lowest storage at 1.2 percent growth, at the end of September in Year 15.
Answer: 341.4 acre-feet, package A's lowest storage with Years 14 and 15 dry, at the end of October in Year 15 (Year 14 bottoms out at 374.8 acre-feet in October).
Answer: At 2.4 percent growth, package A first falls below the reserve at the end of October in Year 14, at 286.2 acre-feet, and its lowest storage is 207.5 acre-feet at the end of October in Year 15, so the drought reserve is the constraint that fails.
Sample package B (revised from A, passes all three tests). Adding dredging raises the total to $3,820,000 + $1,800,000 = $5,620,000, which is $380,000 under the cap.
Answer: $5,620,000 total capital cost for package B.
Answer: 875.7 acre-feet at 1.2 percent growth (end of September, Year 15), 521.4 acre-feet with Years 14 and 15 dry (end of October, Year 15), and 387.5 acre-feet at 2.4 percent growth (end of October, Year 15).
Common misjudgment. Tiered rates and turf rebates alone cost $720,000 and keep the reservoir above the reserve in every normal year (lowest 358.8 acre-feet in Year 15), yet in the dry-year test storage falls to 37.8 acre-feet in October of Year 14, and the reservoir runs dry in Year 15. A team that stops after the normal-year run will believe this cheaper package is enough. Other passing packages exist, such as tiered rates, leak repair, the new well, and dredging for $5,870,000, so judge each team's package by its own model output against this reference.
Differentiation
Support
Give these teams the Year 0 baseline table already built in a spreadsheet, with the formulas for change in storage and end-of-month storage entered in the January row as a model, so their work begins at the projection step. Provide the acre-foot conversion worked once for August, and allow them to choose from a shortened price sheet of four measures: tiered rates, turf rebates, the new well, and dredging. Teams working on paper can record only the four draw months for each year, since the reservoir refills to full by June in every normal year.
Extension
Ask teams to add a column that estimates how each measure changes the average household bill, using a cost-per-customer figure you supply, and to explain which package a household on a fixed income would prefer. Have them name the measure with the widest gap between cost and public support. A second option is to phase the spending, building the well in Year 6 instead of Year 1, and to find the latest year each measure could start without breaking the reserve.
ELL supports
Pre-teach "budget," "reserve," "shortfall," "permit," and "acre-foot" with a labeled diagram of a reservoir showing the usable storage, the full line, and the reserve at the bottom. Provide a labeled spreadsheet template with column headings already written, and allow teams to plan in their home languages before drafting.