📌 Stage 1 · Foundations · Inventory & COGS · Workbook tour · 25-35 min
One candle studio, two channels, and a workbook that answers what did we actually earn
Learning goals
- Describe the Juniper Goods case and why a revenue-only spreadsheet fails an e-commerce maker
- Name the twelve sheets and the order data flows through them
- Read the Settings sheet and explain what the period dates and safety-stock days control
- Retype a Settings value and watch dependent sheets recalculate
Concepts
The business and the blind spot
Juniper Goods LLC is a two-person candle studio outside Portland, Oregon. Maya pours the candles, her partner Sam runs the shop: an Etsy storefront plus their own Shopify site. Sales are climbing into the 2026 holiday peak, and their current spreadsheet tracks revenue per order and nothing else.
When Etsy deposits land, they cannot say what a Campfire Pine jar really earns, because they never priced the wax, the jar, the wick, the label, and the mailer into each sale. This course rebuilds their operations COGS-first: cost of goods sold is computed per order from real purchase prices, so every margin number downstream is honest.
💡 Tips:
- COGS here means materials only. Etsy and Shopify fees are selling costs, tracked separately per order, so you can see both drags on profit.
A pipeline, not a pile of tabs
The twelve sheets form one direction of flow. Read Me and Settings explain and steer. SKUs and Materials are master data: what you sell and what you buy. BOM turns a SKU into a recipe. Purchases logs material buys and creates FIFO cost layers. Production turns materials into finished-goods layers. Sales logs channel orders. FIFO COGS draws each order against batch layers oldest-first. Margins, Cycle Counts, and Reorder read everything and report.
Every pale-green cell is a formula. You type only into white cells, and each sheet you touch updates the ones downstream.
Settings is the control panel
Two dates, Sep 1, 2026 and Sep 30, 2026, define the reporting period. Every period-filtered summary on Margins and Reorder references these cells, so closing a month means changing two cells, not forty formulas. The safety-stock days value feeds the Reorder sheet: safety stock is extra demand coverage you hold on top of lead-time demand.
A workbook that answers for any period is a model; a workbook hardwired to one month is a report. Parameters belong in cells, never inside formulas.
Practice
Fixed case (Sep 2026). The exercise opens on Settings. Pale-green cells are formulas; white cells are typed values.
- Tour the tabs — Click through the sheet tabs in order, from Read Me to Reorder. Confirm every sheet has its title in B2, headers on row 4, and data from row 5.
- Read the parameters — On Settings, find the period (C5 and C6), the selling channels, the FIFO method note, and the sales-tax note in rows 10 and 11.
- Retype the safety-stock days — Cell C8 is blank. Type 7 and press Enter. This is the number of days of extra demand Juniper holds as safety stock.
- Watch the ripple — Open the Reorder sheet. Column F (Safety Stock) recalculates from the value you just typed: for JG-01 it lands near 19.1 units. Change C8 back to 7 if you experimented.
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:
- ☐ Clicked through all twelve sheets and can name what each one contributes to the COGS pipeline
- ☐ Found the period start (Sep 1, 2026) and period end (Sep 30, 2026) on Settings
- ☐ Retyped Settings!C8 as 7 and saw Reorder column F recalculate
- ☐ Verified the baseline: safety stock 7 days, period Sep 1 - Sep 30, 2026, channels Etsy and Shopify