Excel에서 ABC 분석하는 방법: 5가지 쉬운 단계

ABC 분석은 재고 품목을 연간 사용 가치에 따라 세 가지 등급으로 분류합니다. A 등급 품목은 적은 수로 대부분의 금액을 차지하는 품목입니다. C 등급 품목은 많은 수로 적은 금액을 차지하는 품목이며, B 등급은 그 중간에 위치합니다. Excel에서는 연간 가치, 전체 대비 비중, 누적 합계, 그리고 각 등급을 지정하는 수식을 포함한 하나의 테이블로 이를 수행할 수 있습니다.

이 가이드에서는 각 등급의 의미, Excel의 5단계 과정, 실제 적용 예시, 그리고 결과를 차트로 나타내는 방법을 설명합니다. 또한 기준값을 선택하는 방법과 분석이 완료된 후 각 등급을 어떻게 처리해야 하는지도 다룹니다.

ABC 분석이란 무엇인가

ABC 분석은 어떤 품목에 가장 많은 주의를 기울여야 하는지 결정하는 방법입니다. 이는 '적은 비중의 품목이 지출의 큰 비중을 차지한다'는 단순한 패턴에 기초합니다.

Management Sciences for Health (MSH)의 2012년 의약품 지출 분석 및 통제에 관한 장에서는 이를 명확하게 설명하고 있습니다. 이 장에서는 "비교적 적은 수의 품목이 연간 소비 가치의 대부분을 차지한다"고 언급하며, "이러한 현상을 분석하는 것을 파레토 분석(Pareto analysis) 또는 더 흔하게는 ABC 분석이라고 한다"고 덧붙였습니다.

동일한 장에서는 품목을 "연간 사용 가치에 따라 세 가지 범주(A, B, C)로 분류할 수 있다"고 설명합니다. 의약품, 예비 부품, 소매 제품 등 어떤 것을 재고로 보유하든 방법은 동일합니다.

한 가지 놓치기 쉬운 점이 있습니다. 등급은 영구적인 라벨이 아닙니다. MSH는 "사용 패턴이 변경되면 다음에 ABC 분석을 수행할 때 해당 품목이 다른 범주로 분류될 수 있다"고 지적합니다. 따라서 ABC 분석은 일회성 프로젝트가 아니라 정기적인 점검으로 수행할 때 가장 효과적입니다.

A, B, C 등급의 의미

MSH 장에서는 각 등급의 일반적인 범위를 다음과 같이 제시합니다.

등급	품목 비중	연간 가치 비중	일반적인 의미
A	10 ~ 20%	75 ~ 80%	적은 품목, 대부분의 금액
B	10 ~ 20%	15 ~ 20%	중간 그룹
C	60 ~ 80%	5 ~ 10%	많은 품목, 적은 금액

이는 일반적인 범위일 뿐 절대적인 규칙은 아닙니다. MSH는 "이러한 경계는 다소 유연하다"고 말합니다. 예를 들어, MSH의 예시에서는 자금의 70%를 차지하는 품목들을 A 등급으로 설정하기도 했습니다.

등급을 결정하는 가치는 연간 소비 가치, 즉 '연간 사용 수량 × 단가'입니다. 대량으로 사용되는 저렴한 품목이 A 등급에 속할 수 있으며, 일 년에 한 번 사용하는 비싼 품목이 C 등급에 속할 수도 있습니다.

American Journal of Business Education의 2014년 논문은 가치만을 기준으로 사용하는 것에 의문을 제기합니다. 교과서들이 "금액 규모만을 유일한 기준으로 집중한다"고 주장하며 다른 기준을 추가할 것을 권장합니다. 하지만 첫 단계 분석에서는 MSH 장에서 사용하는 가치 기준 방법이 주로 사용됩니다.

시작하기 전에 필요한 것

Excel에서 ABC 분석을 수행하려면 품목당 몇 개의 열만 있으면 됩니다.

