엑셀 재고관리의 모든것 1편 - 이동평균 관리

실습 가이드

위캔두 회원 여러분을 위한 라이브 강의 전체영상은 아래 링크에서 확인하실 수 있습니다.

재고관리를 바라보는 2가지 관점

재고관리(물류)는 크게 2가지 관점에서 접근할 수 있습니다. 바로 "회계적인 관점"과 "물류적인 관점"입니다.

"회계적인 관점"은 단어 그대로 물건이 입고·출고될 때 '금액이 일치하는지 확인하는 것'을 의미합니다. 여기에 더해 물류 과정에서 발생하는 파손이나 손실 같은 예외 상황의 손실금을 어떻게 처리할지 결정하는 부분까지 포함됩니다.

같은 원리로 "물류적인 관점"은 입출고된 물건의 '수량이 일치하는지 확인하는 것'은 물론, 출고 순서를 어떻게 정할지, 적재 기준을 어떻게 가져갈지 등 실무적인 운영까지 함께 고려하는 시각입니다.

재고평가기준을 나누는 이유

재고평가기준은 크게 4가지로 구분됩니다. 재고자산 평가에 관해 간단명료하게 정리된 포스트가 있어 관련 링크도 함께 적어드립니다. (https://st01itstory.tistory.com/7)

이번 강의에서는 재고평가방법의 이론을 자세히 다루기보다, "어떤 이유에서 재고평가기준이 이렇게 여러 가지 방법으로 나뉘는지"를 간단한 예제와 함께 살펴보겠습니다.

오빠두 상사의 오대리가 어제와 오늘 각각 사탕을 10개씩 주문했는데, 어제는 개당 100원, 오늘은 개당 150원에 구매했습니다. 그리고 구매한 사탕 중 10개를 개당 200원에 지인에게 판매하였습니다.

이 경우, 선입선출과 후입선출로 재고자산을 평가하면 어떤 차이가 발생할까요?

후입선출로 재고를 평가하면 재고의 원가가 더 높게 반영되므로, 판매 당일의 매출이익이 더 적게 책정되어 단기적으로 보았을 때 세금을 덜 내는 효과가 발생합니다. (단, 이는 단기 관점에서의 차이이며, 장기적으로는 재고평가기준에 상관없이 동일한 결과로 수렴합니다.)

재고평가방식에 따라 다르게 계산되는 매출이익

일반적으로 물가는 인플레이션의 영향을 받아 점차 상승하므로, 후입선출을 적용하면 단기적으로는 세금을 줄이는 효과를 가져올 수 있습니다. 그러나 우리나라에서는 법적으로 제한되어 있고, 전 세계적으로도 선호되는 방식이 아닙니다. 따라서 실무에서 보편적으로 사용되는 방식은 '이동평균'과 '선입선출'이며, 이번 강의에서는 엑셀 함수만으로 '이동평균' 재고관리를 구현하는 방법을 다룹니다.

정규화 된 데이터에서 이동평균 관리방법

이동평균 재고평가의 핵심은 '재고자산의 입·출고가 발생하는 시점마다 재고자산을 재평가하는 것'입니다. 좀 더 풀어 말하면, '★새로운 재고가 입고될 때마다 재고 원가를 새롭게 계산하는 것'입니다.

이동평균 재고원가의 계산을 공식으로 정리하면 다음과 같습니다.

따라서 데이터가 정규화되어 있다면, 누계로 계산되는 총재고수량과 재고금액으로 이동평균 원가를 손쉽게 산출할 수 있습니다. 계산 순서를 예제파일과 함께 단계별로 살펴보겠습니다.

1. 예제파일 [2] 시트로 이동합니다. 1/1에 개당 300원 제품 10개를 구매하고, 1/2에 5개를 판매하였습니다. 따라서 1/2 마감재고는 총 5개, 총 재고자산은 1,500원입니다.

   

2. 1/3에 개당 500원에 10개를 추가로 구매하였습니다. 이때 이동평균원가는 다음과 같이 계산됩니다.
   =(1,500 + 5,000) / (5 + 10) = 433

   

3. 만약 1/3에 5개 판매가 발생하였다면, B7셀과 D7셀에 각각 아래와 같이 값을 입력합니다.

   

4. 1/4에 개당 700원에 추가로 10개를 구매한 경우는 어떨까요? B8셀, C8셀, E8셀에 각각 입고수량과 단가를 입력하면 이동평균원가가 자동으로 계산됩니다.
   =(4,333 + 7,000) / (10 + 10) = 567

   

현실적인 이동평균 재고관리 방법

앞서 살펴본 것처럼 정규화된 데이터로 재고관리를 할 수 있다면 이동평균 기준의 재고평가가 매우 수월해집니다. 그러나 현실에서는 어떨까요?

실제로는 VBA나 별도의 프로그램의 도움 없이 재고관리를 위해 데이터를 정규화하여 운영하는 것은 거의 불가능에 가깝습니다.

따라서 외부 프로그램의 도움 없이 함수만으로 엑셀에서 재고관리 툴을 구현하려면, 정규화된 데이터를 2개의 큰 틀로 나누어 접근합니다. 바로 '입고내역'과 '판매내역'을 별도의 표로 분리하여 관리하는 방식입니다.

예제파일의 [3] 시트로 이동하여 표를 하나씩 작성하면서 이동평균법 재고관리 실전예제(제품이 1개일 경우)를 완성해보겠습니다. 시트에 수식을 작성하기 전, 이동평균 재고원가 계산 공식을 다시 한번 정리합니다.

기존 공식을 좀 더 세분화하여 풀어쓰면 마지막 세 번째 형태로 정리할 수 있습니다. 즉, 이동평균 재고원가는 "지금까지 구매한 총 재고자산에서 판매된 자산을 차감한 금액과 수량으로 나눈 값"으로 산출할 수 있습니다. 핵심은 "지금까지 구매한 총 재고자산에서 판매된 자산을 차감하는 부분"이며, 수식을 작성하면서 단계별로 살펴보겠습니다.

1. 입고금액 계산 예제파일 [3] 시트로 이동합니다. F8셀에 아래 수식을 입력한 뒤, 아래로 자동채우기합니다.
   =D8*E8

   

2. 총입고자산 - 총판매자산 계산 시트의 J열에는 '판매일 기준' 이전까지 구매한 총 재고자산의 합계를 구한 뒤, '판매일 기준' 이전까지 판매된 총 자산을 차감합니다. SUMIF 함수를 활용하여 아래 수식을 예제파일 J8셀에 입력한 뒤 수식을 아래로 자동채우기합니다. 공식에 사용된 확장범위에 대한 자세한 설명은 기초입문강의 셀 참조방식의 모든것 영상강의를 참고해주세요.
   셀 참조방식의 모든 것 - 기초입문강의
   =IF(H8="",0,SUMIF(C:C,"<="&H8,F:F)-SUMIF($H$7:H7,H8,$M$7:M7))

   

3. 총입고수량 - 총판매수량 계산 시트의 K열에는 '판매일 기준' 이전까지 구매한 총 재고수량에서 판매수량을 차감하여 계산합니다. 이전과 동일하게 SUMIF 함수를 사용합니다. 아래 수식을 예제파일 K8셀에 입력한 뒤, 수식을 아래로 자동채우기합니다.
   =IF(H8="",0,SUMIF(C:C,"<="&H8,E:E)-SUMIF($H$7:H7,H8,$I$7:I7))

   

4. 이동평균원가 계산 총 자산과 수량을 모두 구했으므로, 재고원가는 자산 ÷ 수량으로 손쉽게 계산할 수 있습니다. 아래 수식을 예제파일 L8셀에 입력한 뒤 아래로 자동채우기합니다.
   =IFERROR(J8/K8,0)

   

5. 판매금액(출고금액) 계산 판매된 수량과 원가가 계산되었으므로, 판매 금액도 자동으로 산출됩니다. 아래 수식을 예제파일 M8셀에 입력한 뒤, 수식을 아래로 자동채우기하여 이동평균 재고관리 예제 시트를 완성합니다.
   =L8*I8

   

6. 이후 새로운 입고내역과 판매내역을 추가하는 방법은 영상강의에서 자세히 다루었으므로, 보다 자세한 내용은 영상강의를 참고하시기 바랍니다.

기말재고 내역 계산하기

앞서 입고내역과 판매내역을 계산했으므로, 이제 마무리 작업으로 '현재 시점 기준(또는 특정 시점 기준)'의 기말재고를 출력하는 표를 추가합니다.

1. 기말재고수량 계산 예제파일 [3] 시트의 O8셀에 아래 수식을 입력합니다. 특정 시점 기준으로 구매한 수량에서 판매한 수량을 차감하여 '현재 시점에서 보유 중인 총 재고수량'을 계산합니다.
   =SUMIF(C:C,"<="&$S$5,E:E)-SUMIF(H:H,"<="&$S$5,I:I)

   

2. 기말재고자산 계산 Q8셀에 아래 수식을 입력하면 '현재 시점의 총 재고자산'이 계산됩니다.
   =SUMIF(C:C,"<="&$S$5,F:F)-SUMIF(H:H,"<="&$S$5,M:M)

   

3. 재고원가 계산 재고자산과 수량이 산출되었으므로, 재고원가는 자산 ÷ 수량으로 손쉽게 계산됩니다. 아래 수식을 P8셀에 입력하여 기말재고 내역을 출력하는 표를 완성합니다.
   =Q8/O8

   

실무에 바로 적용할 수 있는 이동평균 관리방법

지금까지 살펴본 내용은 좀 더 쉬운 이해를 위해 '재고 종류가 1개인 경우'를 예제로 다루었습니다. 그러나 현업에서는 각 구분별, 항목별로 여러 개의 재고를 동시에 관리하게 됩니다.

따라서 이번 강의 내용을 현업에 제대로 적용하려면, 앞서 살펴본 입고·판매 내역을 '제품별'로 관리해야 합니다. 이에 대한 내용을 예제파일의 [4] 시트에 완성된 수식과 함께 정리해두었습니다.

[4] 시트는 [3] 시트와 다르게 각 제품별로 입고·판매·잔고 내역을 관리하기 위해 [제품명] 열이 추가되었습니다. [제품명] 열이 추가되면서 달라진 점은 다음과 같습니다.

1. 기존에 사용했던 SUMIF 함수를 SUMIFS 함수로 교체합니다. (제품이 1개일 때는 날짜로만 구분했지만, 제품이 여러 개로 늘어나면서 '제품명'과 '날짜'라는 2개의 조건으로 계산해야 하기 때문입니다.)

   

2. 잔고내역에 '제품명 고유값'을 입력합니다. 잔고내역을 계산하려면 각 기준이 될 제품명의 고유값이 필요합니다. 제품명 대신 다른 고유값(예: 제품코드, 시리얼번호 등)을 사용해도 무방합니다.

   

실무 적용시 추가 고려사항

이번 강의에서 살펴본 내용을 실무에 적용하려면, 강의에서 다룬 항목 외에도 다양한 변수가 추가됩니다. 예를 들어 입고내역에는 '송장번호, 구매처, 출고지' 등이, 판매내역에는 '영수증번호, 고객명, 배송지' 등의 정보가 추가될 수 있습니다.

따라서 상황에 따라 제품명 외에 필요한 항목을 적절히 추가한다면, 별도의 프로그램 없이 엑셀 함수만으로도 이동평균 재고관리를 충분히 운영할 수 있습니다.

다만 한 가지 유의할 점은, 외부 프로그램과 비교하면 엑셀은 아무래도 운영 손이 더 많이 가게 되므로 너무 디테일한 항목은 제외하고 필수 항목만 선정하여 추가하는 것을 권장드립니다.