Keeping records in a small shop

A small neighbourhood shop raises questions every day: which product is running out fast, which one has sat on the shelf for months, who bought on credit and has not paid back yet, why are there fewer boxes on the shelf than in the notebook? You can answer these from records rather than guesswork. In this guide we look at how to keep stock records in a small shop: when a notebook works and when a spreadsheet is better, the core tables and their columns, formulas that calculate stock and warn you, a credit book, stocktaking, the daily close-out, working with staff, backups and common mistakes. At the end there is a practice exercise and a checklist.

The example is illustrative: a family grocery shop in Qibray, owned by Sardor aka. He serves customers himself during the day, and his son Bekzod takes over in the evenings. All figures are made up, and money columns in the examples are shown as ”…” for you to fill in with your own data.

Why records matter

Many shopkeepers think “it is all in my head”. But as the range grows, an assistant joins or credit sales pile up, memory starts to fail. Records show you clearly:

• What sells. If you know how many sacks of flour and boxes of tea go each week, you order the right amount from your supplier.
• What sits on the shelf. Stock that does not sell for months takes up space and may pass its expiry date.
• When to reorder. If each product has a minimum stock level, you get a warning before it runs out.
• Debts. If you do not write down who took what on credit and what the shop itself owes its supplier, it gets forgotten or leads to arguments.
• Losses and theft. If the recorded stock does not match the real count on the shelf, something is wrong. Without records you would never notice the gap.

Sardor aka used to order sugar “by eye”: sometimes it piled up in the storeroom, sometimes it ran out just before a holiday. Once he started keeping records, he saw how much sugar went each week and adjusted his orders accordingly.

Notebook or spreadsheet

You can keep records in a paper notebook or in a spreadsheet. Each has its place.

A notebook is handy because:

• all you need to start is a notebook and a pen, and it works during a power cut;
• family members who are not used to computers can write in it too.

But you have to work out the stock by hand every time, entries are hard to find, and a notebook can be lost or torn.

A spreadsheet (Microsoft Excel or the free Google Sheets) is handy because:

• the stock is calculated automatically by a formula;
• filtering and sorting find any product or date in seconds;
• when stock runs low, the cell turns red by itself as a warning;
• making a copy is easy, and in Google Sheets you can even enter data from your phone.

If you stock only a few products and serve customers yourself, a notebook may be enough. Once you have more than a few dozen products, an assistant, or a lot of credit sales, it is better to move to a spreadsheet. A mixed approach also works well: the assistant writes in the notebook during the day, and the owner copies the entries into the spreadsheet in the evening. What matters is keeping one method going consistently, every day.

If spreadsheets are new to you, start with our guide to Excel and Google Sheets basics.

Core tables and their columns

Create several sheets in one file. The first row of each sheet holds headers, each cell holds one value, and there are no blank rows.

Product list

The foundation of your records: each product appears once, on one row.

The last four columns are filled by formulas (see below).

A code is needed because “Sugar”, “sugar” and “Sugar 1kg” are three different products to the program, while a code never changes. Keep one unit: if tea arrives in boxes but is sold by the packet, keep records in one unit (packets, say) and convert boxes into packets when goods come in. Minimum stock is the level at which it is time to reorder.

Goods in

Every delivery from a supplier is recorded here.

On the first day, add an “Opening stock” entry for each product: the quantity is whatever you counted that day.

Goods out (sales)

Any product that leaves the shop for any reason is recorded here.

The Type column: sale, credit, spoiled, taken for family, returned. A box of tea taken home is also stock that has left the shop.

If recording every sale separately is too much, you can enter one summary row per product at the end of the day.

The stock formula and low-stock warnings

The main rule is simple: stock = goods in − goods out. Opening stock is the first entry in Goods in, so you do not need a separate column. Never type the stock figure by hand; it is calculated only by formula.

Totals in and out with SUMIF

On the Products sheet, in cell E2 (Total in), type:

=SUMIF(In!B:B,A2,In!D:D)

Meaning: look for the code in A2 in column B of the In sheet and add up the quantities in column D of the matching rows. In F2 (Total out):

=SUMIF(Out!B:B,A2,Out!D:D)

In G2 (Stock): =E2-F2. In H2 (Status):

=IF(G2<=D2,"Reorder","Enough")

Fill the formulas down to the last product: a whole-column reference such as B:B stays the same, while A2 becomes A3, A4. Depending on your regional settings, the separator is a comma or a semicolon.

Sales for a period with SUMIFS

For sales over a given period, use SUMIFS, which takes several conditions. Type the start date of the period in K1, and in I2:

=SUMIFS(Out!D:D,Out!B:B,A2,Out!E:E,"sale",Out!A:A,">="&K1)

Meaning: add up goods out where the code equals A2, the type is “sale” and the date is on or after the date in K1. Note that in SUMIF the column being added comes last, while in SUMIFS it comes first.

Before filling this formula down, “lock” the K1 address: select K1 in the formula and press F4. Otherwise, as you fill down, K1 turns into K2, K3 and the result is wrong. This is called an absolute reference. Then sort the table by column I from largest to smallest: the best sellers rise to the top and the slow movers sink to the bottom.

Conditional formatting

1. On the Products sheet, select the stock column from G2 downwards (G2 should be the active cell).
2. In Excel: Home → Conditional Formatting → New Rule → “Use a formula to determine which cells to format”. In Google Sheets: Format → Conditional formatting → Format rules → “Custom formula is”.
3. Enter the formula: =G2<=D2
4. Choose a format: light red fill and dark red text → OK (Done in Sheets).

Now every product whose stock has dropped to the minimum is highlighted in red.

Entering codes without mistakes

On the In and Out sheets it is easy to mistype a code. Select the code column, go to Data → Data Validation (Data → Data validation in Sheets), choose the “List” type and point the source at the code column on the Products sheet. From now on codes can only be picked from a drop-down list.

The credit book

In a neighbourhood shop, selling on credit is normal, but unrecorded credit means lost stock and damaged relationships. Debt runs in two directions: credit given to customers, and what the shop owes its supplier (goods delivered first, paid for later). You can keep both on one sheet:

Rules:

• Goods sold on credit are also recorded on the Out sheet with the type “credit”; otherwise the stock will be wrong.
• Who, when and the due date must be clear; check the entry together with the customer.
• When a debt is settled, do not delete the row: write the date in the “Settled” column. The history is kept.
• Filter for rows where “Settled” is blank to see the list of open debts. Overdue ones can be highlighted with conditional formatting using the formula =AND(F2<TODAY(),G2="") on the due date column.

Stocktaking and analysing discrepancies

A spreadsheet only shows what was written into it. That is why, from time to time, you need to count what is on the shelves and compare it with the records. This is stocktaking.

How to do a stock count

1. Pick a time. When the shop is closed or quiet; record any sales made during the count separately.
2. Work in pairs. One person counts and calls out the numbers, the other writes them down.
3. Go in order. Shelf by shelf, then the storeroom; mark each shelf once counted.
4. Watch the units. Count items in an opened box one by one and convert them into the recorded unit.
5. Record the result on a separate sheet.

In the Difference column: =D2-C2. A negative number means a shortage, a positive one a surplus.

Analysing the difference

If you find a difference, do not rush to blame anyone. First check the simple causes:

• a sale or credit sale was not recorded;
• a unit was mixed up when goods came in (a box recorded as a packet);
• spoiled or expired stock was thrown away but not recorded;
• goods taken for the family were not recorded;
• the supplier delivered short and nobody counted on arrival.

Whether or not you find the cause, record the difference on the Out or In sheet with the type “count adjustment”; do not change the formula or old entries by hand. If the same product keeps showing a difference, count it more often.

For example, you might count fast-moving products once a week and the whole shop once a month.

Daily close-out, staff and tax requirements

Daily close-out

At the end of the day, check: have the sales been entered, has credit gone into the credit book, does the cash in the till match the records? A separate sheet is enough for this:

If there is a difference, write the reason down that same day; the next day it is hard to remember. You can get to know the basic ideas of accounting in our article on starting in accounting from scratch.

Online tills, electronic receipts and tax reporting

Requirements for till equipment, electronic receipts and tax reporting depend on the type of business and the tax regime, and they change from time to time. Check the current requirements with the tax authority, on its official website or at your district tax office. These tables are for internal management and do not replace official requirements.

Responsibility when working with staff

• A name on every entry (the “Entered by” and “Received by” columns).
• A quick count at shift handover. When Bekzod takes over for the evening, he and his father count a few of the fastest-moving products together.
• Limit access. In Google Sheets, use the Share button to give staff only the access they need. Protect the formula columns: in Excel, Review → Protect Sheet; in Sheets, Data → Protect sheets and ranges.
• Check the history. In Google Sheets, File → Version history shows who changed what.
• Agree the rules in advance. Who can be given credit and for how long, and how spoiled goods are recorded.

Backups and common mistakes

Backups

If several months of records are lost, they are almost impossible to rebuild, so a backup is essential:

• Keep the Excel file not only on the computer but also in a cloud service or on a USB stick.
• Google Sheets lives in the cloud, but files can be deleted by accident or you can lose access to the account: once a week, use File → Download → Microsoft Excel (.xlsx).
• If you keep a notebook, photograph the filled pages on your phone.

Read more about the options in our article Don’t lose your data: making a backup.

Common mistakes

• One product under several names, or boxes in one place and packets in another.
• Typing stock by hand, so errors go unnoticed.
• Leaving goods in “for later”, so stock goes negative.
• Not recording what you take for yourself, which causes “unexplained” differences at the count.
• Keeping credit only in your memory.
• Everything on one sheet. Goods in, goods out and the report get mixed up.
• No backup.

Once your records are in order, weekly sales and open debts can be shown as a dashboard.

Practice exercise and checklist

Set up one week of records for your own shop or for Sardor aka’s illustrative shop.

1. Create five sheets: Products, In, Out, Credit, Count.
2. Enter 10 products with a code, name, unit and minimum stock.
3. On the In sheet, record the opening stock and two or three illustrative deliveries.
4. On the Out sheet, enter a week of entries of different types: sale, credit, spoiled, taken for family.
5. Use SUMIF to total goods in and out, then add the stock and status formulas.
6. Use conditional formatting to highlight stock that has dropped to the minimum.
7. Use SUMIFS to work out weekly sales and find the three best sellers.
8. Record two customer debts and one supplier debt, and filter to show the open debts.
9. Enter an illustrative count for three products, calculate the difference and add an adjustment entry.
10. Make a backup of the file.

Checklist

• Every product has a code and a single unit.
• Stock is calculated only by formula.
• Goods in, goods out and credit are recorded on the same day.
• Minimum stock is set and the warning works.
• You can see the list of open debts within a minute.
• Stock counts are regular and reasons for differences are recorded.
• Every entry shows who made it, and formula columns are protected.
• A backup is made every week.
• Till and tax requirements have been checked with the tax authority.

Tidy records are the basis for decisions about expanding the shop or adding new products. If you are just starting out, also read our article on starting a small business.

If you would like to study record keeping and accounting systematically with a teacher, take a look at our association’s free accounting programme and other training programmes. When you are ready, submit an application and our specialists will get in touch.