كيف يمكن للشركة استخدام Solver لتحديد المشاريع التي يجب تنفيذها؟
في كل عام ، تحتاج شركة مثل Eli Lilly إلى تحديد الأدوية التي يجب تطويرها. شركة مثل Microsoft ، والتي البرامج التي يجب تطويرها ؛ شركة مثل Proctor & Gamble ، والتي يجب تطوير منتجات استهلاكية جديدة. يمكن لميزة Solver في Excel مساعدة الشركة على اتخاذ هذه القرارات.
كيف يمكن للشركة استخدام Solver لتحديد المشاريع التي يجب تنفيذها؟
ترغب معظم الشركات في القيام بمشاريع تساهم بأكبر قدر من صافي القيمة الحالية (NPV) ، وفقا لموارد محدودة (عادة ما يكون رأس المال والعمالة). لنفترض أن شركة تطوير البرمجيات تحاول تحديد أي مشروع برمجي من أصل 20 يجب أن تقوم به. يتم إعطاء NPV (بملايين الدولارات) التي ساهم بها كل مشروع بالإضافة إلى رأس المال (بملايين الدولارات) وعدد المبرمجين المطلوبين خلال كل من السنوات الثلاث القادمة في ورقة عمل النموذج الأساسي في Capbudget.xlsx الملفات ، والتي تظهر في الشكل 30-1 في الصفحة التالية. على سبيل المثال، تحقق قيمة المشروع 2 908 مليون دولار. يتطلب 151 مليون دولار خلال السنة الأولى ، و 269 مليون دولار خلال العام 2 ، و 248 مليون دولار خلال العام الثالث. يحتاج Project 2 إلى 139 مبرمجا خلال السنة الأولى ، و 86 مبرمجا خلال السنة الثانية ، و 83 مبرمجا خلال السنة الثالثة. تظهر الخلايا E4:G4 رأس المال (بملايين الدولارات) المتاح خلال كل سنة من السنوات الثلاث، وتشير الخلايا H4:J4 إلى عدد المبرمجين المتاحين. على سبيل المثال ، خلال السنة الأولى ، يتوفر ما يصل إلى 2.5 مليار دولار من رأس المال و 900 مبرمج.
يجب على الشركة أن تقرر ما إذا كان يجب عليها القيام بكل مشروع. لنفترض أنه لا يمكننا القيام بجزء بسيط من مشروع برمجي. إذا خصصنا 0.5 من الموارد المطلوبة ، على سبيل المثال ، فسيكون لدينا برنامج غير عامل من شأنه أن يحقق لنا إيرادات بقيمة 0 دولار!
تتمثل الحيلة في حالات التصميم التي تفعل فيها شيئا ما أو لا تفعله، في استخدام الخلايا المتغيرة الثنائية. إن الخلية المتغيرة الثنائية تساوي دائما 0 أو 1. عندما تساوي خلية متغيرة ثنائية تطابق مشروع 1، نقوم بالمشروع. إذا كانت خلية متغيرة ثنائية تتطابق مع مشروع تساوي 0، فلن نقوم بالمشروع. يمكنك إعداد Solver لاستخدام نطاق من الخلايا الثنائية المتغيرة عن طريق إضافة قيد - حدد الخلايا المتغيرة التي تريد استخدامها ثم اختر Bin من القائمة في مربع الحوار "إضافة قيد".
بفضل هذه الخلفية، أصبحنا جاهزين لحل مشكلة اختيار مشروع البرامج. وكما هو الحال دائما في نموذج Solver، نبدأ بتعريف الخلية المستهدفة والخلايا المتغيرة والقيود.
- الخلية الهدف. نقوم بتعظيم NPV الناتجة عن المشاريع المحددة.
- الخلايا المتغيرة. نبحث عن خلية متغيرة ثنائية 0 أو 1 لكل مشروع. لقد حددت موقع هذه الخلايا في النطاق A6:A25 (وقمت بتسمية النطاق doit). على سبيل المثال، يشير الرقم 1 في الخلية A6 إلى أننا نفذنا المشروع 1؛ يشير الرقم 0 في الخلية C6 إلى أننا لم ننفذ Project 1.
- القيود. نحن بحاجة إلى التأكد من أنه لكل سنة t (t = 1 ، 2 ، 3) ، يكون رأس المال المستخدم في السنة أقل من أو يساوي رأس المال المتاح ، وأن السنة t labor المستخدمة أقل من أو تساوي السنة t العمل المتاحة.
كما ترى، يجب أن تحسب ورقة العمل الخاصة بنا بالنسبة لأي مجموعة مختارة من المشاريع NPV، ورأس المال المستخدم سنويا، والمبرمجين المستخدمين كل عام. في الخلية B2، أستخدم الصيغة SUMPRODUCT(doit,NPV) لحساب إجمالي NPV التي تم إنشاؤها بواسطة مشاريع محددة. (يشير اسم النطاق NPV إلى النطاق C6:C25). بالنسبة لكل مشروع يتضمن 1 في العمود A، تلتقط هذه الصيغة NPV للمشروع، ولكل مشروع يتضمن 0 في العمود A، لا تلتقط هذه الصيغة القيمة الحالية للمشروع. وبالتالي، يمكننا حساب NPV لكل المشاريع، وتكون الخلية الهدف خطية لأنه يتم حسابها عن طريق جمع المصطلحات التي تتبع النموذج (الخلية المتغيرة)*(ثابت). بطريقة مماثلة ، أحسب رأس المال المستخدم كل عام والعمالة المستخدمة كل عام عن طريق نسخ الصيغة SUMPRODUCT(doit,E6:E25) من E2 إلى F2:J2.
أقوم الآن بملء مربع الحوار "معلمات الحلول" كما هو موضح في الشكل 30-2.
هدفنا هو تعظيم NPV للمشاريع المحددة (الخلية B2). إن خلايانا المتغيرة (النطاق المسمى doit) هي الخلايا الثنائية المتغيرة لكل مشروع. يضمن القيد E2:J2<=E4:J4 أن يكون رأس المال والعمالة المستخدمة خلال كل عام أقل من رأس المال والعمالة المتوفرة أو مساوية لهما. لإضافة القيد الذي يجعل الخلايا المتغيرة ثنائية ، أنقر فوق "إضافة" في مربع الحوار "معلمات الحلول" ثم حدد "سلة" من القائمة في وسط مربع الحوار. يجب أن يظهر مربع الحوار "إضافة قيد" كما هو موضح في الشكل 30-3.
نموذجنا خطي لأن الخلية الهدف تحسب كمجموع المصطلحات التي لها الشكل (خلية متغيرة)*(ثابت) ولأن قيود استخدام الموارد يتم حسابها من خلال مقارنة مجموع (الخلايا المتغيرة)*(الثوابت) بثابت.
مع ملء مربع الحوار "معلمات الحلول" ، انقر فوق حل ولدينا النتائج الموضحة سابقا في الشكل 30-1. يمكن للشركة الحصول على صافي صافي إجمالي يبلغ 9293 مليون دولار (9.293 مليار دولار) باختيار المشاريع 2 و3 و6-10 و14-16 و19 و20.
معالجة القيود الأخرى
تتضمن نماذج تحديد المشروع أحيانا قيودا أخرى. على سبيل المثال، لنفترض أنه إذا حددنا Project 3، فيجب علينا أيضا تحديد Project 4. ونظرا لأن الحل الأمثل الحالي يختار Project 3 وليس Project 4، فنحن نعلم أن الحل الحالي لا يمكن أن يظل مثاليا. لحل هذه المشكلة، ما عليك سوى إضافة القيد القائل بأن الخلية المتغيرة الثنائية في Project 3 أصغر من الخلية المتغيرة الثنائية أو مساوية لها في Project 4.
يمكنك العثور على هذا المثال في ورقة العمل If 3 then 4 في Capbudget.xlsx الملفات، المبين في الشكل 30-4. تشير الخلية L9 إلى القيمة الثنائية المرتبطة بالمشروع 3، والخلية L12 إلى القيمة الثنائية المرتبطة بالمشروع 4. بإضافة القيد L9<=L12، إذا اخترنا Project 3، فإن L9 تساوي 1 ويفرض القيد L12 (ثنائي المشروع 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". (انظر الشكل 30-6.) على سبيل المثال، إعداد تفاوت بنسبة 0.5٪ يعني أن Solver سيتوقف في المرة الأولى التي يعثر فيها على حل عملي يقع ضمن 0.5 بالمائة من قيمة الخلية الهدف المثلى النظرية (قيمة الخلية الهدف المثلى النظرية هي القيمة الهدف المثلى عند حذف القيود الثنائية والأعداد الصحيحة). غالبا ما نواجه خيارا بين العثور على إجابة في غضون 10 بالمائة من الإجابة المثلى في 10 دقائق أو إيجاد حل أمثل في غضون أسبوعين من وقت الكمبيوتر! إن قيمة التسامح الافتراضية هي 0.05٪، مما يعني أن Solver يتوقف عندما يعثر على قيمة خلية هدف في حدود 0.05 بالمائة من قيمة الخلية الهدف المثالية النظرية.
المشاكل
- لدى الشركة تسعة مشاريع قيد الدراسة. ويبين الجدول التالي القيمة الحالية التي يضيفها كل مشروع ورأس المال المطلوب من قبل كل مشروع خلال السنتين المقبلين. (كل الأرقام بالملايين.) فعلى سبيل المثال، سيضيف المشروع 1 14 مليون دولار في القيمة الحالية ويتطلب نفقات قدرها 12 مليون دولار خلال السنة الأولى و3 ملايين دولار خلال السنة الثانية. خلال السنة الأولى، يتوفر رأس مال بقيمة 50 مليون دولار للمشروعات، ويتوفر 20 مليون دولار خلال السنة الثانية.
| NPV | نفقات السنة 1 | نفقات السنة الثانية | |
|---|---|---|---|
| المشروع 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 |
| المشروع 7 | 14 | 48 | 4 |
| المشروع 8 | 10 | 36 | 3 |
| المشروع 9 | 12 | 18 | 3 |
- إذا لم نتمكن من تنفيذ جزء بسيط من المشروع ولكن يجب علينا القيام إما بالمشروع بالكامل أو لا شيء منه ، فكيف يمكننا تعظيم NPV؟
- لنفترض أنه إذا تم تنفيذ المشروع 4، يجب تنفيذ المشروع 5. كيف يمكننا تعظيم NPV؟
تحاول شركة نشر تحديد أي كتاب من بين 36 كتابا يجب أن تنشره هذا العام. يوفر Pressdata.xlsx الملف المعلومات التالية حول كل كتاب:
- الإيرادات المتوقعة وتكاليف التطوير (بآلاف الدولارات)
- الصفحات في كل كتاب
- ما إذا كان الكتاب موجها لجمهور من مطوري البرامج (يشار إليه بواسطة 1 في العمود E)
يمكن لشركة نشر نشر كتب يصل مجموعها إلى 8500 صفحة هذا العام ويجب أن تنشر أربعة كتب على الأقل موجهة لمطوري البرامج. كيف يمكن للشركة زيادة أرباحها؟
حول المقالة
تم اقتباس هذه المقالة من كتاب Microsoft Office Excel 2007 Data Analysis and Business Modeling by Wayne L. Winston.
تم تطوير هذا الكتاب المصمم على طراز الفصل الدراسي من سلسلة من العروض التقديمية واين وينستون، وهو خبير إحصائي معروف وأستاذ أعمال متخصص في التطبيقات الإبداعية والعملية ل Excel.