Stock Wizard help

Getting started

What it actually does

The Stock Wizard keeps track of what is in your shop and tells you what to buy. You count your stock, it remembers every sale and delivery from that moment on, and when things run low it builds a purchase order per supplier, ready to check and send. When a count does not match what the sheet expected, it writes the difference down, in money, on a tab that never gets wiped, so you can see whether the same product keeps going missing.

Underneath, every change to your stock is written into a diary called the movement log. Your stock figure is simply that diary added up, which is why the numbers can always be checked, and always repaired.

Setting up, once

The Product Master columns

Column	What to put
SKU	Your product code. Every one must be different.
Product Name	What you call it.
Category	Loose grouping, for example Candles. Used for counting one section at a time.
Supplier	Who you buy it from. Spell it the same way every time, because orders are grouped by this.
Cost Price	What you pay. This is what stock value and order costs are worked out from.
Sell Price	What the customer pays.
Margin %	Leave it alone. It works itself out.
Reorder Point	Get more when stock drops to this. Leave blank to use the default from Settings.
Pack Size	The smallest amount you can order, for example 6.
Lead Time Days	How many days your supplier takes to deliver.
Safety Stock	A few extra units of buffer. About half a week of sales is a good start.
Active	Tick it. Untick to retire a product without deleting its history.

The tabs, and what the colours mean

The tab strip along the bottom of the screen is colour coded by depth, so you can tell at a glance what each tab is for.

Colour	Meaning	Tabs
Deep blue	You type here	Settings, Locations, Product Master, Stocktake Entry, Sales Log
Mid blue	The sheet answers here	Start Here, Stock On Hand, Count Results, Order Builder, Dashboard
Pale blue	The diary. Leave it alone	Movements

Tab	What it is for
Start Here	The five step guide, written for somebody who has never seen the product.
Settings	Your business name, report email and a handful of defaults. Fill in the yellow boxes.
Locations	Your shops, one per line, with an Active tick.
Product Master	Everything you sell, shared across all your shops.
Stock On Hand	How many of each thing each shop has right now. The Stock Wizard writes this. Typing over it does nothing lasting, because the next update rewrites it from the diary.
Stocktake Entry	Where you type a count. It shows what the sheet expected and the difference, live, while you count.
Count Results	Every count line that did not match, forever, with what it was worth. See Count Results.
Sales Log	Where your sales go, typed or pasted from your till.
Movements	The diary of every change to your stock. The Stock Wizard writes it. Never type here.
Order Builder	The order maths for one shop at a time: how fast things sell, what to order, what it costs.
Dashboard	Your one page view: stock value, profit, what to order this week, top sellers, dead stock.

There is also a hidden tab called StocktakeHistory, where older diary entries are filed away each time you finish a count. You never need to open it.

The panel, your control room

Open it with Stock Wizard › Open the Stock Wizard panel. It sits down the right hand side of the screen and it always shows two things: how your shop is doing, and the one most useful thing to do next.

The three figures at the top

• Money in stock: what you have on the shelves, at cost price.
• To order: how many product lines are at or below their reorder point.
• Sales, last 7 days: your week, straight off the Sales Log.

DO THIS NEXT

The panel picks the single most important job and gives it a card with a button. A count that found missing stock comes first, then a shop overdue for counting, then ordering when something is below its reorder point. Deal with the card, press Refresh at the top, and the next most important thing takes its place. Everything else stays one click away under Everything else.

While you count

Start a count from the panel and it switches to a counting view with a progress bar, for example 12 of 40 counted. The numbers catch up every few seconds while you walk the shop and type. When you are done, press Finish the count right there in the panel, and it shows you what matched and what did not.

Counting stock

Starting a count

From the panel, click Count stock, pick your shop from the list, and pick how much of it you are counting: the whole shop, one supplier, or one category. Counting a section at a time is far easier than doing the lot, and completely safe, because of the carry forward rule below.

The same job is on the menu as Stock Wizard › Start counting stock.

Typing the count

Your products appear on the Stocktake Entry tab. Walk the shop and type what you count into the Counted Qty column. Beside each number, the sheet shows what it expected and the difference, live. Rows go red when you found less than expected and green when you found more.

Finishing

Press Finish the count in the panel, or Stock Wizard › Finish counting stock. Your counts become the shop’s new starting figures, the older diary entries for that shop are filed away, and you get a plain report: how many counted, how many carried forward, and every line where your count and the sheet disagreed, with the total your count was out by, in money.

Your stock does not change until you finish. A half typed count that never gets finished changes nothing, and the panel has a Throw this count away link if you want to abandon one cleanly.

Counting again when something did not match

When a count finds differences, the panel offers Count those lines again, and the menu has Stock Wizard › Count again what did not match. This lists only the products that were out, so a forty product count becomes a three product recount.

On a recount, the Expected column shows what your last count said. So the recount answers one precise question: if the difference is zero, your count was right and the stock really is missing. If you get a different number, the first count was the mistake, and finishing corrects it. Either way, once you have recounted, the Stock Wizard stops offering, because those differences have been checked. And when a recount confirms a loss, the panel points you at the tidy habit for next time: see writing off damaged or lost stock.

Count Results, your record of what went missing

Every count line that did not match is written to the Count Results tab, permanently: when it was counted, which shop, the product, what the sheet expected, what you counted, how far out it was, and what that difference is worth in money at that day’s cost price. Red rows are stock that was missing. Green rows are stock that turned up.

Nothing ever wipes this tab, so it becomes your shrinkage record. Sort it by product and you can see whether the same item keeps walking out of the door. Sort it by date and you can compare this month with last month. It is worth a look before you reorder anything that keeps coming up red.

A count where everything matched writes nothing here, which is exactly what you want the tab to mean: if it is empty, nothing has ever gone missing.

Writing off damaged or lost stock

Things break. A box gets dropped, stock gets water damaged, something quietly disappears. The honest move is to record the loss the day it happens, and that is what Stock Wizard › Write off damaged or lost stock is for. The same button sits under Everything else on the panel.

It asks four small questions: which shop, which product, how many, and what happened. Then it shows you the arithmetic, asks before writing anything, and puts one line in the movement diary with the date, your name, the quantity, and your reason. Stock drops straight away. The reason is the part your future self will thank you for, because six months from now “dropped a box unloading the van” explains everything.

Writing off more than the sheet says you have gets a warning first, and the figure then honestly sits below zero until the next count sets it straight. Every write off stays in the diary like any other movement, so the tools in this guide all understand it.

Your sales

Getting sales in

Type or paste your sales into the Sales Log tab, starting in cell A2. Five columns: date, product code, quantity, sale value, and the shop name in column E. Most till exports paste straight in, then you type the shop down column E and double click the little blue square to fill it down.

How sales reach your stock

Sales on the Sales Log are brought into the stock count automatically, every time you start a count, finish a count, or work out what to order. So mostly you never think about it. There is also a button for it, Stock Wizard › Bring my sales in, if you want to do it by hand and see the summary.

Bringing sales in is all or nothing on purpose. If any line has a problem, a made up product code, a missing shop, a text date, nothing is brought in and every problem is named with its row number, so you never have to wonder which half happened. And the same sale can never be counted twice, no matter how many times the same export gets pasted or the button gets pressed.

One quiet rule worth knowing: a sale dated before your last stocktake does not change stock, because when you counted the shelf, that sale had already happened and your count already includes it. It is filed into the history for the record instead.

Ordering

Work out what to order

Click Stock Wizard › Work out what to order, or the button on the panel. The Stock Wizard reads every suggestion from Order Builder, across all your shops, and builds one tidy purchase order tab per supplier, named PO – and the supplier. Each order lists what to buy, per shop, with costs and a total.

The suggestions come from real arithmetic: how fast each product sells in your own sales history, how long the supplier takes, your pack sizes, and your safety buffer. A product the maths is unsure about carries a warning in the Check column, for example a product never counted at that shop, and those warnings are repeated when the orders are built. Look at those lines before sending.

It needs a few weeks of sales in the Sales Log before its quantities get properly clever. Until then it leans on your reorder points, which is why they are worth filling in from day one.

Email the orders to suppliers

Click Stock Wizard › Email the orders to suppliers. You see the full list, who gets what and the totals, and nothing is sent until you say yes. Each order goes as a clean email from your own Gmail, with a copy back to you for your records. The first time you send to a supplier you are asked for their address once, and it is remembered.

Every sent order is stamped, on its own tab, with when it went and where. Run the send again and orders already sent are left alone, listed as such, so a supplier can never be sent the same order twice by accident. If you genuinely want to resend everything, it asks you plainly and does it. If a send fails, the failure is written on that order’s tab and just that order retries on the next run.

Deliveries

When stock turns up, open that supplier’s PO – tab and type what has arrived into the Received Qty column. Then click Stock Wizard › A delivery has arrived. The stock goes into the diary and Stock On Hand updates.

Receiving the same delivery twice adds nothing, and says so. That protection is built into the diary itself, not into your memory.

Tools, for when a number looks wrong

Check my data for mistakes

Stock Wizard › Check my data for mistakes reads everything and changes nothing. It looks for the small mistakes behind most problems: a product code typed twice, sales against a shop that does not exist, dates saved as text, missing cost prices, a product not counted in over ninety days, and the stock picture disagreeing with the diary. Every finding comes with what to do about it. Run it from the panel and the list stays on screen while you fix things.

Rebuild stock from the log

Stock Wizard › Rebuild stock from the log is the repair tool and the trust tool in one. It works every Stock On Hand figure out again from the diary and tells you exactly which figures it put right. If you ever doubt a number, or somebody typed over the stock tab, this is the button. It can only ever make the picture match the diary, so it is always safe to press.

The weekly email

Every Monday morning the Stock Wizard can send you a one page summary: the week’s sales and profit, your stock value, what needs ordering, top sellers, and what is gathering dust. It goes to the address in Settings cell B3.

• Turn it on: Stock Wizard › Weekly email › Turn the weekly email on
• Turn it off: Stock Wizard › Weekly email › Turn the weekly email off
• See one right now: Stock Wizard › Weekly email › Send me one now, to test it

Your data

Full detail is in our privacy policy.

Common problems

The Stock Wizard menu is not there

Reload the spreadsheet page. Google only builds the menu when a spreadsheet loads. If it is still missing after a reload, check you are in the right Google account, see below.

Stock On Hand is empty after a count

Your counts are safe in the diary; the tab just has no lines to show them on yet. Click Stock Wizard › Set my spreadsheet up. That gives every active product a line and redraws the figures from the diary. Then check the Active boxes are ticked on your shops and products, because only active ones get a line.

A product says “Not on Product Master”

The code on that row is not on your product list, usually a typo like SW-01 for SW-001. Fix the spelling, or add the product to Product Master. Finishing a count refuses politely until every code is real, and nothing is changed until then.

The Dashboard looks empty or the order quantities look silly

Nine times out of ten the sales dates are text, not dates. Run Stock Wizard › Check my data for mistakes and it will tell you, with the fix. The tenth time, there simply are not enough weeks of sales yet for the averages to mean much.

A stock figure has gone below zero

That is the Stock Wizard being honest rather than broken. It means more has been sold than it has ever seen counted, which happens when sales are brought in for a shop that has not been counted yet. Count that shop and the figure snaps to reality.

I typed over a cell and got a warning

The formula cells carry a warning, not a lock, so you can always push through if you mean it. If you did it by accident, press Undo. If a stock figure got mangled, Stock Wizard › Rebuild stock from the log puts it right.

The headers are not showing the nice font

The headers use a font called Montserrat, which is built into Google Sheets but sometimes needs waking up in an older file. Click any cell, open the font menu, choose More fonts, search Montserrat, tick it, then run Stock Wizard › Set my spreadsheet up again.

Setup says “This spreadsheet is not empty”

You ran it in a spreadsheet that already has its own tabs named things like Settings or Dashboard, and building would write over your work, so it refuses and names the tabs. The safe path is exactly what it suggests: make a fresh blank spreadsheet with File › New › Spreadsheet and run setup there.

I am signed into more than one Google account

Chrome numbers your signed-in accounts in the address bar as /u/0/, /u/1/ and so on. The Stock Wizard lives in the account it was set up in, so if it seems to have vanished, you are probably looking at the same file from a different account. Quickest test: open the spreadsheet in an incognito window and sign into one account only.

Two of us use the sheet at once. Is that safe?

Yes. Every job that writes to the diary takes a lock first, so two people finishing a count or receiving a delivery at the same moment queue up rather than scrambling each other. If something is busy you are told to wait a moment and try again, and nothing is half done.