📌 Stage 3 · Production and Stock · Inventory & COGS · Unit cost roll-up · 30-40 min

SUMIF the recipe lines into one cost per SKU, then read the margin

Learning goals

  • Roll a multi-line recipe into one cost per SKU with SUMIF
  • Track how much material completed production has consumed
  • Interpret unit margin and margin percent against retail price

Concepts

One SUMIF, one SKU cost

Column F on the SKUs sheet adds every extended cost line for one SKU. The BOM sheet holds 33 lines; each belongs to exactly one SKU, so grouping by SKU code in column B and summing column H collapses each recipe to a single number.

At latest costs, a Campfire Pine jar costs $3.69: $1.28 of wax, $0.51 of fir oil, $1.18 of jar, $0.12 of wick, $0.22 of label, $0.38 of mailer. The trio set costs $11.38. These are the numbers that make a retail price defensible.

=SUMIF(BOM!$B$5:$B$1004,$B5,BOM!$H$5:$H$1004)

Sum BOM extended cost (column H) wherever the BOM SKU (column B) equals this row's code. Six lines roll into $3.69 for JG-01.

The recipe also reports consumption

Columns I and J on the BOM sheet reuse the same grouping in reverse. Column I counts units produced per SKU from completed batches (Lesson 8 wires Production); column J multiplies by quantity per unit, so each recipe line states how much of that material left the shelf.

Add column J by material and you get total consumption, which is exactly what Materials column H reads. That is how the BOM connects finished goods back to raw material stock: 0.5 lb per jar times 140 jars of JG-01 is 70 lb of wax, before any other SKU is counted.

Practice

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

  1. Type the roll-up — Cell F5 is blank. Type =SUMIF(BOM!$B$5:$B$1004,$B5,BOM!$H$5:$H$1004) and press Enter. JG-01 lands at $3.69.
  2. Fill down and compare — Fill F5 down to F9. Costs read $3.69, $3.75, $3.69, $8.52, $11.38. Gift sets cost roughly twice and four times a jar, matching their contents.
  3. Read the unit economics — With the roll-up restored, G5 shows $24.31 unit margin and H5 shows 86.8 percent on a $28.00 retail price. JG-05 shows $52.62 on $64.00.
  4. Trace one material — On BOM, filter or scan the RM-01 lines: column J shows 70, 45, 20, 30, and 37.5 lb consumed by the five SKUs. Their total, 202.5 lb, is the consumed figure on Materials row 5.

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 the SUMIF into SKUs!F5 and got $3.69 for JG-01
  • ☐ Confirmed the JG-05 roll-up of $11.38 and JG-02 of $3.75
  • ☐ Traced wax consumption of 202.5 lb across the five BOM groups
  • ☐ Verified the baseline: BOM costs $3.69 / $3.75 / $3.69 / $8.52 / $11.38, wax consumed 202.5 lb