Cash management and receivables: turn invoices into cash

Cash management receivables work turns an aging report into a view of when customer payments can fund the business. Start with open invoices, apply the receipts already received, and assign a realistic collection date to the remaining balance.

The key distinction is between what a customer owes and what cash will arrive before the next payment run. An invoice can be overdue without being collectible next week, while a balance not yet due can still have a confirmed payment date.

Put invoice and payment records on the same footing

Export the invoice register, applied receipts, credit memos, and current receivables aging report. Use one reporting cutoff and one currency. Keep customer ID and invoice ID so similar customer names cannot join unrelated records.

For each invoice, retain the invoice amount, contractual due date, cash applied through the cutoff, and remaining balance. Keep receipts in a separate transaction list when an invoice has several payments. Otherwise, joining invoice rows to each payment can repeat the original invoice amount and inflate receivables.

The SBA's financial management guidance identifies receivables, payables, available cash, and bank reconciliation as responsibilities that need an owner. For this workflow, the credit team owns the collection commitment, accounting owns application and ledger agreement, and treasury owns the cash timing assumption.

Do not replace the contractual due date with a promise date. Keep both, with the date the promise was last confirmed and a note on any dispute.

Calculate open balances and overdue days

A US distributor reviews these invoices on October 23, 2026. All amounts are USD; the example has no credit memos, write-offs, or currency adjustments. Amounts and days are exact, without rounding.

Use columns A through H for the headings below. Put the cutoff date in L1 and October 30, 2026, in L2 as the collection window end.

In F2, calculate the remaining invoice amount. In G2, calculate overdue days only for an open invoice:

=D2-E2
=IF(F2<=0,0,MAX(0,$L$1-C2))

Fill down through row 5. Dates must be stored as spreadsheet dates, and amounts as numbers. A text date may look correct on screen while producing an incorrect aging calculation.

If a receipt list has invoice ID in column A, receipt date in B, and amount in C, populate E2 from the transactions through the cutoff:

=SUMIFS(Receipts!$C:$C,Receipts!$A:$A,A2,Receipts!$B:$B,"<="&$L$1)

Microsoft documents SUMIFS as summing amounts that meet multiple criteria. Here the criteria are the invoice ID and receipt cutoff. Covirage is not affiliated with Microsoft.

Separate overdue exposure from expected cash

Sum open balances whose due date precedes the cutoff:

=SUMIFS(F2:F5,C2:C5,"<"&$L$1,F2:F5,">0")

Atlas and Delta contribute $12,000 and $20,000, giving $32,000 overdue. Cedar's $32,000 is still within terms at the cutoff. Birch has no open exposure; its historical payment timing is a separate collection-performance question.

Now sum open balances with expected receipt dates after the cutoff and through the window end:

=SUMIFS(F2:F5,H2:H5,">"&$L$1,H2:H5,"<="&$L$2)

Expected receipts are $32,000: Atlas plus Delta. Cedar falls outside the window even though its contractual due date is October 30. All $64,000 of the open balance is contractually due by that date, so the gap between amounts due and expected cash is $32,000.

This gap is a timing assumption requiring follow-up. It is not a conclusion that Cedar will default. Confirm why its expected payment falls after terms and whether the date is supported by the customer.

For summary collection measures, use the dedicated days sales outstanding guide and accounts receivable turnover calculation. Neither aggregate metric replaces the invoice-level dates used here.

Prove the balances before prioritizing calls

The table's invoice cohort reconciles as $100,000 invoiced less $36,000 applied cash equals $64,000 open. For the full company, extend the bridge to include opening receivables, credit memos, write-offs, and other valid ledger adjustments.

Applied receipts also need a bank check. Suppose the bank shows $40,000 of customer receipts, comprising the $36,000 applied above and $4,000 awaiting invoice allocation. The difference is not another overdue invoice payment: it is unapplied cash requiring investigation.

Keep that $4,000 visible in the reconciliation. Applying it without remittance evidence could make the wrong invoice appear paid. Conversely, ignoring it could prompt an unnecessary collection call to a customer that already sent money.

The cash management report can summarize the resulting exposure. The next stage, treasury cash forecasting, combines supported collection dates with supplier, payroll, tax, and financing payment dates.

Where it goes wrong

• Using the invoice total after a partial payment. Chase and forecast the open amount, while retaining the original amount for reconciliation.
• Treating all overdue balances as imminent receipts. Aged debt needs collection evidence, especially when disputed.
• Overwriting due dates with promises. That makes late payment disappear from the record.
• Ignoring unapplied cash or credits. The collection list must agree with accounting before customer contact.
• Duplicating invoice rows through receipt matching. Aggregate receipts by invoice before calculating balances.

Review your receivables file in Covirage

Bring your invoice register, applied receipt export, and expected collection dates to Covirage. Covirage maps the columns, its tools compute and check open balances and payment timing, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See revenue driver analysis for the supporting business context.