Построение прогноза в excel

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

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

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

Уровни временного ряда (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.

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

Шаг 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%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении 🙂

5 способов расчета значений линейного тренда в MS Excel

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

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

1. 1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
   2. Расчет сезонности;
   3. Расчет прогноза;

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

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

Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

Временной ряд продажи по месяцам (см. вложенный файл).

В этом временном ряду у нас есть 2 переменных:

Уравнение линейного тренда y(x)=a+bx, где

y — это объёмы продаж

x — номер периода (порядковый номер месяца)

a – точка пересечения с осью y на графике (минимальный уровень);

b – это значение, на которое увеличивается следующее значение временного ряда;

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

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

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

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

1. Время — значение по оси Х;
2. Значение «a» и «b» уравнения линейного тренда y(x)=a+bx;

Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

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

2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН

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

=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

Для расчета коэффициентов в формулу вводим

известные значения y (объёмы продаж за периоды),

известные значения x (номера периодов),

вместо константы ставим 1,

вместо статистики 0,

Получаем 135135 — значение (b) линейного тренда y=a+bx;

Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо

1. установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке;
2. нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

Получаем 135135, 4594044 — значение (b) и (a) линейного тренда y=a+bx;

2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов — x, для которых хотим рассчитать значения линейного тренда.

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

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

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

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

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

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

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

4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ

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

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

Вместо X поставляем номер периода, для которого рассчитываем значение тренда.

Вместо «известные значения y» — объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

«известные значения x» — это номера периодов для каждого выделенного объёма продаж.

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

5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO

2. Заходим в меню программы и нажимаем «Start_Forecast». Значения линейного тренда рассчитаны.

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

В следующих статье «Как самостоятельно сделать прогноз продаж с учетом роста и сезонности» мы:

О том, что еще важно знать о линейном тренде, вы можете узнать в статье «Что важно знать о линейном тренде».

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

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

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

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

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

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

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Создание прогноза в Excel для Windows

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

Создание прогноза

На листе введите два ряда данных, которые соответствуют друг другу:

ряд значений даты или времени для временной шкалы;

ряд соответствующих значений показателя.

Эти значения будут предсказаны для дат в будущем.

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

Выделите оба ряда данных.

Совет: Если выделить ячейку в одном из рядов, Excel автоматически выделит остальные данные.

На вкладке Данные в группе Прогноз нажмите кнопку Лист прогноза.

В окне Создание листа прогноза выберите график или гистограмму для визуального представления прогноза.

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

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

Этот лист будет находиться слева от листа, на котором вы ввели ряды данных (то есть перед ним).

Настройка прогноза

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

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

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

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

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

Установите или снимите флажок Доверительный интервал, чтобы показать или скрыть его. Доверительный интервал — это диапазон вокруг каждого предсказанного значения, в который в соответствии с прогнозом (при нормальном распределении) предположительно должны попасть 95 % точек, относящихся к будущему. Доверительный интервал помогает определить точность прогноза. Чем он меньше, тем выше достоверность прогноза для данной точки. Доверительный интервал по умолчанию определяется для 95 % точек, но это значение можно изменить с помощью стрелок вверх или вниз.

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

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

Диапазон временной шкалы

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

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

Заполнить отсутствующие точки с помощью

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

Объединение дубликатов с помощью

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

Включить статистические данные прогноза

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

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

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

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

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

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

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

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

Скачайте пример книги.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

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

Как сделать прогноз в Эксел

Здравствуйте, уважаемые читатели блога http://nocleep.ru/. Сегодня хочу поделиться информацией, как с помощью программы Excel можно легко и быстро обработать статистические данные и сделать прогноз.

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

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

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

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

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

1 шаг. Исходные данные. Где взять?

Нам нужны исходные данные. Где их взять? На сайтах статистики, конечно. Я для своих статей беру данные на сайте Федеральной Службы Государственной Статистики. Для этого необходимо покопаться в разделе «Официальная статистика» или «Базы данных».

2 шаг. Исходные данные. Как скачать?

Для расчета прогноза потребуются данные за конкретный период. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года или за несколько лет. Для своего примера я скачаю данные «Число персональных компьютеров в организациях» с 2003 по 2014 годы. И составлю прогноз на 5 лет, т.е. до 2019 года. Для этого нужно:

1) Зайти на сайт Федеральной Службы Государственной Статистики, «Официальная статистика», далее захожу где «Наука, инновации и информационное общество».

2) Выбираю «Информационное общество», затем «Информационные и коммуникационные технологии», скачиваю таблицу с данными Excel.

3 шаг. Подготовка данных для расчета прогноза на 5 лет

Итак, данные у нас есть. Что с ними необходимо сделать?

Во-первых, мне для простого прогноза не нужны все данные таблицы, поэтому, я удаляю лишние строки, оставив только необходимую информацию для прогноза. А именно: года, и «Число персональных компьютеров в обследованных организациях — всего, тыс. шт.». Вот что должно остаться:

