For business users of Microsoft Excel Free guides and templates

Charting Ideas

Derivation of the Formula For Planning Price Changes

Here's how I created the formula that calculates the breakeven sales volume for a prospective change in product prices.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

The article, Should You Raise Prices? Or Should You Lower Them?, introduces the Price-Change formula. Before you use the formula, it's a good idea to assure yourself that it's accurate. So here's how it was derived...

Here are the variables used in the formula:

The arguments for the formulas

Our gross profits before and after the price change are equal to our price minus our product cost, multiplied by our unit sales volume:

The gross profits before and after price changes

We're looking for the gross-profit stay-even point, the point where gross profits after the price change equal gross profits before the price change. That is, we want:

We can rearrange this formula to arrive at:

We can add zero (P0 - P0) to the bottom-left and zero (V0 - V0) to the top right, giving us:

Now we can multiply the left side by 1, in the form of (1/P0) / (1/P0). And we can simplify the right side, to return:

The top-left term is our current profit margin. The bottom-left terms are our current profit margin minus our price change as a percentage of the current price. The right-hand term is 1 + our percentage change in volume after the price change. Moving the 1 to the other side of the equation, and simplifying, gives us the Stay-Even Formula for price changes:

The Stay-Even formula for price changes

Keep in mind that this formula doesn't address other costs that might be affected by your planned price change.

Should You Raise Prices? Should You Lower Them? These Excel Charts Can Help You Answer Those Questions.

Free Excel Dashboards

Charley's SwipeFile charts