Практическая работа «Эконометрические модели в Пакете анализа EXCEL»

ЗАДАНИЕ 11 Эконометрические модели в Пакете анализа EXCEL: Эконометрическая модель, учитывая корреляционные связи, позволяет путем   подбора   аналитической   зависимости   построить   модель   на   базисном периоде   и   при   достаточной   адекватности   модели   использовать   ее   для краткосрочного прогноза. а) загрузить   надстройку   пакет   анализа  EXCEL,  зайти   в  ПАРАМЕТРЫ Excel,  выбрать   НАДСТРОЙКИ,   щелкнуть   снизу   окна   кнопку   ПЕРЕЙТИ   к надстройкам, пометить Пакет анализа, Ок: б) исходные данные для анализа могут быть получены в результате ряда опытов или из сравнения подобных объектов, количество опытов должно быть как минимум на один больше количества факторов Х объекта или процесса; в) заполнить ячейки EXCEL исходными данными; B A №пп 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 C х1 43 70 32 66 94 77 88 54 54 44 56 45 80 30 D х2 3,93 1,06 5,81 4,19 4,86 3,62 3,64 1,99 4,5 2,18 1,77 3,27 3,24 0,63 E х3 19852 27809 20246 11504 13997 13489 17153 15815 21829 12508 16851 18640 31103 18650 F х4 13 16 24 18 12 17 24 15 25 14 24 13 31 15 G х5 3320 1041 5060 4195 4489 3349 3046 1650 3528 2018 1776 2837 2850 1430 I H х6 241 179 226 126 201 249 159 187 125 310 210 117 197 123 J Y 10 15 20 40 12 24 14 26 22 34 35 16 18 13 г) найти среднее значение всех факторов Х, позволяющее в дальнейшем использовать   исследование   в   соответствии   с   понятием   коэффициент эластичности (меню ГЛАВНАЯ, подменю Редактирование, в списке Другие функции найти функцию =СРЗНАЧ() и указать аргументом диапазон колонки чисел, затем скопировать функцию на все колонки): 59,5 3,2 18531,9 18,6 2899,2 189,3 21,4 д) зайти в пункт меню ДАННЫЕ, выбрать Анализ данных в подменю  Анализ, выбрать РЕГРЕССИЯ, подтвердить Ок; Анализ данным Инструменты анализа Гистограмма Скользящее среднее Генерация случайных чисел Ранг и персентиль Регрессия 1 ОК Отмена Справка П6Т Выборка Парный двухвыборочный t­тест для средних Двухвыборочный t­тест с одинаковыми дисперсиями  Двухвыборочный t­тест с различными дисперсиями Двухвыборочный z­тест для средних е) настроить параметры окна регрессии: входной интервал  Y  ­ указать выделением   мышью   диапазон  Y,  входной   интервал   Х   указать   выделением мышью все числовые значения Х, Параметры вывода можно оставить Новый рабочий лист или щелкнуть на свободное место текущего листа, Ок: ж) получить   коэффициенты   уравнения   регрессии   и   коэффициент корреляции. Регрессионная статистика Множеств. R R-квадрат Y-пересечение Переменная X 1 Переменная X 2 Переменная X 3 Переменная X 4 Переменная X 5 Переменная X 6 0,74 0,55 Коэфф. Ст. ошибка 34,21992 18,15065 0,121476 -0,06031 7,520502 -1,77084 -0,00143 0,000561 0,509319 1,089928 0,009388 -0,00035 0,018546 0,044022 t­статистика P­Значение 0,101369 0,634741 0,820585 0,038495 0,069637 0,971421 0,68618 1,885327457 -0,496514896 -0,235468864 -2,543007893 2,139973612 -0,037125732 0,42128646 Уравнение регрессии при этом выглядит следующим образом: Y=34,2199  ­0,0603x1­1,77084x2­0,00143x3+1,089928x4­0,00035x5+0,01854x6 з)   подставляя   в   полученное   уравнение   числовые   значения   факторов можно предсказать дальнейшее изменение определяемого параметра Y. и)   используя,   выведенные   уравнения   регрессии,   получить   значения выходного параметра для всех опытов;  выбрать ячейку К2 и занести формулу: =34,22­0,06*C2­ ■ 1,77*D2­0,00143*E2+1,09*F2­0,00035*G2+0,01854*H2 ■ ■  скопировать формулу на все опыты по столбцу; J2:K15 и построить график; выделить диапазон  Регрессия 45 ------------------------------ “ К л 5 ------------------------------------------------------------------------------------------ 0 ----------------------------------------------------------------------------- 10 11 7 3 5 6 9 1 2 3 4 12 13 14 —♦—Данные У —■— У от 6 факторов Некоторые   факторы   демонстрируют   плохую   корреляцию   с определяемым параметром, поэтому по таблице Стьюдента с вычисленным уровнем   значимости   и   известным   количеством   опытов   можно   найти критическое   значение   параметра  t­статистика  и  исключить   некоторые   из факторов.   Проверка   значимости   модели   регрессии   также   может   быть проведена с использованием F­критерия Фишера. Значимость факторов может также продемонстрировать коэффициент эластичности. к)   вычислить   коэффициенты   эластичности   каждого   из   факторов. Коэффициент   эластичности   показывает,   на   сколько   процентов   в   среднем изменяется результативный признак У, при изменении факторного признака Х на 1%. Коэффициент эластичности находится по формуле: Е = Кх* XCp/YCp где   Кх  ­   коэффициент   уравнения   регрессии   при   факторе,   Хср  и   Уср средние   значения   фактора   и   параметра.   В   результате   получены коэффициенты: 0,26455 0,167157 Оказалось, что лишь изменение факторов Х3 и Х4 может  влиять на результирующий показатель. 1,240829 0,047512 0,951472 0,168395 л) исключить из ряда факторов те, у которых малая по абсолютному значения величина параметра t­статистика, это переменные Х1, Х2, Х5, 36 Х6, и, снова получить коэффициенты уравнения регрессии и коэффициент корреляции; _________Некоторые результаты:________________________________________ Регрессионная статистика Множеств. R R-квадрат Y-пересечение Переменная X 3 Переменная X 4 0,649957 0,422444 Коэфф 28,89433 -0,00124 0,83244 Ст. ошибка t­статистика P­Значение 0,005224 8,322675091 0,000449341 0,018266 0,077633 0,427750113 3,471759755 -2,76881489 1,946089208 Коэффициент корреляции при этом уменьшился, но все факторы имеют высокий   уровень   значимости.   Уравнение   регрессии   при   этом   выглядит следующим образом: м) используя новое уравнение регрессии, получить значения выходного y=28,89433­0,00124x3+0,83244x4 параметра для всех опытов; н) построить график зависимости величины Y от номера опыта; о) сохранить результаты.