Реферат: Решение задач оптимизации в Excel

Решение задач оптимизации в 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$12 связанное $B$2 План запуска рекламы Х1 200 $B$2<=$G$8 связанное $C$2 План запуска рекламы Х2 $C$2<=$G$9 не связан. 100 $D$2 План запуска рекламы Х3 200 $D$2>=$G$10 не связан. 100 $E$2 План запуска рекламы Х4 100 $E$2<=$G$11 связанное /> /> /> /> /> /> /> />

В первой таблице выводятся сведенияо целевой функции. В столбце Исходное значение приведено значение целевой функциидо начала вычислений, в столбце Результат — после оптимизации.

Следующая таблица содержит значенияискомых переменных (изменяемых ячеек) до и после решения задачи.

оптимизация математическая электронная модель

Последняя таблица показывает значениялевых частей ограничений на оптимальном решении задачи. В столбце Формула приведенызависимости, которые были введены в диалоговом окне Поиск решения, в столбце Разницапоказано количество неиспользованного ресурса. Если ресурс дефицитен, т.е. используетсяполностью, то в столбце Статус указывается связанное (соответствующее ограничениеактивно); при неполном использовании ресурса в этом столбце указывается не связанное(ограничение не активно).

Из отчета по результатам применительнок данной задаче видим, что оптимальный план распределения средств состоит в еженедельныхзатратах на телевидение в размере 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 долларов.

Рассмотренный пример показывает,как использование средства «Поиск решения» облегчает задачу принятия оптимальныхрешений в экономике.