of 47

# Pricing Analytics: Optimizing Sales Models

Sales and promotional discounts let retailers reach pools of customers that value the same product differently. Modeling the pool of potential buyers, and how it changes over time, lets you optimize how and when sales and discounts are applies. This presentation provides a hands-on demonstration of modeling the pool of potential buyers, and using Excel’s Solver tool to optimize revenue from that shopper pool by manipulating price.
Published on: Mar 4, 2016
Published in: Data & Analytics

#### Transcripts - Pricing Analytics: Optimizing Sales Models

• 1. PRICING ANALYTICS Optimizing Sales Models
• 2. Sales Rationale •Different shoppers value the same product differently •Pool of potential buyers changes over time for durable goods •When pool is mostly people with low valuation of product, charge a lower price (sale) to support total product sales •When pool is mostly people with high valuation of product, charge higher price to maximize profit
• 3. Example Sales Model •Target’s private-label yoga pants •Model 18 months of sales •Shoppers divided into 3 price pools: •\$24.99 (MSRP) •\$19.99 (Promotion) •\$14.99 (Sale) •Pool of 18,000 potential buyers •Equal number of buyers at each price level
• 4. Example Sales Model •1,000 new market entrants each month at each price point •Half of potential buyers actually purchase each month •Implication: Anyone who values pants at less than the current price is going to potentially purchase them
• 5. Create data table for the 3 price points & their associated price codes
• 6. Make the table a range named PriceCodes
• 7. Enter trial price codes for each of the 18 months in our model
• 8. Enter price lookup formula: =VLOOKUP(B7,PriceCodes,2) Accept formula
• 9. Select handle at bottom right of formula cell, then drag down
• 10. 3,000 shoppers will purchase at each price point
• 11. 3,000 shoppers won’t purchase at each price point
• 12. Buyers = Previous Nonbuyers + (Previous Buyers – Previous Sales) + ½ Previous Sales + ½ New Shoppers
• 13. Nonbuyers = ½ Previous Sales + ½ New Shoppers
• 14. Enter formula for MSRP buyers: =E7+(D7-J7)+0.5*J7+0.5*1000 Accept formula
• 15. Select handle at bottom right of formula cell, then drag down
• 16. Enter formula for MSRP non-buyers: =0.5*1000+0.5*J7 Accept formula
• 17. Select handle at bottom right of formula cell, then drag down
• 18. Enter formula for promotional buyers: =G7+(F7-K7)+0.5*K7+0.5*1000 Accept formula
• 19. Select handle at bottom right of formula cell, then drag down
• 20. Enter formula for promotional non-buyers: =0.5*1000+0.5*K7 Accept formula
• 21. Select handle at bottom right of formula cell, then drag down
• 22. Enter formula for sale buyers: =I7+(H7-L7)+0.5*L7+0.5*1000 Accept formula
• 23. Select handle at bottom right of formula cell, then drag down
• 24. Enter formula for sale non-buyers: =0.5*1000+0.5*L7 Accept formula
• 25. Select handle at bottom right of formula cell, then drag down
• 26. Enter cell reference for MSRP unit sales: =D7 Accept reference
• 27. Select handle at bottom right of formula cell, then drag down
• 28. Enter conditional for promo unit sales: =IF(C7 <= \$F\$4, F7, 0) Accept formula
• 29. Select handle at bottom right of formula cell, then drag down
• 30. Enter conditional for sales unit sales: =IF(C7 = \$F\$2, H7, 0) Accept formula
• 31. Select handle at bottom right of formula cell, then drag down
• 32. Enter formula to calculate monthly revenue: =SUM(J7:L7)*C7 Accept formula
• 33. Select handle at bottom right of formula cell, then drag down
• 34. Enter formula to calculate total revenue: =SUM(M7:M24) Accept formula
• 35. Launch Excel’s Solver tool
• 36. Maximize Total Revenue Monthly Prices Choose Evolutionary solving method