Trong công việc quản lý kho, việc theo dõi trên sổ sách làm sao để sát với 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à thế nào để trên 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 Học Excel Online 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 trên Excel.

Ví dụ về 1 mẫu quản lý kho trên Excel như sau:

Chúng ta có 2 bảng kê, gồm bảng kê nhập và bảng kê xuất:

Yêu cầu: khi lập phiếu xuất kho phải theo dõi được  việc xuất âm kho.

Thế nào là xuất âm kho

Nhắc lại 1 chút về khái niệm xuất âm kho để các bạn hiểu rõ vấn đề này hơn nhé:

Xuất âm kho là việc số lượng xuất kho trên sổ sách vượt quá số lượng hàng hóa thực tế tồn kho. Việc này dẫn tới sau khi xuất số lượng hàng còn tồn trên sổ sách sẽ bị âm (Số tồn sau khi xuất kho = Tổng số xuất kho – Tổng số tồn kho trước khi xuất)

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Việc tồn kho bị âm là trái với thực tế. Bởi thực tế không có việc xuất âm. Và lỗi xuất âm là lỗi khá nghiêm trọng trong công việc quản lý kho.

Do đó việc theo dõi xuất âm kho được thực  hiện theo 2 cách:

• 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ốt nhất cho công việc thì chúng ta nên quản lý theo cách 2 là quản lý âm kho ngay khi lập phiếu xuất kho

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ó để xuất, từ đó sẽ cảnh báo, phát hiện được khi nào 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 đó mà thôi. 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 đó.

Như 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

Xét với mặt hàng A, vị trí ô J3

a. Tổng nhập kho

SUMIFS($C$3:$C$10,$B$3:$B$10,$I3,$A$3:$A$10,”<=”&$K$1)

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
• $B$3:$B$10: vùng điều kiện thứ 1, xét với tên hàng nên chọn cột tên hàng.
• $I3: điều kiện thứ 1 là tên hàng tại ô I3, điều kiện này nằm trong vùng điều kiện thứ 1. 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ứ 2 là thời gian, ngày. Xét cho điều kiện là ngày lập phiếu
• “<=”&$K$1: là xét từ ngày 15/5 trở về trước, do đó phải kết hợp với dấu nhỏ hơn hoặc bằng.

b. Tổng xuất kho

SUMIFS($G$3:$G$10,$F$3:$F$10,$I3,$E$3:$E$10,”<=”&$K$1)

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,”<=”&$K$1)-SUMIFS($G$3:$G$10,$F$3:$F$10,$I3,$E$3:$E$10,”<=”&$K$1)

Á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 formattiing.

Trong Conditional formatting xây dựng công thức như sau:

Ý nghĩa: Xét từng giá trị ở cột L, bắt đầu từ ô L3. Nếu Giá trị nào nhỏ hơn 0 thì sẽ định dạng tô màu vàng

Kết quả như sau:

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ằng J3. Khi chọn vùng K3:K7 thì Data validation sẽ hiểu tương ứng: tại K3 thì <=J3, K4<=J4… K7<=J7

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.

Các bạn có thể thiết lập các chế độ cảnh bảo của Data validation trong mục Error Arlet để 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 được điều đó thì các bạn cần đặc biệt  lưu ý về 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. Chi tiết khóa học xem tại: