Планируем развитие компании на следующий год с помощью MS Excel

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

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

Как достоверно рассчитать себестоимость реализации продукции на предстоящий год?

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

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

Что включить в годовой финансовый план развития бизнеса?

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

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

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

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

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

• выбрать/разработать методику планирования развития бизнеса компании;
• выбрать и утвердить ключевые показатели развития бизнеса, которые будут применяться в расчетной модели.

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

Итак, этапы планирования:

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

2. Планируем производство продукции: сначала рассчитываем натуральные показатели выпуска продукции, а затем — производственную себестоимость выпуска продукции.

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

4. Рассчитываем накладные расходы, к которым мы отнесем коммерческие м управленческие затраты.

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

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

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

К сведению

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

От теории планирования перейдем к построению расчетной модели в Excel. В качестве примера возьмем компанию «Альфа», руководитель экономической службы которой должен спрогнозировать развитие бизнеса компании на 2019 г. Работу по построению модели он начал с создания на листе «Стратегия» таблицы с данными о результатах 2018 г. и ключевых целях на 2019 г. (табл. 1).

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

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

Теперь на основе этих данных можно составлять план продаж.

Расчет производственного и сбытового планов по продукции компании

План продаж на 2019 г. строится на отдельном листе «Продажи» и включает три блока:

1. Объемы реализации в натуральных показателях.

2. Средняя цена реализации единицы продукции.

3. Сумма реализации продукции.

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

Во втором блоке прогнозируем цены реализации продукции в 2019 г. Для этого принимаем расчетную цену 2018 г. за базисную и прибавляем к ней прогноз динамики цен в 2019 г. (динамику цен см. в табл. 1).

Данные третьего блока в модели рассчитываются автоматически путем умножения прогнозного количества реализуемой продукции на прогнозную цену реализации единицы продукции в 2019 г. (табл. 2).

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

План производства = План продаж + Нормативный запас ГП на конец периода – Нормативный запас ГП на начало периода.

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

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

Расчет сумм операционных расходов компании, необходимых для выполнения планов производства и реализации продукции

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

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

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

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

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

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

Ниже укажем стоимость одного человеко-часа по каждой группе продукции. Умножив этот показатель на трудозатраты, получим прогноз расходов на оплату труда (табл. 3.3).

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

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

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

Результаты расчетов для компании «Альфа» — в табл. 4.1.

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

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

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

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

По тем статьям, которые показали динамику роста к концу отчетного года, предусматриваем незначительное увеличение на 2019 г. (на 2 % за квартал). Итоги расчетов — в табл. 4.3.

Формирование финансового плана результатов деятельности компании в следующем году

По итогам всех расчетов и прогнозов руководителю экономической службы компании «Альфа» остается только построить прогноз конечного финансового результата развития бизнеса в 2019 г. Для этого необходимо создать новый лист — «Финрез» и разместить на нем таблицу с расчетом прогнозной величины прибыли по итогам 2019 г. (табл. 5).

В таблицу будут выведены данные о таких показателях, как:

• сумма выручки от реализации продукции (из табл. 2);
• сумма себестоимости реализованной продукции (данные табл. 3.2, 3.3 и 4.1);
• валовой доход компании (сумма выручки – себестоимость реализации);
• коммерческие расходы (из табл. 4.2);
• управленческие расходы (из табл. 4.3);
• сумма операционных затрат (себестоимость + коммерческие расходы + управленческие расходы);
• прибыль/убыток от реализации продукции (валовой доход – коммерческие затраты – управленческие затраты);
• проценты к получению и проценты к уплате;
• прибыль/убыток от хозяйственной деятельности (прибыль/убыток от реализации + проценты к получению – проценты к уплате);
• внереализационные доходы и расходы;
• прибыль/убыток года (прибыль/убыток от хозяйственной деятельности + внереализационные доходы – внереализационные расходы);
• налог на прибыль;
• чистая прибыль компании.