Как построить «легкий» бюджет движения денежных средств. Пример в Excel

10004
Габбасов Ильдар
заместитель финансового директора ООО «Оптовик» – розничная сеть магазинов «Эссен»
Пример бюджета движения денежных средств в Excel, с помощью которого можно оперативно управлять ликвидностью и прогнозировать дефицит денежных средств компании. Источником информации служит бюджет доходов и расходов.

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

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

Как формируется бюджет движения денежных средств

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

Классическое построение бюджета движения денежных средств косвенным методом производится в четыре шага.

Шаг 1. По данным отчетности (форма 2 «Отчет о прибылях и убытках») определяется чистая прибыль компании.

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

Шаг 3. Вычитаются (прибавляются) любые увеличения (уменьшения), произошедшие в статьях текущих активов, за исключением статьи «Денежные средства».

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

Определение чистой прибыли

Сначала денежный поток я детализирую в трех разрезах: поток от операционной деятельности, инвестиционной и финансовой. При этом разделы «легкого» бюджета движения денежных средств будут содержать не все статьи классического косвенного метода. Так, например, раздел «Операционная деятельность» состоит из статей: «Чистая прибыль», «Недежные корректировки (амортизация)», «Изменение оборотного капитала»; «Инвестиционная деятельность» содержит одну статью — «Инвестиции»; «Финансовая деятельность» тоже состоит из единственной статьи «Займы и кредиты». По строке «Займы и кредиты» будут отражаться суммы полученных займов и кредитов, а также их погашение. Также с помощью этой строки я определяю будущую потребность компании в денежных средствах, которые необходимы на покрытие кассовых разрывов (все расчеты, а также пример бюджета движения денежных средств в Excel вы сможете скачать по ссылке в конце статьи ).

Формирование «легкого» бюджета денежных средств косвенным методом начинается с операционной деятельности. Для подсчета денежного потока нужны данные: чистая прибыль, неденежные корректировки и изменение оборотного капитала. Показатель чистой прибыли я беру из БДР (табл. 1). Неденежные корректировки — это все расходы и доходы, которые не подразумевают оттока и притока денежных средств. Я учитываю только амортизацию. Остальные неденежные операции (резервы по потерям, отпускам, взаимозачеты) отражаются как в БДР, так и в балансе, и при подсчете денежного потока через БДР и баланс по ним получится нулевой результат. Поэтому их я не включаю в расчеты.

Таблица 1. Форма отчета БДР, руб.

БДР

Факт

План

Июль 2015

Август 2015

Сентябрь 2015

Октябрь 2015

Ноябрь 2015

Декабрь 2015

Реализация

10 295 478

10 686 614

11 585 811

11 817 527

11 935 702

13 726 057

Себестоимость

5 683 774

6 313 261

7 844 057

8 400 985

8 484 995

9 757 744

Маржинальная прибыль

4 611 704

4 373 353

3 741 754

3 416 542

3 450 707

3 968 313

Расходы

3 132 239

3 163 141

3 280 530

3 354 864

3 394 115

3 835 898

Заработная плата

2 085 910

2 044 191

2 166 843

2 210 180

2 232 282

2 567 124

Маркетинг

514 774

535 365

519 304

529 690

534 987

615 235

Постоянные расходы

200 000

204 000

201 960

199 854

200 578

210 684

Амортизация

115 714

127 285

132 377

130 819

130 553

131 786

Проценты

215 841

252 300

260 046

284 321

295 715

311 069

Прибыль

1 479 465

1 210 212

461 224

61 678

56 592

132 415

Налог на прибыль

295 893

242 042

92 245

12 336

11 318

26 483

Чистая прибыль

1 183 572

968 170

368 979

49 342

45 274

105 932

Расчет изменения оборотного капитала

Расчет оборотного капитала по фактическим данным. Для любого планирования нужна отправная точка — то, что нам уже известно, то есть факт. Поэтому сперва я анализируют факт. Данные беру из баланса. Затем приступаю к планированию. Значение оборотного капитала вычисляю по упрощенной формуле чистого оборотного капитала. Не беру те активы и пассивы, которые несущественно связаны с операционной деятельностью, а значит, слабо влияют на его значение:

ОК = Зап + ДЗ + ДС – КЗ   (1)

где ОК — оборотный капитал;

Зап — запасы, в том числе незавершенное производство и запасы готовой продукции;

ДЗ — дебиторская задолженность;

ДС — денежные средства и их эквиваленты;

КЗ — кредиторская задолженность.

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

Таблица 2. Баланс, руб.

Баланс

