How to manage trade credit accounts without spreadsheets

Credit is how most wholesale businesses win and keep trade customers. A restaurant, a corner shop or a caterer wants to collect stock today and pay at the end of the week or the month. Offering that is normal. Tracking it properly is where things go wrong.

We have run a wholesale business in London since 2014, and for years our credit lived in a spreadsheet. Three people edited it. Nobody fully trusted it. Every Monday morning someone spent three to four hours pulling figures, chasing balances and emailing statements one by one. That was someone's entire morning, every single week.

This guide covers what we learned about running trade credit properly, and what to look for in a system so that the spreadsheet can go.

Why spreadsheets fail for trade credit

A spreadsheet is not wrong on day one. It fails slowly, for predictable reasons.

• It is a copy, not the source. The sale happens at the till. The payment happens at the counter, by bank transfer or on the phone. The spreadsheet is a second record that someone has to update by hand, after the event. Every manual update is a chance to miss something.
• Several people edit it. When three people can change a balance, nobody owns it. A typed-over cell leaves no trail of who changed what, or why.
• The balance and the history drift apart. A customer asks why they owe a certain amount. You add up the rows and get a different number. Now you are arguing with a good customer about your own records.
• Limits are not enforced. A credit limit written in a column does not stop the cashier from putting another order on account. You find out the customer is over the limit when the statement goes out.
• Statements are a weekly project. Building statements from a spreadsheet means filtering, copying, formatting and emailing, one customer at a time.

None of these are discipline problems. They are structural. The fix is structural too: the credit record has to be the same record that the sale and the payment are written to.

The principle: one ledger, three views

In a properly run credit system, a customer's balance, their transaction history and their statement are three views of the same ledger. They cannot disagree, because they are not separate documents.

That is how the Back Office in PalletPOS works. Balances are ledger-driven: every sale on account, every payment and every refund is written as an entry, and the balance is derived from those entries. The statement you send is the same history the customer would see if they asked. When a customer queries their balance, you open their transaction history and walk through it line by line.

If you are evaluating any system for trade credit, ask this question first: is the balance stored separately, or is it calculated from the transactions? If it is stored separately and updated by hand, you have bought a nicer spreadsheet.

Setting up trade accounts properly

Before you extend credit through any system, get the account structure right.

1. Separate approved trade accounts from everyone else

Not every customer who asks for trade prices should get them. In PalletPOS, wholesale pricing is applied only to approved trade accounts, and the till validates the account at the point of sale. A cashier cannot simply switch a walk-in customer onto trade prices. That one rule protects your margin more than anything else on this list.

2. Decide the terms per customer

For each trade account, record:

• the credit limit;
• the payment terms you have agreed (weekly, fortnightly, monthly);
• which price tier applies, and any customer-specific prices;
• who at the customer receives statements.

Write these down once, in the account, rather than in someone's head.

3. Make On Account a payment method, not a note

When a trade customer takes goods on credit, the sale should be recorded as an On Account sale at the till, against that customer. It then appears immediately on their balance, their history and their next statement. No one has to remember to update anything later.

The wholesale POS in PalletPOS treats On Account as a payment method alongside cash and card, and shows the customer's balance and credit position at checkout so the cashier can see where the account stands before adding to it.

Taking payments against an account

Payments are where spreadsheets most often go wrong, because they arrive through so many channels: cash at the counter, card, bank transfer, a part payment on a large balance.

A few rules that have served us well:

• Record every payment against the customer, at the time it is received. Not at the end of the day, not on Monday.
• Allow part payments. Many trade customers pay a round amount towards a balance rather than settling specific invoices. Your system should accept deposits and part payments cleanly.
• Protect against duplicates. A payment recorded twice is as damaging as a payment missed. PalletPOS has duplicate-payment protection built into its payment ledger, and the ledger is append-only: entries are added, not overwritten, so corrections leave a trail.
• Use refunds, not deletions. If something was charged wrongly, refund it. Deleting history is how balances stop adding up.

You can read more about how payment methods, part payments and refunds work on the payments page.

Statements without the Monday morning

The weekly statement run is the single biggest time cost of a spreadsheet credit system. In our case it was three to four hours, every Monday.

When balances, history and statements come from the same ledger, a statement is a report rather than a project. The steps become:

1. Choose the statement period.
2. Review the accounts with balances outstanding.
3. Send the statements.

There is no reconciliation step, because there is nothing to reconcile. The figures on the statement are the figures in the account.

We still recommend a short weekly review, but its purpose changes. Instead of building the statements, you are reading them: which accounts are growing, which are near their limit, who has not paid this period.

We replaced spreadsheets, a separate POS, and four hours of manual invoicing every Monday — with one system that our team actually enjoys using.

That quote is from a London wholesale business that has processed daily trade orders since 2014. The time saved is real, but the bigger change is trust. When the numbers agree everywhere, the conversations with customers get easier.

Controlling who can do what

Credit is money. Treat access to it the way you would treat access to the till drawer.

• Use roles. Cashiers should be able to make On Account sales to approved customers. They usually should not be able to change credit limits or price tiers. PalletPOS has users and roles in the Back Office, and permissions are checked on the server, not just hidden in the interface.
• Keep validation on the server. A rule that only exists on the till screen can be bypassed. Trade-account approval, pricing and duplicate-sale protection should be enforced centrally.
• Unlimited users helps here. If adding a login costs money, businesses share logins, and then you lose any record of who did what. Every PalletPOS plan includes unlimited users, so each person can have their own account.

A weekly credit routine

Once the system is in place, the routine is short. Here is the one we use:

Everything in that table works from one place. Nobody opens a spreadsheet.

Moving off the spreadsheet

If you are switching today, the migration is mostly about opening balances.

1. Pick a cut-over date. Ideally just after a statement run, when balances have been checked with customers.
2. Agree opening balances. Confirm each trade customer's balance with them before you import it, if you can.
3. Import customers, prices and balances. On the Wholesale and Complete plans, activation includes assisted setup and data import.
4. Stop editing the spreadsheet. Keep it read-only for reference. If anyone updates it after cut-over, you have two sources of truth again.
5. Send the first statement from the new system and invite customers to query anything that looks wrong. Queries in the first cycle are normal and useful.

Next steps

If trade credit is what you are fighting with every week, start with the Wholesale plan, which includes trade accounts, tier and customer pricing, On Account, statements and invoices. You can compare plans and start a trial from the pricing page.

Start trialSee pricing