Решите задачу практического прогнозирования с использованием встроенных функций Excel

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 точками прогноза