Quan Ly Nha Hang Voi Google Sheets: Tu Menu Den Doanh Thu

Ngành F&B Viet Nam dang tang truong manh: theo bao cao 2026, thi truong nha hang va dich vu an uong dat hon 333.000 ty dong. Tuy nhien, hon 60% nha hang phai dong cua trong 3 nam dau — phan lon do quan ly tai chinh va van hanh yeu kem. Google Sheets, khi duoc thiet ke dung, co the la he thong quan ly nha hang hieu qua cho quy mo vua va nho.

1. Menu Engineering — Thiet Ke Menu Sinh Loi

Menu Engineering la phuong phap phan tich tung mon an theo 2 chieu: muc do pho bien (bao nhieu nguoi goi) va loi nhuan dong gop (menu item contribution margin).

4 nhom phan loai mon an (BCG Matrix cho F&B)

Cong thuc phan loai menu

Contribution Margin (CM) = Selling Price - Food Cost

Pho bien: so luong ban so voi trung binh
Avg_Popularity = Total_Items_Sold / Number_Of_Menu_Items
Is_Popular = IF(Items_Sold > Avg_Popularity * 0.7, "High", "Low")

Is_Profitable = IF(CM > Avg_CM, "High", "Low")

Category = IF(AND(Is_Popular="High", Is_Profitable="High"), "STAR",
            IF(AND(Is_Popular="High", Is_Profitable="Low"), "PLOWHORSE",
            IF(AND(Is_Popular="Low", Is_Profitable="High"), "PUZZLE", "DOG")))

Dashboard Menu Engineering

Doanh thu tong hop theo nhom:
=SUMIF(Menu_Category, "STAR", Menu_Revenue)

Ty le cac nhom:
=COUNTIF(Category_Column, "STAR") / COUNTA(Category_Column) * 100

Menu Mix (% doanh thu tung mon):
=Item_Revenue / Total_Revenue * 100

2. Food Costing — Tinh Gia Thanh Mon An Chinh Xac

Recipe Costing Sheet — Cong thuc cac mon an

Cong thuc tinh food cost co waste

Chi phi nguyen lieu truoc waste = Quantity * (Unit_Price / 1000)
Chi phi thuc sau waste = Raw_Cost / (1 - Waste_Percentage/100)

Tong food cost 1 mon:
=SUMPRODUCT(Ingredient_Quantities * Unit_Prices / 1000 / (1 - Waste_Rates/100))

Food Cost Percentage (FCP):
FCP = Total_Food_Cost / Selling_Price * 100
Target FCP: 28-35% cho nha hang pho thong, 20-28% cho fine dining

Gia ban khuyen nghi

Suggested Price = Food_Cost / Target_FCP_Decimal
Vi du: Food cost 45000, target FCP 30%:
Suggested Price = 45000 / 0.30 = 150000 VND

Gross Profit per dish:
GP = Selling_Price - Food_Cost
GP Margin = GP / Selling_Price * 100

3. Quan Ly Nguyen Lieu Va Ton Kho

He thong quan ly kho F&B

Kho nha hang co dac diem: nguyen lieu tuoi song (han su dung ngan), nhap nhieu lan/ngay, hao hut tu nhien.

Bieu mau nhap kho hang ngay

Canh bao han su dung

Tinh so ngay con lai:
Days_Until_Expiry = Expiry_Date - TODAY()

Canh bao mau:
Conditional Formatting:
- Do (khan cap): Days_Until_Expiry <= 1
- Cam (canh bao): Days_Until_Expiry <= 3
- Vang (chu y): Days_Until_Expiry <= 5
- Xanh (an toan): Days_Until_Expiry > 5

Tinh gia tri ton kho va chi phi hang ngay

Ton kho hien tai (sau xuat):
Current_Stock = Opening_Stock + Received - Used - Wasted

Chi phi nguyen lieu thuc te ngay:
Daily_Food_Cost = SUM(Used_Quantities * Unit_Prices)

So sanh voi doanh thu:
Actual_FCP = Daily_Food_Cost / Daily_Revenue * 100

Hao hut (Shrinkage):
Shrinkage = (Theoretical_Usage - Actual_Usage) / Theoretical_Usage * 100
Target: duoi 5%

4. Daily Revenue Tracking

Bao cao doanh thu ngay

KPIs quan trong cho nha hang

Revenue Per Available Seat Hour (RevPASH):
RevPASH = Total_Revenue / (Available_Seats * Operating_Hours)

Average Check (gia tri hoa don trung binh):
Avg_Check = Total_Revenue / Number_Of_Covers

Seat Turnover Rate:
Turnover = Number_Of_Covers / Available_Seats

Table Utilization:
Table_Util = Occupied_Tables / Total_Tables * 100

So sanh doanh thu hom nay vs. tuan truoc, thang truoc

Doanh thu hom nay:
=SUMIF(Revenue_Date, TODAY(), Revenue_Amount)

Cung ngay tuan truoc:
=SUMIF(Revenue_Date, TODAY()-7, Revenue_Amount)

% thay doi:
=(Today - LastWeek) / LastWeek * 100

Doanh thu thang nay:
=SUMIFS(Revenue_Amount, Revenue_Date, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),
        Revenue_Date, "<="&TODAY())

5. Staff Scheduling Va Hieu Suat

Lich lam viec hang tuan

Tao lich theo dang ma tran: Hang = Nhan vien, Cot = Ngay trong tuan, Gia tri = Ca (Sang/Trua/Toi/Off)

Tinh so gio lam viec moi tuan:
=COUNTIF(Staff_Row, "Sang")*4 + COUNTIF(Staff_Row, "Trua")*4 + COUNTIF(Staff_Row, "Toi")*6

Chi phi nhan su theo tuan:
=Total_Hours * Hourly_Rate

Labor Cost Percentage:
Labor% = Weekly_Labor_Cost / Weekly_Revenue * 100
Target F&B: 25-35%

Phan tich hieu suat nhan su

Doanh thu theo nhan vien (waitstaff):
=SUMIF(Sales_Staff, Staff_Name, Sales_Revenue)

Doanh thu trung binh moi khach phuc vu:
=Staff_Revenue / Staff_Covers

Upsell rate:
=COUNTIF(Upsell_Staff, Staff_Name) / COUNTIF(Orders_Staff, Staff_Name) * 100

Chi phi nhan su tong hop hang thang

6. Khao Sat & Quan Ly Su Hai Long Khach Hang

He thong thu thap feedback

• QR code tren ban: Lien ket den Google Form, tu dong ghi vao Sheets
• Tab sau bill: 3-4 cau hoi nhanh, khach dien vao truoc khi roi
• Thu thap tu Google Maps/Foody: Import review hang tuan vao Sheets

Cau truc khao sat (Google Form lien ket Sheets)

Cong thuc NPS (Net Promoter Score)

Phan loai: 
Promoters (9-10): khach hang trung thanh
Passives (7-8): hai long nhung khong noi tot
Detractors (1-6): co the lan truyen phan hoi xau

NPS = %Promoters - %Detractors

Vi du: 60% Promoters, 15% Detractors:
NPS = 60 - 15 = 45 (rat tot — target nha hang la tren 30)

=COUNTIF(Scores, ">=9") / COUNTA(Scores) * 100 -
 COUNTIF(Scores, "<=6") / COUNTA(Scores) * 100

7. Bao Cao Nha Hang Hang Thang

P&L Don Gian (Profit & Loss)

Bao cao theo nhom chi phi (Prime Cost)

Prime Cost = Food Cost + Labor Cost
Prime Cost % = (Food_Cost + Labor_Cost) / Revenue * 100
Target Prime Cost: duoi 65% (tot la duoi 60%)

So sanh theo thang qua cac nam

Cung ky nam truoc (Same Period Last Year - SPLY):
=SUMIFS(Revenue, Date, ">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),
        Date, "<="&EOMONTH(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),0))

YoY Growth:
=(This_Month - SPLY) / SPLY * 100

8. Cac Cong Thuc F&B Quan Trong

Cong thuc quan ly ton kho

Break-Even Analysis

Fixed Costs (chi phi co dinh hang thang):
= Tien mat bang + Luong co ban + Khau hao + Phi bao hiem

Variable Cost % (ty le chi phi bien doi):
= (COGS + Variable Labor + Variable Utilities) / Revenue * 100

Break-Even Revenue (doanh thu hoa von):
= Fixed_Costs / (1 - Variable_Cost_Percentage/100)

Break-Even Customers:
= Break_Even_Revenue / Average_Check_Per_Customer

Ket Luan

Google Sheets co the la "back office" du manh cho nha hang co quy mo trung binh — thiet lap bien phap quan ly tai chinh, nguyen lieu, nhan su va dich vu khach hang trong 1 file thong nhat. Quan trong hon la xay dung thoi quen cap nhat du lieu hang ngay — moi thong tin chi co gia tri khi duoc nhap dung va day du.

Kham pha them cac giai phap quan ly F&B chuyen nghiep tai SheetStore hoac xem giai phap chuyen biet cho nha hang & cafe.

Tai Nguyen Bo Sung

• Huong dan Google Sheets tu A-Z 2026
• Ham QUERY Google Sheets toan dien
• Quan ly kho voi Google Sheets