I cannot recommend Erik Haugom’s Essentials of Pricing Analytics enough. Each new chapter poses great intellectual puzzles. And I’m a big fan of those. :money_mouth_face:
Anyways, the topic of Chapter 09 is markdown optimization, where we are trying to find the timing and magnitude of price reduction to maximize revenues. We have all seen this, being active consumers in FMCG market. But markdown optimization applies to high-tech products also, since each product has it’s own product life cycle.
In this chapter, author introduced deterministic markdown management in Excel.
Basically, we have 4 periods, where we start with the highest price and gradually bring the price down in order to maximize the revenues.
For each of the period, we have different demand model:1
- Demand of Period 1: 200 + (-2 * price)
- Demand of Period 2: 150 + (-2 * price)
- Demand of Period 3: 100 + (-2 * price)
- Demand of Period 4: 50 + (-4 * price)
Our initial stock is 400 units, and our salvage price per unit for any unsold unit is 10 USD.
So, we will try to maximize the revenues, given the following conditions: * Each price in the next period should be equal to or lower than the price in the period before. * Price of the last period should be greater than or equal to salvage value (10 USD). * When we sum the units we sold through different periods and the unsold units, the sum should equal to or less than our initial stock of 400. * I don’t see the purpose of this, since the quantity of unsold units is initial stock, minus the stock that we sold through 4 periods.
So, let’s say we start with the scenario that has fixed price through all periods of 10 USD. This is what we get:
| Period | Price | Demand | Revenue | Discount | |
|---|---|---|---|---|---|
| 0 | Period-1 | 10 | 180 | 1800 | 0 |
| 1 | Period-2 | 10 | 130 | 1300 | 0 |
| 2 | Period-3 | 10 | 80 | 800 | 0 |
| 3 | Period-4 | 10 | 10 | 100 | 0 |
| 4 | Unsold | 10 | 0 | 0 | 0 |
| Sum: | 400 | 4000 | 0 |
This problem can be solved through Python, by using scipy.optimize. After defining the function that is going to be plugged into the scipy.optimize, we are getting the following solution:
| Period | Price | Demand | Revenue | Discount | |
|---|---|---|---|---|---|
| 0 | Period-1 | 54.9998 | 90.0003 | 4950 | 0 |
| 1 | Period-2 | 42.5 | 65 | 2762.5 | 0.227271 |
| 2 | Period-3 | 30 | 40 | 1200 | 0.454544 |
| 3 | Period-4 | 11.25 | 5.00002 | 56.2502 | 0.795454 |
| 4 | Unsold | 10 | 200 | 2000 | 0.818181 |
Which is identical to the author’s solution, and gives us total revenue of 10.968,75 USD. To my surprise, I didn’t have to define any constraints or bounds for this solution. But, for the testing of (and learning about) scipy.optimize, I did, and the solution is the same.
Now, what was troubling to me is that the author said that with the price of 42.50 USD through all periods (static price), the revenue should be 10.337,50 USD. But that is not the case. And, to top if all off, with that price, the demand/revenue for Period 04 is (mathematically) negative:
| Period | Price | Demand | Revenue | Discount | |
|---|---|---|---|---|---|
| 0 | Period-1 | 42.5 | 115 | 4887.5 | 0 |
| 1 | Period-2 | 42.5 | 65 | 2762.5 | 0 |
| 2 | Period-3 | 42.5 | 15 | 637.5 | 0 |
| 3 | Period-4 | 42.5 | -120 | -5100 | 0 |
| 4 | Unsold | 10 | 325 | 3250 | 0.764706 |
With this price, total expected revenues are 6.437,50 USD.
The static price that will give us maximum revenues should be 30 USD:
| Period | Price | Demand | Revenue | Discount | |
|---|---|---|---|---|---|
| 0 | Period-1 | 30 | 140 | 4200 | 0 |
| 1 | Period-2 | 30 | 90 | 2700 | 0 |
| 2 | Period-3 | 30 | 40 | 1200 | 0 |
| 3 | Period-4 | 30 | -70 | -2100 | 0 |
| 4 | Unsold | 10 | 200 | 2000 | 0.666667 |
… where the total revenues to be expected are 8.000,00 USD (but the problem with negative demand does not go away). The relationship between static price and total revenues confirms that the optimal static price is 30 USD:
If demand cannot be negative, then the maximum price should be 12.50 USD, since that is the price that will give the demand of zero on Period 4:
| Period | Price | Demand | Revenue | Discount | |
|---|---|---|---|---|---|
| 0 | Period-1 | 12.5 | 175 | 2187.5 | 0 |
| 1 | Period-2 | 12.5 | 125 | 1562.5 | 0 |
| 2 | Period-3 | 12.5 | 75 | 937.5 | 0 |
| 3 | Period-4 | 12.5 | 0 | 0 | 0 |
| 4 | Unsold | 10 | 25 | 250 | 0.2 |
So, I don’t know how the author concluded that the optimum static price is 42.50 USD.
Nevertheless, the point remains: by markdown optimization, revenues could be improved immensely. If we are to compare the revenues given by the static price of 30 USD and the revenues given by optimal markdown optimization, the revenues are improved by 37.11%.2
Footnotes
I hope that author will say something about how to find data for models in other periods. Should I model the relationship based on the historical data? How to define periods? As I see it, the model then should have 3 features: price, demand, days to expiry. And then, the days to expiry should be segmented into \(n\) periods (clustering), and only then could I get those linear (or other appropriate) price-response functions.↩︎
Author’s conclusion, based on comparison of revenues of the static price model (price = 42.50 USD) and the revenues given by optimal markdown optimization, the markdown optimization improves the revenues by 6%.↩︎