📌 Stage 5 · Margin Analysis · Inventory & COGS · Pricing decisions · 30-40 min

Compare the SKUs sheet's plan against what September actually earned

Learning goals

  • Contrast planned unit margin (latest costs) with realized margin (FIFO costs and fees)
  • Complete the TOTAL row and read it as the month's gross P and L
  • Choose a pricing response to material cost increases

Concepts

Two margin numbers, both true

The SKUs sheet says a Campfire Pine jar earns 86.8 percent at retail: retail price minus BOM cost at the latest prices. The Margins sheet says JG-01 on Etsy earned 85.5 percent of net revenue in September, after FIFO cost and Etsy fees.

The gap is not an error. Planned margin prices the next jar at tomorrow's costs; realized margin prices September's jars at the costs actually paid, net of the fees actually charged. When wax rises from $2.40 to $2.56, planned margin falls immediately while realized margin drifts down only as cheap layers drain. Watching both tells you when a price increase is truly due.

=SUM($I$5:$I$14)

TOTAL gross profit for the matrix: the ten pair profits sum to $4,106.43.

=IF($G$15=0,0,$I$15/$G$15)

TOTAL margin percent: $4,106.43 over $4,863.60 net revenue is 84.4 percent. Guarding the division keeps the row safe on an empty month.

What the numbers argue for

Jars poured on the cheap August layers (BATCH-501 at $3.53) are gone; everything now selling costs $3.69 to make and the next wax buy may cost more. Realized JG-01 margin has therefore not yet felt the full increase, but planned margin already has.

Three levers, in the order Juniper would pull them: raise the jar price by a dollar around the November reset (planned margin absorbs the wax increase and then some); steer gift-set traffic to Shopify, where fees take 4.7 instead of 12.8 points on the biggest baskets; and pour JG-05 trios ahead of demand, since the $52.62 unit margin makes them the best use of a pouring day.

💡 Tips:

  • Price changes belong in the SKUs master, never inside order rows: future orders pick up the new price, history keeps the old one.

Practice

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

  1. Type the total profit — Cell I15 is blank. Type =SUM($I$5:$I$14) and press Enter. The month's gross profit is $4,106.43.
  2. Read the whole TOTAL row — Confirm 17 orders, 161 units, $4,863.60 net, $757.17 FIFO COGS, $4,106.43 profit, 84.4 percent margin.
  3. Compare plan and actual for JG-01 — On SKUs, planned margin is 86.8 percent at $3.69 BOM cost. On Margins, realized JG-01 margin is 85.5 percent on Etsy and 86.5 percent on Shopify; the difference is fees plus the batch-cost mix.
  4. Watch a price change ripple — Temporarily set SKUs!E5 to 29. Every JG-01 gross, net, profit, and margin recomputes, on Sales and across the Margins matrix, because order rows price off master data. Note the side effect: even past orders reprice. Undo back to 28.

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 =SUM($I$5:$I$14) into Margins!I15 and got $4,106.43
  • ☐ Confirmed the TOTAL margin of 84.4 percent on $4,863.60 net revenue
  • ☐ Can explain why planned margin (86.8 percent) and realized margin (85.5 percent) differ
  • ☐ Verified the baseline: $757.17 FIFO COGS, $4,106.43 gross profit for September 2026