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

Приходько Д.С. 1

1МБОУ "Гимназия №3" 9"А" класс

Белова Т.А. 1

1МБОУ "Гимназия №3"

Работа в формате PDF

11.6 MB

Автор работы награжден дипломом победителя II степени

Диплом школьника Свидетельство руководителя

Текст работы размещён без изображений и формул.
Полная версия работы доступна во вкладке "Файлы работы" в формате PDF

ВВЕДЕНИЕ

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

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

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

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

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

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

ОСНОВНАЯ ЧАСТЬ

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

Статистические методы прогнозирования

1.1. Экономические прогнозы

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

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

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

оперативные (с периодом упреждения до одного месяца);

краткосрочные (период упреждения – от одного, нескольких месяцев до года);

среднесрочные (период упреждения более 1 года, но не превышает 5 лет);

долгосрочные (с периодом упреждения более 5 лет).

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

1. постановка задачи и сбор необходимой информации;

2. первичная обработка исходных данных;

3. определение круга возможных моделей прогнозирования;

4. оценка параметров моделей;

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

6. построение прогноза;

7. содержательный анализ полученного прогноза.

1.2. Виды временных рядов. Требования, предъявляемые к исходной информации

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

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

Каждый временной ряд содержит два элемента: значения времени; соответствующие им значения уровней ряда.

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

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

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

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

Успешность статистического анализа развития процессов во времени во многом зависит от правильного построения временных рядов.

Большое значение для дальнейшего исследования процесса имеет выбор интервалов между соседними уровнями ряда. Удобнее всего иметь дело с равноотстоящими друг от друга уровнями ряда.

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

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

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

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

1.3. Компоненты временных рядов

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

тренд;

сезонную компоненту;

циклическую компоненту;

случайную составляющую.

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

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

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

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

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

Экономисты разделяют факторы, под действием которых формируется нерегулярная компонента, на 2 вида:

факторы резкого, внезапного действия;

текущие факторы.

Факторы первого вида (например, стихийные бедствия, эпидемии и др.), как правило, вызывают более значительные отклонения. Иногда такие отклонения называют катастрофическими колебаниями.

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

Если временной ряд представляется в виде суммы соответствующих компонент, то полученная модель носит название аддитивной (1.1), если в виде произведения – мультипликативной (1.2) или смешанного типа (1.3):

; (1)

; (2)

, (3)

где: – уровни временного ряда;

– трендовая составляющая;

– сезонная компонента;

– циклическая компонента;

– случайная компонента.

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

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

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

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

Алгоритм сглаживания по простой скользящей средней может быть представлен в виде следующей последовательности шагов:

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

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

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

4. Заменяют фактические значения ряда, стоящие в центре каждого участка, на соответствующие средние значения.

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

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

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

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

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

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

1.5. Прогнозирование развития по линии тренда

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

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

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

Наиболее распространенным вариантом кривой роста является полином:

, (4)

где

– параметры многочлена,

– независимая переменная (время), .

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

Обычно в экономических исследованиях применяются полиномы не выше третьего порядка.

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

где

– фактическое значение уровня временного ряда;

– расчетное значение;

– длина временного ряда.

В качестве другого варианта кривой роста можно рассмотреть простую показательную кривую, которая имеет вид:

. (6)

Если b, то кривая растет вместе с ростом , и падает, если . Параметр характеризует начальные условия развития, а параметр b – постоянный темп роста.

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

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

Также в качестве критерия выбора кривой роста используется средняя квадратическая ошибка:

(7)

где

– фактическое значение уровня ряда;

– расчетное значение уровня ряда, полученное по модели;

– длина ряда.

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

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

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

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

1. субъективной ошибочностью выбора вида кривой;

2. погрешностью оценивания параметров кривых;

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

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

, (8)

где

– расчетное значение уровня ряда, полученное по модели,

– средняя квадратическая ошибка,

– одно из чисел: 1, 2 или 3. При границы доверительного интервала обеспечивают приблизительно 68% надежность прогноза, при обеспечивается 95% надежность, при обеспечивается 99% надежность.

Построение модели сезонности с использование электронной таблицы Excel

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

В качестве исходных данных возьмем данные по объему перевозки пассажиров за 2019-2021 года.

Таблица 1.

Объем перевозки пассажиров за 2019-2021 года

