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

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

מאמר זה הותאם לניתוח נתונים ומידול עסקי של Microsoft Excel על-ידי וויין ל. וינסטון.

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

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

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

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

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

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

הערה:  הדמיית השם Monte Carlo מגיעה מסימולציות המחשב שבוצעו במהלך 1930 ו-1940 כדי לאמוד את ההסתברות שתגובת השרשרת הדרושה לפצצת אטום תעבוד בהצלחה. הפיסיקאים המעורבים בעבודה זו היו מעריצים גדולים של הימורים, ולכן הם נתנו את הסימולציות שם הקוד מונטה קרלו.

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

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

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

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

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

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

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

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

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

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

תמונת ספר

הערה:  בעת פתיחת הקובץ Randdemo. xlsx, לא תראה את אותם מספרים אקראיים המוצגים באיור 60-1. הפונקציה RAND תמיד מחשבת מחדש באופן אוטומטי את המספרים שהוא יוצר בעת פתיחת גליון עבודה או בעת הזנת מידע חדש בגליון העבודה.

תחילה, העתק את התא 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 וסטיית תקן סיגמא. הליך זה מומחש בקובץ Normalsim. xlsx, המוצג באיור 60-3.

תמונת ספר

נניח שברצונך לדמות מבחנים של 400, או איטראציות, עבור משתנה אקראי רגיל עם ממוצע של 40,000 וסטיית תקן של 10,000. (באפשרותך להקליד ערכים אלה בתאים E1 ו-E2, ולציין את המשמעות של תאים אלה וסיגמא, בהתאמה.) העתקת הנוסחה = RAND () מ-C4 עד C5: C403 מפיק 400 מספרים אקראיים שונים. העתקה מ-B4 ל-B5: B403 הנוסחה NORMINV (C4, ממוצע, sigma) יוצרת 400 ערכי ניסיון שונים ממשתנה אקראי רגיל עם ממוצע של 40,000 וסטיית תקן של 10,000. כאשר נלחץ על מקש F9 כדי לחשב מחדש את המספרים האקראיים, הממוצע נשאר קרוב ל-40,000 וסטיית התקן מתקרבת ל-10,000.

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

בסעיף זה, תוכל לראות כיצד ניתן להשתמש בסימולציה Monte Carlo ככלי לקבלת החלטות. נניח שהביקוש לכרטיס יום האהבה כפוף למשתנה האקראי הבא:

דרישה

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. באפשרותך למצוא את הנתונים עבור מקטע זה בקובץ הוולנטיין. 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.)

מספר היחידות שנמכרות קטן מכמות הייצור והביקוש שלנו. ב-cell C8, אתה מחשב את ההכנסות שלנו עם הנוסחה MIN (מופק, דורש) * unit_price. בתא C9, אתה מחשב את עלות הייצור הכוללת עם הנוסחה המופקת * unit_prod_cost.

אם אנו מייצרים קלפים רבים יותר מאלה הדרושים, מספר היחידות שנותרו שווה לביקוש ייצור חיסור; אחרת, לא יישארו יחידות. אנו מחשבים את עלות השירות בתא C10 עם הנוסחה unit_disp_cost * IF (produced>demand, הופק – דרישה, 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 החל ב-cell 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.

על-ידי העתקה מ-cell B13 ל-C13: E13 את ממוצע הנוסחאות (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, אנו מוכרים תמיד את כולם ללא שאריות.

הערה:  בחוברת עבודה זו, האפשרות ' חישוב ' מוגדרת אוטומטית למעט עבור טבלאות. (השתמש בפקודה ' חישוב ' בקבוצה ' חישוב ' בכרטיסיה ' נוסחאות '.) הגדרה זו מבטיחה שטבלת הנתונים שלנו לא תיחשב מחדש אלא אם כן נלחץ על F9, וזה רעיון טוב מכיוון שטבלת נתונים גדולה תאט את העבודה שלך אם היא תיחשב מחדש בכל פעם שתקליד משהו בגליון העבודה. שים לב שבדוגמה זו, בכל פעם שתקיש F9, הרווח הממוצע ישתנה. הדבר קורה מאחר שכל פעם שתקיש F9, רצף שונה של 1000 מספרים אקראיים משמש להפקת דרישות עבור כל כמות הזמנה.

מרווח ביטחון עבור רווח משמעותי     שאלה טבעית שתשאל במצב זה היא, לאיזה מרווח זמן אנו 95 אחוזים בטוחים שהרווח האמיתי של הממוצע ייפול? מרווח זה נקרא מרווח זמן הבטחון של 95 אחוז עבור רווח ממוצע. הנוסחה הבאה מתבצעת מרווח ביטחון באחוזי 95 עבור הממוצע של כל פלט סימולציה:

תמונת ספר

ב-cell J11, אתה מחשב את המגבלה הנמוכה ביותר עבור מרווח האמון של 95 אחוז ברווח הממוצע כאשר לוחות השנה של 40,000 מיוצרים עם הנוסחה D13 – 1.96 * D14/SQRT (1000). ב-cell J12, אתה מחשב את המגבלה העליונה עבור מרווחי האמון של 95% עם הנוסחה D13 + 1.96 * D14/SQRT (1000). חישובים אלה מוצגים באיור 60-7.

תמונת ספר

אנו 95 אחוזים בטוחים שהרווח הממוצע שלנו כאשר לוחות השנה של 40,000 מסודרים בין $56,687 ו-$62,589.

  1. דילר GMC מאמין שביקוש עבור שליחי 2005 יחולק בדרך כלל עם ממוצע של 200 וסטיית תקן של 30. העלות שלו לקבלת שליח היא $25,000 והוא מוכר שליח עבור $40,000. חצי מכל השליחים שאינם נמכרים במחיר מלא ניתנים למכירה עבור $30,000. הוא שוקל להורות על 200, 220, 240, 260, 280 או שליחי 300. כמה פעמים עליו להזמין?

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

    דרישה

    Probability

    15

    ‎0.10

    20

    0.20

    25

    0.30

    30

    0.25

    35

    0.15

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

זקוק לעזרה נוספת?

ניתן לשאול תמיד מומחה ב- Excel Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×