Семинар "Бюджетирование с Business Intelligence". Работа в Excel с использованием Power Query и Power Pivot

То, что мы знаем об Excel — устарело!

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

Большинство пользователей считает Excel просто табличным редактором.

А это уже не так. Для Excel 2010–2016 появился новый функционал — Power Query и Power Pivot. Благодаря этим инструментам отпадает необходимость в написании макросов и создании систем файлов, связанных между собой "многоэтажными" формулами. Теперь у пользователей появился инструмент для работы с действительно большими объёмами данных — в миллионы строк. Кстати, умение работать с большими объёмами данных и со сводными таблицами — это необходимое требование, для высокооплачиваемых вакансий.

На нашем семинаре вы узнаете, как сделать работу аналитика простой и эффективной:

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

Инструменты, которые мы будем проходить — абсолютно новые. Информация по ним в основном на англоязычных сайтах. На семинаре материал выдаётся в понятном виде и на практических примерах реальных внедрений.

Программа семинара

1. Методики по анализу, бюджетированию и подготовке отчётности.
   • Большинство методик бюджетирования, представленных консультантами и описанных в книгах или в интернете говорят, что нужно создавать формы (таблицы) бюджетов. А потом, на основе этих таблиц строить финансовые бюджеты: БДР, БДДС и Прогнозный баланс. Но на практике возникают вопросы: кто должен заполнять таблицы бюджетов? Как добавить факт? Что делать, если выручка упадёт? И т.д. 
     
     На семинаре будет разбираться подход, который поможет решить возникающие проблемы.
     
     Слушатели узнают, как организовывать планы, чтобы их было легко менять, как распределить доступ к вводу данных между отделами компании, как быстро проанализировать факт.
2. Начнём с самого интересного: секрет создания интерактивного наглядного отчёта в Excel.
   • Практически все расчёты, которые выполняют экономисты, финансисты и аналитики предназначены для директора или собственника компании. Иногда в работе бывает, что целый день, неделю или месяц специалисты составляют отчёт, а в итоге его никто не понял и не оценил. Почему? Информация подаётся в виде, понятном специалисту, но не руководителю. Именно поэтому мы начнём с того, как подавать информацию, чтобы её понимали нефинансисты. Чтобы освоить с принципы понятной отчётности, нужно изучить следующие разделы Excel:
     • Создание сводных таблиц и управление ими.
     • Анализ плана и факта с помощью сводных таблиц.
     • Вычисления в сводных таблицах: классические и с помощью DAX-формул (в Power Pivot).
     • Построение интерактивного отчёта на один лист с использованием срезов и временных шкал.
3. Как правильно работать с большими объёмами данных в Excel.
   • Как финансисты работают с большими объёмами данных? Они выполняют четыре важных бизнес-процесса: собирают данные, выполняют расчёты, строят отчёты и делают аналитические выводы. Самое главное в работе аналитика — это выводы и рекомендации. Но наибольшая часть времени уходит на сбор данных и расчёты показателей. Процессы сбора данных и выполнения расчётов можно назвать умной рутиной. Умной — потому что это высокие затраты интеллектуальных сил. А рутина — потому что это постоянно повторяющиеся действия. И автоматизировать их раньше финансовому отделу было сложно. В новом Excel наконец-то появились интересные решения для снижения рутинных операций:
     • Новая надстройка Power Query: построение сводной таблицы из разных листов, книг Excel и баз данных.
     • Надстройка Power Pivot: создание связей между таблицами и построение DAX-формул.
4. Планирование и бюджетирование на основе расчётной модели и заявок.
   • Как строится бюджет? Если посмотреть укрупнённо, то одна часть бюджетных статей рассчитывается, другая — складывается из заявок от подразделений (ЦФО). Отсюда, чтобы построить систему бюджетирования, нужно настроить прогнозно-финансовую модель. И создать систему сбора заявок с разграниченным доступом. И то, и другое можно реализовать в Excel:
     • Прогнозирование бюджета продаж на основе предыдущих данных: тренд и сезонность.
     • Построение бюджетной модели в Excel и её конвертация в базу данных.
     • Построение базы данных для бюджета на основе вводных ограничивающих факторов.
     • Сбор бюджетных заявок от подразделений с разграниченным доступом:
       • Способ 1 (классический) Все данные на сервере компании;Способ 2 (новый) Сборка заявок в бюджет независимо от географической привязки с помощью современного функционала на основе облачных технологий OneDrive.
       • Объединение данных в единую плановую базу и разграничение БДР, БДДС и Прогнозного баланса.
5. Получение фактических данных из учётных систем.
   • Пожалуй, одна из самых сложных операций в работе экономистов и финансистов — это получение фактических данных. Классические таблицы из 1С требуют доработки и дополнительной детализации. Настроенные отчёты все равно нужно выгружать в Excel, а фактические показатели привязывать к плановым. К счастью, новейшие инструменты Excel позволяют подключаться к учётным системам напрямую. Правда на практике не все так легко, как в описаниях, поэтому кроме техники подключения к данным слушателям будут даны рекомендации, как правильно написать техническое задание (ТЗ) в IT-службу:
     • Как подключаться к базе данных.
     • Если подключиться "невозможно", как и в каком виде нужно давать ТЗ программистам на выгрузку данных.
     • Загрузка фактических данных в Excel для анализа.
6. Факторный анализ отклонений бюджета в Excel.
   • Оценка того, как выполняются планы — самое простое, что может быть. Достаточно от факта отнять план. Сложнее ответить на вопрос "почему?". Почему произошло такое отклонение? На этот вопрос можно ответить с помощью наглядной факторной модели отклонений. На семинаре вы узнаете, как она строится и как представить результаты:
     • Объединение плана и факта в Power Query.
     • Расчёты с помощью сводных таблиц.
     • Объединение плана и факта в Power Pivot через связанные таблицы. Выполнение расчётов с помощью DAX-формул.
7. И снова наглядный интерактивный отчёт или итоги.
   • Этот раздел призван закрепить пройденный материал. И если первый отчёт слушатели заполняли на готовых данных, то теперь это будут цифры, которые они сами подготовили в течение семинара. Для участия в семинаре необходим ноутбук с Excel не старше 2013 года с установленными надстройками Power Query и Power Pivot.