Движение денежных средств таблица excel – Как подготовить отчет о движении денежных средств (кэш-фло) за 7 шагов? | статьи

Экспорт из 1С в Excel

Для создания финансовой модели в Excel, а также план-фактного анализа часто требуется сбор информации из внешних для Excel источников. Рассмотрим на примере составления отчета о движении денежных средств (ОДДС) по данным, выгруженным из 1С.

ОДДС – это отчёт, в котором информация о движении денежных средств структурирована в соответствии со справочником статей БДДС и финансовой структурой компании (ЦФО). В данном примере для упрощения финансовая структура не рассматривается. Для формирования отчёта нужно пройти следующие этапы:

  • экспорт данных о бухгалтерских проводках из 1С в Excel и их предварительная обработка;
  • присваивание каждой операции соответствующей статьи справочника ОДДС;
  • формирование сводной таблицы движения денежных средств с автоматическим подсчётом сумм платежей/поступлений по каждой статье.

На первом этапе необходимо сделать карточку счёта 51 «Расчётные счета» за нужный период и сохранить в формате xls (можете скачать файл примера kartochka-51, в нём уже сделан экспорт

из 1С в Excel). Затем открыть этот файл, удалить в нём шапку и итоговые значения внизу:

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

На втором этапе каждой проводке присваивается значения статьи управленческого учёта из справочника. Когда записей много, эта работа должна быть максимально автоматизирована с помощью макросов, но в этом примере всё будет сделано вручную.

Добавьте столбец Статья. Включите фильтр (Меню Данные – область Сортировка и фильтр – кнопка Фильтр). Теперь, выбирая значения фильтров по столбцам Дебет и Кредит, можно заполнить столбец Статья. Например, поступления от заказчиков отражаются проводками Д 51 К 62.1 и Д 51 К 62.2. Чтобы отфильтровать соответствующие проводки, в столбце Дебет нужно выбрать в меню фильтра 51, а в фильтре столбца Кредит

задать Текстовые фильтры…Начинается с… — ввести 62, ОК. Теперь всем отфильтрованным проводкам нужно присвоить значение статьи. Во вторую сверху ячейку столбца Статья можно написать название статьи поступлений/затрат (Поступления от заказчиков) и «протянуть» мышью до конца таблицы.

Аналогичным образом заполняются остальные проводки.

В данном упрощённом примере используются следующие соответствия статей и проводок (если субсчёт не указан – к статье относятся проводки по всем субсчетам данного счёта):

Дебет проводкиКредит проводкиСтатья управленческого учёта
5162Поступления от покупателей
6051Платежи поставщикам
68.151НДФЛ
68.251НДС
68.451Налог на прибыль
68.851Налог на имущество
6951Отчисления в фонды
7051Зарплата
7151Подотчёт
7651Аренда
7651Членские взносы
7651Программы
7651Страховка
9151Расчётно-кассовое обслуживание

Проводка Д 76 К 51 может относиться к разным статьям управленческого учёта.

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

Добавьте столбец Сумма, в котором будeт формула для ячейки I2 — «=E2+G2».

Теперь третий этап – формирование сводной таблицы. Нужно выбрать всю заполненную таблицу (горячая клавиша Ctrl-A), меню ВставкаСводная таблица, в открывшемся окне проверить, что выбрано На новый лист:

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

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

Сначала нужно добавить в отчёт информацию о датах. Отметьте в окне Список полей сводной таблицы поле Дата и переместите соответствующее поле внизу из окошка Названия строк в окошко Названия столбцов. Получится большая сводная таблица, в которой каждому столбцу соответствует день c поступлениями или платежами:

Теперь можно группировать данные по месяцам. Щёлкните правой кнопкой мыши по любой дате, в контекстном меню выберите Группировать… В открывшемся окне выберите Месяцы.

Теперь отчёт приобретает более наглядный вид:

Целиком файл с примером тут: Gotovii-ODDS.

 

Пример как вести управленческий учет в Excel

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

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

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

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

Справочники

Опишем учет работы в кафе. Предприятие реализует продукцию собственного производства и покупные товары. Имеют место внереализационные доходы и расходы.

Для автоматизации введения данных применяется таблица управленческого учета Excel. Рекомендуется так же составить справочники и журналы с исходными значениями.

  1. Типы контрагентов. Кафе сотрудничает с поставщиками продуктов питания, оборудования для хранения, подработки, подсортировки и упаковки товаров, санитарной одежды и столового белья, посуды. В этот же список можно внести банки, поставщиков коммунальных услуг и др.
  2. Статьи затрат. Перечень зависит от работы конкретного пункта общественного питания.

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



Удобные и понятные отчеты

Не нужно все цифры по работе кафе вмещать в один отчет. Пусть это будут отдельные таблицы. Причем каждая занимает одну страницу. Рекомендуется широко использовать такие инструменты, как «Выпадающие списки», «Группировка». Рассмотрим пример таблиц управленческого учета ресторана-кафе в Excel.

Учет доходов

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

При создании списка (Данные – Проверка данных) ссылаемся на созданный для доходов Справочник.

Учет расходов

