Электронная версия журнала

Финансовый контроль и управление платежами в Excel

«Планово-экономический отдел» №11 2017 / Технологии управления

 

Задача специалиста по экономике и финансам — контролировать и анализировать отчетность. Часто такая отчетность представлена большим массивом данных, ручной контроль и анализ которых малоэффективен. Кроме того, финансовому специалисту всегда важно визуализировать собственный отчет перед руководством, сделать его легко читаемым. Значительно упрощает такую работу MS Excel. В данной статье рассмотрим, как с помощью функций Excel найти и разделить минимальные и максимальные платежи, как выбрать все платежи за необходимый период и выявить счета-дублеры, как информативно визуализировать отчет для руководства.

ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ УСЛОВНОГО ФОРМАТИРОВАНИЯ В EXCEL ДЛЯ КОНТРОЛЯ И УПРАВЛЕНИЯ ПЛАТЕЖАМИ

Условное форматирование применяют в Excel для выделения из общего массива данных, которые отвечают определенным условиям. Например, автоматическая заливка ячеек цветом, присвоение знака, сортировка по цвету.

 

 

Выполним анализ и контроль Реестра платежей (см. таблицу) с помощью функций условного форматирования. Реестр состоит из 22 счетов (на практике позиций в отчете, как правило, намного больше).

 

 

Контрольная операция № 1 «Максимумы и минимумы»

Финансовому контролеру необходимо определить, кому из контрагентов компания заплатила за месяц менее 5 тыс. руб., кому — более 50 тыс. руб.

Порядок действий:

1. Выделяем диапазон данных (столбец «Сумма, руб.»), а затем выбираем инструмент: ГлавнаяУсловное форматированиеПравила выделения ячеекМежду. В появившемся окне указываем диапазон для контроля от 0 до 5000 руб. и выбираем зеленую информативную заливку.

2. Аналогично задаем диапазон от 50 тыс. руб. до 1 млн руб., выбрав розовую заливку.

В итоге получаем отчет, представленный в таблице на рис. 1.

 

 

Если отчет объемный, то платежи на значительные суммы, выделенные розовым цветом, будут разбросаны по нескольким страницам, что усложняет работу. Выход из ситуации — выполнить сортировку по цвету. Выделим диапазон, после этого выполним команду: Данные Сортировка (рис. 2).

 

 

По результатам простого форматирования финансовый контролер получит визуализированный отчет (рис. 3).

 

 

Как видно из представленной таблицы-отчета, в августе 2017 г. компания выполнила платежи на сумму более 50 тыс. руб. шести контрагентам: ООО «Меркурий», ООО ТД «Главный», ИП Буравленко А. В., ООО ТД «Центральный», ООО «Альфа», ИП Жердевой А. А. Максимальный платеж уплачен 25.08.2017 ИП Жердевой А. А. согласно счету-фактуре № 3223 на сумму 247 800 руб.

Финансовый контролер должен удостовериться в надлежащем согласовании таких крупных платежей. Нужно проверить, имеются ли договоры, акты выполненных работ, соответствует ли сумма платежа графику оплат и т. д.

Платежи на сумму менее 5 тыс. руб. (зеленая заливка) необходимо сопоставить с общим уровнем кредиторской задолженности. Если компания имеет высокую задолженность перед ООО «Сандра», ООО «Бета», ООО «Кристалл», ПАО «Цемент», ООО «Юпитер», предпринимателем Жердевым М. И., то текущий месяц — самое время сделать поставщикам более существенные оплаты с целью полного погашения «кредиторки» или доказательства своей платежеспособности, возможности выполнять свои обязательства частично.

 

Замечания

  1. Аналогичный отчет для анализа получают, используя заливку трехцветной шкалой.
  2. Цветовая шкала помогает понять распределение и разброс данных.

 

Более светлый градиент свидетельствует о минимальных значениях внутри категории, более насыщенный — о максимальных. Выделим диапазон ячеек и выполним команду: Условное форматированиеЦветовые шкалы. В подчиненном меню выберем цветовую схему «Зеленый-белый» (рис. 4).

Самый темный градиент на сумме 247 800 руб., так как это максимальный платеж за период. Следующий по насыщенности — платеж от 09.08.2017 ООО ТД «Главный» (согласно счету-фактуре № 541) на сумму 114 702 руб.

 

 

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

Excel позволяет применять не только стрелки (направления), но и фигуры, индикаторы, оценки. Из режима «Наборы значков» условного форматирования можно применить гистограммы (см. рис. 5 в «Сервисе форм»). Наполненность гистограммы соответствует сумме. Чем длиннее гистограмма, тем выше сумма.

Следующая удобная для контроля функция: Условное форматированиеПравила отбора первых и последних значений. В зависимости от специфики контролируемых операций специалист может воспользоваться командами: первые 10 элементов; первые 10 %; последние 10 элементов; выше среднего; ниже среднего.

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

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

  • 03.08.2017 — ООО «Меркурий» (счет-фактура № 313);
  • 09.08.2017 — ООО ТД «Главный» (счет-фактура № 541);
  • 15.08.2017 — ИП Буравленко А. В. (счет-фактура № 3999);
  • 18.08.2017 — ООО ТД «Центральный» (счет-фактура № 21);
  • 25.08.2017 — ООО «Альфа» (счет-фактура № 000364);
  • 25.08.2017 — ИП Жердева А. А. (счет-фактура № 3223).

 

