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

27. 20 Industry Projects

27.10 Inventory and Supply Chain: Days of Cover and Reorder List

By Last updated:

Example first. Rani plans replenishment for fictional dark-store warehouses and wants a list of SKUs that will run out before new stock arrives.

Inventory and Supply Chain project — in 30 seconds

What: A Inventory and Supply Chain tracker: one Table tblStock, the calculated column DaysCover, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Inventory and Supply Chain report: Total On Hand, SKUs to Reorder, Average Days of Cover are the numbers a manager reads first.

Tiny example: From 6 rows: Total On Hand = 1,185, SKUs to Reorder = 2, Average Days of Cover = 10.8.

First look at the data (before → after):

Inventory and Supply Chain project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated columns

SKU DaysCover Action
SKU-11 14.0 OK
SKU-12 3.6 Reorder
SKU-13 25.0 OK
SKU-14 5.0 Reorder
SKU-15 13.0 OK
SKU-16 4.0 OK

Expected result — KPI cells (expected results)

KPI Formula Result
Total On Hand =SUM(tblStock[OnHand]) 1,185
SKUs to Reorder =COUNTIF(tblStock[Action],"Reorder") 2
Average Days of Cover =AVERAGE(tblStock[DaysCover]) 10.8
Before table and formula as text

Before — Table tblStock (fictional sample rows)

SKU City OnHand DailyUse LeadDays
SKU-11 Pune 420 30 7
SKU-12 Pune 90 25 5
SKU-13 Nashik 300 12 10
SKU-14 Nagpur 75 15 6
SKU-15 Solapur 260 20 8
SKU-16 Kolhapur 40 10 4

Formula / click path

DaysCover: =[@OnHand]/[@DailyUse]
Action: =IF([@DaysCover]<[@LeadDays],"Reorder","OK")
Total On Hand: =SUM(tblStock[OnHand])
SKUs to Reorder: =COUNTIF(tblStock[Action],"Reorder")
Average Days of Cover: =AVERAGE(tblStock[DaysCover])

Do it step by step:

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

The brief. Build a reorder sheet: days of cover (on hand ÷ daily use), a Reorder flag when cover is shorter than the supplier lead time, total on hand, SKUs to reorder, average days of cover, stock by city, a chart and an Action slicer.

Calculated column formulas:

Column Formula
DaysCover =[@OnHand]/[@DailyUse]
Action =IF([@DaysCover]<[@LeadDays],"Reorder","OK")

KPI cells (check your numbers):

KPI Formula Result
Total On Hand =SUM(tblStock[OnHand]) 1,185
SKUs to Reorder =COUNTIF(tblStock[Action],"Reorder") 2
Average Days of Cover =AVERAGE(tblStock[DaysCover]) 10.8

PivotTable (expected):

City Sum of OnHand
Kolhapur 40
Nagpur 75
Nashik 300
Pune 510
Solapur 260
Grand Total 1,185

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 Action 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 Inventory_Supply_Chain_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.

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

  1. Which city has the highest sum of OnHand?
  2. Filter the Table on City = Pune (Data › Filter): what is Total On Hand for Pune alone?
  3. Add the Clustered Column PivotChart and the Action slicer to your Dashboard sheet.
Answer

Pune (510). For Pune only (2 rows): Total On Hand = 510. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Show SKUs to Reorder next to the rupee total. A percentage or ratio without the size behind it can make a small city look like a big problem.

Loading practice tasks...

  1. Total stock units (Easy): Add up Stock units in D2:D11. (sheet "Data")
  2. Average unit cost (Easy): Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Antibiotic (Easy): How many rows have Category "Antibiotic"? (sheet "Data")
  4. Largest stock units (Easy): The largest value in D2:D11. (sheet "Data")
  5. Days taken (Easy): C2 minus B2. (sheet "Dates")
  6. Start year (Easy): Year of B3. (sheet "Dates")
  7. Due month (Easy): Month number of C3. (sheet "Dates")
  8. Earliest start (Easy): Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Stock units for Antibiotic (Medium): Total of D2:D11 where B is "Antibiotic". (sheet "Data")
  10. Antibiotic in Madrid (Medium): Total of D2:D11 where B is "Antibiotic" and C is "Madrid". (sheet "Data")
  11. Working days (Medium): Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due (Medium): Weekday number of C4 (Sunday = 1). (sheet "Dates")

See all practice tasks in the Task Hub →