Для заполнения отчета применили те же приемы.

Отчет о прибылях и убытках

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

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

Анализ структуры имущества кафе

Источник информации для анализа – актив Баланса (1 и 2 разделы).

Для лучшего восприятия информации составим диаграмму:

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

Скачать пример управленческого учета в Excel

По такому же принципу анализируется пассив Баланса. Это источники ресурсов, за счет которых кафе осуществляет свою деятельность.

Финансовая модель в Excel при покупке бизнеса

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

Отчет о движении денежных средств

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

Предприятие представило отчет о прибылях и убытках:

Баланс:

* Цифры и показатели условные, для примера.

На основании этих двух документов составляем отчет о движении денежных средств в Excel. Будем применять ссылки на ячейки с соответствующими значениями. То есть полностью автоматизируем заполнение.

Какие формулы использовали:

Как получить правильный знак перед значением того или иного показателя (+/-):

  • для статьи Актива Баланса – величина показателя на начало периода «минус» сумма на конец периода;
  • для статьи Пассива – показатель на конец периода «минус» значение на начало периода.

Чтобы было легче составлять отчет, используя ссылки на другие документы, можно их открыть на одной странице. Для этого переходим на вкладку «Вид». Нажимаем два раза кнопку «Новое окно» (у нас два документа, не считая текущий отчет). Потом жмем «Упорядочить все».

Задаем параметры расположения окон:

Все три отчета оказываются в одном окне.

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



Алгоритмы расчета основных финансовых коэффициентов

  1. Рентабельность активов. Отражает размер прибыли на единицу стоимости капитала. Показывает степень эффективности использования имущества. Формула: чистая прибыль / сумму активов. Первое значение возьмем из Отчета о прибылях и убытках. Второе – из Баланса.
  2. Рентабельность собственного капитала. Показывает размер прибыли на единицу стоимости собственного капитала. Позволяет определить эффективность использования капитала. Для собственника бизнеса – «сколько я получу с рубля вложенных в предприятие средств». Расчетная формула: чистая прибыль / среднюю величину собственного капитала. Показатель сравнивают с другими вариантами вложения средств или с процентами по банковскому вкладу.
  3. Прибыльность продаж. Показывает эффективность текущей деятельности. Информационная база для расчета – отчет о прибылях и убытках. Формула: чистая прибыль / выручку.
  4. Рентабельность чистой прибыли. Формула расчета: чистая прибыль / затраты. Информационная база – Отчет о прибылях и убытках.
  5. Коэффициент общей ликвидности. Отношение текущих активов к краткосрочным обязательствам. Он важен для инвестора (покупателя бизнеса), т.к. показывает способность фирмы погашать краткосрочные обязательства только за счет оборотного капитала.
  6. Коэффициент автономии. Отношение собственного капитала к сумме активов организации.

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

Блок Отчет о движении денежных средств

Автор: Андрей Дата: 13.12.2017 Рубрика: Бизнес-план — пошаговая инструкция

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



В этом разделе мы рассмотрим:
1. Таблицы, которые позволяют проводить анализ денежных потоков
2. Денежный поток от операционной деятельности
3. Денежный поток от инвестиционной деятельности
4. Денежный поток от финансовой деятельности
5. Остаток денежных средств на конец каждого прогнозируемого месяца
6. Показатели инвестиционной привлекательности проекта

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

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

Теперь пойдем по пунктам =)

1. Таблицы, которые позволяют проводить анализ денежных потоков

Прогнозные показатели «Отчета о движении денежных средств» можно анализировать на таких листах:

Лист «Исходные данные». Это блок 12 в навигационном фильтре:

Блок 12 на листе Исходные данные

Здесь представлены сводные показатели к каждому из трех вариантов:

Сводный прогноз денежных потоков

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

На листе «Титул» представлены сводные показатели остатка денежных средств на конец первого и третьего расчетных годов, а также выведены показатели эффективности проекта (NPV, PI, DPP):

Свод на листе Титул

На листе «Финансы» есть сводные диаграммы, благодаря которым визуально хорошо видно как денежные потоки меняются из месяца в месяц:

Диаграммы денежных потоков на листе Финансы

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

Фильтр денежных потоков для диаграммы

На листе «11.2.CF (В-1)» представлен самый подробный отчет о движении денежных средств:

Отчет о Движении денежных средств (детально)

На этом примере и будем разбирать отчет о движении денежных средств детально.

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

Индекс инфляции

Далее идет строка, в которой отражаются денежные средства на начало периода. Это как раз те деньги (та наличность), которые будут вам доступны на начало каждого из расчетных месяцев. Если вы видите где-то отрицательное значение, значит, уже в начале этого месяца у вас присутствует дефицит денег!

Денежные средства на начало периода

2. Денежный поток от операционной деятельности

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

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


Мой пример:

Поступление денег от продаж товаров или услуг


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


Вот мой пример:

Постоянные и переменные расходы (платежи)


Начисления на заработную плату, налог на прибыль и НДС сведены в части «Оплата налогов». Здесь мы видим суммы налогов, которые разбиты в соответствии с необходимым периодом оплат. К примеру, начисления на заработную плату, которую я начислял сотрудникам в апреле, я буду платить в госбюджет в первой половине мая. Соответственно такой платеж и отражается в отчете о движении денежных средств в тот период, когда я должен буду взять эту сумму и отнести ее в кассу для оплаты!


Мой пример:

Оплата налогов


В результате, «Операционный денежный поток» будет равен:

Поступление денежных средств от продаж минус Постоянные расходы минус Переменные расходы минус Оплата налогов


Мой пример:

Операционный денежный поток


Индекс дисконтирования (в таблице он обозначен как «Дисконтирующий фактор») подтягивается из блока «Макропоказатели».

Индекс дисконтирования

Строка «Дисконтированный операционный денежный поток» — это Операционный денежный поток умноженный на Индекс дисконтирования (дисконтирующий фактор).

А строка «Кумулятивный дисконтированный операционный денежный поток» — это соответственно Операционный поток накопительно.

Дисконтированный операционный денежный поток

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

3. Денежный поток от инвестиционной деятельности

Эта часть таблицы находится сразу под операционным потоком:


Вот мой пример:

Инвестиционный денежный поток


В эту таблицу вытягиваются платежи из блока «Инвестиции» листа «Исходные данные» в разрезе групп инвестиционных вложений.

После того как применяется дисконтирование к инвестиционному потоку (аналогично пункту 2), мы получаем строки «Дисконтированный инвестиционный денежный поток» и «Кумулятивный дисконтированный денежный поток»:

Дисконтированный инвестиционный денежный поток

«Свободный денежный поток» — это Операционный денежный поток минус Инвестиционный денежный поток

«Дисконтированный свободный денежный поток» учитывает дисконтирование аналогично тому, как описано выше.

Свободный денежный поток

4. Денежный поток от финансовой деятельности

Финансовый денежный поток я разделил на три части:

1. «Денежный поток капитала», куда входят поступления Собственных денежных средств плюс Денежных средств партнеров плюс Денежных средств инвестора минус Вознаграждение инвестора (дивиденды).


Мой пример:

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


2. «Прогнозный денежный поток по кредиту» включает в себя Получение кредита минус Оплата тела кредита минус Погашение процентов по кредиту.


Мой пример:

Денежный поток по кредиту


3. «Прогнозный денежный поток по другим займам» аналогично кредиту рассчитывается так: сумма Полученного займа минус Оплата тела займа минус Погашение процентов по займу.


Это мой пример:

Денежный поток по прочим займам


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

Общий денежный поток

5. Остаток денежных средств на конец каждого прогнозируемого месяца

Эту строку я вывел в отдельный пункт, так как «Остаток денежных средств на конец периода», пожалуй, самый важный показатель в данном прогнозе:

Остаток денежных средств на конец периода

Если в этой строке появляется цифра со знаком минус, это значит, что в данном месяце вам не хватает денежных средств! Соответственно вам ОБЯЗАТЕЛЬНО нужно просмотреть в столбце с отрицательным значением все показатели сверху донизу и внести в проект соответствующие корректировки, чтобы не допустить этого минуса. Или как вариант, изыскать дополнительный источник финансирования для покрытия дефицита денежных средств.

6. Показатели инвестиционной привлекательности проекта

Чтобы увидеть показатели эффективности вложения в проект инвестиционных средств, перейдите на лист «Титул», где в самом низу таблицы размещены нужные нам показатели по каждому варианту организации и развития бизнеса отдельно:

Показатели эффективности инвестиций

— «Чистая приведенная стоимость (NPV)» — говоря простыми словами NPV – это доход, который получит собственник бизнеса после того, как выплатит все налоги и рассчитается с кредиторами и инвесторами. Если NPV имеет отрицательное значение, значит, проект не является прибыльным и вкладывать в него деньги нельзя!

— «Индекс прибыльности (PI)» — это отношение суммы денежных потоков (дисконтированных) к инвестиционным вложениям. Вот что об этом говорит Wikipedia. PI должен быть больше 1, в противном случае это показывает, что проект не является прибыльным.

— «Дисконтированный срок окупаемости (DPP)» — по-простому говоря, это срок окупаемости вложенных инвестиций. Когда «Кумулятивный дисконтированный денежный поток» приобретает положительное значение – это говорит о том, что к этому месяцу инвестиции и окупятся.

Показатели эффективности инвестиций также можно увидеть на листе «Финансы» в отдельных небольших таблицах к каждому варианту:

Показатели эффективности инвестиций

И на листе «Исходные данные» в небольшой таблице под отчетом о движении денежных средств:

Показатели эффективности

Скачать готовую финансовую модель можно здесь


Видеоинструкция по работе с блоком Прогноз денежных потоков



Все анонсы новых постов и инструкций после публикации я выставляю на странице блога в Facebook.

Хотите первым узнать о новой публикации на моем блоге, подписывайтесь на страницу блога в Facebook!



Если материал поста был для Вас полезен, поделитесь ссылкой на него в своей соцсети:

При использовании материалов сайта наличие активной ссылки на www.blogbusiness.com.ua обязательно