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

Что такое скользящая средняя

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

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

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

Итак, на такой график добавляется еще одна линия, о которой мы говорим сегодня. Выглядеть это может примерно так:

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

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

Допустим, у нас есть 10 свечей для однодневного графика. Цены имеют следующие значения:

20 18 16 18 15 21 17 25 23 21

Находим их среднее арифметическое и получаем первую точку:

(20+18+16+18+15+21+17+25+23+21)/10=19,4.

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

(18+16+18+15+21+17+25+23+21+14)/10=18,8. Это будет вторая точка на графике.

Метод средней взвешенной

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

Простая скользящая средняя величина

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

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

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

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

И вот каким получится сглаженный график:

Видите, мы получили некую гладкую линию, которая «менее охотно колеблется» и больше похожа на динамику спроса. Давайте так же построим кривые для периодов сглаживания 2 и 4 мес.

Имеем три кривые:

1. Красная – шаг в 2 месяца;
2. Зеленая – в 3 месяца
3. Фиолетовая – 4 месяца

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

Типы скользящих средних

Простая скользящая средняя

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

SMАзнак равноА1+А2+…+Аппжчере:Азнак равноАвераге ин период ппзнак равноNumber of time periods\begin{aligned} &SMA = \frac{ A_1 + A_2 + \dotso + A_n }{ n } \\ &\textbf{where:} \\ &A = \text{Average in period } n \\ &n = \text{Number of time periods} \\ \end{aligned}​SMA=n

Exponential Moving Average (EMA)

The exponential moving average is a type of moving average that gives more weight to recent prices in an attempt to make it more responsive to new information. To calculate an EMA, you must first compute the simple moving average (SMA) over a particular time period. Next, you must calculate the multiplier for weighting the EMA (referred to as the «smoothing factor»), which typically follows the formula: [2/(selected time period + 1)]. So, for a 20-day moving average, the multiplier would be [2/(20+1)]= 0.0952. Then you use the smoothing factor combined with the previous EMA to arrive at the current value. The EMA thus gives a higher weighting to recent prices, while the SMA assigns equal weighting to all values.

EMAt=[Vt

Бимодальное распределение

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

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

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

Настройка в терминале МТ4 (сглаживание, метод — алгоритм построения)

МТ4 (MetaTrader4) — терминал, предлагающий пользователям выбор не только индикаторов moving average, но и других сервисов.

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

Выбирается также вид цены.

Для этого предлагается несколько параметров:

• основные значения: цена открытия (Open), закрытия (Close), самая высокая (High) и самая низкая (Low);
• средние арифметические показатели: Median Price (от 2 крайних значений), Typical Price (High, Low и цена закрытия), Weighted Close (высчитывается от 4 вариантов).

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

На усмотрение пользователя выбирается стиль (цвет, толщина и т.д.).

Интервалы графика (какой выбрать: дневной, часовой, минутный)

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

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

Если ТФ М5 (5 минут) или М15, эффективным считается период 21. В таком случае реакция на изменение свечей будет быстрой. В сильном тренде сработают линии сопротивления и поддержки.

Для среднего ТФ (Н1, т.е. часовой) выбирают значение 50. Для дневных и недельных (D1 и W1) рекомендуется период 100. 200 и 250 подходят для ТФ, начиная с D1. Данный показатель работает в качестве фильтра скользящего графика, убирая слишком быстрые смены свечей на рынке.

Скорость скользящей — как применяется (пример)

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

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

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

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

Шаг 4

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

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

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

Рассмотрим ВПР в подробностях.

По ходу статьи мы:

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;
2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям;
3. Скорректируем скользящую среднюю сезонностью по группе. Коэффициенты сезонности подтянем с помощью ВПР и разберем функция по частям.

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;

Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе 1 и 2 с помощью Forecast4AC PRO (Как самостоятельно рассчитать коэффициенты сезонности к 3-м месяцам можете прочитать в статье «Расчет прогноза по методу скользящей средней!»)

Для этого установим курсор в начало продаж по товарным группам:

Выберите в настройках «Сезонность» «к 3-м месяцам»:

Нажимаем кнопку «Рассчитать». Получаем в продолжении ряда коэффициенты сезонности к 3-м месяцам:

Копируем сезонность на отдельный лист «к 3-м» получаем табличку, в которой в первом столбце названия товарных групп, а в столбцах со 2-го по 13-й — коэффициенты сезонности для 1 — 12 месяцев:

2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям.

Используем стандартную функцию =срзнач(продажи за 3 последних месяца):

Протянем среднюю на все позиции на 24 месяца вперед:

3. Скорректируем скользящую среднюю сезонностью по группе и разберем ВПР.

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

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

нам важно точно совпадения названия товарной группы)). 1