Год	Квартал	Данные
2019	1 кв.	464,39
	2 кв.	369,45
	3 кв.	350,3
	4 кв.	199,62
2020	1 кв.	809,63
	2 кв.	440,88
	3 кв.	501,51
	4 кв.	327,26
2021	1 кв.	842,55
	2 кв.	715,45
	3 кв.	813,8
	4 кв.	545,1

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

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

Рис. 1. Диаграмма «Данные по перевозкам»

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

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

Построение прогноза предполагает:

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

Расчет трендового прогноза, выражающего общую тенденцию ряда.

Совмещение трендового прогноза с характеристиками циклических колебаний.

Такое построение может быть основано на аддитивной модели или на мультипликативной модели.

В обеих моделях (1), (3) предполагается, что исходный ряд данных представляет собой комбинацию трех составляющих:

– трендовая составляющая, характеризующая основную долговременную тенденцию ряда;

– циклическая (обычно сезонная) составляющая, характеризующая регулярно повторяющиеся изменения исходного ряда;

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

2.1. Построение аддитивной модели

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

Пошаговая процедура построения аддитивной модели:

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

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

3. Определение средних значений циклических характеристик (вычисление средних значений по однородным периодам).

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

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

6. Выбор вида тренда и расчет его параметров (определение формулы тренда).

7. Расчет прогнозных значений по формуле тренда.

8. Наложение нормированных циклических характеристик на трендовый прогноз (их суммирование с трендовым прогнозом).

9. Построение графика исходного ряда, продолженного прогнозными значениями.

10. Построение доверительного интервала.

Полученные результаты при условии прогнозирования на 1 год вперед приведены ниже. Описание построения модели приведено в Приложении 1.

Рис. 2. Прогноз по аддитивной модели на основе экспоненциального тренда

Рис. 3. Прогноз по аддитивной модели на основе экспоненциального тренда с доверительным интервалом

2.2. Построение мультипликативной модели

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

Пошаговая процедура построения мультипликативной модели:

2. Выявление колебаний ряда (деление исходного ряда на сглаженный).

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

5. Устранение циклической составляющей из исходного временного ряда (деление исходного ряда на циклический).

6. Выбор вида тренда и расчет его параметров (определение формулы тренда).

7. Расчет прогнозных значений по формуле тренда.

8. Наложение нормированных циклических характеристик на трендовый прогноз (их перемножение с трендовым прогнозом).

9. Построение графика исходного ряда, продолженного прогнозными значениями.

10. Построение доверительного интервала.

Полученные результаты приведены ниже. Описание построения модели приведено в Приложении 2.

Рис. 4. Прогноз по мультипликативной модели на основе линейного и экспоненциального трендов

Рис. 5. Прогноз по мультипликативной модели на основе экспоненциального тренда с доверительным интервалом

ЗАКЛЮЧЕНИЕ

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

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

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

Статистические методы прогнозирования имеют важное практическое значение в современном мире.

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

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

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

Дуброва Т.А. Статистические методы прогнозирования в экономике: Учебное пособие, практикум, тесты, программа курса. – М.: Московский государственный университет экономики, статистики и информатики, 2004. – 136 с.

Литвинчук С.Ю. Информационные технологии в экономике. Анализ и прогнозирование временных рядов с помощью Excel: учебное пособие. – Нижний Новгород: Нижегородский государственный архитектурно-строительный университет, 2010. – 78 с. .

Кувайская Ю.Г. Статистические методы прогнозирования: учебное пособие.– Ульяновск: УлГТУ, 2019. – 197 с.

Приложение 1.

Построение аддитивной модели в электронной таблице Excel на основе линейного тренда

В исходные данные добавим столбец с порядковым номера квартала (). Учитываем, что мы хотим построить прогноз на 2022 год (4 шага вперед):

Подготовим шаблон модели в соответствии с шагами, описанными в разделе 2.1 работы.

ШАГ 1. Устранение циклических колебаний из исходного ряда .

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

На первом этапе проведем сглаживание при определенной нами длине интервала ( ), результат укажем в ячейке Е5 со сдвигом на полпериода вперед. Для вычисления среднего значения воспользуемся встроенной функцией СРЗНАЧ, которая возвращает среднее арифметическое своих аргументов. В ячейку Е5 вводим функцию СРЗНАЧ со следующими аргументами:

и протягиваем ее до ячейки Е13. В результате получим сглаженные годовые данные.

Теперь проведем повторное сглаживание уже сглаженного ряда при длине интервала, равному 2. База опять четная, и на этот раз результат мы сдвинем на полпериода назад. В ячейку F6 вводим функцию СРЗНАЧ со следующими аргументами:

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

ШАГ 2. Выявление колебаний ряда.

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

В ячейку G6 вводим формулу: «=D6-F6» и протягиваем ее до ячейки G13. В итоге мы получим сезонные колебания нашего исходного ряда.

ШАГ 3. Определение средних значений циклических характеристик.

Для каждого однородного периода найдем среднее значение сезонных колебаний. Для 1 квартала найдем среднее значение колебаний, относящихся к первому кварталу, для 2 кварталу – ко второму и т.д. В ячейку H16 вводим формулу «=СРЗНАЧ(G8;G12)», в ячейку H17 вводим формулу «=СРЗНАЧ(G9;G13)», в H18 – «=СРЗНАЧ(G6;G10)», в H19 – «=СРЗНАЧ(G7;G11)». Получим поквартальные средние значения циклических колебаний.

ШАГ 4. Нормирование средних значений циклических характеристик.

Определим среднее значение поквартальных средних значений циклических колебаний. В ячейку H2 вводим формулу: «=СРЗНАЧ(H16:H19)».

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

В ячейку I16 вводим формулу: «=H16-$H$2», закрепляя ячейку H2 и протягиваем ее до ячейки I19. В итоге мы получим нормированные средние значения сезонных колебаний.

Проверим, что среднее значение нормированных поквартальных средних значений циклических колебаний равняется 0. В ячейку I2 вводим формулу: =СРЗНАЧ(I16:I19)». Результатом вычисления является 0.

В итоге мы получили – циклическую составляющую исходного ряда . Внесем полученные данные в соответствующий столбец.

ШАГ 5. Устранение циклической составляющей из исходного временного ряда .

Для устранения цикличности и построения обессезоненного ряда данных необходимо построить разность между исходным рядом и циклическим рядом . В ячейку К4 вводим формулу «=D4-J4» и протягиваем ее до ячейки К15. В результате получим обессезоненную составляющую исходного ряда.

Отобразим на диаграмме полученные на Шагах 1-5 данные: исходные данные , сезонную составляющую и обессезоненные данные.

ШАГ 6. Выбор вида тренда и расчет его параметров (определение формулы тренда).

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

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

С помощью кнопки блока «Диаграммы» перенесем диаграмму на отдельный лист.

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

Щелкнем по графику правой клавишей мыши, чтобы вызвать контекстное меню .

Выбираем раздел «Добавить линию тренда», чтобы открыть диалоговое окно «Формат линия тренда»:

В диалоговом окне «Формат линии тренда» выберем тип линии тренда. Для выбора предоставляются следующие типы линии тренда:

Экспоненциальная,

Линейная,

Логарифмическая,

Полиномиальная,

Степенная,

Линейная фильтрация.

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

В диалоговом окне «Формат линии тренда» также предлагается:

определить название линии тренда, которое будут включено в легенду,

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

Три дополнительные опции позволяют отобразить на диаграмме:

пересечение линии тренда с осью Y (опция Пересечение кривой с осью Y в точке);

уравнение линии тренда (опция Показывать уравнение на диаграмме);

значение коэффициента детерминации , определяющее достоверность аппроксимации (опция Поместить на диаграмму величину достоверности аппроксимации (R^2)).

Построим линейную, степенную и экспоненциальную линии тренда с отображением уравнения и коэффициента детерминации на 4 месяца вперед.

Коэффициент детерминации характеризует степень близости линии тренда к исходным данным. Он может принимать значения от 0 до 1. Чем больше его значение, тем лучше линия тренда аппроксимирует исходные данные.

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

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

ШАГ 7. Расчет прогнозных значений по формуле тренда.

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

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

Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом.

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

Функции ТЕНДЕНЦИЯ и РОСТимеют одинаковый синтаксис:

=ТЕНДЕНЦИЯ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст])

=РОСТ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст]),

где

Известные_значения_Y – обязательный аргумент. Множество значений переменной Y, которые уже известны;

Известные_значения_Х – обязательный аргумент. Множество значений факторов;

Новые_значения_х – обязательный аргумент. Значения факторов, для которых вычисляется прогнозное значение;

Константа – необязательный аргумент.

Если в функциях ТЕНДЕНЦИЯ и РОСТ аргумент Известные_значения_Х опущен, то предполагается, что это массив натуральных чисел {1; 2; 3; ...} такого же размера, как и массив аргумента Известные_значения_Y. Если опущен аргумент Новые_значения_х, то по умолчанию предполагается, что он совпадает с аргументом Известные_значения_Х.

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

Известные_значения_Y – множество обессезоненных данных (диапазон данных $K$4:$K$15);

Известные_значения_Х – множество значений периодов (диапазон данных $A$4:$A$15);

Новые_значения_х – значения времени , соответствующие периоду построения прогноза (значение ячейки A4).

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

ШАГ 8. Наложение нормированных циклических характеристик на трендовый прогноз.

Построим окончательный прогноз на основе линейного тренда, для этого сложим сезонную и трендовые составляющие. В ячейку М4 введем формулу: «=L4+J4» и протянем ее до ячейки M19. В итоге получим прогнозные значения.

ШАГ 9. Построение графика исходного ряда, продолженного прогнозными значениями.

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

ШАГ 10. Построение доверительного интервала.

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

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

где

– расчетное значение уровня ряда, полученное по модели,

– средняя квадратическая ошибка,

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

где

– фактическое значение уровня ряда;

– расчетное значение уровня ряда, полученное по модели;

– длина ряда.

Для расчета средней квадратической ошибки воспользуемся встроенной в Excel функцией СУММКВРАЗН, которая возвращает сумму квадратов разностей соответствующих значений в двух массивах и встроенной функцией СЧЕТ, которая подсчитывает количество ячеек, содержащих числа.

Функция СУММКВРАЗН имеет следующий синтаксис:

СУММКВРАЗН(массив_x; массив_y)

где

Массив_x – обязательный аргумент. Первый массив или диапазон значений;

Массив_y – обязательный аргумент. Второй массив или диапазон значений.

Введем в ячейку N2 формулу: «=(СУММКВРАЗН(D4:D15;M4:M15)/(СЧЁТ($D:$D)-2))^0,5». Таким образом, мы рассчитаем среднюю квадратическую ошибку.

Ограничим ввод данных в ячейке О2. Мы хотим, чтобы в нее можно было вводить только числа 1, 2 или 3. Для этого выделяем эту ячейку, затем во вкладке «Данные» в блоке «Работа с данными» выберем пункт «Проверка данных» и подпункт «Проверка данных».

В диалоговом окне «Проверка вводимых значений» во вкладке «Параметры» задаем следующие ограничения:

Во вкладках «Сообщение для ввода» и «Сообщение об ошибке» следующие данные:

Теперь в ячейку О2 мы сможем внести только значения 1, 2 или 3.

Осталось только отобразить в ячейке Р2 надежность нашего доверительного интервала. Сделаем это при помощи функции ЕСЛИ, которая возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.

Функция ЕСЛИ имеет следующий синтаксис:

ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

Лог_выражение – обязательный аргумент. Любое значение или выражение, дающее в результате значение ИСТИНА или ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.

Значение_если_истина – необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ИСТИНА. Если аргумент лог_выражение соответствует значению ИСТИНА, а аргумент значение_если_истина опущен (т.е. после аргумента лог_выражение есть только запятая), возвращается значение 0.

Значение_если_ложь – необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ЛОЖЬ. Если аргумент лог_выражение соответствует значению ЛОЖЬ, а аргумент значение_если_ложь опущен (т.е. после аргумента значение_если_истина нет запятой), функция ЕСЛИ возвращает логическое значение ЛОЖЬ. Если аргумент лог_выражение соответствует значению ЛОЖЬ, а значение аргумента значение_если_ложь пусто (т. е. после аргумента значение_если_истина стоит только запятая), функция ЕСЛИ возвращает значение 0 (ноль).

Введем в ячейку Р2 функцию если со следующими параметрами:

Теперь при значении , в ячейке Р2 отобразится 68%, при значении – 95%, при – значение 99%.

Построим доверительный интервал при надежности 95%.

Для построения нижней границы доверительного интервала введем в ячейку N4 формулу: «=$M4-$N$2*$O$2», закрепив при этом необходимые ячейки, и протянем ее до ячейки N19. Таким образом мы найдем значения нижней границы доверительного интервала.

Для построения верхней границы доверительного интервала введем в ячейку О4 формулу: «=$M4+$N$2*$O$2», закрепив при этом необходимые ячейки, и протянем ее до ячейки О19. Таким образом мы найдем значения верхней границы доверительного интервала.

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

Построение аддитивной модели в электронной таблице Excel на основе экспоненциального тренда

Подготовим шаблон модели в соответствии с шагами, описанными в разделе 2.1 работы.

Повторим Шаги с 1 по 6, описанные первом разделе данного приложения.

ШАГ 7. Расчет прогнозных значений по формуле тренда.

Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом.

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

Функции ТЕНДЕНЦИЯ и РОСТимеют одинаковый синтаксис:

=ТЕНДЕНЦИЯ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст])

=РОСТ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст]),

где

Известные_значения_Y – обязательный аргумент. Множество значений переменной Y, которые уже известны;

Известные_значения_Х – обязательный аргумент. Множество значений факторов;

Новые_значения_х – обязательный аргумент. Значения факторов, для которых вычисляется прогнозное значение;

Константа – необязательный аргумент.

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

Известные_значения_Y – множество обессезоненных данных (диапазон данных $K$4:$K$15);

Известные_значения_Х – множество значений периодов (диапазон данных $A$4:$A$15);

Новые_значения_х – значения времени , соответствующие периоду построения прогноза (значение ячейки A4).

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

ШАГ 8. Наложение нормированных циклических характеристик на трендовый прогноз.

Построим окончательный прогноз на основе экспоненциального тренда, для этого сложим сезонную и трендовые составляющие. В ячейку М4 введем формулу: «=L4+J4» и протянем ее до ячейки M19. В итоге получим прогнозные значения.

ШАГ 9. Построение графика исходного ряда, продолженного прогнозными значениями.

ШАГ 10. Построение доверительного интервала.

где

– расчетное значение уровня ряда, полученное по модели,

– средняя квадратическая ошибка,

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

где

– фактическое значение уровня ряда;

– расчетное значение уровня ряда, полученное по модели;

– длина ряда.

Функция СУММКВРАЗН имеет следующий синтаксис:

СУММКВРАЗН(массив_x; массив_y)

где

Массив_x – обязательный аргумент. Первый массив или диапазон значений;

Массив_y – обязательный аргумент. Второй массив или диапазон значений.

В диалоговом окне «Проверка вводимых значений» во вкладке «Параметры» задаем следующие ограничения:

В

о вкладках «Сообщение для ввода» и «Сообщение об ошибке» следующие данные:

Теперь в ячейку О2 мы сможем внести только значения 1, 2 или 3.

Функция ЕСЛИ имеет следующий синтаксис:

ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

Введем в ячейку Р2 функцию если со следующими параметрами:

Теперь при значении , в ячейке Р2 отобразится 68%, при значении – 95%, при – значение 99%.

Построим доверительный интервал при надежности 95%.

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

Приложение 2.

Построение мультипликативной модели в электронной таблице Excel на основе линейного и экспоненциального трендов

Подготовим шаблон модели в соответствии с шагами, описанными в разделе 2.2 работы.

ШАГ 1. Устранение циклических колебаний из исходного ряда .

и

протягиваем ее до ячейки Е13. В результате получим сглаженные годовые данные.

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

Ш

АГ 2. Выявление колебаний ряда.

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

В

ячейку G6 вводим формулу: «=D6/F6» и протягиваем ее до ячейки G13. В итоге мы получим сезонные колебания нашего исходного ряда.

ШАГ 3. Определение средних значений циклических характеристик.

ШАГ 4. Нормирование средних значений циклических характеристик.

Получаем среднее значение, равное 0,9797. Смещаем все характеристики поквартальных средних значений циклические колебаний на эту величину так, чтобы среднее значение смещенных характеристик было равно 1.

В ячейку I16 вводим формулу: «=H16/$H$2», закрепляя ячейку H2 и протягиваем ее до ячейки I19. В итоге мы получим нормированные средние значения сезонных колебаний.

Проверим, что среднее значение нормированных поквартальных средних значений циклических колебаний равняется 1. В ячейку I2 вводим формулу: =СРЗНАЧ(I16:I19)». Результатом вычисления является 1.

ШАГ 5. Устранение циклической составляющей из исходного временного ряда .

Д

ля устранения цикличности и построения обессезоненного ряда данных необходимо поделить исходный ряд на циклический ряд . В ячейку К4 вводим формулу «=D4/J4» и протягиваем ее до ячейки К15. В результате получим обессезоненную составляющую исходного ряда.

Так как значения ряда сезонности изменяется в диапазоне от 0 до 2, то диаграмма в таком виде неинформативна. Добавим для данного ряда значений дополнительную ось. Для этого выделим диаграмму, затем в пункте меню «Работа с диаграммами» во вкладке «Конструктор» в блоке «Тип» нажимаем кнопку «Изменить тип диаграммы». Во всплывающем диалоговом окне «Изменение типа диаграммы» переходим на вкладку «Вид диаграммы». Выбираем вид – комбинированная и напротив ряда данных «Сезонность» указываем необходимость наличия дополнительной оси.

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

ШАГ 6. Выбор вида тренда и расчет его параметров (определение формулы тренда).

С помощью кнопки блока «Диаграммы» перенесем диаграмму на отдельный лист.

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

Щелкнем по графику правой клавишей мыши, чтобы вызвать контекстное меню .

Выбираем раздел «Добавить линию тренда», чтобы открыть диалоговое окно «Формат линия тренда»:

Экспоненциальная,

Линейная,

Логарифмическая,

Полиномиальная,

Степенная,

Линейная фильтрация.

В диалоговом окне «Формат линии тренда» также предлагается:

определить название линии тренда, которое будут включено в легенду,

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

Три дополнительные опции позволяют отобразить на диаграмме:

пересечение линии тренда с осью Y (опция Пересечение кривой с осью Y в точке);

уравнение линии тренда (опция Показывать уравнение на диаграмме);

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

2.1 Построение мультипликативной модели в электронной таблице Excel на основе линейного тренда

ШАГ 7. Расчет прогнозных значений по формуле тренда.

Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом.

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

Функции ТЕНДЕНЦИЯ и РОСТимеют одинаковый синтаксис:

=ТЕНДЕНЦИЯ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст])

=РОСТ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст]),

где

Известные_значения_Y – обязательный аргумент. Множество значений переменной Y, которые уже известны;

Известные_значения_Х – обязательный аргумент. Множество значений факторов;

Новые_значения_х – обязательный аргумент. Значения факторов, для которых вычисляется прогнозное значение;

Константа – необязательный аргумент.

Известные_значения_Y – множество обессезоненных данных (диапазон данных $K$4:$K$15);

Известные_значения_Х – множество значений периодов (диапазон данных $A$4:$A$15);

Новые_значения_х – значения времени , соответствующие периоду построения прогноза (значение ячейки A4).

ШАГ 8. Наложение нормированных циклических характеристик на трендовый прогноз.

П
остроим окончательный прогноз на основе линейного тренда, для этого перемножим сезонную и трендовые составляющие. В ячейку М4 введем формулу: «=L4*J4» и протянем ее до ячейки M19. В итоге получим прогнозные значения.

ШАГ 9. Построение графика исходного ряда, продолженного прогнозными значениями.

ШАГ 10. Построение доверительного интервала.

где

– расчетное значение уровня ряда, полученное по модели,

– средняя квадратическая ошибка,

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

где

– фактическое значение уровня ряда;

– расчетное значение уровня ряда, полученное по модели;

– длина ряда.

Функция СУММКВРАЗН имеет следующий синтаксис:

СУММКВРАЗН(массив_x; массив_y)

где

Массив_x – обязательный аргумент. Первый массив или диапазон значений;

Массив_y – обязательный аргумент. Второй массив или диапазон значений.

Введем в ячейку N2 формулу: «=(СУММКВРАЗН($D$4:$D$15;M4:M15)/(СЧЁТ($D:$D)-2))^0,5». Таким образом, мы рассчитаем среднюю квадратическую ошибку.

В диалоговом окне «Проверка вводимых значений» во вкладке «Параметры» задаем следующие ограничения:

В

о вкладках «Сообщение для ввода» и «Сообщение об ошибке» следующие данные:

Теперь в ячейку О2 мы сможем внести только значения 1, 2 или 3.

Осталось только отобразить в ячейке T2 надежность нашего доверительного интервала. Сделаем это при помощи функции ЕСЛИ, которая возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.

Функция ЕСЛИ имеет следующий синтаксис:

ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

Введем в ячейку T2 функцию если со следующими параметрами:

Теперь при значении , в ячейке T2 отобразится 68%, при значении – 95%, при – значение 99%.

Построим доверительный интервал при надежности 95%.

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

Построение мультипликативной модели в электронной таблице Excel на основе экспоненциального тренда

ШАГ 7. Расчет прогнозных значений по формуле тренда.

Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом.

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

Функции ТЕНДЕНЦИЯ и РОСТимеют одинаковый синтаксис:

=ТЕНДЕНЦИЯ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст])

=РОСТ(Известные_значения_Y; [Известные_значения_Х]; [Новые_значения_х]; [Конст]),

где

Известные_значения_Y – обязательный аргумент. Множество значений переменной Y, которые уже известны;

Известные_значения_Х – обязательный аргумент. Множество значений факторов;

Новые_значения_х – обязательный аргумент. Значения факторов, для которых вычисляется прогнозное значение;

Константа – необязательный аргумент.

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

Известные_значения_Y – множество обессезоненных данных (диапазон данных $K$4:$K$15);

Известные_значения_Х – множество значений периодов (диапазон данных $A$4:$A$15);

Новые_значения_х – значения времени , соответствующие периоду построения прогноза (значение ячейки A4).

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

ШАГ 8. Наложение нормированных циклических характеристик на трендовый прогноз.

Построим окончательный прогноз на основе экспоненциального тренда, для этого перемножим сезонную и трендовые составляющие. В ячейку Q4 введем формулу: «=P4*J4» и протянем ее до ячейки Q19. В итоге получим прогнозные значения.

ШАГ 9. Построение графика исходного ряда, продолженного прогнозными значениями.

ШАГ 10. Построение доверительного интервала.

где

– расчетное значение уровня ряда, полученное по модели,

– средняя квадратическая ошибка,

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

где

– фактическое значение уровня ряда;

– расчетное значение уровня ряда, полученное по модели;

– длина ряда.

Функция СУММКВРАЗН имеет следующий синтаксис:

СУММКВРАЗН(массив_x; массив_y)

где

Массив_x – обязательный аргумент. Первый массив или диапазон значений;

Массив_y – обязательный аргумент. Второй массив или диапазон значений.

Введем в ячейку R2 формулу: «=(СУММКВРАЗН($D$4:$D$15;Q4:Q15)/(СЧЁТ($D:$D)-2))^0,5». Таким образом, мы рассчитаем среднюю квадратическую ошибку.

Ограничим ввод данных в ячейке S2. Мы хотим, чтобы в нее можно было вводить только числа 1, 2 или 3. Для этого выделяем эту ячейку, затем во вкладке «Данные» в блоке «Работа с данными» выберем пункт «Проверка данных» и подпункт «Проверка данных».

В диалоговом окне «Проверка вводимых значений» во вкладке «Параметры» задаем следующие ограничения:

В

о вкладках «Сообщение для ввода» и «Сообщение об ошибке» следующие данные:

Теперь в ячейку S2 мы сможем внести только значения 1, 2 или 3.

Осталось только отобразить в ячейке U2 надежность нашего доверительного интервала. Сделаем это при помощи функции ЕСЛИ, которая возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.

Функция ЕСЛИ имеет следующий синтаксис:

ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

Введем в ячейку U2 функцию если со следующими параметрами:

Теперь при значении , в ячейке U2 отобразится 68%, при значении – 95%, при – значение 99%.

Построим доверительный интервал при надежности 99%.

Для построения нижней границы доверительного интервала введем в ячейку R4 формулу: «=$Q4-$R$2*$S$2», закрепив при этом необходимые ячейки, и протянем ее до ячейки R19. Таким образом мы найдем значения нижней границы доверительного интервала.

Для построения верхней границы доверительного интервала введем в ячейку S4 формулу: «=$Q4+$R$2*$S$2», закрепив при этом необходимые ячейки, и протянем ее до ячейки S19. Таким образом мы найдем значения верхней границы доверительного интервала.

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

Просмотров работы: 80

XVII Международный конкурс научно-исследовательских и творческих работ учащихся
Старт в науке

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

Автор работы награжден дипломом победителя II степени

Старт в науке
XVII Международный конкурс научно-исследовательских и творческих работ учащихся