Sales forecast แต ม นเป นต วเลขท แน นอนแล ว

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

เรามาเริ่มจากหลักการว่า Excel มันพยากรณ์ค่าด้วยวิธีไหนกันดีกว่า? ซึ่งเราจะเริ่มจากหลักการก่อน ให้คุณมีความเข้าใจอย่างถ่องแท้ แล้วค่อยไปดูวิธีแบบสั้นๆ ง่ายๆ ทีหลังนะ

สารบัญ

การ Forecast ตัวเลขแบบ Model เส้นตรง

สมมติว่าผมมีข้อมูลยอดขายจริงตามรูป ถ้าจะให้เดายอดขายในอนาคตว่าเป็นเท่าไหร่? เช่น อยากรู้ว่าในเดือนที่ 15 จะเป็นเท่าไหร่?

วิธีนึงที่ Simple ที่สุดก็คือ จินตนาการว่าเราลากเส้นตรงเส้นนึงมาเป็นตัวแทนของข้อมูลทั้งหมด แล้วก็ดูว่าบนเส้นนั้นที่แกน X เป็น 15 เจ้า แกน Y จะเป็นเท่าไหร่?

เส้นตรงที่จะมาเป็นตัวแทนนั้น ทางสถิติจะใช้หลักการว่า มันต้องเป็นเส้นตรงที่มีค่า Sum of Squares of Error หรือ SSE ต่ำที่สุด ซึ่งคิดมาจาก ระยะห่างระหว่างค่า y จริงกับค่า y ของเส้นตัวแทน (ซึ่งระยะห่างนี้ก็คือ Error) เอาไปยกกำลังสอง (Squares) แล้วบวกกันทั้งหมด (Sum) นั่นเอง

เส้นที่ได้มันก็จะอยู่กลางๆ หน่อยนั่นแหละ ซึ่งเราสามารถสร้างเส้นนี้ง่ายๆ โดยการคลิ๊กขวาที่จุดของกราฟแล้วกด Add Trendline จะได้ดังรูป (ซึ่งเราสามารถกด Display Equation เพื่อแสดงสมการของเส้นตรง กับติ๊กแสดงค่า R-Square เพื่อแสดงความแม่นได้ด้วย ซึ่งยิ่งใกล้ 1 ยิ่งแม่น)

รูปแบบของสมการเส้นตรงที่เราเคยเรียนสมัยประถมหรือมัธยม มันคือ

y=mx+c
y=ความชัน*x + จุดตัดแกน

แปลว่าเส้นตรงดังกล่าวนั้นมีลักษณะดังนี้

• มีความชัน (Slope) คือ 74.14 (ซึ่งแปลว่า x เพิ่ม 1 หน่วย y จะเพิ่ม 74.14 หน่วย)
• และมีจุดตัดแกน y (Intercept) ที่ 2153.8

ซึ่งหากเราต้องการให้ Excel แสดงค่าความชัน กับจุดตัดแกนด้วยสูตร ก็ทำได้ เช่น

ความชัน

=SLOPE(known_ys,known_xs)
known_ys คือ ค่า y actual ซึ่งก็คือยอดขายจริง
known_xs คือ ค่า x actual ซึ่งคือลำดับเดือน
=SLOPE(B2:B13,A2:A13)
=74.13986014

จุดตัดแกน

=INTERCEPT(known_ys,known_xs)
=INTERCEPT(B2:B13,A2:A13)
=2153.757576

ซึ่งถ้าเราลองแทนค่า x เป็น 15 ลงไปในสมการ y = 74.140x + 2153.756 ก็จะได้ยอดขายของเดือนที่ 15 นั่นเอง นี่คือหลักการที่ Excel ใช้ครับ

FORECAST.LINEAR

ซึ่งหากเราต้องการเขียนสูตร ก็สามารถใช้ฟังก์ชัน FORECAST.LINEAR หรือ FORECAST (ใน Excel version เก่ากว่า 2016) ได้เลย

=FORECAST.LINEAR(x,known_ys,known_xs)
=FORECAST.LINEAR(A14,
$B$2:$B$13,$A$2:$A$13)

การ Forecast ตัวเลขแบบ Model ที่มีลักษณะ Seasonal

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

ดังนั้นเราจะเปลี่ยนวิธีไปใช้วิธีอื่น เช่น ใช้ฟังก์ชัน FORECAST.ETS มาช่วย (มีตั้งแต่ Excel 2016 เป็นต้นไป)

FORECAST.ETS ใช้พยากรณ์กรณีมี seasonal

FORECAST.ETS นั้นย่อมาจาก Exponential Triple Smoothing ซึ่งเป็นวิธีการหาแนวโน้มที่สามารถรองรับความเป็น Seasonality ได้นั่นเอง

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

เจ้าฟังก์ชันนี้มี input ที่กรอกได้ค่อนข้างซับซ้อน แต่ก็มีตัวที่จำเป็นต้องกรอกคือ 3 ตัวแรก นั่นคือ

• target_date = วันที่ต้องการรู้ค่า Forecast
• values = ค่า y actual ที่เรารู้
• timeline = ค่า x actual ที่เรารู้ ซึ่งสิ่งสำคัญคือ ต้องมีระยะห่างระหว่างแต่ละจุดเท่ากัน จึงจะใช้ได้ (ต่างจาก Forecast เส้นตรงที่ known’s x จะห่างเท่ากันหรือไม่เท่าก็ได้)

จริงๆ ใส่แค่นี้มันก็หาคำตอบได้แล้วเช่น

=FORECAST.ETS(A14,
$B$2:$B$13,$A$2:$A$13)

จะได้ผลลัพธ์ดังนี้ ซึ่งหากเราไม่ระบุ Seasonality ให้มัน Excel จะพยายาม Detect ค่า Seasonal เอง ซึ่งอาจจะไม่ตรงใจเรา… เช่นในรูปข้างล่างนี้ Excel มันคิดว่าไม่มี Seasonality (หรือมีค่าเป็น 0) ทำให้ผลลัพธ์ออกมาเป็นเส้นตรงแบบ Linear เป๊ะเลย

Tips : ถ้าเราอยากรู้ว่า Excel ทำการ Forecast ให้เราด้วย Seasonality เท่าไหร่ สามารถใช้ฟังก์ชัน FORECAST.ETS.SEASONALITY มาช่วยในการตรวจสอบได้ครับ

\=FORECAST.ETS.SEASONALITY(values,timeline,[data_completion],[aggregation]) \=FORECAST.ETS.SEASONALITY(B2:B13,A2:A13) \=0

ซึ่งแสดงว่าในเคสข้างบนนี้มันคิดว่า Data ผมไม่มี Seasonality จริงๆ ด้วย

แต่ถ้าเรารู้ค่า Seasonality ว่า Data ของเรามันมีความซ้ำๆ ทุกๆ ช่วงระยะเวลาเท่าไหร่ เราสามารถระบุเข้าไปใน input ที่ชื่อว่า seasonality ได้เลย เช่น ถ้าผมมองว่ายอดขายมันจะพุ่งขึ้น 3 เดือนและค่อยตกลง จากนั้นก็พุ่งทีละ 3 เดือนอย่างนี้ไปเรื่อยๆ ดังนั้น Seasonality ควรจะเป็น 3 (ต้องระบุด้วยหน่วยของเวลาเดียวกับ timeline)

ดังนั้น ถ้าระบุ Seasonality เป็น 3 จะได้ดังรูป ซึ่งจะเห็นว่ายอดขายมันมียึกยักเป็น Seasonality แล้ว

แต่ถ้า Data ดรามี Seasonality ชัดเจน มันจะ Detect ได้เองจริงๆ โดยที่เราไม่ต้องไปช่วยระบุให้เลย เช่นผมแก้ Data นิดหน่อยตามช่องสีเหลือง มันจะรู้เลยว่า Seasonality เป็น 3 ครับ และตัว FORECAST.ETS ผมไม่ต้องระบุ Seasonality เลย

นอกจากนี้เรายังสามารถแสดงค่าอื่นๆ ได้อีกเช่นใช้ FORECAST.ETS.CONFINT แสดงค่า Confidence Interval เพื่อไปช่วยคำนวณขอบเขตบนล่างตามระดับความมั่นใจทางสถิติอีกมากมาย ซึ่งจริงๆ แล้วสิ่งที่เราทำทุกอย่างที่ผ่านมา มีวิธีที่ง่ายสุดๆ โดยเราไม่ต้องเขียนสูตรเองด้วยนะ!

วิธี Forecast แบบที่ง่ายที่สุดในสามโลก

ถ้าเราไม่อยากมาปวดหัวกับการเขียนสูตรกลุ่ม FORECAST ที่ผมพูดถึงทั้งหมดในข้อความข้างบนของบทความนี้ ใน Excel 2016 ขึ้นไป จะมี Forecast Sheet ให้กดใน Ribbon Data ได้เลย (อยู่ใกล้ๆ What if Analysis) แค่เราเลือก actual data แล้วไปกดปุ่มมหัศจรรย์นี้ได้เลย

ซึ่งจะเห็นว่ามันยัง Detect Seasonality ไม่ถูกต้อง (เช่นเดียวกับสูตร FORECAST.ETS ตอนแรก)

แต่เราสามารถกด Option แล้วไปตั้งค่า Seasonality เองได้ครับ

ถ้าคิดว่าผลลัพธ์ ok แล้ว ก็กด ok จบเลย

มันจะสร้าง Sheet ใหม่ที่มีทุกอย่างให้เลย ทั้งกราฟ ทั้ง Data มาหมด!! แถมเป็นสูตรให้หมดแล้วด้วย

สรุปแล้วหลอกให้อ่านบทความตั้งนาน ถ้ากด Forecast Sheet ก็จบเลย 555

การ Forecast นั้นมาจากค่า x เพียงตัวแปรเดียว…

อย่างไรก็ตามการ Forecast ที่ Excel ใช้ มันคำนวณค่า y มาจากตัวแปร x ที่มีเพียงตัวแปรเดียวเท่านั้น ดังนั้นพูดได้อีกอย่างว่า มันค่อนข้างอยู่บน Assumption ว่าเหตุการณ์ที่เป็นปัจจัยทำให้เกิดยอดขายไม่ได้เปลี่ยนแปลงอะไรมากมาย หรือ assume ว่าปัจจัยต่างๆ สามารถอธิบายได้ด้วยเวลาที่เพิ่มขึ้นเรื่อยๆ ได้ดีนะครับ เช่น ถ้าเราจะคาดการณ์ยอดขายในช่วงที่มี Covid ระบาด กับช่วงที่ไม่มี Covid ผลมันอาจไม่ work เลยก็ได้ เพราะปัจจัยพื้นฐานมันเปลี่ยนไปแล้ว

ดังนั้นถ้ามีเรื่องของแกน x คือเวลาอย่างเดียว การใช้ Judgement ช่วย Adjust ค่าหลังจาก Forecast อีกที เช่น อาจคิดว่าช่วง Covid ตกลงไป 30-40% จากที่ประมาณการณ์ได้ ก็อาจเป็นสิ่งที่สามารถทำได้เช่นกัน แต่ก็ควรทำด้วยหลักการที่มาจากข้อมูลที่เก็บมาได้นะ

ดังนั้นถ้าเราอยากจะพยากรณ์ให้แม่นยำขึ้นแบบไม่ต้องมโนมาก แปลว่าการเก็บข้อมูลเราต้องละเอียดขึ้น เช่น ต้องใช้ตัวแปร x หลายตัว ไม่ได้มีแค่เรื่องของเวลาอย่างเดียว ซึ่งเราจะไม่ได้ใช้ Forecast แล้ว แต่ต้องเปลี่ยนมาใช้ TREND แทน (หรือไม่ก็ใช้ Regression ใน Analysis Tool Pak)

TREND สามารถรองรับตัวแปร x หลายตัวได้

การประมาณการณ์แบบเส้นตรงนั้น นอกจากจะใช้ FORECAST แล้วยังมีอีกตัวนึงที่ทำหน้าที่คล้ายๆ กันคือ TREND ซึ่งเก่งกว่า FORECAST.LINEAR ซึ่งเก่งกว่าใน 2 ประเด็น คือ

1. สามารถให้ผลลัพธ์เป็น Array Formula แทนที่จะให้ผลลัพธ์เป็นตัวเดียวแบบ FORECAST
2. สามารถรองรับตัวแปร x หลายตัวได้

มาดูประเด็นแรกกันก่อน

=TREND(known_ys,[known_xs],[new_xs],[const])
=TREND(B2:B13,A2:A13,A14:A21)

กรณีที่เราใส่ new_xs (ค่า x ในอนาคตที่อยากรู้ว่า y จะเป็นเท่าไหร่) ไปหลายๆ ตัว ผลลัพธ์จะออกมาเป็น Array ชุดเดียวกันเลย (ถ้าไม่ใช่ Excel 365 อย่าลืมเลือกหลายช่องก่อน แล้วพอใส่สูตรเสร็จต้องกด Control+Shift+Enter ด้วย)

ประเด็นที่สอง ซึ่งคือเรื่องสำคัญคือ TREND ยังรองรับค่า known_xs หลายชุดได้ด้วย (มีตัวแปร x หลายมิติ เช่น อาจมี x1=เดือน, x2=จำนวนสาขา อะไรแบบนี้ได้) ซึ่งจะทำให้สามารถพยากรณ์ได้แม่นยำมากขึ้นไปอีก ซึ่งนี่คือหลักการเดียวกับสิ่งที่เรียกว่า Multiple Linear Regression Analysis เลยล่ะ

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

ความยากของ Regression คือการเตรียมข้อมูล

สิ่งสำคัญคือ Data ต้อง Clean ข้อมูลต้องเป็นตัวเลขทั้งหมด ไม่มีช่องว่าง ไม่มีข้อความ สูตรมันถึงจะใช้ได้นะ

ถ้าค่า x ที่เก็บมาเป็น Category ที่เป็นข้อความ เราก็ต้องแปลงเป็นตัวเลข เช่น 0 กับ 1 ให้ได้ด้วย

นอกจากนี้ ถ้าคิดว่าตัวแปร x บางตัวกับค่า y อาจไม่ได้สัมพันธ์กันแบบเส้นตรง เราก็ต้องมีการ transform ค่า x นั้นๆ ก่อนจะใช้ TREND หรือ Regression นะครับ เช่น อาจใส่ log เข้าไป เป็นต้น

ถ้าข้อมูลยังไม่เรียร้อยก็ต้องจัดการก่อน จะเขียนสูตรแก้ จะใช้ find/replace ช่วย หรือจะใช้ Power Query ก็สุดแล้วแต่ความสามารถของแต่ละคนละ

ลองใส่แต่ตัวเลขไปเล่นๆ

หลังจากผมเลือกมาเฉพาะตัวแปร x mี่เป็นตัวเลขธรรมดา (เพราะขี้เกียจแปลง Category) พอใช้ TREND ได้ค่า predict y แล้ว ก็มาลอง plot กราฟคู่กับ actual y ที่เป็นเฉลย ก็ถือว่าใกล้เคียงกัน 86% (อันนี้คือผมเลือกตัวแปร x มั่วๆ ยังไม่ได้คัดเอาตัวที่ significant จริงๆ)

Tips : ถ้าเราอยากรู้ว่าค่าสัมประสิทธิแต่ละตัวแปร x คือเท่าไหร่ ก็สามารถลองเปลี่ยนค่า x เป็น 1 กับ 0 ก็จะรู้แล้วล่ะ แต่ก็ยุ่งยากอยู่ ดังนั้นยังมีอีกวิธีนึงคือใช้ Regression ใน Analysis Tool Pak Add in ซึ่งต้องไป enable add-in ใน excel option ก่อน

Regression ใน Analysis Tool Pak Add in

ให้เราไปที่ File -> Excel Options -> Add in -> Go แล้วเลือก Analysis ToolPak

จากนั้นค่อยกดเรื่องเครื่องมือใน Ribbon Data

แล้วใส่ Input ค่า Y actual ที่รู้ (1 คอลัมน์) และค่า X ที่รู้ (หลายคอลัมน์) โดยแนะนำว่าใส่หัวตารางไปด้วย แล้วติ๊กคำว่า Labels ซะ

พอกด ok มันจะบอกรายละเอียดทั้งหมดออกมาได้เลยดังนี้ ซึ่งจะเห็นค่า Coefficient (ความชัน) ซึ่งแปลว่าถ้า x เปลี่ยน 1 หน่วย y จะเปลี่ยนเท่าไหร่ และเห็นค่า p-value ด้วย ว่าถ้าน้อยๆ (น้อยกว่า 0.05) แสดงว่าตัวแปร x นั้นๆ มีนัยสำคัญ (Significant)

สรุป

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