Accounts Receivable and Payable Excel Template

This is an Accounting template to deal with the tracking of what money you are owed and what money you owe out. It's pretty simple, but when you go to build an excel template that is able to handle all of this in a user friend way, there are some complexities that come up and so a clean structure is key.

Latest Excel Version (see g sheet version below):




Pricing
You will be taken to a download page once the purchase is complete.

How it works?

Two input cards on the same sheet. One is for Accounts Payable and one for Accounts Receivable. There is also a non-macro version and a google sheet version that works in the same way except you are doing data entry directly on the database tabs. Formatting has been updated on that as well.

Both versions are included in the one-time price and are included on the download page after purchase.

Accounts Receivable

Based on the macro-based input card, you will be able to see on an aggregate and per customer basis what money has been paid, what money is yet to be paid, and what the balance is. Also, you will see for each invoice the amount of money that has been paid, what is still owed to you, and the balance. This allows the breakdown of multiple invoices for the same customer.

Additionally, you will be able to see a 15 year summary by month on the amount owed/paid/balance for each of your customers as well as on an overall basis.

Accounts Payable

The same tracking and same analysis by customer and by invoice as well as the 15 year monthly tracking of what you owe,paid,balance on a per invoice and per vendor basis.

There is some VBA (1 button per AR/AP and 1 button to update invoices across the board. This reduces errors and ensures you enter the data in a structured and organized way.

Visuals

You will have 3 charts. One to track monthly P&L, one for running Accounts Receivable (total amount owed to you/paid/running balance and one for Accounts Payable showing total amount owed out, total amount actually paid out, and total running balance by month. All go for 1 year. This can easily be changed to 2/3/4/5/10 years if desired.

Capacity

Also, you will be able to set up to 250 customers / 100 vendors for starters. This can be expanded to more than 5,000 if needed and excel probably hits a limit around 10,000 unless you have the SQL version of excel (Microsoft charges somewhere around $100 extra dollars for that and it can handle 100's of millions of rows quickly and efficiently.

As far as the # of total records you can have in regular excel, it will probably hit a max around 30,000 to 40,000 before things start to die out unless you have the higher-end version of Excel. The work-around is if you just have a single spreadsheet for each year and at the beginning of the next year you import / manually add any invoice that still has a balance.

Added a Google Sheets Specific Version (included in the $45 purchase above):


If you find this useful, you may also see benefits to this inventory tracking template

or...

this customer relationship manager in google sheets: http://www.smarthelping.com/2018/09/customer-pipeline-manager-crm-in-google.html