По исходным данным необходимо установить степень взаимосвязи между показателями, построить эмпирическую линию регрессии. Найти члены уравнения регрессии и построить теоретическую линию регрессии и спрогнозировать с помощью найденного уравнения регрессии, а также встроенных статистических функций Excel значения на следующий период.
Рассмотрим пример решения задачи корреляционно-регрессионного анализа.
Пример:
Необходимо определить степень зависимости между выручкой от реализации продукции и величиной кредиторской задолженности предприятия. Построить эмпирическую линию регрессии.
Провести подбор функции у=f(x) и определить ее параметры с помощью функций Excel. Построить теоретическую линию регрессии. Осуществить прогноз величины кредиторской задолженности на 2006, 2007 гг. при известных ожидаемых поступлениях выручки. Исходные данные задачи представлены на рис. 9.
Рисунок 9 – Исходные данные
Решение:
1-й шаг:
Определим степень взаимосвязи между исходными данными с помощью коэффициента корреляции. Для этого используется встроенная функция КОРРЕЛ. Из списка функций необходимо выбрать Статистические, в открывшемся списке статистических функций выбираем функцию КОРРЕЛ для поиска коэффициента корреляции Пирсона (r). В открывшемся диалоговом окне в поле Массив 1 вводим ссылки на ячейки, содержащие данные, соответствующие у. В поле Массив 2 вводим ссылки на ячейки, содержащие данные, соответствующие х. По полученному значению коэффициента корреляции судим о степени связи между анализируемыми данными:
|
|
Если 0,6 < r ≤ 1 – связь прямая, сильная.
Если 0,3 < r ≤ 0,6 – связь прямая, средняя.
Если 0 < r ≤ 0,3 – связь прямая, слабая.
Если - 0,6 < r ≤ -1 – связь обратная, сильная.
Если - 0,3 < r ≤ - 0,6 – связь обратная, средняя.
Если 0 < r ≤ - 0,3 – связь обратная, слабая.
Если r = 0 – связь отсутствует.
Если r = ±1 – связь из стохастической переходит в функциональную.
2-й шаг:
Найдем параметры уравнения регрессии с помощью одной из функций (см. рис.10):
Рисунок 10 – Функции Excel, используемые для нахождения членов уравнения регрессии
Из списка функций необходимо выбрать Статистические, в открывшемся списке статистических функций выбираем функцию НАКЛОН для поиска углового коэффициента линии регрессии (m). В открывшемся диалоговом окне в поле Известные значения у вводим ссылки на ячейки, содержащие данные, соответствующие у. В поле Известные значения х вводим ссылки на ячейки, содержащие данные, соответствующие х. Вид диалогового окна функции НАКЛОН представлен на рис.11.
Рисунок 11 – Диалоговое окно функции НАКЛОН
|
|
Аналогичным образом поступаем для нахождения свободного члена уравнения регрессии (b). В данном случае в списке статистических функций выберем функцию ОТРЕЗОК. Вид диалогового окна функции ОТРЕЗОК представлен на рис.12.
Рисунок 12 - Диалоговое окно функции ОТРЕЗОК
Полученное уравнение регрессии имеет вид:
у = 0,164914∙х + 360,1955
3-й шаг:
С помощью Мастера построения диаграмм строим эмпирическую линию регрессии по исходным данным (см. рис.13).
Рисунок 13 – Зависимость величины кредиторской задолженности от выучки от реализации
4-й шаг:
Подставив в полученное уравнение регрессии значения х, найдем т.н. «теоретические» значения у. По полученным данным построим теоретическую линию регрессии, используя в качестве значений у полученные «теоретические» значения (см. рис. 14).
Рисунок 14 – Теоретическая линия регрессии
5-й шаг:
С помощью встроенных функций ПРЕДСКАЗ или ТЕНДЕНЦИЯ спрогнозируем значения выручки и кредиторской задолженности на 2006 и 2007 год.
Рисунок 15 - Спрогнозированные значения
Для этого в столбец, где находятся даты добавим снизу новые значения – 2006 и 2007 г. (если они не даны). Из списка функций необходимо выбрать Статистические, в открывшемся списке статистических функций выбираем функцию ТЕНДЕНЦИЯ. В открывшемся диалоговом окне в поле Известные значения у вводим ссылки на ячейки, содержащие данные, соответствующие у (в данном случае это будут значения выручки). В поле Известные значения х вводим ссылки на ячейки, содержащие данные, соответствующие х. (в данном случае это будут годы) (см. рис.15).
Задания: