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

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

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

Выполнение:

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

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

Рисунок 3.3 - График развития товарооборота торгового предприятия	Рисунок 3.4 - Оценка прогноза товарооборота торгового предприятия на основе линейной кривой роста
Рисунок 3.5 - Оценка прогноза товарооборота на основе логарифмической кривой роста	Рисунок 3.6 - Оценка прогноза товарооборота на основе полиномиальной кривой роста (степень 3)
Рисунок 3.7 - Оценка прогноза товарооборота на основе степенной кривой роста	Рисунок 3.8 - Оценка прогноза товарооборота на основе экспоненциальной кривой роста

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