Фото: Fotolia/PhotoXPress.ru
Временная шкала – основа размещения материала в финансовой модели. Формат, где учетные пункты (продажи, себестоимость, маржа…) размещаются по вертикали, а периоды (1 квартал, 2 квартал, 3 квартал…) откладываются по горизонтали – практически безальтернативный. Это своего рода координатная сетка, в которой ведутся все подсчеты.
В подавляющем большинстве случаев, включая даже некоторые наставления по финмоделированию, авторы моделей обращаются с этой сеткой неправильно. Как это можно сделать, покажем сразу на типичном примере, с которым я встречаюсь регулярно у самых разных авторов.
Нравится ли вам эта сетка? На вид – все в порядке – сперва кварталы, потом года, все понятно подписано?
Нет. Давайте откроем формулы (включив режим Formula Auditing в меню Options или специальной кнопкой) и посмотрим, что у нее внутри. А там внутри полный беспорядок:
Во-первых, вместо числовых значений кварталов (1, 2, 3…) автор ввел текстовые строки («1 кв.», «2 кв.» и т.д.) Судя по всему, он не знал, что если прямо напечатать в ячейке «1 кв.» – Excel превратит эту запись из числа в текст. А это значит, что работать с номерами кварталов как с числами – например, «вывести итоги по полугодиям» или «посчитать оплату с отсрочкой на 2 квартала» – эта модель не сможет.
Способ вывести на экран числовое значение с текстовым ярлычком существует, но он другой. Нужно ввести только число, открыть диалог Format-Cell-Number, выбрать строку Custom и, не изменяя кода числа, написать после него «кв.». Кавычки обозначают текстовую строку, которую Excel визуально прилепит к числу. Внутри же ячейки сохранится введенное число в численном виде. Коды форматирования чисел вообще позволяют делать много интересных и полезных вещей – скажем, визуально убрать лишние нули. Например, код "USD "0,," MM" делает из 1000000000 "USD 1,000 MM", а "USD" #,0##.00,," MM" делает из 500000 "USD 0.5 MM".
Во-вторых, в одной таблице смешаны переменные и формулы, и, ладно бы, переменные были в определенном ряду. Но в ряду 3 идет сначала переменная (колонка B, оранжевый цвет), затем вызовы значений из ряда 7 предыдущей колонки (с C по E, желтый цвет), а затем вызов значения из того же ряда 3 колонки B. И после этого опять вызов из предыдущей колонки. Испортить такой ряд – проще простого, да и проанализировать непросто.
В-третьих, автор сбил нумерацию кварталов, начиная ее заново каждый год. Отсчитать «6-й квартал проекта» он сможет лишь с большим трудом – а если б он пронумеровал кварталы последовательно с начала проекта, это не потребовало бы никаких усилий. Обратное преобразование, «6 квартала» во «2 квартал 2 года» достигается простейшей арифметической операцией – это частное и остаток от деления на 4. Методов ее решения в Excel несколько, простейший, очевидно, такой:
=MOD(номер_квартала,4)+1 -> 2 квартал
(функция MOD выводит остаток от деления, первый аргумент – делимое, второй – делитель, и не забудем, что 1 квартал года соответствует остатку в 0)
=CEILING(номер_квартала,4)/4 -> 2 год
(функция CEILING округляет число в первом аргументе вверх до ближайшего числа, кратного ее второму аргументу)
И самое главное, автор разорвал последовательность кварталов промежуточными итогами годов. Это архитектурная ошибка, которую мы с вами сразу внесем в список запретов:
Расчеты – отдельно, отчеты – отдельно!
«Отчет» – это таблица, которую вы планируете показывать на экране, распечатывать, копировать в презентацию или еще как-то предъявлять другим в завершенном виде. Отчет можно красиво форматировать, располагать строки и столбцы на листе как угодно – это ваше решение. Но никаких расчетов, кроме тех, что нужны собственно для формирования этого отчета, мы в отчете не производим. В идеале он состоит только из вызовов значений, которые уже где-то рассчитаны. Если вам нужно, как в данном случае, переформатировать месячный расчет в квартальный, квартальный в годовой – создайте для этого отдельный лист отчета и сделайте все, что необходимо, в нем.
А расчеты должны быть сделаны просто, компактно, с минимальным функциональным форматированием – то есть таким, которое упрощает вам чтение отчета. И все временные серии там должны быть последовательными.
Когда мы строим координатную сетку, важно помнить, что даты – это тоже числа, причем числа совершенно определенные. В Excel точкой отсчета дат является 1 января 1900 года – это день номер 1. Каждый последующий день увеличивает число на 1, время дня обозначается как дробь. Видимая дата «15 марта 2012 года» (предполагаемая дата выхода этого материала) в Excel будет записана числом 40983. Датой ее делает специальный автоматический код формата – например dd.mm.yy выведет ее как 15.03.12 (евророссийский формат), mmm dd, yyyy как March 15, 2012 (американский формат), а dddd отобразит название дня – «четверг».
Итак, правильный формат листа должен был выглядеть так:
Светло-серый цвет – это исключительно форматирование для удобства глаз, отделяющее шапку таблицы от ее тела. А темные сплошные линии, которые крестообразно делят таблицу между рядами 3 и 4 и колонками A и B – не формат, а результат операции разбиения окна на 4 плоскости и их «замораживания» на месте. Теперь тело таблицы будет прокручиваться само по себе, в то время как первая колонка и три верхних ряда всегда будут оставаться на месте. Если таблица выходит за рамки экрана (а это будет происходить почти всегда) – это даже не удобство, а почти обязательная процедура.
Но это, по-прежнему, статический ряд дат, который не позволяет нам, например, переиграть модель на другую дату начала проекта. Поэтому, давайте переделаем ряд дат в динамический. Предположим, что периодом модели будет месяц, а датой начала – 1 сентября 2012 года.
1. Определим вверху листа новую переменную start_date и введем туда дату «01.09.2012.»
2. (необязательный шаг) В диалоге Define Name создадим три новых переменных, но вместо адресов напишем в их поля ссылок формулы:
start_year -> =YEAR(start_date)
start_month -> =MONTH(start_date)
start_day -> =DAY(start_date)
Это еще одно отличное свойство Excel – имя может быть сокращенным наименованием и формулы. Так мы создали три производных переменных, которые позволят сделать повторяющиеся формулы более читаемыми и снизить счетную нагрузку на Excel (многократные типовые подсчеты будут заменены разовым, и модель будет меньше тормозить).
3. Создадим ряд «Период проекта» и выставим там числовой ряд «1;2;3…n». Это даже не переменные, а просто идентификаторы периода.
4. Создадим ряд «ДАТА» и запишем в него в колонке периода номер 1 (в нашем случае, B) следующую формулу
=DATE(start_year,start_month+B5-1,start_day)
Все. Задача решена – мы получили ряд дат, каждая из которых на месяц больше предыдущей. Число дней в месяце за нас пересчитал Excel. Обратите внимание, что переход на следующий год также совершен корректно: в формуле на F4 численное значение переменной month – 13.
В заключение добавим, что для корректного дисконтирования, особенно когда периоды и ставка дисконтирования велики, в модель вводят «нулевой период» – тот, в первый день которого приходят первые инвестиционные деньги. Если дата начала проекта и учетный день начала периода не совпадают, то «нулевой проект» будет иметь дробную длину.
Домашнее задание: используя полученные знания, рассчитайте динамический ряд дат с началом 1 октября 2011 года, где периодом будет квартал.
