Распределение денежных потоков

Лабораторная работа 22.

Тема "Бюджетирование Создание предпосылок для прогнозирования денежных потоков"

Немного теории

Каждое предприятие индивидуально. И прежде чем создавать систему прогнозирования денежных потоков необходимо разработать внутрифирменные правила, согласно которым будет разрабатываться алгоритм расчетов, и учитываться различные предпосылки по принципу "Что-если?". Рассмотрим эту проблему с позиции финансовых показателей.

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

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

Этот показатель рассчитывается на основании статей баланса. Баланс же составляется на определенную дату. Следовательно, этот показатель соответствует действительности на дату составления баланса, причем на начало дня этой даты. Баланс же изменяется при проведении каждой операции, в том числе и при проведении банковских операций. И если рассматривать баланс в динамике, то величина коэффициента абсолютной ликвидности не является величиной постоянной, а изменяется не только каждый день, но и каждый час как за счет изменения числителя (остатка денежных средств), так и за счет изменения знаменателя (текущей задолженности).

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

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

Рис. 25.1. Графики остатков денежных средств на счетах предприятия

На рис. 25.1. изображены три графика. Первый показывает фактический остаток денежных средств на счетах предприятия и по нему видно, что колебания остатков происходят практически от нуля до 200 тысяч. Второй график находится на уровне 86 тысяч и отражает прямую среднемесячного остатка ДС на счетах предприятия. Отклонения графика фактического остатка денежных средств от усредненного колеблется от 80 тысяч в отрицательную область до 110 тысяч в положительную. При внедрении на предприятии системы прогнозирования денежных потоков предположим, что этот разброс должен быть, например, 20% от усредненного значения. Тогда можно предположить, что 80% денег от рассчитанного усредненного значения будут считаться свободными денежными ресурсами, которые можно разместить с выгодой для предприятия.

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

Коэффициент быстрой ликвидности = (Остаток денежных средств + Счета к получению): Текущая задолженность

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

Коэффициент быстрой ликвидности х Текущая задолженность - Счета к получению = Остаток денежных средств

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

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

Распределение денежных потоков

С целью построения системы прогнозирования денежных потоков на предприятии АО "Стеклодув", специалистами финансового отдела была разработана методика - разделить все поступающие и расходуемые денежные средства на:

• которые можно назвать фиксированными, например оплата налогов, услуг связи, инвестиций в приобретение необоротных фондов и пр. То есть, это те денежные потоки, сумму и сроки которых можно определить с определенной достоверностью;
• которые можно назвать распределенными. То есть те, расход и поступление которых можно усреднить за какой-то промежуток времени с определенной степенью погрешности.

Фиксированные платежи

Службами предприятия в финансовый отдел была предоставлена следующая информация о фиксированных платежах.

Зарплата на предприятии выплачивается в июле 2003 года в течение трех дней.08.07.2003 года зарплата по цеху бутылок:

• оплата труда производственных рабочих линии стандартных бутылок в размере 150000 рублей;
• оплата труда производственных рабочих линии фигурных бутылок в размере 87000 рублей;
• оплата труда вспомогательного персонала в размере 150000 рублей;
• оплата труда менеджеров цеха бутылок в размере 400000 рублей.

09.07.2003 года зарплата по цеху посуды:

• оплата труда производственных рабочих линии тарелок в размере 159000 рублей;
• оплата труда производственных рабочих линии чашек в размере 125000 рублей;
• оплата труда вспомогательного персонала в размере 280000 рублей;
• оплата труда менеджеров цеха посуды в размере 200000рублей

10.07.2003 года зарплата по административному персоналу завода:

• административные расходы. Зарплата менеджеров в размере 1040000 рублей;
• административные расходы. Зарплата служащих в размере 3530000 рублей;

Аренду производственных фондов предполагается оплатить:

• 03.07.2003 года в сумме 400000 рублей;
• 04.07.2003 года в сумме 600000 рублей;
• 07.07.2003 года в сумме 476000 рублей.

Расходы по предоставлению охранных услуг предполагается оплатить в размере 480 тысяч рублей 14.07.2003 года.

Связь оплачивается по 150 тысяч рублей 11-го и 15-го июля 2003 года.

Электроэнергия, согласно договора на ее поставку, должна быть оплачена 10 июля в сумме 468 тысяч рублей по общезаводскому потреблению и по производственным линиям в суммах 240 тысяч, 396 тысяч, 336 тысяч и 396 тысяч рублей.

