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

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

Автоматизируйте решение задачи прогнозирования с помощью системы Sales-Forecast. Смотреть видео

При анализе временных рядов можно выделить две основные цели:

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

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

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

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

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

Для построения прогноза, важно правильно понимать термины, используемые при построении моделей и хорошо ими оперировать. Это и выделение тренда, циклической составляющей ряда, трендциклической, сезонной составляющей и шумовой компоненты; и исследование автокорреляционной и частной автокорреляционной функции для нахождения сезонности; и построение периодограммы, и вычисление сезонного лага (например, при помощи спектрального анализа Фурье).

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

Для наглядного представления применимости моделей в зависимости от интересующей длительности прогноза удобно пользоваться следующей таблицей:

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

STATISTICA — полностью на русском языке!

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

STATISTICA Base

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

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

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

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

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

STATISTICA Advanced

Продукт содержит все возможности STATISTICA Base и расширяет их углубленными методами анализа.

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

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

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

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

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

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

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

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

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

STATISTICA Automated Neural Networks

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

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

STATISTICA Data Miner

Продукт содержит наиболее полный набор методов Data Mining на рынке программного обеспечения, в удобном пользовательском интерфейсе позволяет исследовать большие массивы информации и выявлять в них скрытые правила и закономерности (продукт также содержит все возможности STATISTICA Advanced, STATISTICA Automated Neural Networks).

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

Ниже представлены некоторые примеры применения системы STATISTICA для решения задач прогнозирования в различных областях:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Часть 3

Краткая теория

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

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

Excel проводит линейную экстраполяцию, т.е. рассчитывает наиболее подходящую прямую, которая проходит через серию заданных точек. Задача заключается в нанесении на график набора точек, а затем в подборе линии, по которой можно проследить развитие функции с наименьшей ошибкой. Эта линия называется линией ТРЕНДА. Пользователь может использовать результат вычислений для анализа тенденций и краткосрочного прогнозирования.

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

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

В MS Excel встроены статистические функции рабочего листа.

ТЕНДЕНЦИЯ() — возвращает значения в соответствии с линейной аппроксимацией по методу наименьших квадратов.

РОСТ() — возвращает значения в соответствии с экспоненциальным трендом.

Использование этих функций – еще один способ вычисления регрессионного анализа.

ТЕНДЕНЦИЯ (изв_знач_Y; изв_знач_X; нов_знач_X; константа)

Функция РОСТ возвращает значения в соответствии с экспоненциальным трендом.

Задание к лабораторной работе (часть 3)

Рассчитайте линейный и экспоненциальный прогноз на один год и на последующие три периода (до 2011 года) с помощью маркера заполнения.

Рассчитайте линейный и экспоненциальный прогноз на один год и затем на последующие три периода с помощью функций рабочего листа ТЕНДЕНЦИЯ и РОСТ. Для расчета интервального прогноза после заполнения параметров диалогового окна функции и не выходя из него нажмите комбинацию клавиш Ctrl/ Shift/ Enter.

В строке формул рабочего листа должна появиться формула для расчета элементов массива, например,

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

Постройте графики и линии тренда для первого и второго задания.

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

Режим работы «Скользящее среднее » служит для сглаживания уровней эмпирического динамического ряда на основе метода простой скользящей средней.

Режим работы «Экспоненциальное сглаживание » служит для сглаживания уровней эмпирического динамического ряда на основе метода простого экспоненциального сглаживания.

В диалоговых окнах данных режимов (рисунок 2 и 3) задаются следующие параметры:

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

3. Интервал (только в диалоговом окне Скользящее среднее) – вводится размер окна сглаживания р . По умолчанию р=3 .

Рисунок 2 – Диалоговое окно скользящего среднего

4. Фактор затухания (только в диалоговом окне Экспоненциальное сглаживание) – вводится значение коэффициента экспоненциального сглаживания p . По умолчанию, p=0,3 .

