Размещено на http://www.allbest.ru/

Прогнозирование объема продаж бензина методом экстраполяции трендов

Пучкова В.С.,

Растеряев Н.В.

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

Методы прогнозирования объема продаж можно разделить на три основные группы:

• методы экспертных оценок;

• методы анализа и прогнозирования временных рядов;

• казуальные (причинно-следственные) методы.

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

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

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

В основе казуальных методов лежит попытка найти факторы, определяющие поведение прогнозируемого показателя. Поиск этих факторов приводит собственно к экономико-математическому моделированию ? построению модели поведения экономического объекта, учитывающей развитие взаимосвязанных явлений и процессов. Следует отметить, что применение многофакторного прогнозирования требует решения сложной проблемы выбора факторов, которая не может быть решена чисто статистическим путем, а связана с необходимостью глубокого изучения экономического содержания рассматриваемого явления или процесса. И здесь важно подчеркнуть примат экономического анализа перед чисто статистическими методами изучения процесса.

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

Качественное прогнозирование требует полного анализа данных и построения адекватной модели данных. Однако существуют средства ЭТ MS Excel, которые можно использовать для быстрого предварительного прогнозирования, без глубокого анализа данных, хотя в этом случае нельзя гарантировать качественный прогноз. Можно построить и доверительные интервалы для вычисленного прогнозного значения. Для выделения сезонной составляющей функции прогнозирования в Excel нет встроенных средств, поэтому она также не будет учитываться при составлении быстрого прогноза.

В качестве исходных данных используем данные из таблицы 3.

Таблица 3

Исходные данные для прогнозирования

Год	Месяц	Объём продаж АИ-95, тонн
2011	Январь	128,0
2011	Февраль	129,7
2011	Март	122,9
2011	Апрель	139,6
2011	Май	164,3
2011	Июнь	167,9
2011	Июль	166,5
2011	Август	201,7
2011	Сентябрь	235,9
2011	Октябрь	231,9
2011	Ноябрь	246,5
2011	Декабрь	244,2
2012	Январь	194,7
2012	Февраль	190,1
2012	Март	197,6
2012	Апрель	198,1
2012	Май	213,2
2012	Июнь	247,2
2012	Июль	239,6
2012	Август	299,1
2012	Сентябрь	319,3
2012	Октябрь	340,4
2012	Ноябрь	339,5
2012	Декабрь	369,9
2013	Январь	358,9

Задача прогнозирования заключается в том, чтобы вычислить прогнозные значения месячных объемов продаж на февраль-май 2013 года при условии, что сохранятся существующие тенденции рынка.

Средство построения диаграмм и графиков ЭТ MS Excel автоматически строит линии тренда и автоматически рассчитывает прогнозные значения. Это возможно только в том случае, когда прогнозируемая переменная Y зависит от одного фактора, в роли которого выступает время.

Для графического прогнозирования сначала строится диаграмма или график по значениям прогнозируемой переменной Y. В нашем примере построим точечный график объемов продаж бензина, в зависимости от времени. экстраполяция прогнозирование продажа

Линию тренда нельзя добавить в объемную, лепестковую, круговую и кольцевую диаграммы, а так же в диаграмму с накоплением.

Чтобы по этому ряду данных построить линию тренда, выполните следующие действия.

1. Щелкните на точечной диаграмме, чтобы выделить ее (рис. 1).

Размещено на http://www.allbest.ru/

Рисунок 1

2. В контекстном меню выберите команду Добавить линию тренда …, чтобы открыть диалоговое окно Формат линия тренда.

3. В диалоговом окне Формат линии тренда выберите тип линии тренда (рис. 2).



Размещено на http://www.allbest.ru/

Рисунок 2

Для выбора предоставляются следующие типы линии тренда.

• Экспоненциальная. Уравнение линии экспоненциального тренда имеет вид

где с и b - вычисляемые параметры линии тренда.

• Линейная. Линейная линия тренда - это прямая, которая описывается уравнением

где X - независимая переменная (фактор),

m и b - вычисляемые параметры прямой (параметр m определяет наклон прямой, параметр b - точку пересечения прямой с осью Y).

• Логарифмическая. Уравнение логарифмической линии тренда имеет вид

