Прогнозирование с использованием парной регрессии

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

Таблица 15 - Исходные данные для поиска уравнения связи переменных

Выполнение:

Чтобы найти уравнение парной регрессии, которое наилучшим образом опишет изучаемую зависимость, обратимся к графическому методу. С помощью Мастера диаг­рамм построим точечную диаграмму зависимости товарооборота от оборачиваемости товаров (см. рис. 29).

Рисунок 29 - График зависимости товарооборота от товарооборачиваемости товаров

После того как диаграмма построена, необходимо обратиться к команде Excel «Добавить линию тренда» из контекстного ме­ню панели Диаграмма. Учитывая возможности Excel, оце­ним качество аппроксимации базовых данных каждым из пяти предлагаемых окном диалога Линии тренда типом ли­ний: для линейного уравнения R² = 0,8197; для уравнения логарифмической кривой R² = 0,8216; для уравнений полинома 4-й степени R² = 0,8287; для уравнения степенной кривой R² = 0,8155; для уравнения экспоненциальной кривой R² = 0,8142.

Полученные результаты свидетельствуют, что наиболее адекватно (судя по величине R²) отражают зависимость това­рооборота от изменения товарооборачиваемости кривые, пос­троенные на основе уравнений полиномов 4-й степе­ни. На рис. 28 приведена кривая роста, которую описывает уравнение полинома 4-й степени. Рассчитанный Excel коэффициент R² (0,8287) указывает на достаточно высокое качес­тво приближения базовых данных.

Рисунок 30 - Аппроксимация базовых данных полиномиальной кривой роста

Проведем оценку статистической значимости параметров уравнения полинома 4-й степени, построенного на основе соответствующего массива базовых данных (табл. 16, ячей­ки A2:F18) с помощью функции ЛИНЕЙН. Для формирования выходного массива значе­ний параметров уравнения и статистических характерис­тик обозначим диапазон ячеек В20:F24.

При определении в диалоговом окне ЛИНЕЙН аргумен­тов функции формируется следующая формула массива: =ЛИНЕЙН(В3:В18;С3:F18;ИСТИНА;ИСТИНА).

В первой строке массива результатов, отображенного функцией ЛИНЕЙН после нажатия клавиш Ctrl + Shift + Enter (ячейки В20:F24 табл. 18), находим уточненные в ходе математических расчетов значения параметров уравне­ния.

Таблица 16 - Оценка статистической значимости модели регрессии с помощью функции ЛИНЕЙН (уравнение полинома 4-й степени)

Вывод: Модель связи товарооборота (у) и оборачиваемости това­ров (х), построенная на основе уравнения полинома 4-й сте­пени, имеет вид:

у = 3693352 - 419248х + 17765,93х² - 330,07х³ + 2,269х⁴.

Заметьте, что рассчитанный в массиве Статистика ко­эффициент R², равный 0,8287 (ячейка В22), соответствует значению R², приведенному на рис. 27.

В нашем примере (см. табл. 18) значения всех рассчитан­ных параметров уравнения (ячейки В20:F20) меньше по мо­дулю значений их стандартных ошибок (ячейки В21:F21). Следовательно, надежность оценок параметров регрессии не может быть признана удовлетворительной и составленную модель не следует применять для прогнозирования исследуе­мого показателя.

Используя линии тренда Excel, найдем уравнение другой кривой, для которой значение R₂ будет наибольшим. Таким об­разом, наилучшее качество аппроксимации исходных дан­ных достигается в случае уравнения логарифмической кри­вой (см. рис. 31).

Рисунок 31 - Аппроксимация базовых данных логарифмической кривой роста

Статистическую надежность сделанной оценки можно проверить с помощью F-критерия, расчетное значение которого в случае парной регрессии определяют на основе следующей формулы:

.

Итак, для уравнения парной регрессии с коэффициентом R², равным 0,8216, F = 64,5.

По таблице F-распределения (см. прил. А) находим, что при 5%-ном уровне значимости для распределения Фишера с (1;14) степенями свободы F_крит = 4,60. Поскольку 64,5 > 4,60, можно сделать вывод об адекватности и достаточной точности модели. Следовательно, при условии сохранения существовавшей ранее взаимосвязи переменных на период упреждения модель вида у = -49619ln(х) + 216503 мо­жет быть использована для прогнозирования.

Чтобы составить прогноз объема товарооборота на 17-й месяц, осталось определить значение переменной х (т.е. оборачиваемости товаров) для данного месяца. Это зна­чение может быть получено (в зависимости от характера по­казателя) на основе экстраполяционных методов, методов экспертных оценок или непосредственно задано составите­лем прогноза. Так, будем полагать, что рассматриваемое тор­говое предприятие на 17-й месяц планирует проведение оп­ределенных рекламных мероприятий и выставки-продажи, что по оценкам специалистов позволит ускорить оборачивае­мость товаров в среднем на 1,5 дня. В этом случае товарооборачиваемость по предприятию в прогнозируемом месяце сос­тавит 37,5 дня.

Прогноз объема товарооборота можно получить, создав соответствующую модели формулу в любой (предваритель­но выделенной) ячейке рабочего листа. Выделим, к приме­ру, ячейку В19 (см. табл. 17) и внесем в нее следующую формулу: «=-49619*ln(37,5)+216503». После нажатия клавиши Enter в ячейке В19 отразится прогноз объема товарооборота на 17-й месяц, равный 36 667 тыс. руб.

Таблица 17 - Прогноз товарооборота на основе уравнения логарифмической кривой