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

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

Методика, предложенная Кошечкиным С.А., сочетает в себе и простоту, и адекватность анализа. Особенно важно отметить актуальность работы в MS Excel, как наиболее доступном и простом для понимания программном продукте.

Вторым разделом статьи будет использование доработанного алгоритма на примере, который предоставил Кошечкин С.А.

Таким образом, выбирая линию тренда, характеризующую общую тенденцию развития изучаемого явления, необходимо также рассчитывать сезонную компоненту (S) и смотреть на сколько сильно сумма средних значений S отклоняется от 0. Если эта величина близка к 0, то можно утверждать, что продажи действительно имеют сезонный характер и товар, следовательно, можно называть сезонным.

Следующим упущением автора является отсутствие изучения периода сезонных колебаний. С одной стороны — специалисты сами знают: когда начинают расти продажи, а когда падать, но с другой — не у всех товаров сезонные колебания явно выражены. Кроме того, мнение эксперта еще точнее и убедительнее, когда оно подтверждено конкретными данными.

Итак, если мы уже определили, что в модели существует сезонность (сумма значений S близка к 0), то период сезонности рассчитывается как средняя арифметическая между количеством отрицательных и положительных значений сезонной компоненты.

После нахождения среднеквадратической ошибки модели мы можем делать вывод о точности модели в целом.

4. Построение прогноза. Когда мы определили самую точную модель мы можем перейти на этап прогнозирования, который также описан автором не полностью.

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

1. Почему взяты данные за январь ( F ф t-1 =2 361 ), тогда как оба исследуемых периода начинаются с июля.
2. Как и кем определяется константа сглаживания a . Ведь экспертом, работающим над данной проблемой, является сам автор. А, следовательно, необходим инструментарий определения данной величины.
3. Почему не описан инструментарий получения данных доверительного интервала ( ± 7,8 (руб.) ).
4. Какие «все возможные сценарии прогноза» автор имеет в виду: те которые зависят от константы сглаживания, или те, которые определяются альтернативными моделями.

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

С учетом описанных выше недостатков, можно предположить, что алгоритм должен иметь такой вид:

Определение трендов, для построения альтернативных моделей (T1, T2, T3 …)

Определение уравнений линий трендов (вид, который принимает T1, T2, T3 …, в зависимости от величин объема продаж)

Определение метода расчета сезонной компоненты (в нашем случае это расчет средней арифметической)

Максимальное количество наблюдаемых периодов (минимум=2)

СКО (среднеквадратическое отклонение) для каждого периода

Фактическое значение будущего периода (проверка будет осуществлена только по достижении периода)

Корректировка прогнозных значений, с использованием экспоненциального сглаживания

• разбивка на этапы моделирования и прогнозирования;
• детализация каждого из этапов;
• определение критериев оценки каждого из этапов;
• определение критических значений критериев оценки. Т.е. чем ближе показатель к величине, указанной в последнем столбце — тем вернее будут модель и прогноз.

С учетом проведенных изменений алгоритма попробует использовать его на примере, предоставленном Кошечкиным С.А.

Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого «Пломбир» одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.

В). Определим коэффициент b ₁ модели авторегрессии, для этого повторить пункты В-Г раздела 3.2.1. с учетом того, что в данном случае определяются коэффициенты уравнения первого порядка. В окно исходных данных вставить следующие значения:

Лабораторная работа №

– экстраполирование и интерполирование (выявление промежуточного значения между двумя известными моментами процесса) – построение динамических рядов развития показателей прогнозируемого явления на протяжении периодов основания прогноза в прошлом и упреждения прогноза в будущем (ретроспекции и проспекции прогнозных разработок);

Экстраполяция широко применяется при анализе, прогнозе всех социальных процессов. Например, вывод об уровне развития какой-либо социальной группы можно сделать по наблюдениям за отдельными ее представителями, а о перспективах культуры – по тенденциям в прошлом.

Прогнозирование в среде Excel

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

Д). Используя выражение (3.10), в ячейках G 23- G 25 рассчитать прогнозные значения случайной компоненты для t =22,23,24. При вычислении e (22) в ячейке G 23 использовать значение e (21) из ячейки G 22, при вычислении e (23) в ячейке G 24 использовать значение e (22) из ячейки G 24 и так далее.

Обратите внимание, что Excel просит вас указать диапазон входящих данных. Выберите столбец, в котором содержатся данные, в отношении которых вы хотите построить прогноз (например, цена единицы товара) и выберите “смягчающий фактор”.

Прогнозирование с использованием Пакета аналитических инструментов

• составленный прогноз социально-экономического развития региона в основном оправдался;
• для точного научного прогноза выбранных нами пяти параметров, конечно же, недостаточно, но направление стратегии развития наш прогноз отражает.

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