Решение прикладных задач в Excel


Задачи оптимизации - часть 3


7.2.2 Нахождение экстремума без ограничений

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

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

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

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

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

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

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

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

  • объем сбыта продукции3) (ячейка В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




    Начало  Назад  Вперед