Задача
По предприятиям легкой промышленности региона получена информация, характеризующая зависимость объема выпуска продукции (Y, млн. руб.) от объема капиталовложения (Х, млн. руб.)
табл. 1
Требуется:
1. Найти параметры уравнения линейной регрессии, дать экономическую интерпретацию коэффициента регрессии.
Построим линейную модель Yт = а + b*Х
Для удобства выполнения расчетов предварительно упорядочим всю таблицу исходных данных по возрастанию факторной переменной Х (Данные - Сортировка)

Используем программу регрессия и найдем коэффициенты модели


Коэффициенты модели содержаться в таблице 4 (столбец Коэффициенты).
Модель построена, и ее уравнение имеет вид: Yт = 12,57 + 1,32*Х
Коэффициент регрессии b = 1,32, следовательно, при увеличении объема выпуска продукции Y) на 1млн. руб., объем капиталовложения (Х) увеличивается в среднем на 1,32.
Коэффициент смещения равенства а =12,57, не имеет экономического смысла.
2. Вычислить остатки; найти остаточную сумму квадратов; оценить дисперсию остатков S²?; построить график остатков.
Остатки модели Еi = yi – утi содержаться в столбце Остатки итогов программы РЕГРЕССИЯ (табл. 5).
Программой РЕГРЕССИЯ найдены также остаточная сумма квадратов SSост = 76,97 и дисперсия остатков MSост = 9,62 (табл. 3).
Для построения графика остатков нужно выполнить следующие действия:
1. Вызвать Мастер диаграмм, выбрать тип диаграммы Точечная (с соединенными точками).
2. Для указания данных для построения диаграммы зайти во вкладку Ряд, нажать кнопку Добавить; в качестве значений Х указать исходные данные Х (табл. 1); значения Y– остатки (табл.5).

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

3. Проверить выполнение предпосылок МНК.
Предпосылками построения классической линейной регрессионной модели являются четыре условия, известные как условия Гаусса-Маркова.
1. В уравнении линейной модели Y= а + b*Х +? слагаемое ? – случайная величина, которая выражает случайный характер результирующей переменной Y.
2. Математическое ожидание случайного члена в любом наблюдении равно нулю, а дисперсия постоянна.
3. Случайные члены для любых двух разных наблюдений независимы (некоррелированы).
4. Распределение случайного члена является нормальным.
1). Проведем проверку случайности остаточной компоненты по критерию поворотных точек.
Количество поворотных точек определим по графику остатков: р = 6
EMBED Excel.Chart.8 \s
Вычислим критическое значение по формуле
Ркр = EMBED Equation.3 . При n = 10 найдем ркр = [2,97] = 2
Сравним р = 6 > ркр = 2, следовательно, свойство случайности для ряда остатков выполняется.
2). Равенство нулю математического ожидания остаточной компоненты для линейной модели, коэффициенты которой определены по методу наименьших квадратов, выполняется автоматически.
С помощью функции СРЗНАЧ для ряда остатков можно проверить: Еср=0
Свойство постоянства дисперсии остаточной компоненты проверим по критерию Голдфельда-Квандта.
В упорядоченных по возрастанию переменной Х исходных данных (n = 10) выделим первые 4 и последние 4 уровня, средние 2 уровня не рассматриваем.
С помощью программы РЕГРЕССИЯ построим модель по первым четырем наблюдениям (регрессия-1) и по последним четырем наблюдениям (регрессия-2).

Остаточная сумма квадратов SS1 = 14,5
Остаточная сумма квадратов SS2 = 9,638
Рассчитаем статистику критерия: EMBED Equation.3
Критическое значение при уровне значимости ? = 5% и числах степеней свободы к1 = к2 = 2 составляет Fкр = 19,00002644. Вычисляем с помощью функции FРАСПОБР.
Сравним F = 1,50 < Fкр = 19,00002644, следовательно, свойство постоянства дисперсии остатков выполняется, модель гомоскедастичная.
3) Для проверки независимости уровней ряда остатков используем критерий Дарбина-Уотсона
EMBED Equation.3
Предварительно по столбцу остатков с помощью функции СУММКВРАЗН определим EMBED Equation.3

