מבוא לסימולציה מונטה קרלו ב- Excel

• מי משתמש בסימולציה מונטה קרלו?

• מה קורה בעת הקלדת =RAND() בתא?

• כיצד ניתן לדמות ערכים של משתנה אקראי נפרד?

• כיצד ניתן לדמות ערכים של משתנה אקראי רגיל?

• כיצד יכולה חברה של כרטיסי ברכה לקבוע כמה כרטיסים להפיק?

ברצוננו להעריך במדויק את ההסתברות לאירועים לא בטוחים. לדוגמה, מהי ההסתברות לכך שלתזרימי המזומנים של מוצר חדש יהיה ערך נטו קיים חיובי (NPV)? מהו גורם הסיכון של תיק ההשקעות שלנו? הדמיית מונטה קרלו מאפשרת לנו להציג מצבים של חוסר ודאות ולאחר מכן להפעיל אותם במחשב אלפי פעמים.

בחמשת הפרקים הבאים, תראה דוגמאות לאופן שבו תוכל להשתמש ב- Excel כדי לבצע הדמיות של מונטה קרלו.

חברות רבות משתמשות בסימולציה מונטה קרלו כחלק חשוב מתהליך קבלת ההחלטות שלהן. הנה כמה דוגמאות.

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb ו- Eli Lilly משתמשים בסימולציה כדי להעריך הן את ההחזר הממוצע והן את גורם הסיכון של מוצרים חדשים. ב- GM, מידע זה משמש את המנכ"ל כדי לקבוע אילו מוצרים מגיעים לשוק.

• GM משתמש בסימולציה לפעילויות כגון חיזוי הכנסה נטו עבור הארגון, חיזוי עלויות מבניות ורכשות, וקבע את הנגישות שלו לסיכונים מסוגים שונים (כגון שינויים בקצב הריבית ותנודות שער החליפין).

• לילי משתמשת בסימולציה כדי לקבוע את קיבולת הצמח האופטימלי עבור כל תרופה.

• Proctor ו- Gamble משתמשים בסימולציה כדי לתכנת ולתכנת באופן מיטבי סיכון של מטבע זר.

• סיאר משתמש בסימולציה כדי לקבוע כמה יחידות של כל קו מוצר יש להזמין מהספקים - לדוגמה, מספר הזוגות של מכנסי עיגון שיש להזמין השנה.

• חברות הנפט והתרופות משתמשות בסימולציה כדי להוסיף ערך ל"אפשרויות אמיתיות", כגון הערך של אפשרות להרחיב, ליצור חוזה או לדחות פרוייקט.

• המתכננים הפיננסיים משתמשים בסימולציית מונטה קרלו כדי לקבוע אסטרטגיות השקעה מיטביות לפרישה של לקוחותיהם.

בעת הקלדת הנוסחה =RAND() בתא, אתה מקבל מספר שמניח באופן שווה ערך בין 0 ל- 1. לפיכך, כ- 25 אחוזים מהשעה, אתה אמור לקבל מספר קטן או שווה ל- 0.25; סביב 10 אחוזים מהשעה שבהם אתה אמור לקבל מספר שהוא 0.90 לפחות וכן הלאה. כדי להדגים כיצד הפונקציה RAND פועלת, עיין בקובץ Randdemo.xlsx, המוצג באיור 60-1.

תחילה, העתק מתא C3 לתא C4:C402 את הנוסחה =RAND(). לאחר מכן תן לטווח את השם C3:C402 Data. לאחר מכן, בעמודה 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), הוא לא מציין דבר על הערכים של המספרים האקראיים האחרים שנוצרו.

נניח שהביקוש ללוח שנה מנוהל על-ידי המשתנה האקראי הדיסקרטי הבא:

לפי דרישה	Probability
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), תפיק ערך מדומה של משתנה אקראי רגיל בעל mu ממוצע וסטיית תקן 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.

למעשה, עבור מספר אקראי x, הנוסחה NORMINV(p,mu,sigma) מייצרת את האחוזון ה- pשל משתנה אקראי רגיל עם mu ממוצע וסטיית תקן sigma. לדוגמה, המספר האקראי 0.77 בתא C4 (ראה איור 60-3) נוצר בתא B4 כאחוזון ה- 77 של משתנה אקראי רגיל עם ממוצע של 40,000 וסטיית תקן של 10,000.

בסעיף זה, תראה איך הדמיות מונטה קרלו יכולות לשמש ככלי קבלת החלטות. נניח שהביקוש לכרטיס ליום האהבה מנוהל על-ידי המשתנה האקראי הדיסקרטי הבא:

לפי דרישה	Probability
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 איתאציות). לאחר מכן, אנו קובעים איזו כמות הזמנות תניב את הרווח הממוצע המרבי מעל 1000 איחרות. באפשרותך למצוא את הנתונים עבור מקטע זה בקובץ Valentine.xlsx, המוצג באיור 60-4. אתה מקצה את שמות הטווחים בתאים B1:B11 לתאים C1:C11. טווח התאים G3:H6 מוקצה לבדיקת השם. הפרמטרים של מחיר המכירה והעלות שלנו מוזנים בתאים C4:C6.

באפשרותך להזין כמות ייצור של ניסיון (40,000 בדוגמה זו) בתא C1. לאחר מכן, צור מספר אקראי בתא C2 באמצעות הנוסחה =RAND(). כפי שתואר קודם לכן, אתה מדמה את הדרישה עבור הכרטיס בתא C3 עם הנוסחה VLOOKUP(rand,lookup,2). (בנוסחת VLOOKUP, rand הוא שם התא שהוקצה לתא C3, ולא הפונקציה RAND.)

מספר היחידות שנמכרו הוא הכמות הקטנה יותר של כמות הייצור והביקוש שלנו. בתא C8, אתה מחשב את ההכנסות שלנו באמצעות הנוסחה MIN(produced,demand)*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, הזן ערך שלב של 1 וערך עצירה של 1000. באזור סידרה ב , בחר את האפשרות עמודות ולאחר מכן לחץ על אישור. המספרים 1-1000 מוזנים בעמודה A החל מתא A16.

בשלב הבא נזין את כמויות הייצור האפשריות שלנו (10,000, 20,000, 40,000, 60,000) בתאים B15:E15. אנחנו רוצים לחשב רווח עבור כל מספר ניסיון (1 עד 1000) וכל כמות ייצור. אנו מפנים לנוסחה לרווח (מחושב בתא C11) בתא הימני העליון של טבלת הנתונים שלנו (A15) על-ידי הזנת =C11.

כעת אנחנו מוכנים לגרום ל- Excel לדמות 1000 איפראציות של דרישה עבור כל כמות ייצור. בחר את טווח הטבלה (A15:E1014) ולאחר מכן, בקבוצה כלי נתונים בכרטיסיה נתונים, לחץ על ניתוח 'מה-אם' ולאחר מכן בחר טבלת נתונים. כדי להגדיר טבלת נתונים דו-כיוונית, בחר את כמות הייצור (תא C1) כתא הזנת השורה ובחר תא ריק כלשהו (בחרנו בתא I14) כתא הקלט של העמודה. לאחר לחיצה על אישור, Excel מדמה 1000 ערכי ביקוש עבור כל כמות הזמנה.

כדי להבין מדוע זה עובד, שקול את הערכים הממוקמים על-ידי טבלת הנתונים בטווח התאים 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 דולר, והוא מוכר שליחה ב-40,000 דולר. מחצית מכל ה Envoys לא נמכר במחיר מלא ניתן למכור עבור $30,000. הוא שוקל להזמין 200, 220, 240, 260, 280 או 300 Envoys. כמה הוא צריך להזמין?

2. סופרמרקט קטן מנסה לקבוע כמה עותקים של אנשים צריך להזמין מדי שבוע. הם מאמינים שהביקוש שלהם אנשים על ידי המשתנה האקראי הדיסקרטי הבא:

   לפי דרישה	Probability
   15	‎0.10
   20	0.20
   25	0.30
   30	0.25
   35	0.15

3. הסופרמרקט משלם $1.00 עבור כל עותק של אנשים ומוכר אותו ב-$1.95. ניתן להחזיר כל עותק שלא מוחזר עבור $0.50. כמה עותקים של אנשים לשמור את ההזמנה מהחנות?