Coffee Shop Inventory Spreadsheet: A Free Template

A coffee shop inventory spreadsheet does two jobs. The first is the weekly count: every item you can run out of, beans, milks, syrups, cups and lids, counted at the same time each week against a par level, the amount you want on hand after a delivery, so the order writes itself. The second is the drink cost: what goes into each drink, at what you pay for it, so you know what a latte really costs when milk goes up. With both, the spreadsheet answers what to order, what's sitting on the shelves, and which drinks carry the menu. In the example below, a café's Monday count shows $365.80 of stock and an order for 20 lb of beans and 24 cartons of oat milk, and its oat latte costs $1.55 against a $0.91 regular latte. Below is what to count, how par levels work, the drink costs, and a free template.

The short version

• Count every item you can run out of, weekly, at the same time, before the delivery.
• Set a par for each: a normal week's use plus what you'd use before the next delivery.
• Order = par − on hand, rounded up to whole cases.
• Cost each drink from its recipe: grams of coffee, ounces of milk and syrup, the cup, lid and sleeve.
• Recost drinks when a supplier's price changes.
• Write down what's thrown away, like milk past its date, so the counts make sense.
• TaroStack counts on a phone, works out orders and costs drinks from what you paid. More below.

What to count

Count in the unit you order in or the unit you use, and stick to one. Pastries delivered fresh each morning don't need a weekly count, but write down how many went in the bin.

A Monday count

Pali Roasting Co., a made-up coffee roaster I use in these examples, runs a small café beside its roastery. Monday morning, before the delivery:

Stock on hand is worth $365.80. The order rounds up to whole cases, so the cups land above par; that's the cost of buying by the case, and it's fine for something that keeps. For milk, which doesn't, keep the par close to a week's use. How a par level compares with a reorder point is in par level vs reorder point.

What each drink costs

The oat latte's milk costs three times the whole milk's, so a 75-cent upcharge doesn't quite keep its margin level with the regular latte. Coffee drinks carry high margins on ingredients; rent, wages and equipment come out of them, so the drink cost is where to watch for slips, not the whole story. What food cost should be as a share of sales is in what is a good food cost percentage.

Keeping the count useful

1. Same time every week, before the delivery, so the numbers compare.
2. Count the back stock and the bar: the half-used carton behind the counter is stock too.
3. Write down waste with a reason: milk past its date, a dropped sleeve of cups. Without it, usage looks higher than sales explain; a log for it is in waste log template.
4. Revisit pars each month, and before a holiday or a slow season.

Get the template

Download the coffee shop inventory spreadsheet. The Weekly count sheet takes each item's unit, par, count, case size and cost, and works out what to order, rounded up to whole cases, and the value of what's on hand. The Drink costs sheet takes each drink's recipe and costs it from the prices on the count sheet, so when milk goes up, every drink follows. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.

Where this stops working

A weekly count tells you about last week. Oat milk runs out on Friday because a new oat drink took off on Wednesday, the milk price rose on the last invoice and the drink costs still have the old one, and the waste log lives on a sticky note. With two people counting on different days, the spreadsheet stops matching the shelf.

How TaroStack does it

TaroStack keeps the count, the orders and the costs together.

Counting on a phone. Count the bar and the back on a phone or tablet, with no signal needed in the walk-in, and see what a variance is worth before you post it. Counting suggests what to count first, from what it would cost to be wrong.

Orders from real use. The reorder list works in days of cover at the rate each item really moves, with reorder points suggested from that movement, so oat milk is reordered on Wednesday, not discovered on Friday.

And drinks, costed from what you paid. Each drink is a recipe, with units that convert: beans by the pound, used by the gram; milk by the gallon, used by the ounce. Margins are reported by product, on what things actually cost, and waste comes off as an adjustment with its reason. Square connects, and so does Shopify.

Counting, the reorder list, recipes and margins by product are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do you do inventory for a coffee shop?

Count every item you can run out of at the same time each week, before the delivery, against a par level for each. Order the difference, rounded up to whole cases, and write down waste so the counts make sense.

What should be on a coffee shop inventory list?

Coffee beans, milks, syrups and sauces, hot and cold cups by size, lids, sleeves and straws, any food you hold, and cleaning supplies.

How do you calculate the cost of a latte?

Add the coffee, by weight, the milk, by volume, any syrup, and the cup, lid and sleeve, each at what you paid. In the example, a 12 oz latte with 18 g of espresso and 10 oz of whole milk costs $0.91.

How do you set par levels for a coffee shop?

A normal week's use plus what you'd use before the next delivery arrives. Keep pars for perishables like milk close to a week's use.