Инструкция: как сформировать отчет о дебиторской задолженности в Power BI


Николай Тихомиров, заместитель генерального директора по экономике и финансам ООО «ЛЛК- Интернешнл»
Содержание:

  1. Формирование архитектуры данных для последующего создания интерактивных диаграмм
    1.1. Подготовка исходных массивов данных
    1.2. Формирование запросов для подключения массивов исходных данных
    1.3. Формирование взаимосвязей таблиц, создание модели данных
    1.4. Создание DAX-формул для дополнительных вычислений
  2. Формирование и применение интерактивных аналитических отчетов в Power BI
    2.1. Создание аналитических визуальных элементов и взаимосвязей между ними
    2.2. Последующая актуализация информационных панелей, добавление нового отчетного периода
    1.1. Подготовка исходных массивов данных
    Исходными данными для анализа дебиторской задолженности, как правило, являются выгрузки из систем автоматизации бухгалтерского учета.
    Ввиду того, что дополнительная аналитика не всегда содержится и поддерживается в актуальном состоянии непосредственно в информационной системе автоматизации бухгалтерского учета, возникает необходимость применения дополнительных классификаторов. Данные классификаторы применяются для декомпозиции показателей по зонам ответственности, географическим и другим сегментам рынка.
    В качестве примера будут применены классификаторы покупателей и региональных менеджеров.
    Классификатор покупателей содержит информацию, необходимую для управления дебиторской задолженностью: число дней отсрочки по контракту, информацию о кредитных лимитах, инструментах обеспечения обязательств (выданных банковских гарантиях, полисах страхования дебиторской задолженности).
    Выгрузки из систем бухгалтерского учета содержат следующую основную информацию: покупатель, инвойс (по коророму не погашена дебиторская задолженность), дата и сумма возникновения обязательств покупателя.
    1.2. Формирование запросов для подключения массивов исходных данных.
    Создаем новый файл Power BI. В диалоговом окне выбираем пункт меню «Получить данные». 

В качестве источника указывается файл Excel. В стандартном диалоговом окне указываются файлы — источники.

В диалоговом окне «Навигатор» указываются загружаемые таблицы. На нижеследующем скриншоте отражены загружаемые таблицы применяемых классификаторов.
После завершения загрузки подключаемые таблицы отражаются в правой части окна Power BI в разделе «Поля».

(Во избежание потери данных, созданный файл Power BI необходимо сохранить с использованием стандартных пунктов меню: «Файл» -> «Сохранить»).
Последующее подключение источников данных осуществляется с использованием запросов (Power Query).
Последовательность выбора пунктов меню приведена на нижеследующем скриншоте.

Действия повторяются для каждого подключаемого файла и таблицы данных. (Созданные подключения автоматически сохраняются в запросах, что минимизирует затраты рабочего времени в будущем).
Для обеспечения возможности анализа динамики показателей, в данном примере смоделированы исходные еженедельные данные за 3 календарных месяца, в связи с чем созданы подключения к каждому из них. По результатам созданных подключений, в разделе «Поля» отображаются соответствующие запросы, названия которых соответствуют названиям таблиц Excel.

1.3. Формирование взаимосвязей таблиц, создание модели данных
Для обеспечения возможности анализа показателей в динамике (а не только на последнюю дату отчета), формируемые еженедельно выгрузки целесообразно объединить в одну таблицу, с сохранением названий столбцов
В Power BI для данных целей используются DAX-формулы, создаваемые в разделе «Данные». В данном случае, необходимо создать новую таблицу с использованием функции «UNION». Последовательность действий отражена на нижеследующих скриншотах.
В разделе «Данные» [А] перейти на вкладку «Моделирование» [В] и выбрать пункт «Создать таблицу» [С].

В строке формул ввести формулу, приведенную на нижеследующем скриншоте ([А]-[С]). Для ускорения ввода переменных в формуле целесообразно пользоваться выбором из выпадающих списков, отображаемых автоматически после ввода апострофа («’»). После ввода формулы, в разделе «Поля» отображается новая таблица ([Б], [Е]).
После создания объединенной таблицы необходимо сформировать модель данных, фиксирующую связи между таблицами. Необходимо перейти в раздел «Связи» ^]. Для установления связи между таблицами необходимо «перетащить» (нажать левую кнопку мыши, при нажатой кнопке перевести курсор на связываемую область, отпустить кнопку) поле одной связываемой таблицы ^] на соответствующее поле другой [С], по которому данные таблицы связываются. Например, объединенная таблица выгрузок дебиторской задолженности связывается с классификатором покупателей по полю (столбцу) «Системный код покупателя».
Для отображения типа связи между таблицами необходимо по двойному щелчку мыши на стрелке (см.пункт [А] на скриншоте ниже по тексту) открыть меню «Изменение связи» [С], или выбрать пункт меню «Управление связями» [В]. В соответствующих разделах окна отражаются связываемые таблицы ([В],[Б]), столбцы, по которым они связаны ([Б],[0]), а также тип связи. Для подключаемых классификаторов, предполагающих уникальность кода покупателя, тип связи должен быть «многие-к- одному», или «один-к-одному», поскольку дубли в классификаторах не допускаются.

1.4. Создание DAX-формул для дополнительных вычислений.
Далее будут приведены примеры создания вычисляемых столбцов и мер. По аналогии с Excel, вычисляемый столбец предоставляет собой дополнительный столбец, функции которого возвращают результат, соответствующей заданной строке массива. В отличие от вычисляемых столбцов, вычисляемые меры схожи с применением вычисляемых полей в сводных таблицах Excel, осуществляющих необходимую агрегацию данных, но обладают гораздо большими функциональными возможностями.
Power BI содержит возможность осуществления вычислений в соответствии с контекстом фильтрации данных, заданным пользователем на дашборде.
В данном примере будут создан ряд дополнительных столбцов и мер для обеспечения динамических, интерактивных вычислений.
Создание дополнительного столбца, содержащего значения числа дней отсрочки по договору.
Как было отражено выше, таблицы классификатора покупателей и объединенная таблица выгрузок связаны по полю «системный код покупателя». Функция DAX, возвращающая значение соответствующего столбца связанной таблицы, работает по аналогии с распространенной в Excel функцией «ВПР» (VLOOKUP).
Последовательность действий приведена на нижеследующем скриншоте. Необходимо в разделе «Данные» [A] выбрать активную таблицу [B], после чего на вкладке «Моделирование» [C] выбрать пункт «Новый столбец» [D], после чего в строке формул ввести соответствующую формулу [E], применив функцию «RELATED» и выбрав из списка столбец связанной таблицы [G]. После завершения ввода формулы [H] в перечне полей активной таблицы сформировано поле «Отсрочка,дн» [I].

Аналогичным образом сформируем дополнительный столбец, определяющий дату наступления обязательств по контракту. Последовательность действий приведена на скриншоте ниже по тексту. Шаги ^],[Щ необходимы для корректного отображения значений в поле в формате «Дата».

Далее создается столбец, значения которого соответствуют числу дней просрочки. (Отрицательные значения применяются к инвойсам, по которым срок оплаты еще не наступил).

Как правило, «возраст» просроченной дебиторской задолженности, измеряемый в числе дней просрочки, группируется по периодам для последующего анализа структуры просроченной дебиторской задолженности и соответствующих корректирующих действий.
Для того, чтобы произвести группировку ПДЗ по периодам, используется DAX-функция «SWITCH» совместно с функцией «TRUE». В результате в каждой строке указывается диапазон в днях, под который подпадает соответствующая сумма просроченной дебиторской задолженности.

Для расчета сумм просроченной задолженности создается соответствующий столбец, значения которого зависят от отклонения от срока по договору на дату отсрочки.
«ПДЗ =
Ш(‘ВыгрузкиДЗ(вседатыотчетаУ[Отклонениеотсрокаподоговору,дн]>0;’ВыгрузкиДЗ(вседатыо тчета)'[Сумма по счету, руб.];0)

Для расчета показателя «Доля просроченной дебиторской задолженности в сумме дебиторской задолженности», характеризующего платежную дисциплину покупателей, а также показатели результативности работы региональных менеджеров, необходимо оперировать значением дебиторской задолженности в целом, а не только по тем счетам, где возникла просрочка.
Для того, чтобы в знаменатель включались все строки (счета), а не только те, по которым возникла просроченная дебиторская задолженность, применяются формула БАХ, игнорирующая контекстный фильтр по полю «Период ПДЗ» в знаменателе (кроме выбранной даты отчета). Для данных вычислений создается новая мера: «ДЗ(всего),тыс.руб. = CALCULATE(sum(‘ВыгрузкиДЗ(вседатыотчета)'[Сумма по счету,тыс.руб.]);ALLEXCEPT(‘ВыгрузкиДЗ(вседатыотчета)’;’ВыгрузкиДЗ(вседатыотчета)'[Дата отчета]))
Последовательность действий и формула приведены на нижеследующем скриншоте.

Для расчета показателя «Доля ПДЗ» создадим соответствующую меру:
«Доля ПДЗ =
DГVГОE(SUM(‘ВыгрузкиДЗ(вседатыотчетаУ[ЦДЗ,тыс.руб.]);’ВыгрузкиДЗ(вседатыотчетаУ[ДЗ(всег
о),тыс.руб.])
Во избежание выдачи на информационную панель ошибки деления на ноль, применяется DAX-функция «DIVIDE» [E], которая при делении на ноль выдает пустое значение.

Для сокращения числа разрядов создадим столбец, содержащий значения суммы по счету в тысячах рублей, применим формат с разделителем разрядов и округлением до одного знака после запятой.

  1. Формирование и применение интерактивных аналитических отчетов в Power BI.
    2.1. Создание аналитических визуальных элементов и взаимосвязей между ними.
    На первом этапе целесообразно сформировать основные информационные срезы, которые будут использоваться в отчете.
    Сформируем срез по Федеральным округам.
    В рабочей области «Отчет» (шаг [А] скриншота ниже по тексту) в разделе «Визуализация» [В] выберем визуальный элемент «Срез» [С].
    Из классификатора Федеральных округов [Б] поле «Краткое наименование ФО» [Е] перемещается в область «Поля» [Б]. При этом создаваемый срез должен быть активен. По результатам, срез будет содержать перечень Федеральных округов [О].

Для экономии рабочего пространства разместим срез горизонтально.

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

Аналогично описанному выше форматированию элемента «Срез», добавим кнопку «Выбрать все» [А].
Для того, чтобы исключить категорию «Blank», соответствующую пустым строкам в массиве данных, исключим из вывода на информационную панель все записи таблицы данных, в которых не указан покупатель. Для этого применим фильтр уровня страницы (шаги [B]-[F] скриншота ниже по тексту).
Аналогичным образом добавляются срезы «Период ПДЗ» [А], «Дата отчета» [В], «Обеспечение» [Б] «Сегмент» [Е]. Элементы [2]-[4] — текстовые элементы [1].
Далее выведем на информационную панель основные показатели.
Структура ПДЗ по периодам возникновения.
Добавим элемент визуализации «Круговая диаграмма», выделим отражаемые на нем данные ([4]-[7]). 
Дополнительно примененное форматирование осуществляется в разделе «Формат»:
Для отражения процентных значений на диаграмме необходимо выполнить следующие действия: 
Аналогичным образом на информационной панели размещаются и форматируются следующие элементы: Визуальный элемент «Карточка» [1] для вывода значений:
Общей дебиторской задолженности на дату [2];
Просроченной дебиторской задолженности на дату, в соответствии с выбранными срезами [3];
Доле просроченной дебиторской задолженности в совокупном значении на дату [3]
Структура ПДЗ по каналам продаж (сегментам) [5-6],
Диаграммы с ранжированием просроченной дебиторской задолженности по покупателям и региональным представителям [7-9].

Для изменения порядка взаимодействия визуальных элементов необходимо выбрать пункт меню «Изменить взаимодействие». Далее выбирается активный элемент, с последующим определением формата отражения информации на связанных элементах. На нижеследующем скриншоте приведен пример настройки взаимодействия элементов — диаграмм [А] и [В]: применение фильтра [С], выделение области [Б] или отсутствие взаимосвязи [Е] элемента [В] с элементом [А].

При выборе фильтра [С] — активизация одной из областей диаграммы [А] приводит к изменению порядка следования покупателей на диаграмме [В].
Так, например, если в исходном режиме покупатель с наибольшим значением ПДЗ — «Покупатель_84», то при активизации на диаграмме [А] области «С.От 14 до 30 дней», на диаграмме [В] изменяются значения ПДЗ и переупорядочивается новый список покупателей по выбранному критерию. Первые позиции списка занимают покупатели, имеющие наибольшие значения ПДЗ в выбранном диапазоне «С.От 14 до 30 дней».

При выборе варианта «выделение области» в формировании схемы взаимодействия — ранжирование списка сохранится, а соответствующие показатели будут выделены как части исходных значений:

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

На второй информационной панели отображаются следующие показатели: информационные срезы [1]

  • по Федеральным округам [А],
  • Региональным менеджерам [В],
  • Документам обеспечения обязательств [С],
  • сегментам (каналам продаж) [Б],
  • периодам классификации просроченной дебиторской задолженности [Е].

Графики и гистограммы [2],[Б] отражающие (в соответствии с определяемыми пользователем срезами [А]- [Е]) динамику показателей:
Значение просроченной дебиторской задолженности Доля ПДЗ в общей дебиторской задолженности на дату
Таблицу детализации показателя ПДЗ в соответствии с определенными пользователем срезами и выделенным на диаграмме [Б] столбцом (датой отчета) — по Федеральным округам, Региональным менеджерам, Покупателям.
График динамики общей дебиторской задолженности на дату отчета.
2.2. Последующая актуализация информационных панелей, добавление нового отчетного периода.
При добавлении нового периода необходимо выполнить следующие 3 действия:

  1. Подключить новую таблицу (по аналогии с ранее подключенными)
  2. Добавить название таблицы в формулу создания таблицы (функция «UNION»)
Рубрики: Метки:

1 Comment

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.