<<
>>

4.2 Диагностика проблем с использованием инструментов регрессионного анализа

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

Таблица 7

Расходы, связанные с покупкой товаров и оплатой услуг Покупка товаров Оплата услуг Всего денежных расходов 362,8 107,1 547,3 368,0 93,4 560,7 409,1 94,9 595,5 387,7 84,3 549,1 372,9 79,8 534,5 366,3 84,3 509,6 377,5 87,2 546,3 404,5 85,2 476,6 542,3 90,5 606,2 524,7 100,6 690,5 544,5 97,4 729,9 629,9 107,5 959,0 Для построения регрессионного уравнения, его анализа и графической формализации с помощью инструментов MS Excel необходимо выполнить следующие операции.

Ввести исходные данные, например, заполнить ячейки А7:В20.

Выбрать команду Вставка ^ Диаграмма, либо щелкнуть на кнопке Мастер диаграмм.

На первом шаге работы инструмента Мастер диаграмм необходимо выбрать тип диаграмм. Рекомендуется выбрать Точечная (рисунок 9) Щелкнуть кнопку Далее.

На втором шаге работы инструмента Мастер диаграмм необходимо уточнить, что в текстовом поле Диапазон введены адреса ячеек, содержащих данные необходимые для построения диаграммы (рисунок 10). Щелкнуть кнопку Далее.

На третьем шаге работы инструмента Мастер диаграмм необходимо выбрать параметры диаграммы: название осей, легенду, координатную сетку и т. д. (рисунок 11). Щелкнуть кнопку Далее.

На четвертом шаге необходимо выбрать место размещения диаграммы и щелкнуть на кнопке Готово. Когда диаграмма появится на рабочем лис-

те, необходимо щелкнуть правой кнопкой мыши на любом из маркеров ряда. Появится контекстного меню (рисунок 12) на котором необходимо выбрать команду Добавить линию тренда. Появится диалоговое окно, показанное на рисунке 13.

Рисунок 9 - Мастер диаграмм.<div class=

Шаг 1" />

Рисунок 9 - Мастер диаграмм. Шаг 1

Рисунок 11- Мастер диаграмм Шаг 4

Рисунок 11- Мастер диаграмм Шаг 4

Рисунок 10 - Мастер диаграмм Шаг 2

Рисунок 10 - Мастер диаграмм Шаг 2

Рисунок 12- Контекстное меню

Рисунок 12- Контекстное меню

Рисунок 13 - Окно выбора типа аппроксимации

Рисунок 13 - Окно выбора типа аппроксимации

7. В соответствии с рисунком 13 выбрать тип аппроксимации (например Линейная) и щелкнуть на вкладке Параметры.

Рисунок 14 - Окно установки параметров тренда

Рисунок 14 - Окно установки параметров тренда

В появившемся окне (рисунок 14) проверить, установку флажков для показа уравнения на диаграмме и численное значение достоверности аппроксимации (R 2). Щелкнуть по кнопке ОК. Появляется окно с окончательными результатами расчетов (рисунок 15) .

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

Величина достоверности ап-

Л2

проксимации R представляет собой квадрат коэффициента корреляции между (у) и (х) и на физическом уровне показывает долю дисперсии в общем объеме денежных расходов, связанную с дисперсией денежных расходов на покупку товаров.

Вывод. Анализируя полученные результаты можно сделать вывод о том, что около 78% расходов населения Пензенской области приходится на покупку товаров. Данный вывод может оказаться полезным при принятии решения о формировании потребительской корзины.

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

Построить уравнение множественной регрессии средствами MS Excel можно двумя способами: с помощью функции ЛИНЕЙН или же с помощью пакета Анализ данных.

Результаты расчетов по первому способу приведены на рисунке 16.

Рисунок 15 - Окончательный вариант расчетов

Рисунок 15 - Окончательный вариант расчетов

ЛИНЕЙН(С7: С18; А7: В18; ИСТИНА;ИСТ ИНА) 4,271277911 1,024704 -238,85036 2,287451269 0,840517526 23,71626658 160175,6991 0,22504 58,11124 9

30392,25 179,53751 #Н/Д #Н/Д #Н/Д Рисунок 16 - Результаты расчетов

Коэффициенты регрессии и свободный член уравнения приведены в верхней строке рисунка 16. При составлении уравнения регрессии следует иметь ввиду, что функция ЛИНЕЙН возвращает коэффициенты регрессии не в том порядке, в котором расположены изменяемые переменные в рабочем листе.

Тогда уравнение регрессии имеет вид

