Track coffee shop food cost one ingredient at a time. A weekly milk variance worksheet with a full worked example, in gallons and dollars.
By Jared · 2026-11-15 · 11 min read
To control coffee shop food cost, compare what you actually used with what your sales say you should have used, one ingredient at a time, over one matched week. Start with milk, because it is usually your biggest ingredient line and the easiest to count.
The formula is short. Actual usage is opening stock plus purchases minus closing stock. Theoretical usage is quantity sold times recipe quantity per item. The gap between them is your usage variance, and it is a question to investigate, not an accusation.
This worksheet walks one milk SKU through one week, with a fully worked example in gallons and dollars. There are no industry targets and no benchmarks to chase. Just your numbers, side by side.
Tracking one ingredient at a time works because a single SKU has one unit, one count and one recipe quantity, so a gap has a short list of possible causes. A whole-store food cost percentage blends dozens of lines and hides where the money went.
Start with whole milk, or whichever milk you buy the most of. It is on nearly every drink ticket, it arrives in a clean unit (gallon or case), and you can count it in under a minute. Once the worksheet works for one SKU, copy it to oat milk, then espresso beans, then pastries.
The usual advice for food cost control is broad: centralize purchasing, standardize recipes, audit regularly, negotiate with suppliers. All sensible. None of it tells you what to do on Monday morning. This worksheet does: pick the SKU, fix the week, count, calculate, ask why.
You need two terms. Actual food cost is what left your shelves. Theoretical food cost is what your sales and recipes say should have left. Actual versus theoretical is the core of food cost variance analysis, and this page runs it at the smallest useful scale.
Food cost management is the habit of comparing what you paid for ingredients with what your menu should have used. Purchase cost is the invoice price of an ingredient, such as a gallon of milk. Plate cost, or drink cost, is the ingredient cost inside one item, such as the milk in a 12 oz latte. Variance analysis compares actual usage with theoretical usage and asks why they differ. Doing it for milk first keeps the work small and the lessons reusable.
Actual usage equals opening stock plus receipts plus transfers in, minus transfers out, minus closing stock. It is the beginning inventory plus purchases minus ending inventory formula, with transfers added for multi-shop owners.
Set the rules before you count:
Pick one cutoff. Count at the same time every week, for example Sunday close, before any delivery is put away. The sales week must use the same cutoff.
Count physically. Opening stock is last week's closing count. Do not carry a number from the screen. Count full containers plus partials, and write partials as decimals (a half-full gallon is 0.5).
Use one unit. Gallons for everything, or half-gallons for everything. Convert deliveries on the way in.
Record receipts from invoices. Count what arrived, not what was ordered. Short deliveries are real.
Record transfers. If one shop sends two gallons to another, it is out at one shop and in at the other. Skip this and the sending shop looks wasteful while the receiving shop looks efficient.
Then multiply actual usage by the unit cost from your invoice to get actual cost. If you bought at different prices during the week, use a weighted average, or the most recent invoice if the spread is small. Pick one rule and keep it.
If your counts and ordering already run through a par-level routine, the par-level worksheet covers how to order. This step only cares about what was consumed.
Theoretical usage is the number of each drink sold times the milk in one standard serving, summed across every milk-bearing drink. It is what your recipes say the week should have consumed.
Pull sold drink counts from your Square item sales report for the exact same cutoff window as your count. Then give each drink a standard milk quantity in ounces. If you have not written these down, do it once with a measuring pitcher: a 12 oz latte, a 16 oz latte, a cappuccino, a flat white, a steamer, a cortado.
Two traps to watch:
Modifiers. A latte sold with oat milk does not use whole milk. Split your counts by the milk actually chosen, or you will overstate theoretical whole milk and understate oat.
Units. Recipes are in ounces, your counts are in gallons. One US gallon is 128 fluid ounces. Convert once, at the end.
If you want the per-drink cost side of this, the latte cost calculator shows how to cost one cup. Here we only need the milk quantity, then dollars at the end.
Waste, remakes, staff drinks and known adjustments are logged separately from theoretical usage, and each item goes in exactly one place. That is how you split explained variance from unexplained variance.
The double-counting rule is simple: if a drink was rung up and sold, its milk is already in theoretical usage. Do not log it again.
Remakes. A customer's drink is made wrong and redone. If only the second drink was rung up, the first drink's milk is extra usage. Log the first drink's milk as a remake. Do not log the second.
Staff drinks. If staff drinks are rung up at a discount or zero price, they are already in your sold counts. If they are made off-ticket, log the milk here.
Waste. Expired or spoiled milk, spills, a dropped carton. Log gallons and a reason.
Known adjustments. A gallon used for a sampling event, a training session, a catered order made off-ticket, a count correction you can document.
Write each entry in ounces or gallons, with a short reason and the date. A tally on a clipboard works. What matters is that every number in the explained column has a note next to it. For a ready-made format, see the coffee shop waste log.
This example is fictional. Every number is invented to show the arithmetic. Shop: one location, whole milk only, one week from Monday open to Sunday close, all quantities in gallons, cost at $4.20 per gallon.
Step 1: Actual usage
| Line | Gallons |
|---|---|
| Opening stock (counted) | 6.0 |
| Receipts (two deliveries, 12 + 12) | 24.0 |
| Transfers in | 0.0 |
| Transfers out | 0.0 |
| Closing stock (counted) | 5.5 |
| Actual usage (6.0 + 24.0 + 0 - 0 - 5.5) | 24.5 |
Actual cost: 24.5 x $4.20 = $102.90.
Step 2: Theoretical usage
| Drink | Sold | Milk per drink (oz) | Total oz |
|---|---|---|---|
| 12 oz latte | 300 | 9 | 2,700 |
| 16 oz latte | 180 | 12 | 2,160 |
| Cappuccino | 90 | 6 | 540 |
| Flat white | 40 | 5 | 200 |
| Steamer | 25 | 10 | 250 |
| Total | 5,850 |
5,850 oz / 128 = 45.7 gallons. That is far above the 24.5 actually used, so the example needs a correction: in this fictional shop, half of those lattes were made with oat milk. Splitting by milk type, whole-milk drinks were 150 12 oz lattes, 90 16 oz lattes, 50 cappuccinos, 20 flat whites and 10 steamers. That is 1,350 + 1,080 + 300 + 100 + 100 = 2,930 oz, or 22.9 gallons.
Theoretical cost: 22.9 x $4.20 = $96.18.
Step 3: Logged explained items
| Item | Gallons |
|---|---|
| Remakes (14 drinks, about 8 oz each) | 0.9 |
| Spoiled carton, discarded Thursday | 0.5 |
| Off-ticket staff drinks | 0.3 |
| Total explained | 1.7 |
Variance
| Gallons | Dollars | |
|---|---|---|
| Actual usage | 24.5 | $102.90 |
| Theoretical usage | 22.9 | $96.18 |
| Total usage variance | 1.6 | $6.72 |
| Explained (logged) | 1.7 | $7.14 |
| Unexplained | -0.1 | -$0.42 |
The logged items slightly exceed the gap, so unexplained is about zero. In percentage form, (24.5 - 22.9) / 22.9 = 7.0% over theoretical, almost entirely accounted for. The week is clean.
Explained variance is the part of the gap you can attach to a logged reason. Unexplained variance is what remains, and it is a prompt to look, not a verdict on anyone.
The formula: usage variance equals actual usage minus theoretical usage. A positive number means you used more than sales and recipes predict. Subtract your explained items, and what is left is unexplained.
Before you suspect people, check the boring causes first:
Count error. A partial gallon eyeballed wrong, or a case missed in the walk-in.
Cutoff mismatch. The sales report and the count do not cover the same hours.
Recipe drift. Baristas pour a 12 oz latte with 10 oz of milk, not 9. Measure a few drinks.
Modifier mix. Oat sold, whole poured, or the reverse.
Unit conversion. Half-gallons entered as gallons on a receipt.
Unlogged waste or remakes. The most common one.
One week of unexplained variance means little. A pattern across four or five weeks, in the same direction, at the same shop, is worth a conversation and a closer look at habits and training. Negative unexplained variance is also information. It often means a missed receipt or an overcounted opening stock.
I am deliberately not giving a target percentage. A good number depends on your menu, your milk mix and how carefully you log. Your own trend line is the benchmark that matters.
Run the worksheet at the same time every week, in the same units, for every shop, and compare each shop's variance to its own past weeks first, then to the others.
A weekly routine for one to five shops:
Count every shop at the same close time.
Enter receipts and transfers from invoices.
Pull sold drink counts from Square for the same window.
Calculate actual, theoretical and variance for milk.
Review the waste and remake log, then the unexplained number.
Write one sentence on what you will check next week.
A spreadsheet handles one shop fine. Across three, the friction is keeping the same units, the same recipe quantities and the same cutoff everywhere.
StoreSync is operations software for Square coffee and restaurant shops that puts inventory and recipe costing together in one login and syncs with Square. Use the manual worksheet above to compare your counts with your recipe quantities. If you are weighing tools for the wider job, the guide to food cost management software for Square coffee shops covers the buying criteria. This post is the manual method underneath.
Recipe costing tells you what each item costs to make. Menu engineering uses those costs alongside sales counts to show which items earn the most margin and which to reprice, rework or drop. Inventory management supplies the actual usage numbers that prove whether your recipe costs match reality. Run in a loop, costing sets the expectation, inventory checks it, and menu decisions follow. StoreSync covers inventory and recipe costing for Square shops in one login. Calculate and review the worksheet separately.
The strategies that hold up are simple and repeatable: cost every recipe from current invoice prices, count inventory on a fixed schedule, compare actual and theoretical usage weekly, log waste and remakes as they happen, and review prices with suppliers when costs move. Start with your biggest line, which for most coffee shops is milk, then repeat the same worksheet for cups, syrups and beans. Consistency beats sophistication, so pick a weekly rhythm you can keep.
Do one SKU for four straight weeks before you touch anything else. You will learn your real recipe quantities, your real waste habits and how clean your counts are, and you will have a trend line instead of a guess. Once milk is boring, copy the sheet to the next ingredient. If you run Square and want inventory and recipe costing together in one login across your shops, book a demo at getstoresync.com/demo and bring your milk worksheet.
Actual usage equals beginning inventory plus purchases minus ending inventory. Multiply by unit cost to get cost of goods used. For a multi-shop owner, add transfers in and subtract transfers out. Compare that with theoretical usage (quantity sold times recipe quantity) to see your variance. The worksheet above runs this for one milk SKU.
Actual food cost is what you really used, calculated from counts and receipts. Theoretical food cost is what your sold items and standard recipes say you should have used. The difference is usage variance. Part of it is explained by logged waste, remakes and staff drinks, and the rest is unexplained and worth investigating.
No. Most variance comes from count errors, mismatched cutoffs, over-pouring, modifier mix and unlogged waste or remakes. Treat unexplained variance as a question. Look for a pattern across several weeks at the same shop before drawing any conclusion about people.
StoreSync combines inventory and recipe costing in one login and syncs with Square. One shop is $146 a month billed annually, and each extra shop adds $50 a month. Use the manual worksheet above to calculate and investigate usage variance.