Задача 1
По предприятиям легкой промышленности региона получена информация, характеризующая зависимость объема выпуска продукции (Y, млн.руб.) от объема капиталовложений (Х, млн.руб.)
Таблица 1.
Требуется:
Найти параметры уравнения линейной регрессии, дать экономическую интерпретацию коэффициента регрессии.
Вычислить остатки; найти остаточную сумму квадратов; оценить дисперсию остатков EMBED Equation.3 ; построить график остатков.
Проверить выполнение предпосылок МНК.
Осуществить проверку значимости параметров уравнения регрессии с помощью t-критерия Стьюдента (?=0,05).
Вычислить коэффициент детерминации, проверить значимость уравнения регрессии с помощью F-критерия Фишера (?=0,05), найти среднюю относительную ошибку аппроксимации. Сделать вывод о качестве.
Осуществить прогнозирование среднего значения показателя Y при уровне значимости ?=0,01 при Х=80% от его максимального значения.
Представить графически фактических и модельных значений Y, точки прогноза.
Составить уравнения нелинейной регрессии:
Гиперболической;
Степенной;
Показательной.
Привести графики построенных уравнений регрессии.
9. Для указанных моделей найти коэффициенты детерминации, коэффициенты эластичности и средние относительные ошибки аппроксимации. Сравнить модели по этим характеристикам. Сделать вывод.
Решение
Параметры уравнения линейной регрессии.
Уравнение линейной регрессии имеет вид: Y = ?0+?1*X
Используя инструментарий Excel, рассчитаем параметры линейной модели.
Выберем команду “Сервис”, далее – “Анализ данных”.
В диалоговом окне выберем инструмент “Регрессия”.
В диалоговом окне “Регрессия” в поле “Входной интервал Y” введем диапазон ячеек, содержащих исходные данные Y(x); в поле “Входной интервал X” введем диапазон ячеек, содержащих исходные данные факторов X; выделим также заголовки столбцов и установим флажок “Метки”; в поле “Остатки” поставим флажок.

Рис1.
Результаты вычислений представлены в таблицах 2-5:

Таблица 2.
Таблица 3.

Таблица 4.
Таблица 5.
Коэффициенты модели содержатся в таблице 4 (столбец Коэффициенты). Таким образом, модель построена, и ее уравнение имеет вид:
Y = 12.24 + 0.91*X
Коэффициент регрессии ?1 показывает, что с ростом объема капвложений (Х) на 1 млн.руб. выпуск продукции (У) вырастет на 910 тыс.руб.
2. Вычислить остатки; найти остаточную сумму квадратов; оценить дисперсию остатков EMBED Equation.3 ; построить график остатков.
Остатки модели Ei = yi-yTi содержатся в столбце Остатки программы РЕГРЕССИЯ (таблица 5).
Программой РЕГРЕССИЯ найдены также остаточная сумма квадратов SSост = 12,02 и дисперсия остатков MSост = 1,51 (таблица 3).
Для построения графика остатков нужно выполнить следующие действия:
Вызвать Мастер диаграмм, выбрать тип диаграммы Точечная (с соединенными точками).
Для указания данных для построения диаграммы зайдем во вкладку Ряд, нажмем кнопку Добавить; в качестве значений Х укажем исходные данные Х таблица 1); значения Y – остатки (таблица 5).

Рис2.
В результате получим график остатков:

Рис 3.
3. Проверить выполнение предпосылок МНК.
1) Проведем проверку случайности остаточной компоненты по критерию поворотных точек.
Количество поворотных точек определим по графику остатков: р=9.(рис. 3).
Вычислим критическое значение по формуле, при n = 10.
EMBED Equation.3
EMBED Equation.3
EMBED Equation.3
Неравенство EMBED Equation.3 выполняется (9 >2), следовательно, свойство случайности ряда остатков выполняется.
2) Свойство постоянства дисперсии остаточной компоненты проверим по критерию Голдфельда-Квандта.
Упорядочим всю таблицу исходных данных по возрастанию факторной переменной Х (Данные => Сортировка).
В переменной X исходных данных ( EMBED Equation.3 ) выделим первые 5 и последние 5 уровня.
С помощью программы РЕГРЕССИЯ построим модель по первым пяти наблюдениям (регрессия-1), для этой модели остаточная сумма квадратов EMBED Equation.3 .
С помощью программы РЕГРЕССИЯ построим модель по последним пяти
наблюдениям (регрессия-2), для этой модели остаточная сумма квадратов
SS1 = 0,88.
Рассчитаем статистику критерия: EMBED Equation.3
Критическое значение при уровне значимости EMBED Equation.3 и числах степеней свободы k1=k2=5-1-1=3 составляет EMBED Equation.3 . (Функция FРАСПОБР).(рис. 5).

Рис 4.
Схема критерия:

Сравним F=3,7<Fкр=10,13, следовательно, свойство постоянства дисперсии остатков выполняется, модель гомоскедастичная.
3) Соответствие ряда остатков нормальному закону распределения проверим с помощью R/S - критерия
EMBED Equation.3
С помощью функции МАКС и МИН для ряда остатков определим EMBED Equation.3 =1,581; EMBED Equation.3 =-2,057. Стандартная ошибка модели найдена программой РЕГРЕССИЯ и составляет SE = 1,226 (таблица 2).

Рис 5.
Тогда R/S = 2,968
Критический интервал определяется по таблице критических границ отношения R/S и при n = 10 составляет (2,67; 3,57)
2,968 входит в (2,67; 3,57), значит, для построенной модели свойство нормального распределения остаточной компоненты выполняется.
Проведенная проверка предпосылок регрессионного анализа показала, что для модели выполняются все условия Гаусса-Маркова, т. е. данная модель является классической нормальной регрессионной моделью.
4. Осуществить проверку значимости параметров уравнения регрессии с помощью t-критерия Стьюдента (?=0,05).
t - статистики для коэффициентов уравнения регрессии приведены в таблице 4. Для свободного коэффициента a0 =12,24 определена статистика t(a0) = 11,41. Для коэффициента регрессии a1 = 0,91, определена статистика t(a1) = 21,34.
Критическое значение tкр = 2,306 найдено для уровня значимости ? = 5% и числа степеней свободы k = 10-1-1 = 8 (рис. 6. Функция СТЬЮДРАСПОБР).

Рис 6.
Схема критерия:


Сравнение показывает:
|t(a0) = 11,41| > tкр = 2,306, следовательно, свободный коэффициент а является значимым.
|t(а1) = 21,34| > tкр = 2,306, следовательно, коэффициент регрессии b является значимым, его и объем капиталовложений нужно сохранить в модели.
5. Вычислить коэффициент детерминации, проверить значимость уравнения регрессии с помощью F- критерия Фишера (?=0,05), найти среднюю относительную ошибку аппроксимации. Сделать вывод о качестве модели.
Коэффициент детерминации R-квадрат определен программой РЕГРЕССИЯ (таблица 2). И составляет R2 = 0,983 = 98,3%.
Таким образом, вариация (изменение) объема выпуска продукции (Y) на 98,3% объясняется по полученному уравнению вариацией объема капиталовложений (X).
Проверим значимость полученного уравнения с помощью F - критерия Фишера.
F - статистика определена программой РЕГРЕССИЯ (таблица 3) и составляет F = 455,50.
Критическое значение Fкр = 5,318 найдено для уровня значимости ? = 5% и чисел степеней свободы k1 = 1, k = 8 (рис. 7. Функция FРАСПОБР).

Рис 7.
Сравнение показывает: F = 455,50 > Fкр = 5,318; следовательно, уравнение модели является значимым, его использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенной в модель факторной переменной Х.
Для вычисления средней относительной ошибки аппроксимации рассчитаем дополнительный столбец относительных погрешностей, которые вычислим по формуле EMBED Equation.3 с помощью функции ABS (Рис 8).

Рис 8.
По столбцу относительных погрешностей найдем среднее значение EMBED Equation.3 (функция СРЗНАЧ).
Схема проверки:

