Excel is here to stay.
This applies to demand and supply planning and to the whole supply chain.
There is a constant battle with messy data. Multiple formats. Conflicting inputs. Complex requirements.
Excel formulas can solve the recurring headaches that slow planners down every week.
Here’s how you can use Excel formulas step by step, with common challenges they solve across demand planning, supply planning, inventory, and logistics.
1. Demand Planning
Formula: SUMIFS
Issue: Retailers and distributors send demand data in different formats (daily, weekly, monthly).
Solution: Roll it up into one unified view.
=SUMIFS(Demand, Customer, “Walmart”, Month, “Jan”)
Result: Harmonizes formats so you see total demand by channel.
Formula: TEXT
Issue: Files mix daily, weekly, and monthly demand buckets.
Solution: Standardize into months.
=TEXT(A2,”MMM-YYYY”)
Result: Creates consistent time buckets for analysis.
Formula: FORECAST.LINEAR
Issue: Forecasting manually introduces bias.
Solution: Build a simple statistical baseline.
=FORECAST.LINEAR(NextMonth,Month,Sales)
Result: Neutral forecast you can layer market insights onto.
2. Supply Planning
Formula: IF / IFS
Issue: MOQ and supplier rules are often ignored.
Solution: Apply MOQ directly in the formula.
=IF(Demand<MOQ,MOQ,Demand)
Result: Automatically enforces supplier constraints.
Formula: ROUNDUP
Issue: Orders must match case/pack sizes (e.g., vials in 24s, trays of 6).
Solution: Always round up to the nearest pack size.
=ROUNDUP(Demand/PackSize,0)*PackSize
Result: Ensures clean, feasible orders suppliers can process.
Formula: INDEX + MATCH
Issue: Pulling BOMs or lead times from multiple sheets wastes time.
Solution: Dynamically retrieve the right value.
=INDEX(BOM_Quantity, MATCH(SKU,BOM_SKU,0))
Result: Links demand to BOM or lead time automatically.
3. Inventory Management
Formula: VLOOKUP / XLOOKUP
Issue: Checking inventory vs safety stock for each SKU is time-consuming.
Solution: Retrieve safety stock per SKU.
=XLOOKUP(SKU,SKU_List,SafetyStock)
Result: Instantly flags shortages below safety stock.
Formula: COUNTIFS
Issue: Tracking stockouts across hundreds of SKUs manually is painful.
Solution: Count zero-inventory events.
=COUNTIFS(Inventory,”=0″,SKU,SKU_ID,Date,”>=”&StartDate,Date,”<=”&EndDate)
Result: Identifies chronic stockout offenders.
Formula: NETWORKDAYS
Issue: Replenishment timing often forgets holidays.
Solution: Calculate working days between deliveries.
=NETWORKDAYS(LastDelivery,NextDelivery,Holiday_List)
Result: Reflects real-world replenishment cycles.
4. Logistics & Distribution
Formula: CONCATENATE / TEXTJOIN
Issue: Route IDs are created manually, leading to errors.
Solution: Auto-generate route IDs.
=TEXTJOIN(“-“,TRUE,Depot,Route,Truck)
Result: Clean, traceable delivery IDs.
Formula: FILTER (Excel 365)
Issue: Dispatch data from all warehouses is dumped in one sheet.
Solution: Create warehouse-specific filtered views.
=FILTER(Dispatch_Table,Warehouse=SelectedWarehouse)
Result: Quick filtered reports, no manual clicking.
Formula: SORT + UNIQUE
Issue: Route lists are duplicated and messy.
Solution: Extract clean, sorted unique routes.
=SORT(UNIQUE(Route_List))
Result: Clean route master list in seconds.
Conclusion
Excel can solve for planners many problems in seconds: mismatched formats, ignored supplier or capacity constraints, forgotten pack sizes, or messy logistics data.
Here we covered the formulas below:
Demand planners → Fix multiple formats with SUMIFS and TEXT.
Supply planners → Respect MOQs and packs with IF and ROUNDUP.
Inventory managers → Monitor safety stock with XLOOKUP and COUNTIFS.
Logistics teams → Clean messy routes with TEXTJOIN, FILTER, and SORT+UNIQUE.
Start today by picking one pain point from your job, apply the formula, and cut hours of frustration into minutes.
