Базовый курс по финансовому моделированию. Пошаговая инструкция по созданию финансовой модели в Microsoft Excel

Сбор исходных данных

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

Всем известно, что прогнозирование – дело сложное. Количество прогнозов велико, качество сомнительное, а выводы порой противоречат друг другу. Поэтому лучше использовать один официальный государственный прогноз (например, от Минэкономразвития России, Минфина, ЦБ РФ) и пару коммерческих прогнозов (прогноз Всемирного банка (The World Bank) / прогнозы рейтинговых агентств/ прогнозы крупных коммерческих банков / прогнозы авторитетных фондовых аналитиков, брокерских или управляющих компаний). В приложении 2 приведены ссылки на источники макроэкономических данных. Желательно, чтобы источники были независимыми друг от друга. Таким образом, можно определить «среднюю температуру по больнице». А если у вас есть свое видение макроэкономики, тогда целесообразно его тоже включить в исходные данные.

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

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

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

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

Зачастую после того как вы соберете все доходы и расходы и вычтете одно из другого, необходимость в дальнейших расчетах отпадает сама собой. Да, вы уже догадались. Экономики у проекта нет. Все и так очевидно. Успокойтесь, это нормальное явление. Помните, только один из трех проектов, получивших финансирование, выходит на прибыль. Мой опыт говорит, чтобы найти один интересный проект, надо изучить 10 проектов. Далеко не факт, что и этот проект сможет привлечь стороннее финансирование.

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

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

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

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

Итак, исходные данные собраны. Приступаем к заполнению листа «Исходные данные».

Внешний вид листов – применяем свой стиль

Открываем книгу Excel. Переименовываем лист «Лист 2» в «Исходные данные». Выделяем столбцы A:F (6 столбцов). Уменьшаем их ширину. Выделяем столбец G и увеличиваем его ширину. Выделяем столбец H и изменяем его ширину – примерно 1/3 от ширины столбца G. Следующие четыре столбца I:L оставляете без изменений. Столбцы M:N сжимаем практически до нуля. У вас должен получится внешний вид листа примерно как на рисунке ниже.

Рисунок 22. Первоначальный лист «Лист 2» (до)

Рисунок 23. Лист «Исходные данные» (после)

Только что мы создали «рабочую зону» нашей финансовой модели:

столбцы A:F – заголовки;

столбец G – название статей;

столбец H – единицы измерения;

столбцы I:L – ячейки импорта/экспорта данных на лист, ошибки, расчеты;

столбец I – ячейки для импорта данных из других листов (входящие данные из других листов), ссылки на данные из других листов;

столбец J – ячейки для экспорта данных на другие листы (выходящие данные для других листов), данные для расчетов на других листах;

столбец K – ячейки для проверки расчетов, контрольные ячейки на наличие ошибок;

столбец L – свободная ячейка для любых расчетов;

столбцы M:N – резервные столбцы;

столбцы O и далее – место выполнения всех расчетов.

Возможно, такая структура покажется вам странной. Позже вы поймете, как она удобна.

Рисунок 24. Общая структура для всех листов

Теперь, используя «Стили ячеек», придадим лоск и красоту нашим таблицам. Создаем свой стиль для финмодели и применяем его к нашему листу.

Рисунок 25. Создаем свой стиль

Рисунок 26. Применяем стиль к ячейкам

Рисунок 27.1. Применяем стиль к ячейкам (продолжение)

Рисунок 27.2. Применяем стиль к ячейкам (продолжение)

Рисунок 28. Стили в нашей финмодели

Мы можете воспользоваться файлом – приложением к курсу и посмотреть по шаговые действия3.

Создаем временные ряды

Следующий шаг: определяем временной диапазон наших расчетов. Допустим, наш проект рассчитан на 10 лет. Из них 3 года – строительство, 7 лет – эксплуатация. Таким образом, максимальное количество столбцов будет равно 120 (10 лет Х 12 мес.). Вы спросите, зачем так много? Отвечаю, как правило, финмодель – стратегический инструмент, позволяющий заглянуть в «светлое» будущее, но иногда финмодель превращается в тактический инструмент. Своего рода бюджет на 10 лет. Вот для таких случаев и предусматривается такой запас столбцов. По опыту могу сказать, что такая картина часто встречается на эксплуатационной фазе проекта. Изначально модель была годовая, а потом жизнь потребовала детализации.

Итак, отсчитываем 120 столбцов, начиная с столбца P и заканчивая столбцом EE. Просто протягиваем (заполняем) ячейку P11 вправо до ячейки EF11. Потом скрываем не нужные нам столбцы. Для этого выделяем все столбцы, начиная с EF до конца книги. Наводим курсор на столбец EF и кликаем по нему левой клавишей мыши (или нажимаем Crtl + пробел) – выделяется столбец EF. После чего нажимаем клавиши Shift + Сtrl + → (стрелка в право) – выделяются все столбцы до конца книги. Нажимаем на ленте «Очистить все» («Лента» -> «Основные вкладки» -> «Главная» -> «Редактирование» -> «Очистить» -> «Очистить все»). Тем самым удаляем все ненужные данные и сокращаем размер файла. Далее нажимаем Shift + Alt + → (стрелка в право) – группируем пустые столбцы. Теперь они не будут участвовать в расчетах. Альтернативный вариант группировки – это скрыть ненужные столбцы. Выделить их и нажать правую клавишу мыши, потом выбрать команду «Скрыть». Закрепляем область начиная с P12. Это простое упражнение сократит огромное количество времени при заполнении нашей рабочей зоны.

Рисунок 29. Создание рабочей зоны. Шаг 1: заполняем 120 столбцов

Рисунок 30. Шаг 2: Выделяем пустые столбцы до конца листа и группируем их.

Рисунок 31. Шаг 3: закрепляем область

Рисунок 32. Шаг 4: окончание – до группирования столбцов

Рисунок 33. Шаг 4: окончание – после группирования столбцов

Теперь создаем временные ряды. Заполняем ячейки в столбцы G. В ячейки G2:G9 вводим: «Дата начала периода», «Дата конца периода», «Год», «Порядковый номер года с начала проекта», «Квартал», «Порядковый номер квартала с начала проекта», «Месяц», «Порядковый номер месяц с начала проекта».

Рисунок 34. Создаем временные ряды

Добавляем новые строки для новых данных – фаз проекта: «Строительство», «Эксплуатация». График финансирования: «Акционерное финансирование», «Долг», и график отображения фактических данных «Факт».