EMBED Equation.3 = 176,8894116
Используем найденную программой РЕГРЕССИЯ сумму квадратов остаточной компоненты SSост = 76,9667686. Таким образом,
EMBED Equation.3
Полученное значение d = 2,298 > 2, что свидетельствует об отрицательной корреляции. Перейдем к d' = 4 – d = 4 – 2,298 = 1,702 и сравним ее с двумя критическими уровнями d1 = 0,88 и d2 = 1,32 , которые определяются по таблице d-статистик Дарбина-Уотсона.
d' = 1,702 лежит в интервале от d2 = 1,32 до 2, следовательно свойство независимости остаточной компоненты выполняется.
Проверим выполнение свойства независимости ряда остатков по первому коэффициенту автокорреляции:
EMBED Equation.3
С помощью функции СУММПРОИЗВ найдем для остатков EMBED Equation.3

Следовательно, EMBED Equation.3
Критическое значение для коэффициента автокорреляции определяется как отношение EMBED Equation.3
|r(1)| = 0,18 < rкр = 0,62, следовательно, ряд остатков некоррелирован.
4) Соответствие ряда остатков нормальному закону распределения проверим с помощью R/S – критерия.
EMBED Equation.3
С помощью функции МАКС и МИН для ряда остатков определим EMBED Equation.3 , EMBED Equation.3 . Стандартная ошибка модели найдена программой РЕГРЕССИ и составляет EMBED Equation.3
EMBED Equation.3
Критический интервал определяется по таблице критических границ отношения R/S и при n = 10 составляет (2,67; 3,69)
3,20 € (2,67; 3,69), значит, для построенной модели свойство нормального распределения остаточной компоненты выполняется.
Проведенная проверка предпосылок регрессионного анализа показала, что для модели выполняются все условия Гаусса-Маркова.
4. Осуществить проверку значимости параметров уравнения регрессии с помощью t – критерия Стьюдента (? = 0,05).
t – статистика для коэффициентов уравнения регрессии приведены в табл. 4.
Для свободного коэффициента ? = 12,57 определена статистика t(?) = 2,48
Для свободного коэффициента регрессии b = 1.32 определена статистика t(b) = 9.42
Критическое значение tкр = 2,31 найдено для уровня значимости ? = 5% и числа степеней свободы ? = 10-1-1=8 (Функция СТЬЮДРАСПОБР).
Сравнение показывает:
|t(?)| = 2,48 > tкр =2,31, свободный коэффициент ? является значимым
|t(b)| = 9.42 > tкр =2,31, коэффициент регрессии b является значимым.
5. Вычислить коэффициент детерминации, проверить значимость уравнения регрессии c помощью F- критерия Фишера (? = 0,05), найти среднюю относительную ошибку аппроксимации. Сделать вывод о качестве модели.
Коэффициент детерминации R – квадрат определен программой РЕГРЕССИЯ (табл. 2) и составляет R² = 0.917 = 91.7%
Проверим значимость полученного уравнения с помощью F – критерия Фишера. F – статистика определена программой РЕГРЕССИЯ (табл. 3) и составляет F = 88,71.
Критическое значение Fкр = 5,32 найдено для уровня значимости ? = 5% и чисел степеней свободы ?1 = 1 ,?2 = 8 (функция FРАСПОБР).
Сравнение показывает: F = 88,71 > Fкр = 5,32; следовательно, уравнение модели является значимым, его использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенной в модель факторной переменной X.
Для вычисления средней относительной ошибки аппроксимации дополним таблицу столбцом относительных погрешностей, которые вычислим по формуле EMBED Equation.3 с помощью функции ABS
табл. 6
По столбцу относительных погрешностей найдем среднее значение EMBED Equation.3
3,65 < 5% - модель точная.
Вывод: на основании проверки предпосылок МНК, критериев Стьюдента и Фишера и величины коэффициента детерминации модель можно считать адекватной. Модель является точной, - использовать такую модель для прогнозирования в реальных условиях вполне целесообразно.
6. Осуществить прогнозирование среднего значения показателя Y при уровне значимости ? = 0,1, если прогнозное значение фактора Х увеличится на 80% от его максимального значения.
Прогнозное значение факторной переменной Х составляет:
EMBED Equation.3 = EMBED Equation.3
EMBED Equation.3
Таким образом, если объем выпуска продукции составит 36,8 млн. руб., то объем капиталовложений будет примерно 61,146 млн. руб.
Построим доверительный прогнозный интервал для среднего значения Y. Для этого нужно рассчитать стандартную ошибку прогнозирования:
EMBED Equation.3
Стандартная ошибка модели Se = 3.10 (табл. 2)
По столбцу исходных данных Х найдем среднее значение EMBED Equation.3 и определим EMBED Equation.3 (функция КВАДРОТКЛ).
EMBED Equation.3
При tкр = 1,86 размах доверительного интервала для среднего значения
EMBED Equation.3
Границами прогнозного интервала будут:
EMBED Equation.3
EMBED Equation.3
Таким образом, можно утверждать, что если объем выпуска продукции составит 36,8 млн. руб., то ожидаемый средний объем капиталовложений будет от 59,296 млн. руб. до 62,996 млн. руб.
7. Представить графически: фактические и модельные значения Y точки прогноза.
Для построения чертежа используем Мастер диаграмм (точечная) – покажем исходные данные.
Покажем на графике результаты прогнозирования. С помощью опции Добавить линию тренда построим линию модели.
EMBED Excel.Chart.8 \s
8. Составить уравнения нелинейной регрессии: гиперболической, степенной, показательной. Привести графики построенных уравнений регрессии.
Гиперболическая модель EMBED Equation.3 не является стандартной. Для ее построения выполним линеаризацию: обозначим EMBED Equation.3 и получим вспомогательную модель EMBED Equation.3 . Вспомогательная модель является линейной. Ее можно построить с помощью программы РЕГРЕССИЯ, предварительно подготовив исходные данные: столбец значений yi (остается без изменений) и столбец значений EMBED Equation.3 (табл. 7).
табл. 7
С помощью программы РЕГРЕССИЯ получим:

а = 105,43: b = -1569,01, следовательно, уравнение гиперболической модели EMBED Equation.3
Покажем линию гиперболической модели на графике.
EMBED Excel.Chart.8 \s
Степенная модель EMBED Equation.3 является стандартной. Для ее построения используем Мастер диаграмм: исходные данные покажем помощью точечной диаграммы, затем добавим линию степенного тренда и выведем на диаграмму уравнение модели.
EMBED Excel.Chart.8 \s
Уравнение степенной модели EMBED Equation.3
Показательная модель тоже стандартная (экспоненциальная).
EMBED Excel.Chart.8 \s
Можно вычислить EMBED Equation.3 (функция EXP), тогда уравнение показательной модели EMBED Equation.3 .
9.Для указанных моделей найти коэффициенты детерминации, коэффициенты эластичности средние относительные ошибки аппроксимации. Сравнить модели по этим характеристиками и сделать вывод.
Заполним для каждой модели расчетную таблицу, в которую занесем теоретические значения EMBED Equation.3 , найденные по соответствующему уравнению для каждого уровня исходных данных EMBED Equation.3 ; ошибки модели и относительные погрешности EMBED Equation.3 (таблицы 8-10).

Составим сводную таблицу характеристик качества построенных моделей:
Столбец средних относительных погрешностей показывает, что наиболее точной является гиперболическая модель, ее погрешность – наименьшая. Точность гиперболической модели точная. (3,331 < 5%).
По величине индекса детерминации лучшая модель – гиперболическая. R² = 93,6%, таким образом, вариация объема капиталовложений на 93,6% объясняется по уравнению гиперболической модели вариацией объема выпуска продукции.
Для нелинейных моделей EMBED Equation.3 коэффициенты эластичности определяются соотношением EMBED Equation.3 , согласно которому:
для степенной модели EMBED Equation.3 коэффициент эластичности Э = b и представляет собой постоянную величину;
для показательной модели EMBED Equation.3 коэффициент эластичности Э(х) = x * lnb и зависит от значений фактора Х;
для гиперболической модели EMBED Equation.3 коэффициент эластичности EMBED Equation.3 и также зависит от значения фактора Х.
Для построенной степенной модели EMBED Equation.3 получим Э = 0,7997. Следовательно, этой модели увеличение объема выпуска продукции на 1% приводит к увеличению среднего объема капиталовложений на 0,7997%.
Для показательной и гиперболической моделей результаты расчета коэффициентов эластичности приведены в таблице:
Согласно показательной модели увеличение объема выпуска продукции на 1% приводит к росту среднего объема капиталовложений на величину от 0,61% до 1,05%.
Согласно гиперболической модели при увеличении объема выпуска продукции на 1% происходит рост среднего объема капиталовложений в пределах от -1,23% до -0,48%.