A Pareto chart in Excel is a chart which is made up of a column chart and a line chart to show a cumulative position. This is often a good chart to show an 80 20 scenario, where 80 % of the costs are achieved by 20% of the cost drivers. The chart can be created by segmenting the the data into cost drivers or buckets. If your business expenses were allocated into the following buckets:
- Staff
- Building
- Legal
- Accounting
- Rates
- Entertainment
- Marketing
The column chart will be labelled costs at the y axis and the cost names will be the X axis. The costs will be cumulative and the difference with a Pareto chart is the costs start from largest to smallest. This is what gives the chart its look (see above). So the chart will need to have some sort of dynamism in order to keep the look and feel of a Pareto chart.
In this situation we can use the Excel's LARGE formula to work out the highest costing item. The LARGE formula takes the form;
Large(Cell Location, N) where N is the largest number, 1 represents the largest number in the range, 2 the second largest number and so on. Then just use a LOOKUP or an INDEX and MATCH formula to trap the item description.
The following is an Excel example which should help crystallise the concept:
