You are losing valuable time and money if you're not using Excel to track unpaid invoices for your small business. This is because manually tracking invoices does not provide a complete picture of how many invoices have been overdue. Clients have a history of paying late and realize that sometimes invoices might just be lost.
It is common for your small business' unpaid invoices to get misplaced, forgotten about or outright ignored by clients who don't feel obligated to pay their bills on time.
Use Excel to track unpaid invoices from your clients. Aside from that, Excel Dashboards can be used as an employee retention tool as it acts as a simple and effective solution to track employee retention in your organization.
Although you can go online and take Advanced Excel Courses to enhance your accounting capabilities while becoming a technologist, here are a few simple DIYs you can practice to keep track of your unpaid invoices in Excel without hassle.
1. Create a Client List
The first step in creating an invoice tracker is to create a list of your clients. You should include all relevant information about each client, including their names and addresses, contact information, payment terms, due dates, etc.
2. Build an Invoice Log
An invoice log will allow you to keep track of the invoices that have been sent out and when they are due.
Record Recent and Past Invoice Amounts
Create a new sheet and label it "Invoices Sent."
- In column A, enter the customer name, invoice number, and date sent out on each row
- In column B, enter the invoice amount owed by each customer
- In column C, enter the date when payment was due from each customer
Create another sheet in your spreadsheet program (or open another document) and label it "Invoices Paid."
- In column A, enter the customer name, invoice number and date paid on each row
- In column B, enter the amount paid
- In column C, enter the date when payment was received from each customer
- In column D, enter any notes about this payment, such as whether or not there were any problems with collecting this amount
3. Brainstorm Column Headings and Rows
Another thing you would want to do is brainstorm all of the things you want to track on your spreadsheet. The more information you can get into your invoice tracking spreadsheet, the better off you'll be when it comes time to analyze your data and decide where your business needs improvement.
For example, customer name or company name, invoice number, invoice receipt date, amount due (with interest), date paid (if applicable), and notes about payment terms or outstanding items.
4. Color-Coding for At-a-Glance Tracking
If you color-code your unpaid invoices, you will be able to see at a glance how many are outstanding at any given time.
To do this, create a new column in your spreadsheet where each row can represent one invoice. In each row, type the invoice number (or some other identifier) into the first cell and then use color coding so that each type of invoice has its color.
When you have several different types of unpaid invoices, ensure each type has its specific color so they're easy to distinguish. For example, if you have three types of unpaid invoices — overdue accounts receivable, late payment fees, and interest charges — assign each type a specific color so that when someone looks at them, they can quickly identify what's what.
5. Use Formulas To Calculate Your Data Automatically
An AutoSum function is a handy tool for quickly totaling values in a range of cells. It is not always obvious how to use this function, so let's walk through an example:
You have a list of unpaid invoices and want to know how much money is outstanding on each invoice. You could manually add the total amounts in all the rows, but that would take some time.
Instead, use the AutoSum function to do all the hard work for you:
- Select the cell where you want to sum the total of your unpaid invoices.
- Navigate to the Home tab, and click on AutoSum
- A formula like =AUTOSUM(...) will appear in that cell
- Now go on to select the range of cells you want to add, or you can even choose disorganized cells by pressing the Ctrl button
- Once your required unpaid invoices cells are all covered in the AutoSum formula, press Enter
You can find the AutoSum function in two locations in Excel: Home > AutoSum & Formulas > AutoSum.
The bottom line is that unpaid invoices are no one's favorite thing to deal with. But a simple Excel spreadsheet lets you keep your business on track without the unnecessary stress and worry.
Ben Richardson is a director of Acuity Training one of the UK’s leading providers of classroom based Excel training.
Like what you're reading?
Subscribe to our FREE newsletter and we'll deliver content like this directly to your inbox.