The column that was never collected.

Start at the output

The method is the same every time, and it runs backwards from the thing you have to produce.

Name the deliverable. List every field that appears in it. For each field, answer four questions: what exactly is it, who produces it, in what unit, and on which day does it close. Then go and look at whether the input sheet actually holds it.

Most of the time it does. The value of the exercise is the handful of fields where it doesn't, because those are the outputs you are currently producing by estimating, by asking somebody informally, or by not producing at all.

Do it once for the monthly report and you will find two or three. That is a normal result and a useful one.

The same field, three units

The second thing the exercise finds is subtler than a missing column, and harder to see: a field that exists but doesn't mean the same thing twice.

Pipework reported by one crew in metres and by another in joints. Concrete recorded as poured by the site and as delivered by the store. Hours entered by some supervisors as attended and by others as worked. Each entry is defensible. Aggregated, they produce a total that is not a quantity of anything.

This doesn't show up as an error, which is why it survives. Every row looks reasonable. It shows up as a productivity figure that moves for no reason, or a curve that behaves oddly in one area and not another.

So the unit is part of the field definition rather than a note beside it. If a column can be filled in two ways, it will be, and the correction is made once in the sheet rather than every month in the analysis.

You won't be designing this from scratch

Most planners arrive to a system that already exists. The coding structure was set before you got there. The input sheets are in use. Thirty reports are built on top of them, and people are used to filling them in.

So the realistic version of this work is not designing a model. It is understanding the one you inherited well enough to find its gaps, and adding what is missing without breaking what already works.

That second half is where the difficulty sits, and it has almost nothing to do with the data.

A data table is an interface

Here is the part that is easy to get wrong and expensive to get wrong.

Once a table has been in use for a while, the person opening the file is not its only user. Reports read it. A dashboard reads it. Somebody's automated refresh reads it. A colleague's calculation points at a particular column by position. None of that is visible from inside the file.

Which means the shape of the table has quietly become a contract. Insert a column in the middle and everything downstream that referenced positions is now reading the wrong thing, and it won't announce itself. It shows up two weeks later, in somebody else's output, as a number that looks slightly odd.

So the rule is not that tables cannot change. It is that they change in one direction. New fields are added at the end. Existing columns keep their position and their meaning. If a column has to be retired, it is emptied rather than deleted, at least until you know what was reading it.

This is not a spreadsheet rule and it doesn't depend on which software you use. It is what happens to any shared data source once more than one thing consumes it.

When the field cannot be added

Sometimes the answer is that it cannot be collected. The work is finished, or the crew has demobilised, or capturing it would take longer than the output is worth.

The honest response is to say so rather than to derive it. A figure assembled from a proxy, published without saying it was assembled from a proxy, is exactly the unmeasured number the rest of this track is about — except that this time you produced it yourself.

Report what you have, mark what you don't, and add the field going forward. Nobody is harmed by a gap they were told about. They are harmed by a number that looked like the others.

What stays still and what moves

There are two layers here and they change at different speeds.

Reports change constantly. A new manager wants a different summary. A client asks for another chart. The monthly gets restructured. All of that is normal and none of it is expensive.

The data source underneath should be close to stationary. It is the one thing everything else depends on, and every change to it costs something somewhere you cannot see.

Most reporting problems come from doing this backwards: leaving the collection alone because it is working, and repairing the symptom in each report as it appears. That is how a project ends up with the same correction made in six places and still wrong in a seventh.

System design

A field specification, not a spreadsheet. Four questions per field, and the discipline is that new fields go at the end of the table rather than into the middle of it.

Record	Produced by	Required quality	Verified against	Feeds
Installed quantity	Site engineer	One unit per field, defined once and not per person	Survey	Progress
Area or system	Site engineer	A coded value, not free text	Area coding	Productivity by area
Activity reference	Site engineer	A schedule ID that exists	Schedule	Progress, earned value
Hours	Supervisor	Allocated against the same activity list	Attendance	Productivity
Drawing revision built to	Site engineer	Recorded at the time, not reconstructed	Transmittal register	Progress validity

Column order is fixed once the table is in use. Anything added later goes on the right, keeps its own owner, and doesn't move what is already there.

Practical insight

Take the deliverable you are most often asked to produce off-cycle — the one somebody requests at short notice, that always takes longer than it should.

List its fields and check each against the input sheet. The reason it takes so long is almost always that one or two fields are not collected, and you have been assembling them by hand every time.

Then decide whether to add them. Not into the middle of the sheet: at the end, with a name, a unit, and somebody who owns filling it in. It will be empty for the first month, which feels like failure and is not. From the second month you stop doing that assembly by hand, permanently.

Key takeaways

• A field that was never collected cannot be recovered by redesigning the report.
• Work backwards: name the output, list its fields, and for each ask what, who, what unit, and which day it closes.
• The useful result is the two or three fields the input sheet doesn't hold.
• You will inherit a data model far more often than you will design one.
• A table in use is an interface. Reports, dashboards and other people's calculations read it, and none of them are visible from inside the file.
• Change tables in one direction: new fields at the end, existing columns keep their position.
• Reports change often and cheaply. The source underneath should be close to stationary.

Records born here. Field specification · the input sheet itself · the list of fields a report needs and the sheet doesn't hold.

What is coming next

From here the track goes department by department, in the order the data reaches you. The first is the one whose output looks least like data.

Next week: engineering — deliverable lists, issued-for-construction dates, and the drawing that arrives approved with comments.