무료 안전 재고·재주문점 엑셀 계산기: 서비스 수준, 수요·리드타임 변동, EOQ (수식 설명 포함)

구성

통합 문서에는 시트가 5개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.

시트	용도	입력하는 것	자동 계산되는 것
Start Here	설정과 주문 요약	서비스 수준, 주문당 비용, 보관 비용 %, 연간 근무일	z값, 분석 품목 수, 지금 주문할 품목, 안전 재고 미만, 권장 주문 금액, 안전 재고 금액, 현재 재고 금액, 최저 재고 일수
History	일일 수요 90일 × 20개 품목	날짜, 품목별 수량 열	없음
SKUs	품목별 한 행(20)	품목 코드, 설명, 리드타임과 변동, 단가, 선택적 서비스 수준, 현재 재고, 입고 예정	수요 평균과 편차, z, 리드타임 수요, 안전 재고, 재주문점, 연간 수요, EOQ, 재고 일수, 상태, 권장 주문량
Lists	서비스 수준	자주 쓰는 서비스 수준	없음
Checks	데이터 검사 6개	없음	OK 또는 Review

시작하기 전에

최근 90일 품목별 일일 판매·사용량을 내보내세요(수요 없는 날은 빈칸이 아니라 0).
발주서에서 리드타임을 재세요: 주문부터 입고까지 평균 일수와 보통 며칠씩 달라지는지.
생산을 멈추거나 고객을 잃게 하는 품목은 서비스 수준을 더 높일지 정하세요.

단계별 사용법

서비스 수준과 비용 설정
Start Here B6–B10: 서비스 수준(95%는 보충 주기 20번에 한 번쯤 품절), 주문 한 번 비용, 품목 가치 대비 연간 보관 비용 %, 연간 근무일.
수요 이력 붙여넣기
History 탭: A열에 날짜, 4행에 품목 코드, 그 아래 일일 수량. SKUs 탭이 코드로 각 품목 열을 찾아요.
품목 정보 입력
SKUs 탭: History 머리글과 같은 품목 코드, 설명, 평균 리드타임과 표준편차(일), 단가, 필요하면 I열에 더 높은 서비스 수준.
재고 위치 입력
Q열 현재 재고, R열 이미 주문한 수량.
상태 읽기
T열: Below safety stock(긴급), Order now(재주문점 이하), OK. U열이 주문 수량이에요.
주문하고 반복
주문 후 수량을 입고 예정에 더하고, 매주나 매월 이력을 새로 고치세요.

수식 지도

통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.

시트	셀	함수	계산 내용	읽는 값	쓰이는 곳
Start Here	`B7`	NORM.S.INV	서비스 수준의 z값	B6	별도 지정이 없는 모든 품목에 쓰여요.
SKUs	`D5` 24행까지 아래로 복사됨	IF, IFERROR, AVERAGE, INDEX, MATCH	일평균 수요	A5	리드타임 수요, 연간 수요, 재고 일수에 모두 쓰여요.
SKUs	`E5` 24행까지 아래로 복사됨	IF, IFERROR, STDEV, INDEX, MATCH	수요 변동	D5	안전 재고 공식의 수요 부분이에요.
SKUs	`M5` 24행까지 아래로 복사됨	IF, ROUNDUP, SQRT	안전 재고	K5, F5, E5, D5, G5	재주문점의 일부이고, 재고가 이보다 적으면 긴급이에요.
SKUs	`N5` 24행까지 아래로 복사됨	IF, ROUNDUP	재주문점	L5, M5	상태 열의 기준이에요.
SKUs	`P5` 24행까지 아래로 복사됨	IF, OR, ROUNDUP, SQRT	경제적 주문량(EOQ)	O5, H5	권장 주문의 최소량이에요.
SKUs	`T5` 24행까지 아래로 복사됨	IF	재고 상태	Q5, R5, M5, N5	색이 칠해지고 Start Here에서 셉니다.
SKUs	`U5` 24행까지 아래로 복사됨	IF, OR, MAX	권장 주문량	T5, P5, N5, M5, Q5, R5	Start Here B17에서 금액으로 합산해요.
Start Here	`B17`	SUMPRODUCT, IFERROR	권장 주문 금액	—	이번 구매에 필요한 현금이에요.

모든 수식 설명

아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.

서비스 수준의 z값Start Here!B7

=NORM.S.INV(B6)

표준정규분포의 역함수로 서비스 수준을 필요한 완충 표준편차 개수로 바꿉니다.

부분별 설명

NORM.S.INV(B6): 95% → 1.645, 99% → 2.326, 90% → 1.282.

예시 데이터로 보면: B6 = 95.0% → B7 결과 1.64

어떻게 쓰이나: 별도 지정이 없는 모든 품목에 쓰여요.

바꾸는 방법: B6을 바꾸면 z가 따라와요.

배우기: 엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법 →

일평균 수요SKUs!D5 · 24행까지 아래로 복사됨

=IF(A5="", "", IFERROR(AVERAGE(INDEX(History!$B$5:$U$94, 0, MATCH(A5, History!$B$4:$U$4, 0))),
    0))

History 시트에서 코드로 이 품목 열을 찾아 평균을 냅니다.

부분별 설명

MATCH(A5, History!$B$4:$U$4, 0): 이 품목이 있는 열.
INDEX(History!$B$5:$U$94, 0, …): 행 번호 0은 열 전체를 돌려줘요.
AVERAGE(…): 하루 평균 수량.
IFERROR(…, 0): 이력에 코드가 없으면 0.

예시 데이터로 보면: A5 = BRG-6204 → D5 결과 40.36

어떻게 쓰이나: 리드타임 수요, 연간 수요, 재고 일수에 모두 쓰여요.

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

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

수요 변동SKUs!E5 · 24행까지 아래로 복사됨

=IF(A5="", "", IFERROR(STDEV(INDEX(History!$B$5:$U$94, 0, MATCH(A5, History!$B$4:$U$4, 0))),
    0))

같은 열의 표준편차: 보통의 하루가 평균에서 얼마나 벗어나는지.

부분별 설명

STDEV(INDEX(…, 0, MATCH(…))): 일일 수요의 표본 표준편차.

예시 데이터로 보면: D5 = 40.36 → E5 결과 13.57

어떻게 쓰이나: 안전 재고 공식의 수요 부분이에요.

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

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

안전 재고SKUs!M5 · 24행까지 아래로 복사됨

=IF(A5="", "", ROUNDUP(K5*SQRT(F5*E5^2+D5^2*G5^2), 0))

리드타임 동안의 수요 변동과 늦은 입고를 모두 덮는 완충: z × 결합 표준편차, 정수로 올림.

부분별 설명

F5*E5^2: 평균 리드타임 동안의 수요 분산.
D5^2*G5^2: 주문이 늦어지는 동안의 추가 수요.
SQRT(…): 리드타임 수요의 결합 표준편차.
K5*…: 서비스 수준의 z를 곱함.
ROUNDUP(…, 0): 모자라지 않게 정수로.

예시 데이터로 보면: K5 = 1.64, F5 = 7, E5 = 13.57, D5 = 40.36, G5 = 1.5 → M5 결과 116

어떻게 쓰이나: 재주문점의 일부이고, 재고가 이보다 적으면 긴급이에요.

바꾸는 방법: 리드타임이 변하지 않으면 G5를 0으로 두세요. 그러면 z × σ × √LT가 돼요.

배우기: 엑셀 반올림, 올림, 내림 함수: ROUND, ROUNDUP, ROUNDDOWN 사용법 →

재주문점SKUs!N5 · 24행까지 아래로 복사됨

=IF(A5="", "", ROUNDUP(L5+M5, 0))

리드타임 동안의 예상 수요 + 안전 재고. 현재 재고와 입고 예정의 합이 이 수준까지 떨어지면 주문하세요.

부분별 설명

L5: 일평균 수요 × 평균 리드타임.
+M5: 안전 재고.

예시 데이터로 보면: L5 = 282.49, M5 = 116 → N5 결과 399

어떻게 쓰이나: 상태 열의 기준이에요.

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

배우기: 엑셀 반올림, 올림, 내림 함수: ROUND, ROUNDUP, ROUNDDOWN 사용법 →

경제적 주문량(EOQ)SKUs!P5 · 24행까지 아래로 복사됨

=IF(OR(A5="", H5=0), "", ROUNDUP(SQRT(2*O5*'Start Here'!$B$8/(H5*'Start Here'!$B$9)),
    0))

주문 비용과 보관 비용이 균형을 이루는 주문 크기: √(2 × 연간 수요 × 주문당 비용 ÷ 단위당 연간 보관 비용).

부분별 설명

O5: 연간 수요(일일 × 근무일).
'Start Here'!$B$8: 주문당 비용.
H5*'Start Here'!$B$9: 단위당 연간 보관 비용.
SQRT(2*…/…): EOQ 공식.

예시 데이터로 보면: O5 = 10,088.89, H5 = 3.1 → P5 결과 1,083

어떻게 쓰이나: 권장 주문의 최소량이에요.

바꾸는 방법: 읽은 뒤 공급업체의 포장·팔레트 단위로 맞추세요.

배우기: 엑셀 반올림, 올림, 내림 함수: ROUND, ROUNDUP, ROUNDDOWN 사용법 →

재고 상태SKUs!T5 · 24행까지 아래로 복사됨

=IF(A5="", "", IF(Q5<M5, "Below safety stock",
    IF(Q5+R5<=N5, "Order now", "OK")))

현재 재고만으로 완충보다 적으면 Below safety stock, 현재 재고 + 입고 예정이 재주문점 이하면 Order now, 아니면 OK.

부분별 설명

Q5<M5: 이미 완충을 쓰는 중.
Q5+R5<=N5: 재고 위치가 기준에 도달.

예시 데이터로 보면: Q5 = 520, R5 = 0, M5 = 116, N5 = 399 → T5 결과 OK

어떻게 쓰이나: 색이 칠해지고 Start Here에서 셉니다.

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

배우기: 엑셀 IF 함수 사용법 →

권장 주문량SKUs!U5 · 24행까지 아래로 복사됨

=IF(OR(A5="", T5="OK"), 0, MAX(P5, N5+M5-Q5-R5))

재고 위치를 재주문점 + 안전 재고 여유 위로 되돌릴 만큼 주문하되, EOQ보다 적게는 주문하지 않습니다.

부분별 설명

T5="OK": 주문 없음: 0.
N5+M5-Q5-R5: 재주문점까지의 부족분 + 완충.
MAX(P5, …): 최소 한 번의 경제적 주문.

예시 데이터로 보면: T5 = OK, P5 = 1,083, N5 = 399, M5 = 116, Q5 = 520, R5 = 0 → U5 결과 0

어떻게 쓰이나: Start Here B17에서 금액으로 합산해요.

바꾸는 방법: 목표 재고(order-up-to) 규칙을 쓴다면 그것으로 바꾸세요.

배우기: 엑셀 MIN, MAX 함수 사용법 →

권장 주문 금액Start Here!B17

=SUMPRODUCT(IFERROR(SKUs!$U$5:$U$24*SKUs!$H$5:$H$24, 0))

권장 수량마다 단가를 곱해 더합니다.

부분별 설명

SKUs!$U$5:$U$24*SKUs!$H$5:$H$24: 행별 수량 × 단가.
IFERROR(…, 0): 빈 행 무시.
SUMPRODUCT(…): 곱한 값을 더함.

예시 데이터로 보면: B17 결과 31,377

어떻게 쓰이나: 이번 구매에 필요한 현금이에요.

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

배우기: 엑셀 IFERROR 함수 사용법 →

팁과 한계

용량: 품목 20개, 이력 90일. 품목을 늘리려면 SKUs 표 안쪽에 행을, History B:U 안쪽에 열을 삽입하세요.
이력은 최소 60–90일을 쓰세요. 날이 적으면 표준편차를 믿기 어려워요. 반복되지 않을 일회성 대량 주문은 빼세요.
공식은 수요가 대략 정규분포라고 가정해요. 한 달에 몇 개 나가는 아주 느린 품목은 판단으로 최소량을 정하세요.
99% 서비스 수준은 95%보다 재고가 훨씬 많이 들어요. 전체를 올리기 전에 Start Here의 안전 재고 금액을 비교하세요.
리드타임 변동이 더 큰 원인인 경우가 많아요. 공급업체 견적이 아니라 실제 입고 기록으로 재세요.

도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com

안전 재고·재주문점 엑셀 계산기

이 템플릿으로 할 수 있는 일

구성

시작하기 전에

단계별 사용법

서비스 수준과 비용 설정

수요 이력 붙여넣기

품목 정보 입력

재고 위치 입력

상태 읽기

주문하고 반복

수식 지도

모든 수식 설명

팁과 한계

이 템플릿에 쓰인 기능 배우기