Accounts Receivable Reporting: Aging, Collections, and BigQuery Model

Accounts receivable reporting guide for growing companies: AR aging, collections risk, DSO, disputes, reconciliation, and BigQuery model.

Accounts receivable reporting should tell leadership which customer cash is expected, which cash is late, and which invoices need action before they become a forecast problem.

Accounts receivable reporting is the repeatable process of turning invoices, payments, credits, aging buckets, disputes, customer ownership, and expected collection dates into a finance-owned view of customer cash risk.

For many growing companies, AR reporting starts as an accounting export. Finance reviews open invoices, adds aging buckets, marks a few collection notes, and sends leadership a summary of current and past-due receivables.

That can work when the invoice list is small.

It breaks when the business adds more customers, payment terms, product lines, locations, billing systems, sales owners, project teams, or collection workflows. The accounting report may still be correct, but leadership needs a more useful operating view:

which invoices are collectable
which invoices are disputed
which customers are stretching terms
which balances are concentrated in a few accounts
which promised payments are already behind forecast
which customer, product, or operational issues are delaying cash
whether the AR balance reconciles before it reaches the leadership pack

Good accounts receivable reporting connects finance, sales, operations, and customer ownership around one practical question: what cash should the company expect from customers, and what might prevent it from arriving?

What accounts receivable reporting should do

AR reporting is not only a list of open invoices.

A useful accounts receivable report should answer:

How much customer cash is open right now?
How much is current, near due, past due, severely aged, or at risk?
Which customers, invoices, or segments drive the balance?
Which invoices are disputed, blocked, or waiting on customer action?
Which invoices have promised payment dates?
Which owners are responsible for follow-up?
Which balances are connected to delivery, onboarding, billing, or contract issues?
Which expected collections are included in the cash forecast?
Which AR numbers reconcile to accounting?
Which adjustments, credits, write-offs, or unapplied payments need review?

That is why AR reporting should connect to cash flow reporting and working capital reporting, not sit beside them as a disconnected accounting table.

To balance the customer-cash view with expected vendor cash out, pair it with the accounts payable reporting guide so leadership can see both sides of working capital timing.

Revenue may look strong while collections weaken. Cash may tighten while the income statement looks healthy. A customer may be profitable on paper but consume attention and cash through billing disputes, late payments, or special handling.

The AR report should make those patterns visible early enough for finance and operations to act.

Why AR reporting gets harder as companies grow

Accounts receivable reporting usually becomes unreliable for predictable reasons.

Invoice data is correct but incomplete for management

Accounting systems are the right place to record invoices, payments, credits, and balances.

But the accounting view may not capture everything leadership needs to manage collections:

sales owner
customer success owner
contract terms
delivery status
onboarding blockers
project acceptance status
customer dispute reason
promised payment date
renewal risk
collection note history
forecast inclusion

The invoice balance may be accurate, but finance still has to join it with operating context to explain collection risk.

That same problem appears in revenue reporting, where booked, billed, recognized, collected, and forecast revenue need separate definitions and source ownership.

Customer identity breaks across systems

AR reporting often depends on matching accounting customers to CRM accounts, billing contacts, payment records, parent companies, projects, subscription records, and operational ownership.

The match is rarely perfect.

Common issues include:

parent and child customer accounts
customers renamed after acquisition or rebrand
one operating customer with multiple billing entities
one billing customer tied to several CRM accounts
duplicate customer records
payments received under a different name
invoices issued to a procurement entity
customer names keyed differently across systems

If customer mapping lives in a spreadsheet, the AR report will keep requiring manual review. A shared reporting layer should make customer mapping explicit and reusable.

The QuickBooks to BigQuery reporting pattern covers this kind of accounting and CRM connection for teams that need customer-level finance reporting.

Aging buckets do not explain risk by themselves

AR aging is useful, but it is not enough.

A 45-day balance from a reliable customer with a known approval cycle is different from a 45-day balance tied to a dispute, failed delivery milestone, contract issue, or customer cash problem.

Useful AR reporting should add context:

reason for delay
collection owner
last contact date
promised payment date
dispute status
credit memo expectation
delivery or service blocker
customer concentration
prior payment behavior
forecast treatment

