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

Занятие 1. Общая организация финмодели, шкала времени и периоды.

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

FinanceTube.ru - Больше видео про финансы!

FinanceTube.ru - Больше видео про финансы!

Для удобства восприятия занятия 1 можно скачать итоговый EXCEL-файл первого урока по финансовому моделированию в ниже следующей синей рамке, если Вы авторизованы и подписаны, как минимум, на Тариф 2 Квартал.

Далее на этой странице мы размещаем материал занятия 1 в печатном виде со множеством иллюстраций, а прежде того скачивайте пример финмодели, к которой мы в итоге придем, а также смотрите видео-инструкцию о том, как устроена эта финансовая модель в целом, ну и наконец-таки в путь!

Итак приступаем к обучению...

Разработку финансовой модели мы будем реализовывать в Excel с нуля, для чего создаем пустой файл Excel с расширением xlsx:

Каждый эксель-файл представляет из себя совокупность листов, по этой причине эксель-файлы еще называются книгами. Ярлычки листов эксель-файла расположены в нижней строке и для добавления нового листа книги Excel, достаточно либо нажать на «+» в нижней строке, справа от ярлычка последнего листа

в результате чего появится очередной лист в книге Excel

либо для добавления нового листа книги Excel достаточно навести мышку на тот лист, перед которым Вы желаете вставить новый лист xlsx-книги, нажать на правую кнопку, после чего в появившемся меню нажать на «Вставить…»

и далее нажать на «Ок»

и, тем самым, еще один лист эксель-книги добавлен

Листы эксель-файла состоят из двумерного массива ячеек, где каждая ячейка однозначно задается двумя координатами: по горизонтали номером строки, по вертикали буквой или буквами латинского алфавита.

В ячейки можно вручную вносить текст или числовые данные, а также можно вписывать формулу, значение которой рассчитывается и отображается внутри этой ячейки, причем аргументами такой формулы могут выступать другие ячейки из этого листа или любого другого листа эксель-файла.

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

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

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

получаем переименованный лист «START»

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

имеем подкрашенный в фиолетовый цвет ярлычек листа START нашего файла

Теперь приступим к общим настройкам листа эксель-файла с нашей будущей финмоделью.

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

Далее в появившемся меню нажать на кнопку «Параметры»

и выбрать опцию «Дополнительно»

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

нажимаем на «Ок» и видим, как сетка исчезла

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

после чего в меню «Главная» мы оставляем шрифт «Calibri» (он нас вполне устраивает), а его размер меняем с 11-ого, который идет по умолчанию, на 10-тый, что приводит к чуть более компактному отображению листа

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

Оставляя лист выделенным, нажимаем на правую кнопку мыши и в появившемся меню выбираем опцию «Формат ячеек…», после чего в закладке «Число» выбираем формат «Числовой» с параметрами «0» десятичных знаков после запятой и проставленной галочкой напротив «Разделитель групп разрядов ()»

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

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

В качестве горизонтальной закрепленной области листа мы возьмем блок строк с 1-ой по 14-тую, причем строки с 1-ой по 10-тую будем рассматривать, как технические, их высоту зафиксируем равной 9-ти, для этого выделим первые десять строк, нажимаем на правую кнопку мыши и в появившемся меню нажимаем на «Высота строки» и в появившемся окне вписываем высоту, равную 9-ти

Далее оставляем выделение этих 10-ти строк и в меню «Главная» меняем размер шрифта для этих строк с 10-ти на 8-мь

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

Отделим эти 10 строк от остальных серой пунктирной линией, для чего необходимо, например, выделить 10-тую и 11-тую строки, нажать на правую кнопку мыши, выбрать опцию «Формат ячеек…» и в появившемся окне в закладке «Граница» выбрать стиль границы (в нашем случае пунктирный), цвет и расположение, как показано на рисунке:

Размер высоты строк для оставшихся 4-рех строк закрепляемой области, а именно для строк с 11-той по 14-тую устанавливаем, равным 12-ти

и отделим 14-ую строку от остальных, идущих ниже строк, пунктирной светло серой границей по аналогии, как выше были разделены строки 10 и 11, тем самым, верхняя горизонтальная область для закрепления готова

Переходим к вертикальной закрепленной зоне. Выделим пять первых столбцов с «A» по «E» (далее буквенные названия столбцов будем без кавычек указывать – просто, например, A и E) и установим их ширину, равной 2,78, для этого по аналогии с тем, как мы меняли высоту строк, нажимаем на правую кнопку мыши, выбираем опцию «Ширина столбца…» (обязательно при выделенных столбцах) и так далее

Также, как и для первых 10-ти срок, задаем размер шрифта в столбцах A-E равным 8-ми и цвет шрифта задаем светло серым, поскольку здесь тоже у нас будут технические параметры финансовой модели. Столбцы E и F отделяем пунктирной светло серой границей и получаем следующий вид нашего файла

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

И для того, чтобы закрепленная вертикальная зона не занимала более 60-70% экрана мы для столбцов с F по Z определим их ширины следующим образом:

столбцы F,G,H – зададим ширину, равную 1;

11-ть столбцов с I по S – зададим ширину, равную 7;

и наконец для 7-ми столбцов с T по Z зададим ширину, равную 1.

