Tabellab

Demand History Cleanup: SKU Codes, Promo and Stockout Weeks

Lesson 1 of 20Lab

All lessons
  1. Demand History Cleanup: SKU Codes, Promo and Stockout WeeksLabFree
  2. Moving Average Forecast: Simple and WeightedLab Locked
  3. Forecast Error: MAD, MAPE and BiasLab Locked
  4. Seasonality Index: Spreading an Annual Forecast over MonthsLab Locked
  5. Safety Stock: z × σ × √Lead Time with NORM.S.INVLab Locked
  6. Reorder Point and Inventory Position: When to OrderLab Locked
  7. Projected Inventory Balance: Week by WeekLab Locked
  8. Order Suggestions: Minimum Order Quantity and Pack Size with CEILINGLab Locked
  9. ABC Classification: Annual Value and Cumulative ShareLab Locked
  10. Supplier Lead Times: Average, Variability and On-Time RateLab Locked
  11. Service Level: Unit Fill Rate and Line Fill RateLab Locked
  12. Planner Dashboard: Forecast Chart, Weeks of Cover and AlertsLab Locked
  13. Promotion Uplift: Lift Factors from Past Promos, a Promo Forecast and the Extra OrderLab Locked
  14. From Units to Cartons, Pallets and Trucks: ROUNDUP, Pallet Layers and Truck FillLab Locked
  15. Excess and Slow-Moving Stock: Units Above Target Cover, Excess Value and Weeks Since Last SaleLab Locked
  16. Shelf Life and Expiry Risk: FEFO Order, Weeks Left and Units at RiskLab Locked
  17. Short Supply: Fair-Share Allocation in Whole Cartons and Fill Rate per DestinationLab Locked
  18. Stock Across Warehouses: Cover per Site, Surplus, Shortfall and TransfersLab Locked
  19. Open Purchase Orders: Needed-By vs Confirmed Dates, Days Late and the Expedite ListLab Locked
  20. Final Project – Weekly Replenishment Plan: Safety Stock, Reorder Points, Order Quantities, Pallets and Order ValueLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Use Find & Replace or Power Query for very large exports; for a few hundred rows TRIM and UPPER in a helper column are quicker.
Loading the spreadsheet…

Why demand history cleanup comes before every forecast

You have just started as a supply planner, or you have inherited a range of products from a colleague, and the first thing anyone asks is: how much will we sell next month, and how much do we need to order? The honest answer starts with a less exciting question: how much did customers actually want in the past? This lesson is for new demand and supply planners, inventory analysts, buyers who also plan stock, and small-business owners who reorder from a spreadsheet. It teaches the step that most forecasting tutorials skip: turning a messy shipment export into a clean demand history you can trust.

Forecasting methods get the attention, but every one of them, from a simple moving average to a statistical model, takes the history as given. If a promotion week is counted as normal demand, the forecast goes up and you overstock. If a week when the shelf was empty is counted as normal, the forecast goes down and you run out again, which makes the next history even worse. And if the same product appears under two slightly different codes, half its history simply disappears from your lookups. In the free lab above you are Maya, the new supply planner at Lakeshore Kitchenware, a fictional housewares company, and you clean eight weeks of history for the frying pan LK-210 before anyone builds a forecast on it.

The core idea: shipments are not demand

Your order system records what was shipped or invoiced. A planner wants what was demanded. Most weeks the two are close enough, but in two kinds of weeks they are clearly different. In a stockout week you shipped less than customers wanted because there was nothing left to ship, so the number understates demand. In a promotion week a price cut or advertising pulled extra sales forward, so the number overstates the demand you will see in a normal week.

A practical baseline therefore uses only the normal weeks. That is not the only way planners handle these weeks: some replace a stockout week with an estimate, others model the promotion lift separately so it can be planned again for the next promotion. But leaving the unusual weeks out of the baseline is the simplest correct method, and it is the one this lesson uses. The second idea is data hygiene. The export writes the code as " lk-210", "LK-210 " and "Lk-210". To a person these are the same pan; to XLOOKUP, SUMIFS or a PivotTable some of them are different keys. Spaces always matter in a match, and case matters in tools such as Power Query, so you standardise the code before you use it.

Keep the raw history unchanged and do all cleaning in new columns. If a colleague later asks why week 5 is missing from the baseline, you can show the original number, the note and the flag side by side. Overwriting the export destroys that audit trail.

Worked example: eight weeks of LK-210

Here is the history from the lab after cleaning. Column B shows the raw codes with their spaces made visible, column E the cleaned code and column F the flag. All numbers are fictional.

Week (A)SKU raw (B)Units (C)Note (D)SKU clean (E)Use? (F)
1" lk-210"118NormalLK-210Yes
2"LK-210 "124NormalLK-210Yes
3"lk-210"196PromoLK-210No
4"Lk-210"131NormalLK-210Yes
5"LK-210"42StockoutLK-210No
6"lk-210 "127NormalLK-210Yes
7" LK-210"120NormalLK-210Yes
8"lk-210"136NormalLK-210Yes

Six of the eight weeks are normal. Their shipments are 118, 124, 131, 127, 120 and 136 units, which add up to 756. The baseline is therefore 756 / 6 = 126 units per week, and in Excel it is =AVERAGEIF(F2:F9,"Yes",C2:C9). The usable weeks are counted with =COUNTIF(F2:F9,"Yes"), which returns 6.

LK-210: units shipped per week (example data)NormalPromoStockoutBaseline 126 (normal weeks)W1118W2124W3196W4131W542W6127W7120W8136050100150200Units
Example shipments for LK-210. The promo week (W3) and the stockout week (W5) sit far from the normal weeks, so they are left out of the dashed baseline.

Now compare with the plain average of all eight weeks: =AVERAGE(C2:C9) returns 124.25. That looks close to 126, and it is tempting to say the cleanup made no difference. But it is close only by luck: the promo week (+70 above baseline) and the stockout week (−84 below) almost cancel each other out. Suppose the stockout had not happened and only the promo week were in the data. Then the plain average of the seven weeks would be (756 + 196) / 7 = 136, eight percent above the real baseline, and every order you place from it would be eight percent too big. A rule that works only when the errors cancel is not a rule. The flags make the result correct every time.

From raw export to a usable baselineRaw export8 weeks of LK-210codes with spacesand mixed casepromo + stockoutClean SKU=UPPER(TRIM(B2))one code: LK-210raw column keptFlag weeks=IF(D2="Normal","Yes","No")Promo, Stockoutbecome NoBaseline=AVERAGEIF(F2:F9,"Yes",C2:C9)= 126 per weekEach step is a new column. The raw history in columns B to D is never overwritten.
The cleanup in four steps, each in its own helper column, so the original export stays untouched.

How to do it in Excel, step by step

The steps work in Excel 365, Excel 2021 and Excel for the web. On a Mac use Cmd where Windows uses Ctrl unless noted.

  1. Paste or open the export so that week, raw SKU, units shipped and note are in columns A to D, with headers in row 1. Do not edit these columns.
  2. Add two headers: SKU in E1 and Use? in F1. Give the new columns a light fill (Home › Fill Color) so everyone sees they are calculated.
  3. In E2 type =UPPER(TRIM(B2)) and press Enter. TRIM removes spaces at both ends and reduces double spaces inside the text to one, UPPER turns lk-210 into LK-210.
  4. In F2 type =IF(D2="Normal","Yes","No"). The text in quotes must match the note exactly, apart from case, which IF ignores.
  5. Select E2:F9 and press Ctrl+D to fill both formulas down to week 8.
  6. Below the table, label A11 Baseline weekly demand and type in B11 =AVERAGEIF(F2:F9,"Yes",C2:C9). The first range is checked against the condition, the last range is averaged.
  7. Label A12 Usable weeks and type in B12 =COUNTIF(F2:F9,"Yes").
  8. Check the flags visually: select A1:F9, press Ctrl+Shift+L to add filters and filter column F for No. You should see exactly week 3 (Promo) and week 5 (Stockout).
  9. Optionally colour the excluded weeks: Home › Conditional Formatting › New Rule › Use a formula, enter =$F2="No" and choose a pale fill.
TRIM does not remove the non-breaking space (character 160) that some web systems and PDFs produce. If a code still does not match after TRIM, try =TRIM(SUBSTITUTE(B2,CHAR(160)," ")). A quick test: =LEN(B2) shows how many characters the cell really has.

When the history has many products

A real export has hundreds or thousands of SKUs and a year or more of weeks. The same three helper columns still work, you just need one more condition in the baseline. With the cleaned code in column E and the flag in column F, the baseline for one product is =AVERAGEIFS(C:C,E:E,"LK-210",F:F,"Yes"). Put the SKU list in its own column and refer to it instead of typing the code, and you have a baseline for every product in one column. For very large or recurring exports, Power Query (Data › Get Data) can trim, change case and filter in repeatable steps, but for a few hundred rows the helper columns are faster to build and easier for a colleague to audit.

One more judgment call: how many usable weeks are enough? Six of eight is fine for a lab. If a product has only two or three normal weeks in a quarter, the baseline is fragile, and a planner would look for a longer history or a similar product. That is why the lab asks you to count the usable weeks next to the baseline. A number without its sample size is easy to over-trust.

Exclude or estimate? Two ways to treat a stockout week

Leaving a stockout week out of the baseline is safe, but it throws information away. Some planners prefer to replace the shipped number with an estimate of what demand would have been, for example the baseline itself or the average of the two weeks before and after. In the LK-210 example the neighbours of week 5 are week 4 with 131 units and week 6 with 127 units, so an estimate would be =AVERAGE(C5,C7) = 129 units instead of the 42 that were shipped. Either approach is defensible as long as it is written down and used consistently. What is never defensible is using the 42 as if it were normal demand.

The same thinking applies to partial stockouts. If the pan was available on Monday and Tuesday and then sold out, the week is not empty, but it is still too low. A practical rule many teams use is to flag any week in which the item was out of stock for part of the week, and to note the number of days. The note column in the lab is where such information lives. The richer and more consistent your notes, the better every later step in this course works.

Write your cleanup rules down

  • Which notes exclude a week. For example: Promo, Stockout, Data error. Everything else counts as normal.
  • How codes are standardised. For example: trimmed, upper case, non-breaking spaces replaced.
  • Minimum history. For example: at least six usable weeks before a baseline is used without review.
  • Who may change a flag. Flags change forecasts, so a second person should be able to see why a week was excluded.

Supply planner reviewing weekly demand history and forecast charts in Excel
Every planning task in this course starts from a clean demand history like the one you build in lesson 1.

Common mistakes and how to spot them

  • Averaging all weeks. =AVERAGE(C2:C9) includes promo and stockout weeks. Spot it by checking that the baseline formula refers to the flag column.
  • Cleaning in place. Running Find & Replace on the raw column loses the original values. Spot it if you can no longer explain a number to a colleague. Always add a new column.
  • Forgetting hidden characters. The code looks clean but a lookup still fails. Spot it with =LEN(E2): LK-210 should have 6 characters.
  • Inconsistent notes. The export says "normal", "Normal " and "NORMAL". IF ignores case but not the trailing space. Clean the note column with TRIM as well, or flag with =IF(TRIM(D2)="Normal","Yes","No").
  • Swapped AVERAGEIF ranges. =AVERAGEIF(C2:C9,"Yes",F2:F9) returns #DIV/0! because no number equals "Yes". The criteria range comes first, the range to average last.
  • Excluding too much. Flagging every unusually high week as a promo removes real growth from the history. Only exclude weeks with a known cause, and write the cause in the note.
  • Ignoring the count. A baseline from two weeks looks as precise as one from twenty. Always show the number of usable weeks.

Quick reference: functions and shortcuts in this lesson

Function or shortcutWhat it does in demand cleanupExample
TRIMRemoves leading, trailing and double spaces=TRIM(B2)
UPPERTurns text into capitals=UPPER(TRIM(B2))
IFFlags a week as usable or not=IF(D2="Normal","Yes","No")
AVERAGEIFAverages only the rows that meet one condition=AVERAGEIF(F2:F9,"Yes",C2:C9)
COUNTIFCounts the usable weeks=COUNTIF(F2:F9,"Yes")
AVERAGEIFSAverage with several conditions (SKU and flag)=AVERAGEIFS(C:C,E:E,"LK-210",F:F,"Yes")
EXACTCase-sensitive comparison, shows hidden differences=EXACT(B2,E2)
Ctrl+DFills a formula down the selectionSelect E2:E9, press Ctrl+D
Ctrl+Shift+LTurns the filter on or offFilter column D for Promo
Paste Special › Values (Ctrl+Alt+V)Freezes cleaned codes as valuesBefore you delete helper columns

Real-world uses

  • Monthly forecast review. Before the sales and operations meeting you refresh the export, the helper columns recalculate, and the baseline for every product is ready for the forecast.
  • Post-promotion analysis. With promo weeks flagged, you can compare their sales with the baseline and estimate the lift for the next campaign.
  • Stockout reporting. Counting the weeks flagged "Stockout" per product shows where availability problems distort your history most.
  • Merging two systems. After a system change, codes from the old and new system are written differently. Cleaned codes let you join both histories into one.
  • Small retailer reordering. A shop owner who reorders from a spreadsheet uses the clean baseline instead of last week’s number, so one unusual week does not drive the next order.

What you will build in the rest of this course

Lesson 1 is free. The other nineteen lessons are part of the paid course, a one-time purchase of $9.90, and each one is a lab with the same step-by-step checking. Together they take you from clean history to a complete replenishment decision.

Moving Average Forecast: Simple and Weighted. You turn the clean history into a first forecast and see how weighting recent weeks makes it react faster.

Forecast Error: MAD, MAPE and Bias. You measure how wrong the forecast was in three ways and learn which number answers which question.

Seasonality Index: Spreading an Annual Forecast over Months. You calculate a season index per month and spread a yearly forecast so that busy months get more.

Safety Stock: z × σ × √Lead Time with NORM.S.INV. You size the buffer stock for a chosen service level and see how variability and lead time drive it.

Reorder Point and Inventory Position: When to Order. You combine demand over the lead time with safety stock and decide, from on-hand and on-order stock, whether to order today.

Projected Inventory Balance: Week by Week. You roll stock forward week by week with forecast and incoming orders and find the first week that goes short.

Order Suggestions: Minimum Order Quantity and Pack Size with CEILING. You turn a net requirement into an order the supplier will accept.

ABC Classification: Annual Value and Cumulative Share. You rank products by annual value and split them into A, B and C classes to decide where to spend your attention.

Supplier Lead Times: Average, Variability and On-Time Rate. You analyse real delivery dates to find the lead time and the variability your safety stock should use.

Service Level: Unit Fill Rate and Line Fill Rate. You measure how much of the ordered demand you actually delivered, by units and by order lines.

Planner Dashboard: Forecast Chart, Weeks of Cover and Alerts. You bring it together on one page with a forecast chart, weeks of cover and alerts for the products that need action.

Summary

A forecast is only as good as its history, and shipment history is not the same as demand. Clean the codes with =UPPER(TRIM(B2)), flag promo and stockout weeks with IF, and calculate the baseline from the normal weeks only with AVERAGEIF, next to a COUNTIF of usable weeks. For LK-210 that gives 126 units per week from 6 weeks. Keep the raw data untouched and do everything in helper columns. Now scroll up and try the free lab: clean E2:E9, flag F2:F9 and let the Coach check your baseline.

Frequently asked questions

How do I clean demand history in Excel before forecasting?

Standardise product codes with TRIM and UPPER in a helper column, flag unusual weeks such as promotions and stockouts, and calculate the baseline only from the normal weeks with AVERAGEIF.

Why should stockout weeks be excluded from a forecast baseline?

In a stockout week you shipped less than customers wanted, so the number understates demand. Including it pulls the forecast down and can cause the next stockout.

What does TRIM do in Excel?

TRIM removes spaces at the start and end of text and reduces repeated spaces inside it to one. It does not remove non-breaking spaces, which need SUBSTITUTE with CHAR(160).

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF uses one condition and has the range to average last. AVERAGEIFS takes several conditions, for example SKU and flag, and has the range to average first.

Is shipment history the same as demand?

Not always. Shipments are lower than demand when stock ran out and higher than normal demand during promotions, so planners adjust or exclude those weeks.

Should I delete promo weeks from the data?

No. Keep the raw history and mark the weeks with a flag. You need the promo weeks later to measure the lift of past promotions and to plan the next one.

Keep going: Excel for Supply Planners: Demand Forecast, Safety Stock & Replenishment

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Moving Average Forecast: Simple and Weighted
  2. Forecast Error: MAD, MAPE and Bias
  3. Seasonality Index: Spreading an Annual Forecast over Months
  4. Safety Stock: z × σ × √Lead Time with NORM.S.INV
  5. Reorder Point and Inventory Position: When to Order
  6. Projected Inventory Balance: Week by Week
  7. Order Suggestions: Minimum Order Quantity and Pack Size with CEILING
  8. ABC Classification: Annual Value and Cumulative Share
  9. Supplier Lead Times: Average, Variability and On-Time Rate
  10. Service Level: Unit Fill Rate and Line Fill Rate
  11. Planner Dashboard: Forecast Chart, Weeks of Cover and Alerts
  12. Promotion Uplift: Lift Factors from Past Promos, a Promo Forecast and the Extra Order
  13. From Units to Cartons, Pallets and Trucks: ROUNDUP, Pallet Layers and Truck Fill
  14. Excess and Slow-Moving Stock: Units Above Target Cover, Excess Value and Weeks Since Last Sale
  15. Shelf Life and Expiry Risk: FEFO Order, Weeks Left and Units at Risk
  16. Short Supply: Fair-Share Allocation in Whole Cartons and Fill Rate per Destination
  17. Stock Across Warehouses: Cover per Site, Surplus, Shortfall and Transfers
  18. Open Purchase Orders: Needed-By vs Confirmed Dates, Days Late and the Expedite List
  19. Final Project – Weekly Replenishment Plan: Safety Stock, Reorder Points, Order Quantities, Pallets and Order Value

Buy course — $9.90 See the full course