Факт

Июль 2015

Август 2015

Сентябрь 2015

Внеоборотные активы

5 012 000

5 744 702

5 966 710

Основные средства и НМА

4 980 000

5 712 702

5 934 710

Прочие активы

32 000

32 000

32 000

Оборотные активы

1 451 672

1 420 309

1 726 101

Запасы

684 289

705 052

924 126

Дебиторская задолженность

764 136

712 842

798 321

Денежные средства

3247

2415

3654

ИТОГО АКТИВЫ

6 463 672

7 165 011

7 692 811

Капитал

4 461 225

5 429 395

5 798 374

Уставный капитал

3 000 000

3 000 000

3 000 000

Нераспределенная прибыль

1 461 225

2 429 395

2 798 374

Долгосрочные обязательства

1 951 247

1 682 401

1 839 450

Заемные средства

1 951 247

1 682 401

1 839 450

Краткосрочные обязательства

51 200

53 215

54 987

Кредиторская задолженность

51 200

53 215

54 987

ИТОГО ПАССИВЫ

6 463 672

7 165 011

7 692 811

Подставляю в формулу (1) данные из баланса (см. в табл. 2 на стр. 48). В результате получаю фактические значения оборотного капитала для каждого месяца III квартала:

ОК июл.15 = 684 289 + 764 136 – 51 200 = 1 397 225;

ОК авг.15 = 705 052 + 712 842 – 53 215 = 1 364 679;

ОК сен.15 = 924 126 + 798 321 – 54 987 = 1 667 460.

Значение ОК за сентябрь далее буду учитывать для расчета изменения оборотного капитала на IV квартал 2015 года в таблице 5.

Определение оборачиваемости. Выявляю зависимость оборотного капитала от изменения активности компании. Для этого я его представляю через дни оборачиваемости по формуле:

ОК = Здн. × СС.ср.сут. + ДЗдн. × Реал.ср.сут. × 1,18 ДСдн. × Реал.ср.сут. – КЗдн. × СС.ср.сут. × 1,18  (2)

где Здн. — оборачиваемость запасов в днях;

СС.ср.сут. — среднесуточная себестоимость, т. е. себестоимость за период, поделенная на количество дней периода;

ДЗдн. — оборачиваемость дебиторской задолженности в днях;

Реал.ср.сут. — среднесуточная реализация, т. е. реализация за период, поделенная на количество дней периода;

ДСдн. — оборачиваемость денежных средств и их эквивалентов в днях;

КЗдн. — кредиторская задолженность.

1,18 — поправка на ставку НДС. Если надо применять разные ставки НДС, используйте в формулах средневзвешенную ставку НДС.

Пример

Закупаем сырье на сумму 150 млн рублей, НДС сверху. Из них 30 процентов сырья с НДС (10%), оставшиеся 70 процентов — с НДС (18%). Рассчитаем средневзвешенную ставку НДС.

1. 150 млн рублей × 30% = 45 млн рублей — определяем объем сырья, приобретенного компанией, с НДС (10%);

2. 45 млн рублей × 10/100 = 4,5 млн рублей — определяем сумму НДС (10%);

3. 150 млн рублей – 45 млн рублей = 105 млн рублей — объем сырья с НДС (18%);

4. 105 млн рублей × 18/100 = 18,9 млн рублей — сумма НДС (18%);

5. 4,5 млн рублей + 18,9 млн рублей = 23,4 млн рублей — общая сумма НДС;

6. 23,4 × 100/150 = 15,6 процента — средневзвешенная ставка НДС.

В формуле (2) интересуют значения Здн., ДЗдн., ДСдн., КЗдн. Они необходимы для того, чтобы на основании данных фактического периода (III квартал 2015 года) рассчитать оборотный капитал на будущий период (IV квартал 2015 года). Вычисляю данные значения, используя формулы:

Как построить «легкий» бюджет движения денежных средств. Пример в Excel

Теперь сделаю расчет за фактический и плановый периоды. Данные возьму из БДР (табл. 1) и баланса (табл. 2):

Реал.ср.сут. июл.15 = Реализация за июл.15 (табл. 1) / 31 = 10 295 478 / 31 = 332 112;

СС.ср.сут. июл.15 = Себестоимость за июл.15 (табл. 1) / 31 = 5 683 774 / 31 = 183 348;

Здн. июл.15 = Зап июл.15 (табл. 2) / СС.ср.сут. июл.15 = 684 289 / 183 348 = 3,7;

ДЗдн. июл.15 = ДЗ июл.15 (табл. 2) / (Реал.ср. сут. июл.15 × 1,18) = 764 136 / (332 112 × 1,18) = 1,9;

