วิธีคำนวณค่าคอมมิชชันการขายในสเปรดชีต (อัตราแบบขั้นบันไดและการแบ่งส่วนแบ่ง)

การคำนวณค่าคอมมิชชันการขายในสเปรดชีตให้ถูกต้องนั้นขึ้นอยู่กับการตัดสินใจ 4 ข้อด้วยกัน ได้แก่ ระดับขั้น (tier) ของคุณเป็นแบบก้าวหน้า (progressive) หรือแบบคงที่ (flat) และจะค้นหาอัตราค่าคอมมิชชันอย่างไร จากนั้น ดีลที่มีการแชร์กันจะถูกแบ่งอย่างไร และการเรียกคืนเงิน (clawback) จะไปตกอยู่ที่ไหน หากคุณพลาดข้อแรกไป ตัวเลขหลังจากนั้นทั้งหมดก็จะผิดเพี้ยนไปทันที

การคำนวณทางคณิตศาสตร์นั้นไม่ได้ยากอะไร สิ่งที่ทำให้มันยากคือการที่กฎเกณฑ์ต่างๆ อยู่ในเอกสารแผนงานที่เขียนขึ้นโดยคนอื่น จากนั้นสเปรดชีตจะต้องแปลงกฎเหล่านั้นให้อยู่ในรูปแบบที่เพื่อนร่วมงานสามารถตรวจสอบได้

คู่มือนี้จะอธิบายว่าทำไมสิ่งนี้จึงทำให้สเปรดชีตพัง วิธีการ 3 แบบที่ผู้คนนิยมใช้ และจุดที่โมเดลเริ่มไม่รองรับการเปลี่ยนแปลงของแผนงาน นี่คือเวิร์กโฟลว์ข้อมูล ไม่ใช่คำแนะนำด้านการจ่ายเงินเดือนหรือกฎหมาย ดังนั้นโปรดยืนยันผลลัพธ์กับผู้ดูแลแผนงานดังกล่าว

ทำไมค่าคอมมิชชันการขายจึงทำให้สเปรดชีตพัง

ปัญหาแรกคือคำว่า "tiered" (การแบ่งระดับขั้น) นั้นมีความหมายที่แตกต่างกันสองแบบ และเอกสารแผนงานก็แทบจะไม่เคยระบุเลยว่าเป็นแบบไหน

ในแผนงานแบบ flat tier เมื่อยอดถึงเกณฑ์ขั้นใดขั้นหนึ่งแล้ว อัตราของขั้นนั้นจะถูกนำไปใช้กับยอดทั้งหมด ส่วนในแผนงานแบบ progressive tier ยอดแต่ละส่วนจะได้รับอัตราตามขั้นที่ยอดส่วนนั้นตกอยู่ ซึ่งทำงานในลักษณะเดียวกับฐานภาษีเงินได้ สำหรับยอดจอง (bookings) จำนวน $120,000 ในช่วงระดับขั้นที่ 5%, 7% และ 9% การตีความทั้งสองแบบนี้จะให้ผลลัพธ์ที่ต่างกันถึงหลายพันดอลลาร์

ปัญหาที่สองคือ ดีลหนึ่งดีลจะไม่ได้อยู่เป็นแถวเดียวไปตลอด ดีลที่แชร์กันจะกลายเป็นสองแถว ตัวเร่งยอด (accelerator) จะเปลี่ยนอัตราค่าคอมมิชชันระหว่างงวด การคืนเงินจะหักล้างการชำระเงินบางส่วน และการจำกัดเพดาน (cap) จะตัดยอดรวมลง

ปัญหาที่สามคือความสามารถในการตรวจสอบได้ ค่าคอมมิชชันจะต้องสามารถอธิบายให้ผู้ที่ได้รับเข้าใจได้ เซลล์เดียวที่มีฟังก์ชัน IF ซ้อนกันถึง 6 ชั้นนั้นไม่สามารถอธิบายได้ และนั่นคือรูปแบบที่โมเดลส่วนใหญ่เหล่านี้มักจะเป็นกัน

