ABC 재고 분석 엑셀 템플릿

연간 수량 × 단가: 1년 동안 이 품목의 가치.

부분별 설명

D5*E5

수량 × 단가.

IF(A5="", "", …)

빈 행은 빈칸.

예시 데이터로 보면: D5 = 38, E5 = 9.95 → F5 결과 378.1

어떻게 쓰이나: 나머지는 모두 이 금액으로 순위를 매기고 더해요.

바꾸는 방법: 판매가 매출이 아니라 원가 기준 사용 금액을 쓰세요.

금액을 큰 순서로 순위 매기고, 앞 행에 같은 금액이 몇 개 있었는지 더해 동점을 나눕니다.

부분별 설명

RANK(F5, $F$5:$F$304, 0)

금액 순위, 1 = 최대.

+COUNTIF($F$5:F5, F5)-1

늘어나는 범위: 동점 첫째는 0, 둘째는 1….

예시 데이터로 보면: F5 = 378.1 → G5 결과 140

어떻게 쓰이나: 누적 비중과 Pareto 탭은 순위가 모두 달라야 해요.

바꾸는 방법: 바꿀 것 없음.

이 품목 이상 순위인 모든 품목 금액을 더해 합계로 나눕니다.

부분별 설명

SUMIF($G$5:$G$304, "<="&G5, $F$5:$F$304)

1위부터 이 순위까지의 금액.

/'Start Here'!$B$16

총 금액으로 나눔.

예시 데이터로 보면: G5 = 140 → I5 결과 98.9%

어떻게 쓰이나: 등급은 이 값으로 정해요.

바꾸는 방법: 바꿀 것 없음.

이 품목 직전까지의 비중(누적 − 자기 비중)을 써서 80% 선을 넘는 품목도 A로 둡니다.

부분별 설명

I5-H5

위 순위 품목들의 누적 비중.

<'Start Here'!$B$6

80% 미만: A.

<'Start Here'!$B$7

95% 미만: B.

"C"

나머지.

예시 데이터로 보면: I5 = 98.9%, H5 = 0.1% → J5 결과 C

어떻게 쓰이나: 등급별로 색이 칠해지고 Summary에서 세고 더해요.

바꾸는 방법: 기준은 Start Here에서.

A 등급 품목의 연간 금액을 모두 더합니다(6행은 B, 7행은 C).

부분별 설명

Items!$J$5:$J$304, A5

등급 일치.

Items!$F$5:$F$304

더할 금액.

예시 데이터로 보면: D5 결과 528,002.57

어떻게 쓰이나: 합계로 나눠 금액 비중을 구해요.

바꾸는 방법: 바꿀 것 없음.

등급의 품목 수 × 품목당 연간 실사 횟수(365 ÷ 간격).

부분별 설명

B5

A 등급 품목 수.

F5 = 365/'Start Here'!$B$8

품목당 연간 횟수.

예시 데이터로 보면: B5 = 43, F5 = 12.17 → G5 결과 523.17

어떻게 쓰이나: 합계 ÷ 실사일이 Start Here B21이에요.

바꾸는 방법: 간격은 Start Here에서.

이 분류이면서 A 등급인 품목을 셉니다.

부분별 설명

Items!$C$5:$C$304, A11

분류 일치.

Items!$J$5:$J$304, "A"

A 등급(D11, E11은 B와 C).

예시 데이터로 보면: C11 결과 2

어떻게 쓰이나: 고가 품목이 어느 분류에 몰려 있는지 보여줘요.

바꾸는 방법: 바꿀 것 없음.

순위 1(2, 3 …)인 품목을 찾아 코드를 돌려줍니다.

부분별 설명

MATCH(A5, Items!$G$5:$G$304, 0)

이 순위 품목의 행.

INDEX(Items!$A$5:$A$304, …)

품목 코드.

IFERROR(…, "")

마지막 품목 뒤로는 빈칸.

예시 데이터로 보면: B5 결과 SKU-1108

어떻게 쓰이나: C–E열도 같은 방식으로 금액, 누적 비중, 등급을 순위 순서로 가져와 곡선을 그려요.

바꾸는 방법: 바꿀 것 없음.

C 품목의 재고 금액을 모두 더합니다. 줄일 만한 느리고 저가인 재고예요.

부분별 설명

Items!$J$5:$J$304, "C"

C 등급.

Items!$K$5:$K$304

재고 금액.

예시 데이터로 보면: B22 결과 2,644.63

어떻게 쓰이나: 과잉 재고 점검의 출발점이에요.

바꾸는 방법: "C"를 "A"로 바꾸면 A 품목에 묶인 금액을 볼 수 있어요.