КЗдн. Июл.15 = КЗ июл.15 (табл. 2) / (СС.ср.сут. июл.15 × 1,18) = 51 200 / (183 348 × 1,18) = 0,2

Аналогично рассчитываются значения показателей за август, сентябрь 2015 года. Далее сведу все показатели в таблицу 3. Данные Здн., ДЗдн., КЗдн. за три фактических месяца усредняю (табл. 3, столбец 5) и буду использовать как плановые показатели для следующего квартала (табл. 3, столбцы 6–8). Оборачиваемость денежных средств учитывать не буду, так как их размер в примере минимален, в других случаях нужно использовать формулу (5). Если в дебиторскую задолженность входят выданные авансы, то их нужно считать отдельно, в знаменателе будет среднесуточная себестоимость. При наличии в кредиторской задолженности полученного аванса, его тоже надо учесть отдельно, в знаменателе — среднесуточная реализация.

Таблица 3. Расчет значений оборачиваемости

Наименование показателя

Июль 2015

Август 2015

Сентябрь 2015

Средние значения оборачиваемостей

Октябрь 2015

Ноябрь 2015

Декабрь 2015

1

2

3

4

5

6

7

8

Реал.ср.сут., руб.

386 194

381 211

397 857

442 776

СС.ср.сут. , руб.

183 348

203 654

261 469

271 000

282 833

314 766

Здн., дней

3,7

3,5

3,5

(3,7 + 3,5 + 3,5) / 3 = 3,6

3,6

3,6

3,6

ДЗдн., дней

1,9

1,8

1,8

(1,9 + 1,8 + 1,8) / 3 = 1,8

1,8

1,8

1,8

КЗдн., дней

0,2

0,2

0,2

(0,2 + 0,2 + 0,2) = 0,2

0,2

0,2

0,2

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

Зап = СС.ср.сут. × Здн.   (7)

ДЗ = Реал.ср.сут. × 1,18 × ДЗдн.    (8)

КЗ = СС.ср.сут. × 1,18 × КЗдн.     (9)

Произведу расчет на основании данных таблицы 3 (столбцы 6–8).

Зап окт.15 = СС.ср.сут. окт.15 × Здн.окт.15 = 271 000 × 3,6 = 975 600;

ДЗ окт.15 = Реал.ср.сут. окт.15 × 1,18 × ДЗдн.окт.15 = 381 211 × 1,18 × 1,8 = 809 692;

КЗ = СС.ср.сут. окт.15 × 1,18 × КЗдн. окт.15 = 271 000 × 1,18 × 0,2 = 63 956.

Аналогично рассчитаю показатели для ноября и декабря 2015 года. Обобщу все расчеты в таблице 4. Теперь, подставляя в формулу (1) необходимые значения из таблицы 4, вычислю оборотный капитал и его изменение.

Таблица 4. Расчет величины запасов, дебиторской и кредиторской задолженностей для IV квартала 2015 года, руб.

Расчет составляющих формулы оборотного капитала

Октябрь 2015

Ноябрь 2015

Декабрь 2015

Запасы (Зап)

975 600

1 018 199

1 133 158

Дебиторская задолженность (ДЗ)

809 692

845 048

940 456

Кредиторская задолженность (КЗ)

63 956

66 749

74 285

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

Таблица 5. Расчет оборотного капитала и его изменения, руб.

Показатель

Сентябрь 2015

Октябрь 2015

Ноябрь 2015

Декабрь 2015

Оборотный капитал

1 667 460

1 721 336

1 796 498

1 999 329

Изменение оборотного капитала

1 721 336 – 1 667 460 = 53 876

1 796 498 – 1 721 336 = 75 162

1 999 329 – 1 796 498 = 202 831

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

ОДП = ЧП + А — ∆ОК    (10)

где ОДП — операционный денежный поток;

ЧП — чистая прибыль;

А — амортизация;

∆ОК — изменение значения оборотного капитала за период.

ОДП окт.15 = ЧП окт.15 (табл. 2) + А окт.15 (табл. 2) – ∆ОК окт.15 (табл. 5) = 49 342 + 130 819 – 53 876 = 126 285.

Аналогично рассчитаю значения ноября и декабря 2015 года.

Свой пример бюджета денежных средств в Excel дополню расходами по инвестиционной деятельности (200 000 руб.) в каждом месяце IV квартала. И вычислю дефицит/профицит денежных средств (разница между операционным денежным потоком и инвестициями), который покажет, сколько средств нужно привлечь или их излишек. Результаты вычислений представлены в таблице 6. В примере получился отрицательный результат, который вынуждает привлечь денежные средства на его покрытие.

Таблица 6. Профицит/дефицит денежных средств, руб.

Показатель

Октябрь 2015

Ноябрь 2015

Декабрь 2015

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

126 285

100 665

34 887

Инвестиции

200 000

200 000

200 000

Профицит/дефицит денежных средств

–73 715

–99 335

–165 113

Пример бюджета движения денежных средств в Excel

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

Таблица 7. «Легкий» бюджет движения денежных средств пример в Excel (извлечение), руб.

Статьи

Факт

План

Июль 2015

Август 2015

Сентябрь 2015

Октябрь 2015

Ноябрь 2015

Декабрь 2015

Чистая прибыль

1 183 572

968 170

368 979

49 342

45 274

105 932

Неденежные корректировки

115 714

127 285

132 377

130 819

130 553

131 786

Изменение оборотного капитала

0

32 546

–302 781

–53 876

–75 162

–202 831

Инвестиции

0

–859 987

–354 385

–200 000

–200 000

–200 000

<…>

<…>

<…>

<…>

<…>

<…>

<…>

Займы и кредиты

0

–268 846

157 049

73 715

99 335

165 113

ИТОГО денежный поток

1 299 286

–832

1 239

0

0

0

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

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

Дополнение БДР расчетами оборачиваемостей. Можно вообще не формировать БДДС. Например, я ограничиваюсь тем, что дополняю БДР указанными расчетами, как представлено в таблице, которую можно скачать в конце статьи. Такой расчет удобно вести в Excel. Достаточно один раз настроить форму БДР с дополнительными расчетными статьями изменения оборотного капитала, и можно видеть всю картину состояния дел в компании в прошлом и обозримом будущем.

Кроме того, этот метод позволяет легко управлять плановой ликвидностью, достаточно поменять значения оборачиваемости — и тут же видно результат. Например, предположим, что с поставщиками сырья удалось договориться на отсрочку платежа в пять дней. Этому значению приравниваем оборачиваемость кредиторской задолженности (КЗдн.). В таблице 8 в IV квартале значение кредиторской задолженности 0,2 меняем на 5, при этом автоматически пересчитываются значения кредиторской задолженности, оборотного капитала и все последующие зависимые строки. В результате получается новое значение денежного потока для IV квартала. Как видно, при реализации данной стратегии компании уже в октябре 2015 года удастся высвободить почти 1,5 млн рублей из оборотного капитала и не потребуется привлечение заемных средств.

Таблица 8. Изменение кредиторской задолженности 

Расчет денежного потока:

значения показателей

Июль 2015

Август 2015

Сентябрь 2015

Октябрь 2015

Ноябрь 2015

Декабрь 2015

Реализация среднесуточная (Реал.ср.сут.), руб.

332 112

344 729

386 194

381 211

397 857

442 776

Себестоимость среднесуточная (СС.ср.сут.), руб.

183 348

203 654

261 469

271 000

282 833

314 766

Оборачиваемость запасов (Здн.), дней

3,7

3,5

3,5

3,6

3,6

3,6

Оборачиваемость дебит. задолженности (ДЗдн.), дней

1,9

1,8

1,8

1,8

1,8

1,8

Оборачиваемость кредит. задолженности (КЗдн.), дней

0,2

0,2

0,2

5,0

5,0

5,0

Запасы, руб.

924 126

975 600

1 018 199

1 133 158

Дебиторская задолженность, руб.

798 321

809 692

845 048

940 456

Кредиторская задолженность, руб.

54 987

1 598 900

1 668 715

1 857 119

Оборотный капитал, руб.

1 667 460

186 392

194 532

216 495

Изменение оборотного капитала, руб.

–1 481 068

8 140

21 963

Операционный денежный поток, руб.

1 661 229

357 490

437 316

Инвестиции, руб.

200 000

200 000

200 000

Дефицит/профицит денежных средств, руб.

1 461 229

157 490

Методические рекомендации по управлению финансами компании

Подготовлено по материалам журнала "Финансовый директор"

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

Методические рекомендации по управлению финансами компании

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

Доступно только авторизованным пользователям
  • Скачать авторскую методику расчетов «легкого» БДДС на основе БДР в формате Excel.xlsx


Ваша персональная подборка

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

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

    Рекомендации по теме

    Школа

    Школа

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

    Записаться

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

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

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

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

    А еще...




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

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

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

    
    • Мы в соцсетях
    Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

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

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

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