Подготовка книги Excel к работе
Создайте свой стиль
Мой опыт работы в Excel начался более 10 лет назад. Когда я создал свою первую таблицу за несколько минут, мне все показалось простым, легким и понятным. Одна ячейка складывается с другой. Внизу получается результат. Быстро и просто, и все-таки чего-то не хватает… Красоты и яркости, наглядности! Да, именно этого ей не хватало.
Простая таблица производит удручающее впечатление (особенно когда она занимает весь лист А4). Я тут же принялся заливать цветами названия столбцов и менять шрифты. В итоге получилось что-то похожее на вот такую таблицу.
Рисунок 1. В поисках своего стиля
Разница очевидна, и нет предела совершенству. Когда у нас одна простая таблица, ее можно быстро отформатировать. Ситуация меняется, когда наши расчеты исчисляются сотнями ячеек. Тогда время, потраченное на форматирование книги или листа, состоящего из множества таблиц, может составить несколько часов.
Кроме того, всегда есть место человеческому фактору. Например, у вашего руководителя хороший вкус, и он предлагает другое оформление расчетов или изменение состава и порядка данных. При этом время на выполнение задания ограничено или его нет. Возможно, вы уже оказывались в подобной ситуации. Судорожно ругая всех и вся, вы начинаете форматирование, а руководство звонит каждые 10 минут и спрашивает: «Что ты там возишься? Давай быстрее…»
Благо для нас, начиная с версии Excel 2007, появился отличный инструмент «Стили ячеек». Он находится «Ленте» -> «Основные вкладки» -> «Главная».
Используя данный инструмент, легко создать свой стиль и быстро менять его под нужды потребителей (кем бы они ни были).
Итак, создаем свой стиль. Нажмите на иконку «Стили ячеек». Во вплывающем окне выберите «Создать стиль ячейки…». В новом окне «Стиль» создайте свой стиль для каждого типа ячеек. Например, Заголовок 1 или ИТОГО.
Рисунок 2. Вкладка «Стили ячеек»
Рисунок 3. Окно «Стиль»
Для наглядности можно сделать так. На отдельном листе создайте стили для всех типов ячеек, которые вы будете использовать в своих расчетах. Слева сделайте нужный вам формат ячеек, а справа напишите наименования формата. Потом выберите отформатированную ячейку и нажмите «Стили ячеек» -> «Создать стиль ячейки…» и окно «Стиль», в графе «Имя стиля» удалите «Стиль 1» и напишете новое имя для этого стиля. Например, «Заголовок 1».
Рисунок 4. Создаем свой стиль с помощью «Стиля ячеек»
Рисунок 5. Пример готового своего стиля
Поздравляю, вы создали свой первый стиль. Осталось только его применить. Каждый раз создавая новые таблицы, используйте нужный вам стиль. Пример:
Рисунок 6. Применяем свой стиль.
Шаг 1: создаем простую таблицу
Рисунок 7. Шаг 2: выделяем верхнюю строчку таблицы («Шапку») и применяем стиль «Таблица»
Теперь у нас для всех заголовков таблиц применяется стиль «Таблица». Его можно легко менять – достаточно навести курсор мышки на нужный вам стиль и нажать правую клавишу мыши. Во всплывающем окне выберите команду «Изменить». Появится окно «Стиль», нажмите «Форматировать». Теперь вы можете редактировать.
Рисунок 8. Шаг 3: скопируем таблицу, вставим ее ниже, так, чтобы у нас получилось две таблицы; теперь изменим стиль «Таблица» – поменяем цвет с синего на красный
Рисунок 9. Шаг 4: изменяем стиль «Таблица»
Рисунок 10. Шаг 5: меняем цвет на красный
Рисунок 11. Шаг 6: две готовые таблицы с красными «шапками»
Таким образом, всего за несколько секунд вы можете поменять форматирование вашего документа полностью. Это существенно упрощает работу и экономит время, когда вы делаете множество повторяющихся процедур.
Блуждая по просторам интернета, я нашел отличный сайт, посвященный финансовому моделированию – [битая ссылка] http://www.fimodo.com. На этом сайте выложен отличный макрос (приложение 1), помогающий быстро создать свой стиль. Вот ссылка на него: http://www.fimodo.com/2011/07/using-cell-styles-for-financial-modelling/.
Пройдя по ссылке, вы найдете подробную инструкцию, как использовать макрос. Поэтому я не буду его дублировать. Спасибо автору Damian Matacz.
Горячие клавиши в Excel
Я настоятельно всем рекомендую освоить и использовать горячие клавиши. Это в разы повышает скорость работы. Есть отличный сайт, он посвящен Excel – [битая ссылка] www.planetaexcel.ru. Я всем рекомендую. Его автор, Николай Павлов, собрал 57 лучших сочетаний клавиш в Excel. По этой ссылке вы сможете их скачать ([битая ссылка] http://www.planetaexcel.ru/techniques/11/126/).
Рисунок 12.1. 57 лучших сочетаний клавиш в Excel
(Николай Павлов, planetaexcel.ru)
Рисунок 12.2. 57 лучших сочетаний клавиш в Excel
(Николай Павлов, planetaexcel.ru)
Рисунок 12.3. 57 лучших сочетаний клавиш в Excel
(Николай Павлов, planetaexcel.ru)
Рисунок 12.4. 57 лучших сочетаний клавиш в Excel
(Николай Павлов, planetaexcel.ru)
Панель быстрого доступа
Панель быстрого доступа позволяет вам использовать сочетания клавиш Alt + цифры от 1 до 0. Вы можете запрограммировать 10 команд-кнопок, которые всегда будут под рукой. Ниже я приведу пять моих горячих клавиш. Почему только пять? Все просто: левой рукой я нажимаю на клавиатуре нужную мне комбинацию, а в правой руке я держу мышь и выбираю нужные мне ячейки.
Рисунок 13. 5 горячих кнопок на панели быстрого доступа
Настроить панель быстрого доступа очень просто. Кликните на ней мышкой и выберите «Другие команды..» (см. рисунок 14).
Рисунок 14. Настройка панели быстрого доступа
Принципы финансового моделирования
Существует множество рекомендаций, или принципов, которые желательно соблюдать при создании финансовых моделей. Здесь я приведу свои принципы и покажу другие авторские подходы. Во многом они пересекаются. Навязывать вам ничего не буду. Каждый сам вправе делать свой выбор. Применять или не применять их – выбор за вами. Все представленные ниже принципы носят рекомендательный характер.
Мои принципы финансового моделирования
– Доступность – финансовая модель должна быть доступной для понимания всех пользователей, независимо от их уровня владения Excel.
– Гибкость – финансовая модель должна быстро и легко меняться под текущую ситуацию, новые обстоятельства и потребности клиентов, независимо от первоначальных параметров, сценариев и допущений.
– Наглядность – результаты ваших расчетов должны быть красивыми, краткими, понятными с первого взгляда.
Помните, в большинстве случаев вы создаете финансовую модель для других пользователей. Ваши расчеты наверняка будут проходить проверку или аудит. Поэтому, создавая модель, вы всегда должны помнить о других ее пользователей. Слишком простая модель говорит о слабой проработке проекта, сложная может оттолкнуть потенциальных инвесторов. Не у всех есть время на изучение сложных моделей. Итак, приемы, которыми вы можете пользоваться, чтобы повысить уровень доступности для понимания вашей финансовой модели.
В большинстве случаев финансовая модель строится для поисков нужных условий для реализации проекта. Поэтому гибкость финансовой модели очень важна. Например, изменились условия кредитования или появились новые статьи расходов или доходов. Иногда поиск вариантов и идет параллельно переговорному процессу. В считанные часы появляются новые данные, и финмодель должна быть готова к таким переменам. Все ждут от финмодели результата. Гибкость модели – скорость получения результатов после ввода новых параметров.
Наглядность и презентабельность финансовой модели – это самая сложная часть. Сделать правильный вывод и грамотно его подать – залог успеха любого финансового модельера. Порой я пренебрегаю наглядностью. За что несу справедливое наказание – отправляюсь на доработку. Когда модель построена, ее создатель уже «все знает» о проекте. Поэтому ему трудно понять, что «кто-то еще может этого не знать, ведь это же элементарно». На этом этапе важно вспомнить, для чего и для кого делается финмодель. Вспомнили, а теперь наведем «красоту».
Вы можете изучить другие принципы на [битая ссылка] http://www.corality.com и http://www.fast-standard.org/.
Структура финансовой модели
Перед тем как начать моделировать, желательно понять конечную цель. Как гласит второй навык С. Кови: «Начинайте, представляя конечную цель». Применяя этот навык к финансовому моделированию, вы существенно сократите время на построение финмодели.
Предположим, вы хотите привлечь инвестиции в свой проект. Вы определись с инвестором или кредитором. Уточнили у него, какие показатели проекта должны быть в финансовой модели и их примерные значения. Допустим, инвестора в первую очередь интересует IRR проекта (внутренняя норма доходности). Зная это, вы раскладываете данный показатель на составляющие и избегаете ненужных расчетов, например, вам уже не надо считать коэффициенты ликвидности или платежеспособности.
Рисунок 15. Пример определения конечной цели на показатели IRR
Я настоятельно рекомендую перед началом расчетов определиться с составом показателей и детализацией финмодели. Иначе можете сделать никому не нужную работу.
Далее нужно выбрать структуру модели. Все модели можно условно разделить на два типа:
• вертикальные (пример – «Альт-Инвест»);
• горизонтальные (пример – Corality).
Рисунок 16. Внешний вид вертикальной структуры финмодели
Рисунок 17. Внешний вид горизонтальной структуры финмодели
Принцип разделения прост. Все зависит от хода расчетов и количества листов. При вертикальном типе финмодели все расчеты выполняются на одном листе. Потом из него делаются выдержки (ссылки) на резюмирующие листы.
Рисунок 18. Примерная структура вертикального типа финмодели
Горизонтальный тип финмодели предполагает множество расчетов. Для каждого вида расчета делается свой лист. Например, расчет кредитов идет на отдельном листе «Финансирование».
Рисунок 19. Примерная структура горизонтального типа финмодели
Выбор типа финансовой модели, как правило, зависит от уровня знания Excel. По моему наблюдению, опытные пользователи предпочитают вертикальный тип, менее опытные – горизонтальный. Горизонтальный тип более наглядный, но требует постоянного внимания от создателя модели. Потому что большое число ссылок в разы повышает вероятность появления ошибок. Это связано с постоянными переходами с листа на лист в поисках нужной информации. По этой же причине аудит финмоделей горизонтального типа более трудный.
Я предпочитаю вертикальный тип финмоделей. Он позволяет быстро строить самые сложные модели и проводить их аудит. Используя инструмент «зависимости формул – влияющие/зависимые ячейки» (см. закладку «Формулы» на ленте), вы можете увидеть взаимосвязанные ячейки на одном листе. Для того чтобы повысить наглядность модели, я использую структуру и группировку строк/столбцов.
Типовая структура моих финансовых моделей представлена на рисунке 20.
Рисунок 20. Моя типовая структура финмоделей
Окончательно определиться с составом показателей проекта, с выбором структуры финансовой модели можно с помощью древовидной диаграммы – когда вы идете от конца к началу. От значения ключевых показателей проекта к исходным данным. С каждым шагом вы опускаетесь на новый уровень детализации. Таким образом, вы получаете видение и понимание вашего проекта. Вы начинаете понимать, какие исходные данные вам нужны и как они связаны с ключевыми финансовыми или инвестиционными показателями. Приведу пример такой детализации. На нем показано, как связана выручка оператора полигона твердо-бытовых отходов с численностью населения на территории обслуживания.
Рисунок 21. Пример взаимосвязи исходных данных и выручки
Итак, первым листом для любой финмодели будут исходные данные. Конечно такие данные могут меняться или уточняться, поэтому в модели лучше предусмотреть лист «Мастер сценариев». В нем предусмотреть все возможные отклонения от базовых параметров.
Для более наглядного представления влияния изменения того или иного параметра создается отдельный лист – «Анализ чувствительности». В дальнейшем он поможет вам лучше понимать «узкие» моменты и слабые/сильные стороны проекта. Благодаря этому вы сможете подобрать оптимальные условия реализации проекта.
Как вариант можно объединить данные трех листов («Мастер сценариев», «Анализ чувствительности», «Резюме») в одном листе «Резюме». Это возможно, когда у нас мало меняющихся переменных и нет сложных взаимосвязей. Тогда вы повысите наглядность своей финмодели в разы. На одном листе сможете моделировать разные сценарии и видеть, как они влияют на показатели проекта.
Следующий важный момент – распределение исходных данных по времени. Как правило, на первоначальном этапе построения финмодели нет точных данных. Обычно есть годовые цифры или общие цифры (например, размер инвестиций – Х руб.). Поэтому, когда вам потребуется построить ФМ по кварталам или по месяцам, вы должны разнести общие цифры по соответствующему временному периоду. Для этого делается отдельный лист «Распределение по времени исходных данных (РИД)». Благодаря этому листу вы сможете быстро переходить от месячной отчетности к годовой и наоборот. А также это позволяет предусмотреть несколько временных периодов в вашей модели. Например, первые 5 лет все расчеты будут помесячные, последующие 20 лет – поквартальные или годовые.
Самый важный лист – «Расчет», на нем будут производиться все расчеты. Это основной лист во всей модели. Из него будут производиться выдержки для резюмирующих листов «Отчетность», «Резюме».
В данном курсе на листе «Резюме» я покажу, как сделать «Мастер сценариев» и «Анализ чувствительности» на одном листе. В нем можно создать несколько вариантов сценариев проекта.
В следующих главах я научу вас, как создавать и заполнять все вышеуказанные листы.