Sales Forecast…ทำนายข้อมูลยอดขายด้วย Excel ง่ายๆ (Part 1)
เชื่อว่าหลายๆ คนคงจะอยากสร้างการทำนายตัวเลข หรือข้อมูลต่างๆ ไม่ว่าจะเป็นความต้องการสิ้นค้า (Demand) ในแต่ละช่วงเวลา , ยอดขายสินค้า รวมไปถึงการ ทำนายยอดผู้ป่วย COVID-19 ที่ช่วงนี้เรามักจะเห็นกันเป็นประจำ
โดยวันนี้เราจะมาลองทำ Single Exponential Smoothing (SES) เพื่อทำนายยอดขายสินค้าจากข้อมูลเพียงข้อมูลเดียว ซึ่งก็คือยอดขายที่ผ่านๆ มากันครับ (บอกก่อนว่าใน Part 1 นี้จะเป็นการเล่าพื้นฐานคร่าวๆ ให้เห็นภาพก่อน)
ซึ่งจริงๆ แล้วการทำนายตัวเลขแบบนี้ สามารถทำได้หลายวิธี ซึ่งถ้าจะให้ผมเขียนทั้งหมดก็คงจะเยอะเกินไป เอาเป็นว่าใครสนใจที่อยากจะรู้ลองอ่านจาก link ด้านล่างดูก็ได้ครับ
https://sites.google.com/site/kmaths55/mba/operations-management/forecasting/expo.jpg?attredirects=0
มาเริ่มกันเลย
ผมจะเริ่มจากการสร้างชุดข้อมูลขึ้นมาใหม่ โดยให้คอลัมน์ A เป็นคอมลัมน์ Time ซึ่งก็คือระยะเวลา (ข้อมูลจะมีทั้งหมด 24 ข้อมูล คือ 1 ถึง 24) ส่วนคอลัมน์ B จะเป็นคอลัมน์ Sales ซึ่งคือยอดขาย โดยจะทำการสุ่มตัวเลขระหว่าง 50–100
=RANDBETWEEN(50,100)
แล้วลากหรือ double click ตรงจุดมุมขวาล่าง เพื่อสร้างตัวเลขจำลองทั้งหมด 24 ตัวเลข
เมื่อเราได้ข้อมูลจำลองมาครบแล้ว ก็มาลอง Visualize ข้อมูลดู
กดแถบ Insert แล้วเลิอกกราฟที่เราต้องการแสดงผล (ผมเลือก line chart)
ก็จะได้เห็นทิศทางของยอดจายที่เราสร้างขึ้น (ข้อมูลดู sideway เหมือนกันนะเนี่ย!!!)
หลังจากนั้นจะทำตามขั้นตอนตามรูปด้านล่างต่อเลย…
1. Copy ข้อมูลมาวางใน Sheet ใหม่
2. ผมเปลี่ยนชื่อ Sheet ให้เป็น “ES_DA” (จริงจัตั้งว่าอะไรก็ได้)
3. กดไปที่แถบ Data
4. กดที่ Data Analysis (ใครเจอหรือยังไม่มี เลื่อนไปดูวิธีในรูปถัดไปได้เลยครับ)
5.เลือก Exponential Smoothing
สำหรับใครที่ยังไม่มีปุ่ม Data Analysis ทำตามขั้นตอนนี้ครับ
คลิกแถบ File >> Options ก็จะเจอกับหน้าต่างแบบรูปด้านล่างโดยตรงช่อง Manage: ให้เลือก Excel Add-ins แล้วกด Go…
ก็จะมีหน้าต่างเล็กๆ โผล่ขึ้นมาแบบภาพด้านล่าง
ให้เลือกตามรูปได้เลยครับ เพราะในบทความชุดนี้เราจะใช้ 2 ตัวเลือกในภาพ
ก็จะเห็นแถบ Solver และ Data Analysis แบบนี้เลย
กลับมาที่ขั้นตอนของเราต่อ!!!
จากขั้นตอนก่อนหน้า 5 ขั้นตอนที่เราแนะนำไปแล้วก่อนหน้า
เมื่อกด Data Analysis แล้วก็เจอหน้าต่างเล็กๆ โผล่ขึ้นมา
Input Range: ให้เลือกข้อมูลที่เราต้องการจะนำมาสร้างโมเดลการทำนาย โดยในตัวอย่างนี้จะเลือกข้อมูลทั้ง 24 ข้อมูลในคอลัมน์ BDamping factor: สามารถเลือกค่าได้ระหว่าง 0 ถึง 1 โดยผมเลือกใส่ 0.5 ดูก่อน(รายละเอียดขอไปอธิบายต่อ Part2)Output Range: เราจะให้วางข้อมูลผลลัพธ์ไว้ตรงไหน (ผมเลือกวางโดยเริ่มจาก C2)เลือก Chart Output เพื่อให้แสดงกราฟออกมาด้วยเสร็จแล้วกด OK
ผลที่ได้ออกมาก็จะเป็นแบบนี้!!!
โดยจะมีตัวเลขจากการทำนายปรากฎขึ้นในคอลัมน์ C และมีกราฟที่แสดงผลเปรียบเทียบข้อมูลจริงกับข้อมูลที่ทำนายออกมา
ทีนี้ลองมาเปลี่ยนค่า Damping factor ดูซักหน่อย (จาก 0.5 เป็น 0.9)
ผลที่ได้ดูเหมือนว่าค่า Forecast จะเรียบๆ แบบแปลกๆ
แล้วถ้าลองเปลี่ยนเป็น 0.1 ล่ะ???
ดูล้อไปเหมือนข้อมูล Sales ที่เราสร้างขึ้นเหมือนกันนะเนี่ย (ดูแกว่งมากกว่า 0.5 ด้วย)
แต่เราจะรู้ได้ยังไงว่าข้อมูลที่ Forecast มานั้น ดีพอรึยัง???
ลองมาวิเคราะห์ดูว่า วิธีไหนมาค่า Error ที่น้อยกว่ากัน โดยจะลองยกตัวอย่างการทำให้ดูตามด้านล่างครับ
ตั้งชื่อคอลัมน์ C ให้มีชื่อว่า Forecastคอลัมน์ D ให้สร้างข้อมูลที่มีชื่อว่า Forecast Error โดยเอาข้อมูลของ Sales - Forecast (ตัวอย่างเช่น ช่อง D3 คิดจาก B3 - C3 (79-13) ทำให้ได้ผลลัพธ์ที่ 13 เป็นต้นคอลัมน์ E สร้างข้อมูลที่มีชื่อว่า Squared Error โดย เอา Forecast Error มายกกำลัง 2 ยกตัวอย่างเช่น ในช่อง E3 ให้พิมพ์ D3^2 ซึ่งจะทำให้ได้ค่า 169 เป็นต้นหลังจากนั้นในคอลัมน์ G เราจะทำการคำนวณ Sum Squared Error โดยจะนำข้อมูลใน Squared Error ทั้งหมดมารวมกัน
สามารถลองหา Sum Squared Error ที่ได้จากผลการทำนายของค่า Damping factor ค่าต่างๆ มาเทียบกันดูได้
โดยยิ่ง Sum Squared Error ยิ่งน้อยก็ยิ่งดี
เพราะนั่นเท่ากันว่าค่าความผิดพลาดหรือ Error ที่ได้จากผลต่างของข้อมูลจริง กับการ Forecast ก็ยิ่งน้อยลงไปด้วยในภาพรวม
Key Takeaways
- เราสามารถสร้างการทำนายยอดขาย ความต้องการ การติดโรคติดต่อ ฯลฯ ได้ เพียงแค่มีตัวแปรเพียงแค่ตัวเดียว (แต่จะแม่นหรือไม่แม่นเมื่อเทียบกับการทำนายแบบที่มีหลายๆ ตัวแปร นั่นอีกเรื่องนะ) ได้ด้วย Single Exponential Smoothing (SES)
- ใน Excel มี option ที่ชื่อว่า Data Analysis และ Solver ที่สามารถช่วยในการทำนายได้
- เราได้ลองสร้างการทำนายหลายๆ ชุด
- และพบว่าการปรับค่า Damping factor ที่แตกต่างกับไป ก็ทำให้ผลการทำนายนั้นแตกต่างกันไปด้วย
- สุดท้ายจะดูว่าสมการไหนดีก็ต้องมาดูที่ค่า Sum Squared Error ที่ยิ่งน้อยก็ถือว่าการทำนายนั้นยิ่งดี
สำหรับ Part ที่ 1 แม้ว่าเราจะเพียงแค่ทำนายข้อมูลที่มีอยู่แล้วเท่านั้น โดยยังไม่ได้ลองสร้างการ Forecast ข้อมูลในอนาคตจริง แต่ผมอยากให้ทุกคนได้คุ้ยเคยกับค่าต่างๆ ก่อนเล็กน้อย เพื่อที่จะได้ไปลอง Forecast กันต่อใน Part 2 นั่นเองครับ
