Scenario after scenario is slow
Three scenarios F2 = switching by hand. A data table lets Excel/Sheets run F2 say ten times — 0.8; 0.9; … 1.5 — and for each value it shows I10 (week balance).
One variable = one lever. Two variables only in the next lesson.
A table for F2
-
Kapesne: F2 = spending multiplier, H column model spending, I10 ==D8or another final balance (last day column D). -
In K10 write
0.8. In L101.5. Fill K10:M10 with values 0.8; 1.0; 1.2; 1.4; 1.5 (step as you like). -
In J11 write
=I10— a link to the result. Above row 11 (J10) leave empty or a label. -
Excel: select the matrix K10:M11 (top row = F2 values, left column empty or J11=I10). Data → What-if analysis → Data table. Row input cell: empty. Column: $F$2.
-
Sheets: often a handmade table — in the cell under each F2 temporarily set F2, write I10, put it back — or Apps Script. Simpler a handmade grid: K11
=I10with F2 in K10 as a value — use=D8recalculated viaLETif you have it, otherwise a copy of the model in a column. Exercise: five F2 values, five results written by hand after changing F2 — you will get the idea of a data table.
A data table overwrites the result area. Leave it next to the model (columns K–M), not over formulas in H.
Reading the results
Where does I10 first drop under 0? At F2 = 1.3? That is the answer to “how far can I worsen spending before I end in minus”. The Modeller answers questions, not just numbers.
What often goes wrong
I10 is not a formula that depends on F2. Changing F2 changes nothing — the data table empty or the same.
Forgotten $ on F2. The input cell must be $F$2.
Mixing handmade numbers and the table. In the result cells of a data table you do not write by hand — Excel overwrites.
🏫 Grade average — one grade
B4 as a variable (1–5), B8 average as the result. Data table: B4 runs 1–5, watch the average. “What if only English was 5?”
💰 Pocket money — F2 range
The main exercise. Five to ten F2 values. A line chart from the data table (F2 vs. I10) — Chart maker bonus.
📊 Class survey — sensitivity
One cell E2 (+1 vote) — how does the winner’s share change? A simple copy, not a data table. The sensitivity idea is the same.
🏆 Mini dashboard — reality
The dashboard shows F2=1. The data table stays on Kapesne for you — not for the class.
Now you 💪
First: five F2 values, five I10 results (Excel data table or a handmade write after changing F2).
Second: answer in a sentence: at which F2 do you first fall under zero?
Check: Results react to F2. I10 a formula. The table next to the model, not over it.
What to take from this lesson
- 1D data table = one lever, many results.
- Input cell $F$2, result = formula I10.
- Sheets: a handmade run is OK, the idea is the same as Excel.
© 2026 Ing. Martin Polak / AlgoRhino · Content usage terms