Программное обеспечение маркетинговых исследований ОЦЕНКА РЕКЛАМНОГО ЭФФЕКТА НА ФОНЕ СТАБИЛЬНЫХ ПРОДАЖ Цель - практическое применение методов регрессионного и статистического анализа экономической информации с помощью инструментария программы Microsoft Excel (статистические функции; формулы массивов; функции, реализующие регрессионный анализ). При работе в Excel с большими объёмами данных стандартные способы применения формул и функций нередко приводят к тому, что требуется резервирование диапазонов ячеек для хранения результатов промежуточных вычислений. В таких случаях заполнение ячеек формулами оказывается достаточно трудоемким процессом даже при использовании наиболее эффективных методик тиражирования. Альтернативным способом является применение формул массивов, которые позволяют проводить вычисления сразу над несколькими значениями. Массив представляет собой набор значений, выбираемых из определенного интервала ячеек, например из строки, из столбца или даже нескольких строк и столбцов с данными. 1 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований Исходные данные (файл Lab. M 4. xls) включают: • информацию о продажах в сети магазинов (лист Sales) — данные по продажам в 18 магазинах за период с 1 января по 14 мая 2008 г. ; • сведения о сроках рекламной кампании и затратах на ее проведение (лист Реклама). Основные особенности собранных данных: • присутствуют пропуски (значение ячейки равно 0), означающие, что либо магазин еще не был открыт, либо в этот день в нем не было продаж; • присутствуют отрицательные значения, означающие, что сумма возвращенных в этот день товаров превысила выручку от продаж. Предлагается качественно и количественно оценить эффективность рекламных затрат. 2 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 4 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов В начале выполнения практических заданий требуется корректно учесть факт наличия новых, открывшихся в отчетном периоде магазинов. Способ 1. Автоматизированное определение даты «открытия» магазина, реализованное с помощью создания дополнительной таблицы. а) Вставьте дополнительный столбец после столбца A на листе Sales. Теперь данные о ежедневных продажах начинаются со столбца C, а столбец, соответствующий последнему дню продаж, стал EG. б) Создайте дополнительную таблицу на отдельном листе рабочей книги (рационально полностью скопировать лист Sales, чтобы не повторять его форматирование). в) Преобразуйте с помощью функции ЕСЛИ и ссылок на значения с листа Sales все ненулевые значения продаж в 1. 5 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов 6 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов г) Найдите для Магазина 01 первое значение, равное 1, с помощью функции ПОИСКПОЗ (1, Массив, 0), где вместо Массив следует подставить диапазон ячеек, содержащих выручку магазина — C 2: EG 2. Естественно, что при задании первой формулы следует использовать такую адресацию ячеек, которая обеспечит эффективное тиражирование формулы. Третий аргумент, равный 0, предписывает искать точное совпадение в массиве и допускает неупорядоченность значений в нем (ни по возрастанию, ни по убыванию). 7 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов 8 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов Способ 2. Автоматизированное определение даты «открытия» магазина, реализованное с помощью формулы массивов. Альтернативный способ не требует создания дополнительной таблицы с промежуточными расчетами, может быть выполнен на исходном листе Sales путем введения в ячейку B 2 формулы массива (вводится нажатием клавиш Ctrl+ Shift+ Enter): =ИНДЕКС($C$1: $EG$1; 1; МИН(ЕСЛИ(C 2: EG 2>0; СТОЛБЕЦ(C 2: EG 2)СТОЛБЕЦ($C$1)+1; 65535))). После ввода формула автоматически заключается в фигурные скобки. Если в результате расчета вы получаете число вида 39450, то это означает, что необходимо сменить формат этих ячеек на «дату» (проверьте, работает ли в вашей версии комбинация горячих клавиш Ctrl+ Shift+ @). Как вы можете убедиться, приведенный порядковый номер даты соответствует 3 января 2008 г. , а вообще отсчет дат в Excel по умолчанию начинается с 1 января 1900 г. 9 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов 10 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов Способ 2. Автоматизированное определение даты «открытия» магазина, реализованное с помощью формулы массивов. Альтернативный способ не требует создания дополнительной таблицы с промежуточными расчетами, может быть выполнен на исходном листе Sales путем введения в ячейку B 2 формулы массива (вводится нажатием клавиш Ctrl+ Shift+ Enter): =ИНДЕКС($C$1: $EG$1; 1; МИН(ЕСЛИ(C 2: EG 2>0; СТОЛБЕЦ(C 2: EG 2)СТОЛБЕЦ($C$1)+1; 65535))). После ввода формула автоматически заключается в фигурные скобки. Если в результате расчета вы получаете число вида 39450, то это означает, что необходимо сменить формат этих ячеек на «дату» (проверьте, работает ли в вашей версии комбинация горячих клавиш Ctrl+ Shift+ @). Как вы можете убедиться, приведенный порядковый номер даты соответствует 3 января 2008 г. , а вообще отсчет дат в Excel по умолчанию начинается с 1 января 1900 г. 11 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов Как работает данная формула? В процессе ее вычисления последовательно перебираются все значения из исходного массива C 2: EG 2, создается буферный массив. Всем ячейкам из C 2: EG 2 с положительными значениями ставится в соответствие относительный номер столбца этой ячейки (СТОЛБЕЦ(C 2: EG 2)-СТОЛБЕЦ($C$1)+1), а всем отрицательным и нулевым приписывается число 65535, как заведомо превышающее все осмысленные даты. Затем из буферного массива с помощью функции МИН выбирается минимальное число. Оно будет равно относительному номеру столбца для первой ячейки, в которой зафиксирована любая положительная выручка. Для выбора даты используется функция ИНДЕКС, которая позволяет найти желаемый элемент массива, задаваемого первым аргументом. Второй аргумент предписывает возвращать значение из первой строки массива, а третий аргумент задает требуемый столбец. 12 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 1. Учет наличия новых, открывшихся в отчетном периоде магазинов Указанная формула работает корректно и если ячейки в заголовках столбцов содержат не числовые даты, а текстовые строки. Так как в нашем примере дни сохранены как даты, а не как текст, то можно использовать и более простую формулу массива {=МИН(ЕСЛИ(C 2: EG 2>0; $C$1: $EG$1; 65535))}. Разберитесь и будьте готовы объяснить, как работает данная формула. Итак, формулы массивов представляют собой эффективное средство, позволяющее избежать создания дополнительных таблиц для хранения результатов промежуточных вычислений. Однако следует учитывать, что формулы массивов более сложны для восприятия и понимания, чем обычные формулы. Кроме того, они сопряжены с более высокой вычислительной сложностью, что может быть существенно при значительных объёмах обрабатываемых данных. 13 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация В рассматриваемой задаче дата открытия магазинов нужна не сама по себе, а лишь для разделения всех 18 магазинов на две группы, которые ниже будем условно именовать «старые» и «новые» . Дальнейшие вычисления будем проводить на листе Sales. а) Реализуем альтернативный автоматизированный способ разбиения перечня магазинов на две группы. Для этого в столбце B (озаглавим его Старые) после названий магазинов введите формулу, которая рассчитывает сумму продаж в магазине за первые две недели отчетного периода и, если эта сумма положительна, выдает 1, и возвращает 0 в противном случае. Таким образом все «старые» , уже работавшие в январе магазины получат метку 1. 14 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация Чем можно мотивировать такое разделение магазинов на группы применительно к поставленной цели оценить эффективность рекламных расходов? Предположим для простоты, что в фирме работает один магазин, а второй открывается 1 апреля. За неделю до его открытия 24 марта начинается рекламная кампания в СМИ продолжительностью 1 месяц. Если сопоставить динамику изменения выручки за март с рекламными затратами, то формально можно увидеть, что рекламные затраты с недельным лагом-задержкой приводят к увеличению выручки. Однако самой логичной причиной этого увеличения будет просто сам факт открытия второго магазина, который будучи, например, размещен в торговом комплексе привлекает новых клиентов и без всякого влияния рекламы в СМИ. Если в нашем распоряжении нет результатов опросов клиентов (повлияли ли на их решение о покупке рекламные предложения), то гораздо более корректным будет оценить влияние рекламы на продажи лишь в первом, «старом» магазине фирмы. 15 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация б) Рассчитайте в 20 -й строке (Всего) суммы продаж по всем магазинам за каждый день отчетного периода. При тиражировании формул можно воспользоваться клавиатурными комбинациями вместо выделения мышью. Например, в данном случае после ввода первой формулы в ячейку C 20 можно нажать F 8, перейдя в режим выделения, нажать клавишу End (проверьте, что выделятся все ячейки в 20 -й строке, над которыми есть данные) и применить комбинацию клавиш Ctrl + R для заполнения выделенных ячеек формулой из ячейки C 20. в) Присвойте сегменту ячеек C 20: EG 20 собственное имя (например, Выручка. Всех). Обратите внимание на то, что при создании имени в качестве «области» необходимо указать именно тот лист рабочей книги, на котором проводятся вычисления. В противном случае, при наличии аналогичного имени на каком-либо листе рабочей книги, вы получите сообщение вида «Введенное имя уже существует. Задайте уникальное имя» . 16 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация Определение имен позволяет сделать дальнейшие вычисления более наглядными и понятными. Например, для вычисления суммарной выручки за период уже не потребуется вводить диапазон ячеек в функцию СУММ, а достаточно вызова СУММ(Выручка. Всех). г) В 21 -й строке определите сумму продаж только по старым магазинам. Для этого пригодится вызов следующей функции =СУММЕСЛИ($B$2: $B$19; 1; C 2: C 19). Первый аргумент — массив значений меток, должен быть неизменен при тиражировании формулы. Третий аргумент — массив продаж в магазинах за конкретный день, при тиражировании формулы вправо ссылки будут изменяться. Второй аргумент, равный 1, предписывает суммировать в массиве продаж лишь те ячейки, находящиеся в тех строках, в которых в массиве меток стоит 1. Присвойте сегменту ячеек C 21: EG 21 собственное имя (например, Выручка. Старых). 17 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация 18 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация д) Подведите итоги в B 20 и B 21, рассчитав суммарную выручку магазинов и выручку по группе «Старые» . Обратите внимание на возможный негативный эффект от использования имен ячеек. Так при тиражировании вниз формулы из B 20, окажется, что и в B 21 будет скопирована СУММ(Выручка. Всех). Таким образом при тиражировании имена аналогичны абсолютной адресации ячеек. Еще одной, скорее неудобной, особенностью имен является тот факт, что при копировании листа все определенные на нем имена дублируются, что может привести к недоразумениям, так как неясно, какое из имен будет использоваться в дальнейших вычислениях. Итак, при использовании имен вместо явных ссылок на ячейки есть как положительные, так и негативные стороны: • удачные имена особенно востребованы для параметров; • в некоторых случаях удается получить более компактные формулы за счет исключения ссылок на массивы ячеек; • обилие имен затрудняет понимание формул; • необходимо учитывать особенности тиражирования таких формул. 19 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация е) Постройте точечную диаграмму, отображающую динамику суммарных продаж в старых магазинах. Можно убедиться, что на графике имеются пики продаж 22 февраля и 26 апреля, также выпадает день 1 января, продажи за который по понятным причинам были крайне низки. 20 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация ж) Наложите линейный тренд на график продаж, выведя также уравнение тренда и значение коэффициента детерминированности R 2. з) Прокомментируйте величину R 2 и характер тренда. Из ранее прослушанных курсов вы уже знакомы с правилами определения значимости коэффициентов линейных трендов. Так как на диаграмму не выводится необходимая для такой оценки информация, то потребуется рассчитать эти значения самостоятельно. Уже апробированный способ заключается в использовании возможностей надстройки Анализ Данных. В настоящей работе применим другой способ и воспользуемся встроенными статистическими функциями Excel. е) Введите в A 23 заголовок «Параметры тренда» , а под ним заголовки показателей «Коэффициент наклона» , «Y-пересечение» , «Среднее значение Y» , «Стандартная ошибка Y» , «Количество дней» . Также предварительно присвойте диапазону дат (C 1: EG 1) имя Даты. 21 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация Для расчета перечисленных показателей в С 24: С 28 используйте функции НАКЛОН, ОТРЕЗОК, СРЗНАЧ, СТОШYX, СЧЁТ, а в качестве их аргументов применяйте определенные имена ячеек (Выручка_Старых; Даты). 22 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация С помощью комбинации клавиш Ctrl+ Shift+ ! приведите результаты расчета к стандартному формату с двумя знаками после запятой и пробелом-разделителем тысяч. ж) Присвойте ячейке C 24 имя Наклон. Y, а С 25 — Смещение. Y. Проанализируйте полученные значения. Уже сравнение стандартной ошибки предсказания выручки со средним дневным значением позволяет усомниться в том, что наблюдаемый слабо понижательный тренд достоверен. Как оказывается, в некоторых сборках Excel 2007 уравнение тренда на диаграмме отображается некорректно (а именно, 55524 вместо истинного 555 246). Для исправления необходимо в формате подписи линии тренда явно указать числовой формат. Диаграммы в Excel 2007 имеют и некоторые другие особенности, затрудняющие работу с ними. 23 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация Для более тщательного анализа тренда применим функцию ЛИНЕЙН. Она позволяет получить не только значения коэффициентов тренда, но и регрессионную статистику. В разбираемом примере имеется одна независимая переменная, два коэффициента, а результат, возвращаемый ЛИНЕЙН, займет 2 х 5 ячеек. з) Выделите ячейки F 24: G 28 и введите формулу массива {=ЛИНЕЙН(Выручка. Старых; Даты; ; 1)}. Четвертый аргумент, равный 1, предписывает выводить регрессионную статистику, а пропущенный третий может при необходимости использоваться для приравнивания Y-пересечения к 0. Наиболее интересно для нас значение стандартной ошибки коэффициента наклона, которое расположено в ячейке F 25. Вспомним, что T-статистика для коэффициента равна отношению модуля его значения к стандартной ошибке. 24 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 2. Получение итоговых значений и визуализация Для определения табличного значения T-критического воспользуемся уже применявшейся ранее функцией =СТЬЮДРАСПОБР(0, 05; G 27), которую введем в ячейку I 27. В ячейке G 27 функцией ЛИНЕЙН было возвращено количество степеней свободы, равное количеству дней за вычетом 2 (1 независимая переменная и еще 1 за счет свободного члена уравнения). Применив известный алгоритм, сделайте вывод о значимости или незначимости коэффициента наклона. Если коэффициент окажется незначим, можно считать, что продажи в старых магазинах в среднем остаются неизменными. Разберитесь с помощью справки Excel, что означают остальные значения, возвращенные функцией ЛИНЕЙН. 25 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж Из построенного графика можно заметить, что существует неоднородность продаж по дням недели. Оценим, насколько значим данный эффект. а) Для корректного расчета пропорций продаж полезно исключить из рассмотрения перечисленные выше аномальные значения – выбросы (1 января и др. ). Автоматизируем процедуру отсечения выбросов. Для этого в ячейке C 29 введите значение 0. 98, служащее для определения 98% персентиля. Таким образом, мы планируем исключать 2% самых высоких дневных продаж и 2% самых низких. Присвойте ячейке C 29 имя, описывающее ее содержимое (например, Порог. Отсечения). В ячейках G 29 и I 29 можно разместить значения 98% и 2% персентилей, рассчитываемых функцией =ПЕРСЕНТИЛЬ(Выручка. Старых; Порог. Отсечения) и =ПЕРСЕНТИЛЬ(Выручка. Старых; 1–Порог. Отсечения). 26 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж Присвойте и этим ячейкам собственные имена, например, Верхний и Нижний. С помощью формулы =СЧЁТЕСЛИ(Выручка. Старых; ">"&Верхний) в ячейке K 29 можно установить, что будет отсекаться 3 наибольших (и аналогично 3 наименьших) элемента. Конструкция ">"& позволяет создать строку для проверки, превышает ли дневная выручка верхний порог. Символ & называется символом конкатенации и позволяет объединять в формулах текстовые фрагменты. Его использовать удобнее, чем аналогичную по сути функцию СЦЕПИТЬ. б) В 30 -й строке выведем дни недели, используя уже известную функцию ДЕНЬНЕД, не забыв задать корректный второй аргумент. Модифицируем выражение с помощью функции ЕСЛИ, выводя номер дня лишь для тех дней, когда продажи не превышали верхнего порога и не были ниже порога нижнего. Если же значение выручки выходит за допустимый интервал, то выводится пустая строка. 27 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж Для 1 января 2008 года такая формула может иметь вид =ЕСЛИ(И(C 21>=Нижний; C 21<=Верхний); ДЕНЬНЕД(C 1; 2); "") либо с использованием указанных выше имен =ЕСЛИ(И(Выручка. Старых>=Нижний; Выручка. Старых<=Верхний); ДЕ НЬНЕД(Даты; 2); "") По нашему мнению, первый вариант более нагляден и удобен. При тиражировании второго варианта вправо вычисление формулы происходит таким образом, что неявно каждый раз выбирается тот элемент массивов Даты и Выручка. Старых, который находится в том же столбце, что и рассчитываемая ячейка. После тиражирования формулы на все дни получим массив значений, в котором будут стоять номера дней недели, за исключением дней, соответствующих аномальным значениям выручки. 28 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж 29 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж в) В 31 -й строке продублируем выручку старых магазинов без выбросов. Чтобы не повторять проверку соотношений выручки и пороговых значений, можно применить следующую формулу =ЕСЛИ(ЕЧИСЛО(C 30); C 21; ""), где функция ЕЧИСЛО возвращает ИСТИНА, если в ячейке C 30 находится число. г) Присвоим ячейкам C 30: EG 30 имя Дни. Недели. Без. Выбросов, а ячейкам C 31: EG 31 — Выручка. Без. Выбросов. д) Определим в ячейках C 33: J 41 таблицу следующего вида. 30 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж 31 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж е) Начнем с расчета суммарной выручки за определенные дни недели и количества таких дней, для чего полезны функции СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ. Отметим, что можно было применить и новые, появившиеся в Excel 2007 функции СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН, допускающие проверку нескольких условий (в частности, для этой версии было бы необязательно вычислять отдельный массив Выручка. Без. Выбросов). При расчете среднего за период (в строке Всего) учтите, что оно, вообще говоря, не равно среднему значению от средних по дням недели. Корректирующие коэффициенты рассчитайте как отношение среднего за период к среднему по текущему дню. Ограничьте разрядность коэффициента 2 знаками после запятой. Для расчета стандартного отклонения потребуется очередная формула массива { =СТАНДОТКЛОН(ЕСЛИ($C$30: $EG$30=C 34; $C$31: $EG$31; ""))} 32 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж Разберитесь, как работает данная формула. В ней приходится использовать абсолютные адреса массивов, так как иначе (при использовании имен) выбирается только один элемент массива, находящийся в том же столбце, что и расчетная ячейка. 33 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж Коэффициент вариации рассчитайте, как отношение стандартного отклонения к среднему значению (конечно, корректней использовать в знаменателе модуль среднего, но в описываемом примере все элементы массивов неотрицательны). Ошибку среднего рассчитайте по уже встречавшейся формуле для полуширины доверительного интервала ( ) СТЬЮДРАСПОБР(0. 05; n – 1), где s — стандартное отклонение, n — количество дней, 0. 05 — задает 95% уровень значимости. Вообще говоря, для ее применения требуется показать, что выборка может быть описана нормальным распределением, однако такая проверка трудозатратна и потому не включена в настоящую тему. ж) Присвойте таблице имя Таблица. Дней. Недели. 34 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж 35 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж з) Оцените, насколько сильно различаются продажи по дням недели, и следует ли учитывать эту неоднородность. Можно, например, оценить, значимо ли различается самый неудачный в плане продаж день (понедельник) от других дней. Для этого в свободных ячейках справа прибавьте к среднему значению выручки в понедельник полуширину его доверительного интервала, а из средних всех других дней вычтите соответствующие им полуширины. Если верхняя граница доверительного интервала для понедельника превышает нижнюю границу для какого-либо дня, то это означает, что эти значения (на уровне значимости 95%) статистически неразличимы. Закончите эту процедуру и подведите ее итоги. Итак, следует считать оправданным подход, заключающийся в попытке с помощью корректирующих коэффициентов компенсировать эффект влияния дня недели на ежедневные продажи. 36 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж и) В 43 -й строке, начиная с C 43, для наглядности и удобства дальнейших расчетов скопируйте все даты. В 44 -й строке рассчитайте номера всех дней недели из расчетного периода. к) В 45 -й строке рассчитайте скорректированную на коэффициент дня недели выручку по старым магазинам. Это можно реализовать с помощью формулы =ВПР(C 44; Таблица. Дней. Недели; 5; ЛОЖЬ)*Выручка. Старых. Функция ВПР ищет в первом столбце Таблица. Дней. Недели совпадение со значением дня недели для текущей даты (C 44) и выбирает в найденной строке значение из 5 -го столбца таблицы, т. е. корректирующий коэффициент. Четвертый аргумент (ЛОЖЬ или 0) означает, что не требуется упорядоченности значений в первом столбце. Следует учесть, что этот вариант не является вариантом по умолчанию, но на практике используется гораздо чаще противоположного варианта, активируемого, когда 4 -ый аргумент пропущен или равен ИСТИНА или 1. л) Аналогично в 46 -й строке рассчитайте скорректированную общую выручку. 37 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж 38 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 3. Анализ неравномерности продаж Примененный вид функции ВПР оправдан, если не предполагается изменения вида таблицы Таблица. Дней. Недели. Если же такое изменение возможно, то столбец с корректирующими коэффициентами может оказаться не пятым и потому безопаснее применить одну из форм автоматизированного определения номера этого столбца, например, =ВПР(C 44; Таблица. Дней. Недели; СТОЛБЕЦ($G$33)СТОЛБЕЦ($C$33)+ 1; ЛОЖЬ)*Выручка. Старых При вставке дополнительных столбцов между C и G формула будет изменена автоматически. Вторая форма предполагает неизменное название столбца, использует функцию ИНДЕКС и будет рассмотрена в последующих работах. м) В ячейках B 45 и B 46 рассчитайте суммы по скорректированным продажам. Объясните, почему скорректированные суммы не совпадают с истинными. 39 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 4. Формализация рекламных затрат Рекламный бюджет торговой сети представлен в таблице, расположенной на листе Реклама. 40 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 4. Формализация рекламных затрат Видно, что статьи рекламного бюджета компании можно объединить в три группы: низкозатратные краткосрочные объявления в торговых центрах, низкозатратная постоянная наружная реклама (стенды), затратная краткосрочная реклама в СМИ. а) Подсчитайте, какая доля рекламного бюджета приходится на каждую группу. Отметим, что в январе-феврале никаких рекламных акций не проводилось. Необходимо трансформировать представленную информацию в массив рекламных расходов по календарным дням. б) На листе Реклама создайте 4 дополнительных столбца: Начало, Окончание, Продолжительность, За день. в) В столбцах Начало и Окончание введите сроки действия каждой рекламной акции (можно использовать, в частности, функцию КОНМЕСЯЦА для автоматизированного получения даты окончания месяца). 41 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 4. Формализация рекламных затрат г) В столбце Продолжительность реализуйте формулу для расчета, сколько дней длилась акция. д) В столбце «За день» необходимо рассчитать, какова сумма рекламных расходов, приходящихся на каждый день акции (в предположении об их равномерном распределении). Для единообразного расчета рекомендуется определить формулу, например, следующего вида =ЕСЛИ(ЕЧИСЛО(E 2); D 2*E 2; C 2)/H 2 В ней проверяется, введено ли число в столбце « 30 сек. эфиров» . Если да, то количество эфиров умножается на стоимость одного ролика, иначе берется число из столбца Всего. Получившийся результат делится на количество дней акции. В результате в столбце I получим величину расходов на каждый день акции. 42 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 4. Формализация рекламных затрат 43 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 4. Формализация рекламных затрат е) Теперь заполним 47 -ю строку на основном листе. Это можно реализовать формулой массива {=СУММ(ЕСЛИ(C 43>=Реклама!$F$2: $F$12; ЕСЛИ(C 43<=Реклама!$G $2: $G$12; Реклама!$I$2: $I$12; 0))} Альтернативный вариант связан с использованием упоминавшейся выше функции СУММЕСЛИМН(Реклама!$I$2: $I$12; Реклама!$F$2: $F$12; "<="&C 43; Реклама!$G$2: $G$12; ">="&C 43) Оба варианта проверяют, попадает ли текущая дата в интервал между началом и окончанием рекламной акции и если да, то добавляет в сумму расходов по текущему дню значение из столбца «За день» . Символ конкатенации строк & позволяет задать условия отбора «меньше или равно текущей дате» или «больше или равно текущей дате» . Выбирая между вариантами, можно рекомендовать второй, как более наглядный и простой для понимания. ж) Присвойте массиву С 47: EG 47 имя Расходы. На. Рекламу. 44 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 5. Оценка эффективности рекламных затрат а) Самый простой способ заключается в расчете коэффициента корреляции между корректированной выручкой и рекламными затратами (функция КОРРЕЛ). Его значение составит примерно 0. 34 (для всех магазинов) и 0. 06 (для старых магазинов). Получите эти значения. Положительное значение коэффициента позволяет сделать вывод о позитивном влиянии рекламных затрат на выручку. б) Однако если учесть, что в январе-феврале никаких рекламных акций не проводилось, то можно рассчитать коэффициенты корреляции на интервале март-май. Получите эти значения. Подтверждают ли они наличие связи между рекламными затратами и выручкой? Не будем пытаться искусственно подобрать такой временной интервал, на котором получится максимальный коэффициент корреляции, а проанализируем эффективность трех групп рекламных расходов. 45 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 5. Оценка эффективности рекламных затрат Среднесрочная низкозатратная наружная реклама. Для ее оценки можно ограничиться тем соображением, что изменения тренда продаж не произошло. Более того, появление, например, повышательного тренда после начала такой рекламы весьма вероятно могло быть вызвано сезонными эффектами изменения продаж. В целом, следует признать, что имеющихся данных для анализа этой категории рекламных затрат фирмы недостаточно, а для детального рассмотрения необходимо проводить опросы клиентов, выясняя их отклик на наружную рекламу. Однако низкая доля этой группы в общем рекламном бюджете позволяет считать, что ее эффективность может быть оценена и экспертным путем на базе общих представлений о механизмах воздействия рекламы на клиентов. Затратная краткосрочная реклама в СМИ. в) На базе уравнения тренда продаж и поправочных коэффициентов для дней недели построим массив ожидаемой выручки в старых магазинах (в 52 -й строке). Воспользуемся уже рассчитанными в C 24 и C 25 коэффициентом наклона и Y-пересечением. 46 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 5. Оценка эффективности рекламных затрат С использованием имен расчетная формула будет иметь вид =(Наклон. Y*Даты+Смещение. Y)/ВПР(ДЕНЬНЕД(Даты; 2); Таблица. Дне й. Недели; 5; 0) Разберитесь самостоятельно, почему в данном случае значение по тренду делится на корректирующий коэффициент. Корректирующие коэффициенты должны использоваться, в частности, из-за непродолжительности акции (5 дней), а потому нельзя игнорировать неравномерность продаж по дням недели. г) Найдите в 53 -й строке разницу между фактической и ожидаемой выручкой. Из-за особенностей расчета корректирующих коэффициентов сумма по 53 -й строке не будет равна 0. д) Теперь рассчитаем изменение выручки над ожидаемой за время проведения краткосрочной акции на радио с 23 по 27 апреля. Конечно, можно воспользоваться простым суммированием ячеек DL 53: DP 53. Но полезно сделать шаблон, позволяющий менять интервал дат, автоматически получая прирост выручки. 47 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 5. Оценка эффективности рекламных затрат Зарезервируем под границы интервала дата ячейки C 55 и D 55 и введем в них начало и окончание сроков краткосрочной рекламы в СМИ, а в E 55 введем формулу, рассчитывающую сумму ячеек из 53 -й строки, соответствующих заданному диапазону дат: =СУММЕСЛИМН($C$53: $EG$53; Даты; ">="&$C 55; Даты; "<="&$D 55) Получите значение прироста выручки. В F 55 аналогичным образом вычислите сумму рекламных затрат за период. В G 55 рассчитайте отношение прироста выручки к затратам на рекламную акцию. На основе общеэкономических знаний оцените величину эффекта, достаточна ли отдача от понесенных затрат. е) Ниже, в 56 -й строке оцените эффект от мартовских акций (объявление в торговых центрах). Сравните эффект от двух акций по группе «старых» магазинов. 48 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 5. Оценка эффективности рекламных затрат Подчеркнем, что существенно различная статистическая эффективность акций не может использоваться как единственное основание для однозначных решений о планировании дальнейшего рекламного бюджета. Необходимо проанализировать, не могли ли привести к формальному увеличению эффективности конкретной акции какие-либо неучтенные факторы (например, таким сильным фактором может быть сезонная распродажа). В данной работе мы не будем статистически строго анализировать временные лаги (задержки) между рекламными затратами и отсроченным изменением выручки. Разумно предположить, что все три группы затрат допускают немедленный отклик клиента. Для рекламы на радио такой отклик может быть отсрочен на несколько дней, но в среднем не должен превышать недели (реклама услышана по радио в будний день, поездка в магазин состоялась на выходные). 49 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 5. Оценка эффективности рекламных затрат И, действительно, воспользовавшись шаблоном для расчета прироста выручки, можно установить, что прирост выручки за 24– 30 апреля более чем на четверть превышает прирост за 23– 27 апреля. Разумно предположить, что именно так проявляется временной лаг между восприятием радиорекламы и приходом за покупками в магазин. ж) Самостоятельно рассчитайте с помощью функций НАКЛОН и ОТРЕЗОК тренд для продаж во всей сети, примените стандартные корректирующие коэффициенты для расчета ожидаемой выручки и оцените эффективность рекламных акций на данных о выручке всех магазинов. 50 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 6. Контрольные вопросы 1. Как математические методы могут применяться для оценки эффективности рекламных затрат? 2. Для каких целей удобнее использовать формулы массивов? 3. Какие способы работы в Excel могут быть применены для корректного учета факта наличия новых, открывшихся в отчетном периоде магазинов? 4. В чем состоит необходимость выделения из всех магазинов группы «старые» применительно к оценке эффективности рекламных расходов? 5. Какие преимущества и недостатки дает использование имен вместо явных ссылок на ячейки? 6. Какие встроенные функции Excel могут применяться для определения параметров линейных трендов? 7. Для каких целей применяется функция ЛИНЕЙН? Сформулируйте целевое назначение параметров, возвращенных функцией ЛИНЕЙН. 8. В каких случаях применяется процедура отсечения выбросов? 51 Лекция № 1 (05. 09. 2013)
Программное обеспечение маркетинговых исследований 6. Контрольные вопросы 9. Как называется символ «&» и каково его назначение при использовании в расчетных формулах? 10. Какие методы применялись для оценки разницы продаж по дням недели? Знаете ли вы какие-нибудь еще методы для проведения подобной оценки? 11. Опишите назначение функции ВПР. 12. В чем состоит практический смысл расчета корректирующих коэффициентов? 13. Какие виды рекламы применялись в данной задаче? Возможен ли немедленный отклик клиента на рекламную акцию? 15. Какое значение временного лага между восприятием радиорекламы и приходом за покупками в магазин удалось установить при выполнении данной работы? 16. Какова эффективность каждого вида рекламных акций, рассчитанная на данных о выручке всех магазинов? 52 Лекция № 1 (05. 09. 2013)
Скачать презентацию Программное обеспечение маркетинговых исследований ОЦЕНКА РЕКЛАМНОГО ЭФФЕКТА НА
Lab #1.ppt