재고 회전율 자동 계산 시트 만들기

재고가 얼마나 빠르게 도는지 모른다는 건, 창고 안에 돈이 얼마나 묶여 있는지 모른다는 뜻이다. 회전율을 추적하지 않으면 두 가지 문제가 반복된다. 잘 나가는 상품이 품절로 판매 기회를 놓치거나, 안 나가는 상품이 몇 달째 선반을 차지하면서 보관료만 나가거나.

문제는 알아도 계산이 번거롭다는 거다. 매달 SKU 하나하나 꺼내서 출고량 합산하고 평균 재고 구하고 나눠야 하는데, 50종만 넘어가도 하기 싫어진다. 한 번만 시트를 만들어두면 이후엔 데이터가 쌓이는 것만으로 자동으로 나온다.

시트가 없을 때와 있을 때의 차이

시트 없이 한 달에 한 번 엑셀로 수작업으로 계산하면, 한 번에 30분에서 1시간이 걸린다. 그리고 그 숫자는 보고서에 한 번 쓰이고 다음 달이 되면 다시 처음부터 시작한다. 패턴을 보려면 지난달 파일을 찾아서 옆에 놓고 비교해야 한다.

시트가 있으면 다르다. 매일 입출고 데이터를 한 줄씩 추가하면 월별 회전율이 자동으로 갱신된다. 월말에 탭 하나를 열면 어떤 SKU가 고회전인지, 어떤 게 사장 재고로 내려왔는지가 색으로 바로 보인다.

탭 구조는 단순하게

탭 세 개로 만든다. 첫 번째는 입출고 로그다. 날짜, SKU 코드, 입고/출고 구분, 수량 네 컬럼이 전부다. 두 번째는 SKU 목록이다. SKU 코드와 상품명만 정리해두면 된다. 세 번째 탭에서 회전율이 자동으로 계산된다.

세 번째 탭의 각 행에 SKU 코드를 나열하고, 옆에 수식을 넣는다.

현재고 수식 (B열):
=SUMIFS(로그!D:D, 로그!B:B, A2, 로그!C:C, "입고")
-SUMIFS(로그!D:D, 로그!B:B, A2, 로그!C:C, "출고")

재고 회전율 (D열):
=IF(B2=0, "재고없음", C2/B2)

이 수식들은 로그 탭에 데이터가 추가될 때마다 자동으로 재계산된다. 따로 건드릴 게 없다.

등급은 수식 하나로

E2 =IF(D2="재고없음","확인필요",
IF(D2>=2,"고회전",
IF(D2>=0.5,"중회전",
IF(D2>=0.1,"저회전","사장재고"))))

여기에 조건부 서식을 넣으면 된다. E열 전체를 선택하고, 텍스트가 “고회전”이면 배경 초록, “사장재고”면 배경 빨간색으로 설정한다. 이렇게 하면 탭을 열었을 때 빨간 행만 봐도 어떤 SKU를 정리해야 하는지 바로 보인다.

실제로 쓸 때 생기는 질문들

가장 많이 나오는 질문이 “평균 재고는 어떻게 구하냐”다. 교과서에는 (기초재고 + 기말재고) / 2로 계산하라고 나오는데, 이걸 매달 정확히 구하려면 매달 초와 말 재고를 따로 기록해야 한다. 현장에서 그게 번거롭다면 현재고로 대신해도 된다. 완벽한 수치보다 꾸준히 추적하는 쪽이 낫다.

두 번째 질문은 “회전율이 얼마면 좋은 거냐”다. 카테고리마다 기준이 다르다. 식품·소모품은 월 2회 이상이 일반적이고, 의류·계절 상품은 0.5~1회도 정상 범위다. 절대 기준보다 전월 대비 변화 방향을 보는 게 더 실용적이다. 지난달에 2.0이었는데 이번 달에 0.8로 떨어졌다면 그게 신호다.

한 번 만들어두면 쓸 일이 많아진다. 발주량 결정할 때, 골든존 배치 검토할 때, 거래처에 재고 현황 보고할 때. 데이터가 쌓일수록 이 탭이 하는 일이 늘어난다.