Задача специалиста по экономике и финансам — контролировать и анализировать отчетность. Часто такая отчетность представлена большим массивом данных, ручной контроль и анализ которых малоэффективен. Кроме того, финансовому специалисту всегда важно визуализировать собственный отчет перед руководством, сделать его легко читаемым. Значительно упрощает такую работу 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 тыс. руб. (зеленая заливка) необходимо сопоставить с общим уровнем кредиторской задолженности. Если компания имеет высокую задолженность перед ООО «Сандра», ООО «Бета», ООО «Кристалл», ПАО «Цемент», ООО «Юпитер», предпринимателем Жердевым М. И., то текущий месяц — самое время сделать поставщикам более существенные оплаты с целью полного погашения «кредиторки» или доказательства своей платежеспособности, возможности выполнять свои обязательства частично.
Замечания
- Аналогичный отчет для анализа получают, используя заливку трехцветной шкалой.
- Цветовая шкала помогает понять распределение и разброс данных.
Более светлый градиент свидетельствует о минимальных значениях внутри категории, более насыщенный — о максимальных. Выделим диапазон ячеек и выполним команду: Условное форматирование → Цветовые шкалы. В подчиненном меню выберем цветовую схему «Зеленый-белый» (рис. 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
- Выделяем ячейки, в которых нужно найти позиции-повторы.
- Выполняем команду: Условное форматирование → Правила выделения ячеек → Другие правила.
- Устанавливаем тип правила — Форматировать только уникальные или повторяющиеся значения.
- Из списка выбираем повторяющиеся значения, затем цвет (формат) ячейки. Результат автоматического контроля — в таблице на рис. 8.
Способ 2
- Выделяем ячейки, в которых необходимо проконтролировать позиции-дублеры.
- Выполняем команду: Условное форматирование → Правила выделения ячеек → Повторяющие значения.
- Выбираем формат — цвет заливки и текста (светло-красная заливка и темно-красный текст).
Повторяющиеся значения финансовый контролер увидит еще во время выполнения команды (см. рис. 9 в «Сервисе форм»).
Пусть с помощью функций Excel проводят контроль в реестре фактических платежей за август 2017 г. Выявили задвоенные оплаты по счетам-фактурам:
- № 3202 на сумму 560 руб. — поставщик ООО «Сандра»;
- № 3209 на сумму 11 111 руб. — контрагент ООО «Регион».
Расследование ведут по следующим направлениям: кто допустил ошибку, была ошибка или преднамеренные финансовые махинации, кто разрешил оплату, почему не нашли задвоение на этапе согласования платежей, до фактической оплаты.
Кроме этого, контрагенту направляют Акт сверки взаиморасчетов и письмо с просьбой вернуть ошибочно перечисленные денежные средства (если платеж был ошибочным, то контрагент быстро вернет необоснованно полученную сумму).
Важно принять меры по недопущению подобных двойных оплат в будущем. В такой ситуации тоже поможет Excel. Рассматриваемый реестр платежей плановый, и перед утверждением у руководства финансовый специалист контролирует его на наличие счетов-дублеров.
Часто реестр намного больше того, что представлен в нашем условном примере, поэтому будет удобно не искать и выделять ячейки, а сразу их удалить. Для этого финансист выполняет команду: Данные → Работа с данными → Удалить дубликаты. В результате получен корректный план платежей на сумму 717 145 руб. (рис. 10).
Важно не переусердствовать с применением условного форматирования и иных функций.
Для изменения и удаления примененных правил форматирования выполняют команду: Условное форматирование → Управление правилами. В окне «Диспетчер правил условного форматирования» в поле «Показать правила форматирования для:» выбирают нужный лист. В рассматриваемом случае выбираем «Этот лист».
Если к диапазону ячеек применяется два и более правила условного форматирования, то приоритет обработки определяется порядком их перечисления в данном диалоговом окне. Правило, указанное в списке выше, имеет более высокий приоритет, чем правило, расположенное ниже (рис. 11).
Если финансовый контролер намерен изменить форматирование, то он выделяет нужное правило, нажимает кнопку «Изменить правило». В появившемся окне «Изменение правила форматирования» можно задать новые условия форматирования. Для удаления одного или нескольких правил используют кнопку «Удалить правило».
Чтобы удалить сразу все примененные правила форматирования, выполняют Условное форматирование → Удалить правила, а затем в подчиненном меню выбирают нужную команду.
ВМЕСТО ЗАКЛЮЧЕНИЯ
Excel значительно упрощает контрольные процедуры, позволяет быстро и информативно визуализировать отчет для руководства компании. Важно помнить, что визуализация — это лишь первый этап контроля, а не самоцель.
Предварительные контрольные результаты, полученные форматированием ячеек отчета, нужно анализировать, искать причины, выявлять виновных лиц, вырабатывать меры по недопущению аналогичных проблем в будущем.
Для физических лиц
Для юридических лиц
Подписка по каталогам