Гийом Сен-Жак, 18-06-2008 г. (последняя редакция - 22-02-2010 г.)

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

Преимущества прогнозирования

Прогнозирование поможет вам принимать правильные решения и зарабатывать/экономить деньги. Ниже приведен пример

• Выбирайте оптимальный размер товарных запасов

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

Как? Путем прогнозирования!

Как упростить задачу: обозначения, комментарии, имена файлов

С течением времени по мере накопления данных, у вас будет все больше шансов запутаться и совершать ошибки. Есть ли решение? Будьте организованными: правильное использование обозначений, комментариев и присвоение понятных имен файлам сэкономит много времени.

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

• Используйте комментарии.

Даже если обычно вы не работаете с большими объемами информации, запутаться очень легко. Это особенно актуально, когда вы возвращаетесь к таблицам, созданным вами довольно давно. У Excel есть хорошее решение: комментарии . Кликните правой кнопкой мыши по ячейке, куда вы хотите добавить комментарий и в меню выберите « добавить комментарий ».

Их можно использовать:

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

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

Начало: простой пример прогнозирования с использованием линии тренда

Давайте получим первый прогноз. В этой части мы будем использовать следующий файл: Example1.xls . Данные приведены в качестве примера.

Наши данные: В первом столбце содержатся данные о цене единицы схожих товаров. Цена единицы товара отражает качество продукта. Во втором столбце - данные об объеме продаж.

Что мы хотим узнать: Если мы будем продавать другой продукт, качества соответствующего цене в $150 за единицу, сколько предположительно единиц продукции мы продадим?

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

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

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

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

Чтобы понять точное отношение между данными, в меню "Диаграмма" выберем опцию "Добавить линию тренда".

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

Теперь наша линия тренда отобразилась на диаграмме. Кликнув на диаграмме правой кнопкой можно получить точное уравнение зависимости: y = 102.4x - 191.64.

Понимаем: Количество проданных товаров = 102.4 умножить на цену товара - 191.64.

Поэтому, если мы решим производить товары по цене $150 за единицу, мы можем предположить, что объем продаж составит: 102.4*150 - 191.64 = 15168 штук.

Мы только что успешно закончили наш первый прогноз.

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

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

• После оценки диаграммы, вы можете использовать функцию КОРРЕЛ . В нашем примере функция будет иметь вид: КОРРЕЛ(A2:A83,B2:B83). Если результат близок к 0, тогда корреляция слабая, и вывод таков: реального тренда просто не существует. Если значение близко к 1, тогда корреляция сильная. Последнее очень помогает, так как это увеличивает силу объяснения выявленной вами закономерности.

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

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

Прогнозирование с использованием Пакета аналитических инструментов

Перед тем, как продолжить, проверьте, что Excel ATP (Пакет аналитических инструментов) установлен. Для получения подробной информации обратитесь к секции Установка Пакета аналитических документов.

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

Идем дальше: пример экспоненциальной зависимости

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

Ниже изложено, как произвести подгонку под экспоненциальную модель:

1) Посмотрите на свои данные. Нарисуйте простой график и просто посмотрите на него. Если он соответствует экспоненциальному развитию, он должен выглядеть так:

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

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

Затем, как обычно, получите уравнение линии.

2) К счастью, все это можно проделать напрямую, используя Пакет аналитических инструментов: введите все свои данные в пустую таблицу Excel и в меню выберите Инструменты => Анализ данных

Установка Пакета аналитических инструментов

Это пакет является дополнением к Microsoft Excel, но он не всегда установлен по умолчанию. Для его установки нужно проделать следующее:

1. Убедитесь, что у вас есть установочный диск Office. Excel может запросить вставить диск для установки файлов Пакета.
2. Откройте таблицу Excel и в меню Инструменты выберите Дополнения. Отметьте первый пункт в окне под названием « Analysis ToolPack (Пакет аналитических инструментов) ».
3. Вставьте диск Office CD, если потребуется.
4. Готово! Обратите внимание, что в меню « Инструменты » теперь больше пунктов, в том числе опция « Анализ данных ». Это именно та, которой мы будем пользоваться больше всего.

Использование Пакета аналитических инструментов

... в случае линейной функции

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

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

Теперь нужно дать Excel два аргумента: « шкала Y » и « шкала X ». Шкала Y показывает, что вы хотите рассчитать (например, объем продаж), а на шкале X отражаются данные, которые, как вы думаете, объясняют объем продаж (в нашем примере, цена единицы товара). В нашем примере (см. example1.xls), данные об объеме спроса содержатся в столбце B, в строках с 3 по 90, поэтому для шкалы Y вам необходимо указать « $B$3:$B$90 » и «$A$3:$A$90 » для шкалы X. Когда закончите, нажмите « ok ».

Появится новый лист с « результатами регрессии ».
Самый важный результат содержится в столбце « Коэффициенты » в конце таблицы. Пересечение является константой, коэффициент « переменной X (X variable) » - это коэффициент Х (в данном примере цена единицы товара). Таким образом, мы определяем уравнение "тренда". Объем продаж=Пересечение+КоэффициентХ*цена единицы товара=-126+100*цена товара.

В этой таблице также содержится полезное значение, которое даст вам представление о том, насколько точны ваши вычисления: « R Square ». Если это значение близко к 1, тогда ваши приближения достаточно точные, и это означает что полученное уравнение является достаточно точным представлением ваших данных. Если это значение близко к 0, то приближение недостаточно хорошее, и, возможно, вам нужно попробовать другую модель (см. далее экспоненциальная модель).

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

... используя экспоненциальную модель

Если линейная модель не подходит (если вы получили низкое значение R-square, например 0,1), возможно, вам необходимо использовать экспоненциальную модель.

Запустите Пакет инструментов, как обычно: Откройте таблицу, затем откройте меню « Инструменты » и выберите « Анализ данных ». Вы увидите всплывающее окно с вопросом, какой вид анализа вы хотите провести. Для экспоненциальной модели, выбираем « экспоненциальная ».

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

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

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

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

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

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

• Сколько товара с нерегулярным спросом надо приобрести для поддержания разумного уровня сервиса?

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

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

Пример прогнозирования продаж по методу BRT

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

Допустим, время поставки товара с момента его заказа у поставщика до прихода на склад составляет четыре дня, а текущий остаток на складе - 1 штука. Количество проданных наименований в заданный период - 30 штук.

• В каком количестве необходимо произвести закупку товара сейчас с учетом срока поставки товара?

При расчете на основе средних продаж мы бы получили значение средней реализации товара в размере: 30 штук/31 дней = 0,97 штуки в день, а объем продаж за время поставки составил бы около 4 единиц, точнее 0,97 штуки*4 дня = 3,9 штуки.

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

• Сколько же товара надо держать на складе и какой уровень сервиса можно гарантировать клиентам в этой ситуации, чтобы обеспечить удовлетворение максимального спроса, не потратив излишних денег на большие закупки?

Проведенный выше анализ на основе расчета средних продаж не отвечает на эти вопросы.

Поэтому для прогнозирования нерегулярных продаж крайне важно использовать специальные методы, позволяющие производить анализ нерегулярных событий. Сравнительно недавно стали разрабатываться методы на основе так называемой Bootstrapping-статистики. Одним из таких методов, используемых при анализе нерегулярных и разреженных рядов, является метод под названием Bootstrapping Reaction Time (BRT)*.

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

• Какой вариант прогноза продаж наиболее приемлем, исходя из имеющихся данных?

Для поиска ответа составим таблицу всех возможных вариантов на основании имеющихся данных. Для этого разбиваем наш ряд по порядку на периоды реакции (сроки поставки заказа): сначала с 1 по 4 день, потом с2 по 5, потом с 3 по 6 и т.д. - всего 28 возможных вариантов.

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

• Скольким клиентам наша компания готова обеспечить безусловную доступность товара?

Под «безусловной доступностью» будем понимать следующую ситуацию: если у нас в среднем покупают по 10 штук ежедневно, но был случай, что кто-то купил 100 штук, то «безусловная доступность» означает, что у нас на складе должно быть в наличие 100 штук товара.

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

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

• Какой процент клиентов мы можем обслужить - продать товар, отбросив фактор наличия на складе?

Как правило, это значение колеблется в районе 80-91%. Для нашего примера остановимся на уровне доступности - 80%. Оставшихся клиентов - 20% - мы «отбрасываем», считая, что для них мы не готовы хранить большие запасы товара на складе и не будем учитывать в плане закупок.

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

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

Для нашей выборки это значение равняется 8 штукам, что покроет требование 21 из 28 возможных исходов (если бы мы выбрали уровень доступности 70/10, то это было бы значение 5 штук, что покрывало бы 20 возможных исходов из 28 возможных).

В управленческой логике, найденное нами значение в 8 штук можно интерпретировать следующим образом: при обслуживании 8 из 10 клиентов, в течение 4 дней они купят суммарно меньше 8 штук товара, а закупка будет равняться 8 - 1 = 7 штук. Этот результат существенно отличается от значения, полученного при расчете «простой средней».

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

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

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

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

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

Прогнозирование продаж, анализ фактических результатов, внесение корректив — это уже фактически бизнес-планирование.

Прогнозирование продаж — простая математика

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

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

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

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

Опыт как огромное преимущество

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

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

Используйте прошлые результаты в качестве гида

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

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

Ищите драйверы

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

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

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

Оцените прямые затраты

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

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

Никогда не стройте прогнозы спонтанно

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

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

Опирайтесь на свои прогнозы

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

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

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

1. Регулярную составляющую
2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

1. Тренда
2. Сезонности
3. Циклической составляющей

Однако, в модели необязательно наличие всех этих компонент сразу.

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

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

1. Тренд (Tt)
2. Сезонность (St)
3. Цикличность (Ct)
4. Случайные возмущения (Et)

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

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

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

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Алгоритм построения модели

1. Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
2. Рассчитываем значение сезонной компоненты St.
3. Рассчитываем значения Tt с использованием полученного уравнения тренда.
4. Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
5. Оцениваем качество модели.

Реализация на практике

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

Шаг 1

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

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Шаг 2

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

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

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

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

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

Шаг 3

Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.

T(t) = - 23294 + 34114 * t - 1593 *t^2 + 26,3 *t^3

Вместо t используем значения из столбца Период из соответствующей строки.

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

Вот мы и нашли спрогнозированные значения уровней продаж на 2018 год. Значения отражают возрастающую тенденцию и сезонные пики. Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

Шаг 5

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

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

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении 🙂

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

Преимущества и недостатки

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

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

Как организовать планирование продаж

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

Как подготовить план продаж

Для планирования «от достигнутого» основой служит информация о динамике продаж (в натуральном и стоимостном выражении) за предыдущий период, сопоставимый как по продолжительности, так и по сезонности с плановым. Это требование бывает трудно выполнить, так как продажи обычно прогнозируются в IV квартале, когда год еще не закончился и итоги по нему не подведены. В этом случае используют информацию о фактической реализации за 9 или 10 прошедших месяцев и плановой – за оставшееся до конца года время (ноябрь–декабрь).

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

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

Самый простой способ спланировать продажи – взять объем реализации за базовый период (тот, что берется за основу, например, прошлый месяц или аналогичный месяц прошлого года – при планировании по месяцам) и скорректировать его на желаемый прирост по формуле 1.

Формула 1. Расчет плана продаж

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

Учесть структуру продаж.

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

Таблица 1. План продаж в стоимостном выражении по контрагентам

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

Таблица 2. План продаж в стоимостном выражении по номенклатуре

Можно предусмотреть и двухуровневую структуру плана продаж:

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

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

Таблица 3. План продаж в стоимостном выражении по контрагентам и продукции

Контрагент	Номенклатура
ООО «Елочка»	Конфеты «Бриз»	1500,00	1,015	1522,50
	Конфеты «Грильяж»	1000,00	1,040	1040,00
	Конфеты «Сладкоежка»	1500,00	1,070	1605,00
	Конфеты «Солнышко»	1000,00	1,050	1050,00
	Итого	5000,00	1,044	5217,50
ООО «Замок»	Конфеты «Бриз»	5000,00	1,010	5050,00
	Конфеты «Грильяж»	2000,00	1,040	2080,00
	Конфеты «Сладкоежка»	2000,00	1,075	2150,00
	Конфеты «Солнышко»	1000,00	1,015	1015,00
	Итого	10 000,00	1,030	10 295,00
ООО «Зебра»	Конфеты «Бриз»	1000,00	1,110	1110,00
	Конфеты «Грильяж»	500,00	1,090	545,00
	Конфеты «Сладкоежка»	1500,00	1,100	1650,00
	Конфеты «Солнышко»	1000,00	1,040	1040,00
	Итого	4000,00	1,086	4345,00
ООО «Кенгуру»	Конфеты «Бриз»	7500,00	1,010	7575,00
	Конфеты «Грильяж»	9500,00	1,040	9880,00
	Конфеты «Сладкоежка»	2000,00	1,050	2100,00
	Конфеты «Солнышко»	1000,00	1,030	1030,00
	Итого	20 000,00	1,029	20 585,00
Всего		39 000,00	1,037	40 442,50

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

Таблица 4. План продаж в стоимостном выражении по номенклатуре продукции и покупателям

Номенклатура	Контрагент	Объем продаж за базовый период, руб.	Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.
Конфеты «Бриз»	ООО «Елочка»	1500	1,015	1522,50
	ООО «Замок»	5000	1,010	5050,00
	ООО «Зебра»	1000	1,110	1110,00
	ООО «Кенгуру»	7500	1,010	7575,00
	Итого	15 000	1,017	15 257,50
