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

In the early 1980s I was the CFO of a company owned by a man whose first instinct was to cut prices.

When business was good, he lowered prices to capture market share. And when business was bad, he lowered prices to improve sales. Because he was starting to price us out of business I developed a formula to help him improve his pricing decisions.

These days, I know a marketing manager who struggles to market a high-priced consumable product that has a high profit margin. Consumers carefully ration their use of her product because it’s so expensive. In this instance, a price cut probably would increase the use of her product significantly, and thus improve her company’s profits.

My pricing formula could help her company as well, because it works with both price cuts and price increases.

The Excel-generated figures on this page are graphical tools that make it easy to apply the formula. The figure above shows the Price-Increase version designed to be printed on a full page. There’s also a Price-Decrease version, of course. Both versions of this figure below are designed for the web.

More generally, these figures illustrate a little-used approach to charting, which I call a calc plot. Calc plots are charts designed to allow you to perform calculations using only the chart and (most often) your finger tip.

I’ve used Excel-generated calc plots for several types of calculations that involve decision-making. I’ve used them to set prices for multi-million dollar equipment, to evaluate real estate loans, to manage a firm’s capital structure, and for other decisions that involve trade-offs between various inputs and outputs.

Calc plots are superior to numeric calculations for brainstorming such decisions, because you can see the results of all possible calculations easily. With a pricing calc plot, for example, you can balance the effects of any number of changes in prices, margins, and sales volume. Because all possible results are in front of you, you quickly can get a sense of how these variables interact.

In contrast, using mere numeric calculations gives you only one fact at a time. Using such calculations is like trying to view a fine painting through an electron microscope.

To see the pricing calc plot at work, suppose you sell 100 fishing lures per month at $1 each. Variable costs are 40 cents each, giving you a profit of $60 and a profit margin of 60%.

To increase profits, should you raise or lower prices?

Raise Prices?

If you raise prices by 20%, and your unit volume stays the same, your sales go up to $120. Because your variable costs still are only $40, your profits rise to $80.

But unit sales could fall because of your price increase.

To learn how much your unit sales would have to fall before your profits in dollars would drop below your original $60 profit, use this Stay-Even Formula for price changes:

Here, GP% is your initial gross profit margin as a percentage, and the Greek letter delta means change.

This formula tells you the percentage change in unit sales that will be needed to maintain your current amount of gross profits after you change prices.

(See Derivation of the Formula For Planning Price Changes to learn how I developed this formula.)

Because you’re raising prices in this example, you add the price change percentage, which gives you:

60%
—————– – 1 = -25%
(60% + 20%)

That is, after your price increase, your unit sales must fall by 25% for your gross profit to remain unchanged. With that fall, you sell 75 units that earn a profit of $.80 each, returning your same gross profit of $60.

Therefore, your profits rise if your price increase causes sales to fall by less than 25%.

The figure above shows the stay-even values for a range of possible price increases and initial profit margins. It also illustrates the result of this specific calculation.

Here’s how you use the chart after you print it: Find your current gross profit margin on the X axis. Run your finger tip up the chart until it touches the curve that represents the price increase you’re considering. Then move your finger horizontally to the Y axis. The percentage you find is your worst-case decrease in unit sales. If you think that your price increase will cause unit sales to fall by a larger percentage, then you probably shouldn’t raise prices.

Because these calc-plot figures are generated by Excel, you can modify them to evaluate any range of profit margins and price increases you want. Please keep in mind, however, that the pricing formula addresses only gross profits, or contribution margins, depending on the numbers you use. Price and volume changes can affect other costs that the formula and charts don’t address.

Lower Prices?

What if you cut prices by 20%?

If your unit volume stayed the same, your sales would drop to $80 and your gross profits to $40. So if you want your price cut to improve profits, you’ll need to increase unit sales by at least 50%, as this formula shows:

60%
—————- – 1 = 50%
(60% – 20%)

In other words, for your price cut to succeed, you would need to sell at least 150 units instead of the 100 units per month you currently are selling.

Specifically, if you sell 150 units at a profit of $.40 per unit, you earn $60 as you did before the price cut. If you can do better than a 50% increase in sales, then perhaps you should lower your price.

This figure illustrates the same formula. As in the previous figure, the higher in the chart, the higher your profits. That is, the more that your price cut increases sales, the better.

By experimenting with the stay-even pricing formula and the charts, you quickly can see whether price increases or decreases may be best for your firm.

Set Up the Data for the Charts

The workbook that produced these charts has three worksheets: Report, Data, and Legend.

Here’s the top of the Data sheet.

Cell A3 contains the value for the lowest profit margin in the range to include in the analysis. The formula for cell A4 is:

A4:  =A3+0.01

Format cell A4 as shown and then copy it to the range A5:A53.

Cell B2 contains the value for the lowest potential price increase. Cell C2 has a value or formula for the next-highest increase. I used:

C2:  =B2+0.05

Format cell C2 and then copy it to the range D2:L2.

Cell B3 calculates the first stay-even value. Its formula is:

B3:  =IF($A3<=-B$2,NA(),IF($A3/($A3+B$2)-1>5,NA(),$A3/($A3+B$2)-1))

This formula is rather messy; but its idea is rather simple. It tests for several possible error conditions, and then calculates the stay-even value for a price increase.

Format the cell and then copy it to the range B3:L53.

Here’s the top of the section that contains the data for the second chart.

These cells contain the formulas shown:

B56:  =-B$2

Format it and copy it to the range C56:L56.

A57:  =A3

Format and copy it to the range A58:A107.

B57:  =IF($A57<=-B$56,NA(),
IF($A57/($A57+B$56)-1>5,NA(),$A57/($A57+B$56)-1))

Enter this formula in one line in your formula bar. Format it as shown and then copy it to the range B57:L107.

Set Up Your Legend Sheet

Because the legend offered by Excel’s charts isn’t very flexible, it’s sometimes easier to create our own.

The numbers in rows 2 and 4 contain formulas linked to the equivalent numbers in the Data sheet. For example, here are the formulas for column C:

C2:  =Data!B2
C4:  =Data!C56

The colored lines for each value are merely cell shading in the narrow columns shown.

I used Excel’s Camera tool to return a picture of this legend to the report. I’ll explain this tool below. For now, assign range names to the ranges we’ll use. To assign the name for row 2, select the range $B$2:$K$2 and then press Ctrl+Alt+F3, enter the name LegendInc, and then press OK. (You also can choose Ctrl+F3 and choose New to display the same dialog that Ctrl+Alt+F3 displays.)

Follow a similar procedure to assign the name LegendDec (for Legend Decrease) to the range $B$4:$K$4.

Set Up the Figure

You have to ways to enter the black arrow in cell D23. If you have a recent version of Excel you can insert this symbol. Otherwise, you have to use a special font. Let’s use the second method.

First select cell D23. Type in the letter “p” where you want the arrow to be. Select the letter in your formula bar. Assign the Wingdings 3 font to that letter, and then press Enter. After you do so, you’ll still see “p” in your formula bar, but you should see the arrow in your spreadsheet cell.

Set up your figure description

The charts above include text that describes how to use the charts. If you do want to include the text, draw a text box in the area shown, turn off its borders and area, enter the text, and then format the text with the font you want.

To draw the text box, choose Insert, Illustrations, Shapes, and then click on the Tool Box icon, which is the first icon in the Basic Shapes section.

Now enter the text you want

To format the text box, click its edge, choose Ctrl+1, and then, in the Fill tab, choose No Fill. In the Line Color tab, choose No Line. Close the dialog. Choose Home, Font to set the font you want.

The three numbers — 1, 2, and 3 –  in the plot area also are text boxes. To make them part of the chart, select the chart, click on the text-box icon, then draw a text box to contain a number within the chart.

Set up your legend

You could use two methods to set up the legend images in the two previous chart figures. Both methods produce a Camera tool object, which is a live picture of a specified range in your spreadsheet.

Assuming you’ve not used the Camera tool before, we’ll use the quickest method. Because you named the legend area as I described above, press the F5 option key to launch the GoTo dialog; choose LegendInc from the list, and then press OK. After you do so, Excel selects the legend area.

Press Ctrl+C to copy the Legend range. Select cell D5 in your Report sheet and then choose Home, Clipboard, Paste, As Picture, Paste Picture Link.

After you paste the picture link (also known as a Camera object), position it as shown in the figure above.

Notice that the first figure in this article has no legend. Instead, every other curve is labeled with the price-change percentage that the curve represents. We have the flexibility to use this approach when we have the room that a full printed page represents. But with smaller figures, we usually must use legends.

Set up your chart

To create your Raise Prices? chart, select the range A2:L53 in the Data sheet, then set up a line chart, with no legend. Format the Chart Area to have no border and no Area color.

The default chart displays the X-axis labels at the bottom of the chart rather than at the top near the 0 line. It also displays the labels in a vertical rather than horizontal orientation. To correct these problems, click on the the X-axis labels; press Ctrl+1; and then:

• In Classic Excel, in the Patterns tab, specify that you want Tick Mark Labels Low.In the Alignment tab, click the Degrees spinner arrow once upward and then once downward. Doing so removes the Automatic setting for the orientation. Then choose OK.
• In New Excel, in the Axis Options tab, choose Low in the Axis Labels drop-down list box.In the Alignment tab, click the Custom Angle spinner up once and then down once. Doing so applies the zero-degree setting that Excel won’t let you apply directly.

Notice that I assigned a black color to every other line in the charts. Doing so reduces the clutter that many different colors bring. To change the format, select a line, and then press Ctrl+1. I’m sure you’ll be able to figure out how to use the dialog to apply any format you want.

To apply that format to another line in the same chart, select a line and then press Ctrl+Y or Alt+Enter.

Normally, I don’t use gradient fills, because they tend to distract from the information being presented. Here, however, I used a slight gradient fill to provide a hint that up is good and down is bad. To do so, I made the top of the plot area a slightly lighter shade than the bottom.

To set the two-color background, select the Plot Area; press Ctrl+1, and then:

• In Classic Excel, in the Area section of the dialog, choose Fill Effects. In the Gradient tab, choose two different colors for the area. In the Shading Styles section, choose a Horizontal option.
• In New Excel, in the Fill tab, choose Gradient fill. Under Gradient stops, choose Stop 1, and then choose the first color. Choose Stop 2, and then choose the second color.

In both versions of Excel, make your gradient colors very light so that you can see the lines and gridlines easily.

To format the gridlines, select a gridline than choose Ctrl+1. Make the Major gridlines dark gray and the minor gridlines light gray. Make both grays as light as you can while still making them visible enough to be useful.

To create the Lower Prices? figure, use the same techniques as you did for the first figure.