Работа с excel для платежей

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

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

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

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

где:

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.



2. Составим график погашения кредита. Пока пустой.



3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.



Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

где:

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9<=$B$4;$B$2/$B$4;0).

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:

• взяли кредит 500 000 руб.;
• вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
• переплата составила 184 881, 67 руб.;
• процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
• Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

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

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

Скачать кредитный калькулятор в Excel

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

Оплаты в excel. Реестр учета платежей в excel

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

Ссылки для скачивания:

— Скачать реестр платежей в excel — оплаты в excel

Как начать работу с файлом оплаты в excel

1. Разархивируйте скачанный архив с реестром платежей с помощью 7zip или WinRar.

2. При появлении сообщения о доверенном источнике: закройте программу, кликните правой кнопкой мыши на файле — «Свойства», далее установите галочку напротив «Разблокировать».

3. Если в Вашем Excel запуск макросов по умолчанию отключен, в данном окне необходимо нажать «Включить содержимое».

4. Для полноценной работы с файлом необходимо нажать «Разрешить редактирование» при появлении данного сообщения.

Как завести оплаты в excel и заполнить справочники реестра платежей

Первую настройку макроса оплаты в excel необходимо начать с заполнения справочников, на основании которых будут формироваться дальнейшие варианты полей выбора. Таким образом, остановимся подробнее на редактировании листа «Справочники».

В первой колонке необходимо, по возможности, указать все основные статьи оплат, которые совершает организация в процессе ведения своей деятельности. Далее идет справочник контрагентов, с которыми осуществляется взаимодействие. В столбце «Плательщик» указываем наименование юр. лица (или юр. лиц), от которого будут совершаться платежи. В колонке «Тип платежа» вводим любую внутреннюю классификацию оплат (для примера платежи разделены на обязательные и необязательные). «Платим» — столбец окончательного решения по платежу (по умолчанию стоит 0 или 1). «Статус платежа» может быть заполнен в соответствии с внутренними процессами согласования и проведения выплат в организации. Справочники могут быть отредактированы в дальнейшем, при появлении новых данных.

Важно! Не удаляйте значение Все из второй строки каждой колонки справочника!

Далее переходим к заполнению повторяющихся (ежемесячных, ежеквартальных и т.д.) платежей. Основа, которая является постоянной и переходит из одного периода в другой.

Вносим сумму, выбираем контрагента из выпадающего списка (редактирование на листе Справочник). Таким же образом выбираем юр. лицо плательщика, тип платежа, вводим наименование товара или услуги. Указываем предельный срок оплаты. Дата выбирается с помощью встроенного календаря, вызов которого происходит по нажатию на ячейке правой кнопкой мыши. Заполняем номер счета и номер договора. Осуществляем выбор статьи расхода из выпадающего списка (также редактируется в справочнике). В завершении вводим через календарь дату выставления счета (если известна к моменту заполнения). В верхней части таблицы для наглядности показаны суммы оплат с помощью стандартных формул excel. Итоги считаются по первым двум введенным плательщикам.

Когда оплаты введены в excel, справочники инициализированы, можно переходить на лист «Планирование» для построения автоматизированного реестра платежей на месяц.

В левую часть листа автоматически подтягиваются статьи оплат из справочника реестра платежей в excel. Здесь можно указать плановые месячные значения по каждому из пунктов. Выбираем год и месяц целевого учета, указываем масштаб листа реестра, нажимаем кнопку «Создать реестр на месяц».

Работа с реестром платежей в excel

В результате работы макроса создается месячный лист реестра платежей, в который переносятся все постоянные оплаты с листа «Ежемесячные платежи».

Для того чтобы добавить в реестр очередную оплату необходимо воспользоваться кнопкой «Добавить платеж». Новая строка появится сразу под заголовком таблицы.

Функция «Сводка за день» соберет для вас в отдельном отчете все платежи за указанное число, которое совпадает с датой в колонке «Дата оплаты». Напомню, что для заполнения дат необходимо использовать встроенный календарь, который вызывается кликом по правой кнопке мыши на соответствующей ячейке ввода даты.

«Сводка по статьям» создаст сводный лист в разрезе статей оплат. Сравнит полученные данные с плановыми показателями с листа «Планирование». И покажет отклонение от текущего плана.

В левом верхнем углу таблицы реализован блок фильтров, который позволяет сгруппировать нужные данные для упрощения поиска информации по оплатам. По умолчанию при создании в фильтре указано значение «Все». Выбранная комбинация параметров отражается в колонке «Фильтр», которая принимает значение Истина или Ложь. Формулы в данном столбце ручному редактированию не подлежат!

Заказать разработку программы или доработку реестра платежей в excel

Благодарю Вас за ознакомление с данной инструкцией!

Если мой продукт вам оказался полезен, но Вы хотели бы доработать функционал или донастроить его под логику ведения учета Вашей компании, или заказать любую иную расчетную программу/макрос, проконсультироваться по возможности автоматизации процессов своего бизнеса, связаться со мной можно через WatsApp +79507094770, сайт excellab.ru или написать мне на почту: goryaninov@bk.ru

Ссылки для скачивания:

— Скачать реестр платежей в excel — оплаты в excel

Предлагаю Вашему вниманию другие полезные программы по учету и автоматизации, разработанные мной и описанные в следующих статьях:

— Дневной табель учета рабочего времени в excel
— Почасовой табель учета рабочего времени в excel
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен
— Учет доходов и расходов
— Автоматизированное формирование документов
— Учет выдачи инструментов
— Учет производства. Расчет сдельной заработной платы

Расчет платежей по кредиту удобнее и быстрее с помощью Microsoft Office Excel. Ручной расчет занимает намного больше времени. В этой статье речь пойдет об аннуитетных выплатах, особенностях их расчета, преимуществах и недостатках.

Что такое аннуитетный платеж

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

Кроме того, проценты по кредиту уже включены в общую сумму, выплачиваемую банку.

Классификация аннуитета

Аннуитетные выплаты можно разделить на следующие виды:

1. Фиксированный. Платежи, которые не меняются, имеют фиксированную ставку независимо от внешних условий.
2. Валюта. Возможность изменения суммы платежа в случае падения или повышения курса обмена.
3. Проиндексировано. Выплаты в зависимости от уровня, уровня инфляции. В период аккредитации их размер часто меняется.
4. Переменные. Доход, который может меняться в зависимости от состояния финансовой системы, инструментов.

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

Преимущества и недостатки аннуитетных платежей

Чтобы лучше разобраться в предмете, необходимо изучить основные характеристики этого вида кредитных выплат. Он имеет следующие преимущества:

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

Не обошлось и без недостатков:

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

Из чего состоит платеж по кредиту?

Аннуитетный платеж состоит из следующих компонентов:

• Переплаченные проценты при погашении кредита.
• Часть причитающегося капитала.

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

Как упоминалось выше, вы можете работать с различными типами ссуд и платежей по ссуде в Microsoft Office Excel. Аннуитет не исключение. В общих чертах формула, по которой можно быстро рассчитать аннуитетные взносы, выглядит следующим образом:  

Важно! Вы не можете раскрывать скобки в знаменателе данного выражения, чтобы упростить его.

Основные значения формулы расшифровываются следующим образом:

• AP — аннуитетный платеж (сокращенное наименование).
• О — размер основного долга заемщика.
• PS — процентная ставка, предлагаемая ежемесячно конкретным банком.
• С — количество месяцев, в течение которых предоставляется кредит.

Для усвоения информации достаточно привести несколько примеров использования этой формулы. О них и пойдет речь ниже.

Примеры использования функции ПЛТ в Excel

Вот простая постановка проблемы. Рассчитывать ежемесячный платеж по кредиту необходимо, если банк представляет процентную ставку 23%, а общая сумма составляет 25000 рублей. Срок кредита составляет 3 года. Задача решается по алгоритму:

1. Создайте общую таблицу в Excel на основе исходных данных.

Таблица составлена ​​исходя из состояния задачи. Фактически, вы можете использовать другие столбцы, чтобы подогнать его под

1. Активируйте функцию PMT и введите аргументы для нее в соответствующее поле.
2. В поле «Ставка» напишите формулу «B3 / B5». Это будет процентная ставка по кредиту.
3. В строке «Nper» напишите значение в виде «B4 * B5». Это будет общее количество выплат за весь срок кредита.
4. Заполните поле «Ps». Здесь необходимо указать начальную снимаемую с банка сумму, прописав значение «В2».

Действия, необходимые в окне «Аргументы функции». Вот порядок заполнения каждого параметра

1. Убедитесь, что после нажатия «ОК» в исходной таблице значение «Ежемесячный платеж» было рассчитано».

Окончательный результат. Ежемесячный платеж рассчитан и выделен красным

Дополнительная информация! Отрицательное число указывает на то, что заемщик тратит деньги.

Пример расчета суммы переплаты по кредиту в Excel

В этой задаче необходимо рассчитать сумму, которую человек, взявший ссуду в размере 50 000 рублей с процентной ставкой 27% на 5 лет, переплатит. Всего заемщик производит 12 платежей в год. Решение:

1. Создайте таблицу исходных данных.

Таблица составлена ​​исходя из состояния задачи

1. Вычтите начальную сумму из общей суммы платежа по формуле «= ABS (PMT (B3 / B5; B4 * B5; B2) * B4 * B5) -B2». Его необходимо ввести в строке формул вверху главного меню программы.
2. В результате сумма переплат появится в последней строке созданного банка. На сумму более 41 606 рублей заемщик заплатит больше.

