Как найти прогнозы по прибыли

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

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

• 1С:ERP Управление предприятием;
• 1С:Управление холдингом;
• WA:Финансист (1С:Совместимо).

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

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

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

Комплексная автоматизация от ведущего IT-интегратора 1С. Оценка проекта бесплатно!

Если же говорить о программных продуктах 1С, прибыль можно прогнозировать с учетом прошедшего периода. В 1С:ERP для этого необходимо заполнить правила получения данных по статьям, а в 1С:УХ – правила расчета.

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

Метод прямого счета. Здесь прогнозируемая прибыль складывается из прибылей, входящих в прогнозный отчет – P&L, или, используя российскую терминологию, в БДР. В каждой компании состав такого отчета зависит от индивидуальной методологии его формирования, но в любом случае, он будет содержать то, что нам нужно.

Метод бюджетирования (Нормативный метод). Чтобы пользоваться данной методикой, каждая компания для себя разрабатывает нормы расходования сырья и материалов по видам продукции по каждой статье затрат, которые далее распределяются по ЦФО. И исходя из зафиксированных нормативов рассчитывается прогнозируемая прибыль по компании.

Факторный метод. Принцип этого метода состоит в следующем:

• определяются базовые показатели прибыли текущих и предыдущих периодов или конкретного периода;
• определяются плановые показатели на интересующий период;
• определяются факторы, влияющие на прибыль и, исходя из этих факторов, рассчитываются индексы.

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

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

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

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

Взаимосвязь прогнозной выручки и прогнозной прибыли

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

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

Примеры расчета прогноза прибыли

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

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

• Производственной.
• Финансовой.
• Инвестиционной деятельности.

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

Для расчета данных видов прибыли можно воспользоваться стандартной формулой расчета прибыли:

Плановая прибыль = Плановая выручка – Плановые затраты

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

Плановая ПРИБЫЛЬ по определенному виду деятельности = Выручка по определенному виду деятельности – Затраты, понесенные по определенным видам деятельности

Пример отчета представлен в таблице «Отчет по плановым показателям прибыли по видам деятельности»

Показатель /Сумма (тыс. руб.)	1-ый квартал	2-ый квартал	3-ый квартал	4-ый квартал	ИТОГО за год
План	План	План	План	План
Плановая выручка, полученная от произв-ной (основной) деятельности	229 816	221 519	191 453	207 859	850 647
Плановые затраты, понесенные от произв-ной (основной) деятельности	122 469	118 885	100 951	111 228	453 533
Плановая прибыль от произв-ной (основной) деятельности	107 347	102 634	90 502	96 631	397 114
Плановая выручка, полученная от финансовой деятельности	71 982	84 887	58 300	75 892	291 061
Плановые затраты, понесенные от финансовой деятельности	44 059	28 949	29 579	35 703	138 290
Плановая прибыль от финансовой деятельности	27 923	55 938	28 721	40 189	152 771
Плановая выручка, полученная от инвест. деятельности	56 718	55 938	36 971	40 189	189 816
Плановые затраты, понесенные от инвест. деятельности	27 922	37 218	28 720	37 235	131 095
Плановая прибыль от инвест. деятельности	28 796	18 720	8 251	2 954	58 721

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

• Допустим, что плановая выручка, полученная от финансовой деятельности за первый квартал, составила 71 982 тыс руб.
• Плановые затраты, понесенные от финансовой деятельности за первый квартал, составили 44 059 тыс руб.
• Таким образом прогнозная прибыль от финансовой деятельности за первый квартал составила: 71 982 тыс руб – 44 059 тыс руб = 27 923 тыс руб.

Прогнозирование прибыли в программных продуктах

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

Прогнозирование прибыли в 1С:ERP

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

• «Сценарии товарного планирования»
• «Виды планов»
• «Рейтинги продаж номенклатуры»
• «Источники данных планирования»

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

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

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

Для прогнозирования прибыли в 1С:ERP можно воспользоваться справочником «Модели бюджетирования». Этот справочник предназначен для регистрации и настройки любых моделей бюджетирования для прогнозирования различных показателей, включая прибыль. Например, «Модель для планирования маржинальной прибыли».

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

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

Отметим важное преимущество ведения учета и контроля данных при помощи 1С: план-фактный анализ прогнозируемой прибыли в программных продуктах, о которых речь идет в данной статье, можно проводить в любой момент времени (естественно, при наличии актуальных данных в базах) в независимости от закрытия периода, то есть на различных этапах планирования и вообще – в любой момент времени. Например, при помощи отчета «Бюджет доходов и расходов».

Прогнозирование прибыли в 1С:Управление холдингом

Для прогнозирования прибыли в программе 1С:УХ можно воспользоваться инструментом – «Виды и бланки отчетности». Для этого нам надо настроить такие справочники, как:

• Номенклатурные группы
• Виды аналитик
• Организационные единицы
• Основные валюты
• Периоды отчета
• Сценарии отчета

А также параметры:

• Организационные единицы
• Единицы измерения
• Сценарии
• Период

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

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

План-фактный анализ выполнения мероприятий в 1С:УХ может быть сформирован по каждому мероприятию и на любом этапе.

Оперативные консультации без предоплаты от 1С:Центра компетенции по ERP-решениям

Прогнозирование прибыли в программе WA:Финансист

Прогнозирование прибыли в программе WA:Финансист – специализированной разработке нашей компании на платформе «1С:Предприятие» и полностью совместимой с другими тиражными продуктами фирмы, можно реализовать после внесения данных и утверждения планов в системе. Прогнозирование прибыли в данном программном продукте можно проводить при помощи блока «Настраиваемая отчетность».

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

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

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

ГОДОВОЙ ПРОГНОЗ ПО ВЫРУЧКЕ

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

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

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

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

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

АНАЛИЗ ИТОГОВ И ДИНАМИКИ ПРОДАЖ ЗА ПРОШЕДШИЕ ОТЧЕТНЫЕ ПЕРИОДЫ

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

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

В качестве примера рассмотрим итоги продаж компании «Альфа», которая производит и реализует одежду для мужчин по трем ассортиментным группам: свитеры, костюмы и рубашки. Компании необходимо запланировать выручку на 2018 г. в разбивке по месяцам и ассортиментным группам. Результаты реализации продукции за 2017 г. представлены в табл. 1.

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

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

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

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

Для анализа темпов роста продаж компании «Альфа» взяты данные за четыре последних года (с 2014 г.), поскольку бизнес компании стабильно развивается именно на этом временном отрезке и позволяет предполагать аналогичную динамику развития в 2018 г.

Темпы роста рассчитывают путем деления количественного объема продаж по каждой ассортиментной группе каждого из годов периода на объемы предыдущего года (2015/2014; 2016/2015; 2017/2016). Далее находят среднее значение темпа роста продаж за анализируемый период, а затем на основании фактического количества продаж в последнем отчетном году и полученного среднего значения темпов роста прогнозируют количество реализуемой в планируемом году продукции.

Расчет прогноза количественного объема продаж продукции на 2018 г. для компании «Альфа» показан в табл. 2.

Из таблицы 2 видно, что по каждой товарной группе динамика продаж положительная на протяжении всех рассматриваемых лет. По группе «Костюмы» темпы роста в 2017 г. замедлились по сравнению с 2016 г., поэтому среднее значение за три последних года будет наиболее оптимальным для прогноза на 2018 г. По остальным двум группам темпы роста каждого года стабильные и поэтому практически равны среднему показателю за период.

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

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

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

Теперь для разработки предварительного прогноза выручки компании на 2018 г. нам осталось проанализировать динамику цен реализации единицы продукции за 2017 г. Для этого берем цену реализации января в качестве базовой (100 %) и находим соотношение цены каждого из следующих месяцев в процентном отношении к этой базовой январской цене. В итоге получаем данные табл. 4.

Выводы по данным табл. 4:

1. по группе продукции «Свитеры» цена реализации с сентября увеличилась на 10 %, поэтому среднюю величину цены реализации на 2018 г. следует брать из расчета периода с сентября по декабрь;
2. по группе «Костюмы» увеличение цены реализации стабилизировалось с июля, следовательно, в расчет цены на 2018 г. возьмем период с июля по декабрь;
3. по группе продукции «Рубашки» с апреля возник тренд на снижение цены реализации, поэтому будет логичным для прогноза на 2018 г. взять за основу среднегодовую цену реализации по итогам всего 2017 г.

УВЕЛИЧЕНИЕ ПРОГНОЗА ПРОДАЖ НА ОБЪЕМЫ ВЫРУЧКИ, СВЯЗАННОЙ С РЕАЛИЗАЦИЕЙ МЕРОПРИЯТИЙ ПО РАЗВИТИЮ БИЗНЕСА

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

• расширение ассортиментной линейки выпускаемой продукции по группам «Костюмы» и «Рубашки» с апреля 2018 г.;
• открытие нового розничного магазина компании в июле 2018 г., в котором будет реализовываться продукция всех ассортиментных групп;
• открытие филиала компании в другом регионе страны, где планируется реализация продукции групп «Свитеры» и «Костюмы»;
• увеличение с июля 2018 г. цены реализации по всем группам продукции, поскольку к этому периоду прогнозируется рост себестоимости ее выпуска за счет поднятия отпускных цен поставщиками сырья и услуг;
• проведение сезонных скидок на продукцию группы «Свитеры» в июне-августе и группы «Рубашки» в январе-марте 2018 г.

