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

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

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

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

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

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

תמונת ספר

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

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

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

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

כפי שניתן לראות, גליון העבודה שלנו חייב לחשב את כל הפרוייקטים של NPV, ההון שנעשה בו שימוש מדי שנה, והמתכנתים השתמשו בה מדי שנה. בתא B2, אני משתמש ב -Formula 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 ממולאת, לחץ על פתור ויש לנו את התוצאות המוצגות מוקדם יותר באיור 30-1. החברה יכולה להשיג NPV מירבי של $9,293,000,000 ($9,293,000,000) על-ידי בחירת פרוייקטים 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.) ב-cell L8, אנו מחשבים את הסכום של הערכים הבינאריים המשויכים לפרוייקטים 1 עד 10 עם הנוסחה sum (A6: A15). לאחר מכן נוסיף את האילוץ L8< = L10, אשר מבטיח, לכל היותר, את 4 מתוך 10 הפרוייקטים הראשונים שנבחרו. הפתרון המיטבי החדש מוצג באיור 30-5. הפונקציה NPV ירדה ל-$9,014,000,000.

תמונת ספר

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

תמונת ספר

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

NPV

הוצאה של שנה 1

הוצאה של שנה 2

Project 1

14

12

3

פרוייקט 2

17

54

7

Project 3

17

6

6

Project 4

15

6

2

פרוייקט 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 על-ידי וויין L. וינסטון.

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

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

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

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

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

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

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

×