כיצד חברה יכולה להשתמש ב- Solver כדי לקבוע אילו פרוייקטים היא צריכה לבצע?

בכל שנה, חברה כמו אלי לילי צריכה לקבוע אילו תרופות לפתח; חברה כמו Microsoft, אילו תוכניות לפתח; חברה כמו Proctor & גמבל, אשר מוצרי צריכה חדשים לפתח. התכונה Solver ב- Excel יכולה לעזור לחברה לקבל החלטות אלה.

רוב התאגידים רוצים לבצע פרוייקטים שתורמים את הערך הנוכחי הנטוי (NPV) הגדול ביותר, בכפוף למשאבים מוגבלים (בדרך כלל הון ועבודה). בוא נגיד שהחברה לפיתוח תוכנה מנסה לקבוע אילו מ- 20 פרוייקטי תוכנה היא אמורה לבצע. ה- NPV (במיליוני דולרים) שתרמה על-ידי כל פרוייקט וכן על-ידי ההון (במיליוני דולרים) ומספר המתכנתים הדרושים במהלך כל אחת משלוש השנים הבאות ניתן בגליון העבודה ' מודל בסיסי' בקובץ Capbudget.xlsx, המוצג באיור 30-1 בעמוד הבא. לדוגמה, Project 2 תניב 908 מיליון דולר. הוא דורש 151 מיליון דולר במהלך שנה 1, 269 מיליון דולר במהלך שנה 2, ו- 248 מיליון דולר במהלך שנה 3. Project 2 דורש 139 מתכנתים במהלך שנה 1, 86 מתכנתים במהלך שנה 2 ו- 83 מתכנתים במהלך שנה 3. תאים E4:G4 מציגים את ההון (במיליוני דולרים) הזמינים במהלך כל אחת משלוש השנים, ותאים H4:J4 מציינים כמה מתכנתים זמינים. לדוגמה, במהלך שנה 1 עד 2.5 מיליארד דולר בהון ו- 900 מתכנתים זמינים.

על החברה להחליט אם עליו לבצע כל פרוייקט. נניח שלא נוכל לבצע חלק קטן מפרוייקט תוכנה; אם נקצה 0.5 מהמשאבים הדרושים, לדוגמה, תהיה לנו תוכנית ללא עבודה שתחזיר לנו הכנסות של $0!

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

תמונת ספר

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

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

  • שינוי תאים. אנו מחפשים תא משתנה בינארי 0 או 1 עבור כל פרוייקט. אני כבר ממוקם תאים אלה בטווח A6:A25 (ו- doit טווח). לדוגמה, תא 1 בתא A6 מציין שאנו מתחייבים Project 1; 0 בתא C6 מציין שאנו לא מתחייבים על Project 1.

  • אילוצים. עלינו לוודא כי עבור כל שנה t (t=1, 2, 3), ההון של Year t המשמש הוא קטן או שווה להון של Year t הזמין, ועבודה שנתית בשימוש היא פחות או שווה ל- Year t labor available.

כפי שאתה יכול לראות, גליון העבודה שלנו חייב לחשב עבור כל בחירה של פרוייקטים ה- NPV, ההון המשמש מדי שנה, המתכנתים השתמשו בכל שנה. בתא B2, אני משתמש בנוסחה SUMPRODUCT(doit,NPV) כדי לחשב את ה- NPV הכולל שנוצר על-ידי פרוייקטים נבחרים. (שם הטווח NPV מפנה לטווח C6:C25.) עבור כל פרוייקט עם 1 ב- עמודה A, נוסחה זו בוחרת את ה- NPV של הפרוייקט, עבור כל פרוייקט עם 0 ב- עמודה A, נוסחה זו אינה מרים את ה- NPV של הפרוייקט. לכן, אנו יכולים לחשב את ה- NPV של כל הפרוייקטים, ותא היעד שלנו הוא ליניארי מכיוון שהוא מחושב על-ידי סיכום מונחים העוקבים אחר הטופס (שינוי תא)*(קבוע). באופן דומה, אני מחשב את ההון המשמש מדי שנה ואת העבודה המשמשת מדי שנה על-ידי העתקה מ- E2 ל- F2:J2 הנוסחה SUMPRODUCT(doit,E6:E25).

כעת אני ממלא את תיבת הדו-שיח Solver Parameters כפי שמוצג באיור 30.2.

תמונת ספר

מטרתנו היא להגדיל את NPV של פרוייקטים נבחרים (תא B2). התאים המשתנים שלנו (הטווח הנקרא doit) הם התאים המשתנים הבינאריים עבור כל פרוייקט. האילוץ E2:J2<=E4:J4 מבטיח שבמהלך כל שנה, ההון והעבודה המשמשים הם פחות או שווים להון ולעבודה הזמינים. כדי להוסיף את האילוץ שהופך את התאים המשתנים ל בינאריים, אני לוחץ על הוסף בתיבת הדו-שיח Solver Parameters ולאחר מכן בחר סל מהרשימה באמצע תיבת הדו-שיח. תיבת הדו-שיח הוספת אילוץ אמורה להופיע כפי שמוצג באיור 30.3.

תמונת ספר

המודל שלנו הוא ליניארי מכיוון שתא היעד מחושב כסכום המונחים הכוללים את הטופס (תא משתנה )*(קבוע) ומ מכיוון שמגבלות השימוש במשאבים מחושבות על-ידי השוואת סכום (שינוי תאים)*(קבועים) קבועים.

כאשר תיבת הדו-שיח Solver Parameters מלאה, לחץ על פתור והתוצאות מוצגות מוקדם יותר באיור 30.1. החברה יכולה להשיג NPV מרבי של 9,293 מיליון דולר (9.293 מיליארד דולר) על-ידי בחירת Projects 2, 3, 6-10, 14-16, 19 ו- 20.

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

באפשרותך למצוא דוגמה זו בגליון העבודה אם 3 ולאחר מכן 4 בקובץ Capbudget.xlsx, המוצג באיור 30.4. תא L9 מפנה לערך הבינארי הקשור ל- Project 3 ולתא L12 לערך הבינארי הקשור ל- Project 4. על-ידי הוספת האילוץ L9<=L12, אם אנו בוחרים ב- Project 3, L9 שווה ל- 1 והמגבלות שלנו יכריחו את L12 (Project 4 בינארי) לשוויון 1. האילוץ שלנו חייב גם להשאיר את הערך הבינארי בתא Project 4 בלתי מוגבל אם לא נבחר Project 3. אם לא נבחר באפשרות Project 3, L9 שווה ל- 0 ואילוץ זה מאפשר Project 4 בינארי לשוויון 0 או 1, וזה מה שאנחנו רוצים. הפתרון האופטימלי החדש מוצג באיור 30.4.

תמונת ספר

פתרון מיטבי חדש מחושב אם בחירה ב- Project 3 פירושה מוכרחים לבחור גם Project 4. כעת נניח שנוכל לעשות ארבעה פרוייקטים בלבד מבין פרוייקטים 1 עד 10. (עיין בגליון העבודה של 4 מתוך P1–P10 לכל היותר , המוצג באיור 30.5). בתא L8, אנו מחשבים את סכום הערכים הבינאריים המשויכים ל- Projects 1 עד 10 עם הנוסחה SUM(A6:A15). לאחר מכן נוסיף את האילוץ L8<=L10, אשר מבטיח כי, לכל היותר, 4 מתוך 10 הפרוייקטים הראשונים נבחרו. הפתרון האופטימלי החדש מוצג באיור 30.5. ה- NPV ירד ל- $9.014 מיליארד דולר.

תמונת ספר

מודלים ליניאריים של Solver שבו חלק מהתאים המשתנים או כולם נדרשים להיות בינאריים או מספר שלם בדרך כלל קשים יותר לפתרון מאשר מודלים ליניאריים שבו כל התאים המשתנים מורשים להיות שברים. מסיבה זו, לעתים קרובות אנו מרוצים מפתרון קרוב-אופטימלי לבעיה בתכנות בינארי או שלם. אם מודל Solver פועל במשך זמן רב, ייתכן שתרצה לשקול להתאים את ההגדרה רגישות בתיבת הדו-שיח אפשרויות Solver. (ראה איור 30.6). לדוגמה, הגדרה של 'סובלנות' של 0.5% פירושה ש- Solver יפסיק בפעם הראשונה שהוא ימצא פתרון מעשי שנמצא בטווח של 0.5 אחוז מערך התא האופטימלי האופטימלי של היעד התיאורטי (ערך התא האופטימלי האופטימלי של היעד התיאורטי הוא ערך היעד האופטימלי שנמצא כאשר האילוצים הבינאריים והתאים השלם מושמטים). לעתים קרובות אנו עומדים בפני בחירה בין מציאת תשובה בתוך 10 אחוז מהאופטימלית תוך 10 דקות או מציאת פתרון מיטבי תוך שבועיים של זמן מחשב! ערך הסובלנות המוגדר כברירת מחדל הוא 0.05%, כלומר Solver מפסיק כאשר הוא מוצא ערך תא יעד בתוך 0.05 אחוז מערך התא האופטימלי האופטימלי התיאורטי.

תמונת ספר

  1. לחברה יש תשעה פרוייקטים תחת שיקול. ה- NPV שנוסף על-ידי כל פרוייקט וההון הנדרש על-ידי כל פרוייקט במהלך השנתיים הבאות מוצג בטבלה הבאה. (כל המספרים נמצאים במיליונים.) לדוגמה, Project 1 יוסיף 14 מיליון דולר ב- NPV וידרוש הוצאות של 12 מיליון דולר במהלך שנה 1 ו- $3 מיליון בשנה 2. במהלך שנה 1, 50 מיליון דולר בהון זמינים עבור פרוייקטים, ו- 20 מיליון דולר זמינים במהלך שנה 2.

NPV

הוצאה לשנה 1

הוצאה לשנה 2

Project 1

14

12

3

Project 2

17

54

7

Project 3

17

6

6

Project 4

15

6

2

Project 5

40

30

35

Project 6

12

6

6

Project 7

14

48

4

Project 8

10

36

3

Project 9

12

18

3

  • אם לא נוכל לבצע חלק קטן מפרוייקט, אך עלינו לבצע את הפרוייקט כולו או אף אחד מהם, כיצד נוכל להגדיל את NPV?

  • נניח שאם Project 4 מתבצע, Project 5 חייב להתבצע. כיצד ניתן להגדיל את NPV?

  • חברת פרסום מנסה לקבוע איזה מתוך 36 ספרים היא אמורה לפרסם השנה. קובץ Pressdata.xlsx מספק את המידע הבא אודות כל ספר:

    • עלויות הכנסות ופיתוח צפויה (באלפי דולרים)

    • עמודים בכל ספר

    • בין אם הספר מתוכנן לקהל של מפתחי תוכנה (מצוין על-ידי 1 ב- עמודה E)

      חברת פרסום יכולה לפרסם ספרים הכוללים עד 8500 עמודים השנה, ועלי לפרסם לפחות ארבעה ספרים המותכונים למפתחי תוכנה. כיצד החברה יכולה להגדיל את הרווח שלה?

מאמר זה הותאם מ- Microsoft Office Excel 2007 Data Analysis and Business Modeling by Wayne L. Winston.

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

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

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

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

עד כמה אתם מרוצים מאיכות השפה?
מה השפיע על החוויה שלכם?

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

×