Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
ФГБОУ ВПО ЧЕРЕПОВЕЦКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
Кафедра: менеджмента
Дисциплина: Анализ хозяйственной деятельности
Лабораторная работа №2.
Группировка и КРА.
Выполнила: студентка группы
5МО-31 Широких И.П.
Проверила: преподаватель
Неробова В.А.
Череповец 2012
Цель работы: используя средства MS Excel проводить группировку и КРА.
Задание:
Построить простую аналитическую группировку. Результативным показателем является годовое жалование (дол.).
Таблица 1
|
возраст |
опыт |
пол |
образование |
Заработная плата |
|
х1 |
х2 |
х3 |
х4 |
у |
|
20 |
0 |
0 |
6 |
22100 |
|
31 |
4 |
0 |
6 |
36900 |
|
44 |
14 |
0 |
4 |
60200 |
|
20 |
0 |
1 |
4 |
22100 |
|
55 |
25 |
0 |
2 |
75900 |
|
44 |
14 |
1 |
0 |
56200 |
|
25 |
5 |
0 |
6 |
31900 |
|
55 |
25 |
1 |
2 |
80200 |
|
50 |
20 |
0 |
4 |
68300 |
|
60 |
30 |
1 |
0 |
86100 |
|
40 |
15 |
1 |
2 |
54800 |
|
64 |
29 |
1 |
0 |
91300 |
|
35 |
10 |
0 |
6 |
44800 |
|
64 |
19 |
1 |
2 |
83100 |
|
40 |
15 |
0 |
4 |
54800 |
|
31 |
4 |
1 |
4 |
36900 |
|
25 |
5 |
1 |
6 |
31900 |
|
35 |
10 |
1 |
2 |
44800 |
|
60 |
21 |
1 |
2 |
80200 |
|
50 |
20 |
1 |
2 |
69900 |
Алгоритм решения:
1. Рассчитаем шаг группировки по формуле:
Образование = (6-0)/4=1,5≈2
2. Строим рабочую таблицу “Группировка по образованию”:
Таблица 2
|
Интервалы |
Х входящие в интервал |
У входящие в интервал |
Сумма Х |
Сумма У |
Среднее по Х |
Среднее по У |
|
0-2 |
0 0 0 2 2 2 2 2 2 2 |
56200 86100 91300 75900 80200 54800 83100 44800 80200 69900 |
14 |
722500 |
1,4 |
72250 |
|
2-4 |
4 4 4 4 4 |
60200 22100 68300 54800 36900 |
20 |
242300 |
4 |
48460 |
|
4-6 |
6 6 6 6 6 |
22100 36900 31900 44800 31900 |
30 |
167600 |
6 |
33520 |
3. Остаточная дисперсия:
где - значение признака Y для i-й единицы в j_й группе;
- значение признака Y в j-группе;
- число единиц в j-й группе;
j =1,2,3,…, m.
4. Средняя величина внутригрупповой дисперсии:
=190388250
5. Межгрупповая дисперсия:
=38153010
6. Общая дисперсия:
=228541260
7.Рассчитаем эмпирическое корреляционное отношение, которое измеряет, какую часть общей колеблемости результативного признака вызывает изучаемый фактор.
- коэффициент детерминации,
= 0,833059
- эмпирическое корреляционное отношение
0,91272, следовательно, имеется тесная связь между фактором и результативным показателем.
Построим точечные диаграммы рассеяния:
Определим тесноту связи с помощью функции КОРЕЛЛ:
Ответ 0,993579 – это означает, что между возрастом и зарплатой существует очень сильная линейная прямая корреляционная зависимость:
Рис.1.
Рис.2.
Рис.3.
Теснота связи = - 0,7648946
Это высокая обратная линейная корреляционная зависимость.
Рис.4
2) Определим параметры уравнения многофакторной регрессии, используя функции "Линейн" и "лгрфприбл"
Выделяем диапазон ячеек 5х5. Вызываем функцию «Линейн»:
В первой строке массив данных результативного показателя (зарплата), во второй – факторного показателя. После заполнения всех строк, нажимаем ctrl+shift+Enter.
Получаем:
|
575,342686 |
1295,647884 |
822,1468 |
1064,032 |
-2829,036 |
|
256,0669586 |
702,1831754 |
86,85793 |
52,20385 |
2078,925 |
|
0,998215545 |
1049,103227 |
#Н/Д |
#Н/Д |
#Н/Д |
|
2097,731969 |
15 |
#Н/Д |
#Н/Д |
#Н/Д |
|
9235202736 |
16509263,71 |
#Н/Д |
#Н/Д |
#Н/Д |
Уравнение множественной линейной регрессии имеет следующий вид:
у=1064,032х1+ 822,1468х2+1295,648х3+575,342686х4-2829,036
Коэффициент детерминированности. Коэффициент r2=0,998215545.
(т.е. между оценочным и фактическим значениями у нет особых различий)
Вызываем функцию FРАСПОБР.
Вероятность равна 0,05;
Степень свободы 1 равна 4 (v1 = n – df – 1= 20-15-1=4);
Степень свободы 2 равна 15 (v2 = df)
Нажмем ОК.
F- критическое равно 3,05556828.
F-наблюдаемое равно 2097, 73197
2097,73197>3,05556828, F- наблюдаемое>F-критическое, т.е. r2 статистически значим.
Вычислим T-статистику для линейной функции, поделив mi на sei:
Tнабл1= 575,342686/256,066959=2,246845.
Tнабл2= 1295, 648/702,1832=1,845171
Tнабл3=9,46542
Tнабл4=20,38225
Tнабл5= -1,36082
Воспользуемся функцией СТЬЮДРАСПОБР. Сделаем выводы по критерию Стьюдента, сравнив Т-наблюдаемое значение с критическим:
Вызовем функцию “СТЬЮДРАСПОБР”.
Вероятность: 0, 05;
Степени свободы: 15.
Нажмем ОК.
Ткр= 2, 13144954
tнабл1>tкр,, tнабл3>tкр, tнабл4>tкр, т.е. можно сделать вывод о том, что статистическая значимость соответствующего коэффициента регрессии подтверждается.
tнабл2>tкр, tнабл5>tкр, т.е. можно сделать вывод о незначимости коэффициента регрессии .
Если tнабл > tкритич, то полученное значение коэффициента корреляции признается значимым (нулевая гипотеза, утверждающая равенство нулю коэффициента корреляции, отвергается).
Поскольку tнабл > tкрит, то отклоняем гипотезу о равенстве 0 коэффициента корреляции. Другими словами, коэффициент корреляции статистически - значим.
2.2. Уравнение множественной регрессии (кривой для " ЛГРФПРИБЛ ") имеет следующий вид:
y = (b*(m1^x1)*(m2^x2)*_) (в случае нескольких значений x),
где зависимые значения y являются функцией независимых значений x. Значения m являются основанием, возводимым в степень x, а значения b постоянны. Функция ЛГРФПРИБЛ возвращает массив {mn; mn-1; ...; m1; b}.
На Листе Microsoft Excel выделяем диапазон ячеек 55, вызываем функцию “ЛГРФПРИБЛ”.
Известные значения y: выделяем диапазон ячеек F10:F29, т.е. столбец зарплата;
Известные значения х: выделяем диапазон ячеек B10:Е29, т.е. всю остальную часть таблицы со столбцами возраст, опыт, пол, образование;
Конст и Статистика равны 1, условие “ИСТИНА”.
Далее нажимаем cntrl+shift+Enter. Получаем следующую таблицу:
Таблица 4
|
1,004813 |
0,97817151 |
1,015544 |
1,021409 |
16969,36 |
|
0,023133 |
0,06343432 |
0,007847 |
0,004716 |
0,187807 |
|
0,963838 |
0,09477463 |
#Н/Д |
#Н/Д |
#Н/Д |
|
99,95084 |
15 |
#Н/Д |
#Н/Д |
#Н/Д |
|
3,591126 |
0,13473346 |
#Н/Д |
#Н/Д |
#Н/Д |
Значения таблицы соответствуют значениям на рисунке 1.
Следовательно, уравнение множественной регрессии (кривой для " ЛГРФПРИБЛ ") будет иметь следующий вид:
y=(16969,36*(1,021409^x1)*( 1,015544^ x 2)*( 0,97817151^x3)*( 1,0048131^x4)
Коэффициент детерминированности: r2= 0,963838 (т.е. между оценочным и фактическим значениями у нет особых различий)
Вызываем функцию FРАСПОБР.
Вероятность равна 0,05;
Степень свободы 1 равна 4 (v1 = n – df – 1= 20-15-1=4);
Степень свободы 2 равна 15 (v2 = df)
Нажмем ОК.
F- критическое равно 3,05556828.
F-наблюдаемое равно 99,95084
99,95084>3,05556828, F- наблюдаемое>F-критическое, т.е. r2 статистически значим.
Вычислим T-статистику для функции ЛГРФПРИБЛ, поделив ln mi на sei:
Tнабл1= 0,004802/0,023133=0,20756802.
Tнабл2= -0,347923
Tнабл3= 1,965772
Tнабл4= 4,491816
Tнабл5= 51,85719
Воспользуемся функцией СТЬЮДРАСПОБР. Сделаем выводы по критерию Стьюдента, сравнив Т-наблюдаемое значение с критическим:
Вызовем функцию “СТЬЮДРАСПОБР”.
Вероятность: 0, 05;
Степени свободы: 15.
Нажмем ОК.
Ткр= 2, 13144954
tнабл1<tкр, tнабл2<tкр, tнабл3<tкр, т.е. коэффициент регрессии незначим.
tнабл4<tкр, tнабл5<tкр, т.е. можно сделать вывод о том, что статистическая значимость соответствующего коэффициента регрессии подтверждается, значит данные не противоречат гипотезе о равенстве нулю истинного значения коэффициента
Контрольное задание:
Проведите корреляционно-регрессионный анализ, используя функции «линейная» и «корреляция». Сделайте оценку по критериям Стьюдента и Фишера.
Составьте прогноз ожидаемого объема продаж в будущем периоде, используя функцию «Предсказание».
Воспользуемся функцией ЛИНЕЙН:
Рассчитаем коэффициент Стьюдента 3 способами:
1) t=mi / sei: 0,616982/0,1182=5,219798
2) извлечь из корень F- статистики: =5,219798
3) ) t=
где, - коэффициент корреляции, вычисляемый с помощью функции "КОРРЕЛ".
- среднеквадратическое отклонение:
Найдем коэффициент корреляции. Для этого вызовем функцию “КОРРЕЛ”.
Значение коэффициента корреляции составляет 0,855288.
Таким образом, подставив это число в формулу получаем:
t=5,219798
Вычислим T-статистику для линейной функции, поделив mi на sei:
Tнабл= 0,616982/0,1182=5,2198.
Воспользуемся функцией СТЬЮДРАСПОБР. Сделаем выводы по критерию Стьюдента, сравнив Т-наблюдаемое значение с критическим:
Ткр= 2, 228139 (вероятность 0,05, степень свободы 10)
tнабл>tкр, т.е. 5,2198>2,228139, т.е. можно сделать вывод о том, что статистическая значимость соответствующего коэффициента регрессии подтверждается.
( Коэффициент корреляции равен 0,731517, между двумя факторами (расходы на рекламу и продажи) существует высокая прямая линейная зависимость).
Рассчитаем критерий Фишера по формуле:
F =
Fнабл =
Вызываем функцию FРАСПОБР. Находим F-критическое.
Fкр= 4,9646027. (вероятность - 0,05, степень свободы1 – 1, ступень свободы2 – 10)
27,246296>4,9646027, F- наблюдаемое>F-критическое, т.е. r2 статистически значим.
Составим прогноз ожидаемого объема продаж в будущем периоде, используя функцию «Предсказание».
Вызываем функцию “ПРЕДСКАЗ”:
Х- данное прогнозное значение по расходам на рекламу, которое равно 45 тыс. руб.
Известные значения y- диапазон ячеек С37:N37, т.е. строка продажи.
Известные значения х- диапазон ячеек С36:N36, т.е. строка расходы на рекламу.
Нажимаем ОК.
Получили значение 48,38413 млн. руб.
Литература