การจำลองมอนติคาร์โลเป็นเทคนิคทางสถิติที่ใช้ในการจำลองความไม่แน่นอนและประเมินผลกระทบของความเสี่ยงในสถานการณ์ต่างๆ เช่น การคาดการณ์ทางการเงิน การจัดการโครงการ และการวิเคราะห์สินค้าคงคลัง การสร้างสถานการณ์นับพันสถานการณ์ Monte Carlo Simulation เป็นวิธีการประเมินความแปรปรวนที่ช่วยในการตัดสินใจโดยอาศัยข้อมูลภายใต้ความไม่แน่นอน ในบทความนี้ เราจะใช้การจำลองมอนติคาร์โลโดยใช้ Excel
เรามากำหนดปัญหาในการใช้การจำลองมอนติคาร์โลกัน พิจารณาชุดข้อมูลอีคอมเมิร์ซขนาดเล็กที่คุณจะจำลองผลกำไรตามเมตริกหลักที่ส่งผลต่อผลลัพธ์
ขั้นตอนที่ 1:ตั้งค่าข้อมูลของคุณใน Excel
แทรกเมตริกหลักลงในคอลัมน์ ตัวชี้วัดหลักคือ
- คอลัมน์ ก: หมายเลขจำลอง (1, 2, 3, …, 1000)
- คอลัมน์ B: ราคาขาย (ใช้ฟังก์ชัน RAND เพื่อจำลองราคาต่อหน่วย)
- คอลัมน์ C: หน่วยที่ขายได้ (จำลองจำนวนหน่วยที่ขายได้)
- คอลัมน์ D: ต้นทุนต่อหน่วย (ตัวเลขสุ่มของการคิดต้นทุน)
- คอลัมน์ E: คำนวณรายได้ (คูณหน่วยและต้นทุน)
- คอลัมน์ F: ต้นทุนรวม (ขึ้นอยู่กับต้นทุนคงที่และต้นทุนผันแปร)
- คอลัมน์ E :การคำนวณกำไร (ลบรายได้และต้นทุนรวม)
ขั้นตอนที่ 2:สร้างอินพุตแบบสุ่มโดยใช้ฟังก์ชัน Excel
หากต้องการสร้างตัวเลขสุ่มสำหรับตัวแปรคีย์แต่ละตัว คุณสามารถใช้ฟังก์ชัน RAND, RANDBETWEEN และ NORM.INV ใน Excel ได้
หมายเลขการจำลอง
คุณสามารถใช้ตัวเลือก Series เพื่อแทรกตัวเลขได้ เริ่มแรกให้แทรก 1 ลงในคอลัมน์หมายเลขการจำลอง ในขณะที่เราจะลากคอลัมน์ มันจะสร้างหมายเลขการจำลองโดยอัตโนมัติ
- เลือกเซลล์ A2 และแทรก 1
ราคาขาย:
- การใช้การแจกแจงแบบปกติโดยมีค่าเฉลี่ย $30 และค่าเบี่ยงเบนมาตรฐานที่ $4.5 .
- แทรกสูตรต่อไปนี้ในเซลล์ B2
สูตร:
=NORM.INV(RAND(), 30, 4.5)
สูตรนี้สร้างค่าที่กระจายตามปกติรอบๆ ค่าเฉลี่ย (30) โดยมีค่าเบี่ยงเบนมาตรฐาน 4.5
หน่วยที่ขาย:
- การใช้การแจกแจงแบบปัวซองเพื่อสร้างค่าปัวซองแบบสุ่ม
- แทรกสูตรต่อไปนี้ในเซลล์ C2
สูตร:
สูตรนี้จะสร้างค่าสุ่มภายใน 100 ถึง 550 ตามหน่วยที่ขายของผลิตภัณฑ์นั้นๆ
ต้นทุนต่อหน่วย:
- สร้างการกระจายแบบสม่ำเสมอตั้งแต่ $10 เป็น $18 .
- เลือกเซลล์ D2 และแทรกสูตรต่อไปนี้
สูตร:
รายได้:
- ในการคำนวณรายได้ให้คูณหน่วยที่ขายและราคาขาย
- แทรกสูตรต่อไปนี้ในเซลล์ E2
สูตร:
ต้นทุนทั้งหมด:
คำนวณต้นทุนรวมเป็นผลรวมของต้นทุนคงที่ ต้นทุนผันแปร และต้นทุนการผลิต
- เลือกเซลล์ F2 และแทรกสูตรต่อไปนี้
สูตร:
=($D2 * $C2) + 1000 + (E2 * 0.1)
สูตรนี้จะคำนวณต้นทุนทั้งหมดโดยการรวมต้นทุนคงที่ ต้นทุนผันแปร และต้นทุนการผลิต
- ($D2 * $C2): มันเป็นต้นทุนผันแปร ส่วนนี้จะคำนวณต้นทุนทั้งหมดตามจำนวนหน่วยที่ผลิต (D2) และต้นทุนต่อหน่วย (C2)
- 1,000: จะถูกเพิ่มเป็นต้นทุนคงที่ ซึ่งเป็นจำนวนคงที่โดยไม่คำนึงถึงระดับการผลิต
- (E2 * 0.1): จะคำนวณต้นทุนการผลิตโดยใช้อัตรา 10% กับรายได้
กำไร:
- ในการคำนวณกำไร ให้ใส่สูตรต่อไปนี้
สูตร:
สูตรนี้ลบรายได้ออกจากต้นทุนทั้งหมด
เอาต์พุต:
คุณสามารถดูสูตรและผลลัพธ์ทั้งหมดของตัวแปรได้ หลังจากนั้น คุณสามารถคัดลอกสูตรทั้งหมดเพื่อสร้างแบบจำลองได้
ขั้นตอนที่ 3:คัดลอกสูตรเพื่อสร้างการจำลองทั้งหมด
- ลากสูตรลงใน คอลัมน์ B, C, D, E, F, และ ก เพื่อสร้างข้อมูลสุ่มสำหรับแต่ละการจำลอง
- ทุกแถวจะแสดงการจำลองหนึ่งรายการ
ขั้นตอนที่ 4:วิเคราะห์ผลลัพธ์
เมื่อสร้างแบบจำลองครบ 1,000 รายการแล้ว มาวิเคราะห์ผลลัพธ์ของการจำลองกัน คุณสามารถใช้มาตรการทางสถิติต่อไปนี้เพื่อประเมินศักยภาพในการทำกำไรของธุรกิจ
กำไรเฉลี่ย :
แทรกสูตรต่อไปนี้เพื่อคำนวณกำไรเฉลี่ย
สูตร:
กำไรเฉลี่ยจะช่วยให้คุณได้รับผลลัพธ์ที่คาดหวัง
กำไรขั้นต่ำ:
แทรกสูตรต่อไปนี้เพื่อระบุกำไรขั้นต่ำ
สูตร:
กำไรขั้นต่ำจะแสดงให้คุณเห็นสถานการณ์กรณีที่เลวร้ายที่สุด
กำไรสูงสุด:
ใช้สูตรต่อไปนี้เพื่อระบุกำไรสูงสุด
สูตร:
กำไรสูงสุดจะแสดงให้คุณเห็นผลลัพธ์ที่ดีที่สุดเท่าที่จะเป็นไปได้
ความน่าจะเป็นของการสูญเสีย :
คำนวณเปอร์เซ็นต์ของการจำลองที่กำไรติดลบ
สูตร:
=COUNTIF(G2:G1001,"<0")/COUNTA(G2:G1001)
มันจะช่วยให้คุณทราบว่าคุณอาจต้องสูญเสียมากเพียงใด
ช่วงความเชื่อมั่น 95% (CI) เพื่อผลกำไร :
คำนวณขอบเขตล่างและบนของ CI 95%
ขอบเขตล่าง:
=PERCENTILE(G2:G1001, 0.025)
สูตรนี้จะคำนวณเปอร์เซ็นไทล์ที่ 2.5 ซึ่งหมายความว่า 2.5% ของข้อมูลกำไรอยู่ต่ำกว่าค่านี้
ขอบเขตบน:
=PERCENTILE(G2:G1001, 0.975)
สูตรนี้จะคำนวณเปอร์เซ็นไทล์ที่ 97.5 ซึ่งหมายความว่า 97.5% ของข้อมูลกำไรอยู่ต่ำกว่าค่านี้
เอาต์พุต:
สร้างฮิสโตแกรมของผลกำไร :
- เลือกคอลัมน์กำไร
- ไปที่ ส่วนแทรก แท็บ>> จาก แผนภูมิ>> เลือก ฮิสโตแกรม .
คุณจะได้รับคอลัมน์ฮิสโตแกรมสำหรับกำไร
การกระจายผลกำไรแสดงให้เห็นว่ามีการกระจายผลกำไรอย่างไร
การจำลองมอนติคาร์โลโดยใช้เครื่องมือตารางข้อมูล
คุณสามารถใช้เครื่องมือ Data Table เพื่อใช้การจำลอง Monte Carlo คุณสามารถใช้ตัวเลือก Series เพื่อแทรกหมายเลขจำลองได้
- สร้างตารางข้อมูลด้วยชื่อคอลัมน์
- แทรก 1 ในเซลล์ A2 ของคอลัมน์หมายเลขการจำลอง
- ไปที่หน้าแรก แท็บ>> จาก เติม>> เลือก ซีรีส์ .
- ในซีรีส์ กล่องโต้ตอบ;
- ซีรีส์ใน: คอลัมน์
- ประเภท: เชิงเส้น
- ค่าขั้นตอน: 1
- ค่าหยุด: 1,000
- คลิก ตกลง .
- เลือกทั้งตารางจากเซลล์ A2:G1001
- ไปที่ข้อมูล แท็บ>> จาก What-If_Analysis>> เลือก ตารางข้อมูล .
- ใน ตารางข้อมูล กล่องโต้ตอบ;
- เซลล์ที่ป้อนแถว: เว้นว่างไว้
- เซลล์ที่ป้อนคอลัมน์: เลือกเซลล์ A2
- คลิก ตกลง .
ตารางข้อมูลจะสร้างค่าการจำลอง 1,000 ค่า
เคล็ดลับและแนวทางปฏิบัติที่ดีที่สุด
- ใช้การอ้างอิงเซลล์สำหรับขีดจำกัดของตัวแปรเพื่ออัปเดตได้อย่างง่ายดาย
- ตั้งค่า โหมดการคำนวณ ของคุณ เป็นกำหนดเอง . มิฉะนั้น ข้อมูลจะอัปเดตทุกครั้งที่คุณกด Enter . หากต้องการคำนวณเซลล์ ให้ใช้ คำนวณเลย (F9) ตัวเลือก
- ทำการจำลองอย่างน้อย 1,000 ถึง 10,000 ครั้งเพื่อการคาดการณ์ที่แม่นยำยิ่งขึ้น
- หากคุณต้องการรันการจำลองนับหมื่น VBA ก็สามารถจัดการสถานการณ์เหล่านั้นได้อย่างมีประสิทธิภาพมากขึ้น
บทสรุป
เมื่อทำตามขั้นตอนทั้งหมดแล้ว คุณจะสามารถใช้การจำลอง Monte Carlo ใน Excel ได้ เป็นวิธีการที่หลากหลายในการทำนายผลลัพธ์ที่เป็นไปได้ภายใต้ความไม่แน่นอน ด้วยการใช้ฟังก์ชัน RANDBETWEEN, RAND และ NORM.INV ของ Excel คุณจะสามารถสร้างแบบจำลองที่มีประสิทธิภาพเพื่อวิเคราะห์ความเสี่ยงและปรับปรุงการตัดสินใจได้ การจำลองมอนติคาร์โลช่วยในการวิเคราะห์ความเสี่ยงทางการเงิน เพิ่มประสิทธิภาพไทม์ไลน์ของโครงการ ฯลฯ
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!