33_Laboratornaya_rabota_4

ЛАБОРАТОРНАЯ РАБОТА №4
Оптимальный план затрат на рекламу
1. Постановка задачи
При разработке годового финансового плана деятельности фирмы необходимо определить расходы на рекламу для получения наибольшей прибыли.
Определение проблемной системы. Предварительно проблемная система должна включать:
планируемые показатели сокращенного баланса расходов, доходов и прибыли фирмы;
модели (формулы) причинно-следственных связей объемов продаж, доходов и прибыли в зависимости от затрат на рекламу.
Цели лабораторной работы
Научиться планировать оптимальные объемы затрат на рекламу для увеличения объема продаж и получения наибольшей прибыли.
Освоить методику и технологию оптимизации планов в табличном процессоре Excel с помощью программы Поиск решения (Solver).
Создать лабораторную модель.
Уточнение, ограничение проблемы, описательная модель. Для начала упростим задачу годового планирования и будем определять расходы на рекламу для получения наибольшей прибыли в одном 1-ом квартале года. Предполагается, что увеличение рекламы вызывает увеличение продаж без временного лага (запаздывания, последействия, отклоняющего аргумента), т. е. в этом же квартале.
2. Лабораторная модель
Табличная модель
Обычно план-прогноз движения средств разрабатывается в виде таблицы. Вначале структура документа составляется вчерне на бумаге или сразу в виде электронной таблицы. Вариант разработки плана приведен в Excel табл. 4.1.
Искомые показатели таблицы окружены сплошной жирной рамкой. Это затраты на рекламу и производственная прибыль. Мы должны при разработке плана изменением объема затрат на рекламу добиться наилучшей прибыли. Исходные данные окружены пунктиром. Это коэффициент сезонного изменения объемов продаж, затраты на торговый персонал, цена и себестоимость изделия.

Таблица 4.1
1 квартал
Сезонность 0,9
Число продаж, шт. 4465
Выручка от реализации 178605
Себестоимость 111628
Валовая прибыль 66977
Торговый персонал 8000
Реклама 17093
Косвенные затраты 26791
Суммарные затраты 51884
Произв. прибыль 15093
Норма прибыли 8%
Цена изделия 40
Себестоимость изделия 25



Формулы табличной модели
После составления плановой таблицы необходимо связать причинно-следственные показатели формулами для вычислений. Представление формул и чисел исходных данных дано в табл. 4.2.

Таблица 4.2
А в
2 1 квартал
3 Сезонность 0,9
Число продаж, шт. =35*B3*(B11+3000)^0r5
Выручка от реализации =В5*$В$18
Себестоимость =В5*$В$19
8 Валовая прибыль =В6-В7 10 Торговый персонал 8000
11 Реклама 17093,0626384427
Косвенные затраты =0,15*В6
Суммарные затраты =СУММ(В10:В12)
15 Произв. прибыль =В8-В13
16 Норма прибыли =В15/В6
18 Цена изделия 40
19 Себестоимость изделия 25


Если в ячейки введены числа, то это исходные (внешние, экзогенные) данные. Если в ячейке формула, то это вычисляемый (эндогенный) показатель. Свод формул с пояснениями дан в табл. 4.3.

Таблица 4.3
Строка Содержимое Пояснение
3 Фиксированное значение Сезонная поправка объема продаж
5 =35*ВЗ*(В11+3000)А0.5 Ожидаемое число продаж: в строке 3 - сезонная
поправка; в строке 11 отражены затраты на рекламу
=В5*$В$18 Выручка от реализации: произведение числа продаж (5 строка) на цену изделия (ячейка В18)
=В5*$В$19 Себестоимость: произведение числа продаж (5 строка) и за- трат на изделие (ячейка В19)
=В6-В7 . Валовая прибыль: разность выручки от реализации (строка 6) и себестоимости (строка 7)
Фиксированное значение Расходы на торговый персонал
Фиксированное значение Средства на рекламу
=0.15*В6 Косвенные затраты в фонд корпорации: 15 % выручки от реа- лизации (строка 6)
=SUM (B10:B12) Суммарные расходы: затраты на персонал (10 строка),
рекламу (11 строка) и косвенные затраты (12 строка)
=В8-В13 Производственная прибыль: валовая прибыль (8 строка) за вычетом суммарных затрат (13 строка)
=В15/В6 Норма прибыли: отношение прибыли (15 строка) и выручки от реализации (6 строка)
Фиксированное значение Цена изделия
Фиксированное значение Затраты на изделие

