Алгоритм прогнозирования объёма продаж в MS Excel

Современная наука далеко продвинулась в технологиях прогнозирования. Разработаны эффективные методы по нейросетевому прогнозированию, методы нечёткой логики и т.п. Пакеты программ, реализующие указанные методы не всегда доступны обычным пользователям. Многие задачи прогнозирования можно решить в распространенном редакторе электронных таблиц MS Excel, используя известные методы исследования операций.

Допустим необходимо решить задачу прогнозирования объёма продаж продукции, реализация которой имеет сезонный характер. Если выявить параметр изменения цикла сезонных колебаний, то можно применить полученный ряд прогнозировании с помощью аддитивных и мультипликативных моделей.

Аддитивную модель мы можем записать в виде следующей формулы:

F = T + S + E где: F – наше прогнозируемое значение; Т – выбранный тренд; S – заданный сезонный компонента; Е – ошибка модели.

Мультипликативные модели используются в исследованиях, так как временные ряды содержат величину сезонного параметра в качестве доли значения тренда. Эти модели можно описать следующей формулой:

F = T х S x E

Аддитивная модель содержит постоянную величину сезонного параметра, мультипликативная же включает возрастающую либо убывающую величину этого параметра.

Прогноз объема продаж продукции сезонного типа можно реализовать по следующему алгоритму:

• 1 .Находим такой тренд, который даст возможность аппроксимации фактических данных наилучшим образом.

• 2 .Вычтем из объёма продаж данное значение тренда и найдем значение

• 3 .Определим ошибки данной модели вычислив разность фактического значения и значения возвращаемого моделью.

• 4 .Итак получим следующую модель прогноза продаж:

• 5 .Разработанная модель позволяет построить прогноз объёма продаж, использовав экспоненциальное сглаживание с учетом будущего изменения экономических условий построения трендовой модели. Данная поправка нивелирует ошибки адаптивных моделей, позволяя учесть изменения рынка.

сезонного параметра так, чтобы сумма равнялась нулю.

F = T + S ± E где: F - прогнозируемое значение; Т - тренд; S - сезонная компонента; Е - ошибка модели.

Fnpt = a Fф t-1 + (1-а) Fм t где: Fnp t - прогнозное значение объёма продаж; Fф t-1 - фактическое значение объёма продаж в предыдущем году; Fм t - значение модели; а — константа сглаживания

Применение алгоритма рассмотрим на следующем примере.

Исходная информация представлена в табл. 1.

Таблица 1. Фактические объёмы реализации продукции

Необходимо получить прогноз уровня продаж рассмотренной продукции на следующий год в разбиении по месяцам.

Решение данной задачи рассмотрим в пакете MS Excel, что существенно сокращает сложность построения модели.

Находим среднеквадратическую ошибку модели (Е) по формуле: Е= ∑ О2 : ∑ (T+S)2

где: Т- трендовое значение объёма продаж; S - сезонная компонента; О -отклонения модели от фактических значений Е= 0,003739 или 0.37 %.

Полученное значение ошибки показывает, что модель хорошо аппроксимирует фактические данные, учитывая экономические параметры изменяющегося рынка.

Построим графически следующую модель прогнозирования:

F = T + S ± E

-*- Фылпчегтке тячмг • Модель гл жжчг тюлкчппппда- -, iyечу

Новые экономические условия требую уточнения модели по данным мониторинга объёмов реализации товара, меняя данные расчетной базы, по которой получена модель.