Сеть магазинов одежды постоянно расширялась. Однако, в настоящее время у компании нет систематического подхода к выбору новых торговых точек. Место, в котором компания собирается открыть новый магазин, определяется на основе субъективных данных. Представьте, что Вы - руководитель отдела специальных проектов и планирования, Вам поручили создать план открытия новых магазинов. Этот план должен содержать прогноз годового объема продаж во вновь открываемых магазинах. Вы полагаете, что торговая площадь непосредственно связана с объемом продаж и выручки, и хотите учесть этот факт в процессе принятия решения. Разработайте статистическую модель, позволяющую прогнозировть годовой объем продаж на основе размеров нового магазина.
Данные по годовым объемам продаж yi, тыс. руб. и площадям xi, тыс. кв. м.
| i | ||||||||||||
| yi | 3.7 | 3.9 | 6.7 | 9.5 | 3.4 | 5.6 | 3.7 | 2.7 | 5.5 | 2.9 | 10.7 | 7.6 |
| xi | 1.7 | 1.6 | 2.8 | 5.6 | 1.3 | 2.2 | 1.3 | 1.1 | 3.2 | 1.5 | 5.2 | 4.6 |
Требуется:
- Построить выборочное уравнение линейной парной регрессии (найти значения коэффициентов b0, b1).
- Рассчитать значение выборочного коэффициента корреляции rxy, выборочный коэффициент детерминации R2xy
- Построить графики зависимостей yi и ŷi от xi,
Решение:
- Для определения параметров уравнения регрессии строим расчетную таблицу:
(см. Исходные данные) и рассчитываем необходимые значения
| Искомая величина | Расчетная формула | Номер ячейки в таблице | Формула в Excel | Категория функций |
| Выборочный коэффициент регрессии b1 | 
| B19 | НАКЛОН(B2:B13;C2:C13) | стат |
| Выборочный коэффициент регрессии b0 | 
| B20 | ОТРЕЗОК(B2:B13;C2:C13) | стат |
| Теоретические (аппроксимирующие) значения зависимой переменной ŷi | ŷi = b0 + b1xi | G2-G13 | ПРЕДСКАЗ(C2;B2:B13;C2:C13) | стат |
- Рассчитаем значение выборочного коэффициента корреляции и величину выборочного коэффициента детерминации для построенного уравнения регрессии.
| Искомая величина | Расчетная формула | Номер ячейки в таблице | Формула в Excel | Категория функций |
| Выборочный коэффициент корреляции | 
| B21 | КОРРЕЛ(C2:C13;B2:B13) | статистич |
| Выборочный коэффициент детерминации | 
| B22 | КВПИРСОН(B2:B13;C2:C13) | стат |
3.При построении графиков полученных функций воспользуемся Мастером диаграмм. На вкладке Стандартные выбираем точечную диаграмму, позволяющую сравнить пары значений. Нажимаем кнопку Далее.
На вкладке Диапазон данных выделяем диапазон (х,у) и указываем, что ряды находятся в столбцах. Переходим на вкладку Ряд. В поле Графика просматриваем полученный результат. В поле Имя указываем название «у».
Последовательно нажимая кнопку Добавить, добавляем ряды значений ŷi, yi min, yi max аналогичным образом и задаем соответствующим названия. После просмотра результатов нажимаем кнопку Далее.
Задаем параметры диаграммы. На вкладке Заголовки в полях Название диаграммы, Ось Х (категорий) и ось У(значений) задаем соответствующие названия «зависимость годовых объемов продаж от площади магазина.», «Площадь магазина, тыс. кв.м, руб», «Годовой объем продаж, тыс. руб.». На вкладке Линии сетки добавляем основные линии на оси Х (категорий). Остальные вкладки оставляем без изменения. После просмотра результатов нажимаем кнопку Далее.
Помещаем диаграмму на имеющемся листе и нажимаем кнопку Готово.
Параметры полученной диаграммы можно менять, используя меню Диаграмма, или контекстное меню, вызываемое правой кнопкой мыши.
В частности, целесообразно задать новые значения шкалы осей, чтобы расположить графики наилучшим образом. Для этого необходимо выбрать команду Формат оси и на вкладке Шкала задать требуемые величины в полях Минимальное значение, Максимальное значение и Цена основных делений, убрав флажки из соответствующих полей Авто.
Для более наглядного представления результатов необходимо выбрать ряд ур, с помощью контекстного меню выбрать команду Формат ряда данных на вкладке Вид задать параметры линии и маркера (можно также вызвать команду Добавить линию тренда и в поле Линия тренда на вкладке Тип выбрать поле Линейная). Для рядов yi min, yi max аналогичным образом добавляется линия тренда Полиномиальная при значении степени, равном 2 (по умолчанию). С помощью команды Формат линии тренда при необходимости выбирается тип, цвет и толщина линии, а на вкладе Параметры – название аппроксимирующей кривой и величина интервала прогноза вперед или назад на заданное число единиц. Здесь также задается возможность показать уравнение регрессии и коэффициент детерминации в поле диаграммы.