Окончательный результат. Почти двукратная переплата

Формула вычисления оптимального ежемесячного платежа по кредиту в Excel

Задача с условием: клиент зарегистрировал банковский счет на 200 000 рублей с возможностью ежемесячного пополнения. Необходимо рассчитать сумму платежа, которую человек должен производить каждый месяц, чтобы через 4 года на его счету было 2 000 000 рублей. Ставка 11%. Решение:

1. Нарисуйте тарелку исходя из исходных данных.

Таблица составлена ​​из данных состояния проблемы

1. Введите формулу «= PMT (B3 / B5; B6 * B5; -B2; B4)» в строке ввода Excel и нажмите «Enter» с клавиатуры. Буквы будут отличаться в зависимости от того, в каких ячейках находится таблица.
2. Убедитесь, что сумма платежа рассчитана автоматически в последней строке таблицы.

Окончательный результат расчета

Примечание! Таким образом, чтобы покупатель накопил на счете клиента 2 000 000 рублей по ставке 11% за 4 года, он должен ежемесячно вносить 28 188 рублей. Минус в сумме указывает на то, что клиент терпит убытки, отдавая деньги банку.

Особенности использования функции ПЛТ в Excel

В общих чертах эта формула записывается следующим образом: = PMT (rate; nper; ps; [bs]; [type]). Функция имеет следующие характеристики:

1. При расчете ежемесячных платежей учитывается только годовой платеж.
2. При указании процентной ставки важно производить пересчет исходя из количества годовых платежей.
3. Конкретное число указывается в формуле вместо аргумента Nper. Это период выплаты долга.

Расчет оплаты

Как правило, аннуитетный платеж рассчитывается в два этапа. Чтобы разобраться в теме, нужно рассматривать каждый из этапов отдельно. Об этом и пойдет речь далее.

Этап 1: расчет ежемесячного взноса

Чтобы рассчитать сумму, которую вы должны платить ежемесячно по ссуде с фиксированной ставкой в ​​Excel, вам необходимо:

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

Начальные действия

1. Выберите «PMT» в списке функций и нажмите «OK».

Выбор функции в специальном окне

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

Алгоритм действий по заполнению аргументов функции «PMT»

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

Окончательный результат расчетов

Важно! После расчета гранта можно будет рассчитать сумму, которую заемщик переплатит за весь срок кредита.

Этап 2: детализация платежей

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

1. Составьте справочную таблицу на 24 месяца.

Исходный массив таблиц

1. Поместите курсор в первую ячейку таблицы и вставьте функцию «OSPLT».

Выбор функции детализации платежа

1. Таким же образом дополните аргументы функции.

Заполните все строки в окне аргументов для оператора e

1. При заполнении поля «Период» необходимо указать на табличке первый месяц, указав ячейку 1.

Заполнив тему «Срок»

1. Убедитесь, что первая ячейка столбца «Платеж кредитной организации» заполнена.
2. Чтобы заполнить все строки первого столбца, нужно растянуть ячейку до конца таблицы

Заполнение оставшихся строк

1. Выберите функцию «PRPLT», чтобы заполнить второй столбец таблицы.
2. Заполните все темы в открывшемся окне, как показано на скриншоте ниже.

Заполнение аргументов для оператора «PRPLT»

1. Рассчитайте общий ежемесячный платеж, сложив значения в двух предыдущих столбцах.

Расчет ежемесячных платежей

1. Чтобы рассчитать «Кредиторский остаток», вам нужно сложить процентную ставку с выплатой по основной части ссуды и растянуть до конца банка, чтобы заполнить все месяцы ссуды.

Расчет остатка к оплате

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

Расчет аннуитетных платежей по кредиту в Excel

Функция PMT отвечает за расчет аннуитета в Excel. Принцип расчета в общих чертах заключается в выполнении следующих шагов:

1. Создайте таблицу исходных данных.
2. Составьте план погашения долга на каждый месяц.
3. Выберите первую ячейку в столбце «Платежи по ссуде» и введите формулу для расчета «PMT ($ B3 / 12; $ B $ 4; $ B $ 2)».
4. Растягивает полученное значение для всех столбцов в горшке.

Результат функции PMT

Расчет в MS Excel погашение основной суммы долга

Аннуитетные выплаты должны производиться ежемесячно в определенных размерах. Кроме того, процентная ставка не меняется.

Вычисление остатка суммы основного долга (при БС=0, тип=0)

Допустим, кредит в 100000 рублей взят на 10 лет под 9%. Необходимо рассчитать сумму основного долга за 1 месяц 3-го года. Решение:

