Project 5. 재고 분석을 통한 물류기획 관리 (1)

이 글은 24.02.26에 본인 벨로그에 작성했던 글을 옮겨 온 것이다.

 

 

Project 5. 재고 분석을 통한 물류기획 관리

 

물류 창고 재고관리 데이터분석 담당자가 되었다고 가정해보자.
담당자인 나는 악성 재고 파악과 관리방안 플랜 수립 이라는 프로젝트를 진행한다.
목표는 악성 재고 현황 파악 및 최소화 플랜 수립이다. 다음과 같은 포인트에서 분석을 진행해볼 수 있다.

• 일반 재고와 악성 재고의 비율은?
• 악성 재고의 추산 가치는?
• 해당 악성 재고가 창고에 보관된 기간은?
• 악성 재고를 모두 팔려면 시간이 얼마나 필요한가?
• 악성 재고 내에서도 등급을 매겨서 관리해볼 수 있을까?

중요하게 봐야 할 지표는 무엇이 있을까?

• 입고량, 판매량, 재고(입고량-판매량) 권장 판매가(List price), 실 판매가(Net price), SKU, Unit quantity, DOC(Day of Coverage, 재고량/하루 판매량) 등.
• DOC에 대해 좀 더 알아보자.
  DOC가 낮다 -> 현 재고로 커버할 수 있는 일수가 적다 -> 재고 전환율이 높다.
  DOC가 높다 -> 현 재고로 커버할 수 있는 일수가 많다 -> 재고 전환율이 낮다.
  => DOC가 낮은 상품은 높은 상품에 비해 잘 팔리는 상품이다
  => DOC가 낮은 상품의 발주 주기를, 높은 상품에 비해 짧게 가져가야 한다
  => DOC가 낮은 상품을, 높은 상품에 비해 창고 바깥에 배치하는게 좋다
  => DOC가 높은 상품이 낮은 상품에 비해 악성 재고가 될 확률이 높다.
  
  

이제 데이터를 SQL을 통해 분석해보도록 하자. DBeaver를 사용한다.

SQL 분석

데이터 살펴보기(재고 파악)

• 재고 유형과 sold flag 별 재고 수량 파악하기.

active : 활성재고
historical : 비활성재고
soldflag : 6개월 내 판매 이력 있으면 1, 아니면 0
itemcount : 재고 수량
soldcount : 판매 수량

활성재고의 경우는 계속 잘 팔리는 재고이기 때문에 soldflag나 sold_count 데이터는 없고 보유한 재고량만 데이터로 남아있는 것을 볼 수 있다.

활성재고/비활성재고로 나누어 보면 아래와 같고

활성/비활성/악성재고로 나누어 보면 아래와 같다.

각각 어느 정도의 비중을 차지하는지도 표시해보면?

Total 값도 표시해주고 싶다.

 

• 6개월 내 판매 이력이 있는 비활성 재고에 대해서 파악하기
  

  

6개월 내 판매이력이 있는 비활성재고는 82만개 정도가 쌓여 있으며, 24000개 정도가 팔렸다. 이 추세가 계속된다면 다 파는 데에 201개월 정도가 걸릴 것으로 예상된다.

• 재고 가치 파악
  PriceReg : 물품 가격(원가)
  

  

비활성재고와 활성재고의 재고 가치를 파악해봤을 때, 6개월 내 판매이력이 없는 비활성재고의 가치가 활성재고 가치의 절반 이상임을 확인할 수 있다.

 

악성재고 원인 파악

재고가 쌓이는 원인이 뭘까? 파악해보자.

가격

LowNetPrice : 제품의 실제 판매가격(=권장소비자가격)

아래과 같은 것들을 알 수 있다
1) 비활성재고에 비해 활성재고 원가가 낮다.
2) 판매 이력이 있는 비활성재고의 할인율은 거의 50%이다. 할인율이 높아서 그나마 좀 팔린 것일까?
3) 악성재고(판매이력 없는 비활성재고)의 경우, 할인율을 좀 높여야 판매가 될 것 같다.
4) 활성재고의 할인율이 그리 높지 않은 것은, 이미 잘 팔리고 있는 제품이라서인 듯 하다.

 

신제품 출시

NewReleaseFlag : 1(신제품 출시 or 업그레이드 후 재출시), 0(이미 최신 제품)

어떤 제품의 신 버전 or 업그레이드 버전이 출시되었다면, 구버전은 비활성재고로 남게 될 가능성이 있다. 확인해보자.
1) new relase flag가 2개인 제품을 추려낸 후

2) 해당 sku 중 하나에 관한 데이터 직접 조회

new release flag가 1인 제품 => 최신 버전이 출시된 제품 => 구버전 제품
new release flag가 0인 제품 => 최신 버전 제품
구버전은 비활성재고, 신버전은 활성재고로 분류되어 있는 것을 볼 수 있다.
즉 같은 제품이라면 구버전은 신버전에 비해 안 팔린다는 말이다. 당연하겠지만...

 

창고 보관 기간

창고에 오래 있던 제품은 비활성재고일 확률이 높을까?
ReleaseYear : 창고 입고년도.
현재(2024년) 기준으로 제품들이 창고에 얼마나 있었는지 확인해보자

입고년도와 보관기간은 활성재고 -> 비활성재고 -> 악성재고 순으로 짧다.
당연한 결과인 듯... 안 팔리니까 창고에 오래 보관되어있겠지.

재고 관리를 위한 SKU Grade 기획

창고 계약 기간이 5년(=60개월) 남은 상황이라고 가정해 보자.
이 상황에서 재고관리 데이터분석 담당자인 나는 SKU 유형 별로 세분화해서 각 유형에 맞는 관리방안을 수립하고자 한다.
S -> A -> B -> ... -> F
1) 활성재고[S, A, B] => 물량 기준
2) 비활성재고[C, D, E] => 재고 보유량과 재고 전환율 기준
3) 악성재고 : 그냥 F

해당 기준에 맞춰 결과를 출력하고, 출력 결과를 새 table로 만들어보자.
쿼리는 다음과 같다.

CREATE TABLE Inventory.SKU_grade AS 
(
WITH sub_table AS (
      SELECT sku_number AS SKU
           , CASE WHEN file_type = 'Historical' AND SoldFlag=1 THEN '2. Non-Activated'
                  WHEN file_type = 'Historical' AND SoldFlag=0 THEN '3. BAD'
                  WHEN file_type = 'Active' THEN '1. Activated'
                  ELSE '기타'
                  END AS inventory_type
           , itemcount
           , soldcount
           , round(itemcount/if(soldcount = 0, NULL, soldcount/6), 1) AS MOC
        FROM Inventory
        ORDER BY 1,2
), pct_rank AS (
      SELECT *
           , percent_rank() OVER (PARTITION BY inventory_type ORDER BY itemcount desc) AS pct
        FROM sub_table
), sku_grade AS (
      SELECT *
           , CASE WHEN inventory_type = '1. Activated' AND pct < 0.1 THEN 'S'
                  WHEN inventory_type = '1. Activated' AND pct between 0.1 AND 0.5 THEN 'A'
                  WHEN inventory_type = '1. Activated' AND pct > 0.5 THEN 'B'
                  WHEN inventory_type = '2. Non-Activated' AND pct < 0.5 AND MOC < 60 THEN 'C'
                  WHEN inventory_type = '2. Non-Activated' AND pct > 0.5 AND MOC < 60 THEN 'D'
                  WHEN inventory_type = '2. Non-Activated' AND MOC >= 60 THEN 'E'
                  WHEN inventory_type = '3. BAD' THEN 'F'
                  END AS SKU_grade
        FROM pct_rank
        ORDER BY inventory_type, pct
  )
SELECT SKU
     , inventory_type
     , SKU_grade
  FROM sku_grade
);

잘 만들어졌다!

 

다음 시간엔 PowerBI로 시각화를 해 볼 것이다.