엑셀로 재고관리시스템 구축하기! 유용한 엑셀 함수 3가지

출처: Pixabay

오늘은 엑셀로 재고관리시스템 구축하는 방법을 살펴보려고 합니다. 이커머스 사업이 성공하기 위해선 다양한 상품을 효율적으로 관리하는 것이 중요한데요. 유료 프로그램 도입 없이 엑셀을 활용하여 체계적인 재고 관리 시스템을 구축할 수 있습니다.

오늘은 이커머스 플랫폼 운영에 맞는 엑셀 재고 관리 시트 구성 방법과 함께, 재고 관리 자동화에 유용한 엑셀 함수를 소개할 예정입니다. 실제 예시를 통해 엑셀을 활용한 재고관리 자동화 방법을 자세히 알아볼까요?

📖 재고회전율을 높이는 효율적인 물류 창고 관리 방법 A to Z – 알아보기

1. 엑셀 재고관리 시트 양식

출처: Pixabay

엑셀은 다양한 데이터를 효율적으로 관리하고 분석하는 데 최적화된 도구입니다. 이커머스 플랫폼 운영에 맞춰 엑셀 재고 관리 시스템을 구축하고자 할 때 다음과 같은 재고 관리 시트 구성을 참고해 보세요.

☝🏻재고관리 엑셀 양식의 핵심 포인트

• 수식 활용하기: 엑셀로 재고관리 자동화를 하고자 한다면 수량, 단가, 총액 등을 자동 계산하는 수식을 활용해보세요. 계산 오류를 줄이고 시간을 절약할 수 있어요.
• 조건부 서식 활용하기: 재고가 부족하거나 발주해야 할 시점이 되었을 때 자동으로 표시되도록 조건부 서식을 설정하면 편리하게 재고관리 자동화시킬 수 있어요.
• 데이터 시각화: 재고관리시스템에서 재고 변동 추이를 그래프나 차트로 시각화하면 한눈에 파악하기 좋아요.

1) 재고 현황 시트

가장 먼저, 재고관리 엑셀 양식인데요. 위 재고 현황 시트는 쇼핑몰의 모든 상품 재고를 한눈에 파악하고 관리할 수 있도록 설계된 시트 예시인데요. 고유 식별 코드, 제품명, 카테고리, 현재 재고 수량, 입고 날짜, 출고 날짜 등의 항목을 정리하면 현재 남아있는 재고를 실시간으로 확인하고 관리할 수 있습니다.

2) 주문 내역 시트

주문 내역 시트는 고객의 주문 정보를 체계적으로 관리하고, 주문 처리 과정을 효율적으로 추적하려는 용도로 활용 가능합니다. 미리 만들어진 주문 내역 시트 템플릿을 활용하면 시간을 절약하고, 일관된 양식으로 데이터를 관리하여 주문 오류를 최소화할 수 있습니다.

주문 내역 시트를 쇼핑몰 재고 관리 시스템과 연동하고 엑셀의 다양한 기능을 활용하면 재고 관리의 효율성을 높일 수 있습니다. 또한, 주문 내역과 재고 데이터를 분석하면 판매량이 높은 상품, 인기 있는 상품 조합 등 다양한 정보를 얻어 상품 기획 및 재고 관리에 활용할 수 있습니다.

3) 재고 경고 시트

재고 경고 시트는 특정 품목의 재고 수량이 미리 설정된 최소 재고 수량 이하로 떨어졌을 때 자동으로 경고를 생성하여 적시에 재고를 보충할 수 있도록 돕는 용도로 활용할 수 있습니다. 제품 부족으로 인한 판매 기회 상실이나 생산 차질을 방지하고 불필요한 재고 적체를 줄여 재고 관리 비용을 절감할 수 있죠.

조건부 서식을 활용하면 재고 부족 현황을 시각적으로 쉽게 파악할 수 있을 뿐만 아니라, 다양한 규칙을 설정하여 더욱 복잡한 조건도 표현할 수 있습니다. 예를 들어, 위 예시 표에서는 현재 재고 수량이 최소 재고 수량보다 적은 A 상품과 C 상품의 셀을 빨간색으로 표시하고, VBA 매크로를 통해 담당자에게 알림 메일을 발송하는 등의 자동화된 시스템을 구축할 수 있습니다.

4) 반품 및 손상 시트

반품 및 손상 시트는 제품의 반품 또는 손상에 대한 상세한 정보를 기록하여, 재고 손실을 정확하게 파악하고 그 원인을 분석하려는 목적으로 활용됩니다. 일반적으로 날짜, 상품 코드, 수량, 반품/손상 사유, 처리 상태 등을 포함하여 시트를 구성합니다.

5) 재고 분석 시트

마지막으로 재고 분석 시트 예시입니다. 엑셀 재고 관리 시트를 활용하면 재고 회전율 분석을 통해 상품의 판매 속도를 측정하거나, 적정 재고 수준을 파악하여 판매 부진 상품을 식별할 수 있습니다. 혹은, 재고 대비 수익률 분석을 통해 재고 자산 대비 얼마나 많은 매출을 발생시키는지를 분석하여 재고 관리 효율성을 평가할 수도 있습니다.

✔️ 재고관리시스템 시트 수식 활용하기

• 재고 회전율 = 판매 수량/평균 재고
• 평균 재고 = 시작 재고+종료재고/2 (기간 중 재고의 평균 값)
• 재고 대비 매출 = 매출액/평균 재고

2. 엑셀 재고관리 함수, 어떤 게 많이 쓰이나요?

출처: Pixabay

여기까지 재고관리 엑셀 양식을 살펴보았는데요. 더욱 효과적으로 재고관리 엑셀 양식을 더방법이 있는데, 바로 함수입니다. 함수를 활용하면 복잡한 계산을 자동화하고 재고관리시스템 데이터를 효율적으로 분석할 수 있습니다.

지금부터 다양한 엑셀 함수 중에서도 재고관리 자동화에 특히 유용한 함수들을 꼼꼼히 살펴보고 각 함수가 엑셀 재고관리에서 어떤 역할을 하는지 자세히 알아보도록 하겠습니다.

📢 생산성 향상을 위한 재고 관리 방법 – 알아보기

1) VLOOKUP & IF

VLOOKUP은 찾고자 하는 값을 기준으로 다른 열의 값을 찾아주는 함수입니다. 하지만 첫 번째 열을 기준으로만 검색할 수 있다는 한계가 있어서 더욱 유연한 엑셀 재고관리를 위해 XLOOKUP 함수를 사용하거나, 다른 함수를 조합하여 활용하기도 합니다. 다음으로 IF는 조건의 참/거짓을 판단한 후 특정 결과값을 반환하는 함수입니다.

위 표는 실제 재고관리 엑셀 양식과 함수 활용 예시입니다. 주문 내역 시트의 상품명 열에 VLOOKUP 함수를 사용하여 재고 관리 시트의 “상품명” 열에서 해당 상품의 재고 수량 열의 값을 가져온 뒤, 추가로 IF 함수를 활용해 재고 수량이 주문 수량보다 작으면 재고 부족이, 그렇지 않으면 충분이 표시되도록 설정할 수 있습니다.

2) SUMIF

SUMIF는 지정한 조건을 충족하는 범위 값을 합산하는 함수로, 제품별 판매수량 또는 금액을 정리할 때 활용하기 좋습니다. 예를 들어, 재고관리시스템에서 별도의 입출고 내역 테이블을 두고, 재고 관리 표에서 SUMIF 함수를 사용하여, 해당 품목의 입고량과 출고량을 합산하면 ‘현 재고’를 계산할 수 있죠.

이렇게 하면 수기로 계산하는 번거로움을 줄이고, 재고관리시스템 데이터 입력 오류를 최소화하여 정확한 재고 현황을 파악할 수 있습니다. 실제 활용 예시를 보여드리겠습니다. 위 표에서 숫자가 포함된 열에서 5보다 큰 값만 합산하려고 하는 상황일 때, =SUMIF(B2:B25,”>5″) 수식을 사용할 수 있습니다.

• 절대 참조: 만약 수식을 복사하여 다른 셀에 붙여넣을 때 열 참조가 변경되는 것을 방지하려면 열을 절대 참조(예:$B:$B, $D:$D)로 고정할 수 있습니다.
• 범위 지정: 전체 열을 지정하는 대신 데이터가 있는 실제 범위를 지정할 수 있습니다.

3) COUNTIF

💡COUNTIF(범위, 조건)

• 범위: 조건을 검사할 셀 범위를 지정합니다.
• 조건: 셀이 만족해야 하는 조건을 입력합니다. 숫자, 텍스트, 비교 연산자(>, <, = 등), 와일드카드(*, ?) 등을 사용할 수 있습니다.

쇼핑몰 재고관리에 유용한 COUNTIF 함수는 엑셀에서 특정 조건에 맞는 셀의 개수를 세는 용도입니다. 따라서, 주어진 범위 내에서 특정 값이나 조건을 만족하는 셀이 몇 개인지 확인하고 싶을 때 활용할 수 있습니다. 재고관리시스템 시트에서 특정 사이즈의 의류 개수를 세고자 할 때, 특정 가격대의 상품 개수를 셀 때 유용합니다.

✔️ COUNTIF 함수 활용 예시

1. M 사이즈 의류 개수 세기: 사이즈 열(예:B열)에서 “M”이라는 문자열이 포함된 셀의 개수를 세어 M 사이즈의 의류 총 개수를 구할 수 있습니다. =COUNTIF(B:B, “M”)
2. 2023년 1월 1일 이후 입고된 상품 개수 세기: 입고 날짜 열(예: C열)에서 2023년 1월 1일 이후의 날짜가 포함된 셀의 개수를 세어 2023년 1월에 입고된 상품 총 개수를 구할 수 있습니다. =COUNTIF(C:C, “>=2023-01-01”)

4) 조건부 서식 & 피벗테이블

• 조건부 서식: 조건부 서식은 셀의 값에 따라 서식(색상, 글꼴, 아이콘 등)을 자동으로 변경하는 기능입니다. 특정 조건을 만족하는 데이터를 시각적으로 강조하여 데이터 분석을 쉽게 해줍니다. 특정 범위 내의 값을 강조 표시할 때, 판매량이 목표치를 초과한 셀을 녹색으로 표시할 때 등 다양한 상황에서 활용할 수 있어 재고관리 자동화에 유용합니다.
• 피벗 테이블: 피벗 테이블을 통해 복잡한 재고관리시스템 데이터를 행, 열, 값 영역으로 나누어 원하는 정보를 빠르게 추출할 수 있습니다. 제품별 월별 매출을 비교하고 싶은데, 특정 지역의 데이터만 추출하여 분석하고 싶은 상황이라고 가정해보겠습니다. 이때, 피벗 테이블에서 행에 ‘제품’, 열에 ‘월’, 값에 ‘매출액’을 설정하고, 필터에 ‘지역’을 추가하여 원하는 지역만 선택하면 됩니다.

3. 쉬운 반품 물류 관리는? 리터니즈!

출처: 리터니즈

재고 관리는 모든 사업의 핵심입니다. 하지만, 이중에서도 반품 물류 관리는 많은 기업들에게 부담으로 작용합니다. 재고를 잘 관리하기 위해서는 엑셀 활용도 중요한 요소이나, 현장에서의 효율적인 관리가 더욱 중요합니다. 더군다나 반품 물류는 복잡한 절차로 인해 처리가 더욱 까다롭게 느껴질 수 있죠.

그렇다면 어떻게 해야 할까요? 가장 쉬운 방법은 바로 ‘반품 물류 대행 업체’를 활용하는 것입니다. 대행 업체는 반품 접수부터 재고 관리까지 모든 과정을 대신 처리해주므로, 이를 통해 셀러는 핵심 사업에 집중하고, 반품으로 인한 시간과 비용을 절약할 수 있습니다.

반품 물류 처리, 리터니즈와 함께!

복잡한 반품 처리 과정에서 벗어나 핵심 사업에 집중하세요. 리터니즈는 경쟁력 있는 수거 비용, 자동화된 검수 시스템을 갖춘 반품 물류 전문 업체입니다.

① 빠른 검수 및 반품 처리: 반품된 상품의 상태를 꼼꼼하고 빠르게 검사하여 입고 후 평균 1영업일 이내에 반품 처리를 완료합니다.

② 높은 재판매율: 재판매 가능한 상품은 양품화 과정을 거쳐 바로 재판매 가능한 상태로 입고드립니다. 따라서 빠른 재판매와 낮은 재고율을 유지하고자 하는 셀러에게 유리합니다.

③ 재고처분: 재고로 쌓여있는 제품들을 리퍼샵과 사입업체 연결을 통해 현금화시키고, 판매하기 어려운 제품은 사회복지 시설 등에 기부하여 기부금영수증 발행으로 세금감면의 혜택을 제공합니다.

④비용 효율성: 반품 수거 최저 ‘1,800원대’라는 합리적인 가격으로 전국 어디서든 반품 상품을 수거하여 셀러의 부담을 덜어줍니다.

반품 처리, 정말 귀찮고 복잡하죠? 다양한 반품 사유와 상품 때문에 골머리를 앓고 계시다면 리터니즈를 이용해 보세요. 복잡한 절차를 간소화해 드리고, 비용과 시간을 절약하여 효율적인 사업 운영을 지원합니다.

👇🏻반품 물류 처리 시간 줄이고 싶다면?👇🏻

⏰리터니즈로 반품 물류 해결하기