• 품목명 또는 SKU. 품목당 한 행씩 입력합니다.
• 연간 사용 또는 구매 수량. 모든 품목에 대해 동일한 12개월 기간을 사용합니다.
• 단가. 수량을 측정하는 것과 동일한 단위 기준의 개당 단가입니다.

MSH는 분석 기간의 일치성을 강조합니다. "잘못된 비교를 방지하기 위해 모든 품목에 동일한 검토 기간을 사용해야 합니다." 또한 포장 크기를 혼용하지 않고, 알약 한 개나 단일 상자처럼 단가와 수량에 동일한 기본 단위를 사용할 것을 권장합니다.

데이터가 재고 또는 구매 시스템에서 제공되는 경우 CSV 또는 Excel 파일로 내보내십시오. 해당 기간 동안 활동이 없는 품목은 제거하거나, 그대로 유지하여 C 등급으로 분류되도록 하십시오.

Excel에서 ABC 분석을 수행하는 방법

아래의 5단계는 MSH 장의 방법을 Excel 수식에 맞게 조정한 것입니다. 예시에서는 1행에 제목, 2행에 헤더, 3행부터 12행까지 10개의 품목을 배치했습니다. A, B, C열에는 각각 품목명, 연간 수량, 단가가 입력되어 있습니다.

1단계: 품목, 수량 및 단가 나열하기

품목당 한 행씩 이름, 연간 수량, 단가를 입력하거나 붙여넣습니다. 나중에 테이블을 쉽게 정렬할 수 있도록 2행에 헤더를 추가합니다.

더 진행하기 전에 데이터를 확인하십시오. 빈 단가, 음수 수량, 중복된 SKU가 있는지 확인하십시오. 이러한 오류는 합계를 왜곡할 수 있습니다. 각 열에 빠른 필터를 적용하면 대개 쉽게 찾을 수 있습니다.

동일한 품목을 서로 다른 가격으로 여러 번 구매한 경우, 일관된 하나의 단가를 사용하십시오. MSH는 실제 단가를 추적하기 어려울 때 "가중 평균 또는 선입선출(FIFO) 평균"이 가장 정확한 대안이라고 설명합니다.

2단계: 연간 가치 및 전체 대비 비중 계산하기

D열에서 수량에 단가를 곱하여 각 품목의 연간 가치를 구합니다. D3 셀에 =B3*C3을 입력하고 수식을 아래로 드래그하여 채웁니다.

E열에서 각 가치를 전체 가치의 합계로 나누어 비중을 구합니다. E3 셀에 =D3/SUM($D$3:$D$12)를 입력하고 아래로 채웁니다. 달러 기호($)는 수식이 복사될 때 전체 범위가 고정되도록 유지합니다. E열의 서식을 소수점 둘째 자리까지 표시되는 백분율로 지정합니다.

MSH가 이러한 정밀도를 권장하는 데는 이유가 있습니다. MSH의 표현을 빌리자면, "여러 품목의 가치가 서로 매우 비슷할 수 있으며, 많은 품목이 전체 가치의 1% 미만을 차지할 수 있기 때문"입니다.

3단계: 가치가 큰 순서대로 품목 정렬하기

헤더를 포함한 전체 테이블을 선택하고 D열을 기준으로 내림차순 정렬합니다. Excel에서는 데이터 탭에서 정렬을 선택하고, 정렬 기준을 D열, 정렬 순서를 내림차순으로 설정하면 됩니다.

수식을 선호하는 경우 SORT 함수를 사용하여 정렬된 복사본을 반환할 수 있습니다. Microsoft의 구문은 =SORT(array,[sort_index],[sort_order],[by_col])이며, 여기서 정렬 순서 -1은 내림차순을 의미합니다. 이 테이블의 경우, =SORT(A3:E12,4,-1)은 네 번째 열을 기준으로 가장 높은 가치부터 정렬합니다.

이 단계를 마치면 연간 가치가 가장 높은 품목이 맨 위에 위치하게 됩니다. 이 정렬 순서 덕분에 다음 단계의 누적 합계가 의미를 갖게 됩니다.

