Latest Excel Version (see Google Sheets version below):
$45.00 USD
How it works?
There are a few versions for entering data.
There are a few versions for entering data.
The first is an Excel version that uses a macro and an input card. The user can enter receivable invoices in one input card and payables in another.
There is also a non-macro version included. On this version, the user can enter invoice data directly on the database tabs (one tab for receivables and one for payables) and bypass the input card.
There is also a direct database entry version included, which was built in Google Sheets. You will have all the necessary fields needed so that automatic tracking can happen. This version also utilizes some automated dynamic array formulas that are not available in Excel.
Note, when you go to clear out the example data in the database tabs, make sure you don't overwrite the last two columns in the database tabs where each invoice record resides because they are formulas that define the aging buckets each invoice is in.
Tracking payables and receivables is likely to improve your cash conversion cycle.
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 data analysis that is on the AR module is included for the AP module. The template will track how much money you owe / have paid out / and current balance on a per invoice and per vendor basis as well as the amount owed by aging bucket. To lighten up the file, you can remove some of the tracking years if not needed.
There is some VBA (one button per AR/AP and one 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 three 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 of these visuals go for one year. This can easily be changed to 2/3/4/5/10 years if desired.
Capacity
You will be able to set up to 250 customers / 100 vendors to start. This can be expanded to more than 5,000 if needed and Excel probably hits a limit around 10,000 unless you have a more powerful 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 number 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 more advanced version. The workaround 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 accounts receivable and payable spreadsheet tracker useful, you may enjoy using this inventory tracking template and this FIFO compliant COGS inventory valuation tool in excel
Tracking payables and receivables is likely to improve your cash conversion cycle.
Both versions are included in the one-time price and are included on the download page after purchase.
Accounts Receivable
On a per customer and per invoice basis, the template will automatically display what money has been collected, what money is yet to be collected, and what the remaining balance owed to you is. This allows the breakdown of multiple invoices for the same customer.
Accounts Receivable
On a per customer and per invoice basis, the template will automatically display what money has been collected, what money is yet to be collected, and what the remaining balance owed to you is. This allows the breakdown of multiple invoices for the same customer.
Aging receivable reports also automatically populate based on the data entered. The buckets include:
- Current
- 1-30 Days
- 31-60 Days
- 61-90 Days
- 91-120 Days
- 121+ Days
These can be edited on the 'validation' tab to be any day ranges that you want. The template will display the total value of invoices that reside in each aging bucket.
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 data analysis that is on the AR module is included for the AP module. The template will track how much money you owe / have paid out / and current balance on a per invoice and per vendor basis as well as the amount owed by aging bucket. To lighten up the file, you can remove some of the tracking years if not needed.
There is some VBA (one button per AR/AP and one 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 three 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 of these visuals go for one year. This can easily be changed to 2/3/4/5/10 years if desired.
Capacity
You will be able to set up to 250 customers / 100 vendors to start. This can be expanded to more than 5,000 if needed and Excel probably hits a limit around 10,000 unless you have a more powerful 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 number 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 more advanced version. The workaround 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 accounts receivable and payable spreadsheet tracker useful, you may enjoy using this inventory tracking template and this FIFO compliant COGS inventory valuation tool in excel
NOTE, I no longer offer two free hours of service with the purchase of this template.
More Accounting Spreadsheets / Calculators / Tools:
- Activity-Based Costing Model
- 13-Week Cash Flow Planner
- Sales and Purchases on Account - 3-Statement Helper
- Job Bidding
- Cost Segregation Study
- Financial Statement Generator
- Budget vs Actual Planner
- WACC Calculator
- Equipment Purchase Cost Benefit Analysis
- 3-Statement Financial Model - For Startups
- Tracking Billable Hours
- Volume Discount Pricing
- To-Do List Tracker
- Inventory Template for a 3-Statement Financial Model
- Payroll Calculator and Tracker - Budget vs. Actual
- Profit / Loss and Cash Flow Tracking in Real Time
- Break-even Analysis
- Cash Conversion Cycle
- COGS Inventory Template - (FIFO Based)
- Depreciation Expense Tool
- Depreciation Recapture Calculator
- Expense Tracker
- Financial Statement KPIs
- Google Sheet Inventory Tracker
- Inventory Reordering Planner
- Inventory Restocking Cash Flow Management
- Job Costing
- KPI Dashboard - General Contractor
- Lending Business or P2P Lending Participant
- Project Management
