Трюки с Excel. Функции, важные в повседневной работе финансиста

5182
Истомахина Мария
корреспондент FD
Для финансовых специалистов Excel – один из основных рабочих инструментов. Тем не менее многие его функции не используются – о них просто не знают. Какие инструменты помогут сэкономить время и избежать ошибок при работе с финансовыми моделями в Excel?

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

ОБЕСПЕЧИТЬ СООТВЕТСТВИЕ…

Представьте. Вы составляете отчетность по МСФО, используя Excel. Исходные данные (номера российского плана счетов, а также остатки и обороты по ним) выгружаются из бухгалтерской системы. Что происходит дальше? А дальше специалисты финансового подразделения, используя таблицу соответствия счетов РСБУ и МСФО, вручную переносят данные из одной таблицы в другую. На это может потребо-ваться не один день. Кроме того, такая техника переноса данных зачастую приводит к ошибкам.

На самом деле есть простое решение этой проблемы. «Формировать и консолидировать отчетность по МСФО можно, используя стан-дартные инструменты Excel, – рассказывает Александр Довженок, заместитель начальника отдела финансового учета и формирования финансовой отчетности «Магнитогорского металлургического комбината». – Для этой цели можно применять функцию вертикального просмотра, которая соотносит данные классификатора счетов и бухгалтерского отчета».

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

Все будет гораздо проще, если вписать в столбец «Счет МСФО» формулу вертикального просмотра. Она должна будет иметь следующие компоненты (рис. 1):
– указание на ячейку с номером счета РСБУ (А2);
– ссылку на таблицу соответствия счетов (‘Соответствие счетов’!$A$3:$B$60);
– номер столбца в таблице соответствия счетов, из которого будут выбираться счета МСФО (2 (второй столбец)).
– значение интервального просмотра (другими словами, можно искать приблизительно соответствующие значения). В нашем случае значение устанавливается равным нулю.

На этом вся работа закончена. Excel выберет номера счетов, соответствующие тем счетам РСБУ, которые есть в нашей исходной таблице.

Понятно, что применение функции вертикального просмотра не ограничивается работой со счетами МСФО и РСБУ. Можно делать любые выборки и проверки при условии существования базовой таблицы соответствий.

ПРОРАБОТАТЬ ВСЕ ВАРИАНТЫ

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

«С расчетом экономической эффективности проектов мы сталкиваемся каждый день, – рассказывает Анна Балдина, аналитик компании «Главстрой». – При этом нам приходится менять как количественные показатели проекта, так и стоимостные. Также необходимо учитывать влияние инфляции. Особенно для проектов, которые длятся несколько лет. На каждый из этих показателей удобно устанавить полосу прокрутки, используя которую можно изменять их значения с заданным шагом и определенное заранее количество раз. В результате мы можем сформировать несколько десятков финансовых моделей и определить сценарии развития проекта».

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

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

ЗНАЧЕНИЕ ПОД КОНТРОЛЕМ

Изменяя параметры проекта с помощью счетчика, мы изменяем показатели эффективности проекта. Если результаты вычислений выводят-ся на том же листе, что и подбираемый параметр, то проблем не возникает.

Однако если специалисту приходится постоянно переходить с одного листа на другой, чтобы посмотреть, как повлияли манипуляции с исходными данными на итоговый результат, возникает масса неудобств. Решение, позволяющее контролировать ячейки, содержащие ключевые цифры, предложил Алексей Груздев, консультант по управлению компании «АЛЬТ».

«Действительно, при анализе чувствительности проектов необходимо, чтобы в поле зрения финансиста находились, например, значения: чистый дисконтированный доход (NPV) и внутренняя норма доходности (IRR), – рассказывает Алексей Груздев. – Проект, рассчитанный в Excel, может включать несколько десятков листов, на каждом из которых могут изменяться данные. Чтобы оценить влияние одного изменяемого параметра на результативность проекта в целом, удобно использовать окно контрольного значения».

Подключить этот инструмент можно в меню «Сервис» – раздел «Зависимости формул» – вкладка «Показать окно конт¬рольного значения». Используя пиктограмму «Добавить контрольное значение», делаем ссылку на ячейку, значение которой будет отслеживать. Таких значений можно задать несколько. Далее окно контрольного значения можно перенести в область панели инструментов. В результате мы будем видеть значение в интересующей нас ячейке вне зависимости от того, какой лист книги открыт.

Рисунок 1. Автоматическое дополнение исходных данных номерами счетов МСФО

Трюки с Excel. Функции, важные в повседневной работе финансиста

Рисунок 2. Пример использования счетчика для изменения ставки дисконтирования

Трюки с Excel. Функции, важные в повседневной работе финансиста

www.planetaexcel.ru
На сайте размещена интересная коллекция приемов работы с Excel на все случаи жизни

www.spreadsheetpage.com
Сайт одного из самых известных в мире авторов книг по Excel – John Walkenbach

www.exwork.be
C этого портала можно скачать бесплатно крайне полезную надстройку к Excel – EAT (Exwork Add-In Toolpack)

Методические рекомендации по управлению финансами компании



Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Рассылка




© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Зарегистрируйтесь на сайте,
чтобы скачать образец документа

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании Прочитать книгу «Запасной финансовый выход» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль