of 24

# Pricing Analytics: Estimating Demand Curves Without Price Elasticity

Most techniques used to created demand curves depend on the product’s price elasticity. But what if you don’t have or can’t obtain the price elasticity figures for a particular product? If you can make reasonable estimates of demand for a product at a high, median, and low price point, then you can still construct a reasonable estimate of the demand curve over the range of those prices. This presentation shows how to use Excel’s line fitting and Solver functionality to construct a demand curve without knowing the product’s price elasticity, and determine the optimal price for the product that maximizes profit margin.
Published on: Mar 4, 2016
Published in: Data & Analytics

#### Transcripts - Pricing Analytics: Estimating Demand Curves Without Price Elasticity

• 1. PRICING ANALYTICS Estimating Demand Curves Without Price Elasticity
• 2. Demand Curves Without Elasticity Data •Need to estimate three points on product’s demand curve: •Lowest price we’d consider charging, and demand at that price •Highest price we’d consider charging, and demand at that price •Median price, and demand at that price
• 3. Demand Curves Without Elasticity Data •Excel can fit basic quadratic demand equation to our three price/demand points: d = a(p)2 + b(p) + c •d: demand •p: price •a, b, and c: auto-calculated for us by Excel to give best fit
• 4. Demand Curves Without Elasticity Data •Quadratic curve adjusts to fit all three demand/price points •Reasonable assumption: curve that fits our three points approximates demand between the points •Excel’s Solver can be used against demand curve to determine optimal price
• 5. Example •We’ve just acquired a new product, and need to evaluate pricing ASAP •Could make high/median/low guesses about demand •Running small experiment instead: •3 CVS stores around Harvard Square •Shoppers randomly choose store •Stores have equivalent sales •Pricing: \$1.50, \$2.49, \$3.29 •Unit Sales: 93, 72, 18
• 6. Enter price/demand data points
• 7. Select data points by dragging over them with mouse Insert Scatter with only Markers chart
• 8. Right-click on any data point Choose Add Trendline…
• 9. Select Polynomial trend type Order is 2 since we’re fitting quadratic Display equation on chart Click Close button
• 10. d = -25.86(p)2 + 81.97(p) + 28.23 a b c
• 11. Starting guess for optimal price
• 12. Enter demand formula: =25.86*B6^2+81.97*B6+28.23
• 13. Enter variable cost of producing one unit
• 14. Enter profit formula: =B7*(B6-B8)
• 15. Start the Solver tool
• 16. Maximize Profit By changing Price
• 18. Optimum price Greater than/equal to Minimum price Click OK
• 20. Optimum price Less than/equal to Maximum price Click OK
• 21. Click Solve button
• 22. Optimum price: \$2.47