Метод принятия управленческих решений. Контрольная в Excel.
Описание работы
Работа пользователя Н. Ермолина
Лабораторная работа № 1: Нахождение оценок коэффициентов парной линейной регрессии в MS Excel
Известны годовые доходности компаний А и В, принадлежащих одной отрасли, за некоторый период. Установить по результатам наблюдений зависимость доходности компании А от доходности компании В.
Доходность компании В (Х) Доходность компании А (Y) Yоц
-5,31 -2,54 -2,854431758
16,84 26,5 25,82120886
0,07 4,44 4,110577115
10,03 17,12 17,00490581
4,98 10,19 10,46711867
7,52 13,88 13,75543141
0,23 4,55 4,317714926
5,53 10,28 11,17915489
5,94 11,76 11,70994553
6,09 11,89 11,90413723
0,93 5,14 5,223942846
3,22 7,7 8,188602757
2,08 7,17 6,712745858
2,81 7,57 7,657812118
10,73 17,46 17,91113373
Задача 1
Построить поле корреляции (на отдельном листе) и сформулировать гипотезу о форме связи
1.1. Поле корреляции - точечная диаграмма построенная по исходным данным. Выделите оба столбца с данными.
1.2. Выберите на основной панели инструментов значок с диаграммой и необходимый тип диаграммы, следуйте требуемым шагам. На предпоследнем шаге выберите "На новом листе".
1.3. По виду точечной диаграммы сформулируйте гипотезу о возможном виде связи.
Задача 2. Найти оценки параметров модели парной линейной регрессии
2.1. Используя исходные определения.
Введите в выбранные поля обозначения коэффициентов, определяющие их формулы, (используя MS Equation) и вычисляющие формулы, используя статистические функции Excel.
2.2. Используя встроенные функции Excel: НАКЛОН(У,Х); ОТРЕЗОК(У,Х), ЛИНЕЙН().
2.2.1. При применении функции ЛИНЕЙН() используется функция массива. Выделите 2 ячейки в одной строке(например, зеленые рядом с обозначениями коэффициентов b0 b1) и вызовите из статистических функцию ЛИНЕЙН(..).
В ведите последовательно аргументы функции: ЛИНЕЙН(массив переменной Y, массив переменной Х, 1, 0) После появления коэффициента b1 нажмите F2 и закончите ввод сочетание клавиш Ctrl+Shift+Enter.
Задача 3
Найти оценки коэффициентов регрессии, используя уравнение линейного тренда (построить его на листе поля корреляции)
3.1. Для построения линии тренда выделите на диаграмме точки данных (щелкните на диаграмме кнопкой мыши по точкам данных), после их выделения вызовите контекстное меню правой кнопкой мыши. Выберите пункт "Добавить линию тренда". В появившемся меню выберите линейный тренд, а из вкладки "параметры" отметьте пункты "показывать уравнение на диаграмме" и "вывести R^2".
Задача 4
Найти основные дисперсионные характеристики полученного в предыдущих работах уравнения линейной регрессии:
4.1. Создать таблицу для расчета дисперсий прогноза математического ожидания зависимой переменной и конкретного значения У при Хр = 0,07
4.2. В столбец "расчетные формулы" введите пользуясь редактором формул выражение для расчетной формулы статистической характеристики
4.3 В следующий столбец введите формулу для расчета соответствующей характеристики. При расчетах не вводите дополнительных расчетных столбцов в исходных данных, а используйте математические функции Excel
(СУММ(...), СУММПРОИЗВ(...), СУММКВ(...), КОРЕНЬ()) и(или) функции массивов.
Задача 5
Найти доверительные интервалы для математического ожидания и конкретного значения зависимой переменной для уровня значимости = 0,05.
5.1. Введите в выбранные поля обозначения коэффициентов, определяющие их формулы, (используя MS Equation) и вычисляющие формулы.
Для уровня значимости 0,05 найдите критическое значение статистики Стьюдента , используя стат. функцию СТЬЮДРАСПОБР(..)). В параметрах функции задается полный уровень значимости(возвращается значение для симметричного интервала, называемого "двуххвостовым") и количество степеней свободы. (Можно использовать значение, уже найденное в других лабораторных работах)
Задача 6 Найти прогнозное зависимой переменной используя уравнение регрессии значение и используя статистические функции Excel ( ТЕНДЕНЦИЯ, ПРЕДСКАЗ)
Имеются данные по цене некоторого блага (Х) и количеству (У) данного блага, приобретаемого домохозяйством ежемесячно в течении года. Предполагается, что генеральное уравнение регрессии - линейное 1. Найти оценки коэффициентов регрессии b0 и b1
Цена, X 10 20 15 25 30 35 40
Приобретаемое количество, Y 110 75 100 80 60 55 40
2. С надежностью 0,9 определить интервальные оценки теоретических коэффициентов регрессии
3. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
4. С доверительной вероятностью 0,05 определить интервальную оценку условного математического ожидания Y при Х = 23.
Задача 2.9
Имеются данные за 10 лет по прибылям Х и Y ( в %) двух компаний
Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 найти доверительный интервалы для полученных оценок. Сделать вывод о их значимости.
3. Проверить значимость уравнения регрессии с помощью F критерия Фишера-Снедекора.
4. С доверительной вероятностью 0,01 определить интервальную оценку условного математического ожидания У при Х = 10.
Задача 2.10
Для прогноза возможного объема экспорта на основе ВНП имеются следующие данные.
Предполагается, что генеральное уравнение регрессии - линейное Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 проверить значимость полученных оценок.
3. Проверить значимость уравнения регрессии с помощью определения коэффициента детерминации.
4. С доверительной вероятностью 0,01 определить интервальную оценку величины экспорта при ВНП = 1200.
Задача 2.11
Имеются данные за 7 лет относительно среднего дохода и среднего потребления.
Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1и остаточной регрессии S2
2. С надежностью 0,8 определить интервальные оценки теоретических коэффициентов регрессии
4. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
5. С доверительной вероятностью 0,02 определить интервальную оценку величины потребления при доходе равном 15.
Задача 2.12
Производится анализ количества населения (РОР) и количества практикующих врачей(MED).
Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1и остаточной регрессии S2
2. Проверить при уровне значимости 0,05 значимость уравнения регрессии.
3. С надежностью 0,8 определить интервальные оценки теоретических коэффициентов регрессии
4.Если прогнозное количество населения составить 12 млн, каково математическое ожидание количества врачей? Рассчитайте 99% доверительный интервал для данного предсказания.
Задача 2.13
Имеются данные о зависимости усушки формового хлеба (Y) от продолжительности хранения (Х). Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 определить интервальные оценки теоретических коэффициентов регрессии и сделать вывод об их значимости.
3. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
4. С надежностью 0,01 определить доверительный интервал для значения У при Х = 12.
Задача 2.14
Имеются данные о динамике процента хронических больных на тысячу жителей (Y) за ряд лет наблюдений (Х). Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 проверить значимость полученных оценок.
3. Проверить значимость уравнения регрессии с помощью F критерия Фишера-Снедекора.
4. С доверительной вероятностью 0,01 определить интервальную оценку условного математического ожидания Y при Х = 5.
Задача 2.15
По территориям региона приводятся данные за 19ХХ г.
Требуется: 1. Построить линейное уравнение парной регрессии. 2. Оценить статистическую значимость коэффициентов регрессии с надежностью 0,95. 3. Выполнить прогноз заработной платы Y при прогнозном значении среднедушевого прожиточного минимума х, составляющем 107% от среднего уровня. 4. Оценить точность прогноза, рассчитав ошибку прогноза и его доверительный интервал с доверительной вероятностью 0,025 .
Задача 2.16
Имеются данные о зависимости усушки формового хлеба (Y) от продолжительности хранения (Х). Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 определить интервальные оценки теоретических коэффициентов регрессии и сделать вывод об их значимости.
3. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
4. С надежностью 0,01 определить доверительный интервал для значения У при Х = 12
Владелец магазинов, торгующих мороженным, хотел бы знать, как температура воздуха влияет на объем продаж в течение летнего сезона. Для исследования он в течение 21 дня подряд заполнял таблицу.
n Температура (°F) Объем продаж (тыс. долл.)
1 63 1,52
2 70 1,68
3 73 1,8
4 75 2,05
5 80 2,36
6 82 2,25
7 85 2,68
8 88 2,9
9 90 3,14
10 91 3,06
11 92 3,24
12 75 1,92
13 98 3,4
14 100 3,28
15 92 3,17
16 87 2,83
17 84 2,58
18 88 2,86
19 80 2,26
20 82 2,14
21 76 1,98
Известны годовые доходности компаний А и В, принадлежащих одной отрасли, за некоторый период. Установить по результатам наблюдений зависимость доходности компании А от доходности компании В.
Доходность компании В (Х) Доходность компании А (Y) Yоц
-5,31 -2,54 -2,854431758
16,84 26,5 25,82120886
0,07 4,44 4,110577115
10,03 17,12 17,00490581
4,98 10,19 10,46711867
7,52 13,88 13,75543141
0,23 4,55 4,317714926
5,53 10,28 11,17915489
5,94 11,76 11,70994553
6,09 11,89 11,90413723
0,93 5,14 5,223942846
3,22 7,7 8,188602757
2,08 7,17 6,712745858
2,81 7,57 7,657812118
10,73 17,46 17,91113373
Задача 1
Построить поле корреляции (на отдельном листе) и сформулировать гипотезу о форме связи
1.1. Поле корреляции - точечная диаграмма построенная по исходным данным. Выделите оба столбца с данными.
1.2. Выберите на основной панели инструментов значок с диаграммой и необходимый тип диаграммы, следуйте требуемым шагам. На предпоследнем шаге выберите "На новом листе".
1.3. По виду точечной диаграммы сформулируйте гипотезу о возможном виде связи.
Задача 2. Найти оценки параметров модели парной линейной регрессии
2.1. Используя исходные определения.
Введите в выбранные поля обозначения коэффициентов, определяющие их формулы, (используя MS Equation) и вычисляющие формулы, используя статистические функции Excel.
2.2. Используя встроенные функции Excel: НАКЛОН(У,Х); ОТРЕЗОК(У,Х), ЛИНЕЙН().
2.2.1. При применении функции ЛИНЕЙН() используется функция массива. Выделите 2 ячейки в одной строке(например, зеленые рядом с обозначениями коэффициентов b0 b1) и вызовите из статистических функцию ЛИНЕЙН(..).
В ведите последовательно аргументы функции: ЛИНЕЙН(массив переменной Y, массив переменной Х, 1, 0) После появления коэффициента b1 нажмите F2 и закончите ввод сочетание клавиш Ctrl+Shift+Enter.
Задача 3
Найти оценки коэффициентов регрессии, используя уравнение линейного тренда (построить его на листе поля корреляции)
3.1. Для построения линии тренда выделите на диаграмме точки данных (щелкните на диаграмме кнопкой мыши по точкам данных), после их выделения вызовите контекстное меню правой кнопкой мыши. Выберите пункт "Добавить линию тренда". В появившемся меню выберите линейный тренд, а из вкладки "параметры" отметьте пункты "показывать уравнение на диаграмме" и "вывести R^2".
Задача 4
Найти основные дисперсионные характеристики полученного в предыдущих работах уравнения линейной регрессии:
4.1. Создать таблицу для расчета дисперсий прогноза математического ожидания зависимой переменной и конкретного значения У при Хр = 0,07
4.2. В столбец "расчетные формулы" введите пользуясь редактором формул выражение для расчетной формулы статистической характеристики
4.3 В следующий столбец введите формулу для расчета соответствующей характеристики. При расчетах не вводите дополнительных расчетных столбцов в исходных данных, а используйте математические функции Excel
(СУММ(...), СУММПРОИЗВ(...), СУММКВ(...), КОРЕНЬ()) и(или) функции массивов.
Задача 5
Найти доверительные интервалы для математического ожидания и конкретного значения зависимой переменной для уровня значимости = 0,05.
5.1. Введите в выбранные поля обозначения коэффициентов, определяющие их формулы, (используя MS Equation) и вычисляющие формулы.
Для уровня значимости 0,05 найдите критическое значение статистики Стьюдента , используя стат. функцию СТЬЮДРАСПОБР(..)). В параметрах функции задается полный уровень значимости(возвращается значение для симметричного интервала, называемого "двуххвостовым") и количество степеней свободы. (Можно использовать значение, уже найденное в других лабораторных работах)
Задача 6 Найти прогнозное зависимой переменной используя уравнение регрессии значение и используя статистические функции Excel ( ТЕНДЕНЦИЯ, ПРЕДСКАЗ)
Имеются данные по цене некоторого блага (Х) и количеству (У) данного блага, приобретаемого домохозяйством ежемесячно в течении года. Предполагается, что генеральное уравнение регрессии - линейное 1. Найти оценки коэффициентов регрессии b0 и b1
Цена, X 10 20 15 25 30 35 40
Приобретаемое количество, Y 110 75 100 80 60 55 40
2. С надежностью 0,9 определить интервальные оценки теоретических коэффициентов регрессии
3. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
4. С доверительной вероятностью 0,05 определить интервальную оценку условного математического ожидания Y при Х = 23.
Задача 2.9
Имеются данные за 10 лет по прибылям Х и Y ( в %) двух компаний
Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 найти доверительный интервалы для полученных оценок. Сделать вывод о их значимости.
3. Проверить значимость уравнения регрессии с помощью F критерия Фишера-Снедекора.
4. С доверительной вероятностью 0,01 определить интервальную оценку условного математического ожидания У при Х = 10.
Задача 2.10
Для прогноза возможного объема экспорта на основе ВНП имеются следующие данные.
Предполагается, что генеральное уравнение регрессии - линейное Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 проверить значимость полученных оценок.
3. Проверить значимость уравнения регрессии с помощью определения коэффициента детерминации.
4. С доверительной вероятностью 0,01 определить интервальную оценку величины экспорта при ВНП = 1200.
Задача 2.11
Имеются данные за 7 лет относительно среднего дохода и среднего потребления.
Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1и остаточной регрессии S2
2. С надежностью 0,8 определить интервальные оценки теоретических коэффициентов регрессии
4. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
5. С доверительной вероятностью 0,02 определить интервальную оценку величины потребления при доходе равном 15.
Задача 2.12
Производится анализ количества населения (РОР) и количества практикующих врачей(MED).
Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1и остаточной регрессии S2
2. Проверить при уровне значимости 0,05 значимость уравнения регрессии.
3. С надежностью 0,8 определить интервальные оценки теоретических коэффициентов регрессии
4.Если прогнозное количество населения составить 12 млн, каково математическое ожидание количества врачей? Рассчитайте 99% доверительный интервал для данного предсказания.
Задача 2.13
Имеются данные о зависимости усушки формового хлеба (Y) от продолжительности хранения (Х). Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 определить интервальные оценки теоретических коэффициентов регрессии и сделать вывод об их значимости.
3. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
4. С надежностью 0,01 определить доверительный интервал для значения У при Х = 12.
Задача 2.14
Имеются данные о динамике процента хронических больных на тысячу жителей (Y) за ряд лет наблюдений (Х). Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 проверить значимость полученных оценок.
3. Проверить значимость уравнения регрессии с помощью F критерия Фишера-Снедекора.
4. С доверительной вероятностью 0,01 определить интервальную оценку условного математического ожидания Y при Х = 5.
Задача 2.15
По территориям региона приводятся данные за 19ХХ г.
Требуется: 1. Построить линейное уравнение парной регрессии. 2. Оценить статистическую значимость коэффициентов регрессии с надежностью 0,95. 3. Выполнить прогноз заработной платы Y при прогнозном значении среднедушевого прожиточного минимума х, составляющем 107% от среднего уровня. 4. Оценить точность прогноза, рассчитав ошибку прогноза и его доверительный интервал с доверительной вероятностью 0,025 .
Задача 2.16
Имеются данные о зависимости усушки формового хлеба (Y) от продолжительности хранения (Х). Предполагается, что генеральное уравнение регрессии - линейное
Требуется:
1. Найти оценки коэффициентов регрессии b0 и b1
2. С надежностью 0,95 определить интервальные оценки теоретических коэффициентов регрессии и сделать вывод об их значимости.
3. Определить коэффициент детерминации, сделать соответствующие выводы о качестве уравнения регрессии.
4. С надежностью 0,01 определить доверительный интервал для значения У при Х = 12
Владелец магазинов, торгующих мороженным, хотел бы знать, как температура воздуха влияет на объем продаж в течение летнего сезона. Для исследования он в течение 21 дня подряд заполнял таблицу.
n Температура (°F) Объем продаж (тыс. долл.)
1 63 1,52
2 70 1,68
3 73 1,8
4 75 2,05
5 80 2,36
6 82 2,25
7 85 2,68
8 88 2,9
9 90 3,14
10 91 3,06
11 92 3,24
12 75 1,92
13 98 3,4
14 100 3,28
15 92 3,17
16 87 2,83
17 84 2,58
18 88 2,86
19 80 2,26
20 82 2,14
21 76 1,98
