📌 Stage 1 · Foundations · Inventory & COGS · SKU master data · 30-40 min

Stable codes, one retail price per product, margin math you can trust

Learning goals

  • Explain why a stable SKU code beats typing product names into journals
  • Enter master data once so every other sheet can look it up
  • Compute unit margin and margin percent from retail price and BOM cost

Concepts

Code the product, then stop typing its name

A SKU (stock-keeping unit) is the smallest thing you count separately. Juniper sells five: three single 8 oz jars and two gift sets. JG-01 is the Campfire Pine jar; JG-04 is the two-jar gift set. The code never changes even when the display name, the scent, or the price does.

Every journal in this workbook stores the code, never the name. Names live here, typed once. When Sam renames a product for the holiday season, he edits one cell on SKUs and every Production row, Sales row, and report updates instantly.

One price, one place

Retail price is master data too. Each Sales row multiplies quantity by the price looked up here, so a price change applied to future orders never rewrites history, and a typo cannot hide inside an order row.

Column F rolls up the bill of materials cost per unit (Lesson 7 builds it), and the two columns to its right turn that into unit economics.

=$E5-$F5

Unit margin: retail price minus BOM cost per unit. For JG-01 that is $28.00 - $3.69 = $24.31 of contribution before fees.

=IF($E5=0,0,$G5/$E5)

Margin percent with a divide-by-zero guard, formatted as a percentage. JG-01 lands at 86.8 percent.

💡 Tips:

  • In Excel 365 you could swap lookups for XLOOKUP later in the course; this course sticks to VLOOKUP so every version since 2010 behaves the same.

Practice

Fixed case (Sep 2026). The exercise opens on SKUs. Pale-green cells are formulas; white cells are typed values.

  1. Read the master rows — Confirm the five SKUs: JG-01 through JG-05, with variants (8 oz jar candle, gift sets) and retail prices from $26.00 to $64.00.
  2. Retype the JG-01 retail price — Cell E5 is blank. Type 28 and press Enter. The price column carries the dollar format $28.00.
  3. Check the derived columns — With E5 restored, G5 shows a unit margin of $24.31 and H5 shows 86.8 percent. Columns I through L (produced, sold, shrink, on hand) are wired in Lessons 8 and 9.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed 28 into SKUs!E5 and saw the money format render $28.00
  • ☐ Confirmed JG-01 unit margin of $24.31 and margin percent of 86.8
  • ☐ Can explain why journals reference SKU codes instead of product names
  • ☐ Verified the baseline: five SKUs, retail prices $26.00 / $28.00 / $28.00 / $52.00 / $64.00