Инструменты прогнозирования в microsoft excel

Шаг 2

Так как мы рассматриваем аддитивную модель вида: 

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

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

Зачем нужен план

Самая главная задача плана — продемонстрировать, что его цель хотя бы теоретически достижима

Особенно важно об этом помнить стартаперам — на пути великой идеи часто встаёт не менее великая математика и оказывается, что даже сверхоптимистичный план даёт убытки на протяжении первых 100 лет

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

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

Поэтому даже худой план лучше его отсутствия.

Пример прогнозирования продаж в Excel

Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).

Уравнение линейного тренда:

y = bx + a

• y — объемы продаж;
• x — номер периода;
• a — точка пересечения с осью y на графике (минимальный порог);
• b — увеличение последующих значений временного ряда.

Допустим у нас имеются следующие статистические данные по продажам за прошлый год.

1. Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a. В ячейке D15 Используем функцию ЛИНЕЙН:
2. Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).
3. Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).
4. Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).
5. Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.
6. Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.
7. В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
8. Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:
9. Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
10. На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:

Общая картина составленного прогноза выглядит следующим образом:

График прогноза продаж:

График сезонности:

Алгоритм анализа временного ряда и прогнозирования

Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:

1. Выделяем трендовую составляющую, используя функцию регрессии.
2. Определяем сезонную составляющую в виде коэффициентов.
3. Вычисляем прогнозные значения на определенный период.

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

бланк прогноза деятельности предприятия

Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:

Экономико-статистические методы

Экономико-статистические методы включают широкий спектр различных методов. Не все они получили широкое распространение в практике . Наиболее популярными являются:

Метод экстраполяции – определяет объем продаж как функцию времени (или иного фактора). Как обычно в таких случаях необходим учет динамического (временного) ряда показателя

• Метод скользящей средней величины продаж. Планируемый объем продаж определяется делением фактического объема продаж на продолжительность анализируемого периода. Скользящая средняя величина пересчитывается по мере получения новых значений;
• Метод доли рынка предприятия – в основе данные о среднегодовых темпах роста емкости целевого рынка в прошедшем периоде и планируемой доли предприятия в общем объеме продаж

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

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

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

Методы целевого прогнозирования продаж

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

Менеджеры по продажам и руководство компании задают объем продаж, а затем разрабатывают детализированные планы, которые призваны реализовать планы.

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

1 этап целевого прогнозирования. Задайте необходимый объем продаж (например, в этом году компания планирует продать 100 000 единиц товара).

Если продукт или его прямой аналог имеет историю продаж на рынке, при составлении целевого прогноза учитывайте следующие факторы:

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

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

2 этап целевого прогнозирования. Определите действия, при которых целевой объем продаж может быть реализован с выгодой для компании.

Проанализируйте все затраты, связанные с закупкой и продажами продукта:

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

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

• из каких видов рекламы и средств массовой информации предстоит сделать выбор;
• какие виды рекламы достигнут целевых потребителей;
• сколько будет стоить создание и размещение необходимых видов рекламы.

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

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

Правильный подход

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

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

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

Твитнуть
Поделиться
Поделиться
Отправить

Вам будет интересно

Автоматизация kpi

Разработка KPI

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

Система мотивации персонала KPI

Мотивация персонала на основе KPI

Тренинги по продажам

Что позволяет сделать прогноз продаж

Поговорим о конкретной пользе прогнозирования. Несколько фактов о прогнозе объема продаж:

• Помогает конкретизировать цель. Менеджеры работают на достижение цифр. то количество холодных звонков, общая сумма сделок за месяц, число подписанных контрактов и т.д. Прогноз помогает управленцу ориентироваться. Цель выбирается либо равной прогнозной, либо увеличенной. Если желанная цифра выше предполагаемой, указываются конкретные способы достижения цели и инструменты.
• Определяет формирование ресурсных запасов бизнеса. Прогноз объема продаж, пример которого можно увидеть в небольшой компании, примерно таков. Небольшое производство из месяца в месяц реализует на 5% больше продукции, чем в предыдущем периоде. С учетом этого факта руководитель закупает количество материалов и нанимает рабочих в большем количестве.
• Гарантирует мобильность бизнеса. Сезонные изменения или предсказанные трудности, обоснованные влиянием сторонних факторов, негативно сказываются на объеме продаж. Прогнозирование помогает вовремя подстроиться под изменения. Предупрежден — значит вооружен. Постоянный мониторинг показателей помогает быстрее принимать решения.
• Минимизирует затраты. С точным прогнозом управленец всегда предупрежден об опасности. Анализ “открывает глаза” на возможные убытки, позволяя их избежать. Вариантов прогнозирования объемов продаж десятки.

Мы остановимся на наиболее распространенных.

Роль прогноза объема продаж

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

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

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

Отличие прогноза от плана

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

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

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

Сбор и подготовка статистики продаж

Прогнозирование начинается, конечно, со сбора статистики продаж

Здесь нужно обращать внимание на то, чтобы все сделки были более-менее одного «масштаба», и чтобы количество сделок в месяц было достаточно большое

Например, розничный магазин. Даже в небольшом магазине в месяц могут делаться тысячи и даже десятки тысяч покупок. Сумма каждой покупки, по сравнению с месячной выручкой, весьма мала — 0,0..01% от выручки. Это хорошая ситуация для прогнозирования.

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

Далее мы будем рассматривать пример со статистикой из таблицы 1. На рисунке 1 данные таблицы представлены в виде графика.

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

Существуют две основные модели временного ряда: аддитивная и мультипликативная. Формула аддитивной модели: Yt = Tt + St + et Формула мультипликативной модели: Yt = Tt x St + et Обозначения: t — время (месяц или другой период детализации); Y — значение величины; Т — тренд; S — сезонные изменения; е — шум. Разница между моделями хорошо видна на рисунке 2, где приведены два ряда, с одинаковыми трендами, один ряд — по мультипликативной модели, другой — по аддитивной.

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

Рис. 2. Примеры рядов: слева — по аддитивной модели; справа — по мультипликативной.

В нашем примере мы будем использовать мультипликативную модель.

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

В формулах моделей рядов динамики (Yt = Tt + St + et и Yt = TtSt + et) фигурирует тренд Tt, такой тренд мы будем называть «точным».

В практических задачах выделить точный (вернее, «почти точный») тренд Tt может оказаться технически очень сложно (см. например, пункт в списке литературы).

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

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

Где Mt — значение скользящего среднего в точке t; Yt — значение величины временного ряда в точке t.

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

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

Прологарифмируем уравнение мультипликативной модели, и если шум et не очень большой, то получим аддитивную модель.

Рис. 3. График прологарифмированной величины показателя и тренда Ми скользящего среднего по 12-ти месяцам. Слева на одном графике и величина и тренд. Справа — тренд в увеличенном масштабе. По оси X — номера периодов.

Примечание. Если темпы динамики небольшие, скажем, 10-15% в год, то и с мультипликативной моделью можно работать как с аддитивной (не логарифмирую).

Метод прогнозирования временных рядов

В основе метода статистическое определение средней величины. Для подготовки прогноза понадобится информация об объемах продаж за предыдущий год в разбивке по месяцам. На основе имеющихся данных определяется средний темп роста. Для этого значение месяца делится на число за предыдущий месяц, и полученный результат переводится в проценты. Если в итоге получилось более 100% — мы наблюдаем явный рост. По итогам расчетов за каждый месяц вычисляется средняя величина темпа роста. Затем цифра каждого периода увеличивается на среднее значение. Мы получаем не абсолютно точный прогноз, но приближенный к реальности. Большей точности помогут добиться сложные математические модели и использование специального программного обеспечения.

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

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

Субъективное прогнозирование этапов

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

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

Чем раньше у них есть такие данные, тем лучше будут их финансовые прогнозы.

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

СПРОС ЗАВИСИТ ОТ ПАРАМЕТРОВ ТОВАРОВ — КАК УЧЕСТЬ?

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

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

Распределение спроса на товар в зависимости от размера. Таблица в Excel

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

Зависимость спроса от параметров товаров наблюдается не только в продажах спортивной одежды. В системе 1С: ERP можно настроить дополнительные разрезы учета с помощью характеристик товаров. Например,можно настроить следующие разрезы учета: полотно,цвет,состав полотна,размер. И дальше составлять планы продаж с учетом особенностей распределения спроса по этим параметрам.

Настройки вида номенклатуры в 1С: ERP

В системе данные хранятся в разрезе этих характеристик,поэтому проблем с составлением плана на основе данных прошлых периодов в разрезе этих же характеристик не возникнет.

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

План продаж в 1С: ERP

Фрагмент отчета по продажам в 1 °C: ERP

Когда план не нужен

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

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

Если же вы, как я, получаете удовольствия от достижения цели (или не получаете, но НАДО), то остро встаёт вопрос контроля множества нюансов: расхода времени, денег и прочих ресурсов, соблюдения всяческих критериев и так далее. Тут нам на помощь и приходит планирование.

Составление сценария

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

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

Обе эти субъективные методологии больше похожи на искусство, чем на науку. И служат скорее дополнением к математически-ориентированным подходам.

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

(via)

Как вычислить процент выполнения от общего плана

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

На этот раз регионы не имеют столбца со своим собственным планом. Вместо этого сразу идет столбец «Доля», где каждый показатель продаж сравнивается с общим планом, указанным в ячейке E2. Формула в столбце «Доля» на этот раз выглядит следующим образом =B2/$E$2.

Обратите внимание на то, что в знаменателе формулы используется абсолютная ссылка на ячейку $E$2. Символы доллара указывают нам на то, что ссылка на ячейку с значением общего плана заблокирована

Благодаря этому она не изменяется при копировании формулы в другие ячейки столбца «Доля». В ячейке C6 мы суммируем все проценты чтобы убедиться в точности результата. Как видим снова и на втором рисунке мы получили такое же перевыполнение общего плана – 105%. Итоговые значения в процентах у нас совпали, значит все вычисления формул верны.

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

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

В Google Sheets выбираем Редактор диаграмм  -> Дополнительные и ставим галочку возле Линии тренда. В настройках выбираем Ярлык — Уравнение и Показать R^2.

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.