Финансовое моделирование в Excel - Дмитрий Жаров

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

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

Часть I

Начало

Общий обзор финансовых моделей

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

Теперь посмотрим на ситуацию с прикладной точки зрения. Перед вами стоит задача спрогнозировать работу предприятия на несколько периодов вперед при таком-то наборе вводных параметров. Спрогнозировать – значит сказать, какая будет выручка, какие будут промежуточные метрики (EBITDA, EBIT), какой будет чистая прибыль. Одновременно надо учитывать, что вместе с ростом выручки будет расти потребность в оборотном капитале, и не забыть, что рост выручки почти всегда предполагает вложения в основные средства.

«…Так, с чего начнем?.. Пожалуй, с выручки. Ах да, ведь надо еще учесть, что наша фирма планирует постепенно ужесточить кредитную политику. И не забыть учесть выплаты по кредитам. Кстати, в следующем году должен вернуться долгосрочный заем, предоставленный “дочке”. Интересно, придется ли брать кредиты или обойдемся так? М-да, все по отдельности понятно, а вот как теперь увязать это в единое целое? Так, вроде все увязал, ничего не забыл… вот только баланс не сходится, придется кредитами регулировать. Ой, теперь в другую сторону не сходится! Уменьшу-ка я собственный капитал – вроде все сошлось. Но теперь непонятно, откуда берется этот самый собственный капитал – он уже утратил всякую связь с реальностью из-за правок и подгонок!.. А если я здесь немного изменю параметр, то все начинать сначала?»

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

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

Второй случай, еще более показательный, имел место в одной американской компании, где довелось работать автору этой книги. Модель размером 280 мегабайт хранилась по кускам на четырех серверах и должна была при этом как-то работать. Создавал ее один человек более года. По словам очевидцев, этим подвижником-одиночкой была проделана колоссальная работа! Модель была уже почти готова, как вдруг рассыпалась на куски. Здравый смысл подсказывает такую логику катастрофы «пикирующего моделиста»: сначала отказал один из серверов, на котором хранился важный кусок модели, из-за этого перестали работать другие куски – и так далее. Несмотря на то, что компания весьма успешно работала как раз в области IT, никто так и не сумел восстановить это «произведение искусства».

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

С чего начать

Многих людей, включая меня, раздражает, когда вместо «спортзал» говорят «фитнес-клуб», вместо «бухучет» – «эккаунтинг». Однако есть случаи, где использование иностранных слов не просто оправданно, но и желательно. К таким случаям относится и работа в Excel. Я настоятельно рекомендую читателям установить англоязычную версию этой программы, потому как написание формул на русском оказывается делом весьма непростым и некоторые функции приобретают довольно экзотичный синтаксис – например, ДВССЫЛ, БЗРАСПИС, ПУО. Сам я использую англоязычную версию, но на всякий случай буду приводить формулы на обоих языках.

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

Выбор версии Excel не принципиален, но ниже 2000-й версии опускаться все же не следует. С точки зрения пользователя-финансиста, она особо ничем не уступает более поздним версиям, кроме единственной, но весьма существенной детали – в Excel 2000 нет функции Break Links (Разрыв связей). Порой при копировании данных из одной книги в другую вы незаметно «тащите» за собой и ссылку на другой источник данных. Если создаваемый вами файл небольшой, то ячейки со ссылками можно найти и вручную. Но с увеличением объема модели сложность поиска увеличивается многократно и становится порой почти неразрешимой задачей. Можно пойти на официальный сайт Microsoft и загрузить себе такую функцию, но неопытный пользователь вряд ли отыщет ее без проблем. Об альтернативном методе решения этой проблемы мы расскажем далее.

Начните работу с активизации дополнительных функций Excel, которые по умолчанию остаются неустановленными, т. к. редко используются обычным пользователем. Например, есть замечательная функция EOMONTH (КОНМЕСЯЦА), которая позволяет быстро прописать временной ряд таким образом, что каждый месяц будет автоматически заканчиваться своим последним днем, т. е., когда надо, будет появляться 31-е число, когда надо – 30-е число, а в феврале будет 28 или 29 дней. Какое это имеет отношение к финансам? Самое прямое! И вы в этом убедитесь, когда речь зайдет об операционных составляющих моделей.

Устанавливаются дополнительные функции просто: на панели инструментов пройдите по пути Tools > Add-Ins (Сервис > Надстройки), а далее устанавливайте все, кроме, пожалуй, пакета для работы с VBA (если вы не собираетесь с ним работать). Это «все» вам, скорее всего, не понадобится в полном объеме, но и мешать не будет.

Еще одна важная деталь: добавьте блок кнопок Formula Auditing (Зависимости) на панель инструментов. Этот блок позволяет быстро находить, откуда в конкретную ячейку пришла ссылка и где данные ячейки используются в дальнейшем. В Excel 2003 такой блок выглядит следующим образом:

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

Оформление

Разве оформление – столь важный вопрос, что с него стоит начинать? Да, гораздо более важный, чем может показаться!