AR Aging Report Template: 30/60/90 Day Buckets for SMB Cash Flow — Accounts Receivable

Why 30/60/90 Day Buckets Matter for SMB Cash Flow

An accounts receivable aging report template is a structured spreadsheet that categorizes unpaid invoices into 30/60/90+ day buckets, giving SMB owners a clear snapshot of who owes what and how overdue each payment is. This single tool transforms vague cash flow anxiety into a concrete action plan.

Cash flow problems cause 82% of small business failures, according to a US Bank study.¹ The root cause is rarely low revenue — it is invisible aging receivables that owners do not catch until invoices are 60 or 90 days past due.

The 30/60/90 day bucket system solves this by making overdue invoices visible at a glance. Each bucket represents a time window: current (0–30 days), 31–60 days, 61–90 days, and 91+ days past due. When a customer's invoice moves from the 31–60 bucket into the 61–90 bucket, the owner sees it immediately and can escalate before the debt becomes uncollectible.

Consider a hypothetical SMB with $200,000 in total receivables. If $40,000 sits in the 91+ day bucket, that is 20% of receivables at high risk of becoming bad debt.¹ Without the aging report, the owner might see only the total $200,000 and assume cash is coming. With the buckets, the risk is obvious.

The aging of receivables method also helps estimate bad debt expense. By applying historical collection rates to each bucket — say 2% for current, 10% for 31–60 days, 30% for 61–90 days, and 50% for 91+ days — an owner can calculate a realistic allowance for doubtful accounts.²

Why Your AR Aging Report Is the First Thing a CFO Asks For

When a fractional CFO or financial advisor reviews a new SMB client, the first request is almost always the accounts receivable aging report. The aging report reveals three things instantly: collection discipline, customer payment patterns, and hidden cash risk.

A clean aging report — where the majority of receivables sit in the current bucket — signals that the business invoices promptly, follows up on time, and has customers who respect payment terms. A report where a significant portion of dollars, for example a quarter or more, sit in the 61–90 day bucket signals the opposite: weak collection processes, lenient terms, or a concentration of slow-paying customers.

The report also reveals customer-level risk. If one customer accounts for a large share of the 91+ day bucket — for example, 40% — that single relationship threatens the entire cash position. The CFO can then recommend specific actions: tighten that customer's terms, require a deposit, or stop further work until the balance clears.

For SMB owners deciding whether to hire financial help, the aging report is the diagnostic tool that proves whether cash flow problems are structural or behavioral. A well-maintained report shows the owner already understands the mechanics. A missing or messy report shows where the first intervention should start.

Setting Up 30/60/90 Day Buckets in Your Spreadsheet

Building an accounts receivable aging report template requires five columns and a simple formula. Start with a spreadsheet containing these columns for each customer:

The "Days Outstanding" column uses a formula: =TODAY() - InvoiceDate. This calculates how many days have passed since the invoice was issued.

The "Aging Bucket" column uses a nested IF statement to assign each invoice to the correct bucket:

=IF(DaysOutstanding<=30,"Current",IF(DaysOutstanding<=60,"31-60 Days",IF(DaysOutstanding<=90,"61-90 Days","91+ Days")))

Once the buckets are assigned, create a summary table that totals each bucket across all customers:

Standard AR aging templates include customer-level detail within each bucket, so the owner can see exactly which customers are in the 91+ day category.³ This turns the spreadsheet from a passive report into an active collection tool.

How to Calculate Days Sales Outstanding from Your Aging Data

Days Sales Outstanding (DSO) measures the average number of days it takes to collect payment after a sale. It is calculated directly from the aging data.

The formula is: DSO = (Total Accounts Receivable / Total Credit Sales) × Number of Days

For a monthly calculation: DSO = (Ending AR / Monthly Credit Sales) × 30

Suppose a business has $200,000 in total receivables and $150,000 in monthly credit sales. The DSO is ($200,000 / $150,000) × 30 = 40 days¹. If the business offers net-30 terms, a DSO of 40 means customers are paying an average of 10 days late.

Industry benchmarks vary. A DSO under 30 days is strong for most SMBs. A DSO over 45 days signals that collection processes need improvement. The aging report provides the raw data to calculate this number at the end of every month.

