Markdowns: From Descriptive to Prescriptive

Descriptive, predictive and prescriptive: the 3 approaches you can apply to solve any business problem using analytics. For example markdowns.

Markdowns are big: what to do with excess inventory?

Some statistics from my recent piece: over $2 trillion of inventory in the United States alone — $2,040 billion — $1.43 of inventory for every $1 in sales. So what to do with that 43% excess inventory, when it will reach end-of-life?

When a product exhausts its likelihood of selling, marking down its price is a reasonable approach to try and clear leftover inventory: salvage value, prevent waste.

There are 3 broad approaches to markdowns, which showcase the three main strategies to analytics: descriptive, predictive and prescriptive. Prior to going into the details, a bit of context.

Context: the product pyramid

Like any product-centric analytics problem, markdowns require navigating complex product hierachies, and using the appropriate approach at each level.

An example of product pyramid may look like the following one, taking as example the company called Event Network, the leading operator of experiential retail in North America: stores at iconic cultural attractions and lifestyle settings throughout the US and Canada e.g. museums, zoos, aquariums.

Different levels of the product pyramid require different forecast approaches.

For example, at the lowest level sit specific products, or Stock Keeping Units (in short, SKU): an S-sized green T-shirt with a yellow SAN FRANCISCO print. I know, that’s a weird palette, maybe go for another SKU? The M-sized gray hoodie with a blue print. OK, you get the idea.

At this granular level, sales are relatively infrequent and therefore a detailed forecast will be necessary. Going up the product pyramid, aggregated sales are more frequent, and therefore aggregated forecasting methods can be applied.

In the end, all the methods must match, so that the sum of the parts tallies with the aggregate, e.g. the forecast sum must match the sum of the forecasts.

See the following visual summary for reference:

The 3 approaches to markdowns

You can use three approaches to set optimal markdowns while navigating the complexity of the product pyramid, using different amounts and types of data:

• Descriptive: using historical data only
• Predictive: using price elasticity data
• Prescriptive: using as much data as possible.

Each approach requires different amounts of data, business processes and of course analytics strategy.

The descriptive approach is relatively simple to build and maintain, it requires very little data. No surprise then, that this approach only allows to make top-down recommendations and is therefore relatively ineffective, as I show further below.

On the other end of the spectrum, the prescriptive approach is relatively harder to build and maintain, and requires much larger data sets. However, this approach allows to make fully bottom-up recommendations, and can provide significant financial gains.

The descriptive approach is somewhere in the middle, as I will show later.

So let me start with the baseline approach first: descriptive!

Approach #1: Descriptive markdowns

The first, and also the most straightforward approach, to choosing which products to markdown, relies on a simple rule of thumb:

Discount products with a lot of inventory compared to sales.

The intuition is to use the coverage ratio, or days of inventory, as a proxy for the size of the problem. Steps of calculation:

1. Historical sales of a product: for example, 100 pieces a month
2. Inventory on hand: for example, 1,000 pieces
3. Coverage ratio, e.g. 10 months in this case: 1,000/100 → it will take 10 months, if sales continue at their historical average, to sell-out of the current inventory
4. Markdown budget spent for each product = extra % discount X unit cost of inventory X number of inventory pieces on hand. Read an example below.
5. Ranking of SKU: from the highest coverage downwards.

At this point, pick the first N products until the total available markdown budget will have been spent!

Simple illustrative example of markdown budget for one product:

• was 20% off; now going to 50% off, therefore extra 30% off
• $10 unit cost of inventory
• 10k pieces of inventory on-hand

→ $30k markdown budget spent = 30% x $10 x 10k.

See how this works?

Rank the SKUs in descending order, and you get an easy way to pick and choose: based on the % markdown and budget available, you can easily determine which SKUs to markdown (those with the highest coverage).

Advantages:

• Simple pen-and-paper method, can be done 100% in a spreadsheet like Microsoft Excel
• Makes the CFO’s life easy, as there is 100% certainty over the budget spent, which in fact was their decision to begin with!
• Rooted in simple objective facts about inventory and sales that anyone can easily understand.

Disadvantages:

• Completely results-blind: no guarantee of impact, or sales acceleration, or return on investment as products may not be price-sensitive
• Unclear how to validate the choice of budget level, as there is no indication of appropriateness in how many products (or how deep) to discount
• Ultimately, the archetype of a PUSH decision which is manager-driven rather than customer-driven (PULL).

In short, 100% push decision, Excel-friendly. Nothing wrong with descriptive markdowns, but not that great either. Let them be our baseline model.

The first, almost obvious, unanswered question to be addressed at this point: how price-sensitive are the products? What acceleration can I expect when marking them down?

Enter the price elasticity concept used in predictive markdowns.

Approach #2: Predictive markdowns

What expected impact will a change in price, such as a markdown, have on sales?

The by-the-book answer is to use price elasticity: the related Wikipedia article explains this relatively well, so I will not repeat everything here again.

