Прогнозирование в excel: 6 проверенных способов

Функция ПРЕДСКАЗ.ETS — Служба поддержки Office

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

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

Синтаксис

ПРЕДСКАЗ.ETS(целевая_дата;значения;временная_шкала;;;)

Аргументы функции ПРЕДСКАЗ.ETS описаны ниже.

Целевая_дата (обязательный). Точка данных, для которой предсказывается значение. Целевая дата может быть задана в формате даты/времени или числа.
Если она хронологически расположена до конца ретроспективной временной шкалы, функция ПРЕДСКАЗ.ETS возвращает ошибку #ЧИСЛО!.
Значения (обязательный). Значения представляют собой ретроспективные данные, на основе которых прогнозируются последующие значения.
Временная_шкала (обязательный). Независимый массив или интервал числовых данных. Даты во временной шкале должны отстоять одна от другой на фиксированный интервал и не должны быть нулевыми.
Сортировать массив значений временной не обязательно, так как ПРЕДСКАЗ.ETS автоматически отсортирует ее для расчетов.
Если в заданной временной шкале отсутствует фиксированный интервал между точками, ПРЕДСКАЗ.ETS возвращает ошибку #ЧИСЛО!.
Если временная шкала содержит дублирующиеся значения, ПРЕДСКАЗ.ETS возвращает ошибку #ЗНАЧЕН!.
Если размеры временной шкалы и массива значений не совпадают, ПРЕДСКАЗ.ETS возвращает ошибку #Н/Д.
Сезонность (необязательный). Числовое значение.
Для значения по умолчанию 1 Excel автоматически определяет для прогноза сезонность и использует положительные целые числа в качестве длины сезонного шаблона.
Значение 0 предписывает не использовать фактор сезонности, в результате чего прогноз будет линейным.
Если для этого параметра задано положительное целое число, алгоритм использует его в качестве длины шаблона сезонности.
Для любого другого значения ПРЕДСКАЗ.ETS возвращает ошибку #ЧИСЛО!.

Максимальное значение параметра сезонности — 8760 (количество часов в году). Для любого значения больше этого предела возвращается ошибка #ЧИСЛО!.
Заполнение_данных (необязательный). Хотя временная шкала должна быть задана с фиксированным интервалом между точками данных, функция ПРЕДСКАЗ.ETS принимает данные, в которых отсутствует до 30 % значений, и выполняет автоматическую коррекцию.
Если для этого параметра задано значение 0, алгоритм подставляет вместо отсутствующих точек нули.
Если задано значение 1 (вариант по умолчанию), функция определяет отсутствующие значения как среднее между соседними точками.
Агрегирование (необязательный). Хотя временная шкала должна быть задана с фиксированным интервалом между точками данных, функция ПРЕДСКАЗ.ETS выполняет агрегирование точек с одинаковой меткой времени. Параметр агрегирования — это числовое значение, определяющее способ агрегирования нескольких значений с одинаковой меткой времени. Для значения по умолчанию 0 используется метод СРЗНАЧ; также доступны варианты СУММ, СЧЁТ, СЧЁТЗ, МИН, МАКС и МЕДИАНА.

Что такое прогноз продаж?

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

Добавление линии тренда на график

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

Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена.
Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда».
Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации.
На графике появляется косая линия.

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

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

Внимание. Линию тренда нельзя добавить следующим типам графиков и диаграмм:

лепестковый;
круговой;
поверхностный;
кольцевой;
объемный;
с накоплением.

Бюджет компании пример Excel

Бюджетирование в Excel заключается в создании форм бюджетов в формате Excel и связывании этих форм при помощи формул и макросов. Формы бюджетов, в том числе бюджета доходов и расходов, бюджета движения денежных средств могут быть различными, с укрупненными статьями или более подробные, разбиты на длительные периоды (например, годовой бюджет по кварталам) или на более короткие периоды (например, месячный бюджет по неделям) – в зависимости от потребности финансового менеджмента в компании.

Ниже приведен Бюджет доходов и расходов (пример составления в эксель) и пример Бюджета движения денежных средств.

Рисунок 1. Бюджет доходов и расходов предприятия образец Excel.

Рисунок 2. Бюджет движения денежных средств пример в Excel.

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

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

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

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

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

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

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

y = 4856*x + 105104

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

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

Способ расчета значений линейного тренда в Excel с помощью графика

Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У – объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

Время – значение по оси Х;
Значение “a” и “b” уравнения линейного тренда y(x)=a+bx;

Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36. Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

27-го y=135134*27+4594044=8242686

Способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

Подставляем в формулу

известные значения y – это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
известные значения x – это номера периодов x для известных значений объёмов продаж y;
новые значения x – это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
константа – ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;

Для того чтобы рассчитать значения тренда для всего временного диапазона, в “новые значения x” вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.

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

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

