Nếu bạn đang tìm cách tạo báo cáo tồn kho theo độ tuổi (Inventory aging) trong Excel, bài viết này sẽ hữu ích. Báo cáo tồn kho theo độ tuổi cho biết thời gian từng mặt hàng tồn kho trước khi hết hàng, qua đó phân loại sản phẩm thành tồn kho chậm chuyển, nhanh chuyển hoặc ngưng trệ.
Tải Workbook
4 Bước Tạo Báo Cáo Tồn Kho Theo Độ Tuổi Trong Excel
Tạo báo cáo tồn kho theo độ tuổi cần các bước: xây dựng khung cơ bản, tính toán giá trị bằng công thức, chuyển dữ liệu thành Pivot Table để báo cáo dễ hiểu hơn. Dưới đây 4 bước chi tiết. Chúng tôi dùng Microsoft Office 365, bạn có thể dùng phiên bản khác.
Bước 01: Tạo Khung Cơ Bản
Đây là khung cơ bản của báo cáo Inventory Aging và tập dữ liệu liên quan.
➤ Hình dưới minh họa khung báo cáo cơ bản trên sheet Inventory (Tồn kho).
Có các cột: Mã sản phẩm (Product IDs), Tên sản phẩm (Product), Đơn giá (Unit Prices), Số lượng (Quantities), Hạn sử dụng (Expiry Dates). Bạn nhập dữ liệu thực tế vào đây.
Để tính toán thêm, chèn các cột: Tổng giá trị (Total Price), Thời gian còn lại (Due Time), Trạng thái (Condition).
Tạo khung phân tích trạng thái sản phẩm trên sheet Category (Phân loại).
➤ Tạo danh sách phân loại sản phẩm theo thời gian còn lại (Due Time) để xác định trạng thái/độ tuổi tồn kho. Đặt tên vùng này là limit (giới hạn).
Xem thêm: Cách tạo báo cáo sử dụng Macro trong Excel (với các bước dễ dàng)
Bước 02: Dùng Công Thức Tạo Báo Cáo Tồn Kho Theo Độ Tuổi
➤ Tính tổng giá trị sản phẩm, nhập công thức tại ô E4:
=C4*D4
Trong đó, C4 là Đơn giá, D4 là Số lượng của sản phẩm Apple.
➤ Nhấn ENTER và kéo tay cầm Fill Handle xuống.
Bạn sẽ có tổng giá trị cho từng sản phẩm ở cột Total Price.
➤ Tính thời gian còn lại đến hạn sử dụng so với hôm nay (19-05-22):
=IF((F4-TODAY())<0,0,F4-TODAY())
F4 là Hạn sử dụng, TODAY() trả về ngày hôm nay 19-05-22. Nếu hiệu số âm, IF trả về 0, ngược lại trả về số ngày còn lại (Due Time).
➤ Nhấn ENTER và kéo Fill Handle xuống.
Bạn sẽ có thời gian còn lại cho từng sản phẩm.
➤ Xác định trạng thái sản phẩm bằng tra cứu giá trị Due Time trong sheet Category:
=VLOOKUP(G4, limit,2, TRUE)
G4 là giá trị tra cứu trong vùng tên limit, 2 là chỉ số cột, TRUE cho khớp gần đúng.
➤ Nhấn ENTER kéo Fill Handle xuống.
Cuối cùng, cột Condition (Trạng thái) hiển thị điều kiện tồn kho.
Xem thêm: Công thức Aging trong Excel dùng IF (4 ví dụ phù hợp)
Bài đọc tương tự
- Cách tự động hóa báo cáo Excel bằng Macro (3 cách dễ dàng)
- Dùng công thức Aging cho 30 60 90 ngày trong Excel (5 cách hiệu quả)
- Cách dùng công thức Aging trong Excel loại trừ cuối tuần (4 cách dễ dàng)
- Dùng IF cho các nhóm độ tuổi (Aging Buckets) trong Excel (3 ví dụ)
- Cách dùng công thức phân tích độ tuổi tồn kho trong Excel (2 cách dễ dàng)
Bước 03: Tạo Pivot Table Cho Báo Cáo Tồn Kho Theo Độ Tuổi
Bước này tạo Pivot Table để tổ chức bảng dữ liệu, làm nổi bật độ tuổi tồn kho.
➤ Vào tab Chèn (Insert) >> PivotTable.
Hộp thoại PivotTable from table or range mở ra.
➤ Chọn vùng bảng từ sheet Inventory và nhấn OK.
Bạn được chuyển sang sheet mới với 2 phần: PivotTable và PivotTable Fields.
➤ Kéo Product ID và Product vào vùng Rows (Hàng), Quantity (Số lượng) và Total Price (Tổng giá trị) vào Values (Giá trị), Condition (Trạng thái) vào Columns (Cột).
Để rút gọn tên trường ở vùng Values:
➤ Click mũi tên thả xuống của Sum of Quantity chọn Value Field Settings.
Hộp thoại Value Field Settings mở ra.
➤ Đổi tên trường thành Q (hoặc tùy ý) ở ô Custom Name nhấn OK.
➤ Tương tự đổi Sum of Total Price thành P.
Cuối cùng có 2 tên trường mới ở vùng Values.
Pivot table dữ liệu với trạng thái sản phẩm làm tiêu đề cho số lượng và giá:
Xem thêm: Cách tạo báo cáo tóm tắt trong Excel (2 phương pháp dễ dàng)
Bước 04: Trang Trí Pivot Table
Bước cuối trang trí Pivot Table cho đẹp mắt.
Bảng này không cần tổng cộng nên loại bỏ dễ dàng.
➤ Vào tab PivotTable Analyze >> Options (Tùy chọn) Dropdown >> Options.
Hộp thoại PivotTable Options mở ra.
➤ Chọn tab Totals & Filters (Tổng & Bộ lọc) bỏ tick các tùy chọn Grand Totals (Tổng chung).
➤ Nhấn OK.
Các giá trị tổng hàng/cột đã bị loại bỏ.
➤ Thay đổi giao diện qua tab Design (Thiết kế) chọn theme ưng ý.
Đây là giao diện cuối cùng của Báo Cáo Tồn Kho Theo Độ Tuổi (Inventory Aging Report).
Xem thêm: Cách tạo báo cáo hàng tháng trong Excel (với các bước nhanh)
Kết Luận
Bài viết đã trình bày các bước tạo báo cáo Inventory Aging trong Excel. Hy vọng hữu ích. Mọi góp ý hoặc thắc mắc, hãy chia sẻ ở phần bình luận.
Các Bài Viết Liên Quan
- Tạo báo cáo thu chi trong Excel (3 ví dụ)
- Cách tạo báo cáo PDF bằng Excel VBA (3 thủ thuật nhanh)
- Tạo báo cáo MIS trong Excel cho doanh số (với các bước dễ dàng)
- Tạo báo cáo PDF từ dữ liệu Excel (4 phương pháp dễ dàng)
- Chuẩn bị báo cáo MIS trong Excel (2 ví dụ phù hợp)