Learn How To Do 80/20 Analysis in Excel to Analyze your Business
Learn how to do an 80/20 analysis in Excel, and use the 80-20 principle to manage your business, reduce costs and increase your profits.
You’ve probably heard about Pareto’s Law or the 80-20 principle. Although…most people only understand 20% of it, 80% of the time (rim shot!).
This simple principle can be used to increase sales, profits and increase effectiveness of your marketing, and is easy to use.
Vilfredo Pareto was an Italian economist and sociologist born in 1848. His big discovery in 1897, “Pareto’s Law,” claimed that in any society, 20% of the people would earn 80% of the income. Pareto’s observations are now usually referred to as either the Pareto Principle, Pareto’s Law or simply the 80/20 principle. They have since been used in areas that Pareto could never have imagined. One you learn how to do 80-20 analysis in Excel you might be amazed.
It is surprising how often Pareto’s Law appears in business well over a century later.
Let’s jump right in and learn how to do an 80/20 analysis in Excel!
How to do an 80/20 analysis in Excel; Pareto’s Law to analyze your business
Pareto’s Law is old news to a lot of industrial engineers and people in the quality profession who deal with process improvement. It can also be used to analyze other things in your business. If you’re a manufacturer you can use Pareto’s Law to evaluate whether to add to, or eliminate part of, your product line. You can also evaluate or prioritize your customers.
If you’re a distributor, you can use it to analyze your stocking inventory (and to determine how many of those SKUs that manufacturers claim are “AA movers” really are). Jobbers and retail businesses can use it as a tool to allocate floor and shelf space. Manufacturer’s reps can use it to easily see where to focus time and resources.
The point of all this is that Pareto’s Law is a tool that lets you focus on the few products or customers that bring in most of your revenue and make the biggest impact to your business.
SKU rationalization Excel template
Below, we show a SKU rationalization Excel template. Let’s dig deeper into SKU rationalization and learn how to do an 80/20 analysis in Excel by doing a quick Pareto 80-20 analysis on an imaginary business. Imagine you run Pareto Pistons, a manufacturer with $10 million in sales. Gather the gross sales history for all of your part numbers for the past 12 months in a spreadsheet like Microsoft Excel or Google Sheets. Include everything that you sell, including cataloged part numbers and component or replacement parts.
Step-by-step SKU analysis
If you can also list the total gross profit generated by each part number, that’s even better and you’re ahead of most of your competitors. Sort your list in descending order, with your biggest seller at the top of the list. Manufacturers: if you use MRP or ERP software in your business, it may already be able to produce an ABC analysis with this type of information.
Your 80-20 list should look something like this:
A | B | C | |
---|---|---|---|
1 | Imaginary Part Number | Total Sales $ | Gross Profit $ |
2 | 10-10345 | $1,542,034 | $824,234 |
3 | 10-43445 | $845,453 | $321,324 |
4 | 30-30325 | $798,324 | $239,731 |
5 | 10-10355 | $643,203 | $264,204 |
6 | 40-55055 | $235,342 | $105,420 |
7 | etc. | etc. | etc. |
Since your list will include all your part numbers, it will be hundreds, or thousands, of part numbers long. If it’s thousands and you’re a $10 million manufacturer, you really need this analysis!
This type of spreadsheet can be used for a SKU rationalization template.
If you total the sales column it should equal your gross sales for the year, and the gross profit column should equal your gross profit for the year.
SKU rationalization and the Pareto 80-20 rule
You should now have manufacturing Pareto charts that are eye-opening. If you have never looked at your product sales this way, this step may be an “a-ha! moment” for you. You can quickly visualize where most of your sales come from. You might have intuitively suspected some of this but here is actual data.
Look at what else the data shows you. In the example above, we only listed five part numbers so far, but already the top three “10-” series parts make up about 30% of your total sales. Ask yourself if there is anything you can do to expand on the success of the 10- series: maybe you should develop more applications, or allocate more marketing dollars.
Now that you can see what your customers are buying, sort your data by gross profit and see what is actually helping pay your electric bill. In this example, 30-30325 generates more sales dollars than 10-10355, but 10-10355 generates more gross profit. Does your management team understand why one is more profitable than the other?
80-20 rule examples in Excel
Let’s take the analysis a step further and look at total cumulative sales and cumulative gross profit. Assume total sales = $10 million and total gross profit = $7 million. We’ll show you how to create a spreadsheet like this:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Part Number | Total Sales $ | Cume Sales % | Gross Profit $ | Cume Profit % |
2 | 10-10345 | $1,542,034 | 15% | $824,234 | 12% |
3 | 10-43445 | $845,453 | 24% | $321,324 | 16% |
4 | 30-30325 | $798,324 | 32% | $239,731 | 20% |
5 | 10-10355 | $643,203 | 38% | $264,204 | 24% |
6 | 40-55055 | $235,342 | 41% | $105,420 | 25% |
7 | etc. | etc. | etc. | etc. |
Add columns for Cume Sales % and Cume Profit % and at the bottom of the spreadsheet total up your Sales and Gross Profit columns. Let’s say the row where the totals are is row 50. In Column C enter this formula: =(SUM(B$1:B1)/B$50) and copy it all the way down. In Column E enter this formula: =(SUM(D$1:D1)/D$50) and copy it all the way down. Your Pareto spreadsheet should look like the one above and by the time you get to row 50 the percentages will be 100%.
This Excel sheet, fully built out, is a great SKU rationalization template to use as you continue to learn how to do an 80/20 analysis in Excel.
How to use your Pareto chart and the 80-20 rule in Excel
This is the “meat and potatoes” part of your analysis. This is where the SKU rationalization 80/20 rule comes into play.
Total the sales of your part numbers until you reach about $8 million, or 80% of total sales at Pareto Pistons. If you regularly review product lines and discontinue older products on a regular basis, you will probably find that 15% to 25% of your total part numbers generate this 80% of your sales dollars. If you have not reviewed your product line for a long time (or ever?), don’t be surprised if only 10% of your part numbers generate 80% of your sales.
Now’s the time for tough decisions. Look at the bottom of your list. What is the cost to sell those parts that represent such a small part of your total sales or gross profit? How much overhead, labor and materials are used by the 80% of the parts that represent 20% of your sales (hint: it is much more than 20% of your costs). How much more profit would you have if you eliminated low-volume, high-cost parts?
A word of warning: At this point, the Pareto Pistons sales staff will be screaming that you can’t possibly eliminate these products. Sales people want to sell! On the other hand, your plant manager and accountant will be intrigued by this idea and will wonder if you can eliminate even more of your product line to concentrate on the high volume products. You will have to listen to both sides and make the best decisions.
This analysis is simply a tool, like a wrench. Anyone can use a wrench but you have to decide how hard to tighten the bolts. The SKU rationalization 80/20 rule may force tough choices!
Conclusion on using 80-20, Pareto and Excel to manage your business
Hopefully you learned how to do an 80/20 analysis in Excel. The 80/20 rule shows up in some amazing places. We first observed it two decades ago in a small print shop, with 80% of sales were coming from 20% of the customer base (of course, we used yellow notepad paper and an old-fashioned calculator to figure this out), so we focused on what we could do to increase sales from those best customers.
With some imagination you can make Vilfredo Pareto an unpaid business consultant of yours and increase your bottom line. It may not help in all cases, but hopefully it will work for you at least 80% of the time (rim shot!).
Click for more ideas on business strategy or learn how to create your own brand strategy framework.
Pareto principle in everyday life
•Of the total number of drivers that qualified in NHRA Pro Stock in a recent racing season, 20% of them qualified in about 80% of all the races.
•For managers, many times 80% of employee problems come from 20% of the employees. Are these employees being properly and consistently managed to reduce your headaches?
•Illinois Tool Works, Inc. (NYSE: ITW) successfully uses 80/20 as one of their core philosophies. This $14+ billion company has acquired hundreds of companies over the years, and applies the 80/20 principle to trim under-performing product lines and increase profits. ITW’s philosophy is that each of their more than 600 decentralized business units are structured to focus on the 20% of products and customers that produce 80% of the sales dollars.
•Pareto’s Law of income distribution no longer applies to the modern-day United States, but does apply to total family wealth : today in the U.S., 20% of U.S. families do have 80% of the wealth. However, it’s still close worldwide: 16% of the world’s total population receives 84% of the income, according to a recent World Bank study.