Управление экспериментами
Осуществляется вручную или с помощью Таблицы подстановок и программы оптимизации Поиск решения. Управляющими данными являются суммы затрат на рекламу в ячейке В11. Управление осуществляется с целью максимизации прибыли в ячейке В15.
Задание: для расширения экспериментов можно и нужно менять число периодов планирования, динамику прогноза фиксированных расходов, цену и себестоимость, вводить ограничения для показателей и др.
3. Практическая работа
Чтобы Вы ощутили трудность и вариантность планирования и мощь экономико-математических методов и компьютерных программ, Вам вначале предлагается выполнить оптимальное планирование вручную. Затем выполняется компьютерная оптимизация и смена условий плановых экспериментов.

Задание 1. Предварительный анализ элементов модели
Прогнозиста и плановика всегда преследует препротивное ощущение неуверенности. Будущее однозначно непредсказуемо. Но они его предсказывают, готовят варианты решений. Менеджеры принимают решения. Вы спроектировали плановую таблицу, но вы не уверены, достаточно ли факторов включено в модель, правильно ли отражены причинно-следственные связи в модельных формулах элементов. Вы не математик. Вы информатик-экономист. Даже при наличии готовой формулы вы не чувствуете поведение показателей. Будет ли показатель иметь экстремум? Встретим ли мы многоэкстремальность? Насколько сильно влияние факторов? Поэтому плановик, чтобы почувствовать модель, проверяет поведение ее отдельных элементов и показателей.
Зависимость продаж от затрат на рекламу
В нашем примере аналитики, статистики и экономометристы задали плановикам формулу зависимости объема продаж от затрат на рекламу в строке Число продаж как
=35*ВЗ*(В11+3000)^0,5.
Чтобы понять и почувствовать силу влияния факторов, лучше воспользоваться графиком.
Задание: построить график зависимости числа продаж от затрат на рекламу.
Задание: составить таблицу влияния фактора методом Excel Таблицы подстановки для одной функции с одним аргументом. Решение дано в нижней таблице на рис. 8.1.
Планируя эксперимент, установим начальное значение затрат на рекламу 6000 руб. и будем задавать следующие числа арифметической прогрессией с шагом 4000 руб. Введем в ячейку 123 число 6000, а в J23 - 10000. Отселектируем обе ячейки. Протянем прямоугольную рамку курсора за правый нижний угол с крестиком вдоль строки. Мы получили ряд чисел арифметической прогрессии. Это план однофакторного имитационного эксперимента.
В ячейку Н24 введем формулу нашей функции. Она отображается в строке формул листа Excel вверху рисунка. Отселектируем диапазон с числами и формулой. Исполнить команды меню Данные > Таблица подстановки. Появится диалоговое окно Таблица подстановки (рис. 8.2).


Введем в поле Подставлять значения по столбцам в ячейку В11, т. е. мы будем менять затраты на рекламу в нашей главной таблице. Щелкнем кнопку ОК.
Числами заполняется строка Число продаж. Известными Вам из курса информатики способами построим над таблицей график. Наблюдаем и анализируем зависимость продаж от затрат на рекламу. Продажи растут. Экстремума нет. Значит, оптимальное решение в этой системе "реклама - продажи" невозможно. Крутизна зависимости с ростом затрат убывает, т.е. наблюдается убывающая эффективность фактора. Для количественной оценки убывающей эффективности фактора введем в строку 25 формулы оценки прироста продаж на
·каждом шаге эксперимента вычитанием из строки 24 предыдущего значения из последующего. На интервале исследования прирост продаж убывает от 600 до 300.
Зависимость прибыли от затрат на рекламу
Бесконечное увеличение затрат на рекламу будет бесконечно увеличивать объем продаж. Но изменение прибыли, вероятно, будет иметь максимум, поскольку увеличение затрат на рекламу в конце концов съест всю прибыль. Исследуем эту зависимость.
Методом построения прогрессии заполним строку затрат на рекламу в табл. 4.4. Каждое число копируем в ячейку рекламы табл. 4.1 и после пересчета таблицы копируем число производственной прибыли в соответствующую ячейку строки прибыли табл. 4.4. По данным этой таблицы построим график (рис. 8.3).
Таблица 4.4

Зависимость прибыли от затрат на рекламу имеет ярко выраженный максимум. Менеджер получит оптимальный план, если он определит затраты на рекламу в 18 000 руб. При этом будет получена прибыль 15 000 руб.
Вы почувствовали влияние факторов на отклики (критериальные показатели) и теперь уверенно можете использовать быстрые компьютерные алгоритмы программной оптимизации планов.

Задание 2. Программный поиск оптимального плана на один период
Для поиска оптимального плана использовать программу Excel Поиск решения.
В меню Сервис выполните команду Поиск решения. Появится диалоговое окно (рис. 8.4).



Задайте В15 (прибыль за 1-й квартал) в поле Установить целевую ячейку. Выберите поиск наибольшего значения и укажите в качестве изменяемой ячейки В11 (расходы на рекламу в 1-м квартале). Запустите процесс поиска решения.
В процессе решения задачи в строке состояния будут отображаться сообщения. Через некоторое время появится сообщение о том, что решение найдено. В соответствии с найденным решением (табл. 4.1), затраты 17 093 руб. на рекламу в 1-м квартале, можно получить наибольшую прибыль, которая составит 15 093 руб. Это решение точнее полученного ранее графического решения.
Задание 3. Разработка многоэтапного оптимального плана без ограничений
После того как Вы освоили одноэтапную, статическую оптимизацию плана, можно перейти к многоэтапной, динамической оптимизации и определить бюджет на рекламу в каждом квартале, соответствующий наибольшей сумме годовой прибыли. Форма поквартального годового плана представлена в табл. 4.5.
Таблица 4.5.










Формулы таблицы поквартального годового плана представлены в таблице 4.6.
Таблица 4.6.











Задание: в таблице 4.6 формулы по кварталам идентичны, поэтому для компактности таблицы мы не отображаем 3-й и 4-й кварталы. Сделайте это самостоятельно в своей модели.
Ручной поиск оптимального плана
Будущий информатик-экономист должен почувствовать, как не просто составлять оптимальный план, поэтому Вам предлагается составить план вручную, т. е. не используя программ поиска оптимального решения. Надо найти наилучшее распределение затрат на рекламу по кварталам. Вы подбираете числа плана квартальных затрат на рекламу в строке 11, добиваясь наибольшей суммы годовой прибыли в ячейке F15. Поскольку задаваемая в 3 строке сезонная поправка входит в расчет числа продаж (строка 5) в качестве сомножителя, целесообразно увеличить затраты на рекламу в 4-м квартале, когда прибыль от продаж наибольшая, и уменьшить соответственно в 3-м квартале.
Программный поиск оптимального плана
После того как Вы попытались составить оптимальный план вручную и убедились, что это почти невозможно, предлагается составить оптимальный план с помощью программы оптимизации Поиск решения.
Настройка модели в окне Поиск решения
В меню Сервис выполните команду Поиск решения. Появится диалоговое окно для настройки модели оптимизатора (рис. 8.5).



Задайте F15 (общая прибыль за год) в поле Установить целевую ячейку Выберите поиск максимального значения. Задайте в качестве изменяемых ячеек В11 :Е11 (расходы на рекламу в каждом квартале). Поле Ограничения пусто.
Задача является нелинейной задачей оптимизации средней степени сложности. Нелинейность уравнения связана с операцией возведения в степень в формуле строки числа продаж. Поэтому, нажав кнопку Параметры, задаем в окне настройки алгоритмов нелинейную задачу и выбираем методы Ньютона или градиентный.
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное решение готово. Получили урок полезности математических методов оптимизации.
В табл. 4.7 дано оптимальное решение, найденное программой. Получен план с максимальной годовой прибылью от поквартальных годовых инвестиций в рекламу. Годовая прибыль увеличена до 79 706 руб. при годовых затратах на рекламу 89 706 руб.

Таблица 4.7.















Задание 4. Оптимальный план с ограничением бюджета рекламы
Наиболее близкие к жизни модели учитывают также ограничения, накладываемые на те или иные величины. Эти ограничения могут относиться к ячейкам результата, ячейкам изменяемых данных или другим величинам, используемым в формулах для этих ячеек. Итак, бюджет покрывает расходы на рекламу и обеспечивает получение прибыли, однако наблюдается тенденция к уменьшению эффективности вложений. Поскольку нет гарантии, что данная модель зависимости прибыли от затрат на рекламу будет работать в следующем году (учитывая существенное увеличение затрат), целесообразно ввести ограничение расходов, связаных с рекламой. Предположим, что расходы на рекламу за 4 квартала не должны превышать 40 000 руб. Добавим в рассмотренную задачу соответствующее ограничение.
Настройка модели в окне Поиск решения
В меню Сервис выполните команду Поиск решения и в диалоговом окне (рис. 8.6) нажмите кнопку Добавить. Введите в поле Ссылка на ячейку ссылку F11 (общие расходы на рекламу) листа Excel. Содержимое этой ячейки не должно превышать 40 000 руб. Выберите устанавливаемое по умолчанию отношение <= (меньше или равно). В поле Ограничение, расположенном справа, введите число 40 000. Нажмите кнопку ОК и затем Выполнить.

В табл. 4.8 дано оптимальное решение, найденное программой. Годовой бюджет на рекламу в 40 000 руб. не превышен, ограничение соблюдено. В соответствии с найденным решением на рекламу будет выделено 5117 руб. в 3-м квартале и 15 263 руб. - в 4-м квартале. Прибыль увеличится с 69 662 руб. (при равномерных вложениях по 10 000 руб в квартал, табл. 5) до 71 447 руб. при одинаковом годовом бюджете на рекламу
Таблица 4.8
Изменение ограничений
Задание: поиск решения позволяет экспериментировать с различными параметрами задачи для определения наилучшего варианта решения. Например, изменив ограничения, можно оценить изменение результата. Попробуйте изменить ограничение на рекламный бюджет с 40 000 до 50 000 руб. и посмотрите, как изменится при этом общая прибыль. Поэкспериментируйте с другими значениями и параметрами. Например, подсчитайте с помощью Excel рентабельность продаж при затратах на рекламу от 40000 до 120000 с интервалом в 10000. Выводы отобразите в отчете.
Задание: для расширения экспериментов можно и нужно менять число периодов планирования, динамику прогноза фиксированных расходов, цену и себестоимость, вводить ограничения для показателей и др.
В меню Сервис выберите пункт Поиск решения. В списке Ограничения уже задано ограничение $F$11<=40 000. Нажмите кнопку Изменить. Измените в поле значение с 40 000 на 50 000. Нажмите кнопку ОК, а затем - Выполнить.
Найденное решение представлено в табл. 4.9. Прибыль равна 74 817 руб., что на 3 370 руб. больше прежнего значения 71 447 руб. при ограничении 40 000 руб. Для большинства предприятий увеличение капиталовложений на 10 000 руб., приносящее 3 370 руб. (т. е. 33,7 % рентабельности вложений), является оправданным. Прибыль при таком решении будет на 4 889 руб. меньше по сравнению с задачей без ограничений, однако при этом требуется и на 39 706 руб. капиталовложений меньше.
Таблица 4.9.







Анализ результатов
Проведите анализ самостоятельно.
Составление отчета производится аналогично отчетам по лабораторным работам №№1,2,3










13PAGE 15


13PAGE 14715




15

Приложенные файлы

  • doc 325939
    Размер файла: 637 kB Загрузок: 0

Добавить комментарий