A Cafe Inventory Template for Google Sheets
Mark, founder of Parly·September 2, 2026·7 min read
What a printable count sheet cannot do
A printable count sheet is a good thing. You walk the room, you write numbers in boxes, you are done in ten minutes. I have published the one my shop uses and it still lives at the bar.
What it cannot do is arithmetic. It records what is on the shelf this morning and nothing else. It cannot tell you that you burned 6 cartons of oat since Monday, that this is 2.1 a day, that at 2.1 a day the 4 left will not reach Thursday's delivery, and that you should therefore put down 9. That is a spreadsheet's job, and it is a different document with different columns.
So this is the other half: the living Google Sheet that sits behind the printout. Three tabs, eleven columns, two formulas. Built around about 58 items counted Monday, Wednesday, and Friday, which is the cadence my own shop runs.
Tab one: the item list, and the columns that earn their place
The first tab is the only one you maintain by hand, and it changes maybe once a month. One row per item, in the order you physically walk the room, not alphabetically. Alphabetical order makes you cross the floor eleven times.
| Column | Example | Why it is there |
|---|---|---|
| Item | Oat milk | The name your staff says out loud, not the invoice name |
| Zone | Back fridge | Groups the walk so counting never doubles back |
| Count unit | Carton | The thing you physically count, not the thing you buy |
| Pack size | 12 per case | Turns a count into an order |
| Supplier | Odeko | The sheet sorts the order by this |
| Order day | Mon, Wed, Fri | When you can actually place it |
| Lead time | 1 day | How far ahead the decision has to be made |
| Cost per unit | 2.85 | Makes the order total real before you send it |
| Par | 14 | The level you refill to |
| On hand | formula | Pulled from the newest count column |
| Days of stock | formula | The one number you scan for |
Two of these are the ones people leave out and then rebuild the sheet three weeks later.
Count unit versus pack size. You count cartons and you order cases. If the sheet holds one number for both, every order becomes a mental division problem at 10:40 in the morning, and that is where the mistakes live. Keep them apart and let the sheet multiply.
Order day and lead time. An item you can only order on Monday with a one day lead has to be decided Monday morning, not when it runs low on Thursday. Once those two columns exist, the sheet can tell you how many days a given item actually has to cover, which is the input every par level needs and almost nobody writes down.
💡 Freeze the top row and the first column
View, then Freeze, 1 row and 1 column. On a phone, at the reach-in, an unfrozen sheet means scrolling right until you cannot remember which row you are on. This one setting is the difference between a sheet people use and a sheet people abandon.
Tab two: one column per count date
The second tab is where the counts land, and the shape of it matters more than anything else in this post.
Column A is your item names, in the same order as tab one. Every count adds one new column to the right, headed with the date. Monday's count is column B, Wednesday's is C, Friday's is D. By the end of a month you have thirteen columns and a complete history sitting side by side.
The alternative, which looks tidier and fails, is one tab per count. New tab every Monday, copied from the last one. Do that and by week six you have 18 tabs, no way to see any item's trend, and formulas that break every time someone renames a tab "Mon (2)". One tab, columns going right. The sheet gets wide. That is fine.
Add one row above the dates for the delivery received since the last count, or a small block of rows at the bottom, because without it your usage math counts a delivery as negative consumption and every number after it is wrong.
Tab three: the order, computed instead of typed
The third tab has no typing on it at all. Item, supplier, on hand, daily burn, days of stock, suggested order, line cost. Every cell is a formula reading tabs one and two.
Sort it by supplier and you have your morning: open the tab, look at the Odeko block, sanity check the four numbers that look surprising, send. The whole point of the sheet is to make that tab boring.
The one thing to keep on it is a column you overwrite. Call it "actual." The sheet suggests 9 and you know a delivery is landing tomorrow, so you type 6. Keep both numbers. After a month, the gap between suggested and actual tells you which items your formula is wrong about, and that is how the sheet gets better instead of just getting older.
The two formulas that do all the work
Everything above runs on two calculations. If you build nothing else, build these.
Usage between two counts. Last count, plus what came in, minus this count.
=B2 + Received!B2 - C2
That is the units you actually burned in the gap. Divide by days between counts for the daily rate:
=(B2 + Received!B2 - C2) / DAYS(C$1, B$1)
Two days between Monday and Wednesday, three between Friday and Monday. Using DAYS on the column headers means the weekend gap takes care of itself instead of quietly inflating your Monday burn rate by 50 percent.
The order quantity. Days of stock first, because it is the number you actually scan for:
=IFERROR(OnHand / DailyBurn, "")
Then the order, which is what you need to cover until the delivery after next, minus what you already have, rounded up to a whole pack:
=MAX(0, ROUNDUP(((DailyBurn * DaysToCover) - OnHand) / PackSize, 0))
DaysToCover is not a guess. It is the gap between this delivery and the next one for that supplier, plus the lead time, plus a couple of days of slack on anything you cannot get quickly. For a supplier who delivers Monday and Thursday, Monday's order covers three days. For matcha on a five to seven business day lead, it covers a fortnight and you should treat it accordingly.
Wrap the days of stock cell in conditional formatting: red under 2, amber under 4. Now the sheet does the scanning for you and the morning check is a glance rather than a read.
Share it so it survives the week
A sheet that only the owner can edit becomes a sheet only the owner updates, and then it is not a system, it is a chore with your name on it.
Give whoever counts edit access on the counts tab. Protect tab one and tab three, since those hold the formulas and one accidental paste flattens them. In Google Sheets that is Data, then Protect sheets and ranges, and it takes two minutes.
Then let it go. My manager has run the last 26 counts at my shop, not me, and the numbers hold up because the sheet was built for the person doing the counting rather than the person reading the report. If you have not handed it over yet, handing over the count is worth doing before you refine another formula.
Where the Google Sheet stops
The sheet does real work, and it has one ceiling that no formula fixes.
It only knows what somebody typed into it. That means it is accurate on Monday morning and progressively less accurate every hour after, because between counts nothing tells it that you sold 60 drinks. The daily burn it computes is an average of the last gap, applied blindly forward. On a normal Tuesday that is close. On the Saturday of a heat wave when everything went iced and oat, it is not, and the sheet has no way to know that happened.
Your register does know. It recorded every one of those drinks, and if each drink is mapped to what it pours, that is your real usage by the hour instead of an average from three days ago. The sheet cannot reach that data. That is the gap between what sold and what you used, and it is the honest limit of every spreadsheet, mine included.
The moment you feel it is specific: you find yourself pasting a Square export into a fourth tab to work out what a busy weekend actually burned. That paste is the sheet asking for a system, and what to do when the spreadsheet stops working is its own decision. Until then, keep counting.
Build tab one tonight. Twenty rows in walk order, count unit and pack size in separate columns, and count into it on Monday. The formulas only matter once there are two count columns to subtract.