📌 Stage 4 · Sales and FIFO COGS · Inventory & COGS · Per-sale profit · 30-40 min

One SUMIF lifts the draw log onto every order row

Learning goals

  • Attach each order's FIFO cost to the sales row with a single SUMIF
  • Compute gross profit and margin percent per order
  • Read the difference between material cost drag and fee drag

Concepts

COGS arrives by SUMIF

The FIFO COGS sheet may hold two rows for one order (the split draw), so the Sales row cannot look the cost up directly. It sums instead: add the Cost column wherever the order number matches. Split draws collapse into one number automatically, and an order with no draw rows yet sums to zero rather than erroring.

=SUMIF('FIFO COGS'!$B$5:$B$1004,$B5,'FIFO COGS'!$I$5:$I$1004)

FIFO COGS for this order: sum the draw sheet's Cost column wherever its Order number column equals this row's order. Sheet names with a space are wrapped in single quotes.

=$J5-$K5

Gross profit: net revenue minus FIFO COGS. E-1001: $146.50 - $21.18 = $125.32.

=IF($J5=0,0,$L5/$J5)

Margin percent of net revenue, guarded against a zero-denominator row. E-1001 runs at 85.6 percent.

💡 Tips:

  • If profit looks wildly wrong for one order, check the draw sheet first: a missing draw row shows up here as zero COGS, which flatters the margin.

Two drags, one profit

Each order now shows both costs of selling online. E-1007 paid $56.80 for materials and $55.90 in Etsy fees on $392.10 of net revenue, so fees and wax cost almost the same that day. Across September, materials took $757.17 and fees $456.40 out of $5,320.00 gross.

This is the number the old spreadsheet could not produce: profit per order, per channel, per SKU, from prices actually paid. Lesson 13 aggregates it; Lesson 15 acts on it.

Practice

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

  1. Type the COGS SUMIF — Cell K5 is blank. Type =SUMIF('FIFO COGS'!$B$5:$B$1004,$B5,'FIFO COGS'!$I$5:$I$1004) and press Enter. E-1001 returns $21.18. Fill down through K22.
  2. Read profit and margin — L5 shows $125.32 and M5 shows 85.6 percent for E-1001. E-1007 shows $335.30 profit on $392.10 net.
  3. Spot the cross-period order — E-1011 (Oct 2) shows full profit in the ledger. It will drop out of September reports in the next lesson, but its COGS correctly drained BATCH-504 forever.
  4. Reconcile the month — Sum column K for rows dated Sep 1 through Sep 30: 17 orders, $757.17 of FIFO COGS. Including E-1011 makes $786.69 lifetime.

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 Sales!K5 and got $21.18
  • ☐ Confirmed E-1007 shows $56.80 of COGS against $55.90 of fees
  • ☐ Reconciled September FIFO COGS of $757.17 (lifetime $786.69 with E-1011)
  • ☐ Verified the baseline: September gross profit $4,106.43 on $4,863.60 net, 84.4 percent