Inventory levels, delivery performance, supplier costs — the Excel formulas logistics teams use every day.
-- Closing stock
=opening_stock + received - dispatched
-- Reorder point
=average_daily_demand * lead_time_days + safety_stock
-- Days of stock remaining
=current_stock / average_daily_demand
-- Reorder alert
=IF(current_stock <= reorder_point, "REORDER NOW", "OK")-- On-time delivery rate
=COUNTIFS(status_col,"Delivered",on_time_col,"Yes") / COUNTIF(status_col,"Delivered") * 100
-- Average delivery days
=AVERAGEIF(status_col,"Delivered",days_col)
-- Late deliveries by supplier
=COUNTIFS(supplier_col,"Supplier A",on_time_col,"No")-- Total cost by supplier
=SUMIF(supplier_col,"Supplier A",cost_col)
-- Cost per unit
=total_cost / units_received
-- Cost variance vs budget
=(actual_cost - budget_cost) / budget_cost * 100
-- Cheapest supplier for a product
=MINIFS(unit_price_col, product_col, "Widget A")-- Actual lead time in working days
=NETWORKDAYS(order_date, delivery_date)
-- Average lead time by supplier
=AVERAGEIF(supplier_col, "Supplier A", lead_time_col)
-- Expected delivery date
=WORKDAY(order_date, standard_lead_time)When matching delivery records to a product master file, XLOOKUP is faster and more reliable than VLOOKUP: =XLOOKUP(product_code, master_codes, master_descriptions, "Not found")
Practice these formulas in the Sales & Operations track — 100 exercises covering targets, pipelines, and reporting. Free to start.
Start the Sales & Operations track free →