Бюджет движения денежных средств пример в excel
Эффективное управление бизнесом определяется среди прочего способностью грамотно контролировать денежные потоки предприятия и управлять его платежеспособностью и ликвидностью. Осуществить это возможно с помощью Бюджета движения денежных средств, который является одним из самых распространенных финансовых бюджетов компании.
Бюджет движения денежных средств или как его еще называют бюджет денежных потоков позволяет осуществлять контроль за финансовыми ресурсами организации, с целью избежать дефицита денежных средств с одной стороны, и эффективно использовать избыточные денежные средства с другой стороны, т.е. не допустить профицита средств. Поскольку излишек «свободных» денег (избыточная ликвидность) приводит к недополучению прибыли, то есть к потере возможной прибыли компанией и означает неспособность руководства эффективно использовать имеющиеся ресурсы, а дефицит в свою очередь указывает на ее неплатежеспособность. Таким образом своевременное выявление проблем позволит оперативно реагировать и устранять их, например, излишние средства инвестировать, а недостаток средств перекрыть банковским кредитом.
Определение: Бюджет движения денежных средств – это прогноз поступлений и расходований денежных средств компании, который может формироваться на разные периоды (неделя, месяц, год), однако наиболее часто составляется на год с разбивкой на месяцы. Бюджет состоит из нескольких разделов, где отображается движение денежных средств по основным видами деятельности: операционной, финансовой и инвестиционной.
В качестве основного показателя платежеспособности компании является величина Чистого денежного потока (по англ. Net Cash Flow, фин. Nettorahavirta), который представляет собой сумму денежного потока от всех трех видов деятельности предприятия. Этот показатель характеризует финансовое состояние, определяет перспективы повышения стоимости компании и ее инвестиционную привлекательность. Оценивается Чистый денежный поток (ЧДП) следующим образом:
- Положительный денежный поток, т.е. ЧДП больше нуля: Поступления превышают отток денежных средств, что является положительным фактором для инвесторов. Чем выше показатель, тем привлекательнее будет компания для инвесторов.
- ЧДП равен или близок к нулю: Компании не хватает средств для развития, а значит инвесторы вряд ли заинтересуются таким бизнесом.
- Отрицательный денежный поток, т.е. ЧДП меньше нуля: Отток денежных средств превышает их поступление. Это может говорить об убыточности бизнеса в случае, если такая ситуация имеет затяжной характер. Инвестировать в такую компанию не стоит.
Финансовый анализ бюджета денежных средств основывается на сравнении плановых показателей с фактическими, а также с данными предшествующих периодов. При выявлении отклонений изучаются причины их возникновения, а также оцениваются возможные последствия с точки зрения их влияния на деятельность компании. Таким образом, целью составления бюджета движения денежных средств является эффективное управление этими средствами, для того чтобы иметь их в достаточном количестве для обеспечения нужд предприятия, и в тоже время получать более высокую доходность от вложения свободных денежных средств.
Составляется Бюджет денежных потоков прямым или косвенным методом. Более детальное описание разделов бюджета, методов его составления Вы можете найти на нашем сайте в разделе “ОТЧЕТ О ДВИЖЕНИИ ДЕНЕЖНЫХ СРЕДСТВ КОМПАНИИ".
Если формирование БДДС на вашем предприятии не автоматизировано или вы не знаете как это сделать, можно сделать расчетный файл в Excel.
Начнём с реестра платежей. Реестр платежей формируется в виде простой таблицы.
Год, месяц и дату лучше разбивать по отдельным столбцам, так потом легче работать с фильтрами и сводными таблицами для сверки занесенных и учетных данных.
При занесении получателя платежа лучше, если он будет назваться как в вашей учетной системе, так, во-первых, удобно для сверки, а, во-вторых, если в вашей таблице один и тот же контрагент будет записан по-разному платежи по нему будут не сопоставимы.
Расчетный файл в Excel и консультации по этой и другим темам в учебном курсе "Финансовая модель предприятия в Excel" (промокод первым ученикам на скидку 90% - 100_ПЕРВЫХ)
Основание платежа можно записывать в любой форме, но лучше будет если однотипные назначения будут назваться одинаково и их будет не много. За основу можно взять назначение платежа в платежном поручении.
Столбцы статья, подстатья и ЦФО зависят от принятой на предприятии учетной политики. Их лучше кодировать.
Если вы пользуетесь 1С, то для удобства занесения информации о платежах в Excel нужно воспользоваться функцией "Вывести список" платежных поручений или банковских выписок. Или в лучшем случаем обратиться к программисту, чтобы он настроил список по вашему формату.
Так можно вывести список в 1С
Использование списка из 1С или другой бухгалтерской программы решает проблемы по уникальности наименований контрагентов, упрощает ввод основания платежа и других реквизитов.
После того, как данные о платежах занесены, нужно каждому платежу присвоить статью, подстатью (если есть) и ЦФО. Для этого я использую таблицу с кодами статьей. "Шапку" для каждой группировки статей делать обязательно, это нужно для формулы, которая используется в данном примере.
Разнесение расходов по статьям является достаточно трудоемкой и слабо автоматизируемой работой. Использование таких таблиц позволяет минимизировать ошибки выбора. Например, выбора статьи, которая не предусмотрена для данного ЦФО или подстатьи не соответствующей статье.
Столбец месяц в данном случае носит технический характер, нужен для корректного ввода формулы, которую мы будем рассматривать дальше и для возможности формирования реестра платежей на протяжении всего года.
В расчетном файле таблицу с кодами я обычно размещаю на отдельном листе.
В реестр кодировка статьи вносится также как она внесена в таблицу с кодами. Названия соответствующих столбцов в шапках кодов и реестра тоже должны быть одинаковыми.
Чтобы быстро сверить занесенные данные по суммам с данными счетов бухгалтерского учета можно использовать сводные таблицы
Для сверки оборотов по датам.
Для сверки соответствия статей, назначения платежа и поставщика
После формирования реестра и таблицы кодов можно приступать к формированию бюджета платежей по ЦФО, например в таком виде.
Чтобы разнести данные из реестра в бюджеты по ЦФО я использую формулу БДСУММ.
Рассмотрим синтаксис формулы БДСУММ (можно посмотреть в справке по Excel): БДСУММ(база данных; суммируемое поле; критерии отбора)
База данных - это сам реестр, лучше его расположить на отдельном листе. В формуле ссылка на базу данных должна выделять таблицу с реестром полностью: от первой строки с шапкой, до последней ячейки в последней строке.
Суммируемое поле - наименование столбца по которому нужно суммировать данных. В нашем случае - это "Сумма с НДС"
Критерии отбора - это таблица с кодами статей. Данная формула позволяет сразу делать выборку по сочетанию признаков: месяц, статья, подстатья, ЦФО. Набор критериев должен выбираться вместе с "шапкой", причем "шапка" в таблице с кодами статей должна быть точно такой как в реестре, по этому в таблице критериев "шапка" повторяется для каждого набора критериев. Критерий месяц нужен для того, чтобы пользоваться одним реестром в течении всего года.
Чтобы сократить трудоемкость при занесении формулы в таблицу с план-фактом все ссылки на ячейки в таблице должны содержать знак доллара БДСУММ(реестр!$A$1:$K$60;реестр! $I$1;коды!$A$16:$D$17) . Такую формулу можно копировать в строку ниже с другой статьей. Чтобы сумма считалась по нужной статье нужно в формуле заменить часть выделенную жирным "БДСУММ(реестр!$A$1:$K$60;реестр! $I$1; коды!$A$16:$D$17 )" на критерии соответствующие новой статье.
Переходим к БДДС. Сводный БДДС может выглядеть следующим образом
Верхняя часть - это наши текущие платежи в разрезе статей и ЦФО. Так как классический БДДС группируется на текущую, инвестиционную и финансовую деятельность в столбце "Тип" можно задать соответствующий признак и просуммировать платежи по этому признаку. В столбце "Вид" расходы группируются на переменные и накладные (постоянные) в разрезе видов деятельности. А столбец внешний содержит группировку для внешнего БДДС, который формируется для головной организации и статьи которого отличаются от внутреннего бюджета. Таким образом, сводный БДДС позволяет нам получать любую аналитику.
P.S. В своей работе экономисты используют разные функции Excel, на столько разные, что опыт, полученный на одном предприятии, практически не применим на другом. Пожалуйста, напишите в комментарии, какие задачи не можете решить Вы. Возможно Вы найдете решение, а я тему для статьи.
На основании реестра из статьи " Как систематизировать информацию о платежах в Excel " сделаем БДДС.
В реестре, ссылка на которой дана выше, занесены платежи только по ткущей деятельности. Чтобы разделить поступления и расходы по видам деятельности в БДДС введем в реестра дополнительный столбец и назовем его "БДДС". Всем доходам и расходам, занесенным в предыдущей статье присвоим признак "ТД" - текущая деятельность.
После заполнения реестр будет выглядеть так
Чтобы пример был наглядным добавим поступления и расходы по финансовой (ФД) и инвестиционной деятельности (ИД).
Заполненный БДДС будет выглядеть так
Формулы для заполнения таблицы
Для заполнения данных в БДДС используется формула СУММЕСЛИМН (при работе с формулой лучше использовать мастер формул, так как формула достаточно сложная)
В качестве примера рассмотрим формулу для суммирования поступлений выручки от текущей деятельности (строка 5 таблицы БДДС), которая выглядит так
=СУММЕСЛИМН('Реестра платежей'!$J:$J;'Реестра платежей'!$A:$A;B2;'Реестра платежей'!$F:$F;"Приход";'Реестра платежей'!$G:$G;"ТД";'Реестра платежей'!$H:$H;"Выручка")
Ссылка на столбцы реестра в форме $J:$J используется по той причине, что реестр платежей будет обновляться ежедневно на протяжении всего года, т.е. при использовании в формуле ссылки на конкретный диапазон ячеек возникнет необходимость постоянно изменять ссылки для увеличения диапазона . Чтобы не обновлять ссылки на формулы ежедневно и копировать формулы в следующий месяц и используется ссылка вида $J:$J. Эта ссылка берет данные из всего столбца, без указания начальной и конечной строки, знак доллара нужен для копирования формул в следующий месяц.
Условие в формуле СУММЕСЛИМН можно записывать как в виде ссылки, например, B2 - ссылка на номер месяца, так и виде текста "Выручка" (заключается в кавычке, пишется точно так же как в реестре)
Внимание. Чтобы формула работала необходимо заполнить все признаки (условия) на которые она ссылается. Так из предыдущего рисунка с реестром платежей видно, что наименование статей заполнены не для всех движений денежных средств. На рисунке ниже представлена таблица в которой заполнены все условия, на которые ссылается формула
Модель полезна компаниям, которые корректируют бюджет и хотят оценить, как повлияют на бизнес новые события. Вариантов развития событий может быть несколько, и корректировка должна учесть все.
Технические требования: Excel 2007 и выше
Excel-модель, которая составит прогнозный баланс
Скачайте упрощенную модель, которая содержит все необходимые компоненты. Она автоматически обрабатывает бюджеты прибылей и убытков и движения денежных средств и составляет прогнозный баланс. Вы можете масштабировать ее и добавлять новые статьи в каждый из бюджетов.
Технические требования: Excel 2007 и выше
Готовая модель, которая спланирует доходы компании
Если нужен краткосрочный прогноз с высокой детализацией, используйте готовую модель. Она покажет приближенную к реальности картину событий на ближайшие месяцы и потребует в качестве исходных данных цифры только за прошлые периоды.
Технические требования: Excel 2007 и выше
Excel-модель, которая подскажет, как выполнить бюджет
Используйте этот интерактивный отчет, чтобы анализировать переменные и подбирать параметры, с помощью которых компания сможет достичь целевых финансовых показателей.
Технические требования: Excel 2007 и выше
Excel поможет контролировать общехозяйственные расходы отделов
Чтобы было легче планировать бюджет и затем анализировать факт, ведите учет общехозяйственных расходов в разрезе отделов. С помощью Excel-модели вы сможете сэкономить время и деньги на план-факт анализ общехозяйственных расходов.
Технические требования: Excel 2007 и выше
Excel поможет спрогнозировать итоги года
Эта Excel-модель поможет подвести промежуточные итоги деятельности и совместить в одном отчете результаты прошедших периодов и те, что запланировали на будущие месяцы.
Технические требования: Excel 2007 и выше
Бюджетирование в Excel: подготовка к автоматизации
С помощью этой модели вы сможете протестировать шаблоны форм бюджетов и сам процесс бюджетирования, выявить ошибки и устранить их, привести учет группы компаний к единым нормам, подготовить систему бюджетирования к автоматизации.
Технические требования: Excel 2007 и выше
Упрощенная модель в Excel, которая поможет поставить бюджетирование с нуля
Вам подойдет это решение, если на вас лежит такие задачи, как разработка системы бюджетирования, упрощение процедуры планирования бюджета движения денежных средств и бюджета доходов и расходов, минимизация риска возникновения ошибок в финансовых планах компании.
Технические требования: Excel 2007 и выше
Готовое решение для автоматизации работы над бюджетами
Это решение поможет создать бюджетную модель в Excel, упростить заполнение и консолидацию бюджетов, свести к минимуму ручной ввод данных и риски ошибок в расчетах.
© Актион управление и финансы, Медиагруппа Актион-МЦФЭР, 2007–2017, 8 800 550-07-98, с 9 до 18 по Москве.
Воплощайте свои идеи в жизнь с помощью настраиваемых шаблонов и новых возможностей для творчества при подписке на Microsoft 365.
Отслеживайте финансы вашей организации с помощью шаблона бюджета в виде таблицы или диаграммы
Если вы ищете шаблон бюджета для отслеживания личных финансов или средств предприятия, ваш поиск увенчался успехом. Шаблон таблицы бюджета поможет точно отслеживать ваши расходы.
Доступны шаблоны бюджетов для различных вариантов использования, включая бюджеты для домохозяйств, свадеб, организаций, событий и учебных заведений. Шаблон семейного бюджета позволяет отслеживать ежемесячный доход, расходы и движение денежных средств, а также визуально представлять сведения.
Или можно использовать шаблон бюджета домашнего хозяйства в Excel. В этом шаблоне бюджета указываются прогнозные значения в сравнении с фактическими затратами по отдельным статьям и автоматически вычисляется разница.
В шаблонах бюджетов мероприятий расходы разделяются по статьям, включая затраты на место проведения, угощения, развлечения и т. д., чтобы вы точно знали на что тратятся деньги. Для общих целей воспользуйтесь кольцевым шаблоном бюджета. Один из более наглядных шаблонов, кольцевой бюджет является отличным способом просмотра затрат.
Каждый шаблон бюджета в Excel содержит функции электронной таблицы, диаграммы и настраиваемые стили. Скачайте бесплатный шаблон бюджета, чтобы всегда быть в курсе текущего финансового положения.
Читайте также: