Trong việc quản lý kho, việc theo dõi số sách và thực tế trong kho là rất quan trọng. Một trong những vấn đề thường gặp phải là thiếu sót số sách, đặc biệt là số sách kho trên Excel không bị xuất âm. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách lập công thức thông báo khi xuất kho bị âm trong Excel, giúp việc quản lý kho của bạn trở nên hiệu quả hơn.
Xuất âm kho là gì?
Xuất âm kho là việc số lượng hàng xuất kho trên sổ sách vượt quá số lượng hàng hóa thực tế còn tồn kho. Việc này xảy ra khi số lượng hàng đã xuất không còn tương ứng với số tồn trên sổ sách, dẫn đến những sai sót nghiêm trọng trong công việc quản lý kho. Do đó, việc theo dõi xuất âm kho rất cần thiết.
Có hai cách để quản lý xuất âm kho:
- Cách 1: Quản lý âm kho trong Bảng kê.
- Cách 2: Quản lý âm kho ngay khi lập phiếu xuất kho.
Để đạt hiệu quả tốt nhất, chúng ta nên lựa chọn cách 2 là quản lý âm kho ngay khi lập phiếu xuất kho.
Cách lập công thức tính tồn kho trong phiếu xuất kho
Việc xác định tồn kho trong phiếu xuất kho sẽ giúp chúng ta xác định được giới hạn số lượng hàng có thể xuất, từ đó cảnh báo và phát hiện ngay khi có khả năng xuất âm.
Việc tính tồn kho phụ thuộc vào:
- Điều kiện thời gian: là thời điểm lập phiếu xuất kho. Chỉ tính với những mặt hàng nhập – xuất kho đến thời điểm đó. Không tính những mặt hàng nhập, xuất sau thời điểm đó.
- Điều kiện tên mặt hàng: tùy từng mặt hàng được lựa chọn để tính cho mặt hàng đó.
Vì vậy, ở đây chúng ta cần tính theo nhiều điều kiện (hơn 1 điều kiện) => Sử dụng hàm SUMIFS.
Nguyên tắc tính tồn kho = Tổng nhập – Tổng xuất.
a. Tổng nhập kho
SUMIFS($C$3:$C$10, $B$3:$B$10, $I3, $A$3:$A$10, “)Giải thích công thức:
$C$3:$C$10: vùng cần tính tổng là cột số lượng trong bảng kê nhập. Lưu ý độ rộng các vùng phải bằng nhau. Các vùng đều cần có định dạng.$B$3:$B$10: vùng điều kiện thứ nhất, xét với tên hàng nên chọn cột tên hàng.$I3: điều kiện thứ nhất là tên hàng tại ô I3, điều kiện này nằm trong vùng điều kiện thứ nhất. Cố định cột I để khi sao chép công thức sang cột khác không bị sai lệch tham chiếu tới điều kiện này.$A$3:$A$10: vùng điều kiện thứ hai là thời gian, ngày. Xét cho điều kiện là ngày lập phiếu.
b. Tổng xuất kho
SUMIFS($G$3:$G$10, $F$3:$F$10, $I3, $E$3:$E$10, “)Tương tự như tính tổng nhập kho nhưng xét cho Bảng Xuất kho.
Số tồn kho ở ô J3=SUMIFS($C$3:$C$10, $B$3:$B$10, $I3, $A$3:$A$10, “)
Áp dụng tương tự cho các mặt hàng khác.
Thiết lập cảnh báo khi xuất âm kho
Để cảnh báo xuất âm kho, bạn có thể sử dụng 2 công cụ là:
Conditional Formatting: tự động định dạng khi phát hiện việc xuất âm, giúp bạn nhận biết bằng màu sắc, định dạng trong ô.
Data Validation: tự động đưa ra cảnh báo khi xuất âm, cho phép hoặc từ chối việc xuất âm dựa vào quyết định của người dùng.
Thực hiện như sau:
a. Sử dụng Conditional Formatting:
Tạo thêm 1 cột tồn sau khi xuất = Tồn trước khi xuất – Số lượng xuất.
Chọn toàn bộ vùng Phiếu xuất rồi chọn Conditional Formatting.
Trong Conditional Formatting xây dựng công thức như sau:
=IF(L3<0, TRUE, FALSE)Kết quả như sau:
Kết quả xuất âm kho CF
Như vậy chúng ta có thể thấy khi mặt hàng B xuất 10 thì kho sẽ bị âm.
b. Sử dụng Data Validation
Cách này không cần tới cột phụ, chỉ cần sử dụng trực tiếp Data validation trong cột Số lượng xuất như sau:
Trong Cột K vùng số lượng xuất: K3:K7 chọn Data / Data validation.
Trong cửa sổ Data validation chọn
- Allow = Whole number (chỉ nhập dạng số),
- Data = Less than or equal to (nhỏ hơn hoặc bằng),
- Maximum =
J3(giá trị lớn nhất bằngJ3. Khi chọn vùng K3:K7 thì Data validation sẽ hiểu tương ứng: tại K3 thì).
Khi đó nếu tại cột K nhập giá trị lớn hơn cột J thì sẽ xuất hiện thông báo của Data validation về việc nhập quá giới hạn cho phép.
Bạn cũng có thể thiết lập các chế độ cảnh báo của Data validation trong mục Error Alert để có thể báo lỗi cụ thể, cho phép nhập số lượng lớn hơn số tồn (gây ra xuất âm) hay không.
Kết luận
Chỉ với 2 kỹ thuật cơ bản trong Excel bạn đã có thể kiểm soát việc xuất âm kho rồi. Tuy nhiên để làm điều đó thì các bạn cần đặc biệt lưu ý cấu trúc của các bảng kê Nhập, bảng kê Xuất và cách sử dụng công thức SUMIFS. Công thức SUMIFS được sử dụng rất đa dạng trong kế toán.
Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2sGfXR0.
Ngoài ra để có thể nâng cao kiến thức về Excel và ứng dụng Excel vào quản lý dự án, các bạn có thể tham gia khóa học EX101 – Excel từ cơ bản tới chuyên gia của hệ thống Học Excel Online. Khóa học sẽ giúp bạn nắm vững các hàm, các công cụ trong Excel, ứng dụng Conditional Formatting trong việc tạo gantt chart quản lý tiến độ dự án.