การปัดเศษจะสะสมเพิ่มขึ้นอย่างเงียบๆ การปัดเศษในทุกขั้นตอนระหว่างทาง แทนที่จะปัดเศษเพียงครั้งเดียวตอนจ่ายเงิน จะทำให้เกิดความคลาดเคลื่อนที่เพิ่มขึ้นตามจำนวนแถว และไม่มีวันตรงกับยอดของฝ่ายจ่ายเงินเดือน

สิ่งที่คุณต้องสูญเสียจากเรื่องนี้

ข้อพิพาทที่คุณไม่สามารถแก้ไขได้โดยเร็ว เมื่อตัวแทนขาย (rep) ตั้งคำถามเกี่ยวกับตัวเลข คุณจำเป็นต้องแสดงให้เห็นที่มาที่ไปตั้งแต่ดีลไปจนถึงการจ่ายเงิน สูตรที่ซ้อนกันหลายชั้นไม่สามารถอธิบายให้เข้าใจได้ง่ายๆ การสนทนาจึงลงเอยด้วยการต้องสร้างโมเดลขึ้นมาใหม่

ตัวเลขค่าคอมมิชชันการขายที่ไม่สามารถอธิบายได้ คือตัวเลขที่จะถูกโต้แย้งอีกครั้งในไตรมาสถัดไป

ต้องสร้างใหม่ทุกปีแผนงาน อัตรา ระดับขั้น และตัวเร่งยอด (accelerators) มีการเปลี่ยนแปลงทุกปี และบางครั้งก็เปลี่ยนเป็นรายบุคคล โมเดลที่ฝังอัตราไว้ในสูตรจะต้องถูกเขียนขึ้นใหม่ทั้งหมดแทนที่จะเป็นเพียงการกำหนดค่าใหม่

ความล่าช้าในการกระทบยอด ฝ่ายจ่ายเงินเดือนทำงานละเอียดถึงระดับเซนต์ โมเดลที่มีการปัดเศษระหว่างการคำนวณจะทำให้ตัวเลขคลาดเคลื่อนไปเล็กน้อยในหลายร้อยแถว และการค้นหาสาเหตุก็ใช้เวลานานกว่าการสร้างโมเดลตั้งแต่แรกเสียอีก

วิธีการแก้ปัญหาเฉพาะหน้าที่ผู้คนมักจะลองใช้

ทางเลือกที่ 1: แยกอัตราค่าคอมมิชชันออกจากสูตร

ใส่ระดับขั้นและอัตราค่าคอมมิชชันไว้ในตารางขนาดเล็ก จากนั้นจึงใช้การค้นหาอัตราแทนการเขียนค่าตายตัวลงไปในสูตร ฟังก์ชัน VLOOKUP ที่ตั้งค่า range lookup เป็น TRUE จะช่วยค้นหาระดับขั้นที่ค่านั้นตกอยู่ได้ โดยมีเงื่อนไขว่าตารางจะต้องเรียงลำดับจากน้อยไปหามาก

ฟังก์ชัน XLOOKUP ก็ทำหน้าที่แบบเดียวกันโดยมีโหมดการจับคู่ที่ชัดเจนสำหรับ "ตรงกันทุกประการหรือรายการที่เล็กกว่าถัดไป" ซึ่งทำให้อ่านเข้าใจง่ายกว่าเมื่อกลับมาดูในอีกหกเดือนข้างหน้า ในกรณีที่ตรรกะเป็นเพียงเงื่อนไขสั้นๆ ที่เชื่อมต่อกัน ฟังก์ชัน IFS จะช่วยให้อ่านง่ายกว่าการใช้สูตร IF ซ้อนกัน

