Използване на Solver за определяне на оптималната комбинация от продукти

Как мога да определя месечната комбинация от продукти, която увеличава максимално доходността?

Дружествата често трябва да определят количеството на всеки продукт, което да произвежда ежемесечно. В най-простата си форма проблемът с комбинацията продукти включва как да се определи размерът на всеки продукт, който трябва да се произвежда през един месец, за да се увеличи печалбата. Продуктовият микс обикновено трябва да се придържа към следните ограничения:

• Продуктовият микс не може да използва повече ресурси, отколкото са налични.

• Има ограничено търсене за всеки продукт. Не можем да произвеждаме повече от даден продукт за един месец, отколкото се диктува търсенето, защото излишното производство се губи (например нетрайни лекарства).

Нека сега решим следния пример за проблема с комбинацията продукти. Можете да намерите решението на този проблем във файла 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 фунта суровина. Как тази фирма може да увеличи месечната си печалба?

Ако не знаехме нищо за 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 ще се появи, както е показано на фигура 27–2.

Щракнете върху полето Задаване на целева клетка и след това изберете нашата клетка за печалба (клетка 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)

• Е2<=E8 (количеството произвеждано от лекарството 2 е по-малко или равно на търсенето на лекарство 2)

• F2<=F8 (количеството произвеждано от лекарството 3 е по-малко или равно на търсенето на лекарство 3)

• G2<=G8 (количеството произвеждано от лекарството 4 е по-малко или равно на търсенето на наркотици 4)

• H2<=H8 (количеството произвеждано от лекарството 5 е по-малко или равно на търсенето на наркотици 5)

• I2<=I8 (количеството произвеждано от лекарството 6 е по-малко или равно на търсенето на наркотици 6)

Щракнете върху OK в диалоговия прозорец Добавяне на ограничение. Прозорецът на Solver трябва да изглежда като фигура 27-7.

Въвеждаме ограничението, че променящите се клетки трябва да са нео отрицателни в диалоговия прозорец Опции на Solver. Щракнете върху бутона Опции в диалоговия прозорец Параметри на Solver. Отметнете квадратчето Предполага се линеен модел и полето Предполага се, че не е отрицателно, както е показано на фигура 27-8 на следващата страница. Щракнете върху OK.

Отмятането на полето Предполага се, че нео отрицателна стойност гарантира, че 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 използва много ефективен алгоритъм (метода simplex), за да намери оптималното решение на модела. Ако модел на Solver е линеен и не изберем Предполага се линеен модел, Solver използва много неефективен алгоритъм (метода GRG2) и може да има затруднения при намирането на оптималното решение на модела.

След като щракнете върху OK в диалоговия прозорец Опции на Solver, се връщаме към основния диалогов прозорец "Решатер", показан по-рано на Фигура 27-7. Когато щракнем върху Solve, Solver изчислява оптимално решение (ако съществува такова) за нашия модел на комбинация продукти. Както заявих в глава 26, оптимално решение на модела на продуктовия микс ще бъде набор от променящи се стойности на клетките (фунтове, произведени от всяко лекарство), което максимизира печалбата от набора от всички допустими решения. И отново, допустимо решение е набор от променящи се стойности на клетки, които удовлетворяват всички ограничения. Променящите се стойности на клетките, показани на фигура 27–9, са допустимо решение, тъй като всички производствени нива не са отрицателни, производствените нива не превишават търсенето и използването на ресурси не превишава наличните ресурси.

Променящите се стойности на клетките, показани на фигура 27–10 на следващата страница, представят нерешимо решение поради следните причини:

• Ние произвеждаме повече от наркотици 5 от търсенето за него.

• Използваме повече труд от това, което се предлага.

• Използваме повече суровини от наличните.

След като щракнете върху "Решаване", Solver бързо намира оптималното решение, показано на фигура 27–11. Трябва да изберете Запази решението на Solver, за да запазите оптималните стойности на решението в работния лист.

Нашата лекарствена компания може да увеличи месечната си печалба на ниво от $6,625.20 чрез производство на 596.67 паунда на наркотици 4, 1084 паунда на наркотици 5, и никой от другите наркотици! Не можем да определим дали можем да постигнем максималната печалба от 6625,20 лв. по други начини. Всичко, което можем да сме сигурни е, че с нашите ограничени ресурси и търсене няма начин да направим повече от 6627,20 лв. този месец.