Финансовое моделирование в 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, никто так и не сумел восстановить это 
«произведение искусства».