Without that context, leadership sees a past-due balance but cannot tell whether it is normal timing, operational friction, or real collection risk.

Forecast assumptions drift away from open invoices

Cash forecasts often include expected customer collections.

That forecast becomes fragile when expected collection dates are entered manually and never compared with the live invoice list. Finance may assume payment next week while the invoice is still disputed, missing customer approval, or unpaid beyond terms.

AR reporting should connect open invoices to cash forecast assumptions. When the promised date changes, the forecast should show that change. When actual cash arrives, the forecast should be compared with reality.

That discipline also supports forecast variance reporting, because finance can explain whether a cash miss came from slower customer payment, inaccurate assumptions, billing delay, or operating execution.

Collection ownership is unclear

Collections may involve finance, account management, sales, customer success, delivery teams, project managers, or executives.

If ownership is not visible, late invoices become a shared concern with no clear next action.

An AR report should show who owns the follow-up and what status they are managing. The owner may differ by invoice size, customer tier, age bucket, dispute type, or account relationship.

This is operational reporting as much as finance reporting. The operations reporting guide is relevant when delayed cash reflects delivery, service, fulfillment, or approval problems outside the accounting system.

Core AR metrics to define

The right metrics depend on the business model, but most growing companies should define a focused set before building dashboards.

Open AR balance

Open AR balance is the total customer invoice amount still unpaid.

Define:

which invoice statuses are included
whether taxes, shipping, finance charges, or pass-through fees are included
whether credits and unapplied payments are netted
whether partially paid invoices show gross or remaining balance
whether foreign currency balances are converted
which accounting period or report date is used
whether the number is accounting-reported or modeled

The balance should reconcile to the accounting system before leadership uses it.

AR aging

AR aging groups open invoices by how long they have been outstanding or overdue.

Common buckets include:

current
1 to 30 days past due
31 to 60 days past due
61 to 90 days past due
over 90 days past due

Some businesses should use due-date aging. Others may also need invoice-date aging. The difference matters.

Invoice-date aging shows how long the invoice has existed. Due-date aging shows how late the customer is relative to terms. Both can be valid, but they answer different questions.

Past-due balance

Past-due balance shows the open AR that has crossed the due date.

Useful cuts include:

past due by customer
past due by invoice
past due by sales owner
past due by account manager
past due by product or service line
past due by customer segment
past due by dispute reason
past due by promised payment date

The purpose is not only to produce a total. The purpose is to identify where collection action or operating escalation is needed.

Expected collections

Expected collections translate open AR into likely cash timing.

This view may use:

invoice due date
payment terms
customer payment pattern
promised payment date
collection owner judgment
dispute status
cash forecast override
customer risk category

Expected collections should be labeled as forecast, not actual. They should also be compared with actual cash received so finance can improve the assumptions over time.

Days sales outstanding

Days sales outstanding, or DSO, can be useful when the business needs a simple indicator of collection speed.

Define the calculation carefully:

what revenue or credit sales denominator is used
whether the period is monthly, quarterly, or trailing
whether unusual invoices are excluded
whether customer deposits or prepayments affect the view
whether the metric is meaningful for project, subscription, ecommerce, or mixed models

DSO can be a useful signal, but it can also mislead if revenue timing, invoice timing, or business mix changes. It should support the AR story, not replace invoice-level analysis.

Disputes, credits, and write-off risk

AR reports should separate invoices that are late from invoices that are unlikely to collect without correction.

Useful statuses include:

customer dispute
delivery issue
missing purchase order
pricing or contract mismatch
billing error
credit memo expected
partial payment expected
bad debt review
write-off proposed

These statuses help leadership understand whether the AR problem is a collections process, billing quality issue, service delivery issue, or customer credit risk.

Customer concentration

A large AR balance may be acceptable if it is spread across many reliable customers. It may be riskier if a small number of customers represent most of the balance.

Useful views include:

top customers by open AR
top customers by past-due AR
top customers by over-60 or over-90 balance
AR concentration by parent account
AR concentration by segment, channel, or product line

This matters for cash planning, board reporting, and customer risk discussion.

Source systems to map before building

Accounts receivable reporting usually touches more than the accounting system.

Common sources include:

