Задание № 4

Применение регрессионного анализа в ходе принятия решения

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

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

1. Выделите данные в диапазоне А2:В20.

2. Выберите команду ВставкаДиаграммаНа этом листе либо щёлкните на кнопке Мастер диаграмм.

3. Выделите область построения диаграммы на рабочем листе.

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

5. На шаге 2 работы средства Мастер диаграмм выберите тип диаграммы (график). Щёлкните на кнопке Далее

6. На шаге 3 работы средства Мастер диаграмм выберите вид графика. Щёлкните на кнопке Далее.

7. На шаге 4 работы средства Мастер диаграмм убедитесь, что переключатель Ряды данных находятся установлен в положении В столбцах. Установите значение 1 в поле Считать стлб. Метками оси Х. Щёлкните на кнопке Далее.

8. На шаге 5 работы средства Мастер диаграмм добавьте легенду и названия осей и щёлкните на кнопке Готово.

9. Когда диаграмма появиться в вашем рабочем листе, дважды щёлкните на нём для редактирования. Граница диаграммы будет заштрихованной.

10. Чтобы выбрать ряд, щёлкните на любом из маркеров ряда. Выберите команду ВставкаЛиния тренда и щёлкните, если необходимо, на корешке вкладки Тип. Выберите тип аппроксимации Линейная.

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

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

График также содержит следующее уравнение:

Y=0,543x + 5221,1

Это уравнение регрессии для данных в рабочем листе. Оно показывает зависимость денежной суммы, израсходованной на рекламу определённой продукции, и объёмом продаж в единицах этой продукции. В данном уравнении y означает объём продаж, оно даёт наиболее точную оценку объёма продаж в единицах продукции.

Это, однако, не означает, что, если вы знаете сумму (в долларах), потраченную вами на рекламную кампанию, то вы сможете точно определить объёмы продаж. Например, подставьте в уравнение значение $55400 (сумма, потраченная на рекламу) вместо х, и получите ответ – 35302 единицы продукции. Обратите внимание, что значение $55400 приведено на рис. 1 в качестве одного из фактических результатов наблюдений, однако этому значению соответствует совсем другое значение объёма продаж – 9554. Итак, повторяю: на основе имеющихся у вас данных регрессия даёт наиболее точную оценку (35302 единицы), но не абсолютно точный прогноз.

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

Вы могли бы, например, подставить в уравнение регрессии значение $200000 и получить в качестве приблизительной оценки объёма продаж в единицах продукции 113818. Это вовсе не обязательно означает, что , потратив на рекламу $200000, вы продадите 113818 единиц этой продукции (хотя, конечно, это и не исключено). Существует множество факторов, кроме суммы расходов на рекламу, влияющих на изменения в объёмах продаж, и эти факторы (например, продажная цена единицы продукции) в уравнении регрессии никак не отражены.

Даже если связь между переменными носит причинный характер, вы ни коим образом не можете быть уверены в направлении этой причинности. Вполне вероятна ситуация, когда отдел маркетинга увеличит расходы на рекламу продукции после того, как увеличатся объёмы её продаж. В этом случае именно объёмы продаж будут влиять на размеры сметы на рекламу, а не наоборот.

Правая часть уравнения регрессии – число 0,543 – называется угловым коэффициентом, а число 5221,1 – отрезком, отсекаемым на оси ординат. Коэффициент наклона представляет собой меру наклона линии тренда: чем больше число, определяющее этот коэффициент, тем круче линия тренда. Коэффициент наклона может быть отрицательным числом; и в этом случае линия тренда будет идти из верхнего левого угла графика в нижний правый. Если вам, например, пришлось бы составить график соответствия цены единицы продукции объёмам продаж в единицах продукции, полученная линия тренда, вероятнее всего, имела бы отрицательный наклон: чем больше цена, тем меньше единиц продукции продаётся.

Отрезок на оси ординат указывает, в каком месте линия тренда пересекает ось y (вертикальную ось). В данном случае – это число 52221,1. Это означает следующее: Если мы потратим на рекламу данной продукции $0,то , по предварительным оценкам, мы продадим 5220 экземпляров.

На графике также указано значение преобразованной модели регрессии R²(приближенная оценка её фактического значения). Оно выражает долю дисперсии в у (в данном примере – в расходах на рекламу в долларах).

Представим, что между расходами на рекламу (в долларах) и объёмами продаж (в единицах продукции) существует взаимосвязь (это необязательное предположение, но с его помощью легче понять суть дальнейшего изложения). Когда вы изменяете смету на рекламу продукции, объёмы её продаж также изменяются. Когда изменяются объёмы продаж данной продукции, изменяется дисперсия объёмов продаж всех видов продукции. Чтобы доказать это, введите какие-нибудь числа в рабочий лист Excel – скажем, в ячейки А1:А5. Затем в другую ячейку введите следующую формулу:

=ДИСП(А1:А5)

Теперь, когда вы изменили все числа в ячейках А1:А5, можно увидеть, что значение, возвращённое формулой, также изменилось.

Изменяя значения в А1:А5, вы оказываете влияние на их дисперсию. Знпачение R², показанное на рис.1, равно 0,7081. Оно означает, что приблизительно 71% меры изменчивости объёмов продаж в единицах продукции связан с мерой изменчивости расходов на рекламу в долларах.

Но почему R²? Потому что значение R² является квадратом коэффициента корреляции. Если бы вы ввели в рабочий лист, показанный на рис.1, следующую формулу:

=КОРРЕЛ(А3:А20; В3:В20)2

она вернула бы число 0,7081, т.е. значение, совпадающее со значением R² для этих данных.