Что нужно знать из Excel для работы в финансовой модели
Если вы просматриваете эту страницу, значит, вы уже скачали финансовую модель для планирования и анализа бизнеса, и теперь приступаете к ее заполнению, или же вы решили по моему примеру создать свой бизнес-план со своим файлом для расчетов финансовой части. В любом случае, пост о фишках Excel, которые я использую в своей модели, не будет для вас лишним.
В этом посте мы рассмотрим:
1. Фильтры навигации, созданные мной для того, чтобы максимально упростить навигацию на листе (вкладке) «Исходные данные» и сократить объем одновременно выводимых таблиц на экран.
2. Фильтры сжатия таблиц, которые применяются преимущественно на листах, предназначенных для красивого оформления результатов наших с вами будущих расчетов и последующей их печати.
3. Защиту листа, которая включена с целью защитить ячейки модели, в которых есть формулы, от случайных изменений пользователем.
4. Скрыть / отобразить столбцы и строки – эта функция полезна, когда вам необходимо максимально сжать таблицу перед печатью, убрав ненужные на конкретный момент столбцы или строки.
5. Быстрое перемещение вверх страницы моментально переместит курсор в самый верх экрана, если вы вдруг немножко запутались в какой-либо из таблиц.
6. Закрепление областей будет очень полезно при вводе данных в большие таблицы и поможет сделать ввод данных максимально удобным.
7. Границы печати понадобится выставить в тех таблицах, которые вам необходимо вывести на печать и сделать их при этом максимально презентабельными.
1. Фильтры навигации
Навигация на листе «Исходные данные» организованна посредством стандартных фильтров Excel. Работает она следующим образом – в верхнем левом углу стоит три фильтра: 1. Варианты; 2. Основные блоки модели; 3. Подразделы блоков.
Нажав на первый фильтр, вы увидите список вариантов:
Если вы моделируете только один вариант организации бизнеса, то снимите галочки с вариантов 2 и 3. После этого нажмите кнопку «ОК». В результате этих действий будут скрыты строки с вариантами 2 и 3 в таких таблицах, как «Распределение инвестиций», «Маркетинг», «Операционные расходы» и т.д. Установлен этот фильтр с той целью, чтобы лишние итоги по вариантам 2 и 3, если они вам не нужны, не заполняли экран и меньше вас путали.
Второй по счету фильтр управляет основными блоками на листе «Исходные данные». Чтобы выбрать нужный вам для заполнения блок, раскройте второй фильтр и оставьте галочку только на том блоке, который вам нужен, а остальные галочки снимите и нажмите кнопку «ОК». В результате этих действий на экране останется только тот блок, который вы выбрали, а все остальные будут скрыты. Это сделано для того, чтобы вы могли быстро переключаться с одного блока модели на другой и не путаться в порядке их заполнения.
Третий фильтр, работает аналогичным образом, и использовать его можно в тех блоках, где основной блок содержит несколько подпунктов, как, например, блок «Персонал» содержит в себе «График выходов сотрудников», «График работы и схему персонала», «Фонд оплаты труда».
Если вам удобно двигаться по вкладке «Исходные данные» путем передвижения курсора вниз и большое количество таблиц на экране вас не смущает, тогда пользуйтесь привычным способом, а фильтры навигации пропустите.
Чтобы снять установленный фильтр, нажмите значок фильтра и выберите пункт «Удалить фильтр»:
2. Фильтры сжатия таблиц
На вкладках с желтой заливкой, т.е. на листах с таблицами для печати, помимо фильтра с вариантами в левом верхнем углу можно встретить фильтр сжатия пустых строк.
Чтобы скрыть пустые строки из таблиц, которые вам нужно вывести на принтер и вложить в печатный экземпляр вашего проекта, необходимо на соответствующем фильтре (такой фильтр можно увидеть, например, на листе «1.Инвестиции») убрать галочку «(Пустые)», т.е. оставить только галочку «Сжать». После нажатия кнопки «ОК», на экране скроются все пустые (незаполненные) строки.
3. Снять защиту листа
Защита листа установлена без пароля и служит она исключительно для того, чтобы пользователи модели случайно не повредили формулы или не сбили порядок заполнения ячеек. Благодаря включенной защите на вкладке ввода исходных данных доступными остаются только ячейки, которые нужно заполнять, а все ячейки с формулами даже не выделяются курсором. На листах с желтой заливкой (где размещены готовые таблицы для печати) все ячейки выделяются, но изменить их не возможно, все по той же раннее указанной причине, включенной защите ячеек листа (опять же без пароля).
Если же необходимость снять защиту листа у вас все же возникла, то сделать это можно следующим образом:
3.1. На панели инструментов во вкладке «Главная» в блоке «Ячейки» выбираем «Формат». В открывшемся контекстном меню выбираем пункт «Снять защиту листа…».
После этого все ячейки будут открыты для изменений и лист можно изменять как вам необходимо.
3.2. Чтобы вновь включить защиту листа, нужно выполнить те же действия, что указаны выше, только теперь в контекстном меню вместо пункта «Снять защиту с листа», стоит пункт «Защитить лист».
Нажимаем «Защитить лист» и подтверждаем установку защиты в открывшемся диалоговом окне нажатием кнопки «ОК».
Внимание! Снимать галочки и устанавливать дополнительные не нужно, если только вы твердо не уверены, что делаете правильно!
4. Скрыть/отобразить столбцы или строки
Преимущественно такое действие вам может понадобиться на желтых листах, чтобы сократить размеры таблиц и сделать их удобными для чтения на печатной странице. Скрыть ненужные для отображения на экране столбцы или строки можно такими способами:
4.1. На желтых листах, где есть большие по ширине таблицы, установлена группировка, которая выводиться соответствующими указателями в верхней части над рабочей областью Excel.
Нажатие на квадратик со значком «-» скроет примыкающую слева к нажимаемому квадратику линию. Нажатие же на квадратик со значком «+» наоборот, развернет скрытые столбцы. Путем группировки столбцов, вы можете, к примеру, первый год показать по месяцам, второй по кварталам (оставить видимыми только 4-е столбца), а третий год и вовсе показать одним столбцом, где видны лишь итоги года. Все зависит исключительно от ваших потребностей.
Внимание! Чтобы использовать установленную в модели группировку столбцов, предварительно нужно снять защиту листа (см. пункт выше)! После того, как вы оставите в видимой области только те столбцы, которые вам нужны, защиту листа можно включить снова.
4.2. Другие способы скрыть и отобразить строки и столбцы смотрите здесь.
5. Быстрое перемещение вверх страницы
Чтобы быстро перейти в самый верх страницы на листе «Исходные данные», нажмите на одну из стрелок в разделительном столбце (столбец Н) и курсор тут же будет перемещен в ячейку А1:
6. Закрепить области
6.1. Для того чтобы было удобно работать с большими таблицами на листе «Исходные данные», я сделал некоторые полезные пометки. Таким образом, для закрепления области вам нужно при помощи вертикального и горизонтального скроллов выставить таблицу так, чтобы начало зеленой ячейки стало четко под верхний край рабочей области экрана (т.е. под буквы вверху экрана). После этого нужно мышкой установить курсор в выделенную ячейку над синей стрелкой:
Затем на панели инструментов переходим на вкладку «Вид» и в блоке «Окно» выбираем «Закрепить области». После этого в открывшемся контекстном меню нажимаем «Закрепить области».
В результате этих действий верхняя и левая часть таблицы будут закреплены на экране в максимально удобном для работы положении.
6.2. Чтобы снять закрепление областей и перейти к стандартному перемещению курсора по экрану, выполните все те же действия, что и в пункте 6.1, только вместо «Закрепить области» в контекстном меню, вы увидите «Снять закрепление областей». Его вам и нужно нажать.
7. Границы печати
Эта функция может понадобиться при выводе на печать необходимых вам таблиц (преимущественно это листы с желтой заливкой или основные финансовые отчеты). Здесь стоит обратить внимание на два пункта:
7.1. Ориентация листа (книжная или альбомная). Для более удобного восприятия таблиц на бумаге, я рекомендую их печатать в формате альбомной страницы. При этом обращаю ваше внимание, что чем больше столбцов вы хотите распечатать на одном листе, тем меньший будет шрифт, а читать мелкий шрифт будет довольно тяжело.
Итак, задать ориентацию листа можно перейдя на панели инструментов во вкладку «Разметка страницы» и выбрав в блоке «Параметры страницы» ориентацию «Альбомная» или «Книжная».
7.2. Чтобы перетянуть границу печати листа, необходимо перейти в страничный режим. Для этого на панели инструментов на вкладке «Вид» в блоке «Режимы просмотра книги» выберите «Страничный режим». Появление на экране нумерации листа и границ синего цвета говорит о том, что все вы выполнили правильно и теперь, передвигая при помощи курсора синие границы (вверх-вниз, вправо-влево), вы можете задавать необходимую область печати для каждой страницы.
Вернуться обратно в обычный режим можно тем же образом, как указано выше, только вместо «Страничный режим», нужно выбрать «Обычный».
Скачать готовую финансовую модель можно здесь
Видеоинструкция о функциях Excel, используемых в финансовой модели
<< Перейти к скачиванию модели |
Все анонсы новых постов и инструкций после публикации я выставляю на странице блога в Facebook.
Хотите первым узнать о новой публикации на моем блоге, подписывайтесь на страницу блога в Facebook!
Если материал поста был для Вас полезен, поделитесь ссылкой на него в своей соцсети:
При использовании материалов сайта наличие активной ссылки на www.blogbusiness.com.ua обязательно
7 комментариев
Андрей, здравствуйте! Разрешите уточнить технический момент: можно ли в принципе пользоваться версией Офиса (Excel) 2007 года? При открытии и редактировании Бизнес-плана 3.0 никаких ошибок нет. Заранее благодарю Вас!
Добрый день, Дайяван! Да, 2007 офис тоже подойдет для модели 3.0.
Добрый день! Благодарю Вас за огромную проделанную работу и помощь нам. Скачала файл, начала заполнять, а сохранить не могу, пишет, что обнаружены ошибки при сохранении, файл поврежден. Попробовала сохранить исходный файл с заполненной одной ячейкой — результат такой же, не удается сохранить. Сталкивались ли Вы с такой проблемой? Может у меня исходный поврежденный файл?
Добрый день, Юлия! Очень редко, но на некоторых компьютерах такая проблема стала встречаться. Попробуйте, если есть возможность, на другом компьютере скачать файл и сохранить в нем какое-нибудь изменение. Напишите мне, что получится.
Благодарю! Попробовали на другом компьютере — работает. Переустановили офис и получилось сохранить.
Спасибо за модель! На первый взгляд все необходимое есть. Все продуманно и логично. И самое важное, думаешь о главном, а не о формулах в ексель. ))) Начала заполнять. Возникнут вопросы-напишу. Спасибо!
Пожалуйста, Инга Ивановна! Будут вопросы, конечно пишите, разберемся вместе 🙂