How to Accomplish a Pareto Analysis in Microsoft Excel

When examining spend, the most effective way to start is at the top. This method will expose categories with the most savings opportunity because more savings are reaped from higher spend. A lot of corporate spend falls into only a select few categories. For example, a company may have 80% of its spend going to Financial Services, IT, and Published Products.

A good spend analysis product should display a Pareto Analysis within each category in its reporting suite. From there, any specific piece of intelligence, categories, suppliers, etc. can be exported and analyzed. One useful example of this tactic is exporting static spend by category totals to see where 80% of total spend is going. What follows is a table of categorized customer data exported from Iasta’s Smart Analytics tool:

Here are the steps to accomplish a Pareto analysis of the top categories in Microsoft Excel:

  1. Sort the data in descending order of spend.
  2. Calculate a sum of the total spend for all categories ($2,168,726,261.58).
  3. Divide each category’s spend by the sum of the total to get a % to total. (I.E. Financial and Insurance Services spend would be $398,184,018 / $2,168,726,261.58)
  4. After you have a % to total of every category, sum those percentages to get a cumulative total from top to bottom. Reference column G in the screenshot that follows to see the cumulative formula.

When you complete every step correctly, you should be able to examine the Cumulative column in your spreadsheet to determine where the top 80% of your spend is going. In the previous example, the top 80% of spend lies in four categories:

Be Sociable, Share!

One Response to How to Accomplish a Pareto Analysis in Microsoft Excel

  1. Please share more info for spend analysis

Leave a Response