pricing analytics: estimating demand curves without price elasticity
DESCRIPTION
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.TRANSCRIPT
PRICING ANALYTICS Estimating Demand Curves Without Price Elasticity
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
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
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
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
Enter price/demand data points
Select data points by dragging over them
with mouse
Insert Scatter with only Markers chart
Right-click on any data point
Choose Add Trendline…
Select Polynomial trend type
Order is 2 since we’re fitting quadratic
Display equation on chart
Click Close button
d = -25.86(p)2 + 81.97(p) + 28.23
a b c
Starting guess for optimal price
Enter demand formula: =25.86*B6^2+81.97*B6+28.23
Enter variable cost of producing one unit
Enter profit formula: =B7*(B6-B8)
Start the Solver tool
Maximize
Profit
By changing Price
Add constraint
Optimum price
Greater than/equal to
Minimum price
Click OK
Add constraint
Optimum price
Less than/equal to
Maximum price
Click OK
Click Solve button
Optimum price: $2.47