Прогнозирование стадии возможности: Эта стратегия использует коэффициент закрытия компании или вероятность закрытия сделок на каждом уровне контракта купли-продажи. Формула «прогноз продаж = общая стоимость текущих сделок в цикле продаж x скорость закрытия».
Интуитивное прогнозирование: Эта стратегия подходит для небольших организаций или стартапов, которым не хватает исторических данных, поскольку она фокусируется на наблюдениях торгового представителя. Просто спросите своих агентов по продажам, когда они ожидают закрытия своих соглашений и какую прибыль они ожидают получить.
Историческое прогнозирование: Эта стратегия использует исторические данные (результаты предыдущих циклов продаж) и скорость продаж (скорость, с которой продажи увеличиваются с течением времени). Формула: pпродажи за предыдущий месяц x скорость = дополнительные продажи; а затем: дополнительные продажи + ставка предыдущего месяца = прогнозируемые продажи на следующий месяц..
Многофакторный анализ: Эта стратегия учитывает ряд различных элементов, таких как вероятность закрытия сделок, циклы продаж, выводы торговых представителей и исторические данные. Это включает в себя сложную математику, но CRM, прогнозирование или программное обеспечение для продаж могут упростить ее.
Подход «снизу вверх: Этот подход дает обоснованную оценку того, сколько клиентов может охватить компания. Формула: прогноз продаж = расчетное количество клиентов x средняя стоимость покупок клиентов.
Новый подход к бизнесу: Этот метод предназначен для новых предприятий и небольших стартапов, которым не хватает исторических данных. Он использует прогнозы продаж аналогичной компании, продающей сопоставимые продукты.

Формула коэффициента сезонности

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

Классический метод по средним продажам

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

Коэффициент сезонности каждого месяца = продажи в штуках этого месяца/ продажи за год.

Коэффициент сезонности = среднее значение из коэффициентов по конкретным месяцам.

Рассмотрим на примере.

В строке 21 посчитаны средние продажи за год. Показатель февраля – 8307. Затем мы посчитали средние продажи за второй год. Цифра за февраль – 14243, и так далее для каждого года. После реальные продажи за каждый месяц (строка 20) поделили на средние – за год (строка 21).

7322/8307 = 0,8814 – это коэффициент сезонности для февраля.

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

Особенности использования функции ПРЕДСКАЗ в Excel

Функция имеет следующую синтаксическую запись:

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

Описание аргументов:

x – обязательный для заполнения аргумент, характеризующий одно или несколько новых значений независимой переменной, для которых требуется предсказать значения y (зависимой переменной). Может принимать числовое значение, массив чисел, ссылку на одну ячейку или диапазон;
известные_значения_y – обязательный аргумент, характеризующий уже известные числовые значения зависимой переменной y. Может быть указан в виде массива чисел или ссылки на диапазон ячеек с числами;
известные_значения_x – обязательный аргумент, который характеризует уже известные значения независимой переменной x, для которой определены значения зависимой переменной y.

Примечания:

Второй и третий аргументы рассматриваемой функции должны принимать ссылки на непустые диапазоны ячеек или такие диапазоны, в которых число ячеек совпадает. Иначе функция ПРЕДСКАЗ вернет код ошибки #Н/Д.
Если одна или несколько ячеек из диапазона, ссылка на который передана в качестве аргумента x, содержит нечисловые данные или текстовую строку, которая не может быть преобразована в число, результатом выполнения функции ПРЕДСКАЗ для данных значений x будет код ошибки #ЗНАЧ!.
Статистическая дисперсия величин (можно рассчитать с помощью формул ДИСП.Г, ДИСП.В и др.), передаваемых в качестве аргумента известные_значения_x, не должна равняться 0 (нулю), иначе функция ПРЕДСКАЗ вернет код ошибки #ДЕЛ/0!.
Рассматриваемая функция игнорирует ячейки с нечисловыми данными, содержащиеся в диапазонах, которые переданы в качестве второго и третьего аргументов.
Функция ПРЕДСКАЗ была заменена функцией ПРЕДСКАЗ.ЛИНЕЙН в Excel версии 2016, но была оставлена для обеспечения совместимости с Excel 2013 и более старыми версиями.
Для предсказания только одного будущего значения на основании известного значения независимой переменной функция ПРЕДСКАЗ используется как обычная формула. Если требуется предсказать сразу несколько значений, в качестве первого аргумента следует передать массив или ссылку на диапазон ячеек со значениями независимой переменной, а функцию ПРЕДСКАЗ использовать в качестве формулы массива.

Шаг 5

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

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

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

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

Модель может считаться адекватной, если:

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

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

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

Полезные ссылки:

Ссылка на пример Google Sheets
Построение функции тренда в Excel. Быстрый прогноз без учета сезонности
Бывшев В.А. Эконометрика

Об авторе
Свежие записи

Екатерина ШиповаМагистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

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

Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.

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

Как использовать сезонность в маркетинговой кампании

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

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

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

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

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

