שימוש ב- Solver לתקציב הון

רוב החברה רוצה לבצע פרוייקטים שתורם את הערך הנוכחי הנטוי הגדול ביותר (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 מציין שאנו מתחייבים על פרוייקט 1; 0 בתא C6 מציין שאנחנו לא מתחייבים על פרוייקט 1.

• אילוצים. עלינו לוודא שעבור כל שנה t (t=1, 2, 3), ההון של Year t בשימוש הוא קטן או שווה להון השנה t זמין, ו- Year t labor used is less than or equal to Year t labor available.

כפי שניתן לראות, גליון העבודה שלנו חייב לחשב עבור כל בחירה של פרוייקטים שה- NPV, ההון המשמש מדי שנה ואת המתכנתים שבהם נעשה שימוש מדי שנה. בתא B2, אני משתמש בנוסחה SUMPRODUCT(doit,NPV) לחישוב ה- NPV הכולל שנוצר על-ידי פרוייקטים שנבחרו. (שם הטווח NPV מפנה לטווח C6:C25.) עבור כל פרוייקט עם עמודה A 1, נוסחה זו אוגרת את ה- 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 מולאו, לחץ על Solve והתוצאות מוצגות מוקדם יותר באיור 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, אנו מחשבים את סכום הערכים הבינאריים המשויכים לפרוייקטים 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
פרוייקט 1	14	12	3
פרוייקט 2	17	54	7
פרוייקט 3	17	6	6
פרוייקט 4	15	6	2
פרוייקט 5	40	30	35
פרוייקט 6	12	6	6
פרוייקט 7	14	48	4
פרוייקט 8	10	36	3
פרוייקט 9	12	18	3

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

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

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

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

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

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

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

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

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