Рассчитать ошибку прогноза

3.1) Поставить курсор в ячейку, находящуюся на пересечении столбца «Ошибка прогноза» и строки «Январь».

3.2) Ввести формулу расчетаошибки прогноза(см. табл. 2):

.

3.3) После ввода формулы нажать клавишу Enter и, захватив мышью правый нижний угол ячейки, протянуть ее на всю колонку, включая строку «За год».

 

Прогнозирование результативного показателя на основе нелинейной (показательной) однофакторной регрессии осуществляется по формуле:

.

Техника расчетов результативного показателя на прогнозируемый период по данным 3-го года при этом следующая:

· поставить курсор в ячейку, находящуюся на пересечении столбца «Прогнозное значение показателя» (гр.7 табл.2) и строки «Январь»;

· вызвать “ Мастер функций Þ Категория: Статистические Þ функция: РОСТ”;

· в появившемся диалоговом окне следует ввести все запрашиваемые данные (рис. 1):

§ «Изв_знач_у» - выделить весь диапазон числовых значений результативного показателя по данным третьего года;

§ «Изв_знач_х» - выделить весь диапазон числовых значений одного из факторных показателей за тот же период;

§ «Нов_знач_х» - выделить значение факторного показателя за январь 4-го года;

§ «Константа» - ввести «ИСТИНА».

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

 

 

 

Рис. 1. Расчет прогнозного показателя

 

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

 

Для расчета параметров уравнения линейной регрессии и проверки его адекватности исследуемому процессу, Microsoft Excel располагает функцией Регрессия:

· В главном меню выбрать команду «Сервис Þ Анализ данных».

· В появившемся диалоговом окне «Анализ данных» выбрать значение Регрессия.

· В раскрывшемся диалоговом окне ввести только данные (рис. 2):

a) «входной интервал Y» - выделить весь диапазон числовых значений результативного показателя по данным трех лет;

b) «входной интервал Х» - выделить весь диапазон числовых значений одного из факторных показателей за тот же период.

 

 

Рис. 2. Диалоговое окно ввода параметров инструмента Регрессия

 

· Результаты регрессионного анализа будут представлены на отдельном листе (рис. 3), который называется «ВЫВОД ИТОГОВ» и содержит 3 таблицы:

а) Показатели таблицы «Регрессионная статистика»:

- множественный R – коэффициент корреляции R;

-   R–квадрат – коэффициент детерминации R²;

 

 

Рис. 3. Результат применения инструмента Регрессия

- нормированный R – нормированное значение коэффициента корреляции;

- стандартная ошибка – стандартное отклонение остатков;

-   наблюдения – число исходных наблюдений.

б) Показатели таблицы «Дисперсионный анализ»:

- столбец «F» – расчетное значение F-критерия Фишера F_р;

- столбец «Значимость F» – значение уровня значимости, соответствующее вычисленному значению F_р.

в) Показатели таблицы 3:

- ячейка на пересечении столбца «Коэффициенты» и строки «Y - пересечение» – значение параметра уравнения линейной регрессии а - «отрезок»;

- ячейка на пересечении столбца «Коэффициенты» и строки «Переменная Х1» – значение параметра уравнения линейной регрессии b - «наклон».

 

Для расчета средней ошибки аппроксимации и доверительного интервала прогноза рекомендуется заполнить вспомогательную таблицу (табл. 4)

Таблица 4

Таблица вспомогательных расчетов

Месяц	Значение факторного показателя на 4-ый год, Х i	Фактическое значение результатив-ного показателя на 4-ый год, Yф	Прогнозное значение результатив-ного показателя на 4-ый год, Yт	Yф - Yт		(Yф - Yт)2
37
38
…
Итого	S	S	S	-	S	S

Средняя ошибка аппроксимации вычисляется по формуле:

 

 

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

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

Границы интервалов определяются по формуле:

где Y_Т - точечный прогноз, расчитанный по модели,

   t_a - коэффициент доверия по распределению Стьюдента при уровне значимости a = 0,05 (см. Приложение 2);

S_y –стандартная ошибка аппроксимации, ;

  n – число уровней ряда;

  l – число параметров в уравнении регрессии (для уравнения прямой l=2).

ТЕМА 3. Прогнозирование на основе многофакторных
регрессионных моделей

 

Задание

На основе данных за первые три года (1-36 месяцы):

1) Рассчитать параметры уравнения линейной многофакторной регрессии.

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

3) Оценить тесноту связи с помощью показателей корреляции и детерминации.

4) Оценить с помощью средней ошибки аппроксимации () и F-критерия Фишера качество модели.

5) Рассчитать матрицу парных коэффициентов корреляции и на ее основе установить мультиколлинеарность факторов. После устранения факторов:

· рассчитать параметры уравнения многофакторной регрессии;

· построить уравнение регрессии по новым данным регрессионного анализа, характеризующее зависимость результирующего показателя от факторных в линейной форме;

· по полученному уравнению регрессии сделать прогноз на 4-ый год (37-48 месяцы). Результаты прогнозирования представить в табл. 5;

· оценить качество полученной модели с помощью средней ошибки аппроксимации () и F-критерия Фишера;

6) Повторять п.1.5 до тех пор, пока уравнение регрессии не будет соответствовать двухфакторной линейной модели.

Полученные результаты проанализировать. Сделать выводы.

 

Указания к выполнению

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

y = a + b₁*x₁+ b₂*x₂ + b₃*x₃+ b₄*x₄+ b_n*x_n

где y – прогнозируемый показатель (зависимая переменная);

x₁, x₂, х₃ , х₄, …, х_n – факторы, которые влияют на изменения прогнозируемого показателя;

a - отрезок, начальное значение зависимой переменной «y», экономической интерпретации чаще всего не имеет;

Таблица 5

Результаты прогнозирования на основе многофакторных моделей на 4-й год

Месяцы	Фактические значения показателя	5-ти факторная модель (указать модель)		4-х факторная модель (указать модель)		3-х факторная модель (указать модель)		2-х факторная модель (указать модель)
		Прогнозное значение показателя	Средняя ошибка аппроксимации, %	Прогнозное значение показателя	Средняя ошибка аппроксимации, %	Прогнозное значение показателя	Средняя ошибка аппроксимации, %	Прогнозное значение показателя	Средняя ошибка аппроксимации, %
1	2	3	4	5	6	7	8	9	10
			= (гр.3- гр.2) / гр.2*100		= (гр.5- гр.2) / гр.2*100		= (гр.7- гр.2) / гр.2*100		= (гр.9- гр.2) / гр.2*100
За год	S	S		S		S		S
		а = … b1 = … b2 = … b3 = … b4 = … b5 = …		а = … b1 = … b2 = … b3 = … b4 = …		а = … b1 = … b2 = … b3 = …		а = … b1 = … b2 = …

b₁, b₂, b₃, b₄,…, b_n – коэффициенты регрессии; показывают, на сколько единиц изменится прогнозируемый показатель y при изменении фактора на единицу.

 

Для этого необходимо:

1) Определить параметры уравнения регрессии с помощью функции «Регрессия», согласно методике, приведенной в Задании 2.

· в качестве «Входного интервала Y» выделить соответствующие значения (исходя из задания) результирующего признака. В качестве «Входного интервала Х» выделить весь диапазон факторных признаков (х₁, х₂, х₃, …, х_n) аналогично диапазону Y;

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

2) Оценить тесноту связи с помощью показателей корреляции и детерминации по полученной модели с помощью анализа таблиц, полученных после выполнения функции «Регрессия».

3) Расчет матрицы парных коэффициентов корреляции.

Значения коэффициента парной корреляции используют для анализа тесноты взаимосвязи между переменными x и y. Для их определения:

a)

в главном меню программы последовательно выберите пункты: «Сервис Þ Анализ данных Þ Корреляция». Щелкните по кнопке «ОК» (Рис. 4);

Рис. 4. Диалоговое окно ввода параметров инструмента
«Корреляция»

b) заполните диалоговое окно ввода данных и параметров вывода:

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

· в строке «Группировка» диалогового окна отметить, поставив метку напротив строки «по столбцам», что данные находятся в столбцах;

· вывести матрицу коэффициентов парной корреляции на новый лист; результаты корреляционного анализа будут представлены в виде.

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

Для проверки факторов на мультиколлинеарность осуществляется последовательная проверка коэффициентов парной корреляции с условием: 0,7. Где – значение коэффициента парной корреляции, находящегося на пересечении i–той строки и j-того столбца.

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

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

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

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