엑셀 재고 관리 자동화

재고 관리, 생각만 해도 머리가 지끈지끈 아프시죠? 🤯 쌓여가는 엑셀 시트, 숫자의 향연 속에서 헤매는 당신! 이제 그 고통에서 해방될 시간입니다!🎉 기존의 판매 매출 관리 자동화에 이어, 이번에는 재고 관리까지 엑셀로 자동화하는 마법을 부려보겠습니다. ✨ "엑셀 재고 관리 자동화"라는 마법 주문을 외우면, 월별 품목별 입고량, 출고량, 재고 수량까지 척척! 게다가 재고 부족 시 자동 알림 기능까지?! 이보다 더 편리할 순 없다! 자, 이제 엑셀 마법사의 비밀 레시피를 공개합니다! 두 눈 크게 뜨고 따라오세요! 😉

 

예제파일은 아래 내려보시면 있으니 다운받아서 활용해보세요.

엑셀을 활용한 재고 관리 자동화의 기초

자, 엑셀 마법사가 되어볼까요?! 복잡한 재고 관리, 이제 머리 싸매고 끙끙댈 필요 없어요! 🧙‍♂️ 엑셀만 있으면 재고 관리도 껌이죠! (사실 껌만큼은 아니지만… 훨씬 수월해진다는 건 확실해요! 😉) 기본적인 엑셀 기능만 알아도 재고 관리 자동화의 초석을 다질 수 있답니다. 마치 레고 블록 쌓듯 차근차근, 하나씩 알아가 보자고요! 😄

엑셀 시트 기본 구조

자, 먼저 엑셀 시트의 기본 구조부터 살펴볼까요? 🧐 가장 왼쪽 열(A열)에는 상품명을 입력합니다. "딸기잼," "블루베리잼"처럼 말이죠! 각 제품명은 중복되지 않도록 주의해야 해요! ⚠️ 그 옆 열(B열)에는 입고량을 입력합니다. 그리고 C열부터는 판매량, 재고량, 재고분석을 기록할 겁니다. 

IF 함수 활용

하지만 여기서 멈추면 재미없죠! 😏 `IF` 함수를 활용하면 재고 관리가 더욱 스마트해집니다. 예를 들어 재고량이 10개 미만이면 "재고 부족!!!"이라는 경고 메시지를 표시하고 싶다고 해봅시다. 그럼 어떻게 해야 할까요? `=IF(E3<=80,"재고 부족!!!","정상")` 와 같이 입력하면 됩니다! E3 셀의 값이 80보다 작거나 같으면 "재고 부족!!!"이 표시되고, 그렇지 않으면 정상으로 표기됩니다. 이렇게 하면 재고 부족 상황을 한눈에 파악할 수 있겠죠? 👀

VLOOKUP, COUNTIF, SUMIF 함수 활용

더 나아가 `VLOOKUP` 함수를 사용하면 다른 시트에 있는 데이터를 가져와서 재고 관리에 활용할 수도 있습니다. 예를 들어, 제품 코드를 입력하면 자동으로 제품명, 단가, 판매량 등의 정보를 가져올 수 있어요! 마치 마법처럼 말이죠! 🎩✨ `COUNTIF`, `SUMIF` 함수를 사용하면 특정 조건에 맞는 데이터의 개수나 합계를 구할 수 있습니다. 예를 들어 특정 제품의 총 판매량이나 특정 기간 동안의 총 입고량을 쉽게 계산할 수 있어요! 이처럼 다양한 함수를 활용하면 재고 관리를 더욱 효율적으로 자동화할 수 있습니다. 🚀

엑셀 자동화 시 유의사항

하지만, 너무 욕심내서 한 번에 모든 것을 자동화하려고 하면 오히려 복잡해질 수 있어요. 😵 처음에는 간단한 함수부터 시작해서 점차 복잡한 함수를 활용하는 것이 좋습니다. 그리고 무엇보다 중요한 것은 자신의 상황에 맞는 시트를 만드는 것이죠! 남들이 좋다고 하는 시트를 그대로 따라 하기보다는, 자신의 사업 규모와 특성에 맞게 시트를 구성하는 것이 훨씬 효율적입니다. 👍

자, 이제 엑셀 재고 관리 자동화의 기초를 다졌으니, 다음 단계로 넘어가 볼까요? 😉 판매 매출 데이터와 연동하여 더욱 강력한 재고 관리 시스템을 구축하는 방법을 알아보겠습니다! 기대되시죠? 😄 (다음 편을 기대해주세요! 커밍 쑨~!)

 

판매 매출 데이터와 연동한 재고 관리 시트 만들기

자, 이제 엑셀 재고 관리 자동화의 꽃이라고 할 수 있는 판매 매출 데이터 연동 작업에 뛰어들어 볼까요? 이 부분만 잘 따라오시면 재고 관리 업무 시간을 최소 87.5%는 줄일 수 있다는 놀라운 사실! (믿거나 말거나~?^^)

 

이전 판매 매출 데이터 설명은 아래 링크를 눌러서 확인해주세요.

2025.03.04 - [분류 전체보기] - 엑셀로 판매 매출 관리 자동화 표 만들기

 

농담은 접어두고, 진짜 효율 폭발하는 시트 만드는 법, 지금부터 알려드립니다!

기존에 힘들게 만들었던 판매 매출 데이터 시트, 그냥 썩혀두긴 너무 아깝잖아요? 이걸 재고 관리 시트와 연결하면 정말 마법같은 일이 벌어진답니다! 마법의 주문은 바로... "함수" 입니다! (두둥!) 함수만 잘 활용하면 엑셀이 알아서 척척척! 재고를 계산해준다는 사실! 어떤 함수들을 사용해야 하는지, 지금 바로 확인해보시죠!

SUMIF 함수

하지만 VLOOKUP만으로는 완벽한 재고 관리가 어렵습니다! 왜냐?! 입고량 데이터도 반영해야 하기 때문이죠! 이때 등장하는 함수가 바로 SUMIF 함수입니다! SUMIF 함수는 특정 조건에 맞는 값들의 합계를 구해주는 함수인데요, 예를 들어 '딸기잼' 품목의 입고량을 모두 더하고 싶다면 SUMIF 함수를 사용하면 됩니다. '=SUMIF(조건 범위, 조건, 합계 범위)' 이 공식을 사용하면 되는데, '조건 범위'에는 품목 코드가 있는 셀 범위를, '조건'에는 '딸기잼'을, '합계 범위'에는 입고량이 있는 셀 범위를 지정하면 됩니다. 그러면 '딸기잼' 품목의 입고량이 짠! 하고 나타난답니다!

재고량 계산

SUMIF 함수로 입고량과 판매량을 계산했으니, 남은 건 뭐다? 바로 재고량 계산이죠! 재고량은 '재고량 = 입고량 - 판매량' 공식으로 계산하면 됩니다. 이 공식을 엑셀 시트에 적용하면 품목별 현재 재고량을 실시간으로 확인할 수 있습니다! 대박이죠?!

재고 부족 알림 기능

여기서 끝이 아니에요! 재고 부족 알림 기능까지 추가해볼까요? IF 함수를 사용하면 재고량이 특정 수치 이하로 떨어졌을 때 자동으로 경고 메시지를 표시할 수 있습니다. '=IF(조건, 참일 경우 값, 거짓일 경우 값)' 이 공식에서 '조건'에는 '재고량 <= 80'과 같이 재고 부족 기준을 설정하고, '참일 경우 값'에는 "재고 부족!!!"과 같은 경고 메시지를, '거짓일 경우 값'에는 "정상"을 입력하면 됩니다. 그리고 해당 셀을 선택 후 조건부서식으로 "재고부족"시 빨간색으로 변경하면 눈에 더 잘보이겠죠!

 

조건부서식 > 셀 강조 규칙 > 텍스트 포함... 을 선택해서 '특정 텍스트' > '포함' 옆 칸에 '부족'이란 단어가 들어가면 서식 지정에 있는것 처럼 진한 빨강 텍스트가 있는 연한 빨강 채우기가 적용이 됩니다.그러면 재고가 부족할 때마다 빨간색 경고 메시지가 뜨면서 눈에 확! 띄게 된답니다! 이제 재고 부족으로 인한 스트레스는 안녕~!

자, 이렇게 판매 매출 데이터와 연동한 재고 관리 시트를 만들어봤습니다! 어때요, 참 쉽죠? 이제 엑셀의 마법같은 함수들을 활용해서 재고 관리 업무를 효율적으로 처리하고, 소중한 시간을 다른 중요한 업무에 투자해보세요!

월별 품목별 재고 현황 파악 및 부족 알림 설정

후웁~! 드디어 재고 관리의 꽃이라고 할 수 있는 부분에 도착했네요?! 판매 데이터는 착착 쌓이는데, 정작 창고에는 먼지만 쌓여가는 슬픈 현실... 겪어보신 적 있으신가요? ㅠㅠ 이제 엑셀의 마법으로 그런 눈물겨운 상황과는 이별을 고할 시간입니다! ^^ 자, 이번 챕터에서는 앞서 정리한 판매 데이터를 기반으로 월별 품목별 재고 현황을 파악하고, 재고 부족 시 자동 알림 기능까지 설정하는 놀라운 기술(?)을 전수해 드리겠습니다! 준비되셨나요~?!

판매 데이터 활용

자, 먼저 저희가 만들어 놓은 판매 데이터 시트를 한번 떠올려 보시죠! 거기에는 날짜, 품목, 판매 수량 등의 정보가 깔끔하게 정리되어 있을 겁니다. 이 데이터를 활용해서 월별 품목별 재고 현황 시트를 만들어볼 텐데요, 핵심은 바로 `SUMIFS` 함수와 `IF` 함수의 환상적인 콜라보레이션!

SUMIFS 함수 활용

`SUMIFS` 함수는 조건에 맞는 값들의 합계를 구해주는 만능 함수입니다. 예를 들어, 상품별 월별 판매량을 알고 싶다면, 날짜와 상품명이라는 두 가지 조건을 설정해서 `SUMIFS` 함수를 사용하면 됩니다! 참 쉽죠잉~?

=SUMIFS(표1[수량],표1[월],MONTH(B13), 표1[상품명],재고관리!C13)

이렇게 하면 선택한 월에 어떤제품을 몇 개나 팔렸는지 숫자로 딱! 보여줍니다. 이걸 품목별로, 월별로 착착 정리하면 월별 판매량 데이터 완성! 여기에 초기 재고량과 입고량 데이터를 더하고 빼면 현재 재고량을 계산할 수 있겠죠? 수식으로 표현하면 대략 이런 느낌?!

 

현재 재고량 = 초기 재고량 + 입고량 - SUMIFS(판매수량, 조건1, 조건2, ...)

IF 함수 활용 및 재고 부족 알림

이제 `IF` 함수를 활용해서 재고 부족 알림 기능을 만들어 볼까요? `IF` 함수는 조건에 따라 다른 결과를 출력하는 함수입니다. 예를 들어, 현재 재고량이 10개 미만이면 "재고 부족!!!"이라고 표시하고, 그렇지 않으면 "안전 재고"라고 표시하고 싶다면?!

=IF(현재 재고량<10, "재고 부족!!!", "안전 재고")

짜잔~! 이렇게 하면 재고량이 10개 미만인 품목에는 "재고 부족!!!"이라는 무시무시한(?) 경고 메시지가 나타납니다! 덕분에 재고 관리 담당자는 마음의 평화를 얻고(?) 발 빠르게 재고를 확보할 수 있겠죠?! ^^

 

예제 파일을 다운로드해서
👇👇👇직접 변경해가면서 사용해보세요👇👇👇

반응형

재고_관리_자동화_예제.xlsx

0.04MB

조건부 서식 활용

여기서 꿀팁 하나 더! 조건부 서식 기능을 활용하면 "재고 부족!!!" 메시지가 표시된 셀의 배경색을 빨갛게 바꾸는 등 시각적인 효과를 줄 수 있습니다. 그러면 재고 부족 현황을 한눈에 파악하기 더욱 쉽겠죠?!

심화 기능 추가

자, 그럼 이제 조금 더 심화된 내용으로 들어가 볼까요? 앞서 설명드린 기본적인 재고 관리 시스템에 몇 가지 기능을 추가해서 더욱 강력하게 만들어 보겠습니다.

다양한 조건 설정

`SUMIFS` 함수를 이용하면 날짜, 품목 외에도 다양한 조건을 추가하여 재고 현황을 분석할 수 있습니다. 예를 들어 특정 지역의 특정 창고에 보관된 특정 품목의 재고량을 파악하고 싶다면, 지역, 창고, 품목 등의 조건을 추가하여 `SUMIFS` 함수를 사용하면 됩니다. 이렇게 하면 더욱 세분화된 재고 관리가 가능해지겠죠?

동적 차트 생성

월별 품목별 재고 현황을 차트로 시각화하면 데이터의 추이를 한눈에 파악하기 쉬워집니다. 엑셀의 동적 차트 기능을 활용하면 데이터가 업데이트될 때마다 차트도 자동으로 업데이트되도록 설정할 수 있습니다. 이를 통해 재고 변동 상황을 실시간으로 모니터링하고, 선제적인 대응이 가능해집니다.

데이터 유효성 검사

입력되는 데이터의 형식이나 범위를 제한하여 오류 발생을 방지하는 데이터 유효성 검사 기능도 활용해 보세요. 예를 들어 날짜 입력 시 특정 형식(YYYY-MM-DD)을 지정하거나, 수량 입력 시 0보다 큰 정수만 입력하도록 제한할 수 있습니다. 이렇게 하면 데이터의 정확성을 높이고, 불필요한 오류를 예방할 수 있습니다.

매크로 활용

반복적인 작업을 자동화하는 매크로 기능을 활용하면 재고 관리 업무의 효율성을 더욱 높일 수 있습니다. 예를 들어 매월 말에 자동으로 재고 현황 보고서를 생성하거나, 특정 조건에 따라 자동으로 이메일 알림을 보내도록 설정할 수 있습니다. 매크로는 다음에 다루어보도록 하겠습니다.

 

자동화된 재고 관리로 효율적인 운영 달성

후~ 드디어! 기존 판매 매출 관리에 이어 재고 관리까지 자동화하는 엑셀 시트의 끝자락에 도착했습니다! 이제 여러분은 엑셀의 마법으로 재고 관리의 신세계를 경험하게 될 겁니다. (두둥!) 마치 쇼핑몰 CEO가 된 듯한 기분을 느껴보세요! ^^ 자동화된 재고 관리는 단순히 편리함을 넘어, 사업 운영 전반에 놀라운 효율을 불어넣어 줍니다. 마치 꽉 막힌 고속도로에 뻥~ 뚫린 새로운 길이 생긴 것과 같은 효과랄까요?

자, 그럼 구체적으로 어떤 효율을 기대할 수 있는지 살펴볼까요?

시간 절약

우선, 가장 눈에 띄는 변화는 바로 시간 절약입니다. 기존에 수기로, 또는 여러 프로그램을 오가며 관리하던 재고 데이터를 이제 엑셀 시트 하나로 통합 관리할 수 있게 되었습니다. 얼마나 시간이 절약될까요? 글쎄요, 제 경험상으론 최소 30% 이상은 절약될 거라고 확신합니다! (물론, 업무 환경에 따라 차이는 있겠지만요~? ^^) 예를 들어, 주 5일 근무에 하루 8시간 중 2시간을 재고 관리에 사용했다면, 주 10시간, 월 40시간을 절약할 수 있다는 계산이 나옵니다. 이 절약된 시간을 신제품 개발이나 마케팅 전략 수립 등 핵심 업무에 투자한다면?! 사업 성장에 엄청난 추진력을 얻을 수 있겠죠?!

정확도 향상

두 번째 효율은 정확도 향상입니다. 사람이 하는 일에는 항상 실수가 따르기 마련이죠. 특히, 복잡한 숫자들을 다루는 재고 관리는 더더욱 그렇습니다. 하지만 엑셀의 자동화 기능을 활용하면? 휴먼 에러를 최소화하고 정확한 재고 데이터를 확보할 수 있습니다! 수식을 이용한 자동 계산은 물론이고, 조건부 서식을 활용한 시각적인 알림 기능까지! 이젠 더 이상 숫자의 늪에서 헤매지 않아도 됩니다! 데이터 입력 오류율을 최소 5%에서 1% 미만으로 줄일 수 있다고 감히 예상해 봅니다! 이 정도면 거의 완벽에 가깝다고 할 수 있지 않을까요? ^^

의사결정 속도 향상

세 번째는 의사결정 속도 향상입니다. 실시간으로 업데이트되는 재고 현황을 한눈에 파악할 수 있다면, 급변하는 시장 상황에 빠르게 대응할 수 있겠죠? 예를 들어, 특정 품목의 재고가 부족하다는 것을 즉시 파악하고, 발주를 진행하여 판매 기회를 놓치지 않을 수 있습니다. 데이터 분석에 소요되는 시간을 최대 70%까지 단축시킬 수 있다니, 정말 놀랍지 않나요?! 이를 통해 재고 회전율을 높이고, 불필요한 재고 보유 비용을 절감할 수 있습니다. 마치 순간이동을 하는 것처럼 빠른 의사결정! 이것이 바로 자동화된 재고 관리의 힘입니다!

재고 관리 비용 절감

네 번째, 재고 관리 비용 절감 효과입니다. 불필요한 재고는 창고 공간을 차지하고, 관리 비용을 발생시키는 애물단지와 같습니다. 자동화된 재고 관리 시스템은 정확한 수요 예측을 통해 적정 재고량을 유지하도록 도와줍니다. 이를 통해 재고 유지 비용을 최소 10%에서 최대 20%까지 절감할 수 있습니다. 절약된 비용은 신제품 개발이나 마케팅 등 다른 중요한 분야에 투자할 수 있겠죠?! 마치 숨겨진 보물을 찾은 기분일 겁니다! ^^

고객 만족도 향상

다섯 번째, 고객 만족도 향상입니다. 고객이 원하는 제품을 제때 공급하지 못한다면? 당연히 고객 만족도는 떨어질 수밖에 없습니다. 하지만 자동화된 재고 관리를 통해 재고 부족으로 인한 판매 기회 손실을 최소화하고, 고객에게 원활한 제품 공급을 보장할 수 있습니다. 고객 만족도 향상은 곧 매출 증대로 이어지겠죠?! 이 얼마나 멋진 선순환 구조인가요?!

직원 만족도 향상

여섯 번째, 직원 만족도 향상입니다. 지루하고 반복적인 재고 관리 업무에서 벗어나 더욱 창의적이고 생산적인 업무에 집중할 수 있다면?! 직원들의 업무 만족도는 자연스럽게 높아질 것입니다! 행복한 직원은 더욱 열정적으로 일하고, 이는 곧 회사의 성장으로 이어집니다! 모두가 행복해지는 마법! 이것이 바로 자동화의 힘입니다! ^^

자, 이제 자동화된 재고 관리 시스템의 놀라운 효과를 충분히 이해하셨나요? 이제 망설이지 말고 엑셀 재고 관리 자동화의 세계로 뛰어들어 보세요! 새로운 가능성이 당신을 기다리고 있습니다! 더 이상 재고 관리에 스트레스 받지 마시고, 자동화의 힘으로 스마트하게, 그리고 효율적으로 비즈니스를 운영해 보세요! 당신의 성공을 응원합니다! (짝짝짝!)

 

자, 이제 엑셀 시트가 여러분의 창고가 되었습니다! 어때요, 참 쉽죠? 이제 재고 스트레스는 안녕! 밤새도록 재고 파악하느라 야근할 필요도 없어요. 칼퇴근 보장! 치킨 시켜 먹을 시간은 충분하답니다. 혹시 재고 관리하다가 궁금한 점이 생기면 댓글로 남겨주세요. 엑셀 마법사가 친절하게 답변해 드릴게요! (마법 지팡이는 없지만, 함수는 많으니까요!) 이제 엑셀과 함께 효율적인 재고 관리로 사업 번창의 길만 걸으시길 바랍니다! 그럼 다음에 또 만나요! 😉