ABC-analysis, method of Triangle in Excel

Ivan Bondarenko February 25, 2015 No Comments

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.

Method of triangle in ABC-analysis

Ivan Bondarenko January 21, 2015 No Comments

Hi there! In this post you will find overview of triangle method in ABC-analysis, its pros and cons, and advantage over classical methods.

Firstly, remember basics of Classical ABC-analysis.
The simplest method of classification can be called method of segmentation on three equal groups by quantity of positions or by value of result.

Simplest method

However, this method is only useful for even diminishing series (arithmetical progression). In practice, such distributions are rather exceptions. In examples below, you will see how irrational can be segmentation on equal groups.

Simplest method 2
(more…)

Methods of classification in ABC analysis

Ivan Bondarenko January 15, 2015 No Comments

Hi all, I would like to start here with article about methods of classification in ABC-analysis. I tried to find such compilation in Google, but couldn’t, even couldn’t find info about some methods described below.

Introduction

ABC-analysis – one of simple objects classification methods using defined parameter. Although methods that use one parameter limited, ABC-analysis is one of the most popular analytical tool nowadays. Due to this popularity, plenty of algorithms for grouping items in ABC-analysis were developed. This, from the one hand, gives analyst rich choice of methods for different goals and technical resources, but, from the other hand, contains hidden threats in case of wrong usage of varying methods.

Process of ABC-analysis can be divide on five following steps:

  • Definition of goals
  • Selection of classification parameter
  • Data collection and preparation
  • ABC-classification
  • Interpretation of results
  1. On first step should be defined analysis goals. They influence on further steps.
  2. On second step, depending on goals, parameter of objects classification should be chosen.
  3. Third step contains process of collection and preparation of raw data for analysis.
  4. Using one of algorithms and chosen parameter on fourth step should be processed ABC-classification.
  5. On the last step, results can be observed and needed decisions taken.

In this article considered seven most used methods of grouping in ABC-analysis, shown their pros and cons, and in addition comparison of these methods.

(more…)