Прогнозирование развития показателей с помощью линии тренда Excel

Составить прогноз товарооборота торгового пред­приятия на 17-й месяц (табл. 6) с помощью ко­манды «Добавить линию тренда».

Таблица 6 - Сведения о динамике товарооборота торгового предприятия

Выполнение:

Чтобы составить прогноз развития исследуемого показа­теля, используя линии тренда Excel, сначала необходимо с помощью Мастера диаграмм построить диаграмму (График) его дина­мики на основе базовых данных (ячейки В3:В19 таблицы 6).

Когда диаграмма построена, необходимо щелкнуть правой клавишей мыши на любой точке графика, чтобы открылось контекстное меню, в котором содержится команда «Добавить линию тренда». После ее выбора Excel выведет ок­но диалога Линии тренда, содержащее две основные вкладки: Тип и Параметры.

Вкладка Тип помогает пользователю выбрать тип линии тренда, которая будет аппроксимировать исходные данные. В диалоговом окне предлагается пять типов линий тренда. Для их построения Excel использует модели следующего вида:

- линейную (у = mх + b);

- полиномиальную (у = b + m₁x + m₂x² +...+ m₆х⁶);

- логарифмическую (у = m · ln x + b);

- экспоненциальную (у = m · е^b·x);

- степенную (у = m · х^b).

После задания типа линии тренда выделяют вкладку Па­раметры. Откроется ее окно диалога, в котором пользова­тель определяет следующие важные моменты:

1) количество прогнозируемых периодов и направление прогноза: вперед или назад;

2) когда выбрана линейная, полиномиальная или экспо­ненциальная кривая роста, то в поле Пересечение кривой с осью у в точке 0 задается ее у-пересечение: если данное поле обозначить флажком, то Excel будет искать лучшее уравне­ние кривой, которая на координатной плоскости обязатель­но должна пройти через начало координат;

3) через установку флажка в соответствующих полях ок­на диалога пользователь решает, отражать ли на выходной диаграмме уравнение, на основе которого была построена ли­ния тренда, и размер квадрата коэффициента корреляции r², характеризующий качество аппроксимации.

C помощью ко­манды «Добавить линию тренда» составим сразу пять различных вариантов прогноза товарооборота торгового пред­приятия на 17-й месяц и при этом по r² оценить общее качество моделей, на основе которых они были полу­чены.

Используя возмож­ности Excel по созданию в ячейках рабочего листа формул, с помощью приведенных на графиках уравнений кривых рос­та рассчитаем значения прогноза товарооборота на 17-й месяц (табл. 7).

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

Рисунок 16 - График развития товарооборота торгового предприятия	Рисунок 17 - Оценка прогноза товарооборота торгового предприятия на основе линейной кривой роста
Рисунок 18 - Оценка прогноза товарооборота на основе логарифмической кривой роста	Рисунок 19 - Оценка прогноза товарооборота на основе полиномиальной кривой роста (степень 3)
Рисунок 20 - Оценка прогноза товарооборота на основе степенной кривой роста	Рисунок 21 - Оценка прогноза товарооборота на основе экспоненциальной кривой роста

Вывод: Приведенные на рис. 16-21 графики динамики то­варооборота свидетельствуют, что наибольшая степень при­ближения линии тренда к базовым данным достигнута в слу­чае полиномиальной кривой роста 3-й степени (см. рис. 4, r² = 0,9519), наименьшая — в случае логарифмической кри­вой (см. рис. 3, r² = 0,7779).