
build and run a spreadsheet model that links human emissions to atmospheric CO2 and surface-ocean pH, identify its boundaries, starting conditions, inputs and outputs, and use it to explain how human activity changes the link between Earth systems.
Phase 4: Build
Do Now. If people release 11 GtC per year and 45% stays in the air, how many ppm does atmospheric CO2 rise in one year? (1 ppm = 2.12 GtC.) Show your steps.
11 GtC/yr × 0.45 ÷ 2.12 GtC/ppm is closest to…
Why a computational model. The hatchery wants to know whether its bad-water days are a passing problem or a trend. A computational model applies simple rules over and over, one time step at a time, so we can ask “what if?” about futures nobody has measured. Ours runs in a spreadsheet with one row per year.
The parts of the model. Systems: the atmosphere and the surface ocean, with land plants and the deep ocean represented only as a combined sink. Boundary: global averages; no local upwelling. Starting conditions: 419 ppm and surface pH 8.1 in 2023 (NOAA). Input: human emissions E (GtC/yr). Outputs: the share of E taken up by land and ocean, set by an airborne fraction AF = 0.45. Interactions: more CO2 in the air means more dissolved CO2 and lower pH; higher CO2 also speeds photosynthesis somewhat (one reason the land sink has grown), which the model folds into AF.
The rules. Column A: year. B: emissions E. C: CO2 (ppm); each row = previous row + E × AF ÷ 2.12. D: pH = 8.2 − β × log10(C ÷ 280). E: [H+] = 10−pH. In a spreadsheet, row 3 of column C is =C2+B2*0.45/2.12 and column D is =8.2-$G$1*LOG10(C3/280), with β stored in cell G1 so you can change it once.
Worked example 1: one time step. Rosa started at 2023 with 419 ppm and E = 11 GtC/yr. 2024: 419 + 11 × 0.45 ÷ 2.12 = 419 + 2.33 = 421.3 ppm. Repeating for 27 steps, constant emissions give 419 + 27 × 2.33 = 482 ppm in 2050. The rule is linear because E is constant; change E each year and the curve bends.
Version 1 of the pH rule: no buffer. If [H+] rose in direct proportion to dissolved CO2 (bicarbonate held fixed), then β = 1. That predicts pH 8.2 − log10(419 ÷ 280) = 8.2 − 0.175 = 8.03 today. NOAA’s observations put today’s surface ocean near 8.1. The model overshoots.
Worked example 2: calibrating with evidence. Malik reasoned from 3.05: as CO2 enters, carbonate turns some of the new H+ into bicarbonate, so [H+] rises more slowly than CO2. He calibrated β to the observed change: a drop of 0.1 unit for a CO2 ratio of 420 ÷ 280 = 1.5, so β = 0.1 ÷ log10(1.5) = 0.1 ÷ 0.176 = 0.57. Version 2 then gives, for 2050 at 482 ppm: 8.2 − 0.57 × log10(1.72) = 8.2 − 0.57 × 0.236 = 8.07. The calibration is honest only if he says so: β was fitted to one rounded pair of numbers.
| Scenario (2023–2050) | CO2 in 2050 (ppm) | pH in 2050 (β = 0.57) |
|---|---|---|
| A. Emissions stay at 11 GtC/yr | 482 | 8.07 |
| B. Emissions grow 1% per year | 491 | 8.06 |
| C. Emissions fall steadily to zero by 2070 | 465 | 8.07 |
| D. Like A, but the land sink weakens (AF = 0.55) | 496 | 8.06 |
Reading the runs. Every scenario keeps lowering pH through 2050, because pH follows the total CO2 that has built up, not this year’s emissions. The scenarios separate more over longer runs: under A, CO2 reaches about 600 ppm by 2100 and pH about 8.01, while under C the rise stops after 2070. The model also cannot draw CO2 back down once emissions stop, because AF only applies to new emissions; the real ocean and land would keep absorbing. Human activity has changed the relationship between the systems: the atmosphere now pushes carbon into the ocean every year, so the ocean’s chemistry is tied to our emissions.
Build the spreadsheet with your team, then answer from your own runs.
Model documentation for the brief.
Write a model card (about 150 words): systems included, boundary, starting conditions, inputs, outputs, parameters and their sources, and how you tested the model against NOAA data.
List three limitations of the model (for example, a single β, no local upwelling, no drawdown after emissions stop) and say which one matters most for the hatchery.
The model shows the trend; the hatchery’s bad days sit on top of it. Next class, we design the fix.
Exit ticket: the hatchery owner asks, “Will bad-water days go away on their own?” Answer in two sentences using one number from your model.
Materials: spreadsheets on devices (any spreadsheet program), a starter sheet with years 2023–2100 in column A, the NOAA annual-mean table from 3.06, graph paper. No device? Teams compute every fifth year by hand with calculators and plot the points.
Model notes: β = 0.57 is calibrated to the rounded “8.2 to 8.1” change; more detailed estimates put pre-industrial surface pH near 8.18 and today’s slightly below 8.1, and full seawater chemistry gives a buffer effect that weakens as carbonate is used up. Scenario values: A and D are linear; B and C were run year by year from 2023 (B: E multiplied by 1.01 each year; C: E reduced by 11 ÷ 47 GtC each year).
Listen for: treating model output as a measurement; “the model proves…”; forgetting the log in column D.
Support / stretch: give a half-built sheet with column C done. Stretch: add a column for the relative change in carbonate, using the rough rule that carbonate falls about 20% when CO2 rises 50%, and discuss what it means for Ω.