ניתוח רגישות באקסל (גליון נתונים לדוגמה)

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

קבלת תוצאות מרובות עם טבלת נתונים

יכולות גליון נתונים הן מרכיבים של ניתוח מה-אם - לרוב באמצעות Microsoft Excel. זהו השם השני לניתוח רגישות.

סקירה כללית

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

יסודות על טבלאות נתונים

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

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

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

יצירת טבלת נתונים עם משתנה אחד

שקול את שיטת הניתוח באמצעות הדוגמה של חנות ספרים קטנה עם רק 100 ספרים במלאי. חלקם יכולים להימכר יותר ביוקר (50$), השאר יעלו לקונים פחות (20$). מחושבת סך ההכנסה ממכירת כל הסחורה - הבעלים החליט שהוא ימכור 60% מהספרים במחיר גבוה. אתה צריך לברר כיצד ההכנסה תגדל אם תעלה את המחיר של נפח גדול יותר של סחורות - 70%, וכן הלאה.

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

1. בחר תא פנוי הרחק מקצה הגיליון וכתוב בו את הנוסחה: =תא של סך ההכנסה. לדוגמה, אם הכנסה כתובה בתא C14 (מסומן ייעוד אקראי), עליך לכתוב את זה: =S14.
2. אנו כותבים את אחוז נפח הסחורה בעמודה משמאל לתא זה - לא מתחתיו, זה מאוד חשוב.
3. אנו בוחרים את טווח התאים שבו נמצאים עמודת האחוזים והקישור לסך ההכנסה.

4. אנו מוצאים בלשונית "נתונים" את הפריט "מה אם ניתוח" ונלחץ עליו - בתפריט שנפתח, בחר באפשרות "טבלת נתונים".

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

לאחר לחיצה על כפתור "אישור" בחלון שבו הוזנו הנתונים לצורך הידור הטבלה, יופיעו בשורות תוצאות החישובים.

הוספת נוסחה לטבלת נתונים משתנה בודדת

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

1. בחר שוב את טווח התאים, אך כעת הוא אמור לכלול את הנוסחה החדשה.
2. פתח את תפריט הניתוח "מה אם" ובחר "גליון נתונים".
3. אנו מוסיפים נוסחה חדשה לשדה המתאים בשורות או בעמודות, בהתאם לכיוון הצלחת.

צור טבלת נתונים עם שני משתנים

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

1. כתוב אפשרויות מחיר בשורה אחת עם קישור להכנסה - תא אחד לכל מחיר.
2. בחר טווח של תאים.

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

כתוצאה מכך, כל הטבלה מלאה בסכומי הכנסה אפשרית בתנאים שונים למכירת סחורות.

האץ את החישובים עבור גליונות עבודה המכילים טבלאות נתונים

אם אתה צריך חישובים מהירים בטבלת נתונים שאינם מפעילים חישוב מחדש של חוברת העבודה כולה, יש כמה דברים שאתה יכול לעשות כדי לזרז את התהליך.

1. פתח את חלון האפשרויות, בחר את הפריט "נוסחאות" בתפריט בצד ימין.
2. בחר את הפריט "אוטומטי, למעט טבלאות נתונים" בסעיף "חישובים בחוברת העבודה".

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

כלים נוספים לביצוע ניתוח רגישות

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

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

ניתוח רגישות של פרויקט השקעה באקסל

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

שיטת ניתוח רגישות להשקעה

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

1. שווי נוכחי נטו - מחושב על ידי הפחתת סכום ההשקעה מסכום ההכנסה.
2. שיעור תשואה / רווח פנימי – מציין כמה רווח נדרש לקבל מהשקעה בשנה.
3. יחס ההחזר הוא היחס בין כל הרווחים להשקעה הראשונית.
4. מדד רווח מוזל – מצביע על יעילות ההשקעה.

נוסחה

ניתן לחשב את רגישות ההטמעה באמצעות נוסחה זו: שינוי בפרמטר פלט ב-% / Change in parameter input in %.

פרמטרי הפלט והקלט יכולים להיות הערכים שתוארו קודם לכן.

1. אתה צריך לדעת את התוצאה בתנאים סטנדרטיים.
2. אנחנו מחליפים את אחד המשתנים ועוקבים אחר השינויים בתוצאה.
3. אנו מחשבים את אחוז השינוי של שני הפרמטרים ביחס לתנאים שנקבעו.
4. נכניס את האחוזים שהתקבלו לנוסחה וקובעים את הרגישות.

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

להבנה טובה יותר של מתודולוגיית הניתוח, יש צורך בדוגמה. בואו ננתח את הפרויקט עם הנתונים הידועים הבאים:

1. מלא את הטבלה כדי לנתח את הפרויקט עליה.

2. אנו מחשבים את תזרים המזומנים באמצעות הפונקציה OFFSET. בשלב הראשוני התזרים שווה להשקעות. לאחר מכן, אנו מיישמים את הנוסחה: =IF(OFFSET(Number,1;)=2;SUM(Inflow 1:Outflow 1); SUM(Inflow 1:Outflow 1)+$B5 $)
   ייעודי תאים בנוסחה עשויים להיות שונים, בהתאם לפריסת הטבלה. בסוף מתווסף הערך מהנתונים הראשוניים – ערך ההצלה.

3. אנו קובעים את התקופה עבורה ישתלם הפרויקט. עבור התקופה הראשונית, אנו משתמשים בנוסחה זו: =SUMMESLY(G7: ג17;»<0 אינץ'). טווח התאים הוא עמודת תזרים המזומנים. לתקופות נוספות, אנו מיישמים את הנוסחה הזו: =תקופה ראשונית+IF(First e.stream>0; First e.stream;0). הפרויקט נמצא בנקודת האיזון בעוד 4 שנים.

4. אנו יוצרים עמודה עבור המספרים של התקופות שבהן הפרויקט משתלם.

5. אנו מחשבים את ההחזר על ההשקעה. יש צורך לעשות ביטוי שבו הרווח בפרק זמן מסוים מחולק בהשקעה הראשונית.

6. אנו קובעים את מקדם ההיוון באמצעות נוסחה זו: =1/(1+דיסק.%) ^מספר.

7. אנו מחשבים את הערך הנוכחי באמצעות הכפל - תזרים המזומנים מוכפל במקדם ההיוון.

8. בואו לחשב PI (מדד רווחיות). הערך הנוכחי לאורך זמן מחולק בהשקעה בתחילת הפרויקט.

9. בואו נגדיר את שיעור התשואה הפנימי באמצעות הפונקציה IRR: =IRR(טווח תזרים מזומנים).

ניתוח רגישות להשקעה באמצעות גיליון נתונים

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

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

טיפולוגיה נוספת של ניתוח רגישות היא ניתוח גורמים וניתוח שונות. הסוג הראשון מגדיר את הקשר בין מספרים, השני חושף את התלות של משתנה אחד באחרים.

ANOVA באקסל

מטרת ניתוח כזה היא לחלק את השונות של הערך לשלושה מרכיבים:

1. שונות כתוצאה מהשפעת ערכים אחרים.
2. שינויים עקב היחס בין הערכים המשפיעים עליו.
3. שינויים אקראיים.

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

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

1. מצא את הכלי ניתוח נתונים בלשונית נתונים ופתח את החלון שלו. ברשימה, עליך לבחור ניתוח חד כיווני של שונות.

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

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

ניתוח גורמים באקסל: דוגמה

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

1. עלינו לברר לאילו הביקוש לסחורות גדל הכי הרבה במהלך החודש השני. אנו עורכים טבלה חדשה כדי לקבוע את הצמיחה והירידה בביקוש. הצמיחה מחושבת באמצעות נוסחה זו: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). הקטנת נוסחה: =IF(צמיחה=0; דרישה 1- דרישה 2;0).

2. חשב את הגידול בביקוש לסחורות באחוזים: =IF(צמיחה/תוצאה 2 =0; ירידה/תוצאה 2; צמיחה/תוצאה 2).

3. בואו ניצור תרשים לבהירות - בחר טווח של תאים וניצור היסטוגרמה דרך הכרטיסייה "הוספה". בהגדרות, עליך להסיר את המילוי, ניתן לעשות זאת באמצעות הכלי Format Data Series.

ניתוח שונות דו כיוונית באקסל

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

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

ערך F גדול מה-F קריטי, כלומר הרצפה משפיעה על מהירות התגובה לצליל.

סיכום

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