Tax and GST

A supplier ledger with a running balance

A supplier ledger format with one page per supplier: bills, payments and a running balance, built for a month with three bills and two payments.

Editorial illustration for A supplier ledger with a running balance

A supplier ledger is one page per supplier with four columns that matter: the date, a bill or payment reference, the amount billed or paid, and a running balance. The balance at the bottom is what you owe that supplier. Built once in a spreadsheet, it takes a few seconds to update after each bill or payment.

What are the columns?

ColumnHeadingWhat to enter
ADateDate of the bill or payment
BReferenceBill number, or UPI or NEFT reference for payments
CBilledAmount of a bill, blank for payments
DPaidAmount of a payment, blank for bills
EBalanceRunning balance owed

Keep one sheet tab per supplier, named with the supplier's name, or one long sheet with a supplier column if you have few suppliers.

What does one month look like?

Say a small workshop buys from a steel dealer. In a month there are three bills and two payments, all amounts invented.

DateReferenceBilledPaidBalance
03 OctBill ST-501₹12,000-₹12,000
10 OctNEFT payment-₹12,000₹0
15 OctBill ST-517₹8,500-₹8,500
22 OctBill ST-533₹15,000-₹23,500
28 OctUPI payment-₹10,000₹13,500

The check: bills total 12,000 plus 8,500 plus 15,000, which is ₹35,500. Payments total 12,000 plus 10,000, which is ₹22,000. Owed: 35,500 minus 22,000 is ₹13,500, matching the last balance.

What formula gives the running balance?

In cell E2 of the first row, type the formula below, then fill it down. It adds all bills up to the current row and subtracts all payments up to the same row, so it never needs the previous balance.

  • =SUM($C$2:C2)-SUM($D$2:D2)

Blank cells count as zero, so the formula works for both bills and payment rows. It works in Excel and in Google Sheets. If you prefer to add a row at the bottom each time, copy the formula down by dragging the corner.

How do you spot what is owed?

Look at the last row's balance. It is the amount still due to that supplier. To see all suppliers at once, make a summary tab with one line per supplier and a formula pulling each ledger's final figure, or simply type the closing balances in a column and add them with =SUM() at the bottom.

What should you reconcile each month?

  1. Ask the supplier for a statement of account, if they provide one.
  2. Compare their closing balance with yours.
  3. If the figures differ, find the first row where they disagree.
  4. Common causes are a missing bill, a payment not credited or a credit note.
  5. Write the difference and how it was settled.

When does a tracker help?

Payments are easy to forget to enter. In SpendixAI you can import your bank statement, review the rows and see all payments to a supplier grouped by merchant name, which gives you the Paid column without retyping. Possible duplicate payments, with the same amount to the same merchant within two days, are listed in a report. The Billed column still comes from the supplier's bills.

What if a supplier supplies on credit for months?

The running balance becomes even more useful. Add a column F called Age of oldest unpaid bill, or simply note beside the balance which bill numbers it still contains. A supplier who is owed ₹13,500 mostly from last month's bills is different from one owed the same amount from bills raised this week. Paying in bill order, oldest first, keeps that picture clear unless you agree something else with the supplier.

A weekly upkeep list

  • Enter each new bill as it arrives.
  • Enter each payment the day it is made.
  • Check that balances are not negative without a reason.
  • File a copy of each bill with its row.
  • Review the totals owed on Monday.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports