Чтобы предприятие вовремя могло выполнять взятые на себя финансовые обязательства, следует избегать кассовых разрывов - недостатка денежных средств, необходимых для финансирования очередных расходов. Начиная с версии 3.0.43.152 в программе «1С:Бухгалтерия 8» появился «Платежный календарь», который не только помогает бороться с кассовыми разрывами, но и помогает оперативно управлять финансовыми потоками. В календаре могут отражаться сведения о поступлениях от покупателей и о запланированных оплатах поставщикам; о платежах в бюджет; о просроченных платежах; о денежных средствах, находящихся в пути между банком и кассой (например, при инкассации), и планируемых к поступлению по договорам эквайринга. Новая функциональность особенно полезна для малых и средних компаний, у которых отсутствует финансовый отдел или в штате нет финансового менеджера.
Для стабильной работы предприятия необходимо своевременное осуществление всех запланированных платежей. Для этого на момент выполнения платежа в кассе или на расчетных счетах организации должно быть достаточное количество средств. Ситуация, когда денежных средств временно не хватает, называется кассовым разрывом. В программе «1С:Бухгалтерия 8» редакции 3.0 для предотвращения кассовых разрывов предусмотрена новая функциональная возможность - (рис. 1).
Рис. 1. Форма платежного календаря
Доступ к Платежному календарю осуществляется по одноименной гиперссылке из раздела Руководителю в группе Планирование . В настройках командной панели указывается организация, для которой формируются сведения о планируемых платежах, и период составления прогноза, включая текущую дату. - это отчет, в котором по дням отражается план поступления и расходования денежных средств, а также информация об остатках денежных средств на начало и конец каждого дня.
Отрицательные значения на конец дня обозначают кассовые разрывы. Они выделяются в отчете красным цветом. Для исключения кассовых разрывов необходимо следить, чтобы сумма платежей не превышала сумму поступлений с учетом остатка на начало дня. Используя , бухгалтер видит, где намечается недостаток средств, и может вовремя принять меры для его устранения.
Отчет в «1С:Бухгалтерии 8» редакции 3.0 может включать в себя следующие разделы (при наличии соответствующих ситуаций):
Рассмотрим, какая информация отображается в каждом из разделов.
В разделе Оплата от покупателей отображаются платежи, запланированные согласно документам учетной системы Счет покупателю , Реализация (акты, накладные) , Оказание производственных услуг , Передача ОС , Передача НМА . Напоминаем, что возможность указывать сроки уплаты в этих документах устанавливается в настройках функциональности. Для того чтобы пользователь мог отслеживать сроки ожидаемых оплат от покупателей, в разделе Главное - Функциональность на закладке Расчеты необходимо установить флаг Планирование платежей от покупателей.
Информацию о просроченных платежах можно получить, если перейти по соответствующей гиперссылке в форму помощника Ожидаемая оплата от покупателей (рис. 2).
Рис. 2. Помощник «Ожидаемая оплата от покупателей»
В форме помощника можно изменить срок оплаты одного или нескольких документов, предварительно выделив их курсором. По кнопке Изменить срок оплаты открывается форма ввода нового срока оплаты. Также срок оплаты документа можно изменить непосредственно в колонке Срок оплаты . Выделив строки в списке, можно отправить электронное письмо контрагенту с напоминанием об оплате. Письмо сформируется автоматически по кнопке Напомнить .
В разделе Прочие поступления отображается информация о денежных средствах, внесенных в кассу кредитных организаций, сберегательные кассы или кассы почтовых отделений для зачисления на счет организации, но еще не зачисленные по назначению (остатки по счету 57.01 «Переводы в пути»). Также в разделе отображается планируемое поступление денежных средств по договорам эквайринга (остатки по счету 57.03 «Продажи по платежным картам»).
В разделе Налоги и взносы отображаются задачи по уплате платежей в бюджет (налоги, сборы, страховые взносы). Суммы налогов, сборов и взносов к уплате рассчитываются по данным информационной базы - это либо начисленные суммы налогов, либо данные из подготовленных деклараций или отчетов. Если по платежу не было возможности определить сумму (например, нет ни декларации, ни начислений), то в поле вместо суммы отображается прочерк. Для платежей в бюджет доступны действия, аналогичные соответствующим задачам в Списке задач. По клику на ячейке открывается меню, в котором можно выбрать нужное действие - например, Оплатить или Свериться с ФНС . В случае, когда сумма платежа не определена, программа подскажет, какое действие нужно выполнить - например, подготовить декларацию по соответствующему налогу.
В разделе Платежи поставщикам отображаются платежи, запланированные согласно документам Счет от поставщика , Поступление (акты, накладные) , Поступление доп. расходов , Поступление НМА . По клику на ячейку открывается документ, на основании которого запланирован платеж. Для того чтобы пользователю была доступна возможность указывать сроки оплаты в документах поставщика, необходимо в настройках функциональности программы на закладке Расчеты установить флаг Планирование платежей поставщикам .
Информация о просроченных платежах доступна по соответствующей гиперссылке. При нажатии на нее открывается форма помощника Оплата поставщикам , с помощью которого можно автоматически сформировать список платежных поручений на оплату поставщикам. Для этого нужно отметить флагом строки, по которым нужно сформировать платежные поручения. Платежные документы формируются автоматически по кнопке Создать платежные поручения .
В раздел включены платежи по выплате сотрудникам заработной платы. По клику на ячейке открывается меню, в котором можно выбрать нужное действие, например Выплатить или Свериться с ФНС по НДФЛ (рис. 3).
Рис. 3. Разделы «Налоги и взносы» и «Зарплата»
В разделе Периодические платежи отображается информация о регулярных платежах - например, за коммунальные услуги, связь, аренду, интернет и др. Дату напоминания об очередном платеже и регулярность напоминаний можно установить в форме платежного поручения по гиперссылке Повторять платеж?. Информация о просроченных платежах доступна по соответствующей гиперссылке.
В форме Платежного календаря можно скорректировать планы по приходу / расходу денег с помощью команды Перенести на другой день . Для этого нужно выделить область, содержащую информацию о платеже. Также команда доступна в контекстном меню ячейки платежа. Перенос доступен только для платежей поставщикам и для оплаты от покупателей.
В простом и наглядном виде представляет информацию о прогнозном движении денежных средств с возможными кассовыми разрывами и способствует оперативному принятию мер по недопущению этой ситуации.
Результатом оптимизации платежного календаря является упорядоченный план (прогноз) движения денежных средств, в котором кассовые разрывы отсутствуют.
Платежный календарь – это важный инструмент для эффективного управления денежными средствами предприятия. Чаще всего его формируют в Excel. Читайте, как составить платежный календарь и сохраняйте себе пример в Excel.
Финансовый директор, стремящийся к увеличению стоимости компании, не может оставить без внимания платежный календарь, поскольку это безрисковый способ заработать дополнительные средства, укрепить финансовую дисциплину и обнаружить проблемные зоны в структуре денежного оборота.
Платежный календарь в Excel – основной инструмент в системе оперативного финансового планирования. Он помогает решить, прежде всего, следующие важные задачи:
Как автоматизировать платежный календарь с помощью Excel
Скачайте Excel-модель, чтобы автоматизировать всю работу по ведению и наполнению платежного календаря, а также реестра платежей. Средним компаниям могут понадобиться небольшие корректировки, например изменение состава статей поступления и выбытия средств. Крупным предприятиям модель пригодится в качестве прототипа будущего модуля управления текущими платежами комплексной информационной системы.
Перед тем, как сформировать платежный календарь, требуется сделать два ключевых шага для разработки структуры шаблона: определить формат ввода данных и разработать интерфейс для комфортного мониторинга. Для этого потребуется два рабочих листа.
Чтобы свести к минимуму количество случайных ошибок (опечаток), следует фиксировать данные в виде отдельных операций с уникальным набором аналитик. Стандартный набор в целях ведения управленческого учета состоит из 10 аналитик:
Заданный набор полей легко можно дополнить в случае необходимости (номер договора, НДС, счет, инициатор и другие).
Аналогичные операции добавляются элементарным копированием и незначительной корректировкой (дата, сумма, контрагент). Значения всех аналитик (за исключением полей «Сумма», «Комментарий») ограничиваются списками, чтобы избежать дублирования наименований. А в случае с наименованием «Статьи» и «Кода статьи», привязываются через функцию «ВПР». Таким образом, добавление новой операции отнимает всего несколько секунд. В шапке таблицы с наименованием аналитик устанавливаются фильтры, что позволяет выбрать несколько аналитик и сформировать требуемый массив данных (рис. 1).
Рисунок 1 . Пример базы данных для платежного календаря (кликните, чтобы увеличить)
Лист «База данных» при всей его универсальности с широким диапазоном информации является лишь отличным помощником в структуре календаря платежей, пример которого мы разбираем. Основная роль отведена графику прогноза ликвидности (рис. 2). Визуально это стандартный отчет о движении денежных средств, но с временным шагом в один день. Каждая операция из базы данных находит свое место в матрице прогноза ликвидности. Перенос данных осуществляется через формулу «СУММЕСЛИМН» с заданными параметрами по коду статьи и дате. Таким образом, любая новая операция автоматически отражается в графике, при этом исключена ситуация, когда данные не транслируются из-за ошибок ввода – в данной модели таких проблем нет, так как ключевые аналитики задаются через списки, а значит, любая операция найдет свое место на графике ликвидности.
Рисунок 2 . Пример составления прогноза ликвидности в платежном календаре
Предложенный образец платежного календаря в Excel может выходить за рамки привычного горизонта планирования в один месяц, поскольку порядок ввода позволяет идентифицировать любую операцию. Вполне допустимо осуществлять мониторинг вплоть до квартала, однако в этом случае существует значительная зависимость качества прогноза от принципов планирования, действующих на предприятии. В ситуации, когда у организации налажен процесс систематического обновления плановых данных на всех ЦФО, горизонт ликвидности длинною в квартал может быть качественным и полезным для принятия управленческих решений.
Периодичность формирования может различаться – в зависимости от назначения календаря платежей в системе финансового менеджмента компании, но при этом обновление данных должно проводиться не реже одного раза в неделю, в противном случае информативность и актуальность прогноза будет снижаться до уровня стандартного отчета об исполнении .
Если платежный календарь в Excel ведут несколько человек, отслеживать их правки поможет специальный журнал изменений. Благодаря ему вы сможете контролировать:
Кроме того, журнал изменений можно скрыть от посторонних глаз.
Существенным недостатком платежного календаря считается невозможность (ограниченная возможность) многопользовательского доступа. К сожалению, сегодня не существует технического решения для устранения данной проблемы. Однако этот барьер успешно преодолевается за счет передачи полномочий по ведению календаря в руки опытному сотруднику планово-экономического отдела или казначейства. Плюсы такого подхода очевидны: наличие единственного пользователя системы снизит количество ошибок до минимального уровня, хотя и будет отнимать существенную долю рабочего времени.
В отличие от бюджетной кампании, когда каждый центр финансовой ответственности формирует свой план раз в год и ежеквартально корректирует, платежный календарь требует регулярного обновления – еженедельно или чаще). Формализованный сбор информации со всех ЦФО может быть затруднен, поэтому экономисты должны быть глубоко погружены в ситуацию с оборотом денежных средств по всем направлениям.
Прогнозирование входящих денежных потоков от продаж, безусловно, самый сложный участок работы. При большом количестве мелких стабильных клиентов возможно применение простейшего метода оценки – средней арифметической, когда планируемый объем поступлений за несколько периодов делится на количество периодов. Данные можно актуализировать с помощью ряда косвенных факторов (сезонность, динамика отрасли / рынка, влияния текущей стадии жизненного цикла компании / стратегии). Точность планирования усложняется при наличии нескольких крупных клиентов. В данной ситуации следует воспользоваться методом экспертной оценки. Суть ее заключается в том, что по каждой сумме определяется вероятность ее получения в трех сценариях (консервативный, оптимистический, пессимистический) и затем выводится среднее взвешенное с учетом удельной доли каждого сценария.
Определение даты многих платежей осуществляется без участия инициаторов оплаты. Полезно использовать эмпирические данные, особенно по известным и давним поставщикам. Кроме того, удачным дополнением будет регулярный мониторинг крайних сроков оплат по действующим контрактам (инициаторы платежей имеют устойчивую привычку указывать даты оплат раньше допустимого крайнего срока по договору). То же самое касается уплаты налогов. Хорошим приемом может стать практика оплаты некоторых счетов на несколько дней позже допустимого срока. Подобные мелкие хитрости зачастую остаются незамеченными со стороны контрагента, однако не стоит применять подобную тактику в отношении ключевых поставщиков.
Измерение эффективности любого планового документа осуществляется посредством оценки качества прогнозирования. Платежный календарь не является исключением. установить предельный целевой показатель отклонения факта от плана в зависимости от специфики бизнеса, периодичности обновления отчета и горизонта планирования. В случае с платежным календарем в Excel, пример которого мы разобрали, следует отметить, что регулярное превышение нормы отклонения сведет полезность такого документа к минимуму, а значит компания не заработает несколько дополнительных процентов.
На практике часто бывает, что неточностью грешат один и те же ЦФО. Одним из вариантов борьбы со слабыми «планировщиками» может стать введение регулярных аналитических докладов для высшего руководства, в котором будет даваться оценка упущенным ресурсам из-за некачественного планирования отдельно взятых ЦФО. Другим способом мотивации ответственных за исполнение бюджетов работников может выступить фиксация допустимых отклонений в карте ключевых показателей эффективности для определения размера годового (квартального) бонуса сотрудника.
Качественный платежный календарь по праву занимает центральную позицию в системе управленческого учета компании. Безусловно, комплексное использование этого универсального инструмента позволяет расширить границы классического назначения по мониторингу проблем ликвидности. Логичным продолжением заданного вектора по формированию структуры будет автоматическая подготовка план-фактного отчета БДДС. Для этого нужно лишь ввести блок плана движения денег на график (за месяц или за квартал) и сгруппировать данные факта до аналогичного периода. Кроме того, по мере наполнения базы формируется еще один мощный инструмент контроля и анализа – максимально гибкая матрица всех денежных операций предприятия с высокой степенью достоверности данных.
1. Не допускать кассовых разрывов и невыполнения обязательств компании перед контрагентами. Основная цель формирования платежного календаря -- борьба с кассовыми разрывами, при которых денег в кассе и на расчетном счете нет. Являясь фактически плановым «расписанием» потока ДДС предприятия, платежный календарь позволяет прогнозировать кассовые разрывы, заблаговременно принимать меры, чтобы исключить ситуации необходимости осуществления платежей при отсутствии достаточных средств на счете компании. Следуя принципу «предупрежден -- значит вооружен», можно оперативно изменить планы расходования ДС, тем самым предотвратив кассовый разрыв.
Можно делать предварительные «прикидки», изменять даты поступлений и платежей, согласовывать их с контрагентами -- и отражать все это непосредственно в платежном календаре.
2. Не допускать расходования средств свыше утвержденных сумм. Даже если денег у компании много, это не значит, что их можно расходовать бездумно. Траты допустимы только в рамках принятых бюджетов. Непредвиденные ситуации, требующие выйти за рамки бюджета, являются, скорее, исключениями из общего правила. Платежный календарь позволяет контролировать, находится ли платеж в рамках бюджета.
3. Управлять ликвидностью компании. Одним из основных критериев правильности управленческих решений, принимаемых в финансовой сфере, является положительность совокупного потока ДС в любой момент времени.
4. Предоставлять достоверную информацию в on-line режиме. Финансовую информацию важно получать оперативно. Недостатком платежного календаря, реализованного в Excel, является расход времени на перепостроение отчетности. Платежный календарь должен быть интегрирован в систему оперативного учета так, чтобы для получения достоверных данных не требовалось двойного ввода.
5. Обеспечить надлежащее соблюдение процедур согласования оплаты расходов. Процедура согласования должна включать в себя определенное делегирование ответственности, в зависимости от важности того или иного платежа.
6. Максимально исключить человеческий фактор.
Главная цель использования платежного календаря - борьба с кассовыми разрывами. Представление графика платежей в простой, наглядной форме позволяет наглядно увидеть картину движения денежных средств, сформированную данными оперативного планирования на основании информации о плановых поступлениях и списаниях денежных средств.
Рис. 1
платежный календарь денежный поток
1. Формирование плановых данных (этот процесс реализуется в рамках бюджетирования и является обязательным подготовительным этапом, т.к. проверка возможности оплат осуществляется по плановым данным).
Предполагается, что компания имеет финансовую структуру с выделенными ЦФО, бюджетирование как инструмент управления внедрен и работает. Этот этап является основой для платежного календаря, т.к. проверка возможности оплаты/поступления происходит в разрезе определенных плановых данных.
Важно составить БДДС относительно денежных потоков по основной, инвестиционной и финансовой деятельности, для чего необходимо иметь бюджеты как основной деятельности, так и инвестиционных проектов. За каждой статьей целесообразно закрепить ответственного (т.е. работника, который принимает решения и несет ответственность как за плановые, так и за фактические показатели, отклонения факт-плана)
2. Определение перечня аналитик (статья ДДС, контрагенты, договоры, источник ДС), в контексте которых будет проводиться проверка возможности выплат и поступлений.
Перечень аналитик позволяет создать инструмент для проверки выплат/ поступлений на возможность реализации.
Можно использовать следующие аналитики: ЦФО, источник ДС, контрагент (получатель ДС), договор с контрагентом (в рамках которого будут осуществлены выплаты), статья ДДС (по которой пройдут выплаты ДС), проект (по которому идут инвестиционные выплаты), приоритет платежа.
Выделяя аналитики в учетной системе, необходимо помнить о принципе экономической целесообразности -- завышенные требования к перечню аналитик делают систему трудоемкой и немобильной. Поэтому следует остановиться на перечне, минимально необходимом для решения задач, поставленных компанией перед учетной системой.
3. Построение механизма формирования реестров платежей, заявок.
После определения аналитик и реквизитов платежа формируется инструментарий оплаты/поступления ДС. Система оперативного управления финансами предприятия, реализуемая через бизнес-процесс «Платежный календарь», включает в себя несколько участников (сотрудник, отдел, служба), и каждому необходим инструмент, с которым он будет работать в данной системе. Сотрудники отделов и служб подают заявки на выплату/поступление ДС, а руководство или сотрудники, уполномоченные принимать решения, реализуют их через реестры заявок. Заявка представляет собой документ-запрос на поступления или выплаты ДС, содержащий описательную информацию о данной операции. В заявке отражаются все утвержденные аналитики, вспомогательные реквизиты, приоритетность, если необходимо, и поле для комментариев каждого из участников будущего процесса платежного календаря.
Реестр заявок -- это перечень заявок, консолидированных по определенному признаку (дата, ответственный, источник ДС, вид статей).
И заявка, и реестр заявок на выплату ДС должны содержать полный и достаточный объем информации для принятия решения о выплате/поступлении денег.
4. Описание бизнес-процесса «Платежный календарь», определение в его рамках ответственных лиц по процедурам, задачам.
После проверки и одобрения оплаты предоставленных заявок считается, что платежный календарь как бизнес-процесс реализован в реестрах, утвержденных заявках.
Далее нужно ответить на вопросы: кто, что, когда и в какие сроки. Именно на данном этапе важен процесс разработки платежного календаря, который устанавливает порядок взаимодействия работников и, следовательно, определяет результат платежной дисциплины предприятия и его репутацию добросовестного партнера. Вариантов взаимодействия работников в процессе согласования заявок множество, и каждое предприятие вырабатывает схему, целесообразную и удобную для него.
5. Регламентация и документарное оформление бизнес-процесса «Платежный календарь».
На заключительном этапе бизнес-процесс управления денежными потоками закрепляется в регламентных документах, которые утверждаются внутренним приказом и являются обязательными для исполнения всеми подразделениями и работниками компании. Внутренний документ, определяющий правила функционирования платежной системы компании, должен содержать информацию о порядке прохождения заявок на оплату, сроках, лицах, ответственных за согласование и утверждение, обязанностях и полномочиях сотрудников, последовательности действий.
6. Автоматизация бизнес-процесса «Платежный календарь».
Для поддержания оперативного управления денежными потоками потребуется автоматизация соответствующих бизнес-процессов. Программное обеспечение должно позволять:
· создавать электронные учетные документы платежной системы (например заявки на оплату или реестры);
· формировать электронную отчетность, необходимую для контроля исполнения платежей, выполнения регламентов платежной системы, БДЦС (к примеру, платежного календаря);
· реализовывать поддержку процедур контроля и согласования (бюджетов, заявок на оплату и пр.).
· разграничивать права доступа к финансовой информации для разных уровней ответственности в компании.
Некоторые предприятия для автоматизации бизнес-процессов управления денежными потоками используют Excel и прочие непрофильные программы, хотя такой способ имеет ряд недостатков: низкую оперативность в отражении информации и формировании отчетности, незащищенность от сбоев, проблему двойного ввода данных, необходимость затрат времени на перепостроение отчетности. Поэтому все больше компаний склоняется к выбору специализированного программного обеспечения.
Одной из особенностей платежного календаря является его постоянная актуализация. В случае недостатка денежных средств для удовлетворения всех поступивших заявок используются правила ранжирования платежей в зависимости от их вида и уровня приоритета.
Таблица с суммарными денежными потоками рассмотренная в главе 19, служит больше, для отображения суммарного ежедневного денежного потока (ДП) и остатков денежных средств (ДС), по которым, при их недостатке, можно произвести расчет и стоимость привлекаемых заемных средств. Специалисты финансового отдела задумались над противоположной задачей - отсутствием детализации или аналитики этих денежных потоков в разрезе одного банковского дня. Такая детализация необходима для анализа проводимых кассовых операций и, при необходимости, последующего внесения корректировок в принимаемые управленческие решения, исправляющих возможные неприятности. Поэтому решено дополнить модель прогнозирования движения денежных потоков еще одним рабочим листом с таблицей платежного календаря, который будет отображать:
Стратегический смысл самого платежного календаря заключается в том, чтобы найти узкие места в детализированных потоках ДС и по возможности устранить их. Ведь при положительном потоке за месяц в целом, могут быть всплески остатков ДС ниже нулевой отметки внутри этого месяца. Такая ситуация, когда не хватает денег для ведения оперативной хозяйственной деятельности, называется кассовым разрывом. Не применяя моделирования спрогнозировать возможное поведение наличности практически невозможно, да и исправить тоже. А это невыполнение взятых на себя обязательств с возможным последующим срывом бизнеса в целом или же дополнительными издержками для устранения нежелательных последствий, когда таковые становятся известны слишком поздно. Ведь известно, что стоимость приобретаемых товаров и услуг зачастую зависит от выполнения партнером взятых на себя обязательств.
Другой возможный смысл платежного календаря - оптимизация движения денежных средств (ДДС) с целью максимальной эффективности использования ресурсов, коими и являются деньги. Статьи ДДС в платежном календаре позволяют более эффективно взглянуть на происходящие процессы с целью создания бездефицитного платежного календаря, основанного на анализе предшествующих событий и прогнозировании будущих. Могут существовать различные подходы, например, желание "уложиться" в планируемые денежные ресурсы или расчет минимально возможных сумм привлекаемых кредитов и их стоимости при нехватке ДС. Платежный календарь позволит моделировать будущие потоки ДС по принципу "что-если" и находить более оптимальные варианты, например, в сроках оплаты. При кратковременном избытке денег, принимается решение не размещать их на депозите, а оплатить поставщикам за материалы (товары, услуги) с определенной заранее оговоренной скидкой. Или наоборот, предварительно провести переговоры с покупателем, с целью оплаты им товара ранее установленного срока с предоставлением ему скидки, а не привлекать краткосрочный кредит.
Пример классического платежного календаря
По логике в состав денежных потоков, как средства оплаты, входят непосредственно денежные средства - товар, обладающий мгновенной ликвидностью, так и их эквиваленты. В качестве эквивалентов выступают различные активы, полученные в качестве оплаты за реализованный товар (услуги). Это, как правило, другой товар (взаимозачет, бартер), векселя и пр. Но при поступлении этих эквивалентов сумма на банковском счете остается неизменной и ликвидность эквивалентов не является мгновенной. Поэтому рассматриваем платежный календарь, только для прогнозирования и фиксации непосредственно движения наличности.
В составе классического платежного календаря (рис. 21.1.) можно выделить три составляющих ДДС:
Заполнение платежного календаря и расчеты, которые в нем производятся можно осуществлять самыми различными методами. Например, ввод данных в календарь с клавиатуры на основании произведенных ранее расчетов или предположений. Но этот метод при проведении предприятием активной и многоплановой деятельности не устраивает финансовый отдел. Должна присутствовать определенная стройная система, которая позволит как изменять одновременно массивы данных в платежном календаре, так и анализировать эти данные. Предполагается, что данные в календаре будут обновляться автоматически, на основании разработанного алгоритма и в качестве исходных данных будут использованы все полученные ранее результаты в модели прогнозирования денежных потоков.
Платежный календарь выступает как самый итоговый отчет, на основании которого будет планироваться и контролироваться кассовая жизнь предприятия. В качестве примера возможной автоматизации заполнения платежного календаря рассмотрим операцию поставки товара и получения средств по кредитной линии, рассмотренные в прошлых главах.
Рис. 21.1. Пример классического платежного календаря
Задачи автоматизации формирования платежного календаря
У модели ДенежныеПотоки присутствует существенный недостаток - невозможность просмотра проводимых операций в разрезе одного банковского дня при достаточном количестве введенных записей о проводимых операциях в исходную таблицу на рабочем листе ИсхДанные. Для этой цели введите в состав модели рабочий лист ПК с платежным календарем (рис. 21.2.). Непосредственные задачи автоматизации формирования платежного календаря:
Рис. 21.2. Рабочий лист ПК с созданным платежным календарем и числовым примером за февраль месяц
Создание платежного календаря
Таблица непосредственно платежного календаря расположена в области ячеек С6:AI83. Строки 12:29 календаря выделены для формирования постатейно данных поступивших денежных средств, а строки 32:79 - для списанных.
Перед автоматическим заполнением платежного календаря в ячейку D2 с клавиатуры вводится номер месяца, за который будет формироваться платежный календарь. Промежуточные расчеты при формировании платежного календаря будут производиться в столбцах ВА:ВС.
Формирование наименований статей платежного календаря
Для формирования наименований статей платежного календаря предназначена подпрограмма ФормированиеСтатейКалендаря (рис. 21.3.). Статьи формируются на основании наименований операций, введенных в исходную таблицу на рабочем листе ИсхДанные. Причем, если наименование операции присутствует в исходной таблице, но не проводилась в месяце, за который формируется платежный календарь, эта статья будет все равно присутствовать в календаре. Это объясняется необходимостью унификации платежных календарей за любой период времени. Смысл такой унификации заключается, прежде всего, в наглядности, или желании человека искать вещи на привычном для него месте. Например, в одном месяце предприятие вело активную финансово-хозяйственную деятельность и статей расхода, по которым проводились операции, было 50, а в другом месяце всего 10, и в календаре отражены только статьи с проводимыми операциями. Руководитель, положив на стол два листа бумаги с распечатанными платежными календарями, больше времени потратит на поиск идентичных статей в этих таблицах, чем непосредственно на анализ содержащейся информации и принятие решений.
В подпрограмму входят на две других процедуры: ввод формул выбора наименований статей поступления (ДоходнаяЧасть) и статей списания денежных средств (РасходнаяЧасть). Сама же подпрограмма осуществляет только вставку выбранного текста наименований статей непосредственно в календарь.
В нашем примере в создаваемом платежном календаре можно разместить до 18 статей в доходной части календаря и 48 статей в расходной. При необходимости количество статей может быть уменьшено или увеличено, но тогда необходимо будет редактировать описанные далее макросы формирования платежного календаря и размеры таблицы, в которой календарь размещен.
Если нет необходимости в постоянном обновлении наименования статей при формировании платежного календаря, то тогда выполнение этой подпрограммы можно не производить, поставив перед ее именем в рассматриваемой далее подпрограмме ЗаполнениеПлатежногоКалендаря (рис. 21.5.) знак примечания.
Рис. 21.3. Подпрограмма ФормированиеСтатейКалендаря для формирования наименования статей поступления денежных средств
Формирование наименования статей поступления денежных средств
Прежде чем приступить к записи макроса ДоходнаяЧасть, введите формулы в ячейку ВА12:
=ЕСЛИ(ИсхДанные!I10=0;"";ЕСЛИ(ИсхДанные!H10=1;ИсхДанные!C10;0))
которая вначале проверяет - отличается ли значение в ячейке I10 рабочего листа ИсхДанные (поступление денежных средств) от значения 0. Если отличается, то проводится дополнительная проверка - введен ли признак денежных потоков в ячейку Н10 рабочего листа ИсхДанные. При выполнении этих условий формула возвращает текст наименования операции, введенную в ячейку С10 рабочего листа ИсхДанные.
И в ячейке ВВ12 формула:
=ЕСЛИ(BA12=BA13;0;BA12)
которая вступает в свои полномочия после замены содержимого диапазона ячеек ВА12:ВА5002, содержащих первую формулу на определенные ею значения и последующую их сортировку (по убыванию не считая первую строку строкой заголовка). После сортировки значений, текст одинаковых наименований статей будет содержаться только в смежных ячейках. Тогда эта формула, сравнив значения в двух смежных ячейках, при идентичном тексте возвратит значение 0, а при несовпадении - текст наименования статьи в ячейке находящейся выше. Таким образом, из всех ячеек, содержащим одинаковый текст наименования статьи во всем массиве данных будет выбрана только одна.
Для записи подпрограммы выполните следующие действия после начала записи макроса:
Рис. 21.4. Макрос ДоходнаяЧасть
Формирование наименования статей списания денежных средств
При создании макроса РасходнаяЧасть нет необходимости повторять все действия, выполненные при записи предыдущего. Скопируйте макрос ДоходнаяЧасть в Редакторе Visual Basic и заменив имя отредактируйте его, что заключается в замене ссылки на ячейку с I10 при вводе формулы в диапазон ячеек ВА:
""=IF(ИсхДанные!R[-2]C[-44]=0, ......"
""=IF(ИсхДанные!R[-2]C[-43]=0, ...."
Это относительная система ссылок и указывает на то что столбец I находится 44-м, а столбец J 43-м слева по отношению к столбцу ВА, в ячейки которого вводятся формулы.
Заполнение платежного календаря суммами ежедневных оборотов денежных средств
Подпрограмма ЗаполнениеПлатежногоКалендаряПоДатам (рис. 21.5.) состоит из трех составляющих:
Критериями при выборе данных служат:
Рис.21.5. Подпрограмма ЗаполнениеПлатежногоКалендаряПоДатам
Формулы расчета ежедневных оборотов денежных средств
Для ввода формул определения ежедневных оборотов денежных средств запишите макрос ФормулыЗаполненияПлатежногоКалендаря, предварительно введя следующие формулы. В ячейке ВА10 формула:
=ЕСЛИ(МЕСЯЦ(ИсхДанные!D10)=$D$2;ЕСЛИ(ДЕНЬ(ИсхДанные!D10)=$BA$1;ИсхДанные!C10;0);0)
которая сначала определяет, соответствует ли номер месяца в дате, находящейся в ячейке D10 рабочего листа ИсхДанные номеру месяца, введенному в ячейку D2 рабочего листа ПК. При выполнении этого условия проверяется следующее условие - соответствует ли номер дня месяца этой даты номеру дня месяца, вводимого в ячейку ВА1 рабочего листа ПК. При выполнении этих условий формула возвращает наименование проводимой операции, введенной в ячейку D10 рабочего листа ИсхДанные. В противном случае формула возвращает значение 0.
Формула в ячейке ВВ10:
=ЕСЛИ(BA10=0;0;СУММ(ИсхДанные!I10:J10))
проверяет результат вычисления формулы в ячейке ВА10. Если результат не равен нулю, то формула суммирует значение поступления и списания денежных средств по этой операции, содержащихся в ячейках I10:J10 рабочего листа ИсхДанные. Суммирование значений доходных и расходных статей объясняется тем, что в ячейках I10:J10 значения одновременно содержаться не могут. Это должно обязательно учитываться при формировании таблицы на листе ИсхДанные, ведь иначе такая запись не будет иметь никакого смысла.
Формула в ячейке ВС12:
=СУММЕСЛИ($BA$10:$BA$5000;$C12;$BB$10:$BB$5000)
суммирует обороты движения денежных средств в области ячеек ВВ10:ВВ5000 за номер дня месяца, введенного в ячейку ВА1 и при условии, что текст наименования статьи в ячейке С12 идентичен тексту наименования статьи в области ячеек ВА10:ВА5000.
Запись макроса заключается в последовательном вводе этих формул в диапазоны ячеек BC12:BC79, ВА10:ВА5000 и ВВ10:ВВ5000.
Рис. 21.6. Подпрограмма ввода формул определения оборотов за день по каждой статье управленческого учета
Цикл заполнения платежного календаря ежедневными оборотами движения денежных средств
Основным элементом автоматического заполнения платежного календаря ежедневными оборотами ДДС является цикл For-Next. Последовательность выполняемых циклом операций в подпрограмме ЗаполнениеПлатежногоКалендаряПоДатам (рис. 21.5.) следующая:
Ввод дат и сальдо на начало дня
Задачей макроса КалендарьДатыСальдо (рис. 21.7.) является ввод формул:
Как помните, при создании таблицы на листе СуммПотоки было задано ограничение вертикального расположения таблицы строкой 200 или, исходя из рабочей области, таблица охватывает временной интервал в 198 дней. Ограничение было связано с увеличением скорости обработки информации. Причем алгоритм создания этой таблицы, самостоятельно определяет дату, с которой будет начинается этот временной интервал. Как следствие, если начальная дата находится во второй половине года, то временной интервал перейдет и на следующий год.
Кроме того, создавая различные приложения и модели, на протяжении всей книги, мы стремились к тому, чтобы пользователь при работе с компьютером делал как можно меньше ненужных движений. К таким "движениям" можно отнести и ввод номера года, за который будет формироваться платежный календарь. Поручим Excel самостоятельно производить выбор года. Основной критерий - год должен находиться во временном диапазоне, формируемом на листе СуммПотоки и определяться номером вводимого месяца в ячейку D2 на листе ПК.
Несмотря на кажущуюся сложность, задача довольно проста и определяется одной формулой, по критериям: заданный временной диапазон и значение номера месяца. Формула определения номера года в ячейке D3:
=ЕСЛИ(ИЛИ(ГОД(МИН(СуммПотоки!B3:B200))=ГОД(МАКС(СуммПотоки!B3:B200));МЕСЯЦ(МИН(СуммПотоки!B3:B200))
в первом аргументе функции ЕСЛИ функция ИЛИ проверяет два условия:
При соблюдении одного из условий, или обоих одновременно, формула возвращает минимальное значение года в области дат на рабочем листе СуммПотоки. В противном случае возвращается номер максимального года в этой же области.
Формула формирования даты в ячейке D6:
=ЕСЛИ(ЕОШ(СЦЕПИТЬ(D7;".";$D$2;".";$D$3)*1);0;СЦЕПИТЬ(D7;".";$D$2;".";$D$3)*1)
с помощью функции СЦЕПИТЬ соединяет в единое целое день (ячейка D7), месяц (ячейка D2) и год (ячейка D2). Алгоритм вычислений таких формул описан в главе 7. Цикл заполнения платежного календаря предполагает, что в месяце 31 день. Следовательно, как в нашем примере, если формировать календарь за февраль, в котором 28 дней, то, в этом месяце, начиная с 29-го числа, будет возвращаться значение ошибки #ЗНАЧ. Поэтому функцию СЦЕПИТЬ в первом аргументе функции ЕСЛИ проверяет функция ЕОШ, которая при обнаружении ошибки возвратит значение ИСТИНА. В таком случае функция ЕСЛИ возвратит значение 0, в противном - значение даты в формате Excel, созданной функцией СЦЕПИТЬ.
По этой дате формулой в ячейке D9 определяется остаток денежных средств на начало дня, используя остаток денежных средств в таблице денежных потоков на рабочем листе СуммПотоки:
=ЕСЛИ(ЕНД(ВПР(D6-1;СуммПотоки!$B$3:$E$200;4;ЛОЖЬ));0;ВПР(D6-1;СуммПотоки!$B$3:$E$200;4;ЛОЖЬ))
Основная функция поиска данных формулы - функция ВПР. При отсутствии даты в диапазоне поиска, для устранения возвращаемого формулой значения ошибки, в первом аргументе функции ЕСЛИ применена функция ЕНД. А функция ЕСЛИ выбирает - при отсутствии даты, возвращает значение 0, в противном случае значение остатка денежных средств на заданную дату, найденное функцией ВПР.
Запись макроса КалендарьДатыСальдо заключается в последовательном вводе этих формул в ячейки D3, D6:AH6 и D9:AH9 и последующем редактировании кода VBA после записи макроса.
Рис. 21.7. Подпрограмма КалендарьДатыСальдо
Итоговые расчеты платежного календаря
Подпрограмма КалендарьИтоговыеРасчеты (рис. 21.8.) довольно проста и заключается во вводе всех суммирующих формул в соответствующие диапазоны ячеек. В заключение подпрограмма выделяет весь рабочий лист и заменяет формулы на значения, после чего вводит в ячейку С1 формулу определения текущей даты и времени.
Рис. 21.8. Подпрограмма КалендарьИтоговыеРасчеты
Полный цикл заполнения платежного календаря
Для того чтобы все операции по заполнению платежного календаря происходили автоматически, напишите подпрограмму ЗаполнениеПлатежногоКалендаря (рис. 21.9.). Эта подпрограмма состоит из имен записанных ранее макросов, выполняющих заданные им процедуры расчетов. Для запуска макроса на выполнение создайте кнопку Заполнение платежного календаря (рис. 21.2.) и назначьте ей созданную подпрограмму.
Рис. 21.9. Подпрограмма ЗаполнениеПлатежногоКалендаря
Полный цикл расчета денежных потоков
Для полного расчета всей модели от перерасчета данных на листе ИсхДанные до расчета кредитной линии и заполнения платежного календаря запишите подпрограмму ПолныйРасчетДенежныхПотоков (рис. 21.10.), которая осуществляет весь цикл расчетов, описанный в главах создания модели ДенежныеПотоки. Запуск подпрограммы на выполнение поручите кнопке Полный расчет денежных потоков (рис. 21.2.).
Рис. 21.10. Подпрограмма ПолныйРасчетДенежныхПотоков полного расчета модели денежных потоков
Подпрограмма перепоручает созданным ранее процедурам УточненныйРасчетКредитнойЛинии (рис. 20.12.) и ЗаполнениеПлатежногоКалендаря (рис. 21.9.) выполнение всего цикла вычислений с момента перерасчета таблицы на листе ИсхДанные до заполнения платежного календаря. Кроме этого подпрограмма дополнена сервисными функциями:
Скорость выполнения вычислений всей модели зависит от мощности компьютера и может колебаться от десятков секунд до нескольких минут. В нашей подпрограмме внедрение таймера носит больше обучающий характер.
Работа таймера основана на функции Timer, которая возвращает значение, представляющее число секунд, прошедших после полуночи. Вначале выполнения подпрограммы переменной Х присваивается значение функции Timer в момент нажатия на кнопку, запускающую выполнение вычислений. После выполнения подпрограммы переменной Х присваивается разность между текущим значением функции Timer и зафиксированном ранее значением переменной Х. Полученное значение округляется с помощью функции Round:
X = Application.Round((Timer - X), 0)
Иначе в диалоговом окне показывающим окончание цикла вычислений после целого значения секунд будет присутствовать несколько десятичных знаков.
Создание диалогового окна с помощью функции MsgBox
О возможности создания в Excel пользовательских диалоговых окон с помощью Редактора Visual Basic было написано в главе 5 (рис. 5.16.). Рассмотрим создание и применение диалогового окна с помощью функции MsgBox. Функция MsgBox выводит на экран диалоговое окно, содержащее сообщение и устанавливает режим ожидания нажатия кнопки пользователем.
Синтаксис функции:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
содержит следующие основные именованные аргументы:
В нашей подпрограмме в строке кода VBA:
MsgBox "Полная продолжительность расчета - " & X & " секунд(а/ы)", 0, "Модель прогнозирования движения денежных потоков"
у функции MsgBox имеются следующие аргументы:
Рис. 21.11. Созданное диалоговое окно Модель прогнозирования движения денежных потоков для отображения времени расчета модели и показывающее окончание проведения расчетов
Сворачиванием окна Excel на все время выполнения вычислений
Полный цикл расчета модели может длиться более одной минуты, и для того чтобы это не утомляло глаза при расчете параметров кредитной линии, применялось отключение обновления экрана (рис. 20.12.). Более эффективный способ - сворачивание окна Excel на время выполнения процедуры вычисления. Поэтому допишите две строчки кода VBA. В начале подпрограммы:
Application.WindowState = xlMinimized
и в конце:
Application.WindowState = xlMaximized
Присвоение свойству WindowState значения xlMinimized позволит свернуть окно Excel перед выполнением вычислений, а значение xlMaximized восстановит его во весь экран. В это время можно работать в других программах.
Итоги
Итак, на протяжении пяти глав постепенно создавалась модель прогнозирования денежных потоков. Эта глава как завершающая создания модели для проведения необходимых вычислений.
В этой главе был описан процесс от необходимости применения платежного календаря в деятельности предприятия по управлению наличностью до реализации проекта по его автоматическому формированию. Он несколько отличается от классического (рис. 21.1.), но это легко исправляется, например, организовав экспорт данных из созданного календаря в классический.
Практическое применение платежного календаря для производственного предприятия рассмотрено в главе 26.
