Excel 365: Build an ASC 606 Contract Modification Engine That Auto-Classifies and Calculates Catch-Up Adjustments

Who this quietly bites

1. Separate contract — if the new goods are distinct AND the price is commensurate with standalone selling prices.
2. Prospective — if goods are distinct but the price isn’t commensurate.
3. Cumulative catch-up — if the goods aren’t distinct at all.

Here’s the problem: most finance teams classify these in email threads, sticky notes, or separate Word docs. The amortization schedule gets rebuilt manually in a new tab. Six months later, no one remembers why the numbers changed — and the catch-up adjustment that surprises the CFO in Q3 was actually a Q1 modification that was never properly documented.

A 2023 survey by Deloitte found that contract modifications were the #1 source of restatements in revenue recognition for software and services companies. Not new contracts. Modifications.

Why the usual formula gets it wrong

The typical Excel approach is a static amortization table. You build it at inception, lock the cells, and move on. When a modification hits, you:

• Copy the original tab
• Manually adjust the remaining periods
• Hardcode the new price
• Pray the auditor doesn’t ask for the bridge

This breaks in three ways:

1. No classification trail. The two-threshold test (distinct? commensurate?) lives in someone’s head, not in the workbook.
2. No catch-up calculation. The difference between original recognized revenue and what
3. should have been recognized gets calculated in a separate file — if at all.
4. No audit linkage. The original schedule, modified schedule, and journal entry are three separate tabs with no formula connection.

The fix, two ways

Let’s work through a real example.

The scenario: You have a $120,000 contract with 5 performance obligations, straight-line over 24 months. On July 1, 2024 (month 7), the customer adds a sixth PO and the total price increases to $150,000. The new goods are distinct, and the standalone selling price ratio is 0.95.

Option 1 — Classic (backward-compatible functions, step by step)

Step 1: Build the original schedule.

Create a table with columns: Period, Date, PO_Name, Allocated_Price, Cumulative.

In cell E3 (Allocated_Price), enter and drag down:

=120000/5

In cell F3 (Cumulative), enter and drag down:

=SUM($E$3:E3)

Step 2: Classify the modification.

In a separate cell, run the two-threshold test:

=IF(AND(B7="Yes",B8>=0.8,B8<=1.2),"Separate Contract",

IF(B7="Yes","Prospective","Cumulative Catch-Up"))

Where B7 = “Yes” (distinct goods) and B8 = 0.95 (SSP ratio).

Result: “Separate Contract” — because both thresholds pass.

Step 3: If it were catch-up (the harder case), calculate the adjustment.

Suppose the classification returned “Cumulative Catch-Up” instead. You need to reallocate the new total price ($150,000) across all 6 POs and compare cumulative recognized amounts.

Calculate remaining TPO value:

=SUMPRODUCT((Original_Date>=DATE(2024,7,1))*Original_Amount)

Calculate the catch-up:

=SUMPRODUCT((Recognized_Original-Recognized_Modified)*(Date<DATE(2024,7,1)))

This gives you the delta between what you recognized under the old assumption and what you should have recognized under the new one.

Step 4: Build the modified schedule manually.

Create a new tab. Copy the original schedule for periods 1-6. For periods 7-24, use the new allocation ($150,000 / 6 = $25,000 per PO). The difference between old cumulative and new cumulative at month 6 is your catch-up journal entry.

Option 2 — Excel 365 spill (dynamic-array equivalent)

Step 1: Auto-generate the original schedule with one formula.

In cell E3:

=SEQUENCE(5,,120000/5)

This spills $24,000 down 5 rows. No dragging.

In cell F3:

=SCAN(0,E3#E7,LAMBDA(a,b,a+b))

This spills the cumulative recognized amount: $24,000, $48,000, $72,000, etc.

Step 2: Classify with LET.

In cell D5:

=LET(distinct,B7,
    ssp,B8, 
    IF(AND(distinct="Yes", ssp >= 0.8, ssp <= 1.2),"Separate", IF(distinct="Yes", "Prospective", "Catch-Up"))
  )

LET stores your test variables so the formula reads like plain English.

Step 3: Build the modified schedule dynamically.

In cell G3 (spilled):

=LET(orig,E3#E7,mod_date,DATE(2024,7,1),
    orig_dates,C3#C7,
    completed,orig_dates<mod_date,
    orig_amt,SUM(FILTER(orig,completed)),
    remaining,150000-orig_amt,
    periods,6,
    IF(completed,orig,remaining/periods)
  )

This single formula:

• Identifies which periods were completed before the modification
• Keeps original allocations for completed periods
• Reallocates the remaining price across all 6 POs for future periods

Step 4: Calculate catch-up with SCAN.

In cell J3:

=LET(
    orig_cum,SCAN(0,E3#E7,LAMBDA(a,b,a+b)),
    mod_cum,SCAN(0,G3#G7,LAMBDA(a,b,a+b)),
    orig_cum-mod_cum
  )

This spills the period-by-period catch-up adjustment. Positive values mean you under-recognized; negative means you over-recognized. The sum is your journal entry.

Where else this pattern pays for itself

• SaaS subscription upgrades — when a customer adds seats mid-term
• Construction contract change orders — scope changes with revenue reallocation
• Professional services engagements — when SOWs get amended
• Licensing deals — when royalty rates change retroactively

This same engine works for:

Any time you have a multi-period revenue recognition schedule that changes mid-stream, this pattern saves you from rebuilding amortization tables by hand.

Download the workbook

• A classification decision tree with the two-threshold test
• Original and modified amortization schedules
• Auto-calculated catch-up journal entries
• Sample data for all three modification types (separate, prospective, catch-up)

I’ve built a complete workbook blueprint with both the classic and 365 versions side by side. The workbook includes:

Download the ASC 606 Contract Modification Workbook

Takeaways

• Contract modifications are the #1 source of revenue recognition restatements — not new contracts.
• The classic approach works with SUMPRODUCT, IF(AND()), and manual table rebuilding.
• Excel 365’s LET, SCAN, and SEQUENCE let you build the entire modification engine in four formulas.
• Your action: Audit your current revenue recognition workbooks. If modifications are handled in separate tabs with hardcoded adjustments, rebuild them using this pattern. Your auditor — and your future self — will thank you.