Прил. 3 к занятию 1. Условное форматирование

Приложение №3 к занятию 1 курса обучения финансовому моделированию в Excel.

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

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

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

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

Смотрите видео
об организации системы условного форматирования в финмодели

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

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

Мы постарались создать и предложить Вам на наш взгляд наиболее минималистический шаблон оформления, так сказать, «тела» финансовой модели в Excel.

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

Во-вторых, для каждого из трех уровней необходимо определиться с форматом отображения строк или ячеек. Для первого уровня, т.е. для отображения верхнеуровневых показателей и их значений будем использовать форматирование, включающее в себя: полужирный шрифт, заливку цветом, выделение верхней и нижней границ ячеек. Чтобы таким образом настроить ячейки выделяем их мышкой, идем в меню «Главная» в опцию «Условное форматирование», где выбираем «Создать правило..», и в появившемся окне «Создание правила форматирования» выбираем тип правила «Использовать формулу для определения форматируемых ячеек»

Теперь нам необходимо прописать формулу в строке «Форматировать значения, для которых следующая формула является истинной:». Для написания формулы достаточно понять, что условием того, что в строке 16 находится показатель верхнего уровня, является наличие непустого текста с названием показателя верхнего уровня в ячейке H16 и одновременно «пустота» в двух соседних справа ячейках для второго и третьего уровней. Такое условие можно представить следующей формулой:

=И($H16<>"";$I16="";$J16="")

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

Нажимаем на кнопку «Формат», и в окне «Формат ячеек» задаем формат для отображения показателей верхнего уровня финмодели – в нашем случае мы выбираем полужирный шрифт во вкладке «Шрифт», светло серую заливку во вкладке «Заливка» и наконец во вкладке «Граница» для нижней границы выбираем сплошную линию темно серого цвета, а для верхней – светло серого, но на пару тонов темнее чем цвет заливки.

Нажимаем на «Ок» и проверяем форматирование – впишем «показатель-1» в ячейку H16, и получаем следующее отображение ячеек с показателем верхнего уровня:

Чтобы вся конструкция была устойчивой и «красиво» работала введем в рассмотрение и применение принцип, согласно которому любой показатель второго уровня записывается в паре с соответствующим показателем первого уровня, а любой показатель третьего уровня записывается вместе с одновременным присутствием в двух ячейках слева соответствующих показателей первого и второго уровней, как показано на рисунке:

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

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

=И($H16<>"";$I16<>"";$J16="")

и

=И($H16<>"";$I16<>"";$J16<>"")

Формат для показателей второго уровня сделаем совсем простым – окрасим только нижнюю границу ячеек в серый цвет, а для показателей третьего уровня: окрасим шрифт в серый цвет и нижнюю границу представим светло серым пунктиром.

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

Алгоритм внедрения в финансовую модель описанного здесь метода условного форматирования системы показателей такой:

Шаг 1. Настраиваем условное форматирование показателей в какой-либо вкладке Excel-файла с будущей финмоделью для одной из строк.

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

Шаг 3. При создании новых вкладок финмодели за основу берем лист с уже настроенным условным форматированием из предыдущего шага.

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

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

Следующие уроки: Прил. 4 к занятию 1. Ключевые функции и формулы