File Excel tính điểm hòa vốn: công thức, ví dụ quán cà phê và bảng phân tích độ nhạy
Cách lập file Excel tính điểm hòa vốn: phân loại định phí và biến phí, số dư đảm phí, sản lượng và doanh thu hòa vốn, lợi nhuận mục tiêu, biên an toàn, điểm hòa vốn nhiều sản phẩm và bảng độ nhạy, kèm ví dụ quán cà phê.
Trả lời nhanh: điểm hòa vốn là mức bán mà tại đó doanh thu vừa đủ bù toàn bộ chi phí, lợi nhuận bằng 0. Công thức cốt lõi trong Excel:
- Số dư đảm phí mỗi sản phẩm = Giá bán − Biến phí đơn vị
- Sản lượng hòa vốn = Tổng định phí ÷ Số dư đảm phí mỗi sản phẩm →
=ROUNDUP(DinhPhi/(GiaBan-BienPhi),0) - Doanh thu hòa vốn = Tổng định phí ÷ Tỷ lệ số dư đảm phí →
=DinhPhi/((GiaBan-BienPhi)/GiaBan)
Ví dụ: quán cà phê có định phí 50 triệu/tháng, giá bán bình quân 35.000đ/ly, biến phí 12.000đ/ly thì cần bán 2.174 ly/tháng (khoảng 73 ly/ngày) để hòa vốn. Bài này hướng dẫn dựng file từ đầu, thêm lợi nhuận mục tiêu, biên an toàn, trường hợp nhiều sản phẩm và bảng độ nhạy. Số liệu ví dụ là giả định để minh họa.
Bước 1: Tách định phí và biến phí
Sai lầm phổ biến nhất khi tính điểm hòa vốn là xếp nhầm chi phí. Nguyên tắc: chi phí nào tăng theo số lượng bán là biến phí; chi phí nào giữ nguyên dù bán nhiều hay ít (trong một phạm vi hoạt động) là định phí.
| Loại | Ví dụ quán cà phê | Ví dụ shop bán online |
|---|---|---|
| Định phí | Thuê mặt bằng, lương nhân viên cố định, internet, khấu hao máy pha, phần mềm bán hàng | Lương, thuê kho, phí gói phần mềm, quảng cáo thương hiệu cố định hằng tháng |
| Biến phí | Cà phê, sữa, đá, ly, nắp, ống hút, phí giao hàng qua ứng dụng | Giá vốn hàng, bao bì, phí sàn theo đơn, phí vận chuyển người bán chịu, phí cổng thanh toán |
| Hỗn hợp | Điện (phần nền cố định + phần tăng theo giờ chạy máy) | Lương có hoa hồng theo doanh số |
Chi phí hỗn hợp nên tách làm hai: phần cố định đưa vào định phí, phần thay đổi chia theo sản lượng đưa vào biến phí. Nếu bạn đã theo dõi chi tiêu trong file Excel quản lý chi phí, hãy thêm một cột “Định phí/Biến phí” để tổng hợp nhanh bằng SUMIFS.
Bước 2: Dựng sheet “Đầu vào”
Đặt mọi giả định ở một chỗ, đặt tên ô (Formulas → Define Name) để công thức dễ đọc:
| Ô | Tên | Nội dung | Ví dụ |
|---|---|---|---|
| B3 | GiaBan | Giá bán bình quân (chưa gồm thuế nếu bạn tách thuế) | 35.000 |
| B4 | BienPhi | Biến phí đơn vị | 12.000 |
| B5 | DinhPhi | Tổng định phí tháng | 50.000.000 |
| B6 | LoiNhuanMucTieu | Lợi nhuận muốn đạt | 20.000.000 |
| B7 | SanLuongDuKien | Sản lượng dự kiến bán | 3.000 |
| B8 | SoNgay | Số ngày mở cửa trong tháng | 30 |
Biến phí đơn vị 12.000đ trong ví dụ gồm: nguyên liệu 9.000đ, ly – nắp – ống hút 1.500đ, chi phí khác theo ly 1.500đ. Định phí 50 triệu gồm: thuê mặt bằng 15 triệu, lương 24 triệu, điện nước – internet phần cố định 5 triệu, khấu hao máy móc 2 triệu, chi phí khác 4 triệu.
Bước 3: Sheet “Kết quả” với các công thức
| Chỉ tiêu | Công thức Excel | Kết quả ví dụ |
|---|---|---|
| Số dư đảm phí/ly | =GiaBan-BienPhi |
23.000đ |
| Tỷ lệ số dư đảm phí | =(GiaBan-BienPhi)/GiaBan |
65,7% |
| Sản lượng hòa vốn | =ROUNDUP(DinhPhi/(GiaBan-BienPhi),0) |
2.174 ly |
| Doanh thu hòa vốn | =DinhPhi/((GiaBan-BienPhi)/GiaBan) |
≈ 76,1 triệu |
| Hòa vốn mỗi ngày | =ROUNDUP(DinhPhi/(GiaBan-BienPhi)/SoNgay,0) |
73 ly/ngày |
| Sản lượng để đạt lợi nhuận mục tiêu | =ROUNDUP((DinhPhi+LoiNhuanMucTieu)/(GiaBan-BienPhi),0) |
3.044 ly |
| Lợi nhuận tại sản lượng dự kiến | =SanLuongDuKien*(GiaBan-BienPhi)-DinhPhi |
19 triệu |
| Biên an toàn | =(SanLuongDuKien-DinhPhi/(GiaBan-BienPhi))/SanLuongDuKien |
27,5% |
Kiểm tra lại: 2.174 × 23.000 = 50.002.000đ, vừa vượt định phí 50 triệu. Với 3.000 ly dự kiến, lợi nhuận là 3.000 × 23.000 − 50.000.000 = 19 triệu, thiếu 1 triệu so với mục tiêu 20 triệu, khớp với kết quả cần 3.044 ly.
Biên an toàn 27,5% nghĩa là doanh số có thể giảm khoảng 27,5% so với dự kiến trước khi quán bắt đầu lỗ. Biên an toàn càng thấp, kinh doanh càng nhạy cảm với tháng vắng khách.
Bước 4: Bảng phân tích độ nhạy
Điểm hòa vốn thay đổi mạnh khi giá, biến phí hoặc định phí thay đổi. Tạo một bảng nhỏ để thấy ngay tác động:
| Kịch bản | Thay đổi | Sản lượng hòa vốn | So với gốc |
|---|---|---|---|
| Gốc | Giá 35.000, biến phí 12.000, định phí 50 triệu | 2.174 ly | — |
| Tăng giá 10% | Giá 38.500 | 1.887 ly | −287 ly |
| Nguyên liệu tăng | Biến phí 14.000 | 2.381 ly | +207 ly |
| Tiền thuê tăng | Định phí 55 triệu | 2.392 ly | +218 ly |
Cách làm nhanh trong Excel: lập bảng hai chiều với giá bán ở hàng tiêu đề và biến phí ở cột đầu, ô góc trên bên trái trỏ tới công thức sản lượng hòa vốn, rồi dùng Data → What-If Analysis → Data Table (Row input cell = GiaBan, Column input cell = BienPhi). Nếu không muốn dùng Data Table, viết thẳng công thức trong từng ô: =ROUNDUP(DinhPhi/(C$2-$B3),0) với C2 là giá, B3 là biến phí, rồi kéo sang phải và xuống dưới.
Thêm định dạng có điều kiện: tô xanh các ô có sản lượng hòa vốn thấp hơn sản lượng dự kiến, tô đỏ các ô cao hơn. Bảng này giúp trả lời nhanh các câu hỏi như “nếu cà phê nguyên liệu tăng 2.000đ/ly thì phải tăng giá bao nhiêu để giữ nguyên điểm hòa vốn?”.
Bước 5: Điểm hòa vốn khi bán nhiều sản phẩm
Quán thực tế bán nhiều món với số dư đảm phí khác nhau. Dùng số dư đảm phí bình quân gia quyền theo cơ cấu bán hàng:
| Sản phẩm | Giá bán | Biến phí | Số dư đảm phí | Tỷ trọng sản lượng |
|---|---|---|---|---|
| Cà phê sữa | 30.000 | 10.000 | 20.000 | 50% |
| Trà trái cây | 40.000 | 14.000 | 26.000 | 30% |
| Bánh ngọt | 25.000 | 12.000 | 13.000 | 20% |
- Số dư đảm phí bình quân:
=SUMPRODUCT(D2:D4,E2:E4)= 20.400đ - Giá bán bình quân:
=SUMPRODUCT(B2:B4,E2:E4)= 32.000đ - Tổng sản lượng hòa vốn:
=ROUNDUP(DinhPhi/SUMPRODUCT(D2:D4,E2:E4),0)= 2.451 sản phẩm - Doanh thu hòa vốn ≈ 78,4 triệu (tỷ lệ số dư đảm phí bình quân 63,75%)
- Chia theo cơ cấu: khoảng 1.225 ly cà phê sữa, 735 ly trà trái cây và 490 bánh ngọt
Lưu ý: kết quả chỉ đúng khi cơ cấu bán hàng giữ như giả định. Nếu khách chuyển sang mua nhiều bánh (số dư đảm phí thấp), điểm hòa vốn sẽ tăng. Lấy tỷ trọng thực tế từ file Excel quản lý bán hàng và cập nhật hằng tháng.
Bước 6: Vẽ biểu đồ hòa vốn
- Tạo cột Sản lượng từ 0 đến 4.000, bước 250.
- Cột Doanh thu:
=A12*GiaBan; cột Tổng chi phí:=DinhPhi+A12*BienPhi; cột Định phí:=DinhPhi. - Chọn ba cột, chèn biểu đồ Line (hoặc Scatter with Straight Lines để trục hoành đúng tỉ lệ).
- Giao điểm của đường doanh thu và đường tổng chi phí là điểm hòa vốn; vùng bên phải là vùng có lãi.
Biểu đồ này rất hữu ích khi trình bày với đối tác, ngân hàng hoặc người góp vốn, vì nhìn một lần là hiểu cần bán bao nhiêu mới có lãi.
Đọc kết quả và ra quyết định
- Điểm hòa vốn quá cao so với khả năng bán: xem lại giá, đàm phán giá nguyên liệu, hoặc giảm định phí (mặt bằng nhỏ hơn, bố trí ca hợp lý hơn).
- Số dư đảm phí thấp: ưu tiên đẩy các món có số dư đảm phí cao trong thực đơn, combo và gợi ý bán kèm.
- Biên an toàn dưới 10–15%: rủi ro cao, nên có quỹ dự phòng và theo dõi dòng tiền doanh nghiệp sát hơn.
- Trước khi mở chi nhánh mới: tính điểm hòa vốn riêng cho chi nhánh với định phí của mặt bằng mới.
Lỗi thường gặp
- Đưa giá vốn của cả tháng vào định phí: giá vốn hàng bán là biến phí.
- Quên phí sàn, phí ứng dụng giao đồ ăn tính theo phần trăm đơn: đây là biến phí, làm giảm số dư đảm phí đáng kể.
- Dùng giá niêm yết thay cho giá bán thực sau khuyến mãi, giảm giá.
- Không làm tròn lên sản lượng hòa vốn, dẫn tới con số thiếu.
- Nhầm điểm hòa vốn với hòa vốn tiền mặt: khấu hao không chi tiền hằng tháng, nhưng trả gốc vay thì có. Để xem đủ tiền hay không, đối chiếu với kế hoạch dòng tiền.
Câu hỏi thường gặp
Điểm hòa vốn và thời gian hoàn vốn khác nhau thế nào?
Điểm hòa vốn trả lời “mỗi tháng cần bán bao nhiêu để không lỗ”. Thời gian hoàn vốn trả lời “bao lâu thì thu hồi được số tiền đầu tư ban đầu” (sửa sang, máy móc, đặt cọc). Hai chỉ tiêu bổ sung cho nhau.
Giá bán nên tính trước hay sau thuế?
Nhất quán là quan trọng nhất. Nếu bạn kê khai thuế giá trị gia tăng, dùng giá chưa thuế cho cả giá bán và biến phí. Nếu là hộ kinh doanh nộp thuế theo tỷ lệ trên doanh thu, có thể coi khoản thuế đó như một biến phí theo phần trăm doanh thu.
Có làm trên Google Sheets được không?
Được. ROUNDUP, SUMPRODUCT, SUMIFS và biểu đồ đều có trên Google Sheets; riêng Data Table không có, hãy dùng cách viết công thức trực tiếp trong bảng độ nhạy như hướng dẫn ở Bước 4.
Bao lâu nên tính lại điểm hòa vốn?
Ít nhất mỗi quý, và ngay khi có thay đổi lớn: tăng tiền thuê, tăng lương, đổi nhà cung cấp, điều chỉnh giá bán hoặc thay đổi thực đơn.
Để xem lợi nhuận thực tế từng tháng, kết hợp với mẫu báo cáo kết quả kinh doanh Excel. Xem thêm các hướng dẫn trong nhóm Tài chính doanh nghiệp nhỏ.