of 49

# Pricing Analytics: Creating Linear & Power Demand Curves

An introduction to the two most common types of demand curves (linear and power), which can be used to estimate the price for a product or service that maximizes profit margins. Includes hands-on real-world examples using Excel.
Published on: Mar 4, 2016
Published in: Data & Analytics

#### Transcripts - Pricing Analytics: Creating Linear & Power Demand Curves

• 1. PRICING ANALYTICS Creating Linear & Power Demand Curves
• 2. Demand Curve 0 200 400 600 800 1000 1200 1400 \$0 \$5 \$10 \$15 \$20 \$25 Demand Curve describing how many units of product the market demands for every possible price point
• 3. Demand Curves •Used to estimate price that should be charged for maximum profits •The best price for a product maximizes margins – not unit sales 12 units * \$5 = \$60 50 units * \$1 = \$50
• 4. Estimating Best Price •Need two things to estimate best price: •Variable cost to produce one unit of product •Product’s demand curve
• 5. Estimating Best Price •COG: variable cost to produce one unit of product •p: price we charge customers for 1 unit of product •D(p): customer demand, in units of product, at price p •Profit margin formula: Margin = (p – COG) * D(p) Profit margin per unit Demand for product
• 6. Demand Curves •Demand curves are subject to frequent change •Affected by: •Competitive pressures •Customer sentiment •Macroeconomic factors
• 7. Price Elasticity •The amount demand decreases if prices increased by 1% •Product is price elastic if its elasticity > 1 •Decreasing price of product will increase revenue •Product is price inelastic if its elasticity < 1 •Decreasing price of product will decrease revenue
• 8. Price Elasticity •Examples of price elasticity values in Boston MSA: •Good pricing decisions require understanding of products’ price elasticity Product/Service Elasticity Salt 0.09 Coffee 0.20 Beer 0.95 LCD monitors 1.73 Restaurant meals 2.90 Travel to Ireland 5.27
• 9. Demand Curves •Two most popular types of demand curves: •Linear demand curves •Power demand curves
• 10. Linear Demand Curves •Straight-line relationship between price and demand D = a – bp •D: units of product demanded by customers •p: per-unit price •a and b: adjust curve to fit product’s price elasticity •Excel can auto-calculate a and b for us
• 11. Power Demand Curves •Arc that shows relationship between price and demand, when product’s price elasticity isn’t affected by product’s price D = apb •D: units of product demanded by customers •p: per-unit price •a and b: adjust curve to fit product’s price elasticity •b is additive inverse of price elasticity (ex: b = -2 if elasticity = 2) •Excel can auto-calculate a for us
• 12. Which Curve to Use? •Price elasticity properties tell us which curve is appropriate •Linear demand curve: if product’s price elasticity changes as price changes •Power demand curve: if product’s price elasticity remains constant as price changes
• 13. Constructing Linear Demand Curves •Scenario: •We’re selling polo shirts for Ralph Lauren •Current price per unit p = \$90 •Current demand D = 1,000 shirts •Price elasticity of product: 2.0 •We need two points to construct our line: •We already know (\$90, 1000) is on the curve •Increase price by 1% (\$0.90), demand will decrease by 2% (20 shirts) •Calculated point on curve: (\$90.90, 980)
• 14. Enter our data points
• 15. Select data points by dragging the mouse over them
• 16. Insert “Scatter with only Markers” chart
• 17. Incorrect upwards- sloping demand curve
• 18. Switch Row/Column to fix slope of line
• 19. Correct slope for demand curve
• 20. Right-click a data point, and choose “Add Trendline…”
• 21. Choose “Linear” type Check “Display Equation on chart” Click “Close”
• 22. Demand curve Equation of demand curve
• 23. Value of a Value of b
• 24. Constructing Linear Demand Curves •Linear demand curve equation for this example: D = 3000 – 22.2p •Implication: Every \$0.90 increase in shirt price is going to cost demand for ~22 shirts •Error rate for linear demand curves increases with distance from current price point •Pretty good approximation +/- 5% of current price
• 25. Constructing Power Demand Curves •Use power demand curves when product’s price elasticity doesn’t change when price changes •Same scenario: •We’re selling polo shirts for Ralph Lauren •Current price per unit p = \$90 •Current demand D = 1,000 shirts •Price elasticity of product: 2.0 •Price elasticity doesn’t change when price changes •Excel’s Goal Seek function calculates value of a for us
• 26. Starting guess for value of a
• 27. Current per-unit price
• 28. Enter Excel formula for demand: =B1*B2^-2 Power Demand Curve Formula: D = apb
• 29. Accept formula
• 30. Demand at this price should be 1,000 units – our guess for a was way off
• 31. Goal Seek will change this value… …until our formula yields the correct value here
• 32. Start Goal Seek
• 33. We want to set the cell containing our customer demand…
• 34. …to our known value of 1000…
• 35. …by changing the value of a Click “OK” to run Goal Seek
• 36. Goal Seek sets correct value for a Click “OK” to exit Goal Seek
• 37. Enter prices in increments of \$10 between \$50 and \$140
• 38. Enter Excel power demand curve formula using correct value for a: =\$B\$1*C6^-2
• 39. Right-click cell containing formula, and choose “Copy”
• 40. Select other “Demand” cells, right-click, and choose “Paste as Formula”
• 41. Verify formula is correct by checking demand/price value we know
• 42. Select data cells from table Insert “Scatter with only Markers” chart
• 43. Chart of points in demand curve
• 44. Right-click any data point, then choose “Add Trendline…”
• 45. Select “Power” radio button Click “Close”
• 46. Power demand curve
• 47. Constructing Power Demand Curves •Value of a determined to be 8,100,000 D = 8,100,000p-2 •Price elasticity remains constant for every price on the demand curve