Lesson 1: Inputs, Calculations, Outputs, and Documentation

1 Modelling context

A spreadsheet is a modelling instrument, not a convenient calculator. Its physical layout is a representation of the model's logical structure. When that layout is transparent, another analyst can read the model and identify its assumptions, trace its dependencies, and rerun it on new inputs. When the layout is opaque — formulas buried inside other formulas, hard-coded constants scattered across sheets, outputs recomputed from raw inputs on a separate tab — the model cannot be audited, and therefore cannot be trusted.

The problem is not that opaque models are always wrong. The problem is that you cannot tell. A model whose logic is invisible provides no basis for deciding whether to rely on it. This lesson establishes the layout conventions that make a spreadsheet model legible, testable, and reproducible.

These conventions are not stylistic preferences. They are part of the course standard, and Assignment 1 audits a model against them directly.

Why this matters in practice

Most spreadsheet failures in applied work are not caused by wrong mathematics. They are caused by a formula that referenced the wrong cell, a unit conversion applied twice, a column filled incorrectly past the first row, or a value that was updated in one place but not another. A three-layer architecture makes all of these failures visible.

2 Learning objectives

By the end of this lesson you will be able to

Design a spreadsheet model with separated inputs, calculations, and outputs.
Document each layer so its assumptions, units, sources, and dependencies are explicit.
Apply the rules of the three-layer architecture and explain the purpose of each rule.
Audit an existing spreadsheet model for transparency and structural correctness.
Identify common layout errors that make a model difficult to verify.

3 Prerequisite review

Cell reference: An address such as B4 that identifies a single cell. References are the mechanism by which one calculation uses the result of another.
Relative and absolute reference: A relative reference (B4) shifts when the formula is copied. An absolute reference ($B$4) does not. Mixing them deliberately is essential to making a formula fill correctly.
Named range: A human-readable alias for a cell or range, such as initial_temp. Named ranges make formulas self-documenting and reduce reference errors.
Dependency: The relationship "this cell uses the value of that cell." The dependency graph of a spreadsheet is the network of all such relationships, and it determines what must be recalculated when an input changes.
Circular reference: A dependency cycle in which a cell, directly or indirectly, depends on its own value. Most spreadsheet software will refuse to compute it or iterate silently, either of which is dangerous.

4 Mathematical formulation

The three-layer architecture formalises the natural mathematical structure of a model. Any computational model can be written as a function

y = f(x; θ)

where x is the vector of observed inputs, θ is the vector of parameters, and y is the vector of outputs. The three layers correspond to the three components of this expression:

Inputs hold x and θ. These are the only values edited by hand.
Calculations implement f. Every formula lives here, and every formula refers either to inputs or to other calculations.
Outputs present y. Output cells reference calculations; they never contain formulas of their own beyond simple formatting.

Rules of the architecture

The rules below are what make the layering enforceable rather than aspirational. Each rule closes a specific class of error.

Rules of the three-layer architecture
Rule	Purpose
Inputs are the only hand-edited cells.	Guarantees that every variable in the model can be found and varied in one place.
Every formula lives in the calculation layer.	Guarantees that the model's logic can be inspected, and that there is only one implementation of each piece of it.
Outputs reference calculations, never inputs directly.	Guarantees a single, tested path from assumption to result.
Units are stated once and applied consistently.	Prevents silent unit errors that produce plausible-looking but wrong numbers.
Documentation is a first-class deliverable.	Ensures the assumptions, sources, and dependencies are captured alongside the model.
No formula contains a hard-coded number other than structural constants such as `0` and `1`.	Ensures every parameter is visible and editable at the input layer.

Formal structure of the dependency graph

The layering imposes a direction on the dependency graph. Let I be the set of input cells, C the set of calculation cells, and O the set of output cells. The rules require that:

I ← (no dependencies)
C ← I ∪ C
O ← C

In words: inputs depend on nothing, calculations depend only on inputs and other calculations, and outputs depend only on calculations. This structure guarantees that the dependency graph is acyclic and that its direction is always downward — from inputs, through calculations, to outputs.

Figure 1.6. Anatomy of a three-layer model. The dependency direction is strictly downward: inputs feed calculations, calculations feed outputs. Documentation runs alongside all three layers and records the assumptions and sources that make the model interpretable.

5 Worked example

Laying out a compound-interest model

Suppose we want to model the future value of a savings account with an initial deposit, a monthly contribution, an annual interest rate, and a compounding period. The model is simple enough that its layout might seem obvious, but the same architecture applies at every scale.

Step 1 — List the inputs.

Initial deposit (currency units)
Monthly contribution (currency units per month)
Annual interest rate (per year, as a decimal)
Compounding frequency (times per year)
Time horizon (years)

Each of these is a parameter of the model. They are entered in the input layer, each in its own labelled cell, each with its unit stated in the header.

Step 2 — State the assumptions.

Interest is credited at the stated compounding frequency.
Contributions are made at the end of each month.
The interest rate is constant over the horizon.
No fees, taxes, or withdrawals.

These assumptions are not part of the model's arithmetic, but they determine what the model's output can be used to claim. They belong in the documentation layer, visible alongside the inputs.

Step 3 — Build the calculation layer.

The calculation layer converts the annual rate and compounding frequency into a periodic rate, builds a time series of periods, and applies the recurrence

balance[t] = balance[t−1] · (1 + r_periodic) + contribution

Every quantity referenced in this formula — r_periodic, contribution, and the previous balance — is either an input cell or another calculation cell. The recurrence is written once and filled down the time index; no formula contains a hard-coded number.

Step 4 — Build the output layer.

The output layer presents the final balance, the total contributions, the total interest earned, and a chart of the balance against time. Each output cell references the calculation layer directly. None recomputes the recurrence or references the inputs.

Step 5 — Document.

The documentation layer records: the source of each parameter value, the units, the assumptions, the software version, and a short note explaining what the model can and cannot be used to claim. For this model, the claim-support scope is something like: "This model estimates the future value of a savings account under the stated assumptions; it does not model variable interest rates, and it should not be used to compare savings products with different fee structures."

This is the same three-layer structure that appears in every model in the course. The only thing that changes at larger scales is the number of rows in the calculation layer.

6 Verification check

Verification asks whether the implementation matches the specification. For a spreadsheet model, this begins with structural checks on the layout and then proceeds to numerical checks on the calculations.

Checks to perform

Trace every reference from each output back to inputs. Every output cell should lead, through the calculation layer, to inputs, with no shortcuts. If a path exists from an output directly to an input, the architecture has been violated.
Check for hard-coded constants in formulas. Every number in a formula other than a structural constant (0, 1, or an index) should be an input cell reference or a named range.
Confirm the first and last row of every filled column. The most common spreadsheet error is a relative reference that behaves correctly in the middle of a range and wrongly at the edges.
Check for circular references. The architecture makes them structurally impossible, but they can still arise if a calculation refers to an output cell by mistake.
Test a known case by hand. Compute a small instance — say, three periods of the recurrence — independently of the spreadsheet and compare. This tests the formulas, not the layout.
Change an input and re-verify. Modify one input cell, recompute, and confirm that the outputs respond in the direction and magnitude you expect. This tests the dependency graph.

7 Interpretation

The three-layer architecture is often presented as good practice. What it actually does is make the model legible to a reader who did not build it. This is the property that determines whether the model can be reviewed, reused, or trusted.

Three consequences follow, and each is a reason the architecture matters beyond aesthetics:

Review is possible. A reader can follow every formula from input to output. The most common review question — "where does this number come from?" — has a one-step answer for every cell.
Sensitivity analysis is possible. Because every parameter lives in one place, varying it is a single edit. This makes the sensitivity analyses required by the course's later modules tractable rather than tedious.
Reuse is possible. A model with a clean architecture can be adapted to a new dataset, a new parameter set, or a new horizon without rewriting the logic. A model without one cannot.

The audit question

A useful test is to ask, of any cell in the model: "If I changed this value, what would change downstream, and why?" If the answer is not immediately visible from the layout, the model fails the audit.

8 Uncertainty and limitations

The architecture is a tool for managing a specific class of risk: the risk that the model's implementation does not match its specification in a way that is not visible from the output. It does not manage every risk, and it is important to be clear about what it does and does not address.

What the three-layer architecture does and does not address
Risk	Addressed?	Notes
Wrong cell references	Yes	The dependency graph makes incorrect references visible.
Hard-coded constants buried in formulas	Yes	The rule against constants exposes them at the input layer.
Inconsistent units	Partially	Stating units in headers helps; the architecture does not enforce dimensional consistency.
Wrong formulas	No	A formula can be visible and still incorrect. Verification, not architecture, catches this.
Wrong model form	No	Architecture says nothing about whether the model represents the right system.
Wrong parameter values	No	Estimation and calibration, covered in Lesson 2, are the relevant activities.
Uncertainty in the result	No	Reporting uncertainty is a separate discipline, addressed in later modules.

The last four rows are the ones that matter most. A transparent model is not thereby a correct model, and a well-architected spreadsheet is not thereby a validated one. Architecture is a precondition for verification, not a substitute for it.

Transparency is not correctness

A visible formula can still reference the wrong cell, use the wrong units, or copy incorrectly down a column. Transparency makes such errors findable; it does not make them absent.

9 Common implementation errors

Watch for these

Hard-coded numbers inside formulas. A constant typed into a calculation cannot be found, varied, or documented. Move it to the input layer.
Outputs that recompute. An output cell that repeats a calculation rather than referencing the calculation layer creates a second, untested path from assumption to result.
Mixed units within one column. Convert once, at the input layer, and state the unit in the header.
Filling a formula down a column without checking the first and last row. Absolute and relative references behave differently at the edges of a range. The error is often invisible from the middle.
Overwriting a formula with a value to "fix" a problem. This breaks the dependency graph silently. If a calculation is wrong, fix the formula; do not replace it with a hard-coded number.
Adding a new input without registering it in the input layer. Every parameter used anywhere in the model must appear once, in the input layer. A parameter introduced in the calculation layer is invisible to sensitivity analysis.
Documentation added at the end. The decisions that matter most are made in the moment. Document as you build.

10 Independent modelling task

Your turn

Choose a simple quantitative model you already understand — compound interest, a discounted cash flow, a weighted average, a simple physical relation.
Build it as a three-layer spreadsheet. Inputs on one tab, or in one clearly labelled region, with units stated in the headers. Calculations in a second region, with the recurrence or formula written once and filled down. Outputs in a third region, referencing the calculations.
Add a documentation region. Include: the source of each input value, the assumptions, the units, and one sentence stating the model's claim-support scope.
Change one input by 10 percent and observe the effect. Confirm that the outputs respond in the direction and magnitude you expect. This tests the dependency graph.
Audit your own model against the six rules of the architecture. Identify at least one place where you were tempted to violate a rule, and explain why you did or did not.

This spreadsheet is the basis for Laboratory 1 and will feed the architecture component of Assignment 1.

11 Reflection prompt

Reflect before continuing

Think of a spreadsheet you have built or used that was difficult to understand. What specifically made it difficult? Which of the six rules did it violate?

Then consider the reverse: a spreadsheet that was easy to understand. What made it so? Two or three sentences. The point is to sharpen your sense of what makes a model legible.

12 Lesson summary

Key takeaways

The three-layer architecture separates inputs, calculations, and outputs. Each layer has a distinct role and a distinct rule for what it may contain.
The dependency graph flows strictly downward. Inputs depend on nothing; calculations depend on inputs and other calculations; outputs depend on calculations.
Every parameter lives in exactly one place. This is what makes sensitivity analysis tractable and reuse possible.
Documentation is a deliverable, not a comment. It records assumptions, units, sources, and the model's claim-support scope.
Transparency is not correctness. The architecture makes errors findable; it does not prevent them. Verification is a separate activity.

Coming next

Lesson 2 turns to the numbers that go into the model. Parameter estimation and calibration determine whether the model reproduces observed behaviour — and how much confidence the resulting estimates deserve.

§ Self-quiz, practice, and homework

Practice exercises

Sketch (on paper or in a tool) the three-layer layout for a model that computes the monthly payment on a loan given principal, annual rate, and term in years.
For the same loan model, list every input, every calculation, and every output. Identify any hard-coded constants that should be moved to the input layer.
Take an existing spreadsheet you have built and label each region as input, calculation, or output. Identify at least two violations of the architecture rules.
Write documentation for a simple model of your choice. Include: the source of each input value, the units, the assumptions, and a one-sentence claim-support statement.

Homework problems

Architecture rebuild. Choose a spreadsheet you have previously built that does not follow the three-layer architecture. Rebuild it in a new file following the rules exactly. Submit both the original and the rebuilt version, plus a 500-word document listing every violation in the original and how you resolved it.
Audit report. Choose a publicly available spreadsheet model (many government and NGO sites publish them). Produce an audit report that: describes the model, checks it against each of the six rules, identifies violations, and recommends specific corrections. 800–1000 words.
Documentation template. Design a documentation template for a three-layer model. The template should include: a table of inputs (with source, unit, and last update), a list of assumptions, a description of each calculation region, a list of outputs, and a claim-support statement. Submit the template as a spreadsheet.

Mini project

Build a three-layer spreadsheet model of a system you know — a household budget, a savings projection, a business process. The model must have clearly separated inputs, calculations, and outputs, with a documentation sheet. Submit the file and a one-page summary explaining each modelling decision. This is the direct basis for Laboratory 1 and part of Assignment 1.