This template will help any organization track who owes them money and how much and who they owe money to and how much. I am talking about an accounts receivable and accounts payable tracker. Using this should help improve your cash conversion cycle and general organization in regards to paying bills, collecting receivables, and managing cash flow expectations.

The template works in a pretty simple way. The user enters data into a receivables database and a payables database. From that data, all the reports will auto-populate.

Reports include:

  • Aging payables and receivables i.e. how much is owed per past due day ranges and how much is current (within 30 days).
  • Displays the total value coming due in the upcoming 11 months by month for both AR/AP
  • Shows the top 10 clients / vendors past due by aging bucket
  • Payable and receivable visualization analysis showing total owed / total paid / outstanding balance over a 12-month period
  • Individual payable and receivable reports show total sales / bills against total paid and total current outstanding by vendor / customer as of a defined date.
  • A report that lets the user pick a customer or vendor and see all invoices that exist for that particular account as of a defined date.
  • A monthly analysis by customer / vendor that shows total sales / billed against total paid / collected and outstanding balance for up to 36 months.

The user can enter vendor names / customer names in a validation tab and that will define the available entries via dropdown menus.

Receivables Data Entry Columns:

  • Date
  • Invoice #
  • Invoice Amount
  • Payment Amount
  • Work Type
  • Customer Name
  • Due Date
  • 10 extra fields

Payables Data Entry Columns:

  • Date
  • Type of Transaction
  • Invoice #
  • Vendor
  • Total Owed
  • Amount Paid
  • Due Date

For payments that are partial, you just need to enter the invoice # that matches the invoice being paid and then enter a negative in the ‘amount paid’ column. In this case, you would leave the ‘total owed’ blank since you only make an entry there when the invoice is initially created.

Note, the file has 3 versions that are all included. They are a Google Sheet, Excel - with Macro, and Excel with no Macro. The Google Sheet is accessible through a Microsoft Word doc that contains the link to the sheet. Just hit File > Make a Copy when you go to the link.

Reviews Add a review
No reviews yet

More From smarthelping

Browse our top rated business templates. See All
Franchisor Licensing Financial Model
 Starting a franchisor operation can be rewarding if done right and proper financial planning is conducted. You might have a…
Recycling Financial Model
 This is a recycling financial model. The timeline can be set for up to 10 years with monthly and annual…
Pawn Shop Simulation: 5 Year Financial Model
 This model was built with specific logic in mind for operating a pawn shop. This means two main ways to…
Pitch Deck Financial Visualizations: General Template
 This template is designed to make it easy to create professional visualizations. All the charts have been done so the…
Retail Locations Ramping Model: Up to 25
 This is a general 5-year financial model with dynamic assumptions for opening up (developing or acquiring) and operating up to…
Buy a House in 5 Years: Savings Planner
 If you are trying to save up money for a home, this personal finance template should make it easier to…
See All