📌 Stage 4 · Sales and FIFO COGS · Inventory & COGS · Channel sales log · 30-40 min

One row per order, priced from master data, with fees typed in

Learning goals

  • Log orders with channel, SKU, and quantity, using order numbers that encode the channel
  • Compute gross sales from quantity times a looked-up retail price
  • Treat platform fees as a separate, per-order cost and net them off revenue

Concepts

The journal that starts every margin

Each row is one order: an order number, a real date, the channel (Etsy or Shopify), the SKU, and quantity. Order numbers starting E- are Etsy, S- are Shopify, which makes reading the log effortless. E-1011 sits on Oct 2, just past the period end: it stays in the ledger and counts toward lifetime stock, but September reports exclude it.

The product name fills in from the SKU master, and gross sales price itself off master data.

=$G5*VLOOKUP($E5,SKUs!$B$5:$E$104,4,FALSE)

Gross sales: quantity times the retail price looked up from SKUs. The range B:E puts the retail price in column 4. E-1001 is 6 jars at $28.00 = $168.00.

=$H5-$I5

Net revenue: gross minus platform fees. E-1001 nets $146.50 after $21.50 of Etsy fees.

💡 Tips:

  • All amounts exclude sales tax. US sales-tax rules vary by state and product; confirm yours with your state Department of Revenue before remitting.

Fees are typed, revenue is computed

Etsy fees arrive as one combined monthly reality (listing, transaction, payment processing, plus shipping labels sometimes), so they are typed per order as a single number. Shopify fees are smaller but include payment processing.

Typing fees keeps the log honest about what the platform actually took, and it makes the channel comparison of Lesson 14 possible: Etsy charges run near 12.8 percent of gross here, Shopify near 4.7 percent.

Practice

Fixed case (Sep 2026). The exercise opens on Sales. Pale-green cells are formulas; the type-in columns are order number, date, channel, SKU, qty, and fees. Columns K through M fill in during Lessons 11 and 12.

  1. Read the log — Eighteen orders from E-1001 (Sep 5) to E-1011 (Oct 2). Channels alternate Etsy and Shopify; quantities run 4 to 20.
  2. Type the gross formula — Cell H5 is blank. Type =$G5*VLOOKUP($E5,SKUs!$B$5:$E$104,4,FALSE) and press Enter. E-1001 returns $168.00. Fill down through H22.
  3. Check net revenue — Column J nets fees off gross: E-1001 shows $146.50. September gross is $5,320.00 and fees $456.40, so September net is $4,863.60.
  4. Notice the boundary row — E-1011 (Oct 2) is in the journal and in lifetime stock, but September summaries will skip it. Watch for that in Lesson 13.

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 VLOOKUP gross formula into Sales!H5 and got $168.00
  • ☐ Confirmed E-1001 nets $146.50 after $21.50 of Etsy fees
  • ☐ Can say which order is cross-period and what that means (E-1011, Oct 2)
  • ☐ Verified the baseline: 17 September orders, $5,320.00 gross, $456.40 fees, $4,863.60 net