รูปภาพโดยบรรณาธิการ | กลางการเดินทาง
การคาดการณ์ไปป์ไลน์การขายเป็นกระบวนการที่สำคัญสำหรับธุรกิจในการคาดการณ์รายได้ในอนาคตตามสถานะของข้อตกลงในช่องทางการขาย โมเดลนี้ช่วยในการคาดการณ์ วิเคราะห์ และติดตามยอดขายในอนาคตซึ่งช่วยในการมุ่งเน้นการขาย ในบทความนี้ เราจะแสดงวิธีสร้างแบบจำลองการคาดการณ์ไปป์ไลน์การขายใน Excel
ขั้นตอนที่ 1:เตรียมข้อมูลของคุณใน Excel
หากต้องการสร้างแบบจำลองการคาดการณ์ คุณต้องรับรองข้อมูลการขายที่มีโครงสร้าง ไปป์ไลน์การขายของคุณควรมีข้อมูลต่อไปนี้
- รหัสข้อตกลง :ตัวระบุที่ไม่ซ้ำสำหรับแต่ละดีล
- มูลค่าข้อตกลง :รายได้โดยประมาณจากข้อตกลง
- ขั้นตอนการขาย :สถานะของข้อตกลง (เช่น การสำรวจ การเจรจา ส่งข้อเสนอ ปิดชนะ ปิดแพ้)
- ความน่าจะเป็นของการปิดตลาด :โอกาสโดยประมาณที่จะบรรลุข้อตกลงโดยพิจารณาจากข้อมูลในอดีต
- วันที่ปิดที่คาดหวัง :วันที่คาดการณ์เมื่อข้อตกลงจะเสร็จสิ้น
ฟิลด์จากการคำนวณ:
- รายได้ที่คาดหวังขึ้นอยู่กับอัตรา Conversion :คาดการณ์รายได้โดยพิจารณาจากความน่าจะเป็นและมูลค่าข้อตกลง
- รายได้ถ่วงน้ำหนักสะสม :การดำเนินการรวมของรายได้ถ่วงน้ำหนักในช่วงเวลาหนึ่ง
- หมวดหมู่วันที่ปิดโดยประมาณ :การจัดหมวดหมู่ข้อตกลงตามกรอบเวลา
- สถานะชนะ/แพ้ :ระบุว่าข้อตกลงชนะหรือแพ้
คุณยังสามารถระบุชื่อลูกค้า ตัวแทนขาย อุตสาหกรรม อัตราคอนเวอร์ชัน ขั้นตอนต่างๆ ฯลฯ
ขั้นตอนที่ 2:คำนวณฟิลด์การคาดการณ์
2.1. รายได้ที่คาดหวัง:
- เลือกเซลล์ F2 และแทรกสูตรต่อไปนี้
- ลากสูตรลงเพื่อนำไปใช้กับเซลล์ที่เหลือ
สูตร:
สูตรนี้จะคูณมูลค่าข้อตกลงด้วยความน่าจะเป็นที่จะคืนรายได้ที่คาดหวัง
2.2. รายได้แบบถ่วงน้ำหนักสะสม:
- เลือกเซลล์ G2 และแทรกสูตรต่อไปนี้
- ลากสูตรลงเพื่อนำไปใช้กับเซลล์ที่เหลือ
สูตร:
สูตรนี้ช่วยให้แน่ใจว่าแต่ละแถวจะรวมค่าก่อนหน้าทั้งหมดเข้าด้วยกัน ทำให้เกิดผลรวมสะสม
3.2. หมวดหมู่วันที่ปิดโดยประมาณ:
- เลือกเซลล์ H2 และแทรกสูตรต่อไปนี้
- ลากสูตรลงเพื่อนำไปใช้กับเซลล์ที่เหลือ
สูตร:
=IF(E2<=TODAY()+30, "Short-Term", IF(E2<=TODAY()+90, "Medium-Term", "Long-Term"))
สูตรนี้จะตรวจสอบว่าวันที่ปิดที่คาดหวังอยู่ในช่วงต่อไปนี้หรือไม่
- 30 วัน>> ระยะสั้น
- 31-90 วัน>> ระยะกลาง
- มากกว่า 90 วัน>> ระยะยาว
4.2. การคำนวณสถานะชนะ/แพ้:
- เลือกเซลล์ I2 และแทรกสูตรต่อไปนี้
- ลากสูตรลงเพื่อนำไปใช้กับเซลล์ที่เหลือ
สูตร:
=IF(C2="Closed Won", "Won", IF(C2="Closed Lost", "Lost", "Open"))
สูตรนี้จะตรวจสอบขั้นตอนการขายเพื่อให้แน่ใจว่าสถานะชนะ/แพ้
- “ชนะ” หากข้อตกลงปิดชนะแล้ว
- “แพ้” หากดีลถูกปิด แพ้
- สถานะอื่นจะเป็น "เปิด"
ขั้นตอนที่ 3 แทรกตาราง Pivot เพื่อคาดการณ์รายได้จากการขาย
หากต้องการคาดการณ์รายได้ต่อเดือน คุณสามารถใช้ Pivot Table ได้
- เลือกช่วงข้อมูลของคุณ
- ไปที่ แทรก แท็บ>> เลือก PivotTable .
- เลือก แผ่นงานใหม่>> คลิก ตกลง
ในรายการฟิลด์ Pivot Table :
- ลาก วันที่ปิดที่คาดหวัง ในแถว ส่วน.
- ลากรายได้ที่คาดหวัง ในค่า ส่วน.
- ลาก สถานะชนะ/แพ้ ในคอลัมน์ ส่วน.
- ลากขั้นตอนการขาย ในตัวกรอง ส่วน.
ขั้นตอนที่ 4:แสดงภาพการพยากรณ์
- เลือกข้อมูลตารางสรุป
- ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก PivotChart .
เอาต์พุต:
การกระจายรายได้:
- รายได้ที่คาดหวังทั้งหมด: $30,568
- แพ้: $9,688
- เปิด: $10,480
- ชนะ: $10,400
ข้อมูลเชิงลึกที่สำคัญ:
- เดือนกุมภาพันธ์มีรายได้ที่วอนสูงสุด ($10,400)
- เดือนมีนาคมและเมษายนมีข้อเสนอที่เปิดอยู่มากมาย ซึ่งบ่งชี้ถึงการปิดในอนาคต
- เดือนมิถุนายนและกรกฎาคมมีรายได้ต่ำ ซึ่งส่งสัญญาณถึงช่องว่างไปป์ไลน์
- แผนภูมิแยกข้อเสนอที่แพ้ เปิด และชนะ
ขั้นตอนที่ 5:วิเคราะห์แนวโน้มและทำการปรับเปลี่ยน
คุณสามารถวิเคราะห์ความแม่นยำของการคาดการณ์ไปป์ไลน์การขาย ติดตามตัวชี้วัดหลัก และปรับเปลี่ยนตามข้อมูลเชิงลึกที่แท้จริง
ข้อมูลเชิงลึกที่สำคัญที่ต้องติดตาม:
- ปัญหาคอขวดของท่อ : วิเคราะห์วันที่ปิดที่คาดหวังและสถานะการชนะ/ขาดทุนเพื่อระบุข้อตกลงที่เคลื่อนไหวช้า ข้อเสนอที่ยังคงเปิดอยู่เกิน 60 วันอาจบ่งบอกถึงการชะลอตัว
- แนวโน้มอัตราการแปลง :ตรวจสอบเปอร์เซ็นต์ของข้อตกลงที่เปลี่ยนจากเปิดเป็นปิดชนะ เมื่อเวลาผ่านไป เปรียบเทียบอัตราการชนะในช่วงการขายต่างๆ เพื่อระบุจุดอ่อน
- ความแม่นยำในการพยากรณ์รายได้ :เปรียบเทียบรายได้ที่คาดหวังกับรายได้จริงจากข้อตกลงที่ชนะ หากรายได้จริงลดลงอย่างสม่ำเสมอ การประมาณความน่าจะเป็นอาจต้องมีการปรับเปลี่ยน
เพื่อปรับปรุงความแม่นยำในการพยากรณ์:
- คุณสามารถอัปเดตอัตรา Conversion เป็นประจำตามข้อมูลการชนะ/แพ้ในอดีต เพื่อปรับแต่งการคาดการณ์รายได้
- คุณสามารถใช้หมวดหมู่วันที่ปิด (ระยะสั้น กลาง ระยะยาว) เพื่อทำความเข้าใจแนวโน้มในกรอบเวลาที่แตกต่างกัน
- ติดตามอัตราส่วนการชนะ/แพ้อย่างใกล้ชิดเพื่อประเมินประสิทธิผลของกลยุทธ์การขายและปรับไปป์ไลน์ให้เหมาะสม
บทสรุป
ด้วยการทำตามขั้นตอนข้างต้น คุณสามารถสร้างแบบจำลองการคาดการณ์ไปป์ไลน์การขายที่ใช้งานได้ใน Excel เพื่อประเมินยอดขายในอนาคตตามข้อมูลไปป์ไลน์ ความน่าจะเป็นของการแปลง และมูลค่าข้อตกลง แบบจำลองนี้จะช่วยคุณคาดการณ์มูลค่าในอนาคตและระบุประสิทธิภาพของไปป์ไลน์เพื่อปรับกลยุทธ์การขายให้เหมาะสม ด้วยการอัปเดตและการปรับปรุงอย่างสม่ำเสมอ เครื่องมือนี้สามารถเป็นเครื่องมือที่มีประสิทธิภาพสำหรับการตัดสินใจในองค์กรที่ขับเคลื่อนด้วยการขาย คุณสามารถใช้การปรับแต่งเพิ่มเติมในโมเดลได้ตามความต้องการเฉพาะของคุณ
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!