4단계: 누적 백분율 추가하기

F열에 비중의 누적 합계를 추가합니다. F3 셀에 =SUM($E$3:E3)을 입력하고 아래로 채웁니다. 범위의 첫 번째 부분은 고정되고, 두 번째 부분은 행이 내려갈 때마다 하나씩 늘어납니다.

마지막 행은 100%로 표시되어야 합니다. 그렇지 않은 경우 D열과 E열에 빈 셀이나 텍스트 값이 있는지 확인하십시오.

이 열은 ABC 분석의 핵심입니다. 각 행의 위에 있는 품목들이 다 함께 전체 가치에서 얼마나 많은 비중을 차지하는지 보여줍니다.

5단계: A, B, C 등급 지정하기

G열에서 수식을 사용하여 각 품목에 라벨을 지정합니다. 기준값을 80%와 95%로 설정하고, G3 셀에 다음 수식을 입력한 후 아래로 채웁니다.

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

IFS 함수는 각 조건을 순서대로 확인하고 첫 번째로 일치하는 값을 반환합니다. Microsoft의 자체 예시에서도 동일한 패턴을 사용하며, 마지막 예외 처리로 TRUE를 사용합니다. 누적 비중이 80% 이하인 품목은 A, 95% 이하인 품목은 B, 나머지는 C가 됩니다.

마지막으로 =COUNTIF(G3:G12,"A")를 사용하여 각 등급의 개수를 세고, B와 C 등급에 대해서도 동일하게 적용합니다. 이 개수를 위의 일반적인 범위와 비교해 보십시오. 팀에서 관리하기에 A 등급이 너무 많거나 적다면 기준값을 조정하십시오.

실제 적용 예시

다음은 연간 가치 기준으로 이미 정렬된 10개 품목의 예시 테이블입니다. 여기에 사용된 숫자는 예시일 뿐이며 실제 기업의 데이터가 아닙니다.

품목	연간 수량	단가	연간 가치	비중	누적	등급
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

총 연간 가치는 $150,000입니다. 목록의 30%를 차지하는 3개 품목이 가치의 73.33%를 차지하여 A 등급에 속합니다. 4개 품목은 B 등급에 속하며, 가치의 6%를 차지하는 마지막 3개 품목은 C 등급에 속합니다.

두 가지 세부 사항이 눈에 띕니다. SKU-04는 수량이 단연 가장 많지만, 낮은 단가로 인해 B 등급에 속합니다. 또한 품목이 10개에 불과하므로 등급별 비중이 일반적인 범위와 정확히 일치하지 않을 수 있으며, 이는 품목 수가 적은 목록에서는 정상적인 현상입니다.

결과를 차트로 나타내는 방법

차트를 사용하면 회의에서 패턴을 쉽게 보여줄 수 있습니다. MSH는 품목 번호 대비 누적 백분율을 그래프로 그릴 것을 제안하며, 이를 통해 익숙한 ABC 곡선을 얻을 수 있습니다.

Excel에는 이를 위한 기본 차트가 내장되어 있습니다. Microsoft는 파레토 차트를 "내림차순으로 정렬된 열과 누적 백분율 합계를 나타내는 선이 모두 포함된 차트"로 설명합니다. 파레토 차트를 만들려면 품목명과 연간 가치를 선택한 다음 삽입, 통계 차트 삽입, 파레토를 차례로 선택합니다.

80% 및 95%와 같은 기준값에 두 개의 가로선이나 라벨을 추가하여 보는 사람이 각 등급이 어디서 시작하는지 확인할 수 있도록 하십시오. AI로 파레토 차트를 만드는 방법에 대한 가이드에서 차트 자체에 대해 더 자세히 다룹니다.

기준값 선택하기

단 하나의 정답인 기준값은 없습니다. MSH는 기준값의 선택이 "목록에 있는 품목들 사이에 수량과 가치가 어떻게 분산되어 있는지에 따라 달라진다"고 설명합니다. 또한 "ABC 분석 결과를 어떻게 활용할 것인가"에 따라서도 달라집니다.

