엑셀에서 재고 경과(Aging) 보고서를 효율적으로 작성하는 방법을 찾고 계신가요? 이 글이 큰 도움이 될 것입니다. 재고 경과 보고서는 각 제품의 재고가 소진되기까지 걸리는 기간을 나타내며, 이 기간을 분석하면 제품을 느리게 회전되는 재고, 빠르게 회전되는 재고, 정체된 재고로 손쉽게 분류할 수 있습니다.
그럼 본문으로 바로 들어가겠습니다.
실습 파일 다운로드
엑셀에서 재고 경과 보고서를 만드는 4단계
재고 경과 보고서를 작성하려면 기본 레이아웃을 구성하고, 수식을 활용해 필요한 값을 계산한 뒤, 데이터 집합을 피벗 테이블(Pivot Table)로 변환하여 보고서의 가독성을 높이는 과정이 필요합니다. 아래 4단계에서 각 과정을 자세히 살펴보겠습니다.
본 가이드는 Microsoft Office 365 버전을 기준으로 작성되었으며, 사용 중인 다른 버전에서도 동일하게 따라 할 수 있습니다.
1단계: 기본 레이아웃 만들기
먼저 재고 경과 보고서와 관련 데이터 세트의 기본 골격을 구성합니다.
➤ 아래 그림처럼 '재고(Inventory)' 시트에 재고 경과 보고서의 기본 양식을 만듭니다.
여기에는 제품별 제품 ID(Product Id), 제품명(Product), 단가(Unit Price), 수량(Quantity), 유통기한(Expiry Date)이 입력되어 있습니다. 이 항목에 여러분의 실제 재고 데이터를 입력하면 됩니다.
이후 계산을 위해 총액(Total Price), 잔여기간(Due Time), 상태(Condition) 열을 추가했습니다.
이제 '분류(Category)' 시트에서 제품 상태를 분석하기 위한 또 다른 양식을 만듭니다.
➤ 잔여기간에 따라 재고의 상태나 경과 정도를 나타낼 수 있도록 제품 카테고리 목록을 작성합니다. 해당 범위의 이름도 limit으로 지정해 두었습니다.
2단계: 수식으로 재고 경과 보고서 값 계산하기
➤ 제품의 총 금액을 계산하려면 셀 E4에 다음 수식을 입력합니다.
=C4*D4
여기서 C4는 제품 '사과(Apple)'의 단가(Unit Price)이고, D4는 수량(Quantity)입니다.
➤ Enter 키를 누른 후 채우기 핸들(Fill Handle)을 아래로 드래그합니다.
이렇게 하면 총액(Total Price) 열에 모든 제품의 총 금액이 계산됩니다.
➤ 다음은 오늘 날짜(2022-05-19)를 기준으로 제품 유통기한까지 남은 일수를 계산합니다.
=IF((F4-TODAY())<0,0,F4-TODAY())
여기서 F4는 제품의 유통기한(Expiry Date)이며, TODAY() 함수는 오늘 날짜인 2022-05-19를 반환합니다.
두 값의 차이가 음수가 되면 IF 함수가 0을 반환하고, 양수일 경우 그 차이값이 잔여기간(Due Time)으로 표시됩니다.
➤ 마찬가지로 Enter 키를 누른 후 채우기 핸들을 아래로 드래그합니다.
이제 오늘 날짜 기준으로 각 제품의 남은 기간이 모두 계산되었습니다.
➤ 다음 수식을 적용하면 잔여기간 값을 '분류(Category)' 시트에서 조회하여 제품의 상태를 자동으로 판별할 수 있습니다.
=VLOOKUP(G4, limit,2, TRUE)
여기서 G4는 조회할 값(잔여기간), limit은 조회 범위로 지정한 이름 영역, 2는 반환할 열 번호, TRUE는 유사 일치 옵션입니다.
➤ Enter 키를 누른 후 채우기 핸들을 아래로 드래그합니다.
이제 상태(Condition) 열에 모든 재고의 상태가 표시됩니다.
3단계: 피벗 테이블로 재고 경과 보고서 만들기
이번 단계에서는 재고의 경과 상태를 한눈에 파악할 수 있도록 피벗 테이블을 만들어 데이터를 정리합니다.
➤ 삽입 탭 >> 피벗 테이블 옵션으로 이동합니다.
그러면 '피벗 테이블 만들기' 대화상자가 열립니다.
➤ '재고(Inventory)' 시트에서 표의 범위를 선택하고 확인을 누릅니다.
새 시트가 생성되며, 피벗 테이블(PivotTable) 영역과 피벗 테이블 필드(PivotTable Fields) 영역 두 부분으로 화면이 구성됩니다.
➤ 제품 ID(Product ID)와 제품명(Product) 필드는 행(Rows) 영역으로, 수량(Quantity)과 총액(Total Price) 필드는 값(Values) 영역으로, 상태(Condition) 필드는 열(Columns) 영역으로 각각 드래그합니다.
값(Values) 영역의 필드 이름이 길어지지 않도록 다음과 같이 이름을 변경할 수 있습니다.
➤ 수량 합계(Sum of Quantity) 필드의 드롭다운 버튼을 클릭하고 값 필드 설정(Value Field Settings) 옵션을 선택합니다.
값 필드 설정 대화상자가 열리면,
➤ 사용자 지정 이름(Custom Name) 입력란에 Q 등 원하는 이름을 입력하고 확인을 누릅니다.
➤ 같은 방식으로 간결함을 위해 총액 합계(Sum of Total Price) 필드도 P로 변경합니다.
마지막으로 값(Values) 영역에 새 필드 이름 두 개가 표시됩니다.
아래는 제품 상태를 기준으로 수량과 금액이 정리된 피벗 테이블입니다.
4단계: 피벗 테이블 꾸미기
마지막 단계는 피벗 테이블을 더욱 보기 좋게 다듬는 것입니다.
이 표에서는 총계 값이 불필요하므로 간단히 제거할 수 있습니다.
➤ 피벗 테이블 분석 탭 >> 옵션 드롭다운 >> 옵션 메뉴로 이동합니다.
피벗 테이블 옵션 대화상자가 열리면,
➤ 합계 및 필터(Totals & Filters) 탭을 선택한 후 총계(Grand Totals) 관련 옵션의 체크를 해제합니다.
➤ 마지막으로 확인을 누릅니다.
이렇게 하면 행과 열의 총계 값이 제거됩니다.
➤ 디자인 탭으로 이동해 원하는 테마를 선택하면 디자인도 변경할 수 있습니다.
이것으로 재고 경과 보고서(Inventory Aging Report)의 최종 결과물이 완성되었습니다.
마무리
이 글에서는 엑셀에서 재고 경과 보고서를 작성하는 단계별 방법을 알아보았습니다. 실무에서 재고 관리에 유용하게 활용하시길 바랍니다. 추가로 궁금한 점이나 제안 사항이 있다면 댓글로 자유롭게 남겨주세요.
함께 읽으면 좋은 글
- 엑셀로 수익·비용 보고서 만들기 (예제 3가지)
- 엑셀 VBA로 PDF 형식 보고서 생성하기 (빠른 방법 3가지)
- 엑셀로 판매 MIS 보고서 만들기 (초보자 가이드)
- 엑셀 데이터에서 PDF 보고서 추출하기 (방법 4가지)
- 엑셀 MIS 보고서 준비하기 (실전 예제 2가지)