После моего прихода в компанию «Избёнка», осуществляющую розничную торговлю натуральной молочной продукцией, я думал, что помесячное прогнозирование остались для меня – в прошлом. Ведь из-за того, что стратегическим принципом компании является торговля только натуральной молочной продукцией, потребность в прогнозировании больше недели – отпадает. Всё дело в коротких сроках годности такой продукции – обычно это 3-5 дней. Однако мне пришлось вспоминать прошлые навыки, когда я столкнулся с необходимостью делать заказ на сыр. Дело в том, что он вызревает как раз около месяца. А чтобы через месяц получить у поставщика нужный объём, мне нужно уже сейчас сказать ему, сколько мне понадобится, чтобы он запустил в производственный процесс необходимое для этого количество сырья. А в случае поставок с большим сроком реагирования – будь то: из-за длинного транспортного плеча или трудоёмкого процесса производства возникает необходимость прогнозировать спрос на длительные сроки. Срок реагирования – это время от момента, когда приняли решение о необходимости закупки позиции, и до момента, когда её можно использовать на производстве или отпускать клиенту. При этом в компаниях обычно осуществляется помесячное планирование, поэтому и прогноз желательно иметь в разбивке по месяцам. Выполнение этого же условия требуется при револьверных поставках – то есть таких поставках, когда период между ними меньше срока реагирования:

	январь	февраль	март	апрель	май	июнь
поставка №1	отгрузка	доставка	приёмка
поставка №2		отгрузка	доставка	приёмка
поставка №3			отгрузка	доставка	приёмка
поставка №4				отгрузка	доставка	приёмка

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

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

Упрощённый классический метод.

Первоначально необходимо определить общую динамику продаж: то есть для имеющегося ряда прошлого спроса S i надо построить линейный тренд – долговременную тенденцию изменения временного ряда, выражаемую прямой линией. В Microsoft Excel его уравнение можно получить, добавив на диаграмму временного ряда линейный тренд, а значение тренда для любого месяца Т i – используя функцию ТЕНДЕНЦИЯ. Именно благодаря этим значениям мы сможем рассчитать коэффициенты сезонности K i для каждого i -того месяца в прошлом. Для этого надо разделить значение фактического спроса за каждый месяц на значение линейного тренда за этот же месяц:

Тогда при наличии статистики хотя бы за два-три года появляется возможность рассчитать коэффициенты сезонности для каждого месяца года K m , где: К 1 – коэффициент сезонности января, К 2 – коэффициент сезонности февраля, К 3 – коэффициент сезонности марта, и так далее... Делается это за счёт усреднения всех полученных коэффициентов сезонности за все года для соответствующего месяца:

,

где L m – количество соответствующих месяцев в истории спроса.

Теперь, когда у нас есть эти коэффициенты, мы можем получить прогноз спроса P i на любой будущий i -тый месяц, умножив соответствующий этому месяцу коэффициент сезонности K m на значение тренда Т i для этого месяца: P i = K m · T i .

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

Индуктивный метод Разгуляева.

Данный метод был разработан мной для вычисления прогноза спроса в случаях, когда расчёт по классическому методу занимает слишком много времени из-за большого объёма информации или, вообще, невозможен из-за того, что тренд уходит в минус. Впоследствии он был реализован в нескольких автоматизированных системах, включая "Invertor" для "Эксель" и "Прогноз продаж" для "1С" (http://www.forecastsupply.ru/). Формула расчёта может показаться сначала сложной:

где X – это номер месяца, на который мы прогнозируем спрос, то есть количество месяцев, имеющихся в статистике прошлого спроса по позиции, плюс ещё один.

Но если мы обратим внимание на условие после каждой формулы в фигурной скобке, то увидим, что из всего этого каскада формул нам нужна только одна – та, которая подходит под наш объём имеющейся статистики прошлого спроса. Причём каждая формула состоит только из сложения, умножения и деления. Значок ∑ S i – просто означает, что нам надо просуммировать все значения прошлого спроса, начиная с месяца, номер которого указан снизу этого значка, и заканчивая месяцем, номер которого указан сверху этого значка. Таким образом, получается, что и для понимания, и для реализации в корпоративной информационной системе оказывается проще именно этот метод. И если численные методы нахождения тренда в корпоративной информационной системе компании могут реализовать только программисты со специальным математическим образованием, да и то – для них это будет задачка на неделю-две, то уж сложение, умножение и деление – вам внедрит в течение суток любой программист!.. Да и считаться данный алгоритм будет на порядок быстрее.

Кроме этого у данного метода есть ещё ряд существенных плюсов – он никогда не будет прогнозировать отрицательные значения спроса при положительной прошлой статистике в отличие от классического метода. А ещё он – гибче, то есть быстрее реагирует на проявления динамики спроса. При этом, как и классический метод, индуктивный метод Разгуляева учитывает: как общую динамику спроса, причём, не привязываясь к линейности тренда, так и повторяющиеся из года в год сезонные влияния. Единственный его «недостаток» заключается в том, что вы не можете посчитать прогноз спроса на любой месяц, не посчитав предварительно прогноз спроса на все предыдущие месяца. То есть, если сейчас закончился январь, и вы хотите спрогнозировать спрос в мае, то вам сначала надо будет по имеющейся статистике спрогнозировать спрос в феврале, затем внести это значение в статистику, и на её основании спрогнозировать спрос в марте. После этого по такой же схеме спрогнозировать спрос в апреле и только затем в мае. Однако на практике нам редко нужно прогнозировать спрос через полгода, но не прогнозировать спрос в следующем месяце, поэтому данный недостаток – не так критичен. Ещё одну проблему в применении данного метода – возможность равенства нулю одного из знаменателей в формуле, легко решает переход на формулу строчкой выше, где диапазон суммирования в знаменателе будет – значительно больше, и такой ситуации точно не возникнет. Ачтобы лучше разобраться с формулами, можно скачать Excel -файлы с примерами реализации обоих методов по ссылке:

Оценка точности прогноза.

Как только у нас появляются хотя бы два варианта прогнозирования – сразу же возникает вопрос: «А какой из них лучше?» – Однозначного ответа на него нет и быть не может, так как нет и никогда не будет самого лучшего метода прогнозирования – их надо проверять на ваших данных, чтобы оценить, какой из них лучше предсказывает ситуацию для ваших позиций в ваших каналах продаж. И здесь исследователей подстерегает одна ловушка в определении ошибки прогноза D . Самым распространённым вариантом расчёта такой ошибки является следующая формула:

где: P – это прогноз, а S – факт за тот же месяц.

Однако когда спрашиваешь пользователя этой формулы: «А чему равна ошибка, если факт равен нулю?» – то он попадает в понятное затруднение, ведь на ноль делить нельзя. Некоторые отвечают, что в таком случае D = 100% – мол, мы полностью ошиблись. Однако простой пример показывает, что такой ответ тоже – не верен:

вариант	прогноз	факт	ошибка прогноза
№1			100%
№2			300%
№3

Оказывается, что в варианте развития событий №2, когда мы лучше угадали спрос, чем в варианте №1, ошибка по данной формуле оказалась – больше. То есть ошиблась уже сама формула. Есть и другая проблема, если мы посмотрим на варианты №2 и №3, то увидим, что имеем дело с зеркальной ситуацией в прогнозе и факте, а ошибка при этом отличается – в разы!.. То есть при такой оценке ошибки прогноза нам лучше его заведомо делать менее точным, занижая показатель – тогда ошибка будет меньше!.. Хотя понятно, что чем точнее будет прогноз – тем лучше будет и закупка. Поэтому для расчёта ошибки советую использовать следующую формулу:

.

№1

100%

№2

№3

Как мы видим в варианте №1 ошибка становится равной 100%, причём это уже – не наше предположение, а чистый расчёт, который можно доверить машине. Зеркальные же варианты №2 и №3 – имеют и одинаковую ошибку, причём эта ошибка меньше ошибки самого плохого варианта №1. Единственная ситуация, когда данная формула не сможет дать однозначный ответ – это равенство знаменателя нулю. Но максимум из прогноза и факта равен нулю, только когда они оба равны нулю. В таком случае получается, что мы спрогнозировали отсутствие спроса, и его, действительно, не было – то есть ошибка тоже равна нулю – мы сделали совершенно точное предсказание.

Получение истории спроса.

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

Дефицит. Если товара не было, и продажи из-за этого были равны нулю, то данную статистику ни в коем случае нельзя использовать в «чистом» виде – ведь в таком случае мы будем сами создавать ситуацию такого же дефицита и в будущем. Поэтому дефицит нужно оценивать и прибавлять к продажам, чтобы получить историю спроса с его учётом.

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

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

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

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

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

Валерий Разгуляев

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

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

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

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

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Преимущества и недостатки

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

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

Как организовать планирование продаж

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

Как подготовить план продаж

Для планирования «от достигнутого» основой служит информация о динамике продаж (в натуральном и стоимостном выражении) за предыдущий период, сопоставимый как по продолжительности, так и по сезонности с плановым. Это требование бывает трудно выполнить, так как продажи обычно прогнозируются в IV квартале, когда год еще не закончился и итоги по нему не подведены. В этом случае используют информацию о фактической реализации за 9 или 10 прошедших месяцев и плановой – за оставшееся до конца года время (ноябрь–декабрь).

Если компания применяет различные ставки НДС или занимается несколькими видами деятельности, предусматривающими разные системы налогообложения, то для нее особенно важно прогнозировать продажи в стоимостном выражении без НДС – так план будет корректнее. Это можно рекомендовать и компаниям, применяющим стандартный, 18-процентный НДС. В дальнейшем, при уточнении направлений использования базового прогноза (например, для подготовки бюджета движения денежных средств, для расчета налоговой нагрузки, для постановки задач отделу продаж и т. п.), следует рассчитывать выручку с НДС.

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

Самый простой способ спланировать продажи – взять объем реализации за базовый период (тот, что берется за основу, например, прошлый месяц или аналогичный месяц прошлого года – при планировании по месяцам) и скорректировать его на желаемый прирост по формуле 1.

Формула 1. Расчет плана продаж

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

Учесть структуру продаж.

Объем реализации может прогнозироваться в детальном виде, по товарам или (и) клиентам. Расчеты проводятся по формуле 1, но данные за базовый период берутся в той же аналитике (товары или покупатели). Более того, целевые коэффициенты роста продаж придется также задать индивидуально для каждого вида продукции (покупателя). Прогноз формируется на год в целом либо по периодам – но только при отсутствии сезонных колебаний спроса. При планировании в разрезе по клиентам коэффициенты устанавливаются в зависимости от состояния бизнеса контрагентов (например, если компания-покупатель активно развивается, можно планировать прирост продаж), исходя из достигнутых договоренностей, а также на основе экспертных оценок коммерсантов (см. таблицу 1. План продаж в стоимостном выражении по контрагентам).

Таблица 1. План продаж в стоимостном выражении по контрагентам

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

Таблица 2. План продаж в стоимостном выражении по номенклатуре

Можно предусмотреть и двухуровневую структуру плана продаж:

• по контрагентам (покупателям) и закупаемой ими номенклатуре товаров (см. таблицу 3. План продаж в стоимостном выражении по контрагентам и продукции);
• по номенклатуре и ее покупателям (см. таблицу 4. План продаж в стоимостном выражении по номенклатуре продукции и покупателям).

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

Таблица 3. План продаж в стоимостном выражении по контрагентам и продукции

Контрагент	Номенклатура
ООО «Елочка»	Конфеты «Бриз»	1500,00	1,015	1522,50
	Конфеты «Грильяж»	1000,00	1,040	1040,00
	Конфеты «Сладкоежка»	1500,00	1,070	1605,00
	Конфеты «Солнышко»	1000,00	1,050	1050,00
	Итого	5000,00	1,044	5217,50
ООО «Замок»	Конфеты «Бриз»	5000,00	1,010	5050,00
	Конфеты «Грильяж»	2000,00	1,040	2080,00
	Конфеты «Сладкоежка»	2000,00	1,075	2150,00
	Конфеты «Солнышко»	1000,00	1,015	1015,00
	Итого	10 000,00	1,030	10 295,00
ООО «Зебра»	Конфеты «Бриз»	1000,00	1,110	1110,00
	Конфеты «Грильяж»	500,00	1,090	545,00
	Конфеты «Сладкоежка»	1500,00	1,100	1650,00
	Конфеты «Солнышко»	1000,00	1,040	1040,00
	Итого	4000,00	1,086	4345,00
ООО «Кенгуру»	Конфеты «Бриз»	7500,00	1,010	7575,00
	Конфеты «Грильяж»	9500,00	1,040	9880,00
	Конфеты «Сладкоежка»	2000,00	1,050	2100,00
	Конфеты «Солнышко»	1000,00	1,030	1030,00
	Итого	20 000,00	1,029	20 585,00
Всего		39 000,00	1,037	40 442,50

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

Таблица 4. План продаж в стоимостном выражении по номенклатуре продукции и покупателям

Номенклатура	Контрагент	Объем продаж за базовый период, руб.	Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.
Конфеты «Бриз»	ООО «Елочка»	1500	1,015	1522,50
	ООО «Замок»	5000	1,010	5050,00
	ООО «Зебра»	1000	1,110	1110,00
	ООО «Кенгуру»	7500	1,010	7575,00
	Итого	15 000	1,017	15 257,50
Конфеты «Грильяж»	ООО «Елочка»	1000	1,040	1040,00
	ООО «Замок»	2000	1,040	2080,00
	ООО «Зебра»	500	1,090	545,00
	ООО «Кенгуру»	9500	1,040	9880,00
	Итого	13 000	1,042	13 545,00
Конфеты «Сладкоежка»	ООО «Елочка»	1500	1,070	1605,00
	ООО «Замок»	2000	1,075	2150,00
	ООО «Зебра»	1500	1,100	1650,00
	ООО «Кенгуру»	2000	1,050	2100,00
	Итого	7000,00	1,072	7505,00
Конфеты «Солнышко»	ООО «Елочка»	1000,00	1,050	1050,00
	ООО «Замок»	1000,00	1,015	1015,00
	ООО «Зебра»	1000,00	1,040	1040,00
	ООО «Кенгуру»	1000,00	1,030	1030,00
	Итого	4000,00	1,034	4135,00
Всего		39 000,00	1,037	40 442,50

Учесть факторы, влияющие на рост продаж

На величину выручки влияют два показателя: цена и объем продаж в натуральном выражении. При планировании можно учитывать желаемую динамику каждого из них. Различные источники прироста (цена и количество) принимаются во внимание при формировании целевого процента увеличения (прироста) продаж (см. формулу 2 Расчет целевого процента прироста продаж):

Формула 2. Расчет целевого процента прироста продаж

Например, перед коммерсантами поставили задачу: увеличить объем продаж на 10 процентов. При этом не уточняется, что должно быть источником этого роста. Можно сформулировать цель четче: увеличить количество продаваемого товара на 5 процентов при росте цен на 6 процентов. В этом случае целевой прирост продаж будет равен 11,3 процента ((100% + 5%) × (100% + 6%) : 100% – 100%). Применяя этот способ планирования продаж, нужно учитывать двухуровневую структуру прогноза реализации продукции – ее можно раскрыть как по видам продукции с разделением по контрагентам, так и наоборот (см. таблицу 5. План продаж с учетом динамики цен и объемов реализации). Если у компании большой ассортимент продукции или широкий круг контрагентов, номенклатуры или клиентов лучше объединить в группы. Например, контрагентов можно агрегировать по регионам, масштабам закупок, целям приобретения товара, способам оплаты и т. п.

Таблица 5. План продаж с учетом динамики цен и объемов реализации

Контрагент	Номенклатура	Факт			Коэффи- циент роста цен, ед.	Коэффи- циент роста объема реализации, ед.	Коэффи- циент роста продаж, ед.	План
		Цена, руб.	Коли- чество, кг	Объем продаж, руб.				Цена, руб.	Количество, кг	Объем продаж, руб.
ООО «Елочка»	Конфеты «Бриз»	50,00	30,00	1500,00	1,05	1,06	1,113	52,50	31,80	1669,50
	Конфеты «Грильяж»	100,00	10,00	1000,00	1,03	1,06	1,092	103,00	10,60	1091,80
	Конфеты «Сладкоежка»	25,00	60,00	1500,00	1,04	1,07	1,113	26,00	64,20	1669,20
	Конфеты «Солнышко»	40,00	25,00	1000,00	1,05	1,05	1,103	42,00	26,25	1102,50
Итого	–	125,00	5000,00	–	–-	–	–	132,85	5533,00
ООО «Замок»	Конфеты «Бриз»	40,00	125,00	5000,00	1,07	1,09	1,166	42,80	136,25	5831,50
	Конфеты «Грильяж»	100,00	20,00	2000,00	1,04	1,08	1,123	104,00	21,60	2246,40
	Конфеты «Сладкоежка»	20,00	100,00	2000,00	1,06	1,05	1,113	21,20	105,00	2226,00
	Конфеты «Солнышко»	40,00	25,00	1000,00	1,10	1,06	1,166	44,00	26,50	1166,00
Итого	–	270,00	10 000,00	–	–	–	–	289,35	11 469,90
ООО «Зебра»	Конфеты «Бриз»	50,00	20,00	1000,00	1,08	1,10	1,188	54,00	22,00	1188,00
	Конфеты «Грильяж»	100,00	5,00	500,00	1,09	1,06	1,155	109,00	5,30	577,70
	Конфеты «Сладкоежка»	25,00	60,00	1500,00	1,11	1,10	1,221	27,75	66,00	1831,50
	Конфеты «Солнышко»	40,00	25,00	1000,00	1,06	1,09	1,155	42,40	27,25	1155,40
Итого	–	110,00	4000,00	–	–	–	–	120,55	4752,60
ООО «Кенгуру»	Конфеты «Бриз»	34,90	215,00	7500,00	1,20	1,10	1,320	41,88	236,39	9900,00
	Конфеты «Грильяж»	95,00	100,00	9500,00	1,09	1,03	1,123	103,55	103,00	10 665,65
	Конфеты «Сладкоежка»	20,00	100,00	2000,00	1,08	1,04	1,123	21,60	104,00	2246,40
	Конфеты «Солнышко»	40,000	25,00	1000,00	1,06	1,06	1,124	42,40	26,50	1123,60
Итого	–	440,00	20 000,00	–	–	–	–	469,89	23 935,65
Всего		–	944,90	39 000,00	–	–	–	–	1012,64	45 691,15

Ситуация: как составить прогноз поступления выручки исходя из бюджета продаж

Для подготовки бюджета движения денежных средств необходимо планировать продажи по месяцам, желательно в разрезе контрагентов, так как это позволит учитывать динамику дебиторской задолженности. Выручка прогнозируется с НДС. Если компания не применяет специальные ставки этого налога (10% и 0%), то весь запланированный объем продаж умножается на 18 процентов (см. таблицу 8. План продаж в стоимостном выражении с НДС для бюджета движения денежных средств). В обратном случае потребуется группировать контрагентов и продажи по ним, а затем умножить полученные объемы реализации на соответствующие ставки налога. Составляя бюджет движения денежных средств, не забудьте скорректировать план продаж на прирост и погашение дебиторской задолженности. Если условия платежа для всех контрагентов одинаковы (например, оплата в течение 14 календарных дней после отгрузки), можно уточнять общий план продаж на переходящую дебиторскую задолженность. При различных условиях оплаты необходимо группировать покупателей по длительности отсрочки (см. таблицу 9. Корректировка плана продаж в стоимостном выражении с НДС для бюджета движения денежных средств).

Таблица 6. План продаж в стоимостном выражении с НДС для бюджета движения денежных средств (фрагмент)

Контрагент	Январь			…	Декабрь			Итого за год
		Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.	…	Объем продаж за аналогичный период прошлого года, руб.	Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.	Объем продаж за аналогичный период прошлого года, руб.	Коэффициент роста продаж, ед.	Планируемый объем продаж, руб.
ООО «Елочка»	500,00	1,05	525,00	…	400,00	1,05	420,00	6000,00	1,05	6300,00
ООО «Замок»	600,00	1,04	624,00	…	700,00	1,04	728,00	7800,00	1,04	8112,00
ООО «Зебра»	300,00	1,10	330,00	…	150,00	1,10	165,00	3000,00	1,10	3300,00
ООО «Кенгуру»	2000,00	1,03	2060,00	…	1500,00	1,03	1545,00	21 000,00	1,03	21 630,00
Всего	3400,00	–	3539,00	…	2750,00	–	2858,00	37 800,00	–	39 342,00
НДС (18%)	612,00	–	637,02	…	495,00	–	514,44	6804,00	–	7081,56
Всего с НДС	4012,00	–	4176,02	…	3245,00	–	3372,44	44 604,00	–	46 423,56

Таблица 7. Корректировка плана продаж в стоимостном выражении с НДС для бюджета движения денежных средств (фрагмент)

Показатель	Январь	Февраль	Март	Апрель	Май	…
Дебиторская задолженность на начало периода, руб.	30 000	31 250	27 500	32 750	36 250	…
Объем продаж, руб. с НДС, в т. ч.:	75 000	65 000	74 000	85 000	73 000	…
с отсрочкой платежа 14 календарных дней (приблизительно 50% продаж оплачивается в следующем месяце)	50 000	45 000	57 000	60 000	55 000	…
ООО «Елочка»	20 000	25 000	27 000	30 000	25 000	…
ООО «Замок»	30 000	20 000	30 000	30 000	30 000	…
с отсрочкой платежа 7 календарных дней (приблизительно 25% продаж оплачивается в следующем месяце)	25 000	20 000	17 000	25 000	18 000	…
ООО «Зебра»	10 000	10 000	10 000	10 000	10 000	…
ООО «Кенгуру»	15 000	10 000	7000	15 000	8000	…
Плановая дебиторская задолженность, руб., в т. ч. длиной:	31 250	27 500	32 750	36 250	32 000	…
14 дней	25 000	22 500	28 500	30 000	27 500	…
7 дней	10 000	5000	4250	6250	4500	…
Поступления с учетом прироста (погашения) дебиторской задолженности (дебиторская задолженность на начало периода + объем продаж – плановая дебиторская задолженность)	73 750	68 750	68 750	81 500	77 250	…

Ситуация: как учесть в прогнозе продаж маркетинговые акции и периоды дефицита

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

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

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

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

Результатов различных процессов на основе данных предыдущих периодов времени. В этой статье мы рассмотрим прогнозирование в Excel при помощи очень мощного и удобного инструмента — встроенных статистических функций ЛИНЕЙН и ЛГРФПРИБЛ.

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

В одной из самых популярных статей этого блога подробно рассказано об (рекомендую прочесть). Но в реальных жизненных процессах результат, как правило, зависит от многих независимых друг от друга факторов (переменных). Как выявить и учесть все эти факторы, связать их воедино и на основании накопленных статистических данных спрогнозировать расчетный конечный результат для некоего нового набора исходных параметров? Как оценить достоверность прогноза и степень влияния на результат каждой из переменных? Ответы на эти и другие вопросы – далее в тексте статьи.

Что можно научиться прогнозировать? Очень многое! В принципе, можно научиться прогнозировать любые самые разнообразные результаты процессов в повседневной жизни и работе. Всегда, когда возникает вопрос: «А что будет, если…?» зовите на помощь Excel, рассчитывайте прогноз и проверяйте его достоверность!

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

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

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

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

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

Прежде чем начать решать практическую задачу, хочу обратить внимание на один весьма важный момент. Научиться выполнять прогнозирование в Excel с использованием вышеназванных функций ЛИНЕЙН и ЛГРФПРИБЛ технически не очень сложно. Гораздо сложнее научиться анализировать процесс, приводящий к результату и находить простые факторы, влияющие на него. При этом желательно (но не обязательно) понимать — КАК зависит результат (функция) от каждого из факторов (переменных). Линейная это зависимость или может быть степенная или какая-нибудь другая? Понимание физического смысла процесса поможет вам правильно выбрать переменные. Подбор аппроксимирующей функции следует производить при полном понимании логики и смысла процесса, приводящего к результату.

Подготовка к прогнозированию в Excel.

1. Четко формулируем название и единицу измерения интересующего нас результата процесса. Это и есть искомая функция — y , аналитическое выражение которой мы будем определять с помощью MS Excel.

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

2. Производим анализ процесса и выявляем факторы — аргументы функции — x 1 , x 2 , ... x n — наиболее сильно на наш взгляд влияющие на результат – значения функции y . Внимательно назначаем единицы измерений для переменных.

В примере это:

x 1 — суммарная длина всех прокатных профилей в метрах, из которых изготавливается заказ

x 2 — общая масса всех прокатных профилей в килограммах

x 3 — суммарная площадь всех листов в метрах квадратных

x 4 — общая масса всех листов в килограммах

3. Собираем статистику – фактические данные – в виде таблицы.

В примере – это фактические данные о металлопрокате и фактических сроках выполненных ранее заказов.

Очень важно при выборе переменных x 1 , x 2 , ... x n учесть их доступность. То есть, значения этих факторов должны быть у вас в виде достоверных статистических данных. Очень желательно, чтобы получение значений статистических данных было простым, понятным и нетрудоемким процессом.

Переходим непосредственно к рассмотрению примера.

Небольшой участок завода производит строительные металлоконструкции. Входным сырьем является листовой и профильный металлопрокат. Мощность участка в рассматриваемом периоде времени неизменна. В наличии есть статистические данные о сроках изготовления 13-и заказов (k =13) и количестве использованного металлопроката. Попробуем найти зависимость срока изготовления заказа от суммарной длины и массы профильного проката и суммарной площади и массы листового проката.

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

Примечательно, что найденная зависимость связывает в одной формуле параметры с различными единицами измерения. Это нормально. Найденные коэффициенты не являются безразмерными. Например, размерность коэффициента b – рабочие дни, а коэффициента m 1 – рабочие дни/м.

1. Запускаем MS Excel и заполняем ячейки B4...F16 таблицы Excel исходными статистическими данными. В столбцы пишем значения переменных x i и фактические значения функции y , располагая данные, относящиеся к одному заказу в одной строке.

2. Так как функции ЛИНЕЙН и ЛГРФПРИБЛ — функции выводящие результаты в виде массива , то их ввод имеет некоторые особенности. Выделяем область размером 5×5 ячеек — ячейки I9...M13. Количество выделенных строк всегда — 5, а количество столбцов должно быть равно количеству переменных x i плюс 1. В нашем случае это: 4+1=5.

3. Нажимаем на клавиатуре клавишу F2 и набираем формулу

в ячейках I9...M13: =ЛИНЕЙН(F4:F16;B4:E16;ИСТИНА;ИСТИНА)

4. После набора формулы необходимо для ее ввода нажать сочетание клавиш Ctrl+Shift+Enter. (Знак «+» нажимать не нужно, в записи он означает, что клавиши нажимаются последовательно при удержании нажатыми всех предыдущих.)

5. Считываем результаты работы функции ЛИНЕЙН в ячейках I9...M13.

Карту, поясняющую значения каких параметров в каких ячейках выводятся, я расположил в ячейках I4...M8 для удобства чтения сверху над массивом значений.

Общий вид уравнения аппроксимирующей функции y , представлен в объединенных ячейках I2...M2.

Значения коэффициентов b , m 1 , m 2 , m 3 , m 4 считываем соответственно

в ячейке M9: b =4,38464164

в ячейке L9: m 1 =0,002493053

в ячейке K9: m 2 =0,000101103

в ячейке J9: m 3 =-0,084844006

в ячейке I9: m 4 =0,002428953

6. Для определения расчетных значений функции y — срока изготовления заказа — вводим формулу

в ячейку G4: =$L$9*B4+$K$9*C4+$J$9*D4+$I$9*E4+$M$9 =5,0

y =b +m 1 *x 1 +m 2 *x 2 +m 3 *x 3 +m 4 *x 4

7. Копируем эту формулу во все ячейки столбца от G5 до G17 «протягиванием» и сверяем расчетные значения с фактическими. Совпадение очень хорошее!

8. Предварительные действия все выполнены. Уравнение аппроксимирующей функции y найдено. Пробуем выполнить прогнозирование в Excel срока изготовления нового заказа. Вписываем исходные данные.

8.1. Длину прокатных профилей по проекту x 1 в метрах пишем

в ячейку B17: 2820

8.2. Массу прокатных профилей x 2 в килограммах пишем

в ячейку C17: 62000

8.3. Площадь листового проката, используемого в новом заказе по проекту, x 3 в метрах квадратных заносим

в ячейку D17: 110,0

8.4. Общую массу листового проката x 4 в килограммах вписываем

в ячейку E17: 7000

9. Расчетный срок изготовления заказа y в рабочих днях считываем

в ячейке G17: =$L$9*B17+$K$9*C17+$J$9*D17+$I$9*E17+$M$9 =25,4

Прогнозирование в Excel выполнено. На основе статистических данных мы рассчитали предположительный срок выполнения нового заказа — 25,4 рабочих дней. Остается выполнить заказ и сверить фактическое время с прогнозным.

Анализ результатов.

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

Обратимся к другим данным в массиве, которые вывела функция ЛИНЕЙН.

Во второй строке массива в ячейках I10…M10 выведены стандартные ошибки se 4 , se 3 , se 2 , se 1 , se b для расположенных выше в первой строке массива соответствующих коэффициентов уравнения аппроксимирующей функции m 4 , m 3 , m 2 , m 1 , b .

В третьей строке в ячейке I11 выведено значение коэффициента множественной детерминации r 2 , а в ячейке J11 — стандартная ошибка для функции — se y .

В четвертой строке в ячейке I12 находится, так называемое F -наблюдаемое значение, а в ячейке J12 — df – количество степеней свободы.

Наконец, в пятой строке в ячейках I13 и J13 соответственно размещены ss reg — регрессионная сумма квадратов и ss resid — остаточная сумма квадратов.

На что следует в регрессионной статистике обратить особое внимание? Что для нас наиболее важно?

1. На сколько достоверно прогнозирует срок изготовления полученное уравнение функции y ? При высокой достоверности аппроксимации значение коэффициента детерминации r 2 близко к максимуму — к 1! Если r 2 <0,7…0,8, то различия между фактическими и расчетными значениями функции будут значительными, и применять полученную формулу для прогнозирования, скорее всего, нельзя.

В нашем примере r 2 =0,999388788. Это означает, что найденное уравнение функции y чрезвычайно точно определяет срок изготовления заказа по четырем входным данным. Вышесказанное подтверждается сравнительным анализом значений в ячейках F4…F16 и G4…G16 и указывает на существенную зависимость между сроком изготовления и данными о входящем в заказ металлопрокате.

2. Определим важность и полезность каждой из четырех переменных x 1 , x 2 , x 3 , x 4 в полученной формуле с помощью, так называемой, t -статистики.

2.1. Рассчитываем t 4 , t 3 , t 2 , t 1 , соответственно

в ячейке I16: t 4 = I9/I10 =26,44474886

в ячейке J16: t 3 = J9/J10 =-11,79198416

в ячейке K16: t 2 = K9/K10 =3,76748771

в ячейке L16: t 1 = L9/L10 =3,949105515

t i = m i / se i

2.2. Вычисляем двустороннее критическое значение t крит с уровнем достоверности α =0,05 (предполагается 5% ошибок) и количеством степеней свободы df =8

в ячейке M16: t крит =СТЬЮДРАСПОБР(0,05; J12) =2,306004133

Так как для всех t i справедливо неравенство | t i |> t крит , то это означает, что все выбранные переменные x i полезны при расчете сроков изготовления заказов – y .

Наиболее значимой переменной при прогнозировании в Excel сроков изготовления заказов y является x 4 , так как | t 4 |>| t 3 |>| t 1 |>| t 2 | .

3. Не является ли случайным полученное значение коэффициента детерминации r 2 ? Проверим это, используя F -статистику (распределение Фишера), которая характеризует «неслучайность» высокого значения коэффициента r 2 .

3.1. F -наблюдаемое значение считываем

в ячейке I12: 3270,188104

3.2. F -распределение имеет степени свободы v 1 и v 2 .

v 1 = k — df -1 =13-8-1=4

v 2 = df =8

Рассчитаем вероятность получения значения F -распределения большего, чем F -наблюдаемое

в ячейке I12: =FРАСП(I12;4;J12) =6,97468*10 -13

Так как вероятность получения большего значения F -распределения, чем наблюдаемое чрезвычайно мала, то из этого следует вывод — найденное уравнение функции y можно применять для прогнозирования сроков изготовления заказов. Полученное значение коэффициента детерминации r 2 не является случайным!

Заключение.

Применение функции MS Excel ЛГРФПРИБЛ почти не отличается от работы с функцией ЛИНЕЙН кроме вида уравнения искомой функции, которое принимает для рассмотренного примера следующий вид:

y =b *(m 1 x1 ) *(m 2 x2 )*(m 3 x3 )*(m 4 x4 )

Статистика множественной регрессии, которую рассчитывает функция ЛГРФПРИБЛ, базируется на линейной модели:

ln (y )=x 1 *ln (m 1 )+x 2 *ln (m 1 )...+x n *ln (m n )+ln (b )

Это означает, что значения, например, se i нужно сравнивать не с m i , а с ln (m i ) . (Подробнее об этом почитайте в справке MS Excel.)

Если в результате использования функции ЛГРФПРИБЛ коэффициент детерминации r 2 окажется ближе к 1, чем при использовании функции ЛИНЕЙН, то применение аппроксимирующей функции вида

y =b *(m 1 x 1 )*(m 2 x 2 )…*(m n x n ),

несомненно, является более целесообразным.

Если прогнозное значение функции y находится вне интервала фактических статистических значений y , то вероятность ошибки прогноза резко возрастает!

Для обеспечения высокой точности прогнозирования в Excel необходима точная и обширная статистическая база данных – информация об известных из практики результатах процессов. Но, даже имея в наличии такую базу, вы не будете застрахованы от ложных предположений и выводов. Процесс прогнозирования коварен и полон неожиданностей! Помните об этом всегда! Глубже вникайте в суть прогнозируемого процесса. Тщательней относитесь к выбору и назначению переменных. На полученные результаты всегда смотрите через «очки скептика». Такой подход поможет избежать серьезных ошибок в важных вопросах.

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

Отзывы, вопросы и замечания, уважаемые читатели, пишите в комментариях внизу страницы.

ПРОШУ уважающих труд автора СКАЧАТЬ файл ПОСЛЕ ПОДПИСКИ на анонсы статей!