Banking

Download our invoice tracking spreadsheet

You may be a tech guru, but if you're an independent consultant who has trouble keeping track of invoices and accounts receivable, your IT skills are for naught. Our invoice tracking spreadsheet can help you organize the business side of your consultancy.

Keeping track of invoices and accounts receivable is one of the most mundane tasks a consultant faces: It's certainly not as exciting as coding out a new application or flowcharting the next big development project. But if you want to irritate an otherwise satisfied customer, try starting up a billing dispute with a controller and then discovering you were the one in the wrong.

Of course, the market is flooded with accounting software, from feature-rich packages such as QuickBooks Pro and Peachtree Accounting (each costs a few hundred dollars) to fairly handy freeware and shareware products that you can find on CNET's Download.com. These programs offer a variety of tax-related features and are a good investment if you need to juggle a ton of accounts receivable information.

But having a quick spreadsheet for simple record keeping is never a bad idea, either, particularly since the export features of most accounting programs leave a lot to be desired (just try getting Microsoft Money and QuickBooks to talk to each other seamlessly). You typically end up with a tab-delineated text file that you have to manipulate in Excel before sending it off to a client.

We’ve created a simple Excel workbook that logs invoice data by primary criteria, such as customer, due date, and credits applied. (You can download the file here.) The workbook also does some simple time calculations to give you a visual heads-up when an invoice is overdue and tallies all its information in a handy summary page.

If clients have questions about their billing histories, you can just sort on the appropriate data and do a simple cut-and-paste, and you'll have a clean Excel file that's ready to be attached to that polite, yet stern, second-notice e-mail.

Again, our spreadsheet is not designed to compete with QuickBooks—it's not going to spit out an amortization report for you. It's just a handy tool that may make communicating with clients a little easier.

Asking for money
We've included three tabs in the worksheet for three billing years: 2002, 2003, and 2004. (If you later find that you want to add another year, you can copy one of these tabs and add a few references to the workbook's Accounts Summary sheet.) The sheets' primary sort is around invoice number, and they collect other invoice and payment data points. The fields that deal specifically with invoicing are:
  • Invoice #
  • Date
  • Due Date
  • Customer
  • Amount

These field headers are marked in light green, as you can see in Figure A. All these data points are fairly self-explanatory. Note that the Amount field is for the original invoiced amount; if you later need to make adjustments to that value, the spreadsheet does the math for you.

Figure A
Our workbook's invoicing, account update, and payment update fields


Next to the invoicing fields are the account update fields, marked by light blue shading on their header cells. These fields are:
  • Credit
  • Penalty %
  • Penalty
  • Total Due

After you issue your original invoice, use these fields to update any charges for late payment or credits you wish to extend to your customer. Simply input the appropriate values in the Credit or Penalty % fields, and the workbook calculates the Total Due amount, on which your overall accounts summary is based.

As you can see in our sample project, we've decided to give our new customer a $50.50 credit as a way of encouraging future business. On the flip side of the coin, we have charged our late customer a 5 percent penalty, which also updates the Total Due figure.

Keeping track of time
As we mentioned, our workbook gives you visual hints on who is paying on time and who is running a little late. In Figure A, note that the invoice number entries are formatted differently; this appearance is based on Excel's NOW() function and a little simple math. Invoice numbers have differing appearances, based on the criteria illustrated in Figure B.

Figure B


Any paid invoice number, even if it was paid late, is marked in bold green type. Unpaid invoices that have passed their Due Date appear in bold red type; open invoices that have yet to reach their due date appear in italic green type.

Getting paid
When you do get a check in the mail, the payment update info fields, marked with light yellow shading on their headers, accept that information and calculate where you and your customer stand. These fields are:
  • Total Paid
  • Pay Date
  • Outstanding

Again, just enter the Total Paid and Pay Date values, and let the spreadsheet balance out the account. In our example, our recalcitrant late customer did not pay the $75 penalty, so that figure maps to the Outstanding column.

Each yearly tab sums up these data points and reports them to the workbook's Accounts Summary sheet, which you can see in Figure C. This sheet's first entry is your Total Accounts Receivable, which is a pretty good barometer of where your business stands.

Figure C
The Accounts Summary tab gives you a quick glimpse of your incoming cash flow.


Again, keep in mind that our simple workbook doesn't report many key metrics, such as receivables due in the next 30 days and outbound cash flow. (You've got to spend it to make it.) You could spend a few hours and write in some of those features in Excel, but we'd advise that you'd be better served relying on a more full-featured accounting product—at the very least a freeware product—to manage that information.

After all: Time is money.

About

Ken Hardin is a freelance writer and business analyst with more than two decades in technology media and product development. Before founding his own consultancy, Clarity Answers LLC, Ken was a member of the start-up team and an executive with TechRe...

0 comments