Компьютерные технологии в экономической науке и практике (стр. 18 )

Данные наблюдений
Месяц	Количество праздничных и выходных дней	Суммы, снятые со счетов
январь	12	120458
февраль	6	95700
март	5	75678
апрель	4	51675
май	7	81900
июнь	4	59500

Требуется определить, существует ли взаимосвязь между количеством выходных и праздничных дней и величиной сумм, снимаемых со счетов.

Решение

1.  Введем в диапазон рабочего листа (А1:C8) данные приведенной таблицы (рис. 75 ).

2.  Выполним команду меню Сервис ð Анализ данных.

3.  В раскрывшемся окне диалога Анализ данных (рис.76) из списка выберем Корреляция.

4.  В окне диалога Корреляция (рис. 77):

·  в группе Входные данные в поле Входной интервал укажем адресную ссылку на диапазон, содержащий исходную таблицу (В2:С8), установим переключатель Группирование по столбцам и установием флажок Метки в первой строке;

·  в группе Параметры вывода установим переключатель Входной интервал и введем адресную ссылку на ячейку, которая будет началом диапазона выходного интервала (Е2).

5.  Щелкнем на ОК.

Рис. 75

Рис. 76

После выполнения вычислений в диапазон Е2:G4 будет выведена результирующая корреляционная матрица (рис. 78).

Рис. 77

Рис. 78

Корреляционная матрица является треугольной с единичной диагональю. На самом деле она должна рассматриваться как симметричная, т. е. rij = rji .

Из анализа полученной корреляционной матрицы видно, что коэффициент корреляции между количеством выходных дней и величиной суммы, снимаемой со счетов r= 0,91668. Таким образом, существует сильная прямая линейная связь между количеством выходных и праздничных дней и величинами сумм, снимаемых со счетов.

Задание 36. 

Определите, имеется ли взаимосвязь и какая между годовым уровнем инфляции, ставкой рефинансирования и курсом валюты по следующим данным ежегодных наблюдений:

Уровень инфляции (%)	Ставка рефинансирования ( %)	Курс (руб/$)
84,00%	85,00%	6,3
45,00%	55,00%	13
56,00%	64,00%	22
34,00%	40,00%	27
23,00%	25,00%	29
12,00%	15,00%	31
10,00%	12,00%	31,5

Технология решения задач регрессионного анализа

Важную роль при исследовании взаимосвязей между статистическими выборками кроме корреляционного и дисперсионного анализа играет регрессионный анализ. Регрессия позволяет проанализировать воздействие на какую-либо зависимую переменную одной или более независимых переменных и позволяет установить аналитическую форму (модель) этой зависимости в виде аппроксимирующего полинома.

Если рассматривается зависимость между одной зависимой переменной Y и несколькими независимыми X1, X2, …, Xn, то речь идет о множественной линейной регрессии. В этом случае уравнение регрессии имеет вид:

Y = a0 + a1X1 + a2X2 +…+anXn,

Где a1, a2, …, an - коэффициенты при независимых переменных, которые нужно вычислить (коэффициенты регрессии), a0 –константа.

При построении регрессионной модели важнейшими моментами являются оценка ее адекватности (эффективности) и значимости, на основании которых можно судить о возможности применения в практике полученной модели.

Мерой оценки адекватности регрессионной модели является коэффициент детерминации R2 (R-квадрат), который определяет, с какой степенью точности полученное уравнение регрессии аппроксимирует исходные данные.

Значимость регрессионной модели оценивается с помощью критерия Фишера (F – критерия). Если величина F – критерия значима (р < 0,05), то регрессионная модель является значимой.

В MS Excel можно аппроксимировать экспериментальные данные линейным уравнением до 16 порядка: Y = a0 + a1X1 + a2X2 +…+a16X16. Для вычисления коэффициентов регрессии служит инструмент Регрессия, который можно включить следующей последовательностью операций:

1.  Выполнить команду Сервис ð Анализ данных.

2.  В раскрывшемся окне диалога Анализ данных выбрать из списка строку Регрессия – раскроется окно диалога Регрессия.

3.  В группе Входные данные в поле Входной интервал Y указать адресную ссылку на диапазон, содержащий значения зависимой переменной, а в поле Входной интервал Х – ссылку на диапазон, содержащий значения независимых переменных, т. е. переменных влияние которых на зависимую переменную Y оценивается. Установить флажок Метки, если исходная таблица имеет названия столбцов и флажок Константа-ноль, если а0=0.

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

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

6.  Если нужно получить график нормальной вероятности, то установить флажок График нормальной вероятности.

7.  В выходном диапазоне после выполнения вычислений отображаются результаты дисперсионного анализа, коэффициенты регрессии, стандартная погрешность вычисления Y, среднеквадратичные отклонения, количество наблюдений, стандартные погрешности для коэффициентов.

Значения коэффициентов регрессии размещаются в столбце Коэффициенты:

•  Y - пересечение a0;

•  X1 - a1;

•  X1 – a2 и т. д.

В столбце Р – Значение содержится оценка достоверности отличия соответствующих коэффициентов от нуля. Если P > 0,05, то коэффициент можно считать нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую переменную.

Значение R – квадрат определяет, с какой степенью точности регрессионное уравнение будет аппроксимировать экспериментальные данные. Если R – квадрат > 0,95, то точность аппроксимации высокая. При 0,8< R – квадрат < 0,95 аппроксимация удовлетворительная. В случае, когда R – квадрат <0,6, точность аппроксимации недостаточна и модель требует улучшения.

Кроме инструмента Регрессия в MS Excel для получения параметров уравнения регрессии есть функция ЛИНЕЙН и функция ТЕНДЕНЦИЯ для получения значения Y в требуемых точках.

Пример 48.  Имеются статистические данные о затратах, связанных с рекламой по телевидению, с рекламой в метро и объеме реализации продукции в рублях, приведенные в таблице.

Требуется найти регрессионные коэффициенты для независимых переменных Расходы на рекламу по телевидению и Расходы на рекламу в метро на объем реализации продукции и построить уравнение регрессии.

Затраты на рекламу по телевидению	Затраты на рекламу в метро	Объем реализации
125850	20000	1850000
260500	21000	2500000
150000	22000	1900000
300000	21000	3000000
280000	24000	2600000
290000	23000	2700000
140000	20000	1900000

Решение.

1.  На рабочем листе в диапазон А1: С8 введем данные приведенной таблицы (рис. 80).

2.  Включим инструмент Регрессия.

3.  В открывшемся диалоговом окне Регрессия установим параметры (рис. 79):

·  Входной интервал Y – диапазон С1:C8;

·  Входной интервал X – диапазон A1:B8;

·  Флажок Метки;

·  Выходной интервал – адрес D1;

·  Флажок График нормальной вероятности

·  Флажок График остатков.

После щелчка на кнопке ОК в диапазон D1:L21 будет выведен результат регрессионного анализа (рис. 80).

Полученные результаты и их интерпретация:

Коэффициент детерминации R – квадрат = 0,974641 (аппроксимация высокая).

Значимость F = 0,000643 (р < 0,05- регрессионная модель значима).

Y – пересечение a0= 2102438,6

a1 = 6,4004 – коэффициент при независимой переменной Затраты на рекламу по телевидению.

Рис. 79

Рис. 80

a2 = -54,068 - коэффициент при независимой переменной Затраты на рекламу в метро.

С учетом полученных данных функциональная зависимость величины прибыли от затрат на рекламу запишется в виде полинома:

Y= 2102438,6 + 6,4004 X1 - 54,068X2,

где Х1 – величина затрат на рекламу по телевидению, Х2 – величина затрат на рекламу в метро.

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

Пример 49.  Организация может истратить на рекламу по телевидению и на рекламу в метро 170000 рублей. Требуется оптимальным образом распределить затраты на различные виды рекламы с целью получения максимальной прибыли.