นี่คือการเปลี่ยนแปลงที่มีมูลค่าสูงสุดเพียงอย่างเดียวที่คุณทำได้ เพราะแผนงานของปีถัดไปจะกลายเป็นการแก้ไขตารางแทนที่จะเป็นการเขียนสูตรใหม่ วิธีนี้ช่วยแก้ปัญหาสำหรับระดับขั้นแบบ flat tiers ได้อย่างสมบูรณ์แบบ แต่ไม่สามารถช่วยแก้ปัญหาสำหรับแบบ progressive tiers ได้เลย

ทางเลือกที่ 2: คำนวณระดับขั้นแบบก้าวหน้าอย่างถูกต้อง

สำหรับแผนงานแบบก้าวหน้า ค่าคอมมิชชันคือผลรวมของยอดเงินที่ตกอยู่ในแต่ละระดับขั้นคูณด้วยอัตราของขั้นนั้นๆ ตารางช่วยเหลือ (helper table) ที่มีหนึ่งแถวต่อหนึ่งระดับขั้น ซึ่งแสดงส่วนของดีลที่อยู่ภายในขั้นนั้น จะช่วยให้เห็นภาพและตรวจสอบได้ง่ายขึ้น

หากคุณต้องการให้คำนวณจบในเซลล์เดียว การใช้ฟังก์ชัน SUMPRODUCT กับเกณฑ์ระดับขั้นและส่วนต่างระหว่างอัตราที่ต่อเนื่องกันจะให้ผลลัพธ์แบบเดียวกัน ไม่ว่าคุณจะเลือกรูปแบบใดก็ตาม ควรเก็บตารางช่วยเหลือไว้ที่ใดที่หนึ่ง เพราะนั่นคือสิ่งที่คุณจะต้องนำไปแสดงให้ตัวแทนขายดูเมื่อพวกเขาไม่เห็นด้วยกับตัวเลข

ให้ใช้ฟังก์ชัน ROUND เพียงครั้งเดียวที่ตัวเลขการจ่ายเงินสุดท้าย และห้ามใช้ในระหว่างการคำนวณเด็ดขาด ข้อจำกัดของวิธีนี้คือการบำรุงรักษา เนื่องจากทุกครั้งที่มีการเปลี่ยนระดับขั้น จะส่งผลกระทบต่อโครงสร้างตารางช่วยเหลือรวมถึงตารางอัตราค่าคอมมิชชันด้วย

ทางเลือกที่ 3: จัดการกับการแบ่งดีล การจำกัดเพดาน และการเรียกคืนเงินในฐานะแถวบัญชีแยกประเภท

หลีกเลี่ยงการแก้ไขแถวดีลเดิมโดยตรง แต่ให้บันทึกทุกเหตุการณ์แยกเป็นแถวของตัวเองพร้อมระบุประเภท เช่น เครดิตเริ่มต้น, การจัดสรรส่วนแบ่ง, การปรับตัวเร่งยอด, การลดเพดานยอด หรือการเรียกคืนเงิน

การแบ่งดีล (splits) จะกลายเป็นแถวการจัดสรรสองแถวซึ่งผลรวมของเปอร์เซ็นต์จะต้องเท่ากับ 100% และการตรวจสอบผลรวมนี้จะช่วยดักจับข้อผิดพลาดที่พบบ่อยที่สุดได้ การคืนเงินจะกลายเป็นแถวที่มีค่าติดลบโดยลงวันที่ในงวดที่เกิดขึ้น ซึ่งจะช่วยให้รายงานของงวดก่อนหน้าไม่ได้รับผลกระทบ

วิธีนี้จะทำให้ได้โมเดลที่สามารถตรวจสอบได้ทีละบรรทัด ซึ่งเป็นหัวใจสำคัญของเรื่องนี้ แต่มันก็จะทำให้จำนวนแถวเพิ่มขึ้นถึงสี่เท่า และต้องอาศัยระเบียบวินัยที่ทุกคนที่แตะต้องไฟล์นี้ต้องปฏิบัติตาม คู่มือของเราเกี่ยวกับการเปลี่ยนข้อมูลส่งออกของ CRM ให้เป็นรายงานไปป์ไลน์ ได้ครอบคลุมถึงการเตรียมข้อมูลดีลที่จำเป็นสำหรับขั้นตอนนี้ไว้แล้ว

