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

Построение математической модели

Фирма рекламирует свою продукцию с использованием четырех средств: телевизора, радио, газет и рекламных плакатов. Маркетинговые исследования показали, что эти средства приводят к увеличению прибыли соответственно на 10, 5, 7 и 4 доллара в расчете на 1 доллар, затраченный на рекламу. Распределение рекламного бюджета по различным видам рекламы подчинено следующим ограничениям:

а) Полный бюджет составляет 500000 долларов;

b) Следует расходовать не более 40% бюджета на телевидение и не более 20% бюджета на рекламные щиты;

с) Вследствие привлекательности для молодежной части населения различных музыкальных каналов на радио по этой позиции следует расходовать по крайней мере половину того, что планируется на телевидение.

Необходимо:

Сформулировать и решить задачу распределения средств по различным источникам для получения максимальной прибыли от рекламы;

Объяснить смысл данных отчета по устойчивости;

Определить изменится ли оптимальный план распределения средств, если увеличение прибыли от газетной рекламы снизится до 5 долларов в расчете на 1 доллар, затраченный на рекламу;

Определить, в какой вид рекламы будет выгоднее вложить дополнительные средства в случае увеличения бюджета фирмы.

Составим математическую модель задачи, выбрав в качестве переменных Решение задач оптимизации в Excel, х4 - количество средств, затраченных на телевидение, рекламные плакаты, радио и газеты соответственно. Тогда ожидаемая прибыль от рекламы может быть подсчитана по формуле


Решение задач оптимизации в Excel (1)


Переменные задачи удовлетворяют ограничениям


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

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

Решение задач оптимизации в Excel (2)

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


в левых частях которых вычислены затраты денежных ресурсов на телевидение, радио, газеты и рекламные плакаты, а в правых частях записаны максимально возможные запасы средств на эти ресурсы. Учитывая, что переменные задачи по своему экономическому смыслу не могут принимать отрицательные значения, получаем математическую модель задачи оптимального распределения денежных ресурсов с целью получения максимального дохода от рекламы.


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

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

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

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

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

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


Очевидно, что построенная модель имеет линейную структуру и, следовательно, является задачей линейного программирования.

Создание электронной модели

Чтобы привлечь компьютер к решению этой задачи необходимо ввести исходные данные на лист Excel.

Сначала заносим в таблицу неизменяемые данные, а затем заполняем изменяемые ячейки, в которых расположены компоненты плана Решение задач оптимизации в Excel. На этапе ввода исходных данных сюда заносятся любые числа, например, единицы. После решения в этих ячейках будут находиться оптимальные значения переменных. Целевая функция (суммарный доход) и левые части ограничений (Затраты по видам реклам) подсчитываются в соответствие с составленной моделью по формуле (1) и левым частям (2).

Вид рекламы Х1 Х2 Х3 Х4

Суммарный

доход

План запуска рекламы 1 1 1 1
Прибыль от 1 затраченного $ 10 4 5 7 26

Затраты по видам реклам Затраты по видам реклам Прибыль от вида рекламы

Запасы денежных

ресурсов

(тыс. $)


Х1 Х2 Х3 Х4

На телевидение 1 0 0 0 1 200
На рекламные щиты 0 1 0 0 1 100
На радио 0 0 1 0 1 100
На газеты 0 0 0 1 1 100
Общие затраты на все виды реклам 1 1 1 1 4 500

Отчет по результатам

Отчет состоит из трех таблиц, расположенных на одном листе книги Excel.









Целевая ячейка (Максимум)




Ячейка

Имя

Исходное значение

Результат




$F$3 Прибыль от 1 затраченного $ Сумм-ый доход 26 3700















Изменяемые ячейки




Ячейка

Имя

Исходное значение

Результат




$B$2 План запуска рекламы Х1 1 200


$C$2 План запуска рекламы Х2 1 0


$D$2 План запуска рекламы Х3 1 200


$E$2 План запуска рекламы Х4 1 100















Ограничения




Ячейка

Имя

Значение

Формула

Статус

Разница


$F$43 Бюджет затрачиваемый на рекламу 500 $F$43<=$G$12 связанное 0

$B$2 План запуска рекламы Х1 200 $B$2<=$G$8 связанное 0

$C$2 План запуска рекламы Х2 0 $C$2<=$G$9 не связан. 100

$D$2 План запуска рекламы Х3 200 $D$2>=$G$10 не связан. 100

$E$2 План запуска рекламы Х4 100 $E$2<=$G$11 связанное 0








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

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

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

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

Из отчета по результатам применительно к данной задаче видим, что оптимальный план распределения средств состоит в еженедельных затратах на телевидение в размере 200 000$, на газеты в размере 100 000$ и на радио в размере 200 000$, а вкладывать денежные средства в рекламные щиты не выгодно. Таким образом Решение задач оптимизации в Excel, и максимальный суммарный доход Решение задач оптимизации в Excel 000$. При этом полный бюджет на рекламу продукции используются полностью, т.е. является дефицитным, а также денежные средства отпущенные на телевидение и газеты расходуется полностью и также являются дефицитными, а денежные средства на рекламные щиты расходовать нецелесообразно.

Отчет по устойчивости

Отчет по устойчивости содержит информацию, позволяющую провести постоптимальный анализ решения задачи. Цель анализа заключается в определении таких границ изменения исходных данных задачи (коэффициентов целевой функции и правых частей ограничений), при которых ранее найденный оптимальный план сохраняет свою оптимальность и в изменившихся условиях.










Изменяемые ячейки







Результ.

Нормир.

Целевой

Допустимое

Допустимое

Ячейка

Имя

значение

стоимость

Коэффициент

Увеличение

Уменьшение

$B$2 План запуска рекламы Х1 200 5 10 1E+30 5

$C$2 План запуска рекламы Х2 0 -1 4 1 1E+30

$D$2 План запуска рекламы Х3 200 0 5 2 1

$E$2 План запуска рекламы Х4 100 2 7 1E+30 2








Ограничения







Результ.

Теневая

Ограничение

Допустимое

Допустимое

Ячейка

Имя

значение

Цена

Правая часть

Увеличение

Уменьшение

$F$43 Бюджет затрачиваемый на рекламу 500 5 500 1E+30 100









Отчет состоит из двух таблиц, расположенных на одном листе книги Excel.

В первой таблице (Изменяемые ячейки) приводится следующая информация о переменных:

результирующее значение - оптимальные значения переменных;

нормированная стоимость - ее величина равна значению соответствующей симплексной оценки с противоположным знаком. Для невыпускаемой продукции нормированная стоимость показывает, на сколько изменится целевая функция при принудительном включении единицы этой продукции в оптимальное решение;

коэффициенты целевой функции;

предельные значения приращения коэффициентов целевой функции, которые показывают на сколько можно увеличить и уменьшить каждый целевой коэффициент в отдельности, сохраняя при этом оптимальные значения переменных.

Во второй таблице (ограничения) приводятся аналогичные значения для ограничений задачи:

величины использованных ресурсов (левые части ограничений) при оптимальном плане выпуска продукции;

теневые цены, т.е. оптимальные значения двойственных переменных, которые показывают, как изменится целевая функция при изменении соответствующего запаса ресурса на единицу;

исходные запасы ресурсов (правые части ограничений);

предельные значения приращений ресурсов (их допустимое увеличение и уменьшение), при которых сохраняется оптимальный план двойственной задачи и базисный набор переменных, входящих в оптимальное решение исходной задачи (ассортимент выпускаемой продукции).

Используем результаты отчета по устойчивости для проведения постоптимального анализа в данной задаче:


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

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

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


Исследуем сначала влияние на оптимальный план изменений коэффициентов целевой функции - прибыль от 1 затраченного $ на рекламу определенного вида.

Из первой таблицы следует, что оптимальный план Решение задач оптимизации в Excel затрат на рекламу не изменится, если первоначальная прибыль Решение задач оптимизации в Excel долл. рекламы Х1 возрастет на 1Е+30 доллара или уменьшится на 5 доллара. Другими словами, условие сохранения оптимального плана Решение задач оптимизации в Excel при изменении прибыли от рекламы Х1 имеет вид: Решение задач оптимизации в Excel или Решение задач оптимизации в Excel.

Аналогично, условие сохранения оптимального плана Решение задач оптимизации в Excel при изменении прибыли Решение задач оптимизации в Excel рекламы Х3 имеет вид: Решение задач оптимизации в Excel или Решение задач оптимизации в Excel, и условие сохранения оптимального плана Решение задач оптимизации в Excel при изменении прибыли Решение задач оптимизации в Excel рекламы Х4 имеет вид: Решение задач оптимизации в Excel или Решение задач оптимизации в Excel.

Наконец, при изменении прибыли от рекламы Х2 ранее найденный план Решение задач оптимизации в Excel останется оптимальным, если исходная цена Решение задач оптимизации в Excel возрастет не более чем на 1 доллар. В то же время любое уменьшение цены Решение задач оптимизации в Excel не влияет на оптимальный план Решение задач оптимизации в Excel, так как число Решение задач оптимизации в Excel равно Решение задач оптимизации в Excel, т.е. практически является бесконечно большим числом. Таким образом, условие сохранения оптимальности плана Решение задач оптимизации в Excel при изменении цены Решение задач оптимизации в Excel примет вид Решение задач оптимизации в Excel. Это означает, что рекламу Х2 не выгодно запускать (Решение задач оптимизации в Excel), если прибыль от нее будет не выше 5 долларов. Если же прибыль Решение задач оптимизации в Excel превысит 5 долларов от использования рекламных щитов, то план Решение задач оптимизации в Excel перестанет быть оптимальным, и в новом оптимальном решении Решение задач оптимизации в Excel будет положительным т.е. использование рекламы в виде рекламных щитов станет выгодным.

Отчет по пределам

Третий отчет для данной задачи, называемый отчетом по пределам, состоит из двух таблиц.

Первая таблица в комментариях не нуждается.












Целевое








Ячейка

Имя

Значение







$F$3 Прибыль от 1 затраченного $ Сумм-ый доход 3700
























Изменяемое



Нижний

Целевой


Верхний

Целевой

Ячейка

Имя

Значение


предел

результат


предел

результат

$B$2 План запуска рекламы Х1 200
0 1700
200 3700
$C$2 План запуска рекламы Х2 0
0 3700
-1,15597E-09 3700
$D$2 План запуска рекламы Х3 200
100 3200
200 3700
$E$2 План запуска рекламы Х4 100
0 3000
100 3700










Во второй таблице показано, в каких пределах может изменяться выпуск продукции, вошедшей в оптимальное решение, при сохранении структуры оптимального плана выпуска:

приводятся значения Решение задач оптимизации в Excel в оптимальном решении;

приводятся нижние и верхние пределы изменения значений Решение задач оптимизации в Excel и значения целевой функции при выпуске данного типа продукции на нижнем и верхнем пределах.

Так например, если из оптимального плана исключить запуск рекламы Х1, положив Решение задач оптимизации в Excel и сохранить оптимальные значения остальных переменных, то доход от рекламы продукции будет равен


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


Значения целевой функции - дохода от рекламы продукции, вошедшей в оптимальное решение на верхних пределах везде равно максимальной величине 3700 долларов.

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

Размещено на

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту

Похожие рефераты: