How to Create an AR Aging Report in Google Analytics

Building an Accounts Receivable (AR) aging report from scratch might sound complicated, but it's one of the most powerful things you can do in Google Sheets to gain control over your business's cash flow. Forget clunky software or manual counting - a well-built spreadsheet gives you a clear, instant view of who owes you money and how overdue they are. This guide will walk you through creating a dynamic and automated AR aging report step-by-step, using formulas that do the heavy lifting for you.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why an AR Aging Report is Essential

An Accounts Receivable aging report is a financial document that categorizes unpaid customer invoices by the length of time they've been outstanding. Instead of just a single list of who owes you money, it groups balances into time-based buckets. This simple act of organization is critical for effective cash flow management.

The primary benefit is visibility. A quick glance tells you:

• Which specific invoices are critically overdue and require immediate attention.
• Whether your collections process is effective or if invoices are consistently slipping into older buckets.
• How much cash you can realistically expect to collect in the near future.
• Potential red flags with customers who are habitually late payers.

The standard buckets provide a clear timeline for your receivables:

• Current: Invoices that are yet to be due.
• 1-30 Days Past Due: Recently overdue invoices, often the focus of initial follow-ups.
• 31-60 Days Past Due: A growing concern requiring more persistent collection efforts.
• 61-90 Days Past Due: These invoices pose a higher risk of non-payment.
• 91+ Days Past Due: Considered seriously delinquent, the probability of collection decreases significantly here.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Raw Data Preparation in Google Sheets

The backbone of any good report is clean, well-structured data. In a new Google Sheet tab, which you can name "Invoice Data," create a table with the following essential columns. The consistency of this data is a key component to building an accounting report accurately.

• Invoice Number: A unique ID for each invoice.
• Customer Name: Who the invoice is for.
• Invoice Date: The day you issued the invoice.
• Due Date: The day payment is expected.
• Invoice Amount: The total value of the invoice.
• Amount Paid: How much the customer has paid so far (can be zero).

Make sure your Invoice Date and Due Date columns are formatted as dates by selecting them and going to Format > Number > Date.

Add a Column For 'Outstanding Balance' Calculation

With those aforementioned columns, we can use a formula to dynamically compute all overdue and recently due amounts. Add a seventh column titled "Outstanding Balance." This is simply calculated by subtracting the 'Amount Paid' from the 'Invoice Amount':

=[Invoice Amount] - [Amount Paid]

To automate this process for new rows, use the ARRAYFORMULA function to calculate the 'Outstanding Balance':

=ARRAYFORMULA(IF(E2:E="", "", E2:E - F2:F))

This ensures your data maintains its integrity for consistent analysis.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Calculating 'Overdue' Days on Any Invoice Given

To track overdue invoices, add a new column named 'Days Overdue'. Use the TODAY() function to get the current date and calculate the number of days overdue:

The formula is: =TODAY() - [Due Date]

• Positive Numbers: Indicate days overdue.
• Negative or Zero: Indicate invoices that are not yet due.

Use ARRAYFORMULA to automate this for new data entries:

=ARRAYFORMULA(IF(G2:G>0, TODAY() - D2:D, ""))

Step 3: Create the Structure for AR Report

With our foundational data, formulas and automation do the bulk of the work. Your report should include:

• Bucket Names: Names for different overdue periods.
• Total Outstanding Amount: Calculated using SUMIFS and COUNTIFS.
• Total Outstanding Invoices: The number of invoices requiring follow-up.

Step 4: Filling in Your Newly Customized Financial Report Using SUMIFS & Other Key Functions

Use SUMIFS and COUNTIFS to calculate totals for each period:

SUM & COUNTIF Formula for All "Current" Accounts Receivable

To find total value and count of current invoices:

SUM: =SUMIFS($G:$G, $H:$H, "<=0", $G:$G, ">0")

COUNT: =COUNTIFS($H:$H, "<=0", $G:$G, ">0")

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

COUNTIFS on All Overdue AR: 1-30 Day Buckets

Calculate value and count for 1-30 day overdue invoices:

SUM: =SUMIFS($G:$G, $H:$H, ">=1", $H:$H, "<=30")

COUNT: =COUNTIFS($H:$H, ">=1", $H:$H, "<=30")

COUNTIF Using Established Criteria for Remaining Formulas

Repeat the process for other overdue periods:

• 31-60 Days: Between 31 and 60 days.
• 61-90 Days: Between 61 and 90 days.
• 91+ Days: 91 days or more overdue.

Step 5: How Best You Can Visualize Using Your New AR Report

Use Sheets' chart function to create visualizations. Go to Insert > Chart to design a line or pie chart representing overdue invoices.

Using Conditional Highlighting in Google Sheets for AR Report (Data)

Highlight important data with conditional formatting. Select Format > Conditional formatting for the 'Days Overdue' column, and apply color codes to highlight overdue invoices.

Final Thoughts

This automated and custom financial report provides clearer visibility into cash flow without needing constant CPA guidance. Utilize tools to collect, synthesize, and visualize financial figures for streamlined strategies and processes.

For more advanced reporting, Graphed offers advanced functionalities to seamlessly integrate raw data for a comprehensive view of your financials, easing the process for non-experts.