Restaurant inventory spreadsheet template
Three tabs: Items, Counts and Usage. Log each count once, and the Usage tab works out what you used and what it cost between any two count dates.
Free, no email, no signup. The CSV is the first tab only (Items); the Excel file has every tab. In Google Sheets, use File, then Import, and upload the .xlsx.
Items
Example data. Your item list, once. Example items and costs, replace with yours. The headers are the ones the ShelfCount importer reads directly. cost is the price of one purchase unit.
| name | category | purchaseUnit | countUnit | recipeUnit | cost | parLevel |
|---|---|---|---|---|---|---|
| Whole milk | Dairy | gallon | gallon | fl oz | $4.80 | 28 |
| Oat milk | Dairy | carton | carton | fl oz | $5.50 | 12 |
| Espresso beans | Coffee | lb | lb | oz | $12.40 | 40 |
| Vanilla syrup | Syrups | bottle | bottle | fl oz | $9.50 | 6 |
| 12 oz hot cups | Paper | sleeve | sleeve | each | $4.75 | 20 |
Counts
Example data. One row per item per count date. Add rows every time you count; never overwrite an old count.
| Date | Item | Count | Counted by |
|---|---|---|---|
| 2026-10-05 | Whole milk | 24 | Sam |
| 2026-10-05 | Oat milk | 9 | Sam |
| 2026-10-05 | Espresso beans | 36 | Sam |
| 2026-10-05 | Vanilla syrup | 5 | Sam |
| 2026-10-05 | 12 oz hot cups | 18 | Sam |
| 2026-10-12 | Whole milk | 18 | Sam |
| 2026-10-12 | Oat milk | 6 | Sam |
| 2026-10-12 | Espresso beans | 29 | Sam |
| 2026-10-12 | Vanilla syrup | 3 | Sam |
| 2026-10-12 | 12 oz hot cups | 14 | Sam |
Usage
Example data. Set the start and end dates, type purchases from your invoices, and the rest fills in from the Counts tab.
| Period start | 2026-10-05 | |||||
|---|---|---|---|---|---|---|
| Period end | 2026-10-12 | |||||
| Item | Opening | Purchases | Closing | Usage | Unit cost | Usage cost |
| Whole milk | 24 | 40 | 18 | 46 | $4.80 | $220.80 |
| Oat milk | 9 | 12 | 6 | 15 | $5.50 | $82.50 |
| Espresso beans | 36 | 25 | 29 | 32 | $12.40 | $396.80 |
| Vanilla syrup | 5 | 3 | 3 | 5 | $9.50 | $47.50 |
| 12 oz hot cups | 18 | 10 | 14 | 14 | $4.75 | $66.50 |
What it is for
An inventory spreadsheet has two jobs. It remembers what you have, and it tells you what you used. The second one matters more. Knowing you have 18 gallons of milk is a count. Knowing you used 46 gallons this week is the number that shows food cost, waste and theft.
This workbook keeps the two apart. Items is your list, entered once. Counts is a log with a new row every time you count, so old counts stay put. Usage compares two dates from that log and adds the purchases you type in from invoices.
The data is made up so you can see how the tabs connect. Delete the example rows, keep the headers and the formulas, and fill in your own.
How to use it
- Download the Excel file. The CSV is the Items tab only.
- On Items, list everything you count, with a category, the purchase, count and recipe units, and the cost of one purchase unit from your latest invoice. Keep the lowercase headers as they are.
- On Counts, add a row for every item each time you count: the date, the item name exactly as written on Items, the count and who counted.
- On Usage, set Period start and Period end to two dates that exist in Counts. The Opening and Closing columns fill themselves in.
- Type Purchases for the period from your invoices, in the same count units.
- Read Usage and Usage cost. Sort by Usage cost to see where the money goes.
- For the next period, change the dates. Your old counts stay on the Counts tab.
The columns, one by one
Items
- name, category. One row per thing you count. Keep the lowercase headers exactly as written so the ShelfCount importer reads the tab directly.
- purchaseUnit, countUnit, recipeUnit. The unit you buy in, the unit you count in, and the unit recipes use. This sheet assumes you count in the purchase unit (a gallon, a bottle, a sleeve). If they differ, convert before you enter counts.
- cost. The price of one purchase unit from your latest invoice.
- parLevel. The level you order up to. Optional here; the par sheet template works it out. You can also add optional locations and vendor columns.
Counts
- Date, Item, Count, Counted by. A log. The item name must match the Items tab exactly, because the Usage tab finds counts by name and date.
Usage
- Period start, Period end. The two count dates the period sits between. Both must exist in the Counts tab.
- Opening, Closing. Formulas. The count on the start date and the count on the end date.
- Purchases. Type this in: everything received between the two counts, in count units.
- Usage, Usage cost. Formulas. Units used, and units used times cost per purchase unit.
The formulas
These are the exact formulas in the Excel file, written for the first data row.
Usage
=B5+C5-D5
Opening + purchases - closing. What left the building, whether it was sold, wasted, or lost.
Usage cost
=E5*F5
Usage times cost per purchase unit. Add the column up for the period's actual cost of goods.
Opening / Closing
=SUMIFS(Counts!$C$2:$C$500,Counts!$B$2:$B$500,A5,Counts!$A$2:$A$500,$B$1)
Add up the counts for this item on the period's start date (or end date for closing).
Two limits worth knowing. Item names must match exactly across tabs, because the formulas look them up by name. And purchases are typed by hand, so the sheet is only as accurate as your invoice entry.
A worked example
Example numbers only. Whole milk was counted at 24 gallons on October 5 and 18 on October 12, and 40 gallons arrived in between.
Usage = opening + purchases - closing = 24 + 40 - 18 = 46 gallons. At $4.80 a gallon, Usage cost = 46 x 4.80 = $220.80.
Whether those 46 gallons went into drinks, the bin or the back door, the sheet cannot say. It tells you the total. Comparing it to what your sales say you should have used is the next step, and that is where a spreadsheet gets slow.
When the sheet stops being enough
A spreadsheet works while one person owns it and the item list stays small. It stops working when two people count at once, when invoices change your costs faster than you update the sheet, or when the order still has to be typed into a vendor’s site by hand. Our ShelfCount versus spreadsheets page is honest about where that line sits. The Items tab uses the exact column headers the ShelfCount importer reads, so you can upload this file and it reads that tab directly. The importing your data guide covers how.
Questions we get
How do I calculate inventory usage?
Opening count plus purchases minus closing count. That gives the units that left your stock over the period. Multiply by the cost per unit to get the cost of what you used.
How often should I count?
Weekly works for most shops. The period on the Usage tab can be any two count dates, so you can also compare a month by choosing counts four weeks apart.
Why does the Counts tab keep every old count?
Because usage needs an opening and a closing count, and the opening count is last period's closing count. Overwriting counts destroys the history the Usage tab reads.
Does this work in Google Sheets?
Yes. The formulas use SUMIFS and VLOOKUP, both supported. Import the .xlsx with File, then Import. Date cells keep their format.
How is this different from the food cost calculator?
The calculator works out one period's food cost from two totals. This workbook keeps item-level counts so you can see which items drive the number.
Related
- Food cost calculator turns period totals into a food cost percentage.
- Usage variance shows how ShelfCount compares actual usage to what sales say you should have used.
- Counting inventory covers counts in the app.
- Food cost reports explains theoretical and actual cost.
- Inventory count sheet A walk-order count sheet for a cafe or a restaurant, with blank count boxes. Print it or open it in a spreadsheet.
- Par sheet Par, order quantity and cases to order from daily usage, delivery gaps and safety stock, with the formulas built in.
- Recipe costing template Ingredients, recipes and a modifier swap tab that roll up to cost per drink and the price that hits your target.
- Coffee shops shows how the same counts feed par, orders and drink cost.
Written by the ShelfCount team. We run Stella Coffee in Los Angeles and built these for our own back office first. Last checked October 2026.
ShelfCount keeps the counts, reads purchases from invoices and receipts, and compares usage to what your POS sales say you should have used.