Примеры из книги:
9 бесплатных уроков

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

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

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

Для удобства сгруппируем диапазон «Формат ячеек» (строки 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.

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

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

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

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

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

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

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

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

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

Создаем новый заголовок — «Макроэкономические данные» — в ячейке B161 и подзаголовок в ячейке C162. Вводим тест в ячейках G163:G170: «Период», «МЭР», «Всемирный банк», «Консенсус-прогноз аналитиков», «Собственный прогноз — фиксированный рост», «Собственный прогноз — снижение темпов роста», «с», «на Х% в год».

Вставляем ссылки на источники прогноза. Открываем «Создать примечания» («Лента» -> «Основные вкладки» -> «Рецензирование» -> «Создать примечания») или нажимаем клавиши Shift + F2. В примечание пишем источник. Например: Прогноз социально-экономического развития Российской Федерации на 2015 г. и на плановый период 2016 и 2017 гг.

Теперь заполняем ячейки P162:Y163 (Период) значениями по годам 2015 —2024 гг. Применяем стиль «Таблица». Ниже вставляем данные из прогнозов. Применяем стиль «Тех. вставка».

Как вы заметили, сроки прогнозов ограничиваются 3 годами. Таким образом, нам надо самим сделать предположение об уровне инфляции в 2018—2024 гг.

Например, начиная с 2018 г. уровень инфляции будет соответствовать последнему прогнозному году или снижаться от него на Х% процентов в год.

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

Заполняем ячейки P167:Y167, делаем в них ссылки на ячейку J167, т. е. наш прогноз о 10% ежегодной инфляции. В ячейках P168:Y168 делаем расчет инфляции с постепенным ежегодным снижением на 0,2%.

В ячейке J172 создаем выпадающий список для выбора варианта прогноза и экспорта его данных в другие листы. В этот раз в разделе «Источник» указываем ссылки на ячейки $G$164:$G$168. В ячейку P172 вводим нижеследующую формулу и протягиваем ее до Y172.

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

Для наглядности модели сделаем допущение, что индексация доходов и расходов будет разной. Индексация доходов будет производиться по прогнозу МЭР, а расходов — по прогнозу Всемирного банка.

Создаем новый заголовок «Индексация» в ячейке B174. В ячейке C175 напишем «Доходы». В G175 и G176 введем текст: «Период» и «Выбор варианта индексации доходов». Скопируем ячейку J172 и вставим ее значение в J176. Выберем из списка «МЭР». В соседних ячейках справа напишем единицы измерения: «год» и «% в год». Применим соответствующий стиль «Ед. изм.». Скопируем заголовки для периодов из ячеек P163:Y163 и вставим их в ячейки P175:Y175. В ячейках P176: Y176 вводим следующую формулу.

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

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

Предположим, что для расчетов выручки нам понадобится объем реализации и цена. Сделаем допущение, что максимальная производственная мощность — 1000 тон продукции в год. В 1-й год эксплуатации объем реализации будет 50% от максимального уровня, 2-й год — 80%, 3-й и далее — 90%. Прогнозная цена — 10 руб. за тонну.

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

Тут есть один трюк. Введите цифры 1, 2, 3 в ячейки G185:G187. Теперь нажмите Ctrl +1 — Формат ячеек. Выберите «все форматы», в «тип:» введите текст «0\ «год эксплуатации»». В результате внешний вид ячеек изменится, но для расчетов Excel эти ячейки будут по-прежнему цифрами. Чтобы проверить, сложите их с пустой ячейкой. Такое форматирование ячеек очень удобно, когда у вас много физических единиц измерения.

Вводим формулы в ячейки P183:Y183.

В ячейку P184 вводим формулу ниже и заполняем ее вправо до столбца Y (Y184).

Далее определяем плановую мощность. В ячейки P188:Y188 вводим следующую формулу.

Посчитаем среднюю загрузку в ячейке J188. Разделим O188/O183.
Переходим к цене. В ячейке P189 вводим формулу ниже и протягиваем ее до столбца Y (Y189).

Теперь определим годовую выручку. Перемножим в ячейке P190 ячейки P188*P189. Заполним вправо до столбца Y.

Сделаем расчет годовых расходов на производство. Введем следующие формулы в ячейках P194:Y200.

Применим ко всем ячейкам соответствующие стили, в т. ч. «Л_ИТОГО» и «Л_Операция» (линия «ИТОГО» и линия «результат выполнения операции»). В столбце O выполним суммирование соответствующих строк 188, 190, 194, 200.

Предположим, что наши инвестиции в проект составят 10 000 руб. с НДС. Ставка НДС равна 18%. Инвестиции распределены на 3 года — по 1/3 в год. Создадим в диспетчере имен имя «НДС», где в диапазоне укажем значение =18%.
Заполняем ячейки B203:J213, как указано на рисунке ниже.

В ячейку P205:Y205 вводим формулу, указанную ниже.

В ячейках P206:Y206 рассчитаем НДС с инвестиций. Для этого введем следующую формулу.

В строке ниже (P207) просуммируем инвестиции и НДС. Применим соответствующие стили.

Распределим инвестиции с учетом графика финансирования. В ячейках P209:Y211 введем следующие формулы.

В ячейке P212 просуммируем значение ячеек P209:P211. Далее в P213 перемножим значение ячеек P207*P212. Выделим только что заполненные ячейки и заполним ими столбцы вправо до столбца Y.

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

Переходим к структуре финансирования. Предположим, что акционерные средства составят 30% от всех инвестиций с НДС. Заемные средства — 70%. Процентная ставка по ним будет равна 15%. Срок кредитования — 7 лет. На период строительства действует мораторий на выплату процентов и погашение кредита. Проценты на фазе строительства капитализируются.

Как вы уже заметили, акционерных средств не хватит в 1-й год (3000 руб. источников против 3333,33 руб. инвестиций). Это сделано специально, чтобы вы обратили внимание еще раз на фазы проекта. В последний день 2015 г. фаза акционерного финансирования и фаза долга пересекаются. Таким образом, в одном периоде будет два источника финансирования.

Заполняем ячейки B215:J231, как указано на рисунке ниже.

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

Для этого в ячейку P227 вводим ссылку P213 (инвестиции по годам), т. к. инвестиции в строительство — это отток денег, то они идут со знаком «минус». В ячейки (P228) ниже вводим формулу.

Далее рассчитываем дефицит средств. Складываем инвестиции и акционерное финансирование, получаем дефицит. Рассчитываем график заемного финансирования. Вводим следующую формулу в ячейку P230.

Теперь проверяем баланс. Складываем дефицит и заемное финансирование. Выделяем заполненные ячейки и протягиваем вправо до столбца Y. Применяем соответствующие стили ячеек.
В контрольном столбце К делаем проверку. Вводим формулы в ячейки K227, K228, K230.

В ячейку K231 вводим другую формулу.

В итоге вы должны получить следующую картинку.

В прошлой главе мы использовали «Диспетчер имен» для введения ставки НДС. Это очень удобный инструмент, он позволяет мгновенно изменить ставку во всех расчетах. Альтернативным вариантом ввода исходных данных для расчетов налогов может быть создание отдельных ячеек. В них указываются значения ставок налогов. Недостатком данного метода является большая трудоемкость построения расчетов в финмодели.

Согласитесь, гораздо проще написать в строке формул имя (например: =НДС), чем вводить ссылку на ячейку (например: =«Исходные данные (Шаг 7)»! J232). Поэтому в листе «Исходные данные» мы создадим ячейки с налоговыми ставками и создадим в «Диспетчере имен» соответствующие имена (см. рисунок ниже).»

Обратите внимание: у нас изменилась формула для НДС, раньше там было значение 18%, теперь ссылка на ячейку «Исходные данные (Шаг 7)»! $J$234. Мы изменили формулу только в одном месте и сохранили все другие формулы без изменения.

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

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

Надеюсь, мне удалось вас убедить в важности данного показателя.

Предположим, нет у нас достоверных данных по потребности в оборотном капитале. В этом случае вы можете использовать стандартное допущение, что задержка платежей будет равна 1 месяцу (30 дням) или 1/12 от годовой выручки.

Для нашей модели мы создадим упрощенную инфраструктуру. Мы не будем детализировать задолженность по контрагентам (поставщики, персонал и прочее). Ограничимся крупными цифрами. Сделаем допущение, что период оборачиваемости дебиторской задолженности равен 60 дням, а кредиторской — 30 дням.

Создадим соответствующие ячейки, как на рисунке ниже.

Поздравляю, вы изучили 9-ть первых уроков. Теперь вы научились заполнять лист «Исходные данные». Это первый лист финансовой модели. Он очень важен. От того как и какие данные вы внесете в этот лист, будет зависеть качество ваших расчетов.

Помните важный принцип GIGO (англ. Garbage In, Garbage Out, «Мусор на входе — мусор на выходе»). Поэтому постарайтесь собрать качественные исходные данные, тогда принцип GIGO трансформируется в Good In, Good Out.

Продолжить свое обучение вы можете купив книгу А. В. Финогенова «Базовый курс по финансовому моделированию. Пошаговая инструкция по созданию финансовой модели в Microsoft Excel» или записать к нам на курсы по финансовому моделированию.