เพดานร่วมกัน ทั้งสามวิธีนี้ตั้งอยู่บนสมมติฐานที่ว่าแผนงานจะไม่มีการเปลี่ยนแปลงในระหว่างงวด แต่ในความเป็นจริง การเปลี่ยนแปลงระหว่างปี การรับประกันแบบครั้งเดียว และข้อยกเว้นรายบุคคลมักจะส่งมาทางอีเมล และแต่ละรายการก็เป็นการแก้ไขด้วยตนเองที่ไม่มีใครบันทึกไว้เป็นลายลักษณ์อักษร

วิธีคำนวณค่าคอมมิชชันการขายด้วย Powerdrill Bloom

ขั้นตอนที่ 1: อัปโหลดข้อมูลดีลและตารางอัตราค่าคอมมิชชันของคุณ

อัปโหลดข้อมูลส่งออกของดีลที่ปิดการขายได้และตารางอัตราค่าคอมมิชชันของแผนงานพร้อมกัน Powerdrill Bloom จะวิเคราะห์ข้อมูลทั้งสองส่วน ดังนั้น ปัญหาเรื่องผู้ดูแลที่หายไป ยอดเงินที่ว่างเปล่า และเปอร์เซ็นต์การแบ่งดีลที่ไม่รวมกันเป็น 100% จะถูกตรวจพบก่อนที่จะมีการคำนวณการจ่ายเงินใดๆ

ขั้นตอนที่ 2: อธิบายกฎของแผนงานด้วยภาษาธรรมชาติ

ระบุแผนงานแทนที่จะสร้างมันขึ้นมา เพียงบอกว่าระดับขั้นเป็นแบบก้าวหน้า ระบุช่วงระดับขั้นและอัตราค่าคอมมิชชัน พร้อมทั้งกำหนดเกณฑ์ตัวเร่งยอด (accelerator) และการจำกัดเพดานใดๆ

จากนั้นสั่งให้ตรวจสอบข้อมูลในคราวเดียวกัน ถามว่าดีลใดบ้างที่มีการแบ่งยอดรวมกันแล้วไม่เท่ากับ 100% และตัวแทนขายคนใดที่ทำยอดทะลุเกณฑ์ตัวเร่งยอดระหว่างงวด จากนั้นถามว่าการคืนเงินรายการใดที่ตกอยู่ในงวดที่ต่างจากดีลเริ่มต้น

ขั้นตอนที่ 3: ส่งออกแผนภูมิ รายงาน หรือสไลด์นำเสนอ

ดึงรายงานสรุปรายบุคคลที่แสดงที่มาที่ไปตั้งแต่ดีลไปจนถึงการจ่ายเงิน แผนภูมิแสดงผลงานเปรียบเทียบกับโควตา หรือรายงานสรุปสำหรับฝ่ายการเงิน

ทำไมวิธีนี้จึงดีกว่าการสร้างโมเดลใหม่ในทุกไตรมาส

	วิธีการทำด้วยตนเอง	Powerdrill Bloom
อัตราของปีแผนงานใหม่	แก้ไขตาราง จากนั้นตรวจสอบสูตรอีกครั้ง	ระบุช่วงระดับขั้นและอัตราใหม่
ระดับขั้นแบบก้าวหน้าเทียบกับแบบคงที่	สร้างโครงสร้างตารางช่วยเหลือใหม่	ระบุว่าแผนงานใช้รูปแบบใด
เปอร์เซ็นต์การแบ่งดีลที่ไม่รวมกันเป็น 100%	สร้างคอลัมน์ตรวจสอบด้วยตนเอง	ถามว่าดีลใดที่ไม่ผ่านการตรวจสอบ
การอธิบายตัวเลขให้ตัวแทนขายฟัง	ย้อนรอยที่มาของสูตรใหม่ทั้งหมด	ขอรายละเอียดการคำนวณตั้งแต่ดีลไปจนถึงการจ่ายเงิน

