How to Create an Accounts Receivable Dashboard in Google Sheets with ChatGPT

Buried in spreadsheets trying to figure out who owes you money and when it's due? You don't need a complicated and expensive accounting system to get a clear picture of your accounts receivable. This tutorial will walk you through building a dynamic, easy-to-read accounts receivable (AR) dashboard right inside Google Sheets, using ChatGPT to write the complex formulas so you don't have to.

Why Google Sheets for an AR Dashboard?

Before jumping into the setup, let's briefly cover why Google Sheets is an excellent tool for this task. It's free, cloud-based for easy team collaboration, and surprisingly powerful when you know what to do. While it's not a full-fledged BI tool, it's more than capable of giving you the at-a-glance financial insights you need to manage cash flow effectively.

The typical process for this kind of reporting is painful. Many small businesses live in a cycle of downloading CSV files every Monday, spending hours wrestling them into a report for a Tuesday meeting, and then fielding follow-up questions that require more data deep dives. The result? Half the week is gone just trying to understand what happened last week. A live dashboard in Google Sheets breaks this cycle.

Step 1: Get Your AR Data in One Place

Your dashboard is only as good as the data powering it. The first step is to create a single, clean 'raw data' source within your Google Sheet. Create a new tab and name it something like "AR_Data". This will be the engine for your entire dashboard.

The Data You'll Need

Your AR_Data tab should be set up like a simple database table. You should have one header row at the top, and each row below it should represent a single invoice. Avoid merged cells, extra text, or subtotals in this tab - just raw, clean data.

Here are the essential columns you'll want to include:

• Invoice ID: A unique number for each invoice.

• Customer Name: The name of the client.

• Invoice Date: The date the invoice was issued.

• Due Date: The date the payment is expected.

• Invoice Amount: The total amount of the invoice.

• Amount Paid: The amount that has been paid so far. A zero here means nothing has been paid.

• Status: The current status of the invoice (e.g., 'Paid', 'Sent', 'Overdue').

Calculating Key Helper Columns

To make our dashboard formulas easier, we can add a couple of "helper columns" that perform simple calculations on each row. Don't worry, these are straightforward.

1. Add a new column called "Amount Outstanding." In the first cell under the header, enter the formula: =E2-F2 This assumes Invoice Amount is in column E and Amount Paid is in column F. Drag this formula down for all your rows.

2. Add another column called "Days Overdue." This calculates how many days have passed since the due date. The formula is: =IF(G2="Paid", 0, MAX(0, TODAY()-D2)) This formula checks if the invoice is 'Paid' (if so, it's 0 days overdue). Otherwise, it calculates the difference between today's date and the due date (Column D), ensuring it doesn't show negative numbers for invoices that aren't due yet.

Now your data source is ready. All your AR tracking information is in one place, which is the perfect foundation for our dashboard summary.

Step 2: Planning Your AR Dashboard Metrics

Next, create a new tab and name it "Dashboard." This is where you will build the visual summary. A great dashboard answers your most important questions immediately. For accounts receivable, those questions usually are:

• What is the total amount of money people currently owe us?

• How much of that money is seriously overdue?

• Who are our most tardy customers?

• Is our outstanding AR balance growing or shrinking over time?

Based on these questions, we'll design our dashboard to show four key things:

• Total Outstanding AR: A single, prominent number showing the total balance owed.

• AR Aging Buckets: A breakdown of what's owed by how long it's overdue (e.g., 0-30 days, 31-60 days, 61-90 days, 91+ days). This is critical for assessing cash flow risk.

• Top 10 Overdue Customers: A list of the customers with the largest outstanding, overdue balances.

• AR Over Time Chart: A simple line chart showing the total outstanding balance month over month.

Step 3: Using ChatGPT as Your Formula Guru

This is where the magic happens. Instead of looking up complicated function syntaxes like SUMIFS or QUERY, you can ask ChatGPT to write them for you in plain English. For the AI to give you an accurate formula, you need to provide three pieces of information in your prompt:

1. The Context: Tell it you're working in Google Sheets.

2. The Location of Your Data: Specifically mention your sheet and column names (e.g., "In my 'AR_Data' tab...").

3. Your Goal: Clearly state what you want to calculate.

Let's use this method to build our dashboard components.

Calculating Total Outstanding AR

On your 'Dashboard' tab, pick a cell for your "Total Outstanding AR" metric. Let’s ask ChatGPT to get the formula.

Your Prompt to ChatGPT:

"I'm in Google Sheets. I have a tab named 'AR_Data' where column H contains the 'Amount Outstanding' for each invoice. What formula would sum up all values in column H, excluding the header in H1?"

ChatGPT's Likely Response:

Use this formula:

=SUM(AR_Data!H2:H)

Paste that formula into your dashboard cell. You've now got your most important AR metric - live and automated.

Creating AR Aging Buckets

This is where ChatGPT really shines, as these formulas can get tricky. We need to sum the outstanding balances based on how many "Days Overdue" they are (Column I in our data tab).

Let's structure this on the 'Dashboard' tab with labels in one column and our formulas in the next.

Here's a prompt for the "31-60 Days Overdue" category:

Your Prompt to ChatGPT:

"In my Google Sheet, I have a tab called 'AR_Data'. I want to sum the values in the 'Amount Outstanding' column (H) only for rows where the value in the 'Days Overdue' column (I) is greater than 30 AND less than or equal to 60. Give me the Google Sheets formula for this."

ChatGPT's Likely Response:

Here is the SUMIFS formula you need:

=SUMIFS(AR_Data!H:H, AR_Data!I:I, ">30", AR_Data!I:I, "<=60")

Incredible, right? You just built a specific, conditional summation without needing to know any of the syntax yourself. You can now repeat this process for the other aging buckets, simply adjusting the day numbers in your prompt:

• For 0-30 Days: ...where 'Days Overdue' is >=0 AND <=30

• For 61-90 Days: ...where 'Days Overdue' is >60 AND <=90

• For 91+ Days: ...where 'Days Overdue' is >90

Identifying Top Overdue Customers

Finally, let's create a dynamic list of customers who owe you the most. The QUERY function is perfect for this, but its syntax is intimidating for most users. Let ChatGPT handle it.

Your Prompt to ChatGPT:

"I need a Google Sheets QUERY formula. In my 'AR_Data' tab, I want to find the top 10 customers based on their 'Amount Outstanding'. My data columns are: Customer Name (B), Amount Outstanding (H), and Days Overdue (I). I only want to see customers whose 'Days Overdue' is greater than 0. I want the results to show the customer name and their total outstanding amount, sorted high to low."

ChatGPT's Likely Response:

This QUERY formula should accomplish your goal:

=QUERY(AR_Data!B:I, "SELECT B, SUM(H) WHERE I > 0 GROUP BY B ORDER BY SUM(H) DESC LIMIT 10", 1)

When you paste this into your dashboard, it will automatically generate a sorted table of your top 10 overdue accounts. This list will update in real-time as your underlying AR data changes.

Step 4: Bringing Your Dashboard to Life with Charts

Now that your key metrics are calculated, visualizing a few of them will make the information much easier to digest.

AR Aging Summary Pie Chart

A pie chart is perfect for seeing the breakdown of how much of your AR is in each aging bucket.

1. Select the data from your AR Aging Buckets - both the labels and the calculated amounts.

2. Go to the Insert menu and select Chart.

3. Google Sheets will likely default to a pie chart, but if not, you can select it from the Chart Editor on the right.

Position this chart on your dashboard. Now you can see at a glance if a dangerous percentage of your money is in the "91+ Days Overdue" category.

AR Over Time Line Chart

To see trends, you may need a small "helper table" to summarize data by month. Use the process from step 3 to ask ChatGPT for a SUMIFS formula that totals the 'Invoice Amount' column (E) by month, using the 'Invoice Date' column (C). Once you have that data, select it and insert a Line Chart to see the trend of issued invoices over time.

Final Thoughts

By pairing the organizing power of Google Sheets with the formula-writing genius of ChatGPT, you can create a powerful AR dashboard to monitor your business's financial health. You don't need years of spreadsheet experience, you just need to know what questions to ask. This approach allows you to spend less time wrangling data and more time acting on it.

While building this yourself is an empowering experience, maintaining and updating the raw data in Google Sheets can still be a manual chore. At Graphed, we solve this by turning your entire data reporting process into a simple conversation. Instead of crafting spreadsheet prompts, you can simply connect your accounting software, CRM, and other tools once, and then ask questions like "Create an accounts receivable dashboard showing aging buckets and our top 10 most overdue accounts." Our AI builds an entire interactive, real-time dashboard for you in seconds, with data that's always up-to-date.