SCM팀 재고관리 업무 자동화 사례

가구회사 SCM팀의 재고 관리 업무를 자동화한 사례

• 매일 반복되는 ERP 데이터 추출, 엑셀 변환, 육안 확인 업무를 자동화하여 시간을 단축하고자 함

• 가용재고 부족, 단종 처리 누락, 사양 변경 등 긴급 이슈를 자동으로 필터링하고 담당자에게 알림을 전송하는 시스템을 구축하고자 함

• 특별 관리 품목을 자연어로 관리할 수 있는 대화형 에이전트를 개발하고자 함

• 단종 단품의 짝맞추기 및 추가 생산량을 데이터 기반으로 최적화하고자 함

진행 방법

대부분의 작업을 클로드를 사용하여 진행
(아무래도 엑셀 기반으로 모든 작업을 진행하기 때문에...)

아래는 엑셀 자동화 VBA 매크로 시스템 구축한 요약본

프로젝트 요약: 엑셀 자동화 VBA 매크로 시스템 구축

📌 프로젝트 개요

Raw 데이터를 자동으로 읽어서 엑셀 시트에 업데이트하는 VBA 매크로 시스템 개발

🗂️ 작업한 파일 2개

1️⃣ product_master.xlsm

목적: 일룸SCM 재고/출고 관리

완성된 시트 (5개):

① 종합 시트

• Raw 데이터: C:\Users\FURSYS\Desktop\일룸SCM\raw데이터\코드정보추출\grd_list_*.xls

• 파일 개수: 최신 1개

• 업데이트 주기: 주 1회 (월요일만)

• 데이터 시작: 3행부터 (1행 헤더, 2행 보존)

• 필터링: 6가지 제외 조건 (단종/개발 + 비관리주문품 등)

• 매핑 컬럼: 1~5, 9~13, 25~27, 29~34번 (나머지는 수식 보존)

② 재고일보 시트

• Raw 데이터: C:\Users\FURSYS\Desktop\일룸SCM\raw데이터\재고일보\grd_mst_*.xls

• 파일 개수: 최신 2개 (순서대로 합침)

• 업데이트 주기: 매일 (평일)

• 데이터 시작: 3행부터 (2행 보존)

• 필터링: 창고 5개만 (일룸양지, 일룸양지3, 시디즈양지, 시디즈양지3, 퍼시스양지)

• 매핑 컬럼: 1~22번 (23번 이후 수식 보존)

• 컬럼 구조: 번호, 단품코드, 컬러, 조합, 단품명칭, 시리즈, 창고, 재고구분, 표준구분, 사용구분, 용도구분, 제품구분, 현재고수량, 3개월평균출고, 출고예정량, 입고예정량, 이동중재고, 판매가, 공장도가, 매입단가, 공급업체, 생산라인

③ 사업장재고 시트

• Raw 데이터: C:\Users\FURSYS\Desktop\일룸SCM\raw데이터\사업장현재고\사업장 현재고*.xlsx

• 파일 개수: 최신 1개

• 업데이트 주기: 매일 (평일)

• 매핑 컬럼: 1~3번 (4번 수식 보존)

④ 재고일보_VN 시트

• Raw 데이터: C:\Users\FURSYS\Desktop\일룸SCM\raw데이터\재고일보_VN\grd_mst_*.xls

• 파일 개수: 최신 1개

• 업데이트 주기: 매일 (평일)

• 데이터 시작: 3행부터 (2행 보존)

• 매핑 컬럼: 1~21번 전체

⑤ 일자별 수주수량 시트

• Raw 데이터: C:\Users\FURSYS\Desktop\일룸SCM\raw데이터\일자별 수주수량\일자별 수주수량*.csv

• 파일 개수: 최신 1개

• 업데이트 주기: 매일 (평일)

• 파일 형식: CSV (UTF-16)

• 특징: 동적 컬럼 (날짜가 계속 바뀜)

2️⃣ 단종품목_짝맞추기_자동화.xlsm

목적: BOM/재고/출고 분석

완성된 시트 (3개):

① DATA_BOM 시트

• Raw 데이터: C:\Users\FURSYS\Desktop\일룸SCM\raw데이터\일룸세트코드\grd_ItmList_F_*.xls

• 파일 개수: 최신 4개

• 매핑: 세트코드조합, 세트명칭, 단품코드조합, 단품명칭, 구성수량, 시리즈, 단품컬러, 공급업체

② DATA_재고 시트

• Raw 데이터 3개 폴더:

  • 재고일보: 최신 2개 (창고 필터링)

  • 재고일보_VN: 최신 1개

  • 사업장현재고: 최신 1개

• 중복 처리: 단품코드조합 기준 합산

• 계산식: 가용재고 = 현재고수량 + 입고예정량 - 출고예정량

③ DATA_출고 시트

• 소스: product_master.xlsm의 "세트_월 출고량" 시트 (BO열)

• 특징: 외부 파일 읽기 (열려있는 파일 사용)

🎯 주요 기능

1. 자동 실행 (Workbook_Open)

product_master.xlsm:

• 월요일: 모든 시트 업데이트 (종합 + 재고일보 + 사업장재고 + 재고일보_VN + 일자별 수주수량)

• 화~금: 일일 시트만 (재고일보 + 사업장재고 + 재고일보_VN + 일자별 수주수량)

단종품목_짝맞추기_자동화.xlsm:

• 사전 안내 메시지 → 3개 시트 업데이트 (BOM + 재고 + 출고)

2. 텍스트 형식 유지

• .NumberFormat = "@" 사용

• .Text 속성으로 1E1 → 10 변환 방지

3. 수식 보존

• 매핑되는 컬럼만 .ClearContents

• 수식 있는 컬럼은 건드리지 않음

4. 최신 파일 자동 감지

• GetLatestFile: 1개

• GetLatestFiles: N개 (날짜순 정렬)

5. 조합 컬럼 생성

• "단품코드-컬러" 형식 자동 생성

🛠️ 기술적 해결 사항

문제 1: 수식이 계속 삭제됨

해결: 전체 행 삭제 대신 매핑되는 컬럼만 ClearContents

문제 2: 파일명 패턴 찾기

해결: InStr로 유연한 검색 (언더바 유무 대응)

문제 3: CSV UTF-16 인코딩

해결: 엑셀에서 자동 처리 (VBA는 그대로 읽기)

문제 4: 같은 이름 컬럼 (색상, 색상.1)

해결: 인덱스 번호로 직접 접근

문제 5: 템플릿 파일(.xltm) 오류

해결: .xlsm으로 변경 또는 열린 파일 사용

문제 6: 컬럼 매핑 오류

해결: 정확한 순서로 재매핑 (용도구분 추가 등)

문제 7: 2행 보존 요청

해결: START_ROW = 3으로 변경

📊 데이터 흐름

Raw 데이터 폴더
    ↓
최신 파일 자동 감지
    ↓
파일 열기 (ReadOnly)
    ↓
컬럼 매핑 (Dictionary)
    ↓
필터링 (조건 있는 경우)
    ↓
기존 데이터 삭제 (매핑 컬럼만)
    ↓
데이터 복사 (텍스트 형식 유지)
    ↓
글꼴 설정 (Noto Sans KR, 9pt)
    ↓
저장

🎨 코드 스타일

공통 함수

• GetColumnMapping: 헤더 → Dictionary 매핑

• GetLatestFile: 최신 1개 파일

• GetLatestFiles: 최신 N개 파일 (정렬)

• CopyRowData: 행 데이터 복사 (컬럼별)

• ShouldIncludeRow: 필터링 조건

명명 규칙

• 상수: RAW_DATA_FOLDER_재고일보

• 함수: GetLatestFiles_재고일보

• 시트: TARGET_SHEET_재고일보

✅ 최종 완성 사항

product_master.xlsm

1. ✅ 종합 (월요일, 3행부터, 필터링 6개 조건)

2. ✅ 재고일보 (매일, 3행부터, 창고 필터링, 22개 컬럼)

3. ✅ 사업장재고 (매일, NaN 제거)

4. ✅ 재고일보_VN (매일, 3행부터)

5. ✅ 일자별 수주수량 (매일, CSV)

단종품목_짝맞추기_자동화.xlsm

1. ✅ DATA_BOM (4개 파일 합침)

2. ✅ DATA_재고 (3개 폴더, 중복 합산)

3. ✅ DATA_출고 (외부 파일 읽기)

자동 실행

• ✅ Workbook_Open 이벤트

• ✅ 월요일/평일 구분

• ✅ 사전 안내 메시지

🔧 주요 설정

• 글꼴: Noto Sans KR, 9pt

• 텍스트 형식: @

• 진행 표시: Application.StatusBar

• 오류 처리: Try-Catch 구조

• 수식 보존: 매핑 안 되는 컬럼 보존

총 작업량:

• 파일: 2개

• 시트: 8개

• VBA 매크로: 8개

• 공통 함수: 15개+

• 코드 라인: 약 1500+ 줄

아래는 위에 엑셀 자동화 진행 후 연결해서 작업한 결과물

단종 단품 짝맞추기 & 추가생산량 최적화 도구

1. 목적

단종 예정인 단품들의 재고를 최대한 소진하면서, 추가생산 비용을 최소화하는 최적의 생산량(소진합) 계산

2. 핵심 로직

소진합 결정 방식

1. 재고 최대값에서 시작: 단종품 중 재고가 가장 많은 단품 기준

2. 상한선: 10개월 예상판매량 이하

3. 60% 룰 체크:

   • 추가생산 필요 단품의 입고가 합 / 세트 총 입고가 < 60%

   • 60% 이상이면 소진합을 한 단계씩 줄여가며 재체크

4. 60% 미만이 될 때까지 반복

재고 안분 방식

• 동일 단품이 여러 세트에서 사용될 경우

• 각 세트의 10개월 예상판매량 비율로 재고를 안분

• 안분 시 손실 없이 배분 (남은 수량은 예상판매량 높은 세트부터 1개씩 추가)

3. 파일 구성

※ 두 파일이 같은 폴더에 있어야 함

4. 엑셀 시트 구성

5. 사용 방법

1단계: [입력_단종품목] 시트 → A열에 단종 단품코드 입력 → 저장

2단계: 단종품목_분석실행.py 더블클릭

3단계: 엑셀 파일 다시 열고 → [결과_분석], [추가생산_리스트] 시트 확인

6. 결과 시트 설명

결과_분석 시트 (세트별 상세)

추가생산_리스트 시트 (단품별 요약)

7. 데이터 업데이트 방법

8. 60% 룰 예시

테스트 데이터:

계산 과정:

• 세트 총 입고가: 100,000원

• 소진합 10 → 추가생산 단품 b,c,d → 90% ❌

• 소진합 8 → 추가생산 단품 b,d → 60% ❌

• 소진합 7 → 추가생산 단품 b → 20% ✅

결과: 소진합 = 7

9. 필요 환경

• Python 3.x

• 라이브러리: pip install pandas openpyxl

파이썬을 통해 진행!

최소 3일 이상 걸리는 📋 재고 관리 자동화 시스템 전체 요약

아래는 또 이어서 진행한 자동화 사례..

🎯 프로젝트 목표

엑셀 기반 재고 데이터를 자동 분석하여 발주 필요, 단종 처리, 사양 변경 알림 생성

📊 3가지 핵심 알림

1. 발주 필요 알림 (가용재고 부족)

• 조건: 가용재고 < 안전재고

• 결과: 331건

• 계산식:

  사본

  가용재고 = 현재고 + 입고예정 - 리드타임내출고예정안전재고 = (SCP예측 × 80%) + (3주실적 × 리드타임계수 × 20%)발주필요량 = 안전재고 - 가용재고
  

2. 단종 재고 소진 알림

• 조건: 용도구분 = '재고소진 단종' + 재고 있음

• 결과: 160건

• 목적: 단종 예정 품목의 재고 소진 현황 추적

3. 사양 변경 알림

• 조건: 용도구분 = '사양변경' + 재고 있음

• 결과: 98건

• 목적: 사양 변경 품목의 재고 처리 필요

🔧 안전재고 계산 로직 (핵심!)

가중평균 80:20 방식

안전재고 = (SCP예측 × 80%) + (월환산실적 × 리드타임계수 × 20%)

4단계 우선순위:

1. 실적 + 예측 둘 다 → 가중평균 80:20 ✅ (주로 사용)

2. 실적만 → 실적 × 리드타임계수

3. 예측만 → SCP예측

4. 둘 다 없음 → 3개월 평출 × 리드타임계수

📈 데이터 소스

1. SCP 예측 (미래 계획) - 80% 가중치

• 시트: SCP계획

• 내용: 월별 예측수요 (1월, 2월, 3월, 4월)

• 특징: 성수기/비수기 계절성 반영

• 동적 선택:

  사본

  1월 30일 → 1월 남은 2일 + 2월 전체 28일 + ...2월 15일 → 2월 남은 14일 + 3월 16일 + ...→ 현재 월 기준 자동 계산!
  

2. 최근 3주 실적 (과거 실제) - 20% 가중치

• 시트: 일자별 수주수량

• 기간: 오늘 기준 3주 전 ~ 오늘 (21일)

• 계산: (3주 출고량 / 21일) × 30일 = 월 환산

• 목적: 최신 트렌드 반영

3. 리드타임 등급별 계수

AAA (90일): 3.0
AA  (60일): 2.0
A   (30일): 1.0
B   (20일): 0.67
C   (10일): 0.34
D, VN     : 제외

💡 계산 예시

HSAA0300-IV (B등급):

[입력]
- SCP 예측: 433개 (리드타임 20일 기간)
- 3주 실적: 877개 → 월환산 1,253개
- 리드타임계수: 0.67
- 현재고: 543개
- 입고예정: 0개
- 리드타임내출고예정: 209개

[계산]
안전재고 = (433 × 0.8) + (1,253 × 0.67 × 0.2)
        = 346 + 168 = 514개

가용재고 = 543 + 0 - 209 = 334개

발주필요량 = 514 - 334 = 180개 ✅

🤖 자동화 구조

파일 구조

inventory-alert/
├── SKILL.md                    # 스킬 설명서
├── scripts/
│   ├── analyze_inventory.py   # 메인 분석 스크립트
│   └── daily_check.py         # 일일 체크용
└── references/
    ├── data_structure.md       # 데이터 구조 문서
    └── alert_logic.md          # 알림 로직 문서

실행 방법

python analyze_inventory.py product_master.xlsm 재고알림.xlsx

입력 파일: product_master.xlsm

• 재고확인 (현재고)

• 종합 (기준 데이터)

• 출고예정 (확정 출고)

• 구매담당 (담당자 정보)

• SCP계획 (예측수요)

• 일자별 수주수량 (실적)

출력 파일: 재고알림.xlsx

[요약] - 전체 현황
[1.발주필요] - 331건
[2.단종처리] - 160건
[3.사양변경] - 98건

🎨 출력 형식

• 글꼴: Noto Sans KR

• 크기: 9pt (데이터/헤더), 12pt (제목)

• 숫자: 모두 정수 (소수점 없음)

• 정렬: 발주필요량 내림차순

✅ 핵심 개선사항

1. ✓ 가용재고 계산 정확화

   • 현재고 + 입고예정 - 리드타임내출고예정

2. ✓ SCP 예측 동적 선택

   • 현재 월 기준 자동 계산

   • 성수기/비수기 정확 반영

3. ✓ 최근 3주 실적 반영

   • 실제 출고 데이터로 트렌드 파악

4. ✓ 가중평균 80:20

   • 예측 중심 (80%)

   • 실적 보정 (20%)

   • 재고 부담 완화

5. ✓ 품목 필터링

   • 사용구분 = '사용'

   • 재고구분 = '재고'

   • 리드타임 등급 ≠ D, VN

6. ✓ 데이터 소스 수정

   • 재고확인 48건 → 종합 160건으로 변경

   • 재고구분 필터로 198건 정제

📊 최종 결과

특징: 예측 중심, 실적 보정으로 합리적이고 실행 가능한 발주 계획 수립단 5분만에 끝....

결과와 배운 점

일단 내가 뭘 해야할지 계속 고민하고 상상하는게 가장 중요한것 같음

나의 경우 잠 잘때 똥 쌀때 씼을때 계속 어떻게 자동화할지 계속 생각했음

근데 그게 너무 재밌고 빨리 하고 싶었음

근데 회사업무+육아로.... 많은 시간을 할애하지 못한게 너무나 아쉬움

특히 스터디장님이 별도 시간까지 내면서 오프라인 모임을 열어주셨는데

참석하지 못한게 너무 아쉬웠다

시행착오는 누구나 겪는다고 생각한다

근데 그 시행착오가 있어야 더 완성에 가까운 결과물이 나오는것 같다

그리고 그 과정이 있어야 내가 어떻게 설계했는지 나중에 문제가 생겼을때 어떻게 해결해야되는지 알수 있는것 같다

결론 지피터스 알게되서 너무 좋았음

나중에 또 하고싶다