Между столбцами Y и Z, равно как и между столбцами Z и AA, проведем пунктирную светло серую границу и посмотрим, что получилось в итоге:

Теперь наша задача закрепить зону строк с 1-ой по 14-ую и одновременно зону столбцов с A по Y, так чтобы при перемещении курсора по ячейкам листа вниз или вправо указанные зоны оставались неподвижными. Для этого необходимо выполнить последовательно следующие действия: наводим курсор мыши на ячейку Z15, в верхнем меню выбираем закладку «Вид», в появившемся верхнем горизонтальном меню нажимаем на опцию «Закрепить области» и выбираем там первую строчку «Закрепить области», как показано на рисунке

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

Почти квадратную зону в левом верхнем углу с координатами A1-E10 мы используем под логотип нашего проекта MNGMNT.RU – Управление ВСЕМ, причем картинку с логотипом превратим в гиперссылку, так чтобы при нажатии на него открывался наш сайт. Для того чтобы это сделать, необходимо навести курсор мыши на картинку, нажать на правую кнопку, выбрать в появившемся меню «Гиперссылка…» и в окне с параметрами гиперссылки в строке «Адрес» внести адрес нашего сайта https://mngmnt.ru, а также, нажав на кнопку «Подсказка», указать текстовое сообщение, которое будет отображаться при наведении мышки на картинку еще до активации гиперссылки с помощью нажатия левой кнопки мыши, в нашем случае мы напишем «Переход на сайт проекта MNGMNT.RU - Управление ВСЕМ».

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

Во-первых, начнем с уже созданной нами вкладки START, ее основной функцией является методологическое описание разрабатываемой нами финансовой модели. Так и запишем.

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

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

Следующий блок листов финансовой модели содержит 28 перечисленных ниже листов с их кратким описанием:

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

Lists – здесь задаются все выпадающие списки;

Macro – вкладка, предназначенная для внесения всех необходимых для расчетов финмодели исходных прогнозных макропоказателей, характеризующих внешнюю финансово-экономическую конъюнктуру;

Items – здесь задаются все оборотно-сальдовые статьи, которые будут использоваться в управленческой отчетности, а также их комбинации, в случае если будет иметь место сложная многоуровневая иерархия статей;

KPI – список ключевых показателей эффективности финмодели, включающая в себя коэффициенты классического финансово-экономического анализа и основные показатели инвестиционного анализа;

Mrkting – вкладка для оцифровки рынков сбыта, каналов привлечения клиентов, формирования заказов, формирования доходов и т.п.;

CapEx – здесь формируется календарный план-график капитальных затрат;

Sales – лист моделирования продаж или бюджета доходов финмодели;

Prdction – данная вкладка предназначена для моделирования производственных процессов в виде технологических карт;

HR – лист предназначен для формирования штатного расписания бизнес-проектов или предприятий;

Exp – моделирование расходной части финансовой модели, не включающей себестоимостные затраты, прямые производственные расходы, а также ФОТ, осуществляется в этом листе;

Credits – вкладка отображения кассовых разрывов и расчета внешних источников финансирования вместе с их всевозможными условиями по предоставлению, возврату и выплате процентов;

PL – формирование итогового управленческого отчета о прибылях и убытках;

CF – формирование итогового управленческого отчета о движении денежных средств;

BS – формирование итогового управленческого баланса бизнес-проекта;

MAIN – данная вкладка является главным итоговым листом финансовой модели, в котором сведены все ключевые показатели бизнес-проекта, характеризующие с разных сторон уровень эффективности и коммерческой целесообразности реализовывать моделируемый бизнес-проект;

PLd –здесь формируется детализированный управленческий отчет о прибылях и убытках;

CFd – здесь формируется детализированный управленческий отчет о движении денежных средств;

BSd – здесь формируется детализированный управленческий баланс финмодели;

Sens – анализ чувствительности бизнес-проекта к изменениям предпосылок моделирования;

UnitEc – расчет экономики, нормированной на одну продажу, на один заказ, на одну единицу продукции и т.п., т.е. расчет юнит-экономики бизнес-проекта;

Cfind – формирование и анализ финансового потока косвенным методом;

FinEcAn – проведение финансово-экономического анализа бизнес-проекта путем расчета всех коэффициентов, характеризующих смоделированную деятельность;

InvAn – проведение комплексного инвестиционного анализа финансовой модели проекта;

EBITDA – проведение комплексного анализа собственной операционной деятельности;

Rep – формирование всей управленческой отчетности предприятия в одном листе на основе листа с детализированной отчетностью Repd;

Repd - формирование всей управленческой отчетности предприятия в максимально детализированном виде в одном листе на основе листа с функционалом финмодели Calc;

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

Достаточно часто при разработке финансовой модели того или иного бизнес-проекта оказывается, что сам проект уже стартовал и по нему были произведены по факту различные финансово-хозяйственные операции, которые в том числе были учтены, например, в бухгалтерском учете 1С. В такой ситуации обычно требуется включить в финмодель факт прошлых периодов для того, чтобы видеть картину бизнес-проекта в целом. Поэтому добавим к нашим 30-ти вкладкам еще 8-мь вкладок, из которых 3 отведем под учет факта прошлых периодов, а остальные 5-ть рассмотрим в качестве резервных листов.

Здесь вкладки «Fact», «1S» и «Rules» будут предназначены соответственно для учета фактических операций в рамках финансового моделирования нашего бизнес-проекта, для выгрузок данных о фактических операциях из 1С или каких-либо других учетных бухгалтерских программ и для прописания правил сопоставления выгруженной из 1С информации статьям доходов, расходов и т.п. Остальные пять «зеленых» вкладок будут резервными.

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

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

Начнем с того, что создадим 28-мь кнопок для 28-ми «синих» вкладок, представляющих основу финансовой модели, (см. изображение выше со списком «синих» вкладок), которые разместим в верхней закрепленной области листа, между 1-ой и 10-той строками, а также правее логотипа MNGMNT.RU, т.е. правее столбца E, и до столбца Z, так чтобы не вылезать за пределы закрепленной области листа.

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

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

Наводим мышку на выделенную фигурку и нажимаем на правую кнопку, в выпавшем меню выбираем пункт «Формат фигуры…» (обычно он в самом низу этого меню) и справа появится панель «Формат фигуры»

Для того, чтобы наша фигурка выглядела, как кнопка, выберем для нее градиентную заливку темно синего цвета, как показано ниже на рисунке

Границу фигурки окрасим в темно синий цвет сплошной линией с шириной, равной 3пт, см. рисунок:

Фигурка нашей будущей кнопки перехода на другой лист финмодели примет следующий вид

Теперь добавим текст с названием того листа, на который мы будем перемещаться при нажатии на эту фигурку – «Periods», для этого наводим мышку на фигурку, нажимаем, как обычно, на правую кнопку и выбираем в выпавшем меню опцию «Изменить текст», ну и пишем название необходимого нам листа – «Periods». Формат текста производим посредством выделения фигурки с текстом и нажатия на опцию «Главная» в верхнем меню EXCEL-файла, где размер шрифта выбираем равным 10, начертание выбираем «полужирным» и, наконец, цвет – белым:

С учетом того, что вкладок у нас 28-мь, и их всех нужно разместить в верхней закрепленной области листа, дополнительно настроим размер фигурки так, чтобы она стала чуть более компактной, для этого нажимаем на правую кнопку наведенной на фигурку мышки, и опять выбираем опцию «Формат фигуры», и в появившейся справа панели редактирования фигуры выбираем последовательно раздел «Параметры фигуры», подраздел «Размер и свойства»/«Размер», где устанавливаем высоту и ширину фигурки соответственно равными 0,5 и 1,5 см:

В подразделе «Размер и свойства»/«Свойства» выбираем опцию «Не перемещать и не изменять размеры», как показано ниже на рисунке – это необходимо для того, чтобы при любых манипуляциях со столбцами наши кнопки с гиперссылками на листы финмодели оставались на своих местах и не меняли размеры:

В подразделе «Размер и свойства»/«Надпись» центрируем надпись по периметру фигурки и зануляем все поля внутри фигурки – верхнее, нижнее, левое и правое поля устанавливаем, равными 0 см:

В результате наша фигурка имеет следующий вид:

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

Наводим мышку на фигурку «Periods», выделяем фигурку, нажимаем на правую кнопку мыши, где в появившемся меню жмем на опцию «Гиперссылка». В появившемся окне «Вставка гиперссылки» в левой панели нажимаем на опцию «Место в документе», выбираем только что созданный лист «Periods» и пишем в строке «Введите адрес ячейки:» ссылку на ячейку F15, вместо A1, листа с названием «Periods», как ниже показано на рисунке, а также, нажимая на кнопку «Подсказка…» в правом верхнем углу этого окна, в появившейся строке «Подсказка для гиперссылки» делаем запись «Периоды и горизонты моделирования фактических и прогнозных данных», чтобы она потом отображалась при наведении мышки на кнопку перед нажатием на нее.

Проверяем активность созданной нами кнопки с гиперссылкой на вкладку «Periods», наводя мышку на кнопку и нажимая на нее – оказываемся во вкладке «Periods» - Работает!

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

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

Если Вы переименуете или вообще удалите лист «Periods», после чего нажмете на кнопку с гиперссылкой на лист с названием «Periods», то появится маленькое окошечко, в котором будет написано «Недопустимая ссылка», т.е. ссылка не работает.

А теперь создайте новый лист, назовите его «Periods» и опять нажмите на кнопку с гиперссылкой «Periods». Что мы видим? Мы оказались на вновь созданном листе с названием «Periods».

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

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

Создаем новую кнопку, меняем ее название на «Lists» и выравниваем ее расположение по отношению к кнопке «Periods»

Теперь чтобы кнопка стала гиперссылкой на вкладку «Lists», переименовываем наш заранее созданный фиктивный лист с названия «Periods» на название «Lists»

Наводим мышку на кнопку «Lists», нажимаем на правую кнопку мыши и выбираем из выпавшего меню опцию «Изменить гиперссылку…». В появившемся окне «Изменение гиперссылки» в левой панели видим, что выбрана опция «Место в документе», выбираем вкладку «Lists» и пишем в строке «Введите адрес ячейки:» ссылку на ячейку F15, вместо A1, нашего фиктивного листа с названием «Lists», как ниже показано на рисунке, а также, нажимая на кнопку «Подсказка…» в правом верхнем углу этого окна, в появившейся строке «Подсказка для гиперссылки» меняем запись «Периоды и горизонты моделирования фактических и прогнозных данных» на новую, соответствующую новой кнопке, «Выпадающие списки финансовой модели», чтобы она потом отображалась при наведении мышки на кнопку «Lists» перед нажатием на нее мышкой.

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

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

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

