כיצד יכולה חברה להשתמש ב- Solver כדי לקבוע אילו פרוייקטים עליה לבצע?
בכל שנה, חברה כמו אלי לילי צריכה להחליט אילו תרופות לפתח; חברה כמו מיקרוסופט, אילו תוכנות לפתח; חברה כמו פרוקטור & גמבל, אילו מוצרי צריכה חדשים לפתח. התכונה 'פותר' ב- Excel יכולה לעזור לחברה לקבל החלטות אלה.
כיצד יכולה חברה להשתמש ב- Solver כדי לקבוע אילו פרוייקטים עליה לבצע?
רוב התאגידים רוצים לקחת על עצמם פרויקטים שתורמים את הערך הנוכחי הנקי הגדול ביותר (NPV), בכפוף למשאבים מוגבלים (בדרך כלל הון ועבודה). נניח שחברת פיתוח תוכנה מנסה לקבוע איזה מבין 20 פרויקטי תוכנה עליה לבצע. ה-NPV (במיליוני דולרים) שנתרם על ידי כל פרויקט, כמו גם ההון (במיליוני דולרים) ומספר המתכנתים הדרושים במהלך כל אחת משלוש השנים הבאות ניתנים בגליון העבודה של המודל הבסיסי Capbudget.xlsx הקובץ, המוצג באיור 30-1 בעמוד הבא. לדוגמה, Project 2 מניב 908 מיליון דולר. היא דורשת 151 מיליון דולר במהלך השנה הראשונה, 269 מיליון דולר במהלך השנה השנייה ו-248 מיליון דולר במהלך השנה השלישית. פרויקט 2 דורש 139 מתכנתים במהלך שנה 1, 86 מתכנתים במהלך שנה 2 ו-83 מתכנתים במהלך שנה 3. תאים E4:G4 מציגים את ההון (במיליוני דולרים) הזמין בכל אחת משלוש השנים, ותאים H4:J4 מציינים כמה מתכנתים זמינים. לדוגמה, במהלך השנה הראשונה עד 2.5 מיליארד דולר בהון ו-900 מתכנתים זמינים.
על החברה להחליט אם עליה לקחת על עצמה כל פרויקט. בואו נניח שאנחנו לא יכולים לקחת על עצמנו חלק קטן מפרויקט תוכנה; אם נקצה 0.5 מהמשאבים הדרושים, למשל, תהיה לנו תוכנית לא עובדת שתביא לנו הכנסה של 0 דולר!
הטריק במידול מצבים שבהם אתה עושה או לא עושה משהו הוא להשתמש בתאים משתנים בינאריים. תא בינארי משתנה תמיד שווה ל- 0 או ל- 1. כאשר תא משתנה בינארי שמתאים לפרויקט שווה ל-1, אנחנו עושים את הפרויקט. אם תא משתנה בינארי שמתאים לפרוייקט שווה ל- 0, אנחנו לא מבצעים את הפרוייקט. עליך להגדיר את Solver להשתמש בטווח של תאים משתנים בינאריים על-ידי הוספת אילוץ - בחר את התאים המשתנים שבהם ברצונך להשתמש ולאחר מכן בחר Bin מהרשימה בתיבת הדו-שיח Add Constraint.
עם רקע זה, אנו מוכנים לפתור את בעיית בחירת פרויקט התוכנה. כמו תמיד במודל Solver, אנו מתחילים בזיהוי תא היעד שלנו, התאים המשתנים והאילוצים.
- תא יעד. אנו ממקסמים את ה- NPV שנוצר על-ידי פרוייקטים נבחרים.
- תאים משתנים. אנו מחפשים תא משתנה בינארי 0 או 1 עבור כל פרויקט. איתרתי תאים אלה בטווח A6:A25 (וקראתי לטווח doit). לדוגמה, 1 בתא A6 מציין שאנו לוקחים על עצמנו את פרויקט 1; 0 בתא C6 מציין שאיננו מבצעים את פרויקט 1.
- אילוצים. עלינו להבטיח שעבור כל שנה t (t=1, 2, 3), ההון השנתי t המשמש קטן או שווה להון הזמין של שנת t , והעבודה בשנה t המשמשת קטנה או שווה לעבודה בשנה t .
כפי שניתן לראות, דף העבודה שלנו חייב לחשב עבור כל מבחר פרויקטים את ה- 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 ולאחר מכן בוחר Bin מהרשימה באמצע תיבת הדו-שיח. תיבת הדו-שיח 'הוספת אילוץ' אמורה להופיע כפי שמוצג באיור 30-3.
המודל שלנו הוא ליניארי משום שתא היעד מחושב כסכום המונחים בעלי התבנית (תא משתנה)*(קבוע) ומשום שאילוצי השימוש במשאבים מחושבים על-ידי השוואת הסכום של (תאים משתנים)*(קבועים) לקבוע.
לאחר מילוי תיבת הדו-שיח Solver Parameters, לחץ על Solve וקיבלנו את התוצאות שהוצגו קודם לכן באיור 30-1. החברה יכולה להשיג NPV מקסימלי של 9,293 מיליון דולר (9.293 מיליארד דולר) על ידי בחירת פרויקטים 2, 3, 6-10, 14-16, 19 ו-20.
טיפול באילוצים אחרים
לעתים, למודלים של בחירת פרוייקט יש אילוצים אחרים. לדוגמה, נניח שאם נבחר את פרויקט 3, עלינו לבחור גם את פרויקט 4. מכיוון שהפתרון האופטימלי הנוכחי שלנו בוחר בפרויקט 3 אך לא בפרויקט 4, אנו יודעים שהפתרון הנוכחי שלנו לא יכול להישאר אופטימלי. כדי לפתור בעיה זו, פשוט הוסף את האילוץ שהתא המשתנה הבינארי עבור פרוייקט 3 קטן או שווה לתא המשתנה הבינארי עבור פרוייקט 4.
באפשרותך למצוא דוגמה זו בגליון העבודה 'אם 3 ולאחר מכן 4 ' Capbudget.xlsx הקובץ, המוצג באיור 30-4. תא L9 מפנה לערך הבינארי הקשור לפרוייקט 3, ותא L12 מתייחס לערך הבינארי הקשור לפרוייקט 4. על-ידי הוספת האילוץ L9<=L12, אם נבחר בפרויקט 3, L9 שווה ל- 1 והאילוץ שלנו מאלץ את L12 (הבינארי של פרוייקט 4) להיות שווה ל- 1. האילוץ שלנו חייב גם להשאיר את הערך הבינארי בתא המשתנה של פרויקט 4 ללא הגבלה אם לא נבחר בפרויקט 3. אם לא נבחר את פרויקט 3, L9 שווה 0 והאילוץ שלנו מאפשר לבינארי של פרויקט 4 להיות שווה ל-0 או 1, וזה מה שאנחנו רוצים. הפתרון האופטימלי החדש מוצג באיור 30-4.
פתרון אופטימלי חדש מחושב אם בחירת פרויקט 3 פירושה שעלינו לבחור גם בפרויקט 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 Options. (ראה איור 30-6.) לדוגמה, הגדרת טולרנס של 0.5% פירושה ש- Solver יפסיק בפעם הראשונה שהוא מוצא פתרון אפשרי בטווח של 0.5 אחוז מהערך התיאורטי האופטימלי של תא היעד (ערך תא היעד האופטימלי התיאורטי הוא ערך היעד האופטימלי שנמצא כאשר האילוצים הבינאריים והאילוצים השלמים מושמטים). לעתים קרובות אנו עומדים בפני בחירה בין מציאת תשובה בטווח של 10 אחוזים מהאופטימלי תוך 10 דקות או מציאת פתרון אופטימלי תוך שבועיים של זמן מחשב! ערך ברירת המחדל של טולרנס הוא 0.05%, כלומר ש- Solver מפסיק כאשר הוא מוצא ערך של תא יעד בטווח של 0.05 אחוז מהערך של תא היעד האופטימלי התיאורטי.
בעיות
- לחברה יש תשעה פרויקטים בבדיקה. ה- NPV שהוסיף כל פרוייקט וההון הנדרש לכל פרוייקט במהלך השנתיים הבאות מוצגים בטבלה הבאה. (כל המספרים הם במיליונים.) לדוגמה, פרויקט 1 יוסיף 14 מיליון דולר ב- NPV וידרוש הוצאות של 12 מיליון דולר במהלך השנה הראשונה ו- 3 מיליון דולר במהלך השנה השנייה. במהלך השנה הראשונה, 50 מיליון דולר בהון זמין לפרויקטים, ו-20 מיליון דולר זמינים במהלך השנה השנייה.
| 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?
- נניח שאם פרוייקט 4 מתבצע, יש לבצע את פרוייקט 5. כיצד נוכל למקסם את NPV?
חברת הוצאה לאור מנסה לקבוע איזה מבין 36 הספרים עליה להוציא השנה. Pressdata.xlsx הקובץ מציג את המידע הבא אודות כל ספר:
- עלויות הכנסות ופיתוח צפויות (באלפי דולרים)
- עמודים בכל ספר
- האם הספר מיועד לקהל של מפתחי תוכנה (מצוין על ידי 1 בעמודה E)
חברת הוצאה לאור יכולה לפרסם ספרים בהיקף כולל של עד 8,500 עמודים השנה, וחייבת לפרסם לפחות ארבעה ספרים המיועדים למפתחי תוכנה. כיצד החברה יכולה למקסם את הרווח שלה?
אודות המאמר
מאמר זה עובד מתוך הספר 'ניתוח נתונים ומידול עסקי' של Microsoft Office Excel 2007 מאת וויין ל. וינסטון.
ספר זה בסגנון כיתה פותח מסדרת מצגות של וויין ווינסטון, סטטיסטיקאי ידוע ופרופסור לעסקים המתמחה ביישומים יצירתיים ומעשיים של אקסל.