Sales book template in Excel: sales and due dates
Sales book template in Excel: sales and due dates
In many businesses the sales book is a notebook where someone writes down what was sold and, if there is room left on the line, the date the customer is supposed to pay. By the time the month closes, nobody can say with confidence how much was invoiced or what is still outstanding, and overdue invoices are found late, when the reminder call has already become awkward. It is rarely a lack of sales; it is the lack of an orderly record of those sales.
This sales book template in Excel keeps the daily record, the total of each document and a column showing how many days are left before each invoice falls due, all on one sheet. The same file answers two questions: how much was sold and how much is still to be collected.
The file comes with 200 record lines, drop-down lists for document type, payment method and status, totals at the foot of the sheet and two small summary tables. You download it, open it and start using it the same day, with no formulas to build from scratch.
⬇ Download the template (Excel .xlsx)What the sales book is and who it is for
The sales book is the chronological record of everything the business sold: what was sold, to whom, under which document and for how much. It is not a financial statement or a management report; it is the detail of the day's transactions, written in the order they happen. Its value lies in the fact that every sale keeps its supporting document and its date, so the figures can later be added up, compared and claimed.
What sets it apart from other controls is the axis of the record. A receivables report follows each document and each payment received, one by one, until the debt is cleared. Here the axis is the sale of the day: what was invoiced and to which customer. That is why the sales book answers very concrete questions, such as how much was sold this week, how much was sold for cash and how much on credit, or which invoices are already past their payment date.
The template is built for small and medium businesses, for the trader who invoices every day and for the accounting assistant who needs an orderly record before the figures go into the books. It works equally well for a shop, a workshop, a distributor or a service company, because the columns are general and do not depend on the industry or on the size of the business.
It also works as the memory of the business. When a customer calls three months later saying the goods never arrived, or when you want to check whether the selling price of a product has held steady, the sales book is the source that answers. A well-kept record removes the need to rely on one person's memory, which is the most common risk in small businesses.
How it relates to the other accounting books
The sales book feeds the other controls. What is recorded here as the sale of the day later becomes revenue in the general journal, accumulates by account in the general ledger and is summarised in the trial balance. On top of that, every credit sale with its due date is the raw material of the receivables list: without that date, the collection process has nowhere to start.
That is why the due date column should be filled in even when the sale looks safe. A credit sale with no due date is, in practice, a sale that nobody collects, because there is no set day for follow-up and no warning that the term has run out.
What this sales book template includes
The file is set up so that all you have to do is type the sales. The automatic columns and the lists are already configured, and the totals recalculate on their own as new lines are added.
| Component | What it does |
|---|---|
| 200 sales records | One row for each sale of the period; more rows can be inserted when the month is longer. |
| Drop-down lists | Document type, payment method and status, so the same sale is never written three different ways. |
| Automatic document total | Adds the base and the tax to get the total of each invoice. |
| Automatic days to due date | Compares the due date with today's date and shows how many days are left or how many days overdue the invoice is. |
| Totals block | Accumulates the base, the tax and the total of every recorded sale. |
| Small table by payment method | Separates what was sold for cash from what was sold on credit. |
| Small table by status | Counts how many sales are pending, paid or overdue. |
Every spreadsheet in the set shares the same logic of automatic columns, so if you already use the cash book or the purchase book you will recognise how this one works straight away. If you work alone, typing the sales of the day is enough; if several people use the file, agree on who reviews it at the end of the shift.
Columns of the sales sheet
Each column has a specific job. The ones marked «list» are filled from a drop-down and the ones marked «automatic» should not be typed by hand, because the sheet calculates them.
| Column | What it is for |
|---|---|
| Date | Day the sale was made; puts the record in chronological order. |
| Customer | Name of the buyer, so you know who was sold to and who has to pay. |
| Identification | Customer identification number, useful for matching the record with the documents. |
| Document type (list) | Invoice, sales note, delivery note or other; chosen from the drop-down. |
| Document number | Number of the document supporting the sale, so it can be traced later. |
| Description | What was sold, in a few words; helps you recall the detail months later. |
| Base | Value of the sale before tax. |
| Tax | Tax shown on the document, typed exactly as it appears. |
| Document total (automatic) | Base plus tax for that row. |
| Payment method (list) | Cash or credit, according to how payment was agreed. |
| Due date | Date the customer must pay; this is the column that makes follow-up possible. |
| Status (list) | Pending, paid or overdue; lets you filter what is still to be collected. |
| Days to due date (automatic) | Difference between the due date and today: positive while time remains, negative once it has passed. |
With the status and days-to-due columns you can filter the sheet and see only the overdue sales in seconds. That filter is the call list for the day: each row shows the customer, the document and how many days it has gone unpaid.
How the calculation works: an example with three sales
To see it with figures, imagine three September sales recorded in the template. The first is for cash, the second on credit with a due date the following month and the third falls due on the same day as the sale.
| Date | Customer | Document | Base | Tax | Total | Due date |
|---|---|---|---|---|---|---|
| 02/09 | Customer X | Invoice 1045 | 1,200,000 | 228,000 | 1,428,000 | 02/09 |
| 05/09 | Customer Y | Invoice 1046 | 800,000 | 152,000 | 952,000 | 05/10 |
| 09/09 | Customer Z | Invoice 1047 | 400,000 | 76,000 | 476,000 | 09/09 |
Each row total comes from adding the base and the tax: 1,200,000 plus 228,000 gives 1,428,000; 800,000 plus 152,000 gives 952,000, and 400,000 plus 76,000 gives 476,000. The totals block picks up those three lines and returns a base of 2,400,000, tax of 456,000 and a total of 2,856,000.
The days-to-due column changes meaning depending on the day you open the file. Invoice 1045 is a cash sale and falls due the same day it was issued, so it appears settled immediately. Invoice 1046 falls due on 05/10 and shows the days left until that date. Invoice 1047 falls due on 09/09, so if today is any later date, the column shows a negative number: that sale is overdue and needs to be collected.
One important point about the tax column: it is typed exactly as it appears on the sales document. The template does not calculate taxes, does not decide which rate applies to each transaction and does not replace any official record. It is an internal control tool, not a document that carries weight before third parties.
How to use the template, step by step
Using it is simple as long as you keep to an order. These six steps cover what you need to get going.
- Download the file and open it in Excel, in LibreOffice or in any compatible spreadsheet program.
- Type the date, the customer and the identification number in the first free row of the record.
- Choose the document type from the list and type the number of the document supporting the sale.
- Enter the base and the tax exactly as they appear on the document; the total calculates itself.
- Select the payment method, type the due date if the sale is on credit and choose the status.
- Check the totals block and the two small tables at the end of the day or the month to confirm the figures match the documents.
Tips and common mistakes
Most problems with a sales book do not come from the formulas but from the discipline used to fill it in. These are the slips that keep repeating.
- Do not leave the due date blank: a credit sale with no payment date is a sale nobody collects.
- Always use the lists: typing «credit», «Credit» and «CREDIT» breaks the summaries by payment method.
- Record the sale the same day: leaving the file until Friday piles up errors and omissions.
- Do not delete rows to correct: if a sale is cancelled, it is better to note it in the remarks or change its status than to let it vanish from the record.
- Reconcile against the documents: the totals block must match the sum of the documents issued in the period.
- Remember what this file is: it is an internal control template and does not replace any official record; the tax is typed exactly as it appears on the document and the template does not calculate taxes.
When to move to sales software
While a business invoices a few dozen documents a month, a spreadsheet is the fastest and cheapest tool: everyone understands it, it can be shared and it needs no installation. The change becomes necessary when the volume grows, when several people record sales at the same time, or when the file starts to exist in different copies, each one with different figures.
That is the point at which it is worth looking at a system. A tool such as Kardex Tauro keeps sales, receivables and inventory in one place, with documents numbered automatically and without the risk of overwriting someone else's work. The Excel template remains useful to get started, to tidy up a month that has already closed or to keep a parallel check, but it is not meant to replace the central record of the business.
If your sales currently live in a notebook or on loose sheets of paper, use the template for one full month before deciding anything else. At the end of that month you will have two figures you probably do not have today: how much you sold and how much is still to be collected.
⬇ Download the template (Excel .xlsx)
