Retail Shop Inventory and Valuation

Who this is for

A shop count valued twice: once at what the stock cost you and once at what it is on the shelf for. Department, line, barcode, quantity and both prices on every row.

Independent shopkeepers, market traders and shop managers who count at the year end or before a sale, and need a stock figure for the accounts and a shelf figure for themselves.

What this helps you track

• What is on the shop floor and in the stockroom, grouped by department
• The quantity of each line, with its barcode or supplier code for reordering
• The value of the stock at cost, for the accounts
• The value of the same stock at the shelf price, for planning a sale
• The lines that tie up the most money, once the sheet is totalled

How to use it

1. Write the shop, the department or area, who is counting and the date at the top.
2. Work one fixture or shelf bay at a time. Write the line, its code and its department, then the quantity counted.
3. Enter your cost per unit and your current shelf price on each line. Use the price as it stands on the day of the count, and note any line that is already marked down.
4. Value at cost and value at retail are worked out per line in the browser and the workbook, along with the totals and the summary. On paper, multiply across each line.
5. Count the stockroom on a separate sheet or mark the lines clearly, so that floor stock and back stock can be told apart afterwards.
6. Keep the signed sheet with your year-end papers; the cost total is the figure the accounts will want.

Formats

• Printable PDF (A4 and US Letter)
• Fillable PDF with form fields
• Fill in online, saved in your browser
• Excel / Google Sheets workbook

The printable is laid out inside a box that fits both A4 and US Letter, so the two files are identical and neither one is scaled. The fillable PDF has real form fields. The workbook uses plain formulas only — SUM, IF, COUNTIF and the like — so it opens in Excel, LibreOffice and Google Sheets without anything breaking, and every calculated cell is grey so you can see which figures it worked out.

How to print

Download the A4 or US Letter file to match the paper in your printer, open it in a PDF reader and print at 100% — not "fit to page", which shrinks the writing space. Both files leave a 14 mm margin, which clears the unprintable edge on home printers. To print the online version with your entries in it, use the Print this sheet button, which prints only the sheet and none of the page around it. More on printing.

Questions

Which figure do my accounts need, cost or retail?

Usually the cost figure. The retail total is for your own planning — it shows what the stock would bring in at today's prices and how much room there is for a markdown. Check with your accountant which basis they expect.

Does it work out my margin?

It gives both totals side by side, which is the raw material for a margin, but it does not apply a percentage of its own. Any markup or margin figure is yours to calculate from your own numbers.

How do I handle marked-down stock?

Enter the price it is actually selling at in the retail column and write 'reduced' in the department or a note. That way the retail total reflects the shop as it stands rather than as it was priced.

Can I count the whole shop on one sheet?

For a small shop, yes — the printed sheet runs onto a second page and the browser version adds as many rows as you need. Larger shops usually find a sheet per department quicker to check.