How Spreadsheet Analysis Can Support UAT Before ERP Migration

User Acceptance Testing is supposed to confirm that a new ERP, CRM or application supports the way the business needs to operate. But when important requirements still live inside spreadsheets, UAT can begin without a clear record of the behaviour that needs to be preserved.

Operational workbooks can contain formulas, calculations, thresholds, dependencies, approvals, manual adjustments and business-facing outputs that have evolved over years.

If those rules are not understood before migration, project teams may struggle to answer a basic UAT question:

What result should the new system produce, and what existing business rule are we validating it against?

Why spreadsheets matter to UAT

A spreadsheet may act as an informal application long before an ERP implementation begins.

It may already define:

• how prices are calculated
• how costs are allocated
• which conditions trigger review
• how statuses are determined
• which values feed reports
• which exceptions users handle manually

These behaviours can become implicit acceptance criteria for the new system, even if they have never been documented formally.

UAT should test business behaviour, not just screens

A new ERP screen can look correct while producing a different business result.

That is why UAT should not focus only on whether:

• a user can open a form
• a field accepts data
• a workflow button works
• a report can be generated

It should also validate whether important calculations, decisions and outputs behave as expected.

From spreadsheet formula to UAT scenario

A spreadsheet formula can provide useful evidence for a test scenario when its business meaning is understood.

This creates traceability between the existing spreadsheet and the new application.

Business Rule Registers can provide UAT starting points

A Business Rule Register can help turn spreadsheet logic into structured review and validation material.

For each important rule, a project team may capture:

• Rule ID
• Rule name
• source worksheet
• source formula or evidence
• business meaning
• risk
• business owner
• migration readiness
• sign-off requirement
• validation question
• suggested UAT test

This makes it easier to connect discovery evidence to implementation testing.

What types of spreadsheet logic can become UAT tests?

Calculation rules

Where a spreadsheet calculates totals, prices, costs, margins, commissions or other outputs, UAT can compare expected results against known examples.

Conditional rules

Where a spreadsheet contains branches such as IF/THEN logic, testing should cover the important condition paths.

Reference and lookup rules

Where a spreadsheet retrieves values from reference tables, UAT should confirm that the target system uses the correct source data and returns the expected result.

Approval and status rules

Where workbook fields represent statuses such as Draft, Submitted, Approved or Rejected, testing can verify the equivalent workflow behaviour in the target system.

Business-facing outputs

Reports, dashboards, pricing outputs and operational measures should be tested against accepted business examples.

Manual adjustments

If users currently override values manually, UAT should confirm whether the future process allows, restricts, approves or removes that behaviour.

Example: turning workbook evidence into validation questions

IC-Flow's Migration Readiness Assessment can generate validation questions for detected business-facing rules and outputs.

For conditional logic, the recommended test approach can instead focus on testing each condition branch and confirming the expected outcome.

Why business-owner involvement still matters

Automated analysis can help identify spreadsheet logic and suggest validation scenarios, but the business owner still needs to confirm what is correct.

The project team may need to ask:

• Is this rule still current?
• Is the existing spreadsheet behaviour actually correct?
• Are there exceptions not visible in the formula?
• Should the target system reproduce the rule exactly?
• Should the process be redesigned?
• Who should approve the final behaviour?

UAT should validate the intended business process, not blindly reproduce every historical spreadsheet behaviour.

Spreadsheet analysis can help identify higher-priority UAT areas

Not every formula needs the same level of testing.

Priority can be given to logic connected to:

• customer-facing outputs
• financial calculations
• pricing
• costing
• operational decisions
• deep dependency chains
• business-owner sign-off
• changed formulas

This helps testing teams focus on business impact rather than treating every spreadsheet cell as equally important.

Workbook dependencies can improve UAT coverage

If an important output depends on several upstream values, testing only the final screen may miss problems in the dependency chain.

Understanding the chain helps project teams decide which inputs and intermediate behaviours should be included in the test.

Workbook version changes can trigger regression testing

UAT preparation should also consider whether the source workbook has changed since discovery.

A newer version may have the same:

• number of sheets
• number of formulas
• visible outputs

while containing changed formula logic.

In one IC-Flow workbook comparison example, both versions contained 271 formulas, but detailed analysis still detected 7 formula changes.

Those changes can provide a reason to review affected outputs and repeat relevant validation tests.

Spreadsheet analysis can strengthen requirements traceability

One of the most useful outcomes is being able to trace a test back to why it exists.

That chain can help consultants, project managers and business owners understand:

• where the requirement came from
• why the test matters
• who should validate it
• which business output is affected

What should a migration team capture before UAT?

1. Key business outputs — Which results must remain correct?
2. Business rules — Which calculations and conditions govern those outputs?
3. Dependencies — Which upstream values influence the result?
4. Business owner — Who can confirm the intended behaviour?
5. Expected result — What should the new system produce?
6. Test data — Which examples should be used?
7. Edge cases — Which thresholds, exceptions or branches need testing?
8. Migration decision — Was the rule preserved, redesigned, automated or retired?
9. Target-system location — Where is the rule now implemented?
10. Sign-off evidence — Who approved the result?

How IC-Flow supports migration validation and UAT preparation

IC-Flow is designed to analyse operational spreadsheets before ERP, CRM or application transformation.

Its Migration Readiness Assessment can help produce:

• workbook structure analysis
• key input and output identification
• formula and dependency analysis
• Business Rule Register
• rules requiring review
• business-owner and sign-off requirements
• migration-readiness findings
• suggested validation tests
• target-system notes
• Evidence Pack outputs for review and handover

The generated tests are intended to support project teams and business owners in planning validation. They do not replace business acceptance or implementation-specific test design.

UAT becomes stronger when discovery evidence survives into testing

One common weakness in transformation programmes is the gap between discovery and testing.

Requirements may be discussed early in the project, while UAT happens months later with limited traceability back to the original spreadsheet process.

Structured workbook evidence helps keep that connection visible.

The goal is not merely to prove that the new system works. It is to prove that the important business behaviour still works.

Before ERP sign-off, know what you are testing against

What does this workbook do?
What depends on it?
What changed?
What must be preserved?
How will you prove it still works?

IC-Flow helps turn undocumented spreadsheet logic into structured evidence that can support discovery, migration review, UAT and handover.