Systems Optimization Application - Revenue Optimization

The Retail Game Revenue Optimization Statistical Data as Information Fun with Optimization 1 The Situation Retailer with 2,000 units in inventory Full Retail Price $60 Discount Options 10%, 20% and 40% off Salvage Value of $25 (over 58% off) 15 week selling season No restocking 2 Revenue to date Projected revenues if you continue to Average weekly sell at this price sales at current and at this rate and price salvage what Sales that week remains Projected sales for the season if this rate continues Remaining Inventory How to Play 4 Retail.xls Histories of 15 items Item 1 Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Qty on hand 2000 1943 1845 1790 1749 1689 1650 1544 1489 1425 1382 1251 1139 1077 1046 966 Price 60 60 60 60 60 60 54 54 54 54 54 54 54 54 54 Sales 57 98 55 41 60 39 106 55 64 43 131 112 62 31 80 5 The Challenge Devise a discount strategy to earn maximum revenue What to do with the data in Retail.xls? Competition Select Compete Enter the Random Seed: Your first weeks sales at $60 Implement your strategy for 5 items Record your total relative difference 7 Discussion What are the trade-offs? What information do you need? How to estimate that information? How to use the estimates? 8 Estimating Sales at Discount Qty on Item Week 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 hand 2000 1925 1787 1678 1561 1420 1339 1035 817 669 370 80 0 0 0 0 Price Sales $ 60 75 $ 60 138 $ 60 109 $ 54 117 $ 54 141 $ 54 81 $ 48 304 $ 48 218 $ 48 148 $ 36 299 $ 36 290 $ 36 80 Average Full Price 10% 20% 40% Price $ 60 $ 54 $ 48 $ 36 Est. Avg. Sales 107.3 113.0 223.3 223 Why lower sales? 9 Refining the Estimate Qty on Item Week 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 hand 2000 1925 1787 1678 1561 1420 1339 1035 817 669 370 80 0 0 0 0 Price Sales $ 60 75 $ 60 138 $ 60 109 $ 54 117 $ 54 141 $ 54 81 $ 48 304 $ 48 218 $ 48 148 299 $ 36 290 $ 36 80 $ 36 X Average Full Price 10% 20% 40% Price $ 60 $ 54 $ 48 $ 36 Est. Avg. Sales 107.3 113.0 223.3 223 X (290 + 299)/2 = 294.5 Sales, not demand! 10 Sales Rates Item 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Mean Std Dev Full Price 58.33 107.67 59.33 61.17 92.50 114.14 67.43 53.00 73.71 67.29 100.44 64.11 65.56 61.44 62.33 107.33 75.99 20.74 Sales Rates 10% 20% 76.00 144.00 82.33 77.89 113.67 209.40 119.63 96.75 131.88 97.13 113.00 223.33 101.15 146.35 27.06 56.05 40% 264.25 188.50 196.67 164.00 175.00 294.50 213.82 52.87 11 Relative Sales Rates Item 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Mean Std Dev Full Price 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 - Ratio of Sales Rates 10% 20% 1.30 1.34 1.39 1.27 1.23 1.83 1.77 1.83 1.79 1.44 1.31 0.06 1.73 0.16 40% 2.63 2.94 3.00 2.67 2.81 2.81 0.16 12 What to do with Info? Given initial sales data, how many weeks to sell at each discount? 13 Variables How many weeks to sell at each price What about the order?! How much to salvage? 14 Constraints Total Weeks ≤ Weeks in the Season Total Sales and Salvage = Initial Inventory And? 15 A Model Initial Weekly Rate 90 Full Price Price $ Sales Lift 10% off 60.00 $ 1 54.00 1.31 20% off $ 48.00 1.73 40% off $ Salvage 36.00 2.81 $ 25.00 Salvage Weeks at 1.00 7.12 6.88 - Sales 90.00 837.45 1,072.55 - Total Salvage Revenue Revenues $ 5,400 $ 45,222 $ 51,482 $ - $ 15.00 2,000.00 Total Revenue - $ 102,105 16 How Many Prices? Ignore period 1 At most 2 prices (including salvage) Why? Two constraints - two basic variables 17 Two Constraints P = Average Sales Rate at Full Price x[price] = Weeks we sell at price S = Units we salvage max P*(60x[60] + 54*1.31x[54] + 48*1.73x[48] + 36*2.81x[36] ) + 25S s.t. x[60] + x[54] + x[48] + x[36] ≤ 15 s.t. P*(x[60] + 1.31x[54] + 1.73x[48] + 2.81x[36]) + S = 2000 s.t. x[60] ≥ 1 (This is a bound. Like x[54] ≥ 0) non-negativity 18 Strategies Average Sales at Full Price < 80/wk 20% off & Salvage 80-103/wk 10% off & 20% off 104-133/wk Full Price & 10% off >133/wk Full Price Where are the greatest errors? Why? 19 What about 40% off? Would it ever be wise to discount by 40% 20 Why Not 40% Off? Two Cases: Salvage and No Salvage No Salvage means the lift in sales doesn’t help We Salvage: Weekly Revenue Rate at 20% discount $48*1.73*P = 83.04*P Weekly Revenue Rate at 40% discount $36*2.81*P = 101.16*P Something is wrong with this reasoning! 21 Lost Salvage Value We Salvage: Revenue Rate over salvage at 20% discount ($48 - $25)*1.73*P = 39.79*P Revenue Rate over salvage at 40% discount ($36 - $25)*2.81*P = 30.91*P 40% is not competitive 22 Summary Use optimization to improve decision making even under uncertainty Even if it is only as good, it is automatic. Insights into strategy Ignore period 1 One Price and salvage Two prices and no salvage 23 A Parametric Model Rescale Sales volumes to units of Initial Weekly Sales (Rate) Initial Inventory becomes 2000/Rate Rescale Revenues by dividing by Initial Weekly Sales Only RHS of Inventory Constraint depends on Rate Use Sensitivity Analysis 24 Parametric Model R = Average Sales Rate at Full Price x[price] = Weeks we sell at price S = Units we salvage/R max 60x[60] + 54*1.31x[54] + 48*1.73x[48] + 36*2.81x[36] + 25S s.t. x[60] + x[54] + x[48] + x[36] ≤ 15 s.t. x[60] + 1.31x[54] + 1.73x[48] + 2.81x[36] + S = 2000/R s.t. x[60] ≥ 1 non-negativity 25 Parametric Study Initial Weekly Rate Initial Inv./Initial Weekly Rate 1.00 2000 Full Price 10% off 20% off 40% off Salvage Price $ 60.00 $ 54.00 $ 48.00 $36.00 $25.00 Sales 1 1.31 1.73 2.81 Weeks of sales at each price Total 1.00 0.62 13.38 - 15 Sales and Salvage in Units of Weeks at Full Price 1.00 0.81 23.19 - Total - Revenues/ Initial Weekly Rate $ 60 $ 44 $ 1,113 $ 25.00 Total - $ - $1,217 26 Using Sensitivity Analysis Strategy: 1 week at full price 14 weeks at 20% off Salvage what’s left What increase in sales rate will decrease the Initial Inventory by 1974.73? Constraints Cell Name $H$7 Weeks a $H$8 Sales i Final Value 15.00 2,000.00 Shadow Price 39.87 25.00 Constraint R.H. Side 15 2000 Allowable Increase 1139.279735 1E+30 Allowable Decrease 14 1974.733554 The basis (strategy) remains the same until we decrease the RHS of the Inventory Constraint by 1974.73 27 When to Switch Strategies Current Inventory 2000/Current Rate Find New Rate that decreases this by 1974.73 2000/Current Rate – 2000/New Rate = 1974.73 New Rate = 2000/(2000/Current – 1974.73) = 79.16 28 Parametric Study Initial Weekly Rate Initial Inv./Initial Weekly Rate 1.00 2000 Full Price 10% off 20% off 40% off Salvage Price $ 60.00 $ 54.00 $ 48.00 $36.00 $25.00 Sales 1 1.31 1.73 2.81 Weeks of sales at each price Total 1.00 0.62 13.38 - 15 Sales and Salvage in Units of Weeks at Full Price 1.00 0.81 23.19 - Total - Revenues/ Initial Weekly Rate $ 60 $ 44 $ 1,113 $ 25.00 Total - $ - $1,217 29