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

27. 20 Industry Projects

27.1 Retail and Quick Commerce: City Sales and On-Time Delivery

By Last updated:

Example first. Ravindra Bagale runs fictional Blinkit and Amazon Now dark stores and wants one sheet that shows sales by city and how many orders arrived within 15 minutes.

Retail and Quick Commerce project — in 30 seconds

What: A Retail and Quick Commerce tracker: one Table tblOrders, the calculated column OnTime, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Retail and Quick Commerce report: Total Sales, Average Order Value, On-time % are the numbers a manager reads first.

Tiny example: From 6 rows: Total Sales = ₹2,060, Average Order Value = ₹343.33, On-time % = 66.7%.

First look at the data (before → after):

Retail and Quick Commerce project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

OrderID OnTime
BLK-5001 Yes
AMN-5002 No
BLK-5003 Yes
AMN-5004 Yes
BLK-5005 No
AMN-5006 Yes

Expected result — KPI cells (expected results)

KPI Formula Result
Total Sales =SUM(tblOrders[Amount]) ₹2,060
Average Order Value =SUM(tblOrders[Amount])/ROWS(tblOrders[Amount]) ₹343.33
On-time % =COUNTIF(tblOrders[OnTime],"Yes")/ROWS(tblOrders[OnTime]) 66.7%
Before table and formula as text

Before — Table tblOrders (fictional sample rows)

OrderID City Platform Items Amount Mins
BLK-5001 Pune Blinkit 3 420 11
AMN-5002 Pune Amazon Now 2 260 18
BLK-5003 Nashik Blinkit 5 610 14
AMN-5004 Nagpur Amazon Now 1 150 9
BLK-5005 Nashik Blinkit 4 380 22
AMN-5006 Solapur Amazon Now 2 240 13

Formula / click path

OnTime: =IF([@Mins]<=15,"Yes","No")
Total Sales: =SUM(tblOrders[Amount])
Average Order Value: =SUM(tblOrders[Amount])/ROWS(tblOrders[Amount])
On-time %: =COUNTIF(tblOrders[OnTime],"Yes")/ROWS(tblOrders[OnTime])

Do it step by step:

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

The brief. Build a daily quick-commerce tracker: total sales, average order value, on-time delivery % (15 minutes or less), sales by city, a chart and a dashboard with a Platform slicer.

Calculated column formulas:

Column Formula
OnTime =IF([@Mins]<=15,"Yes","No")

KPI cells (check your numbers):

KPI Formula Result
Total Sales =SUM(tblOrders[Amount]) ₹2,060
Average Order Value =SUM(tblOrders[Amount])/ROWS(tblOrders[Amount]) ₹343.33
On-time % =COUNTIF(tblOrders[OnTime],"Yes")/ROWS(tblOrders[OnTime]) 66.7%

PivotTable (expected):

City Sum of Amount
Nagpur ₹150
Nashik ₹990
Pune ₹680
Solapur ₹240
Grand Total ₹2,060

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 Platform 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 Retail_Quick_Commerce_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 Retail and Quick Commerce dashboard guide and its workbook Retail_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 Amount?
  2. Filter the Table on City = Nashik (Data › Filter): what is Total Sales for Nashik alone?
  3. Add the Clustered Column PivotChart and the Platform slicer to your Dashboard sheet.
Answer

Nashik (₹990). For Nashik only (2 rows): Total Sales = ₹990. The chart shows 4 bars, one per city.

Ravindra Bagale's Tip

Check Total Sales by hand for two rows before you trust the dashboard. If your hand answer and the cell differ, the formula range is usually wrong.

Loading practice tasks...

  1. Total parcels (Easy): Add up Parcels in D2:D11.
  2. Average cost (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail (Easy): How many rows have Carrier "Royal Mail"?
  4. Largest parcels (Easy): The largest value in D2:D11.
  5. Parcels for Royal Mail (Medium): Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in London (Medium): Total of D2:D11 where B is "Royal Mail" and C is "London".
  7. Best in Royal Mail (Medium): Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Rohan Iyer (Medium): The Parcels for "Rohan Iyer" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Royal Mail (Hard): Share of D2:D11 that is "Royal Mail", 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 →