Например, если выйдет закон о полном запрете авиапассажирам провоза внешних аккумуляторов ёмкостью выше 5 000 mAh в ручной клади, продажи ваших скромных пауэрбанков на 2 000 mAh могут подскочить при запуске грамотной маркетинговой кампании.

О таких банальностях, как цветы и торты на 8 марта и 1 сентября не стоит и говорить. В эти праздники не готовит специальные предложения только тот, у кого бизнес абсолютно не может быть ассоциирован с ними — например, продажи бурильного оборудования.

Постройте коммуникации

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

Выделите бюджет

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

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

Бонусы и подарки для клиентов;
Расходы на мероприятия;
Расходы на зарплату сезонных работников;
Транспортные расходы, если вы рассылаете подарки.

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

Что такое прогноз продаж

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

— На основе показателей продаж фирмы за предыдущие периоды;

— На основе статистических данных по другим аналогичным предприятиям в отрасли;

— На основе экспертных оценок (как правило, для инновационных продуктов, когда нет данных за предыдущие периоды и нет аналогичных предприятий);

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

Формулы, используемые при прогнозировании

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

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

столбец статистических значений времени (ваш ряд данных, содержащий значения времени);

столбец статистических значений (ряд данных, содержащий соответствующие значения);

столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);

два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только при проверке доверительный интервал в разделе Параметры.

Планирование продаж и денежных потоков предприятия

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

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

Цель оценки денежных потоков – оценка потенциала компании для развития инноваций и реализации инвестиционных проектов.

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

где: CF_i (Cash Flow) – денежный поток.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

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

Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0). Выделяем 2 ячейки, в левой — формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.
Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).

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

Причины сезонности

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

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

Устоявшиеся традиции и привычки тоже вносят большой вклад в сезонность. Если до Нового года наблюдается всплеск потребительской активности, то после 1 января наступает «мертвый сезон». Это связано с новогодними каникулами, которые утверждены на законодательном уровне. Большинство людей сидят и отдыхают дома после покупок и праздника, а значит меньше ходят в магазины. Поэтому компании часто сокращают маркетинговый бюджет на январь, потому что сезонный спрос падает.

Методы планирования продаж и денежных потоков

Существует множество различных методов прогнозирования объема продаж (денежных потоков): модель скользящего среднего (MA, Moving Average), модель авторегрессии (AR, AutoRegressive), модель авторегрессии скользящего среднего (Autoregressive Moving Average model, ARMA), модель Бокса-Дженкинса и др. В данной статье мы более подробно разберем прогнозирование с помощью модели авторегрессии.

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

Как составить план продаж на месяц: пример

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

Реализация за прошлый год:

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

Так как предприятие развивающееся, для прогнозирования продаж можно использовать линейный тренд. Математическое уравнение: y = b*x + a. Где

y – продажи;
х – порядковый номер периода;
а – минимальная граница;
b – повышение каждого следующего значения во временном ряду.

Значение линейного тренда в Excel рассчитывается с помощью функции ЛИНЕЙН. Составим табличку для определения коэффициентов уравнения (y и х нам уже известны).

Для быстрого вызова функции нажимаем F2, а потом сочетание клавиш Ctrl + Shift + Ins. А комбинацией SHIFT+F3 открываем окно с аргументами функции ЛИНЕЙН (курсор стоит в ячейке G 2, под аргументом b). Заполняем:

Выделяем сразу 2 ячейки: G2 и H2 (значения аргументов b и а). Активной должна быть ячейка с формулой. Нажимаем F2, а потом сочетание клавиш Ctrl + Shift + Enter. Получаем значения для неизвестных коэффициентов уравнения:

Найдем для каждого периода анализируемого временного интервала значение y. Подставим рассчитанные коэффициенты в уравнение.

Следующий этап – расчет отклонений значений фактических продаж от значений линейного тренда:

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

Рассчитаем индекс сезонности для каждого периода. Формула расчета: объем продаж за период / средний объем.

С помощью функции СРЗНАЧ найдем общий индекс сезонности:

Спрогнозируем уровень продаж на будущий месяц. Учтем рост объема реализации и сезонность.

Сначала найдем значение тренда для будущего периода. Для этого в столбце с номерами периодов добавим число 13 – новый месяц. Продлим формулу в столбце «Значение тренда» на одну ячейку вниз:

Умножим значение тренда на индекс сезонности соответствующего месяца (в примере – «января»). Получим рассчитанный объем реализации товара в новом периоде:

По такому же принципу можно спрогнозировать реализацию на 2, 3, 4 и последующие месяцы.

График прогноза с линией тренда:

При построении финансового плана продаж используется понятие «сечения». Это детализации плана в определенном «разрезе»: по времени, по каналам реализации, по покупателям (клиентам), по товарным группам, по менеджерам. Такая детализация позволяет проверить реалистичность прогноза, а в дальнейшем – проверить выполнение.

Составляющие прогноза

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

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

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

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

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

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