📌 Stage 2 · Recipes and Purchases · Inventory & COGS · FIFO cost layers · 35-45 min

Every buy is a priced layer; production drains the oldest layer first

Learning goals

  • Log material purchases with quantity and unit cost
  • Explain why a price change creates a new FIFO layer rather than rewriting old cost
  • Read the layer cascade: consumed from layer, remaining qty, remaining value

Concepts

Prices move; layers remember

Juniper bought soy wax at $2.40 per lb on Aug 18 and at $2.56 per lb on Sep 5. FIFO (first in, first out) says candles poured in early September cost out at the August price until those 120 lb are gone, then at the September price. The August purchase is a cost layer that empties first.

Average cost would blur the two prices together. FIFO keeps each layer's price intact, which is what makes per-order COGS defensible when material prices climb into the holiday season.

💡 Tips:

  • In the downloadable workbook, an Excel Table would auto-expand as you add purchase rows. The teaching model keeps plain ranges so every Excel version and the web exercise behave identically.

The cascade that drains layers

Columns J through L show how much of each layer production has consumed. The trick is a running total: for each purchase row, add up all quantity of the same material bought at or before that row's date, clamp the total against everything ever consumed, and take the difference from the same total excluding this row.

The formula looks long but is three ideas: cumulative purchased to date, total consumed, and the overlap between them.

=$G5*$H5

Line total: quantity times unit cost. PO-2301 is 120 lb at $2.40, so $288.00 of wax arrived in that layer.

=MAX(0,MIN(SUMIFS($G$5:$G$1004,$D$5:$D$1004,$D5,$C$5:$C$1004,"<="&$C5),VLOOKUP($D5,Materials!$B$5:$H$104,7,FALSE))-SUMIFS($G$5:$G$1004,$D$5:$D$1004,$D5,$C$5:$C$1004,"<"&$C5))

Consumed from this layer. The first SUMIFS is cumulative quantity of this material purchased through this row's date; MIN clamps it at total consumed (looked up from Materials column H); the second SUMIFS is the same cumulative total excluding this row; MAX with zero guards layers that start already drained.

Practice

Fixed case (Sep 2026). The exercise opens on Purchases. Pale-green cells are formulas; the type-in columns are PO number, date, material, qty, and unit cost.

  1. Read the journal — Eleven purchases from PO-2301 (Aug 18) to PO-2311 (Sep 22). Note the price rises: wax $2.40 then $2.56, jars $1.10 then $1.18, gift boxes $1.45 then $1.52.
  2. Type the line total — Cell I5 is blank. Type =$G5*$H5 and press Enter. PO-2301 totals $288.00. Fill down through I15.
  3. Watch the first layer drain — On row 5, column J shows 120 consumed and column K shows 0 remaining: the August wax layer is fully used. Its remaining value in L5 is $0.00.
  4. Check the open wax layer — Row 12 (PO-2308, 150 lb at $2.56) shows 82.5 consumed and 67.5 remaining, worth $172.80. That matches the 67.5 lb on hand from Lesson 3.

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 =$G5*$H5 into Purchases!I5 and got $288.00
  • ☐ Confirmed PO-2301 is fully consumed (120 used, 0 left) and PO-2308 keeps 67.5 lb worth $172.80
  • ☐ Can explain why a new price creates a new layer instead of editing the old one
  • ☐ Verified the baseline: 11 purchase layers, September purchases total $1,329.90