Текст книги "Финансовое моделирование в Excel"

#ЦИРКУЛЯР! О циклических ссылках

Циклическая ссылка (circular reference) – это почти всегда плохо, т. к. является симптомом ошибки. Самый простой пример: в ячейке A1 вы пишете формулу =A1+B1. Вот вам и циклическая ссылка. Как она получилась? Да по ошибке, никак более! Искать эту ошибку и исправлять – вот и все решение. Excel, кстати, помогает вести такой поиск. Приведенная ошибка очевидна, поэтому ее легко найти, однако бывает, что она спрятана за несколькими уровнями вычислений, и добраться до нее непросто.

А теперь рассмотрим простой пример: фирма продает товар за 100 рублей, причем его полная себестоимость составляет 90. После налога на прибыль остается 7,6 рубля чистой прибыли, которую теперь можно использовать на какие-то интересные нам нужды. Допустим, что в тот же период фирме надо инвестировать 17,6 руб. Что у нас получается? А вот что:

• 7,6 – прибыль;

• (17,6) – инвестиция;

• (10,0) – дефицит, т. е. потребность в привлечении денег.

Финансировать потребность в деньгах можно многими путями, но предположим, что вы решили взять кредит. Кредит – вещь небесплатная, а значит, надо бы заложить в расчет проценты (пусть для простоты это будет ровно 1 рубль). Проценты теперь всей своей суммой уменьшают налогооблагаемую прибыль, т. е. полная себестоимость оказывается не 90 рублей, а 91 рубль. Следовательно, и налог на прибыль будет меньше, а это значит, что изменится чистая прибыль, которую мы уже учли при расчете потребности в кредите. А это значит, что и расчет потребности в кредите был неверен, а раз так, то надо пересчитать и величину процента по кредиту… Такие сложные расчеты в самом элементарном примере! А если ставка изменится, то все заново пересчитывать? Увы и ах!

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

В панели инструментов идем на Tools > Options > Calculations (Сервис > Параметры > Вычисления) и ставим галочку в окошко Iterations (Итерации). Теперь Excel все «черновую» работу по нахождению необходимой суммы кредита выполняет автоматически. По умолчанию приложение готово повторять такой цикл 100 раз – этого обычно хватает. Если модель действительно большая и содержит не одну циклическую ссылку, то бывает, что приходится и увеличивать количество итераций (верхний предел ограничен 10 000).

Признаки хорошей модели. Чего не надо делать никогда

Хорошая модель – это такая модель, которая:

• правильно отражает бизнес-логику и экономику процессов;

• позволяет осуществлять управление с помощью заданного набора вводных параметров;

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

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

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

Основные признаки хорошей модели:

• Блок вводных параметров (inputs) расположен отдельно от страниц расчетов.

• Используется принцип: одна строка – одна формула. Это означает, что в каждой строке модели нужно использовать только одну формулу, чтобы ее можно было легко скопировать из первой до последней ячейки строки простым протягиванием (разумеется, этот принцип не распространяется на итоговый последний столбец, если таковой имеется). Принцип этот должен применяться в 99,9 % случаев, кроме тех очень редких исключений, когда его использование делает формулу строки слишком громоздкой.

• Используется единый формат на всех страницах модели (см. раздел «Оформление»), не используются варианты «индейской» раскраски.

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

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

А теперь немного про то, чего не надо делать никогда.

• Никогда не вставляйте цифры в ячейки с формулами. Этот фокус называется «плаг» (от английского plug – пробка, заглушка, затычка) и используется теми, кто не может свести модель к желаемому результату – например, добиться сбалансированности актива и пассива баланса. Выглядит эта гадость, например, таким образом =A10+B12/B10+2695,874. Это конец! Если вы имеете дело с большой моделью и такой плаг спрятан где-то в ее недрах, то найти его практически невозможно, по крайней мере вручную (про автоматизированный поиск рассказывается в конце третьей части книги). Можно такой плаг убрать «хирургически», просто скопировав формулу из первой ячейки в каждой строке во все ячейки, находящиеся от нее справа. Вставлять плаг означает открывать дорогу последующим проблемам. Самое неприятное с плагами – все они рано или поздно обнаружатся и дадут о себе знать.

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

• Не вставляйте в помесячные данные столбцы с годовыми итогами; это простое правило вытекает из уже озвученного важнейшего правила, которое гласит: «одна строка – одна формула»

