Бесплатный фрагмент - Базовый курс по финансовому моделированию

Слова благодарности

Я посвящаю эту книгу памяти моего друга — Александра Кузнецова. Все средства, полученные от её продажи, были и будут направлены его жене и детям.

Хочу поблагодарить мою семью за безусловную поддержку при написании книги. Особенно свою любимую супругу Елену. Ее любовь и поддержка всегда придавали мне сил и стимул для преодоления новых вершин. Благодарю моих братьев, Андрея, Ивана и Михаила, за позитив и драйв, за «чувство локтя» в трудную минуту. Хочу выразить свое почтение и благодарность моим родителям, бабушке и деду, и особенно любимой тете Эмме за ее безмерный вклад в мое воспитание и заботу о нас.

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

моим учителям и руководителям:

Ирине Алексеевне Чернухиной за веру в меня и старт моей карьеры экономиста-финансиста,

Олегу Алексеевичу Горелову и Игорю Александровичу Кобзарю за мою трансформацию в «инвестиционщика», за самые глобальные и «креативные» проекты в её практике, а также за мудрые советы;

Михаилу Георгиевичу Быханову за его жизненный подход и пример поведения, к которому надо стремиться;

моим друзьям и коллегам:

по работе в «МОЭК» — Владимиру Пастухову, Лене Чижиковой, Евгению Бузоверову за вашу помощь в решении самых сложных задач и мой первый опыт руководства;

по работе в «Лидер» — Павлу Бесшапову, Юрию Шагинову, Сергею Окутину, Ирине Горюновой, Лине Соколовой и Алисе Митяевой за вашу поддержку в трудное время, за советы и великолепные диалоги в обеденных перерывах.

Спасибо всем вам, и пусть Бог вас хранит.

Предисловие

Идея написать данный курс появилась у меня после посещения семинара Эдварда Бодмера (Edward Bodmer). Это был очень интересный семинар на английском языке. До него я видел семинары, проводимые «Большой четверкой» на русском языке. Однако они не произвели на меня такого впечатления, потому что описывали конкретный пример из их практики. В них отсутствовали драйв и универсальность. Все было определено заранее. Нам оставалось только вбивать формулы в готовый шаблон. Эдвард действовал иначе. Он открывал новый, пустой файл и начинал моделировать…

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

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

Многие думают, что основная цель финансового моделирования — это документ, расчет или файл, который дает доступ к финансированию проекта. У меня есть финмодель, которая показывает высокую рентабельность инвестиций, значит, инвестор/банк даст мне деньги на реализацию проекта. Боюсь, я вас разочарую. Существует большой выбор программ-калькуляторов, пользователи которых вносят свои данные в «крупную клетку», а программа автоматически считает все показатели. Но в банке или фонде вам начинают задавать вопросы про ваше ведение бизнеса, про способы хеджирования рисков вашего бизнеса, про маркетинг и конкуренцию и т. п. В результате вы выходите от инвесторов с пустыми руками и горой вопросов без ответов. Придя домой, вы обнаруживаете, что программы-калькуляторы имеют ограниченный функционал, и вы не можете смоделировать те вопросы-проблемы, которые задали вам кредиторы-инвесторы. Особенно если ваш проект предполагает сложную структуру финансирования. Основная цель финансового моделирования — это формирование ведения и ответа на вопрос: стоит ли тратить ресурсы на реализацию проекта?

Есть общеизвестная фраза — «существуют три самых доходных бизнеса: торговля оружием, наркотиками и лекарствами». Хотите ими заняться? Попытайтесь сделать финансовую модель для любого из этих бизнесов. Первое, что придет вам в голову, — это «как мне преодолеть барьеры для входа в бизнес». Второе — «как после этого остаться в живых и на свободе». И только в третью очередь вы задумаетесь, сколько вы на этом заработаете и во что вам это обойдется. Это и есть формирование ведения.

Еще один пример о пользе моделирования. Предположим, ваш друг предлагает вам «сделку века». У него есть клиент, который готов купить товар с наценкой в 200%. Друг знает, где можно купить товар по низкой цене. Вся сделка должна пройти в течение месяца. Наверное, вы инвестируете в этот проект без сложных расчетов. При благополучном исходе вы хорошо заработаете. Но что, если клиент сможет заплатить только через полгода, а вам срочно потребовались деньги? Вот тут вам потребуются расчеты, а навыки финансового моделирования помогут вам в этом. Возможно, используя факторинг или договор цессии, вы сможете вернуть себе свои инвестиции (продав с дисконтом ваши права по сделке). Этот простой пример показывает, насколько рисковыми могут быть поспешные инвестиционные решения.

Уже на этапе сбора данных для своей финмодели вы сформируете видение вашего бизнеса. У нас появится понимание, нужен ли вам этот проект. Готовы ли вы нести его риски и тратить на него свое время. Это хорошее лекарство от избыточного оптимизма для многих предпринимателей. Результаты моделирования позволяют «приземлять высоко летающих» товарищей с наполеоновскими подходами (главное — ввязаться в бой, а там видно будет).

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

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

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

Итак, давайте начнем моделировать.

Важная информация!

Дорогие читатели, обратите внимание, что из-за большого количества рисунков в книге мне пришлось снизить их качество и разрешение. Многие читатели уже обращали на это внимание в своих письмах.

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

Подготовка книги Excel к работе

Создайте свой стиль

Мой опыт работы в Excel начался более 10 лет назад. Когда я создал свою первую таблицу за несколько минут, мне все показалось простым, легким и понятным. Одна ячейка складывается с другой. Внизу получается результат. Быстро и просто, и все-таки чего-то не хватает… Красоты и яркости, наглядности! Да, именно этого ей не хватало.

Простая таблица производит удручающее впечатление (особенно когда она занимает весь лист А4). Я тут же принялся заливать цветами названия столбцов и менять шрифты. В итоге получилось что-то похожее на вот такую таблицу.

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

Кроме того, всегда есть место человеческому фактору. Например, у вашего руководителя хороший вкус, и он предлагает другое оформление расчетов или изменение состава и порядка данных. При этом время на выполнение задания ограничено или его нет. Возможно, вы уже оказывались в подобной ситуации. Судорожно ругая всех и вся, вы начинаете форматирование, а руководство звонит каждые 10 минут и спрашивает: «Что ты там возишься? Давай быстрее…»

Благо для нас, начиная с версии Excel 2007, появился отличный инструмент «Стили ячеек». Он находится «Ленте» -> «Основные вкладки» -> «Главная».

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

Итак, создаем свой стиль. Нажмите на иконку «Стили ячеек». Во вплывающем окне выберите «Создать стиль ячейки…». В новом окне «Стиль» создайте свой стиль для каждого типа ячеек. Например, Заголовок 1 или ИТОГО.

Для наглядности можно сделать так. На отдельном листе создайте стили для всех типов ячеек, которые вы будете использовать в своих расчетах. Слева сделайте нужный вам формат ячеек, а справа напишите наименования формата. Потом выберите отформатированную ячейку и нажмите «Стили ячеек» -> «Создать стиль ячейки…» и окно «Стиль», в графе «Имя стиля» удалите «Стиль 1» и напишете новое имя для этого стиля. Например, «Заголовок 1».

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

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

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

Блуждая по просторам интернета, я нашел отличный сайт, посвященный финансовому моделированию — http://www.fimodo.com. На этом сайте выложен отличный макрос (приложение 1), помогающий быстро создать свой стиль. Вот ссылка на него: http://www.fimodo.com/2011/07/using-cell-styles-for-financial-modelling/.

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

Горячие клавиши в Excel

Я настоятельно всем рекомендую освоить и использовать горячие клавиши. Это в разы повышает скорость работы. Есть отличный сайт, он посвящен Excel — www.planetaexcel.ru. Я всем рекомендую. Его автор, Николай Павлов, собрал 57 лучших сочетаний клавиш в Excel. По этой ссылке вы сможете их скачать (http://www.planetaexcel.ru/techniques/11/126/).

Панель быстрого доступа

Панель быстрого доступа позволяет вам использовать сочетания клавиш Alt + цифры от 1 до 0. Вы можете запрограммировать 10 команд-кнопок, которые всегда будут под рукой. Ниже я приведу пять моих горячих клавиш. Почему только пять? Все просто: левой рукой я нажимаю на клавиатуре нужную мне комбинацию, а в правой руке я держу мышь и выбираю нужные мне ячейки.

Настроить панель быстрого доступа очень просто. Кликните на ней мышкой и выберите «Другие команды..» (см. рисунок 14).

Принципы финансового моделирования

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

Мои принципы финансового моделирования

— Доступность — финансовая модель должна быть доступной для понимания всех пользователей, независимо от их уровня владения Excel.

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

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

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

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

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

Вы можете изучить другие принципы на http://www.corality.com и http://www.fast-standard.org/.

Структура финансовой модели

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

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

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

Далее нужно выбрать структуру модели. Все модели можно условно разделить на два типа:

• вертикальные (пример — «Альт-Инвест»);

• горизонтальные (пример — Corality).

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

Горизонтальный тип финмодели предполагает множество расчетов. Для каждого вида расчета делается свой лист. Например, расчет кредитов идет на отдельном листе «Финансирование».

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

Я предпочитаю вертикальный тип финмоделей. Он позволяет быстро строить самые сложные модели и проводить их аудит. Используя инструмент «зависимости формул — влияющие/зависимые ячейки» (см. закладку «Формулы» на ленте), вы можете увидеть взаимосвязанные ячейки на одном листе. Для того чтобы повысить наглядность модели, я использую структуру и группировку строк/столбцов.

Типовая структура моих финансовых моделей представлена на рисунке 20.

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

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

Для более наглядного представления влияния изменения того или иного параметра создается отдельный лист — «Анализ чувствительности». В дальнейшем он поможет вам лучше понимать «узкие» моменты и слабые/сильные стороны проекта. Благодаря этому вы сможете подобрать оптимальные условия реализации проекта.

Как вариант можно объединить данные трех листов («Мастер сценариев», «Анализ чувствительности», «Резюме») в одном листе «Резюме». Это возможно, когда у нас мало меняющихся переменных и нет сложных взаимосвязей. Тогда вы повысите наглядность своей финмодели в разы. На одном листе сможете моделировать разные сценарии и видеть, как они влияют на показатели проекта.

Следующий важный момент — распределение исходных данных по времени. Как правило, на первоначальном этапе построения финмодели нет точных данных. Обычно есть годовые цифры или общие цифры (например, размер инвестиций — Х руб.). Поэтому, когда вам потребуется построить ФМ по кварталам или по месяцам, вы должны разнести общие цифры по соответствующему временному периоду. Для этого делается отдельный лист «Распределение по времени исходных данных (РИД)». Благодаря этому листу вы сможете быстро переходить от месячной отчетности к годовой и наоборот. А также это позволяет предусмотреть несколько временных периодов в вашей модели. Например, первые 5 лет все расчеты будут помесячные, последующие 20 лет — поквартальные или годовые.

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

В данном курсе на листе «Резюме» я покажу, как сделать «Мастер сценариев» и «Анализ чувствительности» на одном листе. В нем можно создать несколько вариантов сценариев проекта.

В следующих главах я научу вас, как создавать и заполнять все вышеуказанные листы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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