Решение задач оптимизации в ExcelПостроение математической модели
Фирма рекламирует свою продукциюс использованием четырех средств: телевизора, радио, газет и рекламных плакатов.Маркетинговые исследования показали, что эти средства приводят к увеличению прибылисоответственно на 10, 5, 7 и 4 доллара в расчете на 1 доллар, затраченный на рекламу.Распределение рекламного бюджета по различным видам рекламы подчинено следующимограничениям:
а) Полный бюджет составляет 500000долларов;
b) Следует расходовать не более 40% бюджета на телевидениеи не более 20% бюджета на рекламные щиты;
с) Вследствие привлекательностидля молодежной части населения различных музыкальных каналов на радио по этой позицииследует расходовать по крайней мере половину того, что планируется на телевидение.
Необходимо:
1. Сформулировать и решить задачу распределениясредств по различным источникам для получения максимальной прибыли от рекламы;
2. Объяснить смысл данных отчета по устойчивости;
3. Определить изменится ли оптимальныйплан распределения средств, если увеличение прибыли от газетной рекламы снизитсядо 5 долларов в расчете на 1 доллар, затраченный на рекламу;
4. Определить, в какой вид рекламы будетвыгоднее вложить дополнительные средства в случае увеличения бюджета фирмы.
Составим математическую модельзадачи, выбрав в качестве переменных />, х4 — количествосредств, затраченных на телевидение, рекламные плакаты, радио и газеты соответственно.Тогда ожидаемая прибыль от рекламы может быть подсчитана по формуле
/> (1)
Переменные задачи удовлетворяютограничениям
/>
/>
/> (2)
/>
в левых частях которых вычисленызатраты денежных ресурсов на телевидение, радио, газеты и рекламные плакаты, а вправых частях записаны максимально возможные запасы средств на эти ресурсы. Учитывая,что переменные задачи по своему экономическому смыслу не могут принимать отрицательныезначения, получаем математическую модель задачи оптимального распределения денежныхресурсов с целью получения максимального дохода от рекламы.
/>
/>
/>
/>
/>
/>.
Очевидно, что построенная модельимеет линейную структуру и, следовательно, является задачей линейного программирования.
Создание электронной модели
Чтобы привлечь компьютер к решениюэтой задачи необходимо ввести исходные данные на лист Excel.
Сначала заносим в таблицу неизменяемыеданные, а затем заполняем изменяемые ячейки, в которых расположены компоненты плана/>. На этапеввода исходных данных сюда заносятся любые числа, например, единицы. После решенияв этих ячейках будут находиться оптимальные значения переменных. Целевая функция(суммарный доход) и левые части ограничений (Затраты по видам реклам) подсчитываютсяв соответствие с составленной моделью по формуле (1) и левым частям (2). Вид рекламы Х1 Х2 Х3 Х4
Суммарный
доход План запуска рекламы 1 1 1 1 Прибыль от 1 затраченного $ 10 4 5 7 26 Затраты по видам реклам Затраты по видам реклам Прибыль от вида рекламы
Запасы денежных
ресурсов
(тыс. $) Х1 Х2 Х3 Х4 На телевидение 1 1 200 На рекламные щиты 1 1 100 На радио 1 1 100 На газеты 1 1 100 Общие затраты на все виды реклам 1 1 1 1 4 500 /> /> /> /> /> /> /> /> Отчет по результатам
Отчет состоит из трех таблиц,расположенных на одном листе книги Excel. Целевая ячейка (Максимум)
Ячейка
Имя
Исходное значение
Результат $F$3 Прибыль от 1 затраченного $ Сумм-ый доход 26 3700 Изменяемые ячейки
Ячейка
Имя
Исходное значение
Результат $B$2 План запуска рекламы Х1 1 200 $C$2 План запуска рекламы Х2 1 $D$2 План запуска рекламы Х3 1 200 $E$2 План запуска рекламы Х4 1 100 Ограничения
Ячейка
Имя
Значение
Формула
Статус
Разница $F$43 Бюджет затрачиваемый на рекламу 500 $F$43=$G$10 не связан. 100 $E$2 План запуска рекламы Х4 100 $E$2 /> /> /> /> /> /> />
В первой таблице выводятся сведенияо целевой функции. В столбце Исходное значение приведено значение целевой функциидо начала вычислений, в столбце Результат — после оптимизации.
Следующая таблица содержит значенияискомых переменных (изменяемых ячеек) до и после решения задачи.
оптимизация математическая электронная модель
Последняя таблица показывает значениялевых частей ограничений на оптимальном решении задачи. В столбце Формула приведенызависимости, которые были введены в диалоговом окне Поиск решения, в столбце Разницапоказано количество неиспользованного ресурса. Если ресурс дефицитен, т.е. используетсяполностью, то в столбце Статус указывается связанное (соответствующее ограничениеактивно); при неполном использовании ресурса в этом столбце указывается не связанное(ограничение не активно).
Из отчета по результатам применительнок данной задаче видим, что оптимальный план распределения средств состоит в еженедельныхзатратах на телевидение в размере 200 000$, на газеты в размере 100 000$ и на радиов размере 200 000$, а вкладывать денежные средства в рекламные щиты не выгодно.Таким образом />, и максимальный суммарный доход /> 000$. При этомполный бюджет на рекламу продукции используются полностью, т.е. является дефицитным,а также денежные средства отпущенные на телевидение и газеты расходуется полностьюи также являются дефицитными, а денежные средства на рекламные щиты расходоватьнецелесообразно.Отчет по устойчивости
Отчет по устойчивости содержитинформацию, позволяющую провести постоптимальный анализ решения задачи. Цель анализазаключается в определении таких границ изменения исходных данных задачи (коэффициентовцелевой функции и правых частей ограничений), при которых ранее найденный оптимальныйплан сохраняет свою оптимальность и в изменившихся условиях. Изменяемые ячейки
Результ.
Нормир.
Целевой
Допустимое Допустимое
Ячейка
Имя
значение
стоимость
Коэффициент
Увеличение Уменьшение $B$2 План запуска рекламы Х1 200 5 10 1E+30 5 $C$2 План запуска рекламы Х2 -1 4 1 1E+30 $D$2 План запуска рекламы Х3 200 5 2 1 $E$2 План запуска рекламы Х4 100 2 7 1E+30 2 Ограничения
Результ.
Теневая
Ограничение
Допустимое Допустимое
Ячейка
Имя
значение
Цена
Правая часть
Увеличение Уменьшение $F$43 Бюджет затрачиваемый на рекламу 500 5 500 1E+30 100 /> /> /> /> /> /> /> /> />
Отчет состоит из двух таблиц,расположенных на одном листе книги Excel.
В первой таблице (Изменяемые ячейки)приводится следующая информация о переменных:
· результирующее значение — оптимальные значения переменных;
· нормированная стоимость — ее величина равна значению соответствующейсимплексной оценки с противоположным знаком. Для невыпускаемой продукции нормированнаястоимость показывает, на сколько изменится целевая функция при принудительном включенииединицы этой продукции в оптимальное решение;
· коэффициенты целевой функции;
· предельные значения приращения коэффициентов целевой функции, которыепоказывают на сколько можно увеличить и уменьшить каждый целевой коэффициент в отдельности,сохраняя при этом оптимальные значения переменных.
Во второй таблице (ограничения)приводятся аналогичные значения для ограничений задачи:
· величины использованных ресурсов (левые части ограничений) при оптимальномплане выпуска продукции;
· теневые цены, т.е. оптимальные значения двойственных переменных, которыепоказывают, как изменится целевая функция при изменении соответствующего запасаресурса на единицу;
· исходные запасы ресурсов (правые части ограничений);
· предельные значения приращений ресурсов (их допустимое увеличениеи уменьшение), при которых сохраняется оптимальный план двойственной задачи и базисныйнабор переменных, входящих в оптимальное решение исходной задачи (ассортимент выпускаемойпродукции).
Используем результаты отчета поустойчивости для проведения постоптимального анализа в данной задаче:
/>
/>
/>
Исследуем сначала влияние на оптимальныйплан изменений коэффициентов целевой функции — прибыль от 1 затраченного $ на рекламуопределенного вида.
Из первой таблицы следует, чтооптимальный план /> затрат на рекламу не изменится, еслипервоначальная прибыль /> долл. рекламы Х1 возрастет на 1Е+30доллара или уменьшится на 5 доллара. Другими словами, условие сохранения оптимальногоплана /> приизменении прибыли от рекламы Х1 имеет вид: /> или />.
Аналогично, условие сохраненияоптимального плана /> при изменении прибыли /> рекламы Х3 имеет вид: /> или />, и условие сохраненияоптимального плана /> при изменении прибыли /> рекламы Х4 имеет вид: /> или />.
Наконец, при изменении прибылиот рекламы Х2 ранее найденный план /> останется оптимальным, если исходнаяцена /> возрастетне более чем на 1 доллар. В то же время любое уменьшение цены /> не влияет на оптимальныйплан />, таккак число /> равно/>, т.е. практическиявляется бесконечно большим числом. Таким образом, условие сохранения оптимальностиплана /> приизменении цены /> примет вид />. Это означает, что рекламуХ2 невыгодно запускать (/>), если прибыль от нее будет не выше5 долларов. Если же прибыль /> превысит 5 долларов от использованиярекламных щитов, то план /> перестанет быть оптимальным, и в новомоптимальном решении /> будет положительным т.е. использованиерекламы в виде рекламных щитов станет выгодным.Отчет по пределам
Третий отчет для данной задачи,называемый отчетом по пределам, состоит из двух таблиц.
Первая таблица в комментарияхне нуждается.
Целевое
Ячейка
Имя
Значение $F$3 Прибыль от 1 затраченного $ Сумм-ый доход 3700
Изменяемое
Нижний
Целевой
Верхний
Целевой
Ячейка
Имя
Значение
предел
результат
предел
результат $B$2 План запуска рекламы Х1 200 1700 200 3700 $C$2 План запуска рекламы Х2 3700 -1,15597E-09 3700 $D$2 План запуска рекламы Х3 200 100 3200 200 3700 $E$2 План запуска рекламы Х4 100 3000 100 3700
Во второй таблице показано, вкаких пределах может изменяться выпуск продукции, вошедшей в оптимальное решение,при сохранении структуры оптимального плана выпуска:
· приводятся значения /> в оптимальном решении;
· приводятся нижние и верхние пределыизменения значений /> и значения целевой функции при выпускеданного типа продукции на нижнем и верхнем пределах.
Так например, если из оптимального плана исключить запуск рекламыХ1, положив /> и сохранить оптимальные значения остальныхпеременных, то доход от рекламы продукции будет равен
/>
Значения целевой функции — доходаот рекламы продукции, вошедшей в оптимальное решение на верхних пределах везде равномаксимальной величине 3700 долларов.
Рассмотренный пример показывает,как использование средства «Поиск решения» облегчает задачу принятия оптимальныхрешений в экономике.