This Excel Chart Could Get You Promoted

Charts

There are two or three charts that every business person must know. Arguably two of the top ones are the break-even chart and the burn down chart. My vote for the third chart is Pareto’s Rule, the 80/20 Rule. This chart is the god of productivity improvement. Pareto’s Rule, and its chart, shows you where small changes in your business create huge impacts. This tip will show you how spending five minutes with an Excel Pareto’s Chart created a massive productivity impact for one global company.

The Pareto chart will make you a performance improvement god in your business.

This one chart will make you a performance improvement god in your business.

This one chart will make you a performance improvement God in your business. It might even earn you a bonus or promotion.

Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.

Pareto’s Rule, the 80/20 Rule, has a fascinating history, but its insights into society, economic, science, and business are more than fascinating. They are amazing. This is a powerful tool for change.

In business you can use Pareto’s Rule to identify,

  • The 20% of customers creating 80% of your profit
  • The 20% of troubleshooting issues creating 80% of your support calls
  • The 20% of your products creating 80% of new business
  • The 20% of expense items creating 80% of costs
  • The 20% of your website pages creating 80% of the value on your website

Pareto’s Rule Reduced Workflow by 97% While Maintaining Value

This last bullet, that identified the few website pages that brought 80% of the value to a company, brought Pareto back to my mind. Over the last few years I’ve been helping large global marketing customers develop and implement their online customer experience strategy. Frequently I hear CMOs and digital strategists complain that they don’t have enough resources (read people and money).

Using Pareto and five minutes of data analysis with Excel it was easy to identify the 80% of the marketing value generated by one global company that was generated by just 25 pages out of the 735 pages on their website. Just 25 pages!!! The data you see in the figure is real data, but the site page names have been sanitized.

Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.

Download the Pareto Analysis and Chart Excel file.

Even if this business focuses their resources on the top 50 pages rather than top 25 pages, they will still save considerable resources. Pareto has helped them focus on what is Critical to Success. Remember, first optimize what is Critical to Success, then look for secondary improvements.

Creating a Quick Excel Pareto Analysis

In this example I sucked into Excel 2013’s PowerPivot  approximately 800,000 rows of visitor transaction data from their international website. If the dataset had been smaller I could have imported the data as a TXT or CSV file directly into an Excel sheet and used a normal Excel PivotTable.

The data set contained traditional web analytics data as well as a Sitecore proprietary metric shown here as the Value (Engagement Value) for each visitor transactions. That Engagement Value is a calculated proxy for the visitor’s impact on the company’s strategic objective. Depending upon what you want to improve, instead of “Value by Page” you could use revenue by product, profits by product, expenses by item, frequency by support call topic, etc.

The Pivot Table based on the data is in columns C and D of the figure. Column C is the name of each website page. (It took hours to sanitize the 235MB of data with Excel, so be awestruck! J ) Column D is the Engagement Value for each page.

The sample file contains only the results of the PivotTable. The very large data set and PivotTable itself have been removed so you won’t have a massive download.

Download the Pareto Analysis and Chart Excel file.

Sort Column D for descending Values so the highest Value items are on top. Columns E, F, and G use normal Excel worksheet formulas to create a cumulative Value, a percentage of total value, and the total of all values. The formulas in row 22 are copied down through the entire height of the Pivot Table.

Column   Heading  Formula in Row 22 
C Row Label
(Web Page Name)
Pivot Table Field
(fixed values in the sample file)
D Sum of Value Pivot Table Field
(fixed values in the sample file)
E Cumulative Value =SUM($D$22:D22)
F % Total Value =E22/SUM($D$22:$D$756)
G 80% of Total Value =0.8*SUM($D$22:$D$756)

Notice that the formula in cell E22 uses mixed references so that when the formula is copied down it creates a cumulative sum of the adjacent values in column D.

Cell F22 calculates the percentage of total value. If you were building this sheet to use for more than this analysis you would extend the range D22:D756 down far enough that all possible data was included.

Cell G22 calculates the 80% of total, the straight line, in the chart.

The formulas producing the count of pages are,

I14          =”Total Pages = “&COUNTA(C22:C1048576)

I16          =”Pages Creating 80% of Total Value = “&COUNTIF($F$22:$F$1048576,”<=.8”)

I18          =”Potential Pages in Workflow Reduction = “&TEXT(1-COUNTIF($F$22:$F$1048576,”<=.8″)/COUNTA(C22:C1048576),”##%”)

Download the Pareto Analysis and Chart Excel file.

Creating a Quick Pareto Chart in Excel 2010 and Excel 2013

The quick and dirty Pareto chart, shown in the figure above, is not exactly how Pareto Charts usually appear, but the table does the actual work of identifying the pages that make up 80% of the value and the chart gives a great visual representation of how few pages actually do the heavy lifting for the website.

To create the chart,

1.       Select only the data in Cumulative Value, then insert a Line Chart.
This creates the curved asymptotic line showing diminishing returns.

2.       Select the 80% of Total Value data, copy it, and paste it onto the chart by selecting the chart and pressing Ctrl+V.
This creates the straight line marking the 80% of Total Value.

Analyzing Your Quick Excel Pareto Rule Results

Scan down the % of Total Value column and you can see that the percentage of Value increases as you go down the list. But, the incremental value added of each additional website page decreases.

After only 25 pages, on line 46, 80% of the Value for the website has been generated. That is pretty astounding! These top 25 pages are critical, yet the website has 735 pages total.

This chart doesn’t mean you can completely disregard the other 710 pages. Some are there for supporting information, such as Contact Us or Help. In this case it means that these top 25 pages need to be extremely clear and relevant with a highly developed Call to Action. All Search Engine Optimization (getting them highly ranked on Google) must be optimized. These are pages visitors are really interested in. What are visitors finding of interest and relevance on these pages? What can marketing leverage or multiply? What can you learn from these pages that could be applied to others?

What can marketing do with the extra resources and time they have?

Analyzing Sales and Performance Using Pareto’s 80/20 Rule

If instead of website pages you were analyzing results from product sales or sales from salespeople, you might not want to remove the lower performing products or salespeople, but you should analyze the difference between the top and bottom performers. What puts the top products on top? You might want to analyze these products using a BCG Growth-Share matrix? <LINK>

If you are looking at the performance of salespeople you might want to give sales assistants to the top performers to increase their performance even more. You might want to institute a sales mentoring program where new salespeople can shadow top performers.

Now that Pareto’s 80/20 Rule has uncovered opportunity it is up to your creativity to take advantage of that opportunity.

Pareto’s Rule is Not Always 80/20

Pareto’s Rule is not always an exact 80/20 ratio. From my experience the ratio is usually in the range of 80/10 to 80/25. However, this is the first time I’ve seen such a huge impact by such a small input.

Just 25 pages out of 735 pages on the site account for 80% of the strategic impact. That’s three percent (3%) of the pages accounting for 80% of this company’s strategic impact. And, I had already selectively removed a few outlier pages such as Home, Checkout, and Login.

Pareto’s Rule and Its Impact on Your Career and Business

Leverage points like those identified by Pareto’s Rule can make huge differences in any system, whether it is business, society, manufacturing, you name it. In business, if you apply Pareto’s Rule to the right Quick Win business case you just might leverage yourself into a promotion.

Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.

Download the Pareto Analysis and Chart Excel file.

Share the power...

Leave a Comment:

Leave a Comment: