Урок 3
Заполнение временных рядов
Для удобства сгруппируем диапазон «Формат ячеек» (строки 26:81).
Вводим «Временные параметры»: число дней в году (365 дней), месяцев в году (12 месяцев), кварталов в году (4 квартала), месяцев в квартале (3 месяца). Сделаем это в ячейках J84:J87. Применим стиль «Допущения». В столбце H — «Ед. Изм.» (единицы измерения) — введем «число» и применим стиль «ед. изм.».
Рисунок № 1. Временные параметры
Используя «Диспетчер имен», присвоим соответствующие имена ячейкам J84:J87 («Лента» -> «Основные вкладки» -> «Формулы» -> «Диспетчер имен» -> «Создать»)
Рисунок № 2. Присвоение имен диапазону ячеек
Теперь определяем даты проекта: даты начала и конца проекта, продолжительность каждой фазы. Предположим, что старт проекта будет 1 января 2015 г. Срок проекта — 10 лет.
Заполняем «Даты». Вводим в ячейки J90 — 01.01.2015, в J91 — 10. Далее в ячейке J92 произведем расчет количества кварталов в проекте по формуле ниже.
Формула № 1. Расчет кварталов в проекте
Обратите внимание:
в формуле используется «Имя Диапазона» — «Число_кварталов_в_году».

Продолжаем расчеты. Рассчитываем количество кварталов, месяцев, дней и дату окончания проекта. Присвоим ячейке имя — «Дата_конца_проекта»
Формула № 2. Расчет кварталов, месяцев, дней и даты окончания проекта
Добавим названия для ячеек G92:G95: «Количество кварталов в проекте», «Количество месяцев в проекте», «Количество дней в проекте». Создадим соответствующие диапазоны имен. Группируем строки с 90 по 95.
Рисунок № 3. Заполнение «Дат проекта»
Переходим к «Количеству временных периодов» в финмодели. Сразу скажу, что оно может быть любым. Я обычно использую два — месяцы и кварталы. Расчет по месяцам лучше использовать до момента выхода проекта на полную мощность или до конца этапа строительства. На фазе эксплуатации лучше использовать кварталы, т. к. большинство налоговых выплат в России производится поквартально.

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

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

Аналогичную процедуру проводим для J100 и J106. Только в этот раз в разделе «Источник» напишите слова «месяц; квартал; полугодие; год».
Рисунок № 4. «Создание выпадающего списка — 1 и 2»
Рисунок № 5.«Создание выпадающего списка — месяц, квартал, полугодие, год»
Формула № 3. Определение продолжительности периода в зависимости от выбранного периода (год, квартал, месяц)
В J103 вводим нижеследующую формулу.
Формула № 4. Определение продолжительности периода в зависимости от выбранного периода (год, квартал, месяц)
Создаем новые диапазоны имен (см. рисунок ниже).
Рисунок № 6. Имена, используемые в формулах
Далее заполняем рабочую область P100:P103. Строим проекцию основного временного ряда. В ячейку O103 вводим формулу ниже. Это техническая ячейка (применяем стиль «Тех_вставка»).
Формула № 5. Техническая ячейка
В ячейки P100:P103 вводим формулы ниже.
Формула № 6. Рабочая область – основной временной период
Теперь выделяем ячейки с формулами P100:P103. Нажимаем Shift + Atl + -> (стрелка в право) — выделяем диапазон P100:EE103. Нажимаем Ctrl + R (заполняем ячейки вправо). Применяем стиль «Дата». Создаем контрольные ячейки K102 и K103 и вводим в них следующие формулы. После применяем стиль «Тревога».
Формула № 7. Контрольные ячейки основного временного ряда
Переходим к созданию вспомогательного временного периода. Здесь практически все то же самое за исключением ячейки J108. В ней вы можете указывать любое значение. Поэтому у нее будет соответствующий стиль – «Допущение». Таким образом, основной временной период рассчитывается автоматически, а вспомогательный – так, как необходимо пользователю.

Далее заполняем рабочую область P106:P109. В ячейку O106 вводим формулу ниже. Это техническая ячейка.
Формула № 8. Техническая ячейка
Вводим нижеследующие формулы в ячейки P106:P109.
Формула № 9. Рабочая область – вспомогательный временной период
Повторяем все процедуры по форматированию и заполнению ячеек, описанные выше. Создаем контрольные ячейки K108 и K109 и вводим в них следующие формулы. Вводим нижеследующие формулы в ячейки P106:P109.
Формула № 10. Контрольные ячейки вспомогательного временного ряда
Создаем две вспомогательные ячейки для построения вспомогательного временного периода – G111 и G112. Вводим в них формулы.
Формула № 11. Вспомогательные ячейки для построения вспомогательного временного периода.
Теперь переходим к заполнению рабочей области P5:EE6 (наш временной ряд).
В ячейку O6 вводим формулу ниже. Эта техническая ячейка позже поможет нам при расчетах IRR и NPV.
Формула № 12. Техническая ячейка
В ячейки P5:P6 вводим следующие формулы.
Формула № 13. Временной ряд
В ячейку Q5 – другую формулу
Формула № 14. Ячейка Q5
Выделяем диапазон Q5:Q6. Нажимаем Shift + Atl + -> (стрелка вправо) — выделяем диапазон Q5:EE6. Нажимаем Ctrl + R (заполняем ячейки вправо). Применяем стиль «Дата». Проверяем нашу работу. Создаем формулы в ячейках K5 и K6. Применяем для них стиль «Тревога».
Формула № 15. Контрольные ячейки K5 и K6
Поздравляю, мы закончили с созданием временного ряда. У вас должна получиться вот такая картинка.
Рисунок № 7. Готовые временные ряды
Раскрываем структуру – нажимаем на цифру «3» в левом верхнем углу.

Заполняем «Периоды». В ячейках P9:P14 вводим следующие формулы. Потом выделяем данные ячейки, применяем соответствующие стили и заполняем вправо до конца листа.
Формула № 16. P9:P14
Обратите внимание, что некоторые формулы ссылаются на ячейки O11:O14. Они специально оставлены пустыми. Примените к этим ячейка стиль «Пустая ячейка».

Проверяем себя. Вводим формулы в контрольные ячейки K10, K12 и K14.
Формула № 17. Контрольные ячейки K10, K12, K14
Важно: контрольные ячейки сейчас настроены под квартальный (основной) и помесячный (вспомогательный) период. Если вы измените параметры временных периодов, то возможно появление сообщения «Ошибка» в контрольных ячейках. Таким образом вы можете себя еще раз проверить. И если потребуется, то изменить соответствующие настройки для временных периодов по полугодиям или годам.
Рисунок № 8. Завершение формирования временных рядов
Последний шаг. Создаем заголовок «Количество дней в периодах» – ячейка B114. Вводим текст в ячейки G115:G117: «Количество дней в периоде», «Количество дней в году», «Количество дней в квартале». Вводим нижеследующие формулы, выделяем диапазоны, применяем стиль и заполняем диапазон P115:EE117.
Формула № 18. Количество дней в периодах – диапазон P115:EE117
Группируем строки A98:A112 – «Количество временных периодов», A115:A117 –
«Количество дней в периодах». Переходим к фазам проекта.
Рисунок № 9. Заполненные временные ряды
Для удобства и экономии времени, вы можете воспользоваться файлом–приложением к книге А. В. Финогенова «Базовый курс по финансовому моделированию. Пошаговая инструкция по созданию финансовой модели в Microsoft Excel» и посмотреть пошаговые действия в Excel.

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