Конфеты «Грильяж»	ООО «Елочка»	1000	1,040	1040,00
	ООО «Замок»	2000	1,040	2080,00
	ООО «Зебра»	500	1,090	545,00
	ООО «Кенгуру»	9500	1,040	9880,00
	Итого	13 000	1,042	13 545,00
Конфеты «Сладкоежка»	ООО «Елочка»	1500	1,070	1605,00
	ООО «Замок»	2000	1,075	2150,00
	ООО «Зебра»	1500	1,100	1650,00
	ООО «Кенгуру»	2000	1,050	2100,00
	Итого	7000,00	1,072	7505,00
Конфеты «Солнышко»	ООО «Елочка»	1000,00	1,050	1050,00
	ООО «Замок»	1000,00	1,015	1015,00
	ООО «Зебра»	1000,00	1,040	1040,00
	ООО «Кенгуру»	1000,00	1,030	1030,00
	Итого	4000,00	1,034	4135,00
Всего		39 000,00	1,037	40 442,50

Учесть факторы, влияющие на рост продаж

На величину выручки влияют два показателя: цена и объем продаж в натуральном выражении. При планировании можно учитывать желаемую динамику каждого из них. Различные источники прироста (цена и количество) принимаются во внимание при формировании целевого процента увеличения (прироста) продаж (см. формулу 2 Расчет целевого процента прироста продаж):

Формула 2. Расчет целевого процента прироста продаж

Например, перед коммерсантами поставили задачу: увеличить объем продаж на 10 процентов. При этом не уточняется, что должно быть источником этого роста. Можно сформулировать цель четче: увеличить количество продаваемого товара на 5 процентов при росте цен на 6 процентов. В этом случае целевой прирост продаж будет равен 11,3 процента ((100% + 5%) × (100% + 6%) : 100% – 100%). Применяя этот способ планирования продаж, нужно учитывать двухуровневую структуру прогноза реализации продукции – ее можно раскрыть как по видам продукции с разделением по контрагентам, так и наоборот (см. таблицу 5. План продаж с учетом динамики цен и объемов реализации). Если у компании большой ассортимент продукции или широкий круг контрагентов, номенклатуры или клиентов лучше объединить в группы. Например, контрагентов можно агрегировать по регионам, масштабам закупок, целям приобретения товара, способам оплаты и т. п.

Таблица 5. План продаж с учетом динамики цен и объемов реализации

Контрагент	Номенклатура	Факт			Коэффи- циент роста цен, ед.	Коэффи- циент роста объема реализации, ед.	Коэффи- циент роста продаж, ед.	План
		Цена, руб.	Коли- чество, кг	Объем продаж, руб.				Цена, руб.	Количество, кг	Объем продаж, руб.
ООО «Елочка»	Конфеты «Бриз»	50,00	30,00	1500,00	1,05	1,06	1,113	52,50	31,80	1669,50
	Конфеты «Грильяж»	100,00	10,00	1000,00	1,03	1,06	1,092	103,00	10,60	1091,80
	Конфеты «Сладкоежка»	25,00	60,00	1500,00	1,04	1,07	1,113	26,00	64,20	1669,20
	Конфеты «Солнышко»	40,00	25,00	1000,00	1,05	1,05	1,103	42,00	26,25	1102,50
Итого	–	125,00	5000,00	–	–-	–	–	132,85	5533,00
ООО «Замок»	Конфеты «Бриз»	40,00	125,00	5000,00	1,07	1,09	1,166	42,80	136,25	5831,50
	Конфеты «Грильяж»	100,00	20,00	2000,00	1,04	1,08	1,123	104,00	21,60	2246,40
	Конфеты «Сладкоежка»	20,00	100,00	2000,00	1,06	1,05	1,113	21,20	105,00	2226,00
	Конфеты «Солнышко»	40,00	25,00	1000,00	1,10	1,06	1,166	44,00	26,50	1166,00
Итого	–	270,00	10 000,00	–	–	–	–	289,35	11 469,90
ООО «Зебра»	Конфеты «Бриз»	50,00	20,00	1000,00	1,08	1,10	1,188	54,00	22,00	1188,00
	Конфеты «Грильяж»	100,00	5,00	500,00	1,09	1,06	1,155	109,00	5,30	577,70
	Конфеты «Сладкоежка»	25,00	60,00	1500,00	1,11	1,10	1,221	27,75	66,00	1831,50
	Конфеты «Солнышко»	40,00	25,00	1000,00	1,06	1,09	1,155	42,40	27,25	1155,40
