Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.
Neale Blackwood CPA:
Welcome to Excel Tips. My name is Neale Blackwood and in this episode, I wanted to discuss the Pareto principle and Excel.
Excel has lots of built-in functions and features that work really well with Pareto analysis. Now the Pareto principle is probably more commonly known as the 80/20 rule. The usual business example is that 80% of your sales might be driven by 20% of your clients. Pareto was an Italian sociologist and economist, and he noticed that 80% of the land was owned by 20% of the people.
This relationship can be really useful to identify the areas that you need to focus on, which is typically the idea behind the 80/20 rule. Now it is working on percentages, and 80 plus 20 does equal 100, but that's not part of the rule.
Okay? So the relationships don't have to add up to 100. It’s basically a small percentage of one thing drives a large percentage of another. So it might be that 15% of your clients drive 75% of your sales. So, it doesn’t have to add up to 100% of for the 80/20 rule.
Now Excel has many built-in features that are related to the Pareto principle. It's even got a Pareto chart, which I'll talk about a little later.
One thing to consider when you are doing Pareto analysis is also segmentation. Typically, you look at things in totals when you're working with the 80/20 rule, but think about whether you can segment your data and look at things by department or by state as well, because the 80/20 rule might apply even at lower levels.
By the way, if you check out the companion video, there is a example of the Pareto chart. A Pareto chart is a combination chart of a column chart as well as a line chart. The columns represent the sales value and they're sorted.
So on the left-hand side are the higher sales values all the way across to the right-hand side, which has the lowest sales values. Along with that, there is a cumulative percentage of sales, lets use sales as the example.
The line chart will plot on the secondary axis, which is the axis on the right-hand side. Typically, it rises fairly quickly and steeply, then slowly tapers off and plateaus up to the 100% level.
The idea is that you have an 80% line on there, and when your cumulative percentage hits that 80%, all of the clients to the left of that point are the important clients. They're the ones that are generating most of your sales.
Now, unfortunately, the Excel Pareto built-in chart doesn't have that 80% line on there. The grid lines displayed on it are the grid lines for the value rather than the percentage. The advantage with the Pareto chart is that you can have a list of clients and their sales values next to them.
The list doesn't need to be sorted, and then you can use that as the data source for the chart. Part of the Pareto functionality will automatically sort it for you. We can use a combo chart in Excel to mimic what a Pareto chart should look like.
In terms of the combo chart, you've got to do a little bit more work. Again, you can check out the companion video to see how to do it. You can use the UNIQUE function to get a unique list of clients. Then you can use the SUMIFS function to get the value of sales for each of those clients.
There is also a SORT function that allows you to sort. With these three functions, you can create a list that you can then create a combo chart that can have the 80% line on it as well as the other percentage lines.
So, again, check out the companion video. Go through the steps to create those.
Now, pivot tables also have some built-in Pareto analysis. They can automatically sort the values from top to bottom. Unfortunately, the Pareto chart can't use a pivot table as its source. Not all charts can be run off a pivot table, and Pareto is one of those that can’t be.
But you can easily create a pivot table that's got the sales sorted. Adding the cumulative percentage, however, is not intuitive. When you create a pivot table, you typically drag the client name field to the rows, and then you grab the amount or the sales column to the values section.
If you want to put in a cumulative percentage as well, you drag that amount column again to the values section. When you right-click that second column of sales values, there is an option for "Show Values As," and there's another option there for "Percentage of Running Total." This will give you the cumulative percentage that builds up.
That’s automated, so if you update the data, the pivot table will automatically update that. Something else you might want to consider is adding a rank to the listing so you can see where the sales values fall in rank as well.
Again, to do that, you simply drag the amount column to the values section again. Right-click, select "Show Values As," and there's a ranking option for highest to lowest. So that will give you a ranking number.
Something to think about is that Pareto analysis over time can show you clients who are building up. They might not have been in the top 20% six months ago, but they have since entered it.
So it's always worthwhile to keep track of how clients are progressing, and some might be going down the list. This again helps you focus on areas that might be worthwhile looking into.
There's also a filter option with pivot tables that allows you to filter the listing so you can see the top 80%. It’s under the filter options as a value option. It’s called "Top 10," but it is a lot more flexible than that.
You can do the top or the bottom. You can filter by items, so you can say the top 10 items, or you can change the value and you can change between items or percent. So to see the list of all clients that make up your 80%, simply use a top 10 filter, change it to 80, and change it to percent. And it will only list the entries that will add up to the top sales that add up to the 80%.
Again, consider segmentation, as it's really easy with pivot tables. Pivot tables have what are called a slicer. Under the Pivot Table Analysis ribbon tab, there is the ability to insert a slicer. A slicer is a visual filter interface that’s really easy to use. You can filter by any of the fields in your table.
So you can filter by state, department, or customer category—however you segment your sales. Segmentation is also possible with formulas. There is a FILTER function as well.
With the example of UNIQUE, SUMIFS, and SORT functions, you can insert the FILTER function in that in that. You could change the report just to filter by a state. Again, the companion video has an example of that.
The last thing I wanted to talk about was conditional formatting. That’s located on the Home ribbon. There’s a drop down there with the second option for "Top Bottom Rules."
That is once again very similar to the top 10 in pivot tables. You can do top 10 items or top 10%, and you can change that value. I did do a previous article and podcast discussing how you can actually add interaction to these options, as the built-in ones don't allow you to link back to the spreadsheet.
So you have to key them in. But you can use some formula rules to achieve similar result, which allows for some interaction where you can change a cell on the spreadsheet to update the percentage you wanted to review.
So that was a previous podcast and article on adding interaction to the conditional formats.
Okay, so Excel has lots of built-in features and functions that can help you with your Pareto analysis. Think about other things that you might be able to measure. It is quite remarkable that it applies across so many different areas, it does apply in business, agriculture, even sport.
The 80/20 rule is quite universal. It is worthwhile investigating, and it can help you focus on the most important factors that can give you the best bang for your buck.
Hope you found this useful, and thanks for listening.
Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing it with colleagues, clients, or anyone else looking for the best Excel tips and tricks.
To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. We hope you can join us next time for another episode of Excel Tips.
