рис.1.1

рис.1.1

ü Выберем команду Сервис=>Анализ данных;

ü В диалоговом окне Анализ данных выберем строку корреляция и подтвердим кнопкой ОК (рис1.2):

рис1.2

ü На табло Корреляция в поле Входной интервал введём диапазон исход­ных данных. Если выделяются заголовки, то ставится флажок в Метке первой строки (рис.1.3):

рис.1.3

Анализ матрицы коэффициентов парной корреляции (рис.1.4.):

ü зависимая переменная y , т.е. чистый доход имеет тесную связь с исполь­зованным капиталом – x₅ (r_yx5=0.445), c численностью служащих – x₃ (r_yx3=0.786), с оборотом капитала x₂ (r_yx2=0.861);

рис.1.4

ü факторы x₅ и x₃ (r_x3x5=0,501), что свидетельствует об отсутствии мульти­коллинеарности (линейная или близкая связь между факто­рами), поэтому из двух переменных оставим использованный капитал;

ü первоначально имели n=20, m=4, после исключения незначимых факто­ров получили n=20, k=2, т.е. в модель включается только два фактора;

Выбор вида модели и расчёт ее параметров:

На основе корреляционного анализа выбираем линейную двухфакторную мо­дель регрессии и для дальнейших расчётов составляем новую таблицу исход­ных данных (рис.1.5):

рис.1.5

Оценку параметров модели выполняем с помощью МНК в матричном виде:

А = (X’X)^-1X’Y

В общем виде имеем:

Первоначально транспонируем матрицу X (в Excel=>ТРАНСП), затем её умно­жаем на исходную матрицу X (в Excel=>МУМНОЖ) и вычислим обрат­ную матрицу (в Excel=>МОБР). После этого обратную матрицу умножим на транспонируемую и на У (в Excel=>МУМНОЖ), (рис.1.6.):

рис.1.6.

Уравнение регрессии зависимости чистого дохода у от оборота капитала x₁ и использованного капитала x₂ можно записать в следующем виде:

у=0,680+0,034x₁-0.004x₂

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

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

ü Выберем команду Сервис=>Анализ данных;

ü В диалоговом окне Анализ данных выберем инструмент Регрессия, а за­тем щёлкнем на кнопку ОК, (рис.1.7):

рис.1.7

ü В диалоговом окне Регрессия в поле Входной интервал У введём адрес одного диапазона ячеек, который представляет зависимую перемен­ную. В поле Входной интервал Х введём адреса одного или нескольких диапазонов, которые содержат значения независимых переменных;

ü Выберем параметры вывода (Новая рабочая книга);

ü В поле Остатки поставим необходимые флажки;

ü ОК (рис.1.8):

рис.1.8

Результат регрессионного анализа (рис.1.9; рис.1.10):

рис.1.9

                                            рис.1.10

Оценим качества всего уравнения регрессии:

Зная предсказанные по модели значения зависимой переменной У и значения ос­таточной компоненты можно рассчитать значения коэффициента детермина­ции:

Коэффициент R² показывает долю вариации результативного признака под воз­действием изучаемых факторов, т.е. 76,9 % вариации зависимой перемен­ной учтено в модели и обусловлено влиянием факторов оборота капитала и фак­тора использования капитала.

Коэффициент множественной корреляции , что означает тес­ноту связи зависимой переменной У-чистый доход с факторами оборот капи­тала и использование капитала на 87,7%.

Проверка значимости уравнения регрессии производится по F-критерию Фи­шера по формуле:

Табличное значение F-критерия при уровне значимости 0,05 и степенях сво­боды v₁=k=2 b v₂=n – k - 1=20-2-1=17 составляет 3,592. Поскольку F_расч>F_табл, то уравнение регрессии адекватно описывает реальный процесс.

Оценка статистической значимости коэффициентов множественной регрес­сии оценивается по t-критерию Стьюдента, значения которого вычисляются по формулам:

t_aj=a_j/S_aj, S_aj=S_e*,

где b_jj – диагональный элемент матрицы (X’X)^-1:

Поскольку t_расч>t_табл=3,222 (в Excel=>СТЬЮДРАСПОБР), то а₀,а₁ значимы.

Анализ влияния факторов оборота капитала и использования капитала на незави­симую переменную У – чистый доход вычисляется по коэффициенту эла­стичности и -коэффициентов:

Э₁=0,034*27,552/1,619=0,5786

Э₂=-0,004*16,810/1,619=0,042

При изменении оборота капитала чистый доход изменится на 57,86%, а при изме­нении фактора использование капитала на 4,2%;

₁=0,034*0,010/0,223=0,0015

₂=-0,004*0,019/0,223=0,003

Бета-коэффициент показывает, на какую часть величины СКО меняется сред­нее значение у с изменением j-ой независимой переменной на одно СКО отклоне­ние при неизменных остальных независимых переменных. Так, что при увеличении оборота капитала чистый доход увеличится на 34 тыс.руб., а при увеличении использованного капитала чистый доход увеличится на 76 тыс.руб.

Случайный характер остатков проверяется по графику остатков (рис.1.11):

На графике нет направленности в расположении точек Е₁, то Е_i -  случайные величины, следовательно теоретические у хорошо аппроксимируют фактиче­ские у. таким образом средняя величина остатков равна 0 и, следовательно, ос­татки не зависят от значений факторов х, Е_отн=1,391%, она меньше 7% - мо­дель имеет хорошее качество.

Рассчитаем прогнозные значения х₁ и х₂ при х_прог=х_мах*0,8

х_прог1=165,4*0,8=132,32

х_прог2=60,6*0,8=48,48

Рассчитаем точечный и интервальный прогноз:

С помощью Мастера  диаграмм в Excel для фактора оборот капитала и использо­вание капитала подберём модель тренда (рис.1.11, 1.12):

                                 рис.1.11

1.12

Для фактора оборот капитала:

Для фактора используемый капитал:

Для получения точечного прогноза по модели у=0,680+0,034х₁-0,004х₂ необхо­димо подставить в неё найденные прогнозные значения факторов обо­рот капитала и используемый капитал:

Уt₂₁=0.680+0.034*0.056-0.004*0.037=0.6818

Уt₂₂=0.680+0.034*0.012-0.004*0.044=0.6802

Далее рассчитаем доверительный интервал прогноза: