Excel Skills for Supply Chain

2 min read

Excel remains useful across planning, inventory, procurement and logistics because it is fast for ad hoc analysis and scenario modeling.

The goal is not to master every Excel feature. It is to solve supply-chain problems reliably.

Data cleaning

Learn to handle:

  • duplicates;
  • missing values;
  • inconsistent dates;
  • text/number mismatch;
  • extra spaces;
  • unit-of-measure issues;
  • invalid IDs.

Use tables and reproducible steps where possible.

Lookups

Useful functions include:

  • XLOOKUP;
  • INDEX/MATCH;
  • XMATCH.

Supply-chain examples:

  • join supplier lead time to materials;
  • retrieve item cost;
  • map warehouse location;
  • match forecast to actual.

Pivot tables

Use pivots for:

  • spend by supplier;
  • inventory by category;
  • delivery performance by carrier;
  • demand by product;
  • shortage by planner.

SUMIFS and COUNTIFS

These are useful for exception counts and grouped totals.

Example:

  • open PO quantity by material;
  • late orders by supplier;
  • inventory value by site.

Date and text functions

Supply chains are full of dates.

Useful tasks:

  • calculate lead time;
  • aging;
  • week/month buckets;
  • due-date flags;
  • parse shipment references.

Inventory analysis

Build:

  • ABC segmentation;
  • days of supply;
  • slow-moving flags;
  • reorder logic;
  • excess inventory scenarios.

Forecast and planning models

Useful models:

  • moving averages;
  • forecast error;
  • bias;
  • demand versus supply;
  • capacity scenario.

Scenario analysis

Use assumptions clearly.

Examples:

  • lead time +20%;
  • demand +15%;
  • MOQ reduced;
  • supplier delayed;
  • capacity added.

Power Query

Power Query helps make repeated cleaning/combining steps more reproducible.

Good use cases:

  • monthly PO files;
  • shipment extracts;
  • supplier files;
  • inventory snapshots.

Project ideas

Build one workbook that another person could audit.

Include:

  • raw data;
  • cleaned data;
  • assumptions;
  • calculations;
  • output;
  • decision note.

“Advanced Excel” becomes credible when the workbook supports a real supply-chain decision.

Related content

Sources