Как создать прогноз продаж в Excel (шаг за шагом)
В следующем пошаговом примере показано, как создать прогноз продаж в Excel.
Шаг 1: Создайте данные
Во-первых, давайте создадим набор данных, который показывает общий объем продаж какой-либо компании за 18 месяцев подряд:
Примечание.Чтобы использовать функцию прогноза на следующем шаге, убедитесь, что все ваши даты расположены через равные промежутки времени. Например, даты в наборе данных выше отличаются друг от друга на один месяц.
Шаг 2: Создайте прогноз
Затем выделите ячейки в диапазоне A1: B19 , затем щелкните вкладку « Данные » на верхней ленте и щелкните « Лист прогнозов» в группе « Прогноз »:
В появившемся новом окне щелкните стрелку раскрывающегося списка « Параметры » внизу, чтобы указать даты начала и окончания прогноза , затем нажмите « Создать », чтобы автоматически создать прогноз:
Прогноз будет автоматически сгенерирован на указанные вами будущие даты вместе с пределами доверительного интервала 95%:
Вот как интерпретировать вывод:
- Прогнозируемые продажи на 01.07.2021 составляют 172,518, а 95% доверительный интервал для этого прогноза составляет [159,9, 185,14].
- Прогнозируемый объем продаж на 1 августа 2021 г. составляет 179,12, а 95% доверительный интервал для этого прогноза составляет [ 162,14 , 196,11].
Линейный график с прогнозируемыми значениями также создается автоматически:
Вот как интерпретировать строки:
- Темно-синяя линия представляет исторические значения продаж.
- Темно-оранжевая линия представляет прогнозируемые значения продаж.
- Светло-оранжевые линии представляют собой 95-процентные доверительные интервалы для прогнозируемых значений продаж.
Шаг 3. Измените прогноз (необязательно)
При создании прогноза вы можете выбрать отображение результатов в виде гистограммы вместо линейного графика, щелкнув значок столбцов в правом верхнем углу:
Синие столбцы представляют исторические значения продаж, а оранжевые столбцы представляют прогнозируемые значения продаж.
Обратите внимание, что доверительный интервал для каждого прогноза продаж будет представлен вертикальными планками ошибок вдоль верхней части каждой из оранжевых полос.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
«ПРОГНОЗ» и «ПРОГНОЗ». Функции ЛИНЕЙЛ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование прогноза. Функции LINEARи FORECAST в Microsoft Excel.
Примечание: В Excel 2016 функция ПРОГНОЗ была заменена функцией ПРОГНОЗ. LINEAR в составе новых функций прогнозирования. Синтаксис и использование этих двух функций одинаковы, но старая функция ПРЕДСПРОС в конечном итоге будет отознана. Он по-прежнему доступен для обратной совместимости, но мы думайте использовать новую функцию ПРОГНОЗ. Функция ЛИНЕЙЛ.
Описание
Вычислить или предсказать будущее значение с помощью существующих значений. Будущее значение — это значение y для заданного значения x. Существующие значения — это известные значения x и y, а будущее значение предсказывается с помощью линейной регрессии. Эти функции можно использовать для предсказания будущих продаж, требований к запасам или потребительских тенденций.
Синтаксис
ПРОГНОЗ или ПРОГНОЗ. Аргументы функции ЛИННЕЯ следующую:
Обязательно
«Указывает на»
Точка данных, для которой предсказывается значение.
Известные_значения_y.
Зависимый массив или интервал данных.
Известные_значения_x.
Независимый массив или интервал данных.
Замечания
- Если x не является числом, ТО ЕСТЬ и ПРОГНОЗ. ЛиНЕЙНАЯ возвращает #VALUE! значение ошибки #ЗНАЧ!.
- Если known_y или known_x пустые или имеет больше точек данных, чем в других, ТО ЕСТЬ и ПРОГНОЗ. ЛИНЕЙЛ возвращает значение #N/A.
- Если дисперсия known_x равна нулю, то ЕСТЬ ПРОГНОЗ и ПРОГНОЗ. Linear возвращает #DIV/0! значение ошибки #ЗНАЧ!.
- Уравнение для FORECAST и FORECAST. Это a+bx, где: и где x и y — средние значения выборок СРЗНАЧ(известные_значения_x) и СРЗНАЧ(известные_значения_y).
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Известные значения y
Известные значения x
Быстрое прогнозирование в Microsoft Excel
Особенно приятно, что вводить вручную эти функции и их многочисленные аргументы совершенно не требуется — в Microsoft Excel для этого есть гораздо более удобный инструмент, получивший название Лист прогноза (Forecast Sheet) . Давайте рассмотрим работу с ним на следующем примере.
В качестве исходных исторических данных возьмем с сайта AutoVercity реальную статистику по продажам автомобилей в России за 2019-2020 годы (все марки суммарно):
Представим на минуту, что сейчас конец 2020 года и мы хотим, используя эти данные, сделать помесячный прогноз продаж автомобилей на следующие полтора года. Выделим всю нашу таблицу и на вкладке Данные воспользуемся кнопкой Лист прогноза (Data — Forecast Sheet) .
В открывшемся окне зададим следующие настройки:
- Дату завершения прогноза
- Сезонность — почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. «узор» колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
- Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
- В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений — интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.
После нажатия на кнопку Создать будет сформирован новый лист с прогнозной таблицей и диаграммой, которая по ней построена:
В верхней части таблицы будут идти строки с историческими данными (синяя линия), а в момент их окончания произойдет переключение на три новых столбца с прогнозом функцией ПРЕДСКАЗ.ETS и верхней и нижней границами доверительного интервала, вычисленного с помощью функции ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ.
Ссылки по теме
- Моделирование и оценка вероятности выигрыша в лотерею
- Оптимизация доставки в Excel с помощью Поиска решения (Solver)
- Быстрое добавление новых данных в диаграмму
Прогнозирование продаж в Excel и алгоритм анализа временного ряда
Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.
В данном примере будем использовать линейный тренд для составления прогноза по продажам на бушующие периоды с учетом сезонности.
Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.
Excel – это лучший в мире универсальный аналитический инструмент, который позволяет не только обрабатывать статистические данные, но и составлять прогнозы с высокой точностью. Для того чтобы оценить некоторые возможности Excel в области прогнозирования продаж, разберем практический пример.
Пример прогнозирования продаж в Excel
Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).
Уравнение линейного тренда:
- y — объемы продаж;
- x — номер периода;
- a — точка пересечения с осью y на графике (минимальный порог);
- b — увеличение последующих значений временного ряда.
Допустим у нас имеются следующие статистические данные по продажам за прошлый год.
- Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a . В ячейке D15 Используем функцию ЛИНЕЙН:
- Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).
- Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).
- Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).
- Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.
- Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.
- В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
- Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:
- Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
- На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:
Общая картина составленного прогноза выглядит следующим образом:
График прогноза продаж:
Алгоритм анализа временного ряда и прогнозирования
Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:
- Выделяем трендовую составляющую, используя функцию регрессии.
- Определяем сезонную составляющую в виде коэффициентов.
- Вычисляем прогнозные значения на определенный период.
Нужно понимать, что точный прогноз возможен только при индивидуализации модели прогнозирования. Ведь разные временные ряды имеют разные характеристики.
Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры