Skip to content
Praval Technologies

Case study

Demand forecasting and markdown optimization with AI

A mid-size US retail chain replaced spreadsheet-and-instinct buying with forecasting models and Copilot-assisted Power BI dashboards on Microsoft Fabric, buying closer to true demand and marking down smarter.

Markdown loss avoided
$3.4MMarkdown loss avoidedAgainst the prior-season run-rate
Stockouts on core lines
↓24%Stockouts on core linesOn core replenishment lines
Forecast accuracy
78%Forecast accuracyUp from 61%
Gross margin
+2.1 ptsGross marginOn piloted categories

Buying on instinct, correcting with the price gun

A Columbus-based retail chain buys roughly 40,000 SKUs a season across 60 stores. Demand plans were built in spreadsheets from last year's sales plus a buyer's judgement, and when the plan was wrong, the markdown pen fixed it. The engagement ran around seven months, phased, for the Head of Merchandising and the CDO.

Forecasts built on last year. Plans extrapolated prior-season sales, ignoring weather, local demographics, promotions and price elasticity.

Markdowns as a reflex. Slow sellers were cut late and cut deep: one blanket discount across all stores, regardless of local sell-through.

Wrong stock, wrong store. Allocation ignored store-level demand differences, so one branch sold out while another discounted the same item.

Answers took days. Every "how is this category tracking?" question meant an analyst ticket and a wait, too slow for in-season decisions.

Margin lost at both ends of the season

Bad forecasts cost twice: sales missed on the lines that sold out, and margin given away on the lines that didn't.

  • 31% of units sold at markdown, most of it discounted late and deep.
  • $11M annual markdown spend, largely unplanned.
  • 61% forecast accuracy: roughly two in five units mis-planned.

A season planned at 52% gross margin landed at 41.6%, the gap given away almost entirely at the price gun:

Gross margin
Planned52.0%
Lost to markdown−7.9 pts
Lost to stockouts−1.5 pts
Lost to shrink−1.0 pts
Realized41.6%

"We found out we'd bought wrong the same way every season: when the markdown report landed. By then the only lever left was price."

— Head of Merchandising, Columbus-based retail chain

What a usable forecast had to do

We audited three seasons of sales, inventory and price history to find where plans broke down. The pattern was clear: errors concentrated in new lines, weather-sensitive categories, and store-level allocation. That set the bar.

  • Forecast at SKU × store × week, not just chain level.
  • Factor in price, promotions, weather and local demand.
  • Recommend markdown timing and depth, not just flag slow sellers.
  • Show buyers the reasoning: no unexplained black-box numbers.
  • Let merchants ask questions in plain language, in seconds.
  • Refresh in-season, so plans adjust as the season moves.

Forecast, price, and a dashboard merchants can talk to

One Fabric model feeding two decisions (what to buy, and when to mark it down) surfaced in Power BI where buyers already work, with Copilot for the questions in between.

Signals inFabric modelsDecisions out
Three seasons of POS sales historyDemand forecast at SKU × store × weekBuy quantities by line
Inventory and receipts by storeNew-line forecasting by attributeStore-level allocation
Price and promotion calendarPrice elasticity by categoryMarkdown recommendations
Weather and local demographicsMarkdown timing and depth optimizerIn-season reforecast alerts
Product hierarchy and attributesRetrained in-seasonCopilot Q&A for merchants

What merchants ask Copilot:

  • "Which lines are tracking more than 20% below plan this week?"
  • "If I mark this category down 20% now instead of 30% in week 10, what happens to margin?"
  • "Which stores are selling this SKU fastest, and where should I transfer stock from?"

Phased rollout, measurable change

Piloted on two categories before scaling chain-wide, proving accuracy against held-back actuals before any buying decision depended on it.

  1. Data foundation: sales, inventory, price and promotion history unified in a Fabric lakehouse with a shared product hierarchy.
  2. Baseline and backtest: measure current forecast accuracy, then backtest models against three seasons of held-out actuals.
  3. Pilot two categories: run models alongside the existing process; buyers compare recommendations before committing.
  4. Markdown optimizer: add price elasticity and markdown timing recommendations, with margin impact shown per option.
  5. Scale and enable: roll out chain-wide in Power BI with Copilot, and train merchants to self-serve their own questions.

Error concentrated in new lines and seasonal categories early in the season, exactly where buying decisions get locked. Forecast error by category, from the pre-model baseline (week 1) to after in-season reforecasting (week 15):

CategoryW1W3W5W7W9W11W13W15
Outerwear38%34%29%22%16%12%10%9%
Footwear31%28%24%19%14%11%9%8%
Basics22%19%16%13%11%9%8%7%
Seasonal46%41%33%25%18%13%11%10%
New lines52%45%36%27%20%15%12%11%

The old markdown pattern waited, then cut deep to clear: 10% and then 30%, late in the season. The optimizer starts earlier and steps down gradually (10%, 15%, 25%, 35%), clearing the same units at a materially higher average selling price. Measured against a 75% planning target, forecast accuracy rose in every category: basics, footwear, outerwear, seasonal and new lines alike.

Buying closer to demand, discounting by choice

Merchandising moved from correcting mistakes with price to planning against a forecast that reflects how the season actually behaves. Markdowns still happen, but by decision, earlier and shallower, rather than as an end-of-season clearance reflex. Buyers get their own answers in seconds instead of waiting on a report.

Result
Markdown loss avoided$3.4M
Stockouts↓ 24%
Forecast accuracy61% → 78%
Gross margin+2.1 pts

Built on Microsoft Fabric, and Power BI with Copilot.

Recognise any of this in your own estate?

Start with the problem rather than the technology, and we will tell you honestly whether it is ours to solve.