Предполагается произвести оплату маркетинговых исследований в размерах 400 и 320 тысяч рублей, 9-го и 22-го июля соответственно, а также оплатить рекламу 4, 11, 18 и 25 июля по 450 тысяч рублей.

Предполагается получение денег 17 июля за реализованные основные средства в суммах 26,4 тысячи, 6,0 тысяч и 13,2 тысячи.

Новое оборудование предполагается оплатить 10, 15 и 21 июля в суммах 1440 тысяч, 1003,2 тысячи и 1080 тысяч рублей.

3 июля необходимо погасить кредит в размере 1 млн. рублей и выплатить проценты по нему в размере 20 тысяч рублей.

Поступление процентов за ежедневные остатки по банковским счетам будет 22 июля в размере 200 рублей.

От реализации продукции от заказчика по приобретению фигурных бутылок по предварительной договоренности деньги поступят равными долями по 1,92 миллиона рублей 4, 11, 18 и 25 июля.

По предварительным расчетам нужно оплатить налоги:

• 21 числа налог на добавленную стоимость - 200000 рублей;
• 23 числа налог на прибыль - 800000 рублей;
• 22 числа налог с оборота - 277305 рублей.

Эти фиксированные денежные расходы и поступления необходимо ввести в таблицу расположенную на рабочем листе ФиксПлатежи (рис. 25.2.).

Автоматизация ввода данных по фиксированным платежам

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

Рис. 25.2. Рабочий лист ФиксПлатежи

Справочник расшифровки кодов управленческого учета

Для автоматизации процесса ввода данных по фиксированным платежам предварительно создайте список - справочник расшифровки кодов счетов управленческого учета. Этот список можно создать в любом месте книги СистемаБюджетов. В нашем примере он расположен на рабочем листе Пульт в области W4:Y50 (рис. 25.3.).

Рис. 25.3. Справочник расшифровки кодов управленческого учета

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

Элемент управления Список производит поиск кода управленческого учета, формирует список по созданному справочнику и помещает результат в ячейку АА1 рабочего листа ФиксПлатежи. Для задания этих параметров с помощью команды Формат/Элемент управления откройте диалоговое окно Формат элемента управления и на вкладке Элемент управления (рис. 8.9.) введите в поля Формировать список по диапазону и Помещать результат в ячейку необходимые адреса диапазона и ячейки.

Этому элементу управления назначен макрос ПоискСтатьиФиксированногоПлатежа, который вводит в ячейки F1:F2 формулы поиска кода статьи управленческого учета и наименования этой статьи. При каждом обращении к элементу управления формулы обновляются.

Рис. 25.4. Макрос ПоискСтатьиФиксированногоПлатежа

Например, в ячейку F1 макрос вводит формулу:

=ВПР($AA$1;Пульт!$W$4:$Y$50;СТРОКА(E2);ЛОЖЬ)

Формула оригинальна тем, что для поиска данных кода управленческого учета во втором столбце справочника в качестве третьего аргумента функции ВПР используется функция СТРОКА (рис. 25.5.). Функция возвращает номер строки, определяемой ссылкой, а в качестве ссылки - аргумента функции СТРОКА может быть указана относительная ссылка на любую ячейку строки 2. При последующем копировании формулы в ячейку F2, в функции СТРОКА изменится ссылка на ячейку Е3, а соответственно и в функции ВПР третий аргумент поменяется на значение 3, и, следовательно, поиск информации в справочнике будет осуществляться в столбце 3.

Рис. 25.5. Панель функции СТРОКА

Для записи макроса ПоискСтатьиФиксированногоПлатежа, введите в ячейку F1 формулу, и после начала записи макроса выделите ячейки F1:F2, нажмите клавишу F2, а затем Enter и остановите запись макроса, после чего отредактируйте код VBA.

Ввод кода и статьи управленческого учета

Ввод в список выбранного кода управленческого учета и наименования статьи расшифровки кода осуществляется с помощью кнопки Ввод. Этой кнопке назначен макрос ВводФиксированногоПлатежа (рис. 25.6.)

Рис. 25.6. Подпрограмма ВводФиксированногоПлатежа

Задачей подпрограммы ВводФиксированногоПлатежа является:

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

Перед записью макроса введите в ячейку D11 формулу:

=ВПР($AA$1;Пульт!$W$4:$Y$50;СТОЛБЕЦ(B11);ЛОЖЬ)

и скопируйте ее в ячейку Е11. Формула аналогична предыдущей, введенной в ячейку F1, но в качестве третьего аргумента функции ВПР используется функция СТОЛБЕЦ. Вместо формул в ячейки D11 и Е11 можно ввести ссылки на ячейки F1 и F2. Результат будет тот же.

Строка кода VBA Rows(Row).Copy означает - скопировать строку, номер которой присвоен переменной Row.

Ввод даты и суммы фиксированного платежа

В нашем примере ввод даты и суммы фиксированного платежа производится вручную с клавиатуры, но при желании и этот процесс можно автоматизировать. Проверка правильности вводимых дат осуществляется с помощью кнопки Проверка введенных дат, которой назначен макрос ПроверкаВведенныхДат (рис. 25.7.). Задачей макроса является проверка - введены ли даты в области С11:С510 при вводе кодов управленческого учета в области D11:D510, а если введены, то соответствуют ли они датам рабочих дней в диапазоне ячеек V10:V40 на рабочем листе Прогноз (рис. 25.11.). При обнаружении ошибки макрос формирует в ячейке С7 текст: ОШИБКА ВВОДА ДАТ!. На рис. 25.8. показан пример неправильного ввода дат.

Рис. 25.7. Подпрограмма ПроверкаВведенныхДат

Рис. 25.8. Фрагмент рабочего листа ФиксПотоки с обнаруженными ошибками ввода дат

Перед записью макроса ПроверкаВведенныхДат введите формулу в ячейку В11:

=ЕСЛИ(D11=0;0;ЕСЛИ(ВПР(C11;Прогноз!$V$10:$V$40;1;ЛОЖЬ)=C11;0))

которая при наличии кода управленческого учета в ячейке D11, проверяет - есть ли такая дата в области V10:V40 рабочего листа Прогноз. Если дата отсутствует, то формула возвращает значение ошибки #Н/Д.

В ячейке С7 формула суммирует диапазон B11:B510. При обнаружении ошибки функция ЕНД возвращает значение ИСТИНА и тогда функция ЕСЛИ возвращает текст, что обнаружена ошибка:

=ЕСЛИ(ЕНД(СУММ(B11:B510));"ОШИБКА ВВОДА ДАТ!";0)

Удаление последней записи

Для удаления последней введенной записи в список фиксированных платежей, создайте кнопку Очистка последней строки, которой назначьте макрос УдалениеПоследнейСтроки (рис. 25.9.). Этот макрос легко создается копированием текста кода VBA подпрограммы ВводФиксированногоПлатежа (рис. 25.6.) в Редакторе Visual Basic и незначительным последующим редактированием. Подпрограмма определяет номер строки последней заполненной ячейки в столбце D, выделяет эту строку и очищает содержимое.

Рис. 25.9. Подпрограмма УдалениеПоследнейСтроки

Распределенные платежи

На основе анализа поступления и расхода денежных средств за прошлые периоды принято решение проводить прогнозирование распределенных денежных потоков подекадно. С этой целью, учитывая данные прошлых периодов и перспективы на июль 2003 года, приняты показатели поступления и расхода денежных средств в пропорциях, приведенных в таблицах 20.1. - 20.5.

Таблица 25.1. Подекадное планирование поступления денежных средств за реализованную продукцию

Таблица 25.2. Подекадное планирование расхода денежных средств на приобретение материалов для производства

Таблица 25.3. Подекадное планирование расхода денежных средств на административные общецеховые расходы

Таблица 25.4. Подекадное планирование расхода денежных средств на командировочные расходы

Таблица 25.5. Подекадное планирование расхода денежных средств на приобретение канцелярских товаров

Рабочий лист Прогноз

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

• расчет сумм распределенных платежей (столбцы G:О) - рис. 25.10.;
• расчет временного отрезка планируемого периода (столбцы Q:Х) - рис. 25.11..

Рис. 25.10. Рабочий лист Прогноз с областью расчета сумм распределенных платежей

Рис. 25.11. Область расчета временного отрезка на рабочем листе Прогноз

Расчет временного отрезка планируемого периода

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

• расчеты со всеми календарными днями планируемого месяца (столбцы Q:Т);
• расчеты только с рабочими днями планируемого периода (столбцы V:Х).

Формулы расчета календарных дней планируемого месяца

Прежде всего, определитесь с областью расчета календарных дней планируемого месяца. Исходить нужно из того, что количество календарных дней в различных месяцах может колебаться от 28 до 31. Исходя из максимального количества дней в месяце, введите формулы в столбце Q в тридцать одну ячейку - диапазон ячеек Q10:Q40 (рис. 25.12.). Формула в ячейке Q10 ссылается на введенное значение даты первого дня планируемого месяца на рабочем листе Пульт, а остальные добавляют к предыдущей дате один день.

Рис. 25.12. Формулы левой части расчета временного отрезка планируемого периода

Формулы в столбце R сравнивают номер месяца в первой ячейке временного отрезка Q10 с номерами месяцев в соседней ячейке. Это объясняется необходимостью избежать попадания во временной отрезок дат следующего месяца в том случае, если календарных дней в месяце тридцать и менее. Эта формула при совпадении номеров месяца будет возвращать значение ИСТИНА, а при различии - ЛОЖЬ.

Следующая формула в столбце S определяет с помощью функции ДЕНЬНЕД номер дня недели этой даты. Следует обратить внимание на второй аргумент функции ДЕНЬНЕД равный значению 2. В этом случае номер дня недели - суббота равен шести, а воскресения - семи, что и использует следующая формула в столбце Т.

Формулы в столбце Т созданы на основании функций ЕСЛИ, ИЛИ и НЕ. При значении дня недели равному значению 6 и более, или несовпадении номеров месяцев, формула будет возвращать значение "", а в противном случае дату, находящуюся в этой строке столбца Q.

Расчеты, проводимые с рабочими днями планируемого периода

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

Даты в столбце V вводятся с помощью подпрограммы ВременнойОтрезок, на которой остановимся далее.

Формула, введенная в столбце W, носит справочный характер и определяет день недели даты, введенной в столбце V. Формула, введенная в столбце Х, определяет с помощью функции ДЕНЬ номер дня в месяце.

Рис. 25.13. Формулы правой части расчета временного отрезка планируемого периода

Автоматизация расчета временного отрезка

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

Рис. 25.14. Подпрограмма ВременнойОтрезок

Расчет сумм распределенных платежей

Формулы автоматизации расчета подекадных процентов распределения сумм

В первую очередь определите, по каким статьям управленческого учета будут производиться вычисления фиксированных и распределенных платежей. Это может быть детализация по каждой статье управленческого учета или консолидированное распределение, например, по оплате всего материала для производства. После выбора методики, введите в столбец G в строках выбранных статей учета знак "+".

Размер процентов доли сумм по распределенным платежам за первую и вторую декады введите в столбцы H и I. В третьей декаде расчет процентов производится с помощью формулы, которая при наличии знака "+" в столбце G из 100% вычтет сумму процентов, введенных в столбцы H и I за первую и вторую декаду, или в противном случае возвратит значение ноль.

Рис. 25.15. Область ввода подекадных процентов распределения платежей

Формулы расчета распределенных платежей

Область расчета распределенных платежей можно разделить на следующие:

• формулы расчета количества рабочих дней в декаде (ячейки К1:М1);
• формулы расчета декадных распределенных платежей (область К13:М83);
• формулы определения суммы фиксированных платежей по каждой статье управленческого учета (столбе О).

Рис. 25.16. Формулы расчета распределенных платежей

В ячейке К1 с помощью функции СЧЕТЕСЛИ определяется количество рабочих дней в месяце, если порядковые номера дней месяца равны или менее значения 10. Аналогичная формула находится в ячейке L1, но сравнивает номера дней месяца со значением 20, и из вычисленных рабочих дней за первые двадцать дней месяца вычитает значение рабочих дней первой декады, вычисленное формулой в ячейке К1. Формула в ячейке М1 сравнивает номера дней месяца со значением 20 - больше или равно, после чего определяет количество рабочих дней в третьей декаде. Таким образом, определяется количество рабочих дней во всех трех декадах месяца.

Формула в ячейке О13 определяет сумму фиксированных поступлений платежей, введенных в лист ФиксПлатежи по статье, введенной в ячейку Е13, и копируется в область ячеек О13:О27. Формула в ячейке О31 копируется в область О31:О83 и определяет сумму фиксированных платежей расхода денежных средств.

Формулы в ячейках К13, L13 и М13 определяют разность между общей суммой планируемых платежей согласно БДДС и суммой фиксированных платежей по этой статье. После чего умножают полученную сумму разности на процент доли распределенных платежей в этой декаде и делят на количество определенных в ячейках К1:М1 рабочих дней в декаде. Таким образом, рассчитывается планируемая среднедневная сумма распределенных платежей по каждой статье.

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

После создания формул поручите их ввод макросу ФормулыРаспределения, который после этого заменит их на вычисленные значения и очистит область промежуточных расчетов - Q10:T40 и X10:X35.

Рис. 25.17. Подпрограмма ФормулыРаспределения