관리 역량이 실질적인 한계입니다. MSH는 "A 등급에 대한 품목 할당은 관리 역량에 기초해야 한다"고 직접적으로 명시하고 있습니다. 팀에서 매달 50개의 품목을 면밀히 검토할 수 있는 수준인데 A 등급 품목이 300개라면 분석의 목적이 퇴색됩니다.

몇 가지 일반적인 접근 방식은 다음과 같습니다.

• 가치 기준값. 가치의 최대 80%까지는 A, 최대 95%까지는 B, 나머지는 C로 설정합니다. 위에서 사용한 방법입니다.
• 품목 수 기준값. 가치 기준 상위 20% 품목은 A, 다음 30%는 B, 나머지는 C로 설정합니다.
• 고정 목록. 일부 팀은 가치 비중과 관계없이 상위 25개 또는 50개 품목을 A 등급으로 고정하여 설정합니다.

어떤 방식을 선택하든 이를 기록해 두고 매번 동일하게 적용하십시오. 이번 분기의 등급과 지난 분기의 등급을 비교하는 작업은 기준값이 동일하게 유지될 때만 의미가 있습니다.

각 등급별 처리 방법

ABC 분석의 핵심은 돈이 집중되는 곳에 노력을 기울이는 것입니다. MSH 장에서는 분석 결과를 활용하는 몇 가지 방법을 제시합니다.

• A 등급 품목을 더 자주 주문하십시오. MSH는 A 등급 품목을 "더 자주, 소량으로 주문하면 재고 유지 비용을 줄일 수 있다"고 설명합니다.
• A 등급 가격 협상을 최우선으로 진행하십시오. 해당 장에 따르면 "분석에서 A 등급 제품으로 분류된 품목의 가격을 인하하면 상당한 비용 절감 효과를 얻을 수 있습니다."
• A 등급 재고를 더 자주 실사하십시오. MSH는 "순환 재고 실사는 ABC 분석을 바탕으로 진행되어야 하며, A 등급 품목에 대해 더 빈번한 실사를 수행해야 한다"고 지적합니다.
• A 등급 주문 상태를 면밀히 모니터링하십시오. A 등급 품목의 예상치 못한 품절은 비용이 많이 드는 긴급 구매로 이어질 수 있습니다.

C 등급 품목은 대량으로 덜 자주 주문하고 실사 횟수를 줄이는 등 더 단순한 규칙을 적용할 수 있습니다. B 등급은 그 중간에 위치합니다. 느리게 회전하는 재고가 우려된다면, 장기 미회전 재고를 파악하는 방법에 대한 가이드가 이 분석과 좋은 시너지 효과를 낼 것입니다.

AI로 더 빠르게 수행하기

데이터가 깨끗하게 정리되면 Excel 단계는 몇 분밖에 걸리지 않습니다. 하지만 내보낸 데이터를 정리하고 매 분기마다 이 작업을 반복하는 데는 더 많은 시간이 소요됩니다.

AI 워크스페이스를 사용하면 단 한 번의 요청으로 연산과 정렬을 모두 처리할 수 있습니다. 재고 또는 구매 내보내기 파일을 Powerdrill Bloom에 업로드하고, 자연어로 원하는 기준값에 따른 ABC 분석을 요청하십시오. 각 품목의 연간 가치, 비중, 누적 백분율, 등급과 함께 파레토 차트까지 요청할 수 있습니다.

그런 다음 일반 스프레드시트처럼 확인하십시오. 직접 계산한 합계와 총 연간 가치를 비교해 보고, 각 등급에서 두 개 정도의 품목을 무작위로 점검해 보십시오. 당사의 Excel AI assistant 페이지에서 이러한 스프레드시트 작업에 대해 더 자세히 다루고 있습니다. 예측 도구에 대한 더 넓은 시각을 원하신다면 재고 및 수요 예측을 위한 최적의 AI 도구 모음을 확인해 보십시오.

피해야 할 흔한 실수들

• 기간 혼용. 한 품목은 12개월, 다른 품목은 6개월을 기준으로 삼으면 비중 계산이 무의미해집니다.
• 가치 대신 수량 사용. 등급은 수량 자체가 아니라 '수량 × 단가'에 의해 결정됩니다.
• 누적 합계를 구하기 전에 정렬을 잊는 것. 정렬되지 않은 목록에서 누적 백분율을 계산하면 품목이 잘못된 등급으로 분류됩니다.
• 등급을 영구적인 것으로 취급. 품목은 등급 간에 이동하므로 매 분기 또는 매년 분석을 다시 실행하십시오.
• 역량을 무시한 기준값 설정. 밀착 관리하기에 너무 긴 A 등급 목록은 결국 B 등급과 다를 바 없는 수준의 관리만 받게 됩니다.
• 중요한 저가 품목 무시. 가치가 낮은 품목이라도 재고가 바닥나면 작업이 중단될 수 있습니다. MSH 장에서는 ABC 분석과 함께 필수적(vital), 중요(essential), 비필수적(nonessential) 품목을 별도로 평가하는 방식을 병행할 것을 권장합니다.

내보낸 품목 목록이 정리되지 않고 복잡하다면, Powerdrill Bloom을 사용하여 첫 번째 ABC 테이블과 차트를 만들어 볼 수 있습니다.

자주 묻는 질문 (FAQ)

재고 관리에서 ABC 분석이란 무엇인가요?

ABC 분석은 연간 소비 가치에 따라 품목을 세 가지 등급으로 분류합니다. A 등급 품목은 적은 수로 대부분의 가치를 차지하는 품목입니다. C 등급 품목은 많은 수로 적은 가치를 차지하는 품목이며, B 등급은 그 중간에 위치합니다. 이는 팀이 자금이 집중되는 곳에 통제 노력을 집중할 수 있도록 돕습니다.

Excel에서 ABC 분석은 어떻게 계산하나요?

각 품목의 연간 수량에 단가를 곱한 다음, 전체 합계로 나누어 각 품목의 비중을 구합니다. 가치가 큰 순서대로 정렬하고, 비중의 누적 합계를 추가한 후 IFS와 같은 수식을 사용하여 등급을 지정합니다. 80%와 95%의 기준값은 MSH 장에서 제시하는 일반적인 범위에 해당합니다.

ABC 분석의 백분율 기준은 어떻게 되나요?

일반적인 가이드라인에 따르면 A 등급은 품목의 10 ~ 20%를 차지하며 가치의 75 ~ 80%를 차지합니다. B 등급은 품목의 10 ~ 20%를 차지하며 가치의 15 ~ 20%를 차지합니다. C 등급은 품목의 60 ~ 80%를 차지하며 가치의 5 ~ 10%를 차지합니다.

Excel에서 ABC 분류를 위한 수식은 무엇인가요?

누적 백분율이 F열에 있고 데이터가 3행부터 시작하는 경우, =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")를 사용하십시오. 본인의 기준값에 맞게 0.8과 0.95를 변경할 수 있습니다. 중첩 IF 수식을 사용해도 동일한 작업을 수행할 수 있습니다.

ABC 분석이 왜 중요한가요?

재고 자금의 대부분이 어디에 쓰이는지 보여주므로, 팀이 해당 품목을 더 밀착 관리할 수 있습니다. 대표적인 활용법으로는 A 등급 품목을 더 자주 주문하고, 가격 협상을 우선적으로 진행하며, 재고 실사를 더 빈번하게 수행하는 것 등이 있습니다. 또한 계획과 일치하지 않는 지출을 감지하는 데도 도움이 됩니다.

출처: Management Sciences for Health, MDS-3 제40장: 의약품 지출 분석 및 통제 · Ravinder 및 Misra, 재고 관리를 위한 ABC 분석 (2014) · Microsoft 지원, SORT 함수 · Microsoft 지원, IFS 함수 · Microsoft 지원, 파레토 차트 만들기.