Excel cho Revenue Management phù hợp để làm sạch dữ liệu, kiểm tra báo cáo, tổng hợp doanh thu theo ngày lưu trú và quan sát tốc độ đặt phòng. Công cụ này đặc biệt hữu ích khi người dùng đang học doanh thu khách sạn, chưa có hệ thống RMS hoặc cần thử nghiệm một cách phân tích trước khi tự động hóa.
Excel không thay thế PMS, channel manager hay RMS. Chất lượng phân tích vẫn phụ thuộc vào cấu trúc dữ liệu nguồn, quy ước trạng thái đặt phòng và cách khách sạn ghi nhận doanh thu. Các bảng và công thức dưới đây là ví dụ giả định, không phải quy trình bắt buộc cho mọi khách sạn.
Excel hỗ trợ Revenue Management như thế nào?
Một file Excel doanh thu khách sạn có thể hỗ trợ bốn nhóm công việc chính:
- Làm sạch dữ liệu xuất từ PMS, OTA hoặc channel manager.
- Tra cứu và chuẩn hóa tên kênh, phân khúc, loại phòng, trạng thái booking.
- Tổng hợp room revenue, số phòng bán và ADR theo ngày lưu trú.
- So sánh các snapshot để tính pickup, drop và trực quan hóa booking pace.
Điểm quan trọng là phải xác định câu hỏi kinh doanh trước khi tạo công thức. Chẳng hạn, báo cáo có thể trả lời: ngày lưu trú nào đang tăng booking nhanh, kênh nào đóng góp doanh thu, ngày nào còn nhiều phòng trống hoặc mức giá bán bình quân thay đổi ra sao.
Nếu cần xây dựng luồng báo cáo hoàn chỉnh thay vì chỉ tạo một bảng tính riêng lẻ, có thể tham khảo cách tổ chức hệ thống Revenue Report khách sạn từ dữ liệu nguồn đến quyết định.
Cấu trúc file Excel doanh thu khách sạn
Nên tách dữ liệu nguồn, dữ liệu đã làm sạch và báo cáo thành các sheet khác nhau. Một workbook cơ bản có thể gồm:
| Sheet | Mục đích |
|---|---|
| Raw_Data | Lưu nguyên dữ liệu xuất từ hệ thống, không sửa trực tiếp |
| Mapping | Lập bảng quy đổi tên kênh, trạng thái, loại phòng hoặc phân khúc |
| Clean_Data | Chứa dữ liệu đã chuẩn hóa để tính toán |
| Daily_Summary | Tổng hợp theo ngày lưu trú |
| Pace_Data | Lưu các snapshot OTB theo ngày chụp dữ liệu |
| Dashboard | Biểu đồ và các chỉ số cần theo dõi |
Sau khi đưa dữ liệu vào Excel, nên dùng Ctrl + T để chuyển vùng dữ liệu thành Table. Table tự mở rộng khi thêm dòng mới, dùng được tên cột trong công thức và giảm nguy cơ bỏ sót dữ liệu.
Bảng dữ liệu giả định
Ví dụ dưới đây giả định mỗi dòng thể hiện số phòng và room revenue của một booking trong một ngày lưu trú:
| Booking_ID | Snapshot_Date | Booking_Date | Stay_Date | Status | Channel | Rooms | Room_Revenue |
|---|---|---|---|---|---|---|---|
| B001 | 01/06/2025 | 28/05/2025 | 15/06/2025 | Confirmed | OTA A | 1 | 1.200.000 |
| B002 | 01/06/2025 | 30/05/2025 | 15/06/2025 | Confirmed | Direct | 2 | 2.600.000 |
| B003 | 01/06/2025 | 31/05/2025 | 16/06/2025 | Cancelled | OTA B | 1 | 0 |
| B004 | 01/06/2025 | 01/06/2025 | 16/06/2025 | Confirmed | Corporate | 1 | 1.100.000 |
Các con số chỉ dùng để minh họa thao tác Excel. Khi áp dụng thực tế, cần thống nhất room revenue đã gồm hay chưa gồm thuế, phí, phụ thu và các khoản ngoài doanh thu phòng.
Nếu dữ liệu nguồn chỉ có ngày đến và ngày đi, cần chuyển mỗi booking thành các dòng theo từng ngày lưu trú. Ngày trả phòng thường không được tính là một room night, nhưng quy tắc cuối cùng phải phù hợp với dữ liệu PMS của khách sạn. Không nên sao chép toàn bộ doanh thu của kỳ lưu trú vào từng ngày vì thao tác đó sẽ làm tăng doanh thu sai lệch; nên dùng doanh thu phòng theo ngày hoặc một nguyên tắc phân bổ được ghi rõ.
Bước 1: Chuẩn hóa và làm sạch dữ liệu
Giữ nguyên dữ liệu nguồn
Không nên chỉnh sửa trực tiếp sheet Raw_Data. Khi phát hiện lỗi, hãy sửa trong Power Query, cột phụ hoặc sheet Clean_Data. Cách này giúp đối chiếu lại dữ liệu ban đầu và tìm nguyên nhân khi tổng báo cáo không khớp.
Làm sạch mã booking và dữ liệu dạng chữ
Dữ liệu sao chép từ hệ thống có thể chứa khoảng trắng thừa hoặc ký tự không in được. Nếu bảng dữ liệu có tên tblBooking, có thể chuẩn hóa mã booking bằng công thức:
=UPPER(TRIM(CLEAN(SUBSTITUTE([@Booking_ID],CHAR(160)," "))))
Công thức kết hợp các thao tác:
SUBSTITUTEthay khoảng trắng đặc biệt bằng khoảng trắng thông thường.CLEANloại bỏ một số ký tự không in được.TRIMxóa khoảng trắng thừa.UPPERthống nhất chữ hoa cho mã booking.
Không nên tự động đổi tên kênh hoặc trạng thái chỉ bằng Find & Replace nếu có nguy cơ thay nhầm nội dung. Bảng mapping và hàm tra cứu sẽ dễ kiểm soát hơn.
Chuyển ngày dạng text thành ngày Excel
Cần kiểm tra Booking_Date, Stay_Date và Snapshot_Date có phải ngày Excel thật hay chỉ là chuỗi ký tự. Có thể thử đổi định dạng ô sang Number: nếu ô hiển thị số tuần tự, Excel đã nhận diện đó là ngày.
Với chuỗi cố định theo dạng dd/mm/yyyy, một cách chuyển đổi là:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
Chỉ nên dùng công thức này khi toàn bộ dữ liệu tuân thủ đúng một cấu trúc. Nếu file trộn dd/mm/yyyy, mm/dd/yyyy và ngày có kèm giờ, nên xử lý bằng Power Query và chỉ định locale phù hợp để tránh đảo ngày với tháng.
Kiểm soát dòng trùng
Không phải mọi dòng lặp Booking_ID đều là dữ liệu trùng. Một booking nhiều đêm có thể xuất hiện ở nhiều ngày lưu trú; cùng một booking cũng xuất hiện trong nhiều snapshot của báo cáo pace.
Tùy cấu trúc dữ liệu, khóa kiểm tra có thể là tổ hợp:
Booking_ID + Stay_Date + Snapshot_Date + Room_Type
Chỉ xóa dòng khi đã xác nhận đó là bản ghi trùng hoàn toàn. Không nên xóa tất cả Booking_ID lặp lại vì có thể làm mất room night hoặc lịch sử thay đổi booking.
Bước 2: Tra cứu và phân nhóm dữ liệu
Một trong những nhóm hàm Excel cho Revenue Manager hữu ích nhất là hàm tra cứu. Thay vì viết nhiều điều kiện IF lồng nhau, có thể tạo bảng tblChannelMap gồm hai cột:
| Raw_Channel | Channel_Group |
|---|---|
| OTA A | OTA |
| OTA B | OTA |
| Website | Direct |
| Walk-in | Direct |
| Company Contract | Corporate |
Sau đó dùng XLOOKUP để phân nhóm:
=XLOOKUP([@Channel],tblChannelMap[Raw_Channel],tblChannelMap[Channel_Group],"Chưa phân loại")
Giá trị Chưa phân loại giúp phát hiện tên kênh mới hoặc dữ liệu sai chính tả. Không nên mặc định các trường hợp không tìm thấy thành OTA hay Direct vì có thể làm sai cơ cấu kênh bán.
Nếu phiên bản Excel chưa hỗ trợ XLOOKUP, có thể dùng:
=IFERROR(INDEX(tblChannelMap[Channel_Group],MATCH([@Channel],tblChannelMap[Raw_Channel],0)),"Chưa phân loại")
Cách tương tự có thể áp dụng cho:
- Chuẩn hóa trạng thái booking.
- Gom loại phòng vào nhóm phòng.
- Phân loại retail, corporate, wholesale hoặc group.
- Gắn cờ booking được tính vào OTB.
Quy tắc trạng thái cần do khách sạn xác định. Chẳng hạn, Confirmed có thể được tính vào OTB, còn Cancelled không được tính; nhưng các trạng thái như No-show, Tentative hoặc Waitlist có thể được xử lý khác nhau giữa các đơn vị.
Bước 3: Tổng hợp theo ngày lưu trú
Revenue Management thường cần nhìn theo Stay_Date, không chỉ theo ngày tạo booking hay ngày xuất báo cáo. Ba khái niệm cần phân biệt là:
Booking_Date: ngày đặt phòng được tạo.Stay_Date: ngày khách sử dụng phòng.Snapshot_Date: ngày chụp trạng thái dữ liệu OTB.
Nhầm ba trường này là nguyên nhân phổ biến khiến báo cáo pace hoặc doanh thu theo ngày bị sai.
Tạo danh sách ngày lưu trú
Với Excel 365, có thể lấy danh sách ngày lưu trú duy nhất:
=SORT(UNIQUE(tblBooking[Stay_Date]))
Nếu không có hàm mảng động, có thể dùng PivotTable với Stay_Date ở vùng Rows.
Tổng hợp số phòng và doanh thu
Giả sử bảng đã có cột Active_Flag, trong đó booking được tính có giá trị 1 và booking không được tính có giá trị 0. Công thức tổng số phòng theo ngày là:
=SUMIFS(tblBooking[Rooms],tblBooking[Stay_Date],[@Stay_Date],tblBooking[Active_Flag],1)
Công thức tổng room revenue:
=SUMIFS(tblBooking[Room_Revenue],tblBooking[Stay_Date],[@Stay_Date],tblBooking[Active_Flag],1)
ADR được tính từ room revenue chia cho số phòng bán:
=IFERROR([@Room_Revenue]/[@Rooms_Sold],0)
Nếu có dữ liệu số phòng khả dụng, có thể tính thêm:
Occupancy = Rooms Sold / Available Rooms
RevPAR = Room Revenue / Available Rooms
Số phòng khả dụng cần phản ánh đúng quyết định về phòng khóa bán, phòng hỏng hoặc thay đổi inventory. Không nên mặc định dùng tổng số phòng vật lý nếu báo cáo vận hành của khách sạn áp dụng quy tắc khác.
Tổng hợp bằng PivotTable
Một PivotTable theo ngày lưu trú có thể được thiết lập như sau:
- Rows:
Stay_Date. - Values: Sum of
Rooms, Sum ofRoom_Revenue. - Columns hoặc Filters:
Channel_Group,Room_Type,Segment. - Filters:
Active_Flag,Status.
Nếu cần đếm số booking, cần lưu ý một booking nhiều đêm có nhiều dòng. COUNT thông thường sẽ đếm số dòng, không phải số booking duy nhất. Có thể thêm dữ liệu vào Data Model và dùng Distinct Count cho Booking_ID.
Tổng doanh thu từ file phân tích nên được đối chiếu với báo cáo vận hành. Quy trình đóng ngày và đối soát doanh thu khách sạn giúp xác định rõ số nào đã chốt và số nào mới là dữ liệu OTB cho tương lai.
Bước 4: Tính pickup và phân tích booking pace
Booking pace thể hiện tốc độ booking được tích lũy trước ngày lưu trú. Để phân tích pace, cần lưu nhiều snapshot OTB tại các thời điểm khác nhau. Một file xuất tại duy nhất một thời điểm không thể cho biết booking đã tăng hoặc giảm như thế nào.
Bảng snapshot tối thiểu có thể gồm:
| Snapshot_Date | Stay_Date | OTB_Rooms | OTB_Revenue |
|---|---|---|---|
| 01/06/2025 | 15/06/2025 | 12 | 14.400.000 |
| 08/06/2025 | 15/06/2025 | 18 | 22.000.000 |
| 01/06/2025 | 16/06/2025 | 10 | 11.500.000 |
| 08/06/2025 | 16/06/2025 | 9 | 10.600.000 |
Đây là số liệu giả định để minh họa. Chênh lệch OTB có thể đến từ booking mới, hủy phòng, thay đổi số đêm, chuyển ngày ở, điều chỉnh giá hoặc sửa số lượng phòng.
Tính lead time
Trong từng dòng snapshot, số ngày còn lại trước ngày lưu trú được tính bằng:
=[@Stay_Date]-[@Snapshot_Date]
Định dạng kết quả là Number. Có thể đặt tên cột là Lead_Days.
Tạo ma trận OTB theo snapshot
Giả sử bảng snapshot có tên tblSnap, cột A chứa Stay_Date và hàng 1 chứa các Snapshot_Date. Công thức lấy OTB Rooms tại giao điểm là:
=IF(COUNTIFS(tblSnap[Stay_Date],$A2,tblSnap[Snapshot_Date],B$1)=0,NA(),SUMIFS(tblSnap[OTB_Rooms],tblSnap[Stay_Date],$A2,tblSnap[Snapshot_Date],B$1))
Việc trả về #N/A khi không có snapshot giúp phân biệt dữ liệu bị thiếu với OTB bằng 0. Nếu thay mọi ô thiếu bằng 0, báo cáo có thể tạo ra một khoản drop không có thật.
Pickup giữa hai snapshot được tính bằng:
Pickup Rooms = OTB Rooms kỳ sau - OTB Rooms kỳ trước
Pickup Revenue = OTB Revenue kỳ sau - OTB Revenue kỳ trước
Kết quả âm thường được gọi là drop, nhưng không nên mặc định toàn bộ drop là cancellation. Cần đối chiếu dữ liệu chi tiết để xác định booking bị hủy, rút ngắn kỳ lưu trú, chuyển ngày hoặc được điều chỉnh thủ công. Một báo cáo Pickup/Drop hàng ngày nên lưu cả snapshot hiện tại và snapshot so sánh để người dùng truy ngược được chênh lệch.
So sánh các đường pace
Để so sánh pace giữa các giai đoạn, có thể tạo PivotTable:
- Rows:
Lead_Days. - Columns: tháng lưu trú, nhóm ngày hoặc kỳ so sánh.
- Values: OTB Rooms, OTB Revenue hoặc Occupancy OTB.
Các đường pace chỉ có ý nghĩa khi các kỳ so sánh tương đối đồng nhất. Nếu số ngày, quy mô inventory hoặc cơ cấu phòng khác nhau, nên chuẩn hóa theo số phòng khả dụng hoặc tách thành các nhóm phù hợp thay vì chỉ so sánh tổng số phòng.
Bước 5: Trực quan hóa dữ liệu
Biểu đồ đường cho booking pace
Biểu đồ đường phù hợp để thể hiện OTB Rooms hoặc OTB Occupancy theo Lead_Days:
- Trục ngang: số ngày trước ngày lưu trú.
- Trục dọc: OTB Rooms hoặc OTB Occupancy.
- Mỗi đường: một tháng, một ngày lưu trú hoặc một nhóm ngày so sánh.
Nên sắp xếp lead time theo một chiều nhất quán, chẳng hạn từ xa đến gần ngày lưu trú. Tiêu đề biểu đồ cần ghi rõ chỉ số, kỳ lưu trú và đơn vị.
Biểu đồ cột cho pickup và drop
Biểu đồ cột theo Stay_Date giúp nhận biết ngày nào có pickup cao hoặc drop đáng chú ý. Có thể dùng màu khác nhau cho giá trị dương và âm, nhưng không nên dùng quá nhiều màu cho các kênh hoặc phân khúc trên cùng một biểu đồ.
Biểu đồ xu hướng doanh thu và ADR
Có thể dùng biểu đồ đường để theo dõi Room Revenue, Rooms Sold hoặc ADR theo ngày lưu trú. Khi so sánh nhiều tháng, cần giữ cùng cách tính và phạm vi ngày. Cách thiết kế biểu đồ Revenue Trend so sánh các tháng có thể hỗ trợ nhận biết xu hướng mà không biến dashboard thành một bảng quá nhiều chỉ số.
Hạn chế dùng biểu đồ 3D vì khó so sánh độ cao. Nếu dùng hai trục cho Rooms Sold và ADR, cần ghi rõ đơn vị và kiểm tra thang đo để tránh tạo cảm giác tương quan không có căn cứ.
Kiểm tra chất lượng trước khi sử dụng báo cáo
Trước khi dùng file để đưa ra quyết định giá hoặc kênh bán, nên kiểm tra:
- Phạm vi ngày: dữ liệu đã bao phủ đúng kỳ lưu trú cần phân tích chưa?
- Kiểu dữ liệu: các cột ngày, số phòng và doanh thu có phải dạng số thực sự không?
- Trạng thái booking: quy tắc tính Confirmed, Cancelled, No-show và Tentative đã được thống nhất chưa?
- Dòng trùng: đã kiểm tra đúng theo Booking_ID, Stay_Date và Snapshot_Date chưa?
- Đơn vị doanh thu: room revenue đã gồm thuế, phí hoặc dịch vụ khác chưa?
- Snapshot thiếu: có ngày nào không lưu dữ liệu nhưng đang bị hiểu nhầm thành OTB bằng 0 không?
- Đối soát: tổng số phòng và doanh thu có khớp với báo cáo nguồn trong cùng phạm vi không?
- Khả năng truy vết: từ một con số trên dashboard có thể quay lại dòng booking tạo ra con số đó không?
Sau khi dữ liệu pace đã ổn định, có thể kết hợp OTB hiện tại, pickup gần đây, lịch sử, ngày trong tuần và yếu tố kinh doanh đã biết để lập forecast. Một mẫu forecast doanh thu khách sạn theo ngày và tháng có thể là bước tiếp theo, nhưng forecast vẫn cần giả định rõ ràng và không nên được xem là kết quả chắc chắn.
Một file Excel cho Revenue Management tốt không nhất thiết phải có nhiều công thức. File cần trả lời đúng câu hỏi, giữ được dữ liệu nguồn, phân biệt rõ ngày đặt với ngày lưu trú, lưu lịch sử snapshot và cho phép kiểm tra lại từng con số trước khi người dùng quyết định giá hoặc điều chỉnh kênh bán.
