Audit Royalty Calculations Against Contract Terms

A platform sends a royalty statement. Your accounting ingests it, pays the licensor their share, and files it. Nobody ever asks: “was the statement right in the first place?” When someone finally does — usually because a licensor complains about underpayment — reconstructing the expected amount requires reading the contract, cross-referencing the statement, applying splits, accounting for MG recoupment phase, and checking holdbacks. Per title. Per territory. Per period.

This tutorial builds a repeatable variance audit. You load contract terms once, drop in the platform’s normalized statement, and the workbook tells you which lines underpaid, which are missing, and which are explained by MG recoupment. Target runtime: 45 minutes per platform, per quarter.

Prerequisite: you’ve already run the statement through the Clean Royalty Statements from Platforms normalizer.

Step 1: Load the Expected Sheet From Contract Terms

Expected has one row per (title × territory × period). For each, compute the expected net owed:

Expected Net = Platform Gross × Licensor Royalty %

Adjusted Expected =
  IF(MG Status = "RECOUPING", Expected Net recouped against MG balance,
     Expected Net)

If you have a contract register with royalty % per title, the Expected sheet is a VLOOKUP against that. If you don’t, pulling contract terms into a register is the pre-work — the Contract Metadata Audit tutorial covers it.

Step 2: Paste Reported Data

Drop the canonical output from your normalizer into the Reported sheet. Columns: Title | Territory | Period | Gross USD | Net USD | FX Rate | Platform.

Step 3: Compute Variance

The variance report left-joins Expected on Reported by Title | Territory | Period:

Variance = Reported Net - Adjusted Expected
Variance % = Variance / Adjusted Expected

Status =
  IF(Reported Net is blank, "MISSING",
    IF(ABS(Variance) < max($50, 5% × Expected), "OK",
      IF(Variance < 0, "UNDERPAID",
      IF(Variance > 0, "OVERPAID", "OK"))))

Tolerance thresholds are deliberate: $50 or 5%, whichever is higher. Below that, noise from rounding and FX dwarfs signal. Adjust to your book size.

Step 4: Filter Out Explained Deltas

Not every variance is a problem. Before flagging a line for investigation, apply these exclusions:

• HELDBACK. The title was held back in that territory for that period per contract. Tag Expected rows with Status = HELDBACK; variance excludes them.
• RECOUPING. MG still being recouped — no cash net due to licensor until MG is paid back from gross. Statement under-reports net intentionally.
• FX drift. If Reported used a different FX rate than Expected, the variance is FX noise. Compare Gross USD (with matching FX) instead of Net USD for a sanity check.
• Timing. Reported may include adjustments for a prior period. Reconcile against reported period, not statement period.

Step 5: Investigate the Unexplained UNDERPAID Rows

Prioritize by dollar magnitude. A $15k underpayment on one title matters more than 30 × $100 underpayments across a long tail.

For each significant UNDERPAID row:

1. Pull the source statement row from the Source Row column — the raw platform data.
2. Compare to the contract: does the royalty % match what the platform applied?
3. Check MG balance: has the MG fully recouped yet? If yes, the licensor should be getting the full split.
4. Check holdbacks: is this territory in a holdback window the platform forgot?

When you find the cause, note it on the Variance Report row. The note log is how you prove a pattern — if the same platform consistently under-reports Japan, that’s a dispute, not a rounding error.

Step 6: Raise Disputes Upstream

Any unexplained UNDERPAID row > tolerance goes on a dispute list with:

• Title + Territory + Period
• Expected Net
• Reported Net
• Variance
• Hypothesis (wrong %, missing territory, MG miscalculation, etc.)
• Supporting contract clause (section + page #)

Send one consolidated dispute per platform per quarter. Platforms respond faster to a batched, clearly-structured dispute than to ad-hoc emails.

Common Issues

• Contract says “net receipts” but the statement reports “gross revenue.” Different bases. The licensor’s royalty % may be applied to the wrong number. Clarify the basis in the contract audit; don’t leave it ambiguous in Expected.
• Blended rates on compilations. Five titles on one album, one blended royalty rate. The platform may report unit-level, but your contract is at the pool level. Aggregate before comparing.
• Participations cascading down a waterfall. If the licensor is a second-position beneficiary behind a guild minimum and a studio distribution fee, Expected is not just Gross × %. Participation modeling is out of scope for this audit — use dedicated royalty software for full waterfall (see the Vistex review for the enterprise option).
• Reported currency ≠ paid currency. Platform reports in USD, pays the licensor in GBP after a separate FX conversion. The licensor sees a different number than the statement. This is a treasury issue, not a royalty-calc issue — flag separately.

Download the Template

The Royalty Reconciliation Workbook has Expected / Reported / Variance pre-wired with the full status logic. Load Expected once per contract portfolio; refresh Reported each quarter from the statement normalizer.