Порядок работы. 3.2.1 Построение детерминированной части прогнозирующей модели ВР(этап 1):

3.2.1 Построение детерминированной части прогнозирующей модели ВР(этап 1):

а) ввести исходные данные ВР (не менее 20 чисел) в столбец A первого листа программы Excel, как показано на рисунке 3.1;

б) предположим, что исходный временной ряд описывается выражением 3.4. Для построения параболической зависимости необходимо в столбец B ввести нумерацию элементов ВР t, а в столбец С квадрат t, т.е. t² (для получения модели полиномиальной зависимости третьей степени в следующий столбец вводятся данные t³, четвертой степени – t⁴ и т.д.);

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

г) активизировать режим вычисления коэффициентов уравнения регрессии в следующем порядке: “Вставка – Функция – Статистические - Линейн.- Ок”;

д) в появившемся окне ввести следующие исходные данные:

1) Известные_значения_у – диапазон, содержащий данные об объекте (выделить мышью столбец данных ВР);

2) Известные_значения_х – диапазон, содержащий данные времени и квадрата времени (выделить столбцы B и C);

3) Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении 3.6 (если вставить “1”, то свободный член a₀ рассчитывается, если -“0”, то свободный член равен 0;

4) Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет.

Чтобы раскрыть таблицу коэффициентов модели, надо нажать одновременно на комбинацию клавиш < CTRL> +< SHIFT> +< ENTER>.

Для введенных исходных данных: а₀ = 4.2828, а₁ = -0.032, а₂ = 0.0023.

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

ŷ _t (3.9)

е) рассчитать модельные значения y_t в диапазоне t=1-20, подставляя в полученное уравнение значения t и t². Все данные в таблице должны быть отцентрированы, дробные числа округлены до третьего знака после запятой. Результаты расчетов примера представлены на рисунке 1 в столбце D (Y_пр1);

ж) используя графические инструменты Excel, построить графики исходного ряда и ряда, расcчитанного по выражению (3.9). Рисунок должен иметь название, отформатирован по ширине листа, оси графиков должны быть обозначены. На рисунке 3.1 эти графики обозначены соответственно Y и Y_пр1. Сопоставить сходство графиков. Если они сильно отличаются, то возможна ошибка в расчетах;

з) рассчитать прогнозные оценки ВР на моменты времени t =21; t =22; t =23. Построить график модельных данных для t =1, 2, 3,..., 23. (рисунок 3.1“б”).

3.2.2 Построение стохастической части модели ВР (этап 2):

а) для каждого наблюдения ряда в столбце E рассчитать отклонения e(t), как разность между соответствующими данными столбцов A и D так, как показано на рисунке 3.2”а”;

б) для определения коэффициента b₁ уравнения (3.9) расположим в расчетной таблице данные случайной компоненты так, как показано в столбце F на рисунке 3.2“а”;

а)

б)

Рисунок 3.1 – Расчетные данные (“а”) и графики детерминированной части (“б”) прогнозирующей модели ВР

в) определим коэффициент b ₁ модели авторегрессии, для этого повторить пункты в-г раздела 3.2.1. с учетом того, что в данном случае определяются коэффициенты уравнения первого порядка. В окно исходных данных вставить следующие значения:

1) Известные_значения_у – выделить мышью диапазон ячеек E 3- E 21;

2) Известные_значения_х – выделить мышью диапазон ячеек F 3- F 21.

В ячейке I 9 представлено расчетное значение коэффициента b 1= 0.6257.

В результате расчетов методом наименьших квадратов уравнение авторегрессии первого порядка имеет вид:

(3.10)

Уравнение (3.10) построено без свободного члена b ₀;

г) в столбце G расчетной таблицы (рисунок 3.2 “а”) по выражению (3.10) рассчитать модельные значения случайной компоненты для t =2, 3, 4,..., 21;

д) используя выражение (3.10), в ячейках G 23- G 25 рассчитать прогнозные значения случайной компоненты для t =22, 23, 24. При вычислении e (22) в ячейке G 23 использовать значение e (21) из ячейки G 22, при вычислении e (23) в ячейке G 24 использовать значение e (22) из ячейки G 24 и так далее.

3.2.3 Расчет оценок полного прогноза (этап 3) производится по выражению (3.1) для t =21, 22, 23, 24 в ячейках H 22- H 25 по данным ячеек D 22 и G 22, D 23 и G 23, D 24 и G 24, D 25 и G 25. По результатам расчетов, представленных в колонках A, D и H построить графики исходного ВР, прогноза на основе детерминированной модели и графика оценок прогноза с учетом случайной компоненты. На рисунке 3.2”б” для выбранного примера эти графики обозначены как Y, Y_пр1 и Y_пр2.. Как видно из рисунка, график Y_пр2 более близок к графику Y, что свидетельствует о повышении точности прогнозных оценок при учете случайной компоненты. Дать анализ графиков, полученных в результате выполнения заданного варианта.

Контрольные вопросы

1 Привести примеры экономических и технических задач, где нужны прогнозные оценки.

2 Дать характеристику модели прогноза.

3 Как выбираются модели детерминированной и стохастической составляющей прогноза?

4 Описать процесс определения коэффициентов модели в среде Exсel.

5 Чем отличаются процедуры интерполяции и экстраполяции ВР?

а)

б)

Рисунок 3.2 – Расчетные данные (“а”) и графики (“б”) полного прогноза ВР

Варианты заданий

В таблице 3.1 представлены данные временных ядов для прогнозирования.

Таблица 3.1 – Таблица временных рядов

Лабораторная работа № 4. Защита данных с использованием шифра Цезаря

Цель работы: Освоить технологию шифрования и дешифрования информации в среде Excel с использованием шифра Цезаря.