Hands on: Use Excel to balance profits

If a business wants to increase sales by reducing prices it can only do it profitably if costs are reduced.

A reader has asked how to plot this relationship using Excel. The analysis of price sensitivity can be a complex subject but I can show a simple example.

Screen 1 shows a fictitious retailer’s income statement for the previous year.

First choose Options on the Tools menu and then the Calculation tab and check the box ‘Accept labels in formulas’.

Screen 2 shows the calculations needed to prepare a price sensitivity analysis chart. In cell B39 enter:

=Operating Expenses+Operating Income

and Excel will enter the values from the income statement above. Similarly, if you recreate this example, in cell B40 you can enter:

=Cost of Goods Sold/Net Sales

In cell B44 enter 2.5 and in C44 enter 5. In D44 enter:

=C44+$B$44

and drag this along to I44 so that each cell along the row increases by 2.5. In B41 enter:

=(100-B44)/100-$B$40

and drag this to I41. In B42 enter:

=$B$39/B41/$B$23

and drag this along to I42. In B43 enter:

=(B42-1)*100

and drag this along to I43. Save the file.

Highlight the range B43 to I43, click the Chart tool and choose the first Area Chart sub-type.

Next, put B44:I44 as the category X axis labels. Then give the chart the title, Price Sensitivity Analysis. The category X axis title is: Price decrease % and the value Y axis title is: Sales increase %.

Now choose ‘As new sheet’. Give the tab a name such as Price Chart. Click Finish and, apart from a little colour formatting, you’ll have a chart like that shown in screen 3 .