Прогноз в эксель, Как составить прогноз на Excel
Недостаточно информации. В таких случаях рекомендуют использовать не линейную регрессию, а логарифмический тренд, чтобы результаты прогнозов были более точными. Отвечаем на вопросы диалогового меню:. Эту кривую оставить на графике, остальные удалить. Прогнозирование является важной составляющей управления бизнесом.
Нормированный R-квадрат. Стандартная ошибка. Дисперсионный анализ. Переменная X 1. Переменная X 2. Результаты регрессионного анализа.
Результаты прогноза представлены в столбце E таблицы 3. Таблица 3. Объем продаж. Прогнозирование на основе анализа временных рядов. Временной ряд - это ряд наблюдений, проводившихся регулярно через равные интервалы времени. Он может быть равен году, неделе, суткам или даже минутам, в зависимости от характера рассматриваемой переменной.
Прогнозирование с применением метода скользящего среднего. Сглаживание ряда динамики с помощью скользящей средней заключается в том, что вычисляется средний уровень из определенного числа первых по порядку уровней ряда, затем — средний уровень из такого же числа уровней, начиная со второго, далее — начиная с третьего и т. Таким образом, при расчетах среднего уровня как бы «Скользят» по ряду динамики от его начала к концу, каждый раз отбрасывая один уровень в начале и добавляя один следующий.
Интервал сглаживания, то есть число входящих в него уровней определяют, используя следующее правило: если необходимо сгладить мелкие, беспорядочные колебания, то интервал сглаживания берут по возможности большим ; если же нужно сохранить более мелкие волны и освободиться от периодически повторяющихся колебаний — интервал сглаживания уменьшают. Для прогнозирования объема продаж необходимы средние значения данного показателя за последние несколько лет.
В таблице 4 представлены ежеквартальные объемы продаж компании, специализирующегося на розничной продаже детских игрушек. Таблица 4. Один из способов создания скользящего среднего в Excel является прямое введение формулы.
Если записать формулу в ячейку C 6, то это приведет к потере некоторых показателей скользящего среднего рис. В данном случае у временного ряда имеется сезонная вариация, при этом показатель скользящего среднего имеет тенденцию к увеличению. Исходные данные и скользящее среднее объема продаж. Другим способом создания скользящего среднего является использование надстройки Пакет Анализа , инструмента анализа Скользящее среднее:.
В меню Сервис выбрать команду Анализ данных. Появится диалоговое окно Анализ данных , где из списка выбрать инструмент Скользящее среднее рис. Окно инструмента Скользящее среднее. В поле Входной интервал указать диапазон В 2 :В В поле Интервал ввести количество кварталов, которое включается в подсчет скользящего среднего - 4. Интервал сглаживания по умолчанию используется значение 3 определяют, используя правило: если необходимо сгладить мелкие колебания временного ряда, то интервал берут по возможности большим , если нужно сохранить мелкие волны — интервал сглаживания уменьшают.
В поле Выходной интервал ввести адрес ячейки или щелкнуть на этой ячейке в рабочем листе для вывода результатов. Поставить значки для вывода графика и стандартных погрешностей. Excel представит результаты решения в столбцах E и F табл. График Скользящее среднее. Таблица 5. Прогнозирование с использованием метода Экспоненциального сглаживания. При прогнозировании с применением метода сглаживания учитывается отклонение предыдущего прогноза от реального показателя, а сам расчет проводится по следующей формуле:.
Если при сравнении прогноза с реальными значениями сглаженные данные при выбранном a значительно отличаются от исходного ряда, необходимо перейти к другому параметру сглаживания чем больше значение a , тем больше сглаживание. В ячейки А 2 :В17 рис. Для составления прогноза используем инструмент Экспоненциальное сглаживание пакета Анализ данных :.
В появившемся окне инструмента Экспоненциальное сглаживание рис. Окно инструмента Экспоненциальное сглаживание. Входной интервал — В 2 :В17;. Фактор затухания константа сглаживания a — 0,3 значение по умолчанию — 0,3 ;. Выходной интервал : С12;. Поставить флажки для вывода графика и стандартных погрешностей. Excel представит результаты решения в следующем виде - рис. Диапазон С 2 :С13 — рассчитанные значения экспоненциального сглаживания по формуле 1.
Диапазон D 2: D 17 — рассчитанные значения стандартных погрешностей.
Результаты решения задачи с помощью инструмента Экспоненциальное сглаживание. Прогнозирование на основе тренда. Тренд — это общая долгосрочная тенденция изменения временного ряда, лежащая в основе его динамики. Первая стадия анализа временных рядов — построение графика данных. Программа Excel позволяет построить тренд непосредственно на графике данных и на его основе сделать прогноз о развитии изучаемого процесса на предстоящий промежуток времени.
В Excel существует шесть различных типов линий трендов аппроксимация и сглаживание , которые могут быть добавлены в диаграмму Excel :. Возможно также построение линий трендов на основе скользящих средних. Линии тренда позволяют графически отображать тенденции данных и прогнозировать данные. Построение линий трендов выполняется в следующей последовательности:. На основе введенных данных на лист Excel с помощью мастера диаграмм построить диаграмму. Нагляднее всего выбрать тип диаграммы График , вид — график с маркерами, помечающими точки данных.
Щелкнув правой кнопкой мыши непосредственно на диаграмме, выбрать из появившегося контекстного меню команду Д обавить линию тренда. На экране появится диалоговое окно Линия тренда. На вкладке Тип из шести предложенных выбрать один из типов линии тренда. При выборе типа Полиномиальная в поле Степень необходимо указать степень полинома возможен выбор от 2 до 6. При выборе типа Линейная фильтрация в поле Точки следует указать количество наблюдений, которое включается в любое вычисление скользящего среднего возможен выбор от 2 до Раскрыть вкладку Параметры.
На вкладке Параметры :. Excel может расположить уравнение таким образом, что оно перекроет некоторые данные графика или линии тренда. В этом случае следует выделить это уравнение, щелкнув на нем мышью, а затем перетащить его в другое, более удобное место. Значение коэффициента детерминации R 2 — это оценка точности адекватной модели. При подборе линии тренда к данным Excel автоматически рассчитывает R 2 и по требованию отображает это значение на диаграмме.
Выводимое значение R - квадрат коэффициент детерминации определяет, с какой степенью точности полученное уравнение аппроксимирует исходные данные. Чтобы удалить линию тренда, следует выделить её и нажать [ Del ]. Опыт работы подобных Видеокафе показал следующую динамику значений посещаемости кафе в зависимости от входной платы:.
Входная плата х ,.
Количество посетителей у. Ввести исходные данные на лист Excel. С помощью мастера диаграмм построить график посещений видеокафе в зависимости от входной платы. Построить линии трендов с использованием линейной, логарифмической, полиномиальной и экспоненциальной функций. Для каждой из этих функций показать на графике уравнение кривой и величину достоверности аппроксимации R 2 R 2 показывает степень приближения тренда к фактическим значениям динамического ряда.
Графическое представление данных с использованием логарифмической, полиномиальной функций и уравнения кривых должны выглядеть так:. Логарифмический и полиномиальный типы линий тренда. По величине R 2 определить кривую, которая наиболее точно воспроизводит характер изменения исследуемых показателей с максимальным значением R 2.
Эту кривую оставить на графике, остальные удалить. Итак, выбранное уравнение кривой полиномиальный тип :. Полученные коэффициенты а, b коэффициенты при х , заносим в таблицу данных табл. В таблицу добавить столбцы: Выручка экспериментальная и Выручка теоретическая. Для расчетов использовать формулу:. Результаты вычислений представлены в таблице 6. Используя метод наименьших квадратов минимизируем Погрешность-отклонение теоретических данных от фактических.
Формулу записываем в ячейку Н 9. Таблица с вычислениями должна выглядеть так табл. Таблица 6. С помощью сервисной функции Поиск решения Сервис- Поиск решения подобрать коэффициенты при х — а и b , стараясь минимизировать погрешность. Отвечаем на вопросы диалогового меню:. Использование функции Поиск решения. При неправильном заполнении нажать кнопку В осстановить.
Ответив на вопросы диалогового меню рис. Программа выдает запрос на сохранение найденного решения сохранить и выбрать тип отчета — Результаты. В итоге результаты вычислений будут выглядеть так рис. Результаты вычислений с помощью функции Поиск решения. Таблица 7.
Задание: Составить прогноз роста народонаселения России, используя сведения из статистического справочника за период с по гг. Таблица 8.
Исходные данные. Численность населения, млн. Для решения поставленной задачи необходимо знать вид функциональной зависимости, связывающей количество жителей и время, а также коэффициенты этой зависимости. Для определения коэффициентов используются экспериментальные данные, для нахождения же вида зависимости нужны либо теоретические предпосылки, либо подбор экспериментальных видов зависимости. Для нахождения этой функциональной зависимости осуществляем подбор значений коэффициентов а и b.
Сначала определим их значения приближенно, для чего построим график роста статистической численности и аппроксимируем его. Это осуществляется с помощью представления на графике линии тренда. Для построения линии тренда необходимо:. В результате на графике появится линия тренда и уравнение с подобранными коэффициентами а и b.
Линия тренда, уравнение и значение R 2. В данной статье мы с вами рассмотрим несколько таких методик и произведем все необходимые расчеты в Excel. Еще больше про анализ данных в Excel мы рассказываем на нашем открытом курсе « Аналитика в Excel ». Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе.
Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение. Следующий шаг: давайте определимся, что нам нужно учесть при построении прогноза.
Когда мы исследуем наши данные, нам необходимо учесть следующие факторы:. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде. Однако, помимо регулярной составляющей, в временном ряде присутствует еще некоторое случайное отклонение. Интуитивно это понятно — продажи могут зависеть от многих факторов, некоторые из которых могут быть случайными.
С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать? Классический вариант такой: — Аддитивная модель используется, если амплитуда колебаний более-менее постоянная; — Мультипликативная — если амплитуда колебаний зависит от значения сезонной компоненты.
Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:. Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!
Нам необходимо будет сделать следующую последовательность действий:. На выходе мы получили 2 числа: первое — коэффициент a , второе — свободный член b. Теперь нам нужно рассчитать для каждого периода значение линейного тренда.
Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Что нам это дало? То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее.
Это и дает нам коэффициент сезонности. В конце формулы делим на 2, так как в расчете фигурировало 2 периода. Рассчитали только 12 коэффициентов, так как один коэффициент учитывает продажи сразу за 2 аналогичных периода. Итак, теперь мы на финишной прямой. Нам осталось рассчитать тренд для будущих периодов и учесть коэффициент сезонности для них. Давайте амбициозно построим прогноз на год вперед. Сначала создаем столбец, в котором прописываем номера будущих периодов.
