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

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

 

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

 

	А	В	С
	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни
			43,5
			43,0
			43,0
			43,5
			43,0
			42,5
			43,0
			41,5
			42,0
			41,5
			40,5

Продолжение таблицы 3.18

 

	А	В	С
	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни
			40,0
			40,0
			39,0
			39,5
			39,0

 

Выполнение:

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

 

 

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

товарооборачиваемости товаров

 

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

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

 

 

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

 

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

 

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

 

	А	В	С	D	E	F
	Порядковый номер месяца	Объем товарооборота, ден. ед.	Оборачиваемость товаров, дни (х)	х^2	х^3	х^4
			43,5	1892,3	82312,9	3580610,1
				1849,0	79507,0	3418801,0
				1849,0	79507,0	3418801,0
			43,5	1892,3	82312,9	3580610,1
				1849,0	79507,0	3418801,0
			42,5	1806,3	76765,6	3262539,1
				1849,0	79507,0	3418801,0
			41,5	1722,3	71473,4	2966145,1
				1764,0	74088,0	3111696,0
			41,5	1722,3	71473,4	2966145,1
			40,5	1640,3	66430,1	2690420,1
				1600,0	64000,0	2560000,0
				1600,0	64000,0	2560000,0
				1521,0	59319,0	2313441,0
			39,5	1560,3	61629,9	2434380,1
				1521,0	59319,0	2313441,0
	17 (прогноз)		37,5
		2,269166117	-330,0698403	17765,93	-419248	3693351,7
	Статистика	100,6502388	16606,9862
	0,828678371	1050,630858	#Н/Д	#Н/Д	#Н/Д
	13,30168019		#Н/Д	#Н/Д	#Н/Д
	58730919,23	12142077,21	#Н/Д	#Н/Д	#Н/Д

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

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

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

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

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

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

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

 

 

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

 

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