Прогноз продаж

Методика прогнозирования выручки с помощью авторегрессионной модели Текст научной статьи по специальности «Экономика и экономические науки»

ЭКОНОМИКА, УПРАВЛЕНИЕ И РЫНОКПРОДУКЦИИ

ЭКОНОМИКА, УПРАВЛЕНИЕ И РЫНОК ПРОДУКЦИИ

УДК 338.45:669

Бобровский В.А.

МЕТОДИКА ПРОГНОЗИРОВАНИЯ ВЫРУЧКИ с помощью АВТОРЕГРЕССИОННОЙ МОДЕЛИ

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

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

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

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

У, = а0 + а1 У,-1 + a2У,-2 + ••• + атУ,-т + e, .

Порядок авторегрессии (m) определяется точностью получаемой оценки по принципу эффективности: как только повышение порядка авторегрессии не отражает повышение эффективности модели, приращение членов в модель прекращается.

На практике получение оценки по предложенной модели не всегда является надежной ввиду наличия неоднородных процессов в экономике. Наличие последних приводит к поиску механизма, имеющего возможность отражения таких процессов. Часто такие явления носят фиксированный характер, но их появление не отражается статистикой. Так, например, наличие государственного заказа на предприятии носит вполне фиксированный и информативный характер, но прогнозирование таких процессов выждит за рамки математического аппарата. Включение некоторого фиктивного параметра f, может значительно увеличить качество прогноза. Модель с фиктивной переменной можно представить следующим образом:

У, = а0 + a,y, , + а2у, 2 +… + a у, + b, f + et.

-У t 0 W t-1 2s t-2 ms t-m w t t

Фиктивной переменной присущи следующие условия:

