Главная Обратная связь

Дисциплины:

Архитектура (936)
Биология (6393)
География (744)
История (25)
Компьютеры (1497)
Кулинария (2184)
Культура (3938)
Литература (5778)
Математика (5918)
Медицина (9278)
Механика (2776)
Образование (13883)
Политика (26404)
Правоведение (321)
Психология (56518)
Религия (1833)
Социология (23400)
Спорт (2350)
Строительство (17942)
Технология (5741)
Транспорт (14634)
Физика (1043)
Философия (440)
Финансы (17336)
Химия (4931)
Экология (6055)
Экономика (9200)
Электроника (7621)






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



Рабочий лист РаспределенныеПлатежи (рис. 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. Графики с корректировкой запланированных платежей по продукции чашка

Итоги

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

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

 

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

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

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

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

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

Открытие файла прогнозирования денежных потоков

Для переноса данных фиксированных и распределенных денежных потоков из файла СистемаБюджетов в таблицу исходных данных на рабочий лист ИсхДанные (рис. 17.1.) файла ДенежныеПотокиБюджет, нажмите кнопку Перенос данных в платежный календарь. Кнопке назначено выполнение подпрограммы ПереносДанныхДенежныеПотоки (рис. 26.1.).

Задача подпрограммы ПереносДанныхДенежныеПотоки - сравнить текущее время и дату в ячейках:

  • J1 листа Пульт книги СистемаБюджетов;
  • С1 листа ПК книги ДенежныеПотокиБюджет

и в зависимости от того идентичны ли эти значения определить открыт ли файл ДенежныеПотокиБюджет. С этой целью в ячейку L1 листа Пульт вводится ссылка на ячейку С1 листа ПК, после чего сравниваются их текущие значения, округленные до 4-го знака после запятой (секунды). Округление играет роль в случае, если в Excel будет открыто несколько файлов, то возможно что при сравнение двух значений по функции определения текущего времени даст расхождение в миллионные доли секунды, и тогда Excel воспримет это как неравенство. Если значения равны, следовательно файл ДенежныеПотокиБюджет открыт, в противном случае закрыт.

Рис. 26.1. Подпрограмма ПереносДанныхДенежныеПотоки

Основным элементом подпрограммы ПереносДанныхДенежныеПотоки является инструкция If-Then-Else, которая применяется для изменения хода выполнения подпрограммы в зависимости от результатов проверки условия.

Упрощенный синтаксис инструкции:

If условие Then
[инструкции]
Else
[инструкции_else]
End If

Элементы инструкции If-Then-Else в нашем примере:

    • If - ключевое слово обозначающее начало инструкции;
    • условие - проверяемое условие равенства значений текущего времени, возвращаемых функцией ТДАТА в двух файлах;
    • Then - ключевое слово обозначающее конец проверки условия;
    • инструкции - при выполнении условия идентичности текущего времени в двух файлах подпрограмма осуществляет переход к файлу ДенежныеПотокиБюджет и затем к выполнению подпрограммы ПереносДанных;
    • Else - ключевое слово, обозначающее конец участка, выполняемого при выполнении условия, или начало участка, выполняемого при несоответствии условия;
    • инструкции - активизация пользовательского диалогового окна (рис. 26.5.) с вопросом об открытии файла. При нажатии на кнопку Да выполнение подпрограммы переходит к процедуре ОткрытиеФайла, на кнопку Нет - завершение выполнения подпрограммы;
    • End If - ключевое слово обозначающее конец инструкции.

Перенос данных в файл прогнозирования денежных потоков

Если файл ДенежныеПотокиБюджет закрыт, то при нажатии на кнопку Да пользовательского диалогового окна (рис. 26.5.) выполнение процедуры переходит к выполнению макроса ОткрытиеФайла (рис. 26.2.), для записи которого выполните команду Файл/Открыть и откройте файл. В конце макроса введите имя подпрограммы ПереносДанных.

Задача подпрограммы ПереносДанных (рис. 26.2.) - очистка рабочей области листа ИсхДанные файла ДенежныеПотокиБюджет и перенос остатка денежных средств на начало планируемого периода. Выполнение всех остальных действий подпрограмма поручает другим процедурам.

Рис. 26.2. Подпрограммы ОткрытиеФайла и ПереносДанных


Просмотров 387

Эта страница нарушает авторские права

allrefrs.ru - 2021 год. Все права принадлежат их авторам!