Этап. Параболическая зависимость.

Расчеты этого этапа представлены на рис. 2.

Рис. 2. Квадратичная регрессия

Для проведения этого этапа достаточно внести следующие изменения:

- подвести курсор к линии тренда и нажать правую клавишу. В контекстном меню выбрать пункт Формат линии тренда. Нажать кнопку Тип.Выбрать – полиномиальную степени 2. На диаграмме изменить название: «квадратичная модель»;

- пересчитать остатки. Для чего в ячейку D2 ввести формулу: . Скопировать протягиванием до ячейки D19. Остатки автоматически пересчитаются, а на диаграмме (рис. 2) – анализ остатков автоматически образуется результат.

Этап. Прогнозирование.

Т.к. коэффициент детерминации высок R²=0,9999 (почти 1) и остатки разбросаны в полосе от -6 до 6 ) (что говорит об адекватности и работоспособности модели), то в качестве основы для прогнозов берем модель .

Подставляя вместо t число 19, т.к. июль является 19 месяцем, по отношению к 18-ти предыдущим (по которым велся анализ), и производим расчет:

.

Аналогично, для августа при t=20:

.

Вывод: В июле 2013 г. следует ожидать спрос в размере 862,40 (тыс. шт.), а в августе 996,63 (тыс. шт.).

Примечание. К полученным результатам надо относиться осторожно, т.к. мы осуществляем прогноз в области, которая выходит за область проведения эксперимента.

2. Технология построения линии регрессии с помощью пакета Анализ данных

Пример 2. Изучается зависимость себестоимости продукции y (тыс. руб.) от объема валовой продукции х₁ (млн. руб.) и производительности х₂ (тыс. руб. на чел.). По данным 11 предприятий отрасли (рис. 3, А4:В14, F4:F14) следует построить линию регрессии и провести её анализ. Сделать прогноз относительно предприятия со значениями х₁=8,5 и х₂=22.

Рис. 3

Рис. 4.

ЗАДАНИЕ 1. Парная регрессия.

Исследуется зависимость выпуска готовой продукции на одного рабочего – переменная y от электровооруженности труда на одного рабочего (кВт/ч) – переменная х. Выборочные данные по ряду предприятий приведены ниже. Необходимо:

- в качестве первого приближения построить линейную модель;

- по графику остатков обосновать введение в модель квадратичного члена;

- сделать вывод о работоспособности квадратичной модели;

- сделать прогноз о величине выпуска готовой продукции, если величина электровооруженности равна х=8,5 (кВт/ч).

ЗАДАНИЕ 2. Множественная регрессия.

По данным готовых отчетов 10 предприятий:

y – себестоимость товарной продукции (млн. руб.);

x₁ – объем валовой продукции (млн. руб.);

x₂ – производительность труда (тыс. руб на 1 чел.).

Построить уравнение линейной регрессии . Проверить адекватность и работоспособность модели. Предсказать значение себестоимости y для предприятия с показателями: х₁=155, х₂=15.