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

Копируем ячейки J90:J97 и вставляем в ячейки J121: J126. В ячейке J121 вводим ссылку на дату начала проекта. Делаем допущение, что дата начала строительства совпадает с датой начала проекта. В ячейку G122 («Срок строительства») вводим наше допущение по продолжительности строительства — 3 года. Применяем соответствующие стили для ячеек. Создаем новый диапазон имен: «Дата начала строительства», «Срок строительства», «Дата конца строительства».
Рисунок № 1. Создание сроков для фазы строительства
Разворачиваем группировку («цифра 3» в левом верхнем углу). В ячейку P17 вводим следующую формулу и заполняем строку вправо. Применяем стиль «Флаг».
Формула № 1. Создаем флаг для фазы строительства
Далее применяем инструмент «Условное форматирование» («Лента» -> «Основные вкладки» -> «Главная» -> «Стили»). Выделяем ячейки P17:EE17. Кликаем левой клавишей мыши на иконку «Условное форматирование». В новом окне выбираем «Создать правило». Далее «Форматировать только ячейки, которые содержат». Внизу выбираем «Значение», «Равно». Вводим «=1». Ниже выбираем формат для наших ячеек. «Формат» -> «Шрифт» -> цвет шрифта выбираем желтый. Переходим во вкладку «Заливка» -> «Цвет фона» выбираем желтый. «Цвет узора» — ставим рыжий, «Узор» — наклонная линия.
Теперь вы можете наглядно увидеть, где начинается и заканчивается фаза строительства.
Рисунок № 2. Условное форматирование для фазы строительства
Повторяем все вышеуказанные процедуры для фазы «Эксплуатация». При этом уточняем даты и сроки эксплуатации. В ячейках J129 и J130 вводим следующие формулы.
Формула № 2. Срок на фазе эксплуатации
Создаем «флаги» фазы эксплуатации. В ячейку P18 вводим формулы. Применяем стиль «Флаг». Повторяем процедуру с условным форматированием, только цвет фона будет синий.
Формула № 3. Создаем флаг на фазе эксплуатации
Рисунок № 3. Условное форматирование для фазы эксплуатации
Осталось сделать «флаги» для «Графика финансирования». Пока у нас нет точных параметров получения и возврата финансирования, поэтому будем исходить из среднерыночных условий. Например, сделаем допущение, что в первый год проекта финансирование будет за счет средств акционеров, а во второй и далее — заемных средств. Соответственно, срок возврата долга — 7 лет с момента их получения.

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

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

Теперь наведем красоту: сгруппируем все фазы, вставим единицы измерения, и самое главное, применим условное форматирование к ячейкам P5:EE6. Для этого их нужно будет выделить и применить нижеследующие условия форматирования ячеек. Выбираем из типов правил «Использовать формулу для определения форматируемых ячеек». Пишем формулу ниже. Выбираем формат — рыжую заливку и границу ячейки. Повторяем данную процедуру для фазы «Эксплуатация» и «Фактические данные», при этом меняем ссылки на флаги фаз и цвет заливки.
Формула № 4. Условное форматирование временного ряда
Рисунок № 5. Условное форматирование временного ряда
Теперь даты временного ряд будут окрашены в цвета соответствующей фазы. Снимем закрепление области и вставим дополнительную строку между «Фактическими данными» и «Форматом ячеек». Новую строку введем из группировки. Закрепим новую область – ячейка P26.
Рисунок № 6. Улучшаем группировку — новая область закрепления
Для удобства и экономии времени, вы можете воспользоваться файлом–приложением к книге А. В. Финогенова «Базовый курс по финансовому моделированию. Пошаговая инструкция по созданию финансовой модели в Microsoft Excel» и посмотреть пошаговые действия в Excel.

Для этого купите книгу и напишите письмо на afinmodels@icloud.com.
Made on
Tilda