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

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

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

• לתמהיל המוצרים אין אפשרות להשתמש במשאבים רבים יותר מאלה הזמינים.

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

כעת נפתור את הדוגמה הבאה של הבעיה ' ערבוב מוצרים '. באפשרותך למצוא את הפתרון לבעיה זו בקובץ Prodmix. xlsx, המוצג באיור 27-1.

נניח שאנו עובדים עבור חברת תרופות שמייצרת שישה מוצרים שונים במפעל שלהם. ייצור של כל מוצר דורש עבודה וחומרי גלם. שורה 4 באיור 27-1 מציגה את שעות העבודה הדרושות להפקת פאונד של כל מוצר, ושורה 5 מציגה את קילוגרמי חומר הגלם הדרושים להפקת פאונד של כל מוצר. לדוגמה, הפקת פאונד של מוצר 1 דורשת שש שעות של עבודה ו-3.2 קילוגרמים של חומרי גלם. עבור כל תרופה, המחיר לפאונד מופיע בשורה 6, עלות היחידה לכל לירה מתבצעת בשורה 7, והתרומה לכל לירה ניתנת בשורה 9. לדוגמה, Product 2 מוכר עבור $11.00 לכל לירה, תוך הקשה על עלות יחידה של $5.70 לכל לירה, והיא תורמת רווחים של $5.30 לכל לירה. דרישת החודש עבור כל תרופה ניתנת בשורה 8. לדוגמה, דרישה עבור Product 3 היא 1041 פאונד. החודש, 4500 שעות העבודה ו-1600 קילו של חומרי גלם זמינים. כיצד יכולה חברה זו למקסם את הרווח החודשי שלה?

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

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

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

(העבודה בשימוש לכל ליטרת התרופה 1) * (התרופה 1 קילוגרם הופק) +
(העבודה בשימוש לכל ליטרת של תרופה 2) * (התרופה 2 קילוגרם הופק) +...
(העבודה בשימוש לכל ליטרת התרופה 6) * (הסם 6 קילוגרמים המופק)

אנו יכולים לחשב את השימוש בעבודה בצורה מייגעת יותר, כמו D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. באופן דומה, השימוש בחומר גלם עשוי להיות מחושב כ- D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. עם זאת, הזנת נוסחאות אלה בגליון עבודה עבור שישה מוצרים גוזלת זמן רב. תאר לעצמך כמה זמן יידרש אם עבדת עם חברה שמפיקה, לדוגמה, 50 מוצרים במפעל שלהם. דרך קלה הרבה יותר לחשב את השימוש בעבודה וחומרי גלם היא להעתיק מ-D14 ל-D15 את הנוסחה SUMPRODUCT ($D $2: $I $2, D4: I4). נוסחה זו מחשבת D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (שהוא שימוש בעבודה שלנו) אך קל הרבה יותר להיכנס! שים לב שאני משתמש בסימן $ עם טווח D2: I2 כך שכאשר אני מעתיק את הנוסחה אני עדיין תופס את תמהיל המוצרים משורה 2. הנוסחה בתא D15 מחשבת שימוש בחומר גלם.

בצורה דומה, הרווח שלנו נקבע על-ידי

(התרופה 1 רווח לכל לירה) * (התרופה 1 קילוגרם הופק) +
(תרופה 2 הרווח לכל לירה) * (התרופה 2 קילוגרם הופק) +...
(התרופה 6 רווח לכל לירה) * (התרופה 6 קילוגרמים הופק)

הרווח מחושב בקלות בתא D12 עם הנוסחה SUMPRODUCT (D9: i9ב, $D $2: $I $2).

כעת ניתן לזהות את שלושת הרכיבים של מודל ה-Solver של תמהיל המוצרים שלנו.

• תא יעד.המטרה שלנו היא למקסם את הרווח (מחושב בתא D12).

• שינוי תאים.מספר הקילוגרמים המופקים מכל מוצר (המופיע בטווח התאים D2: I2)

• אילוצים. יש לנו את האילוצים הבאים:

  • אין להשתמש ביותר עבודה או חומר גלם מאלה שזמינים. כלומר, הערכים בתאים D14: D15 (המשאבים הנמצאים בשימוש) חייבים להיות קטנים או שווים לערכים בתאים F14: F15 (המשאבים הזמינים).

  • אין להפיק יותר מתרופה מאשר בדרישה. כלומר, הערכים בתאים D2: I2 (קילוגרמים המופקים מכל תרופה) חייבים להיות קטנים או שווים לביקוש לכל תרופה (המופיעה בתאים D8: I8).

  • לא ניתן לייצר כמות שלילית של תרופה כלשהי.

אראה לך כיצד להזין את תא היעד, לשנות את התאים והאילוצים ב-Solver. לאחר מכן, כל שעליך לעשות הוא ללחוץ על לחצן ' פתור ' כדי למצוא תמהיל מוצרים להגדלת הרווח!

כדי להתחיל, לחץ על הכרטיסיה נתונים ובקבוצה ניתוח, לחץ על Solver.

תיבת הדו Solver Parameters תופיע, כפי שמוצג באיור 27-2.

לחץ על התיבה הגדר תא יעד ולאחר מכן בחר את תא הרווח שלנו (cell D12). לחץ על התיבה על-ידי שינוי תאים ולאחר מכן הצבע על טווח D2: I2, המכיל את הקילוגרמים המופקים מכל תרופה. תיבת הדו אמורה להיראות כעת באיור 27-3.

אנו מוכנים כעת להוסיף אילוצים למודל. לחץ על לחצן הוסף. תראה את תיבת הדו הוספת אילוץ, המוצגת באיור 27-4.

כדי להוסיף את אילוצי שימוש במשאבים, לחץ על התיבה הפניה לתא ולאחר מכן בחר את טווח D14: D15. בחר < = מהרשימה האמצעית. לחץ על התיבה אילוץ ולאחר מכן בחר את טווח התאים F14: F15. תיבת הדו הוספת אילוץ אמורה להיראות כעת כאיור 27-5.

הבטיחו כעת ש-Solver מנסה ערכים שונים עבור התאים המשתנים, רק צירופים המחייבים הן את D14< = F14 (העבודה הנמצאת בשימוש היא קטנה או שווה לעבודה זמינה) ו- D15< = F15 (חומר גלם שנמצא בשימוש קטן או שווה ל- חומר גלם זמין) ייחשב. לחץ על הוסף כדי להזין את אילוצי הביקוש. מלא את תיבת הדו הוספת אילוץ כפי שמוצג באיור 27-6.

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

• D2< = D8 (הסכום המופק מהסם 1 קטן או שווה לביקוש עבור התרופה 1)

• E2< = E8 (כמות התוצרת של התרופה 2 היא קטנה או שווה לביקוש עבור התרופה 2)

• F2< = F8 (הכמות המופקת מהסם 3 שבוצעה היא קטנה או שווה לביקוש לסמים 3)

• G2< = G8 (הסכום המופק מהסם 4 שנוצר קטן או שווה לביקוש עבור תרופה 4)

• H2< = H8 (הכמות המופקת מהסם 5 שבוצעה היא קטנה או שווה לביקוש לסמים 5)

• I2< = I8 (הסכום המופק מהסם 6 שנוצר הוא קטן או שווה לביקוש לתרופה 6)

לחץ על אישור בתיבת הדו הוספת אילוץ. החלון Solver אמור להיראות כמו איור 27-7.

אנו מזינים את האילוץ שמשתנה תאים חייב להיות לא שלילי בתיבת הדו אפשרויות Solver. לחץ על לחצן ' אפשרויות ' בתיבת הדו ' פרמטרים של Solver '. סמן את התיבה ' הנח מודל ליניארי ' ואת התיבה ' הנח שאינם שליליים ', כפי שמוצג באיור 27-8 בעמוד הבא. לחץ על 'אישור'.

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

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

• כל אילוץ עונה על "דרישת מודל ליניארי". משמעות הדבר היא שכל אילוץ מוערך על-ידי הוספת התנאים של הטופס (שינוי תא) * (קבוע) והשוואת הסכומים לקבוע.

מדוע בעיה זו ליניארית של Solver? תא היעד שלנו (רווח) מחושב

(התרופה 1 רווח לכל לירה) * (התרופה 1 קילוגרם הופק) +
(תרופה 2 הרווח לכל לירה) * (התרופה 2 קילוגרם הופק) +...
(התרופה 6 רווח לכל לירה) * (התרופה 6 קילוגרמים הופק)

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

אילוץ העבודה שלנו מוערך על-ידי השוואת הערך שנגזר ממנו (העבודה הנמצאת בשימוש לכל ליטרת התרופה 1) * (התרופה 1 ליש ט הופק) + (העבודה בשימוש לכל ליטרת התרופה 2) * (התרופה 2 קילוגרמים שהופקו) +... (העבודה שלנוed לכל ליטרת התרופה 6) * (הסם הופק ב-6 קילוגרם) לעבודה הזמינה.

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

אילוצי הביקוש שלנו מקבלים את הטופס

(תרופה 1 הופק) < = (תרופה 1 דרישה)
(תרופה 2 הופק) < = (התרופה 2 דרישה)
§
(תרופה 6 הופק) < = (התרופה 6 דרישה)

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

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

• אם מודל Solver הוא ליניארי ואנו בוחרים ' הנח מודל ליניארי ', Solver מובטח למצוא את הפתרון המיטבי למודל Solver. אם מודל Solver אינו ליניארי, ייתכן ש-solver עשוי למצוא את הפתרון המיטבי או לא למצוא אותו.

• אם מודל Solver הוא ליניארי ואנו בוחרים באפשרות ' הנח מודל ליניארי ', Solver משתמש באלגוריתם יעיל מאוד (שיטת סימפלקס) כדי למצוא את הפתרון האופטימלי של המודל. אם מודל Solver הוא ליניארי ואנו לא בוחרים באפשרות ' הנח מודל ליניארי ', Solver משתמש באלגוריתם לא יעיל מאוד (שיטת GRG2) ועשוי להתקשות למצוא את הפתרון האופטימלי של המודל.

לאחר לחיצה על אישור בתיבת הדו ' אפשרויות solver ', אנו חוזרים לתיבת הדו solver הראשי, המוצגת מוקדם יותר באיור 27-7. כאשר אנו לוחצים על ' פתור ', Solver מחשב פתרון אופטימלי (אם קיים אדם כלשהו) עבור מודל תמהיל המוצרים שלנו. כאמור, בפרק 26, הפתרון המיטבי למודל תמהיל המוצרים יהיה קבוצה של שינוי ערכי תא (קילוגרמים המופקים מכל תרופה) המגדילים את הרווח בקבוצה של כל הפתרונות האפשריים. שוב, פתרון אפשרי הוא קבוצה של שינוי ערכי תא המספקים את כל האילוצים. ערכי התאים המשתנים המוצגים באיור 27-9 הם פתרון אפשרי מכיוון שכל רמות הייצור אינן שליליות, רמות ייצור אינן חורגות מהביקוש, ושימוש במשאבים אינו חורג מהמשאבים הזמינים.

ערכי התאים המשתנים המוצגים באיור 27-10 בעמוד הבא מייצגים פתרון לא מתאים מהסיבות הבאות:

• אנו מייצרים יותר מהסם 5 מהביקוש לכך.

• אנו משתמשים ביותר עבודה ממה שזמין.

• אנו משתמשים בחומרי גלם רבים יותר ממה שזמין.

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

חברת התרופות שלנו יכולה למקסם את הרווח החודשי שלה ברמה של $6,625.20 על-ידי הפקת 596.67 קילוגרמים של תרופה 4, 1084 קילוגרמים של תרופה 5, ואף אחת מהתרופות האחרות! אין באפשרותנו לקבוע אם ניתן להשיג את הרווח המרבי של $6,625.20 בדרכים אחרות. כל מה שאנחנו יכולים להיות בטוחים בו הוא שהמשאבים והביקושים המוגבלים שלנו, אין דרך להרוויח יותר מ-$6,627.20 החודש.