Формирование закрепленной зоны в строках с 1-ой по 10-тую мы на некоторое время завершаем и переходим далее. В 11-той строке мы разместим название листа и заголовок листа, отражающий его содержание. Название листа мы поместим в ячейку F11, где оформим его отображение с помощью прямой формулы на ячейку в листе «START», в которой название листа впервые появляется в финмодели. Формула в ячейке F11 будет иметь следующий вид:

=START!I25

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

Ячейку F11 с названием текущего листа форматируем так, чтобы шрифт был 8пт, полужирный и темно синего цвета:

В ячейку I11 по аналогии подтягиваем краткое описание предназначения листа из столбца K при помощи формулы «=START!K25» и форматируем ячейку I11 так, чтобы шрифт был полужирным и темно красного цвета, а также его размер остался 10пт – это у нас будет заголовок листа.

Строку 12 мы сейчас пропустим, но потом еще вернемся как к ней, так и к строке 11, дело в том, что в расчетных и отчетных вкладках нашей финансовой модели в этих строках, начиная со столбца AA, а также в столбце X будут отображаться даты начала и конца периодов, но об этом чуть позже.

В 13-той строке будем указывать названия столбцов листа или заголовки, например, как показано ниже на рисунке:

Следующая строка 14 у нас отводится под формирование системы контроля финансовой модели, в нее на каждом листе будут стекаться все контрольные «точки» листа и далее, суммируя их по всей 14-той строке, начиная от столбца F и завершая столбцом PL (это столбец, на котором будет заканчиваться шкала времени расчетов финмодели), сумма будет отображаться в ячейке E14, т.е. в ячейке E14 мы прописываем формулу:

=сумм($F14:$PL14)

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

Также шрифт 14-той строки выберем равным 8-ми, полужирным и отцентрируем по горизонтали текст в ячейках.

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

Кнопки для перехода на листы «START» и «Cntrl» мы создаем абсолютно также, как и предыдущие 28-мь кнопок верхней панели, и располагаем их на уровне 12-той и 13-той строк левой вертикальной закрепленной области:

С оставшимися «зелеными» кнопками проделываем тоже самое и выкладываем их на левой закрепленной области листа, ниже 15-той строки.

В завершение типовой базовой настройки листа мы, начиная с ячейки A15 и ниже (например, до 70-той строки), пропишем в ячейках формулу

=строка()

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

Итак, наш типовой лист имеет следующий вид:

Стартовая часть нашего занятия завершена, в оставшейся части занятия мы настроим еще три вкладки нашей будущей финансовой модели, а именно вкладки «Cntrl», «Periods» и «Lists».

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

В появившемся окне «Переместить или скопировать» выбираем лист перед которым необходимо вставить копию вкладки «START», а также обязательно ставим галочку напротив «Создать копию»:

Меняем название вновь созданного листа с «START (2)» на «Cntrl», цвет ярлычка вкладки «Cntrl» меняем на ярко красный, а также в этом новом листе «Cntrl» меняем цвет линии границы кнопки «START» с красного на синий, а у кнопки «Cntrl» с синий на красную, и перенастраиваем ссылки в ячейках F11 и I11 с 25-той строки вкладки «START» на 26-тую, где прописана информация с названием листа и его заголовком для вкладки «Cntrl». Также очищаем от всего содержимого листа «START» в «теле» листа – например путем протягивания или копирования строки 20 на все нижележащие до строки 62. Итого получаем следующий вид вкладки «Cntrl»:

Теперь создаем еще две копии контрольного листа и создаем заготовки для вкладок «Periods» и «Lists»:

Лист «Cntrl» пока что «отложим в сторону» и приступим к настройке вкладки «Periods».

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

- момент или дату старта расчетов финансовой модели;

- минимальный временнОй шаг или период учета с целью детализации принадлежности финансово-хозяйственных операций по дате их реализации;

- горизонт моделирования в количестве минимальных периодов учета и расчетов;

- периодизацию отчетности;

- горизонт учета фактически произведенных операций.

Для начала настроим вертикальную разметку столбцов листа «Periods».

Договоримся о том, что все показатели в финансовой модели будут иметь не более, чем трехуровневую структуру, и отведем под нее столбцы H, I и J; столбец Q будет служить для задания пользователем финмодели тех или иных исходных данных; столбец X будет являться итоговым столбцом по отношению к столбцам детализирующим расчеты и отчеты модели в рамках блока столбцов, начинающихся со столбца AA и далее до последнего периода детализации расчетов; и наконец остальные столбцы пока что сделаем по ширине, равными 1, чтобы иметь возможность потом их использовать, под различные новые цели моделирования.

Далее настроим условное форматирование строк, начиная с 16-той, в зависимости от того уровня, на котором находится рассматриваемый показатель.

