POS Data Mapping & Clean-Up Worksheet

Quick welcome — why this matters

Dirty or inconsistent POS mappings make your sales, food-cost and inventory numbers lie to you. This worksheet helps you turn POS events into reliable signals for costing, forecasting and ordering by mapping POS items, modifiers and combos to the canonical inventory and finance records your team uses every day.

What this guide gives you

• A clear mapping schema (what fields to capture and why)
• An example mapping table you can copy into a spreadsheet or database
• Practical validation checks (sales vs. consumption, price and COGS sanity checks)
• Common POS pitfalls and how to fix them
• A small governance playbook: owners, cadence, and change control

How to use this worksheet

1. Export a list of POS menu items, SKUs, modifiers, combos and categories.
2. Use the mapping table below to match each POS row to your inventory/recipe/finance master records.
3. Run the validation checks to find anomalies.
4. Fix mappings, update recipes/portions where needed, and lock changes behind a change-log and owner.
5. Repeat the checks weekly at first, then move to a regular monthly audit once stable.

Mapping schema — fields to capture

Capture these columns for every POS row. They form the minimum master mapping that supports accurate cost and inventory calculations.

• POS_Item_ID — unique identifier from the POS (SKU or PLU)
• POS_Item_Name — the descriptive name as shown in POS exports
• POS_Category — category in the POS (useful for front-of-house reporting)
• Canonical_Item_ID — link to your inventory/recipe master (ingredient or finished-goods ID)
• Canonical_Name — inventory/recipe name
• Item_Type — finished dish | recipe | ingredient | charge | modifier
• Portion_Size — portion tied to the POS item (units: each, oz, g, ml)
• Yield_Factor — recipe yield factor to convert menu portion to purchase units
• Modifier_Flag — yes/no (is this a modifier that alters ingredients?)
• Combo_Components — if a combo, list the POS_Item_IDs or canonical items it expands into
• Accounting_Mapped — GL code or finance category for reporting
• Last_Reviewed_Date and Owner — governance fields
• Notes — special handling, edge cases, or mapping heuristics

Example mapping row (spreadsheet-friendly)

Common POS pitfalls and how to handle them

• Composite items / combos: Many POS combos register as a single sale — you must map the combo to the underlying recipe components (or split sales) so ingredient usage is tied to inventory consumption.
• Modifiers that change cost: Free-text modifiers or open-price modifiers break automation. Convert common modifiers to structured modifier SKUs that map to ingredient or labor deltas.
• Portion ambiguity: If the POS tracks a menu item but not portion size (small/medium/large), add explicit portion SKUs or map each size to a unique POS_Item_ID.
• Duplicate names: Different POS items may use similar names. Rely on POS IDs, not names, and normalize names during mapping.
• Promotions and comp codes: Discounts, comped items, and coupons affect revenue but may not reflect ingredient consumption. Tag them and exclude or treat them specially in cost analyses.
• Back-of-house-only items: Prep-only SKUs might not appear in POS sales but consume inventory. Map and track them in inventory/recipes even if not sold directly.

Validation checks — practical reconciliation tests

Run these checks after mapping to find where data or mapping problems remain.

1. Sales vs. Expected Usage: Convert POS sales into expected ingredient usage using recipe portions and compare to actual inventory consumption. Flag variance thresholds (e.g., >8%).
2. SKU-level Price/COGS sanity: Compare average Selling Price * Quantity to recorded revenue for each POS SKU. Separately compute expected COGS from ingredient costs. Look for mismatches or negative margins.
3. Inventory Movement Gaps: Identify inventory usage not explainable by mapped POS sales (e.g., high butter usage with no corresponding sales).
4. Zero-Sales SKUs still in recipes: Find POS items with zero sales but mapped to recipes that still consume inventory; decide if they should be removed or left for reporting.
5. Modifier reconciliation: Ensure modifier counts on tickets match the number of modifier SKUs consumed in recipes.

Small governance playbook

Mappings are living data. Without owners and cadence they degrade. Use this lightweight playbook.

• Owner: Assign a Data Steward (often a Kitchen Manager + Finance contact). This person approves mapping changes and runs validation checks.
• Change control: Use a change log (spreadsheet or simple ticket) capturing who changed a mapping, why, and when.
• Cadence: Weekly quick checks for fast-moving menus during launch; otherwise monthly audits. Run full reconciliation quarterly or when menu changes occur.
• Access: Lock mapping master for edits; provide read-only views to shift leads and purchasing staff.
• Escalation: If variance > X% repeatedly, trigger a recipe review, waste audit, or inventory recount.

Quick checklist to get started (15–60 minutes)

1. Export POS item list and recent sales (30 days).
2. Open the mapping template and match top 80% SKUs by sales volume first.
3. Mark combos and modifiers for special handling.
4. Run one reconciliation check for the busiest week and note large variances.
5. Assign an owner and schedule the next audit.

Next steps and capability ideas

Once mappings are stable, automate the reconciliation and surface daily alerts for suspicious variance. Consider:

• Exporting the mapping as a canonical master that POS integrators, purchasing, and finance all read from.
• Automating sales-to-ingredient consumption transforms so inventory and ordering systems can consume clean signals.
• Promoting common modifiers to structured SKUs and removing free-text entries.

Where this guide fits in a larger toolkit

This worksheet pairs naturally with a Waste & Inventory Audit, Recipe Costing templates, and a Food Cost Dashboard. Treat mapping as foundational: clean mappings improve forecasting, purchasing, COGS reporting, payroll planning and AI-enabled forecasts.

Helpful reminders

• Map by POS ID, not only by name.
• Start with the high-volume items — they drive most variance.
• Keep a simple owner and change-log — people change menus; mappings must change intentionally.

Image search phrase: pos mapping worksheet