This article provides essential guidance on using the Client Credit Tracking Sheet, a tool designed to help your business manage client credit balances that originate in Xero but are not visible in LightSpeed or Vital.
Maintaining this sheet accurately ensures all relevant staff are aware of outstanding client credits and helps streamline their correct application to future invoices, preventing financial discrepancies and enhancing client satisfaction.
General Instructions & Best Practices
- Download a copy of the spreadsheet from here.
- Sheet Name Format should be [Your Franchise Name] - Client Credits Tracker e.g., "Poolwerx Brisbane - Client Credits Tracker"
- Put your tracker into a google drive and share it with all staff members who need visibility of client credit balances but do not have direct access to Xero (e.g., schedulers, technicians, retail staff).
- Update this sheet immediately when a new Xero credit is identified or when an existing credit is applied.
- Regularly reconcile this sheet against your Xero Aged Receivables Detail report to ensure accuracy.
- Once a credit is applied in Xero, ensure the relevant fields on the this sheet are updated (e.g., Applied to Xero Invoice # and Applied Total). Clear out the Applications To Do column for that entry.
- Once a Credit is Fully Applied, remove the tag from the Vital Client.
Understanding your Client Credit Tracker
This sheet provides comprehensive visibility for your team, allowing you to track:
- Details of credit notes and overpayments issued in Xero.
- Confirmation of whether corresponding notes and tags have been added in Vital.
- How credits have been or should be applied.
- The current remaining outstanding balance for each credit.
To maintain data integrity and prevent errors, avoid manually editing the following columns. These are either automatically calculated or should only be updated by the team member responsible for applying the credits in Xero:
- Outstanding Balance: This column is automatically calculated (Initial Credit Amount - Applied Total).
- Status: This column is automatically updated based on the outstanding balance (e.g., "Open," "Partially Applied," "Fully Applied").
- Applied Total: This should only be updated after the application has occurred in Xero.
Utilise the preset views or the built-in filters at the top of the Google Sheet to quickly find the information you need:
Using Preset View
To quickly see common views (e.g. open credits, fully applied credits):
- Go to Data > Change View
- Click the view you want to see
To change the view, go to Data > Change View and exit the current view or click on another to change it.
Using Filter Slicers
To set the custom filter:
- Click on the Triangle next to the Name
- You can filter by condition (e.g. Is greater than xx) or by values (e.g., hiding blanks by unticking it).
| Column Header | Description | Example Data |
|---|---|---|
| Client Name | The full name of the client with the credit. Optional: Hyperlink to the Xero contact page | ABC Pools Pty Ltd |
| Status | Status of the credit. | Partially Applied |
| Outstanding Balance | Remaining credit available. This is a formula of Initial - Applied Total | 75.00 |
| Initial Credit Amount | Total original credit amount issued | $200.00 |
| Date Created | Date issued | 15-Jun-25 |
| Reason for Credit | Briefly explain how the credit originated (e.g., "Client overpaid Invoice #12345", "Goodwill gesture for service issue", "Refund for cancelled service", "Migrated from NetSuite"). | Overpayment on Inv #INV-PSC-500 |
| Invoice(s) Applied to | Reference to the invoices credit has been applied against. | INV-PSC-1005 ($50) |
| Applied Total | The total applied amount. | 125.00 |
| Vital Notes Added | Indicate if a note regarding this credit has been added to the client's profile in Vital. | Yes |
| Vital Tag Added | Indicate if a tag regarding this credit has been added to the client's profile in Vital. | Yes |
| Applications To Do | If not yet applied, note which invoice and amount to apply against | Please apply $75 to INV‑1012 |
| Notes | Open field for any extra notes. |
Example Use Cases
Here's how different team members can leverage this sheet in their daily tasks:
Customer Service/Admin/Business Management:
- Scenario: A client queries their credit balance.
- Action: Search for their name in the Client Name column to quickly view the Outstanding Balance and Status.
Finance/Admin:
- Scenario: You're auditing accounts to ensure all credits have corresponding notes and tags in Vital.
- Action: Filter by Vital Notes Added or Vital Tag Added to "No" to identify entries needing attention.
- Scenario: You're chasing payment for an invoice and notice the client has an outstanding credit in Xero.
- Action: Add a note in the Applications To Do column indicating which invoice the credit should be applied against. Once applied, update the Applied Total, Invoice(s) Applied to, and Status columns.
Scheduler or Finance (Invoicing from Vital):
- Scenario: You are creating or reviewing an invoice in Vital before sending it to the client.
- Action: Before sending, quickly check this Client Credit Tracking Sheet (by client name) to see if the client has an Outstanding or Partially Applied credit. If so, apply the credit in Xero first (following the "Managing Xero Credit Balances" guide), and finally send the adjusted invoice to the client to avoid overbilling.