ช่วงนี้ผมทำรีพอร์ตตัวนึงครับ เป็นเคสที่น่าจะโหดสุดเท่าที่เคยทำมา 😱

รีพอร์ตที่ว่าคือ

ให้ประมาณการ (Forecast) ส่วนลดพิเศษ (Rebate) ที่คาดว่าจะได้ เพื่อนำไปลงบัญชีตามหลักเกณฑ์คงค้าง (Accrual Basis) ของละเดือน

ฟังเผิน ๆ เหมือนจะง่าย แต่ปัญหามี 4 ข้อ
1. เงื่อนไขของส่วนลดพิเศษ
2. จำนวนคู่ค้าที่มีส่วนลดพิเศษ
3. ความถี่ในการจ่ายส่วนลดพิเศษ
4. สมมติฐานที่อาจเปลี่ยนแปลง

<< 1. เงื่อนไขของส่วนลดพิเศษ >>

มาดูเงื่อนไขของส่วนลดพิเศษกัน แบ่งได้เป็น 5 กรณี

[1. ส่วนลดพิเศษแบบเรตคงที่]

แบบนี้คือง่ายสุด
เช่น ส่วนลดพิเศษ 1% ของยอดซื้อทั้งหมด
ซื้อน้อยซื้อมากก็ได้ส่วนลดพิเศษเรตนั้น

[2. ส่วนลดพิเศษแบบตั้งเป้า คิดเรตคงที่]

แบบนี้ต้องตั้งเป้าร่วมกันก่อน เช่น ถ้าซื้อเกินหนึ่งล้านบาท ได้ส่วนลดพิเศษ 2% ของยอดซื้อทั้งหมด

แปลว่า ถ้าซื้อไม่ถึงเป้า ก็ไม่ได้ส่วนลดพิเศษเลย

[3. ส่วนลดพิเศษแบบตั้งเป้า คิดจำนวนเงินคงที่]

แบบนี้ก็ต้องตั้งเป้าร่วมกัน และถ้าถึงเป้าแล้ว จะได้ส่วนลดพิเศษเป็นจำนวนเงินคงที่

เช่น ถ้าซื้อเกินหนึ่งล้านบาท ได้ส่วนลดพิเศษ 50,000 บาท

แปลว่า ถ้าซื้อสองล้านบาท ก็ได้ส่วนลดพิเศษ 50,000 บาท

หรือพูดง่าย ๆ คือ ส่วนลดพิเศษไม่ได้เพิ่มตามยอดซื้อ (แต่ยอดซื้อต้องถึงเป้า)

[4. ส่วนลดพิเศษแบบตั้งเป้า คิดเรตแปรผัน]

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

เช่น ถ้าซื้อไม่ถึงหนึ่งล้านบาท ได้ส่วนลดพิเศษ 0.5%
ถ้าซื้อหนึ่งล้านแต่ไม่ถึงสองล้านบาท ได้ส่วนลดพิเศษ 1% ของยอดซื้อทั้งหมด
ถ้าซื้อมากกว่าสองล้านบาท ได้ส่วนลดพิเศษ 2% ของยอดซื้อทั้งหมด

[5. ส่วนลดพิเศษแบบตั้งเป้า คิดเรตตามขั้นบันได]

แบบนี้ก็ต้องตั้งเป้าร่วมกันก่อน แต่เป้าจะเป็นแบบขั้นบันได แต่ละขั้นจะมีเรตไม่เท่ากัน ให้คำนวณเฉพาะส่วนที่เกินจากเป้าของขั้นบันไดนั้น ๆ
(หลักการคล้ายคำนวณภาษีเงินได้บุคคลธรรมดา)

เช่น ถ้าซื้อไม่ถึงหนึ่งล้านบาท ได้ส่วนลดพิเศษ 0.5%
ถ้าซื้อมากกว่าหนึ่งล้านแต่ไม่ถึงสองล้านบาท ส่วนที่เกินหนึ่งล้านจะได้ส่วนลดพิเศษ 1%
ถ้าซื้อมากกว่าสองล้านแต่ไม่ถึงสามล้านบาท ส่วนที่เกินสองล้านจะได้ส่วนลดพิเศษ 2%
ถ้าซื้อสามล้านขึ้นไป ส่วนที่เกินสามล้านได้ส่วนลด 3%

ใช่, แบบนี้คำนวณยากที่สุด 😭

<< 2. จำนวนคู่ค้าที่มีส่วนลดพิเศษ >>

มาดูเรื่องจำนวนคู่ค้าที่มีส่วนลดพิเศษกันบ้าง

ความโหดของเคสนี้คือ มีคู่ค้าที่มีส่วนลดพิเศษถึง 200 เจ้า!

บางเจ้าก็มีส่วนลดพิเศษแบบตั้งเป้า บางเจ้าก็ไม่ตั้งเป้า
และบางเจ้าก็ตั้งเป้าแบบขั้นบันได

แถมเป้าและเรตของแต่ละเจ้าก็ไม่เท่ากัน

<< 3. ความถี่ในการจ่ายส่วนลดพิเศษ >>

มาดูเรื่องความถี่ในการจ่ายส่วนลดพิเศษกันบ้าง

การจ่ายส่วนลดพิเศษมีความถี่ 3 ระดับคือ
1. จ่ายทุก 3 เดือน
2. จ่ายทุก 6 เดือน
3. จ่ายทุก 12 เดือน

ปัญหาคือ แต่ละเจ้า (200 เจ้า) มีความถี่ในการจ่ายไม่เหมือนกัน
บางเจ้าจ่ายทุก 3 เดือน และจ่ายเพิ่มพิเศษให้ทุก 12 เดือน (ถ้าถึงเป้า)

แต่ปัญหาคือ การประมาณการครั้งนี้ (Forecast) ต้องคิดเสมือนว่ามีการจ่ายส่วนลดพิเศษทุกเดือน เพื่อนำไปบันทึกบัญชีตามหลักเกณฑ์คงค้าง!

แปลว่า ถ้าคู่ค้าจ่ายทุก 3 เดือน ต้องประมาณการว่าจ่ายทุกเดือน

แต่ละเดือนไม่ใช่เอาไปหาร 3
แต่ต้องคูณด้วยอัตราส่วนของยอดซื้อเดือนนั้นหารด้วยยอดซื้อทั้งไตรมาส

เช่น ถ้าเดือนหนึ่งซื้อหนึ่งแสน เดือนสองซื้อสองแสน เดือนสามซื้อหนึ่งแสน
ยอดซื้อของทั้งไตรมาสคือสี่แสน

อัตราส่วนของเดือนหนึ่งคือ 0.25 (หนึ่งแสนหารสี่แสน)
อัตราส่วนของเดือนสองคือ 0.5 (สองแสนหารสี่แสน)
อัตราส่วนของเดือนสามคือ 0.25 (หนึ่งแสนหารสี่แสน)

เหตุผลที่ทำแบบนี้ เพื่อไม่ให้ส่วนลดพิเศษปูดในเดือนในเดือนหนึ่ง
(ส่วนใหญ่มักจะปูดเดือนปิดไตรมาส ซึ่งหลักการบัญชีจะไม่ยอมให้เป็นแบบนั้น)

<< 4. สมมติฐานที่อาจเปลี่ยนแปลง >>

มาดูเรื่องสมมติฐานที่อาจเปลี่ยนแปลงกันบ้าง

ใช่, เนื่องจากเป็นการประมาณการ (Forecast) ทุกอย่างจึงต้องเป็นการคำนวณโดยอาศัยสมมติฐาน (Assumptions)

นั่นแปลว่า ถ้าสมมติฐานเปลี่ยน ตัวเลขประมาณการทั้งหมดก็ต้องเปลี่ยน และต้องคำนวณใหม่ได้ทันที
(หรือรอคำนวณได้ไม่เกิน 3 นาที)

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

แปลว่าต้องสร้างโมเดลการคำนวณ (Forecasting Model) ที่สามารถปรับสมมติฐาน แล้วดูผลลัพธ์ได้ทันที

ใช่, ไม่ง่ายเลย..

ผมงัดความรู้ทุกอย่างใน Excel ออกมาใช้แทบทั้งหมด ไม่ว่าจะเป็นฟังก์ชัน, Table, Power Query หรือ Pivot Table

คีย์สำคัญอยู่ที่การออกแบบตารางเงื่อนไขส่วนลดพิเศษ ถ้าออกแบบตารางดี งานก็เบาไปเยอะ

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

การคำนวณส่วนลดพิเศษแบบขั้นบันได ต้องใช้ LOOKUP แบบ approximate match
และต้องใช้ฟังก์ชัน FILTER มาช่วย
ถ้าไม่ใช้ฟังก์ชัน FILTER ไม่รู้ว่าจะดึงเงื่อนไขขั้นบันไดของคู่ค้านั้น ๆ ได้อย่างไร

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

พอตรวจสอบว่าได้ ก็ต้องกระจายส่วนลดพิเศษ (เช่น ส่วนลดพิเศษในรอบไตรมาส) เข้าไปในแต่ละเดือน
(ตามอัตราส่วนยอดซื้อของเดือนนั้น ๆ )

ตารางคำนวณทั้งหมด สร้างด้วย Power Query เพราะสามารถ Group By, Merge Queries และ Append Queries ได้สะดวก

ผลลัพธ์จาก Power Query คือตารางในรูปแบบฐานข้อมูล (Database, Flat Table)
เหตุผลที่ทำเป็นรูปแบบฐานข้อมูล เพื่อนำไปสรุปเป็น Pivot Table ได้สะดวก

ผมใช้เวลาร่วมสองวันในการทำ
บอกเลย เหนื่อยมาก!
(แก้สูตรไม่รู้กี่รอบ 😭)

ผลลัพธ์ที่ได้ค่อนข้างน่าพอใจ
ติดขัดตรงใช้เวลารีเฟรช Power Query ประมาณหนึ่งนาที
แต่เป็นตัวเลขที่พอยอมรับได้

น่าเสียดายที่ข้อมูลเป็นความลับ ไม่อย่างนั้นอาจนำไฟล์มาให้ดู 🙂

ความท้าทายต่อไปก็คือ เมื่อการซื้อขายเกิดขึ้น มีโอกาสที่ต้องกระจายส่วนลดพิเศษเข้าไปในการซื้อขายครั้งนั้น ๆ เพื่อวิเคราะห์ผลกำไรที่แท้จริง
(หลักการคล้าย SAP COPA (Controlling Profitability Analysis))

ความเศร้าก็คือ สินค้าที่เข้าข่ายส่วนลดพิเศษมีมากกว่า 1,000 SKU
และสินค้าบางตัวก็ไม่เข้าข่ายส่วนลดพิเศษ
แถมสินค้าทั้งหมดมีมากกว่า 10,000 SKU!

ถ้าจะเอาจริง จ้างบริษัทที่ปรึกษาละกันนะ ผมไม่ไหวแล้ว 😅