В искомое значение передаем название товарной группы и фиксируем столбец:

1. В искомое значение передаем название товарной группы и фиксируем столбец:

=СРЗНАЧ(BD3:BF3)*впр($C3 (передаем название товарной группы и фиксируем столбец с помощью значка «$»);’к 3-м’!$A$3:$M$4;данные!BG$2+1;0)

Подробнее о фиксировании ссылок читайте в статье «Как зафиксировать ссылку в Excel».

2. В таблицу передаем таблицу с коэффициентами сезонности для товарных групп и фиксируем таблицу:

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4(передаем таблицу с товарными группами и фиксируем таблицу с помощью значка «$»);данные!BG$2+1;0)

В первом столбце таблицы содержатся искомые значения — названия товарных групп. Фиксируем таблицу, чтобы формула имела такой вид ‘к 3-м’!$A$3:$M$4 и ссылки не поехали, когда мы будем протягивать формулу.

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

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1(передаем номер столбца в котором содержится искомый коэффициент сезонности для соответствующего месяца и фиксируем строку с номерами столбцов месяца  с помощью значка «$»);0)

Т.к. номер столбца в таблице с сезонностью для первого месяца будет вторым, то прибавляем «1»

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1(прибавляем 1, т.к. номер столбца в таблице с сезонностью для первого месяца 2, в первом столбце название товарных групп);0)

4. =СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1;0 (ищем точное соответствие названий товарных групп))

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

=СРЗНАЧ(BD3:BF3)*ВПР($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1;0)

Получаем расчет прогноза по методу скользящей средней к 3-м месяцам по товарным позициям, используя сезонность по товарной группе.

Данный подход может значительно увеличить точность расчета прогноза по товарным позициям внутри группы. Попробуйте рассчитать прогноз по методу скользящей средней к 2-м и 4-м месяцам, используя функцию ВПР и Forecast4AC PRO на текущий год, и сравните прогнозы с фактическими продажами. Выберите модель, которая была максимально близка к факту.

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

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

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

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

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

Сообщений 1 страница 3 из 3

Поделиться12013-02-07 08:40:54

Цель работы: освоение приемов прогнозирования количественных характеристик системы по регрессионной модели путем восстановления значений и экстраполяции. Используемое программное обеспечение: табличный процессор Microsoft Excel.

Задание 1

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

1. Построить следующую электронную таблицу:

2. Подставить в ячейку А2 значение концентрации угарного газа, равного 3 мг/куб. м. В результате получим:

Справочная информация

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

Задание 2

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

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

Полученный график приведен на рисунке.

2. Оценить приблизительно на полученном графике значение функции при значении аргумента, равном 6.

Задание для самостоятельного выполнения на получение регрессионных зависимостей

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

Скользящее среднее в Excel

Здравствуйте. Скользящая средняя (по-английски – moving average) активно используется в трейдинге для определения трендов, точек входа в рынок и выхода из него, и т.д. Однако, этот метод применяют и в оценке бизнес-процессов. Он позволяет отсеять факторы случайности и оценить реальную динамику процессов.

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

В этой статье я опишу три популярнейших способа реализации Moving Average: простое скользящее среднее, экспоненциальное и взвешенное

Простая скользящая средняя величина

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

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

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

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

И вот каким получится сглаженный график:

Видите, мы получили некую гладкую линию, которая «менее охотно колеблется» и больше похожа на динамику спроса. Давайте так же построим кривые для периодов сглаживания 2 и 4 мес.

Имеем три кривые:

1. Красная – шаг в 2 месяца;
2. Зеленая – в 3 месяца
3. Фиолетовая – 4 месяца

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

Экспоненциальное скользящее среднее

Такой метод – это частный случай взвешенной скользящей средней. Его идея состоит в том, чтобы давать различный вес каждому периоду внутри выбранного интервала. Формула имеет вид:

α – весовой коэффициент, характеризующий скорость старения данных прошлых периодов

P_t – значение исследуемой величины в поточном интервале

EMA_t-1 – величина экспоненциального скользящего среднего в предыдущий период времени.

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

Весовой коэффициент рассчитывают так, чтобы статистическая ошибка вычислений была минимальной. Но я на практике использую упрощенный вариант, подсмотренный на сайте allfi.biz:

N – интервал сглаживания

Теперь для нашего примера построим экспоненциальную скользящую с интервалом в 3 месяца:

График получится таким:

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

Взвешенное скользящее среднее

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

n – интервал сглаживания,

P_t—i – значение исследуемой величины в период t-i

Формула достаточно простая, хотя и громоздкая. В числителе – сумма произведений величин продаж на каждом интервале и весового коэффициента для данного периода. Чем старше период, тем ниже коэффициент. В знаменателе – арифметическая прогрессия числа n.

Например, при интервале сглаживания в 3 месяца, формула для третьего периода будет такой:

Вот расчет в Экселе взвешенного скользящего согласно этой формулы:

Диаграмма для периодов 2, 3 и 4 месяца – такая:

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

Красная линия – простая скользящая, зеленая линия – экспоненциальная – фиолетовая – взвешенная.

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

Когда это уместно.

На этом всё, спасибо, что прочли статью и поделились с друзьями. Жду ваших вопросов и комментариев!

Простые скользящие средние против взвешенных скользящих средних

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

Например, если вы используете три периода времени для расчета скользящего среднего, то вес, присвоенный каждому периоду времени, будет равен 0,333. Или, если вы используете четыре периода времени для расчета скользящей средней, тогда вес, присвоенный каждому периоду, будет равен 0,25.

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

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

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

Дополнительные ресурсы

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

Как рассчитать экспоненциальную скользящую среднюю в ExcelКак рассчитать кумулятивное среднее в Excel

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

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

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

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

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

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

• С помощью маркера заполнения
• С помощью функций рабочего листа

Первый способ

Линейное приближение

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

Экспоненциальное приближение

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

Второй способ

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

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

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

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

Формат

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

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

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

Задание 1:

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

Задание 2:

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

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

{ = ТЕНДЕНЦИЯ (B
3:
G
3;
B
2:
G
2;
B
2:
H
2)}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

F пр t = a F ф t-1 + (1-а) F м t

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

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

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

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

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

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

Средний инвестор не получает средний доход

Посещает ли средний студент колледжа колледж среднего размера, растет ли среднее дерево в среднем лесу и получает ли средний инвестор средний доход? Нет.

В одном исследовании средний доход от инвестиции 100 долларов на срок 30 лет составил 760 долларов, или 7% в год. Звучит неплохо. Но эта статистика не показывает, что 9% инвесторов потеряли деньги, а огромному числу инвесторов, 69%, не удалось достигнуть показателя среднего дохода. Так случилось потому, что среднее арифметическое было смещено из-за нескольких человек, заработавших больше среднего.

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

Рассчитать скользящую среднюю

Когда вы будете готовы рассчитать скользящее среднее, нажмите кнопку «Анализ данных» на вкладке «Данные». Выберите «Скользящее среднее» из списка и нажмите «ОК».

Вверху окна скользящей средней введите диапазон ввода в соответствующее поле. Вы также можете щелкнуть внутри поля, а затем перетащить диапазон данных. При желании вы можете установить флажок «Ярлыки в первой строке» и указать интервал.

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

Когда вы закончите настройку скользящей средней, нажмите «ОК», и вы увидите результат. У вас есть значения, начинающиеся в ячейке, которую вы выбрали для вывода. Если вы выбрали вывод диаграммы, у вас также будет удобный линейный график, отображающий данные.

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Заключение

Скользящая средняя – это достаточно простой в применении инструмент. Чаще всего в трейдинге используют SMA. Кривые строятся автоматически, однако получение прибыли при помощи этого метода требует опыта. Для этого начинающему трейдеру рекомендуется попрактиковаться на тестере торгового терминала.

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