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

В тази статия се обсъжда използването на Solver – добавка на Microsoft Excel, която можете да използвате за условен анализ, за да определите оптимална продуктова комбинация.

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

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

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

Нека сега решим следния пример за проблема с продуктовия микс. Можете да намерите решение на този проблем в 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 само на етапа "проба и грешка". По същество 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, както е показано на фигура 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)
• E2<=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 е линеен? Нашата целева клетка (печалба) се изчислява като

(Лекарство 1 печалба на паунд)*(Произведено лекарство 1 паунд) +
(Лекарство 2 печалба на паунд)*(Произведено лекарство 2 паунда) + ...
(Печалба от лекарство 6 на фунт)* (Произведено лекарство 6 паунда)

Това изчисление следва модел, при който стойността на целевата клетка се извлича чрез събиране на членове от вида (променяща се клетка)*(константа).

Нашето ограничение на труда се оценява чрез сравняване на стойността, получена от (Труд, използван за килограм Лекарство 1)*(Произведено лекарство 1 паунда) + (Труд, използван за килограм Лекарство 2)*(Произведено лекарство 2 паунда)+ ... (Труд, използван за килограм лекарство 6)* (произведено лекарство 6 паунда) към наличния труд.

Следователно трудоограничението се оценява чрез събиране на членовете на формата (променяща се клетка)*(константа) и сравняване на сумите с константа. Както ограничението на труда, така и ограничението на суровината удовлетворяват изискването на линейния модел.

Нашите ограничения в търсенето приемат формата

(произведено лекарство 1)<=(Наркотик 1 Търсене)
(Произведено лекарство 2)<=(Търсене на лекарство 2)
§
(Произведено лекарство 6)<=(Търсене на лекарство 6)

Всяко ограничение на търсенето също така удовлетворява изискването на линейния модел, защото всяко се оценява чрез събиране на членовете на формата (променяща се клетка)*(константа) и сравняване на сумите с константа.

След като показахме, че нашият модел на продуктовия микс е линеен модел, защо трябва да ни интересува?

• Ако моделът на Solver е линеен и изберем "Предполагане на линеен модел", Solver гарантирано ще намери оптималното решение за модела на Solver. Ако моделът на Solver не е линеен, Solver може да намери или да не намери оптималното решение.
• Ако моделът на Solver е линеен и изберем "Предполагам линеен модел", Solver използва много ефективен алгоритъм (симплексния метод), за да намери оптималното решение на модела. Ако моделът на Solver е линеен и не изберем "Предполагаем линеен модел", Solver използва много неефективен алгоритъм (GRG2 метода) и може да има затруднения при намирането на оптималното решение на модела.

След като щракнете върху OK в диалоговия прозорец "Опции на Solver", се връщаме към основния диалогов прозорец на Solver, показан по-рано на фигура 27-7. Когато щракнем върху Solve, 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 този месец.

Моделът на Solver винаги ли има решение?

Да предположим, че търсенето за всеки продукт трябва да бъде удовлетворено. (Вижте работния лист " Няма осъществимо решение " във файла Prodmix.xlsx.) След това трябва да променим нашите ограничения на търсенето от D2:I2<=D8:I8 на D2:I2>=D8:I8. За да направите това, отворете Solver, изберете ограничението D2:I2<=D8:I8 и след това щракнете върху "Промяна". Появява се диалоговият прозорец "Промяна на ограничение", показан на фигура 27-12.

Изберете >= и след това щракнете върху OK. Сега се уверихме, че Solver ще обмисля промяна само на стойностите на клетки, които отговарят на всички изисквания. Когато щракнете върху Solve, ще видите съобщението, че "Solver не можа да намери осъществимо решение". Това послание не означава, че сме допуснали грешка в нашия модел, а по-скоро, че с нашите ограничени ресурси не можем да отговорим на търсенето на всички продукти. Solver просто ни казва, че ако искаме да отговорим на търсенето на всеки продукт, трябва да добавим повече труд, повече суровини или повече и двете.

Какво означава, ако един модел на Solver даде резултата от "Множество стойности не се сходят"?

Нека видим какво ще се случи, ако позволим неограничено търсене на всеки продукт и позволим да се произвеждат отрицателни количества от всеки наркотик. (Можете да видите тази задача със Solver в работния лист "Задаване на стойности не се сходят " във файловия Prodmix.xlsx.) За да намерите оптималното решение в тази ситуация, отворете Solver, щракнете върху бутона "Опции" и изчистете отметката от полето "Приемане, че не е отрицателно". В диалоговия прозорец Параметри на Solver изберете ограничението на търсенето D2:I2<=D8:I8 и след това щракнете върху "Изтрий", за да премахнете ограничението. Когато щракнете върху Solve, Solver връща съобщението "Задаване на стойностите на клетките да не се сходят". Това съобщение означава, че ако целевата клетка трябва да бъде максимизирана (както в нашия пример), има осъществими решения с произволно големи стойности на целевите клетки. (Ако целевата клетка трябва да бъде намалена, съобщението "Задаване на стойностите на клетките да не се сходят" означава, че има осъществими решения с произволно малки стойности на целевите клетки.) В нашата ситуация, позволявайки отрицателното производство на лекарство, ние всъщност "създаваме" ресурси, които могат да бъдат използвани за производство на произволно големи количества други лекарства. Предвид неограниченото ни търсене, това ни позволява да правим неограничени печалби. В реална ситуация не можем да направим безкрайно количество пари. Накратко, ако виждате "Задайте стойности не се сходят", това означава, че вашият модел има грешка.

Проблеми

1. Да предположим, че нашата фармацевтична компания може да закупи до 500 часа труд за 1 долар повече на час от настоящите разходи за труд. Как можем да увеличим печалбата?

2. В завод за производство на чипове четирима техници (A, B, C и D) произвеждат три продукта (продукти 1, 2 и 3). Този месец производителят на чипове може да продаде 80 единици от Продукт 1, 50 единици Продукт 2 и най-много 50 единици Продукт 3. Техник А може да произвежда само продукти 1 и 3. Техник Б може да произвежда само продукти 1 и 2. Техник В може да направи само продукт 3. Техник D може да направи само продукт 2. За всяка произведена единица продуктите допринасят за следната печалба: Продукт 1, $6; Продукт 2, $7; и Продукт 3, $10. Времето (в часове), необходимо на всеки техник за производство на продукт, е както следва:

   Продукт	Техник А	Техник Б	Техник В	Техник Г
   1	2	2,5	Не може да се направи	Не може да се направи
   2	Не може да се направи	3	Не може да се направи	3,5
   3	3	Не може да се направи	4	Не може да се направи

3. Всеки техник може да работи до 120 часа на месец. Как производителят на чипове може да увеличи максимално месечната си печалба? Да предположим, че може да се произведе дробен брой единици.

4. Завод за производство на компютри произвежда мишки, клавиатури и джойстици за видеоигри. Печалбата на единица, използването на труд на единица, месечното търсене и използването на единица машинно време са дадени в следващата таблица:

   	Мишки	Клавиатури	Джойстици
   Печалба/единица	8 щ.д.	11 щ.д.	9 щ.д.
   Използване на труд/единица	.2 часа	.3 часа	.24 часа
   Машинно време/единица	0,04 часа	0,055 часа	0,04 часа
   Месечно търсене	15 000	27,000	11,000

5. Всеки месец са налични общо 13 000 работни часа и 3000 часа машинно време. Как производителят може да увеличи месечния си принос на печалбата от завода?

6. Разрешаване на нашия пример с лекарството, като приемем, че трябва да бъде задоволено минимално търсене от 200 единици за всяко лекарство.

7. Джейсън прави диамантени гривни, колиета и обеци. Той иска да работи максимум 160 часа на месец. Той има 800 унции диаманти. Печалбата, работното време и унциите диаманти, необходими за производството на всеки продукт, са дадени по-долу. Ако търсенето на всеки продукт е неограничено, как може Джейсън да увеличи печалбата си?

   Продукт	Единична печалба	Работни часове за единица	Унции диаманти за единица
   Гривна	300 лв.	.35	1,2
   Колие	200 лв.	.15	.75
   Обеци	100 лв.	0,05	.5