Hello and welcome to Excel Highway, your one-stop shop for all things Excel! Today, due to popular demand, I’m sharing an Accounts Payable Template that you can use to track your customer invoices, see where they stand, and know what payments are still pending. This simple file will give you a great overview of your financial situation.
Template Overview
This template includes five sheets:
- Customer Dashboard (we’ll end the video with this one)
- Invoice Status
- Payments
- Invoices
- Parameters
Let’s walk through them!
1. Parameters Sheet
The Parameters sheet is where you set up your customer names and define the time frame for your invoices. Here, you’ll input:
- Customer name
- Invoice date
- Expected payment due date
For example, I’ve randomly generated customer names (using a name generator I built), and you can adjust these values as you need. The best part? You can use data validation to restrict the range of the due dates, making sure everything stays organized.
You’ll also notice that you can create a drop-down list to set the due date range (e.g., 30, 60, or 90 days) to fit your business needs.
2. Invoices Sheet
The Invoices sheet is where the magic happens. This is where you’ll input all invoice details:
- Invoice number
- Customer name
- Description of the service or product
- Amount due
The good news? The formula automatically pulls the due date based on the time frame set in the Parameters sheet. Plus, the due dates are automatically aggregated into monthly buckets for easy tracking. This helps keep you on top of what’s due, even as you enter new invoices.
For instance, if you’re entering an invoice for April, the system will automatically categorize it for that month, even if the invoice is from an earlier date.
3. Payments Sheet
The Payments sheet is where you track any payments against invoices. You’ll start by selecting the invoice number (which pulls from the Invoice Status). From there, you’ll input:
- Payment amount
- Payment date
The system will flag whether the payment was made on time. For example, if a payment is received late, it will show up in a “Late Payment” column in yellow, helping you easily identify any overdue balances.
4. Invoice Status Sheet
The Invoice Status sheet is fully automatic and pulls in the status for each invoice. You’ll see:
- Customer name
- Amount due
- Amount paid
- Balance remaining
- Invoice status (Open/Closed)
This sheet is especially useful for tracking the current status of your invoices in real-time. It filters out closed invoices and only shows the open ones, so you always know what needs attention.
5. Customer Dashboard
The Customer Dashboard is the final piece of the puzzle. It allows you to analyze your customers on both a granular level and an overall monthly level. You can:
- See which customers are paying on time and which are not.
- Track outstanding balances and total payments.
- View payment status and overdue amounts.
With the help of SumIfs, Filter, and Unique functions, you’ll get a clear overview of your customer’s payment performance and can focus on customers with overdue payments.
How to Use the Template
- Parameters Sheet: Define the customer and invoice time frame.
- Invoices Sheet: Input invoice details and let the system automatically pull due dates.
- Payments Sheet: Add payment details and track outstanding balances.
- Invoice Status Sheet: Automatically track invoice statuses.
- Customer Dashboard: Analyze payments on a customer and monthly level.
This simple, yet powerful template is designed to give you a complete overview of your accounts payable situation.
Final Thoughts
Whether you’re tracking a few invoices or dozens, this template will keep your accounts payable in order. With its simple yet effective structure, you’ll always know which invoices are due, which payments have been made, and which ones are still pending.
If you found this video and template helpful, don’t forget to hit that red subscribe button, like the video, and share it with your friends and colleagues. Also, feel free to leave a comment – I’d love to hear from you!
Thanks for reading, and I’ll see you in the next video. Take care!