Moving from Spreadsheets to Structured S106 Tracking

Your council has tracked S106 obligations in spreadsheets for years. They contain real data — agreement references, obligation amounts, trigger dates, payment records. Replacing them isn't a technology decision. It's a data migration.

This guide covers the practical steps for moving from Excel-based S106 monitoring to structured tracking — whether you're adopting dedicated software, building a database, or restructuring your spreadsheets into something more maintainable.

This is general guidance, not a product recommendation or legal advice.

Why Spreadsheets Break at Scale

Spreadsheets work for S106 monitoring until they don't. The failure point is usually between 50 and 100 live agreements. Common symptoms:

• Version conflicts. Two officers update the same file. One overwrites the other's changes. Neither notices until an IFS discrepancy surfaces in December.
• Structural inconsistency. The officer who built the spreadsheet left three years ago. New obligations get recorded in a different format. Half the formulas are broken.
• No audit trail. A contribution amount changes from £150,000 to £175,000. When? Who changed it? Was it an indexation calculation or a correction? The spreadsheet doesn't say.
• No alerting. Conditional formatting can highlight rows with approaching deadlines — if someone opens the file and notices. Spend-by deadlines don't announce themselves.
• Flat data model. Spreadsheets are rows and columns. S106 monitoring needs a hierarchical structure: agreements contain obligations, obligations have triggers and financial terms, triggers link to development milestones. Forcing this into a flat table creates either very wide rows (30+ columns) or multiple linked sheets that break when someone renames a tab.

The frustration described by officers at Bristol City Council (700+ agreements across multiple spreadsheets — "not efficient or effective") and Wokingham Borough Council ("years of unsatisfactory work with Excel spreadsheets and duplication") on the PAS Knowledge Hub reflects a structural problem, not a skill problem. These are competent officers using the wrong tool.

Before You Migrate: Audit Your Current Data

Don't move bad data into a new system. Start by auditing what you have:

Step 1: Inventory Your Spreadsheets

List every file involved in S106 tracking:

• Main S106 register (the "master" spreadsheet)
• Financial tracking sheets (contributions received, allocated, spent)
• Monitoring sheets (trigger tracking, compliance checks)
• IFS compilation sheets
• Archive files (completed agreements)
• Any personal tracking files maintained by individual officers

You may find data in places you didn't expect. Officers often maintain personal copies when the shared file is unreliable.

Step 2: Assess Data Completeness

For each live agreement, check whether your data includes:

Required Field	Present?	Notes
Agreement reference
Site address
Agreement date
Each obligation listed separately		This is where most gaps are
Trigger type for each obligation
Financial amounts with indexation basis
Receipt dates (not just amounts received)
Spend-by periods/deadlines
Current status of each obligation

If obligations aren't listed separately — if the spreadsheet has one row per agreement with a notes column summarising the obligations — you have a significant restructuring task before any migration.

Step 3: Identify Data Quality Issues

Common problems to flag:

• Missing receipt dates (you have amounts but not when they arrived)
• Inconsistent obligation categorisation (is it "education" or "school" or "primary education"?)
• Broken formulas (especially indexation calculations)
• Duplicate records (the same agreement appears in multiple files)
• Missing agreements (signed but never added to the register)

Document these issues. They need to be resolved during migration, not after.

The Migration Process

Phase 1: Define Your Target Data Structure

Before moving any data, decide what your new structure looks like. At minimum, you need three levels:

Level 1: Agreement

• Planning application reference
• Site address
• Agreement date
• Parties
• Status (live / discharged / modified)

Level 2: Obligation

• Obligation type (financial / in-kind delivery / restriction)
• Purpose (education / highways / affordable housing / open space / etc.)
• Trigger type and milestone
• Financial terms (amount, indexation basis, base date)
• Spend-by period
• Status (pending / triggered / paid / allocated / spent / delivered / discharged)

Level 3: Transaction

• Date
• Type (trigger recorded / invoice issued / payment received / allocation made / spend recorded)
• Amount
• Reference
• Officer

This structure works whether you're building a database, adopting software, or even restructuring your spreadsheets.

Phase 2: Migrate Live Agreements First

Don't try to migrate everything at once. Start with live agreements only — those with active obligations.

For each agreement:

1. Extract each obligation as a separate record
2. Record the current status of each obligation
3. For financial obligations: record receipts, allocations, and spend to date
4. Calculate spend-by deadlines from receipt dates
5. Flag any obligations where data is missing (receipt date unknown, trigger status unclear)

Work through agreements in order of deadline urgency. Agreements with spend-by deadlines in the next 18 months are your priority.

Phase 3: Verify Against Source Documents

For any obligation where the spreadsheet data seems incomplete or contradictory, go back to the original agreement document. This is time-consuming but essential — migrating incorrect data into a new system gives you faster access to wrong information.

Priority verification:

• Financial amounts and indexation terms
• Spend-by periods (these are often in a general clause, not next to each obligation)
• Trigger wording (especially occupation thresholds — is it "prior to" or "upon"?)

Phase 4: Run in Parallel

Don't switch off the old spreadsheet on day one. Run the new system alongside it for one quarter — ideally a quarter that includes your IFS reporting period. Compare outputs. If the new system produces an IFS that matches your manual compilation, your migration is validated.

What to Do If You're Staying on Spreadsheets

If dedicated software isn't in the budget or procurement pipeline, you can still improve your spreadsheet-based monitoring significantly:

1. Restructure to obligation-level. One row per obligation, not one row per agreement. This is the single most impactful change.

2. Standardise field formats. Use a dropdown list for obligation types, not free text. Consistent categorisation makes reporting possible.

3. Add a receipt date column. If you only track amounts, add receipt dates. You can't calculate spend-by deadlines without them.

4. Create a deadline view. A filtered view showing all financial contributions sorted by spend-by deadline, highlighting anything within 18 months.

5. Lock structure, not data. Protect the column headers and formulas. Let officers edit data cells. This prevents the structural drift that makes spreadsheets unmaintainable.

6. Set up calendar reminders. For every contribution with a spend-by deadline, create a calendar event at the 18-month, 12-month, and 6-month marks. This is manual but better than nothing.

For a structured approach to deadline tracking, try the S106 Spend Deadline Calculator.

For the full monitoring process, see The Complete Guide to S106 Monitoring for Planning Officers.

Sources

• PAS Knowledge Hub — Section 106 Databases Discussion
• Town and Country Planning Act 1990, Section 106
• PAS — Infrastructure Funding Statements