Как с помощью Excel составить прогноз спроса на продукцию

1244
Фото © shutterstock.com
Фото © shutterstock.com
Смирнов Всеволод
руководитель направления «Игрушки» группы компаний «Квадрум»
Вы сможете спрогнозировать продажи, не прибегая к сложным формулам, рассчитать коридор спроса, определив верхнюю и нижнюю границы будущих продаж, использовать универсальный метод прогноза продаж для любого периода.

Вместо громоздких формул для прогноза спроса на продукцию мы используем один график в Excel, который строим исходя из данных о продажах компании. Алгоритм вывели самостоятельно, опираясь на советы знакомых бизнесменов и материалы из интернета. С помощью графика прогнозируем продажи на месяц, несколько месяцев или год. Чтобы повторить опыт, вам потребуется версия Excel 2003–2016 года. Кроме того, в конце статьи вы найдете альтернативный способ, который позволит построить прогноз за несколько минут. Однако он подходит исключительно для версии Excel 2016 года.

Шаг 1. Чтобы прогнозировать спрос на товары, собираем данные о продажах компании

Чтобы приступить к анализу, вам понадобятся данные о продажах компании за весь период ее существования. Чем больше информации, тем точнее прогноз. У нас, к примеру, есть сведения о продажах с января 2013 года по август 2015-го. Заносим их в таблицу (рисунок 1).

Как с помощью Excel составить прогноз спроса на тпродукцию

Шаг 2. Делаем прогноз спроса на продукцию на заданный период

Чтобы спрогнозировать продажи, к примеру, на месяц или на будущий год, используем функцию «ПРЕДСКАЗ» в Excel. Функция основана на линейной регрессии и предназначена для прогнозирования продаж, потребления товара и пр.

В ячейку C34 записываем функцию:

=ПРЕДСКАЗ(x; известные_значения_у; известные_значения_х),

где

х — дата, значение для которой необходимо предсказать (ячейка A34);

известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды (B2:B33). В строке с формулой выделяем диапазон B2:B33 и нажимаем кнопку F4, чтобы он закрепился. В итоге получаем ($B$2:$B$33);

известные значения x — ссылка на ячейки таблицы с данными продаж за известные периоды ($A$2:$A$33).

Далее распространяем функцию на все интересующие нас даты и получаем прогноз на каждый месяц будущего года (рисунок 1).

Шаг 3. Делаем расчет коэффициента сезонности для прогноза спроса

Чтобы учесть сезонные спады и рост продаж, с помощью стандартных функций вычисляем коэффициент сезонности. Для этого суммы продаж за первый и второй год делим на общую сумму продаж за два года и умножаем на 12. С помощью клавиши F4 устанавливаем абсолютные ссылки, чтобы расчет шел исключительно из нужного нам диапазона (рисунок 1).

=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12

Далее копируем формулу и вставляем в ячейки F2:F13 как формулу массива. Завершаем ввод сочетанием клавиш: Ctrl+Shift+Enter. Если этого не сделать, функция вернет значение ошибки #ЗНАЧ! В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т. д. Для наглядности можно назначить ячейкам процентный формат. Правой кнопки мыши выбираем «Формат ячеек», затем вкладку «Число» и далее вкладку «Процентный, два знака после запятой».

Шаг 4. Корректируем прогноз спроса на продукцию, учитывая сезонность

Добавим рассчитанные коэффициенты в имеющуюся функцию «ПРЕДСКАЗ» (ячейки C34:C45):

=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)*ИНДЕКС($F$2:$F$13;МЕСЯЦ(A34))

Чтобы скорректировать продажи, учитывая коэффициент, используем функцию «ИНДЕКС» (рисунок 2).

Как с помощью Excel составить прогноз спроса на тпродукцию

Первым аргументом в функции указываем ссылку на 12 ячеек с коэффициентами сезонности ($F$2:$F$13), вторым — номер месяца, чтобы вернуть коэффициент для нужного месяца (для этого используем функцию «месяц», которая возвращает только номер месяца из указанной даты). Для сентября 2015 года формула индекса выглядит так:

ИНДЕКС($F$3:$F$14;МЕСЯЦ(A35))

Чтобы скорректировать прогноз, нужно значение «ИНДЕКС» умножить на значение «ПРЕДСКАЗ», которое рассчитывали в шаге 2. Вот что мы получим:

=ПРЕДСКАЗ(A34; $B$2:$B$33; $A$2:$A$33)*ИНДЕКС ({97,48%:98,99%:90,38%:94,66%:100,86%:99,02%:100,66%:110,39%:100,47%:104,82%:105,13%:97,14%}; 9)

Распространяем функцию на дальнейшие периоды и получаем скорректированный прогноз с учетом сезонности в ячейках C34:С45 (рисунок 1).

Важная новость для подписчиков!

Шаг 5. Рассчитываем отклонение и строим два сценария

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

Верхние и нижние границы коридора спроса строим по формуле (ячейка G2 на рисунке 1):

=ДОВЕРИТ(0,05 (АЛФА); СТАНДОТКЛОН(C34:C45); СЧЕТ(C34:C45)),

где

