Data Reconciliation for Spreadsheets

Techniques for recovering missing values from financial and tabular data using mathematical constraints.

Core Principles

1. Row/Column Sum Constraints

When totals are provided, missing values can be recovered:

Missing = Total - Sum(Known Values)

Example: If a row sums to 1000 and you know 3 of 4 values (200, 300, 400), the missing value is:

Missing = 1000 - (200 + 300 + 400) = 100

2. Year-over-Year (YoY) Change Recovery

When you have percentage changes between periods:

Current = Previous × (1 + YoY_Change/100)

To recover a previous value from current:

Previous = Current / (1 + YoY_Change/100)

3. Percentage Share Recovery

When you know a value's share of the total:

Value = Total × (Share/100)

Example: If total budget is 50000 and a department's share is 20%:

Value = 50000 × 0.20 = 10000

4. Compound Annual Growth Rate (CAGR)

For multi-year growth analysis:

CAGR = ((End_Value / Start_Value)^(1/years) - 1) × 100

Example: If Year 1 was 1000 and Year 5 is 1500 (4 years of growth):

CAGR = ((1500/1000)^(1/4) - 1) × 100 = 10.67%

To recover a start or end value:

End = Start × (1 + CAGR/100)^years
Start = End / (1 + CAGR/100)^years

5. Cross-Validation

Always verify recovered values:

• Do row totals match column totals?
• Are percentage shares consistent?
• Do YoY changes recalculate correctly?

Recovery Strategy

1. Identify constraints: What mathematical relationships exist?
2. Find solvable cells: Which missing values have enough information?
3. Solve in order: Some values may depend on others (chain dependencies)
4. Validate: Check all constraints still hold

Chain Dependencies

Sometimes you must solve values in a specific order:

• Recover budget value A from Sheet 1
• Use A to calculate YoY percentage in Sheet 2
• Use that percentage to verify or calculate another value

Always map out dependencies before starting.

Common Patterns in Budget Data

Constraint Type	Formula	When to Use
Sum to total	Missing = Total - Σ(known)	Missing one component
YoY forward	New = Old × (1 + %/100)	Know previous + change
YoY backward	Old = New / (1 + %/100)	Know current + change
Share of total	Part = Total × share%	Know total + percentage
CAGR	((End/Start)^(1/n)-1)×100	Multi-year growth rate