accounting or ERP system
invoicing or billing platform
CRM
payment processor
bank feeds
subscription platform
ecommerce platform
contract or order system
customer success platform
ticketing or dispute workflow
project delivery or service operations tool
cash forecast spreadsheet

For each source, define:

system owner
refresh frequency
key identifiers
customer and parent account mapping
invoice and payment identifiers
required dates
required statuses
known data quality issues
reconciliation point
whether the data is accounting-approved, operational, or forecast

If the broader reporting foundation is still being scoped, Small Business Data Warehouse Requirements is a practical checklist for source systems, ownership, and KPI definitions.

Date and status logic needs precision

AR reporting depends on dates that sound similar but answer different questions.

Useful date fields may include:

invoice date
due date
service period
delivery completion date
payment date
deposit date
settlement date
accounting period
collection note date
promised payment date
forecast collection date
write-off date

The model should name which date drives each metric.

Status logic needs the same discipline. Invoice status, payment status, dispute status, delivery status, collection status, and forecast status are related but not identical.

If a dashboard blends those fields without labels, finance and operations will debate the number when the real problem is unclear logic.

This is one reason a KPI definition framework matters. AR metrics need written definitions, owners, inclusions, exclusions, timing rules, and reconciliation checks.

What the BigQuery AR model should include

BigQuery can be a practical foundation for accounts receivable reporting when the company needs to connect accounting, billing, payments, CRM, operations, collections, and forecast data.

A sensible first model may include:

raw source tables for accounting, billing, CRM, payments, and collection notes
cleaned staging tables with consistent customer, invoice, payment, and date fields
customer and parent-account mapping tables
owner mapping tables for sales, account management, customer success, finance, or operations
invoice fact table
payment fact table
credit memo and adjustment fact table
invoice-to-payment matching table
AR snapshot table by reporting date
AR aging table by due-date and invoice-date logic
expected collection schedule
dispute and collection status table
cash forecast linkage table
reconciliation table comparing modeled AR with accounting AR
exception tables for missing customer mappings, unmatched payments, stale invoices, duplicate records, and invoices without owner or due date

The first version does not need every possible workflow.

It should cover the AR questions leadership asks repeatedly and remove the spreadsheet work that makes collection risk hard to explain.

For many SMB and mid-market teams, that means starting with invoices, payments, customers, due dates, open balances, aging buckets, collection status, and reconciliation. More advanced segments can come after the basic model is trusted.

If the warehouse foundation is not in place yet, BigQuery implementation is the natural starting point. If the source tables already exist but reporting is still manual, BigQuery reporting automation is usually the more focused path.

Reconciliation and exception checks

AR reporting affects cash, working capital, customer risk, and board materials, so reconciliation needs to be visible.

Useful checks include:

modeled open AR compared with the accounting AR report
invoice totals compared with billing system totals
payments matched to invoices
unapplied payments flagged for review
credit memos matched to original invoices
duplicate invoice numbers
missing due dates
missing customer mappings
missing owner mappings
invoices past due with no collection status
invoices marked disputed with no dispute reason
forecast collections tied to invoices that are already paid, credited, or written off
stale promised payment dates
large balances with no recent collection note

These checks do not need to dominate the leadership dashboard. But they should be available before the numbers are used.

This is the same principle behind dashboard trust. Leaders do not need to see every technical detail, but finance needs a visible path from the chart back to reconciled source records.

Segment views that make AR actionable

Accounts receivable reporting becomes more useful when the business can see where the risk is coming from.

Common segment views include:

customer
parent account
customer segment
sales owner
account manager
customer success owner
product or service line
project
location
channel
invoice size band
payment terms
dispute reason
age bucket
expected collection week

The right segments depend on the decisions leadership needs to make.

If cash forecast risk is the main issue, expected collection week and customer concentration matter. If collection accountability is weak, owner and status matter. If invoices are delayed because of operational blockers, project, delivery status, and dispute reason may matter more than sales owner.

This is also where AR reporting can connect to customer profitability reporting. A customer with good revenue and margin can still create management friction if cash collection is slow, disputed, or unpredictable.

How AR should appear in leadership reporting

Leadership does not need a full collections workbook in every meeting.

The executive view should usually show:

total open AR
current and past-due AR
severe aging balance
largest customer concentrations
expected collections by week or month
disputed or blocked AR
invoices requiring executive escalation
reconciliation status
changes since the last reporting cycle
forecast impact

For CFO dashboards, AR should sit beside cash, revenue, margin, expenses, forecast, and working capital. The CFO dashboard requirements guide covers how those finance views should fit together.

For boards, AR should be summarized around cash risk, concentration, forecast confidence, and operating actions. If AR movement affects cash runway or working capital, the logic should align with board reporting instead of becoming a separate explanation.

Common mistakes to avoid

Mistake 1: treating AR aging as the whole report

AR aging is useful, but it does not show collection owner, dispute reason, promised payment date, operational blocker, forecast treatment, or customer concentration by itself.

Mistake 2: mixing invoice-date aging and due-date aging

Invoice-date aging and due-date aging answer different questions.

If the report does not label the rule, leadership may think an invoice is late when it is simply on longer payment terms.

Mistake 3: ignoring credits, partial payments, and unapplied cash

Credits, partial payments, refunds, and unapplied payments can make open AR look wrong even when the accounting records are valid.

The reporting model should surface those exceptions instead of hiding them in manual cleanup.

Mistake 4: separating collections from operations

Late payment is not always a finance follow-up problem.

It may be caused by delivery acceptance, missing documentation, unresolved support issues, purchase order problems, or contract mismatch. AR reporting should help route those issues to the right owner.

Mistake 5: using spreadsheet overrides without expiration

Finance judgment is sometimes necessary, especially for expected collection dates.

But overrides should have owner, reason, timestamp, and expiration. Otherwise, the cash forecast can depend on stale assumptions that no longer match the invoice list.

A practical first phase

The first version of AR reporting should be narrow enough to finish and strong enough to replace recurring manual work.

A practical first phase looks like this:

define the AR questions leadership asks repeatedly
separate open balance, aging, past due, disputes, expected collections, and forecast inclusion
map accounting, billing, payment, CRM, and owner sources
define customer and parent-account mapping rules
define invoice-date, due-date, payment-date, and forecast-date logic
centralize invoices, payments, credits, customers, and collection status in BigQuery
create AR snapshot, aging, expected collection, and exception tables
reconcile modeled AR to the accounting report
publish a concise finance-owned AR view for leadership
compare expected collections with actual cash each reporting cycle

That scope is enough for many growing companies to move from spreadsheet AR tracking to a dependable finance and operations reporting process.

The goal is not to build a complex collections platform on day one. The goal is to give leadership a clear view of customer cash risk, give finance a repeatable model, and give operators a better way to see where action is needed.

FAQ

What should accounts receivable reporting include?

Accounts receivable reporting should include open invoice balance, AR aging, past-due balance, payment terms, expected collection dates, customer concentration, disputes, credits, collection owner, DSO where useful, and reconciliation status. It should also show whether numbers are accounting-approved, operational, or forecast.

Why does AR reporting become unreliable?

AR reporting becomes unreliable when accounting, billing, CRM, payments, and collections data use different customer mappings, invoice statuses, timing rules, and manual spreadsheet adjustments. The issue is usually the reporting foundation, not one bad AR export.

How can BigQuery improve accounts receivable reporting?

BigQuery can centralize accounting, billing, CRM, payment, and collections data, then model invoice status, AR aging, expected collections, disputes, reconciliation checks, and leadership-ready AR reporting tables. It is most useful when AR reporting depends on more than one source system.

What is the difference between AR reporting and cash flow reporting?

AR reporting explains open receivables, aging, collection risk, disputes, and expected customer payments. Cash flow reporting shows actual and forecast cash movement across receipts, disbursements, working capital, and financing activity. The two views should connect, but they should not be treated as the same report.

Final thought

Accounts receivable reporting should make customer cash risk visible before it becomes a surprise.

The best AR reports do more than total open invoices. They show aging, ownership, disputes, expected collections, customer concentration, forecast impact, and reconciliation status in one repeatable reporting model.

When invoices, payments, credits, customer mappings, collection notes, and forecast assumptions are modeled in BigQuery, finance can explain the number, operations can see what needs action, and leadership can make cash decisions from a view it trusts.