Проверить выполнение предпосылок МНК (оценить адекватность модели).

Для оценки адекватности модели исследуют остатки .Исследование остатков предполагает проверку наличия у них следующих пяти свойств (предпосылок МНК).

а. Нулевая (или близкая к ней) средняя величина остатка.

б. Случайный характер остатка.

в. Независимость (отсутствие автокорреляции) остатков.

г. Соответствие ряда остатков нормальному закону распределения.

д. Гомоскедастичность (постоянство) дисперсии остатков.

a.Для вычисления среднего значения остатка используем функцию СРЗНАЧ (Приложение 5).В данной задаче , поэтому первое свойство остатков выполняется.

б. Для проверки случайности остатков используем критерий поворотных точек. Анализируя построенный график остатков делаем вывод, что в этой задаче число поворотных точек р=8. В случайном ряду чисел должно выполняться строгое неравенство при n=10. Так как фактическое количество поворотных точек p=8 и , то свойство случайности остатков выполняется.

в.При проверке независимости (отсутствия автокорреляции) используется коэффициент автокорреляции .Для расчета автокорреляции используется стандартная функция КОРРЕЛ(С25-С33;С26-С34)= -0,5349 (Приложение 6).Оценим значимость полученного коэффициента автокорреляции с использованием t-критерия.Наблюдаемое значение t-критерия: =1,79 (расчет выполнен средствами Excel).Табличное значение t-статистики определяется с помощью функции СТЬЮДРАСПОБР(0,05;8)=2,306 (Приложение 7).Поскольку наблюдаемое значение t-критерия меньше табличного, то коэффициент автокорреляции незначим, т.е. остатки не автокоррелированы. Свойство независимости остатков выполняется.

г. Соответствие ряда остатков нормальному закону распределения определяется при помощи R/S-критерия: (Приложение 8).Полученное значение этого критерия попадает между табулированными границами (2,67-3,57) с заданным уровнем значимости ( ) и n=10, таким образом, свойство нормальности остатков выполняется.

д.Для оценки нарушения гомоскедастичности по тесту Гольдфельда-Квандта необходимо выполнение следующих шагов:

д.1.Упорядочениеn наблюдений по мере возрастания переменной x (Приложение 9);

д.2.Разделение совокупности данных на две группы соответственно с малыми и большими значениями фактора x, построение по каждой из групп уравнения регрессии. Разделение на две группы по фактору xимеет вид:

y,млн. руб	x,млн. руб.		y,млн. руб	x, млн. руб.
26,00	15,00		51,00	33,00
28,00	18,00		63,00	41,00
33,00	19,00		61,00	41,00
43,00	26,00		62,00	42,00
43,00	27,00		67,00	44,00

Выполнив в Excel функцию РЕГРЕССИЯ для каждой группы, получим уравнения регрессии:

д.3.Определение остаточной суммы квадратов для первой и второй регрессий. С помощью функции СУММКВ получим: для первой регрессии СУММКВ=7,663, для второй – 4,596.

д.4. Расчет значения F-статистики: 7,663/4,596=1,667.

д.5. Определение табличного значения F-статистики, которое производится при помощи функции FРАСПОБР (Приложение 10). , где =0,1. =5, m=2, n=10. . Значение F-расчетного меньше F-табличного. Это свидетельствует о том, что гетероскедастичность не обнаружена и, следовательно, выполняются свойства гомоскедастичности остатков.

Оценка адекватности модели выполнена. Проведенный анализ показывает, что построенная модель является адекватной реальному процессу, её можно использовать для построения прогнозных оценок.

4. Осуществить проверку значимости параметров уравнения регрессии с помощью t-критерия Стьюдента ( ).

Для оценки статистической значимости параметров модели используем t-критерий. Расчетное значение t-статистики определяется по формулам (Приложение 11): ; .

Табличное значение t-критерия можно найти с помощью функции СТЬЮДРАСПОБР(0,05;8)=2,306 (Приложение 12).Поскольку , то параметр а₀ статистически значим.Поскольку, , то параметр a₁ статистически значим, существенно отличается от 0.

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

Коэффициент детерминации можно, например, определить по формуле: (Приложение 13).

Таким образом, все вариации в объеме выпуска продукции y на 99,3% обусловлены вариацией в объеме капиталовложений x, т.е. изменениями фактораx, учтенного в модели.Соответственно, все изменения в y на 0,7 % обусловлены изменениями факторов, неучтенных в модели.

Оценка значимости уравнения регрессии проводится с помощью F-критерия. Расчетное значение F-критерия в нашем случае определяется по формуле:

.

Табличное значение F-критерия определяется с помощью функции FРАСП (Приложение 14). . Поскольку расчетное значение F-критерия Фишера больше табличного, то уравнение регрессии признается значимым, соответствующим фактическим данным.

Находим среднюю относительную ошибку аппроксимации (Приложение 15):

2,4%.

Таким образом, модельные значения отклоняются от фактических значений y в среднем на 2,4%– получена модель хорошего качества,высокой точности.

6. Осуществить прогнозирование среднего значения показателя y при уровне значимости , если прогнозное значение фактора x составит 80% от его максимально значения.

; .

Коэффициент Стьюдента для 8 степеней свободы и на уровне значимости рассчитывается при помощи функции СТЬЮДРАСПОБР(0,1;8)=1,8595.

Отклонение от линии регрессии рассчитывается по формуле: , где –cтандартная ошибка.

Вычислив величину отклонения от линии регрессии, можно найти доверительный интервал, в котором ожидается появление прогнозируемого среднего значения y=54,05. Область задаётся интервалом . Интервал в данной задаче: (51,386;56,697).(Приложение 16).

7. Представить графически: фактические и модельные значения y, точки прогноза.

Строим график «Линейная регрессия», для этого скопируем в лист с вычислениями прогнозируемых значений график подбора с листа «Регрессия y». Соединим точки графика отрезками (активировать курсором точки – тип данных – отрезки).

Переименовываем график подбора в «Линейную регрессию». К существующим данным добавляем новые (Исходные данные – Ряд – Добавить): для точечного прогноза, нижней и верхней границ прогноза, указывая соответствующие данные (Приложение 17).

8. Составить уравнения гиперболической (а), степенной (б), показательной (в) регрессийи построить их графики.

а) Гиперболическая модельрегрессии имеет вид: .

Приведем эту модель к линейному виду, осуществив замену переменных . Получим линейное уравнение . С помощью функции РЕГРЕССИЯ находим значения коэффициентовa₀=83,340 и a₁=-947,945 и, соответственно,уравнение . Т.е. модель гиперболической регрессии имеет вид .

Строим вспомогательные таблицы и с помощью приложения: Мастер диаграмм, строим модель гиперболической регрессии в MS Excel.

б) Степенная модель регрессии имеет вид: .

Осуществим линеаризацию модели. Прологарифмируем исходное уравнение и получим . Полагая , получим линейную модель .С помощью функции MSExcel составим дополнительные таблицы для расчета и используем функцию РЕГРЕССИЯ для нахождения параметров a₀ и a₁. ПолучимА=0,376, a₀=2,377, a₁=0,879. Окончательно модель степенной регрессии принимает вид .

Найдем теоретическое значение y, построим график степенной регрессии при использовании приложения: Мастер диаграмм.

в)Показательная модель регрессии имеет вид: .

Прологарифмируем уравнение регрессии, тогда получим . Полагая , получим .

Найдем значение .С помощью функции РЕГРЕССИЯ найдем параметрыА=1,2404, a₁=0,0135. Тогда модель показательной регрессии примет вид

Найдем теоретическое значение y.Построим график показательной регрессии при использовании приложения Мастер диаграмм.