Account Receivable / Payable Spreadsheet

Apparently I have a few readers who have small businesses, and darn it, they're having a tough time tracking how much money they're owed by certain customers. How do I know this?

Easy — they email me and ask for help!

My full-featured suggestion for tracking things like this — small-biz accounts receivable and accounts payable — would be QuickBooks. My quick 'n' dirty solution, however, is Excel (or OpenOffice, if you prefer free software).

Creating a spreadsheet to handle this task isn't tough. Really, it's not much more than a modified check register spreadsheet. Thus it comes out looking like this:

In the workbook, each customer or vendor has its own worksheet; you can make copies of the blank worksheet as necessary. Simply enter transactions just as you would in a check register, and the spreadsheet will keep a running balance on each worksheet. Download a copy below:

I'm confident that you'll find the Account Receivable / Payable spreadsheet to be super-easy to use. But a few more details might be in order:

Making Copies of the Blank Worksheet

The spreadsheet comes with single blank Account Receivable / Payable worksheet. If you have more than one customer or vendor to track (let's hope!), you'll want to expand the spreadsheet and make multiple copies of this worksheet — one for each customer or vendor.

Not sure how to make copies of the blank worksheet? It's easy!

1. Right-click the blank worksheet.
2. Select "Move or Copy."

3. Highlight "(move to end)" and place a checkmark in "Create a copy."

4. Click OK.

That should do it. You now have another blank worksheet.

Entering Transactions

When you download the spreadsheet, it will already be populated with a few sample transactions from an imaginary Account Receivable for a customer named Baytown Hills Properties.

As the spreadsheet shows, Baytown Hills currently owes us $333.63. We've performed four services for them, and they've made two payments. Hopefully these transactions will give you an idea of how the spreadsheet functions.

Once you've looked over the sample transactions, delete them. You're ready to begin entering your own.

Simply enter your customer's (or vendor's) opening balance in Cell H6, and the date of that balance in Cell A6.

Your transactions (obviously) populate all the following rows!

Questions? Comments? Contact Me!

Got a question or issue? Don't hesitate — drop me a line. I'll get back to you as soon as possible!