Анализ «план-факт» с помощью надстройки Power Query Excel на примере ТРЦ

Рассмотрим возможности надстройки Power Query Excel для проведения анализа «план-факт» деятельности торгово-развлекательного центра.

План-фактный анализ (анализ «план-факт») актуален для большинства финансовых бюджетов[1]. Если необходимо детально изучить причины отклонений от плана, он применяется и для отдельных операционных и функциональных бюджетов.

Такой анализ выполняется в масштабе всей компании либо для отдельных центров финансовой активности (бизнес-единиц), проектов или направлений деятельности. Это позволяет уточнить, из-за каких именно структурных элементов бизнеса возникают отрицательные или положительные отклонения плановых статей бюджетов от фактических.

План-фактный анализ представляет собой сравнение плановых и фактических показателей финансового бюджета и выявление причин отклонений.

Исходные данные для план-фактного анализа

Изучим возможности применения надстройки Power Query для анализа «план-факт» деятельности торгово-развлекательного центра (ТРЦ).

Для анализа «план-факт» будем использовать следующие данные:

1) планируемые величины статей доходов и расходов (бюджет доходов и расходов БДР);

2) фактические величины статей доходов и расходов (Отчет по доходам и расходам).

Аналитика статей доходов и расходов приведена в табл. 1 в разрезе бизнес-единиц и направлений их деятельности.

Power Query — это надстройка, которая обеспечивает удобный поиск, трансформацию и обновление данных для аналитиков, дашборд[2]-профессионалов и других пользователей.

Power Query представляет собой новую вкладку в ленточном интерфейсе Excel, где можно импортировать, преобразовывать и объединять данные из различных источников.

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

Преимущества Power Query:

1. Можно использовать различные форматы исходных данных: xml, csv, xls, doc.

2. Можно загружать данные в таблицу Excel из различных источников:

• Интернет;
• базы данных SQL, Oracle, Access, IBM DB, Mysql, Sybase и т. д.;
• веб-службы, протоколы, интерфейсы и облачные хранилища.

3. Возможность слияния нескольких таблиц в одну с установкой связей по определенным критериям

4. Поиск данных по каталогам.

5. Возможность автоматически «собирать» и обновлять таблицу из нескольких исходных таблиц, расположенных в одной папке.

Эти возможности используются также в надстройке Excel Power BI[3]., а также в надстройке Power Query Add-In, доступной для предыдущих версий Excel.

Основные этапы подключения и преобразования данных в Excel посредством Power Query представлены на схеме.

Несмотря на то что некоторые виды анализа применяют лишь некоторые из этих шагов, каждый шаг важен в процессе анализа и преобразования данных.

Подключение и преобразование данных

Чтобы использовать возможности надстройки Power Query, необходимо создать запрос (query) в рабочей книге Excel. Запрос позволяет подключить, просмотреть и преобразовать данные из различных источников. Затем преобразованные данные загружаются в таблицу (лист) Excel или во встроенную модель данных в Excel, далее при необходимости данные обновляются. Также есть возможности редактировать и пересылать сформированный запрос.

Анализ «план-факт» деятельности ТРЦ с помощью надстройки Power Query

Этап 1. Подготавливаем исходные данные для анализа «план-факт» в Power Query

Шаг 1. Формируем плановые показатели (лист книги Excel «Модель БДР план»)

Ежегодные показатели доходов и расходов, запланированные планово-экономическим подразделением ТРЦ на 5-летний период, приведены в табл. 3. Диапазону ячеек указанной плановой таблицы присваивается имя «БДР-план» (именованный диапазон для формирования соответствующего запроса [БДР-план]).

Шаг 2. Формируем фактические показатели (лист книги Excel «Модель Отчет факт»)

Ежегодные показатели доходов и расходов, зарегистрированные в отчетности ТРЦ в  течение 5-летнего периода, приведены в табл. 3. Диапазону ячеек указанной отчетной таблицы присваивается имя «ОТЧЕТ-факт» именованный диапазон для формирования соответствующего запроса [Отчет-факт]).

Этап 2. Формируем запросы для план-фактного анализа в Power Query

Шаг 3. Формируем запрос [БДР-план] в модели данных

Запрос [БДР-план] включает в себя следующие операции (рис. 1):

1) выбор любой ячейки таблицы на листе «Модель БДР план»;

2) выбор вкладки «Данные» ленточного интерфейса Excel 2016;

3) выбор вида запроса «Из таблицы» в разделе «Скачать & преобразовать».

Power Query формирует проект запроса по данным таблицы «Модель БДР план»;

4) запрос именуется БДР-план, из меню ленты запроса выбирается «Закрыть и загрузить в…», применяя опции «Только создать подключение», «Добавить эти данные в модель данных»;

5) после подтверждения (нажимаем ОК) запрос [БДР план][4] загружается в модель данных[5].

Шаг 4. Формируем запрос [Отчет-факт] в модели данных

Запрос [Отчет-факт] включает следующие операции (аналогично запросу [БДР план]):

1) выбор таблицы на листе «Модель Отчет факт»;

2) выбор вкладки «Данные» ленточного интерфейса Excel 2016;

3) выбор вида запроса «Из таблицы» в разделе «Скачать & преобразовать».

Power Query формирует проект запроса по данным таблицы «Модель Отчет факт»;

4) запрос именуется Отчет-факт, в меню выбирается «Закрыть и загрузить в…», применяя опции «Только создать подключение», «Добавить эти данные в модель данных». После подтверждения (нажимаем ОК) запрос [Отчет факт] загружается в модель данных.

Этап 3. Объединяем запросы для план-фактного анализа в Power Query