• Не пишите длинные названия страниц файла Excel, тем более состоящие из нескольких слов и/или цифр, – при ссылке на данные из этой страницы понять формулу будет решительно невозможно. Например, вместо имени «Годовой отчет по группе за 2007» назовите страницу «ОтчетГруп07».

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

• Употребляйте циклические ссылки исключительно редко и только в случаях, подобных описанному в предыдущем разделе. Самая серьезная проблема циклических ссылок состоит в том, что наряду с полезной по ошибке или невнимательности возможно появление одной или нескольких ненужных, а значит, вредных циклических ссылок. И найти их при разрешенном к использованию режиме итераций будет невозможно. Что делать? Периодически проверяйте вашу модель – отключайте режим итераций и смотрите, не появились ли циклические ссылки там, где их присутствие не планировалось.

• Не удаляйте ни одной ячейки, предварительно не убедившись, что на нее не ссылаются другие ячейки. Это очень просто проверить с помощью блока кнопок «Formula Auditing» (Зависимости) на панели инструментов.

• Отсюда вытекает другое важнейшее правило, которое будет понятнее после прочтения книги: не соединяйте блоки модели большим количеством связей (ссылок), ограничьтесь их минимально необходимым количеством. Это становится очень важным при «вырезании» отдельных блоков, их замене и т. д.

Часть II
Строим модель

Понимай экономику процесса: драйверы и метрики

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

Какова бизнес-модель описываемого процесса? Откуда деньги приходят, как используются, как создается добавленная стоимость, как она потом распределяется? Если вы пытаетесь описать процесс, который сами понимаете слабо, обратитесь за помощью к тому, кто его придумал или хорошо понимает.

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

Как в этом случае строится модель? У производственных компаний есть плановая себестоимость – для нас это некая данность (т. е. как раз те самые «синие» цифры, о которых уже сказано в разделе про форматирование), менять которую мы не будем. Аргументом (т. е. такими же «синими» цифрами) мы здесь можем выбрать процент наценки (mark up), который будет обеспечивать заданную небольшую (1–5 %) прибыльность. Поскольку товар продается напрямую в материнскую компанию, то выручка производственных предприятий становится автоматически прямой себестоимостью материнской компании (т. е. визуально в модели это цифры зеленого цвета – прямые ссылки).

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

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

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

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

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

Что будет являться драйвером в каждом конкретном случае? Вопрос не так прост, как кажется. Он вытекает из гораздо более глобального вопроса о том, как создается стоимость. После того, как определились с тем, как деньги в компанию приходят, помогают создать добавленную стоимость и уходят (вспомните формулу Маркса «деньги – товар – деньги»), надо понять, что заставляет их двигаться именно в таких объемах, именно по такому маршруту и именно с такой скоростью. Эти движущие силы и являются драйверами.

Например, для сотового оператора основными драйверами стоимости являются количество абонентов и ARPU (average revenue per user – средняя выручка на одного клиента), или, если выразиться проще, количество проданного товара и его цена. Количество абонентов и то, сколько денег они периодически платят оператору, зависят от множества факторов, из которых основные – сумма, истраченная на рекламу и продвижение услуг, количество передающих станций, пропускная способность устройств, обрабатывающих сигналы (коммутаторы, ретрансляторы и пр.), эффективность устройств, поддерживающих оказание более дорогих услуг, чем просто звонки и отправка SMS. Причем каждый из этих факторов (драйвер более низкого уровня) влияет на основные драйверы по-своему. Например, количество денег, потраченных на рекламу, влияет на количество новых абонентов, и влияние это описывается некой зависимостью – например, такой: каждые 10 рублей, потраченные на рекламу в январе, обеспечивают двух новых абонентов в феврале и еще одного – в марте. Тогда мы имеем следующее: потратив в январе 10 рублей (абстрагируемся пока от капитальных затрат, связанных с установкой передающих станций и коммутаторов), мы получаем двух новых абонентов в феврале и одного в марте (т. е. в марте у нас будет уже три абонента). Предполагая, что ARPU на одного абонента составляет, скажем, 100 рублей в месяц, получаем, что выручка за февраль составит 200 рублей, а за март – 300 рублей. Откуда мы взяли 100 рублей? Например, отсюда: наш конкурент в этой же отрасли имеет ARPU в 200 рублей в месяц, но этот конкурент работает давно, у него лояльные и обеспеченные клиенты и пр. Вот маркетологи и оценивают, что в обозримом будущем для расчетов следует брать не более 50 % от ARPU конкурента.

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

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

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

В завершение темы метрик и драйверов приведем простую схему-модель драйверов стоимости производственной компании (рис. 2.1).

Совершенно очевидно, что подобное разложение драйверов «по винтикам» возможно не только в таком, но и в любом другом виде: например, по клиентам (количество клиентов в отношении к прибыли на одного клиента), по филиалам (количество филиалов в отношении к прибыли на один филиал) и т. п.

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

Архитектура модели: общие правила, бизнес-логика

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

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

Бывают ситуации, когда какие-то модули с относительно неизменными результатами, но обобщающие огромный блок информации имеет смысл выделять в отдельный файл, не привязанный к основной модели. Данные расчетов в таком случае будут представлены в основной модели в виде численных значений синим цветом (т. е. с ручным вводом данных – специалисты называют такие данные английским термином hard-coded inputs). Разбиение одной модели на несколько файлов является довольно рискованным подходом, т. к. связи между файлами имеют тенденцию периодически «ломаться», разрушая результат кропотливого труда. Хотя бывают случаи, когда такой риск оправдан защитой основной модели от перегрузок.

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

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

Общая логика модели может выглядеть, например, так:

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

Вводные параметры (inputs) базируются на: а) исторических данных (косвенно) и б) прогнозах развития компании в будущем. Очевидно, что прогнозы не могут быть взяты с потолка, а каким-то образом должны отражать и то, что было раньше, поэтому блок «Прогнозные данные» пунктиром связан с блоком «Исторические данные». Готовые вводные параметры задают прогноз отчета о прибылях и убытках (в основном через динамику выручки и маржи), а также оборачиваемость многих статей активов и пассивов (показано на рисунке пунктиром). Большинство статей баланса так или иначе будет определяться показателями отчета о прибылях и убытках (выручкой и себестоимостью или только выручкой), поэтому баланс привязан к отчету о прибылях и убытках. Кроме того, есть важнейшая прямая связь между этими двумя отчетами: чистая прибыль из отчета попадает в раздел «Собственный капитал» баланса (невероятно важное уравнение, которое нередко забывают: накопленная прибыль на конец периода равна накопленной прибыли на начало периода плюс чистая прибыль за период минус дивиденды). Блок «Финансирование» стоит на приведенном рисунке особняком – помимо долгосрочных кредитов/займов есть потребность в краткосрочных, главная задача которых заключается в финансировании текущих кассовых разрывов (подробнее см. раздел про то, как сводить баланс). Отчет о движении денежных средств строится на основании отчета о прибылях и убытках и баланса, а не наоборот (указано направлением стрелок)! Блоки аналитики и пользовательских отчетов определяются в каждом конкретном случае и могут брать для анализа данные из любого из блоков, расположенных выше.

Располагать информационные блоки можно по листам (максимально сохраняя при этом линейность):

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

Если данных немного, или они считаются в других файлах, или отсутствуют большие вспомогательные таблицы, то небольшую модель вполне можно разместить на одной странице снизу вверх (но не слева направо!):

Нормальной является ситуация, когда все прогнозные вычисления начинаются с отчета о прибылях и убытках, продолжаются бухгалтерским балансом и заканчиваются отчетом о движении денежных средств. Обратите внимание, что отчетом о движении денежных средств прогнозная финансовая отчетность должна именно заканчиваться, а не начинаться – этот отчет является производным отчета о прибылях и убытках и бухгалтерского баланса. Неоднократно приходилось встречаться с моделями, где все делалось наоборот: ситуация напоминала строительство дома начиная с крыши, а не с фундамента. Фундаментом любой модели является именно отчет о прибылях и убытках, и главное, что из него должно быть понятно, – рост выручки и рентабельность операций. Именно выручка от основных операций и та ее часть, которую компания может оставить себе в виде маржи, условно определяют половину экономической части каждой конкретной компании. Остальную половину определяют данные из баланса, а именно – оборачиваемость активов и структура капитала. И это все! Не бывает таких ситуаций, при которых работу над моделью надо начинать с отчета о движении денежных средств (если только вы не ставите перед собой задачу подгонки результатов).