From spreadsheet to platform: keep the model explainable

Define what needs to improve

Spreadsheets are useful because assumptions and calculations can sit close together in a familiar format. A platform can help when several people need a consistent project record, repeatable calculations or clearer review responsibilities. Neither format removes the need for sound judgement. Begin by identifying the costly failure in the current process: conflicting versions, repeated manual entry, unclear ownership, slow comparisons or difficulty explaining a result.

Write a small set of observable requirements. For example, a reviewer should be able to identify which sales assumptions changed since approval, and the team should be able to reproduce the previous funding forecast. ICAEW recommends assessing whether a spreadsheet is suitable for its purpose and managing important workbooks according to their risk. A migration should respond to a defined need, rather than the age of the existing file.

Sources: ICAEW: 20 principles for good spreadsheet practice, 2024 edition

Inventory the model people actually use

Choose an agreed workbook version and preserve it unchanged as the comparison baseline. Catalogue its input sheets, outputs, external links, named ranges, hidden areas, macros and manual overrides. Ask the current owner to demonstrate a complete update, including the steps performed outside the workbook. An emailed cost allowance or a manual adjustment in a presentation may be part of the real process even though it is absent from the formula graph.

Record the purpose and owner of each important output. Separate the approved case from experiments and identify which external records support the assumptions. A structured review can then examine data, logic and whether the outputs make sense. ICAEW distinguishes data, formula, process and communication errors; importing a workbook successfully does not establish that the underlying appraisal has been validated.

Sources: ICAEW: How to review a spreadsheet

Translate meaning before transferring values

Create a mapping that states what each transferred value means, its unit and its basis. A construction rate per square metre is incomplete without the relevant area definition and scope. Revenue may mean contracted sales, recognised revenue or cash collected. A percentage may apply to hard costs, total development cost or gross receipts. Agree these definitions before deciding which source cell maps to which destination field.

Timing conventions deserve the same attention. Record whether flows occur at month start, month end or actual dates, and distinguish annual rates from period rates. Microsoft documents XIRR for dated flows that are not necessarily periodic, whereas IRR is for periodic flows. Two systems can therefore disagree because their conventions differ even when both calculations are internally consistent. The migration record should expose that difference rather than forcing a match through an unexplained adjustment.

Sources: Microsoft: XIRR function

Reconcile from inputs through to returns

Start with quantities and rates, then compare revenue, costs and the period cash schedule. Only after these reconcile should you compare financing, investor distributions and return measures. Use explicit tolerances appropriate to the unit and calculation. Rounding a displayed total can hide an underlying timing difference, while an immaterial decimal difference can distract from a missing cost category.

Keep a discrepancy log containing the source result, destination result, difference, explanation and resolution. Include cases that expose boundaries: no sales in an early period, a delayed completion, a repayment near the end of the model and a revised unit mix. An unexplained difference is an open issue, not evidence that the newer system is wrong or right. The reference workbook may also contain errors that should be corrected openly in both records.

Consider a sample migration with costs of 10, 20, 15 and 5 million across four quarters. The workbook collects 0, 10, 20 and 30 million; the transferred model collects 0, 0, 30 and 30 million. Both show 60 million of receipts, 50 million of costs and 10 million of surplus. Yet their largest quarter-end deficits are 20 million and 30 million respectively. A receipt has moved, and comparing totals alone misses it.

Record the quarter 2 receipt difference as -10 million and quarter 3 as +10 million, measured as destination less source. Suppose inspection finds that the importer mapped a due date to a later handover date. The source payment schedule supports the earlier instalment, so the modeller corrects the mapping and reruns both period schedules. If the source date had instead proved wrong, the approved correction would belong in the workbook and the destination. Do not offset either difference with a balancing entry.

• Reconcile quantities, area bases and currency before monetary totals.
• Compare cash by period before comparing a cumulative balance.
• Explain changes in calculation conventions separately from data corrections.

Define a controlled boundary with Excel

A migration does not have to remove spreadsheets from every task. A team may still use them to collect a cost schedule, inspect an export or prepare a bespoke analysis. Decide which system owns each assumption and where a reviewer authorises a change. If values travel in both directions, identify the originating project and version so an old worksheet cannot silently overwrite a more recent decision.

Write down the actual exchange contract: supported fields, units, blank-value behaviour, validation rules and what happens to formulas or unsupported rows. Distinguish importing values from executing an arbitrary workbook. Pilot the complete round trip with representative data and rejected inputs. A clear handoff is more valuable than the word integration, which can describe anything from a manual export to a live connection.

Make review and ownership explicit

Assign responsibility for the model specification, assumption updates and approval of published results. Preserve an approved snapshot with its supporting documents, review notes and date. The next person should be able to establish what changed and why without asking the original modeller to reconstruct a conversation. A record of changes is useful only when it connects to the result that was actually issued.

The AQuA Book distinguishes verification, checking that analysis meets its specification, from validation, checking that it is fit for its intended purpose. Apply both during migration. Test that a cost escalation is calculated correctly, then challenge whether the chosen escalation assumption is appropriate. Confirm that exports, reviewer access and recovery arrangements work in practice. A good calculation surrounded by an unreliable handoff can still lead to a poor decision.

Sources: UK Government: The AQuA Book

Cut over after a real review cycle

Run a complete reporting cycle in parallel before making the new environment the operational reference. Include a genuine assumption change, a reviewer challenge and the final approved output. Resolve material discrepancies and record any intentionally different treatment. Keep the prior version accessible for comparison, but make the current source of authority unambiguous so the team does not continue issuing competing forecasts.

Measure the result against the original requirements: can another modeller reproduce the case, can a reviewer trace the revision, and can the team explain the funding movement? Continue to refine the process after the first project. The goal is a model that remains understandable as people, assumptions and project stages change, with spreadsheets retained wherever they serve that goal well.