5. Выходной интервал / Новый рабочий лист / Новая рабочая книга – в положении Выходной интервал активизируется поле, в которое необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона. Размер выходного диапазона будет определен автоматически, и на экране появится сообщение в случае возможного наложения выходного диапазона на исходные данные. В положении Новый рабочий лист открывается новый лист, в который начиная с ячейки А1 вставляются результаты анализа. Если необходимо задать имя в поле, расположенное напротив соответствующего положения переключателя. В положении Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 вставляются результаты анализа.

6. Вывод графика – устанавливается в активное состояние для автоматической генерации на рабочем листе графиков фактических и теоретических уровней динамического ряда.

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

Рисунок 3 – Диалоговое окно экспоненциального сглаживания

Данные о реализации (млн. руб.) продуктов сельскохозяйственного производства магазинами потребительской кооперации города приведены в таблице, сформированной на рабочем листе Microsoft Excel (рисунок 4). В указанном периоде (2009 – 2012 гг.) требуется выявить основную тенденцию развития данного экономического процесса.

Рисунок 4 – Исходные данные

Для решения задачи используем режим работы «Скользящее среднее ». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 5, рассчитанные в данном режиме показатели – на рисунке 6, а построенные графики – на рисунке 7.

Рисунок 5 – Заполнение диалогового окна

Рисунок 6 – Результаты анализа

Рисунок 7– Скользящее среднее

В столбце D (рисунок 5) вычисляются значения сглаженных уровней. Например, значение первого сглаженного уровня рассчитывается в ячейке D5 по формуле =СРЗНАЧ(С2:С5), значение второго сглаженного уровня – в ячейке D6 по формуле =СРЗНАЧ(С5:С8) и т.д.

В столбце E вычисляются значения стандартных погрешностей с помощью формулы =КОРЕНЬ (СУММАКВРАЗН (блок фактических значений; блок прогнозных значений) / размер окна сглаживания).

Например, значение в ячейке Е10 рассчитывается по формуле =КОРЕНЬ(СУММКВРАЗН(С7:С10;О7:В10)/4).

Вместе с тем, как отмечалось выше, если размер окна сглаживания является четным числом (р=2m ), то рассчитанное усредненное значение нельзя сопоставить какому-либо определенному моменту времени t, поэтому необходимо применять процедуру центрирования.

Для рассматриваемого примера р=4 , поэтому процедура центрирования необходима. Так, первый сглаженный уровень (265,25) записывается между II и III кв. 2009 г. и т.д. Применяя процедуру центрирования (для этого используем функцию СРЗНАЧ), получаем сглаженные уровни с центрированием. Для III кВ. 2009 г. определяется серединное значение между первым и вторым сглаженными уровнями: (265,25 + 283,25)/2 = 274,25; для IV кв. 2009 г. центрируются второй и третий сглаженные уровни: (283,25 + 292,00)/2 = 287,6 и т.д. Рассчитанные значения представлены в таблице 1. Скорректированный график скользящей средней представлен на рисунке 8.

Читайте также:  Расчет средневзвешенной процентной ставки портфеля в Excel

Таблица 1 – Динамика сглаженных уровней реализации продукции

Год Квартал Размер реализации, млн. руб. Сглаженные уровни с центрированием
274,25
287,63
297,00
307,50
334,63
374,13
402,88
421,00
429,00
430,75
435,38
446,63

Рисунок 8 – Скорректированный график скользящего среднего

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

Рисунок 9 – Заполнение диалогового окна «Экспоненциальное сглаживание»

Рисунок 10 – Результаты анализа

Рисунок 11 – Экспоненциальное сглаживание

В столбце D (рисунок 10) вычисляются значения сглаженных уровней на основе рекуррентных соотношений.

В столбце E рассчитываются значения стандартных погрешностей с помощью формулы =КОРЕНЬ(СУММКВРАЗН (блок фактических значений; блок прогнозных значений) / 3). Как легко заметить (сравните рисунок 8 и 11), при использовании метода простого экспоненциального сглаживания, в отличие от метода простой скользящей средней, сохраняются мелкие волны.

Как проявляется и чем лечить кандидоз горла?

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