Контрольная операция № 2 «Выделить период»

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

Предположим, рассматриваемый реестр платежей плановый, сегодня пятница — 04.08.2017. Финансовому специалисту необходимо спланировать платежи на следующую неделю: Условное форматированиеПравила выделения ячеекНа следующей неделе; заливка — светло-красная (см. рис. 7 в «Сервисе форм»). На следующей рабочей неделе компании предстоит выполнить платежи шести поставщикам на сумму 158 993 руб.

 

ОБРАТИТЕ ВНИМАНИЕ

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

 

Вполне возможно, что спланированные платежи — предоплата. В условиях ограниченности денежных средств важно исключить необоснованные траты, в особо сложных ситуациях — инициировать расторжение договора и, как следствие, аннулирование счетов на предоплату по одному или нескольким контрагентам: ООО «Сандра», ООО «Бета», ООО «Кристалл», ООО ТД «Главный», ООО «Регион», ООО «Омега».

Контрольная операция № 3 «Выявить позиции-дублеры»

Для предотвращения (если документ плановый) или выявления (если документ отчетный) ошибок и махинаций Реестр платежей принято контролировать на наличие повторяющихся счетов. В Excel такой контроль можно выполнить двумя способами.

 

Способ 1

  1. Выделяем ячейки, в которых нужно найти позиции-повторы.
  2. Выполняем команду: Условное форматирование → Правила выделения ячеек → Другие правила.
  3. Устанавливаем тип правила — Форматировать только уникальные или повторяющиеся значения.
  4. Из списка выбираем повторяющиеся значения, затем цвет (формат) ячейки. Результат автоматического контроля — в таблице на рис. 8.

 

 

Способ 2

  1. Выделяем ячейки, в которых необходимо проконтролировать позиции-дублеры.
  2. Выполняем команду: Условное форматирование → Правила выделения ячеек → Повторяющие значения.
  3. Выбираем формат — цвет заливки и текста (светло-красная заливка и темно-красный текст).

Повторяющиеся значения финансовый контролер увидит еще во время выполнения команды (см. рис. 9 в «Сервисе форм»).

 

Пусть с помощью функций Excel проводят контроль в реестре фактических платежей за август 2017 г. Выявили задвоенные оплаты по счетам-фактурам:

  • № 3202 на сумму 560 руб. — поставщик ООО «Сандра»;
  • № 3209 на сумму 11 111 руб. — контрагент ООО «Регион».

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

Кроме этого, контрагенту направляют Акт сверки взаиморасчетов и письмо с просьбой вернуть ошибочно перечисленные денежные средства (если платеж был ошибочным, то контрагент быстро вернет необоснованно полученную сумму).

Важно принять меры по недопущению подобных двойных оплат в будущем. В такой ситуации тоже поможет Excel. Рассматриваемый реестр платежей плановый, и перед утверждением у руководства финансовый специалист контролирует его на наличие счетов-дублеров.

Часто реестр намного больше того, что представлен в нашем условном примере, поэтому будет удобно не искать и выделять ячейки, а сразу их удалить. Для этого финансист выполняет команду: ДанныеРабота с даннымиУдалить дубликаты. В результате получен корректный план платежей на сумму 717 145 руб. (рис. 10).

 

 

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

Для изменения и удаления примененных правил форматирования выполняют команду: Условное форматированиеУправление правилами. В окне «Диспетчер правил условного форматирования» в поле «Показать правила форматирования для:» выбирают нужный лист. В рассматриваемом случае выбираем «Этот лист».

Если к диапазону ячеек применяется два и более правила условного форматирования, то приоритет обработки определяется порядком их перечисления в данном диалоговом окне. Правило, указанное в списке выше, имеет более высокий приоритет, чем правило, расположенное ниже (рис. 11).

Если финансовый контролер намерен изменить форматирование, то он выделяет нужное правило, нажимает кнопку «Изменить правило». В появившемся окне «Изменение правила форматирования» можно задать новые условия форматирования. Для удаления одного или нескольких правил используют кнопку «Удалить правило».

 

 

Чтобы удалить сразу все примененные правила форматирования, выполняют Условное форматирование Удалить правила, а затем в подчиненном меню выбирают нужную команду.

ВМЕСТО ЗАКЛЮЧЕНИЯ

Excel значительно упрощает контрольные процедуры, позволяет быстро и информативно визуализировать отчет для руководства компании. Важно помнить, что визуализация — это лишь первый этап контроля, а не самоцель.

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

Е. С. Панченко, бизнес-консультант

Статья опубликована в журнале «Планово-экономический отдел» № 11, 2017.

Купить этот номер в электронном виде

Подпишитесь на нашу рассылку

Рассылка о новых материалах в блоге и новых номерах журналов. Отправляется в среднем 1 письмо в 2 недели.