Пример выполнения задания с помощью пакета анализа Excel

Пакет анализа - это надстройка Excel, которая представляет широкие возможности для проведения статистического анализа. Установка средств Пакет анализа

В стандартной конфигурации программы Excel вы не найдете средства Пакет анализа. Это средство надо установить в качестве надстройки Excel. Для этого выполните следующие действия:

1. Выберите команду Сервис => Надстройки.

2. В диалоговом окне Надстройки (рис. 12) установите флажок Пакет анализа.

3. Щелкните по кнопке ОК.

В результате выполненных действий в нижней части меню Сер­вис появится новая команда Анализ данных. Эта команда предостав­ляет доступ к средствам анализа, которые есть в Excel.

Рис. 12. Диалоговое окно Надстройки

Продемонстрируем возможности Пакета программ на следующем примере.

Пример

Построим модель объема реализации одного из продуктов фир­мы.

Объем реализации - это зависимая переменная Y. В качестве не­зависимых, объясняющих переменных выбраны:

Х₁ - время,

Х₂ - расходы на материал,

Х₃ - цена изделия,

Х₄ - средняя цена по отрасли,

X₅ - индекс расходов.

Статистические данные по всем переменным приведены в табл. 5.

В рассматриваемом примере число наблюдений п = 16, фактор­ных признаков т = 5.

Таблица 5

Использование инструмента Корреляция

Для проведения корреляционного анализа нужно выполнить сле­дующие действия:

1) расположить данные в смежных диапазонах ячеек;

2) выбрать команду Сервис => Анализ данных (рис. 13). Появит­ся диалоговое окно Анализ данных (рис. 14);

Рис.13. Выбор команды Анализ данных

3)в диалоговом окне Анализ данных выбрать инструмент Корреляция (рис.14), щелкнуть по кнопке ОК. Появится диалоговое окно Корреляция (рис.15);

Рис.14. Выбор команды Анализ данных

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

5) выбрать параметры вывода. В данном примере - установить переключатель «Новый рабочий лист»;

6) щелкнуть по кнопке ОК.

Рис.15. Диалоговое окно Корреляция

На новом рабочем листе получаем результаты вычислений- таблицу значений коэффициентов парной корреляции(рис.16).

Рис.16. Результаты корреляционного анализа

Выбор вида модели

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т.е. объем реализации, имеет тесную связь:

- с индексом расходов r_yX5 =0,816,

• с расходами наматериал r_yX2 = 0,646,

• со временем r_yX1 = 0,678.

Однако факторы Х₁ и Х₅ тесно связаны между собой : r_X1X5=0,96,

что свидетельствует о наличии коллинеарости. Из этих двух пере­менных оставим в модели Х₅ - индекс расходов. Пе­ременные X₁ (время), X₃ (цена изделия) и Х₄ (цена отрасли) также исключаем из модели, т.к. связь их с результативным признаком Y (объемом реализации) невысокая.

После исключения незначимых факторов имеем п=16,k = 2. Модель приобретает вид:

= а_о+а₁Х₂+а₂Х₅.

Оценка параметров модели

На основе метода наименьших квадратов проведем оценку пара­метров регрессии по формуле (3). При этом используем данные, при­веденные в табл.6.

Таблица 6

Непосредственное вычисление (вычисление «вручную») вектора оценок параметров регрессии а согласно формуле (3) весьма гро­моздко, т.к. матрица независимых переменных X имеет довольно вы­сокую размерность (16 х 3), матрица Y- размерности (16 х 1). В табл. 7 приведены размерности матриц - результатов промежуточных дей­ствий.

Таблица 7

Задача существенно упрощается при использовании средств Ex­cel. Операции, предписанные формулой (3) целесообразно проводить с помощью следующих встроенных в Excel функций:

•МУМНОЖ - умножение матриц,

•ТРАНСП - транспонирование матриц,

•МОБР - вычисление обратной матрицы.

Для вычисления вектора оценок параметров регрессии а в Excel необходимо выполнить следующие действия:

1. Ввести данные (табл. 6).

2. Выделить диапазон ячеек для записи вектора а, соответствующий его размерности (3x1) (рис. 16).

3. Используя встроенные в Excel функции, ввести формулу (3), определяющую вектор а.

