Рынок икт. Рынок ИКТ лабы. Решение систем линейных уравнений с помощью теоремы Крамера и с помощью обратной матрицы. Решение систем линейных уравнений методом ЖорданаГаусса. Построение экономикоматематической модели межотраслевого баланса модель затратывыпуск
 Скачать 1.22 Mb.
|
|
2. Решения задач корреляционного и регрессионного анализа с помощью пакета анализа. Пакет анализа - это надстройка, которая представляет широкие возможности для проведения статистического анализа. Установка средств Пакет анализа. Для установки средства Пакет анализа необходимо выполнить следующие действия 1. Выберите команду Сервис Надстройки. 2. В диалоговом окне Надстройки рисунок 1) установите флажок Пакет анализа. 3. Щелкните на кнопке ОК. После этого в нижней части меню Сервис появится новая команда Анализ данных. Эта команда предоставляет доступ к средствам анализа, которые есть в Excel. 47 Рисунок 1 - Для активизации надстройки Пакет анализа следует установить соответствующий флажок. Пример 2 Задача состоит в построении модели для предсказания объема реализации одного из продуктов фирмы. Объем реализации - это зависимая переменная Y. В качестве независимых, объясняющих переменных выбраны время – Х, расходы на рекламу Х, цена товара Х, средняя цена конкурентов Х , индекс потребительских расходов Х. 1. Построение системы показателей (факторов. Анализ матрицы коэффициентов парной корреляции Статистические данные по всем переменным приведены в таблице 6. В этом примере п 16, т 5. Таблица 6 Y X1 Х ХЗ Х Х объем реализации время реклама цена цена конкурента индекс потребительских расходов 126 4 15 17 100 137 1 4.8 14.8 17.3 98.4 148 2 3.8 15.2 16.8 101.2 191 3 8.7 15.5 16.2 103.5 274 4 8.2 15.5 16 104.1 370 5 9.7 16 18 107 432 6 14.7 18.1 20.2 107.4 445 7 18.7 13 15.8 108.5 367 8 19.8 15.8 18.2 108.3 367 9 10.6 16.9 16.8 109.2 321 10 8.6 16.3 17 110.1 307 11 6.5 16.1 18.3 110.7 331 12 12.6 15.4 16.4 110.3 345 13 6.5 15.7 16.2 111.8 364 14 5.8 16 17.7 112.3 384 15 5.7 15.1 16.2 112.9 Использование инструмента Корреляция. Для проведения корреляционного анализа выполните следующие действия 1) данные для корреляционного анализа должны располагаться в смежных диапазонах ячеек 2) выберите команду Сервис=>Анализ данных 3) в диалоговом окне Анализ данных выберите инструмент Корреляция рисунок 4.2.1), а затем щелкните на кнопке ОК; 4) в диалоговом окне Корреляция в поле Входной интервал необходимо ввести диапазон ячеек, содержащих исходные данные. Если выделены и заголовки столбцов, то установить флажок Метки впервой строке (рисунок 2); 5) выберите параметры вывода. В данном примере - установите переключатель Новый рабочий лист 6) ОК. 48 В таблице 7 приведены промежуточные результаты при вычислении коэффициента корреляции по формуле (1) Таблица 7 t Y Х ( y t − y) ( y t − y) 2 (x t − x) (x t − x) 2 ( y t − y)(x t − x) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 126 137 148 191 274 370 432 445 367 367 321 307 331 345 364 384 4 4,8 3,8 8,7 8,2 9,7 14,7 18,7 19,8 10,6 8,6 6,5 12,6 6,5 5,8 5,7 -180.813 -169.813 -158.813 -115.813 -32.8125 63.1875 125.1875 138.1875 60.1875 60.1875 14.1875 0.1875 24.1875 38.1875 57.1875 77.1875 32693.16 28836.29 25221.41 13412.54 1076.66 3992.66 15671.91 19095.79 3622.535 3622.535 201.2852 0.035156 585.0352 1458.285 3270.41 5957.91 -5.29375 -4.49375 -5.49375 -0.59375 -1.09375 0.40625 5.40625 9.40625 10.50625 1 .30625 -0.69375 -2.79375 3.30625 -2.79375 -3.49375 -3.59375 28.02379 20.19379 30.18129 0.352539 1.196289 0.165039 29.22754 88.47754 110.3813 1.706289 0.481289 7.805039 10.93129 7.805039 12.20629 12.91504 957.1762 763.0949 872.4762 68.76367 35.88867 25.66992 676.7949 1299.826 632.3449 78.61992 -9.84258 -0.52383 79.96992 -106.686 -199.799 -277.393 Сумма 4909 148.7 0 158718.4 0 362.0494 4896.381 Среднее значение 306.81 9.2937 0 r y,x = = 0.646. 4896.38 158718.4 * 362.05 49 Рисунок 2 - Выбор инструмента Корреляция. Рисунок 3 - Диалоговое окно Корреляция подготовлено к выполнению анализа данных. Таблица 8 Объем реализации Время Реклама Цена Цена конкурента Индекс потребительских расходов Столбец 1 Столбец 2 Столбец 3 Столбец 4 Столбец 5 Столбец 6 50 0 a 1 1 Объем реализации Время Реклама Цена Цена конкурента Индекс потребительских расходов 1 0.678 0.646 0.233 0.226 0.816 1 0.106 0.174 -0.051 0.960 1 -0.003 0.204 0.273 1 0.698 0.235 1 0.030 1 Анализ матрицы коэффициентов парной корреляции (таблица 8) показывает, что зависимая переменная, те. объем реализации, имеет тесную связь с индексом потребительских расходов (r yx5 =0.816), с расходами на рекламу (r yx2 = 0.646) и со временем (r yx1 =0.678). Однако факторы Хи Х 5 тесно связаны между собой (r x1x5 = 0.96), что свидетельствует о наличии мультиколлинеарности. Из этих двух переменных оставим в модели Х - индекс потребительских расходов. В этом примере n= 16, m = 5, после исключения незначимых факторов n = 16, k = 2. 2. Выбор вида модели и оценка ее параметров Оценка параметров регрессии осуществляется по методу наименьших квадратов по формуле (6), с использованием данных, приведенных в таблице 9. Таблица 9 Y X0 X1 Х объем реализации реклама индекс потребительских расходов 126 1 4 100 137 1 4.8 98.4 148 1 3.8 101.2 191 1 8.7 103.5 274 1 8.2 104.1 370 1 9.7 107 432 1 14.7 107.4 445 1 18.7 108.5 367 1 19.8 108.3 367 1 10.6 109.2 321 1 8.6 110.1 307 1 6.5 110.7 331 1 12.6 110.3 345 1 6.5 111.8 364 1 5.8 112.3 384 1 5.7 112.9 1 x x −1 y a 1 1 1 1 1,1 x 2,1 x 1 1 1 y a = x x ... x * 1,2 2,2 * x x ... x * 2 , 1 1,1 1,2 1,16 ... 1,1 1,2 1,16 ... 2 x 2,1 x 2,2 ... x 2,16 x x x 2,1 x 2,2 ... x 2,16 y 16 148.7 1715.7 1,16 2,16 16 ( X T X ) = 148.7 1744.03 16036.2 , 1715.7 16036.2 184282.13 51 1 a 39.2314 ( X T X ) −1 = 0.06752 0.06752 0.00299 − 0.3711 0.00088 , − 0.3711 − 0.00088 0.0354 a 0 −1471.314 a = ( X T X ) −1 X T Y = a = 9.568 . 2 15.754 Уравнение регрессии зависимости объема реализации от затратна рекламу и индекса потребительских расходов можно записать в следующем виде Y =-1471.314+ 9.568X 1 + 15.754Х 2 Расчетные значения Y определяются путем последовательной подстановки в эту модель значений факторов, взятых для каждого момента времени t. Рисунок 4 - Диалоговое окно Регрессия подготовлено к выполнению анализа данных. 114 Применение инструмента Регрессия. Для проведения регрессионного анализа выполните следующие действия 1) выберите команду Сервис=>Анализ данных 2) в диалоговом окне Анализ данных выберите инструмент Регрессия рисунок 1), а затем щелкните на кнопке ОК; 3) в диалоговом окне Регрессия в поле Входной интервал Y» введите адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал X» введите адреса одного или нескольких диапазонов, которые содержат значения независимых переменных (рисунок 4); − 52 4) если выделены и заголовки столбцов, то установить флажок Метки впервой строке 5) выберите параметры вывода. В данном примере - установите переключатель Новая рабочая книга 6) в поле Остатки поставьте необходимые флажки 7) ОК. Таблица 10 Регрессионная статистика Множественный R R- квадрат Нормированный квадрат Стандартная ошибка Наблюдения 0.927 0.859 0.837 41.473 16.000 Регрессионная статистика № Наименование в отчете EXCEL Принятые наименования Формула 1 Множественный R Коэффициент множественной корреляции, индекс корреляции R = R 2 2 квадрат Коэффициент детерминации, R 2 e(t) 2 ( y − y) 2 R 2 = 1 − = t ( y − y ) 2 ( y − y) 2 t t t 3 Нормированный квадрат Скорректированный R 2 R 2 = 1 − (1 − R 2 ) n −1 n − k −1 4 Стандартная ошибка Стандартная ошибка оценки e(t) 2 S e = n − k −1 5 Наблюдения Количество наблюдений, п n Таблица 11 Дисперсионный анализ Df SS MS F Регрессия 2 136358.334 68179.167 39.639 Остаток 13 22360.104 1720.008 Итого 15 158718.438 Пояснения к таблице 11 Df- число степеней свободы SS - сумма квадратов MS F - критерий Фишера Регрессия K=2 ( yˆ − y) 2 t ( yˆ − y) 2 / k t R 2 / k F = (1 − R 2 )(n − k −1) Остаток n-k-1=13 e(t) 2 e(t) 2 /(n − k −1) 53 N Итого n-1=15 ( y t − y) 2 Таблица 12 Коэффициенты Стандартная ошибка статистика пересечение -1471.3143 259.7660 -5.6640 Реклама 9.5684 2.2659 4.2227 Индекс потребительских расходов 15.7529 2.4669 6.3858 Во втором столбце таблицы 12 содержатся коэффициенты уравнения регрессии а, а, а. В третьем столбце содержатся стандартные ошибки коэффициентов уравнения регрессии (2), а в четвертом - статистика (11), используемая для проверки значимости коэффициентов уравнения регрессии. Уравнение регрессии зависимости объема реализации от затратна рекламу и индекса потребительских расходов, полученное с помощью Excel, имеет вид Y=-1471.314+9.568X 1 +15.754X 2 3. Оценка качества модели В таблице 13 приведены вычисленные по модели значения Y и значения остаточной компоненты. Таблица 13 ВЫВОД ОСТАТКА Наблюдение Предсказанное Y Остатки 1 142.247 -16.247 2 124.697 12.303 3 159.237 -11.237 4 242.353 -51.353 5 247.021 26.979 6 307.057 62.943 7 361.200 70.800 8 416.802 28.198 9 424.177 -57.177 10 350.325 16.675 11 345.365 -24.365 12 334.724 -27.724 13 386.790 -55.790 14 352.052 -7.052 15 353.230 10.770 16 361.725 22.275 Проверку независимости проведем с помощью критерия Дарбина-Уотсона. 2 2 30336.23 d = e(t) − e(t −1) : t =2 t =1 e(t) = 22360.10 = 1.357. N 54 t В качестве критических табличных уровней при N = 16, двух объясняющих факторах при уровне значимости 5% возьмем величины d 1 =0,98 и d 2 = 1,54. Так как расчетное значение попало в интервал от до d 2 , то нельзя сделать окончательный вывод поэтому критерию. Для определения степени автокорреляции вычислим коэффициент автокорреляции и проверим его значимость при помощи критерия стандартной ошибки. Стандартная ошибка коэффициента корреляции рассчитывается следующим образом SEr k = 1/ n. Коэффициенты автокорреляции случайных данных обладают выборочным распределением, приближающимся к нормальному с нулевым математическим ожиданием и средним квадратическим отклонением, равным 1/ Если r 1 находится в интервале -1.96*0.25≤r 1 ≤1.96*0.25, = 1/ = 0.25. то можно считать, что данные не показывают наличие автокорреляции первого порядка, так как -0.49≤r 1 =0.305≤0.49, и свойство независимости выполняется. Вычислить для модели коэффициент детерминации R 2 = R 2 = 1 − e(t) 2 = ( yˆ t − y) 2 = yx1x 2 ( y t − y ) 2 ( y t − y ) 2 = 1 − 22360.104 /158718.44 = 136358.3 /158718.44 = 0.859. Он показывает долю вариации результативного признака под воздействием изучаемых факторов. Следовательно, около 86% вариации зависимой переменной учтено в модели и обусловлено влиянием включенных факторов. Проверку значимости уравнения регрессии произведем на основе вычисления критерия Фишера: R 2 / k F = (1 − R 2 )(n − k −1) = 0.859 / 2 (1 − 0.859) /13 = 39.6. Табличное значение критерия при доверительной вероятности 0,95 v 1 = k = 2 v 2 = n-k- 1 = 16-2- 1 = 13 составляет 4.81. Поскольку F pac > табл, уравнение регрессии следует признать адекватным. Значимость коэффициентов уравнения регрессии а, а 2 оценим с использованием критерия Стьюдента. t aj = a j / S aj = a j / S e b jj , 39.2314 ( X T X ) −1 = 0.06752 0.06752 0.00299 − 0.3711 0.00088 , − 0.3711 − 0.00088 0.00354 b 22 =0.0299, b 33 =0.00354, t a1 = 9.5684 / 2.2659 = 9.5684 / 41.473 t a2 = 15.7529 / 2.4669 = 15.7529 / 41.473 = 4.223, = 6.3858. n 16 0.00299 0.00354 t − 55 Табличное значение критерия при уровне значимости 5% и степенях свободы (16-2-1 = 13) составляет 1,77. Так как t pac > t табл, то коэффициенты а, а 2 существенны (значимы. 4. Проанализировать влияние факторов на зависимую переменную по модели для каждого коэффициента регрессии вычислить коэффициент эластичности, β- коэффициент) Учитывая, что коэффициент регрессии невозможно использовать для непосредственной оценки влияния факторов на зависимую переменную из-за различия единиц измерения, используем коэффициент эластичности Э) и β- коэффициент, которые соответственно рассчитываются по формулам Э Э i = a i * S xi : S y , где S y = β 1 =9,568*4,913/102,865=0,457; β 2 =15,7529*4,5128/102,865=0,691. Коэффициент эластичности показывает, насколько процентов изменяется зависимая переменная при изменении фактора на 1%. Бета-коэффициент с математической точки зрения показывает, на какую часть величины среднего квадратического отклонения меняется среднее значение зависимой переменной с изменением независимой переменной на одно среднее квадратическое отклонение при фиксированном на постоянном уровне значении остальных независимых переменных. Это означает, что при увеличении затратна рекламу в нашем примерена тыс. руб. объем реализации увеличится на 47 тыс. руб. (0.457-102.865). 5. Определить точечные и интервальные прогнозные оценки объема реализации на два квартала вперед (t 0.7 = 1,12) Прогнозные значения Х 1пр (17), Х 2пр (18) и Х 1пр (17), Х 2пр (18) можно определить или вычислить на основе экстраполяционных методов. Для фактора Х Затраты на рекламу выбрана модель Х 12.83 - 11.616t + 4.19t 2 - 0.552t 3 + 0.020t 4 – 0/0006t 5 , по которой получен прогноз на два месяца вперед. Графики модели временного ряда Затраты на рекламу приведены на рисунке 5. Упреждение Прогноз 1 5.75 2 4.85 ( y − y i ср ) 2 N −1 56 Рисунок 5- Выбор тренда для временного ряда Затраты на рекламу. Задание 1. Объем реализации - это зависимая переменная Y. В качестве независимых, объясняющих переменных выбраны время – Х, расходы на рекламу Х, цена товара Х, средняя цена конкурентов Х , индекс потребительских расходов Х. Статистические данные по всем переменным приведены в таблице 1. В этом задании п 16, т 5. Таблица 1 Y X1 Х ХЗ Х Х объем реализации время реклама цена цена конкурента индекс потребительских расходов 126 4 15 17 100 137 1 4.8 14.8 17.3 98.4 148 2 3.8 15.2 16.8 101.2 191 3 8.7 15.5 16.2 103.5 274 4 8.2 15.5 16 104.1 370 5 9.7 16 18 107 432 6 14.7 18.1 20.2 107.4 445 7 18.7 13 15.8 108.5 367 8 19.8 15.8 18.2 108.3 367 9 10.6 16.9 16.8 109.2 321 10 8.6 16.3 17 110.1 307 11 6.5 16.1 18.3 110.7 331 12 12.6 15.4 16.4 110.3 345 13 6.5 15.7 16.2 111.8 364 14 5.8 16 17.7 112.3 384 15 5.7 15.1 16.2 112.9 57 Построить модель для предсказания объема реализации одного из продуктов фирмы. Задание 2. Бюджетное обследование семи случайно выбранных семей дало результаты (в тыс. руб, показанные в таблице 3. Таблица 3 Наблюдение Накопления. Y Доход, X 1 3 40 2 6 55 3 5 45 4 3.5 30 5 1.5 30 6 4.5 50 7 2 35 Требуется 1) построить однофакторную модель регрессии 2) оценить накопления семьи, имеющей доход 42 тыс. руб 3) отобразить на графике исходные данные, результаты моделирования. |