В цифровом выражении результаты этих мероприятий представлены в табл. 5.

Для корректного планирования выручки компании «Альфа» на 2018 г. все указанные в таблице показатели роста продаж продукции должны найти отражение в предварительном прогнозе объемов реализации.

ПРЕДВАРИТЕЛЬНЫЙ ПРОГНОЗ ПРОДАЖ НА ПРЕДСТОЯЩИЙ ГОД

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

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

• данных анализа за прошедший год;
• мероприятий по развитию бизнеса в планируемом году;

2) определить цену реализации единицы продукции на основе динамики за прошедший год и планов по изменению цены в планируемом году;

3) рассчитать сумму выручки в планируемом году.

Сделаем предварительный прогноз выручки на 2018 г. для компании «Альфа».

Шаг 1. По каждой из ассортиментных групп определяем помесячно количество реализуемой продукции в рамках существующего бизнеса. Для этого прогноз количества реализации продукции на 2018 г. (см. табл. 2) умножаем на долю продаж продукции расчетного месяца в общем прогнозе количества реализации продукции на 2018 г. (см. табл. 3).

Шаг 2. По каждой из групп определяем помесячно рост продаж под влиянием реализации мероприятий по развитию бизнеса.

Шаг 3. Рассчитываем по каждой группе цену реализации единицы продукции на основе динамики цен в 2017 г. (см. табл. 4).

Шаг 4. Производим корректировку цены реализации продукции с учетом влияния намеченных мероприятий по развитию бизнеса (см. табл. 5).

Шаг 5. Находим прогноз суммы выручки компании на 2018 г. путем сложения данных шага 1 и шага 2 с последующим их умножением на цену реализации из шага 4.

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

РАСЧЕТ ВЛИЯНИЯ РАЗЛИЧНЫХ ФАКТОРОВ НА ДИНАМИКУ ПРОДАЖ

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

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

Для компании «Альфа» актуальными являются два фактора:

1) сезонность спроса;

2) коэффициент эластичности.

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

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

К СВЕДЕНИЮ

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

Для продукции компании «Альфа» подобная статистика за три последних года показала, что коэффициент эластичности группы «Свитеры» равен 0,4. Для групп «Костюмы» и «Рубашки» этот коэффициент составил 0,2 и 0,3 соответственно.

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

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

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

Важная деталь: учет влияния коэффициента эластичности сделал прогноз выручки на 2018 г. еще более достоверным.

СОПОСТАВЛЕНИЕ СКОРРЕКТИРОВАННОГО ПРОГНОЗА С РЕСУРСНЫМИ ВОЗМОЖНОСТЯМИ КОМПАНИИ

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

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

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

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

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

Потребность в рабочих рассчитывают через умножение норматива трудозатрат на выпуск единицы продукции на прогнозируемое количество реализации единиц продукции в 2018 г. с последующим делением этого произведения на годовой фонд рабочего времени одного сотрудника (6 ч в день × 22 рабочих дня в месяце × 12 мес.).

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

П_пл = (Годовой объем продаж / 360 дн. × Норматив товарных запасов в днях реализации) / Норматив хранения количества продукции на 1 м² склада.

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

П_ф = Рост потребностей в закупках сырья на год / 360 дн. × Средний срок реализации продукции в днях,

Рост потребностей в закупках сырья = (Сумма реализации продукции по прогнозу – Сумма реализации продукции по факту прошлого года) × Доля сырья и материалов в сумме выручки от реализации.

В таблице 8 приведены расчеты по проверке достаточности ресурсов компании «Альфа» для выполнения плана продаж на 2018 г.

Выводы по данным табл. 8:

• компании не хватает рабочих (потребность — 6 чел.);
• обеспеченность складскими площадями близка к критическому состоянию;
• финансовые ресурсы у компании есть.

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

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

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

Процедура прогнозирования

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

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

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

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

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



2. Теперь нам нужно построить линию тренда. Делаем щелчок правой кнопкой мыши по любой из точек диаграммы. В активировавшемся контекстном меню останавливаем выбор на пункте «Добавить линию тренда».



3. Открывается окно форматирования линии тренда. В нем можно выбрать один из шести видов аппроксимации:
   • Линейная;
   • Логарифмическая;
   • Экспоненциальная;
   • Степенная;
   • Полиномиальная;
   • Линейная фильтрация.

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

   В блоке настроек «Прогноз» в поле «Вперед на» устанавливаем число «3,0», так как нам нужно составить прогноз на три года вперед. Кроме того, можно установить галочки около настроек «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации (R^2)». Последний показатель отображает качество линии тренда. После того, как настройки произведены, жмем на кнопку «Закрыть».



4. Линия тренда построена и по ней мы можем определить примерную величину прибыли через три года. Как видим, к тому времени она должна перевалить за 4500 тыс. рублей. Коэффициент R2, как уже было сказано выше, отображает качество линии тренда. В нашем случае величина R2 составляет 0,89. Чем выше коэффициент, тем выше достоверность линии. Максимальная величина его может быть равной 1. Принято считать, что при коэффициенте свыше 0,85 линия тренда является достоверной.



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

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

Урок: Как построить линию тренда в Excel

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

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

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

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

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

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

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

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

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

1. Выделяем незаполненную ячейку на листе, куда планируется выводить результат обработки. Жмем на кнопку «Вставить функцию».



2. Открывается Мастер функций. В категории «Статистические» выделяем наименование «ПРЕДСКАЗ», а затем щелкаем по кнопке «OK».



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

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

   Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.

   После того, как вся информация внесена, жмем на кнопку «OK».



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



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

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

Урок: Экстраполяция в Excel

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

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

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

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

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

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

1. Производим обозначение ячейки для вывода результата и запускаем Мастер функций обычным способом. В категории «Статистические» находим и выделяем наименование «ТЕНДЕНЦИЯ». Жмем на кнопку «OK».



2. Открывается окно аргументов оператора ТЕНДЕНЦИЯ. В поле «Известные значения y» уже описанным выше способом заносим координаты колонки «Прибыль предприятия». В поле «Известные значения x» вводим адрес столбца «Год». В поле «Новые значения x» заносим ссылку на ячейку, где находится номер года, на который нужно указать прогноз. В нашем случае это 2019 год. Поле «Константа» оставляем пустым. Щелкаем по кнопке «OK».



3. Оператор обрабатывает данные и выводит результат на экран. Как видим, сумма прогнозируемой прибыли на 2019 год, рассчитанная методом линейной зависимости, составит, как и при предыдущем методе расчета, 4637,8 тыс. рублей.

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

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

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

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

1. Выделяем ячейку вывода результата и уже привычным путем вызываем Мастер функций. В списке статистических операторов ищем пункт «РОСТ», выделяем его и щелкаем по кнопке «OK».



2. Происходит активация окна аргументов указанной выше функции. Вводим в поля этого окна данные полностью аналогично тому, как мы их вводили в окне аргументов оператора ТЕНДЕНЦИЯ. После того, как информация внесена, жмем на кнопку «OK».



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

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

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

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

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

1. Производим выделение ячейки, в которой будет производиться вычисление и запускаем Мастер функций. Выделяем наименование «ЛИНЕЙН» в категории «Статистические» и жмем на кнопку «OK».



2. В поле «Известные значения y», открывшегося окна аргументов, вводим координаты столбца «Прибыль предприятия». В поле «Известные значения x» вносим адрес колонки «Год». Остальные поля оставляем пустыми. Затем жмем на кнопку «OK».



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



4. Теперь нам предстоит выяснить величину прогнозируемой прибыли на 2019 год. Устанавливаем знак «=» в любую пустую ячейку на листе. Кликаем по ячейке, в которой содержится фактическая величина прибыли за последний изучаемый год (2016 г.). Ставим знак «+». Далее кликаем по ячейке, в которой содержится рассчитанный ранее линейный тренд. Ставим знак «*». Так как между последним годом изучаемого периода (2016 г.) и годом на который нужно сделать прогноз (2019 г.) лежит срок в три года, то устанавливаем в ячейке число «3». Чтобы произвести расчет кликаем по кнопке Enter.

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

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

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

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

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

1. В списке операторов Мастера функций выделяем наименование «ЛГРФПРИБЛ». Делаем щелчок по кнопке «OK».



2. Запускается окно аргументов. В нем вносим данные точно так, как это делали, применяя функцию ЛИНЕЙН. Щелкаем по кнопке «OK».



3. Результат экспоненциального тренда подсчитан и выведен в обозначенную ячейку.



4. Ставим знак «=» в пустую ячейку. Открываем скобки и выделяем ячейку, которая содержит значение выручки за последний фактический период. Ставим знак «*» и выделяем ячейку, содержащую экспоненциальный тренд. Ставим знак минус и снова кликаем по элементу, в котором находится величина выручки за последний период. Закрываем скобку и вбиваем символы «*3+» без кавычек. Снова кликаем по той же ячейке, которую выделяли в последний раз. Для проведения расчета жмем на кнопку Enter.

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

Урок: Другие статистические функции в Excel

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