엑셀 재고관리 양식: 재고·발주·판매 관리 템플릿

구성

통합 문서에는 시트가 7개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 자동으로 계산돼요.

시작하기 전에

파일을 열면 바로 쓸 수 있어요. 셀 색으로 각 칸의 용도를 알 수 있어요.

1. 파일을 열어요. 엑셀 위쪽에 노란색 제한된 보기 표시줄이 나오면 편집 사용을 눌러요. 인터넷에서 받은 파일은 항상 이렇게 열려요.
2. 파일› then 다른 이름으로 저장으로 내 사본을 저장해 두면 언제든 원본으로 다시 시작할 수 있어요.
3. 예시 데이터를 지워요. Products의 예시 상품 3개를 내 상품으로 바꾸고, Stock Movements, Sales, Purchase Orders의 예시 행을 지워요. 연한 노란색 셀만 지우면 초록색 셀은 다시 계산돼요.

단계별 사용법

처음에는 순서대로 따라 하세요. 그다음부터는 매일 또는 매달 입력하는 단계만 반복하면 돼요.

1. 보고 월 정하기

   Start Here의 B6에 그 달의 1일을 입력해요. 대시보드의 Monthly Sales가 이 달을 기준으로 계산돼요. 재고 수량은 어떤 달을 고르든 항상 현재 값이에요.

2. 상품 목록 만들기

   Products에 상품마다 한 행씩 입력해요.

   • SKU: MUG-01처럼 짧고 겹치지 않는 코드예요. 다른 시트에서는 이 목록의 SKU를 드롭다운으로 골라요.
   • Product와 드롭다운에서 고르는 Category(Lists에서 카테고리 수정)
   • Unit Cost(매입 원가), Price(판매가)
   • Opening Stock: 템플릿을 쓰기 시작한 날의 재고 수량
   • Reorder Point: 다시 주문하고 싶은 수량

   그러면 On Hand, Inventory Value, Status가 계산돼요. On Hand가 재주문 시점 이하가 되면 Status에 빨간색 Reorder가 표시돼요.

3. 입고와 재고 조정 기록하기

   판매가 아닌 이유로 재고가 바뀌면 Stock Movements에 기록해요.

   • Purchase: 공급업체에서 받은 입고 수량. 양수로 입력해요.
   • Adjustment: 재고 실사, 파손, 분실에 따른 조정. 줄일 때는 -2처럼 음수, 늘릴 때는 양수로 입력해요.

   Date를 입력하고 SKU와 Type은 드롭다운에서 고른 뒤 Quantity와 Unit Cost를 입력하면 Value(수량 × 원가)가 계산돼요.

4. 판매 기록하기

   Sales에 주문마다 Date, Order #, SKU(드롭다운), Customer, Quantity를 입력해요.

   Unit Price는 Products의 Price 열에서 불러오고, Revenue는 수량 × 단가예요.

   Status는 Open, Shipped, Cancelled 중에서 골라 배송 상황을 관리해요.

5. 발주 관리하기

   공급업체에 주문하면 Purchase Orders에 발주 번호, 공급업체, SKU, 수량, 원가, 주문일, 입고 예정일을 입력하고 Status를 Open으로 둬요. Total이 계산되고, Open 발주를 모두 더한 값이 대시보드의 Open PO Value가 돼요.

   물건이 도착하면:

   • 발주의 Status를 Received로 바꿔요.
   • Stock Movements에 받은 수량만큼 Purchase 행을 추가해요. 실제로 재고를 늘리는 건 이 단계예요. 발주만으로는 재고가 바뀌지 않아요.

6. 재고 부족 확인하기

   Products의 Status 열을 Reorder로 필터링하면 주문할 상품만 볼 수 있어요. Start Here의 Low Stock Items에 개수가 나와요.

7. 대시보드 보기

   Start Here에서 Inventory Value(현재 재고 × 원가), Low Stock Items, Monthly Sales, Open PO Value, 전체 Units On Hand를 볼 수 있어요.

   내비게이션 링크 옆에는 판매 주문 상태별 개수와 차트가 있어서 처리하지 않은 주문을 놓치지 않아요.

8. Checks 시트 확인하기

   Checks는 재고 부족, 음수 재고(보통 입고 기록이 빠진 경우), SKU가 빠진 판매, 재주문이 필요한데 열린 발주가 없는 상품을 점검해요. Where to Fix에 열어 볼 시트가 나와 있어요.

9. 드롭다운 바꾸기

   이동 유형, 판매·발주 상태, 상품 카테고리는 Lists 시트에서 가져와요. 열의 빈 칸에 새 항목을 입력하면 그 드롭다운에 나타나요.

   Stock Movements, Sales, Purchase Orders의 SKU 드롭다운은 Products의 SKU를 보여 주기 때문에 상품을 추가하면 바로 쓸 수 있어요.

수식 이해하기

수식을 몰라도 템플릿은 쓸 수 있지만, 수식을 살펴보면 엑셀 공부에 도움이 돼요. 엑셀에서 셀을 클릭하면 수식 입력줄에 수식이 보여요.

=IF(A5="", "", F5
   + SUMIFS('Stock Movements'!$D$5:$D$104, 'Stock Movements'!$B$5:$B$104, A5, 'Stock Movements'!$C$5:$C$104, "Purchase")
   + SUMIFS('Stock Movements'!$D$5:$D$104, 'Stock Movements'!$B$5:$B$104, A5, 'Stock Movements'!$C$5:$C$104, "Adjustment")
   - SUMIFS(Sales!$E$5:$E$104, Sales!$C$5:$C$104, A5, Sales!$H$5:$H$104, "<>Cancelled"))

=IF(A5="", "", IF(H5<=G5, "Reorder", "OK"))

=IF(C5="", "", IFERROR(VLOOKUP(C5, Products!$A$5:$E$54, 5, FALSE), 0))

=COUNTIF(Products!$J$5:$J$54, "Reorder")

팁과 한계

• 상품 50개, 재고 이동 100건, 판매 100건, 발주 50건까지 들어가요. 늘리려면 Ctrl+H를 누르고 범위를 통합 문서, 찾는 위치를 수식으로 정한 뒤 $104를 $1004로($54는 $504로) 바꾸세요.
• 가끔 실제 재고를 세어 보세요. On Hand와 다르면 Opening Stock을 고치지 말고 차이만큼 Adjustment 행을 추가하세요.
• Products에서 Price를 바꾸면 새 판매에 새 가격이 쓰여요. 예전 판매 행도 함께 바뀌므로, 정확한 기록이 필요하면 그 행의 초록색 Unit Price 셀에 옛 가격을 직접 입력하세요.