📌 Stage 2 · Recipes and Purchases · Inventory & COGS · Latest unit cost · 30-40 min

Find the most recent purchase date, then price the BOM at today's costs

Learning goals

  • Find a material's most recent purchase date with SUMPRODUCT and MAX
  • Look up the unit cost on that date with SUMIFS
  • Price BOM lines at latest cost and extend them into an extended cost

Concepts

Latest cost in two steps

Planning needs one number per material: what the next buy will cost. The workbook finds the newest purchase in two steps. First, the latest date: multiply a true/false test (does each purchase row match this material) against the date column, and take MAX of the products inside SUMPRODUCT. Non-matching rows contribute zero, so the maximum is the newest matching date.

Second, the price on that date: SUMIFS the unit cost where material matches and date equals that newest date. For wax that is Sep 5, 2026 and $2.56 per lb.

=SUMPRODUCT(MAX((Purchases!$D$5:$D$1004=$B5)*Purchases!$C$5:$C$1004))

Latest purchase date. The comparison produces a column of 1s and 0s; multiplying by the date column keeps matching dates and zeroes the rest; MAX picks the newest; SUMPRODUCT hands the single value out as a number formatted as a date.

=SUMIFS(Purchases!$H$5:$H$1004,Purchases!$D$5:$D$1004,$B5,Purchases!$C$5:$C$1004,$J5)

Latest unit cost: sum the Unit Cost column where the material code matches this row and the purchase date equals the latest date just computed. If two layers shared one date, their costs would add; Juniper never splits a material across same-day layers.

💡 Tips:

  • MAXIFS would be the modern one-step answer in Excel 2019 and later; the SUMPRODUCT pattern above works everywhere, including the in-browser exercise.

Price the recipe

On the BOM sheet, column G looks the latest cost out of Materials (column 10 of the range starting at B: the cost column K), and column H multiplies by quantity per unit. The half-pound of wax in a Campfire Pine jar costs 0.5 times $2.56, or $1.28.

This is the planning view of cost: what the recipe costs at prices you are paying now. Lesson 7 rolls these lines up into one number per SKU, and Lesson 11 contrasts it with actual FIFO cost per order.

Practice

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

  1. Read the last-purchase dates — Column J on Materials shows the newest buy per material. Soy wax reads Sep 5, 2026; gift boxes read Sep 22, 2026 (the $1.52 layer).
  2. Type the latest-cost formula — Cell K5 is blank. Type =SUMIFS(Purchases!$H$5:$H$1004,Purchases!$D$5:$D$1004,$B5,Purchases!$C$5:$C$1004,$J5) and press Enter. Soy wax returns $2.56.
  3. Value the shelf — Column L multiplies on-hand quantity by latest cost. For wax: 67.5 lb at $2.56 is $172.80. All eight materials together come to $738.00.
  4. Check the BOM pricing — Open the BOM sheet: G5 is $2.56 (wax at latest cost) and H5 is $1.28 for the half-pound line.

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 SUMIFS into Materials!K5 and got $2.56 for soy wax
  • ☐ Confirmed the wax BOM line prices at $2.56 per lb and $1.28 extended
  • ☐ Can explain the two-step latest-cost pattern (newest date, then cost on that date)
  • ☐ Verified the baseline: materials on hand value $738.00 at latest cost