3. Ход выполнения работы

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

Задания:

Задание № 1

Вы являетесь финансовым аналитиком компании «Глобус». Имеются следующие данные о динамике выручки от реализации продукции и динамике средних остатков задолженности за кредит в оборотные средства компании:

Таблица 12 – Исходные данные

Год	Выручка от реализации продукции, тыс. грн.	Средние остатки задолженности за кредит, тыс. грн.
1996	5560	1200
1997	5710	1240
1998	5950	1300
1999	6150	1350
2000	6350	1410
2001	6600	1460
2002	6840	1500
2003	7050	1580
2004	7250	1600
2005	7450	1650

Необходимо:

1. Определить уравнения линейных трендов, характеризующие тенденции развития:

1) выручки от реализации продукции;

2) средних остатков задолженности за кредит.

Представить тренды в графическом виде.

2. Вычислить коэффициент корреляции между остатками задолженности за кредит и выручкой от реализации продукции.

3. Спрогнозировать на 2006 г. с помощью экстраполяции величину:

1) выручки от реализации продукции;

2) среднего остатка задолженности за кредит.

Сделайте выводы.

Задание №2

Затраты Государственного бюджета Украины на общественные и социальные услуги характеризуются следующими данными, млн. грн.

Таблица 13 – Исходные данные

Статьи	Год
	2000	2001	2002	2003	2004
Образование	2815	3031	3249	3463	3720
Охрана здоровья	132	142	153	163	175
Социальная защита и социальное обеспечение	3565	3838	4114	4385	4711
Жилищно-коммунальное хозяйство	73	79	85	90	97
Культура	294	316	339	362	388
Средства массовой информации	338	364	390	416	447
Физкультура и спорт	133	144	152	162	175
ВСЕГО общественные и социальные услуги	7350	7914	8482	9041	9713

Определите основную тенденцию динамических рядов социальных и общественных услуг (всего и по отдельным статьям) путем аналитического выравнивания по прямой. Постройте тренды, нанесите уравнения трендов.

Экстраполируйте сумму затрат Государственного бюджета Украины на общественные и социальные услуги (всего и по отдельным статьям) до 2010 года.

Сделайте выводы.

Задание 3.

По данным годовых отчетов десяти промышленных предприятий провести корреляционный и регрессионный анализ зависимости себестоимости товарной продукции от производительности труда.

Таблица 14 – Исходные данные

Завод	Производительность труда, грн./чел	Себестоимость единицы продукции, грн.
1	2	6
2	1	4
3	3	10
4	4	16
5	5	18
6	5,2	22
7	5,7	22
8	6	30
9	8	66
10	9	64
11	9,3
12	9,9
13	11,2

Необходимо:

1. Определить степень зависимости между себестоимостью единицы продукции и величиной производительности труда.

2. Построить эмпирическую линию регрессии.

3. Провести подбор функции у=f(x) и определить ее параметры с помощью функций Excel.

4. Построить теоретическую линию регрессии.

5. Осуществить прогноз для трех недостающих значений. Сделать выводы.

Задания для СРС:

Задание №1

По данным за 12 месяцев компании провести корреляционный и регрессионный анализ зависимости дохода от реализации продукции от затрат на рекламу (см. табл. 15)

Таблица 15 – Исходные данные

Завод	Затраты на рекламу, тыс. грн.	Доход от реализации, тыс. грн.
1	211,46	254,85
2	250,07	318,55
3	268,6	348,8
4	322,73	407,9
5	352,85	483,35
6	520,39	516
7	608,77	539,7
8	681,18	540,2
9	728,94	561,8
10	752,64	637,3
11	819,97	720
12	820,52	790,9
13	849,6

Необходимо:

1. Определить степень зависимости между затратами на рекламу и доходом от реализации продукции.

2. Построить эмпирическую линию регрессии.

3. Провести подбор функции у=f(x) и определить ее параметры с помощью функций Excel.

4. Построить теоретическую линию регрессии.

5. Осуществить прогноз дохода от реализации на следующий месяц.

Задание №2

Имеются следующие данные по выданным кредитам и объемом инвестиций в экономику Украины (см. табл. 16)

Таблица 16 – Исходные данные

Кредиты, млрд. грн.	2500	2650	2890	3500	3550	4200	5000	5620	7000	8000
Объем инвестиций, млрд. грн.	1262	1854	1950	2030	2120	2365	2500	2789	3000

Необходимо:

1) определить степень зависимости между величиной выданных кредитов и объемом инвестиций в экономику Украины;

2) Построить эмпирическую линию регрессии.

3) Провести подбор функции у=f(x) и определить ее параметры с помощью функций Excel.

4) Построить теоретическую линию регрессии.

5) Осуществить прогноз недостающего значения.

Контрольные вопросы:

1. В чем заключается задача регрессионного анализа?

2. С помощью каких функций можно найти угол наклона уравнения регрессии?

3. С помощью каких функций можно найти свободный член уравнения регрессии?

4. Что показывает коэффициент корреляции? С помощью какой функции можно определить коэффициент корреляции Пирсона?

5. Что такое аппроксимация данных?

6. Что такое интерполяция?

7. Что такое экстраполяция?

8. В каких случаях для определения прогнозных значений целесообразно использовать функцию ТЕНДЕНЦИЯ, а в каких РОСТ?