«ДОВЕРИТ» возвращает доверительный интервал, используя нормальное распределение. Функция учитывает колебания продаж компании, включая сезонные.

«АЛФА» — уровень значимости для вычисления доверительного уровня. Показатель 0,05 означает, что мы получим прогноз с точностью 95%.

«СТАНДОТКЛОН» — стандартное отклонение генеральной совокупности. Показывает, насколько прогнозируемые продажи отличаются от реальных.

«СЧЕТ» подсчитывает количество месяцев, по которым мы прогнозируем продажи.

Чтобы получить оптимистичный и пессимистичный сценарии, в ячейки D34 и D35 записываем формулы (рисунок 1).

Оптимистичный: =$C34+$G$2 (прибавляем к сумме прогноза сумму рассчитанного доверительного интервала)

Пессимистичный: =$C34–$G$2 (вычитаем из суммы прогноза сумму доверительного интервала)

Чтобы по полученным данным построить график, в ячейки C33, D33 и E33 копируем значения из ячейки B33. Далее выделяем все данные (A1:E45), переходим на вкладку «Вставка», находим вкладку «Диаграммы» и затем вкладку «График». В итоге получаем график с коридором спроса (рисунок 3).

Как с помощью Excel составить прогноз спроса на тпродукцию

Вывод. Построив коридор спроса, внимательно следим за продажами в новом году. В 99 % случаев они развиваются в рамках коридора. Если нет — анализируем продажи еще раз и строим новый график.

Мнение эксперта

Метод эффективен для прогнозирования продаж малого количества SKU

Максим Люлин,
генеральный директор «Актион-пресс»

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

К недостаткам метода отнесу сложность учета изменения цен, влияния аукционной деятельности. Кроме того, при оценке продаж в рублях вы не можете объективно оценить долю продаж компании в отраслевой нише, поэтому рискуете потерять долю рынка. Ваши конкуренты могут этим воспользоваться и предложить товар по более низкой цене.

Мнение эксперта

Метод идеален для анализа продаж по зафиксированным показателям

Кирилл Чихачев,
генеральный директор «МЦФЭР-пресс»

До прочтения статьи я был знаком с методом в теории. Теперь, попробовав его на практике, могу сказать, что он мне понравился. Метод идеален для анализа продаж по зафиксированным показателям: количество продуктов, сбытовая мощность и пр. Его также стоит применять для малого числа продуктов: рост и падение спроса на каждый из них зависит от разных причин. Прогноз предельно понятен, логичен и точен. Однако для еще большей точности я бы учел следующие моменты.

Максимальное и минимальное значения продаж проще рассчитывать исходя из двух точек в начале и конце периодов, а не искать точки, через которые должна проходить прямая.

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

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

Подпишитесь и получите свежий номер журнала "Коммерческий директор", в котором:
    Подписаться со скидкой 45%>>>


    Ваша персональная подборка

      Подписка на статьи

      Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

      Рекомендации по теме

      Школа руководителя

      Школа руководителя

      Проверьте свои знания и приобретите новые

      Записаться

      Самое выгодное предложение

      Самое выгодное предложение

      Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

      Живое общение с редакцией

      Выбор редактора

      Дайджест самых нужных статей

      Шеф-редактор журнала «Коммерческий директор» Амина Атавова

      Главный редактор журнала «Коммерческий директор»
      Амина Атавова.

      Читать сейчас




      © 2011–2016 ООО «Актион управление и финансы»

      Журнал «Коммерческий директор» –
      профессиональный журнал коммерсанта

      Зарегистрировано Федеральной службой по надзору в сфере связи, информационных технологий и массовых коммуникаций (Роскомнадзор) Свидетельство о регистрации ПИ № ФС77-62255 от 03.07.2017

      Политика обработки персональных данных

      Все права защищены. Полное или частичное копирование любых материалов сайта возможно только с письменного разрешения редакции журнала «Коммерческий директор». Нарушение авторских прав влечет за собой ответственность в соответствии с законодательством РФ.

      
      Яндекс.Метрика
      • Мы в соцсетях
      Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам.Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.
      Зарегистрируйтесь на сайте и продолжите чтение! Это бесплатно и займет всего минуту!

      Вы сможете бесплатно продолжить чтение этой статьи, а также получите доступ к сервисам на сайте для зарегистрированных пользователей:

      • методики, проверенные на практике
      • правовая база
      • полезные подборки статей
      • участие и просмотр вебинаров

      У меня есть пароль
      напомнить
      Пароль отправлен на почту
      Ввести
      Я тут впервые
      И получить доступ на сайт Займет минуту!
      Введите эл. почту или логин
      Неверный логин или пароль
      Неверный пароль
      Введите пароль
      Зарегистрируйтесь на сайте и документ Ваш! Это бесплатно и займет всего минуту!

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

      • методики, проверенные на практике
      • правовая база
      • полезные подборки статей
      • участие и просмотр вебинаров

      У меня есть пароль
      напомнить
      Пароль отправлен на почту
      Ввести
      Я тут впервые
      И получить доступ на сайт Займет минуту!
      Введите эл. почту или логин
      Неверный логин или пароль
      Неверный пароль
      Введите пароль