Практическая работа № 16. Прогнозирование объема продаж товара

Скачать: 16.files.zip [707,39 Kb] (cкачиваний: 29)  

Практическая работа № 16.

Прогнозирование объема продаж товара

Цель работы: Научиться формировать базы данных о движении товара и прогнозировать продажи.

Основные теоретические сведения

Канал распределения товара – это путь движения товара от продавца (производителя) к покупателю, а также это организации либо отдельные лица, занимающиеся доведением товара от производителя к потребителю.

Каналы распределения делятся по числу составляющих его участников (уровней):

1) канал нулевого уровня: производитель – потребитель;

2) одноуровневый канал: производитель – различный торговец – потребитель;

3) двухуровневый канал: производитель – оптовая фирма – розничная фирма – потребитель.

Наличие у фирмы большого числа уровней снижают ее возможность контролировать деятельность участников товародвижения.

2. Понятие, сущность и классификация услуг

Маркетинг в сфере услуг – это аренда товаров, ремонт товаров, принадлежащих потребителям, а также и личные услуги.

Бывает, что товары и услуги смешиваются. Например, когда мы оплачиваем номер в гостинице за проживание, мы же не берем ничего с собой, кроме как воспоминаний. Или приобретая книгу, мы в первую очередь приобретаем умственные способности, а не бумагу с чернилами.

Классификация услуг:

1) по осязаемости: осязаемые (предполагает наличие реального объекта – аренда оборудования) и неосязаемые (например, уровень обслуживания);

2) по уровню квалификации специалистов: потребители используют большую избирательность при выборе услуги, требующей высокой квалификации;

3) некоммерческие услуги: услуги, носящие общественный характер и, как правило, не приносящие прибыль;

4) по степени регулирования: со стороны законодательства, моральных принципов, религии, жизненного уклада;

5) по трудоемкости: автоматизированные и ручные услуги. Трудоемкость возрастает при привлечении высококвалифицированного персонала;

6) по степени контакта с потребителями: тесный либо незначительный.

3. Неосязаемость, неотделимость и непостоянство качества услуг

Характеристики услуг, отличающие их от товаров:

1) неосязаемость – невозможность демонстрации, транспортировки, хранения, упаковки. Например, услуги по ремонту и обслуживанию. Возможно лишь описать, какие преимущества появится у товара в результате;

2) неотделимость, т. е. качество предоставления услуги непосредственно зависит от уровня квалификации специалиста;

3) непостоянство означает, что качество может быть различным, даже если услугу оказывает одна и та же фирма.

4. Роль услуг в системе маркетинга

Главная роль услуг – это стимулирование сбыта. При осуществлении сервисных услуг происходит непосредственный контакт с покупателем. Сервис выступает дополнительным средством повышения конкурентоспособности товара.

Например, для фирм, торгующих промышленными товарами, на первый план выходит техническое обслуживание. Прибыль от этого вида услуг может превышать прибыль от продажи самих товаров. Поэтому в стратегии маркетинга сложных промышленных механизмов и машин важен принцип: сначала – организация сервисного обслуживания, а затем – сама продажа товара.

5. Дифференцирование предложения товара

Дифференцирование предложения товара может осуществляться с помощью следующих типов поддержки:

1) товар;

2) услуги, сопутствующие товару: сроки поставки, установка, монтаж, ремонт, гарантия;

3) персонал: повышение квалификации, обучение;

4) имидж: создание уникального обращения, связано с товаром; придание т

Задания к практической части

Ввод данных

Данные о продажах записываются в формате Excel на листе с именем «Продажа товара (подробно)», поступления ТМЦ регистрируются на листе «Приход товара (подробно)». Перечень ТМЦ и их цены записаны на листе Товары. Структура таблиц показана на рис. 1, 2, 3.

Рис. 2. Структура базы «Продажа товара (подробно)»

Рекомендации по заполнению базы данных.

Исходные данные целиком и полностью определяют результаты последующего анализа. Потому водить данные следует осмысленно, заранее продумав структуру товарооборота, темпы реализации продукции, используемые цены, динамику поступления и т.п. Если этого не сделать, то все проблемы возникнут на окончательных этапах работы и устранять их будет сложнее.

Дать единый рецепт на все случаи невозможно. Предлагаем придерживаться схемы заполнения базы данных, которая использовалась при подготовке демонстрационного примера.

Вначале следует сформировать небольшую базу данных, некоторый «микроцикл» реализации товаров. База данных будет состоять из прихода (или нескольких приходов) ТМЦ и их реализации по оптовым, мелкооптовым и розничным ценам.

Для этого вначале выбираем дату прихода ТМЦ, например 19/06/2013. Вводим одну (или несколько) приходных накладных, включив в нее всюноменклатуру. Это будет поступление товаров.

Для каждой позиции товара определяем среднийсрок его реализации T_i (например, в днях), исходя из этих сроков выбраем период ;

В нашем примере средний срок реализации составил 3 дня. В пределах этого срока (с 19/06/13 по 21/06/13) имитируем продажу всего закупленного объема товаров. Для этого вводим документы «Расходная накладная», используя оптовыеи мелкооптовые цены, а также различные количества реализуемых ТМЦ

В пределах одной даты целесообразно построить 5-7 документов на реализацию. Расходные накладные нужно равномерно распределить в пределах выбранного периода Т (таким образом, всего будет построено 15-20 документов).

Затем дополняем базу данных документами на продажу по розничным ценам. Накладных на реализацию по розничным ценам должно быть больше, чем по оптовым и мелкооптовым.

Важно проследить, чтобы часть документов на реализацию по розничным ценам состояла только из одного вида ТМЦ.

При заполнении базы следует контролировать величину остатков: в конце периода Т остатки должны быть близки 0.

Закончив формирование базы данных на период Т, определяем основные показатели товарооборота, чтобы убедиться в отсутствии явных ошибок. Рекомендуется посчитать внутреннюю ставку дохода и NPV (см. ниже), а также определить рентабельность по каждой позиции ТМЦ.

Будем считать, что структура реализации примерно одинакова. Поэтому дальнейшее заполнение базы данных можно выполнить копированием сформированного фрагмента, внеся в него незначительные отличия по объемам реализации (скажем — 5-15%).

На завершающем этапе базу данных дополняем продажами по розничным ценам. Таким образом в примере была построена база данных движения ТМЦ на 1 месяц.

Определение суммарных объемов продаж, поступления товаров и остатков

Первый этап анализа товарооборота — определение суммарных объемов поступления и реализации товаров. Основной инструмент для определения итоговых данных — сводные таблицы (меню Данные4Сводная таблица…). Исходной информацией служат листы «Приход товара (подробно)» и «Продажа товара (подробно)». Результирующие таблицы следует разместить на листах с именами «Приход товара» и «Продажа товара». Формы результирующих отчетов показаны на рис. 4,5

Таблица остатков формируется также с использованием сводных таблиц по нескольким диапазонам консолидации, а именно — по листам «Приход товара (подробно)» и «Продажа товара (подробно)». Форма таблицы остатков показана на рис. 6.

Рис 5. Структура отчета «Продажа товара»

Анализ структуры

Анализ выполняется на основании продаж (лист «Продажа товара (подробно))». Определить итоговые объемы удобно мастером сводных таблиц или итогами (меню Данные4Сводная таблица…). Вид сводной таблицы показан на рис. 7.

В Excel 2002 и выше при построении диаграммы по сводной таблице, график располагается на отдельном листе. Чтобы поместить диаграмму и сводную таблицу на один лист нужно преобразовать сводную таблицу в обычную командой:

Правка4Специальная Вставка (значения)

На основании структуры продаж строим диаграмму, как показано на рис. 8. Аналогично выполняется анализ структуры продаж в стоимостном выражении.

Рис.8Диаграмма «Структура продаж (по количеству)»

Рис. 9. Исходные данные для формирования потоков платежей

Контроль правильности исходных данных

Прежде чем приступить к обработке данных, нужно проконтролировать их правильность (лучше посмотреть, куда прыгаешь до прыжка, а не сразу после!).

Методы контроля выбираются индивидуально. Рекомендуем, как минимум, выполнить следующие действия:

qсопоставить суммарные объемы продаж таблицах Приход товара(подробно), Продажа товара(подробно), Приход товара, Продажа товара и База, основной инструмент – автофильтр и выборочное суммирование;

qпроанализировать количество цен, соответствующих каждому виду ТМЦ, основной инструмент – сводные таблицы и формула СЧЕТЕСЛИ().

qУбедиться, что в базе данных нет документов, содержащих строки с различными датами.. Иными словами, все сроки, образующие документ, должны содержать одинаковое значение даты.

Рис. 10 Отчет для контроля заполнения дат в документах

Формула в поле ПР имеет вид: =СЧЁТЕСЛИ(B3:F3;">0")

Из таблицы видно, что документ РН-21 содержит ошибку, в нем имеются строки с датами 20/06/04 и 21/06/04. В режиме автофильтра ошибку нужно локализовать и исправить.

Определение показателя внутренней ставки дохода (IRR)

На основании таблицы База при помощи инструмента сводных таблиц построить отчет, показанный на рис. 11. Эту таблицу разместить на отдельном листе с именем ДинамикаСт. Формула для определения внутренней ставки дохода (IRR) имеет вид:

Рис. 11Отчет о потоках платежей и значения IRR

Рис. 12 Отчет о значениях чистой приведенной прибыли

Формула определения NPV (ячейка G4):

=ЧПС(B$3;ДинамикаСт!$B4:$F4)

Для графического определения внутренней ставки дохода по отчету о показателе NPV нужно построить семейство диаграмм, как показано на рис. 13.

Корреляционный анализ динамики продаж

Выполнение данного этапа можно начать с формирования сводной таблицы по динамике продаж в количественномвыражении (см. рис. 14). Таблица строится по данным листа «Продажа товара (подробно)». В данной таблице определены суммы по полю количества продаж из таблицы «Продажа товара (подробно)» по каждой дате реализации. Итоги по строкам и столбцам подводить не следует, для чего можно отключить соответствующие параметры сводной таблицы.

.Рис. 14. Отчет о динамике продаж в количественном выражении

Перед применением надстройки корреляционного анализа таблицу нужно транспонировать. Это следует сделать для того, чтобы указать метки в столбцах таблицы как подписи данных. Для транспонирования таблицы нужно:

qскопировать ее в буфер обмена;

qвставить на лист командой Правка4Специальная Вставка, установив флажок Транспонировать;

Пример транспонированной таблицы приведен на рис. 15.

Рис. 15.Транспонированная таблица динамики продаж

Для оценки взаимосвязи объемов продаж между видами ТМЦво времени используем надстройку Анализданных (Сервис4Анализ данных4Корреляционный анализ).

При заполнении бланка с параметрами корреляционного анализа (рис. 16) нужно указать область исходных данных, столбец с метками (именами переменных), ячейку, начиная с которой будет размещаться результат расчетов.

По результатам корреляционного анализа подготовить таблицу, как показано на рис. 17. Средствами условного форматирования нужно выделить наибольшие и наименьшие значения коэффициентов корреляции (в диапазоне 0,95— -1, -0,95 — -1). Сделать выводы относительно полученных результатов.

Рис. 16. Параметры корреляционного анализа

Определение зависимости объемов продаж между различными видами товаров

Начинать расчет нужно с отбора документов, которые содержат более одного вида ТМЦ. Вначале строим сводную таблицу, как показано на рис. 18.

=СЧЁТЕСЛИ(B4:U4;">0") (ячейка V4)

Рис. 17. Результат корреляционного анализа

Рис. 18 — Таблица для определения количества ТМЦ в документах

При построении такой таблицы мастером сводных таблиц следует наиболее продаваемый ТМЦ разместить в области строк, остальные — в области данных и рассчитать по ним количество значений. Это покажет, сколько раз продавался тот или иной ТМЦ в паре с наиболее «ходовым». Если указать операцию суммирования, отчет покажет, в каком объеме продавался тот или иной товар паре с наиболее «ходовым».

При большом объеме результирующей таблицы необходимо выполнить группировку строк, разбив таблицу на интервальные значения. Например, можно объединить (сгруппировать) строки с объемами продаж от 1 до 10 шт., от 10 до 50 шт., от 50 до 100 шт. и более 100 шт. Доступ к операции группировки (разгруппировки) можно через панель инструментов Сводные таблицы. Включить эту панель инструментов можно при помощи команды меню:

Вид4Панелиинструментов4Сводные таблицы.

Обратиться к команде группировки (разгруппировки) можно через панель инструментов Сводные таблицы:

Сводная таблица4Группаиструктура4Группировать

Сводная таблица4Группаиструктура4Разгруппировать.

Регрессионный анализ товарооборота

Основная задача этого этапа — получение аналитической зависимости между продажами отдельных видов ТМЦ. Рекомендуем такую схему работы.

В качестве исходных данных используем таблицу динамики продаж, аналогичную представленной на рис.15. По структуре продаж выбираем ТМЦ (назовем его А), для которого будет выполниться прогноз объемов реализации. В примере это Сухарики.

По данным корреляционного анализа определяем ТМЦ, объемы реализации которых взаимосвязаны с товаром А. В примере это Иогурт Чудо, Сырок «Чудо», Сырок слад.

Рис. 20 Исходные данные для регрессионного анализа результат прогнозирования

Вызываем надстройку Анализ данных (Сервис4Анализданных4Регрессионный анализ) и заполняем бланк запроса, как показано на рис. 21. В нем указываем область таблицы со независимыми переменными, блок значений зависимой переменной и начало блока, в который будут записаны результаты анализа. Разумеется, что в конкретном примере адреса этих блоков будут отличаться от используемых в примере.

Рис. 21Бланк с параметрами регрессионного анализа

Результат регрессионного анализа показан на рис. 22. Основная информация — это показатели точности регрессии (в примере значение R-квадрат больше 0,95, что свидетельствует о хорошем приближении), а также коэффициенты регрессии. По данным коэффициентам строим прогноз, как показано на рис. 20 и отклонение прогнозируемый значений от реальных (колонки F,G).

В примере формула регрессии имеет вид: Y=1,221+0.489X₁+1,352X₂+ 0,309 X₃

Формулы Excel для прогнозируемого объема продаж ТМЦ Сухарики и отклонения прогноза от реальных величин выглядят так:

=C2*$B$38+D2*$B$39+E2*$B$40+$B$37

=B2-F2

Значения этих показателей показаны на рис. 20 (колонки F, G).

Контрольные вопросы:

1. Что называется каналом распределения товара.

2. Перечислите классификацию услуг

3. Дайте характеристику услугам, отличающим их от товара.

4. Охарактеризуйте роль услуг в системе маркетинга.

5. С помощью каких типов поддержки можно осуществлять дифференцирование предложения?