แถวสุดท้ายคือแถวที่ช่วยประหยัดเวลาได้อย่างแท้จริง ความพยายามส่วนใหญ่ในงานค่าคอมมิชชันไม่ใช่การคำนวณ แต่เป็นการอธิบาย และการอธิบายคือสิ่งที่สูตรที่ซ้อนกันหลายชั้นทำให้เป็นไปไม่ได้

ข้อผิดพลาดที่พบบ่อย

การใช้อัตราเดียวกับยอดทั้งหมดในแผนงานแบบก้าวหน้า นี่คือข้อผิดพลาดที่มีมูลค่าความเสียหายสูงที่สุดในกลุ่มนี้ และมักจะส่งผลให้เกิดการจ่ายเงินเกินหรือจ่ายเงินขาดแก่กลุ่มพนักงานที่ทำผลงานได้ดีที่สุดอย่างรุนแรงที่สุดเสมอ

การเขียนอัตราค่าคอมมิชชันตายตัวลงไปในสูตร วิธีนี้จะใช้ได้ผลเพียงปีเดียว และจะทำให้การเปลี่ยนแผนงานในปีถัดไปกลายเป็นการเขียนสูตรใหม่ทั้งหมด ควรเก็บอัตราค่าคอมมิชชันไว้ในตารางที่คุณสามารถส่งต่อให้ฝ่ายการเงินได้

การปัดเศษในทุกขั้นตอน ให้ปัดเศษเพียงครั้งเดียวตอนจ่ายเงิน การปัดเศษระหว่างทางจะทำให้เกิดความคลาดเคลื่อนของตัวเลขซึ่งจะไม่ตรงกับยอดของฝ่ายจ่ายเงินเดือน

การแก้ไขแถวเดิมเมื่อมีการคืนเงิน วิธีนี้จะทำให้รายงานของงวดก่อนหน้าที่ได้รับการอนุมัติไปแล้วเกิดความเสียหาย ให้เพิ่มแถวที่มีค่าติดลบโดยลงวันที่ในงวดที่เกิดการคืนเงินแทน

การลืมว่าเปอร์เซ็นต์การแบ่งดีลจะต้องรวมกันได้ 100% การจัดสรรยอด 60% สองรายการจะทำให้มีการจ่ายค่าคอมมิชชันออกไปถึง 120% ซึ่งดูปกติอย่างมากในแผ่นงานหากไม่มีการตรวจสอบ

การเก็บกฎเกณฑ์ของแผนงานไว้ในอีเมลเท่านั้น โมเดลค่าคอมมิชชันการขายที่กฎเกณฑ์ต่างๆ อยู่ในเธรดอีเมลจะไม่สามารถตรวจสอบหรือส่งมอบงานต่อได้ ให้เขียนกฎเหล่านั้นลงในเวิร์กบุ๊กโดยตรง

การปะปนคำจำกัดความของงวดเวลา วันที่ปิดดีล วันที่ในใบแจ้งหนี้ และวันที่ได้รับชำระเงิน จะให้ผลลัพธ์ที่แตกต่างกันสามแบบ ให้เลือกแบบใดแบบหนึ่ง บันทึกไว้ และนำไปใช้กับทุกแถว ซึ่งเป็นระเบียบวินัยแบบเดียวกับการทำรายงานเปรียบเทียบงบประมาณกับค่าใช้จ่ายจริง

บทสรุป

ตัดสินใจว่าแผนงานเป็นแบบก้าวหน้าหรือแบบคงที่ ย้ายอัตราค่าคอมมิชชันไปไว้ในตาราง คำนวณระดับขั้นอย่างชัดเจน และบันทึกการแบ่งดีล การจำกัดเพดาน และการเรียกคืนเงินแยกเป็นแถวต่างหาก โครงสร้างดังกล่าวจะช่วยให้โมเดลรองรับการตรวจสอบและการเปลี่ยนแปลงแผนงานได้ โมเดลค่าคอมมิชชันการขายจะถูกตัดสินจากความสามารถในการทำความเข้าใจของผู้อื่นที่มาดูต่อ

