An ingredient inventory template has one row for each thing you buy to make your food, counted in the unit you use it in: flour in pounds, not bags; vanilla in fluid ounces, not bottles. Each row has what's on hand, what a unit cost, and a reorder level worked out from how fast you use it and how long the supplier takes. Behind the rows sit the lots: each delivery with its supplier's lot number and date, so the oldest gets used first and a recalled sack can be found. Packaging belongs on the same sheet, because a missing box stops an order as surely as missing butter. Below is a sheet for a made-up bakery, how to set the levels, and the file.
The short version
- One row for each ingredient and each kind of packaging.
- Count in the unit you use. Write the pack size beside it.
- Reorder level = days the supplier takes × what you use in a day, plus a margin.
- Every delivery is a lot with a date. Oldest first.
- Allergens get their own shelf and their own mark on the sheet.
- TaroStack takes ingredients off stock when you record a batch. More below.
An example sheet
| Item | Unit | On hand | Reorder at | Unit cost | Value | Status |
|---|---|---|---|---|---|---|
| Bread flour, 50 lb bags | lb | 320 | 200 | $0.48 | $153.60 | OK |
| Sugar, granulated | lb | 85 | 100 | $0.85 | $72.25 | REORDER |
| Butter, unsalted | lb | 44 | 36 | $4.80 | $211.20 | OK |
| Eggs, large | dozen | 18 | 15 | $4.20 | $75.60 | OK |
| Whole milk | gal | 6 | 4 | $4.10 | $24.60 | OK |
| Dark chocolate chips | lb | 22 | 25 | $6.40 | $140.80 | REORDER |
Twelve rows in all, $958.65 of ingredients and packaging, with four to reorder: sugar, chocolate chips, walnuts and label rolls.
One unit, written down
The trouble with ingredients is that they have three units. Flour is bought by the 50 lb bag, counted by the bag and the part-bag, and used by the gram. Pick one for the sheet, the one your recipes can be added up in, and write the pack size in the item's name: "Bread flour, 50 lb bags". Then six and a half bags is 325 pounds, and a recipe that takes 12 pounds can be subtracted from it.
The unit cost follows the same unit. A $24 bag is $0.48 a pound, and that's the number a recipe's cost is built from.
Reorder levels
A reorder level isn't a feeling. It's how much you'll use while you wait for the delivery, plus a margin.
| Ingredient | Used a day | Supplier takes | Used while waiting | Margin | Reorder at |
|---|---|---|---|---|---|
| Bread flour | 45 lb | 3 days | 135 lb | 65 lb | 200 lb |
| Sugar | 20 lb | 3 days | 60 lb | 40 lb | 100 lb |
| Butter | 12 lb | 2 days | 24 lb | 12 lb | 36 lb |
The margin covers a late truck or a big order. Butter's is small because the dairy comes twice a week. Sugar is at 85 pounds against a level of 100, so it's on today's order.
Then look ahead. The sheet says butter is fine at 44 pounds, above its level of 36. Saturday's wedding order needs 60. A reorder level only knows about ordinary days, so check the week's big orders against the sheet as well. The sum is set out in reorder point example, and how much to hold in how much inventory to keep on hand.
Lots and dates
| Received | Item | Supplier's lot | Quantity | Best by | Note |
|---|---|---|---|---|---|
| Nov 3 | Bread flour | M-26308 | 200 lb | May 3 | Older lot: use first |
| Nov 17 | Bread flour | M-26322 | 250 lb | May 17 | |
| Nov 14 | Butter, unsalted | B-4471 | 36 lb | Dec 10 | |
| Nov 10 | Walnut halves | W-0915 | 25 lb | Feb 10 | Tree nuts: own shelf |
Two lots of flour are open in the dry store. Using the older one first is the whole rule, and it only happens if the older bags are in front. If the mill ever recalls M-26308, this tab says you had it. Which loaves it went into is a different record, the batch sheet.
Allergens. Walnuts have their own category on the sheet and their own shelf, low down, so nothing spills onto other ingredients. Mark every allergen ingredient the same way.
Where things are kept is covered shelf by shelf in dry storage inventory template and walk-in cooler inventory sheet, and counting in how to count inventory.
Get the template
Download the ingredient inventory template. Four sheets: inventory with value and REORDER, lots with days left, a count sheet that works out each difference and what it's worth, and a read-me. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
Every batch changes a dozen rows. Forty loaves take flour, yeast, salt and bags off the sheet, and nobody types that in at five in the morning. So the sheet is right on the day it's counted and wrong by the next afternoon, and the reorder flags are only as good as the last count.
How TaroStack does it
TaroStack takes the ingredients off when you record what you made.
Each product is a recipe by weight. Units convert, so flour bought by the bag and used by the gram is one item.
Record a batch and its ingredients come off stock. The batch notes which lots it used, automatically.
"What could I make right now" says how many batches the stock on hand allows, and which ingredient runs out first: the answer to Saturday's butter.
A reorder list in days of cover, with reorder points suggested from how fast things really go, and each supplier's lead time.
Allergens on Growth. With allergen tracking on, a batch can't start if its label leaves out an allergen its ingredients contain.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| Take flour off after a batch | Type it in | Recorded with the batch |
| Enough butter for Saturday | A sum by hand | What could I make right now |
| When to reorder sugar | A level you set once | Days of cover, from real use |
| Which loaves used lot M-26308 | Not on the sheet | The lots on each batch |
Stock, lots, recipes and batches are on every plan, from $9.99 a month (5 recipes) or $39 a month (50 recipes, with allergen tracking). The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.
