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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Постройка графика

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

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

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

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

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

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

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

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

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

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

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

Готово!

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

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

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

• Как должно выглядеть уравнение на языке Excel?
• Например, мое уравнение:
• y = −0,1837×2 + 2,9289x + 83,664
• на языке ексел будет выглядеть так:
• =-0,1837*A18^2+2,9289*A18+83,664

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

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

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

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

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

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

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

Источник: http://officeassist.ru/excel/kak-sdelat-prognoz-v-excel/

Как в экселе сделать линию тренда. Инструменты прогнозирования в Microsoft Excel

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

Рассмотрим, как добавить линию тренда на график в Excel.

Добавление линии тренда на график

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

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

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

Внимание!!! Линию тренда нельзя добавить следующим типам графиков и диаграмм:

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.

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

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

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

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

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

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

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

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

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

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

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 – номер периода.

Полиномиальная линия тренда в Excel

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

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

• Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.
• Скачать примеры графиков с линией тренда
• Зато такой тренд позволяет составлять более-менее точные прогнозы.

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

1.  Что такое тренд (это важно как отправная точка) 2) Построение трендовых линий 3) Использование в практической торговле
2. Субъективность метода
3.  Что такое тренд _________________
4. Прежде, чем перейти к построению трендовой линии, надо разобраться непосредственно с самим трендом. Не будем вдаваться в академические споры и для простоты примем следующую формулу:

Тренд (восходящий) – это последовательность растущих максимумов и минимумов, при этом каждый последующий максимум (и минимум) выше предыдущих. Тренд (нисходящий) – это последовательность падающих (убывающих) максимумов и минимумов, где каждый последующий минимум (и максимум) НИЖЕ предыдущего.

Построение трендовых линий ____________________________

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

Это самый сложный вопрос! Мне доводилось видеть дискуссии на много страниц только о том, КАК ПРАВИЛЬНО строить линию тренда! А ведь нам надо не только строить, но и торговать по ней…

Что бы построить трендовую линию надо иметь, как минимум, два максимума (нисходящий тренд) или два минимума (восходящий тренд). Мы должны соединить эти экстремумы линией. Важно соблюдать следующие правила при построении линий:

Важен угол наклона линии тренда. Чем более крутой угол наклона, тем меньше надежность. — Оптимально строить линию по двум точкам. Если строить по трем или более точкам – надежность трендовой линии снижается (вероятен ее пробой).

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

1. Данные правила помогут вам правильно строить трендовые линии!
2. Торговля по трендовым линиям ____________________________
3. Мы имеем две принципиально разные возможности: А) Использовать линию как уровень поддержки (сопротивления), что бы войти по ней по направлению тренда
4. Б) Использовать трендовую линию Форекс для того, что бы сыграть на пробой (разворот) тренда.
5. Оба способа хороши, если уметь «правильно их готовить».

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

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

• Если мы хотим сыграть на слом линии тренда, то надо действовать немного иначе: 1) Ждем касание линии 2) Ждем отскока 3) На образовавшуюся галочку ставим ордер бай-стоп (или sell стоп)
• Обратите внимание на рисунок.
• Мы дождались образования галочки и выставили ордер бай стоп на ее максимум.

Через некоторое время ордер сработал, и мы вошли в рынок. Возникает закономерный вопрос – почему нельзя было войти в рынок сразу?

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

Субъективность метода _________________________

Кажется все просто? На деле, используя данный метод, мы столкнемся со следующими трудностями: А) Угол наклона линии (всегда можно построить линии тренда имеющие разный наклон. Б) Что считать пробоем трендовой линии (насколько пунктов или процентов цена должна «переломить» линию, что бы считать это прорывом)?

1. В) Когда линию считать «устаревшей» и строить новую?
2. Обратите внимание на рисунок.

Красной линией обозначен один из вариантов начертания. Неопытный трейдер мог так провести линию (и поплатиться за это).

В данном деле важен практический опыт. То есть не удается все свести к нескольким простым правилам построения. Именно поэтому индикатора трендовых линий не существует. Точнее, может и существует, но строит их «криво» и неправильно. Эта техника изначально «заточена» под опыт и мастерство трейдера.

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

