온라인 쇼핑몰이나 소규모 판매업체를 운영하다 보면 잘 팔리는 상품은 갑자기 품절되고, 판매가 부진한 상품은 창고에 쌓이는 문제가 발생합니다. 재고가 부족하면 판매 기회를 놓치고, 반대로 너무 많이 확보하면 자금이 재고에 묶이게 됩니다.
이러한 문제를 줄이려면 상품별 판매량과 입고 소요 기간을 기준으로 안전재고를 설정하고, 발주가 필요한 시점과 수량을 계산해야 합니다.
엑셀과 챗GPT를 활용하면 복잡한 재고관리 프로그램 없이도 상품별 안전재고, 발주점, 발주 필요 수량을 자동으로 계산하는 관리표를 만들 수 있습니다.
1. 안전재고와 발주점은 무엇이 다를까?
안전재고는 예상보다 판매량이 증가하거나 거래처의 입고가 지연될 때를 대비해 추가로 확보하는 재고입니다.
발주점은 현재 재고가 일정 수준까지 감소했을 때 새로운 상품을 주문해야 하는 기준 수량입니다.
예를 들어 하루 평균 10개가 판매되고 상품을 주문한 후 입고까지 5일이 걸린다면, 입고 기간 동안 예상되는 판매량은 50개입니다.
여기에 안전재고 20개를 추가하면 발주점은 70개가 됩니다.
| 구분 | 계산 기준 | 결과 |
|---|---|---|
| 하루 평균 판매량 | 10개 | 10개 |
| 입고 소요일 | 5일 | 5일 |
| 입고 기간 예상 판매량 | 10×5 | 50개 |
| 안전재고 | 별도 확보 | 20개 |
| 발주점 | 50+20 | 70개 |
현재 재고가 70개 이하로 감소하면 발주를 검토해야 합니다. 다만 발주점은 주문 시점을 판단하는 기준이며, 실제 주문 수량과는 구분해야 합니다.
2. 엑셀 재고관리표 만들기
엑셀을 실행하고 A1부터 J1까지 다음 항목을 입력합니다.
| 열 | 항목 | 입력 내용 |
|---|---|---|
| A | 상품명 | 판매 상품 이름 |
| B | 하루 평균 판매량 | 최근 판매량 기준 |
| C | 입고 소요일 | 주문부터 입고까지 |
| D | 안전재고 일수 | 추가 확보 기간 |
| E | 현재고 | 현재 보유 수량 |
| F | 입고예정 수량 | 이미 주문한 수량 |
| G | 안전재고 | 자동 계산 |
| H | 발주점 | 자동 계산 |
| I | 발주 필요 수량 | 자동 계산 |
| J | 발주 상태 | 자동 표시 |
2행부터 상품별 데이터를 입력합니다.
| 상품명 | 일평균 판매 | 입고일 | 안전일 | 현재고 | 입고예정 |
|---|---|---|---|---|---|
| 여성 티셔츠 | 10 | 5 | 3 | 45 | 0 |
| 남성 셔츠 | 6 | 7 | 4 | 30 | 10 |
| 청바지 | 4 | 6 | 3 | 50 | 0 |
| 운동복 | 8 | 4 | 2 | 20 | 0 |
| 양말 세트 | 15 | 3 | 2 | 25 | 20 |
입고 소요일과 안전재고 일수는 모두 일 단위로 입력합니다. 하루 평균 판매량은 최근 30일 판매량을 30으로 나누어 구할 수 있습니다.
3. 안전재고 자동 계산하기
안전재고는 하루 평균 판매량에 추가 확보 기간을 곱하는 방식으로 간단하게 계산할 수 있습니다.
안전재고 = 하루 평균 판매량 × 안전재고 일수
G2 셀에 다음 수식을 입력합니다.
=ROUNDUP(B2*D2,0)여성 티셔츠의 하루 평균 판매량이 10개이고 안전재고 일수가 3일이라면 안전재고는 30개입니다.
ROUNDUP 함수는 소수점이 발생했을 때 수량을 올림하여 정수로 표시합니다.
G2 셀의 수식을 아래 행까지 복사하면 상품별 안전재고가 자동으로 계산됩니다.
이 방식은 일정한 평균 판매량을 가정한 간편 계산법입니다. 판매량 변동이 심하거나 공급 지연이 잦은 상품은 안전재고 일수를 더 보수적으로 설정해야 합니다.
4. 발주점 자동 계산하기
발주점은 입고 기간 동안 예상되는 판매량에 안전재고를 더해 계산합니다.
발주점 = 하루 평균 판매량 × 입고 소요일 + 안전재고
H2 셀에 다음 수식을 입력합니다.
=ROUNDUP(B2*C2+G2,0)여성 티셔츠를 예로 들면 다음과 같습니다.
- 하루 평균 판매량 : 10개
- 입고 소요일 : 5일
- 안전재고 : 30개
- 발주점 : 80개
현재고가 80개 이하로 감소하면 새로운 상품의 발주를 검토해야 합니다.
발주점을 설정하면 상품이 완전히 품절되기 전에 재고를 확보할 수 있습니다.
5. 발주 필요 수량 자동 계산하기
발주가 필요하다는 사실을 확인했다면 실제로 몇 개를 주문해야 하는지도 계산해야 합니다.
이번 실습에서는 입고 기간 동안 판매될 예상 수량과 안전재고를 확보하는 것을 목표로 합니다.
발주 필요 수량 = 발주점 − 현재고 − 입고예정 수량
단, 계산 결과가 음수라면 발주 수량은 0으로 표시합니다.
I2 셀에 다음 수식을 입력합니다.
=MAX(0,H2-E2-F2)예를 들어 여성 티셔츠의 재고 상황이 다음과 같다고 가정하겠습니다.
| 항목 | 수량 |
|---|---|
| 발주점 | 80개 |
| 현재고 | 45개 |
| 입고예정 수량 | 0개 |
| 발주 필요 수량 | 35개 |
따라서 35개를 발주하면 계산상 목표 재고 수준을 맞출 수 있습니다.
이 계산은 미입고 주문 수량을 전부 입고될 물량으로 인정하는 간편 방식입니다. 실제 운영에서는 입고 지연, 불량품, 예약 판매 수량 등도 확인해야 합니다.
또한 이 방식은 최소 확보 수량을 계산하는 것이므로, 한 번에 2주 또는 1개월치 상품을 구매하는 업체라면 별도의 목표 재고 기간을 반영해야 합니다.
6. 발주 상태 자동 표시하기
상품이 많아지면 발주가 필요한 상품을 일일이 확인하기 어렵습니다.
엑셀 IF 함수를 사용하면 발주 필요 여부를 자동으로 표시할 수 있습니다.
J2 셀에 다음 수식을 입력합니다.
=IF(I2>0,"발주 필요","재고 충분")발주 필요 수량이 1개 이상이면 ‘발주 필요’, 0개이면 ‘재고 충분’으로 표시됩니다.
앞에서 입력한 예시 데이터를 기준으로 계산하면 다음과 같은 결과가 나옵니다.
| 상품명 | 안전재고 | 발주점 | 발주 수량 | 상태 |
|---|---|---|---|---|
| 여성 티셔츠 | 30 | 80 | 35 | 발주 필요 |
| 남성 셔츠 | 24 | 66 | 26 | 발주 필요 |
| 청바지 | 12 | 36 | 0 | 재고 충분 |
| 운동복 | 16 | 48 | 28 | 발주 필요 |
| 양말 세트 | 30 | 75 | 30 | 발주 필요 |
이렇게 작성하면 상품별 발주 수량과 상태를 한눈에 확인할 수 있습니다.
7. 조건부 서식으로 발주 필요 상품 표시하기
발주가 필요한 상품을 색상으로 구분하면 재고관리표를 더욱 편리하게 사용할 수 있습니다.
- 엑셀에서 A2 범위를 선택합니다.
- 상단 메뉴에서 ‘홈’을 클릭합니다.
- ‘조건부 서식’을 선택합니다.
- ‘새 규칙’을 클릭합니다.
- ‘수식을 사용하여 서식을 지정할 셀 결정’을 선택합니다.
- 다음 수식을 입력합니다.
=$J2="발주 필요"서식에서 연한 빨간색 채우기를 선택하고 확인을 누릅니다.
이제 발주가 필요한 상품의 행 전체가 자동으로 강조됩니다.
반대로 재고가 충분한 상품은 연한 녹색으로 표시하도록 별도 규칙을 추가할 수도 있습니다.
8. 챗GPT로 재고관리 수식 만들기
엑셀 함수에 익숙하지 않다면 챗GPT에 필요한 계산 조건을 설명하고 수식을 요청할 수 있습니다.
다음과 같이 입력해 보겠습니다.
“엑셀로 쇼핑몰 재고관리표를 만들려고 합니다. B열은 하루 평균 판매량, C열은 입고 소요일, D열은 안전재고 일수, E열은 현재고, F열은 입고예정 수량입니다. G열에는 안전재고, H열에는 발주점, I열에는 발주 필요 수량, J열에는 발주 상태를 자동 계산하는 수식을 만들어 주세요. 재고 수량은 정수로 올림하고 발주 수량은 음수가 나오지 않도록 해주세요.”
챗GPT가 제시한 수식을 엑셀에 입력한 다음 예시 상품으로 계산 결과를 검증합니다.
이때 챗GPT가 만들어 준 수식이라고 해서 무조건 정확한 것은 아닙니다. 실제 열 위치와 수식의 참조 범위가 일치하는지 반드시 확인해야 합니다.
9. 판매량이 달라질 때 안전재고 조정하기
모든 상품에 같은 안전재고 일수를 적용하는 것은 효율적이지 않을 수 있습니다.
판매량이 일정한 상품과 계절에 따라 판매량이 크게 달라지는 상품은 재고관리 기준을 다르게 설정하는 것이 좋습니다.
| 상품 유형 | 안전재고 일수 예시 | 관리 방법 |
|---|---|---|
| 판매량 일정 | 2~3일 | 평균 판매량 기준 |
| 판매량 변동 큼 | 5~7일 | 판매 추세 확인 |
| 입고 지연 잦음 | 7~10일 | 공급 일정 확인 |
| 계절성 상품 | 별도 설정 | 성수기 수요 반영 |
| 판매 부진 상품 | 최소 설정 | 과잉재고 방지 |
위 기간은 실습을 위한 예시이며 모든 업종에 적용되는 표준 수치는 아닙니다.
실제 안전재고는 판매량 변동, 공급업체 납기 준수율, 품절 허용 수준 등을 고려해 설정해야 합니다.
예를 들어 여름철에 판매가 증가하는 반팔 티셔츠는 성수기 판매 데이터를 기준으로 계산하는 것이 최근 1년 평균 판매량만 사용하는 것보다 적절할 수 있습니다.
10. 실제 쇼핑몰에서 활용할 때 주의할 점
안전재고와 발주 수량을 자동 계산하더라도 실제 발주 전에 확인해야 할 사항이 있습니다.
첫째, 현재고는 판매 가능한 재고를 기준으로 관리해야 합니다. 불량품이나 반품 예정 상품을 판매 가능 재고에 포함하면 품절 위험이 커집니다.
둘째, 입고예정 수량은 이미 발주했지만 아직 도착하지 않은 상품만 입력해야 합니다. 입고가 완료되면 해당 수량을 현재고로 이동하고 입고예정 수량에서는 차감해야 중복 계산을 방지할 수 있습니다.
셋째, 공급업체의 최소 주문 수량과 박스 단위를 확인해야 합니다. 계산상 35개가 필요하더라도 10개 단위로만 주문할 수 있다면 실제 발주량은 40개가 될 수 있습니다.
넷째, 현재고에서 이미 고객에게 판매하기로 확정한 미출고 수량을 제외해야 합니다. 예약 주문이나 미출고 주문이 많은 쇼핑몰에서는 이를 별도 항목으로 관리하는 것이 좋습니다.
다섯째, 발주점과 발주 수량을 같은 기준으로 해석해서는 안 됩니다. 이번 실습은 입고 기간의 예상 수요와 안전재고를 기준으로 한 간편 모델이며, 정기 발주나 장기 재고 확보 방식에는 별도의 계산이 필요합니다.
11. 재고관리표 완성 후 확인할 사항
엑셀 재고관리표를 완성했다면 다음 사항을 점검합니다.
- 상품별 하루 평균 판매량이 실제 판매 기록과 일치하는지 확인합니다.
- 입고 소요일은 공급업체의 실제 납품 기간을 기준으로 입력합니다.
- 안전재고 일수는 상품별 판매 특성에 맞게 조정합니다.
- 입고예정 수량과 현재고가 중복되지 않도록 관리합니다.
- 발주 필요 수량이 음수로 표시되지 않는지 확인합니다.
- 신규 상품을 추가했을 때 수식과 조건부 서식이 적용되는지 확인합니다.
엑셀에서 입력 범위를 표로 변환하려면 데이터 영역을 선택한 뒤 Ctrl+T를 누르면 됩니다. 표 기능을 사용하면 상품이 추가될 때 수식과 서식을 확장하기가 편리합니다.
공식 기능 설명은 마이크로소프트의 엑셀 지원 페이지에서 확인할 수 있습니다.
확인하기 : Microsoft 엑셀 IF 함수 공식 설명
확인하기 : Microsoft 엑셀 조건부 서식 사용법
12. 마무리
엑셀과 챗GPT를 활용하면 별도의 유료 재고관리 프로그램 없이도 안전재고와 발주 필요 수량을 자동 계산하는 기본 관리표를 만들 수 있습니다.
핵심은 하루 평균 판매량, 입고 소요일, 안전재고 일수, 현재고, 입고예정 수량을 정확하게 관리하는 것입니다.
처음에는 상품 5~10개로 계산 결과를 검증한 뒤 실제 판매 상품 전체로 확대하는 것이 좋습니다.
특히 온라인 쇼핑몰에서는 품절로 인한 판매 손실뿐 아니라 과잉재고로 발생하는 보관비와 자금 부담도 함께 관리해야 합니다.
상품별 판매 기록을 주기적으로 업데이트하고 안전재고 기준을 조정하면 더욱 효율적인 재고관리와 발주 계획을 수립할 수 있습니다.
답글 남기기