Судебные аудиторы могут использовать Excel, чтобы быстро просматривать сотни тысяч записей и находить подозрительные транзакции. В этом сегменте мы рассмотрим некоторые из этих методов.
Случай 1:
Адреса поставщиков и адреса сотрудников
Используйте функцию ПОИСКПОЗ, чтобы сравнить числовую часть уличного адреса в записях вашего сотрудника с числовой частью уличного адреса ваших поставщиков. Есть ли вероятность, что некоторые сотрудники также продают услуги компании?
- Начните со списка поставщиков и списка сотрудников.
- Формула, например
=LEFT(B2,7), изолирует числовую часть адреса улицы и несколько первых букв названия улицы.
- Создайте аналогичную формулу, чтобы изолировать ту же часть адресов поставщиков.
- Функция ПОИСКПОЗ будет искать часть адреса в C2 и пытаться найти совпадение в частях поставщика в H2: H78. Если совпадение найдено, в результате будет указан относительный номер строки, в которой найдено совпадение. Если совпадений не найдено, будет возвращено # N / A.
- Любые результаты в столбце MATCH, которые не имеют # N / A, являются потенциальными ситуациями, когда сотрудник также выставляет счет компании как поставщику. Отсортируйте по столбцу СООТВЕТСТВИЕ по возрастанию, и все записи о проблемах появятся вверху.
Случай 2:
Необычные колебания в базе данных поставщиков
У компании 5000 продавцов. Мы воспользуемся точечной диаграммой, чтобы визуально найти 20 поставщиков, которых следует проверить.
- Получите список идентификаторов поставщика, количества счетов-фактур, общей суммы счета-фактуры за этот год.
- Получите список идентификаторов поставщика, количества счетов-фактур, общей суммы счета-фактуры за предыдущий год.
- Используйте ВПР, чтобы сопоставить эти списки с пятью столбцами данных:
- Добавьте новые столбцы для Дельты подсчета и Дельты суммы:
- Выберите данные в H5: G5000. Вставьте диаграмму рассеяния (XY). Большинство результатов будет сгруппировано в середине. Вас интересуют выбросы. Начните с продавцов в области, отмеченной рамкой; они отправили меньше счетов на гораздо большую сумму в долларах:
Заметка
Чтобы найти поставщика, связанного с точкой, наведите указатель мыши на точку. Excel сообщит вам дельту счета и дельту суммы, которые необходимо найти в исходном наборе данных.
Случай 3:
Использование сводной таблицы для детализации
В этом случае мы смотрим на счета-фактуры и дебиторскую задолженность. Посредством различных анализов данных выясните, какие два аналитика по дебиторской задолженности проводят после обеда в пятницу в баре вместо работы.
- Я начал с двух наборов данных. Первый - это данные счета, счет, дата, клиент, сумма.
- Следующие данные - счет-фактура, дата получения, полученная сумма, имя представителя A / R.
- Рассчитайте количество дней к оплате. Это Дата получения - Дата выставления счета. Отформатируйте результат как число вместо даты.
- Рассчитайте день недели. Это
=TEXT(ReceiptDate,"dddd") - Выберите одну ячейку в наборе данных. Используйте данные - сводная таблица (Excel 97-2003) или вставка - сводная таблица (Excel 2007)
- В первой сводной таблице было указано количество дней до выплаты. Щелкните правой кнопкой мыши одно значение и выберите Group and Show Detail - Group. Группируйте по 30-дневным периодам.
- Переместите дни к оплате в область столбца. Поместите клиентов в область ряда. Поместите доход в область данных. Теперь вы можете видеть, какие клиенты не спешат платить.
- Удалите Дни к оплате и поместите День недели в поле столбца. Удалите клиента и поместите представителя в область строк. Теперь вы можете видеть суммы, полученные по дням недели.
- Выберите ячейку в области данных. Нажмите кнопку «Параметры поля» (на панели инструментов сводной таблицы в Excel 97–2003 или на вкладке «Параметры» в Excel 2007).
- В Excel 97-2003 нажмите «Еще». В Excel 2007 щелкните вкладку «Показать значения как». Выберите% строки.
- Результат: кажется, что Боб и Соня обрабатывают гораздо меньше счетов в пятницу, чем другие. Загляните к ним в офис в пятницу днем, чтобы узнать, а) они ли действительно работают, и (б) есть ли стопка необработанных чеков, болтающихся в ящике их стола до пятницы.
