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

#### 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