Обработка экономической информации на ЭВМ

Отчетливо видна выраженная положительная корреляция между ценами на золото и индексом РТС (рисунок 3.1).

Однако статистически достоверный вывод о наличии корреляции может быть сделан только на основе оценки значимости рассчитанного коэффициента корреляции. Эта значимость определяется только самим значением коэффициента корреляции r и объемом выборки n. Оценка значимости рассчитывается по величине t, вычисляемой как следующее отношение:

[3.2].

Эта величина подчиняется распределению Стьюдента, зависимость уровня значимости p (и доверительной вероятности 1 — p) от значения t можно найти в специальных таблицах распределения Стьюдента. Также, для определения этой зависимости можно использовать MS Excel, так как распределение Стьюдента является одной из стандартных встроенных функций MS Excel. В принятых нами обозначениях вызов этой функции имеет вид СТЬЮДРАСП (t, n — 2; 2). Здесь второй аргумент n — 2 соответствует числу степеней свободы (уменьшение на 2 связано с наличием двух переменных), третий аргумент отвечает оценке двух «хвостов» распределения, а не одного.

Обычно в MS Excel анализ корреляций производится следующим образом:

в меню «Сервис» выбираем «Анализ данных»;

выделяем инструмент «Корреляции», после чего жмем ОК;

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

отметим способ Группирования (по столбцам или строкам);

указываем флажок «Метки», если первая строка содержит названия переменных (в случае группировки по строкам первый столбец содержит названия переменных);

обозначим выходной интервал, для этого достаточно указать левую верхнюю ячейку будущего диапазона, или отмечаем выдачу данных на отдельном листе;

Применим описанный инструмент анализа для выявления корреляционных связей между динамикой мировых цен на золото, курсов акций ОАО «Сбербанк России», РАО «Газпром», ОАО «Лукойл», РАО «ЕЭС» и динамикой индекса РТС. Полученные коэффициенты корреляции отражены на рисунке 3.

2.

Рисунок 3.

2. Корреляционный анализ мировых цен на золото, курсов акций ОАО «Сбербанк России», РАО «Газпром», ОАО «Лукойл», РАО «ЕЭС» и индекс РТС.

Анализ выявленных корреляций позволяют заключить, что почти все ряды данных характеризуются положительными, и, по-видимому, высоко достоверными корреляциями. Действительно, так как объем выборки равен 23, то для уровня значимости 0,05 доверительная граница значимости коэффициента корреляции равна 0,415. Это значение рассчитывается по формуле [3.2] (с помощью распределения Стьюдента).

3.2 Регрессионный анализ Рассмотрим построение множественной регрессии с помощью средств MS Excel. Пусть входными данными являются:

M значений функции {y1, …yM};

ряд S аппроксимирующих ее факторов x (1), … x (S), для любого фактора s (1 (s (S) задано M значений фактора x (s)1, …, x (s)M.

Нам необходимо построить M уравнений регрессии:

y1 = b0 + b1*x (1)1 + bS*x (S)1 ,

…

yM = b0 + b1*x (1)M + bS*x (S)M ,

где b0, b1, …, bS − коэффициенты, подлежащие определению. Также необходимо оценить степень (относительную силу) зависимости исследуемой функции от каждой независимой переменной.

Общее качество любой аппроксимации характеризуется параметром R2. (R квадрат, коэффициент детерминации). Это значение нормируют, так что оно располагается в интервале [0; 1]. Значения R2 близкие к 1 определяют модель (или аппроксимацию) более высокого качества. Пусть для простоты мы аппроксимируем n значений функции y, соответствующими n значениями теоретической модели y (t). Расчет величины R2 производится по следующей формуле:

, [3.3].

Статистический вывод о значимости уравнения множественной регрессии проверяется следующим образом:

Производится общая проверка методом F-теста, который выясняет, объясняют ли набор переменных {х (s)} значимую долю вариации функции у, то есть, преобладает ли влияние факторов x (1), …, x (S) на изменение функции у над ее независимыми колебаниями. Если регрессия не является значимой, то рассмотрение ее смысла не имеет.

Если F-тест подтверждает значимость регрессии, анализ продолжается с помощью t-тестов для отдельных коэффициентов регрессии. Целью проведения t-тестов является выяснение, насколько значимым является влияние каждой переменной x (s) на функцию у при условии, что все другие переменные x (u) (1 (u (S, u (s) остаются неизменными. Построение доверительных интервалов и проверка гипотез на адекватность для отдельного коэффициента регрессии происходит на основе расчета соответствующей стандартной ошибки.

Также при построении множественной регрессии важно избегать такого явления как мультиколлинеарность. Мультиколлинеарность — положение, при котором одна или более независимых переменных X, входящих в уравнение регрессии, являются точными линейными функциями от одной или более других независимых переменных того же уравнения. Также не желательно и чрезмерная корреляционная зависимость переменных (они же по определению «независимые»). Использование в регрессионной модели переменных, коэффициент корреляции между которыми превосходит значение 0.8, считается нецелесообразным. Единственным способом исключения мультиколлинеарности следует считать исключение одной из зависимых переменных. Применяют также пошаговые процедуры создания регрессионных моделей. При этом добавление некоторой переменной происходит на основе анализа величин коэффициентов корреляции ее с другими (уже включенными в анализ) переменными.

В качестве примера применения описанной методики регрессионного анализа построим регрессионную модель для индекса РТС (функция Y) на основе динамики мировых цен на серебро и курса акций ОАО «Сбербанк России» (две переменные X). Для построения возьмем период времени с февраля 2003 по декабрь 2004 г., всего 23 значения по каждому месяцу. Индекс РТС формируется на основе множества факторов, так что нельзя исключить влияние отдельной крупной компании (ОАО «Сбербанк России»). Цена на серебро включена как некоторый дополнительный параметр, возможно способный улучшить качество регрессионной модели.

Для контроля явления мультиколлинеарности отметим, что корреляция между двумя выбранными переменными X (0,778, рисунок 3.2), хотя и велика, но она не превосходит рекомендуемого максимума 0,8.

Расположим входные данные (массив значений функции y, а также массивы значений каждого из факторов {x (s)}) в отдельных столбцах на листе MS Excel. Далее будем действовать стандартным образом:

в меню «Сервис» вызываем пункт «Анализ данных»;

в появляющимся стандартном диалоговом окне пакета анализа MS Excel выбираем инструмент анализа «Регрессия»;

возникло окно установления входных данных регрессии, в поля «Входной интервал Y» и «Входной интервал X» вводим, соответственно, диапазоны значений функции y и общий диапазон переменных x (прямоугольная область — все столбцы с разными курсами акций);

отметим доверительную вероятность как 95% (значение по умолчанию);

укажем в поле «Выходной интервал» ту ячейку, от которой будет формироваться весь блок получаемых выходных данных — статистических характеристик вычисляемой регрессии (или установим выдачу данных на отдельном листе);

нажмем кнопку ОК.

В результате получено содержательное представление параметров регрессионной модели (рисунок 3.4).

Рисунок 3.

4. Лист MS Excel с результатами расчета статистических показателей множественной регрессии. Функция Y для построения регрессии — индекс РТС. Переменные X — динамика мировых на серебро и курс акций ОАО «Сбербанк России». Использован период времени с февраля 2003 г. по декабрь 2004 г., всего 23 точки по каждому месяцу.

Рассмотрим эту выдачу подробнее. Вначале показывается общее качество регрессионной модели (Рисунок 3.4, вверху, таблица «Регрессионная статистика»). Вычисление R2 показало, что 58 процентов вариации значений функции y объясняются факторами {x (s)} (R-квадрат = 0.345). То есть на долю других, не учитываемых моделью факторов, приходится около 42%.

Затем (Рисунок 3.4 в центре, таблица «Дисперсионный анализ») приведены результаты F-теста. С достоверностью (Значимость F = 0,17) показано, что регрессия значима (отметим, что по известным только ему одному причинам, для вычисления F-теста пакет MS Excel берет отношение меньшей дисперсии к большей, обычно в статистических таблицах указывается обратное отношение).

Наконец, наиболее важные данные представляет таблица внизу рисунка 3.

4. Столбец «Коэффициенты» содержит свободный член регрессии b0, а также коэффициенты регрессии b1, b2. Свободному члену соответствует строка «Пересечение Y», a следующие строки, озаглавленные именами переменных — линейным коэффициентам регрессии. В следующих столбцах таблицы приводятся: стандартная ошибка, t-статистика (и соответствующий ей уровень значимости, P-значение) и доверительный интервал для каждого коэффициента (b0, b1).

Заметим, что уровень значимости (P-значение) меньший 0,05 позволяет судить о значимости отдельного показателя. Таким образом, показано, что Y-пересечение (b0) положительно (коэффициент 307,376, стандартная ошибка 71,022, t-статистика 4,328, P-значение 0,32). Относительно коэффициента b1 (при переменной «серебро») сказать этого нельзя, так как отрицательное значение -2,307 имеет стандартную ошибку 3,329, соответственно t-статистика -0,693 определяет P-значение 0,49 (>0,05). Следовательно, нельзя утверждать, что коэффициент b1 отличен от нуля. Коэффициент b2 (при переменной «ОАО Сбербанк России») оказался положительным, ему отвечает P-значение 0,0011<0,05. Значит, использование этой переменной в регрессионной модели вполне корректно.

Приведем регрессионную модель, из которой исключена первая переменная X «серебро» (Рисунок 3.5).

Рисунок 3.

5. Лист MS Excel с результатами расчета статистических показателей множественной регрессии. Функция Y для построения регрессии — индекс РТС. Переменная X — курс акций ОАО «Сбербанк России». Использован период времени с февраля 2003 г. по декабрь 2004 г., всего 23 точки по каждому месяцу.

Заметим, что исключение не значимой переменной позволило даже увеличить значение t-статистики для переменной X.

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

4. Прогнозирование экономических параметров

4.

1. Прогнозирование на основе регрессионной модели Множественная регрессия была подробно рассмотрена в предыдущем разделе, здесь мы лишь в качестве иллюстрации построим график построенной регрессионной модели и сравним ее с моделируемой функцией. На рисунке 4.1 приведен график изменения индекса РТС, а также регрессионная модель, отражающая график индекса РТС в зависимости от курса акций ОАО «Сбербанк России».

Рисунок 4.

1. График изменения индекса РТС, а также регрессионная модель, отражающая изменение индекса РТС в зависимости от курса акций ОАО «Сбербанк России». Линейная зависимость отражает регрессионную модель, построенную в разделе 3.

2.

Заметно, что модель хорошо согласуются с реальными данными, можно утверждать, что модель адекватно отображает данные.

Коэффициент b1 = 0,843 можно трактовать так: прирост курса акций на единицу (1 доллар) вызывает рост индекс РТС на величину 0,843. Коэффициент b0 отражает гипотетический индекс РТС при нулевом курсе акций.

4.

2. Прогнозирование на основе трендовых моделей, сравнение результатов прогнозирования разных методов.

Термином линейный регрессионный анализ обозначают аппроксимацию, описываемую линейной взаимосвязью между исследуемыми переменными: y = a + b*x. В случае криволинейных зависимостей применяются самые разные математические функции. К числу основных аппроксимационных моделей можно отнести функции следующего вида:

степенная: y = a*xb;

полиномиальная: y = a + b*x + c*x2 + d*x3 + …+ z*xn; (a, b, c, …, z — коэффициенты полинома);

логарифмическая: y = a + b*lg (x);

экспоненциальная: y = a*exp (b*x).

Решение математических уравнений связи предполагает вычисление по исходным данным их параметров, например, для линейной модели y = a + b*x рассчитываются свободный член, а и коэффициент регрессии b.

Пакет программ MS Excel способен строить простейшие регрессионные модели (линейная регрессия функции y от одной или множества переменных x). Также для каждого ряда данных на диаграммах MS Excel возможно построение аппроксимаций основных типов. В точности, всего может быть построено 6 типов моделей:

линейная;

логарифмическая;

полиномиальная (задается максимальная степень полинома);

степенная;

экспоненциальная;

скользящее среднее (задается длина скользящего окна).

Первые пять моделей уже были представлены выше, а скользящее среднее представляет собой профиль {x (W)}, в каждой точке представляющий собой среднее значение по некоторой ближайшей окрестности значений переменной x, Таким образом, единственным параметром скользящего среднего является длина скользящего окна W.

Для построения любой трендовой модели необходимо действовать следующим образом:

Выделить на диаграмме ряд, для которого необходимо построить линию тренда.

Нажать пункт «Добавить линию тренда» в меню «Диаграмма».

Выбрать необходимый тип линии тренда из шести перечисленных выше.

Для полиномиального типа нужно задать максимальную степень полинома, а для скользящего среднего — длину скользящего окна.

Также возможно задать точку пересечения линии тренда с осью y (значение переменной y при x = 0), предусмотреть отображение на диаграмме уравнения линии тренда и значения R2, характеризующего общее качество линии тренда.

Проиллюстрируем создание аппроксимаций в MS Excel на примере построения линий тренда для мировых цен на золото (рисунки 4.2 и 4.3). В данном случае мы использовали протяженный интервал времени — с октября 2002 по февраль 2008 года, всего 23 значения.

Рисунок 4.

2. Динамика мировых цен на золото (доллар за унцию) и ее аппроксимации: линейный — линейный тренд; степень — степенной тренд; полином 6 — полиномиальный тренд (максимальная степень полинома 6). Для каждого тренда показано значение R2, отражающее общее качество тренда (рост значения R2 соответствует лучшей аппроксимации).

Рисунок 4.

3. Динамика мировых цен на золото (доллар за унцию) и ее аппроксимации: логарифм — логарифмический тренд; экспоненц. — экспоненциальный тренд. Для каждого тренда показано значение R2, отражающее общее качество тренда (рост значения R2 соответствует лучшей аппроксимации).

Как уже отмечалось ранее (раздел 3.2), качество аппроксимации отражается значением R2, приближение которого к +1 снизу означает улучшение предсказательной силы модели (линии тренда). Легко видеть (рисунки 4.2 и 4.3), что по сравнению с полиномиальным трендом, сравнительно низким качеством аппроксимации характеризуются линейный, логарифмический, степенной и экспоненциальный тренды. Этот факт достаточно очевиден, так как линия тренда полинома (выбрана максимальная степень 6) использует большее число параметров по сравнению с любым другим трендом из примененных на рисунках 4.2 и 4.

3.

В заключение обсуждения построения аппроксимаций следует также отметить, что кроме MS Excel, существует ряд специализированных пакетов программ для построения аппроксимаций. Например, пакет CurveExpert определяет зависимость достаточно общего вида y = f (x), где функция f (x) моделируется на основе достаточно большого разнообразия математических зависимостей. Таким образом, производится быстрый отбор и построение самых сложных аппроксимационных моделей.

Заключение

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

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

Список литературы

Абдулазар Л. Лучшие методики применения Excel в бизнесе. Вильямс Диалектика. 2006 г. 464 с.

Мотов В.В. Word, Excel, PowerPoint — просто, кратко, быстро. Инфра-М, 2007 г. 206 с.

Ланджер М. Создание электронных таблиц и диаграмм в Excel. НТ Пресс 2005 г. 144 с.

Минько А. А. Статистический анализ в MS Excel. Профессиональная работа. Диалектика, 2004 г. 437 c.

Гмурман В. Е. Теория вероятностей и математическая статистика. — М.: Высшая школа, 1999 г.

Елисеева И.И., Юзбашев М. М. Общая теория статистики. — М.: Финансы и статистика, 1999 г.

Шторм Р. Теория вероятностей. Математическая статистика. Статистический контроль качества. — М.: Мир, 1970 г. 368 с.