Cách tính hoa hồng bán hàng trong bảng tính (Tỷ lệ phân tầng và phân chia)

Tính toán chính xác hoa hồng bán hàng trên bảng tính phụ thuộc vào bốn quyết định cốt lõi. Các bậc hoa hồng của bạn là lũy tiến hay cố định, và tỷ lệ hoa hồng được tra cứu như thế nào? Tiếp theo, một giao dịch chung được phân chia ra sao, và các khoản thu hồi (clawback) được ghi nhận ở đâu? Chỉ cần sai sót ở quyết định đầu tiên, mọi con số phía sau đều sẽ sai lệch.

Phép toán không hề khó. Điều khiến nó trở nên phức tạp là các quy tắc nằm trong một tài liệu kế hoạch do người khác viết. Sau đó, bảng tính phải mã hóa các quy tắc đó dưới dạng mà đồng nghiệp có thể kiểm toán được.

Hướng dẫn này sẽ giải thích lý do tại sao việc này làm hỏng các bảng tính, ba phương pháp mọi người thường dùng, và thời điểm mô hình không còn đáp ứng được những thay đổi của kế hoạch. Đây là một quy trình xử lý dữ liệu, không phải lời khuyên về bảng lương hay pháp lý, vì vậy hãy xác nhận kết quả với người chịu trách nhiệm quản lý kế hoạch.

Tại sao hoa hồng bán hàng làm hỏng bảng tính

Vấn đề đầu tiên là từ "bậc" (tiered) có hai ý nghĩa khác nhau, và các tài liệu kế hoạch hiếm khi chỉ rõ đó là loại nào.

Trong kế hoạch bậc cố định (flat tier), khi đạt đến một mốc doanh số, tỷ lệ của mốc đó sẽ áp dụng cho toàn bộ số tiền. Trong kế hoạch bậc lũy tiến (progressive tier), mỗi phần của số tiền sẽ được tính theo tỷ lệ của mốc mà nó rơi vào, tương tự như cách hoạt động của các bậc thuế thu nhập. Với doanh số $120,000 được chia theo các mốc 5%, 7% và 9%, hai cách hiểu này sẽ cho ra kết quả chênh lệch nhau tới hàng nghìn đô la.

Vấn đề thứ hai là một giao dịch không chỉ nằm ở một hàng mãi được. Một giao dịch chung sẽ được tách thành hai hàng, một hệ số tăng tốc (accelerator) làm thay đổi tỷ lệ hoa hồng giữa kỳ, một khoản hoàn tiền sẽ đảo ngược một phần của khoản thanh toán, và một mức trần (cap) sẽ giới hạn tổng số tiền.

Vấn đề thứ ba là khả năng kiểm toán. Hoa hồng phải giải thích được cho người nhận nó. Một ô duy nhất chứa sáu hàm IF lồng nhau thì không thể giải thích nổi, và đó lại là định dạng mà hầu hết các mô hình này thường sử dụng.

Việc làm tròn số tích lũy một cách âm thầm. Làm tròn ở mỗi bước trung gian, thay vì chỉ làm tròn một lần khi thanh toán, sẽ tạo ra sai số tăng dần theo số lượng hàng và không bao giờ khớp được với bảng lương.

Những tổn thất bạn phải gánh chịu

Những tranh chấp không thể giải quyết nhanh chóng. Khi một nhân viên kinh doanh thắc mắc về một con số, bạn cần chỉ ra lộ trình từ giao dịch đến thanh toán. Một công thức lồng nhau không thể đọc thành lời, vì vậy cuộc trò chuyện sẽ biến thành việc xây dựng lại từ đầu.

Một con số hoa hồng bán hàng không thể giải thích được là một con số sẽ tiếp tục bị khiếu nại vào quý tới.

Phải xây dựng lại mô hình mỗi năm. Tỷ lệ, các mốc doanh số và hệ số tăng tốc thay đổi hàng năm và đôi khi thay đổi theo từng nhân viên. Một mô hình mã hóa tỷ lệ trực tiếp bên trong các công thức sẽ phải viết lại hoàn toàn thay vì chỉ cần cấu hình lại.

Sự chậm trễ khi đối chiếu. Bảng lương yêu cầu chính xác đến từng xu. Một mô hình làm tròn số ở giữa quá trình tính toán sẽ tạo ra những chênh lệch nhỏ trên hàng trăm hàng, và việc tìm ra nguyên nhân còn mất nhiều thời gian hơn cả việc xây dựng mô hình ban đầu.

Những giải pháp tạm thời mọi người thường thử

Tùy chọn 1: Đưa tỷ lệ hoa hồng ra ngoài công thức

Hãy đặt các mốc doanh số và tỷ lệ hoa hồng vào một bảng nhỏ, sau đó tra cứu tỷ lệ thay vì nhập cứng nó. Hàm VLOOKUP với tham số tìm kiếm phạm vi được đặt thành TRUE sẽ tìm thấy mốc doanh số mà một giá trị rơi vào, với điều kiện bảng đó được sắp xếp theo thứ tự tăng dần.

Hàm XLOOKUP cũng làm điều tương tự với chế độ khớp rõ ràng cho "khớp chính xác hoặc mục nhỏ hơn tiếp theo", giúp dễ đọc hơn sau sáu tháng. Trong trường hợp logic thực sự là một chuỗi điều kiện ngắn, hàm IFS sẽ dễ đọc hơn nhiều so với các câu lệnh IF lồng nhau.

Đây là thay đổi mang lại giá trị cao nhất, bởi vì kế hoạch của năm tới sẽ chỉ là việc chỉnh sửa bảng thay vì viết lại công thức. Nó giải quyết hoàn toàn vấn đề của bậc cố định nhưng lại không giải quyết được bậc lũy tiến.

Tùy chọn 2: Tính toán bậc lũy tiến một cách chính xác

Đối với kế hoạch lũy tiến, hoa hồng là tổng số tiền rơi vào mỗi mốc nhân với tỷ lệ của mốc đó. Một bảng phụ trợ với mỗi hàng đại diện cho một mốc doanh số, hiển thị phần giao dịch nằm trong mốc đó, sẽ giúp việc này trở nên trực quan và dễ kiểm tra hơn.

Nếu bạn muốn tính toán trong một ô duy nhất, hàm SUMPRODUCT áp dụng trên các ngưỡng mốc doanh số và phần chênh lệch giữa các tỷ lệ liên tiếp sẽ cho ra cùng một kết quả. Dù bạn chọn hình thức nào, hãy giữ lại bảng phụ trợ ở đâu đó, vì đó là thứ bạn sẽ đưa ra để giải thích cho nhân viên kinh doanh khi họ không đồng ý với kết quả.

Chỉ áp dụng hàm ROUND một lần duy nhất tại con số thanh toán cuối cùng, và tuyệt đối không làm tròn ở giữa quá trình tính toán. Hạn chế của phương pháp này là việc bảo trì: mỗi khi thay đổi mốc doanh số, bạn sẽ phải tác động đến cả cấu trúc phụ trợ lẫn bảng tỷ lệ hoa hồng.

Tùy chọn 3: Xử lý các khoản phân chia, mức trần và thu hồi như các hàng trong sổ cái

Hãy cưỡng lại ý muốn điều chỉnh trực tiếp trên hàng giao dịch gốc. Thay vào đó, hãy ghi lại mọi sự kiện thành một hàng riêng biệt với một loại cụ thể: ghi nhận gốc, phân chia tỷ lệ, điều chỉnh hệ số tăng tốc, giảm trừ theo mức trần, thu hồi.

Khi đó, việc phân chia sẽ trở thành hai hàng phân bổ có tổng phần trăm phải bằng 100%, và việc kiểm tra tổng số này sẽ giúp phát hiện lỗi phổ biến nhất. Một khoản hoàn tiền sẽ trở thành một hàng có giá trị âm được ghi nhận vào kỳ phát sinh, giúp giữ nguyên các báo cáo của kỳ trước.

Điều này tạo ra một mô hình có thể được kiểm toán theo từng dòng, và đó chính là mục tiêu cốt lõi. Tuy nhiên, nó cũng tạo ra số lượng hàng nhiều gấp bốn lần, và đòi hỏi một tính kỷ luật mà bất kỳ ai chạm vào tệp tin cũng phải tuân theo. Hướng dẫn của chúng tôi về cách chuyển đổi dữ liệu xuất từ CRM thành báo cáo pipeline sẽ đề cập đến việc chuẩn bị dữ liệu giao dịch mà quy trình này phụ thuộc vào.

Mức trần chung. Cả ba phương pháp đều giả định rằng kế hoạch sẽ ổn định trong suốt kỳ hạn. Trên thực tế, các thay đổi giữa năm, các khoản bảo đảm một lần và các trường hợp ngoại lệ cho từng nhân viên thường được gửi qua email, và mỗi thay đổi đó là một sự điều chỉnh thủ công không ai ghi chép lại.

Cách tính hoa hồng bán hàng với Powerdrill Bloom

Bước 1: Tải lên dữ liệu giao dịch và bảng tỷ lệ hoa hồng

Tải lên tệp xuất giao dịch đã chốt và bảng tỷ lệ hoa hồng của kế hoạch cùng một lúc. Powerdrill Bloom sẽ phân tích hồ sơ của cả hai, nhờ đó các lỗi như thiếu người phụ trách, số tiền bị bỏ trống và tỷ lệ phân chia không đủ 100% sẽ được phát hiện trước khi bất kỳ khoản thanh toán nào được tính toán.

Bước 2: Mô tả các quy tắc của kế hoạch bằng ngôn ngữ tự nhiên

Hãy mô tả kế hoạch thay vì tự xây dựng nó. Hãy nêu rõ các bậc là lũy tiến, cung cấp các mốc doanh số cùng tỷ lệ hoa hồng, và chỉ định ngưỡng hệ số tăng tốc cũng như bất kỳ mức trần nào.

Sau đó, yêu cầu thực hiện các bước kiểm tra trong cùng một lượt. Hãy hỏi xem những giao dịch nào có tỷ lệ phân chia không đạt tổng 100%, và những nhân viên nào đã vượt qua ngưỡng hệ số tăng tốc ở giữa kỳ. Tiếp theo, hãy hỏi xem những khoản hoàn tiền nào rơi vào một kỳ khác so với giao dịch gốc của chúng.

Bước 3: Xuất biểu đồ, báo cáo hoặc slide trình bày

Xuất báo cáo chi tiết cho từng nhân viên hiển thị lộ trình từ giao dịch đến thanh toán, biểu đồ đạt chỉ tiêu so với hạn mức (quota), hoặc một bản tóm tắt cho bộ phận tài chính.

Tại sao phương pháp này vượt trội hơn việc xây dựng lại mô hình mỗi quý

	Phương pháp thủ công	Powerdrill Bloom
Tỷ lệ hoa hồng cho năm kế hoạch mới	Chỉnh sửa bảng, sau đó xác minh lại công thức	Mô tả các mốc doanh số và tỷ lệ mới
Bậc lũy tiến so với bậc cố định	Xây dựng lại cấu trúc phụ trợ	Cho biết kế hoạch sử dụng loại nào
Tỷ lệ phân chia không đạt tổng 100%	Cột kiểm tra thủ công	Hỏi xem những giao dịch nào không vượt qua kiểm tra
Giải thích một con số cho nhân viên kinh doanh	Tái dựng lại lộ trình công thức	Yêu cầu bảng phân tích chi tiết từ giao dịch đến thanh toán

Hàng cuối cùng chính là hàng giúp tiết kiệm thời gian thực tế nhất. Phần lớn công sức trong việc tính toán hoa hồng không nằm ở việc tính toán, mà là ở việc giải thích, và việc giải thích là điều mà một công thức lồng nhau khiến cho không thể thực hiện được.

Các sai lầm thường gặp

Áp dụng một tỷ lệ duy nhất cho toàn bộ số tiền trong kế hoạch lũy tiến. Đây là sai lầm gây tổn thất lớn nhất trong nhóm này và nó luôn dẫn đến việc trả thừa hoặc trả thiếu nhiều nhất cho những nhân viên xuất sắc nhất.

Nhập cứng tỷ lệ hoa hồng bên trong công thức. Cách này chỉ hoạt động được trong một năm và biến việc thay đổi kế hoạch của năm sau thành một công việc phải viết lại từ đầu. Hãy giữ các tỷ lệ hoa hồng trong một bảng biểu mà bạn có thể bàn giao cho bộ phận tài chính.

Làm tròn ở mọi bước. Chỉ làm tròn một lần tại thời điểm thanh toán. Việc làm tròn trung gian tạo ra sai số khiến số liệu không thể đối chiếu khớp với bảng lương.

Chỉnh sửa hàng gốc khi có khoản hoàn tiền. Việc này làm hỏng các báo cáo trước đó vốn đã được đồng ý. Hãy thêm một hàng có giá trị âm được ghi ngày trong kỳ phát sinh hoàn tiền.

Quên rằng tổng tỷ lệ phân chia phải bằng 100%. Hai khoản phân bổ 60% sẽ chi trả tới 120% hoa hồng và trông hoàn toàn bình thường trên trang tính.

Chỉ lưu giữ các quy tắc kế hoạch trong email. Một mô hình hoa hồng bán hàng có các quy tắc nằm trong một luồng email thì không thể kiểm toán hoặc bàn giao được. Hãy viết chúng trực tiếp vào bảng tính (workbook).

Lẫn lộn các định nghĩa về kỳ hạn. Ngày chốt giao dịch, ngày xuất hóa đơn và ngày nhận thanh toán sẽ cho ra ba kết quả khác nhau. Hãy chọn một định nghĩa, ghi lại và áp dụng cho mọi hàng — cùng một tính kỷ luật như đối với một báo cáo ngân sách so với thực tế.

Kết luận

Hãy quyết định xem kế hoạch là lũy tiến hay cố định, đưa các tỷ lệ hoa hồng vào một bảng, tính toán các mốc doanh số một cách rõ ràng, và ghi nhận các khoản phân chia, mức trần cũng như thu hồi thành các hàng riêng biệt. Cấu trúc đó sẽ đứng vững trước một cuộc kiểm toán và sự thay đổi kế hoạch. Một mô hình hoa hồng bán hàng được đánh giá dựa trên việc liệu người khác có thể hiểu và làm theo được hay không.

Điều khiến nó trở nên tốn kém là việc phải xây dựng lại mỗi khi kế hoạch thay đổi, cộng với việc phải giải thích sau đó. Nếu đó là nơi tiêu tốn thời gian trong quý của bạn, hãy thử Powerdrill Bloom trên tệp xuất giao dịch và bảng tỷ lệ hoa hồng của bạn. Xem thêm hướng dẫn của chúng tôi về cách tính chi phí thu hút khách hàng từ bảng tính, cùng với các trang Excel AI assistant và AI financial analysis.

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

Sự khác biệt giữa các bậc hoa hồng bán hàng cố định và lũy tiến là gì?

Bậc cố định áp dụng một tỷ lệ duy nhất cho toàn bộ số tiền sau khi đạt đến một mốc doanh số. Bậc lũy tiến chỉ áp dụng tỷ lệ của từng mốc cho phần số tiền nằm trong mốc đó, tương tự như các bậc thuế thu nhập.

Làm thế nào để tra cứu tỷ lệ hoa hồng mà không cần dùng các câu lệnh IF lồng nhau?

Hãy đặt các mốc doanh số và tỷ lệ hoa hồng vào một bảng đã được sắp xếp, sau đó sử dụng hàm VLOOKUP với tìm kiếm tương đối hoặc hàm XLOOKUP được thiết lập ở chế độ khớp chính xác hoặc nhỏ hơn tiếp theo. Cả hai cách đều cho phép bạn thay đổi tỷ lệ hoa hồng mà không cần chạm vào công thức.

Các giao dịch chung nên được xử lý như thế nào?

Hãy ghi nhận một hàng phân bổ cho mỗi nhân viên kinh doanh với tỷ lệ phần trăm rõ ràng, và thêm một bước kiểm tra để đảm bảo tổng các tỷ lệ phần trăm bằng 100%. Việc điều chỉnh trực tiếp trên hàng giao dịch gốc sẽ khiến cho việc phân chia không thể kiểm toán được.

Các khoản thu hồi và hoàn tiền được ghi nhận ở đâu?

Trong kỳ phát sinh hoàn tiền, dưới dạng một hàng có giá trị âm tham chiếu đến giao dịch gốc. Việc chỉnh sửa hàng gốc một cách hồi tố sẽ làm thay đổi các báo cáo vốn đã được đồng ý và thanh toán trước đó.

Khi nào thì nên làm tròn các con số?

Chỉ một lần duy nhất, tại số tiền thanh toán cuối cùng. Việc làm tròn ở các bước trung gian sẽ tạo ra sai số tích lũy trên nhiều hàng, đây là lý do phổ biến khiến mô hình hoa hồng không thể đối chiếu khớp với bảng lương.