Excel remains the most widely used planning tool.
No one can argue with that.
Even with advanced planning systems in place, Excel continues to be part of nearly every planner’s workflow.
In fact, most modern planning solutions allow downloads to Excel, and many teams still rely on spreadsheets for analysis, flexibility, and speed.
In other words: spreadsheets aren’t going anywhere.
In this article, I cover 9 essential Excel functionalities every supply planner should know.
For each one, you’ll find:
- A real-world situation
- A challenge
- A practical solution using Excel
Let’s dive in!
1. VLOOKUP / XLOOKUP in Action
Situation:
A supply planner at a consumer goods company is preparing for the monthly S&OP review. They’ve just received the consensus demand plan, a list of SKUs with agreed forecast quantities for the next 6 months. Separately, they have the latest supply plan, which includes the projected production and inventory for the same SKUs.
Challenge:
The two datasets don’t talk to each other. The demand plan is in one Excel file, and the supply plan is in another. Matching them manually by SKU to identify gaps would take hours and risk errors.
Solution:
The planner uses XLOOKUP to quickly match the two datasets by SKU and pull the supply quantities next to the consensus demand.
=XLOOKUP([@SKU], SupplyPlan[SKU], SupplyPlan[AvailableQty])
Now, for each SKU in the demand plan, the corresponding supply quantity is displayed in the same row. This allows the planner to compare demand and supply side-by-side and calculate any gaps.
2. SUMIFS in Action
Situation:
A materials planner at a juice company is preparing next month’s cap requirements. The company bottles juice in two sizes:
- 1-gallon bottles
- 64-ounce (half-gallon) bottles
Each bottle needs 1 cap, but the cap type is different depending on the bottle size. The 1-gallon bottle uses a larger cap, while the 64-ounce bottle uses a standard cap.
The production plan shows how many bottles of each SKU will be produced, and each SKU is linked to a specific cap type in the bill of materials (BOM).
Challenge:
The production data includes SKUs with different formats and volumes. The planner wants to calculate how many caps of each type are needed.
Solution:
The supply planner uses SUMIFS to calculate the total number of caps required per cap type.
Ste p 1 – The planner first uses XLOOKUP to add a Cap Type column to the production plan:
=XLOOKUP([@SKU], BOM[SKU], BOM[CapType], “Missing”)
Step 2 – Then, the planner uses SUMIFS to total up caps by cap type:
To calculate Large Caps:
=SUMIFS(Production[Units], Production[CapType], “Large Cap”)
To calculate Standard Caps:
=SUMIFS(Production[Units], Production[CapType], “Standard Cap”)
3. Conditional Formatting in Action
Situation:
A supply planner at a snack food company manages production for potato chips packaged in various bag sizes: 1 oz, 2.5 oz, and family-size 10 oz bags. Each week, the company wants to review dozens of SKUs across different regions to check for stockouts, overproduction, or SKUs without production plans.
Challenge:
With hundreds of rows of data, it’s hard to spot which chip SKUs are at risk. A planner might miss that the 2.5 oz BBQ bag is about to run out or that they’re overproducing the 10 oz plain chips.
Solution:
The planner applies Conditional Formatting in Excel to make risks jump out visually:
- Red fill if ending inventory is below safety stock
=EndingInventory < SafetyStock
- Yellow fill if planned production exceeds demand by more than 30%
=PlannedProduction > ForecastQty * 1.3
- Gray font if planned production is missing (zero or blank)
=OR(PlannedProduction=0, ISBLANK(PlannedProduction))
With these rules in place:
- Red = stockout risk (e.g., 2.5 oz BBQ)
- Yellow = overstock risk (e.g., 10 oz Plain)
- Gray = no production planned (e.g., 1 oz Salt & Vinegar)
The planner now opens the file and sees risks quickly. I love conditional formatting! It is one of my favorites.
- Pivot Tables in Action
Situation:
Continuing with our example, a demand planner at the same snack company needs to review weekly sales and forecast performance for potato chips packaged in the various bag sizes: 1 oz, 2.5 oz, and 10 oz. The raw data includes thousands of rows with sales, forecasts, and regions. Leadership wants a quick overview:
- Which bag sizes are underperforming?
- Are certain regions missing targets?
Challenge:
The data is too large and unstructured to make quick sense of. Sorting or filtering manually takes too long. Mistakes are easy to make.
Solution:
The planner uses a Pivot Table in Excel to summarize performance by bag size and region:
- Insert Pivot Table from the raw data
- Drag Region into Rows
- Drag Bag Size (1 oz, 2.5 oz, 10 oz) into Columns
- Add Actual Sales and Forecast to Values
- Add a calculated field:
Forecast Accuracy = 1 – ABS(Sales – Forecast)/Forecast
Now the planner has a clear, compact table showing:
- Total sales by bag size and region
- Forecasts vs actuals
- Where accuracy is strong or weak
They can filter by week, zoom into underperforming regions, and even export a summary for the S&OP meeting.
5. Excel Dashboards in Action
Situation:
A supply planner at a cereal company needs to give the operations team and executives a daily overview of key metrics:
- Production vs. plan
- Inventory levels by cereal type
- Days of supply for top SKUs
They manage SKUs like corn flakes, honey granola, and chocolate puff cereal across multiple plants.
Challenge:
The data comes from different tabs: one for production, one for inventory, one for demand. Every day, the planner spends an hour copying and pasting charts and tables into emails. It’s repetitive, inefficient, and prone to mistakes.
Solution:
The planner builds a simple Excel dashboard with live connections to the core data sheets. It includes:
- A bar chart showing production vs. plan by cereal type
- A line chart for weekly inventory trends (in pounds)
- A table showing days of supply per SKU, color-coded (low stock in red, healthy in green)
- Slicers to filter by plant or week. Slicers are great!
The dashboard is set on one tab, pulling automatically from the latest data updates.
6. Dynamic Safety Stock Calculators in Action
Situation:
A materials planner at an orange juice manufacturer needs to calculate how much safety stock to hold for key raw materials, like concentrated orange juice, sugar, and bottle caps. Demand fluctuates based on seasonality (peak in summer), and supplier lead times vary.
The team previously used fixed safety stock numbers, but frequent stockouts during high demand and excess inventory in off-peak periods led to operational headaches.
Challenge:
The fixed safety stock approach isn’t responsive to real variability. The planner needs a way to calculate safety stock based on actual demand fluctuation and lead time.
Solution:
The planner creates a Dynamic Safety Stock Calculator in Excel using this formula:
Safety Stock = Z × STDEV.P(Demand) × √Lead Time
Where:
- Z = Service level factor (e.g., 1.65 for 95%)
- STDEV.P(Demand) = Standard deviation of weekly or daily demand (in gallons)
- Lead Time = Number of days or weeks it takes to replenish (from vendor or production)
Example:
For 1-gallon orange juice bottles, based on 12 weeks of demand:
- Standard deviation = 1,200 gallons
- Lead time = 2 weeks
- Z = 1.65
=1.65 * STDEV.P(DemandRange) * SQRT(2)
This is about 2,800 gallons of safety stock for that SKU.
7. Rolling Coverage Calculators in Action
Situation:
A supply planner at a cookie manufacturer needs to track how many weeks of supply are left for key SKUs like chocolate chip, oatmeal raisin, and double fudge. The leadership team wants to avoid both stockouts and excess inventory as promotions and seasonal spikes approach.
Challenge:
The planner receives weekly demand forecasts and inventory reports; but they’re stored in separate Excel tabs. It’s not easy to answer basic questions like:
- “How long will this inventory last?”
- “Do we have enough stock to cover next month?”
Manually calculating weeks of supply for each SKU is time-consuming and error-prone.
Solution:
The planner builds a simple formula in Excel to calculate rolling weeks of supply:
=OnHandInventory / WeeklyForecast
If inventory is in cases of cookies, and weekly demand is also in cases, the result is the coverage in weeks.
Example:
- SKU: Chocolate Chip (12-pack)
- On-hand inventory: 6,000 cases
- Weekly forecast: 1,500 cases
=6000 / 1500 → 4 weeks of supply
The formula is applied across all SKUs and updated weekly. The planner highlights:
- <2 weeks = stock risk
- 8 weeks = overstock risk
8. Scenario Planning Sheets in Action
Situation:
A demand planner at a regional donut manufacturer is preparing for a potential sales spike due to a large retail promotion in the Southeast. The commercial team has three different estimates:
- Best case: 20% increase in demand
- Expected case: 10% increase
- Worst case: flat demand
The planner needs to understand how each scenario will impact production, raw materials, and inventory.
Challenge:
With multiple scenarios and hundreds of SKUs (glazed, chocolate frosted, jelly-filled, etc.), it’s easy to lose track of how assumptions change outcomes. Manually reworking the plan each time is slow and messy.
Solution:
The planner builds a Scenario Planning Sheet in Excel using drop-downs and formulas.
At the top of the sheet:
- A drop-down menu allows selecting the case: Best, Expected, or Worst
In the background:
- The forecast row uses an IF or CHOOSE formula to adjust demand based on the selected case
=BaseForecast * CHOOSE(ScenarioSelector, 1, 1.1, 1.2)
- Where:
- 1 = Worst case (no increase)
- 1.1 = Expected case (10% increase)
- 1.2 = Best case (20% increase)
All downstream calculations (production volume, flour and sugar needs, fryer time, etc.) update automatically.
9. Power Query for Data Cleanup in Action
Scenario:
A supply planner at a frozen pizza company receives weekly sales, inventory, and production files from 5 different plants. Each plant submits Excel sheets in different formats; with different column names, extra rows, inconsistent date formats, and even typos in SKU names like “Pepperoni Lrg” vs. “Large Pepperoni.”
The planner needs to consolidate everything into a clean dataset to generate reports for:
- Production planning
- Ingredient requirements (cheese, dough, sauce)
- Service level tracking
Challenge:
Manually copying, cleaning, and merging data from five spreadsheets every week takes 4–6 hours and results in frequent errors. The reporting is always late; and supply issues go unnoticed.
Solution:
The planner uses Power Query in Excel to automate the cleanup and consolidation process:
- Load all plant files into Power Query
- Apply consistent column renaming, remove blank rows, standardize SKU names using a lookup table
- Merge queries into one master dataset
- Set the query to refresh with one click each week
Now, the planner just clicks “Refresh All”, and the cleaned, consolidated data is ready in under 30 seconds. Power Query is a game changer.
Which functionality do you like the most? And why?
