Автоматизация и моделирование бизнес-процессов в Excel

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

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

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

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

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

Одинаковой точки зрения на оптимальное значение величины коэффициента абсолютной ликвидности нет, и предполагается, что эта величина, в зависимости от вида деятельности предприятия, может колебаться от 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. Подпрограмма ФормулыРаспределения

Создание таблицы распределенных платежей

Рабочий лист РаспределенныеПлатежи (рис. 25.18.) — практически точная листа ФиксПлатежи, но на нем отсутствуют элементы управления и данные в него вносятся полностью в автоматическом режиме при нажатии на кнопку Создание таблицы распределенных платежей на листе Пульт. Кнопке назначен макрос СозданиеТаблицыРаспрПлатежей (рис. 25.19.).

Рис. 25.18. Рабочий лист РаспределенныеПлатежи

Подпрограмма СозданиеТаблицыРаспрПлатежей

Подпрограмма СозданиеТаблицыРаспрПлатежей производит только две самостоятельные операции:

• создание копии рабочего листа Прогноз;
• ввод в диапазон Е11:Е510 формулы определения наименования статей управленческого учета:
• =ЕСЛИ(ЕНД(ВПР(D11;Пульт!$X$4:$Y$50;2;ЛОЖЬ));0;ВПР(D11;Пульт!$X$4:$Y$50;2;ЛОЖЬ))
• и последующую замену ее на значения.

Выполнение всех остальных операций производится с помощью других подпрограмм. Две из них — ВременнойОтрезок и ФормулыРаспределения уже рассматривались в этой главе.

Рис. 25.19. Подпрограмма СозданиеТаблицыРаспрПлатежей

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

Copy Before:=Sheets(5)

Подпрограмма ПереносДанныхБДДС

Подпрограмма ПереносДанныхБДДС предназначена для копирования данных сумм на листе БДДС и вставке их в лист Прогноз.

Рис. 25.20. Подпрограмма ПереносДанныхБДДС

Подпрограмма УдалениеСтолбцовСтрок

Для записи макроса УдалениеСтолбцовСтрок (рис. 25.21.), последовательно удалите столбцы и строки на вновь созданном листе Прогноз (2). Только не следует забывать, что при удалении столбцов А:D правые столбцы сместятся влево и столбец F переместится на место столбца В. После этого отредактируйте код VBA подпрограммы.

Рис. 25.21. Макрос УдалениеСтолбцовСтрок

После выполнения макроса УдалениеСтолбцовСтрок лист Прогноз (2) примет вид, показанный на рис. 25.22. Что получилось после создания копии листа Прогноз, удаления столбцов и строк? Временная таблица в области столбцов А:D со всеми атрибутами списка Excel, с которой можно делать все что вздумается, например, произвести сортировку рабочего диапазона.

Рис. 25.22. Рабочий лист Прогноз (2) после удаления столбцов и строк

Подпрограмма СортировкаПлатежей

Для записи макроса СортировкаПлатежей (рис. 25.24.), поместите табличный курсор в область созданной таблицы и, выполнив команду Данные/Сортировка, вызовите диалоговое окно Сортировка диапазона (рис. 25.23.). Это диалоговое окно позволяется произвести последовательную сортировку таблицы в выбранной последовательности и выбранному режиму: по убыванию или по возрастанию. Задайте сортировку всех трех столбцов с числовыми данными по убыванию в любой последовательности. Такая "тройная" операция сортировки дает стопроцентную гарантию, что все ячейки, содержащие суммы распределенных платежей в столбцах В:D, будут расположены в верхней области листа.

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

Рис. 25.23. Диалоговое окно Сортировка диапазона

Рис. 25.24. Подпрограмма тройной сортировки по убыванию созданной таблицы

Подпрограмма ФормулыРаспрПлатежей

Задача подпрограммы ФормулыРаспрПлатежей (рис. 25.25.) ввести формулы в созданный лист Прогноз (2).

Рис. 25.25. Подпрограмма ФормулыРаспрПлатежей

В диапазон ячеек G2:G26 вводятся ссылки на ячейку расположенную выше. В диапазон J1:J26 вводятся формулы определения среднедневной подекадной суммы равномерных поступлений денежных средств в зависимости от декады. Формула в ячейке J1:

