ABC-analysis, method of Triangle in Excel

This post continues topic described in two previous posts – ABC analysis. Very popular, simple method, however in most of cases non-accurate.
In previous post I showed how to increase accuracy of ABC-analysis by using special method – Triangle method. And now time to show real example.

Assume that we have list of sales

3-1

To apply method of triangle we have to
1. Sort data descending by Net Value

3-2

2. Convert Net Value to Net Sales – exclude possible Returns.
=IF([@[Net Value]]<0, 0, [@[Net Value]])

3-3

3. For each row calculate part of total Net Value – NS Normalization: [@[Net Sales]] / SUM([Net Sales])

3-4

4. For each row calculate [Contribution in result]: sum of [part of Net Value] from 1st ranked position till current row =SUM(SALES[[#Headers],[NS Normalization]]:[@[NS Normalization]])

3-5

5. Make normalization of rank: =[@Position] / MAX([Position])

3-6
6. Find Pareto Point – point where [@[Contribution in result]] + [@[Position Normalization]] = 100% (or closest point).

3-7

Resulting table

3-8

Then we will be able to find Xp and Yp – coordinates of Pareto Point.
In example file I placed these coordinates in separate cells and gave corresponding names to that ranges: “Xp” and “Yp”.

3-9

After that, using formulas

3-10
Which in Excel are:
=(Xp*Yp – Xp^2) / (2*Yp)
=(3*Xp*Yp + Xp^2) / (2*Yp)

We can find upper bound of groups A and B. And then mark all positions with corresponding class.

3-11

Such calculation can be easily done in PowerPivot where we can avoid sorting and grouping of transactional sales data – DAX can handle all necessary calculations.

Example workbook can be found here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s