Размещено на http://www.allbest.ru/

Содержание

Введение

1. Прогнозы с применением метода скользящего среднего

2. Прогнозы с применением функций регрессии

3. Прогноз с помощью функции экспоненциального сглаживания

4. Аддитивная и мультипликативная модели

Заключение

Введение

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

Ременной ряд - это последовательность измерений в последовательные моменты времени. Анализ временных рядов включает широкий спектр разведочных процедур и исследовательских методов, которые ставят две основные цели:

(a) определение природы временного ряда;

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

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

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

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

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

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

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

Необходимые требования к временному ряду:

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

- наблюдения должны фиксироваться в одно и то же время (в одинаковый момент каждого периода);

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

1. Прогнозы с применением метода скользящего среднего

По ряду динамики потребность в работниках, заявленная в службы занятости за месяц за 2002-2010 годы (файл ORT.xls) спрогнозируйте развитие изучаемого процесса на два месяца следующего за отчетным года методами скользящего среднего. Используйте различную величину ширины окна скольжения и на основе какого-либо критерия (например, ошибка аппроксимации) выберите лучшую величину ширины окна скольжения, по которой целесообразно проводить прогнозирование.

Для выполнения данного задания используем метод построения прогноза с помощью скользящего среднего, при котором прогноз любого периода получается как среднее по некоторому фиксированному числу значений временного ряда. Одним из способов создания скользящего среднего в Excel является прямое введение формулы СРЗНАЧ и автоматическое заполнение на нужное количество периодов.

Другим способом является использование надстройки «Пакет анализа».

В меню «Данные» необходимо выбрать «Анализ данных», затем выбираем «Скользящее среднее».

Рисунок 1. - Прогноз потребности в работниках с помощью скользящего среднего:

Результат представлен в таблице 1.

Таблица 1. - Прогноз с использованием метода скользящего среднего:

По результатам расчетов стандартных погрешностей и по результатам графического анализа, представленного на рисунке 1, можно сделать вывод о том, что прогноз, сделанный с помощью скользящего среднего с интервалом 3, является более достоверным по сравнению с прогнозом, сделанным с помощью скользящего среднего с интервалом 6, поскольку в первом случае средняя величина погрешности меньше, чем во втором (244,37< 475,13).

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

2. Прогнозы с применением функций регрессии

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

Для выполнения данного задания используем метод построения прогноза с применением функций регрессии, которые позволяют оценивать взаимосвязь между фактическими данными наблюдений и другими данными (чаще всего временной компонентой). Самым простым способом вычисления прогноза с помощью регрессии является функция ТЕНДЕНЦИЯ.

Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между результатом наблюдений и временем, в которое это наблюдение было зафиксировано.

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

Таблица 2. - Прогноз с применением функций регрессии: ТЕНДЕНЦИЯ и РОСТ:

Результаты прогноза представлены графически на рисунке 2.

Рисунок 2. - Прогноз потребности в работниках с использованием функций ТЕНДЕНЦИЯ и РОСТ:

По приведенным в таблице расчетам можно заметить, что использование функций ТЕНДЕНЦИЯ и РОСТ дает разные результаты. Прогноз, составленный на основе функции ТЕНДЕНЦИЯ, дает нам возможность предположить, что в первые два месяца 2011 года по сравнению с предыдущими годами будет наблюдаться рост потребности в работниках. Аналогичное представление нам даёт составление прогноза на основе функции РОСТ.

В результате расчета ошибки аппроксимации можно заметить, что прогноз, выполненный с помощью функции ТЕНДЕНЦИЯ, является более достоверным, по сравнению с использованием функции РОСТ. Так для функции ТЕНДЕНЦИЯ ошибка аппроксимации составила 36,13 %, а для функции РОСТ 37,87%. Таким образом, можно сказать, что, рассматривая две функции: ТЕНДЕНЦИЯ и РОСТ, в нашем случае, из них наилучшей является функция ТЕНДЕНЦИЯ и, соответственно, она дает нам более достоверные результаты прогноза.

3. Прогноз с помощью функции экспоненциального сглаживания

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

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

Метод экспоненциального сглаживания может давать более удачные результаты при наличии высокого уровня автокорреляции во временном ряду. Значение корреляции более 0,5 означает достаточно высокий уровень автокорреляции во временном ряду.

В результате расчета автокорреляции было получено значение 0,978, которое является больше 0,5, что означает достаточно высокий уровень автокорреляции.

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

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

Результаты прогноза представим в таблице 3.

Таблица 3. - Прогноз потребности в работниках с помощью экспоненциального сглаживания:

Рисунок 3. - Экспоненциальное сглаживание при различных факторах затухания:

Итак, экспоненциальное сглаживание с фактором затухания 0,1 лучше всего аппроксимирует исходные данные, и прогноз, построенный на основе этих данных, является наиболее достоверным. Таким образом, можно сказать, что в первый месяц 2011 года следует ожидать уменьшение потребности в работниках, а во второй - увеличение. То есть, потребность в работниках на основании данных таблицы 3 за январь 2011 года, спрогнозированная с помощью функции экспоненциального сглаживания составит 3824,46 чел., а на февраль - 3940,75 чел.

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

Таблица 4. - Годовой объем продаж моторного масла:

Прогнозы с помощью изученных нами методов представлены в таблице 5.

Таблица 5. - Прогноз с помощью методов скользящего среднего, экспоненциального сглаживания, линейной и нелинейной регрессии:

При экспоненциальном сглаживании в рассматриваемом примере наилучший прогноз получается при факторе затухания (1-б) = 0,4 со средней погрешностью = 35,58, который лучше всего аппроксимирует исходные данные. Сравнение погрешностей представлено в таблице 6.

Таблица 6. - Средние погрешности при различных факторах затухания:

Расчетные данные представим графически на рисунках 4, 5.

Рисунок 4. - Прогноз годового объема продаж с применением метода скользящего среднего и экспоненциального сглаживания при факторе затухания 0,4:

Рисунок 5. - Прогноз с помощью линейной и нелинейной регрессии:

Как видно из таблицы 5, прогноз, выполненный с использованием функции ТЕНДЕНЦИЯ, имеет наименьшую среднюю погрешность (19,57), следовательно, более достоверен, чем остальные методы прогноза. На основании данных прогнозов можно сделать вывод об увеличении годового объема продаж моторного масла в 2012 и 2013 годах.

4. Аддитивная и мультипликативная модели

В таблице 7 приведены данные по объёму продаж мазута компании АПИ в странах Восточной Европы в период с 2000 по 2003 гг. (данные условные, приведены в тыс. баррелей за каждый четырехмесячный период года.):

Таблица 7. - Данные по объему продаж мазута:

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

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

Рисунок 6. - Объем продаж мазута:

Среди рассмотренных трендов наиболее выгодным является полиномиальная (2 порядка), т.к. величина достоверности аппроксимации для него наибольшая R² = 0,1529 (табл.8). Уравнение данного тренда выглядит следующим образом:

Для прогнозирования воспользуемся двумя моделями: аддитивная и мультипликативная сезонность на основе метода наименьших квадратов. Использование данных моделей представлено в таблице 9.

Построение аддитивного прогноза объема продаж мазута происходило с использованием следующих формул:

Построение мультипликативного прогноза объема продаж мазута происходило с использование следующих формул:

Ошибка аппроксимации была рассчитана по следующей формуле:

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

Таблица 8. - Аддитивная и мультипликативные модели сезонности объема продаж мазута:

Графическое изображение представлено на рисунке 7.

Рисунок 7. - Аддитивный и мультипликативный объём продаж мазута:

Таким образом, исходя из проведённых расчётов, можно сделать вывод, что прогноз, построенный на основе модели аддитивной сезонности, является более достоверным (ошибка аппроксимации: 1,72%<2,88).

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

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

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

Для построения моделей прогнозов был выбран полиномиальный тренд с величиной достоверности аппроксимации R² = 0,483.

Таблица 9. - Выбор типа тренда:

Для составления прогноза потребности в работниках на 2011 год построим аддитивную и мультипликативную модели сезонности на основе метода наименьших квадратов.

Данные расчеты представлены в таблице 10.

Рисунок 8. - Линейный, экспоненциальный, полиномиальный тренд оборота розничной торговли:

Графические результаты аддитивного и мультипликативного прогнозов потребности в работниках за 2011 год на основе метода наименьших квадратов представлены на рисунке 9.

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

Таблица 10. - Аддитивная и мультипликативная модели сезонности:

Рисунок 9. - Прогноз оборота розничной торговли с помощью использования аддитивной и мультипликативной сезонности на основе МНК:

Таблица 11. - Использование аддитивной и мультипликативной сезонности на основе метода скользящей средней:

Рисунок 10. - Прогноз оборота розничной торговли с помощью использования аддитивной и мультипликативной сезонности на основе метода скользящей средней:

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

Таблица 12. - Обобщенные сведения о стандартных ошибках на основе различных методов прогнозирования:

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

Заключение

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

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

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

- метод скользящих средних;

- регрессионная модель;

- метод сглаживания;

- аддитивная и мультипликативная модели сезонности.

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

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

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

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

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

Размещено на Allbest.ru