Food cost calculator for Excel
A spreadsheet that costs one dish from what you paid your suppliers. Type your ingredients and the selling price. The sheet gives you the cost per portion and the food cost percentage.
Free. No email asked. Also opens in Google Sheets and Apple Numbers.
What is in the file
One sheet for one dish, with room for ten ingredients. White cells are yours to fill in. Blue cells are worked out for you.
| Row | Ingredient | Paid | Bought | Unit | Usable % | Used | Unit | Cost |
|---|---|---|---|---|---|---|---|---|
| 2 | Beef | $300.00 | 10 | kg | 100% | 300 | g | $9.00 |
| 3 | Potatoes | $32.00 | 5 | kg | 100% | 200 | g | $1.28 |
| 12 | Cost of the dish | $10.28 | ||||||
| 13 | Portions | 1 | ||||||
| 14 | Cost per portion | $10.28 | ||||||
| 15 | Selling price | $34.00 | ||||||
| 16 | Food cost | 30.2% | ||||||
| 17 | Gross profit per portion | $23.72 | ||||||
| 18 | Target food cost | 30% | ||||||
| 19 | Price needed for the target | $34.27 | ||||||
The beef and potatoes are an example, the same one as on the online food cost calculator, so both give $10.28 and 30.2 percent. Type over them with your own dish. Units are picked from a list: g, kg, oz, lb, ml, L and each.
Build it yourself: the four formulas
Start a blank sheet with these columns: A the ingredient, B what you paid, C how much you bought, D its unit, E the usable share, F how much the dish uses, G its unit, H the cost. Put your first ingredient in row 2.
- Price per unit. What you paid, divided by what you bought. The usable share is the part you serve after trimming or cooking. Type 100% when nothing is lost. =B2/(C2*E2)
- Cost of each ingredient. The price per unit, times the amount the dish uses. CONVERT changes the amount used into the unit you bought in, for example grams into kilograms. =B2/(C2*E2)*CONVERT(F2,G2,D2) CONVERT needs Excel's own unit names: g, kg, lbm for pounds, ozm for ounces, l and ml. The file you download uses a small units table instead, so you can pick lb or oz from a list.
- Cost per portion. Add the ingredients, then divide by the number of portions. Here the ingredients are rows 2 to 11 and the portions are in H13. =SUM(H2:H11)/H13
- Food cost percentage. The cost per portion divided by the selling price. Here they are in H14 and H15. =H14/H15 To go the other way and set a price for a target, divide the cost per portion by the target in H18. =H14/H18
Where a spreadsheet stops being enough
- Prices go out of date. The sheet uses the price you typed. When a supplier raises a price, nothing changes until you type the new one.
- Every dish is another sheet. An ingredient used in twenty dishes has to be updated in twenty places, or linked by hand.
- Units are typed by hand. A case of 6 cans, bought by the case and used by the kg, takes a formula most people get wrong once.
- It only costs dishes. It does not know your stock, your sales or your profit for the month.
Excel sheet, online calculator or Foodnance
| Excel sheet | Online calculator | Foodnance | |
|---|---|---|---|
| Cost | Free | Free | From $79 CAD a month, with a 14-day free trial |
| Prices come from | You type them | You type them | A photo of the supplier invoice |
| When a price changes | You update each sheet | You run it again | Every dish is re-costed for you |
| Keeps your dishes | Yes, in your own files | No, nothing is saved | Yes |
| Best for | A first try on one or two dishes | One quick answer | A full menu with prices that move |
See what Foodnance costs, or go back to the food cost calculator.
Excel template questions
Short answers to what people usually ask.
Is the Excel food cost template free?
Yes. Download it and use it as you like. We do not ask for your email address.
Does it work in Google Sheets?
Yes. Upload the file to Google Drive and open it with Google Sheets. The formulas work the same way. It also opens in Apple Numbers.
How do I convert units in Excel?
Use the CONVERT function. Give it the amount, the unit it is in and the unit you want, so 300 with g and kg returns 0.3. Excel writes pounds as lbm and ounces as ozm. A weight cannot be converted to a volume without knowing the ingredient.
How often should I update the prices?
Each time a supplier invoice arrives with a different price. Until you type the new price, the sheet keeps showing the old cost.
Can I cost more than one dish?
Yes. Copy the Dish sheet once for each dish. An ingredient used in several dishes has to be updated on each copy when its price changes.
When the spreadsheet becomes a second job
Foodnance is food costing software for independent Canadian restaurants and bakeries. Photograph a supplier invoice, and your recipe costs, margins and P&L update themselves.
Request accessCurious about the cost? See pricing
Questions? We are here to help. Email us [email protected]