การใช้ excel บทความนี้ขอนำเสนอ ตัวอย่างการพยากรณ์ยอดขายด้วยเทคนิค Monte Carlo Simulation ก่อนอื่นขอแจ้งก่อนว่าที่ผ่านมาเรานำเสนอการสร้างแผนภูมิฮิสโตแกรมกันมาหลายบทความแล้ว บทความนี้เป็นการนำฮิสโตแกรมมาใช้ประมาณการผลกำไรและหาความเสี่ยงของจากการพยากรณ์ยอดขายกันครับ โดยผมจะเริ่มจากการกล่าวถึงการสร้างแบบจำลองเพื่อพยากรณ์ยอดขายโดยใช้ Monte Carlo Simulation โดยผลที่ได้จากการจำลองจะนำไปสร้างแผนภูมิฮิสโตแกรมและนำไปสู่การวิเคราะห์แผนภูมิเป็นลำดับถัดไป สุดท้ายจะชี้ให้เห็นถึงแนวคิดการใช้ Monte Carlo Simulation กับการจำลองเหตุการณ์ต่างๆกันเพื่อให้เป็นประโยชน์กับท่านผู้อ่านต่อไป มาเริ่มกันเลยครับ
เริ่มจาก สมมุติว่าเราได้พัฒนาสินค้าใหม่ชนิดหนึ่งขึ้นมา เราต้องการคาดเดาผลกำไรจากการขายสินค้าชนิดนี้และความเสี่ยงที่สินค้าชนิดนี้จะขาดทุน ดังนั้นการจำลองเหตุการณ์เพื่อพยากรณ์ยอดขายและกำไรจึงเป็นวิธีการที่ถูกนำมาใช้ โดยมีขั้นตอนดังนี้1. การสร้างแบบจำลองของเหตุการณ์ ในปัญหานี้ เราต้องการคำนวณผลกำไรจากการขายสินค้าใหม่ดังกล่าว ซึ่งผลกำไรสามารถเขียนเป็นแบบจำลองได้ดังนี้
กำไร(Y) = รายได้ - รายจ่าย
โดยรายได้เกิดจาก
จำนวนการสั่งซื้อ (S) * กำไรต่อหน่วย (P)
และจำนวนการสั่งซื้อสามารถคำนวณได้จากจำนวนลูกค้าเป้าหมายที่บริษัทต้องไปนำเสนอ (L) *โอกาสที่ลูกค้าจะสั่งซื้อสินค้าดังกล่าว (R)
ดังนั้นรายได้สามารถคำนวณได้จาก L*R*P
รายจ่ายคำนวณได้จาก
รายจ่ายคงที่(H) + รายจ่ายในการนำเสนอลูกค้าทั้งหมด
รายจ่ายในการนำเสนอลูกค้าทั้งหมดจะเท่ากับ รายจ่ายจากการไปนำเสนอลูกค้าต่อราย(C) *L
ดังนั้น รายจ่ายคำนวณได้จาก H + L*C
ดังนั้น Y = L*R*P - (H+L*C)
จากแบบจำลองผลกำไรจะพบว่า ผลกำไรจะขึ้นอยู่กับตัวแปรอิสระ L, R, P, C, H โดยเราพบว่าตัวแปรบางตัวมีความผันผวนตลอดเวลา เรามาดูความผันผวนของแต่ละตัวแปรกันครับ
1. กำไรต่อหน่วยของสินค้า (P) อาจมีการผันผวนเนื่องจากต้นทุนการผลิต ค่าธรรมเนียมต่างๆ หรือแม้แต่ราคาตั้งต้นในการขาย สมมุติให้มีการผันผวน ระหว่าง 1,400 - 1,600 บาทต่อหน่วย
2. โอกาสที่ลูกค้าจะสั่งซื้อสินค้าดังกล่าว (R) อยู่ระหว่าง 5% - 20% จากกลุ่มลูกค้าเป้าหมาย
3. จำนวนลูกค้าเป้าหมายที่ไปนำเสนอ(L) ผันผวนอยู่ที่ 1,300 - 1,700 ราย
4. รายจ่ายจากการไปนำเสนอลูกค้าต่อราย(C) ผันผวนอยู่ระหว่าง 10 - 25 บาท
จากความผันผวนของตัวแปรที่กล่าวมาจึงพบว่าผลกำไรมีได้หลากหลายค่า ขึ้นอยู่กับตัวแปรอิสระดังกล่าว ดังนั้นหากเราสามารถจำลองตัวแปรอิสระดังกล่าวขึ้นมาในแต่ละครั้งเราก็สามารถคำนวณผลกำไรในแต่ละครั้งได้เช่นกัน และหากเราดำเนินการคำนวณผลกำไรได้จำนวนครั้งมากขึ้น ก็จะสามารถวิเคราะห์หาโอกาสความน่าจะเป็นที่จะเกิดผลกำไรของการขายสินค้าและความเสี่ยงของการขายสินค้าดังกล่าวได้จากการวิเคราะห์ฮิสโตแกรมของผลกำไร ซึ่งวิธีการจำลองตัวแปรอิสระให้อยู่ในช่วงที่ผกผันเพื่อคำนวณหาผลกำไรด้วยจำนวนชุดของคำตอบมากๆนั้นจะเรียกว่าเทคนิคการจำลองด้วย Monte Carlo โดยในที่นี้จะขอคำนวณค่าผลกำไรที่เป็นไปได้จำนวน 5000 ครั้ง ก่อนนำไปสร้างฮิสโตแกรมผลกำไรต่อไป
2. การจำลองตัวแปรอิสระให้อยู่ในช่วงผกผันในแต่ละครั้ง จะใช้สูตร excel : RAND() ซึ่ง จะคืนค่าตัวเลขที่สุ่มได้ระหว่าง มากกว่าหรือเท่ากับ 0 ถึงน้อยกว่า 1 โดยค่าที่ถูกสุ่มมีโอกาสเท่าๆกัน เราสามารถสั่งให้ excel สุ่มตัวเลขใหม่ได้โดยการกดปุ่ม F9 มาดูรูปแบบของ Worksheet ใน excel ก่อนทำการจำลองตัวแปรอิสระ ดังแสดงในภาพที่ 1
ภาพที่ 1 ค่าผกผันของตัวแปรอิสระ
3. สูตร excel สำหรับจำลองตัวแปรอิสระแต่ละตัวกำหนดได้ดังนี้ (เริ่มที่การจำลองครั้งที่ 1 แถวที่ 10)
R : A10 = $B$2 + RAND()*($C$2 - $B$2)
L : B10 = $B$3 + RAND()*($C$3 - $B$3)
P : C10 = $B$4 + RAND()*($C$4 - $B$4)
C : D10 = $B$5 + RAND()*($C$5 - $B$5)
4. สูตร excel สำหรับคำนวณผลกำไรจากการจำลองครั้งที่ 1
F10 = $A10*$B10*$C10 - ($B$6 + $B10*$D10)
6. ผลการจำลองแสดงได้ดังภาพที่ 2
ภาพที่ 2 การจำลองผลกำไรจากการขายสินค้าครั้งที่ 1
7. ทำการสำเนาสูตร excel ในแถวที่ 10 ไปอีก 4,999 แถว (แถวที่ 5009 ) เพื่อจำลองผลกำไรให้ครบ 5,000 ครั้ง
8. สังเกตุดูผลกำไรจะเปลี่ยนไปทุกครั้งที่มีการคำนวณใหม่ เนื่องจากสูตร excel: RAND (กดปุ่ม F9)
9. ดำเนินการเตรียมข้อมูลผลกำไรในช่วงข้อมูล F10:F5009 เพื่อเตรียมสร้างแผนภูมิฮิสโตแกรม ตามบทความ การใช้ excel สร้างแผนภูมิฮิสโตแกรม โดยใช้สูตร excel: FREQUENCY ได้รูปแบบของข้อมูลดังแสดงในภาพที่ 3
ภาพที่ 3 ข้อมูลสำหรับสร้างฮิสโตแกรมใน excel
10. ฮิสโตแกรมผลกำไรที่คาดว่าจะเกิดขึ้น ดังภาพที่ 4
ภาพที่ 4 ฮิสโตแกรมผลกำไรจากการพยากรณ์ยอดขายสินค้าใหม่
11. หากกดปุ่ม F9 รูปร่างฮิสโตแกรมจะเปลี่ยนไป
เพื่อให้การศึกษาการใช้ excel เป็นไปอย่างมีประสิทธิภาพ ผมขอจบเนื้อหาการพยากรณ์ยอดขายด้วยเทคนิค Monte Carlo Simulation เพียงเท่านี้ก่อน ในบทความถัดไปจะนำเสนอการวิเคราะห์ฮิสโตแกรมที่ได้จากการจำลองด้วยเทคนิค Monte Carlo Simulation โดยจะกล่าวถึงความเป็นไปได้ในการทำกำไรและความเสี่ยงที่อาจจะเกิดขึ้นในการขายสินค้า สวัสดีครับ
