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

27. 20 Industry Projects

27.11 Logistics: On-Time Delivery by Route

By Last updated:

Example first. Ravindra Bagale dispatches fictional trucks from a Pune hub to five cities and wants on-time delivery tracked per shipment.

Logistics project — in 30 seconds

What: A Logistics tracker: one Table tblShipments, 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 Logistics report: Shipments, On-time %, Total Km are the numbers a manager reads first.

Tiny example: From 6 rows: Shipments = 6, On-time % = 66.7%, Total Km = 2,365.

First look at the data (before → after):

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

Expected result — Calculated column

ShipID OnTime
SH-201 Yes
SH-202 No
SH-203 Yes
SH-204 Yes
SH-205 No
SH-206 Yes

Expected result — KPI cells (expected results)

KPI Formula Result
Shipments =ROWS(tblShipments[ShipID]) 6
On-time % =COUNTIF(tblShipments[OnTime],"Yes")/ROWS(tblShipments[OnTime]) 66.7%
Total Km =SUM(tblShipments[Km]) 2,365
Before table and formula as text

Before — Table tblShipments (fictional sample rows)

ShipID City Km PromisedDays ActualDays
SH-201 Nagpur 720 2 2
SH-202 Solapur 250 1 2
SH-203 Nashik 210 1 1
SH-204 Kolhapur 230 1 1
SH-205 Nagpur 720 2 3
SH-206 Sambhaji Nagar 235 1 1

Formula / click path

OnTime: =IF([@ActualDays]<=[@PromisedDays],"Yes","No")
Shipments: =ROWS(tblShipments[ShipID])
On-time %: =COUNTIF(tblShipments[OnTime],"Yes")/ROWS(tblShipments[OnTime])
Total Km: =SUM(tblShipments[Km])

Do it step by step:

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

The brief. Build a shipment sheet: an on-time flag (actual days ≤ promised days), number of shipments, on-time %, total kilometres, kilometres by destination city, a chart and an OnTime slicer.

Calculated column formulas:

Column Formula
OnTime =IF([@ActualDays]<=[@PromisedDays],"Yes","No")

KPI cells (check your numbers):

KPI Formula Result
Shipments =ROWS(tblShipments[ShipID]) 6
On-time % =COUNTIF(tblShipments[OnTime],"Yes")/ROWS(tblShipments[OnTime]) 66.7%
Total Km =SUM(tblShipments[Km]) 2,365

PivotTable (expected):

City Sum of Km
Kolhapur 230
Nagpur 1,440
Nashik 210
Sambhaji Nagar 235
Solapur 250
Grand Total 2,365

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Bar › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick OnTime 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 Logistics_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 Logistics dashboard guide and its workbook Logistics_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 Km?
  2. Filter the Table on City = Nagpur (Data › Filter): what is Shipments for Nagpur alone?
  3. Add the Clustered Bar PivotChart and the OnTime slicer to your Dashboard sheet.
Answer

Nagpur (1,440). For Nagpur only (2 rows): Shipments = 2. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Keep the calculated column OnTime inside the Table. New rows then get the formula automatically, and the PivotTable only needs Refresh.

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 Neha Gupta (Medium): The Parcels for "Neha Gupta" (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 →