Решение прикладных задач в 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


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

        Активизируем Сервис=>Поиск решения и опишем условия решения задачи:
      • задайте ячейку B14, как содержащую целевую функцию4);
      • определите цель оптимизации - Максимальное значение;
      • укажите ячейку, значение которой будет изменяться при поиске наилучшего решения5) - B10;
      • при назначении параметров укажите на нелинейность модели. Для остальных параметров используйте установки по умолчанию, которые подходят для решения большинства задач.

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

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


          Содержание раздела