엑셀 재고관리 핵심, 이동평균 관리 방법 알아보기
엑셀 재고관리 1편 - 이동평균 목차 바로가기
영상 강의
위캔두 회원 여러분을 위한 라이브 강의 전체영상은 아래 링크에서 확인하실 수 있습니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 재고관리의 모든것 - 이동평균 관리예제파일[엑셀고급] 엑셀 재고관리의 모든것 - 이동평균 관리완성파일
재고관리를 바라보는 2가지 관점
재고관리(물류)는 크게 2가지 관점으로 바라볼 수 있습니다. 바로 "회계적인 관점"과 "물류적인 관점" 인데요.
재고관리는 회계적인 관점과 물류적인 관점에서 처리할 수 있습니다. "회계적인 관점"은 단어 그대로 물건이 오고 나갈때 '금액이 일치하는지 확인하는 것'을 이야기 합니다. 이와 더불어 물류 과정중에 발생하는 파손, 손실과 같은 예외적인 상황에 대한 손실금을 어떻게 처리할지 등을 결정하는 것을 중심으로 바라보는 것이 되겠습니다.
같은 원리로 "물류적인 관점"은 오고 나간 물건의 '수량이 일치하는지 확인하는 것'과 더불어 나가는 물건을 어떤 순서로 출고할 것인지, 또는 물건을 어떤 기준으로 적재할 것인지 등의 실무적인 부분도 고려하는 것을 이야기합니다.
재고평가기준을 나누는 이유
재고평가기준은 크게 4가지로 구분됩니다. 재고자산 평가관련하여 아래 간단명료하게 잘 정리된 포스트가 있어 관련 링크도 같이 적어드립니다. (https://st01itstory.tistory.com/7)
- 총평균법
: 전체기간(시작일~종료일)의 재고금액을 총 수량으로 나누는 재고평가방법입니다. - ★이동평균법
: 재고자산의 입/출고가 발생하는 시점마다 재고자산을 재평가하는 방법입니다. '대부분의 ERP에서 사용되는 방식입니다. - ★선입선출법
: 먼저 입고된 재고가 먼저 출고되는 형태로 재고자산을 평가하는 방법입니다. - 후입선출법
: 나중에 입고된 재고가 먼저 출고되는 형태로 재고자산을 평가하는 방법입니다.
이번 강의에서는 재고평가방법의 이론를 다루는 것 보다, "어떠한 이유에서 재고평가기준이 이렇게 여러가지 방법으로 나뉘는지"에 대해 간단한 예제를 살펴보도록 하겠습니다.
오대리는 어제, 오늘 이틀간 100원과 150원에 사탕을 각 10개씩 구매한 뒤, 그 중 10개를 200원에 판매하였습니다. 오빠두 상사의 오대리가 어제, 오늘 각각 사탕을 10개씩 주문했는데, 어제는 개당 100원, 오늘은 개당 150원에 구매했습니다. 그리고 구매한 사탕 중 10개를 개당 200원씩 지인에게 판매하였습니다.
자 이럴 경우, 선입선출과 후입선출로 재고자산을 평가하게되면 어떤 차이가 발생할까요?
맞습니다. 후입선출로 재고평가를 하면 재고의 원가가 더 높게 반영되므로 판매 당일 매출이익이 더 적게 책정되어 단기적으로 보았을 때 세금을 덜 내도 되는 유리한 부분이 발생합니다. (단, 이는 단기적으로 볼 때이며 장기적으로는 재고평가기준에 상관없이 동일한 결과가 계산됩니다.)
재고평가방식에 따라 다르게 계산되는 매출이익
항목 매출액 재고원가 매출이익 선입선출 2,000 1,000 1,000 후입선출 2,000 1,500 500 이동평균 2,000 1,250 750 일반적으로 물가는 인플레이션(물가가 상승하는 효과)가 적용되므로 후입선출을 적용할 경우 단기적으로는 세금을 적게는 효과를 가져올 수 있지만, 우리나라는 법적으로 제한 되어있고 전 세계적으로도 그리 선호되는 방식이 아닙니다. 따라서 보편적으로 사용되는 방식은 '이동평균'과 '선입선출' 방식이며 이번 강의에서는 엑셀 함수만으로 '이동평균' 재고관리를 하는 방법에 대해 알아보겠습니다.
정규화 된 데이터에서 이동평균 관리방법
이동평균 재고평가의 핵심은 '재고 자산의 입/출고가 발생하는 시점마다 재고자산을 재평가하는 것'입니다. 좀 더 쉽게 말하면, '★새로운 재고가 입고될 때마다 재고 원가를 새롭게 계산하는 것'입니다.
이동평균 재고원가를 계산을 공식으로 풀이하면,
재고원가 = (총재고자산 + 신규입고자산) / (총재고수량 + 신규입고수량)으로 풀이할 수 있습니다.
따라서 데이터가 정규화되어 있다면, 누계로 계산되는 총재고수량과 재고금액으로 이동평균 원가를 손쉽게 계산할 수 있는데요. 계산순서를 예제파일과 함께 살펴보겠습니다.
- 예제파일 [2] 시트로 이동합니다. 1/1 에 개당 300원 제품 10개를 구매하고, 1/2에 5개를 판매하였습니다. 따라서 1/2 마감재고는 총 5개, 총 재고자산은 1,500원 입니다.
1월 2일에 5개가 판매된 뒤, 마감재고로 총 5개, 금액은 총 1,500원이 계산됩니다. - 1/3에 개당 500원에 10개를 추가로 구매하였습니다. 따라서 이동평균원가는 아래와 같이 계산됩니다.
=(1,500 + 5,000) / (5 + 10) = 433
개당 500원에 10개를 추가 구매하면, 이동평균 원가는 개당 433원으로 계산됩니다. - 만약 1/3에 5개 판매가 발생하였다면, B7셀과 D7셀에 각각 아래와 같이 값을 입력합니다.
재고 중 5개가 판매되어, 마감재고는 총 10개, 금액은 총 4,333원이 계산됩니다. - 1/4 에 개당 700원에 추가로 10개를 구매한 경우는 어떨까요? 아래와 같이 B8셀, C8셀, 그리고 E8셀에 각각 입고수량과 단가를 작성하면 이동평균원가가 자동으로 계산됩니다.
=(4,333 + 7,000) / (10 + 10) = 567
개당 700원에 10개를 추가로 구매하면, 개당 원가는 567원으로 계산됩니다.
현실적인 이동평균 재고관리 방법
앞서 알아본 것 처럼 정규화된 데이터로 재고관리를 할 수 있다면 이동평균을 기준으로 재고평가를 손쉽게 할 수 있습니다. 하지만 현실에서는 어떨까요?
아무래도 VBA나 별도의 프로그램의 도움없이 재고관리를 위해 데이터를 정규화하여 관리한다는 것은 현실적으로 불가능할 것입니다.
VBA이나 외부 프로그램의 도움없이 정규화된 데이터로 재고관리를 하는 것은 현실적으로 불가능합니다. 따라서 VBA나 기타 외부 프로그램의 도움없이 함수만 사용하여 엑셀만으로 재고관리 툴을 구현하려면 정규화 된 데이터를 2개의 큰 틀로 나눠서 접근할 수 있습니다. 바로 '입고내역'과 '판매내역'을 별도의 표로 나눠서 관리하는 것입니다.
예제파일의 [3] 시트로 이동하여 표를 하나씩 작성하며 이동평균법 재고관리 실전예제 (제품이 1개일 경우)를 완성해보겠습니다. 시트에 수식을 작성하기 전에, 이동평균 재고 원가를 계산하기 위한 공식을 다시 한번 풀어보겠습니다.
= 이동평균 재고원가
= (총재고자산 + 신규입고자산) / (총재고수량 + 신규입고수량)
= (총입고자산 - 총판매자산 + 신규입고자산) / (총입고수량 - 총판매수량 + 신규입고수량)기존 공식을 좀 더 세분화하여 풀어쓰면, 마지막 3번째 공식으로 작성할 수 있습니다. 즉, 이동평균 재고원가는 "지금까지 구매한 총 재고자산에서 판매된 자산을 제한 금액과 수량을 구해서 나눠준 값"으로 계산 할 수 있습니다. 여기서 핵심은 "지금까지 구매한 총 재고자산에서 판매된 자산을 제하는 것"인데요. 수식을 작성하면서 단계별로 알아보겠습니다.
- 입고금액계산 예제파일 [3] 시트로 이동합니다. F8셀에 아래 수식을 입력한 뒤, 아래로 자동채우기 합니다.
=D8*E8
입고내역의 단가 x 수량으로 금액을 계산합니다. - 총입고자산 - 총판매자산 계산 시트의 J열에는 '판매일 기준' 이전까지 구매한 총 재고자산의 합계를 구한 뒤, '판매일 기준' 이전까지 판매된 총 자산을 빼줍니다. SUMIF 함수를 이용하여 아래 수식을 예제파일 J8셀에 입력한 뒤 수식을 아래로 자동채우기합니다. 공식에 사용된 확장범위에 대한 자세한 설명은 기초입문강의 셀 참조방식의 모든것 영상강의를 참고해주세요.
=IF(H8="",0,SUMIF(C:C,"<="&H8,F:F)-SUMIF($H$7:H7,H8,$M$7:M7))
SUMIF 함수를 활용하여 판매일 기준 이전까지의 재고금액을 계산합니다. - 총입고수량 - 총판매수량 계산 시트의 K열에는 '판매일 기준' 이전까지 구매한 총재고수량 - 판매수량을 계산합니다. 이전과 동일하게 SUMIF 함수를 사용하여 계산합니다. 아래 수식을 예제파일 K8셀에 입력한 뒤, 수식을 아래로 자동채우기합니다.
=IF(H8="",0,SUMIF(C:C,"<="&H8,E:E)-SUMIF($H$7:H7,H8,$I$7:I7))
SUMIF 함수를 활용하여 판매일 기준 이전까지의 재고수량을 계산합니다. - 이동평균원가 계산 총 자산과 수량을 구했으므로 재고원가는 자산 ÷ 수량으로 아주 손쉽게 계산할 수 있습니다. 아래 수식을 예제파일 L8셀에 입력한 뒤 아래로 자동채우기합니다.
=IFERROR(J8/K8,0)
재고 금액과 수량이 계산되었으므로, 재고 원가는 금액 ÷ 수량으로 손쉽게 계산할 수 있습니다. - 판매금액(출고금액) 계산 판매된 수량과 원가가 계산되었으므로, 판매된 금액도 자동으로 계산됩니다. 아래 수식을 예제파일 M8셀에 입력한 뒤, 수식을 아래로 자동채우기하여 이동평균 재고관리 예제 시트를 완성합니다.
=L8*I8
판매 재고 금액을 계산하여 표 작성을 마무리합니다. - 이후 새로운 입고내역과 판매내역을 추가하는 방법은 영상강의에서 다뤄드렸으니 자세한 내용은 영상강의를 참고해주시길 바랍니다.
기말재고 내역 계산하기
앞서 입고내역과 판매내역을 계산했으므로, 이제 마무리 작업으로 '현재시점기준 (또는 특정시점 기준)'의 기말재고를 출력하는 표를 추가합니다.
- 기말재고수량 계산 예제파일 [3] 시트의 O8 셀에 아래 수식을 입력합니다. 특정 시점기준, 구매한 수량 - 판매한 수량을 계산하여 '현재 시점에서 들고있는 총 재고 수량'을 계산합니다.
=SUMIF(C:C,"<="&$S$5,E:E)-SUMIF(H:H,"<="&$S$5,I:I)
입고/판매 기록을 참고하여 특정일자 기준 잔고수량을 계산합니다. - 기말재고자산 계산 Q8 셀에 아래 수식을 입력합니다. 아래 수식을 입력하면 '현재 시점의 총 재고 자산'이 계산됩니다.
=SUMIF(C:C,"<="&$S$5,F:F)-SUMIF(H:H,"<="&$S$5,M:M)
입고/판매 기록을 참고하여 특정일자 기준 잔고금액을 계산합니다. - 재고원가 계산 재고 자산과 수량이 계산되었으므로, 재고 원가는 자산 ÷ 수량으로 손쉽게 계산할 수 있습니다. 아래 수식을 P8셀에 입력하여 기말재고 내역을 출력하는 표를 완성합니다.
=Q8/O8
수량과 금액으 계산되었으므로, 재고 단가는 금액 ÷ 수량으로 손쉽게 계산됩니다.
실무에 바로 적용할 수 있는 이동평균 관리방법
지금까지 알아본 내용은 좀 더 쉬운 이해를 위해 '재고 종류가 1개인 경우'를 예제로 살펴보았습니다. 하지만 현업에서는 각 구분별, 항목별로 여러개의 재고를 동시에 관리하게 되는데요.
따라서 이번 강의에서 알아본 내용을 현업에 제대로 적용하려면, 기존에 알아본 입고/판매 내역을 '제품별'로 관리해줘야 합니다. 이에 대한 내용을 예제파일의 [4] 시트에 완성된 수식과 함께 적어드렸는데요.
[4] 시트는 [3] 시트와 다르게 각 제품별로 입고/판매/잔고 내역을 관리하기 위해 [제품명] 열이 추가되었습니다. [제품명] 열이 추가되면서 달라진 점은 아래와 같습니다.
- 기존에 사용했던 SUMIF 함수를 SUMIFS 함수로 바꿔줍니다. (제품이 1개일때는 날짜로만 구분했지만, 제품이 여러개로 추가되면서 '제품명'과 '날짜', 2개의 조건으로 계산합니다.)
수량과 금액 계산에 적용하던 조건이 기존 1개(날짜)에서 2개(날짜, 제품명)으로 증가하였습니다. - 잔고내역에 '제품명 고유값'을 입력합니다. 잔고내역을 계산하려면 각 기준이 될 제품명의 고유값이 필요합니다. 제품명 대신 다른 고유값 (예: 제품코드, 시리얼번호 등)을 사용해도 무방합니다.
기말재고를 계산하기 위한 제품의 고유값을 입력합니다.
실무 적용시 추가 고려사항
이번 강의에서 알아본 내용을 실무에 적용하려면 강의에서 다룬 것 외에도 다양한 변수가 추가될 것입니다. 예를들어 입고내역에는 '송장번호, 구매처, 출고지' 등이 될 것이고, 판매내역에는 '영수증번호, 고객명, 배송지' 등의 정보가 추가 될 것입니다.
따라서 상황에 따라 제품명 이외에 필요한 항목을 적절히 추가한다면, 별도의 프로그램 없이 엑셀 함수만으로도 이동평균 재고관리를 손쉽게 할 수 있게 됩니다.
단 한가지 주의 사항으로는, 엑셀을 사용하는 것과 외부 프로그램을 사용하는 것을 비교하면 엑셀을 사용하는 것이 아무래도 손이 더 많이가게 되므로, 너무 디테일한 항목은 제외하고 필수 항목만 선정하여 추가하는 것을 권장드립니다.
