Pricing Sensitivity Model
Analyzes how changes in price affect demand volume, revenue, and profit. Calculates price elasticity, identifies the optimal price range for maximum profit, and defines risk thresholds where pricing changes become destructive.
Risk - Pricing Sensitivity Model.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Price changes are made without understanding demand impact
- The profit-maximizing price point is unknown
- No data on how elastic or inelastic demand is for the product
- Risk of revenue decline from overpricing is not quantified
- Competitive pricing pressure effects are unmodeled
Who This Is For
- Product managers setting or adjusting pricing strategies
- Revenue teams evaluating discount and promotion impacts
- CFOs assessing the profit impact of proposed price changes
- Market analysts studying competitive pricing dynamics
Inputs
- $Current Price
- #Current Monthly Volume
- $Unit Variable Cost
- $Price Change Increment to Test
- #Estimated Elasticity Coefficient
Outputs
- Revenue curve across price range
- Profit curve across price range
- Optimal price for maximum profit
- Safe price range (within 5% of max profit)
- Risk thresholds: prices where profit turns negative
How Calculations Work
The model uses the price elasticity of demand to estimate volume at each price point: new volume = current volume * (new price / current price) ^ (-elasticity). Revenue is price times volume. Profit is revenue minus total costs (variable cost times volume plus fixed costs). Curves are plotted across a range of prices. The optimal price maximizes the profit function. Risk thresholds mark where the profit curve crosses zero.
Example Use Case
Scenario: Current price: $79. Volume: 1,200 units/month. Variable cost: $31/unit. Fixed costs: $22,000/month. Elasticity: 1.8. Testing $5 increments from $49 to $119.
Result: Optimal price: $72 (profit: $30,480/month, volume: 1,384). Current price of $79 yields $27,600/month profit. Safe range: $65-$82. Below $52 or above $108, profit turns negative. A $10 price increase to $89 drops volume to 988 and profit to $23,280.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How do I estimate price elasticity?
If you have historical data on price changes and resulting volume changes, elasticity = (% change in volume) / (% change in price). Without data, use industry benchmarks: necessities are 0.2-0.8, discretionary products 1.0-2.5, luxury goods 2.0-4.0.
Does this model competitive responses?
The base model assumes competitors hold prices constant. For competitive scenarios, adjust the elasticity coefficient upward (competitors matching your price cut reduces your volume gain).
Can I model different customer segments?
Run the model separately for each segment with its own elasticity and volume. This is useful when segments have different price sensitivities.
What if my product has low elasticity?
Low elasticity (below 1.0) means demand is inelastic. Price increases generate more revenue because volume barely changes. The model will show the optimal price is higher than the current price.
How accurate are the projections?
Accuracy depends on the elasticity estimate. The model is directionally reliable for understanding trade-offs but should not replace A/B pricing tests for critical decisions. Use it for scenario planning, not exact predictions.
Download Pricing Sensitivity Model
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.