Stratifications

Learn Excel from MrExcel Podcast Episode 2101: Stratifications with a Pivot Table.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen.  This is one of the question, one of the things I talk about all the time in my Live Power Excel seminars.  I was surprised I don't have a video out on YouTube once.  And this goes back to when I was working in accounting, 20-25 years ago and we had this great VP of Sales who moved from California to our home office and John, they had to find a quarter office for him.  So, he ended up on my floor and he was surrounded by cubes and cubes and cubes of people doing order entry.  And so every day,     John will walk pass all these order entry people and he would look at all the payroll that was involved in order entry.  And to John, our business was simple.  Every month he would have a million dollar sale.  Alright, and those were the things that made him all the money.  The reason we had 18 or 20 order entry people was because we were then selling spare parts, batteries and, you know, things that broke.  And he was just- he was looking at all of these small orders and how many people we had to employ for the small organization like, I want to give all those small orders where we just find a value-added reseller, let them deal with the small things.  I just want to deal with a million dollar orders.  You could get rid of all these order entry people, just have one person to enter my big order each month. 

And so I wanted to demonstrate to John those small orders were higher profit.  Alright, and I did that using a Pivot Table.  So, choose one cell, this is our Invoice Register, two years worth of data.  Let it go to a New Worksheet, click OK and now this Pivot Table is going to be unlike any other Pivot Table you've ever built because we're going to take the Revenue Field which normally goes to the Values area.  And we're going to move it to Rows like that.  Alright, and these are dollar amounts, alright?  So, here's an order for 1735 dollars, here's an order for 1741 dollars, and all the way down at the bottom.  Those are the big orders, alright, they’re million dollar orders. 

Alright, so, first thing very unusual: taking a Numeric Field and moving in to Rows; second thing that's very unusual is we're going to take a Text Field such as Customer and move it to Values, alright?  That just- this is completely backwards.  Usually Customers will be here, Sum of Revenue there.  And this is Count of Customer but this is really # of Orders, alright.  So we're seeing that there was one order for $1,699, one order for 1736 and again not interesting in this current format but this is where Pivot Tables are really good.  They can help us create Stratifications. 

Take this data and show it in layers or in buckets so we choose our first revenue field and that's a Group Field, and they choose some crazy things here and I always override this.  I want to go from 0 to 16000000 dollars and let's just say 10000 dollar buckets like that.  And you might have to experiment with this.  You can always come back to this Grouping Field and change it but I want to really kind of focus on those small orders and click OK.  Alright, and now here we are.  We can see for each of these items they were, sure enough, 458 small orders under 10000 dollars, 530 under 20,000 – between 10000 and 20000, and then it kind of falls off.  Not too many orders up there on the 20,000 to 29,000.

Now, what would be really nice here is if in the Grouping we could have different sized buckets.  Alright, that would be good because these items up here, I really want that just to be one big bucket.  Yeah, I can't do that.  Alright, but now that I have this simple – I missed a one page report, it will get my point across.  I'm going to take Profit and move that to the Values area, I’m going to say Profit and move it to the Values area again.  I'm going to take Profit a third time and move it to the Values area.  And free to this, we're going to change the calculation.  Alright, so the first one, I just want to see how many dollars are in each bucket, but then the second one, I'm going to double click.  I'm going to say show the Values as a % of the Column, so % of Profit.  And then here I'm going to say our percentage, our running percentage.  So Show Values As.  And this is brand new in Excel 2010 so if you're, for some reason back in Excel 2007, you're not going to have this one - the % Running Total In.  You'd have to build the calculation outside the Pivot Table. 

So Accum. % of Profit, click OK.  Alright, and so I can say, “Hey, look, John.  You know, if you're going to get rid of all these small orders, yeah, it's a lot of orders.  But it's over a third of your profit, alright?  And if you're going to be losing 20 points on all these, you know, that is a huge chunk of money that you're going to give away.” 

Hey, one thing that watch out for here with the Stratifications is that Field Revenue that I put in the Rows area, that field is not going to work in this Pivot Table by default.  If I take the Revenue Field and drag it down here to the bottom, it will always default to Count.  I don't quite understand why that happens.  I know it happens that I'm used to it.  Double-click and first change it to SUM and then maybe change it to whatever calculation you want to do, our % Running Total In and we'll call this Accum. % of Revenue, like that.  Click OK.  And sure enough you'll be able to see that, you know, yeah it's 22% of the total revenue but higher profit margin on those small items, almost 30% of your profit.  And you can at least try and make the argument that there's a value to all of those folks over in order entry because they're entering high profit orders. 

Alright, so creating Stratifications in a Pivot Table.  Now, all kinds of Pivot Table topics are in this book, Power Excel with MrExcel, 617 Excel mystery solved.  Click that “i” on the top right-hand corner, go and take a look on how to buy the book.

Today's episode: Creating Stratifications with the Pivot Table.  First, we build a Pivot Table.  It's kind of backwards: Revenue in the Rows area and Customer in the Values area.  Select that first revenue cell in the Pivot Table, use the Group command.  Choose a bucket size and the starting point.  It’d be nice if the buckets could be variable but they aren't.  Add Additional Fields such as: Profit, Percentage of the Column and % Running Total In.  When you re-use Revenue though, be careful make sure to change from Count to Sum or your numbers will be wrong.  Alright, there you go. 

Well, hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.