2020-04-20
394
1.1) Поставить курсор в ячейку последней строки табл. 6 «а =…» и вызвать “ Мастер функций Þ Категория: Статистические Þ функция: отрезок”, при этом в качестве «Изв_знач_у» выделить весь диапазон числовых значений результативного показателя по данным третьего года, в качестве «Изв_знач_х» выделить диапазон числовых значений фактора времени (1-12) за тот же период.
Далее, осуществив последовательно весь набор действий, определить отрезок по объединенным данным за три года (t изменяется от 1 до 36).
1.2) Поставить курсор в ячейку последней строки табл. 6 «b =…» и вызвать “Мастер функций Þ Категория: Статистические Þ функция: наклон”, принцип действия аналогичен определению «отрезка».
2) Рассчитать прогнозные значения «y» на 4-ый год.
2.1) Поставить курсор в ячейку «Январь» прогнозируемого года.
2.2) Ввести формулу: = абсолютный адрес отрезка + абсолютный адрес наклона * относительный адрес значения t в январе прогнозируемого года.
В первом случае параметры тренда определялись по данным за 12 месяцев; при прогнозировании на январь следующего года надо подставить в уравнение тренда значение t равное 13, при прогнозировании на февраль t = 14 и т.д. Во втором случае динамический ряд содержит наблюдения за 36 месяцев, при прогнозировании на январь следующего года надо ввести значение t = 37 и т.д.
2.3)После ввода формулы нажать клавишу Enter и захватив мышью правый нижний угол ячейки, протянуть ее на 12 месяцев прогнозируемого года.
2.4) В строке «За год» рассчитать суммарное значение прогнозируемого показателя за год.
3) Рассчитать ошибку прогноза по формуле:
3.1) Поставить курсор в ячейку, находящуюся на пересечении столбца «Ошибка прогноза» и строки «Январь».
3.2) Ввести формулу расчетаошибки прогноза(см. табл. 6).
3.3) После ввода формулы нажать клавишу Enter и, захватив мышью правый нижний угол ячейки, протянуть ее на всю колонку, включая строку «За год».
Прогнозирование результативного показателя на основе нелинейного (показательного) уравнения тренда осуществляется по формуле:
.
Техника расчетов результативного показателя на прогнозируемый период по данным 3-го года при этом следующая:
· поставить курсор в ячейку, находящуюся на пересечении столбца «Прогнозное значение показателя» (гр. 6, табл. 6) и строки «Январь»;
· вызвать “ Мастер функций Þ Категория: Статистические Þ функция: РОСТ”;
· в появившемся диалоговом окне следует ввести все запрашиваемые данные (рис. 5):
§ «Изв_знач_у» - выделить весь диапазон числовых значений результативного показателя по данным третьего года;
§ «Изв_знач_х» - выделить весь диапазон числовых значений фактора времени за тот же период (1-12);
§ «Нов_знач_х» - выделить значение фактора времени за январь 4-го года;
§ «Константа» - ввести «ИСТИНА».
Рис. 5. Расчет прогнозного показателя
Для определения остальных прогнозных значений показателя повторить весь набор вышеуказанных действий.
Для определения прогнозных значений результативного показателя по объединенным данным за три года порядок расчета аналогичен вышеприведенному расчету.
Прогнозирование результативного показателя с учетом трендовой (Т), циклической (сезонной, S) и случайной компонент (Е) может осуществляться по аддитивным и мультипликативным моделям.
Аддитивная модель имеет вид:
Y = T + S + E.
Чтобы построить аддитивную модель и сделать по ней прогноз необходимо:
1) Выровнять результативный показатель методом скользящей средней с периодом скольжения, равным 12[1]. Для этого:
· создать таблицу для расчета сезонной компоненты (табл. 8);
Таблица 8
Расчет оценок сезонной компоненты в аддитивной модели
| Период, t | Значение результативного показателя (y) | Скользящие средние, СС(12) | Центрированные СС | Сезонная компонента, S |
| 1 | ||||
| 2 | ||||
| … | ||||
| 47 | ||||
| 48 |
· внести исходные данные в колонки «Период», «Значение результативного показателя»;
· поставить курсор в ячейку, находящуюся на пересечении строки, соответствующей периоду «7», и столбца «Скользящие средние, СС (12)» табл. 8 и вызвать “ Мастер функций Þ Категория: Статистические Þ функция: СРЗНАЧ”. В качестве «Число 1» выделить весь диапазон числовых значений результативного показателя с 1 по 12 периоды;
· после ввода формулы нажать клавишу Enter и, захватив мышью правый нижний угол ячейки, протянуть ее до 31 периода [2].
2)Построить график, отражающий соотношение скользящих средних и фактических значений результативного показателя, для этого:
· в табл. 8 выделить столбцы «Значения результативного показателя» «Скользящие средние, СС (12)», и вызвать “ мастер диаграмм ”;
· выбрать тип диаграммы (Стандартные Þ Тип: График Þ Вид: График с маркерами) и нажать «Далее»;
· в появившемся диалоговом окне в разделе «Диапазон данных» выбрать «Ряды в: столбцах» и нажать «Далее»;
· задать основные параметры диаграммы применительно к своим данным и нажать «Далее»;
· определить место для размещения диаграммы (на отдельном листе) и нажать «Готово».
3) Центрировать полученные скользящие средние:
· поставить курсор в ячейку, находящуюся на пересечении строки, соответствующей периоду «7», и столбца «Центрированные СС» табл. 8;
· вызвать “ Мастер функций Þ Категория: Статистические Þ функция: СРЗНАЧ”. В качестве «Число 1» выделить две последовательно скользящих средних;
· после ввода формулы нажать клавишу Enter и, захватив мышью правый нижний угол ячейки, протянуть ее до 30 периода [3].
4) Найти сезонную компоненту S как разность между значением результативного показателя и центрированной скользящей средней за один и тот же период, внести их в графу «Сезонная компонента, S» табл. 8.
5) Рассчитать средние за каждый период (по всем годам) оценки сезонной компоненты, для чего построить дополнительную таблицу (табл. 9).
Таблица 9
Расчет значений сезонной компоненты
| Показатели | Год | Номер месяца | |||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
|
| 1 | - | - | - | - | - | - | … | … | … | … | … | … |
| 2 | … | … | … | … | … | … | … | … | … | … | … | … | |
| 3 | … | … | … | … | … | … | - | - | - | - | - | - | |
| Итого за i -ый месяц (за все годы) | S | S | S | S | S | S | S | S | S | S | S | S | |
| Среднее значение сезонной компоненты | |||||||||||||
| Скорректированная сезонная компонента | |||||||||||||
6) Ввести корректирующий коэффициент, т.к. сумма значений сезонных компонент по всем месяцам должна быть равна нулю. Он рассчитывается как сумма средних сезонных компонент, деленная на их количество.
7) Рассчитать скорректированные значения сезонной компоненты как разность между ее средней оценкой и корректирующим коэффициентом.
8) Элиминировать влияние сезонной компоненты, вычитая скорректированную сезонную компоненту из каждого значения. Результаты занесем в табл. 10.
9) Определить тренд (Т), для этого рассчитать параметры a и b уравнения линейного тренда (см. выше). Подставляя последовательно значения фактора времени в полученное уравнение тренда заполнить столбец «Расчетные значения тренда» табл. 10;
10) Рассчитать случайные отклонения Е: E = Y – T – S. Результаты внести в табл. 10.
11) Прогнозные значения рассчитать как сумму тренда и сезонной компоненты: F = T + S. Результаты прогнозирования внести в соответствующий столбец табл. 7.
Таблица 10
Расчет выровненных значений Т и ошибок Е
| Период | Значения результативного показателя (yt) | Сезонная компонента (St) | yt – St = T+E | Расчетные значения тренда (T) | Случайное отклонение (Е) |
| 1 | |||||
| 2 | |||||
| … | |||||
| 47 | |||||
| 48 |
Мультипликативная модель имеет вид:
Y = T 
S E.
Чтобы построить мультипликативную модель и сделать по ней прогноз необходимо:
1. выровнять результативный показатель методом скользящей средней с периодом скольжения, равным 12. Для этого:
· создать таблицу для расчета сезонной компоненты (табл. 11);
· внести исходные данные в колонки «Период», «Значение результативного показателя»;
· дальнейший порядок расчета скользящих средних полностью совпадает с методикой аддитивной модели, применяемой на данной этапе;
2. построить график, отражающий соотношение скользящих средних и фактических значений результативного показателя, при этом порядок построения графика полностью совпадает с методикой аддитивной модели, применяемой на данном этапе;
3. центрировать полученные скользящие средние – методика, применяемая на данном этапе, полностью совпадает с методикой аддитивной модели;
4. найти оценки сезонной компоненты S как частное от деления фактических значений результативного показателя на соответствующие центрированные скользящие средние, внести результаты расчетов в графу «Оценки сезонной компоненты, S» табл. 11;
Таблица 11
Расчет оценок сезонной компоненты в аддитивной модели
| Период, t | Значение результативного показателя (y) | Скользящие средние, СС(12) | Центрированные СС | Оценка сезонной компоненты, S |
| 1 | ||||
| 2 | ||||
| … | ||||
| 47 | ||||
| 48 |
5) Рассчитать средние за каждый период (по всем годам) оценки сезонной компоненты, для чего построить дополнительную таблицу (табл. 12).
6) Ввести корректирующий коэффициент, т.к. сумма значений сезонной компоненты по всем месяцам должна быть равна числу периодов в цикле (в данном случае – 12). Он рассчитывается как число периодов в цикле, деленное на сумму средних оценок сезонной компоненты. Результаты внести в соответствующий столбец табл. 12.
Таблица 12
Расчет значений сезонной компоненты
| Показатели | Год | Номер месяца | |||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
|
| 1 | - | - | - | - | - | - | … | … | … | … | … | … |
| 2 | … | … | … | … | … | … | … | … | … | … | … | … | |
| 3 | … | … | … | … | … | … | - | - | - | - | - | - | |
| Итого за i -ый месяц (за все годы) | S | S | S | S | S | S | S | S | S | S | S | S | |
| Средние оценки сезонной компоненты | |||||||||||||
| Скорректированная сезонная компонента | |||||||||||||
7) Определить скорректированные значения сезонной компоненты, умножив ее средние оценки на корректирующий коэффициент. Результаты внести в соответствующий столбец табл. 12.
8) Элиминировать влияние сезонной компоненты, разделив каждое значение результативного показателя на соответствующие значения сезонной компоненты: yt: St. Результаты занести в табл. 13.
9) Определить тренд (Т), для этого можно воспользоваться расчетами параметров a и b уравнения линейного тренда, представленными в аддитивной модели. Подставляя последовательно значения t = 1, …, 36, найти уровни Т для каждого периода (столбец «Расчетные значения тренда» табл. 13).
Таблица 13
Расчет выровненных значений Т и ошибок Е
| Период | Значения результативного показателя (yt) | Сезонная компонента (St) | T * E = yt: St | Расчетные значения тренда (T) | Случайное отклонение (Е) |
| 1 | |||||
| 2 | |||||
| … | |||||
| 47 | |||||
| 48 |
10) Рассчитать случайные отклонения Е: E = Y: (T * S). Результаты внести в табл. 13.
11) Прогнозные значения рассчитать как произведения тренда и сезонной компоненты: F = T * S. Результаты прогнозирования внести в соответствующий столбец табл. 7.
При анализе моделей нельзя забывать об автокорреляции.
Автокорреляция – это корреляционная зависимость между последовательными уровнями одного и того же динамического ряда.
Существует ряд способов обнаружения автокорреляции остатков. Наиболее простым можно считать критерий Дарбина-Уотсона. Рассчитывается статистика d:
, 
.
Результаты вычислений оформляются в виде табл. 14.
Таблица 14
Расчет критерия Дарбина-Уотсона
| Период | Фактические значения результативного показателя, YФ | Прогнозные значения результативного показателя, YТ | et = YФ –YТ | et-1 | (et – et-1)2 | et2 |
 37
| е1 | - | - | … | ||
| 38 | е2 | е1 | ||||
| 39 | е3 | е2 | ||||
| … | ||||||
| Итог | S | S | - | - | S | S |
В зависимости от интервала, в который попадает значение d, принимается определенный вывод (табл. 15). Значения dн и dв определяются из таблицы значений статистик Дарбина-Уотсона при 5% уровне значимости.
Степень автокоррелированности также измеряется с помощью коэффициента корреляции, который называется в данном случае коэффициентом автокорреляции.
Таблица 15
Интервалы значений статистики d
| Значение статистики d | Вывод |
| Принимается гипотеза: автокорреляция отсутствует. |
| Принимается гипотеза о существовании положительной автокорреляции остатков. |
 и 
| При данном уровне значимости нельзя прийти к определенному выводу. |
| Принимается гипотеза о существовании отрицательной автокорреляции остатков. |
Коэффициент автокорреляции уровней ряда 1-го порядка, измеряющий зависимость между соседними уровнями ряда t и t-1, рассчитывается по формуле:
,
где 
; 
.
Вспомогательные расчеты рекомендуется оформить в виде табл.16.
Таблица 16
Расчет коэффициента автокорреляции 1-ого порядка
| t | yt | yt-1 | 
| 
| 
| 
| 
|
 1
| y1 | - | - | - | - | - | - |
| 2 | y2 | y1 | |||||
| 3 | y3 | y2 | |||||
| … | … | … | |||||
| Итого | S | S | 0 | 0 | S | S | S |
Коэффициент автокорреляции 1-го порядка тесно связан с критерием Дарбина-Уотсона: d ≈ 2(1 – r1).
Коэффициент автокорреляции 2-го порядка характеризует тесноту связи между уровнями yt и yt-2:
,
где 
; 
.
Вспомогательные расчеты рекомендуется оформить в виде табл.17.
Таблица 17
Расчет коэффициента автокорреляции 2-ого порядка
| t | yt | yt-2 | 
| 
| 
| 
| 
|
 1
| y1 | - | - | - | - | - | - |
| 2 | y2 | - | - | - | - | - | - |
| 3 | y3 | y1 | |||||
| 4 | y4 | y2 | |||||
| … | … | … | |||||
| Итого | S | S | 0 | 0 | S | S | S |
Аналогично определяются коэффициенты автокорреляции более высоких порядков.
