Monte Carlo Simulation เป็นเทคนิคทางสถิติสำหรับจำลองความไม่แน่นอนและประเมินผลกระทบของความเสี่ยงในสถานการณ์อย่าง Financial Forecasting, Project Management, Inventory Analysis การสร้าง Scenarios หลายพันรอบ Monte Carlo ให้วิธีประเมิน Variability ช่วยตัดสินใจจากข้อมูลภายใต้ความไม่แน่นอน บทความนี้จะ Implement Monte Carlo Simulation ด้วย Excel
กำหนดโจทย์สำหรับ Monte Carlo: ชุดข้อมูล E-commerce ขนาดเล็ก จำลอง Profit จาก Key Metrics ที่ส่งผลต่อ Outcome
ขั้นตอนที่ 1: จัดเตรียมข้อมูลใน Excel
ใส่ Key Metrics ในคอลัมน์:
- Column A: Simulation Number (1, 2, 3, …, 1000)
- Column B: Selling Price (ใช้ RAND จำลองราคาต่อหน่วย)
- Column C: Units Sold (จำลองจำนวนหน่วยที่ขายได้)
- Column D: Cost per Unit (สุ่มต้นทุนต่อหน่วย)
- Column E: คำนวณ Revenue (คูณ Units และ Price)
- Column F: Total Costs (จาก Fixed และ Variable Costs)
- Column G: Profit Calculation (Revenue - Total Cost)
ขั้นตอนที่ 2: สร้าง Random Inputs ด้วย Excel Functions
ใช้ RAND, RANDBETWEEN, NORM.INV สร้างตัวเลขสุ่มสำหรับแต่ละ Variable
Simulation No.
ใช้ Series Option ใส่ตัวเลข ใส่ 1 ใน Simulation No. Column ลากลงไปจะ Auto-generate
- เลือกเซลล์ A2 ใส่ 1
Selling Price:
- ใช้ Normal Distribution Mean $30 SD $4.5
- ใส่สูตรใน B2
สูตร:
=NORM.INV(RAND(), 30, 4.5)
สูตรสร้างค่าที่กระจาย Normal รอบ Mean 30 SD 4.5
Units Sold:
- ใช้ Poisson Distribution สุ่มค่า Poisson
- ใส่สูตรใน C2
สูตร:
สูตรสร้างค่าสุ่ม 100-550 เป็น Units Sold
Cost per Unit:
- Uniform Distribution $10 ถึง $18
- เลือก D2 ใส่สูตร:
สูตร:
Revenue:
- คำนวณ Revenue = Units Sold × Selling Price
- ใส่สูตรใน E2
สูตร:
Total Cost:
Total Cost = Fixed Costs + Variable Costs + Production Costs
- เลือก F2 ใส่สูตร:
สูตร:
=($D2 * $C2) + 1000 + (E2 * 0.1)
สูตรคำนวณ Total Cost รวม Fixed, Variable, Production Costs
- ($D2 * $C2): Variable Cost คำนวณจาก Units (C2) × Unit Cost (D2)
- 1000: Fixed Cost คงที่
- (E2 * 0.1): Production Cost 10% ของ Revenue
Profit:
- คำนวณ Profit ใส่สูตร:
สูตร:
สูตรลบ Revenue - Total Cost
ผลลัพธ์:
เห็นสูตรและผลลัพธ์ตัวแปรทั้งหมด ต่อไป Copy สูตรสร้าง Simulations
ขั้นตอนที่ 3: Copy Formulas สร้าง Simulations ทั้งหมด
- ลากสูตรลงใน Columns B, C, D, E, F, G สร้าง Random Data สำหรับแต่ละ Simulation
- แต่ละแถว = 1 Simulation
ขั้นตอนที่ 4: วิเคราะห์ผลลัพธ์
เมื่อได้ 1000 Simulations มาวิเคราะห์ผลลัพธ์ ใช้ Statistical Measures ประเมินศักยภาพกำไร
Average Profit:
สูตรคำนวณ Average Profit:
สูตร:
Average Profit ช่วยคาดการณ์ผลลัพธ์ที่คาดหวัง
Minimum Profit:
สูตรหา Minimum Profit:
สูตร:
Minimum Profit แสดง Worst-case Scenario
Maximum Profit:
สูตรหา Maximum Profit:
สูตร:
Maximum Profit แสดง Best Possible Outcome
Probability of Loss:
คำนวณ % Simulations ที่ Profit เป็นลบ
สูตร:
=COUNTIF(G2:G1001,"<0")/COUNTA(G2:G1001)
ช่วยประเมินความเสี่ยงขาดทุน
95% Confidence Interval (CI) for Profit:
คำนวณ Lower/Upper Bounds ของ 95% CI
Lower bound:
=PERCENTILE(G2:G1001, 0.025)
คำนวณ Percentile 2.5th หมายถึง 2.5% ของข้อมูล Profit ต่ำกว่าค่านี้
Upper bound:
=PERCENTILE(G2:G1001, 0.975)
คำนวณ Percentile 97.5th หมายถึง 97.5% ของข้อมูล Profit ต่ำกว่าค่านี้
ผลลัพธ์:
สร้าง Histogram ของ Profit:
- เลือกคอลัมน์ Profit
- แท็บ Insert >> Charts >> Histogram
ได้ Histogram ของ Profit
การกระจายกำไรแสดง Profit Spread ยังไง
Monte Carlo Simulation ด้วย Data Table Tool
ใช้ Data Tool Implement Monte Carlo ได้ ใช้ Series Option ใส่ Simulation Numbers
- สร้าง Data Table พร้อม Column Names
- ใส่ 1 ใน A2 ของ Simulation No.
- แท็บ Home >> Fill >> Series
- ใน Series dialog:
- Series in: Columns
- Type: Linear
- Step value: 1
- Stop Value: 1000
- OK
- เลือกตารางทั้งหมด A2:G1001
- แท็บ Data >> What-If_Analysis >> Data Table
- ใน Data Table dialog:
- Row input cell: เว้นว่าง
- Column input cell: เลือก A2
- OK
Data Table จะสร้าง 1000 Simulation Values
Tips และ Best Practices
- ใช้ Cell References สำหรับ Variable Limits เพื่อ Update ง่าย
- ตั้ง Calculation Mode เป็น Manual ไม่งั้นข้อมูลจะอัปเดตทุกครั้งกด Enter ใช้ Calculate Now (F9)
- Run อย่างน้อย 1,000 - 10,000 Simulations สำหรับความแม่นยำ
- ถ้าต้องการรอบสิบพัน VBA จัดการได้ดีกว่า
สรุป
ทำตามขั้นตอนทั้งหมด Implement Monte Carlo Simulation ใน Excel ได้ วิธีหลากหลายพยากรณ์ Outcome ภายใต้ความไม่แน่นอน ด้วย RANDBETWEEN, RAND, NORM.INV สร้างโมเดลแข็งแกร่งวิเคราะห์ความเสี่ยง ปรับปรุง Decision Making Monte Carlo ช่วยวิเคราะห์ Financial Risk, Optimize Project Timelines ฯลฯ
Get FREE Advanced Excel Exercises with Solutions!
