3. Обязательные задания для выполнения обучающимися

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

Пример 1.

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

    Необходимо составить матрицу парных коэффициентов корреляции и на ее основе дать рекомендации о включении в модель тех или иных факторов.

Решение.

Парные коэффициенты корреляции вычисляются на основе формул (1) и (2):

Таблица 1.
Объем реализации     Время     Реклама     Цена     Цена конкурента     Индекс потребительских расходов
y     x1     x2     x3     x4     x5
126     1     4     15,0     17,0     100,0
137     2     4,8     14,8     17,3     98,4
148     3     3,8     15,2     16,8     101,2
191     4     8,7     15,5     16,2     103,5
274     5     8,2     15,5     16,0     104,1
370     6     9,7     16,0     18,0     107,0
432     7     14,7     18,1     2,02     107,4
445     8     18,7     13,0     15,8     108,5
367     9     19,8     15,8     18,2     108,3
367     10     10,6     16,9     16,8     109,2
321     11     8,6     16,3     17,0     110,1
307     12     6,5     16,1     18,3     110,7
331     13     12,6     15,4     16,4     110,3
345     14     6,5     15,7     16,2     111,8
364     15     5,8     16,0     17,7     112,3
384     16     5,7     15,1     16,2     112,9

 

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

Мы пойдём другим путём. Копируем таблицу в EXCEL.

Рис.1. Исходные данные задачи

Воспользуемся надстройкой «Анализ данных» пакета анализа EXCEL. Пакет анализа представляет собой надстройку, т. е. программу, которая доступна при установке Microsoft Office или Excel. Чтобы использовать надстройку в Excel, необходимо сначала загрузить ее. Как загрузить данный пакет для Microsoft Excel 2013, Microsoft Excel 2010, Microsoft Excel 2007.
Использование пакета анализа Microsoft Excel 2013. При проведении сложного статистического или инженерного анализа можно упростить процесс и сэкономить время, используя надстройку «Пакет анализа». Чтобы выполнить анализ с помощью этого пакета, следует указать входные данные и выбрать параметры. Расчет будет выполнен с использованием подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Некоторые инструменты позволяют представить результаты анализа в графическом виде.
Функции анализа данных можно применять только на одном листе. Если анализ данных проводится в группе, состоящей из нескольких листов, то результаты будут выведены на первом листе, на остальных листах будут выведены пустые диапазоны, содержащие только форматы. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности.

Ниже описаны инструменты, включенные в пакет анализа. Для доступа к ним нажмите кнопку «Анализ» данных в группе Анализ на вкладке Данные. Если команда Анализ данных недоступна, необходимо загрузить надстройку «Пакет анализа».

Загрузка и активация пакета анализа

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

В окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Рис. 2. Меню надстройки «Анализ данных»

В появившемся окне «Корреляция» ввести диапазон данных «у» и все «х» (рис.2.).

Рис. 3. Окно «Корреляция»

Для этого нужно в окошке входной интервал ввести диапазон данных A4:F20 и так как в сроке A4:F4 текстовые переменные установить птичку в окошке метки в первой строке.

Рис. 3. Окно корреляция с введенным диапазоном данных

Нажать ОК. На новом листе появится матрица парных коэффициентов корреляции (корреляционная матрица). Первый столбец которой указывает на степень корреляционной связи факторов х1-х5 с результативным признаком у. Остальные столбцы указывают на корреляционную зависимость факторов между собой (рис.4.).

Рис.4. Матрица парных коэффициентов корреляции

Как видно из рис.4., парный коэффициент корреляции факторов х1-х5 равен 0,96 (), что свидетельствует о наличии мультиколлинеарности. Одним из условий регрессионной модели является предположение о линейной независимости объясняющих переменных (факторных признаков), т.е. решение задачи возможно только тогда, когда столбцы и строки исходных данных линейно независимы. Для экономических показателей это условие выполняется не всегда. Линейная или близкая к ней связь между факторами называется мультиколлинеарностью и приводит к линейной зависимости нормальных уравнений, что делает вычисление параметров либо невозможным, либо затрудняет содержательную интерпретацию параметров. Из этих двух переменных оставим в модели xj — индекс потребительских доходов. Да и экономический смысл подсказывает, что в индексе потребительских расходов информации больше, так как он также является функцией времени.

Для этого скопируем табл. 1. на новый лист. И на нём удалим столбец с переменной х1. Чтобы удалить столбец с переменной х1 нужно щелкнуть правой кнопкой мыши на названии столбца А и в появившемся меню выбрать — удалить (рис.5.).

Рис.5. Удаление столбца с фактором х1. Получим табл.2.

 

Теперь можно преступить к регрессионному анализу. Команда «Данные/Анализ данных/Регрессия». Появится диалоговое окно «Регрессия» в котором нужно ввести входные интервалы х и у и установить птички в окошках «Метки» и «Уровень надёжности 0,95». Установка уровня надёжности 0,95 свидетельствует о выборе уровня значимости 5%.

Рассчитывается F статистика и сравнивается с порогом. Пороговое значение выбирается по таблицам F распределения Фишера. В программе F, а можно воспользоваться полем «Значимость F». При 5% уровне значимости его значение должно быть меньше 0,05 для того, чтобы выбранное уравнение регрессии было значимым. У нас «Значимость F» =1,96*10-5 т.е. выбранное нами линейное уравнение регрессии значимо.

 

Таблица 3.
Объем реализации     Реклама     Цена конкурента     Индекс потребительских расходов
y     x2     x4     x5
126     4     17     100
137     4,8     17,3     98,4
148     3,8     16,8     101,2
191     8,7     16,2     103,5
274     8,2     16     104,1     
370     9,7     18     107     
432     14,7     2,02     107,4     
445     18,7     15,8     108,5     
367     19,8     18,2     108,3     
367     10,6     16,8     109,2     
321     8,6     17     110,1     
307     6,5     18,3     110,7     
33J     12,6     16,4     110,3     
345     6,5     16,2     111,8     
364     5,8     17,7     112,3     
384     5,7     16,2     112,9     

 

Из рис.7, видно, что коэффициент при переменной х3 незначим, так как Р-значение=0,62. Следовательно переменную х3 нужно также исключить из модели. Чтобы сохранить промежуточные таблицы, опять копируем табл.2. на лист 3. и удаляем столбец с х3. Получим таблицу 3.

Опять вычисляем регрессию с помощью программы «Анализ данных». Окно программы «Регрессия» будет таким же как на рис.6. только изменится блок ячеек «Входной интервал х». Вывод итогов будет следующим (рис.8).

Рис.8. Вывод итогов регрессии

Поскольку всё  еще остаётся незначимым, то исключаем х4.

Окончательные итоги регрессии приведены на рис.9. Для повышения достоверности полученных результатов, оба теста повторяются с уровнем значимости в 1%. Он устанавливается с помощью уровня надёжности 0,99. В нашем случае коэффициенты уравнения регрессии а2 и а1, а также само уравнение регрессии будут значимы.

Рис. 9. Вывод итогов регрессии

В результате получим следующую пространственную регрессионную модель.

y = 9.57 x2 + 15.75 x5

Экономический смысл полученного уравнения регрессии. Если в рекламу вложить миллион рублей, то выручка от реализации продукта увеличится на 9.57 миллионов рублей. Вот почему мы каждый фильм по телевизору смотрим по 3-4 часа. Увеличение индекса потребительских расходов населения на 10% увеличивает выручку от реализации продукта на 1,6 миллиона руб.