Prof. Caro and Gallien wrote up in 2010 an excellent freely available scientific paper detailing the approach of fast-fashion retailer Zara to elasticity-based markdowns, using regression-based techniques.

Discount products with high price elasticity.

What is price elasticity? Long story short, what % change in sales can I expect from a certain % change in price? For example, if my ‘price elasticity’ was -2, then a -10% change in price (negative sign: it’s a discount/price reduction) will be expected to yield a +20% increase in volume sold (-10% X -2 = +20%). And so on.

To calculate this coefficient, one traditional approach is to run the regression of historical sales volumes on historical prices, at a suitable level of granularity. For example: by category by store by week.

Based on elasticity, you can now calculate the expected return from applying any % markdown, or price change.

The benefit? From a price reduction of -10% (for example), with an elasticity of -2 you get a volume of sales increase of +20%. Therefore, if were selling 100 units at $10 each and making $1,000 revenues, now you will be selling 120 units (+20%) at $9 each (-10%). So your new revenues will be $1,080, a positive gain of +8%.

Walking through the math of all possible combinations with an elasticity of -2:

This chart shows in simple terms how you can choose the optimal markdown based on expected impact.

• If you want max revenues, then a -35% markdown would be optimal
• If you want max margin, then a -15% markdown would be optimal
• If you want a bit of both, then a markdown in-between -15% & -35% would be optimal, depending on the relative importance of revenues and margin targets.

The problem with this approach, is that while the t-value of the coefficients is reasonably good (the price does indeed influence sales), the R-squared of the model is not so good.

The resulting price elasticities converge to 0, when the volume of sales is high: the 1-dimension regression of volume versus price is very noisy.

Therefore, a method with some advantages and also some significant disadvantages, compared to a descriptive model.

Advantages:

• Relatively simple method, at least its simple version can be done partially in a spreadsheet like Microsoft Excel
• Provides guidance on which level of budget to invest in markdowns, and expected impact, based on the historical price elasticity
• Rooted in data about price sensitivity of demand, which increases the amount of customer-centricity of the process.

Disadvantages:

• Poor predictive power: the R-squared of these methods is relatively poor, so the model is not completely fit for purpose
• Predictions only feasible at intermediate levels of aggregation, e.g. store/category, as item-level elasticity is often impossible to determine
• Limited input signals, as price is not the only factor influencing sales, and product prices have inter-related effects e.g. due to cannibalisation.

In short, a 50–50% push decision, somewhat Excel-friendly. Way better than descriptive markdowns, but still limited in terms of ability to guide decisions.

The next unanswered question to be addressed at this point: how to account for more complex input signals? How to build a system that has a greater impact potential?

Enter machine learning and prescriptive markdowns.

Approach #3: Prescriptive markdowns

Since prices are not the only factor influencing sales, traditional predictive models based on price elasticity fail the test of accuracy.

What about throwing more data at the problem? The prescriptive approach requires the use of more advanced techniques, such as machine learning and automated optimization.

Discount the right products at the right time.

When adding data sets, traditional methods like regressions and time series show an initial uptick in performance, with the first few data sets, but then when adding even more, they suffer from problems such as collinearity and noise, that make them increasingly unfit for purpose.

Specifically, up to 7 variables, the regression model is better performing than machine learning.

When volume and variety of data increases, machine learning beats regression, enabling more granularity and accuracy.

In terms of markdowns, the % prediction error when using a machine learning approach, and therefore bigger data sets, is significantly lower.

This in turn unlocks significant financial gains, compared to the baseline model which is always the simple, descriptive approach.

Therefore, this approach provides the most advantages, but also some disadvantages compared to the other two approaches.

Advantages:

• Better performance: greater financial benefits, accuracy, breadth of input signals
• Self-learning system reducing operational risk, auto-adapting future recommendations in case of market deviation from expectations
• Granularity of recommendations at the SKU/store level with opportunity to integrate custom rules and human input as needed.

Disadvantages:

• Relatively complex set-up: requireing more data and specialist expertise to use more advanced techniques
• Very large data assets needed so to enable self-learning and rapid responsiveness to market conditions
• Ultimately, an advanced model that makes sense in the context of broader capabilities across other processes, not just markdowns.

In short, a 0% push, pure-pull decision; not Excel-friendly at all. Much better than both descriptive and prescriptive markdowns, even if suitable only to analytics-aware organizations.

Customer-centricity through data

Ultimately, any data is about the customer. Whether directly — such as data around customer age, sex, and so on — or indirectly — such as data around weather, competition, and so on.

Data inject the customer into daily management decisions.

The more data are appropriately used, the more customer-centric the management decisions. Prescriptive methods leverage machine learning to use the most data, in the best way possible.

Summary: the three approaches

You have now seen three different methods to using data: descriptive, predictive, prescriptive.

Evolving your approach to analytics requires diligence and a good dose of customer-centricity, but can ultimately be worth millions of $ in everyday impact.

Happy markdowns!