Предсказуемые продажи. Прогнозирование продаж в Excel с учетом сезонности

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

  • поведение потребителей;
  • предыдущие и планируемые стратегии продвижения продукта;
  • действия конкурентов-производителей;
  • внешняя среда предприятия, ее изменения.

Все существующие методы прогнозирования продаж условно можно разделить на четыре основные группы: основанные на суждении; ориентированные на потребителя; экстраполяции продаж; моделирование.

1. Методы, основанные на суждении. Эта группа включает в себя такие методы, как изучение намерений контрагентов, ролевые игры, экспертные оценки, метод Дельфи, мозговой атаки, сводный прогноз службы продаж.

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

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

Экспертные оценки. Сущность данного метода заключается в выработке коллективного мнения группы специалистов по определенному продукту. На практике выделяется несколько методов экспертной оценки. Рассмотрим один из них - метод баллов, в ходе которого на первом этапе формируется экспертная группа из специалистов в данной области, численность которой должна быть равна или больше 9 человек, состав группы должен быть однородным. На следующем этапе всеми членами экспертной группы коллективно определяются важнейшие параметры (3-5) объекта, способные повлиять на объем продаж. Затем экспертным путем устанавливается степень важности, или ранг, каждого выделенного параметра. Для прогнозирования или расчета полезного эффекта и каждого элемента затрат но каждому классу объектов одного назначения строится своя система баллов, так как на полезный эффект и элементы затрат влияют свои факторы или параметры .

Важно запомнить!

Метод экспертных оценок существенно отличается от изучения намерений контрагентов, так как если эксперту предлагают дать оценку динамики рынка, от него не требуется репрезентативность, как раз наоборот - каждый эксперт уникален. Как правило, привлекается от 5 до 20 экспертов, причем наиболее эффективный способ получения единой оценки - взвешивание отдельных результатов с равными весами. Точность прогнозов, полученных с использованием данного метода, можно поднять путем применения процедур типа Дельфи.

Метод Дельфи. Является одной из разновидностей метода экспертных оценок. Его суть заключается в итеративной процедуре получения интегрального показателя с последовательным снижением дисперсии расхождений экспертных оценок. Специфика этого метода в том, что обобщение результатов исследования осуществляется путем индивидуального письменного опроса экспертов в несколько туров по специально разработанной процедуре. Надежность метода считается высокой при прогнозировании па период как от одного до трех лет, так и на более отдаленное время. В зависимости от цели прогноза для получения экспертных оценок может привлекаться от 10 до 150 экспертов.

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

Достоинством экспертных методов является их относительная простота и применяемость для прогнозирования практически любых ситуаций, в том числе в условиях неполной информации. Особенностью этих методов является возможность прогнозировать качественные характеристики рынка (например, изменение социально- политического положения, влияние экологии на производство и потребление тех или иных товаров).

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

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

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

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

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

где t + 1 - прогнозный период; t - период, предшествующий прогнозному периоду (год, месяц и т.д.);;/, + , - прогнозируемый показатель; т,_ i - скользящая средняя за два периода до прогнозного; п - число уровней, входящих в интервал сглаживания; y t - фактическое значение исследуемого явления за предшествующий период; у,_ { - фактическое значение исследуемого явления за два периода, предшествующих прогнозному.

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

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

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

где t - период, предшествующий прогнозному; t + 1 - прогнозный период; U [+ i - прогнозируемый показатель; а - параметр сглаживания; y t - факти-

ческое значение исследуемого показателя за период, предшествующий прогнозному; U t - экспоненциально взвешенная средняя для периода, предшествующего прогнозному.

При прогнозировании данным методом возникают затруднения, связанные с выбором значения параметра сглаживания а и определения начального значения t/ 0 .

Метод экспоненциального сглаживания наиболее эффективен при разработке среднесрочных прогнозов.

Регрессионный анализ. Данный метод является обобщением модели временного ряда. Широко применяется на практике специалистами-управ- ленцами и легко рассчитывается при помощи Excel. Эта форма экстраполяции основана на регрессионном анализе, при котором временной период считается независимой переменной.

4. Методы на основе моделирования (ассоциативная категория методов прогнозирования). Они включают в себя метод опережающих индикаторов и эконометрические модели.

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

Эконометрические модели представляют собой крупномасштабные регрессионные модели на основе нескольких уравнений. В настоящее время не особенно популярны у менеджеров из-за своей дороговизны и стремления компаний снизить все свои издержки. Однако с их помощью можно анализировать последствия реализации различных стратегий, планировать динамику рынка и бизнес-среды, тем самым генерируя различные сценарии развития. При выборе данного метода следует принимать во внимание, что необходимо будет прогнозировать значения объясняющих факторов. С некоторыми из них (например, мода) могут возникнуть большие проблемы .

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

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

  • Фатхутдинов Р. Л. Стратегический маркетинг: учебник. М. : ЗАО «Бизнес-школа“Интел-Синтез”», 2000. С. 198-200.
  • Сводный прогноз службы продаж. Прогноз объема продаж составляютспециалисты отдела службы продаж. Достоинство данного метода заключается в том, что специалисты отдела продаж находятся в тесном контактес продавцами, которые отлично знают своих потребителей, спецификуих поведения, объемы закупок продукта. На основе данных оценок частоустанавливаются квоты продаж товара. Однако, как показывает практика,иногда их размер бывает несколько занижен продавцами.
  • Методы, ориентированные на потребителя. Среди них выделяютдва основных - тестирование рынка и обзоры состояния рынка. Тестирование рынка. Сущность данного подхода заключается в проведении первичных маркетинговых исследований рынка. Для сбора информации но исследуемому рынку продукта специалисты часто прибегаютк проведению фокус-групп и опросам потребителей в местах продажипродукта. Напомним, что обычно под фокус-группой понимается группареспондентов, включающая от восьми до десяти потенциальных потребителей, собранных вместе для обсуждения темы, в которой каждый из нихв той или иной степени заинтересован. Процесс обсуждения идет по заранее разработанному сценарию под руководством ведущего - модератора.Обсуждение может длиться до двух часов, хотя иногда возникает необходимость работать и дольше. Дискуссии в фокус-группах относятся к методам качественного анализа, так как полученные данные нельзя (в статистическом смысле) назвать репрезентативными для этой конкретной группынаселения. Обзоры состояния рынка. Сущность данного метода заключаетсяв исследовании рынка и опросе потенциальных потребителей продуктаотносительно степени их готовности приобрести анализируемый товар.Обычно потенциального потребителя просят оценить степень готовностикупить определенный товар по 10-балльной шкале, где 10 баллов соответствует твердому намерению респондента купить данный товар. Затем полученные результаты относительно намерения совершить покупку переносятна общую численность населения в стране. Учитывая склонность потребителей в реальной жизни переоценивать вероятность покупки продукта,менеджеры при составлении прогноза объема продаж часто используютподход «но максимуму», т.е. подсчитывают только количество максимальных оценок (10 баллов).
  • Методы экстраполяции продаж (методы временного ряда). Они основываются на имеющихся данных относительно объемов продаж за пре-
  • 2 URL: http://www.ekonomika-st.ru
  • Там же.
  • URL: http://www.ekonomika-st.ru

Требуется ли прогноз расходов на следующий год или проецирование ожидаемых результатов для ряда в экспоненциальном эксперименте, вы можете использовать Microsoft Office Excel для автоматического создания будущих значений, основанных на существующих данных, или для автоматического создания экстраполяция значений, основанная на линейных расчетах и тенденциях роста.

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

Автоматическое заполнение ряда для линейной наилучшей тенденции

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

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

    Перетащите маркер заполнения в нужном направлении, увеличив значения или уменьшив значения.

Совет: ряд (вкладка "Главная ", Группа " Редактирование ", кнопка " Заливка ").

Автоматическое заполнение ряда для экспоненциального роста

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

Чтобы заполнить ряд для экспоненциальной тенденции, выполните указанные ниже действия.

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

    Если вы хотите улучшить точность цикла тренда, выберите дополнительные начальные значения.

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

Например, если выделенные начальные значения в ячейках C1: E1 - 3, 5 и 8, перетащите маркер заполнения вправо, чтобы заполнить с помощью увеличения значений тенденций, или перетащите его влево, чтобы заполнить с уменьшением значений.

Совет: Чтобы вручную управлять созданием ряда или заполнять его с помощью клавиатуры, нажмите кнопку ряд (вкладка "Главная ", Группа " Редактирование ", кнопка " Заливка ").

Заполнение линейного тренда или значений тенденций роста вручную

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

    В линейной серии начальные значения применяются к алгоритму наименьших квадратов (y = mx + b) для создания ряда.

    В ряде роста начальные значения применяются к алгоритму экспоненциальной кривой (y = b * m ^ x) для создания ряда.

В любом случае значение шага не учитывается. Созданный ряд эквивалентен значениям, возвращаемым функцией тенденция или рост.

Чтобы ввести значения вручную, выполните указанные ниже действия.

    Выделите ячейку, в которой нужно начать ряд. Ячейка должна содержать первое значение в ряду.

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

    На вкладке Главная в группе Редактирование нажмите кнопку Заполнить и выберите пункт Прогрессия .

    Выполните одно из указанных ниже действий.

    • Чтобы заполнить весь ряд вниз по листу, щелкните столбцы .

      Чтобы заполнить ряд на листе, нажмите кнопку строки .

    В поле шаг введите значение, на которое нужно добавить ряд.

    В разделе тип выберите вариант линейный или рост .

    В поле значение остановки введите значение, по которому нужно остановить ряд.

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

Вычисление тенденций с помощью добавления линии тренда на диаграмму

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

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

    Щелкните диаграмму.

    Щелкните ряд данных, в который вы хотите добавить линия тренда или скользящее среднее.

    На вкладке Макет в группе анализ нажмите кнопку линия тренда , а затем выберите нужный тип регрессионной линии тренда или скользящего среднего.

    Чтобы настроить параметры и отформатировать регрессионную линию тренда или скользящее среднее, щелкните линию тренда правой кнопкой мыши и выберите в контекстном меню пункт Формат линии тренда .

    Выберите нужные параметры линии тренда, линии и эффекты.

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

      Если вы выбрали скользящее среднее , введите в поле период число периодов, которые будут использоваться для расчета скользящего среднего.

Примечания:

    В поле « на основе ряда » перечислены все ряды данных на диаграмме, поддерживающих линии тренда. Чтобы добавить линию тренда в другой ряд, щелкните его имя в поле, а затем выберите нужные параметры.

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

Выполнение регрессионного анализа с помощью надстройки "пакет анализа"

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

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

Ниже показано, как с помощью маркера заполнения создать линейную тенденцию чисел в Excel в Интернете.

Значения проекта с помощью функции листа

Использование функции ПРЕДСКАЗ Функция ПРЕДСКАЗ вычисляет или прогнозирует будущее значение с использованием существующих значений. Предсказываемое значение - это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. Эту функцию можно использовать для предсказания будущих продаж, потребностей в запасах и тенденций потребителей.

Использование функции тенденция или функции роста Функции тенденция и рост могут вырезки будущих значений y , которые расширяют прямую линию или экспоненциальную кривую, которая лучше описывает существующие данные. Кроме того, они могут возвращать только значения y по известным значениям x для наилучшего размера линии или кривой. Чтобы отобразить линию или кривую, описывающую существующие данные, используйте существующие значения x и y , возвращаемые функцией тенденция или рост.

Использование функции ЛИНЕЙН или функции ЛИНЕЙН Для вычисления прямой линии или экспоненциальной кривой с существующими данными можно использовать функцию ЛИНЕЙН или ЛИНЕЙН. Функция ЛИНЕЙН и функция ЛИНЕЙН возвращают различные статистические данные по регрессии, в том числе наклон и перехват линии наилучшего размера.

Функция

Описание

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

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также (на английском языке).

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

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

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

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


Способ 2: оператор ПРЕДСКАЗ

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

ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

«X» – это аргумент, значение функции для которого нужно определить. В нашем случае в качестве аргумента будет выступать год, на который следует произвести прогнозирование.

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.


Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ . Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ , а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа» , но он не является обязательным и используется только при наличии постоянных факторов.

Данный оператор наиболее эффективно используется при наличии линейной зависимости функции.

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


Способ 4: оператор РОСТ

Ещё одной функцией, с помощью которой можно производить прогнозирование в Экселе, является оператор РОСТ. Он тоже относится к статистической группе инструментов, но, в отличие от предыдущих, при расчете применяет не метод линейной зависимости, а экспоненциальной. Синтаксис этого инструмента выглядит таким образом:

РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы у данной функции в точности повторяют аргументы оператора ТЕНДЕНЦИЯ , так что второй раз на их описании останавливаться не будем, а сразу перейдем к применению этого инструмента на практике.


Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ . Его синтаксис имеет такой вид:

ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Последние два аргумента являются необязательными. С первыми же двумя мы знакомы по предыдущим способам. Но вы, наверное, заметили, что в этой функции отсутствует аргумент, указывающий на новые значения. Дело в том, что данный инструмент определяет только изменение величины выручки за единицу периода, который в нашем случае равен одному году, а вот общий итог нам предстоит подсчитать отдельно, прибавив к последнему фактическому значению прибыли результат вычисления оператора ЛИНЕЙН , умноженный на количество лет.


Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ . Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Как видим, все аргументы полностью повторяют соответствующие элементы предыдущей функции. Алгоритм расчета прогноза немного изменится. Функция рассчитает экспоненциальный тренд, который покажет, во сколько раз поменяется сумма выручки за один период, то есть, за год. Нам нужно будет найти разницу в прибыли между последним фактическим периодом и первым плановым, умножить её на число плановых периодов (3) и прибавить к результату сумму последнего фактического периода.


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

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

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

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

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

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

Лучшая статья месяца

Мы подготовили статью, которая:

✩покажет, как программы слежения помогают защитить компанию от краж;

✩подскажет, чем на самом деле занимаются менеджеры в рабочее время;

✩объяснит, как организовать слежку за сотрудниками, чтобы не нарушить закон.

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

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

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

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

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

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

Шаг 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 и т. д. Для наглядности можно назначить ячейкам процентный формат. Правой кнопки мыши выбираем «Формат ячеек», затем вкладку «Число» и далее вкладку «Процентный, два знака после запятой».

  • Сезонные спады в бизнесе: 3 способа вытянуть продажи

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

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

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

Первым аргументом в функции указываем ссылку на 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).

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

  • Как регулярное изучение спроса повышает динамику продаж на 648%

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

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

Максим Люлин,

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

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

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

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

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

Кирилл Чихачев,

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

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

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

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

Из данной статьи вы узнаете, как в Excel рассчитать прогноз продаж с учетом роста и сезонности .

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

Процесс расчета прогноза разделим на 3 части:

  1. Расчет значение тренда ;
  2. Определение коэффициентов сезонности ;
  3. Прогнозирование продаж ;

Рассчитаем прогноз по месяцам на 2 года и 3 месяца на основании продаж за 5 лет (см. вложенный файл).

Для расчета значений тренда:

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

Для расчета коэффициентов сезонности:

  1. Рассчитываем отклонение фактических значений от значений тренда. Для этого фактические значения делим на значения тренда;
  2. Для каждого месяца определяем среднее отклонение за последние 5 лет.
  3. Определяем общий индекс сезонности - среднее значение коэффициентов, рассчитанных в 4 пункте;
  4. Рассчитываем коэффициенты сезонности ; каждый коэффициент из пункта 4 делим на коэффициент из пункта 5 (см. вложенный файл);

Рассчитываем прогноз продаж с учетом роста и сезонности:

  1. Задаём период, на который мы хотим рассчитать прогноз. Для этого продлеваем номера периодов временного ряда на 2 года и 3 месяца.
  2. Рассчитываем значения трена для будущих периодов . В уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде (от 61 до 87). Получаем y-значения линейного тренда для каждого будущего периода (см. вложенный файл).
  3. Рассчитываем прогноз . Для этого значения линейного тренда умножаем на коэффициенты сезонности.

Прогноз с учетом роста и сезонности готов.

Для более точного прогнозирования продаж не достаточно учитывать рост и сезонность, необходимо также учесть еще дополнительные факторы, которые значительно влияют на объем продаж, такие как

  • мероприятия по стимулированию сбыта,

    ввод новых продуктов,

    открытие новых направлений продаж,

    спец. клиенты с разовыми значительными закупками

и т.д., но об этом в следующих статьях.

Точных вам прогнозов!

С помощью программы Forecast4AC PRO вы сможете рассчитывать прогноз с учетом роста и сезонности для более, чем 5000 строк одновременно одним нажатием клавиши. Легко и быстро!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :

  • Novo Forecast Lite - автоматический расчет прогноза в Excel .
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.