บทนําสู่การจําลอง Monte Carlo ใน Excel

• ใครใช้มอนเตคาร์โลจําลอง?

• จะเกิดอะไรขึ้นเมื่อคุณพิมพ์ =RAND() ในเซลล์

• คุณจะจําลองค่าของตัวแปรสุ่มแบบแยกได้อย่างไร

• คุณจะจําลองค่าของตัวแปรสุ่มปกติได้อย่างไร

• บริษัทบัตรอวยพรสามารถกําหนดจํานวนบัตรที่จะผลิตได้อย่างไร

เราต้องการประมาณความน่าจะเป็นของเหตุการณ์ที่ไม่แน่นอนอย่างถูกต้อง ตัวอย่างเช่น อะไรคือความน่าจะเป็นที่กระแสเงินสดของผลิตภัณฑ์ใหม่จะมีมูลค่าปัจจุบันสุทธิเป็นบวก (NPV) ปัจจัยเสี่ยงของพอร์ตการลงทุนของเราคืออะไร การจําลอง Monte Carlo ช่วยให้เราสามารถจําลองสถานการณ์ที่ไม่แน่นอนแล้วเล่นบนคอมพิวเตอร์หลายพันครั้ง

ในห้าบทถัดไป คุณจะเห็นตัวอย่างของวิธีที่คุณสามารถใช้ Excel เพื่อจําลอง Monte Carlo

บริษัทหลายแห่งใช้การจําลอง Monte Carlo เป็นส่วนสําคัญในกระบวนการตัดสินใจของพวกเขา ต่อไปนี้เป็นตัวอย่างบางส่วน

• ทั่วไปมอเตอร์, Proctor and Gamble, Pfizer, Bristol-Myers Squibb และ Eli Lilly ใช้การจําลองเพื่อประมาณการทั้งผลตอบแทนเฉลี่ยและปัจจัยเสี่ยงของผลิตภัณฑ์ใหม่ ซีอีโอจะใช้ข้อมูลนี้ในการตรวจสอบผลิตภัณฑ์ที่จะออกสู่ตลาด

• GM ใช้การจําลองกิจกรรมต่างๆ เช่น การคาดการณ์รายได้สุทธิของบริษัท คาดการณ์ต้นทุนโครงสร้างและต้นทุนจัดซื้อ และกําหนดความเสี่ยงประเภทต่างๆ (เช่น การเปลี่ยนแปลงอัตราดอกเบี้ยและความผันผวนของอัตราแลกเปลี่ยน)

• ลิลลี่ใช้การจําลองเพื่อกําหนดความจุของพืชที่เหมาะสมสําหรับยาแต่ละตัว

• Proctor และ Gamble ใช้การจําลองแบบจําลองและการป้องกันความเสี่ยงจากการแลกเปลี่ยนเงินตราต่างประเทศอย่างเหมาะสม

• Sears ใช้การจําลองเพื่อกําหนดจํานวนหน่วยของแต่ละสายผลิตภัณฑ์ที่ควรสั่งซื้อจากซัพพลายเออร์ ตัวอย่างเช่น จํานวนคู่ของกางเกงเทียบที่ควรสั่งซื้อในปีนี้

• บริษัทน้ํามันและยาใช้การจําลองค่า "ตัวเลือกจริง" เช่น มูลค่าของตัวเลือกในการขยาย ทําสัญญา หรือเลื่อนโครงการ

• นักวางแผนการเงินใช้การจําลอง Monte Carlo เพื่อกําหนดกลยุทธ์การลงทุนที่ดีที่สุดสําหรับการเกษียณของลูกค้าของพวกเขา

เมื่อคุณพิมพ์สูตร =RAND() ในเซลล์ คุณจะได้รับตัวเลขที่มีแนวโน้มเท่าๆ กันที่จะถือว่ามีค่าใดๆ ระหว่าง 0 และ 1 ดังนั้นประมาณ 25 เปอร์เซ็นต์ของเวลาคุณควรได้รับตัวเลขที่น้อยกว่าหรือเท่ากับ 0.25 ประมาณ 10 เปอร์เซ็นต์ของเวลาที่คุณควรได้รับตัวเลขที่มีค่าอย่างน้อย 0.90 และอื่นๆ เมื่อต้องการแสดงให้เห็นว่าฟังก์ชัน RAND ทํางานอย่างไร ให้ดูที่ Randdemo.xlsx ไฟล์ ที่แสดงในรูป 60-1

ก่อนอื่น ให้คัดลอกจากเซลล์ C3 ไปยัง C4:C402 สูตร =RAND() จากนั้นให้คุณตั้งชื่อช่วงข้อมูล C3:C402 จากนั้น ในคอลัมน์ F คุณสามารถติดตามค่าเฉลี่ยของตัวเลขสุ่ม 400 ตัว (เซลล์ F2) และใช้ฟังก์ชัน COUNTIF เพื่อกําหนดเศษส่วนที่อยู่ระหว่าง 0 และ 0.25, 0.25 และ 0.50, 0.50 และ 0.75 และ 0.75 และ 1 เมื่อคุณกดแป้น F9 ตัวเลขสุ่มจะถูกคํานวณใหม่ โปรดสังเกตว่าค่าเฉลี่ยของตัวเลข 400 คือประมาณ 0.5 เสมอ และประมาณ 25 เปอร์เซ็นต์ของผลลัพธ์จะอยู่ในช่วง 0.25 ผลลัพธ์เหล่านี้สอดคล้องกับคําจํากัดความของตัวเลขสุ่ม โปรดทราบว่าค่าที่สร้างโดย RAND ในเซลล์ต่างๆ จะเป็นอิสระจากกัน ตัวอย่างเช่น ถ้าตัวเลขสุ่มที่สร้างขึ้นในเซลล์ C3 เป็นตัวเลขจํานวนมาก (ตัวอย่างเช่น 0.99) ตัวเลขนั้นจะบอกให้เราทราบถึงค่าของตัวเลขสุ่มอื่นๆ ที่สร้าง

สมมติว่าความต้องการปฏิทินถูกควบคุมโดยตัวแปรสุ่มแบบแยกต่อไปนี้:

อุปสงค์	ความน่าจะเป็น
10,000	0.10
20,000	0.35
40,000	0.3
60,000	0.25

เราจะให้ Excel เล่นหรือจําลองความต้องการนี้สําหรับปฏิทินหลายครั้งได้อย่างไร เคล็ดลับคือการเชื่อมโยงค่าที่เป็นไปได้แต่ละค่าของฟังก์ชัน RAND กับความต้องการที่เป็นไปได้สําหรับปฏิทิน งานที่มอบหมายต่อไปนี้ทําให้แน่ใจว่าความต้องการ 10,000 รายการจะเกิดขึ้น 10 เปอร์เซ็นต์ของเวลา และอื่นๆ

อุปสงค์	กําหนดตัวเลขแบบสุ่ม
10,000	น้อยกว่า 0.10
20,000	มากกว่าหรือเท่ากับ 0.10 และน้อยกว่า 0.45
40,000	มากกว่าหรือเท่ากับ 0.45 และน้อยกว่า 0.75
60,000	มากกว่าหรือเท่ากับ 0.75

เมื่อต้องการสาธิตการจําลองความต้องการ ให้ดูที่ Discretesim.xlsx ไฟล์ ที่แสดงในรูป 60-2 ในหน้าถัดไป

กุญแจสําคัญในการจําลองของเราคือการใช้ตัวเลขสุ่มเพื่อเริ่มต้นการค้นหาจากช่วงตาราง F2:G5 ( การค้นหาที่มีชื่อ) สุ่มตัวเลขที่มากกว่าหรือเท่ากับ 0 และน้อยกว่า 0.10 จะให้ผลลัพธ์เป็นความต้องการ 10,000 สุ่มตัวเลขที่มากกว่าหรือเท่ากับ 0.10 และน้อยกว่า 0.45 จะให้ผลดีต่อความต้องการ 20,000 ตัวเลขสุ่มที่มากกว่าหรือเท่ากับ 0.45 และน้อยกว่า 0.75 จะให้ผลดีต่อความต้องการ 40,000 และสุ่มตัวเลขที่มากกว่าหรือเท่ากับ 0.75 จะให้ผลดีต่อความต้องการ 60,000 คุณสร้างตัวเลขแบบสุ่ม 400 ตัวโดยการคัดลอกจาก C3 ไปยัง C4:C402 สูตร RAND() จากนั้นคุณจะสร้างเวอร์ชันทดลองใช้ 400 ครั้ง หรือการวนซ้ําของความต้องการปฏิทินโดยการคัดลอกจาก B3 ไปยัง B4:B402 สูตร VLOOKUP(C3,lookup,2) สูตรนี้ช่วยให้มั่นใจว่าตัวเลขสุ่มใดๆ ที่น้อยกว่า 0.10 สร้างความต้องการ 10,000 ตัวเลขสุ่มใดๆ ระหว่าง 0.10 และ 0.45 สร้างความต้องการ 20,000 และอื่นๆ ในช่วงเซลล์ F8:F11 ให้ใช้ฟังก์ชัน COUNTIF เพื่อกําหนดเศษส่วนของการคํานวณซ้ํา 400 ครั้งของเราที่ให้ผลกับความต้องการแต่ละรายการ เมื่อเรากด F9 เพื่อคํานวณตัวเลขแบบสุ่มใหม่ ความน่าจะเป็นที่จําลองได้จะใกล้เคียงกับความน่าจะเป็นตามมาของเรา

ถ้าคุณพิมพ์ในเซลล์ใดๆ สูตร NORMINV(rand(),mu,sigma) คุณจะสร้างค่าจําลองของตัวแปรสุ่มปกติที่มี sigmaค่าเฉลี่ยและค่าเบี่ยงเบนมาตรฐาน กระบวนการนี้จะแสดงในไฟล์ Normalsim.xlsx ซึ่งแสดงในรูป 60-3

สมมติว่าเราต้องการจําลองการทดลองหรือการวนซ้ํา 400 ครั้งสําหรับตัวแปรสุ่มปกติที่มีค่าเฉลี่ย 40,000 และการเบี่ยงเบนมาตรฐาน 10,000 (คุณสามารถพิมพ์ค่าเหล่านี้ในเซลล์ E1 และ E2 และตั้งชื่อ ค่าเฉลี่ย ของเซลล์เหล่านี้และ sigma ตามลําดับ) การคัดลอกสูตร =RAND() จาก C4 ไปยัง C5:C403 จะสร้างตัวเลขสุ่มที่แตกต่างกัน 400 ตัว การคัดลอกจาก B4 ไปยัง B5:B403 สูตร NORMINV(C4,mean,sigma) สร้างค่าทดลองที่ต่างกัน 400 ค่าจากตัวแปรสุ่มปกติที่มีค่าเฉลี่ย 40,000 และค่าเบี่ยงเบนมาตรฐาน 10,000 เมื่อเรากดแป้น F9 เพื่อคํานวณตัวเลขแบบสุ่มใหม่ ค่าเฉลี่ยยังคงใกล้เคียงกับ 40,000 และค่าเบี่ยงเบนมาตรฐานใกล้เคียงกับ 10,000

โดยหลักแล้ว สูตร NORMINV(p,mu,sigma) จะสร้างเปอร์เซ็นต์ไทล์ pเป็นเปอร์เซ็นต์ไทล์ที่ 3 ของตัวแปรสุ่มปกติที่มีค่าเฉลี่ย mu และ sigma ค่าเบี่ยงเบนมาตรฐาน ตัวอย่างเช่น ตัวเลขสุ่ม 0.77 ในเซลล์ C4 (ดูรูป 60-3) สร้างในเซลล์ B4 โดยประมาณเปอร์เซ็นต์ไทล์ที่ 77 ของตัวแปรสุ่มปกติที่มีค่าเฉลี่ย 40,000 และค่าเบี่ยงเบนมาตรฐานเท่ากับ 10,000

ในส่วนนี้คุณจะเห็นว่าการจําลอง Monte Carlo สามารถใช้เป็นเครื่องมือในการตัดสินใจได้อย่างไร สมมติว่าความต้องการบัตรวันวาเลนไทน์ถูกควบคุมโดยตัวแปรสุ่มแยกต่อไปนี้:

อุปสงค์	ความน่าจะเป็น
10,000	0.10
20,000	0.35
40,000	0.3
60,000	0.25

บัตรอวยพรขายในราคา $4.00 และต้นทุนผันแปรของการผลิตแต่ละบัตรคือ $ 1.50 ทิ้งบัตรจะต้องทิ้งของที่ค่าใช้จ่ายของ $0.20 ต่อบัตร. ควรพิมพ์บัตรกี่ใบ

โดยทั่วไปเราจําลองปริมาณการผลิตที่เป็นไปได้แต่ละรายการ (10,000, 20,000, 40,000 หรือ 60,000) หลายครั้ง (ตัวอย่างเช่น 1000 รอบ) จากนั้นเราจะพิจารณาว่าปริมาณการสั่งซื้อใดที่ให้ผลกําไรเฉลี่ยสูงสุดจากการวนซ้ํา 1,000 ครั้ง คุณสามารถค้นหาข้อมูลสําหรับส่วนนี้ได้ในไฟล์ Valentine.xlsx ซึ่งแสดงในรูป 60-4 คุณกําหนดชื่อช่วงในเซลล์ B1:B11 ไปยังเซลล์ C1:C11 ช่วงเซลล์ G3:H6 ได้รับการกําหนด การค้นหาชื่อ ราคาขายและพารามิเตอร์ต้นทุนของเราจะถูกป้อนในเซลล์ C4:C6

คุณสามารถป้อนปริมาณการผลิตทดลอง (40,000 ในตัวอย่างนี้) จากนั้น ให้สร้างตัวเลขสุ่มในเซลล์ C2 ด้วยสูตร =RAND() ตามที่อธิบายไว้ก่อนหน้านี้ คุณจําลองความต้องการสําหรับการ์ดในเซลล์ C3 ด้วยสูตร VLOOKUP(rand,lookup,2) (ในสูตร VLOOKUP ค่า rand คือชื่อเซลล์ที่กําหนดให้กับเซลล์ C3 ไม่ใช่ฟังก์ชัน RAND)

จํานวนของหน่วยที่ขายได้มีขนาดเล็กลงของปริมาณการผลิตและความต้องการของเรา ในเซลล์ C8 คุณคํานวณรายได้ของเราด้วยสูตร MIN(ที่ผลิต,ความต้องการ)*unit_price ในเซลล์ C9 คุณสามารถคํานวณต้นทุนการผลิตทั้งหมดด้วยสูตรที่ผลิตได้*unit_prod_cost

ถ้าเราผลิตบัตรมากกว่าที่เป็นความต้องการจํานวนหน่วยที่เหลืออยู่เหนือเท่ากับการผลิตลบความต้องการ; มิฉะนั้นจะไม่มีหน่วยเหลืออยู่ เราคํานวณต้นทุนการกําจัดของเราในเซลล์ C10 ด้วยสูตร unit_disp_cost*IF(ผลิต>ความต้องการ,ผลิต-ความต้องการ,0) สุดท้าย ในเซลล์ C11 เราคํานวณกําไรของเราเป็นรายได้ - total_var_cost-total_disposing_cost

เราต้องการวิธีที่มีประสิทธิภาพในการกด F9 หลายครั้ง (ตัวอย่างเช่น 1000) สําหรับแต่ละปริมาณการผลิตและรวมกําไรที่คาดหวังของเราสําหรับแต่ละปริมาณ สถานการณ์นี้เป็นหนึ่งที่ตารางข้อมูลสองทางมาถึงการช่วยเหลือของเรา (ดูบทที่ 15 "การวิเคราะห์ระดับความลับด้วยตารางข้อมูล" สําหรับรายละเอียดเกี่ยวกับตารางข้อมูล) ตารางข้อมูลที่ใช้ในตัวอย่างนี้แสดงในรูป 60-5

ในช่วงเซลล์ A16:A1015 ให้ใส่ตัวเลข 1–1000 (สอดคล้องกับการทดลองใช้ 1000 ครั้งของเรา) วิธีง่ายๆ ในการสร้างค่าเหล่านี้คือการเริ่มต้นด้วยการใส่ 1 ในเซลล์ A16 เลือกเซลล์ จากนั้นบนแท็บ หน้าแรก ในกลุ่ม การแก้ไข ให้คลิก เติม แล้วเลือก ชุดข้อมูล เพื่อแสดงกล่องโต้ตอบ ชุดข้อมูล ในกล่องโต้ตอบ ชุดข้อมูล ที่แสดงในรูปที่ 60-6 ให้ใส่ Step Value เป็น 1 และค่า Stop เป็น 1000 ในพื้นที่ ชุดข้อมูลให้เลือกตัวเลือก คอลัมน์ แล้วคลิก ตกลง ตัวเลข 1–1000 จะถูกใส่ในคอลัมน์ A เริ่มต้นในเซลล์ A16

ต่อไปเราจะป้อนปริมาณการผลิตที่เป็นไปได้ของเรา (10,000, 20,000, 40,000, 60,000) ในเซลล์ B15:E15 เราต้องการคํานวณกําไรสําหรับหมายเลขทดลองใช้แต่ละหมายเลข (1 ถึง 1000) และปริมาณการผลิตแต่ละปริมาณ เราอ้างอิงสูตรสําหรับกําไร (คํานวณในเซลล์ C11) ในเซลล์ด้านบนซ้ายของตารางข้อมูลของเรา (A15) โดยการใส่ =C11

ขณะนี้เราพร้อมที่จะหลอก Excel ให้จําลองความต้องการ 1,000 ครั้งสําหรับแต่ละปริมาณการผลิต เลือกช่วงตาราง (A15:E1014) จากนั้นในกลุ่ม เครื่องมือข้อมูล บนแท็บ ข้อมูล ให้คลิก การวิเคราะห์แบบ What If แล้วเลือก ตารางข้อมูล เมื่อต้องการตั้งค่าตารางข้อมูลแบบสองทาง ให้เลือกปริมาณการผลิตของเรา (เซลล์ C1) เป็นเซลล์ป้อนค่าของแถว แล้วเลือกเซลล์ว่าง (เราเลือกเซลล์ I14) เป็น เซลล์ที่ใส่คอลัมน์ หลังจากคลิก ตกลง Excel จะจําลองค่าความต้องการ 1,000 รายการสําหรับปริมาณการสั่งซื้อแต่ละรายการ

เมื่อต้องการทําความเข้าใจว่าทําไมจึงใช้ได้ผล ให้พิจารณาค่าที่ตารางข้อมูลวางในช่วงเซลล์ C16:C1015 สําหรับแต่ละเซลล์เหล่านี้ Excel จะใช้ค่า 20,000 ในเซลล์ C1 ใน C16 ค่าเซลล์ที่ป้อนเข้าของคอลัมน์ 1 จะถูกวางในเซลล์ว่างและตัวเลขสุ่มในเซลล์ C2 จะคํานวณใหม่ กําไรที่สอดคล้องกันจะถูกบันทึกในเซลล์ C16 ค่าป้อนเข้าของเซลล์คอลัมน์ของ 2 จะถูกวางในเซลล์ว่าง และตัวเลขสุ่มใน C2 จะคํานวณใหม่ กําไรที่สอดคล้องกันจะถูกป้อนในเซลล์ C17

ด้วยการคัดลอกจากเซลล์ B13 ไปยัง C13:E13 สูตร AVERAGE(B16:B1015) เราจะคํานวณกําไรที่จําลองค่าเฉลี่ยสําหรับปริมาณการผลิตแต่ละปริมาณ ด้วยการคัดลอกจากเซลล์ B14 ไปยัง C14:E14 สูตร STDEV(B16:B1015) เราจะคํานวณค่าเบี่ยงเบนมาตรฐานของกําไรที่จําลองไว้ของเราสําหรับแต่ละปริมาณการสั่งซื้อ ทุกครั้งที่เรากด F9 มีการจําลองความต้องการซ้ํา 1000 ครั้งสําหรับแต่ละปริมาณการสั่งซื้อ การผลิตบัตร 40,000 ใบจะให้ผลกําไรที่มากที่สุด ดังนั้นจึงปรากฏว่าการผลิตการ์ด 40,000 ใบเป็นการตัดสินใจที่เหมาะสม

ผลกระทบของความเสี่ยงต่อการตัดสินใจของเรา      ถ้าเราผลิตได้ 20,000 ใบแทนที่จะเป็น 40,000 ใบ ผลกําไรที่คาดว่าจะลดลงประมาณ 22 เปอร์เซ็นต์ แต่ความเสี่ยงของเรา (ตามที่วัดโดยค่าเบี่ยงเบนมาตรฐานของกําไร) ลดลงเกือบ 73 เปอร์เซ็นต์ ดังนั้นหากเราละเลยความเสี่ยงอย่างมากการมีการ์ด 20,000 ใบอาจเป็นการตัดสินใจที่ถูกต้อง โดยบังเอิณผลิตการ์ด 10,000 ใบมักจะมีค่าเบี่ยงเบนมาตรฐาน 0 ใบเพราะถ้าเราผลิตไพ่ 10,000 ใบเราจะขายไพ่ทั้งหมดโดยไม่มีของเหลือเสมอ

ช่วงความเชื่อมั่นสําหรับกําไรเฉลี่ย      คําถามธรรมชาติที่จะถามในสถานการณ์นี้คือช่วงเวลาใดที่เรา 95 เปอร์เซ็นต์มั่นใจว่าผลกําไรเฉลี่ยที่แท้จริงจะลดลง? ช่วงเวลานี้เรียกว่า ช่วงความเชื่อมั่น 95 เปอร์เซ็นต์สําหรับกําไรเฉลี่ย ช่วงความเชื่อมั่น 95 เปอร์เซ็นต์สําหรับค่าเฉลี่ยของผลลัพธ์การจําลองจะถูกคํานวณโดยสูตรต่อไปนี้

ในเซลล์ J11 คุณคํานวณขีดจํากัดล่างสําหรับช่วงความเชื่อมั่น 95 เปอร์เซ็นต์บนกําไรเฉลี่ยเมื่อสร้างปฏิทิน 40,000 ปฏิทินด้วยสูตร D13–1.96*D14/SQRT(1000) ในเซลล์ J12 คุณสามารถคํานวณขีดจํากัดบนสําหรับช่วงความเชื่อมั่น 95 เปอร์เซ็นต์ของเราด้วยสูตร D13+1.96*D14/SQRT(1000) การคํานวณเหล่านี้จะแสดงในรูป 60-7

เราคือ 95 เปอร์เซ็นต์แน่ใจว่าผลกําไรเฉลี่ยของเราเมื่อมีการสั่งซื้อปฏิทิน 40,000 รายการอยู่ระหว่าง $ 56,687 และ $ 62,589

1. ตัวแทนจําหน่าย GMC เชื่อว่าความต้องการสําหรับ 2005 Envoys จะแจกจ่ายตามปกติโดยมีค่าเฉลี่ย 200 และค่าเบี่ยงเบนมาตรฐานของ 30. ค่าใช้จ่ายของเขาในการรับเอนโว่คือ $ 25,000 และเขาขาย Envoy ในราคา $ 40,000 ครึ่งหนึ่งของทุกเอนโว่ไม่ได้ขายในราคาเต็มสามารถขายได้ในราคา $30,000 เขากําลังพิจารณาการสั่งซื้อ 200, 220, 240, 260, 280 หรือ 300 Envoys เขาควรสั่งกี่คน?

2. ซูเปอร์มาร์เก็ตขนาดเล็กพยายามกําหนดจํานวนสําเนานิตยสาร People ที่พวกเขาควรสั่งซื้อในแต่ละสัปดาห์ พวกเขาเชื่อว่าความต้องการของ People อยู่ภายใต้ตัวแปรสุ่มแบบแยกต่อไปนี้:

   อุปสงค์	ความน่าจะเป็น
   15	0.10
   20	0.20
   25	0.30
   30	0.25
   35	0.15

3. ซูเปอร์มาร์เก็ตจ่าย $ 1.00 สําหรับแต่ละสําเนาของ People และขายมันสําหรับ $ 1.95 สําเนาที่ไม่ได้ขายแต่ละรายการสามารถส่งคืนได้ในราคา $0.50 ใบสั่งซื้อของ People ควรมีจํานวนเท่าใด