🛒 Lesson 11 · FIFO COGS: draining layers oldest-first

📌 Stage 4 · Sales and FIFO COGS · Inventory & COGS · FIFO draws · 35-45 min

Write each order's draw against a batch layer, including split draws

Learning goals

• Apply the FIFO rule to finished goods: oldest completed batch first
• Pull sale details onto the draw rows with VLOOKUP against the order number
• Handle a draw that splits across two batches at two unit costs

Concepts

The audit trail of cost

This sheet is deliberately explicit: one row per (order, batch) pair, typed by you following one rule: serve the order from the oldest completed batch that still has units. Nothing here is clever, which is the point. When a margin number is questioned, you can point at nine or nineteen rows and walk any order back to the batch and the purchase layers behind it.

E-1001 (6 jars, Sep 5) draws 6 from BATCH-501 at $3.53. By Sep 19, BATCH-501 has served 46 of its 60 jars; E-1007 needs 16, so it takes the last 14 at $3.53 and 2 from BATCH-504 at $3.69. That split row is FIFO in one picture.

=VLOOKUP($B5,Sales!$B$5:$E$1004,4,FALSE)

SKU for the draw: look the order number up in Sales and return column 4 of B:E, the SKU. The same pattern with 2 returns the date and with 3 the channel.

=VLOOKUP($F5,Production!$B$5:$G$1004,6,FALSE)

Unit cost of the layer: look the batch number up in Production and return column 6 of B:G, the planned unit cost typed when the batch was planned.

=$G5*$H5

Cost of this draw: unit cost times quantity taken. E-1001's draw is 6 at $3.53 = $21.18.

Order of typing, order of layers

Work the draws in date order and check two invariants as you go. First, a batch's draws can never exceed its quantity; Production column K (Layer Units Left) exists to prove it, dropping batch by batch toward zero. Second, you never touch a younger batch while an older one of the same SKU still has units.

Materials follow the same rule automatically through the Purchases cascade of Lesson 5; this sheet is the finished-goods twin of that machinery, written out where you can read it.

Practice

Fixed case (Sep 2026). The exercise opens on FIFO COGS. Pale-green cells are formulas; you type the order number, batch number, and quantity taken.

1. Type the cost formula — Cell I5 is blank. Type =$G5*$H5 and press Enter. E-1001's draw costs $21.18. Fill down through I23.
2. Find the split draw — Rows 15 and 16 are both E-1007: 14 jars from BATCH-501 at $3.53 ($49.42) and 2 from BATCH-504 at $3.69 ($7.38). Together they cost $56.80.
3. Check the layer leftovers — Open Production. Column K now reads: BATCH-501 zero left, BATCH-504 fifty left, BATCH-502 fourteen, BATCH-503 seventeen, BATCH-505 twelve, BATCH-506 thirteen, BATCH-507 fifty.
4. Prove the invariant — Sum the qty taken per batch on this sheet and confirm no batch exceeds its Production quantity. BATCH-504: 2 + 20 + 8 = 30 taken of 80 poured, 50 left.

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 FIFO COGS!I5 and got $21.18
• ☐ Found the E-1007 split draw (14 at $3.53, 2 at $3.69, $56.80 total)
• ☐ Confirmed BATCH-501 is fully drained and BATCH-504 keeps 50 units
• ☐ Verified the baseline: 19 draw rows, September FIFO COGS $757.17