  # 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, MBAMicrosoft 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: Our gross profits before and after the price change are equal to our price minus our product cost, multiplied by our unit sales volume: 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: Keep in mind that this formula doesn't address other costs that might be affected by your planned price change.

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