How to Identify Spreadsheet Business Rules Before ERP Migration

Operational spreadsheets often contain far more than data. They can hold formulas, calculations, approvals, status logic, exceptions and dependencies that have quietly become part of how the business actually works.

When an organisation moves from spreadsheets into an ERP, CRM or new application, these rules can easily be overlooked during discovery.

A migration team may understand what data needs to move without fully understanding:

• how values are calculated
• which conditions drive decisions
• which outputs people rely on
• how different sheets depend on one another
• which rules require business-owner approval
• what needs to be preserved, redesigned or retired

What does this spreadsheet actually do, what depends on it, and which business rules must be preserved?

What is a spreadsheet business rule?

A business rule is logic that determines how a process, calculation, decision or output should behave.

In a traditional application, these rules may exist in application code, workflow configuration, validation rules, database logic, process documentation or system configuration. In spreadsheet-driven processes, the same logic may instead be embedded inside Excel.

Examples include:

• Net Price = List Price - Discount
• an order requires approval when its value exceeds a threshold
• a vehicle is marked for review when maintenance cost exceeds a limit
• commission changes according to customer type
• stock warnings appear when inventory falls below a minimum
• a dashboard metric is calculated from several other worksheets

The formula itself is only part of the requirement. The underlying business rule may be:

Apply the approved customer discount to the current list price when calculating the net selling price.

Why business rules hidden in spreadsheets matter during ERP migration

Requirements can be missed

A workbook may contain calculations or conditions that users consider obvious because they have worked with them for years. Those rules may never appear in formal process documentation, so the implementation team can build the new process without them.

Existing behaviour can be unintentionally changed

A formula may look simple while depending on several other fields, worksheets or calculations. Recreating only the visible output can result in different behaviour in the new system.

Important decisions may depend on undocumented calculations

Pricing, costing, forecasting, operational planning and reporting spreadsheets can directly influence business decisions. If the calculation logic is not understood, the migration team may not know which outputs require additional validation.

UAT may start without clear expected results

Users are often asked to test a new system without a structured record of the original spreadsheet rules. Documented spreadsheet business rules can provide a starting point for migration validation and UAT.

Where spreadsheet business rules are commonly found

Formulas and calculations

Pricing calculations, margins, tax calculations, commissions, cost allocations, utilisation rates, financial ratios and operational KPIs can all represent business logic.

Conditional logic

Functions such as IF, IFS, AND and OR can represent decisions and process rules.

Lookup and reference logic

Functions such as XLOOKUP, VLOOKUP, INDEX and MATCH may connect transactions to pricing tables, product information, customer classifications, employee information or other reference data.

Cross-sheet dependencies

Changing one part of that chain can affect business-facing outputs elsewhere.

Status and approval logic

Fields such as Draft, Submitted, Approved, Rejected, On Hold and Complete may indicate that the workbook contains an informal approval or operational workflow that needs to be considered during system design.

Manual adjustments

Users may manually override prices, costs, forecasts, quantities, approval statuses or exceptions. Those adjustments need to be understood because the target system may require an explicit override process, permission model or approval workflow.

From spreadsheet formula to migration requirement

Spreadsheet logic:
TotalCost = FuelCost + MaintenanceCost + ManualAdjustment

Business rule:
Total vehicle cost must include fuel expenditure, maintenance expenditure and approved manual adjustments.

Migration question:
Should manual adjustments continue to be permitted in the target system, and who should be authorised to make them?

UAT requirement:
Verify that the target system produces the expected total cost when fuel, maintenance and approved adjustment values are supplied.

How many business rules can exist in one workbook?

Potentially far more than expected. A workbook may contain hundreds of formulas, repeated calculations and conditional expressions.

One IC-Flow demonstration workbook contains:

This does not mean a business user should manually review hundreds of individual Excel cells. The important task is to identify, classify and prioritise the logic so that implementation teams can focus on the rules and outputs that matter.

A Business Rule Register makes spreadsheet logic reviewable

Rather than leaving business logic inside Excel, it can be converted into a structured Business Rule Register.

• Rule ID
• Rule name
• Rule type
• Source worksheet
• Source formula or evidence
• Business meaning
• Risk
• Confidence
• Migration readiness
• Business owner required
• Sign-off required
• Validation question
• Target-system consideration

Not every spreadsheet rule should be migrated

Preserve

The rule is still required and should behave consistently in the target system.

Redesign

The business requirement remains valid, but the spreadsheet implementation should be replaced with a better system process.

Automate

A manual spreadsheet activity may become a workflow, calculation or integration.

Clarify

The rule cannot be safely migrated until a business owner confirms its purpose.

Retire

The rule no longer reflects the current business process and should not be reproduced.

Business owners still need to validate the rules

Automated analysis can identify and structure spreadsheet logic, but it should not replace business ownership.

• whether a rule is current
• whether the interpretation is correct
• whether exceptions exist
• who owns the process
• whether the rule should survive migration
• whether it should be redesigned
• what the expected output should be

Business rules can improve UAT

Business rule:
Maintenance cost must include labour and parts but exclude costs covered by warranty.

Suggested validation scenario:
Create one maintenance record containing labour and parts, and one maintenance record covered by warranty. Confirm that the target system calculates the expected cost in both cases.

Spreadsheet versions should also be considered

Business rules may change while an ERP implementation is underway. A team might complete discovery using Pricing_Model_v4.xlsx while users later start working from Pricing_Model_v7_FINAL.xlsx.

The workbook may still contain the same worksheets, the same number of formulas and the same visible outputs while the formulas themselves have changed.

• changed formulas
• added or removed logic
• structural changes
• potential business impact
• areas requiring regression testing

What should an ERP project capture from important spreadsheets?

1. Workbook purpose — What business process does the workbook support?
2. Key inputs — Which information enters the process and where does it come from?
3. Important outputs — Which results support customer, financial or operational decisions?
4. Formulas and calculations — What calculations produce those outputs?
5. Dependencies — Which worksheets, fields and calculations depend on one another?
6. Business rules — What business meaning sits behind the spreadsheet logic?
7. Owners — Who can confirm that each important rule is correct?
8. Migration decisions — Should each rule be preserved, redesigned, automated or retired?
9. Validation tests — How will the implementation team confirm that important behaviour has been preserved?
10. Target-system considerations — Where might the rule belong in the ERP, CRM, workflow platform or application?

How IC-Flow supports spreadsheet business-rule discovery

• Workbook structure: sheets, fields, formulas and important outputs
• Dependencies: relationships between formulas, fields and worksheets
• Business rules: detected spreadsheet logic converted into a structured Business Rule Register
• Migration readiness: rules requiring review, clarification, ownership or sign-off
• Validation evidence: suggested questions and tests that can support UAT and stakeholder review
• Target-system planning: guidance on how spreadsheet-derived requirements may need to be represented in ERP, CRM, low-code or custom applications
• Evidence Pack: a structured report that can support discovery, migration planning, review and handover

Before migrating the spreadsheet, understand the process inside it

What does this workbook do?
What depends on it?
What changed?
What must be preserved?