Softwiro
Free Excel template

Cash book and customer accounts sheet

A free Excel sheet that combines a cash book with customer accounts. You record cash received and paid out and the sheet works out the balance after each entry. You record what you sold to each customer by quantity and price, and the cost appears as quantity × price. Each customer's payments are taken from the cash book to give the amount due = cost − payments.

Excel file (.xlsx) · 202 KB · 8 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Try the sheet here, on the example

The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.

Example from the sheet

Example from the “العملاء” tab (Arabic), with the numbers and formulas that are in the file.
الإجمالي76,825.004,725.002,100.00
العميلعدد البنودإجمالي التكلفةإجمالي الدفعاتالمستحق عليهالحالة
مكتبة النور21,850.001,500.00350.00عليه مستحق
بقالة الأمل21,600.00600.001,000.00عليه مستحق
محل السلام22,250.001,500.00750.00عليه مستحق
شركة الفجر11,125.001,125.000.00خالص

Example from the “العملاء” tab (Arabic), with the numbers and formulas that are in the file.

What is inside

  • “الإعدادات” (Settings): The year and the opening cash balance.
  • “التصنيفات” (Categories): The income and expense categories you use.
  • “العملاء” (Customers): Each customer's name and phone number, with the number of items, total cost, total payments, amount due and status. It holds up to 300 customers.
  • “المبيعات” (Sales): One row per item: date, customer, description, quantity and price, with the cost worked out as quantity × price. It holds up to 2,000 items.
  • “الخزنة” (Cash book): Every cash receipt and payment with its date, description and category, and the cash balance after it. For a receipt that is a customer payment, you pick the customer's name and it is deducted from what they owe.
  • “ملخص شهري” (Monthly summary): For each category, what came in or went out in every month, plus total income, total expenses and the net.

The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.

What makes it different

A payment is entered once in the cash book. It raises the cash balance and reduces the customer's amount due at the same time, instead of being typed into two sheets where the numbers drift apart.

How to use it

  1. In the “الإعدادات” (Settings) tab, enter the year and the opening cash balance. In “التصنيفات” (Categories), enter your income and expense categories.
  2. In the “العملاء” (Customers) tab, enter each customer's name and phone number.
  3. In the “المبيعات” (Sales) tab, record each item sold: date, customer, description, quantity and price. The cost appears as quantity × price.
  4. In the “الخزنة” (Cash book) tab, record every receipt and payment. If a receipt is a payment from a customer, pick their name in the customer column.
  5. The “العملاء” (Customers) tab works out, for each customer, the total cost, total payments and the amount due = cost − payments.
  6. Open “ملخص شهري” (Monthly summary) to see income, expenses and the net for each month.

Before you start:

  • A payment is entered once in the “الخزنة” (Cash book). It raises the cash balance and lowers the customer's amount due together.
  • The “العملاء” (Customers) tab counts payments only from the receipts column. Money paid out does not enter a customer's account.
  • Write the customer's name in the same form in “العملاء”, “المبيعات” and “الخزنة”. The drop-down list prevents spelling differences.
  • Record cash book entries in date order so the running balance is correct.
  • The sheet holds 300 customers, 2,000 sales items and 2,000 cash book entries.

Excel file (.xlsx) · 202 KB · 8 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

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

Still running this on Excel?

This sheet needs someone to copy every invoice and payment into it. In Accountwiro an invoice and a payment are recorded the moment they happen, and the customer's balance shows in a single statement of account.

In this sheetIn Accountwiro
You copy every invoice and payment into the sheet by hand.It shows the customer's invoices, payments and outstanding balance in a single statement of account.
Invoices are typed or copied by hand.It creates an invoice by scanning a barcode or searching by product name, calculates discounts and taxes, and sends it to the customer over WhatsApp.
Stock shortages are discovered late.It updates quantities after every sale and alerts you when a product drops below the required level.
Anyone who opens the sheet sees every number.Permissions per user: the sales employee issues invoices, and the warehouse employee manages stock without access to financial data.

See Accountwiro · Ask for a demo

Frequently asked questions

How do I calculate what a customer owes in Excel?

Enter each item with its quantity and price and the cost appears as quantity × price. Pick the customer's name on every payment that goes into the cash book. The sheet then works out the amount due = total cost − total payments.

Do I enter a customer's payment in the cash book and in the customers sheet?

No. You enter it once in the “الخزنة” (Cash book) tab and pick the customer's name. It adds to the cash balance and is deducted from what the customer owes.

What does the status “دائن” (creditor) mean?

It means the customer has paid more than the cost of what they bought, so their balance is negative. The status “خالص” (settled) means they have paid exactly what they owed.

Is the template free?

Yes, with no sign-up and no email address. Use it, edit it and share it as you like.

Does it work in Google Sheets and on mobile?

The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.

What is the difference between the white and the gray cells?

You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.

Can I use the sheet from an AI assistant?

Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html

More free templates for: Shops and stores

More free templates

All templates · Free tools