10) Обоснуйте с помощью коэффициента детерминации R2, какое уравнение более точно описывает зависимость между изучаемыми показателями.
Решение:
1. Для расчета линейного коэффициента корреляции воспользуемся стандартной функцией ExcelКОРРЕЛ().
r=0,87208
Для расчета коэффициента ранговой корреляции Спирмена составим вспомогательную расчетную таблицу:
X | Y | Rx | Ry | d=Rx-Ry | d2 |
157 | 107 | 2 | 1 | 1 | 1 |
273 | 182 | 8 | 7 | 1 | 1 |
150 | 151 | 1 | 4 | -3 | 9 |
301 | 307 | 11,5 | 14 | -2,5 | 6,25 |
539 | 321 | 22 | 15 | 7 | 49 |
520 | 347 | 21 | 18 | 3 | 9 |
584 | 396 | 24 | 23 | 1 | 1 |
600 | 390 | 25 | 22 | 3 | 9 |
332 | 354 | 14 | 19 | -5 | 25 |
262 | 211 | 7 | 9 | -2 | 4 |
331 | 330 | 13 | 17 | -4 | 16 |
397 | 328 | 15 | 16 | -1 | 1 |
424 | 390 | 17 | 22 | -5 | 25 |
168 | 169 | 3 | 6 | -3 | 9 |
413 | 261 | 16 | 12 | 4 | 16 |
448 | 364 | 20 | 20 | 0 | 0 |
430 | 403 | 18 | 24 | -6 | 36 |
572 | 406 | 23 | 25 | -2 | 4 |
285 | 152 | 10 | 5 | 5 | 25 |
246 | 227 | 6 | 10 | -4 | 16 |
445 | 298 | 19 | 13 | 6 | 36 |
183 | 131 | 5 | 2 | 3 | 9 |
301 | 237 | 11,5 | 11 | 0,5 | 0,25 |
283 | 188 | 9 | 8 | 1 | 1 |
170 | 141 | 4 | 3 | 1 | 1 |
Итого: | 309,5 |
Тогда коэффициент ранговой корреляции будет равен:
2. Рассчитаем доверительный интервал для коэффициента корреляции при доверительной вероятности 0,90.
Доверительный интервал определяем как:
где t – квантиль распределения Стьюдента.
0,97694=
1,7065=
Таким образом, интервальная оценка для истинного значения коэффициента корреляции будет:
0,75174£r£0,93621
3. Проверим значимость коэффициента корреляции. Находим фактическое значение t-статистики Стьюдента:
Критическое значение tкр(0,9;23)=1,7109
Т.к.
4. Найдем оценки коэффициентов уравнения регрессии. Для этого воспользуемся функцией Excel ЛИНЕЙН(). Получим следующие значения коэффициентов:
a0=55,328, a1=0,61355.
Таким образом, уравнение регрессии будет иметь вид:
5. Построим диаграмму рассеяния и отобразим на ней уравнение регрессии:
6. Выполним точечный прогноз себестоимости, если планируемый объем товарной продукции в следующем году 500.
Тогда прибыль составит ПР=500-362,1=137,9 млн. руб., а рентабельность
7. Найдем доверительный интервал для себестоимости, используя формулу:
Значение ошибки регрессии находим с помощью функции СТОШYX():
s=49,583.
Сумму квадратов отклонений считаем с помощью функции КВАДРОТКЛ():
Тогда
Находим доверительный интервал:
337,29
8. Представим зависимость между себестоимостью и стоимостью товарной продукции с помощью:
а) уравнения параболы:
б) степенной функции
в) логарифмической функции
г) экспоненциальной функции
д) уравнения гиперболы
Для этого сделаем замену
Получим следующий отчет работы надстройки:
ВЫВОД ИТОГОВ | ||||||||
Регрессионная статистика | ||||||||
Множественный R | 0,865643 | |||||||
R-квадрат | 0,749338 | |||||||
Нормированный R-квадрат | 0,73844 | |||||||
Стандартная ошибка | 50,72761 | |||||||
Наблюдения | 25 | |||||||
Дисперсионный анализ | ||||||||
df | SS | MS | F | Значимость F | ||||
Регрессия | 1 | 176932,1 | 176932,1 | 68,75715 | 2,31E-08 | |||
Остаток | 23 | 59185,67 | 2573,29 | |||||
Итого | 24 | 236117,8 | ||||||
Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | Нижние 95,0% | Верхние 95,0% | |
Y-пересечение | 459,7745 | 24,85374 | 18,49921 | 2,63E-15 | 408,3606 | 511,1883 | 408,3606 | 511,1883 |
Переменная X 1 | -55589,1 | 6703,949 | -8,29199 | 2,31E-08 | -69457,3 | -41720,9 | -69457,3 | -41720,9 |
Получим следующее уравнение:
R2=0,74934
9) Коэффициенты детерминации R2 для каждого варианта аппроксимации рассчитаны на графиках. Для этого нужно при добавлении тренда в параметрах указать «поместить на диаграмму величину достоверности аппроксимации».
Для гиперболической зависимости его величину можно взять из итоговой таблицы работы надстройки.
10) Обоснуйте с помощью коэффициента детерминации R2, какое уравнение более точно описывает зависимость между изучаемыми показателями.
Поскольку максимальное значение коэффициента детерминации, равное 0,8012, имеет степенной тренд, то это уравнение более точно описывает зависимость между изучаемыми показателями.
Список литературы:
1. Практикум по теории статистики: Учеб. пособие для экон. спец. вузов [Р.А. Шмойлова, А.Б. Гусынин, В.Г. Минашкин, Н.А. Садовникова]; Под ред. Р.А. Шмойловой. – М.: Финансы и статистика, 2004.
2. Теория статистики / Под ред. Шмойловой Р.А. - М., 2003.
3. Статистика: Учеб. пособие / [Л.П. Харченко, В.Г. Долженкова, В.Г. Ионин и др.]; НГАЭиУ. - 2-е изд., перераб. и доп. - М.: ИНФРА-М, 2006.