ABC 분석과 파레토 분석 그리고 80대 20 법칙

ABC 분석이란 '관리할 대상을 세 그룹(예를 들어 A, B, C)으로 나누고, A 그룹에 관리 노력을 집중함으로써 효과를 높이려는 분석 방법'을 말합니다.

예를 들어서 일정 기간 동안 판매된 제품 중에서 상/하위 20%에 해당하는 제품을 파악한 다음, 상위 제품에 대해서는 공격적인 프로모션을 진행하고 매출 하위 품목들은 품목수를 정비하기로 의사결정 할 수 있습니다.

이 분석 방법은 이탈리아 경제학자인 알프레도 파레토 ^{Alfred Pareto}에 의해 도출된 분석 방법이기에 '파레토 분석' 또는 '80대 20의 법칙'이라고도 부릅니다.

이번 시간에 만들어 볼 차트입니다. 엑셀 2016 버전부터는 파레토 차트가 엑셀의 기본 차트에 포함되어 있습니다. 물론 그 이전 버전에서도 어렵지 않게 파레토 차트를 만들 수 있습니다.

파레토 차트 만들기(엑셀 2016 이전)

1. 왼쪽과 같은 날 것(?) 그대로의 데이터로는 [완성 예]와 같은 파레토 차트를 만들 수 없습니다. 데이터와 차트의 관계는 물체와 그림자 간의 관계와 비슷합니다. 그림자 모양을 바꾸려면 물체의 모양을 바꿔야 합니다. 그림자를 바꾸는 게 아니고.

2. '구성비'와 '누적구성비'를 구합니다. D4, E4, E5 셀에는 다음과 같은 수식을 입력하고 채우기 핸들을 이용하여 수식을 아래로 복사합니다.

3. CTRL 키를 이용하여 떨어져 있는 영역인 B3:B9, D3:E9 영역을 범위로 지정하고 [삽입] 탭 - [차트] 그룹 - [추천 차트] 명령을 클릭합니다. [차트 종류 변경] 대화상자에서 [모든 차트] 탭에서 [혼합형] 차트를 선택합니다.

4. 구성비 계열(파란 막대)을 마우스 오른쪽 버튼으로 클릭하고 [데이터 레이블 추가] 메뉴를 선택합니다.

5. 누적구성비(꺾은선)에 대해서도 동일한 작업을 진행하여 데이터 레이블을 추가합니다. 여기까지 마치면 차트 모양이 아래와 같은 형태로 됩니다.

6. '세로 (값) 축'을 마우스 오른쪽 버튼으로 클릭하고 [축 서식] 메뉴를 선택합니다. [축 서식] 작업창에서 최대값을 '1'로 수정합니다.

7. 구성비 계열(파란 막대)을 마우스 오른쪽 버튼으로 클릭하고 [데이터 계열 서식] 메뉴를 선택합니다. 작업창에서 [계열 겹치기], [간격 너비]를 각각 0으로 지정합니다.

8. 구성비 계열의 레이블을 마우스 오른쪽 버튼으로 클릭하고 [데이터 레이블 서식] 명령을 선택합니다. 작업창에서 [레이블 위치] - [가운데]로 지정합니다.

9. 데이터 레이블의 색을 흰색으로 지정합니다. 차트 제목을 적당한 것으로 변경하고 차트의 나머지 서식은 자신의 취향에 따라 적절히 조정하여 완성합니다.

ABC 분석하기(엑셀 2016)

아래에서 소개하는 내용은 엑셀 2016 버전부터 지원하는 파레토 차트를 이용하였다는 점을 제외한 나머지 방법은 2016 이전 버전에서도 동일하게 사용할 수 있습니다.

1. 예제 파일의 '2' 시트에서 금액을 기준으로 내림차순 정렬합니다. 합계 부분을 제외하고 범위로 지정한 것과 활성화 된 셀이 금액 필드(D3 셀)에 있다는 점에 유의하세요.

2. '금액구성비'와 '누적구성비'를 추가합니다. E3, F3, F4 셀에 다음 수식을 입력하고 아래로 복사합니다.

3. CTRL 키를 누른 채 B2: B17, E2: E17, F2: F17 영역을 범위로 지정하고 [삽입] 탭 - [차트] 그룹 - [추천 차트] 명령을 선택합니다.

4. [차트 삽입] 대화상자에서 [추천 차트] 탭에서 [파레토] 차트를 선택하고 [확인] 버튼을 클릭합니다.

5. 파레토 차트가 간단하게 만들어졌습니다.

6. ABC 분석을 위해 차트 모양을 다듬어 줍니다. 아래 과정은 반드시 해야 하는 것은 아니며, 차트를 좀 더 시각적으로 보여주기 위한 것입니다. 누적 기준 상위 70%를 벗어나는 K를 클릭하고, 잠시 후에 다시 한 번 K를 클릭하면 K 막대(요소)만 선택됩니다.

7. 이 상태(K 요소만 선택된 상태)에서 K를 마우스 오른쪽 버튼으로 클릭하고 [데이터 요소 서식] 메뉴를 선택합니다.

8. [데이터 요소 서식] 작업창에서 [채우기] - [색]을 '연한 파랑'으로 지정합니다.

9. 6~8번 과정을 반복하여 B등급과 C등급에 해당하는 품목의 색상을 변경합니다. 차트의 나머지 서식은 [완성 예]를 참고하여 마무리합니다.