Поделим расчет прогноза продаж на 3 части :

  1. Расчет показателей тенденций.
  2. Выявление данных сезонности.
  3. Прогнозирование объемов реализации.

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

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

Определим показатели уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн().

Для этого в ячейки Excel вводим функцию =Линейн(объемы продаж за 5 лет; номера периодов; 1;0).

Выделяем 2 ячейки, в левой – формула =Линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2+Ctrl+Shift+Enter). Excel выведет для нас значения коэффициентов a и b.

Рассчитываем значения тренда

Для этого в уравнение y = bx + a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y – значение линейного тренда для каждого периода.

2. Для расчета коэффициентов сезонности:

  • Выводим отклонения фактических данных от показателей тренда. Для получения результата реальные показатели делим на значения тренда.
  • По всем месяцам выводим средние отклонения за последние 5 лет.
  • Определяем общий индекс сезонности – среднее значение коэффициентов, рассчитанных в 3 пункте.
  • Выводим коэффициенты сезонности. Каждый коэффициент из пункта 3 делим на коэффициент из пункта 4.

3. Рассчитываем формулу прогноза продаж с учетом роста и сезонности:

  • Определяем период, на который необходимо сделать прогноз. Продлеваем номера периодов временного ряда на 2 года и 3 месяца.
  • Рассчитываем значения тренда для будущих периодов. В уравнение y = bx + a подставляем полученные коэффициенты тренда b и а, x – номер периода во временном ряде. Определяем y – значение линейного тренда для каждого будущего периода.
  • Рассчитываем прогноз. Для этого значения линейного тренда умножаем на коэффициенты сезонности.

Прогноз роста реализации с учетом сезонности готов.

Составить свой пример сценария продаж можно, меняя коэффициенты a и b в линейном тренде y = bx + a.

Как узнать, какую модель выбрать?

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

Зачем прогнозировать сбыт?

Многие подумают: «Зачем заниматься гаданием и предсказаниями, которые могут и не сбыться? Ведь бизнес любит точность. Я просто сделаю план, назначу ответственных исполнителей, дам им инструкции и пусть достигают поставленных целей».

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

Прогнозы бывают, как позитивными, так и негативными.

зачем нужны прогнозы

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

Прогнозирование необходимо для:

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

Приведем несколько примеров.

Ситуация №1

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

Ситуация №2

Интернет-магазин забил склад вентиляторами. Из-за того, что лето оказалось нежарким, на складе образовался избыток товара. Это привело к издержкам и потере прибыли.

Ситуация №3

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

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

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

Разновидности методов прогнозирования

Разновидности методов прогнозирования

Процедура будет полезна:

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

3.3. Коррелограмма и идентификация лаговых переменных в уравнениях АR и АRМА.

При практическом построении модели АRМА(/? q) наиболее трудным является определение параметров ряд, т. е. определение оптимального количества лагов. При этом инструментами для нахождения соответствующих лаговых переменных являются автокорреляционная функция и частная автокорреляционная функция.

Программа ЕViеws позволяет довольно быстро найти оптимальные параметры р и q для модели АRМА, для этого используется коррелограмма зависимости между различными лагами временного ряда с ежемесячными курсами американского доллара к российскому рублю.

Алгоритм действий № 5.

Как построить коррелограмму в ЕViеws.

Шаг 1. Выбор основных опций для построения коррелограммы.

С этой целью загрузим в ЕViеws ежемесячные данные по курсу доллара (столбец с данными обозначим как USDоllаr) в соответствии с алгоритмом действий № 2 «Импорт данных и создание рабочего файла в ЕViеws», изложенным в главе 1.

Далее строим коррелограмму, тем более что в ЕViеws сделать это довольно просто. С этой целью в Wоrкfilе (рабочем файле) этой программы открываем файл USDоllаr. После чего в файле USDоllаr нам необходимо выбрать опции VIЕW/СОRRЕLОGRАМ, а в появившемся окне (рис. 3.1) СОRRЕLОGRАМ SРЕСIFIСАТIОN (спецификация коррелограммы) оставить заданные по умолчанию опцию LЕVЕL (исходный уровень) и опцию LАGS ТО INСLUDЕ (максимальная величина лага, включенного в коррелограмму). В результате у нас получится коррелограмма исходных уровней (фактических значений курса доллара) временного ряда USDоllаr с величиной лага от 1 до 36.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Шаг 2. Дополнительные возможности, которые можно использовать для построения коррелограммы.

Если бы мы выбрали, например, опцию 1SТ DIFFЕRЕNСЕ (разница исходных уровней 1-го порядка) или 2ND DIFFЕRЕNСЕ (разница исходных уровней 2-го порядка), тогда была бы построена коррелограмма не исходных уровней временного ряда, а соответственно их первых и вторых разностей. Например, исходный уровень для курса доллара по состоянию на апрель 2010 г. был равен 29,2886 руб. В то время как разница исходных уровней 1-го порядка на эту же дату оказалась равна -0,0752 руб. (т. е. по сравнению с прошлым месяцем курс доллара снизился на 7,52 коп.), а разница исходных уровней 2-го порядка составила 0,5094 руб. (т. е. падение курса доллара по сравнению с предыдущим месяцем уменьшилось на 50,94 коп.).

В полученной коррелограмме (см. табл. 3.1) можно увидеть, как меняются коэффициенты автокорреляции (Аutосоrrеlаtiоn, или АС) и частной автокорреляции (Раrtiаl Соrrеlаtiоn, или РАС) в зависимости от изменения величины лага. Корреляционную зависимость между последовательными уровнями временного ряда называют автокорреляцией уровней ряда. Так, коэффициент автокорреляции уровней первого порядка измеряет корреляционную зависимость между динамикой курса доллара временного ряда t и динамикой курса доллара временного ряда t-1, т. е. в нашем случае измеряется коэффициент автокорреляции при лаге в один месяц. В свою очередь коэффициент автокорреляции уровней второго порядка измеряет зависимость между динамикой курса доллара временного ряда t и динамикой курса доллара временного ряда t- 2, т. е. при лаге в два месяца. И так далее, вплоть до коэффициента автокорреляции уровней 36-го порядка, измеряющего зависимость между динамикой курса доллара временного ряда t и динамикой курса доллара временного ряда t-36, т. е. с лагом в 36 месяцев.

При этом коэффициент автокорреляции уровней к-го (т. е. 1-го, 2-го…., 36-го) порядка находится в ЕViеws по следующей формуле:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Следует заметить, что коэффициент автокорреляции, рассчитываемый в ЕViеws, несколько отличается от обычно вычисляемого коэффициента автокорреляции. Дело в том, что в ЕViеws с целью упрощения вычислений в качестве Y — взята средняя для всей выборки, в то время как обычно для рядов Yt и Yt_к берутся свои средние.

Частной автокорреляционной функцией называют серию частных коэффициентов автокорреляции г, измеряющих связь между текущим лагом временного ряда Yt и предыдущими лагами временного ряда Yt-1, Yt_2…., Yt_к_1 с устранением влияния других промежуточных временных лагов. Вполне естественно, что при нулевом лаге коэффициент частной корреляции = 1, а при лаге к = 1 1 = r1, т. е. коэффициент частной корреляции равен коэффициенту автокорреляции.

Для лага к больше 1 ЕViеws рекурсивно вычисляет частную автокорреляцию по следующей формуле:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Где rк коэффициент автокорреляции для лага к.

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

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Судя по полученной коррелограмме (см. табл. 3.1), уровень автокорреляции (АС) между исходными уровнями временного ряда USDоllаr постоянно убывает начиная с 1-го лага. В свою очередь уровень частной корреляции (РАС) резко снижается уже после 1-го лага, а после 2-го лага осциллирующим образом стремится к нулю (т. е. колеблется вокруг нуля).

В том случае, когда мы хотим построить модель авторегрессионного процесса АR(/?), для определения оптимального числа р мы должны использовать частную автокорреляционную функцию. При этом следует исходить из следующего критерия: оптимальное число р в уравнении авторегрессии должно быть меньше лага, в котором частная автокорреляционная функция начинает стремиться к нулю. Судя по коррелограмме, помещенной в табл. 3.1, коэффициент частной автокорреляции для лага один месяц (или лага 1-го порядка) равен 0,99, а для лага два месяца (или лага 2-го порядка) -0,25. Однако для 3-го порядка коэффициент частной автокорреляции равен -0,014, причем начиная с этого лага величина этого коэффициента колеблется вокруг нулевого уровня. Следовательно, можно сделать вывод, что для прогнозирования курса доллара с помощью модели авторегрессии необходимо использовать модель АR(2), которая примет следующий вид:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

В свою очередь при идентификации модели АRМА(/? q) в качестве лага р выбирается лаг, после которого начинает убывать частная автокорреляционная функция, а в качестве лага q — лаг, после которого начинает убывать автокорреляционная функция. Исходя из табл. 3.1 легко прийти к выводу, что коэффициент автокорреляции начинает убывать уже с лага 2-го порядка. Аналогичный вывод можно сделать и относительно коэффициента частной автокорреляции. Поэтому для прогнозирования курса доллара с помощью модели авторегрессии со скользящими средними в остатках необходимо использовать модель АRМА(1, 1), которая примет следующий вид:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Два последних столбца в табл. 3.1 показывают соответственно Q-статистику Люнга — Бокса (Q-Stаt) и ее значимость (Рrоb.) для каждого лага. Следует иметь в виду, что Q-статистика для лага к является тестовой статистикой при нулевой гипотезе об отсутствии автокорреляции между динамикой курса доллара временного ряда t и динамикой курса доллара временного ряда t- к.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

При этом Q-статистика Люнга — Бокса для лага к-го порядка находится по следующей формуле:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Где Т — число наблюдений;

m — число проверяемых лагов.

Например, для лага 1-го порядка формула (3.12) имеет следующее значение:

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Ехсеl и ЕViеws

Следует иметь в виду, что в том случае, когда в табл. 3.1 значимость (Рrоb.) 0-статистики будет больше 0,05, то нулевую гипотезу об отсутствии автокорреляции между уровнями ряда с лагом А:-го порядка нельзя считать опровергнутой с 95 %-ным уровнем надежности. Если значимость 0-статистики будет больше 0,01, но меньше 0,05, то нулевую гипотезу об отсутствии автокорреляции между уровнями ряда с лагом А:-го порядка нельзя считать опровергнутой с 99 %-ным уровнем надежности. Судя по коррелограмме исходных уровней временного ряда USDоllаr (см. табл. 3.1), значимость Q-статистики для всех 36 лагов равна нулю, поэтому нулевая гипотеза об отсутствии автокорреляции в остатках отклоняется для всех лагов.

Читайте также:  Примеры формул с использованием функций МИН и МИНА в Excel

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

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

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

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

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

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

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

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

Метод скользящей средней

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

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

Часть 3

Краткая теория

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

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

Excel проводит линейную экстраполяцию, т.е. рассчитывает наиболее подходящую прямую, которая проходит через серию заданных точек. Задача заключается в нанесении на график набора точек, а затем в подборе линии, по которой можно проследить развитие функции с наименьшей ошибкой. Эта линия называется линией ТРЕНДА. Пользователь может использовать результат вычислений для анализа тенденций и краткосрочного прогнозирования.

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

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

В MS Excel встроены статистические функции рабочего листа.

ТЕНДЕНЦИЯ() — возвращает значения в соответствии с линейной аппроксимацией по методу наименьших квадратов.

РОСТ() — возвращает значения в соответствии с экспоненциальным трендом.

Использование этих функций – еще один способ вычисления регрессионного анализа.

ТЕНДЕНЦИЯ (изв_знач_Y; изв_знач_X; нов_знач_X; константа)

Функция РОСТ возвращает значения в соответствии с экспоненциальным трендом.

Задание к лабораторной работе (часть 3)

Рассчитайте линейный и экспоненциальный прогноз на один год и на последующие три периода (до 2011 года) с помощью маркера заполнения.

Рассчитайте линейный и экспоненциальный прогноз на один год и затем на последующие три периода с помощью функций рабочего листа ТЕНДЕНЦИЯ и РОСТ. Для расчета интервального прогноза после заполнения параметров диалогового окна функции и не выходя из него нажмите комбинацию клавиш Ctrl/ Shift/ Enter.

В строке формул рабочего листа должна появиться формула для расчета элементов массива, например,

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

Постройте графики и линии тренда для первого и второго задания.

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

Инструмент «Скользящее среднее» можно вызвать в диалоговом окне команды «Анализ данных» из меню «Сервис».

С помощью инструмента скользящей средней я составляю прогноз экономических показателей таблицы 1.1(табл. 3.1).

Таблица 3 .1 ― Оценка тенденции поведения показателей исследуемого динамического ряда методом скользящего среднего

На основании данных таблицы строю график скользящей средней.

Рисунок 3.1 – Скользящее среднее

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

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

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

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

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

функция прогноза Excel прогнозирует исторические данные

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

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

Мы рассмотрим каждый вариант в отдельности.

функция прогноза Excel прогнозирует исторические данные

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

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

функция прогноза Excel прогнозирует исторические данные

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

Это означает, что Excel на 95 процентов уверен, что прогнозируемое значение будет находиться между этими двумя строками.

функция прогноза Excel прогнозирует исторические данные

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

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

функция прогноза Excel прогнозирует исторические данные

И вот как это выглядит после того, как я изменил сезонность на 4:

функция прогноза Excel прогнозирует исторические данные

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

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

функция прогноза Excel прогнозирует исторические данные

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

функция прогноза Excel прогнозирует исторические данные

Если вы не знаете, что означают эти статистические данные, не беспокойтесь о них.

функция прогноза Excel прогнозирует исторические данные

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

функция прогноза Excel прогнозирует исторические данные

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

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

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

функция прогноза Excel прогнозирует исторические данные

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

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

Прогноз плана продаж

Без прогноза какую-либо предпринимательскую деятельность – в т. ч. и торговлю – невозможно начать. К примеру, вы хотите открыть веломагазин – после обустройства торговой площадки, её заключительной подготовки и завоза велотехники и велоаксессуаров вы планируете как можно скорее распродать. Предположим, завезено в общей сложности 500 велосипедов разных марок и моделей для взрослых – а вам нужно продать их за неделю, велосезон ведь уже начался. Какова будет ваша ценовая политика? Что вы противопоставите вашим конкуретам – по качеству, ценникам, возможностям купить в рассрочку и т. д.? Какие запчасти к этим велосипедам у вас будут продаваться? Как пойдёт ваш план по продажам в ближайший месяц, квартал, за весь велосезон с апреля по октябрь включительно? На эти вопросы вам и даст ответ ваше умение прогнозировать рост продаж.

Businessman using telescope and stock market graph

Регрессионный анализ с помощью диаграмм

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

Постройте диаграмму на основе данных, содержащихся в ячейках А2:А11 (рисунок 4). Щелкнув мышью на диаграмме, вы получите возможность ее редактировать. Щелкните на ряде нужных данных для его выбора.

После этого выполните следующие шаги.

  1. Выберите команду ДиаграммаДобавить линию тренда.
  2. Выберите тип линии тренда Линейная.
  3. Щелкните на корешке вкладки Параметры.
  4. В поле ПрогнозВперед_на введите количество желаемых периодов, на протяжении которых линия тренда будет проложена вперед.
  5. При желании, можете установить флажок опции Показывать уравнение на диаграмме. В результате уравнение для прогноза разместится на графике в виде текста. Excel может расположить уравнение таким образом, что оно пе­рекроет некоторые данные графика или линии тренда (либо, частично, само уравнение). В этом случае выделите уравнение, щелкнув на нем мышью, а затем перетащите его в другое, более удобное место.

основанные на регрессии, непосредственно на диаграмме

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

Ссылка на основную публикацию