Accounts receivable and accounts payable are two sides of the same cash flow picture. Accounts receivable is money customers owe you. Accounts payable is money you owe vendors and suppliers. Tracking both in Excel gives you a complete view of your near-term cash position — what is coming in, what is going out, and whether you will have enough cash to cover your obligations.
Most small businesses track one or the other but not both in a connected way. This guide covers setting up both trackers and linking them to a simple cash flow view that shows your net position at any point in time.
Money customers owe you for work already done or products already delivered. Your invoices that have not been paid yet. This is an asset on your balance sheet.
Money you owe vendors, suppliers, and contractors for goods or services already received. Bills you have not paid yet. This is a liability on your balance sheet.
The gap between AR and AP is your net working capital position. If you have $30,000 in outstanding invoices from customers but $40,000 in bills due to suppliers in the next 30 days, you have a cash flow problem even if your business is technically profitable. Tracking both together is what gives you that visibility.
Your AR tracker logs every outstanding invoice with automatic aging — how many days each invoice has been unpaid — and groups them into aging buckets so you can see at a glance how much is current, how much is 30-60 days late, and how much is seriously overdue.
| Column | What to Enter or Formula |
|---|---|
| Invoice # | Your invoice number |
| Customer | Who owes you money |
| Invoice Date | When the invoice was sent |
| Due Date | When payment is due |
| Invoice Amount | Total amount invoiced |
| Amount Paid | Payments received so far, 0 if unpaid |
| Balance Due | =Invoice Amount minus Amount Paid |
| Days Outstanding | =IF(Amount Paid=Invoice Amount, 0, TODAY()-Invoice Date) |
| Aging Bucket | =IF(Days=0,"Paid",IF(Days<=30,"Current",IF(Days<=60,"31-60 Days",IF(Days<=90,"61-90 Days","90+ Days")))) |
| Status | =IF(Balance=0,"Paid",IF(Due Date less than TODAY(),"Overdue","Pending")) |
Your AR aging report — the most important AR summary — shows how much is outstanding in each aging bucket:
| Customer | Current | 31-60 Days | 61-90 Days | 90+ Days | Total |
|---|---|---|---|---|---|
| Smith Co | $3,200 | $0 | $0 | $0 | $3,200 |
| Jones LLC | $0 | $1,800 | $0 | $0 | $1,800 |
| Davis Corp | $0 | $0 | $4,500 | $0 | $4,500 |
| Brown Inc | $0 | $0 | $0 | $2,100 | $2,100 |
| Total | $3,200 | $1,800 | $4,500 | $2,100 | $11,600 |
Your AP tracker logs every bill you owe — vendor invoices, contractor payments, supplier charges — with due dates and payment status. This is what tells you what cash needs to go out and when, so you can plan your payments around your incoming cash.
| Column | What to Enter or Formula |
|---|---|
| Bill # | Vendor's invoice number for reference |
| Vendor | Who you owe money to |
| Category | What type of expense this is |
| Bill Date | When the bill was received |
| Due Date | When payment is due |
| Amount Due | Total amount owed |
| Amount Paid | What you have paid so far |
| Balance | =Amount Due minus Amount Paid |
| Days Until Due | =Due Date minus TODAY(), negative means overdue |
| Priority | =IF(Days Until Due less than 0,"Overdue",IF(Days Until Due less than 7,"Pay Now",IF(Days Until Due less than 30,"Pay Soon","OK"))) |
Total AP due in the next 30 days — your most important AP summary number:
With both AR and AP tracked, your net cash position dashboard answers the question every small business owner should be able to answer at any time: considering what customers owe me and what I owe vendors, what is my true cash position?
The net 30-day cash flow number is what tells you whether next month is going to be comfortable or tight. If you have $18,400 in the bank but $15,000 of AP due next week and only $4,000 of AR expected before then, you have a problem that needs action now — not after the bills come due.
Tracking AR and AP separately is useful. Tracking them together is genuinely powerful because it gives you a complete, real-time picture of your cash position that goes beyond just your bank balance. A business with $50,000 in the bank but $60,000 of bills due next week is in a very different position than one with $50,000 in the bank and $80,000 of customer payments arriving this month.
Most small businesses do not have this visibility. Having it puts you in a fundamentally stronger position to manage your cash, make decisions about spending, and avoid the kind of cash crunches that force bad decisions.
If you would like a custom AR and AP tracking workbook built for your business, with your customers, your vendors, and your payment terms, that is exactly what we do at HelpMyData.
Tell us about your billing and payment setup and we'll build a complete Excel accounts receivable and payable system with aging reports and cash flow visibility. Starting at just $75.
📧 Get Started Today