Источник: https://www.amok.ru/kak-v-eksele-sdelat-liniyu-trenda-instrumenty-prognozirovaniya-v.html

Новые вызовы — “старые” решения!?

• Общий алгоритм выполнения прогнозирования
• В целом, процесс прогнозирования можно выполнить соблюдая последовательность расчёта следующих пунктов:
• Выделение трендовой составляющей как функции регрессии от нескольких факторов – обязательный этап прогнозирования;
•   Выделение сезонной составляющей   в виде сезонных коэффициентов;
• Трендовая и сезонная составляющая комбинируются (с помощью операции сложения или умножения) в единую функцию прогнозирования – виртуальный (не вычислительный) этап;
• Вычисляются прогнозные значения как значения функции «прогнозирования» при будущих значениях факторов.
• “Качество прогноза напрямую зависит от качества построенной модели данных”
• Поэтому для более полного, более точного прогноза необходимо правильно задать исходные данные.
• Таким образом, наш алгоритм преобразуется в более расширенную последовательность выполнения действий:
• Подготовка данных. Этап предварительного анализа имеющихся данных: анализ резко выделяющихся наблюдений, восстановление пропущенных данных, исключение факторов, явно не влияющих на прогнозируемую переменную У;
• Выделение трендовой составляющей – подбор функций, аппроксимирующих трендовую компоненту, и вычисление параметров этих функций, отбор значимых факторов;
• Выделение сезонной составляющей – вычисление сезонных коэффициентов по разностям между фактическими значениями переменной У и вычисленным значениям тренда;

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

Вычисление прогнозного значения. На основе отобранных функций прогнозирования вычисляются прогнозные значения.

1. Суть всех реализованных методов сводится к построению функции регрессии («тренда») с дальнейшей ее экстраполяцией на основе данных за последние 6 месяцев.
2. Прогноз производится в несколько этапов
3. Подготовка исходных данных

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

• Расчёт доверительных интервалов и окончательный прогноз
• Этап №1
• Входные данные для прогнозирования в Excel
• Надо сказать, что в виду большого ассортимента товаров, находящихся на складах компании, наиболее оптимальным, в плане ведения бизнеса, для компании было внедрение системы краткосрочного прогнозирования (период упреждения был равен 1 месяцу).
• Большинство методов и алгоритмов прогнозирования, реализованных в Excel связано с построением тренда.
• В рамках поставленной задачи : построение прогноза на 1 месяц вперёд, для реализованной модели в Excel было необходимо и достаточно построение модели по шести последним месяцам продаж.

Исходные данные по продажам за последние шесть месяцев

Этап №2

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

А) построение линейной функции регрессии:

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

Выполняется в два этапа:

• 1) Для определения погрешности прогноза:
• Рис 2. Построение прогноза при помощи функции ТЕНДЕНЦИЯ
• а) По 5-ти известным периодам, строится прогноз на 6-ой период;
• б) Вычисляется погрешность (сравнение прогноза с исходным значением за 6-ой период). Определение погрешности прогноза
• в) Определение прогнозного значения на следующий (седьмой) период:Рис 4. Определение прогноза с учётом погрешности
• Прогнозированиеметодом скользящего среднего
• Прогноз по скользящим средним реализуется при помощи встроенной формулы Excel:
• Y’3=СРЗНАЧ(Y1÷ Y3) Сглаживание исходных данныхметодом скользящих средних (по 3-м месяцам)

Построение прогноза по уже сглаженным исходным данным

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

1. Расчёт погрешности методапрогнозирования по скользящим средним
2. После сглаживания исходных данных,воспользуемся отношением:
3. ε = 1/N * Σ |yi – y’i| / yi
4. Поскольку первые два периода сглаженных значений не определяются, расчёт величины погрешности определяется за 4 периода (с 3-го по 6-ой)!
5. Б) Построение нелинейной функции регрессии
6. Метод экспоненциального сглаживания
7. Алгоритм построения функций регрессии:
8. прогнозирование методом экспоненциального сглаживания

Экспоненциальное сглаживание применяется к временным ряда, т.е. тогда, когда фактором, от которого зависит переменная прогнозирования Y, является время t.

• В этом методе сглаживания
• “учитывается «старение» данных – в процессе сглаживания больший вес имеют последние данные”
• При прогнозе данным методом, величина прогноза зависит от значения коэффициента сглаживания α, характеризующий актуальность давности данных(0 < α < 1, чем ближе значение коэффициента к нулю, тем больше влияние именно последних данных).
• Экспоненциальное сглаживание для построения функции регрессии
• Сглаживание производится в соответствии с формулой:
• упрог i = (1 — α)*уi + α*уi-1
• где 0 < α < 1,
• чем больше значение α, тем выше степень сглаживания
• Расшифровка формулы
• i-значение сглаженной равно:
• упрог i =  (1 – коэф. сглаживания α) *
•  yi-значение не сглаженной +
•  α*y(i — 1)-значение сглаженной
• Экспоненциальное сглаживание  для прогнозирования
• Прогноз производится по следующей формуле:
• упрог i = (1 — α)*уi-1 + α*уi-1
• где 0 < α < 1,
• Расшифровка формулы
• i-значение сглаженной равно:
• упрог i =  (1 – коэф. сглаживания α) *
•    y(i -1)-значение не сглаженной +
•    α * y(i — 1)-значение сглаженной
• Данный вариант экспоненциального сглаживания позволяет выполнить прогноз по сглаженным данным.
• Однако, здесь надо отметить, что прогнозные значения одинаковы для любого числа периодов, отчитанных вперед
• Основной недостаток – сдвиг вправо графиков сглаженных данных по отношению графика исходных данных
• Алгоритм аппроксимации методом экспоненциального сглаживания:
• Загрузка исходных данных;
• Искусственное «зануление» исходных данных за последний отчётный период;
• Сглаживание пяти исходных данных.
• Сглаживание осуществляется по формуле:
• упрог i = (1 — α)*уi + α*уi-1
• где 0 < α < 1,
• Расшифровка формулы
• упрог i =  (1 – коэф. сглаживания α) *
• yi -значение не сглаженной +
• α*y(i — 1) -значение сглаженной
• 4) Поиск коэффициента сглаживания α на искусственно «зануленном» периоде на основе пяти известных периодов (условие — выбор осуществляется при минимальной ошибке прогноза);
• Прогноз на шестой период по пяти известным осуществляется по формуле:
• упрог i = (1 — α)*уi-1 + α*уi-1
• Расшифровка формулы
• i-значение сглаженной равно:
• упрог i =  (1 – коэф. сглаживания α)*(i -1)-значение не сглаженной + α*(i — 1)-значение сглаженнойРис 7. Массив справа — исходные данные;массив слева — сглаженные данные
• 1) Определение минимальной ошибки аппроксимации учитывает погрешность по каждому из отчётных периодов («шагов»), а не только по одному конечному периоду.
• Определяется по формуле:
• Средняя ошибка аппроксимации (ε) – используется для оценки адекватности уравнения регрессии.
• ε = 1/N * Σ |yi – y’i| / yi
• После определение минимальной погрешности и соответствующей ей коэффициента сглаживания α, мы используем данное значение (α) для прогнозирования на следующие периоды по уже сглаженным данным.
• То есть, уже по шести известным исходным данным, мы делаем прогноз на седьмой отчётный период.
• Изначально, сглаживаем уже шесть исходных периодов, и только затем делаем прогноз на седьмой период.
• Прогноз осуществляется по той же формуле, что мы делали и для прогноза по пяти известным на шестой:
• упрог i = (1 — α)*уi-1 + α*уi-1
• Расшифровка формулы
• i-значение сглаженной равно:
• упрог i =  (1 – коэф. сглаживания α)*y(i -1)-значение не сглаженной + α*y(i — 1)-значение сглаженной

Современный бизнес ставит перед нами всё новые задачи. Для решения некоторых из них, особенно в условиях непростой финансово-экономической ситуации как в стране, так  и мире в целом, использование классических методов и подходов, порой, бывает недостаточно — необходима разработка новых инновационных методов, подходов и инструментов!

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

ПРИМЕР

У Вас может быть прекрасный, во всех отношениях, прогноз — Вы учли практически всё, что только можно было учесть. И вот, наступает момент истины — выясняется, что Ваш конкурент, будучи уже со своим прогнозом, прогнозом столь же прекрасным, смог привлечь большую часть рынка (клиентов), чем Вы и как следствие — показал более высокий результат по продажам. Возможно Вы спросите: “Как же так?”

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

1. Об этом, какие необходимо использовать методы, детально  написано ниже
2. Если же мы с Вами планируем сделать прогноз продаж, в условиях конкурентной среды, то это — совершенно другое.
3. Отличие подходов заключается в наличии конкурентной среды: если в первом случае (прогноз урожайности) конкурентов нет; то во втором случае — прогноз необходимо делать с учётом  возможных действий конкурентов.
4. В мировой практике, нам не удалось найти ответ на вопрос: “Существуют ли программы или инструменты, способные съимитировать поведение конкурентов, конкурентной среды?”
5. Поэтому мы занялись разработкой данного инструмента.
6. “LIM” — пошаговый бизнес-симулятор, реализованный на базе MS Excel.
7. Алгоритм (прошивка) — нейронная сеть, способная выстроить стратегию компании в условиях конкурентной среды.

“Движение вперёд срабатывает как стратегия лишь тогда, когда ты очень точно знаешь, где именно находится это самое вперёд”.

Источник: https://forecast-ing.ru/forecasting_excel_1.html

Задание 3. Прогнозирование развития показателей с помощью линии тренда Excel

Составить прогноз товарооборота торгового пред­приятия на 17-й месяц (см. данные таблицы 3.6) с помощью ко­манды Добавить линию тренда.

Таблица 3.6 — Сведения о динамике товарооборота торгового предприятия

А	В	С	D	E	F
Порядковый номер месяца	Объем товарооборота, ден. ед.
Итого

Выполнение:

Чтобы составить прогноз развития исследуемого показа­теля, используя линии тренда Excel, сначала необходимо с помощью Мастера диаграмм построить диаграмму (График) его дина­мики на основе базовых данных (ячейки В3:В19 таблицы 3.6).

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

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

линейную (у = mх + b);

полиномиальную (у = b + m1x + m2x2 +…+ m6х6);

•  логарифмическую (у = m · ln x + b);
• экспоненциальную (у = m · еb·x);
•  степенную (у = m · хb).

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

1. количество прогнозируемых периодов и направление прогноза: вперед или назад;
2.  когда выбрана линейная, полиномиальная или экспо­ненциальная кривая роста, то в поле Пересечение кривой с осью у в точке 0 задается ее у-пересечение: если данное поле обозначить флажком, то Excel будет искать лучшее уравне­ние кривой, которая на координатной плоскости обязатель­но должна пройти через начало координат;
3.  через установку флажка в соответствующих полях ок­на диалога пользователь решает, отражать ли на выходной диаграмме уравнение, на основе которого была построена ли­ния тренда, и размер квадрата коэффициента корреляции r2, характеризующий качество аппроксимации.
4. C помощью ко­манды Добавить линию тренда составим сразу пять различных вариантов прогноза товарооборота торгового пред­приятия на 17-й месяц и при этом по r2 оценить общее качество моделей, на основе которых они были полу­чены.

Используя возмож­ности Excel по созданию в ячейках рабочего листа формул, с помощью приведенных на графиках уравнений кривых рос­та рассчитаем значения прогноза товарооборота на 17-й месяц (таблица 3.7).

Таблица 3.7 — Прогноз товарооборота на 17-й месяц

Тип модели тренда	Формула расчета прогноза	Прогноз объема товарооборота на 17-й месяц, ден. ед.
Линейная	=437,43*17+27920	35356,3
Логарифмиче­ская	=2429,4*ln(17)+26981	33864,0
Полиноми­альная	=3,9737*17^3-88,245*17^2+925,09*17+27432	1 37178,5
Степенная	=27215*17^0,0774	33887,9
Экспоненци­альная	=28081*е^(0,0138*17)	35490,0

Рисунок 3.3 — График развития товарооборота торгового предприятия	Рисунок 3.4 — Оценка прогноза товарооборота торгового предприятия на основе линейной кривой роста
Рисунок 3.5 — Оценка прогноза товарооборота на основе логарифмической кривой роста	Рисунок 3.6 — Оценка прогноза товарооборота на основе полиномиальной кривой роста (степень 3)
Рисунок 3.7 — Оценка прогноза товарооборота на основе степенной кривой роста	Рисунок 3.8 — Оценка прогноза товарооборота на основе экспоненциальной кривой роста

Вывод: Приведенные на рисунках 3.3–3.8 графики динамики то­варооборота свидетельствуют, что наибольшая степень при­ближения линии тренда к базовым данным достигнута в слу­чае полиномиальной кривой роста 3-й степени (см. рисунок 3.6, r2 = 0,9519), наименьшая — в случае логарифмической кри­вой (см. рисунок 3.5, r2 = 0,7779).

Не нашли то, что искали? Воспользуйтесь поиском:

Источник: https://studopedia.ru/7_95887_zadanie—prognozirovanie-razvitiya-pokazateley-s-pomoshchyu-linii-trenda-Excel.html

Прогноз в excel

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

1. В таблицу заносятся исходные статистические данные анализа показателей за определенный период, по которым строится точечный график
2. Необходимо получить аналитическую зависимость F(x), гдеF – объем продаж, аx – период. Но, в данном случае, значение года (числа 1984, 1985 и т.д.) не может быть значением независимой переменной.
3. В качестве независимой переменной может быть только номер периода (1, 2, 3 и т.д.). Поэтому необходимо войти в режим«Свойства диаграммы» — «Исходные данные» – вкладка «Ряд»и удалить значениях (если они выделялись при построении диаграммы).
4. Получение зависимости проводится по аппроксимирующей линии (линии тренда). Для добавления линии тренда в диаграмму необходимо установить указатель на любую точку диаграммы.

Вызвать контекстное меню и выбрать режим «Добавить линию тренда».

1. На вкладке «Тип»выбрать одну из пяти предложенных линий тренда (линейная).
2. На вкладке «Параметры»включить два переключателя
3. Получаем график с линией тренда и указанием уравнения и величины достоверности. Однако, задача заключается в том, чтобы из пяти предлагаемых зависимостей выбрать лучшую (по величине достоверности наиболее близкой к 1).
4. Для этого нужно получить все пять зависимостей и сравнить их.
5. Чтобы не накладывать все зависимости на один график, необходимо скопировать уравнение и величину достоверности первой зависимости в свободную ячейку листа, и установить курсор на линии тренда.
6. Вызвать контекстное меню и выбрать режим «Формат линии тренда».
7. На вкладке «Тип»выбрать другую зависимость (логарифмическая).
8. Выполнить пункты 6 – 8 для остальных зависимосьей.

Уравнение лучшей зависимости выделить цветом и оставить соответствующую линию тренда на графике. 10. Для получения графического отображения прогноза на 2 периода вперед, необходимо установить указатель на линии тренда, вызвать контекстное меню, выбрать режим «Формат линии тренда», на вкладке«Параметры»указать параметры прогноза:

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

В свободных ячейках листа записать свои
выводы относительно прогнозных значений.

ПРИМЕЧАНИЕ! Для прогноза используются зависимости, величина достоверности которых не менее 0,75. Если по реальным данным получается меньшая достоверность, необходимо пересмотреть исходные статистические данные: проверить их соответствие или добавить новые значения!!!

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

Для прогнозирования значений могут использоваться функции:

Функция	Описание
ПРЕДСКАЗ	Прогнозирование значений
ТЕНДЕНЦИЯ	Прогнозирование линейной зависимости.
РОСТ	Прогнозирование экспоненциальной зависимости.
ЛИНЕЙН	Построение линейного приближения.
ЛГРФПРИБЛ	Построение экспоненциального приближения.

Источник: https://studfile.net/preview/5690896/

Прогнозирование с помощью Microsoft Excel

Для прогнозирования с Microsoft Excel необходимо ввести следующие основные понятия.

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

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

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

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

MS EXCEL содержит ряд родственных статистических функций для экстраполяционного анализа. К ним относят функции «ТЕНДЕНЦИЯ», «РОСТ», «ЛИНЕЙН», «ЛГРФПРИБЛ». Подробнее рассмотрим первую из них.

• ТЕНДЕНЦИЯ –возвращает значение в соответствии с линейным трендом, аппроксимирует прямой линией (по методу наименьших квадратов) массивы «известные значения У» и «известные значения X», возвращает значения У в соответствии с этой прямой для заданного массива «новые значения X». Синтаксис данной функции:
• ТЕНДЕНЦИЯ(известные значения У, известные значения X, новые значения X, конст) где:
•  известные значения у – это множество значений у, которые уже известны для соотношения у = мх + + в;
•  известные значения х – это необязательное множество значений х, которые уже известны для соотношения у = м х + в;
•  новые значения х – это новые значения х, для которых тренд возвращает соответствующие значения у;
• конст – это логическое значение, которое указывает, требуется ли, чтобы константа «в» была равна нулю.
• РОСТ – подобна «ТЕНДЕНЦИЯ» и «ЛИНЕЙН», но аппроксимирует данные экспоненциальной прямой.
• ЛИНЕЙН – вычисляет прямую, но возвращает параметры прямой, а не массив значений У.
• ЛГРФПРИБЛ – подобна «ТЕНДЕНЦИЯ» и «ЛИНЕЙН», но аппроксимирует данные экспоненциальной прямой.
• Получить информацию о том, как MS EXCEL аппроксимирует данные прямой можно с помощью справки: [F1] _ «Использование Microsoft Excel» _ «Решение задач путём анализа данных» _ «Статистический анализ данных» _ «Регрессия» _ «ЛИНЕЙН».
• Команда «ЛИНИЯ ТРЕНДА» (меню «ВСТАВКА») содержит две вкладки:
• вкладку «ТИП» и вкладку «ПАРАМЕТРЫ». Вкладка «ТИП» имеет функции:

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

•  Тип тренда / регрессии определяет тип линии тренда: линейный, полиномиальный (степень выражается как целое число от 2 до 6), логарифмический, экспоненциальный, степенной, скользящее среднее (указывается количество периодов, использованное для усреднения).
• Вкладка «ПАРАМЕТРЫ» имеет функции модификации линии тренда:
• Имя тренда (располагается в легенде):

«автоматическое». Microsoft Excel именует линию тренда, основываясь на выбранном типе и на ряде данных, с которым она ассоциирована. Например, если «линейный» тренд добавляется ко второму ряду данных в группе типа диаграмм, он выводит имя «Линейный (Ряд 2)».

«пользовательское». Позволяет ввести имя длиной до 256 символов (в версии 5.0).

Прогноз. Доступен только для регрессий (для скользящего среднего не доступен).

«Вперёд на» –указывается количество периодов, на которое проектируется будущее в линии тренда, или от оси у.

«Назад на» –указывается количество периодов, на которое проектируется прошлое в линии тренда, или от оси у.

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

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

Показывать значение R-квадрат на диаграмме. Выводит значение среднего квадрэтического отклонения для линии тренда в метке линии тренда на диаграмме.

Изменение установок линии тренда приемлемо для регрессионного анализа (экстраполяции), а не для скользящего среднего. Можно присвоить имя линии тренда или изменить тип её экстраполяции. Можно также вывести метки линии тренда, например, среднеквадратическое отклонение или уравнение экстраполяции, можно изменить У-пересечение.

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

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

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

Выходной интервал. Введите ссылку для верхней левой ячейки интервала, в который хотите вывести выходную таблицу.

Если выделен флажок «Стандартные ошибки», Microsoft Excel сгенерирует двухколоночную таблицу со значениями стандартных ошибок в правом столбце.

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

Стандартные ошибки. Выделите флажок «Стандартные ошибки», если хотите включить значения стандартных ошибок в выходную таблицу.

Если выделен флажок «Стандартные ошибки», Microsoft Excel сгенерирует двухколоночную таблицу со значениями стандартных ошибок в правом столбце.

Если флажок «Стандартные ошибки» не выделен, Microsoft Excel сгенерирует одноколоночную таблицу без значений стандартных ошибок.

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

Метки. Если первая строка и первый столбец входного интервала содержат метки, выделите флажок «Метки». Если входной интервал не содержит меток, очистите флажок «Метки». Microsoft Excel сгенерирует подходящие метки данных для выходной таблицы.

Линию тренда можно отформатировать, задать её цвет, стиль, толщину. Если при этом доступна «Метка линии тренда», то можно и задавать числовой формат,цвет шрифта и прочие характеристики этой метки.

Источник: https://megaobuchalka.ru/5/5544.html