Spreadsheets

A daily sales, wastage and food-cost sheet for a small restaurant

A daily sales, wastage and food-cost sheet for a small restaurant in Excel, with a food-cost percentage formula and a weekly view to find where money leaks.

Editorial illustration for A daily sales, wastage and food-cost sheet for a small restaurant

Food-cost percentage is the value of the food you used, divided by what you sold. A small restaurant can track it with a daily sales sheet, a purchases and wastage log and a weekly summary. The formula is simple: opening stock plus purchases minus closing stock, divided by sales. The useful part is comparing weeks with your own earlier weeks, not with anyone else's number.

What goes in the daily sales row?

ColumnHeadingNotes
ADateOne row per day
BCashCounted in the drawer
CUPIMatches the credits
DCardMachine settlement
EDelivery appsGross sales, not payout
FTotal sales=SUM(B2:E2)
GFood purchasesVegetables, dairy, meat, grocery
HWastage valueFrom the wastage log

For delivery apps, record the gross order value in column E. If the payout is lower, put the difference in your expenses as commission, so the food-cost percentage is not flattered by a smaller sales figure.

How do you log wastage?

Keep a Wastage tab with date, item, quantity, cost value and reason: spoilt, over-prepared, returned by a customer, staff meal or spillage. Entering the reason is the point. A total on its own does not tell you what to change. For the daily wastage value use =SUMIFS(Wastage!$D$2:$D$2000, Wastage!$A$2:$A$2000, A2).

How do you calculate food cost?

Count stock at the start and end of the week, even approximately, at what you paid. Then:

  • Food used: =opening stock + purchases for the week - closing stock
  • Food cost percentage: =food used / sales for the week
  • Wastage percentage: =wastage value / sales for the week

Say a week's sales are ₹2,10,000, opening stock is ₹18,000, purchases are ₹66,000 and closing stock is ₹20,000. Food used is 18,000 + 66,000 - 20,000 = ₹64,000. Food cost is 64,000 divided by 2,10,000, about 30.5%. These are made-up figures, not a benchmark.

How do you build the weekly view?

Add a Week column in the daily sheet with =A2-WEEKDAY(A2,2)+1, giving the Monday of the week. On a Weekly tab, list each Monday and use:

  • Sales: =SUMIFS(Daily!$F$2:$F$400, Daily!$I$2:$I$400, A2)
  • Purchases: =SUMIFS(Daily!$G$2:$G$400, Daily!$I$2:$I$400, A2)
  • Wastage: =SUMIFS(Daily!$H$2:$H$400, Daily!$I$2:$I$400, A2)

If you cannot count stock weekly, use purchases divided by sales as a rough guide, and treat a sudden jump as a prompt to look closer.

Where do the leaks usually hide?

  • Portions that creep larger than the recipe card says.
  • Over-ordering perishables before a festival or weekend that did not deliver.
  • Supplier price changes that nobody entered into the menu price.
  • Staff meals and sampling that never reach a record.

What would you check on Monday morning?

  1. The three highest wastage entries from last week and their reasons.
  2. Whether UPI and card totals match the bank credits.
  3. Which item had the biggest price change from the supplier.

How do you price a dish from this data?

Add a Recipes tab with each dish, its ingredients, the quantity and the current price per unit. The sum is the plate cost. Divide plate cost by the menu price to get that dish's food-cost percentage, and sort the list. Dishes with high percentages and low sales are candidates for a recipe change, a smaller portion or a new price. Update the ingredient prices from your latest purchase bills at least once a month.

Re-cost the Recipes tab after any festival season, because vegetable, oil and dairy prices move and the plate cost moves with them.

Supplier payments and UPI settlements are on your bank statements, and SpendixAI can import those so purchases by vendor are easy to total when you fill the Purchases column.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports