Планирование развития компании пищевой промышленности в Excel

Как разработать алгоритм построения прогноза развития бизнеса компании в файле Excel?

Как рассчитать объемы и суммы продаж на прогнозируемый период?

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

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

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

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

И конечно, план развития компании следует составлять с учетом отраслевой специфики деятельности компании.

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

Разрабатываем алгоритм построения прогноза развития компании в Excel

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

Мы разработаем прогноз развития на 2020 г. (в файле Excel) для предприятия пищевой промышленности, а именно — для комбината по выпуску напитков.

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

Выручка от реализации продукции = Количество декалитров продукции × Цена реализации 1 дкл.

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

Чистая выручка от продаж = Выручка от реализации продукции – Сумма акциза.

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

Валовой доход от продаж = Чистая выручка от продаж – Сырьевая себестоимость выпуска продукции.

Далее вносим в план развития показатели производственных затрат, которые делим на прямые затраты производства (расходы производственных подразделений) и общепроизводственные затраты (расходы вспомогательных производств и общепроизводственных подразделений).

К сведению

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

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

Исходя из вышесказанного в нашем варианте плана развития комбината по производству напитков доход от производства продукции определим по формуле:

Производственный доход = Валовой доход от продаж – (Прямые затраты производства + Общепроизводственные затраты).

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

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

Прибыль (убыток) от хозяйственной деятельности = Производственный доход – Коммерческие затраты – Административные затраты.

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

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

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

• Продажи.
• Сырьевые затраты.
• Прямые затраты.
• Общепроизводственные затраты.
• Коммерческие затраты.
• Административные затраты.
• План развития 2020.

Формируем план объемов и продаж на прогнозируемый период

Создание плана развития предприятия на 2020 г. начинаем с разработки плана продаж. Для этого на отдельном листе книги Excel создаем разделы:

• План продаж, дкл.

• Прогноз цен реализации 1 дкл.

• Прогноз продаж, руб.

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

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

• Пиво в фасовке кеги объемом 50 л, полиэтиленовые (ПЭТ) бутылки объемом 1,25 л и стеклянные бутылки объемом 0,5 л.
• Лимонады в фасовке ПЭТ-бутылки объемом 1,25 л и стеклянные бутылки объемом 0,5 л.
• Минеральная вода в фасовке ПЭТ-бутылки объемом 1,5 и 0,5 л.

Формирование плана реализации начинаем с заполнения раздела «Влияние коэффициента сезонности».

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

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

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

Объем продаж за месяц = Объем продаж за год × Коэффициент сезонности спроса за месяц.

Затем формируем данные раздела «Прогноз цен за 1 дкл: указываем планируемые цены реализации по каждому виду продукции и месяцу планируемого года. В завершение работы над планом продаж обеспечим автоматическое заполнение раздела «Прогноз продаж, руб.», прописав в каждой ячейке раздела простую формулу:

Сумма продаж за месяц = Объем продаж за месяц × Цена реализации 1 дкл продукции.

Полученный в итоге план реализации продукции комбината по производству напитков на 2020 г. представлен в табл. 1.

Формируем план производственных расходов на прогнозируемый период

Второй этап разработки плана развития — составление прогноза сырьевых затрат на выпуск продукции.