Пример функции СУММЕСЛИ в Excel при анализе дебиторской задолженности
Практический пример применения функции Excel «СУММЕСЛИ» при анализе дебиторской задолженности из списка клиентов.
Делаем выбор компаний-контрагентов, объем денежной задолженности, которых превышает сумму издержек, в случае судебного разбирательства.
В условиях рыночной экономики подавляющее число предприятий работают по принципу отсрочки платежа. Как следствие, около девяноста процентов всех фирм непременно сталкиваются с проблемами просроченной дебиторской задолженности. Нередко дело доходит до судебных разбирательств.
При этом общий объем просроченной дебиторской задолженности может быть как незначительным, так и вовсе мизерным. Другими словами, нет особого смысла доводить дело до суда, потому как уровень судебных издержек превышает объем просроченной оплаты.
Сегодня мы будем составлять каталог компаний-неплательщиков, а также определять те предприятия-должники, на которых можно будет подавать в судебные инстанции, рассчитывая на положительный исход дела (при учете выплаты всех набежавших процентов).
Для этого будет использоваться оператор «СУММЕСЛИ» в документе Excel.
Начинать сортировку контрагентов-должников нужно с того, что в документе Excel создается каталог фирм следующего вида:
Применив пакет информации о времени отгрузки товара (либо поставки услуги) можно вычислить крайний временной срок оплаты по дебиторской задолженности, исходя из представленной отсрочки. Необходимо прибавить число дней предоставленной отсрочки ко дню, когда была произведена физическая поставка продукции.
Дабы можно было добавить количество дней ко времени отгрузки товара в файле Excel, следует выполнить следующий порядок действий.
Вычисление всех просроченных суток
Для начала активируется соответствующая ячейка, после чего в ней же прописывается соответствующая информация (без пробелов), а также производится активация необходимых полей следующим образом:
= ДАТА + ЧИСЛО и нажимаем ENTER.
В случае если дата не была отображена в таблице соответствующим образом, следует проверить формат поля, и увериться в том, что он установлен на значении «Дата»:
Если же все действия выполнены правильно, получаем следующее:
Теперь можно приступить к расчету всех просроченных суток. Для этого следует вычесть с текущей даты расчета день последнего срока оплаты дебиторской задолженности.
Быстро и эффективно вычесть в документе Excel из одной даты иную, следует сделать следующее:
Проводится активация необходимой ячейки, после чего в ней же прописывается (БЕЗ ПРОБЕЛОВ) необходимое число. Также можно произвести активацию ячеек следующим образом:
= ДАТА(1) – ДАТА(2) ENTER.
Если в конечном результате дата отобразилась некорректно, стоит проверить, какой установлен на данной ячейке формат. Если же установлен какой-нибудь другой, отличающийся от «числового», его стоит заменить.
В текущем случае будем ссылаться только на одну и ту же дату, а потому можно воспользоваться абсолютной ссылкой.
Протянув нашу формулу до конца таблицы Excel, мы получим следующее:
Теперь можно приступать к расчету начисленного процентного штрафа за просрочку оплаты.
Расчет пени за все время просрочки
Рассмотрим два возможных варианта:
Ежедневно на сумму просроченной дебиторской задолженности начислялся 0,1 процент в сутки.
Исходим из объема рефинансирования на время расчета (возьмем десять процентов)
Расчет начисленной пени в документе Excel:
1. Ежедневно на сумму просроченной задолженности начислялось 0,1 процент
Проводим активацию необходимой ячейки в таблице, а также прописываем в ней соответствующие числовые значения (БЕЗ ПРОБЕЛОВ). Проводим активацию следующим образом:
= 0,1 процент х объем просроченной задолженности х число просроченных календарных суток ENTER.
2. Процент рефинансирования на время расчета с контрагентом (десять процентов).
Производим активацию необходимой ячейки в Excel. Можем также активировать требуемые поля следующим способом:
= 10 процентов х Объем задолженности контрагента/365 х Число просроченных календарных дней ENTER.
Просуммируем рассчитанный объем набежавшей пени и общую сумму просроченной дебиторской задолженности.
Теперь можно приступить к ранжировке тех предприятий-должников, на которых есть экономический смысл подавать в судебные инстанции.
Логический оператор «СУММЕСЛИ»
Допустим ситуацию, при которой стоимость судебных издержек достигает 950 российских рублей в расчете на одного клиента. Дабы узнать объем денежных средств, на которую можно рассчитывать после положительного завершения судебной тяжбы, используем логический оператор.
Сделаем активную ячейку, в которой должен быть получен расчет денежных средств. Находим в меню (выделено красным цветом) «Математические» и выбираем «СУММЕСЛИ».
Теперь посмотрим, что нужно указать:
Давайте разбираться по порядку.
Окно под названием «Диапазон» необходимо с той целью, дабы можно было выбрать сферу значений, среди которых будет произведена необходимая нам выборка. В данном случае, это должна быть вся информация в последнем столбике, помимо итогового объема денежных средств.
Окно «Критерий» необходимо для того, чтобы можно было задать требуемые условия ранжировки. В текущем случае, этот диапазон будет достигать «>950».
«Диапазон суммирования» требуется для установления значений, объем которых нам необходимо рассчитать при учете условия. В частности, окно «Диапазон» будет совпадать с «Диапазон суммирования» по причине того, что нами устанавливается числовое значение, которое имеет прямое отношение исключительно к последнему столбцу.
После того как заполнены все поля:
По результату произведенных расчетов, мы получаем общий объем денежных средств, планирующихся к получению при исключении всех компаний-должников, чей объем долга не превышает девятьсот пятьдесят российских рублей (стоимости судебного разбирательства).
Для практической демонстрации возможностей «СУММЕСЛИ» приведем еще одну выборку контрагентов.
В первом приведенном примере «Диапазон суммирования» и «Диапазон» полностью совпали. Теперь же рассмотрим случай, при котором в данных столбцах указываются совершенно иные значения.
К примеру, наша задача заключается в расчете объема денежного долга только по «Контрагенту №1»
В данном случае, нам следует в окне «Диапазон» проставить значения первого столбца, а в окне «Критерий» вручную прописать «Контрагент №1». Также можно выбрать любую ячейку, которая содержала бы в себе подобные значения.
В окне «Диапазон суммирования» стоит выбрать последний столбец, потому как нам необходимо выбрать дебиторскую задолженность по Контрагенту №1 из общего объема.
Получаем следующий результат:
Как видно из представленного примера, функция Excel «СУММЕСЛИ» располагает широким спектром возможностей, а при правильном использовании может оказать незаменимую помощь для решения многих задач.