где с и b - вычисляемые параметры линии тренда.

• Полиномиальная. Уравнение полиномиальной линии тренда имеет вид

где ci и b - вычисляемые параметры линии тренда.

Здесь же счетчик Степень позволяет задать степень полинома n - это может быть целое число от 2 до 6.

• Степенная. Уравнение линии степенного тренда имеет вид

где c и b - вычисляемые параметры линии тренда.

• Линейная фильтрация. При выборе этой опции происходит сглаживание данных методом скользящего среднего.

Если ряд данных содержит нулевые или отрицательные значения, то линии тренда Экспоненциальная и Степенная будут недоступны.

4. В диалоговом окне Формат линии тренда также предлагается

• определить название линии тренда, которое будут включено в легенду,

• задать количество периодов, на которые будут прогнозироваться данные (вперед и назад).

Три дополнительные опции позволяют отобразить на диаграмме

• пересечение линии тренда с осью Y (опция Пересечение кривой с осью Y в точке);

• уравнение линии тренда (опция Показывать уравнение на диаграмме);

• значение коэффициента детерминации R2, определяющее достоверность аппроксимации (опция Поместить на диаграмму величину достоверности аппроксимации (R^2)).

5. После того как выбран тип линии тренда и сделаны другие установки, щелкните на кнопке Закрыть, чтобы получить линию тренда и прогноз на указанное число периодов.

График линии тренда для нашего примера показан на рис. 3. В качестве аппроксимирующей функции выбрана степенная функция. Прогноз сделан на четыре месяца вперед.

Размещено на http://www.allbest.ru/

Рисунок 3

Чтобы просмотреть все возможные линии тренда, постройте сначала одну из них, задав все необходимые параметры (количество периодов для прогноза, название, вывод уравнения линии тренда и значения коэффициента детерминации R2). Затем, щелкните правой конкой мыши на линии тренда и выберите в контекстном меню команду Формат линии тренда, в появившемся диалоговом окне можно выбрать другой тип линии тренда. При этом все ранее сделанные установки переносятся на новую линию тренда. Перебрав все возможные линии тренда, остановите выбор на той, для которой коэффициент детерминации имеет наибольшее значение.

Коэффициент детерминации R2 характеризует степень близости линии тренда к исходным данным. Он может принимать значения от 0 до 1. Чем больше его значение, тем лучше линия тренда аппроксимирует исходные данные.

Какие же прогнозные значения мы получили? Мы имеем прогноз и можем увидеть, как ведет себя прогнозируемая переменная, но числовые значения прогноза неизвестны.

Определить числовые значения можно визуально из графика линии тренда, если увеличить размер области построения диаграммы и сделать дополнительные линии сетки по оси абсцисс. В нашем примере получаем, что на 29-й период (май 2013 года) прогнозное значение продаж составит примерно 210 тонн (смотри рис. 4).

Рисунок 4

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

Более точные значения прогнозируемой переменной можно получить, если воспользоваться приведенным уравнением линии тренда. В нашем примере оно имеет вид:

у = 97,43 · Х ^0,343

Подставляя поочередно вместо Х номера периодов 1, 2, ..., 29, получим искомые, в том числе и прогнозируемые, значения объемов продаж.

Эти вычисленные значения показаны на рис. 5 в диапазоне ячеек F4:F32.

Размещено на http://www.allbest.ru/

Рисунок 5

Для их вычисления в ячейки Н 1 и Н 2 сначала были введены найденные параметров линии тренда с и b, затем в ячейку F4 была введена формула, которая показана на рис. 5 в строке формул, далее эта формула была скопирована вниз на диапазон F5:F32. Коэффициент детерминации R2 в данном случае равен 0,761.

Теперь остановимся на вопросе, какой тип линии тренда выбирать. Выбирается тот тип линии тренда, который обеспечивает максимальное значение коэффициента детерминации R2. Например, если к нашим данным применить полиномиальную линию тренда с показателем степени 6, то, как показано на рис. 6, аппроксимация исходных данных будет хорошей, значение коэффициента детерминации составит 0,961.

Размещено на http://www.allbest.ru/

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

• возможность прогнозирования только для случая зависимости прогнозируемой переменной от одного фактора;