у = - 238,85 + 1,025х1 + 4,27х2 , (3)

где у - общие расходы;

х1 - расходы, связанные с покупкой товаров; х2 - расходы, связанные с оплатой услуг.

Во второй строке рисунка приведены стандартные погрешности (ошибки) коэффициентов регрессии.

В общем случае они характеризуют стандартные отклонения выборочного среднего.

А2

В третьей строке рассчитан квадрат коэффициента корреляции R и

приведена величина стандартной погрешности (ошибки) аппроксимации.

В четвертой строке приведено значение F - статистики, необходимой для проверки адекватности уравнения регрессии (условия является ли ус-тановленная связь случайной или же нет). Кроме того, здесь же показано число степеней свободы, которое необходимо для определения критического табличного значения критерия Frf.

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

Л2

общей суммой квадратов, тем больше значение коэффициента корреляции R .

Используя данные расчетов, оценим адекватность полученного уравнения регрессии. Для определения Frf можно воспользоваться либо статистической функцией ГРАСПОБР ( ) табличного процессора MS Excel, либо таблицей распределения Фишера - Снедекора (F - распределения), приведенной в [ 10] на с. 499. Входом в таблицу являются величины v1 - число переменных в уравнении регрессии и v2 - число степеней свободы. Для уровня надежности 95% , v1 =2 и v2 = 9 имеем F^ = 4,26. Так как Fp = 23,71 значительно больше Frf = 4,26 то можно сделать вывод, что полученное уравнение адекватно описывает рассматриваемое явление.

Оценку значимости коэффициентов уравнения регрессии выполним с использованием t - статистики, которая рассчитывается путем деления соответствующего коэффициента регрессии на его стандартную погрешность (ошибку). В результате получим:

4р - статистика для первого коэффициента =1,025/ 0,225 =4,55;

-1р - статистика для второго коэффициента =4,271/ 2,287 =1,49. Для определения 1кр можно воспользоваться либо статистической функцией СТЬЮДРАСПОБР ( ) табличного процессора MS Excel, либо таблицей распределения Стьюдента (t - распределения), приведенной в [ 10 ] на с. 493. Входом в таблицу является параметр v - число степеней свободы. Для уровня надежности 95% и v = 9 имеем 1кр = 2,26. Так как 1р = 4,55 для первого коэффициента больше 1кр = 2,26 то можно сделать вывод, что первый коэффициент является значимым. И наоборот второй коэффициент является незначимым и его можно исключить из уравнения.

Рассмотрим построение уравнения более удобным вторым способом с помощью пакета Анализ данных и его инструмента Регрессия

В соответствии с изложенной выше последовательностью активизировать Пакет анализа и выбрать из списка инструмент Регрессия. Появляется окно диалога, показанное на рисунке17.

Ввести ячейки в поле Входной интервал У и поле Выходной интервал Х, установить флажок Уровень надежности 95, указать Выходной интервал и

нажать кнопку ОК. Результаты расчетов показаны на рисунке 18.

Рисунок 17 - Окно диалога

Рисунок 17 - Окно диалога

Регрессионная статистика Множественный R 0,916797429 R-квадрат 0,840517526 Нормированный R-квг 0,805076976 Стандартная ошибка 58,11124159 Наблюдения 12 Дисперсионный анализ

df SS MS F Значимость F Регрессия 2 160175,6991 80087,84954 23,71626658 0,00025835 Остаток 9 30392,24759 3376,916399 Итого 11 190567,9467 Коэффициент Стандартная ошибка t-статистика P-Значение Нижние 95% Y- пересечение -238,850358 179,5375101 -1,330364657 0,216121449 -644,9927319 Переменная X1 1,024703982 0,225040296 4,553424439 0,001379169 0,515627077 Переменная X 2 4,271277911 2,287451269 1,867265095 0,094710635 -0,903300306 Рисунок 18 - Результаты расчета

Уравнение регрессии, полученное с помощью инструмента Регрессия , практически не отличается от полученного с помощью функции ЛИНЕЙН, однако приведенный сопутствующий статистический материал гораздо удобнее и полнее описывает рассматриваемый пример и раскрывает его физическую сущность.

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

Нормированный R - квадрат позволяет произвести оценку объема ис-следуемой выборки. Так в частности, при увеличении объема выборки, численное значение Нормированного R - квадрат приближалось бы к фактическому значению R - квадрат.

Вторая таблица рисунка 18 показывает результаты дисперсионного анализа.

В третьей таблице помимо коэффициентов регрессии и их стандартных погрешностей (ошибок) приведены расчетные t - статистики для каждого параметра уравнения регрессии, а также величины их доверительных интервалов. Кроме того, в таблице приводится р - значение, называемое так же р - уровень. Этот показатель находится в убывающей зависимости от уровня надежности формируемых статистических данных. Он показывает вероятность появления ошибки, связанной с распространением полученного результата на всю статистическую совокупность в целом. Например, р - уровень = 0,05 (1/20) показывает, что имеется 5% вероятность, что найденная в выборке связь между переменными является лишь случайной особенностью данной выборки. С помощью параметр р -значение имеется возможность предварительной оценки, без использования специальных статистических таблиц, значимости коэффициентов уравнения регрессии. Например, из третьей таблицы рисунка 18 видно, что р - значение второй переменной большее 0,05, что может служить основанием вывода о не значимости второй переменной. Этот вывод нами установлен ранее путем сравнения расчетного и критического значений t - статистик.

Учитывая, что уравнение регрессии адекватно описывает изучаемое явления, а также то, что t -статистика и р - значение для переменной х2 не значительно отличаются от критических значений уровня надежности, принимаем решение оставить переменную х2 в уравнении регрессии.

Вывод. В целом, выполненные исследования позволяют сделать вывод о том, что около 92% всех денежных расходов населения Пензенской области тратится им на покупку товаров и оплату услуг, причем около 80% из них расходуется на покупку товаров и лишь примерно 12 % на оплату услуг. Другие виды расходов, например покупка валюты, ценных бумаг и т.д. можно считать статистически не значимыми. Полученный вывод может быть использован при принятии управленческого решения по формированию потребительской корзины, расчету прожиточного минимума, оценки перспектив деятельности банковских структур и рынка ценных бумаг.

Полученное регрессионное уравнение может быть использовано и для прогнозирования расходов населения. Наиболее простой способ это подстановка в уравнение регрессии прогнозных значений переменных. Однако, MS Excel представляет более простой и надежный способ прогнозирования с ис-

пользованием функции ТЕНДЕНЦИЯ. Данная функция вычисляет уравнение регрессии так же как это делает функция ЛИНЕЙН, и при необходимости может применяться для новых прогнозных значений переменных.

На рисунке 19 в нижней его части показаны результаты прогнозирования с использованием функции ТЕНДЕНЦИЯ ^Й Потреб. расходы А В С D Э 409,1 94,3 595,5 10 387,7 84,3 549,' 11 372,9 79,8 534,5 12 366,3 84,3 509,6 13 377,5 87,2 546,3 и 404,5 85,2 47Б ,Е! 15 542,3 90,5 606,2 1G 524,7 100,6 690,5 17 544,5 97/ 729,9 18 629,9 107,5 9Э9,0 19 500,0 200,0 {=ТЕНЦЕНЦИЯ(С7:С18;А7:В18;А19:В23)1| 20 400,0 290,0 1238,9 21 450 450 2144,341494 22 EDO ЗОО 1657,355404 23 550 270 1477,931868 2^ 2. Рисунок 19 - Результаты прогнозирования

Изменяя соотношения переменных х1 (ячейка А ) и х2 (ячейка В ), результат прогноза денежных расходов населения отображается в ячейках С19:С23. Приведенная в этих ячейках сумма прогнозных расходов также может служить исходной информацией для принятия соответствующего управленческого решения.

<< | >>
Источник: Кошевой О.С.. Разработка управленческих решений. 2005

Еще по теме 4.2 Диагностика проблем с использованием инструментов регрессионного анализа:

  1. 1.3. ОЦЕНКА КОНКУРЕНТОСПОСОБНОСТИ ОБЪЕКТОВ РЫНКА
  2. ГЛОССАРИЙ
  3. 4.2 Диагностика проблем с использованием инструментов регрессионного анализа
  4. ЗАКЛЮЧЕНИЕ
  5. Разработка методов анализа и оптимизации бизнес-процессов финансово-кредитной организации. Определение этапов рационализации бизнес-процессов
  6. Основные векторы построения и совершенствования инновацион­ной культуры организации
- Бренд-менеджмент - Инновационный менеджмент - История менеджмента - Коммуникационный менеджмент - Кризисный менеджмент - Менеджмент предприятий - Общие вопросы менеджмента - Организационное поведение - Проектный менеджмент - Стратегический менеджмент - Управление персоналом - Управление рисками - Финансовый менеджмент -
- Аудиторская деятельность - Банки - Бизнес - Бухгалтерский учет - Кредит - Маркетинг - Менеджмент - Философия - Финансы - Экономика -