# 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.

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!

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)

### The Revised Waterfall Chart

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

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.

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