{1, У — у, ^ 0 I0,yt — yt > 0

(1)

Вестник МГТУ им. Г. И. Носова. 2009. № 1.

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

Бобровский В.А.

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

1. Определение порядка авторегрессии.

2. Расчет отклонений фактических значений от расчетных

3. Получение значений фиктивной переменной.

4. Получение мультитрендовой модели.

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

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

ния анализа предлагаем анализируемым показателям задать бинарный характер по следующему закону:

/:=

° x — x -1

> 0 < 0

(2)

Таким образом, мы определяем гипотезу связи фиктивной переменной и показателя, вхэдящего в систему анализа по правилу: «Положительное изменение показателя xt по отношению к предыдущему состоянию означает проявление «случайного фактора» f».

Для проверки предложенной методики построим модель для прогнозирования выручки ОАО «ММК». В качестве исходного материала используем показатель выручки за 2 года помесячно. Результаты вычислений представлены в таблице.

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

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

yt = 879,27 + 0,9309у,; R2 = 0,8764.

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

yt = 804,09 + 0,0875yt_х + 1356; R2 = 0,9664 .

Данные построения мультитрендовой модели выручки

Дата yt У,-1 yt,1 (yt,1 — yt) fl yt,2 ( yt,2 — yt ) fl

01.02.2003 5 970,4 6 296,9 6740,85 -770,436 0 6311,08 -340,670 0

01.03.2003 8 082,6 5 970,4 6436,90 1645,678 1 7382,06 700,518 1

01.04.2003 7 557,6 8 082,6 8403,02 -845,471 0 7872,71 -315,155 0

01.05.2003 7 604,2 7 557,6 7914,30 -310,060 0 7413,55 190,691 1

01.06.2003 6 989,2 7 604,2 7957,76 -968,566 0 7454,38 -465,186 0

01.07.2003 7 472,4 6 989,2 7385,23 87,156 1 8273,03 -800,636 0

01.08.2003 7 850,9 7 472,4 7835,02 15,888 1 8695,61 -844,694 0

01.09.2003 7 919,0 7 850,9 8187,38 -268,345 0 7670,11 248,925 1

01.10.2003 7 849,6 7 919,0 8250,79 -401,199 0 7729,68 119,907 1

01.11.2003 7 399,3 7 849,6 8186,14 -786,802 0 7668,95 -269,606 0

01.12.2003 7 646,5 7 399,3 7767,03 -120,501 0 7275,18 371,342 1

01.01.2004 7 547,2 7 646,5 7997,12 -449,942 0 7491,36 55,819 1

01.02.2004 7 444,8 7 547,2 7904,64 -459,842 0 7404,48 40,325 1

01.03.2004 9 621,7 7 444,8 7809,34 1812,400 1 8671,48 950,265 1

01.04.2004 11 204,2 9 621,7 9835,77 1368,473 1 10575,32 628,922 1

01.05.2004 12 151,0 11 204,2 11308,86 842,107 1 11959,30 191,668 1

01.06.2004 11 552,9 12 151,0 12190,13 -637,274 0 11430,72 122,135 1

01.07.2004 11 814,8 11 552,9 11633,37 181,451 1 12264,17 -449,357 0

01.08.2004 12 045,4 11 814,8 11877,22 168,157 1 12493,28 -447,900 0

01.09.2004 12 304,6 12 045,4 12091,83 212,738 1 12694,91 -390,337 0

01.10.2004 13 383,1 12 304,6 12333,11 1050,034 1 12921,59 461,555 1

01.11.2004 12 486,8 13 383,1 13337,11 -850,311 0 12508,32 -21,516 0

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

3. Улучшена ранее построенная модель за счет повторного использования алгоритма:

yt = 258,076 + 0,0882yt +1439 f \ + 780f 2;

R2 = 0,988.

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

Вторую фиктивную переменную можно интерпретировать как ускорение изменения выручки.

Наличие больших весовых коэффициентов характеризует степень влияния полученных переменных

Вестник МГТУ им. Г. И. Носова. 2009. № 1.

ЭКОНОМИКА, УПРАВЛЕНИЕ И РЫНОКПРОДУКЦИИ

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

1. Затраты, млн руб.

2. Средняя цена на внутреннем рынке. тыс. руб.

3. Отгрузка продукции, тыс. т.

4. Средняя цена тыс. руб.

5. Средняя цена на экспорт, тыс. руб. (X1).

6. Затраты в незавершенном производстве, млн руб. (X2).

7. Запасы, млн руб.

Реализация поставленной задачи исполняется методом корреляционного анализа. Исследуемые временные ряды были приведены к бинарному виду по формуле (2).

Произведенный корреляционный анализ показал связь первой фиктивной переменной с полученным бинарным рядом от характеристики — «средняя цена на экспорт». Вторая переменная имеет обратную связь с характеристикой — затраты в незавершенном производстве. Причем прямой корреляционный анализ показателей «выручки» и «затраты в незавершенного производстве» не дал положительного результата (параметр B для показателя X2 является незначимым). Из чего следует, что характеристика «затраты в незавершенном производстве» является факторным признаком для показателя выручки, т.к. лишь обуслэвливает изменение результирующего показателя, а «средняя цена на экспорт» — результативным признаком.

Для определения преимуществ мультитрендовой модели построим модели множественной регрессии с аналогичными переменными.

yt = -265,285 + 0,346y>_1 + 0,425 X1, + 0,001Xt2; R2 = 0,936.

Полученное уравнение множественной регрессии уступает по показателю аппроксимации, показатель Х2 является незначимым.

Такой вывод может характеризовать наличие внутренней тенденции во временном ряде заданной двумерным параметром. И открывает новое направление — разделение временною ряда на составляющие — включение в модель параметров, не связанных статистически с зависимой переменной, представляет отдельный интерес.

Список литературы

1. Давние В.В., Тинякова В.И. Прогнозные модели экспертных предпочтений. Воронеж: Изд-во Воронеж. гос. ун-та, 2005. 248 с.

2. Дуброва Т.А. Статистические методы прогнозирования: Учеб. пособие. М.: ЮНИТИ-ДАНА, 2003. 206 с.

Дополним экономический анализ построением мультитрендовой модели затрат:

У,

t (затра ты)

■35,668 + 0,9551yt_1 + 678f \ + 379/2;

R2 = 0,995.

Отметим, что значения параметров / и /2 для уравнения выручки в 2 раза выше аналогичных показателей для уравнения затрат, что свидетельствует о повышении эффективности работы предприятия или положительном значении эффекта масштаба. Получение прогнозных оценок выручки и затрат позволяет проанализировать эффективность работы предприятия. Получение комплекса прогнозных оценочных значений характеристик деятельности субъекта поможет не только определить приоритетные направления деятельности, но и смоделировать возможное состояние предприятия при условии сохранения развития тенденции. Сопоставление значений фиктивных переменных в полученных авторегрессионных моделях для одноименных значений временных рядов представляет следующие результаты. Значение параметра фиктивной переменной /1 совпадает в 75% случаев в рамках изучаемого периода, а для параметра фиктивной переменной /2 значения совпадают в 60°% случаев.

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

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

Прогноз продаж в Excel

Хитрости » 23 Март 2017 Дмитрий 51696 просмотров

Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (15)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Скачать файл, используемый в видеоуроке:

Прогноз_продаж.xls (59,5 KiB, 12 033 скачиваний)

Прогнозирование продаж является неотъемлемой частью при планировании работы коммерческих и финансовых служб, поэтому задача довольно актуальная. Вариантов построения прогнозов достаточное множество, но я хочу показать как сделать простой, но в то же время достаточно жизнеспособный прогноз «на скорую руку», без лишних телодвижений и поправок «на ветер»(читайте как: без кучи доп.расчетов, которые применяются для создания более точных прогнозов). Почему я это уточняю? Потому что на мой взгляд, каким бы точным ни был прогноз продаж – это всего лишь предположение и быть уверенным в том, что именно так и будет развиваться ход событий, никак нельзя.
И тем не менее при помощи встроенных в Excel функций мы можем построить довольно неплохой прогноз даже с учетом сезонности. Плюс я хочу показать как сделать не просто прогноз, а прогноз с отклонениями – пессимистичный и оптимистичный. С помощью подобной модели можно будет выстроить тактику продаж таким образом, чтобы постараться максимально «вписаться» в границы между пессимистичным и оптимистичным прогнозом.
А в довершение мы построим красивый график с прогнозом. Исходные данные
Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:

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

Чтобы дальше в статье не запутать вас столбцами и где они должны быть добавлены, сразу приведу конечную структуру:

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

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

Прогноз_продаж.xls (59,5 KiB, 12 033 скачиваний)

В файле два листа:

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

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

Расчет прогноза
Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ(FORECAST). Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку C34 записываем функцию (и распространяем на все прогнозируемые даты – C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)
Сама функция требует указания следующих входных данных:

  • х — Дата, значение для которой необходимо спрогонозировать (A34)
  • Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
  • Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

С одной стороны, мы уже имеем готовый прогноз, а с другой…Данная функция пока не учитывает фактор сезонности. А это в продажах в большинстве случаев немаловажный фактор. Поэтому желательно потратить еще чуточку времени и сделать так, чтобы прогноз получился еще больше приближен к реальности. Для учета фактора сезонности сначала необходимо вычислить коэффициент сезонности для каждого месяца. Для этого добавим в столбец Коэффициент сезонности следующую формулу:
=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12
=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года). Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!)

Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е. 24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=((56 769+68 521)/ 1 542 293)*12
=((сумма за янв.2013 + сумма за янв.2014)/ общая сумма за два года(янв.2013 – дек.2014))*12

В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т.д. Я для наглядности назначил ячейкам процентный формат(правая кнопка мыши -Формат ячеек -вкладка Число -Процентный(Format cells -Number -Percent), два знака после запятой):

Теперь добавим учет этих коэффициентов для расчета прогноза в имеющуюся функцию ПРЕДСКАЗ(ячейки C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)*ИНДЕКС($F$2:$F$13;МЕСЯЦ(A34))
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)*INDEX($F$2:$F$13,MONTH(A34))
Здесь применяется функция ИНДЕКС(INDEX), в которой первым аргументом указываем ссылку на 12 ячеек с коэффициентами сезонности($F$2:$F$13), а вторым – номер месяца, чтобы вернуть коэффициент именно для нужного месяца(для этого используем функцию МЕСЯЦ(MONTH), которая возвращает только номер месяца из указанной даты). Для сентября 2015 это будет выглядеть так:
=ПРЕДСКАЗ(A34; $B$2:$B$33; $A$2:$A$33)*ИНДЕКС({97,48%:98,99%:90,38%:94,66%:100,86%:99,02%:100,66%:110,39%:100,47%:104,82%:105,13%:97,14%}; 9)

Основную задачу выполнили – у нас есть прогноз на будущие периоды. Теперь осталось в дополнение к самому прогнозу, создать допустимые верхние и нижние границы, которые часто еще называют оптимистичный прогноз и пессимистичный. Такой прогноз даст нам возможность более гибко планировать тактику на будущие периоды.
Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН(C34:C45); СЧЁТ(C34:C45))
=CONFIDENCE(0.05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ(CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

  • алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
  • станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН(STDEV), передавая ей для расчетов спрогнозированные данные
  • размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
Оптимистичный: =$C34+$G$2
Пессимистичный: =$C34-$G$2

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

График
Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

Теперь выделяем все данные (A1:E45), переходим на вкладку Вставка(Insert) – группа Диаграммы(Charts) -График(Line). И получим такую картину:

Наглядно и сразу понятно что к чему и чего можно ожидать.

  • Синим – фактические продажи
  • Оранжевый – прогноз
  • Серый – Оптимистичный прогноз
  • Желтый – Пессимистичный

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

Быстрый прогноз в Excel 2016 и выше
Начиная с версии 2016 в Excel появилась замечательная возможность создать прогноз двумя кликами мыши. При этом сразу с оптимистичным и пессимистичным развитием событий и графиком. За основу возьмем все те же исходные данные из двух столбцов:
Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные(Data) -группа Прогноз(Forecast) -Лист прогноза(Forecast Sheet):
В появившемся окне раскрываем пункт Параметры(Options) и настраиваем:

  • Завершение прогноза(Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
  • Начало прогноза(Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
  • Доверительный интервал(Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
  • Сезонность(Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
  • Диапазон временной шкалы(Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
  • Диапазон значений(Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
  • Заполнить отсутствующие точки с помощью(Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
  • Объединить дубликаты с помощью(Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
  • Включить статистические данные прогноза(Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).

После нажатия кнопки Создать(Create) будет создан новый лист, в котором будет создана таблица со всеми необходимыми данными и формулами и готовым графиком:
если при создании был отмечен пункт Включить статистические данные прогноза(Include Forecast Statistics), то правее таблицы основных данных будет так же создана таблица статистических данных:

Скачать файл:

Прогноз_продаж.xls (59,5 KiB, 12 033 скачиваний)

Так же см.:
Как быстро подобрать оптимальный вариант решения
Автообновляемая сводная таблица

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *