DataMosaic#What's in it
- Count: one row per store and SKU, for up to 1,000 lines. You fill in the white columns, and the grey ones work themselves out. A line that does not agree is shaded.
- By store: type a store's name to see its lines counted, lines that agree, record accuracy, and variance in units and at cost, plus a total for all stores.
- Reasons: the list behind the Reason column. Edit it to match your own.
- How to use: the steps below, inside the file.
The Count sheet arrives with six example rows from an invented store, so you can see the format:
| Store | SKU | System | Counted | Variance | Value | Reason |
|---|---|---|---|---|---|---|
| Store 014 | TEE-BLK-M | 24 | 24 | 0 | 0.00 | |
| Store 014 | TEE-BLK-L | 18 | 15 | −3 | −27.60 | Not yet known |
| Store 014 | CAP-NVY | 9 | 9 | 0 | 0.00 | |
| Store 014 | SCARF-GRY | 6 | 7 | +1 | 11.40 | Counting error |
| Store 027 | TEE-BLK-L | 11 | 13 | +2 | 18.40 | Transfer not booked |
| Store 027 | SOCK-WHT-3P | 30 | 30 | 0 | 0.00 |
Invented figures. Delete these rows before you start.
#How to use it
- Export system stock for the stores you are counting, at the moment the count starts. Stock that moves during the count is the commonest false variance.
- Paste it into Count: store, SKU, description, unit cost and system quantity, one row per store and SKU.
- Type the counted quantities. Leave a line blank if it was not counted, so it does not count as a match.
- Sort by Absolute value and look at the largest variances first.
- Give each variance you explain a reason, so the next count starts from a pattern rather than from nothing.
- Read By store for each store's record accuracy and the value of its variances.
#The formulas
Nothing is hidden. These are the ones that do the work, as they sit in row 6:
Variance (units) =IF(OR(F6="",E6=""),"",F6-E6)
Variance value =IF(OR(G6="",D6=""),"",G6*D6)
Absolute value =IF(H6="","",ABS(H6))
Lines counted =COUNTIFS(Count!$A$6:$A$1005,A5,Count!$F$6:$F$1005,"<>")
Lines that agree =COUNTIFS(Count!$A$6:$A$1005,A5,Count!$G$6:$G$1005,0)
Record accuracy =IF(B5=0,"",C5/B5)#Why record accuracy, and not just the totals
On the example rows, Store 014 is two units and 16.20 short in total. That sounds small. But three of its four lines are wrong: one shirt size is three short, a scarf is one over, and the two partly cancel out. Its record accuracy is 50%, and the absolute value of its variances is 39.00, more than twice the net.
Totals hide the errors that cancel out; record accuracy and absolute value do not. That is why the template shows all three side by side.
#When a template isn't enough
A template is the right tool for a count. It is the wrong tool for the question in between counts: does the stock the ERP holds still match what the stores and the web shop say? That changes every night, and nobody pastes a spreadsheet every night.
The checks for that are the same ones a reconciliation uses everywhere, worked through in data reconciliation: techniques, and a worked retail example.
#Questions people ask
#Is the template really free?
Yes. No sign-up and no email. Download it, use it and change it however you like.
#Does it work in Google Sheets?
Yes. Upload it to Google Drive and open it with Google Sheets. The formulas and the Reason list work there as they do in Excel.
#How is inventory record accuracy calculated?
Divide the number of counted lines with no variance by the number of lines counted. Store 014 above has 2 lines that agree out of 4 counted, so its record accuracy is 50%.
#What is a good inventory record accuracy?
It depends on the business and on what is counted. What matters most is the trend from one count to the next, and whether the reasons behind the variances are getting fixed.
