Two SKUs, Three Calculated Fields, Zero Guessing
Two items: EOD K9 unit LP and Alpha Drug/Patrol LP. That's the entire product line. And yet the number of ways a two-item operation can lose track of money and stock is remarkable. Units sold versus units given away is not the same number. Profit on current stock is not the same as total revenue. Reorder date is not the same as reorder trigger. The moment you conflate any of these, you're running your margins on gut feel.
This template was built for a working K-9 product line — the kind of specialty item sold at law enforcement events, through police association channels, and occasionally handed out to handlers, partner agencies, or supporters who didn't pay. That "given away" number isn't a rounding error. It's a real cost that most small operation spreadsheets swallow into the sold column and never account for.
The Calculated Fields Are the Business Logic
The Current Stock formula is straightforward: Amount Stocked minus Amount Sold minus Amount Given Away. What that formula does is force you to track the giveaways as a distinct category, not a vague reduction. When you've stocked 200 EOD K9 plates and show 160 "sold," that last 40 might be 25 actual sales, 10 given to partner agencies, and 5 still physically on a shelf. Without the three-way split, you don't know which one.
Profit Of Current Stock uses the spread between List Price and Unit Price, multiplied by Amount Sold only. It does not factor in given-away units. That's the correct calculation — you didn't make money on the giveaways, so they don't count toward realized profit. What they do represent is a real cost against your Total Order Cost, which is why running Current Stock alongside Profit Of Current Stock tells you your actual margin position on any given order cycle.
The List Price versus Unit Price architecture also handles the common situation where cost fluctuates between supplier orders while list price stays fixed for continuity. When your supplier raises the per-unit cost on a reorder, you enter the new Total Order Cost and new Unit Price for the new order cycle, and the profit calculation automatically reflects the compressed margin. No retroactive editing of old records needed.
Finding Dead Stock Before It Ties Up Cash
Status has two values: Current and Discontinued. It's a simple flag that filters your active inventory from products you've stopped carrying. When you run an event and need to know what to bring, a filter on Current gives you your live SKUs. When you're doing an end-of-year financial review, filtering on Discontinued with any positive Current Stock figure immediately shows you what you've got sitting in a box somewhere that isn't moving.
The Re-Order Date and ReOrder Amount fields close the procurement loop. When your EOD plate stock drops to minimum threshold and you know your supplier takes three weeks to fulfill, the Re-Order Date is the calendar anchor. The Comments field is where you put the supplier contact, the minimum order quantity, and any lead time notes — practical information that doesn't belong in a structured field but needs to live next to the record it describes.
For an operation running two SKUs with variable giveaway rates and multi-cycle ordering, that calculated Current Stock field — the one doing the three-way subtraction — is the number you check before every event, before every reorder, and before you tell anyone what your available inventory is.