Как прогнозировать доходы. Инструменты прогнозирования в Microsoft Excel

Вопрос №0008: «Здравствуйте! Подскажите пожалуйста, как прогнозировать выручку от продаж, здесь идет зависимость от кол-ва? Я сравнивала с предыдущем годом но мне кажется не то! Нужно среднюю прибыль умножать на прирост? А как найти этот коэффициент? Мне нужно составить прогноз выручки.»

Ответ:

Добрый день, Алина.

Прогнозирование выручки от продаж на предприятиях на практике осуществляется двумя методами.

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

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

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

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

Пример формы для необходимых расчетов вы можете найти в списке дополнительных материалов к данной статье или в разделе основного меню « ».

Если у Вас возникнут вопросы, буду рад на них ответить.

Прогнозирование выручки

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

Декомпозиция факторов

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

Личный опыт

Сергей Пустовалов, финансовый директор компании "Талосто" (Санкт-Петербург)

Прогноз развития "Талосто" составляется сроком на пять лет с разбивкой по годам и бизнес-направлениям, и рассчитывается в пессимистичном, оптимистичном и наиболее реальном вариантах. В качестве основных факторов, влияющих на финансовый прогноз компании, используются: валовой продукт по рынкам сбыта, вложения в рекламу, действия конкурентов, прирост сегментов рынка. Целевым показателем для нас является рыночная доля компании – она должна расти быстрее рынка, или, по крайней мере, вместе с ним. Так, при пессимистичном сценарии рост целевых сегментов рынка считается минимальным и составляет 15 % в год.

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

Прогнозирование факторов

Теперь нужно построить прогнозы релевантных факторов. При наличии статистической информации о прошлой динамике в качестве отправных значений прогноза удобнее всего использовать зависимость фактора от времени (тренд). Ее можно определить с помощью Excel, построив график, добавив линию тренда, и выведя уравнение зависимости (см. График 1).

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

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

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

Справка

Проще всего выразить зависимость фактора от времени с помощью линейной зависимости:

где Y – прогнозируемый фактор,

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

Прогнозирование выручки

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

Пример

Прогнозирование выручки компании

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

Определив список релевантных факторов, нужно построить прогнозные значения для каждого из них. Рассмотрим построение прогноза для одного из главных фактов – объема жилищного строительства.

График 1
Прогнозирование объема жилищного строительства

В течение последних лет на рынке наблюдался интенсивный рост объемов строительства и цен на квартиры. Половина аналитиков считает, что рынок "перегрет", и в 2005 - 2006 годах люди, скупавшие квартиры в инвестиционных целях, начнут их продавать (пессимистичный прогноз). Известно, что такие инвесторы составляют 20 % от общего числа покупателей квартир, поэтому объем строительства кратковременно упадет на 40 % (так как инвесторы продадут свои 20 % квартир), а потом вырастет на 20 %. Соответственно, рассчитанные значения объема жилищного строительства в 2005 - 2006 годов необходимо скорректировать, умножив сначала на 0,6, а затем на 0,8. В то же время, вторая половина аналитиков считает, что спрос, и, соответственно, объем строительства буду расти на 15 % в год (оптимистичный прогноз), причем с развитием ипотечного кредитования темпы роста со временем будут увеличиваться. Основываясь на прогнозах банковских специалистов относительно количества выданных ипотечных кредитов (допустим, эта цифра на ближайшие 3 года составляет 4,5 млрд. долл. США, что при текущей средней цене в 1500 долл. США за кв.м. составит 3 млн. кв.м.), и, зная текущий объем строительства (например, 50 млн.кв.м.), можно сделать вывод о том, что за счет этого фактора наш прогноз на 2005 - 2006 годы вырастет на 6 %. Корректирующий коэффициент для каждого года будет равен 1,03. Предположим, что остальные факторы в ближайшие два года останутся неизменными. Тогда при реализации оптимистичного варианта рост рынка в целом также составит 21 %, а при реализации пессимистичного – 100 - (0,6 + 0,8) / 2 = - 30 %.

Теперь необходимо определить, каким образом будет изменяться рыночная доля компании. Для сохранения и увеличения доли рынка в прогнозируемом периоде планируется заменить половину ассортимента продаваемых товаров на сантехнику, являющуюся "модной". Известно, что темпы роста потребления такой сантехники составляют 20 % в год, при темпе роста рынка 5 % в год, следовательно, увеличение рыночной доли компании за счет смены половины ассортимента составит 7,5 % ((20 % - 5 %)х 50 %) (оптимистичный вариант). Если же спрос на "модную" сантехнику в будущем периоде снизится до 10 %, рост доли рынка составит 2,5 % ((10 % - 5 %) х 50 %).

Объединим влияние внешних и внутренних факторов в таблицу:

Таблица 1

Таким образом, мы получили два граничных значения для выручки компании – 30,08% и –28,25%.

Сценарий будущего

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

(Подробнее о моделировании деятельности компании см. статью "Модель оценки стоимости компании: разработка и применение", № 12, 2003, с.10 – Примеч. редакции)

Личный опыт

Сергей Пустовалов

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

Затем прогнозные доходы и расходы компании сводятся воедино, и получаются четыре граничных варианта развития (см. Таблицу 2).

Таблица 2

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

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

Личный опыт

Денис Иванов , генеральный директор ЗАО "Финансовый резерв" (Москва)

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

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

Похожая информация.

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

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

Методы прогнозирования

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

Экспертные методы

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

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

Статистические методы

Если рынок относительно предсказуем и компания располагает данными о предыдущей динамике прогнозируемого показателя или же о динамике факторов, которые на него влияют, то для кратко- или среднесрочного прогнозирования целесообразно использовать статистические методы. Эти методы основаны на предположении, что в будущем анализируемый показатель будет изменяться по тем же законам, что и в прошлом. Статистические методы различной сложности используют практически все рыночно ориентированные компании, применяя при этом либо Excel, либо специализированные статистические программы (SPSS, Statistica и т. д.).

Рассмотрим два статистических метода - построение тренда и метод цепных индексов.

Построение тренда. Бо"льшая часть статистических методов прогнозирования основана на построении тренда, то есть математического уравнения, описывающего поведение прогнозируемого показателя. Наиболее распространенным примером такого уравнения является зависимость объема продаж от времени. Динамика показателя может описываться как прямой линией (линейный тренд), так и кривой (нелинейный тренд). При построении уравнений линии нужно руководствоваться следующими правилами:

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

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

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

Этапы прогнозирования

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

Учет факторов

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

Построение прогнозных значений

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

Личный опыт Сергей Пустовалов, финансовый директор компании «Талосто» (Санкт-Петербург)

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

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

ООО «Сантехника» - быстроразвивающаяся компания, специализирующаяся на оптовой торговле отделочными материалами и сантехникой экономкласса. Факторы, влияющие на объем продаж, приведены на рисунке. Рассмотрим построение прогноза для одного из главных факторов - объема продаж сантехники для новых жилых домов на 2005-2006 годы.

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

Y=14,762Ln(x) + 18,313,

где Y - объем строительства жилья;
х - время (по годам, за х = 1 принят 1998 год);
14,762 и 18,313 - рассчитанные коэффициенты.

Аналитики рынка недвижимости ожидают, что в прогнозируемые годы на рынке произойдут значительные изменения. Часть экспертов ожидает резкого увеличения спроса на новое жилье и роста объемов продаж на 20% в год от базового прогноза (оптимистический прогноз). Другие специалисты считают, что рынок «перегрет», поэтому в 2005 году инвесторы, скупавшие жилье для спекулятивных целей, начнут его продавать, что приведет к падению объема продаж новых квартир на 40%. Затем, по их мнению, рынок стабилизируется и в 2006 году вырастет на 20% от уровня 2005 года (пессимистический вариант). В свою очередь банковские специалисты считают, что реализация государственной программы по развитию ипотеки будет способствовать увеличению строительства жилья на 3% ежегодно. Остальные внешние факторы останутся неизменными. Все полученные прогнозы сведены в табл. 1.

Теперь необходимо определить, как изменится прогноз под влиянием внутренних факторов. Для сохранения и увеличения доли рынка в прогнозируемом периоде планируется расширить ассортимент продаваемых товаров за счет современной дорогой сантехники. Спрос на такую сантехнику постоянно увеличивается, поэтому благодаря смене ассортимента компания планирует увеличить свои продажи на 20% (оптимистический прогноз), в худшем случае - на 10% (пессимистический). Теперь можно суммировать эффект от внутренних и внешних факторов для 2005 года (см. табл. 2). Аналогичную таблицу легко составить и для 2006 года.

Несмотря на то что полученные прогнозы объема продаж сантехники для нового жилья сильно варьируются (от -28,2% до +43,6%), они предоставляют руководству фирмы важную информацию. Менеджеры могут видеть, как изменятся объемы продаж в случае, если бум на рынке жилья будет продолжаться или если цены на «спекулятивное» жилье начнут падать. Имея такие прогнозы, они могут разрабатывать сценарии действий компании в той или иной ситуации и определять пути достижения положительного финансового результата при любом развитии событий.

Сценарии будущего

Для каждого варианта прогноза доходов необходимо разработать соответствующий ему сценарий расходов. Это делается с помощью существующей бюджетной модели компании 1 . Затем прогнозируемые доходы и запланированные расходы компании сводятся воедино и получаются четыре граничных варианта развития (см. табл. 3). Для каждого варианта строятся основные финансовые бюджеты - БДДС, БДР и прогнозный баланс. Анализ полученных результатов с точки зрения стратегии компании и ее финансовых показателей позволяет разработать план действий для каждого из сценариев развития с учетом свойственных им рисков. В итоге компания должна получить наиболее вероятный сценарий своего развития и разработанный комплекс мероприятий и действий на случай отклонения фактических показателей от их прогнозируемых значений.

Личный опыт

Денис Иванов, генеральный директор ЗАО «Финансовый резерв» (Москва)

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

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

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

Личный опыт

Олег Фракин, финансовый директор ООО «Винный мир Холдинг» (Москва)

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

В компаниях, для которых большая часть издержек является постоянной (как, например, у нас), существует еще один «тормоз» для оперативного управления в рамках прогноза. При неисполнении плана продаж я не могу, к примеру, уволить половину персонала, чтобы снизить постоянные издержки. Регулировать можно только переменные издержки. Частично эту проблему можно решить путем перевода постоянных издержек в переменные, то есть использования аутсорсинга, например, в IT-службе, но поступать так можно не всегда. В итоге сценарии работы для нашей компании достаточно очевидны: если мы заработали за период на 20% меньше запланированного, то сэкономить я смогу максимум 5% от заранее утвержденной суммы.

Ошибки прогнозирования

Оценка одного варианта развития

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

Чаше всего при прогнозировании используется метод экстраполяции, то есть определение зависимости между параметрами модели на основе данных прошлых периодов и перенос этих зависимостей на будущие. Например, компания прогнозирует на пять лет вперед рост продаж по 10% в год, при этом не учитывает текущие тенденции рынка и возможные экономические события (например, вступление России в ВТО). В итоге такие прогнозы уже через год могут оказаться бесполезными. Поэтому экстраполяция годится только в качестве инструмента «заготовки» прогнозных значений.

Личный опыт

Евгений Дубинин, заместитель финансового директора строительной компании «ЛЭК-Москва»

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

Недооценка или игнорирование факторов

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

Неполный учет предполагаемых изменений

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

Стремление выдать желаемое за действительное

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

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

Прогнозирование – это очень важный элемент практически любой сферы деятельности, начиная от экономики и заканчивая инженерией. Существует большое количество программного обеспечения, специализирующегося именно на этом направлении. К сожалению, далеко не все пользователи знают, что обычный табличный процессор 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 тыс. рублей, что опять не сильно отличается от результатов, полученных при вычислении предыдущими способами.

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

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

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

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

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

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

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

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

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

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

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

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

• Устойчивый характер переменных расходов к выручке от продаж.
• Неизменность постоянных расходов.
• Постоянство структуры продаж по видам (группам) продукции.

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

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

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

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

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

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

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

Таблица 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

Произошел структурный сдвиг, повлиявший на коэффициент маржинального дохода (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 раза. Как видим, факторный анализ существенно меняет расчет прибыли, который можно было выполнить на основе одного лишь