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


         

в состав Excel, содержит пример


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

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