Retail Shop Ledger Template (Excel + Google Sheets)

A retail shop owner in Saudi Arabia lives every day between the cashier, the supplier, the customer, and the bank. With each sale or purchase, a new piece of paper piles up in the drawer: a supplier invoice for stock, a Mada terminal receipt, a bank transfer slip, an IOU from a customer who will pay at month-end. By the end of the day, the owner is staring at a stack of papers and a small handwritten notebook, trying to answer one question: did I make a profit today or not? How much is actually left in the cash drawer? And does the inventory match the sales?

The real problem is not the volume of work, it is the absence of a clear bookkeeping system. Many shop owners in Riyadh, Jeddah, and Dammam rely on a handwritten ledger that starts neatly at the beginning of the month, turns into chaos after two weeks, and is forgotten entirely before the end of the quarter. The result: the owner does not know the real net profit, cannot file an accurate VAT return with the Zakat, Tax and Customs Authority (ZATCA), and only notices an employee dipping into the cash drawer when it is far too late.

This page gives you a ready-made ledger template built specifically for retail shops in the Saudi market. It covers everything you need: recording daily sales by payment method, tracking expenses and purchases, following up on suppliers and credit customers, calculating 15% VAT, and running an organized daily and monthly close. The template is downloadable in Excel and Google Sheets, and you can run it directly inside Qoyod to turn it from a static file into a live accounting system that works for you around the clock.

What a retail shop ledger is, and why it differs from a traditional general ledger

A retail shop ledger is the daily record that captures every financial movement passing through the shop: every riyal entering the cash drawer, every riyal going out to a supplier, every piece of stock moving from the shelf into a customer’s hands. The fundamental difference between this ledger and a traditional general ledger is that the retail ledger combines several functions in one place: journal, inventory, customers, suppliers, and cash, all linked together in real time.

The traditional general ledger was designed for large companies with a separate accounting department, with a strict separation between the American journal, the subsidiary ledger, and the general ledger. But a shop owner running 80 to 250 transactions a day has neither the time nor the team for that level of complexity. They need one practical ledger that answers three questions at every moment: how much did I sell today, how much did I earn, and how much is left in the cash, the inventory, and the receivables?

The core function of the ledger in an active retail shop

• Tracking daily revenue: recording sales by payment method (cash, Mada, bank transfer, or credit), and separating VAT-inclusive sales from net revenue.
• Controlling expenses and purchases: documenting every supplier invoice and every operating expense such as rent, electricity, employee wages, and internet bills.
• Following up on receivables and payables: knowing who owes the shop money and whom the shop owes, with due dates and aging schedules.
• Calculating 15% VAT: separating output VAT from input VAT in preparation for the monthly or quarterly return to ZATCA.
• Daily close: reconciling the physical cash drawer against the book balance at the end of every working day.

When the paper ledger turns from tool to burden

The paper ledger works well in the first few months of a shop’s life, when transaction volume is low and the owner is the only one behind the cashier. But the moment the first employee is hired, the first credit supplier arrives, or annual sales cross the 375,000 SAR VAT registration threshold, the paper ledger starts to crumble under the weight of the data.

The differences between a handwritten ledger and an electronic ledger linked to an accounting system

Many shop owners think the difference between paper and electronic bookkeeping is just a change of medium, but the difference goes much deeper. An electronic ledger linked to an accounting system like Qoyod turns recording into a live data source that feeds purchasing, hiring, and pricing decisions, while a paper ledger remains a dead archive that no one reads unless ZATCA asks for it.

Speed and accuracy

In a handwritten ledger, recording one sale takes 40 to 60 seconds: writing the date, the invoice number, the item, the quantity, the price, the total, and the tax. In an electronic system, the same transaction takes 4 to 7 seconds, and the system calculates the tax automatically and deducts from inventory in real time.

Interconnection between modules

• Paper: every module is isolated. The sales ledger knows nothing about the inventory ledger, the inventory ledger knows nothing about the supplier ledger, and you carry the mental load of tying them together.
• Electronic: every sale deducts from inventory, adds to revenue, calculates the tax, and updates the credit customer’s balance in one single step.

Readiness for e-invoicing

ZATCA mandates e-invoicing for commercial establishments in its second phase, and a handwritten ledger cannot issue an XML invoice that is signed and approved. An electronic system issues it automatically and submits it to the authority in real time.

Protecting data from loss

A paper ledger can burn, get lost, or be eaten by humidity in the shop’s storeroom. A cloud system stores every transaction on secured servers with multiple backups, and the shop owner never loses their data no matter what happens to the device.

The mandatory columns in any retail shop ledger

Any ledger worthy of the name must contain a set of core columns that let you read the full financial transaction at a single glance. The downloadable template includes all these columns, but it is important to understand why each one is there and how it is used.

Why the running balance column is non-negotiable

Many handwritten ledgers skip this column because it requires constant addition, but it is the difference between a ledger that helps you and one that confuses you. The running balance tells you at any moment exactly how much you actually have in the cash drawer, the bank, or in customer receivables, and it lets you catch any recording error within seconds by matching the book balance against the actual balance.

A ready-made chart of accounts for a retail shop

The chart of accounts is the skeleton of any accounting ledger. The template provides a simplified chart, battle-tested across dozens of retail shops in Saudi Arabia, classified according to the international accounting sequence with adaptations for the Saudi market.

How to use the chart of accounts day to day

• Tie every movement to an account number: every sale goes to 4100, every rent payment to 5200, every stock purchase to 1300. This lets monthly reports generate themselves.
• Leave room to grow: notice the numerical gaps in the chart (1110, 1111, then 1200). They allow new accounts to be added without breaking the sequence, such as a new bank or a new branch.
• Separate owner’s drawings: the biggest mistake shop owners make is mixing personal expenses with shop expenses. Account 3200 solves this problem at the root.

How to record daily sales

Sales are the lifeblood of a retail shop, and the way they are recorded defines the quality of the entire ledger. In the Saudi market, payment methods have become very diverse: cash, Mada, bank transfers, credit, and delivery platforms like Jahez and HungerStation for food retailers. Each payment method needs slightly different accounting treatment.

Cash sales

The simplest and most direct type. The customer pays in cash, walks out of the shop, and the transaction is done. The accounting entry: debit cash, credit sales and output VAT. Example of a sale worth 230 SAR including tax:

• Net sales: 200 SAR recorded in account 4100.
• Output VAT: 30 SAR recorded in account 2200.
• Cash on hand: 230 SAR recorded in account 1100.

Sales by Mada and credit cards

This is where the card network commission shows up, deducted automatically by the bank, usually between 0.4% and 1.85% depending on the card type. Do not forget to record the commission as a separate expense in account 5250, otherwise your revenue will look higher than it really is. The funds reach the bank within one or two working days at most Saudi banks.

Credit sales to B2B customers

If your shop sells to other shops, restaurants, or companies on credit, the transaction must be recorded in accounts receivable (account 1200), not in cash. The template includes a separate sheet for tracking each credit customer with their balance, debt age, and due date.

Sales through delivery platforms

Food shops and restaurants in Riyadh and Jeddah increasingly rely on platforms like Jahez, HungerStation, and Careem Now. These platforms deduct their commission from the gross invoice (between 18% and 30%) before transferring the amount. You must record the full value as revenue and then record the commission as a separate expense. Do not record only the net amount, or you will lose the ability to see your real sales volume.

How to record expenses, purchases, and suppliers

The other side of the ledger, and the side where accuracy is usually neglected. Shop owners care more about revenue than expenses because revenue is more exciting, but expenses are what eat into profit, and the accuracy of recording them is what decides whether you will ever know your true net profit or not.

Purchases from suppliers

When goods are received from a supplier, the transaction must be recorded immediately, even if it is on credit. The entry: debit inventory (1300) and input VAT (1400), credit accounts payable (2100). Example of a stock purchase worth 11,500 SAR including tax from a supplier in Souq Al Salam in Riyadh:

• Net purchases: 10,000 SAR to inventory account 1300.
• Input VAT: 1,500 SAR to account 1400. This is later offset against output VAT in the tax return.
• Accounts payable: 11,500 SAR in account 2100, settled later per the agreed terms.

Fixed operating expenses

Rent, electricity, internet, employee wages, maintenance. These recur monthly at roughly the same value and should be scheduled in the ledger ahead of time so they do not catch you off guard at month-end. The template includes a fixed-expense schedule that can be filled in at the start of each year.

Managing the supplier register

• A record for every supplier: trade name, tax number, contact number, opening balance, payment terms (cash, 30 days, 60 days).
• Tracking payment due dates: the template generates an alert for every invoice approaching its due date within 7 days.
• Accounts payable aging report: classifies amounts owed by aging bucket: less than 30 days, 30 to 60, 60 to 90, more than 90.

Daily and monthly closing of the ledger

The close is the moment of truth. Everything you recorded during the day or the month must match the actual reality: the cash drawer, the bank balance, the inventory. If the numbers match, your ledger is sound. If they do not, the hunt for the error begins.

Daily close steps

• Physically count the cash: count every banknote and coin in the drawer and compare against the book balance at the end of the day.
• Review terminal receipts: print the Mada terminal report and compare the day’s total transactions against what is recorded in the ledger.
• Record any variance: if the difference is less than 5 SAR, it is usually a rounding error and is booked to a cash-variance account. If it is larger, stop and investigate before closing the day.
• Close the drawer: pull all cash except an opening float for tomorrow, and deposit the rest in the shop safe or prepare it for the bank deposit.

Monthly close steps

The monthly close is deeper and more time-consuming than the daily one. It usually takes 2 to 4 hours for a medium-sized retail shop. The steps include:

• Physical inventory count: count the stock physically and compare against the book inventory. Any variance is recorded in an inventory shrinkage or shortage account.
• Bank reconciliation: pull the bank statement and compare every transaction on it against what is recorded in your ledger.
• VAT aggregation: sum the output VAT for the month, subtract the input VAT, and the difference is what must be remitted to ZATCA.
• Generating profitability reports: the income statement, the balance sheet, and the receivables report.

E-invoicing for retail shops

ZATCA has required all VAT-registered establishments to apply e-invoicing, and Phase Two has moved to a direct integration with the Fatoora platform. For a retail shop still issuing paper invoices or relying on an old cash register, this is no longer optional.

E-invoice requirements

• XML format or embedded PDF/A-3: not a JPG image or a Word file, but a structured machine-readable format.
• Cryptographic stamp and digital signature: every invoice must be digitally signed with a certificate approved by the authority.
• Direct integration with the Fatoora platform: the invoice is submitted in real time to receive a clearance number before being delivered to the customer.
• QR code: contains the core invoice data so the customer and any ZATCA officer can verify it.
• Mandatory fields: the seller’s tax number, the precise date and time, line-item details, the net amount, the tax, and the total.

The difference between a B2C and a B2B invoice

Shops selling to the end consumer issue a simplified invoice, smaller and less detailed, but it must still include a QR code, an invoice number, and the issue date. Credit sales to companies (B2B) require a full tax invoice that includes the buyer’s tax number and their full commercial address.

The reports the ledger generates each month

The ledger is not the end of the story, it is the beginning. The real value shows up in the reports it produces at the end of each month, which answer the decisive questions for purchasing, pricing, and expansion decisions.

Income statement (monthly profitability)

Shows net sales, cost of goods sold, gross profit, operating expenses, and net profit. Example: a grocery shop in the Al Malaz district of Riyadh with monthly sales of 145,000 SAR, cost of goods of 98,000 SAR, gross profit of 47,000 SAR, operating expenses of 28,000 SAR, and net profit of 19,000 SAR. These figures do not surface clearly from a paper ledger without hours of manual work.

Inventory aging report

• Fast-moving items: sold within 7 to 30 days. These are the cash generators of the shop.
• Slow-moving items: sitting on the shelf for 60 to 120 days. These need a price cut or a promotional push.
• Dead stock: more than 180 days without a sale. These should be cleared at cost to free up capital.

Receivables aging report

Classifies credit customers by aging bucket: less than 30 days (normal), 30 to 60 (watch), 60 to 90 (call), more than 90 (becomes a doubtful debt). This single report can save the shop tens of thousands of SAR a year in debts that would otherwise be written off.

Zakat and tax report

Generated with one click. Shows total output VAT, input VAT, and the net amount payable to ZATCA, with the option to export the return ready to upload to the authority’s platform.

Common mistakes in retail shop bookkeeping

After following thousands of retail shops in Saudi Arabia, the same mistakes repeat themselves in different shapes. Knowing them in advance saves the shop owner months of accounting headaches and thousands of SAR in penalties.

Mixing personal accounts with shop accounts

The most common mistake, and the most damaging in the long run. The owner takes cash out of the drawer for personal expenses without recording it, or pays shop bills from their personal account. The result: they do not know their real profit and cannot give accurate figures to the authority. The fix: account 3200 (owner’s drawings) must be used for every riyal leaving the shop for non-business purposes.