• ограниченный набор типов линий тренда;

• невозможность автоматического вычисления прогнозных значений.

Есть и достоинство - это быстрое построение линии тренда.

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

Приведем список и описание функций, используемых в процессе прогнозирования (табл. 2).

Таблица 2

Список функций, применяемых в регрессионном анализе

Функция	Назначение
ЛГРФПРИБЛ	Возвращает параметры кривой, полученной в результат экспоненциальной аппроксимации исходных данных методом наименьших квадратов
ЛИНЕЙН	Возвращает массив коэффициентов функции регрессии, полученный в результате аппроксимации исходных данных методом наименьших квадратов
ПРЕДСКАЗ	Возвращает предсказанное значение функции на основе линейной регрессии для массивов известных значений X и Y или интервалов данных
РОСТ	Рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных
СТОШYX	Возвращает стандартную ошибку аппроксимации для линейной регрессии
ТЕНДЕНЦИЯ	Возвращает значение прогноза в соответствии с линейной функцией регрессии

Каждая из этих функций имеет не менее двух аргументов, один из которых задает массив значений независимой переменной X, а второй - массив значений зависимой переменной Y. В некоторых функциях можно задавать не только одномерный массив переменной X, но и двумерный, т.е. существует возможность исследовать зависимость между набором факторов X и переменной Y и строить множественную регрессию. Функции ЛГРФПРИБЛ и РОСТ работают с экспоненциальной регрессией, а остальные - с линейной.

Для быстрого вычисления прогнозных значений переменной Y без явного построения функции прогнозирования используют статистические функции ПРЕДСКАЗ, РОСТ и ТЕНДЕНЦИЯ.

Эти функции применяются в следующих ситуациях.

• Функция ПРЕДСКАЗ применяется, если функция прогнозирования (функция регрессии) зависит от одного фактора X и предполагается, что между прогнозируемой переменной Y и фактором X существует линейная зависимость вида:

Y = m Х +b.

• Функция ТЕНДЕНЦИЯ применяется, если функция прогнозирования линейно зависит от нескольких факторов, а также в случае полиномиальной зависимости прогнозируемой переменной Y от одного фактора.

Зависимости между прогнозируемой переменной Y и факторами X1,Х 2,..,Xk имеют вид:

Y = b₀ + m₁ · Х₁ + m₂ · Х₂ +K+ m_k · Х_k

линейная множественная регрессия, либо

Y = b₀ + m₁ · Х₁ + m₂ · Х² +K+ m_k · Х ^k

полиномиальная регрессия от одного фактора, либо в виде комбинации множественной и полиномиальной регрессий.

* Функция РОСТ применяется, если функция прогнозирования экспоненциально зависит от нескольких факторов, т.е. предполагается, что между прогнозируемой переменной Y и факторами X1,Х 2,..,Xk имеют вид:

Функция ПРЕДСКАЗ имеет синтаксис:

=ПРЕДСКАЗ(х; Известные значения_Y; Известные значения_Х),

где аргумент х - значение фактора, для которого вычисляется прогноз; аргумент Известные значения _Y - одномерный массив значений переменной; Y (или ссылка на диапазон ячеек, содержащий этот массив); аргумент Известные значения _Х - массив значений фактора X (или ссылка на диапазон ячеек, содержащий этот массив).

Функции ТЕНДЕНЦИЯ и РОСТ имеют одинаковый синтаксис:

=ТЕНДЕНЦИЯ(Известные значения_Y; Известные значения_Х; Новые_значения_х; Константа).

=РОСТ(Известные значения_Y; Известные значения_Х;

Новые_значения_х; Константа),

где

аргумент Известные значения_Y - одномерный массив значений переменной;

Y (или ссылка на диапазон ячеек, содержащий этот массив); аргумент Известные значения_Х - массив значений факторов

X1,Х 2,..,Xk (или ссылка на диапазон ячеек, содержащий этот массив); аргумент Новые_значения_х - значения факторов, для которых

вычисляется прогнозное значение; аргумент Константа принимает логическое значение: если он имеет значение ИСТИНА или 1 либо опущен, то коэффициент уравнения регрессии b0 вычисляется как обычно; если же он имеет значение ЛОЖЬ или 0, то коэффициент полагается равным 0, и значения коэффициентов уравнения регрессии вычисляются с учетом этого условия.

Если в функциях ТЕНДЕНЦИЯ и РОСТ аргумент Известные значения_Х опущен, то предполагается, что это массив натуральных чисел

{1; 2; 3;...} такого же размера, как и массив аргумента Значения_Y. Если опущен аргумент Новые_значения_х, то по умолчанию предполагается, что он совпадает с аргументом Известные значения_Х.

Эти функции используют для одновременного вычисления массива прогнозных значений по заданному массиву {х} значений факторов. Для этого в качестве аргумента х надо указать массив {х}, а саму функцию применяют как формулу массива: выделяют диапазон ячеек, в котором будет записан выходной массив прогнозных значений, затем вводят функцию и завершают процедуру нажатием комбинации клавиш <Ctrl+Shift+Enter>.

На примере покажем применение функции ЛИНЕЙН для вычисления прогнозных значений объемов продаж на 26-29 периоды. Эта функция имеет синтаксис:

=ЛИНЕЙН(Известные_значения_Y; Известные_значения_Х; Константа; Статистика), где аргумент Известные_значения_Y - одномерный массив (или ссылка на

диапазон ячеек, содержащий этот массив) значений переменной Y; аргумент Известные_значения_Х - массив (или ссылка на диапазон ячеек, содержащий этот массив) значений факторов X. Если данный аргумент опущен, предполагается, что это массив натуральных чисел {1; 2;

3;...} такого же размера, как и массив Значения_Y; аргумент Константа - логическое значение, которое указывает, должен ли коэффициент b0 быть равным 0. Если этот аргумент имеет значение ИСТИНА, 1 или опущен, то коэффициент вычисляется как обычно. Если

аргумент имеет значение ЛОЖЬ или 0, то b0 полагается равным 0, и значения коэффициентов mi подбираются с учетом этого условия;

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

Аргументы функции ЛИНЕЙН представлены на рис. 7.



Рисунок 7

Значения параметров m и b0 размещаем в ячейках I2 и J2 соответственно. В ячейке I4 записываем формулу линейного уравнения и, автозаполнением, копируем её в ячейки диапазона I5:I32 (см. рис. 8).

Размещено на http://www.allbest.ru/

Рисунок 8

С помощью функции ТЕНДЕНЦИЯ вычислим прогнозные значения для полиномиальной функции регрессии. Допустим, что в нашем примере объемы продаж полиноминально зависят от времени (ограничимся многочленом третьей степени):

Y = b₀ + m₁ ·t + m₂ ·t² + m₃ ·t³.

В этом случае необходимо, чтобы в качестве аргумента Известные значения_Х были заданы значения t, t2 и t3 . Эти значения на рабочем листе, показанном на рис. 9, вычисляются в столбцах K, L и M. Аналогично для значений периодов, для которых вычисляется прогноз, необходимо подсчитать квадраты и кубы этих периодов (значения в столбцах O, P и Q на рис. 9). Функции ТЕНДЕНЦИЯ, по которой вычисляются прогнозные значения в столбце R, показана в строке формул, а ее диалоговое окно - на рис. 10. Получили еще один прогноз.

Рисунок 9



Рисунок 10

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

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

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

Так как прогнозирование является отдельной наукой, то целесообразно (по мере возможности) использование нескольких методов прогнозирования при решении какой-либо проблемы. Это повысит качество прогноза и позволит определить "подводные камни", которые могут быть незамечены при использовании только одного метода. Также необходимо соотносить полученный прогноз с прецедентами в решении данной проблемы, если такие имели место при похожих условиях функционирования аналогичной организации (конкурента). И при определенной корректировке, в соответствии с этим прецедентом, принимать решения.

Литература

1. Баззел Р.Д. и др. Информация и риск в маркетинге. ? М.: Финстатинформ, 1993.

2. Беляевский И.К. Маркетинговое исследование: информация, анализ, прогноз. ? М.: Финансы и статистика, 2001.

3. Статистическое моделирование и прогнозирование: Учебное пособие / Под ред. А.Г. Гранберга. ? М.: Финансы и статистика, 1990.

Размещено на Allbest.ru

...