Ход выполнения работы. По исходным данным необходимо установить степень взаимосвязи между показателями, построить эмпирическую линию регрессии

По исходным данным необходимо установить степень взаимосвязи между показателями, построить эмпирическую линию регрессии. Найти члены уравнения регрессии и построить теоретическую линию регрессии и спрогнозировать с помощью найденного уравнения регрессии, а также встроенных статистических функций 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).

Задания: