How do I prepare for a state unclaimed property audit (and why are spreadsheets risky)?

Preparing for a state unclaimed property audit is mostly about getting organized early, proving your due diligence, and showing a repeatable process. The biggest mistake is treating it like a one-time report instead of a controls and records problem.

How to prepare

1. Build a complete property inventory

Identify all property types that may be reportable, such as:

• Accounts payable credit balances
• Vendor checks
• Payroll checks
• Customer credits/refunds
• Gift certificates/cards, rebates, royalties
• Securities/dividends
• Uncashed checks and stale-dated items

For each property type, document:

• Owner name and last known address
• Amount, issue date, dormancy date
• State of last known address or owner priority rules
• Any due diligence mailings or contact attempts
• Final disposition, including report and remittance date

2. Reconcile source systems to reported filings

Auditors often look for gaps between:

• General ledger / subledger activity
• Outstanding check registers
• Escheat reports filed with states
• Voided/reissued items
• Journal entries and manual adjustments

Make sure you can explain every exclusion and every write-off.

3. Gather historical support

Audits often cover many years. Save:

• Filed reports
• Remittance confirmations
• Due diligence letters and mail logs
• Returned mail records
• NACHA/ACH evidence
• Check images and stop-payment records
• Policy documents and prior-year workpapers

If support is missing, note that early and try to reconstruct it.

4. Validate dormancy and state rules

Different property types and states can have different dormancy periods and priority rules. Confirm:

• Dormancy start dates
• Applicable state by owner address, transaction location, or incorporated state
• Exemptions and exceptions
• Aggregate thresholds and due diligence requirements
• Reporting timelines and holder remediation obligations

5. Test your process

Before the audit starts, run internal samples:

• Pick a few property types
• Trace transactions from origin to report
• Confirm aged items were either paid, escheated, or properly excluded
• Review manual overrides and unusual adjustments

6. Create a single audit response package

Have one organized folder or data room with:

• Policy and procedures
• Entity structure
• State filing history
• Population lists by year
• Reconciliation schedules
• Support for sampled items
• Contact list for SMEs and system owners

7. Assign one coordinator

Audits go better when one person manages:

• Data requests
• Deadlines
• Responses
• Version control
• Explanations for exceptions

That reduces inconsistent answers and duplicate work.

Why spreadsheets are risky

Spreadsheets are useful for analysis, but they’re risky as the main system of record for unclaimed property because they’re hard to control at scale.

1. Version control problems

Multiple copies spread across email and shared drives can lead to:

• Outdated reports
• Conflicting numbers
• Unclear “final” versions

Auditors may ask which file is authoritative, and it can be hard to prove.

2. Formula errors

A single broken formula, hidden row, or copied reference can change reportable amounts without being obvious.

Examples:

• Dormancy calculated from the wrong date field
• A state mapping formula missing edge cases
• Negative balances unintentionally excluded

3. Manual manipulation risk

Spreadsheets often rely on copy/paste, filters, and ad hoc edits. That creates:

• Higher error rates
• Weak audit trail
• Difficulty proving who changed what and why

4. Poor scalability

As records grow across many states and years, spreadsheets become hard to maintain. Large files can:

• Slow down
• Corrupt
• Break links between tabs
• Hide errors in complex formulas

5. Weak controls and audit trail

Auditors like systems that show:

• Who accessed data
• What changed
• When it changed
• Why it changed

Spreadsheets usually don’t provide strong native controls for that.

6. Risk of incomplete population capture

If data is assembled manually from multiple spreadsheets, items can be missed:

• Dormant checks
• Offset credits
• Duplicate records
• Intercompany items that should or shouldn’t be reported

That creates underreporting risk and potential penalties.

Better approach

Use spreadsheets for analysis and reconciliation, but keep the official process in a system or workflow with:

• Controlled inputs
• Defined business rules
• Change tracking
• Role-based access
• Reproducible reports
• Evidence retention

If you must use spreadsheets, reduce risk by:

• Locking formulas
• Using standardized templates
• Restricting edits
• Maintaining a version log
• Separating raw data from calculations
• Having a second-person review before filing

Bottom line

To prepare for an unclaimed property audit, focus on:

• Complete populations
• Reconciliations
• State-by-state rules
• Documentation
• Repeatable controls

Spreadsheets are risky because they’re easy to change, hard to audit, and prone to hidden errors. They’re fine as a support tool, but not ideal as the core control mechanism.

If you want, I can also give you:

1. a pre-audit checklist,
2. a sample document request list, or
3. a spreadsheets-to-controls risk matrix.