Использование средства «Поиск решения для определения затрат на рекламу, при которых прибыль от реализации товара будет максимальной

Практическое занятие № 6-7

Использование информационной технологии

Поиск решения

Цель: освоить методику применения средств Поиск решения

для решения нелинейных задач с помощью Excel.

План занятия:

1. Общие положения.

2. Использование средства «Поиск решения для определения затрат на рекламу, при которых прибыль от реализации товара будет максимальной.

Литература

1. Информационные технологии в маркетинге./ Под редакцией чл.-корр. Международной академии информатизации проф. Г.А. Титоренко - М.; ЮНИТИ-ДАНА,- 2003 - 335 с. Библ. КИБ

2. Михайлов А. Проектирование маркетинговой информационной системы / А. Михайлов // Маркетинг. – 2000. - № 2. – С. 27-36

3. Пінчук Н.С. Інформаційні системи і технології в маркетингу: Навч.-метод. посібник для самост. вивч. дисцип. / Н.С. Пінчук, Г.П. Галузинський, Н.С. Орленко. – К.: КНЕУ, 2001. – 296 с.

4. Бажин И.И. Информационные системы маркетинга. - М. - ГУ ВШЭ -2003 - 688 с.

5. Рыбальченко В. Маркетинг и современные информационные технологи: CRM / В. Рыбальченко // Отдел маркетинга. – 2003. - №8. – С. 58-60

6. Федосеев В.В. Экономико – математические методы и модели в маркетинге / В.В.Федосеев М. Финстатинформ 1996 Библ КИБ

1. Общие положения.

Книга Solverex.xls, входящая в состав Excel, содержит пример решения типичной задачи маркетинга: определения затрат на рекламу, при которых прибыль от реализации товара будет максимальной. Решение этой задачи позволяет определить: "Стоит ли вкладывать дополнительные средства в рекламу, чтобы увеличить прибыль?".

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

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

Использование средства «Поиск решения для определения затрат на рекламу, при которых прибыль от реализации товара будет максимальной

Исходные данные задачи состоят из 2-х блоков: "Планируемые показатели" и "Данные о продукции". Так, в 1-м квартале запланированы следующие показатели реализации:

• сезонный фактор (ячейка В2) - равным 0.9²⁾;
• затраты на заработную плату персонала (ячейка В9) - 8 тыс. грн;
• затраты на рекламу (ячейка В10) - 10 тыс. грн.

Данные о продукции:

• цена реализации (ячейка В17) - 40 грн;
• себестоимость (ячейка В18) - 25 грн.

Расчет планируемых показателей производят так:

• объем сбыта продукции³⁾ (ячейка В4) нелинейно зависит от сезонного фактора и затрат на рекламу

=35*B2*(B10+3000)^0,5

• доход с оборота определяется как ожидаемое количество проданных единиц продукции (ячейка В4), умноженное на себестоимость продукции, поэтому в ячейку В5 введем формулу

=B4*B17

• фраза "себестоимость реализованной продукции" на языке математики выглядит как

B6=B4*B18

• очевидно, что валовая прибыль, имеющая в электронной таблице адрес В7, определяется как

=B5-B6

• накладные расходы фирмы будем исчислять в объеме 15% дохода с оборота, то есть в ячейку B11 введем формулу

=0,15*B5

• валовые издержки рассчитаем как сумму затрат на заработную плату персонала, рекламу и накладные расходы, то есть

B12=СУММ(B9:B11)

• прибыль от продукции определим как валовую прибыль минус валовые издержки, то есть

B14=B7-B12

• рентабельность

B15=B14/B5

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

Рисунок 1 - Данные для поиска экстремума в задаче маркетинга

Активизируем Сервис => Поиск решения и опишем условия решения задачи:

• задайте ячейку B14, как содержащую целевую функцию⁴⁾;
• определите цель оптимизации - Максимальное значение;
• укажите ячейку, значение которой будет изменяться при поиске наилучшего решения⁵⁾ - B10;
• при назначении параметров укажите на нелинейность модели. Для остальных параметров используйте установки по умолчанию, которые подходят для решения большинства задач.

Щелкнув на кнопке Выполнить, Вы увидите следующий результат оптимизации (рис. 2).

Рисунок 2 - Результат оптимизации

Как видно из полученного решения при, затратах на рекламу 17 093 грн. прибыль от реализации товара будет максимальной. Однако следует отметить, что максимизация прибыли не обязательно соответствует наивысшему значению рентабельности. Ее значение уменьшилось до 8%.

Контрольное задание 1

Покажите, что функция

f = (x-1)² + y2 - 0,5cos(2z)

имеет минимальное значение -0,5 при x=1; y=-7,9E-07 и z=3,14159.

Указание

В качестве исходных данных для поиска минимума примите x=1, y=2 и z=3.

Контрольное задание 2

Покажите, что при ограничениях x + 2y <= 8, 2x - y <= 12, x >= 0, y >= 0 функция

f = x(2 - x) + 2y(2 - y)

имеет максимальное значение 3 при x=1 и y=1.

Указание

В качестве исходных данных для поиска минимума примите x=0 и y=0.

1) Эту задачу иногда называют "Транспортная".

2) Сезонный фактор отражает колебания спроса на товар в зависимости от времени года. Например, зимой чаще покупают теплые вещи.

3) Объем сбыта определяется количеством (штуками) проданной продукции.

4) Помните, что в адресе должна указываться ячейка, в которой содержится формула, а не числовое значение, дата, или текст.

5) Помните, что переменные - это числовые значения, а не даты, формулы или текст.