A Receivables and Cash Dashboard for Small Service Businesses in Data Studio

• The three questions the page answers
• The data
• Calculated fields I add
• Layout
• Traps to avoid

Harsh Khubchandani, independent Data Studio (formerly Looker Studio) consultant, New Delhi. Website: datastudioconsultant.com

Owners of small service businesses often know their revenue but not their cash position. Invoices go out, some get paid late, and the bank balance surprises everyone at month end. A receivables page in Data Studio will not collect money for you, but it makes the late invoices impossible to ignore.

§The three questions the page answers

1. How much is owed to us right now, and how much of it is overdue?
2. Which clients are late, and by how long?
3. What cash should arrive in the next 30 days if clients pay on their usual terms?

§The data

• An invoice export from the accounting tool: invoice number, client, issue date, due date, amount, amount paid, paid date. Most tools export this to CSV; I land it in Google Sheets or BigQuery on a schedule.
• A small client sheet with payment terms and an owner for each account, maintained by the business, not by me.

§Calculated fields I add

• Outstanding = amount minus amount paid.
• Days overdue = days between due date and today, only when outstanding is above zero.
• Ageing bucket with a CASE statement: Current, 1-30, 31-60, 61-90 and 90+ days.
• Average days to pay per client, from paid invoices in the last 12 months. This is the number that makes the 30-day forecast honest.

§Layout

• Scorecards: total outstanding, overdue amount, share of receivables over 60 days.
• A stacked bar of outstanding amount by ageing bucket.
• A client table sorted by overdue amount, with the account owner next to it so the follow-up has a name.
• A simple expected-cash chart: open invoices placed on due date plus that client’s average delay.

§Traps to avoid

• Partial payments and credit notes: model them in the data, not with chart filters.
• Currency: if clients pay in different currencies, convert at one agreed rate source and say which on the page.
• Access: receivables are sensitive, so I use viewer’s credentials or a restricted share list.

I build these reports inside the client’s own Google account and reconcile the totals with the accounting tool before handover. If you want one, start with a free 30-minute review at https://datastudioconsultant.com/.