1. Создайте таблицу и рассчитайте ежемесячный платеж, используя приведенную выше формулу PS.
2. Рассчитайте сумму взноса, необходимую для погашения части долга, по формуле «= -PMT- (PS-PS1) * ставка = -PMT- (PS + PMT + PS * ставка)».
3. Рассчитайте сумму основного долга за 120 периодов по известной формуле.
4. С помощью оператора ПРПЛТ найдите количество выплаченных процентов за 25-й месяц.
5. Проверить результат.

Вычисление суммы основного долга, которая была выплачена в промежутке между двумя периодами

Этот расчет лучше всего производить просто. Для расчета суммы за два периода необходимо использовать следующие формулы:

• = «- BS (ставка; end_period; plt; [ps]; [type])) / (1 + type * rate)».
• = «+ BS (rate; start_period-1; plt; [ps]; [type])) / SE (start_perio = 1; 1; 1 + type * rate)».

Примечание! Буквы в скобках заменяются конкретными значениями.

Досрочное погашение с уменьшением срока или выплаты

Если вам нужно сократить срок кредита, вам нужно будет произвести дальнейшие расчеты с помощью оператора IF. Таким образом можно будет проверить нулевой баланс, который не должен быть достигнут до истечения срока платежа.

Досрочное погашение с сокращенным сроком погашения

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

Снижение выплат по кредиту

Кредитный калькулятор с нерегулярными выплатами

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

1. Введите дни месяца, за которые производятся выплаты, и укажите их количество.
2. Проверьте отрицательные и положительные суммы. Отрицательный вариант предпочтительнее.
3. Подсчитайте дни между двумя датами внесения денег.

Расчет периодического платежа в MS Excel. Срочный вклад

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

Заключение

Таким образом, рассчитывать аннуитетные платежи в Excel проще, быстрее и эффективнее. За их расчет отвечает оператор PMT. Подробные примеры можно найти выше.

Как построить в Excel модель для управления платежами?

Как настроить Журнал входящих заявок?

Как визуализировать представленную в нем информцию?

В чем преимущества такой модели управления платежами?

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

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

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

Этап 1. Прорабатываем справочники и аналитики

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

Задействованные аналитики представлены на рис. 1. Их набор типичен для любой информационной среды по управлению платежами.

Центры финансовой ответственности (ЦФО) определяются системой бюджетирования компании. Руководители ЦФО несут ответственность за своевременное инициирование и согласование заявок на платежи. Дополнительно регламентируют, на какие виды платежей имеет право подавать заявки каждый ЦФО.

Если системы бюджетирования в компании нет, перечень ЦФО определяется штатным расписанием и должностными обязанностями персонала. При необходимостив качестве ЦФО указывают отдельного сотрудника.

Руководитель ЦФО при направлении в финансовую службу компании заявки на исходящий платеж обязан:

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

В рассматриваемой Excel-модели три приоритета платежей:

• высокий;
• средний;
• низкий.

Их может быть больше, но финансовые специалисты не рекомендуют использовать более пяти, так как это снижает управляемость платежами.

Платеж может иметь один из трех статусов: заявлен, оплачен, частично (частичная оплата). При необходимости перечень расширяют:

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

Это легко сделать, так как каждая таблица с аналитиками отформатирована как «умная» — каждую можно легко дополнить, не сбивая настроек Excel-модели.

Однако если будет задействовано больше статусов, их нужно будет отражать в Журнале заявок. Это увеличит трудозатраты учетного персонала на документооборот и обработку заявок.

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

Фрагмент Справочника контрагентов представлен в табл. 1. Согласно этому Справочнику в группу компаний входят ИП Михайлов А. Е., ООО «Карьер», ООО «Спец-сервис». В условиях дефицита денежных средств часто исключают платежи аффилированным компаниям, оплачивают только внешним контрагентам. В таблице 1 первая позиция — «– без контрагента». Она введена в Справочник, чтобы в Журнале заявок не оставлять пустые поля. Ее используют для таких платежей, как зарплата, налоги, социальные взносы (на усмотрение руководства).

В таблице 2 представлен фрагмент Справочника статей движения денежных средств. Его функции:

• увязать заявки на исходящие оплаты с бюджетом;
• систематизировать заявки;
• упростить ввод заявок — не нужно указывать в Журнале содержание счета (за пигменты, подшипники, ремни, электродвигатели и т. д.).

Важно предусмотреть информативную цифровую кодификацию статей движения денежных средств (ДДС). Коды и статьи соединены сцепкой (рис. 2) =A2&» «&B2, добавлены пробелы (в кавычках).

Для заполнения графы «Приоритет» применен выпадающий список (см. рис. 2): вкладка Данные → Работа с данными → Проверка данных. В окне Проверка вводимых значений указывают Тип данных → Список.