PRICING ANALYTICS
Optimizing Price
Optimizing Price
•Best price – price that yields max profits, not necessarily max unit sales
•Excel’s Solver tool can be...
Excel Solver
Sets value to be maximized or minimized Variables that can be adjusted to optimize objective cells
Restrict...
Excel Solver
•Solver tries all reasonable solutions that fit the specified model
•Chooses optimal solution – values for ...
Pricing Optimization Example
•Find best price for ink jet printer
•Current price: $75
•Demand at current price: 5,000 p...
Enter price and demand values
Select data cells by dragging over with the mouse Insert Scatter with only Markers chart
Swap axis data to fix slope of demand curve
Right-click data point Choose Add Trendline…
Select Linear Trendline Check option to Display Equation on chart
Click Close button
Demand Curve Formula:
d = 15,000 – 133.33 * p
Enter per-unit manufacturing cost
Enter initial guess for optimal price
Enter demand formula:
=15000-133.3*B7 Accept formula
Total Profit = (Price – Unit Cost) * Demand Accept formula
Enter profit formula:
=B8*(B7-B5)
Start Solver
Maximize
Total Profit
By changing price
Select GRG Nonlinear solving method
Click Solve button
Optimal price per printer: $86
Total profit: $95,415.98
Complementary (Tie-In) Products
Product
Tie-In
DVD player
DVDs
Razor
Blades
Cell phone
Car charger
Flashlight
Ba...
Pricing Optimization w/Tie-In Product
•Including profits from tie-in products lowers optimum price for original product
...
Enter cost to manufacture printer
Ink cartridges we’ll sell per printer
Profit per ink cartridge sold
Initial guess for optimal price
Enter demand formula:
=15000-133.3*B7
Printer Profits = [(Price - Unit Cost) * Demand]
Printer Profits = [(Price - Unit Cost) * Demand] +
(Demand * Cartridges per Printer * Profit per Cartridge)
Enter updated total profit formula: =B5*(B4-B1)+(B5*B2*B3)
Start Solver
Maximize
Total Profit By changing price
Click Solve
Best price for our printers is a $9 loss per sale!!
Total profit: $525,112.42
Pricing Analytics: Optimizing Price
of 34

Pricing Analytics: Optimizing Price

The “best” price for a product or service is one that maximizes profits, not necessarily the price that sells the most units. This presentation uses real-world examples to explore how Excel’s Solver functionality can be used to calculate the optimal price for any product or service.
Published on: Mar 4, 2016
Published in: Data & Analytics      
Source: www.slideshare.net


Transcripts - Pricing Analytics: Optimizing Price

  • 1. PRICING ANALYTICS Optimizing Price
  • 2. Optimizing Price •Best price – price that yields max profits, not necessarily max unit sales •Excel’s Solver tool can be used to construct useful pricing models
  • 3. Excel Solver Sets value to be maximized or minimized Variables that can be adjusted to optimize objective cells Restrictions on how Solver can change variable cells
  • 4. Excel Solver •Solver tries all reasonable solutions that fit the specified model •Chooses optimal solution – values for variable cells that produce best value for target cell
  • 5. Pricing Optimization Example •Find best price for ink jet printer •Current price: $75 •Demand at current price: 5,000 printers •Cost to produce one printer: $59 •Price elasticity: 2.0 •Linear demand curve •Two known points on demand curve: •(p=$75, d=5000) •(p=$75.75, d=4900)
  • 6. Enter price and demand values
  • 7. Select data cells by dragging over with the mouse Insert Scatter with only Markers chart
  • 8. Swap axis data to fix slope of demand curve
  • 9. Right-click data point Choose Add Trendline…
  • 10. Select Linear Trendline Check option to Display Equation on chart Click Close button
  • 11. Demand Curve Formula: d = 15,000 – 133.33 * p
  • 12. Enter per-unit manufacturing cost
  • 13. Enter initial guess for optimal price
  • 14. Enter demand formula: =15000-133.3*B7 Accept formula
  • 15. Total Profit = (Price – Unit Cost) * Demand Accept formula Enter profit formula: =B8*(B7-B5)
  • 16. Start Solver
  • 17. Maximize Total Profit By changing price
  • 18. Select GRG Nonlinear solving method
  • 19. Click Solve button
  • 20. Optimal price per printer: $86 Total profit: $95,415.98
  • 21. Complementary (Tie-In) Products Product Tie-In DVD player DVDs Razor Blades Cell phone Car charger Flashlight Batteries Inkjet printer Ink cartridges
  • 22. Pricing Optimization w/Tie-In Product •Including profits from tie-in products lowers optimum price for original product •Assumptions for our example: •Average printer lifetime: 3 years •Ink cartridge lifetime: 6 months •Ink cartridges sold per printer: 6 (2/yr * 3 yrs) •Ink cartridges must be priced at $34 •Profit per ink cartridge sold is $12
  • 23. Enter cost to manufacture printer
  • 24. Ink cartridges we’ll sell per printer Profit per ink cartridge sold
  • 25. Initial guess for optimal price
  • 26. Enter demand formula: =15000-133.3*B7
  • 27. Printer Profits = [(Price - Unit Cost) * Demand]
  • 28. Printer Profits = [(Price - Unit Cost) * Demand] + (Demand * Cartridges per Printer * Profit per Cartridge)
  • 29. Enter updated total profit formula: =B5*(B4-B1)+(B5*B2*B3)
  • 30. Start Solver
  • 31. Maximize Total Profit By changing price Click Solve
  • 32. Best price for our printers is a $9 loss per sale!! Total profit: $525,112.42

Related Documents