สร้างโมเดลพยากรณ์ Sales Pipeline ใน Excel: คู่มือขั้นตอนละเอียด

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

ขั้นตอนที่ 1: จัดเตรียมข้อมูลใน Excel

เพื่อสร้างโมเดลพยากรณ์ ต้องมีข้อมูลขายที่มีโครงสร้าง Sales Pipeline ควรมีข้อมูลดังนี้:

• Deal ID: รหัสเฉพาะของแต่ละข้อตกลง
• Deal Value: รายได้ประมาณจากข้อตกลง
• Sales Stage: สถานะข้อตกลง (เช่น Prospecting, Negotiation, Proposal Sent, Closed Won, Closed Lost)
• Probability of Closing: โอกาสประสบความสำเร็จจากข้อมูลย้อนหลัง
• Expected Close Date: วันที่คาดว่าจะปิดข้อตกลง

คอลัมน์คำนวณ (Calculated Fields):

• Expected Revenue Based on Conversion Rate: รายได้คาดการณ์จากโอกาสและมูลค่าข้อตกลง
• Cumulative Weighted Revenue: ผลรวมสะสมของรายได้ถ่วงน้ำหนักตามเวลา
• Estimated Close Date Category: จัดกลุ่มข้อตกลงตามช่วงเวลา
• Win/Loss Status: แสดงว่าข้อตกลงชนะหรือแพ้

สามารถเพิ่มชื่อลูกค้า, เซลส์, อุตสาหกรรม, อัตราการแปลง, ระยะต่างๆ ได้อีกด้วย

ขั้นตอนที่ 2: คำนวณฟิลด์พยากรณ์

2.1 Expected Revenue:

• เลือกเซลล์ F2 ใส่สูตรต่อไปนี้
• ลากสูตรลงไปใช้กับเซลล์อื่นๆ

สูตร:

สูตรคูณ Deal Value กับ Probability เพื่อหารายได้ที่คาดการณ์

2.2 Cumulative Weighted Revenue:

• เลือกเซลล์ G2 ใส่สูตรต่อไปนี้
• ลากสูตรลงไป

สูตร:

สูตรนี้ทำให้แต่ละแถวรวมค่าก่อนหน้าทั้งหมด สร้างผลรวมสะสม

3.2 Estimated Close Date Category:

• เลือกเซลล์ H2 ใส่สูตรต่อไปนี้
• ลากสูตรลงไป

สูตร:

=IF(E2<=TODAY()+30, "Short-Term", IF(E2<=TODAY()+90, "Medium-Term", "Long-Term"))

สูตรตรวจสอบ Expected Close Date ว่าอยู่ในช่วงไหน:

• ≤ 30 วัน >> Short-Term
• 31-90 วัน >> Medium-Term
• > 90 วัน >> Long-Term

4.2 Win/Loss Status Calculation:

• เลือกเซลล์ I2 ใส่สูตรต่อไปนี้
• ลากสูตรลงไป

สูตร:

=IF(C2="Closed Won", "Won", IF(C2="Closed Lost", "Lost", "Open"))

สูตรตรวจสอบ Sales Stage เพื่อกำหนดสถานะ Win/Loss:

• “Won” ถ้าเป็น Closed Won
• “Lost” ถ้าเป็น Closed Lost
• อื่นๆ >> “Open”

ขั้นตอนที่ 3: ใช้ Pivot Table พยากรณ์รายได้ขาย

เพื่อพยากรณ์รายได้ต่อเดือน ใช้ Pivot Table:

• เลือกช่วงข้อมูล
• แท็บ Insert >> PivotTable
• เลือก New Worksheet >> OK

ใน Pivot Table Field List:

• ลาก Expected Close Date ไป Rows
• ลาก Expected Revenue ไป Values
• ลาก Win/Loss Status ไป Columns
• ลาก Sales Stage ไป Filters

ขั้นตอนที่ 4: วิเคราะห์ด้วยแผนภูมิ

• เลือกข้อมูล Pivot Table
• แท็บ PivotTable Analyze >> PivotChart

ผลลัพธ์:

การกระจายรายได้:

• Total Expected Revenue: $30,568
• Lost: $9,688
• Open: $10,480
• Won: $10,400

ข้อมูลสำคัญ (Key Insights):

• เดือนกุมภาพันธ์มี Won Revenue สูงสุด ($10,400)
• มีนาคมและเมษายนมีข้อตกลง Open เยอะ แสดงว่าจะปิดได้ในอนาคต
• มิถุนายนและกรกฎาคมรายได้ต่ำ แสดงว่ามีช่องว่างใน Pipeline
• แผนภูมิแยก Lost, Open, Won ชัดเจน

ขั้นตอนที่ 5: วิเคราะห์แนวโน้มและปรับปรุง

คุณสามารถวิเคราะห์ความแม่นยำ ติดตาม KPI และปรับตามข้อมูลจริง

KPI ที่ควรติดตาม:

• Pipeline Bottlenecks: วิเคราะห์ Expected Close Date และ Win/Loss Status เพื่อหาข้อตกลงที่เคลื่อนไหวช้า ข้อตกลงที่ค้าง Open เกิน 60 วันอาจบ่งชี้ปัญหา
• Conversion Rate Trends: ติดตาม % ข้อตกลงที่จาก Open กลายเป็น Closed Won ตามเวลา เปรียบเทียบ Win Rate ตาม Sales Stage เพื่อหาจุดอ่อน
• Revenue Forecast Accuracy: เปรียบเทียบ Expected Revenue กับ Actual Revenue จาก Won Deals หากรายได้จริงต่ำกว่าอยู่เสมอ อาจต้องปรับค่า Probability

เพื่อเพิ่มความแม่นยำ:

• อัปเดต Conversion Rate เป็นประจำจากข้อมูล Win/Loss ย้อนหลัง
• ใช้ Close Date Category (Short/Medium/Long-Term) เข้าใจแนวโน้มในแต่ละช่วงเวลา
• ติดตาม Win/Loss ratios ประเมินกลยุทธ์ขายและปรับ Pipeline ตามนั้น

สรุป

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

Get FREE Advanced Excel Exercises with Solutions!