공급업체 평가 스코어카드 엑셀 템플릿
공급업체 평가는 양쪽이 확인할 수 있는 숫자가 있을 때 잘 돼요. 이 통합 문서는 모든 입고(약속일과 입고일, 주문·입고·불량 수량, 지급 단가와 계약 단가)를 기록하고, 고른 기간 동안 공급업체별 납기 준수율, 충족률, 불량률, 단가 차이를 만들어요. 각 지표는 목표와 하한 사이에서 0–100점이 되고, 점수에 가중치를 둬 하나로 합친 뒤 공급업체마다 등급과 상태를 매겨요.
- 시트:
- 6
- 지원 버전:
- Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
- 업데이트:
이 템플릿으로 할 수 있는 일
- 입고별 지연 일수, 납기 준수 표시(유예일 선택), 단가 차이를 담은 입고 기록
- SUMIFS·COUNTIFS로 원하는 기간의 공급업체별 납기 준수율, 충족률, 불량률, 단가 차이
- 지표별로 목표와 하한 사이 0–100점, 가중 합산, 히트맵
- 등급(A+ ~ D)과 Preferred / Approved / Probation 상태, 점수 차트
구성
통합 문서에는 시트가 6개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.
| 시트 | 용도 | 입력하는 것 | 자동 계산되는 것 |
|---|---|---|---|
| Start Here | 기간, 규칙, 결과 | 기간 시작·종료, 유예일, 최소 입고 건수, Preferred·Probation 기준 | 기간 내 입고, 전체 납기 준수율, 단가 초과 지급액, Preferred·Probation 수, 최고·최저 업체 |
| Weights | 목표, 하한, 가중치 | 지표별 목표와 하한, 가중치, 등급 구간 | 가중치 합계 |
| Deliveries | 입고 기록(400) | PO 날짜, 공급업체, PO, 약속일, 입고일, 수량, 지급 단가, 계약 단가 | 지연 일수, 기간 포함, 납기 준수, 단가 차이, 계약가 기준 금액 |
| Scorecard | 공급업체별 한 행(20) | 공급업체, 분류, 대응성 평가 1–5 | 입고 건수, 납기 준수율, 충족률, 불량률, 단가 차이, 점수 5개, 가중 점수, 등급, 상태 |
| Lists | 분류 | 공급업체 분류 | 없음 |
| Checks | 데이터 검사 6개 | 없음 | OK 또는 Review |
시작하기 전에
- 구매 시스템에서 입고 내역을 내보내세요: PO, 공급업체, 약속일, 입고일, 수량, 단가.
- 팀(가능하면 공급업체와도)과 목표를 합의하세요: 어느 수준의 납기와 불량이 우수이고 어느 수준이 불가인지.
- 대응성은 누가 얼마나 자주 평가할지 정하세요. 유일하게 판단이 들어가는 점수예요.
단계별 사용법
기간 선택
Start Here B6, B7: 이 사이에 입고된 건을 평가해요. 유예일(B8), 공정한 점수를 위한 최소 입고 건수(B9), Preferred·Probation 기준(B10, B11)을 정하세요.
목표와 가중치 설정
Weights 탭: 지표마다 100점이 되는 값과 0점이 되는 값, 반영 비중. 가중치 합은 100%여야 해요.
공급업체 목록
Scorecard A, B, I열: 이름, 분류, 1–5 대응성 평가.
모든 입고 기록
Deliveries 탭: 입고마다 한 행. 미입고 주문은 입고일을 비워 두면 도착할 때까지 무시돼요.
스코어카드 읽기
E–H열에 측정 지표, J–N열에 점수, O–Q열에 가중 점수, 등급, 상태.
공급업체와 검토
공급업체별 행을 공유하세요. 그 업체로 필터한 Deliveries 탭이 근거예요.
수식 지도
통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.
| 시트 | 셀 | 함수 | 계산 내용 | 읽는 값 | 쓰이는 곳 |
|---|---|---|---|---|---|
| Deliveries | L5 404행까지 아래로 복사됨 | IF, AND | 평가 기간 포함 여부 | E5 | 스코어카드의 모든 SUMIFS가 조건으로 써서, 기간을 바꾸면 전부 다시 평가돼요. |
| Deliveries | M5 404행까지 아래로 복사됨 | IF | 납기 준수 | E5, D5 | 공급업체별로 더해 준수 건수가 돼요. |
| Deliveries | N5 404행까지 아래로 복사됨 | IF | 단가 차이 금액 | I5, J5, G5 | 공급업체별로 더해 계약가 기준 금액으로 나누면 단가 차이 %예요. |
| Scorecard | C5 24행까지 아래로 복사됨 | IF, COUNTIFS | 기간 내 입고 건수 | A5 | Start Here B9 최소치보다 적으면 "Not enough data". |
| Scorecard | F5 24행까지 아래로 복사됨 | IF, OR, SUMIFS | 충족률 | C5 | G5(불량 ÷ 입고)와 H5(차이 ÷ 계약가 금액)도 같은 방식이에요. |
| Scorecard | J5 24행까지 아래로 복사됨 | IF, OR, ROUND, MAX, MIN | 지표 점수 0–100 | E5 | K5–N5도 충족률, 불량, 단가, 대응성에 같은 방식이에요. 불량과 단가는 목표가 하한보다 낮지만 같은 공식이 그대로 통해요. |
| Scorecard | O5 24행까지 아래로 복사됨 | IF, OR, ROUND | 가중 점수 | J5, K5, L5, M5, N5 | 등급, 상태, 차트를 움직여요. |
| Scorecard | P5 24행까지 아래로 복사됨 | IF, LOOKUP | 등급 | O5 | 상태 옆에 표시돼요. |
| Scorecard | Q5 24행까지 아래로 복사됨 | IF | 상태 | O5 | Start Here B18, B19에서 셉니다. |
| Start Here | B21 | IFERROR, INDEX, MATCH, MINIFS | 최저 점수 공급업체 | — | 가장 먼저 대화할 상대예요. |
모든 수식 설명
아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.
=IF(E5="", 0, IF(AND(E5>='Start Here'!$B$6, E5<='Start Here'!$B$7),
1, 0))입고일이 기간 시작과 종료 사이면 1, 아니면 0(미입고 포함).
부분별 설명
E5=""- 아직 미입고: 0.
AND(E5>='Start Here'!$B$6, E5<='Start Here'!$B$7)- 기간 안.
예시 데이터로 보면: E5 = 2026년 1월 20일 → L5 결과 0
어떻게 쓰이나: 스코어카드의 모든 SUMIFS가 조건으로 써서, 기간을 바꾸면 전부 다시 평가돼요.
바꾸는 방법: 날짜는 Start Here에서.
배우기: 엑셀 AND 함수와 OR 함수 사용법 →=IF(E5="", "", IF(E5<=D5+'Start Here'!$B$8, 1, 0))약속일 + 유예일 이내에 입고되면 1, 아니면 0.
부분별 설명
E5<=D5+'Start Here'!$B$8- 약속일(+유예) 이내 입고.
예시 데이터로 보면: E5 = 2026년 1월 20일, D5 = 2026년 1월 20일 → M5 결과 1
어떻게 쓰이나: 공급업체별로 더해 준수 건수가 돼요.
바꾸는 방법: 유예일은 Start Here B8(예: 다음 날 아침 입고면 1).
배우기: 엑셀 IF 함수 사용법 →=IF(E5="", "", (I5-J5)*G5)입고 수량에 대해 계약 단가보다 더(또는 덜) 낸 금액.
부분별 설명
(I5-J5)- 단위당 지급 단가 − 계약 단가.
*G5- 입고 수량을 곱함.
예시 데이터로 보면: I5 = 50.57, J5 = 51.48, G5 = 200 → N5 결과 -182
어떻게 쓰이나: 공급업체별로 더해 계약가 기준 금액으로 나누면 단가 차이 %예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →=IF(A5="", "", COUNTIFS(Deliveries!$B$5:$B$404,
A5, Deliveries!$L$5:$L$404,
1))이 공급업체의 기간 내 입고를 셉니다.
부분별 설명
Deliveries!$B$5:$B$404, A5- 공급업체 일치.
Deliveries!$L$5:$L$404, 1- 기간 내.
예시 데이터로 보면: A5 = Apex Fasteners → C5 결과 20
어떻게 쓰이나: Start Here B9 최소치보다 적으면 "Not enough data".
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →=IF(OR(A5="", C5<'Start Here'!$B$9),
"", SUMIFS(Deliveries!$G$5:$G$404,
Deliveries!$B$5:$B$404, A5,
Deliveries!$L$5:$L$404, 1)/SUMIFS(Deliveries!$F$5:$F$404,
Deliveries!$B$5:$B$404, A5,
Deliveries!$L$5:$L$404, 1))기간 내 이 공급업체 입고의 입고 수량 ÷ 주문 수량.
부분별 설명
SUMIFS(Deliveries!$G$5:$G$404, …)- 입고 수량.
/SUMIFS(Deliveries!$F$5:$F$404, …)- 주문 수량.
OR(A5="", C5<'Start Here'!$B$9)- 입고가 너무 적으면 빈칸.
예시 데이터로 보면: C5 = 20 → F5 결과 99.1%
어떻게 쓰이나: G5(불량 ÷ 입고)와 H5(차이 ÷ 계약가 금액)도 같은 방식이에요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IF(OR(A5="", E5=""), "",
ROUND(MAX(0, MIN(1, (E5-Weights!$C$5)/(Weights!$B$5-Weights!$C$5)))*100,
0))측정값을 하한(0점)과 목표(100점) 사이의 직선 위에 놓고 양 끝을 자릅니다.
부분별 설명
(E5-Weights!$C$5)- 하한 위로 떨어진 거리.
/(Weights!$B$5-Weights!$C$5)- 하한–목표 범위 대비 비율.
MAX(0, MIN(1, …))- 0 미만, 1 초과 없음.
ROUND(…*100, 0)- 정수 점수.
예시 데이터로 보면: E5 = 95.0% → J5 결과 100
어떻게 쓰이나: K5–N5도 충족률, 불량, 단가, 대응성에 같은 방식이에요. 불량과 단가는 목표가 하한보다 낮지만 같은 공식이 그대로 통해요.
바꾸는 방법: 목표와 하한은 Weights 탭에서.
배우기: 엑셀 MIN, MAX 함수 사용법 →=IF(OR(A5="", J5=""), "",
ROUND(J5*Weights!$D$5+K5*Weights!$D$6+L5*Weights!$D$7+M5*Weights!$D$8+N5*Weights!$D$9,
1))지표 점수마다 가중치를 곱해 더합니다.
부분별 설명
J5*Weights!$D$5- 납기 × 30%.
+K5*Weights!$D$6 …- 충족 20%, 품질 25%, 단가 15%, 대응성 10%.
ROUND(…, 1)- 소수 한 자리.
예시 데이터로 보면: J5 = 100, K5 = 100, L5 = 100, M5 = 92, N5 = 100 → O5 결과 98.8
어떻게 쓰이나: 등급, 상태, 차트를 움직여요.
바꾸는 방법: 가중치는 Weights 탭에서.
배우기: 엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기 →=IF(O5="", "", LOOKUP(O5,
Weights!$A$13:$A$17, Weights!$B$13:$B$17))근사 일치 LOOKUP: 점수를 넘지 않는 가장 큰 구간 시작값을 찾아 등급을 돌려줍니다.
부분별 설명
LOOKUP(O5, Weights!$A$13:$A$17, Weights!$B$13:$B$17)- 0 D, 60 C, 70 B, 80 A, 90 A+.
예시 데이터로 보면: O5 = 98.8 → P5 결과 A+
어떻게 쓰이나: 상태 옆에 표시돼요.
바꾸는 방법: Weights 탭에서 구간을 고치세요(오름차순 유지).
배우기: 엑셀 조회 수식: XLOOKUP, VLOOKUP, INDEX/MATCH 사용법 →=IF(A5="", "", IF(O5="", "Not enough data",
IF(O5>='Start Here'!$B$10, "Preferred", IF(O5<'Start Here'!$B$11, "Probation", "Approved"))))점수가 없으면 Not enough data, Preferred 기준 이상이면 Preferred, Probation 기준 미만이면 Probation, 아니면 Approved.
부분별 설명
O5=""- 입고가 너무 적음.
O5>='Start Here'!$B$10- Preferred(85).
O5<'Start Here'!$B$11- Probation(70).
예시 데이터로 보면: O5 = 98.8 → Q5 결과 Preferred
어떻게 쓰이나: Start Here B18, B19에서 셉니다.
바꾸는 방법: 기준은 Start Here에서.
배우기: 엑셀 IF 함수 사용법 →=IFERROR(INDEX(Scorecard!$A$5:$A$24,
MATCH(MINIFS(Scorecard!$O$5:$O$24, Scorecard!$O$5:$O$24, ">0"), Scorecard!$O$5:$O$24, 0)),
"")0보다 큰 최저 점수(평가 안 된 업체 제외)를 찾아 그 업체를 돌려줍니다.
부분별 설명
MINIFS(Scorecard!$O$5:$O$24, Scorecard!$O$5:$O$24, ">0")- 실제 최저 점수.
MATCH(…, 0)- 그 행.
INDEX(Scorecard!$A$5:$A$24, …)- 공급업체 이름.
예시 데이터로 보면: B21 결과 Granite Castings
어떻게 쓰이나: 가장 먼저 대화할 상대예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →팁과 한계
- 용량: 공급업체 20곳, 입고 400건. 더 필요하면 Ctrl+H($404)로 범위를 늘리세요.
- 나쁜 한 달이 업체를 규정하지 않도록 최근 6개월이나 12개월 같은 이동 기간으로 평가하세요.
- 분할 입고는 입고마다 한 행으로 쓰되 주문 수량은 첫 행에만 넣어야 충족률이 낮게 나오지 않아요.
- 여기서 불량률은 불량 ÷ 입고 수량이에요. PPM으로 관리한다면 1,000,000을 곱하세요.
- 평가하기 전에 목표를 공급업체와 공유하세요. 스코어카드는 함께 개선하는 도구일 때 가장 잘 통해요.
이 템플릿에 쓰인 기능 배우기
도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com