Customizable Spreadsheet Template: Price Cross-Elasticity of Multiple Products

This model uses results of pricing tests to estimate the impact of price changes on unit sales, revenue and profit margins for several related products. The results can help you optimize revenue or profit margin with limited pricing experiments.

The model helps address two key pricing questions.

• What prices for a product line with several products will maximize its revenue?
• What prices for a product line of several products will maximize its profits? (Advanced version only)

The products can include your own products, competing products, and substitutes that are not direct competitors.

The key to estimating sales from pricing tests is to estimate a constant price elasticity for each product, and constant price cross-elasticities for each pair of products from test results.

• Price elasticity is defined by the following relationship: If I raise (lower) the price by x% (where x is small), then unit sales rise (fall) by elasticity * x%.
• Price cross-elasticity between Product A and Product B is defined by the following relationship: If I raise (lower) the price of Product B by x% (where x is small), then the unit sales of Product A rise (fall) by corss-elasticity * x%.

This model assumes that price elasticity and cross-elasticity is constant over the range of prices being considered.

The key step in computing profit margins is to estimate costs as a function of unit sales levels for the products. Costs can be cost of goods or total costs or whatever you choose.

The model offers two types of price elasticity analysis.

• Simple (or constant) price elasticity. (This is the only price elasticity method in the Standard version.) This is the standard 'textbook' model of price elasticity, characterized by the relation sales units = constant x price^elasticity, where elasticity is a constant derived from pricing test data.
• Generalize (or non-constant) price elasticity. (Advanced versions offer both elasticity methods.) This model allows the elasticity to change as a function of price. For a given set of pricing test data, this method is better at identifying prices that maximize revenue or profits.

Pricing tests proceed through the following steps.

1. Select a large market where you sell the product at a standard price, and you know how many units you sell at that price (per time period).
2. Select some smaller test markets within the large market that have similar response to price changes. The test markets should be chosen to minimize "cross-talk" between test markets about prices, and commuting between test markets to buy at a lower price. For example, test markets consisting of large cities separated by a few hundred miles generally meet these criteria.
3. Offer the product at different prices in the different test markets. You probably want to include some prices above the current standard price, and some below. Run the test for sufficient time that customers react to the test prices as if they were permanent prices. Measure the unit sales in each test market.
4. Enter the following data into the price testing template.
• The price and sales units for the entire market that prevailed before the pricing test
• The number of units sold in each test market before the pricing test
• The price and number of units sold in each test market during the pricing test

From this data the model estimates the price sensitivity (price elasticity) of the total market. It then estimates the revenue and sales units (and profit margin if that is included in your model) for the entire market at different prices that you specify.

The model has several other key features.

• The model provides some standard statistical measures of goodness of fit of the model and the market test data.
• The model reports r^2 ('r squared') for each product and pair of products. These measure the fraction of the variance in sales units for a product that is accounted for the change in price of that product or another product, and by the model. r^2 takes on values between zero and one. A value over 0.8 means that the constant price elasticity method accounts for nearly all of the variation in unit sales with price. A value below 0.2 means that other factors besides a constant price elasticity are needed to explain well the variations in sales units across test markets.
• The model reports a 'standard error of elasticity' for each product and each pair of products. These measure the likely range of price elasticity (and cross-elasticity of two products) that are reasonably consistent with the data.
• The template contains comments on factors that help to ensure that market tests yield valid conclusions about pricing behavior.