Close
Логин:  Пароль: 
Поиск на форуме:
Расширенный поиск
buhgalter-info.ru
kadrovik-info.ru
sekretar-info.ru
economist-info.ru
Журнал «Справочник экономиста»
Журнал «Справочник экономиста»

Голосование:

Знаете ли вы, что такое Ассессмент?






Вы можете выбрать другой город для информера

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

Статья была опубликована в журнале «Справочник экономиста» № 6 июнь 2019.
Все права защищены. Воспроизведение, последующее распространение, сообщение в эфир или по кабелю, доведение до всеобщего сведения статей с сайта разрешается правообладателем только с обязательной ссылкой на печатное СМИ с указанием его названия, номера и года выпуска.

Рассмотрим возможности надстройки 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

 

[1] БДР (бюджета доходов и расходов), БДДС (бюджета движения денежных средств), ББЛ (бюджета по балансовому листу).

[2] Дашборд (англ. Dashboard) — аналитический инструмент, наглядное представление информации о бизнес-процессах, о состоянии какого-то объекта в виде динамических онлайн-диаграмм.

[3] Power BI — это комплексное программное обеспечение бизнес-анализа (BI) компании Microsoft, объединяющее несколько программных продуктов, имеющих общий технологический и визуальный дизайн, соединителей (шлюзов), а также web-сервисов. Power BI относится к классу self-service BI, и BI с резидентным вычислением (англ. in-memory computing). Является частью единой платформы Microsoft Power Platform.

Ключевой и самый первый продукт линейки — Power BI Desktop состоит из трех интегрированных компонентов, имеющих каждый свой интерфейс:

  • Power Query (редактор запросов) — выполняет загрузку и очистку данных (ETL);
  • PowerPivot (наборы данных и модели данных) — интерфейс работы с табличными данными в оперативной памяти где выполняются запросы к данным, агрегация, расчёты и т. п.;
  • Power View — подсистема визуализации и построения отчётов (Reporting).
  •  

[4] Квадратными скобками (напр., [БДР план]) обозначены объекты модели данных.

[5] Выбор варианта (опции) загрузки запроса «Таблица» создаст дополнительную таблицу в книге Excel. Этот выбор имеет смысл после внесения всех данных анализа «план-факт» в Модель Данных» 

© 2006—2019, ООО «Профессиональное издательство» — издательство журнала «Планово-экономический отдел».
Воспроизведение, последующее распространение, сообщение в эфир или по кабелю, доведение до всеобщего сведения материалов с сайта разрешается правообладателем только с указанием гиперссылки на данный сайт, если не указано иное.