Tracking DSO month over month reveals trends. If DSO rises from 35 to 50 over three months, the owner knows that payment delays are compounding even if total revenue looks healthy. The aging report's bucket distribution explains why — perhaps a growing percentage of invoices are slipping into the 61–90 day bucket.

The One Number That Predicts a Cash Crush Three Weeks Early

The percentage of receivables in the 61–90 day bucket is the single most predictive metric for an impending cash crunch.³

The logic is straightforward. Invoices in the 61–90 day bucket have a 30–40% probability of becoming uncollectible, based on historical collection data across industry segments.⁴ If 18 of 202 industry segments report 10% or more of aging dollars in the 91+ day category, the risk of invoices aging further is real.⁵

Consider a business with $300,000 in monthly operating expenses and $400,000 in total receivables. Suppose 15% of receivables ($60,000) sit in the 61–90 day bucket, and half of those eventually become bad debt — the business loses $30,000 in expected cash. That gap hits in approximately three weeks when those invoices would have been due.

The owner who monitors this single percentage can act before the cash crunch arrives. When the 61–90 day bucket exceeds a typical threshold of 15%, freeze discretionary spending, call every customer in that bucket, and consider pausing new work for the slowest payers.

When to Escalate: Aging Thresholds That Trigger a Collection Call

Accounts receivable escalation triggers should be tied directly to aging buckets. A structured escalation policy removes emotion from collection decisions and ensures consistent follow-up.

The 61–90 day threshold is the critical escalation point. At this stage, the invoice is approaching the point where collection probability drops significantly. A phone call from the owner signals urgency and often resolves the issue within one week.

For the 91+ day bucket, the escalation should include a written demand letter and a formal credit hold. The business should not deliver additional products or services until the balance is cleared. If the customer does not respond within 14 days, the account should be referred to a third-party collection agency or small claims court.

These escalation triggers should be documented in the company's credit policy and shared with all team members who touch the billing process. Consistency is what makes the system work.

Connecting Your Aging Report to a 13-Week Cash Flow Forecast

The accounts receivable aging report feeds directly into a 13-week cash flow forecast. The forecast projects when cash will actually arrive, not when invoices were sent.

To connect the two, assign collection probabilities to each aging bucket. For example:

Apply these rates to the total dollars in each bucket to estimate cash collections over the next 13 weeks. Then layer in expected operating expenses — payroll, rent, supplier payments — to see whether the business will have enough cash to cover obligations.

A typical SMB with $1M–$10M in revenue maintains 25–40 active invoices at any time, making manual forecasting error-prone.⁶ The aging report automates the collection estimate, turning a subjective guess into a data-driven projection.

If the 13-week forecast shows a cash shortfall in week 7, the owner has six weeks to act: accelerate collections, delay non-essential payments, or draw on a line of credit. Without the aging report feeding the forecast, that shortfall would arrive as a surprise.

Automating the Template So It Updates Without Manual Work

Manual spreadsheet updates break down when the business has more than 25 active invoices. The solution is to connect the aging template to the accounting system or use a template that pulls data automatically.

Most accounting platforms — QuickBooks Online, Xero, FreshBooks — generate aging reports natively. The owner can export the data to a spreadsheet template that auto-calculates buckets, DSO, and collection estimates. The key is to set up a recurring export schedule: every Monday morning, export the latest aging data and paste it into the template.

For businesses using Excel or Google Sheets, a template with built-in formulas eliminates manual bucket assignment. The template should include:

• A data import sheet where raw invoice data is pasted
• A calculation sheet that auto-assigns buckets using the IF formula
• A summary dashboard showing bucket totals, DSO, and the 61–90 day percentage
• A 13-week forecast sheet that pulls collection estimates from the aging data

The entire update process should take less than 10 minutes per week. If it takes longer, the template needs simplification or the business should consider automated AR software.

Your Next Step

Export your current accounts receivable data into a spreadsheet and build the five-bucket aging template using the IF formula described above. Calculate your 61–90 day bucket percentage. If it exceeds a typical 15% threshold, call every customer in that bucket this week. If it is under that threshold, set a recurring Monday morning reminder to update the report. For questions about structuring your AR process or connecting it to a cash flow forecast, contact [email protected].