4. Нажать одновременно клавиши CTRL + SHIFT + ENTER. Появится результат (рис. 17).

Таким образом, имеем

Рис. 16. Выделение диапазона ячеек (3 х 1) для записи вектора оценок параметров регрессии а

Уравнение регрессии зависимости объема реализации от затрат на рекламу и индекса потребительских расходов можно записать в виде:

= -1471,3143 + 9,5684*Х₂+15,7529*Х₅.

Рис. 17. Результат вычислений - вектор оценок параметров регрессии а

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

Применение инструмента Регрессия

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

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

2. в диалоговом окне Анализ данных выберите инструмент Рег­рессия. Щелкните по кнопке ОК;

3. в диалоговом окне Регрессия в поле «Входной интервал F» введите адрес диапазона ячеек, который представляет зависимую пе­ременную Y. В поле «Входной интервал X» введите адреса одного или нескольких диапазонов, которые содержат значения независимых переменных (в рассматриваемом примере - переменные Х₂, Х₅). Если выделены заголовки столбцов, то установить флажок «Метки в пер­вой строке»;

5. выберите параметры вывода. В данном примере – установите переключатель «Новая рабочая книга»;

6. в поле «Остатки» поставьте необходимые флажки;

7. щелкните по кнопки ОК.

Результаты представлены на рис. 18 и заключены в таблицах.

Пояснения к таблице «Регрессионная статистика» (рис. 18)

Рис. 18. Результаты регрессионного анализа, проведенного с помощью Excel

Пояснения к таблице «Дисперсионный анализ» (рис. 18)

Во втором столбце таблицы дисперсионного анализа (рис. 18) со­держатся коэффициенты уравнения регрессии а₀, а₁ а₂, в третьем столбце содержатся стандартные ошибки коэффициентов уравнения регрессии, в четвертом - F-статистика, используемая для проверки значимости коэффициентов уравнения регрессии.

Рис.19. График остатков

Оценка качества модели

В таблице «Вывод остатка» (рис. 18) приведены вычисленные по модели значения и значения остаточной компоненты е.

Исследование на наличие автокорреляции остатков проведем с помощью d-критерия Дарбина - Уотсона. Для определения величины d-критерия воспользуемся расчетной таблицей 7.

Имеем:

.

В качестве критических табличных уровней при п = 16, двух объ­ясняющих факторах при уровне значимости = 0,05 возьмем величи­ны вd_L = 0,98 и d_U=1,54 (приложения А и Б). Расчетное значение d = 1,3567 попало в интервал от d_L= 0,98 до d_U =1,54 (рис.20)

Таблица 7

Рис. 20. Сравнение расчетного значения d-критерия Дарбина -Уотсона с критическими значениями вd_L и d_U

Так как расчетное значение d-критерия Дарбина-Уотсона попало в зону неопределенности, то нельзя сделать окончательный вывод об автокорреляции остатков по этому критерию.

Для определения степени автокорреляции вычислим коэффици­ент автокорреляции и проверим его значимость при помощи критерия стандартной ошибки. Стандартная ошибка коэффициента корреляции рассчитывается по формуле:

Коэффициенты автокорреляции случайных данных должны об­ладать выборочным распределением, приближающимся к нормаль­ному с нулевым математическим ожиданием и средним квадратическим отклонением, равным

Если коэффициент автокорреляции первого порядка r₁ находится в интервале

-1,96 * 0,25 < r₁ < 1,96* 0,25,

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

Используя расчетную таблицу 7, получаем:

.

Так как -0,49 < r₁ =0,3046 < 0,49, то свойство независимости остатков выполняется.

Вычислим для построенной модели множественный коэффици­ент детерминации

.

Множественный коэффициент детерминации показывает долю вариации результативного признака под воздействием включенных в модель факторов Х₂ и Х₅. Т.о., около 86 % вариации зависимой пере­менной (объема реализации) в построенной модели обусловлено влиянием включенных факторов Х₂ (расходы на рекламу) и Х₅ (индекс потребительских расходов).

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

.

Табличное значение F-критерия при доверительной вероятности 0,95, степенями свободы ₁=k=2 и ₂=(n-k-1)=16-2-1=13 составляет F_табл=3,8.

Поскольку

F_факт=39б599 F_табл=3,8,

то уравнение регрессии следует признать адекватным.

Значимость коэффициентов уравнения регрессии а₁ и а₂ оценим с использованием t-критерия Стьюдента:

t_a1=a₁/S_a1=9,5684/2,2659=4,2227,

t_a2=a₂/S_a2=15,7529/2,4669=6,3857.

Табличное значение t-критерия Стьюдента при уровне значимо­сти 0,05 и степенях свободы (16-2-1) = 13 составляет t_ma6n =2,16. Так как

t_a1=4,2227 t_ma6n =2,16,

t_a2=6,3857 t_ma6n =2,16.

то отвергаем гипотезу о незначимости коэффициентов уравнения регрессии а₁ и а₂.

Влияние факторов на зависимую переменную

Проанализируем влияние включенных в модель факторов на за­висимую переменную по модели. Учитывая, что коэффициенты рег­рессии невозможно использовать для непосредственной оценки влия­ния факторов на зависимую переменную из-за различия единиц изме­рения, вычислим соответствующие коэффициенты эластичности, -коэффициенты:

,

,

,

.

Таким образом, при увеличении расходов на материл на 1 % ве­личина объема реализации изменится приблизительно на 0,3 %, при увеличении расходов на 1 % величина объема реа­лизации изменится на 5,5 %.

Кроме того, при увеличении затрат на материалы на 4,9129 ед. объ­ем реализации увеличится на 47 тыс. руб. (0,4569*102,865147), при увеличении расходов на 4,5128 ед. объем реализа­ции увеличится на 71 ед. (0,6911*102,865171).

Точечное и интервальное прогнозирование

Найдем точечные и интервальные прогнозные оценки объема реализации на два квартала вперед.

Для построения прогноза результативного признака Y и оценок прогноза необходимо определить прогнозные значения, включенных в модель факторов Х₂ и Х₅. В п. 1.3 на рис. 10 приведен результат по­строения тренда и прогнозирования по тренду для временного ряда «Индекс расходов».

В качестве аппроксимирующей функции выбран полином второй степени - парабола:

Х₅ = 97,008 + 1,739 t - 0,0488 t²,

по которой построен прогноз на два шага вперед, причем прогнозные значения на 17-ый и 18-ый периоды соответственно составляют:

Х₅(17) = 97,008+1,739*17-0,0488*17²= 112,4678,

Х₅(18) = 97,008 +1,739*18-0,0488* 18²= 112,4988.

Описанным выше способом (п. 1.3) построим линию тренда для временного ряда «Расходы на материалы» (рис. 20).

Рис. 20. Результат построения тренда и прогнозирования по тренду для временного ряда «Расходы на материал»

Для фактора Х₂ «затраты на материал» выбираем полиномиальную модель пятой степени (этой модели соответствует наибольшее значе­ние коэффициента детерминации):

Х₂= -0,00055157*t⁵ + 0,02915029*t⁴ - 0,55145744 *t³ + 4,31897327*t² - 11,61564797*t + 12,83076923.

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

Прогнозные значения на 17-ый и 18-ый периоды соответственно составляют:

Х₂(17) = 5,7485,

Х₂(18) = 4,8485.

Для получения прогнозных оценок переменной 7 по модели

=-1471,3143 + 9,5684*X₂+15,7529*X₅

подставим в нее найденные прогнозные значения факторов Х₂ и Х₅, получим:

(17) =-1471,3143 + 9,5684*5,7485 + 15,7529*112,4678 = 355,3805,

(18) = -1471,3143 + 9,5684*4,8485 + 15,7529*112,4988 = 347,2573.

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

верхняя граница прогноза: (n+l) + U(l),

нижняя граница прогноза: (n+l) - U(l),

где

, V_пр=X_пр^T(X^TX)^-1X_пр.

Имеем

,

t_кр=2,16 (по таблице при =0,05 и числе степеней свободы 13),

, .

Тогда с использованием Excel , имеем

V_пр(17)=X_пр^T(X^TX)^-1X_пр=0,2300,

U(1)=41,473*2,16*=42,9714

и

V_пр(18)=X_пр^T(X^TX)^-1X_пр=0,2613,

U(2)=41,473*2,16*=45,7964.

Результаты прогнозных оценок модели регрессии представим в таблице прогнозов (табл. 8).

Таблица 8