Главбух как финдир. Модели для планирования развития компании в Excel

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

Какие преимущества предоставляет пользователю планирование в Excel по сравнению с использованием специализированного ПО?

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

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

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

Независимо от сферы деятельности компании и планов развития в модели должны быть отражены:

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

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

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

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

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

К сведению

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

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

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

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

Предположим, руководитель экономической службы торговой компании «Волга» должен спланировать развитие бизнеса компании в 2018 г. Для этого решили построить расчетную модель в табличном редакторе Excel.

Шаг 1. Ставим цели развития компании

Создаем таблицу для постановки основных целей развития:

в строках даем перечень целевых показателей;
в столбцах указываем значения этих показателей за истекший отчетный год (2017-й) и цели развития на прогнозируемый 2018 г.

Пользователь вносит в таблицу данные о фактических результатах работы за прошлый год — столбец «Факт 2017» и прогнозах на 2018 г. — столбец «Цель 2018 (выделены зеленым цветом), остальные показатели рассчитываются автоматически.

В итоговом виде расчет целей развития компании «Волга» на 2018 г. представлен в табл. 1.

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

Так, объем реализации планируется увеличить на 20 %, рентабельность продаж — почти на 5 %, а рентабельность самого бизнеса компании должна вырасти с 3,4 % до 8,5 %, т. е. более чем в два раза.

Оценим достижимость этих целей. Для этого проанализируем основные показатели табл. 1:

1. Нарастить объемы продаж планируется, увеличив реализацию товарной группы 1 на 10 %, товарной группы 2 — на 20 %.

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

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

2. Уровень себестоимости продаж по товарным группам 1 и 2 на 2018 г. планируется понизить по сравнению с 2017 г. на 5 %. Обоснование — получена дополнительная скидка от поставщика (зафиксирована в договоре поставки на 2018 г.).

3. Операционные расходы вносятся в таблицу на основании уровня переменных затрат (коммерческих и логистических) к объему продаж и сумме постоянных расходов (управленческих), прогнозируемого на 2018 г.

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

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

Шаг 2. Составляем маркетинговый план

На втором листе нашей расчетной модели — маркетинговый план, в котором будет обоснован прогноз увеличения продаж в 2018 г. В плане необходимо детализировать прогноз реализации товаров на листе «Цели» в количественном измерении по каналам сбыта, чтобы убедиться в корректности целевого показателя.

Направления маркетинговой стратегии развития компании «Волга»:

Сохранить в 2018 г. положительную динамику продаж по товарным группам 1 и 2 на уровне роста, достигнутого в 2017 г.
Увеличить продажи по товарной группе 2 за счет введения в ассортиментную линейку новой номенклатуры товаров.
Начать реализацию ассортимента товаров по новой товарной группе 3.
Открыть со второго квартала 2018 г. новый розничный магазин.
Открыть с третьего квартала 2018 г. представительство компании в соседнем регионе и начать там реализацию товаров из товарных групп 1 и 3.

Маркетинговый план компании «Волга» на 2018 г. (лист «Маркетинг» в расчетной модели) — в табл. 2.

По строке «По факту динамики» отражен прогноз роста количества реализуемого ассортимента по первой (+2 %) и второй товарной группе (+10 %), основанный на фактической динамике роста в 2017 г.

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

По строке «За счет открытия нового магазина» отражен плановый товарооборот магазина, который начнет работать со второго квартала 2018 г.

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

В целом маркетинговая детализация прогноза реализации количества товара на 2018 г. подтверждает достоверность годовой цели.

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

Шаг 3. Прогнозируем инвестиции

На третьем листе нашей расчетной модели располагается прогноз инвестиций, который детализирован поквартально и состоит из трех разделов:

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

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

Потребность в привлечении кредитов рассчитывается как разность между общей суммой инвестиций в квартал и суммой прибыли компании из листа «Финплан».

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

Прогноз инвестиций компании «Волга» на 2018 г. — в табл. 3.

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

Шаг 4. Составляем план продаж

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

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

Прогноз продаж 2018 г. компании «Волга» — в табл. 4.

Шаг 5. Составляем прогноз операционных расходов

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

Прогноз операционных расходов компании «Волга» на 2018 г. представлен в табл. 5.

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

Шаг 6. Финансовый план

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

В таблице 6 представлен финансовый план развития компании «Волга» на 2018 г., который не только соответствует годовым целям развития (лист «Цели»), но и определяет параметры будущих бюджетов доходов и расходов на каждый квартал нового года.

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

Статья опубликована в журнале «Справочник экономиста»