Финансовое моделирование в Excel

Дмитрий Жаров

Финансовое 
моделирование 
в Excel

Москва
2008

УДК 657.1.011.56
ББК 65.052.20в631
 
Ж34

ISBN 978-5-9614-0885-0
© Жаров Д.О., 2008
© ООО «Альпина Бизнес Букс», 2008

Ж34
Жаров Д.
Финансовое моделирование в Excel / Дмитрий Жаров. — М.: Альпина 
Бизнес Букс, 2008. — 170 с. 

ISBN 978-5-9614-0885-0

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

УДК 657.1.011.56
ББК 65.052.20в631

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

Редактор С. Кривошеин

Издано при содействии ООО ФК «ОТКРЫТИЕ»

Содержание

Предисловие ....................................................................................................................5
Введение ...........................................................................................................................7

Часть I. НАЧАЛО ............................................................................................................9
Общий обзор финансовых моделей ....................................................................... 9
С чего начать ........................................................................................................ 11
Оформление .......................................................................................................... 12
#ЦИРКУЛЯР! О циклических ссылках .............................................................. 16
Признаки хорошей модели. Чего не надо делать никогда .............................. 17

Часть II. СТРОИМ МОДЕЛЬ  .....................................................................................21
Понимай экономику процесса: драйверы и метрики  ...................................... 21
Архитектура модели: общие правила, бизнес-логика .................................... 25
Финансовая часть модели ................................................................................... 30
Баланс должен сходиться! Или «магия чисел» ................................................. 42
Операционная часть модели .............................................................................. 64
Капитальные затраты и амортизация........................................................... 69
Налоги: НДС.......................................................................................................... 70
Налоги: налог на прибыль .................................................................................... 79
Переложение данных на годы и кварталы ........................................................ 95

Часть III. ВСЯКИЕ ПОЛЕЗНОСТИ ...........................................................................97
Аналитика и другие полезности ........................................................................ 97
Специальные возможности и функции Excel ................................................. 100
«Считалки» ......................................................................................................... 104
Работа «в четыре руки» .................................................................................... 107
Проверка и стресс-тестирование ................................................................... 108
Отладка формул, исправление ошибок ........................................................... 111
F-клавиши. ........................................................................................................... 112
Диаграммы и графики ....................................................................................... 115
Важнейшие функции Excel ................................................................................ 117
Специальные средства для проверки моделей  ............................................... 124

Часть IV. ПРОДОЛЖАЕМ СТРОИТЬ МОДЕЛЬ ..................................................127
Архитектура модели: макроуровень ............................................................... 127
Прогнозирование в иностранной валюте ....................................................... 146
Моделирование в условиях инфляции .............................................................. 156

Заключение ..................................................................................................................159

Моим родителям 

Предисловие

В мире есть немало хороших книг по финансовым дисциплинам. В России же, 
к сожалению, их до сих пор недостаточно даже с учетом переводных изданий. 
И все же нехватка качественной финансово-экономической литературы, которая 
была особенно заметна в 1990-е годы, сегодня постепенно восполняется трудами 
российских экономистов. Книга, которую вы держите в руках, — хорошее тому 
подтверждение. 
С людьми, профессионально владеющими своим предметом, всегда приятно 
общаться — пусть даже и посредством чтения их книг. Дмитрий Жаров своим 
предметом владеет безукоризненно. Автор хорошо знает и понимает то, о чем 
пишет, поскольку рассуждает не об отвлеченных предметах, а о тех знаниях и 
опыте, которые были получены в реальной работе. Кроме того, он опирается на 
солидную образовательную базу, включая полученную в США степень MBA. 
Фактическая ценность материала книги подкрепляется и умением автора легко 
и доступно его изложить.
У книги «Финансовое моделирование в Excel» немало и других достоинств, 
которые, по моему убеждению, читатели обязательно оценят. От детального 
описания создания моделей автор переходит к анализу «большой картинки» — 
роли моделирования в практической работе финансиста. Он словно берет читателя 
за руку и проводит его кратчайшим курсом к цели, предупреждая о любых 
возможных проблемах и трудностях финансового моделирования. Такие переходы 
от общего к частному и наоборот помогают не только усвоить значение 
мелких деталей, но и дать общее понимание экономической природы описываемых 
явлений. 
Еще одним важным достоинством книги является то, что она не фокусируется 
лишь на теме построения моделей. Она охватывает целый спектр смежных 
экономических и деловых дисциплин: бухгалтерский учет, анализ бизнес-
процессов, финансы и т.д. Иными словами, название книги по большому счету 
далеко не полностью отражает все многообразие ее содержания! Автор не ограничивается 
описанием простого моделирования и раскрывает сущность таких 
сложных финансовых категорий, мало знакомых отечественному читателю, 
как, например, консолидация отчетности, причем делает это профессионально 

ФИНАНСОВОЕ МОДЕЛИРОВАНИЕ В EXCEL
6

и, что немаловажно, доступно. Причиной тому послужило не желание автора 
блеснуть эрудицией, а необходимость, ибо мастерство создания моделей заключается 
не только в виртуозном владении электронными таблицами, но и в 
понимании того, что и почему должно делаться именно так, а не иначе. 
Будучи профессионалом-практиком, автор точно знает, какими инструментами 
и в каком объеме необходимо владеть для грамотного финансового моделирования. 
В книге нет пустословных рассуждений — в ней собраны знания из 
нескольких областей экономики, что делает ее незаменимым пособием именно 
для практиков!
Пожалуй, самым большим плюсом книги является то, что она не только 
учит, но и заставляет думать: для многих проблем предлагается несколько вариантов 
решения с описанием всех достоинств и недостатков этих решений, 
что дает читателям возможность сделать свой собственный вывод по каждому 
из рассматриваемых вопросов. Думаю, автор сознательно пошел на то, чтобы 
книга выглядела не как учебник с одной «единственно правильной» моделью. 
Она скорее предстает руководством по логике построения составных частей 
моделей, а уж выбор количества и степени сложности этих частей остается за 
читателем. 
Не навязывая готовых шаблонов, «Финансовое моделирование в Excel» объясняет 
причины и последовательность тех или иных шагов, логику, которой следует 
руководствоваться. Эта книга — не простой набор схем, методов и подходов, 
а приглашение к совместному обсуждению, поиску решения, сотворчеству. 
Она отвечает не только на вопрос «как моделировать», но и «как моделировать 
эффективно», а также «зачем моделировать именно так, а не иначе». Более того, 
на основе реальных ситуаций автор наглядно показывает, как такие навыки могут 
и должны применяться на практике.
Дмитрий Жаров задал стандарт написания пособия для специалистов в области 
финансового моделирования, на который, вне всякого сомнения, будут 
равняться все последующие авторы, пишущие на эту тему.
Желаю автору и всем читателям побольше новых, хороших и полезных 
книг.

Михаил Сухобок,
Старший Управляющий директор 
ООО ФК «ОТКРЫТИЕ»

Введение

Мысль написать данное пособие возникла давно, после того как я довольно 
длительное время провел за созданием финансовых моделей в Excel. В процессе 
работы приходилось сталкиваться с моделями, созданными коллегами-
финансистами, и эти модели подчас казались или неубедительными, или не 
слишком профессиональными. В итоге я пришел к выводу, что накопленный 
мною опыт финансового моделирования непременно будет интересен и полезен 
моим коллегам.
Несмотря на обилие книг на рынке финансовой литературы, ни одно из представленных 
там изданий не отвечает критериям, которые используешь при выборе 
помощника в практической работе. Эта ситуация имеет место и при поиске 
книг по созданию моделей в Excel. Часто бывало, что, открыв многостраничный 
том, я вдруг видел, что половина книги посвящена математическим моделям 
оптимального распределения ресурсов, расчету площади круга и т. п. Какими бы 
интересными ни были эти вопросы, ни я, ни большинство финансистов никак 
не касаемся их в ежедневной работе. Поэтому многое приходилось постигать 
на практике самому или с помощью коллег. 
Иными словами, я написал книгу, которой несколько лет назад мне не хватало 
самому. Найди я нечто подобное тогда, сколько времени и сил удалось 
бы сэкономить! Впрочем, отсутствие полезных пособий помогло накопить собственный 
опыт, и это дает мне уверенность, что книга будет полезна тем собратьям 
по цеху, кто этот путь еще не прошел. Надеюсь, что она поможет им 
найти ответы на многие вопросы, сбережет время и станет еще одной ступенью 
на пути к дальнейшему профессиональному росту.
Кому адресована эта книга? Если вам регулярно приходится заниматься прогнозной 
финансовой отчетностью (для целей оценки, или управления оборотным 
капиталом), если вы понимаете финансы предприятия, основы бухгалтерского 
учета и налогообложения, хорошо знакомы с приложением Excel (это 
чрезвычайно важная оговорка, поскольку данная книга — не учебник по работе 
в Excel!), но при этом у вас возникают вопросы о том, как лучше написать модель, 
как сделать ее удобной в работе и превратить в реальный инструмент для 
принятия решений, значит, вы тот, для которого написана эта книга. 

ФИНАНСОВОЕ МОДЕЛИРОВАНИЕ В EXCEL
8

Работать проще, когда не приходится разгадывать «нетривиальную» логику, 
заложенную в модель коллегой, когда язык таблиц и расчетов, а также его представление 
понятны всем. Чем больше людей будет одинаково артикулировать 
свои финансовые гипотезы и расчеты, тем больше у них останется времени на 
обсуждение действительно важных деталей сделок. Книга наверняка поможет 
людям, не слишком хорошо знакомым с моделированием, и что-то полезное для 
себя найдут в ней даже опытные специалисты. 
Еще раз стоит отметить, что данная книга является практическим пособием 
и ни в коей мере не претендует на полный и всесторонний охват всех вопросов, 
так или иначе связанных с финансовым моделированием. Я не даю инструкций 
о том, как шаг за шагом (точнее, ячейка за ячейкой) строить одну модель и с ней 
идти по жизни дальше. Прочитав книгу, вы сможете построить модель с «прозрачной» 
логикой, которая по крайней мере не будет выглядеть устрашающей 
для других пользователей. 
Кроме того, я предлагаю максимально полный, на мой взгляд, набор альтернативных 
способов прогнозировать данные, связывать между собой, прописывать «
движок» модели и пр. Иначе говоря, я рассказываю о различных способах 
изготовления кирпичей, из которых складывается любая модель финансовой 
отчетности. Какие из них понадобятся, в каком количестве, какого размера и 
формы — решать вам в зависимости от конкретных задач.
Акцент в книге сделан на три важнейшие составляющие моделирования и 
прогнозирования: а) на понимание лежащей в основе бизнес-процессов экономической 
логики, которую необходимо перенести в модель; б) на понимание 
того, как экономика укладывается в формы бухгалтерской отчетности и 
в) непосредственно на механику моделей (о которой вообще мало пишется), 
программирующих экономику и бухгалтерию из двух предыдущих пунктов. 
В этом смысле на рынке пока не присутствует никакого иного пособия, подобного 
предлагаемой книге по охвату. 
Обращаю внимание читателей, что группы понятий лист/страница, столбец/
колонка, рабочая книга/файл/модель, прибыль/маржа, рентабельность/маржа 
используются как синонимы и почти всегда полностью заменяют друг друга — 
это будет понятно из контекста.

ЧАСТЬ I 
Начало

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

Финансовыми моделями называют самые разные вещи в зависимости от специализации «
моделиста». Профессионалам в области управленческого учета 
интересно посчитать себестоимость и варианты ее изменения, построить прогнозный 
баланс, оценить отклонения. Тех, кто занимается казначейскими вопросами, 
интересуют потребности в финансировании кассовых разрывов, расчеты 
риска (VaR). Те, кто торгует опционами, считают стоимость опционов в 
зависимости от изменений их сроков, волатильности стоимости актива и т. д. 
В Excel можно моделировать макроэкономические взаимосвязи, бизнес-задачи 
(основанные на теории игр, теории массового обслуживания/очередей, теории 
Маркова, линейном программировании) и многое другое. 
Теперь посмотрим на ситуацию с прикладной точки зрения. Перед вами стоит 
задача спрогнозировать работу предприятия на несколько периодов вперед 
при таком-то наборе вводных параметров. Спрогнозировать — значит сказать, 
какая будет выручка, какие будут промежуточные метрики (EBITDA, EBIT), какой 
будет чистая прибыль. Одновременно надо учитывать, что вместе с ростом 
выручки будет расти потребность в оборотном капитале, и не забыть, что рост 
выручки почти всегда предполагает вложения в основные средства. 
«…Так, с чего начнем?.. Пожалуй, с выручки. Ах да, ведь надо еще учесть, что 
наша фирма планирует постепенно ужесточить кредитную политику. И не забыть 
учесть выплаты по кредитам. Кстати, в следующем году должен вернуться 
долгосрочный заем, предоставленный “дочке”. Интересно, придется ли брать 

ФИНАНСОВОЕ МОДЕЛИРОВАНИЕ В EXCEL
10

кредиты или обойдемся так? М-да, все по отдельности понятно, а вот как теперь 
увязать это в единое целое? Так, вроде все увязал, ничего не забыл... вот только 
баланс не сходится, придется кредитами регулировать. Ой, теперь в другую сторону 
не сходится! Уменьшу-ка я собственный капитал — вроде все сошлось. Но 
теперь непонятно, откуда берется этот самый собственный капитал — он уже 
утратил всякую связь с реальностью из-за правок и подгонок!.. А если я здесь 
немного изменю параметр, то все начинать сначала?»
Примерно таков ход мыслей человека, столкнувшегося с необходимостью 
смоделировать финансовый прогноз. Знакомая ситуация? Если так, то вы купили 
эту книгу вовремя. В ней как раз идет речь о том, как строить модели так, 
чтобы сохранялась бизнес-логика и при этом все сходилось без подгонок. 
Сразу хотим предостеречь: не пытайтесь превратить Excel во всемогущий 
инструмент обработки и хранения данных! В моей практике встречались ситуацию, 
когда Excel был явно не по назначению перегружен. В одном случае 
это приложение использовалось как инструмент бюджетирования в крупной 
компании, занимающейся оптовыми продажами большого ассортимента товаров. 
В таблицу при этом вносились плановые данные по закупкам, логистике 
и продажам по очень многим позициям, потом на базе этого считалась общая 
прогнозная прибыль по компании и ее соответствие плану. Модель «весила» 
больше 150 мегабайт (не считая кучи вспомогательных моделей), создавалась 
по кускам и поддерживалась разными людьми, с периодичностью раз в квартал 
эти куски сшивались в единое целое. Модель работала не очень устойчиво, 
периодически в ней что-нибудь «падало». Компания продавала продукцию 
широчайшего ассортимента, и эта модель больше походила на базу данных, 
которую приспособили под решение финансовых функций.
Второй случай, еще более показательный, имел место в одной американской 
компании, где довелось работать автору этой книги. Модель размером 280 мегабайт 
хранилась по кускам на четырех серверах и должна была при этом как-
то работать. Создавал ее один человек более года. По словам очевидцев, этим 
подвижником-одиночкой была проделана колоссальная работа! Модель была 
уже почти готова, как вдруг рассыпалась на куски. Здравый смысл подсказывает 
такую логику катастрофы «пикирующего моделиста»: сначала отказал один 
из серверов, на котором хранился важный кусок модели, из-за этого перестали 
работать другие куски — и так далее. Несмотря на то, что компания весьма 
успешно работала как раз в области IT, никто так и не сумел восстановить это 
«произведение искусства». 

Часть I. Начало
11

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

С чего начать

Многих людей, включая меня, раздражает, когда вместо «спортзал» говорят 
«фитнес-клуб», вместо «бухучет» — «эккаунтинг». Однако есть случаи, где использование 
иностранных слов не просто оправданно, но и желательно. К таким 
случаям относится и работа в Excel. Я настоятельно рекомендую читателям установить 
англоязычную версию этой программы, потому как написание формул 
на русском оказывается делом весьма непростым и некоторые функции приобретают 
довольно экзотичный синтаксис — например, ДВССЫЛ, БЗРАСПИС, 
ПУО. Сам я использую англоязычную версию, но на всякий случай буду приводить 
формулы на обоих языках.
Книга ссылается на версии Excel 2000 и 2003. Версия 2007 имеет несколько 
отличающийся интерфейс, и если у вас стоит она, то необходимо будет вносить 
небольшие коррективы относительно поиска некоторых функций, но логика 
моделирования от этого никак не изменится. 
Выбор версии Excel не принципиален, но ниже 2000-й версии опускаться все 
же не следует. С точки зрения пользователя-финансиста, она особо ничем не 
уступает более поздним версиям, кроме единственной, но весьма существенной 
детали — в Excel 2000 нет функции Break Links (Разрыв связей). Порой при копировании 
данных из одной книги в другую вы незаметно «тащите» за собой и 
ссылку на другой источник данных. Если создаваемый вами файл небольшой, 
то ячейки со ссылками можно найти и вручную. Но с увеличением объема модели 
сложность поиска увеличивается многократно и становится порой почти 
неразрешимой задачей. Можно пойти на официальный сайт Microsoft и загрузить 
себе такую функцию, но неопытный пользователь вряд ли отыщет ее без