File Excel quản lý kho: mẫu nhập – xuất – tồn tự động và cách làm từng bước

Trả lời nhanh: file Excel quản lý kho hiệu quả được xây quanh một sổ nhập – xuất duy nhất: mỗi lần hàng vào hoặc ra kho là một dòng với ngày, số phiếu, mã hàng, loại (Nhập/Xuất), số lượng và đơn giá. Từ sổ này, sheet báo cáo nhập – xuất – tồn dùng SUMIFS để tính tồn đầu kỳ, nhập trong kỳ, xuất trong kỳ và tồn cuối kỳ cho từng mã hàng ở bất kỳ khoảng thời gian nào, kèm giá trị tồn theo phương pháp bình quân gia quyền và cảnh báo khi hàng xuống dưới mức tối thiểu.

Cách làm này phù hợp với cửa hàng, xưởng nhỏ, kho vật tư của công ty dịch vụ hay shop online có nhiều mặt hàng. Bài viết đi từng bước, giải thích công thức và kèm ví dụ tính giá xuất kho; các số liệu trong ví dụ chỉ để minh họa.

Cấu trúc file quản lý kho

Điểm khác biệt với cách ghi tồn kho bằng tay là bạn không bao giờ sửa trực tiếp số tồn. Mọi thay đổi đều đi qua một dòng nhập hoặc xuất, nên luôn truy được lý do khi số liệu lệch với thực tế.

Bước 1: Danh mục hàng hóa, vật tư

Mỗi mặt hàng có một mã hàng duy nhất, ngắn, không dấu và không đổi, ví dụ GA4-70. Nếu một sản phẩm có nhiều quy cách (màu, kích thước) với tồn kho riêng, mỗi quy cách là một mã. Thêm cột tồn tối thiểu — mức mà dưới đó cần đặt hàng — và cột vị trí kho (kệ, ngăn) để người xuất hàng tìm nhanh.

Bước 2: Sổ nhập – xuất

Sheet NhapXuat có các cột: A Ngày, B Số phiếu, C Loại, D Mã hàng, E Số lượng, F Đơn giá, G Thành tiền (=E2*F2), H Đối tượng, I Ghi chú. Một vài quy ước giúp file luôn đúng:

• Cột Loại chỉ nhận hai giá trị Nhập và Xuất (dùng danh sách thả xuống).
• Số lượng luôn là số dương; cột Loại quyết định cộng hay trừ.
• Đơn giá nhập là giá mua cộng chi phí vận chuyển, bốc xếp phân bổ cho từng đơn vị. Với dòng xuất, đơn giá có thể để trống và tính ở báo cáo (Bước 4).
• Hàng trả lại nhà cung cấp ghi là Xuất, khách trả hàng nhập lại kho ghi là Nhập, kèm ghi chú.

Doanh nghiệp áp dụng chế độ kế toán có thể in phiếu nhập kho, phiếu xuất kho theo mẫu trong chế độ kế toán mà đơn vị đang dùng (ví dụ mẫu 01-VT, 02-VT); số phiếu trên sổ Excel nên trùng với số phiếu đó để dễ đối chiếu.

Bước 3: Báo cáo nhập – xuất – tồn theo kỳ

Trên sheet BaoCaoNXT, nhập ngày bắt đầu kỳ ở C1 và ngày kết thúc ở C2. Với mã hàng ở cột A từ dòng 5, các công thức số lượng là:

• Tồn đầu kỳ (C5): =SUMIFS(NhapXuat!$E:$E,NhapXuat!$D:$D,$A5,NhapXuat!$C:$C,"Nhập",NhapXuat!$A:$A,"<"&$C$1)-SUMIFS(NhapXuat!$E:$E,NhapXuat!$D:$D,$A5,NhapXuat!$C:$C,"Xuất",NhapXuat!$A:$A,"<"&$C$1)
• Nhập trong kỳ (D5): =SUMIFS(NhapXuat!$E:$E,NhapXuat!$D:$D,$A5,NhapXuat!$C:$C,"Nhập",NhapXuat!$A:$A,">="&$C$1,NhapXuat!$A:$A,"<="&$C$2)
• Xuất trong kỳ (E5): công thức như D5 nhưng thay “Nhập” bằng “Xuất”.
• Tồn cuối kỳ (F5): =C5+D5-E5
• Cảnh báo (G5): =IF(F5<=XLOOKUP(A5,DanhMuc!A:A,DanhMuc!F:F),"Cần đặt hàng","")

Đổi ngày ở C1, C2 là có báo cáo tháng, quý hay năm. Nếu Excel trên máy dùng dấu phẩy thập phân, thay dấu phẩy phân cách trong công thức bằng dấu chấm phẩy.

Bước 4: Tính giá xuất kho và giá trị tồn

Có ba phương pháp tính giá hàng xuất kho thường gặp: bình quân gia quyền, nhập trước – xuất trước (FIFO) và thực tế đích danh. Trên Excel, bình quân gia quyền cuối kỳ là dễ làm nhất:

Đơn giá bình quân = (Giá trị tồn đầu kỳ + Giá trị nhập trong kỳ) ÷ (Số lượng tồn đầu kỳ + Số lượng nhập trong kỳ)

Giá trị nhập trong kỳ tính bằng SUMIFS trên cột G (thành tiền) với cùng điều kiện như cột D. Giá trị xuất = số lượng xuất × đơn giá bình quân; giá trị tồn cuối = giá trị đầu kỳ + giá trị nhập − giá trị xuất.

Ví dụ minh họa — giấy A4 (ram) trong một tháng:

Phương pháp tính giá xuất kho dùng cho sổ sách kế toán phải thống nhất trong kỳ kế toán và phù hợp chế độ kế toán của doanh nghiệp; hãy thống nhất với kế toán trước khi chọn.

Bước 5: Thẻ kho cho từng mặt hàng

Khi cần xem lịch sử một mã hàng, sheet TheKho lọc sổ nhập xuất theo mã chọn ở B1. Với Excel 365: =FILTER(NhapXuat!A2:I5000,NhapXuat!D2:D5000=B1). Cột tồn lũy kế bên cạnh cộng số nhập và trừ số xuất theo từng dòng. Thẻ kho giúp trả lời nhanh các câu hỏi như “tháng trước xuất cho bộ phận nào bao nhiêu?”.

Bước 6: Kiểm kê định kỳ

Không file nào đúng mãi nếu không đếm thực tế. Mỗi tháng hãy kiểm kê một nhóm hàng — ưu tiên hàng giá trị cao hoặc xuất nhập nhiều — và kiểm kê toàn bộ ít nhất vào cuối năm. Khi số đếm khác số trên báo cáo, ghi một dòng Nhập (thừa) hoặc Xuất (thiếu) với số phiếu “KK” và lý do, không sửa đè số liệu cũ. Biên bản kiểm kê nên có chữ ký người kiểm và thủ kho.

Lỗi thường gặp

• Sửa tay số tồn thay vì ghi phiếu: mất dấu vết.
• Một mã cho nhiều quy cách: tổng tồn đúng nhưng không biết thiếu cỡ nào.
• Quên chi phí vận chuyển khi nhập: giá trị tồn thấp hơn thực tế, kéo theo giá bán tính sai.
• Không ghi hàng trả lại, hàng hỏng: nguyên nhân phổ biến nhất của chênh lệch.
• Dồn chứng từ cuối tháng mới nhập: tồn kho trên file không dùng được để quyết định đặt hàng.

Câu hỏi thường gặp

File quản lý kho khác file quản lý bán hàng thế nào?

File quản lý kho tập trung vào số lượng và giá trị hàng vào – ra kho, kể cả xuất dùng nội bộ hay chuyển kho. File quản lý bán hàng tập trung vào đơn hàng, doanh thu và lãi gộp theo kênh. Shop nhỏ có thể dùng một file; khi kho phức tạp hơn, nên tách riêng.

Quản lý nhiều kho trong một file được không?

Được. Thêm cột “Kho” vào sổ nhập xuất và thêm điều kiện kho vào các công thức SUMIFS. Chuyển kho ghi hai dòng: xuất ở kho đi và nhập ở kho đến.

Có dùng mã vạch với Excel được không?

Có. Máy quét mã vạch USB hoặc Bluetooth thông thường nhập mã như bàn phím, nên chỉ cần dùng mã vạch làm mã hàng hoặc thêm cột mã vạch và tra bằng XLOOKUP.

Khi nào nên chuyển sang phần mềm quản lý kho?

Khi có nhiều người nhập xuất cùng lúc, nhiều kho, cần quản lý lô – hạn sử dụng hoặc đồng bộ tồn kho với nhiều kênh bán. Cấu trúc dữ liệu trong bài vẫn là nền tảng khi chuyển đổi.

Xem thêm các hướng dẫn trong nhóm Bán hàng online & quản lý kho.