Во-вторых, данные для прогноза необходимо транспонировать, т.е. выстроить их в вертикальную таблицу. Для этого необходимо:
1) Выделить всю таблицу.
2) Буфер обмена/«Копировать»
3) Выделить новую ячейку, куда будете вставлять транспонированную таблицу.
4) Буфер обмена/«Вставить/Специальная вставка/Транспонировать»

В итоге получаем вертикальную таблицу:

В-третьих, для того, чтобы точки будущего прогноза встали на одну линию, необходимо переименовать годы в цифры: 1, 2, 3, 4, 5 и т.д.

Готово! Теперь можно приступать к постройке графика.

4 шаг. Постройка графика

Построим точечную диаграмму с линиями. Для этого необходимо:
1) Выделить вертикальную таблицу (оба столбца с шапкой).
2) Дальше идем: Вставка/Диаграммы/Точечная/Точечная с гладкими отрезками и маркерами. Получаем вот такой график:

3) Для удобства уберем с графика все линии. Для этого, выделяем сначала горизонтальные линии/Delete (на клавиатуре), аналогично выделяем вертикальные линии/ Delete (на клавиатуре). Вот так:

4) Добавим легенду. Для этого нужно щелкнуть по таблице, в правом верхнем углу появится крестик. Щелкнув по нему, выбираем — легенда. В результате появится надпись:

5 шаг. Строим прогноз на 5 лет

1) Если мы планируем построить прогноз на 5 лет, то соответственно, нужно продлить столбец с цифрами 1, 2, 3, 4, 5 и т.д. на 5 ячеек. Помните, что они у нас заменят года? Я их для вас выделила желтым цветом. Получаем продленную таблицу следующего вида:

2) Добавим линию тренда. Для этого необходимо: щелкнуть правой кнопкой мыши по знакомому нам крестику в правом верхнем углу таблицы/выбрать «Линия тренда». На графике появится линейная функция.

3) Щелкнув по линии тренда мышкой два раза, открывается окно «Формат линии тренда».

Что мы можем с этим делать?

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

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

В-третьих, рекомендую полученное уравнение и коэффициент тесноты связи также покрасить в синий цвет, что и линия тренда. Так Вы никогда не перепутаете, какое уравнение к какой линии тренда относится. Для этого: щелкаем по уравнению, чтобы выделить прямоугольник для закрашивания, справа переходим во вкладку, где ведерко с краской. Выбираем: «Заливка» — «Сплошная заливка» — «Цвет» — синий. Готово!

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

5) Для постановки точек прогноза выбираем уравнение, где коэффициент тесноты связи R² — наибольшее число. В моем случае это «Полиномиальный тренд» с уравнением и теснотой связи:

6) Теперь внимательно. Выбранное уравнение нужно набрать языком Excel в ячейке начала прогноза.

Как должно выглядеть уравнение на языке Excel?

Например, мое уравнение:

y = -0,1837×2 + 2,9289x + 83,664

на языке ексел будет выглядеть так:

=-0,1837*A18^2+2,9289*A18+83,664

Что я сделала?
— убрала «y», потому что в Excel все формулы начинаются со знака «=»,
— ввела знак умножения «*» — в Excel он обозначается звездочкой,
— подставила вместо «х» — число года начала прогноза, в моем случае это «А18»,
— ввела знак, обозначающий степень «^».

В какую ячейку вводить уравнение?

Я ввожу в ячейку «В18», и получаю первую цифру прогноза на 2015 год (вы же помните, что все года мы заменили порядковыми цифрами?).

7) Аналогичным способом вводим уравнение на все 5 лет прогноза, не забывая заменять адрес ячейки года. У меня это выглядит вот так (уравнение справа поставила для вас).

Теперь добавим полученные точки прогноза на график. Для этого нужно добавить ряды — щелкнуть по области графика правой кнопкой мыши/Выбрать данные/Добавить. Дать название ряда «Прогноз на 2015 год». Ввести значения х и y: «х» — обозначение года 2015, в моем случае он заменен цифрой «13» (ячейка «А18»), а «y» — полученное число прогноза на 2015 год в ячейке «В18».

9) Аналогичным образом вводим остальные четыре точки.

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

Итог

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

Алгоритм прогнозирования объёма продаж в MS Excel

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

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

Аддитивную модель прогнозирования можно представить в виде формулы:

где: F – прогнозируемое значение; Т – тренд; S – сезонная компонента; Е – ошибка прогноза.

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

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

Рис. 1. Аддитивная и мультипликативные модели прогнозирования.

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

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

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

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

3.Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели.

4.Строится модель прогнозирования:

где:
F– прогнозируемое значение;
Т– тренд;
S – сезонная компонента;
Е — ошибка модели.

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

где:
F_{пр t} — прогнозное значение объёма продаж;
F_{ф t-}1 – фактическое значение объёма продаж в предыдущем году;
F_{м t} — значение модели;
а – константа сглаживания

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

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

Применение алгоритма рассмотрим на следующем примере.

Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.

Таблица 1.
Фактические объёмы реализации продукции