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

         

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


Книга 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



      Содержание  Назад  Вперед