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