Practice and Answer Key: Cloud Kitchen Weekly Report
Goal of this practice: Build a complete one-page report from raw orders using the same steps as the 20 projects.
या practice चं ध्येय: 20 projects सारख्याच steps वापरून raw orders पासून एका पानाचा पूर्ण report बनवा.
इस practice का लक्ष्य: 20 projects जैसे ही steps से raw orders से एक पन्ने की पूरी report बनाएँ.
Suppose Rani runs a fictional cloud kitchen that sells in four cities. She has 8 orders for the week and needs sales, food cost, on-time delivery (30 minutes or less) and margin by city on one dashboard. All data is fictional practice data.
| OrderID | City | Channel | Amount | FoodCost | Mins |
|---|---|---|---|---|---|
| CK-01 | Pune | Delivery | 640 | 210 | 24 |
| CK-02 | Pune | Pickup | 380 | 120 | 12 |
| CK-03 | Nashik | Delivery | 520 | 190 | 35 |
| CK-04 | Kolhapur | Delivery | 450 | 150 | 28 |
| CK-05 | Pune | Delivery | 720 | 260 | 31 |
| CK-06 | Nagpur | Pickup | 290 | 95 | 10 |
| CK-07 | Nashik | Delivery | 560 | 180 | 22 |
| CK-08 | Kolhapur | Pickup | 340 | 110 | 15 |
Exercise (one under the other):
- Convert the range to a Table named
tblKitchen(Ctrl+T). - Add the calculated columns Margin and OnTime.
- Write the five KPI formulas on a KPI sheet.
- Insert a PivotTable of Sum of Margin by City.
- Add a Clustered Column PivotChart and a Channel slicer.
- Build the Dashboard sheet with KPI cards, the chart and the slicer.
Expected result (check your numbers):
| Check | Formula | Expected |
|---|---|---|
| Sales | =SUM(tblKitchen[Amount]) |
₹3,900 |
| Food Cost % | =SUM(tblKitchen[FoodCost])/SUM(tblKitchen[Amount]) |
33.7% |
| Average Order Value | =AVERAGE(tblKitchen[Amount]) |
₹487.50 |
| On-time % | =COUNTIF(tblKitchen[OnTime],"Yes")/ROWS(tblKitchen[OnTime]) |
75.0% |
| Delivery Margin | =SUMIFS(tblKitchen[Margin],tblKitchen[Channel],"Delivery") |
₹1,900 |
| Pivot: Sum of Margin, Kolhapur | (PivotTable) | ₹530 |
| Pivot: Sum of Margin, Nagpur | (PivotTable) | ₹195 |
| Pivot: Sum of Margin, Nashik | (PivotTable) | ₹710 |
| Pivot: Sum of Margin, Pune | (PivotTable) | ₹1,150 |
| Pivot: Grand Total | (PivotTable) | ₹2,585 |
Answer key (open after you try)
Margin: =[@Amount]-[@FoodCost]
OnTime: =IF([@Mins]<=30,"Yes","No")
Sales: =SUM(tblKitchen[Amount])
Food Cost %: =SUM(tblKitchen[FoodCost])/SUM(tblKitchen[Amount])
Average Order Value: =AVERAGE(tblKitchen[Amount])
On-time %: =COUNTIF(tblKitchen[OnTime],"Yes")/ROWS(tblKitchen[OnTime])
Delivery Margin: =SUMIFS(tblKitchen[Margin],tblKitchen[Channel],"Delivery")
PivotTable: Rows City, Values Sum of Margin; PivotChart Clustered Column; Slicer Channel
Common mistakes:
| What you see | Why | Fix |
|---|---|---|
| On-time % shows 0% | OnTime column typed as "yes " with a space | Use the formula, not typed text |
| Food Cost % shows 33.5% | Averaged the row percentages | Use SUM ÷ SUM |
| Pivot shows Sum of Amount | Wrong field in Values | Drag Margin into Values |
| On-time % shows 75.0% but you expected more | 2 orders took more than 30 minutes | That is the correct answer; the rule is ≤ 30 |
Ravindra Bagale's rule: every dashboard number must match a KPI cell you can check by hand. Here Pune has the highest margin.
Quick recap
- Every project follows one pattern: Table → calculated column → KPI cells → PivotTable → chart → dashboard.
- Ratios of totals (SUM ÷ SUM) are the right way to show overall %, not an average of row percentages.
- SUMIFS, COUNTIF and AVERAGEIFS answer "how much, how many, how big" for one group.
- Check each KPI by hand for two rows and against the expected table before you share.
- Templates, guides and ready-made dashboards give you more practice in the same industry.
Loading practice tasks...
- Total seats (Easy): Add up Seats in D2:D11.
- Average price per seat (Easy): Average of E2:E11, rounded to 2 decimals.
- Rows in Basic (Easy): How many rows have Plan "Basic"?
- Largest seats (Easy): The largest value in D2:D11.
- Seats for Basic (Medium): Total of D2:D11 where B is "Basic".
- Basic in Chicago (Medium): Total of D2:D11 where B is "Basic" and C is "Chicago".
- Best in Basic (Medium): Largest E2:E11 value where B is "Basic".
- Value of row 2 (Medium): D2 times E2.
- Look up Ananya Nair (Medium): The Seats for "Ananya Nair" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Basic (Hard): Share of D2:D11 that is "Basic", rounded to 3 decimals.
- Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".