Сравним: 3,19% < 5%, следовательно, модель является точной.
Вывод: на основании проверки предпосылок МНК, критериев Стьюдента и Фишера и величины коэффициента детерминации модель можно считать полностью адекватной. Дальнейшее использование такой модели для прогнозирования в реальных условиях целесообразно.
6. Осуществить прогнозирование среднего значения показателя Y при уровне значимости EMBED Equation.3 , если прогнозное значение фактора X = 80% от его максимального значения.
Рассчитаем по уравнению модели прогнозное значение показателя У (расчеты представлены на рис 9):
Упр=а0+а2*Хпр= 12,24+0,91*Хпр

Рис 9.
Таким образом, если объем капиталовложений составит 31,2 млн. руб., то ожидаемый объем выпуска продукции составит около 40,60 млн. руб.
Зададим доверительную вероятность EMBED Equation.3 и построим доверительный прогнозный интервал для среднего значения Y.
Для этого нужно рассчитать стандартную ошибку прогнозирования:
EMBED Equation.3
Предварительно подготовим:
- стандартную ошибку модели EMBED Equation.3 (Таблица 2);
- по столбцу исходных данных Х найдем среднее значение EMBED Equation.3 (функция СРЗНАЧ) и определим EMBED Equation.3 (функция КВАДРОТКЛ).
Следовательно, стандартная ошибка прогнозирования для среднего значения составляет:
EMBED Equation.3
При EMBED Equation.3 размах доверительного интервала для среднего значения
EMBED Equation.3
Границами прогнозного интервала будут
EMBED Equation.3
EMBED Equation.3
Таким образом, с надежностью 90% можно утверждать, что если объем капиталовложений составит 31,2 млн. руб., то ожидаемый объем выпуска продукции будет от 39,65 млн. руб. до 41,55 млн. руб.
Представить графически фактические и модельные значения Y точки прогноза.
Для построения графика используем Мастер диаграмм (точечная) – покажем исходные данные (поле корреляции).
Покажем на графике результаты прогнозирования. Для этого в опции Исходные данные добавим ряды:
Имя > прогноз; значения EMBED Equation.3 ; значения EMBED Equation.3 ;
Имя > нижняя граница; значения EMBED Equation.3 ; значения EMBED Equation.3 ;
Имя > верхняя граница; значения EMBED Equation.3 ; значения EMBED Equation.3

Рис 10.
8. Составить уравнения нелинейной регрессии: гиперболической; степенной; показательной.
Гиперболическая модель EMBED Equation.3 не является стандартной.
Для ее построения выполним линеаризацию: обозначим EMBED Equation.3 и получим вспомогательную модель EMBED Equation.3 . Вспомогательная модель является линейной. Ее можно построить с помощью программы РЕГРЕССИЯ, предварительно подготовив исходные данные: столбец значений EMBED Equation.3 (остается без изменений) и столбец преобразованных значений EMBED Equation.3 (таблица 6).
Таблица 6

С помощью программы РЕГРЕССИЯ получим:
Таким образом, EMBED Equation.3 ; а1=-415,749, следовательно, уравнение гиперболической модели EMBED Equation.3 .
С помощью полученного уравнения рассчитаем теоретические значения EMBED Equation.3 для каждого уровня исходных данных EMBED Equation.3 .
Покажем линию гиперболической модели на графике. Для этого добавим к ряду исходных данных EMBED Equation.3 , ряд теоретических значений EMBED Equation.3 .

Степенная модель EMBED Equation.3 является стандартной. Для ее построения используем Мастер диаграмм: исходные данные покажем с помощью точечной диаграммы, затем добавим линию степенного тренда и выведем на диаграмму уравнение модели.
EMBED Excel.Chart.8 \s
Таким образом, уравнение степенной модели. EMBED Equation.3
Показательная модель EMBED Equation.3 тоже стандартная (экспоненциальная).
Построим ее с помощью Мастера диаграмм.
EMBED Excel.Chart.8 \s
Можно вычислить EMBED Equation.3 (функция EXP), тогда уравнение показательной модели EMBED Equation.3