=ЕСЛИ(ЛЕВСИМВ($G$1;1)="В";0;ЕСЛИ($F1="";0;ВПР($G$1;$A$1:$D$26;ЕСЛИ(ДЕНЬ($F1)<=10;2;ЕСЛИ(ДЕНЬ($F1)<=20;3;4));ЛОЖЬ)))

В первую очередь формула проверяет первый левый символ кода управленческого учета, и, если им является буква В (выплаты/расходы), то формула возвращает значение 0. Далее формула проверяет, присутствует ли в ячейке F1 дата. При ее отсутствии (день не рабочий) также возвращается значение 0. При не выполнении двух первых условий, функция ВПР производит поиск в области А1:D26 значений сумм, указанного в ячейке G1 кода управленческого учета, при этом номер столбца определяется в зависимости от номера дня даты, введенной в столбце F. Например, если номер дня до 10-ти, то 2-й столбец, если выше 20-го номера, то столбец 4-й.

Формула в ячейке К1 аналогична предыдущей, но возвращает суммы расхода сумм равномерных платежей по первой букве П:

=ЕСЛИ(ЛЕВСИМВ($G$1;1)="П";0;ЕСЛИ($F1="";0;ВПР($G$1;$A$1:$D$26;ЕСЛИ(ДЕНЬ($F1)<=10;2;ЕСЛИ(ДЕНЬ($F1)<=20;3;4));ЛОЖЬ)))

Эти формулы "начинают" работать только при выполнении макроса ЦиклФормированияПлатежей и их смысл будет описан далее.

Подпрограмма ЦиклФормированияПлатежей

Основой подпрограммы ЦиклФормированияПлатежей является цикл Do-Loop.

Рис. 25. 26. Подпрограмма ЦиклФормированияПлатежей

Последовательность выполнения ЦиклФормированияПлатежей подпрограммы следующая:

• переменной Y присваивается значение количества рабочих дней в месяце путем суммирования содержимого ячеек К1:М1 на рабочем листе Прогноз;
• далее выполнение подпрограммы переходит к циклу Do -Loop. Цикл будет выполняться до тех пор, пока переменная Z не будет равна нулю, о чем говорит оператор Until. В свою очередь переменная Z равна сумме ячеек столбцов В (2), С (3) и D (4) строки, устанавливаемой переменной Х;
• переменная Х увеличивается с выполнением каждого цикла на единицу. Поэтому переменной Z постепенно присваивается сумма значений ячеек В1:D1, затем ячеек В2:D2 и так далее. Цикл продолжается до тех пор, пока суммы ячеек не будут равны нулю, что говорит о том, что далее ячейки содержащие значения отсутствуют;
• ячейке G1 присваивается значение, находящееся в ячейке на пересечении строки равной переменной Х и столбца 1 (Х,1). При первом выполнении цикла это будет значение ячейки А1, при втором А2 и так далее с увеличение значения переменной Х на единицу при выполнении каждого цикла. При изменении содержимого ячейки G1 это значение повторяется в ячейках нижестоящих в столбце G (см. рис. 25.25.), а так как это значение текста кода управленческого учета, по которому производится выборка данных, то формулы в ячейках столбца J и K возвращают планируемое значение ежедневных оборотов денежных средств по этому коду управленческого учета. Ежедневное значение равно значению доли запланированных распределенных денежных потоков за эту декаду, деленное на количество рабочих дней в этой декаде;
• выделяется ячейка с координатами (Y, 11). Так как переменная Y равна количеству рабочих дней (их количество в июле 2003 года равно 23 дням), а 11-й столбец — это столбец К, то при расчетах за июль месяц должна быть выделена ячейка К18;
• от активной ячейки (К18) выделяется диапазон ячеек (F1:К18) и копируется в буфер обмена. Выделяемый диапазон определяет код VBA Range(ActiveCell, ActiveCell(-Y + 2, -4)), в котором адрес ячейки F1 определяет относительная адресация ячейки ActiveCell(-Y + 2, -4) относительно ячейки К18. Сдвиг по столбцам влево определяет значение -4, а по строкам значение переменной -Y, уменьшенное на 2;
• далее переменной Row присваивается значение заполненных ячеек в столбце АА увеличенное на единицу;
• в столбце АА выделяется ячейка на пересечении строки Row и 27-го столбца (столбец АА), в которую производится вставка значений скопированной области.

