با استفاده از حل کننده برای تعیین ترکیب بهینه محصول

در این مقاله با استفاده از Solver ، یک برنامه افزودنی Microsoft Excel که می توانید برای تجزیه و تحلیل What-If استفاده کنید ، برای تعیین یک ترکیب بهینه محصول استفاده می شود.

چگونه می توانم ترکیب ماهانه محصول را که سودآوری را به حداکثر می رساند ، تعیین کنم؟

شرکت ها اغلب برای تولید ماهانه نیاز به تعیین مقدار هر محصول دارند. در ساده ترین شکل خود ، مشکل مخلوط محصول شامل نحوه تعیین میزان هر محصولی است که باید در طی یک ماه تولید شود تا سود را به حداکثر برساند. مخلوط محصول معمولاً باید به محدودیت های زیر رعایت کند:

ترکیب محصول نمی تواند از منابع بیشتری از آنچه در دسترس است استفاده کند.

برای هر محصول تقاضای محدودی وجود دارد. ما نمی توانیم بیشتر از یک ماه محصول تولید کنیم تا اینکه تقاضا را دیکته کند ، زیرا تولید اضافی هدر می رود (به عنوان مثال ، یک داروی قابل خراب شدن).

اکنون می توانیم مثال زیر از مشکل مخلوط محصول را حل کنیم. می توانید راه حل این مشکل را در پرونده prodmix. xlsx ، که در شکل 27-1 نشان داده شده است ، پیدا کنید.

بیایید بگوییم که ما برای یک شرکت دارویی کار می کنیم که شش محصول مختلف را در گیاه آنها تولید می کند. تولید هر محصول نیاز به کار و مواد اولیه دارد. ردیف 4 در شکل 27-1 ساعت کار مورد نیاز برای تولید یک پوند از هر محصول را نشان می دهد ، و ردیف 5 پوند مواد اولیه مورد نیاز برای تولید یک پوند از هر محصول را نشان می دهد. به عنوان مثال ، تولید یک پوند محصول 1 به شش ساعت زایمان و 3. 2 پوند مواد اولیه نیاز دارد. برای هر دارو ، قیمت هر پوند در ردیف 6 داده می شود ، هزینه واحد برای هر پوند در ردیف 7 داده می شود ، و سهم سود هر پوند در ردیف 9 داده می شود. به عنوان مثال ، محصول 2 با قیمت 11. 00 دلار در هر پوند ، متحمل می شود. هزینه واحد 5. 70 دلار در هر پوند ، و 5. 30 دلار سود در هر پوند کمک می کند. تقاضای ماه برای هر دارو در ردیف 8 آورده شده است. به عنوان مثال ، تقاضا برای محصول 3 1041 پوند است. در این ماه 4500 ساعت زایمان و 1600 پوند مواد اولیه در دسترس است. چگونه این شرکت می تواند سود ماهانه خود را به حداکثر برساند؟

اگر چیزی در مورد Solver Excel نمی دانستیم ، با ساخت یک کاربرگ برای پیگیری سود و استفاده از منابع مرتبط با ترکیب محصول ، به این مشکل حمله خواهیم کرد. سپس ما از آزمایش و خطا برای تغییر ترکیب محصول برای بهینه سازی سود بدون استفاده از کار بیشتر یا مواد اولیه بیشتر از آنچه در دسترس است ، استفاده می کنیم و بدون تولید دارویی بیش از حد تقاضا. ما در این فرآیند فقط در مرحله آزمایش و خطا استفاده می کنیم. در اصل ، 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 محصول در کارخانه آنها تولید می کرد چقدر طول می کشید. یک راه بسیار ساده تر برای محاسبه نیروی کار و مصرف مواد خام، کپی کردن فرمول SUMPRODUCT($D$2:$I$2, D4:I4) از D14 به D15 است. این فرمول 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) محاسبه می شود.

اکنون می توانیم سه جزء مدل حل کننده ترکیب محصول خود را شناسایی کنیم.

سلول هدف. هدف ما به حداکثر رساندن سود است (محاسبه شده در سلول D12).

تغییر سلول هاتعداد پوند تولید شده از هر محصول (در محدوده سلولی D2:I2 ذکر شده است)

محدودیت ها. ما محدودیت های زیر را داریم:

از نیروی کار یا مواد خام بیشتر از حد موجود استفاده نکنید. یعنی مقادیر سلول های D14:D15 (منابع استفاده شده) باید کمتر یا مساوی با مقادیر سلول های F14:F15 (منابع موجود) باشد.

بیش از تقاضای دارو تولید نکنید. یعنی مقادیر سلول های D2:I2 (پوند تولید شده از هر دارو) باید کمتر یا برابر با تقاضای هر دارو باشد (در سلول های D8:I8 ذکر شده است).

ما نمی توانیم مقدار منفی هیچ دارویی تولید کنیم.

من به شما نشان خواهم داد که چگونه سلول هدف، تغییر سلول ها و محدودیت ها را در حل کننده وارد کنید. سپس تنها کاری که باید انجام دهید این است که روی دکمه حل کلیک کنید تا ترکیبی از محصولی که حداکثر سود را به همراه دارد پیدا کنید!

برای شروع، روی تب Data و در گروه Analysis، روی Solver کلیک کنید.

توجه: همانطور که در فصل 26، "مقدمه ای بر بهینه سازی با حل کننده اکسل" توضیح داده شد، Solver با کلیک کردن روی دکمه Microsoft Office، سپس Excel Options و سپس Add-In ها نصب می شود. در لیست Manage، روی Excel Add-ins کلیک کنید، کادر Solver Add-in را علامت بزنید و سپس روی OK کلیک کنید.

همانطور که در شکل 27-2 نشان داده شده است، کادر محاوره ای Solver Parameters ظاهر می شود.

روی جعبه Set Target Cell کلیک کنید و سپس سلول سود ما (سلول D12) را انتخاب کنید. روی کادر By Changing Cells کلیک کنید و سپس به محدوده D2:I2 اشاره کنید که حاوی پوند تولید شده از هر دارو است. کادر محاوره ای اکنون باید به شکل 27-3 باشد.

اکنون آماده اضافه کردن محدودیت ها به مدل هستیم. روی دکمه Add کلیک کنید. کادر محاوره ای Add Constraint را می بینید که در شکل 27-4 نشان داده شده است.

افزودن این محدودیت ها تضمین می کند که وقتی Solver ترکیب های مختلف را برای مقادیر متغیر سلول امتحان می کند، تنها ترکیب هایی در نظر گرفته می شوند که پارامترهای زیر را برآورده کنند:

در کادر محاوره ای Add Constraint روی OK کلیک کنید. پنجره Solver باید مانند شکل 27-7 باشد.

در کادر محاوره ای گزینه های حل این محدودیت را وارد می کنیم که تغییر سلول ها باید غیرمنفی باشد. روی دکمه Options در کادر محاوره ای Solver Parameters کلیک کنید. همانطور که در شکل 27-8 در صفحه بعد نشان داده شده است، کادر فرض مدل خطی و کادر فرض غیر منفی را علامت بزنید. روی OK کلیک کنید.

علامت زدن کادر فرض غیر منفی تضمین می کند که حل کننده تنها ترکیبی از سلول های در حال تغییر را در نظر می گیرد که در آنها هر سلول در حال تغییر یک مقدار غیر منفی را در نظر می گیرد. ما کادر فرض مدل خطی را علامت زدیم زیرا مسئله ترکیب محصول نوع خاصی از مسئله حل کننده است که مدل خطی نامیده می شود. اساساً یک مدل حل کننده تحت شرایط زیر خطی است:

سلول هدف با جمع کردن عبارات فرم (تغییر سلول)*(ثابت) محاسبه می شود.

هر محدودیت "نیاز مدل خطی" را برآورده می کند. این بدان معنی است که هر محدودیت با جمع کردن عبارات فرم (تغییر سلول)*(ثابت) و مقایسه مجموع با یک ثابت ارزیابی می شود.

چرا این مشکل حل کننده خطی است؟سلول هدف ما (سود) به صورت محاسبه می شود

(سود دارو 1 پوند)*(دارو 1 پوند تولید شده) + (سود دارو 2 پوند)*(دارو 2 پوند تولید شده) + .(سود دارو 6 پوند)*(دارو 6 پوند تولید شده)

این محاسبه الگویی را دنبال می کند که در آن با اضافه کردن شرایط فرم (تغییر سلول)*(ثابت) ، مقدار سلول هدف حاصل می شود.

محدودیت نیروی کار ما با مقایسه مقدار به دست آمده از (زایمان مورد استفاده در هر پوند دارو 1)*(داروی 1 پوند تولید شده) + (کار مورد استفاده در هر پوند دارو 2)*(داروی 2 پوند تولید شده) +… (به ازای هر پوند دارو 6)*(داروی 6 پوند تولید شده) به زایمان موجود.

بنابراین ، محدودیت نیروی کار با اضافه کردن شرایط فرم (تغییر سلول)*(ثابت) و مقایسه مبالغ با یک ثابت ارزیابی می شود. هم محدودیت نیروی کار و هم محدودیت مواد اولیه نیاز به مدل خطی را برآورده می کند.

محدودیت های تقاضا ما شکل می گیرد

هر محدودیت تقاضا همچنین نیاز به مدل خطی را برآورده می کند ، زیرا هر یک با افزودن شرایط فرم (تغییر سلول)*(ثابت) و مقایسه مبالغ با یک ثابت ارزیابی می شود.

با نشان دادن اینکه مدل مخلوط محصول ما یک مدل خطی است ، چرا باید اهمیت دهیم؟

اگر یک مدل حل کننده خطی باشد و ما مدل خطی فرض را انتخاب می کنیم ، حل کننده برای یافتن راه حل بهینه برای مدل حل کننده تضمین می شود. اگر یک مدل حل کننده خطی نباشد ، حل کننده ممکن است راه حل بهینه پیدا کند یا نتواند.

اگر یک مدل حل کننده خطی باشد و ما مدل خطی فرض را انتخاب می کنیم ، Solver از یک الگوریتم بسیار کارآمد (روش Simplex) برای یافتن راه حل بهینه مدل استفاده می کند. اگر یک مدل حل کننده خطی باشد و ما مدل خطی فرض را انتخاب نمی کنیم ، Solver از یک الگوریتم بسیار ناکارآمد (روش GRG2) استفاده می کند و ممکن است در یافتن راه حل بهینه مدل مشکل داشته باشد.

پس از کلیک بر روی OK در کادر گفتگوی Solver Options ، ما به کادر گفتگوی اصلی Solver باز می گردیم که در شکل 27-7 نشان داده شده است. هنگامی که ما روی حل کلیک می کنیم ، Solver یک راه حل بهینه (در صورت وجود) را برای مدل مخلوط محصول ما محاسبه می کند. همانطور که در فصل 26 بیان کردم ، یک راه حل بهینه برای مدل مخلوط محصول می تواند مجموعه ای از مقادیر تغییر سلول (پوند تولید شده از هر دارو) باشد که سود را نسبت به مجموعه تمام راه حل های امکان پذیر به حداکثر می رساند. باز هم ، یک راه حل امکان پذیر مجموعه ای از تغییر مقادیر سلولی است که همه محدودیت ها را برآورده می کند. در حال تغییر مقادیر سلولی نشان داده شده در شکل 27-9 یک راه حل امکان پذیر است زیرا تمام سطح تولید غیر منفی است ، سطح تولید از تقاضا تجاوز نمی کند و میزان مصرف منابع از منابع موجود تجاوز نمی کند.

مقادیر در حال تغییر سلول نشان داده شده در شکل 27-10 در صفحه بعد به دلایل زیر یک راه حل غیرقابل نفوذ را نشان می دهد:

ما بیشتر از تقاضا برای آن داروی 5 تولید می کنیم.

ما بیشتر از آنچه در دسترس است استفاده می کنیم.

ما از مواد اولیه بیشتر از آنچه در دسترس است استفاده می کنیم.

پس از کلیک بر روی حل ، Solver به سرعت راه حل بهینه نشان داده شده در شکل 27-11 را پیدا می کند. برای حفظ مقادیر راه حل بهینه در صفحه کار ، باید راه حل نگهدارنده را انتخاب کنید.

شرکت دارویی ما می تواند با تولید 596. 67 پوند داروی 4 ، 1084 پوند داروی 5 و هیچ یک از داروهای دیگر ، سود ماهانه خود را در سطح 6،625. 20 دلار به حداکثر برساند! ما نمی توانیم تعیین کنیم که آیا می توانیم به حداکثر سود 6،625. 20 دلار از راه های دیگر برسیم. تنها چیزی که می توانیم از آن اطمینان داشته باشیم این است که با منابع و تقاضای محدود ما ، هیچ راهی برای پرداخت بیش از 6،627. 20 دلار در این ماه وجود ندارد.

Suppose that demand for each product must be met. (See the No Feasible Solution worksheet in the file Prodmix.xlsx.) We then have to change our demand constraints from D2:I2 to D2:I2>= D8: i8. برای انجام این کار ، حل کننده را باز کنید ، D2: i2 را انتخاب کنید

Select>= ، و سپس OK را کلیک کنید. ما اکنون اطمینان حاصل کرده ایم که Solver فقط ارزشهای سلولی را که تمام خواسته ها را برآورده می کند ، در نظر می گیرد. وقتی روی حل کلیک می کنید ، پیام "حل کننده نمی تواند یک راه حل عملی پیدا کند". این پیام به معنای این نیست که ما در مدل خود اشتباه کردیم ، بلکه این است که با منابع محدود ما ، ما نمی توانیم تقاضا برای همه محصولات را برآورده کنیم. Solver به سادگی به ما می گوید که اگر می خواهیم تقاضا برای هر محصول را برآورده کنیم ، باید کار بیشتری ، مواد اولیه بیشتر یا بیشتر از هر دو را اضافه کنیم.