Excel поможет избежать перерасхода денег

10983
Ходарев Антон
финансовый управляющий ГК «Сапсан», д.э.н.
Проверка заявок на платеж на соответствие лимитам по статьям бюджета – задача не самая сложная. При условии, что все реквизиты платежа и другие атрибуты документа указаны верно. С подобной работой прекрасно справится любой специалист финансовой службы. Но при ручной проверке всегда есть риск. Человек может устать, ошибиться при вводе, посмотреть не на ту статью бюджета, особенно если речь идет об огромной таблице. Поэтому застраховаться от возможных проблем, воспользовавшись одной из функций Excel, будет совсем не лишним.

Стандартная функция «СУММЕСЛИ», реализованная в Excel, может творить чудеса. К примеру, с ее помощью, прежде чем согласовать очередную заявку на платеж, можно легко и быстро проверить, приведет ли это к перерасходу по той или иной статье бюджета движения денежных средств (БДДС).

Допустим, у торговой компании есть бюджет движения денежных средств. В нем в отдельный блок выделены статьи, платежи по которым осуществляются на основе поступающих заявок от подразделений (оплата товаров поставщикам, аренда, транспортные расходы). А также предусмотрены графы, названия которых говорят сами за себя: «План на месяц»; «Факт на текущую дату»; «Сумма заявленных, но не исполненных на текущую дату платежей»; «Ожидаемые отклонения от бюджета при выполнении всех заявок».

Далее. Основные сведения из заявок на платеж, поступающих от руководителей подразделений, отражаются в реестре платежей. Где фиксируется дата подачи заявки, реквизиты счета, подразделение, инициировавшее платеж, и сумма. А главное – для каждой заявки указывается бюджетная статья, в рамках которой будет осуществлена выплата, и статус заявки – «оплачено» или нет (в последнем случае графа не заполняется).

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

=F14&« »&H14,

где F14 – ссылка на ячейку, в которой указывается, по какой статье бюджета запланирована выплата;

Н14 – ссылка на ячейку со статусом заявки (оплачено или нет), а знак & – функция, объединяющая текстовые значения. Пробел в кавычках (« »), присутствующий в формуле, введен в формулу для того, чтобы текст не «слипался». Если в ячейке F14 – указано «Оплата товаров», Н14 – «оплачено», то выше описанная формула выдаст вот такой результат: «Оплата товаров оплачено».

Рисунок 1 Усовершенствованный реестр заявок на платеж

Excel поможет избежать перерасхода денег

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

Вот как это сделать. Копируем на тот же лист, где находится реестр платежей, перечень наименований статей БДДС. Лучше их разместить в ячейках над реестром, а точнее, над графой «Статья бюджета», в которой указывается, какой бюджетной статье будет сопоставлена та или иная заявка. Потом вставленные наименования, а вернее, строки, можно будет скрыть, чтобы не мешали. Дальше выделяем в реестре платежей ячейки столбца «Статья бюджета». Лучше выделить с запасом – например, 500 ячеек. Все будет зависеть от того, сколько заявок в компании обычно формируется за месяц. Открываем меню «Данные» и переходим в подменю «Проверка». В открывшемся окне «Проверка вводимых значений» на вкладке «Параметры» задаем тип данных – «список» (см. рис. 2).

Рисунок 2 Окно функции «Проверка»: ввод условия на проверку названий статей БДДС

Excel поможет избежать перерасхода денег

После этого в появившейся строке «Источник» достаточно ввести ссылку на недавно скопированный перечень наименований бюджетных статей (в нашем случае она выглядит так: =$F$1:$F$8). После этого останется кликнуть кнопку «Ок». Перейдя на одну из ячеек в реестре, куда будут вводиться наименования статей бюджета движения денежных средств, вы увидите, что рядом с ячейкой появилась стрелка. Теперь система не позволит ошибиться в названии статьи. А это очень важно.

А теперь перейдем в бюджет движения денежных средств и воспользуемся секретным оружием Excel – функцией «СУММЕСЛИ». Начнем с графы «Факт на текущую дату» (см. рис. 3). Сумму фактических выплат можно определить, суммировав все выплаты по заявкам со статусом «оплачено». И при этом не забыть, что надо отобрать не просто все оплаченные заявки, а еще и распределить их по статьям бюджета.

 Рисунок 3 Бюджет движения денежных средств с формулой подсчета сумм платежей по заявкам

 Excel поможет избежать перерасхода денег

Например, определим, сколько средств потратила компания на оплату товаров. Для этого в ячейку на пересечении строки «Оплата товаров» и графы «Факт на текущую дату» в бюджете движения денежных средств вводим формулу:

=СУММЕСЛИ(‘Реестр заявок на платеж’!I14:I57;«Оплата товаров оплачено»;’Реестр заявок на платеж’!G14:G39),

где 'Реестр заявок на платеж'!I14:I57 – ссылка на все ячейки технического столбца в реестре заявок. Реестр расположен на отдельном листе в книге Excel, поэтому выглядит так громоздко. Перебирая данные из этого столбца, функция будет либо складывать суммы заявок, либо игнорировать их;

«Оплата товаров оплачено» – это и есть критерий, которым будет руководствоваться программа, решая, учитывать тот или иной платеж или нет. Совпадает значение в ячейках технического столбца с этой фразой – складываем, нет – пропускаем;

'Реестр заявок на платеж'!G14:G39 – ссылка на все ячейки, содержащие суммы по заявкам на платеж.

Аналогичные формулы вводим для всех статей, оплата по которым осуществляется на основании заявок от руководителей подразделений.

Теперь остается в бюджет добавить данные о тех заявках, которые еще не были оплачены – графа «Сумма заявленных, но не исполненных на текущую дату платежей». Вместо оплаченных заявок надо будет заставить Excel отобрать те, по которым деньги еще не ушли. Формула для той же статьи «Оплата товаров» будет выглядеть так:

=СУММЕСЛИ(‘Реестр заявок на платеж’!I14:I57;«Оплата товаров»;’Реестр заявок на платеж’!G14:G39).

Стоит обратить внимание на то, что в критерии отбора заявок («Оплата товаров») после названия статьи бюджета должен стоять пробел. Помните: создавая технический столбец в реестре заявок, мы добавили знак пробела. Его и надо учесть.

Итак, все готово. Теперь сведений в бюджете более чем достаточно, чтобы заранее предвидеть перерасход средств.

Вложенные файлы

Доступно только подписчикам
  • Пример проверки заявок на соответствие бюджету.xls


Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Опрос

Вы планируете менять работу в новом году?

  • Да, планирую 36%
  • Подумываю об этом 26.4%
  • Нет, пока никаких перемен 28%
  • Это секрет! 9.6%
Другие опросы

Рассылка



© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Чтобы скачать файл, пожалуйста, зарегистрируйтесь

Сайт журнала «Финансовый директор» - это профессиональный ресурс для сотрудников финансовых служб и профессиональных управленцев.

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании
Прочитать книгу «Я – финансовый директор. Секреты профессии» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль