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

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

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

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

Рисунок № 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. Заполненные временные ряды