Выделяем ячейки H16, I16, J16, и настраиваем условное форматирование в соответствии со следующим набором правил форматирования в зависимости от комбинаций содержимого этих ячеек.

Правило 1. Показатель верхнего уровня. Условие: первая ячейка не пустая, остальные две пустые.

Формула: =И($H16<>"";$I16="";$J16="")

Формат: заливка светло серая, шрифт полужирный, верхняя граница светло серая, нижняя граница серая. См. изображение:

Правило 2. Показатель второго уровня. Условие: первая и вторая ячейки не пустые, третья пустая.

Формула: =И($H16<>"";$I16<>"";$J16="")

Формат: только окрашивание нижней границы в светло серый цвет. См. изображение:

Правило 3. Показатель третьего уровня. Условие: все три ячейки не пустые.

Формула: =И($H16<>"";$I16<>"";$J16<>"")

Формат: шрифт серый, нижняя граница пунктирная и окрашена в светло серый цвет. См. изображение:

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

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

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

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

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

Поместим наш выпадающий список возможных «минимальных периодов расчетов» в столбец I во вкладке «Lists», как показано ниже на рисунке

Чтобы данный выпадающий список заработал во вкладке «Periods», перейдем в нее и, во-первых, в ячейке H16 введем прямую ссылку на заголовок выпадающего списка, находящегося во вкладке «Lists», в ячейке I13 – формула «=Lists!$I$13».

И, во-вторых, настроим сам выпадающий список на ячейку Q16, для чего нажимаем на опцию «Данные» в верхнем горизонтальном меню EXCEL-файла, выбираем кнопку «Проверка данных» и там еще раз нажимаем на «Проверка данных…»

В появившемся окне «Проверка вводимых значений» в закладке «Параметры» выбираем тип данных «Список» и в строке источник прописываем формулой («=Lists!$I$16:$I$22») ссылку на блок ячеек во вкладке «Lists», где находятся значения выпадающего списка «минимальный период расчетов».

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

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

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

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

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

Вот и мы поставим интересную задачу, связанную с выпадающими списками. Очень бы хотелось, чтобы, когда пользователь, прописывая в столбце H (показатели первого уровня) название выпадающего списка из вкладки «Lists», копирует ранее настроенную ячейку с выпадающим списком в соответствующую ячейку в столбце Q, то автоматически выпадающий список перенастраивался на тот, название которого стоит напротив этой ячейки с новым выпадающим списком.

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

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

=столбец()

Во второй строке, т.е. в ячейке I2, нам понадобится формула, возвращающая буквенное название столбца, в качестве такой формулы, можно, например, использовать следующую:

=ЛЕВСИМВ(АДРЕС(1;СТОЛБЕЦ();4);ДЛСТР(АДРЕС(1;СТОЛБЕЦ();4))-1)

Правее от столбца I с выпадающим списком рассмотрим еще пару столбцов J и K, в первом из которых запишем формулу нумерации значений выпадающего столбца

=МАКС(J$15:J15)+1

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

=ЕСЛИ(ИЛИ(J16="";J16=0);0;ЕСЛИ(СЧЁТЕСЛИ(I$15:I$100000;I16)=1;0;1))

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

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

Заключительным ингредиентом для написания универсальной формулы выпадающего списка является формула записи массива ячеек, в которых находятся актуальные значения выпадающего списка, эту формулу мы для каждого выпадающего списка будем размещать в строке 15. Сама формула имеет вид:

=$F$11&"!"&I$2&$A16&":"&I$2&($A15+МАКС(J$15:J$100000))

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

Итак, стандартно настроенный выпадающий список у нас будет выглядеть вот так:

Теперь переходим во вкладку «Periods», наводим мышку на ячейку Q16, которая у нас настроена на выпадающий список и меняем формулу

=Lists!$I$16:$I$22

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

=ДВССЫЛ(ИНДЕКС(Lists!$15:$15;СУММЕСЛИМН(Lists!$1:$1;Lists!$13:$13;$H16)))

Данная формула посредством функции ИНДЕКС «находит» в строке 15 вкладки «Lists» ровно ту запись блока ячеек, в котором сосредоточены значения выпадающего списка с заголовком, прописанным в соответствующей ячейке столбца H с показателями верхнего уровня листа «Periods».

Отметим, что при использовании такого универсального подхода необходимо контролировать, чтобы показатель в столбце H верхнего уровня действительно совпадал с одним из заголовков выпадающих списков вкладки «Lists». Такой контроль осуществим в столбце G, в ячейках левее ячеек показателей верхнего уровня, где пропишем формулу проверки нахождения в листе «Lists» такого же заголовка, как в столбце H, при условии, что в столбце R находится «галочка»:

=ЕСЛИ($R16="^";ЕСЛИ(СЧЁТЕСЛИ(Lists!$13:$13;H16)=1;0;1);0)

В 14-той строке естественно прописываем соответствующую формулу суммирования «контрольных ноликов».

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

=ЕСЛИ(ИЛИ($Q16=0;$Q16="");0;ЕСЛИ($R16="^"; ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ(ИНДЕКС(Lists!$15:$15; СУММЕСЛИМН(Lists!$1:$1;Lists!$13:$13;$H16)));$Q16)=1;0;1);0))

Контроль ошибок в оформлении выпадающих списков финмодели работает, как изображено на рисунке:

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

Здесь мы ячейки с датой старта и количеством периодов горизонта моделирования настроили следующим образом.

Для даты старта настройка в виде даты в формате «ДД.ММ.ГГ» с ограничением снизу по дате на уровне 1-ого января 1980г.:

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

Понятно, что наиболее часто используемый минимальный период расчетов финмодели – это месяц, и количество месяцев в 400 штук с лихвой покрывает 99,9% запросов на финансовые модели в ежемесячной детализации. Но поскольку мы здесь претендуем на создание финансовой модели максимально универсального типа, то видим, что при выборе в качестве минимального периода расчетов периода в один день, горизонт моделирования в максимальные 400 таких минимальных периодов составит всего один год и 1 месяц с маленьким хвостиком. С другой стороны, Вы спросите, а что это бывает актуально, реализовывать финансовые модели в ежедневной детализации? Поверьте, что да, и еще как актуально! Для крупных интернет компаний, у которых за сутки на сайте формируются заказы в количестве в несколько тысяч штук, да плюс к тому присутствует явно выраженное внутринедельное неравномерное распределение объемов созданных заказов в зависимости от дня недели, такое построение финансового моделирования особенно важно. Автору не один раз приходилось настраивать в крупнейших российских интернет-компаниях системы финансового моделирования, бюджетирования и управленческого учета, которые работали в ежедневном разрезе.

Этих трех параметров, а именно минимальный период расчетов, начальная дата и горизонт моделирования, нам теперь вполне достаточно для формирования временнОй шкалы финансовой модели в виде последовательности заданных минимальных периодов.

Строки 20, 21 и 22 отведем под задание начальных, конечных дат и названий периодов финансового моделирования, как показано на рисунке:

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

Сначала формулу

=ЕСЛИ(ИЛИ($Q$17="";$Q$17=0);"";ЕСЛИ($Q$16=Lists!$I$16;$Q$17; ЕСЛИ($Q$16=Lists!$I$17;$Q$17-ЕСЛИ(ДЕНЬНЕД($Q$17)=1;7;ДЕНЬНЕД($Q$17))+2; ЕСЛИ($Q$16=Lists!$I$18;КОНМЕСЯЦА($Q$17;-1)+ЦЕЛОЕ((ЕСЛИ(ДЕНЬ($Q$17)=31;30; ДЕНЬ($Q$17))-1)/10)*10+1;ЕСЛИ(ИЛИ($Q$16=Lists!$I$19;$Q$16=Lists!$I$21); КОНМЕСЯЦА($Q$17;-1)+1;ЕСЛИ($Q$16=Lists!$I$20; КОНМЕСЯЦА($Q$17;-(МЕСЯЦ($Q$17)-3*ЦЕЛОЕ((МЕСЯЦ($Q$17)-1)/3)))+1; ЕСЛИ($Q$16=Lists!$I$22;КОНМЕСЯЦА($Q$17;-МЕСЯЦ($Q$17))+1;"")))))))

вставляем в ячейку X20, тем самым уточняя для финмодели корректную дату старта моделирования в соответствии с типом выбранного минимального периода.

Формулу

=ЕСЛИ(AA$16>$Q$18;"";ЕСЛИ(AA$16=1; ЕСЛИ($X20="";"";$X20);ЕСЛИ(Z21="";"";Z21+1)))

вставляем в ячейку AA20 (старт первого расчетного периода финмодели), причем для работы этой формулы нам потребуется техническая настройка в строке 16, где, начиная со столбца AA, мы пропишем формулу

=МАКС($Z16:Z16)+1

которая будет отображать номер периода расчетов финмодели (от 1 до 400).

Далее формулу

=ЕСЛИ(AA20="";"";ЕСЛИ($Q$16=Lists!$I$16;AA20; ЕСЛИ($Q$16=Lists!$I$17;AA20+6;ЕСЛИ($Q$16=Lists!$I$18;ЕСЛИ(ДЕНЬ(AA20)=21; КОНМЕСЯЦА(AA20;0);AA20+9);ЕСЛИ($Q$16=Lists!$I$19;КОНМЕСЯЦА(AA20;0); ЕСЛИ($Q$16=Lists!$I$20;КОНМЕСЯЦА(AA20;2); ЕСЛИ(ИЛИ($Q$16=Lists!$I$21;$Q$16=Lists!$I$22);КОНМЕСЯЦА(AA20;11);"")))))))

вставляем в ячейку AA21, получая расчет конечной даты расчетного периода финмодели.

И наконец формулу

=ЕСЛИ(ИЛИ(X20="";AA21="");"";МАКС($Z21:$PK21))

вставляем в ячейку X21, где у нас будет отображаться дата горизонта моделирования.

Также для удобства визуального восприятия мы добавляем 22-тую строку к нашей системе периодизации финмодели, прописывая в ячейке AA22 следующую формулу:

=ЕСЛИ(AA20="";"";ЕСЛИ($Q$16=Lists!$I$16;ЕСЛИ(ДЕНЬНЕД(AA20)=1;"вс"; ЕСЛИ(ДЕНЬНЕД(AA20)=2;"пн";ЕСЛИ(ДЕНЬНЕД(AA20)=3;"вт";ЕСЛИ(ДЕНЬНЕД(AA20)=4;"ср"; ЕСЛИ(ДЕНЬНЕД(AA20)=5;"чт";ЕСЛИ(ДЕНЬНЕД(AA20)=6;"пт";"сб")))))); ЕСЛИ(ИЛИ($Q$16=Lists!$I$17;$Q$16=Lists!$I$18); СЧЁТЕСЛИМН($Z$17:AA$17;МЕСЯЦ(AA20);$Z$18:AA$18;ГОД(AA20)-2000)& ЕСЛИ($Q$16=Lists!$I$17;"нд-";"дк-")&ЕСЛИ(AA$17=1;"янв";ЕСЛИ(AA$17=2;"фев"; ЕСЛИ(AA$17=3;"мар";ЕСЛИ(AA$17=4;"апр";ЕСЛИ(AA$17=5;"май";ЕСЛИ(AA$17=6;"июн"; ЕСЛИ(AA$17=7;"июл";ЕСЛИ(AA$17=8;"авг";ЕСЛИ(AA$17=9;"сен";ЕСЛИ(AA$17=10;"окт"; ЕСЛИ(AA$17=11;"ноя";"дек")))))))))))&AA$18;ЕСЛИ($Q$16=Lists!$I$19;AA20; ЕСЛИ($Q$16=Lists!$I$20;(ЦЕЛОЕ((AA$17-1)/3)+1)&"кв"&AA$18&"г"; ЕСЛИ($Q$16=Lists!$I$21;AA$16&"год"; ЕСЛИ($Q$16=Lists!$I$22;ГОД(AA20)&"г.";"")))))))

Для которой с использованием функций МЕСЯЦ() и ГОД() нам потребуется добавить еще две технические строки, а именно строки 17 и 18, в которых, начиная со столбца AA, будут отображаться число месяца и номер года в третьем тысячелетии даты старта соответствующего расчетного периода из строки 20.

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

Последовательная вложенность функций ЕСЛИ() в этих формулах обусловлена тем, что нам необходимо перебрать один за другим все варианты выбора пользователем финансовой модели из выпадающего списка минимального расчетного периода:

- если в качестве периода выбран «день», то временнАя шкала финмодели начинается с указанной пользователем стартовой даты;

- если в качестве периода выбрана «неделя», то шкала начинается с понедельника той недели, внутри которой оказалась дата старта моделирования;

- если в качестве периода выбрана «декада», то шкала начинается с 01-ого, 11-ого или 21-ого числа месяца в зависимости от того в какую декаду месяца попала стартовая дата;

- если в качестве периода выбран «месяц», «квартал» или «календарный год», то шкала начинается с первого числа того календарного месяца, квартала или года, внутри которого оказалась дата старта моделирования, заданная пользователем;

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

Таким вот образом мы расписали словами одну из «громоздких» формул из перечисленных выше, откуда очевидно вытекает, что ничего сверх сложного в этих формулах нет.

Для читателя будет полезным разобрать самостоятельно остальные прописанные формулы.

И вот как теперь выглядит наша вкладка с периодизацией финмодели:

Отметим, что здесь на рисунке горизонт моделирования в количестве периодов указан равным 60, т.е., начиная со столбца AA, должно быть задействовано 60 последовательных столбцов под задание временнОй шкалы, и для завершения формирования шкалы времени финмодели нам необходимо скопировать столбец AA на 59 столбцов правее AA. Но мы пока что не будем этого делать. Сделаем тогда, когда настроим все формулы для первого периода шкалы из столбца AA для отображения всех задуманных нами параметров модели, которые должны быть на листе «Periods». После чего мы одним протягиванием столбца AA на 399 столбцов правее, вплоть до столбца PJ, дооформим шкалу времени.

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

Теперь перейдем к вопросу отражения фактических данных. Тут на самом деле возникает два последовательных уточняющих вопроса:

- учитывать или нет в финмодели факт прошлых периодов?

- если фактические данные учитывать, то за какой период?

Для того чтобы пользователь в рамках работы с финансовой моделью мог отвечать на подобные вопросы введем в рассмотрение выпадающий список во вкладке «Lists» с заголовком «ответ “да/нет”» и списком выпадающих значений «да» и «нет». Для чего скопируем столбцы H,I,J,K правее в столбцы L,M,N,O, переименуем заголовок, удалим лишние 5-ть последних записей выпадающего списка и пропишем новые его значения в первых двух записях – «да», «нет», см. изображение:

В листе «Periods» создаем два вопроса для пользователя с ответами из выпадающего списка «да/нет», как ниже показано на рисунке:

Далее строки 20 и 21 копируем в строки 28 и 29, меняем названия на «дата начала фактического периода» и «дата окончания фактического периода» соответственно и настраиваем формулы в ячейках с датами начала и окончания фактических периодов с учетом дополнительных двух значений, которые располагаем в следующих двух строках столбца Q:

- «по умолчанию - сегодняшний день» со значением в ячейке Q30 (используем формулу «=СЕГОДНЯ()», возвращающую сегодняшнюю дату);

- «задать вручную дату окончания факта» со значением, которое пользователь задает самостоятельно вручную в ячейке Q31, в случае если в ячейке Q25 выбрано значение «да», а в Q26 – «нет».

При написании формул начала и окончания фактических периодов необходимо учитывать, что все должно начинаться с формулы окончания периода в ячейке AA29:

=ЕСЛИ($Q$25=Lists!$M$16;ЕСЛИ(AA$21< =ЕСЛИ($Q$26=Lists!$M$16;$Q$30;$Q$31);AA$21;"");"")

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

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

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

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

Получается так, что в рамках нашей логики построения шкалы времени все листы финмодели должны разделяться, как минимум, на три типа: расчетные, отчетные и технические, где первые два типа листов будут в строках 11,12 и 13 верхней закрепленной области, начиная со столбца X, иметь шкалу времени соответственно с минимальным периодом и отчетным периодом, а третий тип листов – это листы подобные вкладке «Lists», где не нужна шкала времени, т.е. эти листы носят технический характер для финансовой модели.

Хорошо, вводим в рассмотрение во вкладке «Lists» еще один выпадающий список с заголовком «тип листа» и тремя значениями: «расчетный», «отчетный» и «технический».

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

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

Как мы уже вскользь упомянули ранее, система контроля в Excel-файле с финмоделью у нас выстраивается таким образом, чтобы внутри каждой вкладки был свой контроль, который пронизывает контрольными ноликами по горизонтали и вертикали весь лист, собирает все в строку 14 и далее 14-тая строка суммируется в ячейке E14 опять же каждой вкладки, после чего все контрольные суммы в ячейках E14 собираются в листе «Cntrl» и теперь уже их общая контрольная сумма направляется в каждую вкладку финмодели в ячейки с адресом D14. Очевидно, что при такой системе организации контроля сложно ошибиться и сразу не заметить ошибку и более того, сложно оперативно не разобраться, где произошел сбой.

В листе «Cntrl» нам потребуются пять столбцов со следующими заголовками: «лист», «ячейка с контролем», «контроль», «номер листа» и «тип листа», причем последнее название тянется прямой ссылкой с соответствующего заголовка вкладки «Lists». В столбец «лист» мы подтягиваем названия листов из вкладки «START» прямыми ссылками, в ячейках столбца «ячейка с контролем» напротив названий листов мы вписываем вручную в качестве значений адреса тех ячеек этих листов, в которых находятся их общие контроли – как мы хорошо помним, это везде ячейка E14. В столбце «контроль» мы прописываем формулу

=ДВССЫЛ(I18&"!"&K18)

которая очевидно по своей сути является прямой ссылкой на ячейки E14, тех листов, названия которых прописаны напротив в столбце «лист».

В ячейке M16 мы суммируем контроли всех листов финмодели и потом отображаем их в ячейке D14 всех вкладок модели, см. рисунок:

Также нам здесь понадобились еще два технических столбца: «номер листа» и «тип листа». В первом из них прописана функция ЛИСТ(), которая возвращает порядковый номер ярлычка листа в нижней горизонтальной панели эксель-файла, и послужит она нам для того, чтобы контролировать совпадение названия листа, с одной стороны, прописанного в ярлычке, а, с другой стороны, того, которое указано в списке листа «START». Такой контроль нам необходим, поскольку все гиперссылки наших кнопок навигации по вкладкам финмодели настроены на вполне конкретные названия листов, а значит изменение пользователем названия того или иного листа может привести к ошибкам внутри xlsx-файла с финмоделью.

Для контроля названий листов поместим в ячейку F14 каждой вкладки эксель-файла формулу:

=ЛИСТ($A$1)-СУММЕСЛИМН(Cntrl!$O$15:$O$100000;Cntrl!$I$15:$I$100000;$F$11)

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

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

С вкладкой «Cntrl» мы завершили, переходим к последним настройкам листа «Periods».

Отобразим в листе «Periods» тип этого листа в 12-той строке закрепленной области (выше заголовков), как показано на рисунке:

Наконец, в строках 11, 12 и 13, начиная со столбца X, т.е. в ячейках, отведенных под отображение в листах финансовой модели шкалы времени, пропишем формулы, точнее единую формулу, которую потом протянем, суть которой сводится к тому, что если в ячейке Q12 тип листа отобразился со значением «расчетный», то шкала времени подтягивается из соответствующих ячеек строк 20, 21 и 22, отвечающих за формирование расчетных периодов, если значение – «отчетный», то шкала времени подтягивается отчетная из строк 35, 36 и 37, ну а уж если значение – «технический», то «пусто».

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

- номер столбца;

- номер периода модели;

- номер факт-периода;

- номер план-периода;

- число даты начала периода;

- номер месяца даты начала периода;

- год даты начала периода;

- число даты окончания периода;

- номер месяца даты окончания периода;

- год даты окончания периода.

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

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

Посмотрим, как это выглядит.

Начало шкалы времени:

Окончание шкалы времени:

Максимальный горизонт шкалы времени – 400-тый период (столбец PJ):

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

Для удобства восприятия занятия 1 можно скачать итоговый EXCEL-файл первого урока по финансовому моделированию в ниже следующей синей рамке, если Вы авторизованы и подписаны, как минимум, на Тариф 2 Квартал.

Успехов Вам в изучении финансового моделирования!

Следующие уроки: Прил. 1 к занятию 1. Система контроля финмодели