สิ่งที่ทำให้มีต้นทุนสูงคือการต้องสร้างโมเดลใหม่ทุกครั้งที่แผนงานเปลี่ยนไป รวมถึงการต้องมาคอยอธิบายในภายหลัง หากนั่นคือสิ่งที่คุณต้องเสียเวลาไปในแต่ละไตรมาส ลองใช้ Powerdrill Bloom กับข้อมูลส่งออกของดีลและตารางอัตราค่าคอมมิชชันของคุณ นอกจากนี้ โปรดดูคู่มือของเราเกี่ยวกับการคำนวณต้นทุนการได้มาซึ่งลูกค้า (customer acquisition cost) จากสเปรดชีต รวมถึงหน้า Excel AI assistant และ AI financial analysis ของเรา

คำถามที่พบบ่อย

ระดับขั้นค่าคอมมิชชันการขายแบบคงที่ (flat) และแบบก้าวหน้า (progressive) แตกต่างกันอย่างไร?

ระดับขั้นแบบคงที่ (flat) จะใช้อัตราเดียวกับยอดทั้งหมดเมื่อยอดถึงเกณฑ์ขั้นนั้นๆ ส่วนระดับขั้นแบบก้าวหน้า (progressive) จะใช้อัตราของแต่ละขั้นกับเฉพาะยอดเงินส่วนที่อยู่ในขั้นนั้นๆ เท่านั้น เช่นเดียวกับฐานภาษีเงินได้

ฉันจะค้นหาอัตราค่าคอมมิชชันโดยไม่ใช้สูตร IF ซ้อนกันได้อย่างไร?

ใส่ระดับขั้นและอัตราค่าคอมมิชชันไว้ในตารางที่จัดเรียงลำดับแล้ว จากนั้นใช้ VLOOKUP แบบจับคู่ใกล้เคียง (approximate matching) หรือใช้ XLOOKUP ที่ตั้งค่าเป็นตรงกันทุกประการหรือรายการที่เล็กกว่าถัดไป ทั้งสองวิธีนี้ช่วยให้คุณเปลี่ยนอัตราค่าคอมมิชชันได้โดยไม่ต้องแก้ไขสูตรเลย

ควรจัดการกับดีลที่มีการแชร์กันอย่างไร?

บันทึกแถวการจัดสรรหนึ่งแถวต่อตัวแทนขายหนึ่งคนพร้อมระบุเปอร์เซ็นต์ที่ชัดเจน และเพิ่มการตรวจสอบว่าผลรวมของเปอร์เซ็นต์เท่ากับ 100% การแก้ไขแถวดีลเดิมโดยตรงจะทำให้ไม่สามารถตรวจสอบการแบ่งยอดได้

การเรียกคืนเงินและการคืนเงินควรบันทึกไว้ที่ใด?

บันทึกในงวดเวลาที่เกิดการคืนเงิน โดยบันทึกเป็นแถวที่มีค่าติดลบและอ้างอิงถึงดีลเริ่มต้น การแก้ไขแถวเดิมย้อนหลังจะทำให้รายงานที่ได้รับการอนุมัติและจ่ายเงินไปแล้วเกิดการเปลี่ยนแปลง

ควรปัดเศษตัวเลขเมื่อใด?

ปัดเศษเพียงครั้งเดียวที่ยอดชำระเงินสุดท้าย การปัดเศษในขั้นตอนระหว่างทางจะทำให้เกิดความคลาดเคลื่อนสะสมในหลายๆ แถว ซึ่งมักจะเป็นสาเหตุหลักที่ทำให้โมเดลค่าคอมมิชชันไม่ตรงกับยอดของฝ่ายจ่ายเงินเดือน