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

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

    Традиционные методы.

    Методы маржинального анализа.

    Экономико-математические методы.

К традиционным методам прогнозирования прибыли обычно относят методы:

  1. Прямого ассортиментного расчета.
  2. Укрупненный (по плановому объему товарной продукции и изменению остатков нереализованной продукции).
  3. Совмещенного расчета.

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

Методы маржинального анализа включают:

  1. Расчет точки безубыточности и моделирование прибыли по соотношению «затраты-выпуск-прибыль» (costs-volume-profit).
  2. Планирование прибыли на основе эффекта операционного и финансового левериджа (рычага).
  3. Планирование прибыли на основе предельных (дополнительных) издержек и предельного дохода.

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

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

PSp = PSb ± DS ± DN ± DVC ± DFC ± DP

Где,
PSp - прогнозируемая прибыль;
PSb - прибыль базисного периода;
DS - влияние изменения объема продаж;
DN - влияние структурного (ассортиментного) сдвига в объеме продаж;
DVC - влияние изменения переменных расходов;
DFC - влияние изменения постоянных расходов;
DP - влияние изменения продажных цен на продукцию организации.

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

Покажем методику прогнозирования прибыли от продаж на небольшом примере :

Таблица 1. Отчетные данные в базовом периоде

Показатели Условное
обозначение 		Величина Метод расчета
1. Объем продаж, тыс. руб. S 500 000 Отчет
2. Переменные расходы, тыс. руб. VC 320 000 Отчет
3. Коэффициент переменных расходов b 0,64 VC: S
4. Маржинальный доход, тыс. руб. MP 180 000 S - VC
5. Коэффициент маржинального дохода а 0,36 MP: S
6. Постоянные расходы, тыс. руб. FC 80 000 Отчет
7. Прибыль от продаж, тыс. руб. PS 100 000 MP - FC
8. Операционный рычаг OL 1,8 MP: PS

В выручке от продаж выделим три товарные группы А, В, С со следующими структурными показателями:

Таблица 2. Структура продаж предприятия

Прогнозируются следующие цели на будущий период:

  1. Рост выручки от продаж на 15%.
  2. Изменение структуры продаж: товарная группа А - 50%; товарная группа В - 40%; товарная группа С - 10%.
  3. Снижение переменных расходов на 10%.
  4. Увеличение постоянных расходов на 20 тыс. руб.
  5. Рост цен на продаваемую продукцию на 20%.

Выполняем прогнозный расчет прибыли от продаж по факторам:

1. Фактор влияния объема продаж на прибыль (DS) может быть рассчитан по формуле:

DS = DTs x OL x PS

где, DTs - прогнозируемый прирост выручки от продаж; OL - операционный рычаг; PS - прибыль от продаж в базовом периоде.

15% x 1,8 = +27% (эффект операционного рычага).
100000 x +27% = +27000

2. Фактор структурных сдвигов в объеме продаж повлияет на прибыль следующим образом (DN)
Товарные группы Удельный вес
в % 		MP
на рубль 		Взвешенный
MPn продаж
1. Группа А 50% 0,6 0,6 x 0,5 = 0,3
2. Группа В 40% 0,32 0,32 x 0,4 = 0,128
3. Группа С 10% 0,1 0,10 x 0,1 = 0,01
Итого 100% x 0,44

Произошел структурный сдвиг, повлиявший на коэффициент маржинального дохода (MPn). Коэффициент MP увеличился с 0,36 до 0,438, то есть на 0,078, или на +7,8%. Его влияние на прогнозируемую прибыль определится по формуле:

DN = Sb x Tb x DMPn

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

500000 x 1,15 x +7,8% = +44850

3. Влияние на прибыль фактора переменных расходов (DVC) определится как:

DVC = Sb x Ts x VCn x VCp

где, VCn - коэффициент переменных расходов с учетом структурных сдвигов (1 - MPn); VCp - прогноз снижения переменных расходов (с обратным знаком).

500000 x 1,15 x (1 - 0,438) x +10% = +32315

4. Влияние на прибыль фактора постоянных расходов (DFC) определяется прямым счетом, но с обратным знаком. При увеличении постоянных расходов на прибыль (+20000) прибыль снизится на эту же сумму (-20000).

5. Влияние на прибыль фактора цен (DP) определится по формуле:

DP = Sb x Ts x Pp

где, Pp - прогнозируемое изменение цен на продаваемую продукцию.

500000 x 1,15 x +20% = 115000.

В результате мы получим величину прогнозируемой прибыли: 100000 + 27000 + 44850 + 32315 - 20000 + 115000 = 299165 тыс. рублей.

Итак, по нашему расчету прибыль от продаж должна возрасти со 100000 тыс. рублей до 299165 тыс. рублей или в 2,99 раза. Как видим, факторный анализ существенно меняет расчет прибыли, который можно было выполнить на основе одного лишь

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

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

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

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


Способ 2: оператор ПРЕДСКАЗ

Экстраполяцию для табличных данных можно произвести через стандартную функцию Эксель ПРЕДСКАЗ . Этот аргумент относится к категории статистических инструментов и имеет следующий синтаксис:

ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

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

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.


Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.

Способ 3: оператор ТЕНДЕНЦИЯ

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

ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ , а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа» , но он не является обязательным и используется только при наличии постоянных факторов.

Данный оператор наиболее эффективно используется при наличии линейной зависимости функции.

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


Способ 4: оператор РОСТ

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

РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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


Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ . Его синтаксис имеет такой вид:

ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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


Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ . Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Как видим, все аргументы полностью повторяют соответствующие элементы предыдущей функции. Алгоритм расчета прогноза немного изменится. Функция рассчитает экспоненциальный тренд, который покажет, во сколько раз поменяется сумма выручки за один период, то есть, за год. Нам нужно будет найти разницу в прибыли между последним фактическим периодом и первым плановым, умножить её на число плановых периодов (3) и прибавить к результату сумму последнего фактического периода.


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

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

" № 17/2010

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

Да, чтобы спланировать выручку на следующий месяц или год, не обязательно проводить углубленный анализ рынка и спроса. Для этого финансовая служба может взять на вооружение метод PERT (Program Evaluation Review Technique). Его суть сводится к следующему. У сотрудников коммерческого отдела достаточно выяснить, какие продажи они ожидают в самом лучшем случае (оптимистичный сценарий (О)), в самом худшем (пессимистичный сценарий (П)) и каковы реалистичные цифры по реализации (реалистичный сценарий (Р)). После чего наиболее вероятный объем продаж (Впродаж) определяется по формуле:

Впродаж = (П + 4 × Р + О) : 6.

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

Тогда можно рассчитать, как выручка компании реагировала на изменение статистики. Проще всего это сделать в Excel через функцию =КОРРЕЛ. Скажем, если компании хочет определить зависимость своей выручки от ВВП, то понадобится внести данные по выручке и ВВП в Eхсеl за каждый месяц, а затем набрать формулу =КОРРЕЛ и выбрать два диапазона ячеек с цифрами – сначала по выручке, затем по ВВП.Полученная цифра покажет, насколько сильно выручка компании зависит от изменения российского ВВП. Если коэффициент получится больше 0,9, то скорее всего выручка компании будет вести себя так же, как ВВП. Если коэффициент в интервале от 0,8 до 0,9, то зависимость двух показателей небольшая. Аналогичный расчет можно сделать и по другой статистике. А потом выбрать тот показатель, от которого выручка зависит сильнее всего. И уже по этим данным строить прогноз.

Есть ли какие-то профессиональные секреты, которые помогают эффективнее управлять финансами компании?

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

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

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

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

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

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

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

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

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

Для планирования «от достигнутого» основой служит информация о динамике продаж (в натуральном и стоимостном выражении) за предыдущий период, сопоставимый как по продолжительности, так и по сезонности с плановым. Это требование бывает трудно выполнить, так как продажи обычно прогнозируются в 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 …

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

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

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

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

Условное форматирование (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}}