When to use this
Run this workbook every time you do a full food inventory count — usually once per month before you close the books or update prime cost. Use one month tab at a time (Jan–Dec) so the usage and COGS math lines up with your accounting period.
What the workbook tracks
- Each month tab tracks Beginning Inventory, Purchases, Ending Inventory, and Usage Cost for every food item.
- The bottom-right of each month tab gives you a COGS-ready summary you can plug straight into your prime cost calculator.
Inputs — what goes where
- Category (A) — Dry Goods, Dairy, Meat, Produce, Freezer, etc.
- Item / Description (B) — the product name as you know it on the shelf or on the order guide.
- Pack Size (C) — e.g. “6/#10 cans”, “1 gal”, “1/40 lb”, “1 doz”.
- Count UOM (D) — how you count it on inventory: usually “Case” or “Each”.
- Beginning Qty (E) — how many you had on hand at the START of the month.
- Purchases Qty (F) — total quantity received during the month.
- Ending Qty (G) — how many you have on hand on the final inventory count day.
- Case Price (H) — cost per full case, if you buy in cases.
- Unit Price (I) — cost per single unit (can, pound, each, etc.). This is the price the math uses.
Formulas — exactly what the sheet does
- Beginning Value (J) = Beginning Qty × Unit Price
- Purchase Value (K) = Purchases Qty × Unit Price
- Ending Value (L) = Ending Qty × Unit Price
- Usage Qty (M) = Beginning Qty + Purchases Qty − Ending Qty
- Usage Cost (N) = Usage Qty × Unit Price (your food cost for that item for the month)
At the bottom-right of each month tab, totals roll up automatically:
- Total Beginning Inventory
- Total Purchases
- Total Ending Inventory
- COGS (Month) = Total Beginning + Total Purchases − Total Ending
Tip: Take COGS (Month) from the summary box and divide by your period food sales to get Food Cost %. Then plug that into your prime cost calculator.
Walk-through example
Say you’re tracking a 6/#10 can tomato product:
- Beginning Qty: 5 cases
- Purchases Qty: 3 cases
- Ending Qty: 2 cases
- Unit Price: $20.00 per case (or true unit)
- Usage Qty = 5 + 3 − 2 = 6
- Usage Cost = 6 × $20.00 = $120.00 food cost for that item for the month
Repeat this for all tracked items. The summary box at the bottom-right gives you total food COGS for the month.
Common pitfalls
- Mixing units — counting in “Each” but pricing in “Case” without converting the unit price.
- Skipping purchases — forgetting transfers or emergency vendor runs, which understates usage.
- Using different count dates for beginning and ending than your accounting period.
- Leaving Unit Price outdated after vendor price changes.