Delayed recording

Piling up the day’s invoices to record them at the end of the week, or the month’s invoices at month-end. Every day of delay raises the chance of forgetting a transaction or losing an invoice. The golden rule: record the same day, and preferably the same moment, using an electronic system.

Ignoring returns

• Returns reduce sales and output VAT together: failing to record them inflates the tax return and you end up paying more tax than you owe.
• They require a separate credit note: it is not enough to strike out the original invoice. An electronic credit note must be issued through the Fatoora platform.

Relying on the owner’s memory

“I remember everything” is a common and dangerous phrase. Human memory fails after 3 to 5 days, and any transaction not recorded within 24 hours has more than a 60% chance of being lost. The electronic system solves this at the root because it forces recording at the moment of the transaction.

Not reconciling the cash drawer daily

Postponing the cash count to the end of the week or month makes catching theft or error nearly impossible. A daily reconciliation surfaces the variance immediately and allows investigation while the trail is still fresh.

How Qoyod helps you manage your shop’s ledger

Qoyod is not just an electronic substitute for the paper ledger, it is a complete accounting system designed for the Saudi market and tuned to the needs of retail shops of every size. The moment you sign up, you find the retail shop sector ready in the retail shops sector in Qoyod with a chart of accounts already in place.

E-invoicing fully included

Qoyod is approved by ZATCA for Phase Two of e-invoicing. It issues invoices in signed XML format, submits them to the authority in real time, and returns the clearance stamp and QR code ready on the invoice without any intervention from you.

Sales, purchases, and inventory in one interface

• An invoice in three clicks: pick the customer, add the item, issue. The system calculates the tax, deducts from inventory, and updates receivables in real time.
• Reorder point alerts: you are alerted when an item is close to running out, based on its historical average consumption.
• Instant financial reports: income statement, balance sheet, cash flows, and receivables aging, all one click away at any point in the month.

Integrations with point-of-sale systems and banks

Qoyod integrates with a number of approved point-of-sale systems in the Saudi market, allowing daily sales data to flow automatically from the cashier device into the ledger without any manual entry. It also supports integration with bank statements to speed up monthly reconciliation.

24/7 support in Arabic

The Qoyod support team is available 24 hours a day, 7 days a week, answering accounting, operational, and technical questions in Arabic, by live chat or phone. To find the plan that suits your shop’s size, see the pricing page, and for a full view of the capabilities, see the features page.

Frequently asked questions

Can I use the template without signing up to Qoyod?

Yes, the template is available in Excel and Google Sheets and can be used on its own. But to get the most out of it, especially for e-invoicing and integration with ZATCA, it is recommended to run it inside Qoyod, where it turns from a file into a live, connected system.

How long does it take to set the ledger up for real use?

If you will use the template in Excel, you need 30 to 45 minutes to fill in opening balances and the chart of accounts. If you move to Qoyod, setup takes only 10 to 15 minutes because the chart of accounts and the sector are pre-configured.

Is the template suitable for a small shop with low transaction volume?

Yes, the template is built with the flexibility to disable unused sheets. A small shop running 20 to 40 transactions a day only needs 4 sheets out of 12, and the rest are kept for future growth.

What do I do if I discover an error in a previous entry?

Do not erase the old entry. Add a correcting entry on the date you discovered the error that reverses the financial effect, with a clear note in the description field referring to the original entry number. This preserves the audit trail, which is a ZATCA requirement.

Does the template handle sales in currencies other than the Saudi riyal?

The base template is built for the Saudi riyal. If you have sales in US dollars or UAE dirhams, it is preferable to use Qoyod, which supports multi-currency pricing with automatic exchange rate updates.

How do I record stock the shop owner takes for personal use?

It is recorded at cost in the owner’s drawings account (3200), with a matching reduction in inventory (1300) for the same amount. This entry is essential to prevent the cost of goods sold from being inflated and to keep the gross margin accurate.

Is the template compliant with ZATCA requirements?

Yes, the template separates output VAT from input VAT and keeps records that align with the authority’s requirements. However, Phase Two e-invoicing requires a system connected to the Fatoora platform, which is what Qoyod provides.

How often should I update the physical inventory count?

The minimum is once a month with the monthly close. For active shops (more than 100 transactions a day), the better practice is a weekly partial count of one item category on a rotating basis, so that the full inventory is counted every 4 to 6 weeks without disrupting operations.