Итого	–	110,00	4000,00	–	–	–	–	120,55	4752,60
ООО «Кенгуру»	Конфеты «Бриз»	34,90	215,00	7500,00	1,20	1,10	1,320	41,88	236,39	9900,00
	Конфеты «Грильяж»	95,00	100,00	9500,00	1,09	1,03	1,123	103,55	103,00	10 665,65
	Конфеты «Сладкоежка»	20,00	100,00	2000,00	1,08	1,04	1,123	21,60	104,00	2246,40
	Конфеты «Солнышко»	40,000	25,00	1000,00	1,06	1,06	1,124	42,40	26,50	1123,60
Итого	–	440,00	20 000,00	–	–	–	–	469,89	23 935,65
Всего		–	944,90	39 000,00	–	–	–	–	1012,64	45 691,15

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

Для подготовки бюджета движения денежных средств необходимо планировать продажи по месяцам, желательно в разрезе контрагентов, так как это позволит учитывать динамику дебиторской задолженности. Выручка прогнозируется с НДС. Если компания не применяет специальные ставки этого налога (10% и 0%), то весь запланированный объем продаж умножается на 18 процентов (см. таблицу 8. План продаж в стоимостном выражении с НДС для бюджета движения денежных средств). В обратном случае потребуется группировать контрагентов и продажи по ним, а затем умножить полученные объемы реализации на соответствующие ставки налога. Составляя бюджет движения денежных средств, не забудьте скорректировать план продаж на прирост и погашение дебиторской задолженности. Если условия платежа для всех контрагентов одинаковы (например, оплата в течение 14 календарных дней после отгрузки), можно уточнять общий план продаж на переходящую дебиторскую задолженность. При различных условиях оплаты необходимо группировать покупателей по длительности отсрочки (см. таблицу 9. Корректировка плана продаж в стоимостном выражении с НДС для бюджета движения денежных средств).

Таблица 6. План продаж в стоимостном выражении с НДС для бюджета движения денежных средств (фрагмент)

Контрагент	Январь			…	Декабрь			Итого за год
		Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.	…	Объем продаж за аналогичный период прошлого года, руб.	Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.	Объем продаж за аналогичный период прошлого года, руб.	Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.
ООО «Елочка»	500,00	1,05	525,00	…	400,00	1,05	420,00	6000,00	1,05	6300,00
ООО «Замок»	600,00	1,04	624,00	…	700,00	1,04	728,00	7800,00	1,04	8112,00
ООО «Зебра»	300,00	1,10	330,00	…	150,00	1,10	165,00	3000,00	1,10	3300,00
ООО «Кенгуру»	2000,00	1,03	2060,00	…	1500,00	1,03	1545,00	21 000,00	1,03	21 630,00
Всего	3400,00	–	3539,00	…	2750,00	–	2858,00	37 800,00	–	39 342,00
НДС (18%)	612,00	–	637,02	…	495,00	–	514,44	6804,00	–	7081,56
Всего с НДС	4012,00	–	4176,02	…	3245,00	–	3372,44	44 604,00	–	46 423,56

Таблица 7. Корректировка плана продаж в стоимостном выражении с НДС для бюджета движения денежных средств (фрагмент)

Показатель	Январь	Февраль	Март	Апрель	Май	…
Дебиторская задолженность на начало периода, руб.	30 000	31 250	27 500	32 750	36 250	…
Объем продаж, руб. с НДС, в т. ч.:	75 000	65 000	74 000	85 000	73 000	…
с отсрочкой платежа 14 календарных дней (приблизительно 50% продаж оплачивается в следующем месяце)	50 000	45 000	57 000	60 000	55 000	…
ООО «Елочка»	20 000	25 000	27 000	30 000	25 000	…
ООО «Замок»	30 000	20 000	30 000	30 000	30 000	…
с отсрочкой платежа 7 календарных дней (приблизительно 25% продаж оплачивается в следующем месяце)	25 000	20 000	17 000	25 000	18 000	…
ООО «Зебра»	10 000	10 000	10 000	10 000	10 000	…
ООО «Кенгуру»	15 000	10 000	7000	15 000	8000	…
Плановая дебиторская задолженность, руб., в т. ч. длиной:	31 250	27 500	32 750	36 250	32 000	…
14 дней	25 000	22 500	28 500	30 000	27 500	…
7 дней	10 000	5000	4250	6250	4500	…
Поступления с учетом прироста (погашения) дебиторской задолженности (дебиторская задолженность на начало периода + объем продаж – плановая дебиторская задолженность)	73 750	68 750	68 750	81 500	77 250	…

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

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

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

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