엑셀 재고관리 방법 총정리｜안전재고 계산법부터 재고관리표 만들기까지

엑셀 재고관리 방법 총정리｜안전재고 계산법부터 재고관리표 만들기까지

재고관리는 제조업과 유통업에서 가장 중요한 업무 중 하나입니다. 재고가 부족하면 생산이나 판매에 차질이 발생하고, 반대로 재고가 너무 많으면 불필요한 비용이 발생합니다.

특히 자재관리나 구매업무를 처음 담당한다면 어떤 항목부터 관리해야 할지 막막할 수 있습니다.

이번 글에서는 엑셀을 활용한 재고관리 방법부터 안전재고 계산법, 재고일수 관리, 발주 시점 판단까지 실무에 필요한 내용을 쉽게 정리하겠습니다.

1. 재고관리가 중요한 이유

재고관리는 단순히 창고에 보관된 제품의 수량을 확인하는 업무가 아닙니다.

현재 재고와 앞으로 필요한 수량을 비교하고, 적절한 시점에 필요한 물량을 확보하는 활동입니다.

재고관리가 제대로 이루어지지 않으면 다음과 같은 문제가 발생할 수 있습니다.

- 재고 부족에 따른 생산 및 납품 차질
- 긴급 발주와 운송으로 인한 추가 비용
- 과잉재고에 따른 보관 비용 증가
- 장기재고 및 불용재고 발생
- 공급업체 납기 지연에 대한 대응 부족

따라서 효율적인 재고관리를 위해서는 현재 재고뿐 아니라 미래의 수요와 입고계획까지 함께 관리해야 합니다.

2. 엑셀 재고관리표에 필요한 필수 항목

엑셀로 재고관리표를 만들 때는 다음 항목을 기본적으로 포함하는 것이 좋습니다.

관리 항목| 주요 내용
자재코드| 품목별 고유 관리번호
자재명| 제품 또는 자재 이름
현재고| 현재 보유하고 있는 재고
일평균 사용량| 하루 평균 소비 수량
입고예정량| 향후 입고될 예정 수량
리드타임(LT)| 발주부터 입고까지 소요 기간
안전재고| 안정적인 공급을 위해 확보할 재고
재고일수| 현재 재고로 사용 가능한 기간

이 항목들을 하나의 관리표에 구성하면 품목별 재고 상태를 한눈에 확인할 수 있습니다.

특히 재고일수와 안전재고를 함께 관리하면 자재 부족 위험을 빠르게 파악할 수 있습니다.

3. 안전재고란 무엇일까?

안전재고는 예상보다 수요가 증가하거나 공급업체 납기가 지연되는 상황에 대비하기 위해 추가로 보유하는 재고입니다.

예를 들어 하루 평균 100개를 사용하는 자재가 있다고 가정하겠습니다.

회사의 안전재고 기준이 14일이라면 다음과 같이 계산할 수 있습니다.

안전재고 = 일평균 사용량 × 안전재고 기준일수

100개 × 14일 = 1,400개

따라서 해당 자재는 1,400개를 안전재고 기준으로 관리할 수 있습니다.

엑셀에서는 다음과 같이 계산합니다.

"=D2*E2"

D2에 일평균 사용량, E2에 안전재고 기준일수가 입력되어 있다는 가정입니다.

다만 이는 일정한 사용량을 가정한 간단한 계산법입니다. 실제 운영에서는 수요 변동성과 공급업체의 납기 안정성도 함께 고려해야 합니다.

4. 재고일수 계산 방법

재고일수는 현재 보유한 재고로 며칠 동안 사용할 수 있는지를 나타내는 지표입니다.

재고일수 = 현재고 ÷ 일평균 사용량

예를 들어 현재고 2,000개, 일평균 사용량 100개라면 재고일수는 20일입니다.

엑셀에서는 다음과 같이 계산할 수 있습니다.

"=IFERROR(C2/D2,0)"

C2는 현재고, D2는 일평균 사용량입니다.

단, 이 수식은 일평균 사용량이 일정하다는 전제로 계산합니다. 사용량이 0인 품목은 별도 표시하는 것이 좋습니다.

또한 현재고 기준 재고일수에는 미래 입고예정량이 반영되지 않습니다.

따라서 실제 부족 발생 시점을 판단할 때는 일자별 또는 주차별 입고계획과 사용계획을 함께 확인해야 합니다.

5. 적절한 발주 시점은 어떻게 판단할까?

안전재고와 함께 알아두면 좋은 개념이 재주문점(ROP)입니다.

재주문점은 재고가 일정 수준에 도달하면 새로운 발주를 검토해야 하는 기준입니다.

기본 계산 방법은 다음과 같습니다.

재주문점 = 리드타임 동안 예상 사용량 + 안전재고

하루 평균 100개를 사용하고 공급업체 리드타임이 10일, 안전재고가 500개인 경우를 살펴보겠습니다.

- 리드타임 예상 사용량: 100개 × 10일 = 1,000개
- 안전재고: 500개
- 재주문점: 1,500개

이 경우 재고가 1,500개 수준에 도달하기 전에 신규 발주를 검토해야 합니다.

다만 기존 발주잔량과 입고예정일이 있는 경우에는 이를 함께 고려해야 중복 발주를 방지할 수 있습니다.

6. 실무에서 활용하기 좋은 엑셀 관리 방법

엑셀 재고관리표를 만들었다면 단순한 수량 입력에서 끝내지 말고 부족 위험을 자동으로 확인할 수 있도록 설정하는 것이 좋습니다.

대표적인 방법이 조건부 서식입니다.

예를 들어 다음과 같이 재고일수에 따라 색상을 구분할 수 있습니다.

- 7일 미만: 빨간색 — 긴급 확인 필요
- 7일 이상 14일 미만: 노란색 — 주의
- 14일 이상: 초록색 — 기준 충족

위 기준은 예시이며 실제 기준은 품목별 리드타임과 회사의 안전재고 정책에 따라 달라집니다.

이렇게 설정하면 여러 품목을 관리하는 경우에도 위험 자재를 빠르게 식별할 수 있습니다.

추가로 엑셀의 필터, 피벗테이블, SUMIFS 함수를 활용하면 품목별 입출고 현황과 공급업체별 재고 수준도 편리하게 관리할 수 있습니다.

7. 재고관리에서 가장 중요한 것은 미래 재고 예측

재고관리 업무에서 주의해야 할 부분은 현재 재고가 충분하다고 해서 앞으로도 문제가 없다는 보장은 없다는 점입니다.

예를 들어 현재 재고가 20일분 있더라도 다음 입고가 30일 뒤라면 자재 부족 가능성이 있습니다.

반대로 현재 재고가 5일분에 불과하더라도 이틀 뒤 충분한 물량이 입고된다면 실제 생산 차질은 발생하지 않을 수 있습니다.

따라서 정확한 재고관리를 위해서는 다음 세 가지 정보를 함께 확인해야 합니다.

1. 현재 보유하고 있는 재고
2. 앞으로 사용해야 하는 수량
3. 향후 입고될 물량과 일정

이 데이터를 일자별 또는 주차별로 관리하면 자재 부족 시점을 사전에 예측할 수 있습니다.

마무리

엑셀을 활용한 재고관리는 복잡한 시스템 없이도 충분히 시작할 수 있습니다.

처음부터 모든 기능을 구현하기보다 현재고, 일평균 사용량, 안전재고, 재고일수부터 관리하는 것이 효과적입니다.

이후 업무에 익숙해지면 발주잔량, 입고예정일, 주차별 수요계획 등을 추가하여 더욱 체계적인 재고관리표로 발전시킬 수 있습니다.

가장 중요한 것은 데이터를 꾸준히 업데이트하고 부족 위험을 사전에 발견할 수 있는 관리체계를 만드는 것입니다.

재고관리의 목표는 재고를 무조건 많이 확보하는 것이 아니라, 필요한 자재를 필요한 시점에 적정한 수량으로 준비하는 것입니다.