Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.16 Restaurants: Spend per Cover and Food Cost

By Last updated:

Example first. Salman runs a fictional restaurant brand in five cities and wants sales, spend per guest and food cost for the day.

Restaurants project — in 30 seconds

What: A Restaurants tracker: one Table tblBills, the calculated column FoodCostPct, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Restaurants report: Sales, Average Spend per Cover, Food Cost % are the numbers a manager reads first.

Tiny example: From 6 rows: Sales = ₹9,900, Average Spend per Cover = ₹550.00, Food Cost % = 32.9%.

First look at the data (before → after):

Restaurants project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

BillID FoodCostPct
R-501 31.9%
R-502 34.4%
R-503 32.5%
R-504 35.7%
R-505 30.5%
R-506 37.8%

Expected result — KPI cells (expected results)

KPI Formula Result
Sales =SUM(tblBills[BillAmount]) ₹9,900
Average Spend per Cover =SUM(tblBills[BillAmount])/SUM(tblBills[Covers]) ₹550.00
Food Cost % =SUM(tblBills[FoodCost])/SUM(tblBills[BillAmount]) 32.9%
Before table and formula as text

Before — Table tblBills (fictional sample rows)

BillID City Channel Covers BillAmount FoodCost
R-501 Pune Dine-in 4 2,480 790
R-502 Pune Delivery 2 960 330
R-503 Nashik Dine-in 6 3,540 1,150
R-504 Kolhapur Delivery 3 1,290 460
R-505 Nagpur Dine-in 2 1,180 360
R-506 Solapur Delivery 1 450 170

Formula / click path

FoodCostPct: =[@FoodCost]/[@BillAmount]
Sales: =SUM(tblBills[BillAmount])
Average Spend per Cover: =SUM(tblBills[BillAmount])/SUM(tblBills[Covers])
Food Cost %: =SUM(tblBills[FoodCost])/SUM(tblBills[BillAmount])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblBills in Table Design › Properties › Table Name.
  2. Add the column FoodCostPct with =[@FoodCost]/[@BillAmount]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblBills[BillAmount]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and BillAmount in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Channel.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a daily restaurant sheet: food cost % per bill, sales, average spend per cover (sales ÷ covers), overall food cost %, sales by city, a chart and a Channel slicer.

Calculated column formulas:

Column Formula
FoodCostPct =[@FoodCost]/[@BillAmount]

KPI cells (check your numbers):

KPI Formula Result
Sales =SUM(tblBills[BillAmount]) ₹9,900
Average Spend per Cover =SUM(tblBills[BillAmount])/SUM(tblBills[Covers]) ₹550.00
Food Cost % =SUM(tblBills[FoodCost])/SUM(tblBills[BillAmount]) 32.9%

PivotTable (expected):

City Sum of BillAmount
Kolhapur ₹1,290
Nagpur ₹1,180
Nashik ₹3,540
Pune ₹3,440
Solapur ₹450
Grand Total ₹9,900

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Channel and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Restaurants_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Restaurants dashboard guide and its workbook Restaurants_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of BillAmount?
  2. Filter the Table on City = Nashik (Data › Filter): what is Sales for Nashik alone?
  3. Add the Clustered Column PivotChart and the Channel slicer to your Dashboard sheet.
Answer

Nashik (₹3,540). For Nashik only (1 row): Sales = ₹3,540. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Write the definition of Sales under its KPI card in small text. Two people reading the same dashboard should agree on what the number means.

Loading practice tasks...

  1. Total covers (Easy): Add up Covers in D2:D11.
  2. Average bill amount (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown (Easy): How many rows have Outlet "Downtown"?
  4. Largest covers (Easy): The largest value in D2:D11.
  5. Covers for Downtown (Medium): Total of D2:D11 where B is "Downtown".
  6. Downtown in Seattle (Medium): Total of D2:D11 where B is "Downtown" and C is "Seattle".
  7. Best in Downtown (Medium): Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Vikram Singh (Medium): The Covers for "Vikram Singh" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Downtown (Hard): Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →