استخدام Solver لتحديد مزيج المنتجات المثالي

كيف يمكنني تحديد مزيج المنتجات الشهري الذي يؤدي إلى زيادة الربحية ؟

تحتاج الشركات غالبا إلى تحديد كميه كل منتج ليتم تقديمه علي أساس شهري. في النموذج الأبسط ، تشمل مشكله مزيج المنتج كيفيه تحديد مقدار كل منتج يجب ان يتم إنتاجه خلال شهر لزيادة الأرباح. يجب ان تلتزم مزيج المنتج عاده بالقيود التالية:

• لا يمكن ان يستخدم مزيج المنتج موارد أكثر من تلك المتوفرة.

• هناك طلب محدود لكل منتج. لا يمكن ان تنتج أكثر من منتج خلال شهر من المدة التي يفرضها الطلب ، لان الإنتاج الزائد يتم الحصول عليه (علي سبيل المثال ، بيريشابل).

دعنا نبدا الآن المثال التالي لمشكله خلط المنتج. يمكنك العثور علي الحل لهذه المشكلة في الملف برودميكس ، الذي يظهر في الشكل 27-1.

فلنفترض اننا نعمل علي شركه العقاقير التي تنتج سته منتجات مختلفه في النبات. يتطلب إنتاج كل منتج العمالة والمواد الخام. يعرض الصف 4 في الرسم التوضيحي 27-1 ساعات العمل المطلوبة لإنتاج جنيه من كل منتج ، ويظهر الصف 5 الأرطال التي يحتاجها المواد الخام للحصول علي جنيه لكل منتج. علي سبيل المثال ، يتطلب إنتاج رطل من المنتجات رقما ست ساعات من العمالة والأرطال 3.2 من المادة الخام. بالنسبة إلى كل المخدرات ، يتم تقديم السعر لكل رطل في الصف 6 ، ويتم تقديم تكلفه الوحدة لكل رطل في الصف 7 ، ويتم تقديم مساهمه الأرباح لكل رطل في الصف 9. علي سبيل المثال ، يتم بيع المنتج 2 ل $11.00 لكل رطل ، ويؤدي إلى تكلفه وحده $5.70 لكل رطل ، والمساهمة بالربح المقابل لكل رطل. يتم توفير طلب الشهر لكل المخدرات في الصف 8. علي سبيل المثال ، المطالبة بالمنتج 3 هو 1041 أرطال. في هذا الشهر ، يتوفر 4500 ساعات العمل والأرطال للمواد الخام. كيف يمكن لهذه الشركة تكبير الأرباح الشهرية الخاصة بها ؟

إذا لم نجد اي شيء حول Excel Solver ، سنقوم بمهاجمه هذه المشكلة عن طريق إنشاء ورقه عمل لتعقب استخدام الأرباح والموارد المقترنة بمزيج المنتج. وبعد ذلك ، سنستخدم الإصدار التجريبي والخطا لتنويع مزيج المنتجات لتحسين الربح بدون استخدام المزيد من العمالة أو مواد خام أكبر من المتاحة ، ومن دون ان تكون اي مخدرات زائده عن الطلب. نحن نستخدم Solver في هذه العملية فقط في مرحله الإصدار التجريبي والخطا. بشكل أساسي ، ان Solver هو مشغل تحسين الأداء الذي يقوم بتنفيذ عمليه البحث التجريبي والخطا.

يعتبر المفتاح الخاص بحل مشكله خلط المنتجات بفعالية حساب استخدام الموارد والأرباح المقترنة بأي مزيج منتجات محدد. أداه مهمة يمكننا استخدامها لجعل هذه العملية الحسابية هي الدالة SUMPRODUCT. تضرب الدالة SUMPRODUCT القيم المناظرة في نطاقات الخلايا وترجع مجموع هذه القيم. يجب ان يتوفر لكل نطاق خلايا يتم استخدامه في تقييم SUMPRODUCT الابعاد نفسها ، والتي تشير إلى انه يمكنك استخدام SUMPRODUCT مع صفين أو عمودين ، ولكن ليس بأحد الاعمده وصف واحد.

كمثال عن كيفيه استخدام الدالة SUMPRODUCT في مثال مزيج المنتج الخاص بنا ، فاننا نحاول حساب استخدام الموارد الخاصة بنا. يتم حساب استخدام العمالة الخاص بنا بواسطة

(العمالة المستخدمة لكل رطل بالمخدرات 1) * (تم إنتاج الجنيه 1) +
(العمالة المستخدمة لكل رطل في العقاقير 2) * (تم إنتاج الأرطال 2) +...
(العمالة المستخدمة لكل رطل في العقاقير 6) * (تم إنتاج الأرطال 6 رطلا)

يمكننا حساب استخدام العمالة بطريقه أكثر D4ه بالشكل التالي ك D2 * + E2 * E4 + F2 * F4 + G2 * G4 + + 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 mix الخاص بنا.

• الخلية الهدف.هدفنا هو زيادة الربح (محسوبة في الخلية D12).

• تغيير الخلايا.عدد الأرطال الناتجة عن كل منتج (مدرج في نطاق الخلايا D2: I2)

• القيود. لدينا القيود التالية:

  • لا تستخدم المزيد من العمالة أو مواد خام أكبر من المتاحة. اي ، يجب ان تكون القيم الموجودة في الخلايا D14: D15 (الموارد المستخدمة) اقل من أو تساوي القيم الموجودة في الخلايا F14: F15 (الموارد المتوفرة).

  • لا تنتج المزيد من العقاقير التي تتم في الطلب. اي ان القيم الموجودة في الخلايا D2: I2 (الأرطال الناتجة عن كل المخدرات) يجب ان تكون أصغر من الطلب أو مساويه لها لكل المخدرات (الموجودة في الخلايا D8: I8).

  • لا يمكننا إنشاء مبلغ سالب لأي مخدرات.

سنعرض لك كيفيه إدخال الخلية المستهدفة وتغيير الخلايا والقيود في Solver. وكل ما تحتاجه هو النقر فوق زر الحل للبحث عن مزيج منتج لتكبير الأرباح!

للبدء ، انقر فوق علامة التبويب بيانات ، وفي المجموعة تحليل ، انقر فوق Solver.

سيظهر مربع الحوار معلمات Solver ، كما هو موضح في الشكل 27-2.

انقر فوق المربع تعيين الخلية الهدف ثم حدد خليه الأرباح (D12 الخلية). انقر فوق المربع بتغيير الخلايا ثم أشر إلى النطاق D2: I2 ، الذي يحتوي علي الأرطال الناتجة عن كل المخدرات. يجب ان يظهر مربع الحوار الآن الشكل 27-3.

أنت الآن جاهز لأضافه قيود إلى النموذج. انقر فوق الزر أضافه. سيظهر مربع الحوار أضافه قيد الذي يظهر في الشكل 27-4.

لأضافه قيود استخدام الموارد ، انقر فوق المربع مرجع الخلية ، ثم حدد النطاق D14: D15. حدد _lt_ = من القائمة الوسطي. انقر فوق المربع قيد ، ثم حدد نطاق الخلايا 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) * (تم إنتاج الجنيه الواحد) + (العمالة المستخدمة لكل رطل في العقاقير 2) * (تم إنتاج الجنيه 2) +... (العمالة في الولايات المتحدة المنقضية في كل رطل من المخدرات 6) * (تم إنتاج الأرطال 6 الادويه) إلى العمل المتوفر.

التالي ، يتم تقييم قيد العمالة عن طريق أضافه شروط النموذج (تغيير الخلية) * (ثابت) ومقارنه بين المبالغ المتغيرة. يفي كل من قيد العمالة وقيد المادة الخام بمتطلبات النموذج الخطي.

تتخذ قيود الطلب الخاصة بنا النموذج

(تم إنتاج المخدرات 1) _lt_ = (العقاقير 1
التي تم إنتاجها) (العقاقير 2 التي تم إنشاؤها) (
العقاقير 2) §
(العقاقير 6 التي تم إنتاجها) _lt_ = (طلب العقاقير 6)

يفي كل قيد طلب أيضا بمتطلبات النماذج الخطية ، لان كل منها يتم تقييمها عن طريق أضافه شروط النموذج (تغيير الخلية) * (ثابت) ومقارنه الجمع بين القيمة الثابتة.

هل أظهرت ان نموذج مزج المنتجات الخاص بنا هو نموذج خطي ، لماذا يجب ان نكون ؟

• إذا كان نموذج Solver خطيا وقامنا بالتحديد علي النموذج الخطي ، سيكون من المضمون ان يجد Solver الحل الأمثل لنموذج الحلول. إذا لم يكن نموذج Solver خطيا ، فمن المحتمل ان يقوم Solver أو لا يجد الحل الأمثل.

• إذا كان نموذج Solver خطيا وقامنا بتحديد افتراض نموذج خطي ، فان Solver يستخدم خوارزميه فعاله جدا (أسلوب سيمبليكس) للعثور علي الحل الأمثل للنموذج. إذا كان نموذج Solver خطيا ولم تحدد النموذج الخطي ، سيستخدم Solver خوارزميه غير فعاله (أسلوب GRG2) وقد يكون لديك صعوبة في العثور علي الحل الأمثل للنموذج.

بعد النقر فوق "موافق" في مربع الحوار "خيارات solver" ، سنقوم بالعودة إلى مربع الحوار "solver الرئيسي" ، الذي يظهر سابقا في الشكل 27-7. عند النقر فوق حل ، يحسب Solver حلا أمثلا (في حاله وجود واحده) لنموذج مزج المنتج. عندما قمت بالتحديد في الفصل 26 ، سيكون الحل الأمثل لنموذج خلط المنتجات عبارة عن مجموعه من قيم الخلايا (الأرطال الناتجة عن كل المخدرات) التي تزيد عن الربح في مجموعه الحلول المجدية. مره أخرى ، حل مجدي عبارة عن مجموعه من قيم الخلايا التي تفي بكل القيود. تعد قيم الخلايا التي تظهر في الشكل 27-9 حلا مجديا لان كل مستويات الإنتاج غير سالبه ، ولا تتجاوز مستويات الإنتاج الطلب ، ولا يتجاوز استخدام الموارد الموارد المتوفرة.

تمثل قيم الخلايا التي يتم عرضها في الرسم التوضيحي 27-10 علي الصفحة التالية حلا لم يتم للأسباب التالية:

• نوفر المزيد من العقاقير 5 من الطلب الخاص به.

• نحن نستخدم المزيد من العمالة أكثر مما هو متوفر.

• نحن نستخدم مواد خام أكثر مما هو متوفر.

بعد النقر فوق "حل" ، يقوم Solver بسرعة بالعثور علي الحل الأمثل الذي يظهر في الشكل 27-11. تحتاج إلى تحديد الاحتفاظ بحل الحلول للمحافظة علي قيم الحل الأمثل في ورقه العمل.

بإمكان شركه المخدرات الخاصة بنا زيادة الأرباح الشهرية الخاصة بها في مستوي $6,625.20 عن طريق إنتاج 596.67 رطل لمخدرات 4 والأرطال التي يزيد عن المخدر 5 وأي من المخدرات الأخرى! لا يمكننا تحديد ما إذا كان بإمكاننا تحقيق الحد الأقصى لربح $6,625.20 بطرق أخرى. كل ما علينا سوي التاكد من انه يتضمن الموارد والطلبات المحدودة الخاصة بنا ، ولا يمكنك القيام بأكثر من $6,627.20 هذا الشهر.