Рис. 25.27. Лист Прогноз (2) с отсортированными данными распределенных платежей

Контроль выполнения цикла расчета

В главе 18 была рассмотрена панель инструментов Отладка (рис. 18.5.). При выполнении циклов иногда возникает необходимость наблюдения за поведением переменных. Ведь от их значения зависит своевременное завершение цикла. Для просмотра контрольного значения переменной (или выражения) предназначена кнопка Контрольное значение панели Отладка. Для просмотра контрольного значения выделите переменную, например Y, и нажмите кнопку Контрольное значение, что вызовет появление диалогового окна Контрольное значение (рис. 25.28.), в котором отражено в областях:

• Контекст — местонахождение переменной: окно программ РавномерныеПлатежи, имя подпрограммы ЦиклРавномерныеПлатежи;
• Выражение — переменная Y;
• Значение — указывает на количество рабочих дней в июле 2003 года равное 23.

Рис. 25.28. Диалоговое окно Контрольное значение

При нажатии на кнопку Добавить в Редакторе Visual Basic добавится окно Контрольное значение (рис. 25.29), в котором при пошаговом прохождении подпрограммы можете наблюдать значения переменных. Это же окно открывается при нажатии на кнопку Окно контрольного значения на панели Отладка.

На рис. 25.29. Контроль значения переменных в окне Контрольное значение

Подпрограмма ПереносДанныхРаспрПлатежей

Задачей подпрограммы ПереносДанныхРаспрПлатежей (рис. 25.30.) является копирование области АА1:AF500 на листе Прогноз (2), выделение ячейки С11 на листе РаспределенныеПлатежи и вставка содержимого ячеек скопированной области, но как значений через диалоговое окно Специальная вставка. Предполагаем, что будет переноситься не более 500 записей. Это количество определяется приблизительно, например, предполагаем, что большего количества записей не будет. Можно было бы определить их точное количество, но в таком случае это неоправданно усложнило бы подпрограмму, увеличило время выполнения и еще предварительно потребовалась бы предварительная очистка рабочей области на листе РаспределенныеПлатежи.

Подпрограмма УдалениеЛиста

Лист Прогноз (2) выполнил свои функции и его можно удалить. Подпрограмма УдалениеЛиста (рис. 25.28.) предназначена для удаления рабочего листа Прогноз (2), для записи которой активизируйте этот лист и выполните команду Правка/Удалить лист.

Рис. 25.30. Подпрограммы переноса данных и удаления листа

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

Application.DisplayAlerts = False

После выполнения макроса свойство DisplayAlerts автоматически примет свое стандартное значение True (ИСТИНА).

Рис. 25.31. Диалоговое окно с предупреждающим сообщением о невозможности отмены операции удаления листов

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

Рабочий лист с рассчитанными данными показан на рис. 25.18.

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

Задача финансового менеджера на основе имеющихся данных построить наиболее реальный прогнозный график движения денежных потоков и организовать его выполнение соответствующими службами. Показанный график прогнозных поступлений, например, по тем же чашкам, может и не носить прямолинейный характер. Возможен смешанный подход, например, корректировка графика поступлений с помощью ввода данных поступления выручки в лист ФиксПлатежи. Например, 5-го июля стало известно, что прямолинейная структура распределенных платежей будет изменена и ожидаемая сумма в 100 тысяч поступит не 14 июля, а 15-го. Введите в лист ФиксПлатежи сумму 100 тысяч 14-го со знаком минус, а 15-го со знаком плюс. График изменился (рис. 25.33.). Проведите полный цикл расчетов, рассматриваемых далее в следующей главе, и примите управленческие меры исправления ситуации в результате возможной разбалансировки ранее запланированных платежей.

Рис. 25.32. Графики запланированных и фактических платежей по продукции чашка

Рис. 25.33. Графики с корректировкой запланированных платежей по продукции чашка

Итоги

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

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