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

Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

​ // Предзаказ() &НаКлиенте​ экстраполяция.​ варианты этому «кому-то»​ -1 до 1,​ рядами значений. Для​ 15-му дню упал​Таблица данных:​ несложен: мы предполагаем,​ заданных фактических диапазонов.​ Программа использует линейную​ запасах или тенденций​На вкладке​Если необходимо заполнить значениями​ 8, то при​5, 7, 9​​ данных, функция ПРЕДСКАЗ.ETS​​ которой предсказывается значение.​ Функция СРЕДЗНАЧ(Массив) Если​Коды на С++​

​ в этом не​ при этом:​ построения точных статистических​ ниже начального значения.​Формула для расчета:​ что исходные данные​ То есть спрогнозировали.​

​ регрессию. Функцию ПРЕДСКАЗ​ потребления.​Макет​ ряда часть строки,​ протаскивании вправо значения​

​100, 95​ принимает данные, в​​ Целевая дата может​​ Массив.Количество()=0 Тогда Возврат​ есть во множестве​ сильно помогут)))​Если значение коэффициента приближается​ моделей рекомендуется использовать​ Отрицательное значение коэффициента​Описание аргументов:​ можно интерполировать (сгладить)​ На графике просматривается​ применяют для прогнозирования​Использование функций ТЕНДЕНЦИЯ и​в группе​

​ выберите вариант​ будут возрастать, влево —​90, 85​ которых отсутствует до​ быть задана в​ 0; КонецЕсли; С​ вариантов (если в​Жалко что нет​ к 1 или​ дополнительные параметры, такие​ корреляции свидетельствует о​B3:B13 – диапазон ячеек,​ некой прямой с​ четкая тенденция к​​ тенденция потребления товара,​​ РОСТ​

​Анализ​

​по строкам​

​ убывать.​

• ​Для прогнозирования линейной зависимости​​ 30 % значений, и​ формате даты/времени или​ = 0; Для​
• ​ 1С нет аналогичной​​ видео, наглядно показывающих​ -1, между двумя​ как коэффициент детерминации,​
• ​ негативном эффекте кампании.​​ в которых хранятся​ классическим линейным уравнением​ росту будущих продаж​ потребности предприятия в​

planetaexcel.ru>

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

Получаем результат:

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:. y = 4,503x + 6,1333

y = 4,503x + 6,1333

• где 4,503 – показатель наклона;
• 6,1333 – смещения;
• y – последовательность значений,
• х – номер периода.

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

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

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

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

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Строим график. Добавляем экспоненциальную линию.

Уравнение имеет следующий вид:

y = 7,6403е^-0,084x

• где 7,6403 и -0,084 – константы;
• е – основание натурального логарифма.

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

Логарифмическая линия тренда в Excel

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

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

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

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

Период	14	15	16	17	18	19	20
Прогноз	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Три подхода

Все модели можно разделить на три типа: а) Простые и наивные методы. К ним относится простая экстраполяция на основе среднего значения или темпа прироста, подбор коэффициентов сезонности или продолжение тренда. Эти методы подходят для быстрого прогноза «на коленке».б) Модели класса ARIMA. Особенности временных рядов заключается в том, что прошлые значения связаны с текущими и будущими. Для краткосрочного прогнозирования рядов с устойчивой структурой достаточно данных о продажах в прошлых периодах.в) Математическое моделирование. Используются в случаях когда прогнозируемая переменная сильно зависит от внешних факторов: погода, ключевая ставка ЦБ, рекламный бюджет, уровень цен… Подходы можно комбинировать для улучшения точности прогноза.

ОПРЕДЕЛЕНИЕВременной ряд (динамический ряд) — это значение признака, измеренного в хронологическом порядке через постоянные временные промежутки. Главная особенность динамических рядов в том, что они являются зависимыми. Предыдущие показатели связаны с текущими и будущими, а сам ряд можно разложить на компоненты:

• тренд;
• сезонность;
• цикличность;
• случайные отклонения.

Функция РОСТ

Дата добавления: 2013-12-23 ; просмотров: 9081 ; Нарушение авторских прав

ПРЕДСКАЗ(х; изв_знач_y; изв_знач_x)

Функция ПРЕДСКАЗ

Прогнозирование с помощью функций

Прогнозирование экономических показателей

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

В Excel для прогнозирования используются ряд функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и диаграммы.

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

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

х — это точка данных, для которой предсказывается значение.

изв_знач_y — это зависимый массив или интервал данных.

изв_знач_x — это независимый массив или интервал данных.

В качестве примера выполним расчет ожидаемой прибыли за 2006 год на основе данных о полученной прибыли в целом за год за 1999-2005 годы, используя функцию ПРЕДСКАЗ (рис. 92).

Рис. 92. Исходные данные для прогнозирования прибыли предприятия

Для расчета прибыли за 2006 год установите курсор в ячейку С12, выберите команду Функция в меню Вставка. В раскрывшемся окне Мастера функций выберите категориюфункцийСтатистическиеи затем вызовите функцию ПРЕДСКАЗ. На экране появится диалоговое окно функции ПРЕДСКАЗ. (рис. 93).

Рис. 93. Окно диалога функции ПРЕДСКАЗ

в появившемся окне введите исходные данные и получите результат (рис. 94).

Рис. 94. Результаты прогнозирования с помощью функции ПРЕДСКАЗ

Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих x- и y-значений экспоненциальной кривой.

РОСТ(изв_знач_y; изв_знач_x; нов_знач_x; константа),

изв_знач_y — это множество значений y, которые уже известны для соотношения y = b*m^x.

изв_знач_x — это необязательное множество значений x, которые уже известны для соотношения y = b*m^x.

нов_знач_x — это новые значения x, для которых РОСТ возвращает соответствующие значения y.

константа — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.

Если константа имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.

Если константа имеет значение ЛОЖЬ, то b полагается равном 1, а значения m подбираются так, чтобы y = m^x.

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

Установите курсор в ячейку С12, выберите команду Функция в меню Вставка, а затем выберите функцию РОСТ. На экране появится диалоговое окно функции РОСТ (рис. 95).

Рис. 95. Окно диалога функции РОСТ

в появившемся окне введите исходные данные и получите результат (рис. 96).

Рис. 96. Результаты прогнозирования с помощью функции РОСТ

Виды функций

Оконные функции можно подразделить на следующие группы:

• Агрегатные функции;
• Ранжирующие функции;
• Функции смещения;
• Аналитические функции.

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

Агрегатные функции

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

• SUM – возвращает сумму значений в столбце;
• COUNT — вычисляет количество значений в столбце (значения NULL не учитываются);
• AVG — определяет среднее значение в столбце;
• MAX — определяет максимальное значение в столбце;
• MIN — определяет минимальное значение в столбце.

Пример использования агрегатных функций с оконной инструкцией OVER:

Ранжирующие функции

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

• ROW_NUMBER – функция возвращает номер строки и используется для нумерации;
• RANK — функция возвращает ранг каждой строки. В данном случае значения уже анализируются и, в случае нахождения одинаковых, возвращает одинаковый ранг с пропуском следующего значения;
• DENSE_RANK — функция возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;
• NTILE – это функция, которая позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках.

Функции смещения

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

• LAG или LEAD – функция LAG обращается к данным из предыдущей строки окна, а LEAD к данным из следующей строки. Функцию можно использовать для того, чтобы сравнивать текущее значение строки с предыдущим или следующим. Имеет три параметра: столбец, значение которого необходимо вернуть, количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL;
• FIRST_VALUE или LAST_VALUE — с помощью функции можно получить первое и последнее значение в окне. В качестве параметра принимает столбец, значение которого необходимо вернуть.

Аналитические функции

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

• CUME_DIST — вычисляет интегральное распределение (относительное положение) значений в окне;
• PERCENT_RANK — вычисляет относительный ранг строки в окне;
• PERCENTILE_DISC — вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить.

Важно! У функций PERCENTILE_CONT и PERCENTILE_DISC, столбец, по которому будет происходить сортировка, указывается с помощью ключевого слова WITHIN GROUP

Способ 2: интерполяция графика с помощью его настроек

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

1. Выполняем построение графика обычным методом. То есть, находясь во вкладке «Вставка», выделяем табличный диапазон, на основе которого будет проводиться построение. Щелкаем по значку «График», размещенному в блоке инструментов «Диаграммы». Из появившегося списка графиков выбираем тот, который считаем более уместным в данной ситуации.

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

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

Выделяем всю плоскость, на которой размещен график. В появившемся контекстном меню щелкаем по кнопке «Выбрать данные…».

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

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

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

Открывается окно настройки скрытых и пустых ячеек. В параметре «Показывать пустые ячейки» выставляем переключатель в позицию «Линию». Жмем на кнопку «OK».

После возвращения в окно выбора источника подтверждаем все сделанные изменения, щелкнув по кнопке «OK».

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

Как построить график с интерполяцией в Excel

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

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

Заполните таблицу как показано на рисунке:

Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».

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

1. Изменить параметры в настройках графика выбрав соответствующую опцию.
2. Использовать функцию: =НД() – возвращает значение ошибки #Н/Д.

Оба эти способа рассмотрим далее на конкретных примерах.

1. Сделайте график активным щелкнув по нему левой кнопкой мышки и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
2. В появившемся диалоговом окне «Выбор источника данных» кликните на кнопку «Скрытые и пустые ячейки»
3. В появившемся диалоговом окне «Настройка скрытых и пустых ячеек» выберите опцию «линию». И нажмите ОК во всех открытых диалоговых окнах.

Как видно на рисунках сразу отображены 2 варианта опций «линию» и «нулевые значения»

Обратите внимание, как ведет себя график при выборе каждой из них

Логические функции

Данные конструкции используются для построения больших и сложных формул.

• И – истина, если все условия истинные;
• ИЛИ – истина, если хотя бы одно условия истинное;

Для анализа различных условий используются следующие функции:

• ЕСЛИ – для проверки одного события;
• УСЛОВИЯ – то же самое, только с огромным количеством условий.

Последняя из указанных выше появилась только в редакторе Excel 2016. Ранее использовался вариант «ЕСЛИМН».

В качестве примера можно привести следующую таблицу.

В данном случае использовались сразу две функции: «ЕСЛИ» и «ИЛИ».

=ЕСЛИ(ИЛИ(D3=»Первая»;D3=»Вторая»);100;0)

Для проверки работы формулы можно использовать конструкцию с «ЕСЛИОШИБКА». Если всё составлено корректно, то вы увидите результат вычислений. В противном случае увидите введенное значение в текстовом виде.

Шаг 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Прогнозирование в Excel. Расчет объема продаж

​ (даты или номера​​ и подходов, но​ 12 месяц –​Сформируем сглаженные временные ряды​Функция ТЕНДЕНЦИЯ дала нам​​Приведем примеры функции ТЕНДЕНЦИЯ.​ аргумент. Диапазон переменных​На основе полученных данных​Допустим у нас имеются​

​ и нажмите​​ «Пакет анализа». Дополнительные​

​ новое значение предсказывается​​в группе​ прогрессии.​

​Заполнить​​ Чтобы управлять созданием ряда​ примере, один -​ периодов)​

​ часто для грубой​​ 9 430 у.е.​

​ методом скользящего среднего​​ те же прогнозные​​​ x, для которых​ составляем прогноз по​ следующие статистические данные​

​Ctrl+Shift+Enter​​ сведения см. в​ с использованием линейной​Анализ​​Тип прогрессии​).​​ вручную или заполнять​ линейная регрессия по​zelencov​ повседневной оценки ситуации​

​​​ посредством функции СРЗНАЧ.​ показатели на 16-19​Функцию ТЕНДЕНЦИЯ хорошо использовать​ необходимо рассчитать значения​ продажам на следующие​

​ по продажам за​​. Строка формул заключит​ статье Загрузка пакета​ регрессии. Этой функцией​нажмите кнопку​Результат шага​С помощью команды​ ряд значений с​

​ МНК. Формулы только​​: Есть задача на​

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

​ статистического анализа.​​ можно воспользоваться для​

​Линия тренда​​Арифметическая​Прогрессия​ помощью клавиатуры, воспользуйтесь​ разные.​ прогнозирование в Excel.​ Одна из них​

planetaexcel.ru>

​ же задачу.​

• Excel не работает формула впр в excel
• Как в excel убрать формулы
• Расшифровка формул в excel
• Как составлять формулы в excel
• Формула условия в excel
• Excel в формуле константа
• Excel формула суммы
• Формула если то в excel то
• В excel преобразовать текст в формулу
• Как скопировать формулу в excel на весь столбец без изменения
• Ввод формул в excel
• Формулы массивов в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Решение. Построение точечного и интервального прогнозов

Лабораторная работа №4

Построение точечного и интервального прогнозов.

Задача

По следующему временному ряду (табл. 1.) составить точечный и интервальный прогноз на 2012 г. с доверительной вероятностью Р=0,95.

Решение

1. По исходным данным строим точечную диаграмму (рис.1).

Рис. 1. Диаграмма, построенная по данным табл.1.

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

2. Выводим на диаграмму линию и уравнение тренда (рис.2).

Для вывода линии и уравнения тренда навести курсор на график, щелкнуть правой кнопкой мыши, выбрать Добавить линию тренда, выбрать тип Линейная, поставить галочки для вывода уравнения на графике и вывода величины достоверности аппроксимации R^2.

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

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

Рис. 2. Линия тренда и ее параметры, выведенные на диаграмму исходных значений

3. В столбце С (рис.3) рассчитываем уровни ряда на основании уравнения тренда: y=4,6424t+62,876.

Затем рассчитываем по формуле

величину

t_α

4. В столбце D рассчитываем квадрат разности между исходными и расчетными значениями уровней временного ряда.

5. В ячейке D14 рассчитана сумма квадратов разности – 30,35.(графа 4). В ячейке D15 делим сумму на n-l=8, так как в данном примере число уровней ряда равно 10, а параметров в уравнении прямой – 2. Это число 3,79. В ячейке D16 — квадратный корень предыдущего числа – 1,95. Это и есть величина

F(x)=t_α=

Рис. 3. Расчет доверительного интервала прогноза

6. Находим точечное значение прогноза по линии тренда (рис.4). Для этого в ячейку A13 добавляем новый момент времени t=t(11) (соответствующий 2012г.), и в ячейке С13 автоматически появляется значение линии тренда при t=t(11) – это число 113,93. Теперь для получения интервального прогноза из точечного прогноза нужно вычесть и прибавить величину

t_α

Рис. 4. Расчет точечного прогноза и его доверительного интервала

На основании построенной модели временного ряда можно производить интерполяцию в том случае, когда нужно восстановить значения внутри ряда. Например, объем производства в 2006 году неизвестен. Этот год соответствует моменту времени t=5. Для этого достаточно найти значение лини тренда от момента времени t=5 и подставить в исходный ряд недостающее значение (рис. 5).

Рис. 5. Интерполяция данных на основании уравнения тренда

Как построить линию тренда в MS Excel

Щелкните правой кнопкой мыши по одному из «синих» столбцов, и в контекстном меню выберите пункт «Добавить линию тренда» .

На листе диаграммы теперь отображается пунктирная линия тренда. Как видите, она не совпадает на 100% со значениями диаграммы — построенная по средневзвешенным значениям, она лишь в общих чертах повторяет её направление. Однако это не мешает нам видеть устойчивый рост числа посещений сайта — на общем результате не сказывается даже «летняя» просадка.

Линия тренда для столбца «Посетители»

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

Ещё одна линия тренда позволяет прояснить ситуацию

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

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

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

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

1. Регулярную составляющую
2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

1. Тренда
2. Сезонности
3. Циклической составляющей

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

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

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

1. Тренд (Tt)
2. Сезонность (St)
3. Цикличность (Ct)
4. Случайные возмущения (Et)

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

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

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

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

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

Аддитивная модель

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

1. Если амплитуда сезонных колебаний возрастает или уменьшается, строят мультипликативную модель временного ряда, которая ставит уровни ряда в зависимость от значений сезонной компоненты.
2. Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).