Softwiro
Free Excel template

Cash book and customer accounts in Excel (free): quantity × price minus payments = amount due

Record cash in and out and what you sold each customer, and see what each one still owes and the month summary.

4 min read

You sell to customers on credit and move cash in and out of the till every day, and you want to know at any moment what the till holds and what each customer owes. The free cash book and customer accounts sheet does that: you enter items and payments once, and both the cash and the customer figures come out.

The problem

Many people keep a cash book and a customer book. A payment goes into both, one gets forgotten, the figures drift, and "what does Ahmed owe?" takes an hour of adding up.

How it works

  • In Settings enter the year and the opening cash balance, and in Categories your income and expense names.
  • In Customers enter each customer and phone.
  • In Sales enter each item with date, customer, description, quantity and price. Cost = quantity × price appears.
  • In Cash enter every receipt and payment. If a receipt is a customer payment, pick the customer.
  • Customers then shows each customer's items, total cost, total payments, amount due and status.
  • Monthly summary gives income, expenses and net per month.

How the numbers are worked out

  • Cost = quantity × price per item; a customer's cost is the sum of their items.
  • Payments = the sum of the cash "in" column on rows where you picked that customer.
  • Amount due = total cost − total payments: positive means "owes", zero "settled", negative "in credit".
  • Cash balance = opening balance + total in − total out up to that row.

Open the sheet at /ar/templates/cash-customers-account. It holds 300 customers, 2,000 items and 2,000 cash rows.

Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

Cheat sheet: cash box and customer accounts (1/3)Cheat sheet: cash box and customer accounts (2/3)Cheat sheet: cash box and customer accounts (3/3)
0 downloads · 3 pages

Questions this raises

How do I work out what a customer owes in Excel?

Enter each item with quantity and price to get cost = quantity × price, and pick the customer on each payment in the cash sheet. The sheet gives amount due = total cost − total payments.

Do I enter a payment twice?

No. Enter it once in the cash sheet with the customer name; it enters the balance and reduces what the customer owes.

Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project