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

Исходные данные

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

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


[Закрыть]. Для коммунальных предприятий важен прогнозный уровень инфляции, потому что рост тарифа привязан к росту инфляции. Для нефтяных компаний – прогнозные цены на нефть и т. д.

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

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

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

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

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

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

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


[Закрыть]… Если осталась прибыль, то переходим дальше.

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

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

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

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

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

Открываем книгу 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. Стили в нашей финмодели

Мы можете воспользоваться файлом – приложением к курсу и посмотреть по шаговые действия44
  Чтобы получить файлы приложения к данному курсу, направьте мне письмо на [email protected].


[Закрыть].

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

Следующий шаг: определяем временной диапазон наших расчетов. Допустим, наш проект рассчитан на 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. Создаем временные ряды

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

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

Добавим красоты. В ячейку A1 вставим формулу названия листа. Теперь название листа будет автоматически отражаться в данной ячейке.

Формула 1. Название листа

Вставим две строчки ниже и в ячейках B2 и B3 напишем «Сценарий», «Наличие ошибок». Позже мы изменим их. Они будут показывать название выбранного «Сценария» и сообщать о наличии ошибок.

Теперь вы должны получить вот такой результат.

Рисунок 35. Добавляем фазы и графики реализации проекта

Сгруппируем временной ряд по строчкам. Выделяем строки 7—23. Нажимаем Shift + пробел – выделяем строки. Затем Shift + Alt + → (стрелка в право) – группируем строки 7—23. Потом делаем подгруппы. Добавим еще одну строчку между 7 и 8 строкой. В ячейке G8 напишем подзаголовок «Период». Далее группируем строки. Делаем три подгруппы. В результате должна получиться похожая таблица (см. рисунок 36).

Рисунок 36. Группировка строк

Кликая мышкой по цифрам 1, 2, 3, вы будете открывать нужную вам группу. Группируем строки (кликаем на цифру 1). Переходим к заполнению временных рядов.

Заполняем временной ряд

Для удобства сгруппируем диапазон «Формат ячеек» (строки 26:81).

Вводим «Временные параметры»: число дней в году (365 дней), месяцев в году (12 месяцев), кварталов в году (4 квартала), месяцев в квартале (3 месяца). Сделаем это в ячейках J84:J87. Применим стиль «Допущения». В столбце H – «Ед. Изм.» (единицы измерения) – введем «число» и применим стиль «ед. изм.».

Рисунок 37. Временные параметры

Используя «Диспетчер имен», присвоим соответствующие имена ячейкам J84:J87 («Лента» -> «Основные вкладки» -> «Формулы» -> «Диспетчер имен» -> «Создать»)55
  Для наглядности и улучшения понимания здесь и далее все имена диапазонов будут написаны на русском. На практике лучшие писать все имена диапазонов на английском языке или в латинской транскрипции. Это повысит совместимость вашего файла в разных версиях Excel.


[Закрыть].

Рисунок 38. Присвоение имен диапазону ячеек

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

Заполняем «Даты». Вводим в ячейки J90 – 01.01.2015, в J91 – 10. Далее в ячейке J92 произведем расчет количества кварталов в проекте по формуле ниже.

Формула 2. Расчет кварталов в проекте

Обратите внимание: в формуле используется «Имя Диапазона» – «Число_кварталов_в_году».

Продолжаем расчеты. Рассчитываем количество кварталов, месяцев, дней и дату окончания проекта. Присвоим ячейке имя – «Дата_конца_проекта».

Формула 3. Расчет кварталов, месяцев, дней и даты окончания проекта

Добавим названия для ячеек G92:G95: «Количество кварталов в проекте», «Количество месяцев в проекте», «Количество дней в проекте». Создадим соответствующие диапазоны имен. Группируем строки с 90 по 95.

Рисунок 39. Заполнение «Дат проекта»

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

В ячейки G98:G108 вводим текст: «Выбор количества временных периодов в модели», «Временной период для модели (основной)», «Дата начала основного периода», «Продолжительность периода», «Дата конца основного периода», «Временной период для модели (вспомогательный)», «Дата начала вспомогательного периода», «Продолжительность периода», «Дата конца вспомогательного периода».

Используем инструмент «Проверка данных» для создания выпадающего списка («Лента» -> «Основные вкладки» -> «Данные» -> «Работа с данными» -> «Проверка данных»). В открывшемся окне в разделе «Тип данных» выбираем вариант «Список» и указываем в разделе «Источник» цифры 1 и 2. Теперь вы сможете выбирать количество временных периодов в модели из выпадающего списка в ячейке J98. Наведите курсор на данную ячейку, и вы увидите стрелочку вниз. Нажмите на стрелочку и выберите нужное вам число

Аналогичную процедуру проводим для J100 и J106. Только в этот раз в разделе «Источник» напишите слова «месяц; квартал; полугодие; год».

Рисунок 40. Создание выпадающего списка – 1 и 2

Рисунок 41. Создание выпадающего списка – месяц, квартал, полугодие, год

Заполняем ячейки J100: J103. В ячейку J101 вводим имя – «Дата_начала_проекта». В ячейке J101 делаем формулу (см. ниже).

Формула 4. Определение продолжительности периода в зависимости от выбранного периода (год, квартал, месяц)66
  В данном курсе будут использованы «мегаформуры». Это противоречит принципу доступности, зато позволит улучшить наглядность и сократить число вспомогательных строк. Любую «мегаформулу» можно разделить на составные части и выполнить расчеты в нескольких ячейках.


[Закрыть]

В J103 вводим нижеследующую формулу.

Формула 5. Расчет конца основного временного периода

Создаем новые диапазоны имен (см. рисунок ниже).

Рисунок 42. Имена, используемые в формулах

Далее заполняем рабочую область P100:P103. Строим проекцию основного временного ряда. В ячейку O103 вводим формулу ниже. Это техническая ячейка (применяем стиль «Тех_вставка»).

Формула 6. Техническая ячейка

В ячейки P100:P103 вводим формулы ниже.

Формула 7. Рабочая область – основной временной период

Теперь выделяем ячейки с формулами P100:P103. Нажимаем Shift + Atl + -> (стрелка в право) – выделяем диапазон P100:EE103. Нажимаем Ctrl + R (заполняем ячейки вправо). Применяем стиль «Дата». Создаем контрольные ячейки K102 и K103 и вводим в них следующие формулы. После применяем стиль «Тревога».

Формула 8. Контрольные ячейки основного временного ряда

Переходим к созданию вспомогательного временного периода. Здесь практически все то же самое за исключением ячейки J108. В ней вы можете указывать любое значение. Поэтому у нее будет соответствующий стиль – «Допущение». Таким образом, основной временной период рассчитывается автоматически, а вспомогательный – так, как необходимо пользователю.

Далее заполняем рабочую область P106:P109. В ячейку O106 вводим формулу ниже. Это техническая ячейка.

Формула 9. Техническая ячейка

Вводим нижеследующие формулы в ячейки P106:P109.

Формула 10. Рабочая область – вспомогательный временной период

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

Формула 11. Контрольные ячейки вспомогательного временного ряда

Создаем две вспомогательные ячейки для построения вспомогательного временного периода – G111 и G112. Вводим в них формулы.

Формула 12. Вспомогательные ячейки для построения вспомогательного временного периода

Теперь переходим к заполнению рабочей области P5:EE6 (наш временной ряд). В ячейку O6 вводим формулу ниже. Эта техническая ячейка позже поможет нам при расчетах IRR и NPV.

Формула 13. Техническая ячейка

В ячейки P5:P6 вводим следующие формулы.

Формула 14. Временной ряд

В ячейку Q5 – другую формулу.

Формула 15. Ячейка Q5

Выделяем диапазон Q5:Q6. Нажимаем Shift + Atl + -> (стрелка вправо) – выделяем диапазон Q5:EE6. Нажимаем Ctrl + R (заполняем ячейки вправо). Применяем стиль «Дата». Проверяем нашу работу. Создаем формулы в ячейках K5 и K6. Применяем для них стиль «Тревога».

Формула 16. Контрольные ячейки K5 и K6

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

Рисунок 43. Готовые временные ряды

Далее. Раскрываем структуру – нажимаем на цифру «3» в левом верхнем углу.

Заполняем «Периоды». В ячейках P9:P14 вводим следующие формулы. Потом выделяем данные ячейки, применяем соответствующие стили и заполняем вправо до конца листа.

Формула 17. P9:P14

Обратите внимание, что некоторые формулы ссылаются на ячейки O11:O14. Они специально оставлены пустыми. Примените к этим ячейка стиль «Пустая ячейка».

Проверяем себя. Вводим формулы в контрольные ячейки K10, K12 и K14.

Формула 18. Контрольные ячейки K10, K12, K14

Важно: контрольные ячейки сейчас настроены под квартальный (основной) и помесячный (вспомогательный) период. Если вы измените параметры временных периодов, то возможно появление сообщения «Ошибка» в контрольных ячейках. Таким образом вы можете себя еще раз проверить. И если потребуется, то изменить соответствующие настройки для временных периодов по полугодиям или годам.

Рисунок 44. Завершение формирования временных рядов

Последний шаг. Создаем заголовок «Количество дней в периодах» – ячейка B114. Вводим текст в ячейки G115:G117: «Количество дней в периоде», «Количество дней в году», «Количество дней в квартале». Вводим нижеследующие формулы, выделяем диапазоны, применяем стиль и заполняем диапазон P115:EE117.

Формула 19. Количество дней в периодах – диапазон P115:EE117

Группируем строки A98:A112 – «Количество временных периодов», A115:A117 – «Количество дней в периодах». Переходим к фазам проекта.

Рисунок 45. Заполненные временные ряды

Разбиваем проект на фазы

В ячейке B119 вводим текст «Фазы проекта». В C120 – «Строительство». В ячейки G121:G126 вводим текст: «Дата начала строительства», «Срок строительства», «Количество кварталов в фазе», «Количество месяцев в фазе», «Количество дней в фазе», «Дата конца строительства».

Копируем ячейки J90:J97 и вставляем в ячейки J121: J126. В ячейке J121 вводим ссылку на дату начала проекта. Делаем допущение, что дата начала строительства совпадает с датой начала проекта. В ячейку G122 («Срок строительства») вводим наше допущение по продолжительности строительства – 3 года. Применяем соответствующие стили для ячеек. Создаем новый диапазон имен: «Дата начала строительства», «Срок строительства», «Дата конца строительства».

Рисунок 46. Создание сроков для фазы строительства

Разворачиваем группировку («цифра 3» в левом верхнем углу). В ячейку P17 вводим следующую формулу и заполняем строку вправо. Применяем стиль «Флаг».

Формула 20. Создаем флаг для фазы строительства

Далее применяем инструмент «Условное форматирование» («Лента» -> «Основные вкладки» -> «Главная» -> «Стили»). Выделяем ячейки P17:EE17. Кликаем левой клавишей мыши на иконку «Условное форматирование». В новом окне выбираем «Создать правило». Далее «Форматировать только ячейки, которые содержат». Внизу выбираем «Значение», «Равно». Вводим «=1». Ниже выбираем формат для наших ячеек. «Формат» -> «Шрифт» -> цвет шрифта выбираем желтый. Переходим во вкладку «Заливка» -> «Цвет фона» выбираем желтый. «Цвет узора» – ставим рыжий, «Узор» – наклонная линия.

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

Рисунок 47. Условное форматирование для фазы строительства (шаг 1)

Рисунок 48. Условное форматирование для фазы строительства (шаг 2)

Рисунок 49. Условное форматирование для фазы строительства (шаг 3)

Повторяем все вышеуказанные процедуры для фазы «Эксплуатация». При этом уточняем даты и сроки эксплуатации. В ячейках J129 и J130 вводим следующие формулы.

Рисунок 50. Условное форматирование для фазы строительства (шаг 4)

Формула 21. Срок на фазе эксплуатации

Создаем «флаги» фазы эксплуатации. В ячейку P18 вводим формулы. Применяем стиль «Флаг». Повторяем процедуру с условным форматированием, только цвет фона будет синий.

Формула 22. Создаем флаг на фазе эксплуатации

Рисунок 51. Условное форматирование для фазы эксплуатации

Осталось сделать «флаги» для «Графика финансирования». Пока у нас нет точных параметров получения и возврата финансирования, поэтому будем исходить из среднерыночных условий. Например, на практике максимальный срок банковского кредитования в России около 7 лет77
  Он может быть и больше. Все зависит от условий кредита, обеспечения и гарантий, процентной ставки и многого другого.


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

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

Повторим все операции по созданию фаз и «флагов» для акционерного финансирования и долга. При этом изменим цвета: для акционерного финансирования выберем фиолетовый, а для долга – зеленый.

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

Рисунок 52. Завершение создания фаз проекта

Временной ряд «Фактические данные» заполняем аналогично другим рядам. Только его продолжительность сделаем равной одному месяцу (для примера) и выберем красную заливку.

Теперь наведем красоту: сгруппируем все фазы, вставим единицы измерения, и самое главное, применим условное форматирование к ячейкам P5:EE6. Для этого их нужно будет выделить и применить нижеследующие условия форматирования ячеек. Выбираем из типов правил «Использовать формулу для определения форматируемых ячеек». Пишем формулу ниже. Выбираем формат – рыжую заливку и границу ячейки. Повторяем данную процедуру для фазы «Эксплуатация» и «Фактические данные», при этом меняем ссылки на флаги фаз и цвет заливки.

Формула 23. Условное форматирование временного ряда

Рисунок 53. Условное форматирование временного ряда (шаг 1)

Рисунок 54. Условное форматирование временного ряда (шаг 2)

Рисунок 55. Условное форматирование временного ряда (шаг 3)

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

Рисунок 56. Улучшаем группировку – новая область закрепления

Переходим к заполнению макроэкономических данных.