POS Data Mapping Template (SKUs, modifiers, categories)

POS Data Mapping Template — SKUs, Modifiers, Categories

Purpose: Give teams a straightforward, repeatable template and instructions to map point-of-sale (POS) data to inventory, recipe and finance records so sales, cost of goods, and menu performance can be analyzed accurately.

When to use this template

• Onboarding a new POS, inventory, or back‑office system.
• Reconciliations after pricing or recipe changes.
• Diagnosing gaps between sales reports and inventory/COGS.
• Preparing consistent mapping for multi‑location rollouts.

How this template is organized

1. Instructions & recommended workflow
2. Mapping tables (templates + example rows)
3. Common mismatch checks & reconciliation examples
4. Validation checklist & monitoring KPIs
5. Next steps & capability notes

Quick workflow (recommended)

1. Export POS menu, SKU and modifier lists (include all IDs and names).
2. Export inventory master (item IDs, purchase UOM, pack sizes, AP costs), recipe database (recipe IDs, portion yields), and finance GL code list.
3. Fill the mapping tables below (one row per POS SKU / modifier option).
4. Run the mismatch checks and reconciliation examples to surface gaps.
5. Resolve mismatches (correct naming, create missing inventory items or recipe records, adjust portion yields, set revenue category/GL mapping).
6. Schedule a weekly or monthly reconciliation until mappings stabilize.

Mapping tables (use one spreadsheet tab per table)

1) POS SKU → Inventory / Recipe / Finance

One POS SKU per row. Add extra columns for local notes or status.

2) POS Modifier Option → Inventory / Pricing Impact

Modifiers that change cost or price (extra cheese, premium add‑ons, sides swapped) should be mapped so COGS and sales mix reflect reality.

3) POS Category → Revenue Category / P&L Mapping

Ensure POS categories map consistently to finance GLs and reporting categories.

4) Crosswalk: POS Payment Types & Discounts → Finance

Map payment types, discounts and comps so net sales and discounts appear correctly in accounting.

Common mismatch checks and reconciliation examples

Run these checks after mapping is populated. Use pivot tables or JOINs in your BI tool.

• Unmapped POS SKUs: Identify POS_Item_IDs with no Inventory_Item_ID or Recipe_ID. (Filter where Inventory_Item_ID is blank.)
• Multiple inventory items mapped to one POS SKU: Ensure intentional bundling (e.g., build kits) — otherwise fix duplicates.
• Modifier cost mismatch: Compare Modifier cost impact (Qty_Used × AP_Cost) to expected margin on modifier price.
• Category/GL drift: Compare last 30 days sales by POS_Category to mapped Revenue_GL totals — large differences indicate mapping errors or posting problems.
• Recipe vs. Inventory cost variance: For a sample of menu items, calculate Cost_Per_Portion (from recipe) and compare to Inventory-derived cost per portion. Flag >10% variance for review.

Reconciliation example (simple)

Goal: Verify that total food sales on POS ties to revenue GLs and that COGS computed from recipes matches inventory usage.

1. POS: Sum Net_Sales by POS_Item_ID for period.
2. Mapping: Join POS_Item_ID → Recipe_ID and compute COGS = SUM(Cost_Per_Portion × Qty_Sold).
3. Inventory: Compare expected usage (from recipes × qty sold) to actual inventory withdrawals or purchases for period.
4. Investigate variance: Missing recipe link, wrong portion size, or incorrect AP cost.

Spreadsheet formula hints

• Use INDEX/MATCH or XLOOKUP to bring Inventory AP_Cost into POS mapping rows.
• Compute Cost_Per_Portion = (AP_Cost_Per_Purchase_UOM / Purchase_Pack_Size) × Portion_Quantity_Used (adjust UOMs).
• Flag rows where Cost_Per_Portion is blank or > expected threshold with conditional formatting.

Validation checklist (before trusting mapped data)

• All active POS SKUs have Inventory_Item_ID or a documented reason for not mapping (e.g., delivery only).
• Modifiers that affect cost or price are mapped to inventory items and have Qty_Used defined.
• UOMs are normalized (LB vs LBS vs LB(s) cause mismatches).
• AP costs are current (update when vendor costs change).
• Recipe yields and portion sizes have been verified with kitchen test.
• Revenue categories map to finance GLs and are approved by accounting.
• Monthly reconciliation process and owner assigned (e.g., Ops lead + Finance).

Common pitfalls & how to avoid them

• Inconsistent naming: Standardize names and rely on IDs wherever possible. Use a canonical master file.
• Missing modifiers mapping: Unmapped modifiers create invisible cost or revenue — treat them as first-class mapping entries.
• Different UOMs: Normalize purchase UOM and recipe UOM; provide conversion factors when needed.
• Bundled or combo items: Break combos into component recipe mappings if you need accurate COGS per component.
• Multiple POS IDs for the same menu item: Consolidate or document intentional duplicates (e.g., location‑specific SKUs).

KPIs and monitoring to keep mappings healthy

• % POS SKUs fully mapped (target: 100% of active SKUs within 30 days of go‑live)
• Weekly unmapped SKUs trend
• Variance between recipe-based COGS and inventory usage (target: <10% after stabilization)
• Number of mapping changes per month (helps spot instability)

Next steps & operational guidance

1. Create a copy of this template as your canonical mapping workbook (one tab per mapping table).
2. Assign an owner for initial mapping and a secondary owner in finance to approve revenue GL mapping.
3. Run the mismatch checks weekly for the first 3 months, then move to monthly.
4. Document mapping rules and UOM conversion standards in a short SOP so new hires can maintain consistency.
5. Consider adding an automated reconciliation job in your BI tool once mappings are stable.

Example: Short troubleshooting guide

Symptom: Food sales appear correct, but food COGS from inventory is higher than recipe-based COGS by 20%.

1. Check for unmapped modifiers that add cost but not price.
2. Verify AP cost updates — a recent price increase may not be reflected in recipe cost.
3. Check UOM mismatch causing wrong cost-per-portion calculation.
4. Look for theft/waste or preparation yield issues recorded in inventory system.

Template distribution & change management

Keep the mapping workbook under version control (shared drive or document management). When a POS menu update occurs, require the ops owner to submit mapping changes before the menu goes live. Log all mapping changes with date, owner, and reason.

Suggested attachments and automation opportunities

• Attach a blank CSV export template matching the POS export fields so imports are repeatable.
• Automate bringing POS exports into the mapping workbook using your ETL or BI tool to reduce manual copy/paste.
• Build a reconciliation dashboard that highlights unmapped SKUs and COGS variance.

Where this template fits in a larger toolkit

This template is a core part of a POS Integrations toolkit that should include: mapping master, reconciliation dashboard, mapping SOP, and a mapping change log. Consider packaging these items together so locations or new restaurants can adopt them quickly.

If you want, we can convert this template into an interactive mapping form so teams can submit mappings, store them centrally, and track change history.