Решите задачу практического прогнозирования с использованием встроенных функций Excel для быстрого вычисления коэффициента детерминации R2. Используйте данные за 2010 год.
Таблицы с исходными данными по годам для задачи прогнозирования
(ВАРИАНТ 3)
Месяц 2010
(время t) Производственные затраты тыс. руб.
(фактор Х1) Затраты на рекламу тыс. руб. (фактор Х2) Объемы продаж тыс. руб.
(переменная Y)
янв
905,8 199,8 1282
фев
902,5 211,5 1292,7
мар
903,0 206,8 1228,9
апр
889,8 225,7 1392,6
май 889,8 219 1647,3
июн
892,8 235,7 1672,9
июл
888,3 231,3 1660,5
авг
875,8 241,1 2011,7
сен
883,9 238,1 2351,9
окт
875,1 248,1 2513,9
ноя
871,6 256,9 2468,5
дек
879,8 251,9 2746,2
Обратите внимание на то, что при построении тренда используются не числа, соответствующие датам, а последовательность целых чисел, обозначающих номер по порядку значения ряда данных.
Нужно полное решение этой работы?
Решение
1.
Изображаем исходные данные по переменным-факторам Х1-Производственные затраты и Х2-Затраты с помощью точечных диаграмм.
Также на эти диаграммы добавляем линию тренда (правой кнопкой мыши). В параметрах тренда ставим галочки Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации R^2.
Наблюдается тенденция к снижению Производственные затраты X1 и увеличению Затрат на рекламу Х2.
Эти тенденции задаются с помощью линейных уравнений, которые Excel поместил на диаграммах:
X1=-2,921t+907,17
X2=4,8766t+198,79
Делаем выводы:
производственные затраты с каждым периодом уменьшаются на 2,921 тыс
. руб.;
затраты на рекламу с каждым периодом увеличиваются на 4,8766 тыс. руб.
Коэффициенты детерминации показывают качество уравнений:
производственные затраты на 84,17% зависят от времени; затраты на рекламу на 92,34% зависят от времени.
2.
Для того чтобы спрогнозировать значения месячных объемов продаж на январь-март следующего года (переменной Y), построим уравнение зависимости переменной Y от факторов Х1 и Х2 в виде Y=a0+a1∙X1+a2∙X2
Чтобы рассчитать параметры a0, a1, a2 применяем инструмент Регрессия из пакета Анализ данных.
На выходе получаем таблицы, в которых нам нужен коэффициент детерминации R^2 и искомые параметры уравнения a0, a1, a2.
По коэффициентам из столбца Коэффициенты записываем уравнение зависимости Объемов продаж (Y) от факторов Х1-производственных затрат и Х2-затрат на рекламу:
Y=3347,57-7,50∙X1+22,43∙X2
Коэффициент детерминации R^2 = 0,8227 означает, что Объемы продаж на 82,27% зависят от факторов Х1 и Х2.
3.
Рассчитываем прогнозы для Х1 и Х2, считая что выявленные в переменных Х1 и Х2 тенденции сохраняются.
Январь-март 2014-го года – это периоды с номерами 13, 14, 15.
Точки прогноза факторов на диаграммах
4.
Подставляем спрогнозированные значения Х1 и Х2 в уравнение зависимости Y от Х1 и Х2
Y=3347,57-7,50∙X1+22,43∙X2
и получаем прогнозные значения Объемов продаж
Построим график Объемов продаж Y точками прогноза
Задать вопрос
на новый заказ в Автор24.
