Cách Tính Tồn Kho Nguyên Liệu Quán Cafe Bằng Google Sheets 2026

Trả lời nhanh: Tồn kho lý thuyết = tồn đầu kỳ + nhập − lượng đã dùng, trong đó lượng đã dùng bằng số ly bán nhân định lượng từng món. Trên Google Sheets, lập 3 bảng (nguyên liệu, công thức món, nhật ký bán), dùng SUMPRODUCT để trừ kho, rồi đối chiếu với số đếm thực tế mỗi cuối tuần.

Thử hình dung anh Khoa mở một quán cafe nhỏ. Sáng thứ Hai anh mở thùng cà phê bột, nhẩm lại số kg nhập hôm thứ Năm tuần trước, thấy đáng lẽ còn dùng được đến thứ Tư. Thứ Bảy đã cạn. Anh không biết cà phê đi đâu: pha đậm tay hơn, nhân viên đổ ly hỏng, tặng ly cho khách quen, hay đơn giản là anh nhớ sai số nhập. Sổ tay ghi tiền nhập, không ghi lượng dùng, nên không có gì để đối chiếu.

Bài này đi từng bước dựng một file Google Sheets để trả lời câu hỏi "tuần này lẽ ra phải dùng bao nhiêu, còn bao nhiêu, và kho thật lệch bao nhiêu". Mọi con số trong bài là giả định để minh họa, bạn thay bằng số của quán mình.

Vì sao tồn kho trên sổ cứ lệch với kho thật

Quán cafe khác tiệm tạp hóa ở chỗ bạn không bán nguyên liệu ra theo đơn vị nhập. Bạn nhập cà phê bột theo kg, sữa đặc theo lon, trà theo gói, nhưng bán ra theo ly. Một ly cà phê sữa đá "ăn" 18 gram cà phê và 25 gram sữa đặc, và không ai ghi mấy con số đó mỗi lần pha. Vì vậy tồn kho không thể đếm từ phiếu bán hàng, nó phải được suy ra.

Cách suy ra chỉ gồm một phép tính:

Tồn lý thuyết = tồn đầu kỳ + nhập trong kỳ − (số ly bán × định lượng mỗi ly)

Con số này là "kho trên giấy". Đến cuối tuần bạn đếm kho thật, rồi lấy kho thật trừ kho lý thuyết. Phần chênh lệch mới là thứ đáng quan tâm: nó gom cả pha dư, đổ bỏ, ly tặng, cân đong sai và ghi sót. Nếu không có kho lý thuyết, bạn chỉ có một cảm giác mơ hồ rằng "hình như hao".

Muốn phép tính này chạy trơn tru, file cần ba bảng tách riêng: danh sách nguyên liệu, công thức từng món, và nhật ký số ly bán. Tách riêng để khi đổi giá nhập hay đổi công thức pha, bạn chỉ sửa đúng một chỗ.

Bảng nguyên liệu: tồn đầu, tồn tối thiểu, đơn giá

Tạo một sheet tên NguyenLieu. Dòng 1 là tiêu đề, dữ liệu bắt đầu từ dòng 2. Mỗi nguyên liệu một dòng, và mọi nguyên liệu phải quy về một đơn vị nhỏ (gram hoặc ml) để khỏi phải đổi qua đổi lại. Cà phê nhập 1 kg thì ghi 1000.

• Cột A: mã nguyên liệu (CF001, SD001...). Mã ngắn, không dấu, không trùng nhau.
• Cột B: tên nguyên liệu.
• Cột C: đơn vị (g hoặc ml).
• Cột D: tồn đầu kỳ, tức số đếm thực tế của lần kiểm kho gần nhất.
• Cột E: nhập trong kỳ, cộng dồn các lần nhập từ lần kiểm kho trước.
• Cột F: đã dùng, sẽ có công thức ở phần sau.
• Cột G: tồn lý thuyết, =D2+E2-F2.
• Cột H: tồn tối thiểu, mức mà dưới đó bạn phải nhập thêm.
• Cột I: cảnh báo.
• Cột J: đơn giá mỗi gram/ml.
• Cột K: giá trị tồn, =G2*J2.

Tồn tối thiểu là cột người ta hay đặt bừa. Cách đặt có cơ sở: lấy lượng dùng trung bình mỗi ngày nhân với số ngày từ lúc đặt đến lúc hàng về, rồi cộng thêm một ngày dự phòng. Giả sử cà phê bột dùng khoảng 450 g mỗi ngày, nhà cung cấp giao sau 3 ngày, tồn tối thiểu hợp lý là 450 × 4 = 1.800 g, làm tròn lên 2.000 g.

Dữ liệu minh họa sau một tuần bán, với bốn nguyên liệu:

Mã (A)	Tên (B)	ĐV (C)	Tồn đầu (D)	Nhập (E)	Đã dùng (F)	Tồn lý thuyết (G)	Tối thiểu (H)	Cảnh báo (I)
CF001	Cà phê bột	g	5.000	4.000	3.120	5.880	2.000	Đủ
SD001	Sữa đặc	g	8.000	0	5.000	3.000	3.500	Cần nhập
ST001	Sữa tươi	ml	6.000	0	4.800	1.200	2.000	Cần nhập
TL001	Trà lài	g	1.000	0	480	520	300	Đủ

Bảng công thức món: mỗi ly dùng bao nhiêu

Đây là phần tốn công nhất nhưng chỉ làm một lần. Tạo sheet CongThuc với ba cột: A là mã món, B là mã nguyên liệu, C là định lượng cho một ly. Mỗi món dùng bao nhiêu nguyên liệu thì có bấy nhiêu dòng, kiểu "bảng dọc". Cách này hơi dài nhưng công thức tính về sau đơn giản hơn nhiều so với việc kéo một bảng ngang mỗi món một cột.

Mã món (A)	Mã nguyên liệu (B)	Định lượng một ly (C)
CFS	CF001	18
CFS	SD001	25
BX (bạc xỉu)	CF001	12
BX	SD001	25
BX	ST001	60
TD (trà đào)	TL001	8

Định lượng ở đây là số giả định. Cách lấy số thật: pha thử ba ly của cùng một món, cân nguyên liệu từng ly, lấy trung bình. Đừng lấy theo công thức của sách hay của quán khác, vì độ đậm và cỡ ly mỗi quán một khác. Món có topping thì thêm dòng riêng cho topping, tính theo lượng của một suất.

Sheet thứ ba tên BanHang là nhật ký bán, cực kỳ đơn giản: cột A là ngày, cột B là mã món (CFS, BX, TD), cột C là số ly. Cuối ca, người đứng quầy cộng số ly mỗi món rồi nhập một dòng cho mỗi món. Giả sử tuần này bán tổng cộng 120 ly CFS (cà phê sữa đá), 80 ly BX (bạc xỉu) và 60 ly TD (trà đào).

Công thức trừ kho theo số ly bán

Quay lại sheet NguyenLieu. Ô F2 (lượng cà phê bột đã dùng) cần làm ba việc: tính tổng số ly bán của từng món, nhân với định lượng của nguyên liệu này trong món đó, rồi cộng lại. Một công thức làm hết:

=SUMPRODUCT(SUMIF(BanHang!$B$2:$B$1000, CongThuc!$A$2:$A$200, BanHang!$C$2:$C$1000) * (CongThuc!$B$2:$B$200=A2) * CongThuc!$C$2:$C$200)

Đọc từ trong ra ngoài. SUMIF(...) trả về, cho mỗi dòng của bảng công thức, tổng số ly đã bán của món ở dòng đó (BanHang cột B là mã món, cột C là số ly). (CongThuc!$B$2:$B$200=A2) chỉ giữ lại các dòng công thức thuộc nguyên liệu đang xét, ở đây là mã trong ô A2. Cuối cùng nhân với cột định lượng và SUMPRODUCT cộng tất cả lại.

Với dữ liệu tuần mẫu, ô F2 của cà phê bột ra: 120 × 18 + 80 × 12 = 2.160 + 960 = 3.120 g, khớp bảng ở trên. Sữa đặc (SD001): 120 × 25 + 80 × 25 = 5.000 g. Sữa tươi (ST001): 80 × 60 = 4.800 ml. Trà lài (TL001): 60 × 8 = 480 g. Kéo công thức từ F2 xuống các dòng dưới, vì ô A2 là tham chiếu tương đối nên mỗi dòng tự tính cho nguyên liệu của mình.

Hai lưu ý nhỏ. Vùng 1000 dòng của BanHang và 200 dòng của CongThuc là con số chọn dư ra, quán bán nhiều thì kéo dài thêm. Còn nếu bảng bán hàng nhiều lên đến mức file ì ạch, hãy lưu mỗi tuần một sheet riêng rồi dọn sheet BanHang sau khi kiểm kho, như phần cuối bài mô tả.

Nếu bạn muốn xem cách tính định lượng nhiều món hơn, bài Quản Lý Kho Nguyên Liệu & Định Lượng Nhà Hàng, Quán Cafe Bằng Google Sheets 2026 đi sâu hơn vào việc dựng bảng định lượng cho thực đơn dài.

Cảnh báo dưới định mức để khỏi hết hàng giữa ca

Cột G đã cho bạn tồn lý thuyết, cột H là mức tối thiểu. Cột cảnh báo chỉ là một phép so sánh. Ô I2:

=IF(G2<=H2,"Cần nhập","Đủ")

Chữ thôi chưa đủ, vì nhìn một bảng cả chục nguyên liệu mắt rất dễ lướt qua. Bôi đỏ luôn cả dòng: chọn vùng A2:K50, vào Định dạng, chọn Định dạng có điều kiện, chọn "Công thức tùy chỉnh" và nhập =$G2<=$H2. Chọn màu nền đỏ nhạt. Từ giờ dòng nào tụt dưới mức tối thiểu sẽ tự đỏ.

Trong bảng mẫu, sữa đặc còn 3.000 g so với mức 3.500 g, và sữa tươi còn 1.200 ml so với mức 2.000 ml, nên cả hai dòng đỏ. Đây là chỗ bảng chứng minh giá trị: nếu chỉ nhìn tồn đầu 8.000 g sữa đặc, bạn tưởng còn thoải mái.

Muốn thêm một cột gợi ý số lượng nên nhập, đặt tạm mục tiêu là đưa tồn lên gấp đôi mức tối thiểu. Ô L2 (hoặc cột trống bên phải):

=IF(G2<=H2,H2*2-G2,0)

Với sữa tươi: 2.000 × 2 − 1.200 = 2.800 ml cần nhập. Hệ số 2 chỉ là điểm xuất phát, bạn chỉnh theo chu kỳ giao hàng thực tế. Nguyên liệu nào chỉ nhập được theo thùng cố định thì làm tròn lên bội số của thùng bằng tay.

Kiểm kho cuối tuần và đọc chênh lệch

Kho lý thuyết chỉ có nghĩa khi bạn đem nó ra so với kho thật. Chọn một thời điểm cố định, ví dụ tối Chủ nhật sau khi đóng quầy, đứng đếm và cân từng nguyên liệu. Đừng đếm giữa ca vì nguyên liệu đang nằm trong ly và máy pha.

Thêm hai cột vào NguyenLieu: cột L nhập số đếm thực tế, cột M là chênh lệch.

Ô M2: =IF(L2="","",L2-G2)

Thêm cột N để quy ra tiền: =IF(L2="","",M2*J2) (J2 là đơn giá mỗi gram, giả định cà phê 200.000đ/kg tức 200 đồng/gram).

Giả sử đếm thực tế cà phê bột là 5.400 g trong khi tồn lý thuyết là 5.880 g. Chênh lệch là −480 g, tương đương khoảng 8,2% so với tồn lý thuyết, trị giá 480 × 200 = 96.000đ trong một tuần. Con số này đủ nhỏ để không hoảng, nhưng đủ lớn để bạn tự hỏi nó đến từ đâu.

Cách đọc chênh lệch hữu ích là chia thành hai loại. Chênh lệch âm đều đặn tuần nào cũng vậy thường nghĩa là định lượng trong bảng công thức thấp hơn thực tế: nhân viên pha tay đậm hơn công thức, hoặc bạn cân ly mẫu lúc rảnh còn lúc đông thì ước chừng. Chỉnh lại công thức hoặc huấn luyện lại thao tác. Chênh lệch nhảy lung tung, tuần âm tuần dương, thường là do nhập sai (ghi sót lần nhập, cân nhầm gói) chứ không phải hao thật.

Vài nguồn hao mà bảng công thức không thấy được, nên xử lý bằng cách ghi nhận riêng: ly pha hỏng đổ bỏ, ly pha thử, ly tặng khách, nguyên liệu hết hạn. Cách đơn giản nhất là cho chúng một mã món riêng như "DOBO" trong BanHang và gán định lượng trung bình. Khi đó chênh lệch còn lại mới thật sự là chênh lệch chưa giải thích được.

Kiểm kho xong, làm ba việc. Copy cột L, dán vào cột D bằng "Dán đặc biệt, chỉ giá trị" để số đếm thực thành tồn đầu kỳ mới. Xóa cột E (nhập trong kỳ) về 0. Cuối cùng chép sheet BanHang sang một sheet lưu trữ đặt tên theo tuần rồi xóa dữ liệu cũ, để tuần sau SUMIF chỉ cộng số ly của tuần mới. Nếu bạn phân vân nên tính tồn theo kiểu nào cho hợp với việc kinh doanh của mình, bài So Sánh 3 Cách Tính Tồn Kho Trên Google Sheets 2026 đặt các cách tính cạnh nhau.

Khi file tự dựng bắt đầu nặng nề

File tự dựng có điểm yếu thật, và nói thẳng thì có ba cái. Thứ nhất, nó chỉ đúng khi người đứng quầy nhập đủ số ly mỗi ca; quên một ngày là kho lý thuyết lệch cả tuần. Thứ hai, ghi nhập kho bằng tay vào cột E không có nhật ký, nên khi con số sai bạn không biết ai sửa và sửa lúc nào. Thứ ba, quán có vài chục món, mỗi món năm sáu nguyên liệu thì bảng công thức bắt đầu khó theo dõi bằng mắt.

Nếu bạn đã quen với cách làm trên và muốn bỏ phần nhập tay đó, Phần Mềm Quản Lý Quán Cafe Trên Google Sheets của SheetStore là bản đã dựng sẵn cùng ý tưởng, chạy trên Google Sheets và Apps Script trong tài khoản Google của bạn, dữ liệu nằm trong Google Drive của bạn. Phần kho nguyên liệu có cảnh báo dưới định mức, trừ kho tự động theo công thức món khi có đơn, nhập/xuất/kiểm kho đều có log, tính giá trị tồn kho quy ra tiền và gửi email cảnh báo tồn kho. Giá 529.000đ, mua một lần dùng trọn đời, không phí hằng tháng, có thể dùng thử 7 ngày trước khi quyết định.

Phần mềm này dành cho quán nhỏ. Nếu quán bạn cần khách đặt món qua QR tại bàn, màn hình bếp theo thời gian thực hoặc nhiều chi nhánh, hãy xem Phần mềm POS quản lý quán cafe & nhà hàng. Còn nếu chưa muốn mua gì, bộ ba bảng ở trên đủ để bạn nhìn ra quán mình đang hao ở đâu trong vài tuần đầu.

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

Công thức tính tồn kho lý thuyết cho quán cafe là gì?

Tồn lý thuyết = tồn đầu kỳ + nhập trong kỳ − (số ly bán × định lượng mỗi ly). Ví dụ cà phê bột: tồn đầu 5.000 g, nhập 4.000 g, đã dùng 3.120 g (120 ly × 18 g + 80 ly × 12 g), nên tồn lý thuyết còn 5.880 g. Đây là kho trên giấy, dùng để đối chiếu với số đếm thực tế.

Làm sao đặt mức tồn tối thiểu cho từng nguyên liệu cho hợp lý?

Lấy lượng dùng trung bình mỗi ngày nhân với số ngày từ lúc đặt đến lúc hàng về, rồi cộng thêm một ngày dự phòng. Ví dụ cà phê bột dùng khoảng 450 g mỗi ngày, nhà cung cấp giao sau 3 ngày: 450 × 4 = 1.800 g, làm tròn lên 2.000 g. Dưới mức này, bảng sẽ báo "Cần nhập".

Kho thực tế lệch so với kho lý thuyết thì nên hiểu thế nào?

Nếu chênh lệch âm đều đặn tuần nào cũng vậy, thường là định lượng trong bảng công thức thấp hơn thực tế, bạn cần chỉnh công thức hoặc huấn luyện lại thao tác pha. Nếu lúc âm lúc dương thì thường do nhập sai, như ghi sót lần nhập hay cân nhầm gói, chứ chưa chắc là hao thật.

Ly pha hỏng, ly tặng khách hay nguyên liệu hết hạn thì ghi vào đâu?

Bảng công thức không thấy được các khoản hao này, nên hãy cho chúng một mã món riêng, ví dụ "DOBO", ghi trong sheet BanHang và gán định lượng trung bình. Khi đó phần chênh lệch còn lại mới thật sự là chênh lệch chưa giải thích được, giúp bạn biết quán đang hao ở đâu.