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

5306
Истомахина Мария
корреспондент 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–2017 ООО «Актион управление и финансы»

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

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

    
    • Мы в соцсетях
    Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

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

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

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