Решение задач оптимизации в ExcelПостроение математической модели

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

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

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

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

Необходимо:

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

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

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

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

Составим математическую модель задачи, выбрав в качестве переменных , х₄ - количество средств, затраченных на телевидение, рекламные плакаты, радио и газеты соответственно. Тогда ожидаемая прибыль от рекламы может быть подсчитана по формуле
 (1)
Переменные задачи удовлетворяют ограничениям




 (2)


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










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

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

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

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

Целевая ячейка (Максимум)
	Ячейка	Имя		Исходное значение	Результат
	$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

Изменяемые ячейки
				Результ.	Нормир.	Целевой	Допустимое	Допустимое
	Ячейка	Имя		значение	стоимость	Коэффициент	Увеличение	Уменьшение
	$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

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

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

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





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

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

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

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

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

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

Размещено на Allbest.ru