Pocket Price Waterfall Chart in Excel

I’m working on some pricing reports for a client, and one of the requests was for a Pocket Price Waterfall chart. I hadn’t made one of those before, and fortunately the client sent me a sketch of the chart that they wanted. It looked something like this.

image

The good news was that it looked similar to other waterfall charts that I’ve made. However, instead of two highlighted columns, to show start and finish, it had several highlighted columns.

The Pocket Price

The term “pocket price” was new to me, and some Googling led me to an article published by McKinsey & Company in 2003 – The Power of Pricing. They coined the term “Pocket Price” to describe the amount that you actually get from a customer, after the hidden costs and off-invoice discounts that might exist. The article shows a waterfall chart, similar to the one that my client wanted. Maybe that’s where they got the idea!

Add the Extra Columns

To experiment with adding more highlighted columns, I started with the cash flow waterfall chart from a previous blog post, and added rows for the Quarterly totals. In the previous version, there was only one number in the End column, and I needed a number in each Quarter row, where I wanted a highlighted column.

I created a new formula in the End column, cells C4:C14, to check the Net Cash Flow column (G). If that cell is empty, show the sum of the previous amounts.

=IF(LEN(G4)=0,SUM(B3,E3:F3)-D4,””)

The Base column is the reverse, showing the sum if the cell in column G is NOT empty.

=IF(LEN(G4)=0,””,SUM(B3:C3,E3:F3)-D4)

waterfallchartqtr01

The Revised Waterfall Chart

Here is the revised waterfall chart, with a highlighted column for each quarterly total, ending in September.

waterfallchartqtr02

Then, I used the same formulas with some faked Pocket Price data, to create the Pocket Price waterfall chart. There aren’t any green columns, because all of the values are negative amounts, bringing down the price.

waterfallchartqtr03

And now I’m ready to create the real Pocket Price waterfall chart, whenever we get to that stage in the project.

Download the Sample File

To see the waterfall charts and the worksheet formulas, you can download the Pocket Price Waterfall Chart sample file. The file is in Excel 2007 format, and zipped. There are no macros in the file.

Waterfall Chart Utility and Video

If you need to make waterfall charts, take a look at Jon Peltier’s time-saving Excel Chart Utility. While working on this chart, I discovered that Jon’s add-in has this multi-highlighted column feature built in. It also handles data that crosses the X Axis, with totals in the negative amounts.

To see how I used Jon’s add-in to quickly create a sample Pocket Price waterfall chart, please watch this Excel video tutorial.

_____________

You may also like...

2 Responses

  1. P.J. says:

    Very cool. Thanks for posting!

  2. Ravi says:

    Very helpful. Thanks.

Leave a Reply to P.J. Cancel reply

Your email address will not be published. Required fields are marked *