This guide takes you from opening the file to your first accuracy report. The short version: set your rules once, paste your cycle-count export, tell the workbook which column is which, and read the dashboard.
Want the thinking behind the numbers? Read Inventory Record Accuracy: How to Calculate It.
Step 1: Save a master copy
Save the original file somewhere safe and work from a copy, for example Cycle Counts - Main DC.xlsx. If anything breaks, a fresh copy from the master beats repairing formulas.
Step 2: Set your rules in 02_SETTINGS
Yellow cells are yours to change.
- Facility name: shows on the dashboard.
- Data source: Import (paste a system export into 04_IMPORT_COUNTS) or Manual (type counts into 03_COUNT_LOG). Only one is active at a time.
- Record accuracy target %: the share of counts that should be within tolerance.
- Count tolerance %: a count is accurate when |count − book| ≤ book × this %. 0% means an exact match only. The tracker applies one tolerance to all counts.
- A, B, and C count frequency (days): reference values for your cycle-count calendar.
The values that ship in the file are placeholders. Replace them with your own.
Step 3: Paste your export into 04_IMPORT_COUNTS
Paste the whole export starting at cell A4, header row included. You don’t need to delete columns or rearrange anything.
- Any column order works, up to 100 columns.
- Title rows above the header are fine. The workbook finds the header row on its own.
- Numbers stored as text, including SAP-style trailing minus signs like “5-”, are converted automatically.
- Capacity is 5,000 count lines per import.
- Dates must be real dates or text that your Excel recognizes.
- Requires Excel 2010 or later on Windows or Mac.
Step 4: Map your columns in 05_DATA_MAPPING
Tell the tracker which header in your export holds each field. Use the yellow dropdowns in the Mapped source header column.
| Tracker field | Required? | What it is |
|---|---|---|
| SKU / Material | Yes | Unique item identifier |
| Book Quantity | Yes | System quantity before the count |
| Count Quantity | Yes | Physical counted quantity |
| Count Date | Yes | Date of the count; drives the weekly trend |
| Description | No | Readable item description |
| Location | No | Bin or storage location |
| Unit Cost | No | Used for dollar variance |
| Reason Code | No | Variance reason, translated into a root-cause category |
| ABC Class | No | Enables accuracy by class |
Check the Found in your export? and Import check columns. When every required field is found, the import check turns green. If the workbook picks the wrong header row, type the correct row number into Header row override.
Reason codes
Different systems use different codes. List the codes from your export in the translation table and pick a root-cause category for each. Any code you haven’t mapped shows as Unrecognized, so add it and choose a category. You only do this once per export layout, and the mapping stays saved.
Step 5: Review 08_DASHBOARD
The dashboard shows:
- Record accuracy against your target
- Quantity accuracy
- Absolute and net dollar variance
- Weekly accuracy trend against target
- Accuracy by ABC class
- Top 10 variances by absolute dollars
- Variance by root cause
How the metrics are calculated
| Metric | Definition |
|---|---|
| Record accuracy % | Share of counted lines where ABS(count − book) ≤ book × tolerance |
| Quantity accuracy % | Average of 1 − ABS(count − book) ÷ book per line. Shows how far off the misses are. |
| Absolute $ variance | Sum of ABS(quantity variance) × unit cost. What the misses cost, regardless of direction. |
| Net $ variance | Sum of quantity variance × unit cost. Overs and shorts offset each other. |
Period accuracy comes from summed counts rather than an average of weekly percentages.
Review it weekly. Look at the few SKUs and root causes driving most of the dollar loss, and assign fixes to those.
Manual mode
If you don’t have a system export, set Data source to Manual in Settings and type counts into 03_COUNT_LOG, one row per count. Amber headers are inputs and teal headers calculate on their own. Root cause comes from a dropdown list in the log.
Troubleshooting
| Symptom | Check first | Then try |
|---|---|---|
| Import check says ERROR | 05_DATA_MAPPING column E | Fix every field marked NOT FOUND by picking the exact header from the dropdown. |
| Dashboard is blank | Settings → Data source | Import reads 04. Manual reads 03. Make sure your data is in the tab you selected. |
| Wrong header row | 05_DATA_MAPPING → Header row override | Type the correct row number into the override cell. |
| Accuracy looks too low | Settings → Count tolerance | 0% means exact match only. Set the tolerance your operation uses. |
| Root cause shows Unrecognized | Reason-code translation table | Add each listed code and pick its category. |
| Weekly chart has gaps | Count Date mapping | Check that dates show as dates, not text, in 06_NORMALIZED_COUNTS. |
Things to avoid
- Renaming the tracker’s field names; it finds your data by header name.
- Deleting rows from your export before pasting it.
- Typing into system tabs.
- Editing formulas in tabs 06 and 07.
About the file
It’s a standard .xlsx with no macros. System sheets have red tabs and are protected without a password. Use Review → Unprotect Sheet only when you’re troubleshooting. Keep your master copy.
Questions? hello@logisticsdeck.ai