|
Если таблица на рабочем листе ИсхДанные предназначена для ввода данных по каждой проводимой финансовой операции, то можно предположить что присутствует возможность ввода за один день нескольких записей. В таком случае возникает необходимость ежедневного суммирования оборотов по этим операциям, а также вычисления остатка денежных средств на конец/начало каждого банковского дня. Поэтому специалистами финансового отдела АО "Стеклодув" принято решение, что следующий рабочий лист создаваемой модели прогнозирования денежных потоков должен содержать таблицу расчета суммарных ежедневных денежных потоков, в которой:
- рассчитываются ежедневные обороты, как по поступлению денежных средств, так и по их списанию;
- на основании вычисленных ежедневных оборотов и остатка денежных средств на начало периода прогнозирования и конец предыдущего дня, определяется остаток денежных средств на конец текущего дня;
- при необходимости в этой таблице должна присутствовать возможность ввода в нее рассчитанных сумм налогов;
- должен присутствовать контроль правильности введенных дат;
- расчеты должны проводиться как по каждой валюте в отдельности, так и в общей массе денежных потоков.
Создаваемая таблица на рабочем листе СуммПотоки (рис. 19.1.) расположена в области В2:Е200. При таких размерах таблица показывает движение денежных средств (далее ДДС) за 197 дней и при необходимости ее размеры могут быть уменьшены или увеличены.
Формулы, используемые для создания таблицы
Выборка данных из рабочего листа ИсхДанные осуществляется в столбцах АА:АС листа СуммПотоки по введенному признаку движения денежных средств (ДДС) в столбец Н рабочего листа ИсхДанные.
Для этого введите формулы в ячейку АА3:
=ЕСЛИ(ИсхДанные!H10=1;ИсхДанные!D10;"")
и в ячейку АВ3:
=ЕСЛИ($AA3="";0;ИсхДанные!I10)
Первая формула при наличии введенного признака ДДС в ячейку Н10 рабочего листа ИсхДанные возвращает значение даты, введенной в ячейку D10 того же рабочего листа. А вторая формула при наличии даты в ячейке АА3 возвращает значение поступления денежных средств (далее ДС) в ячейке I10 рабочего листа ИсхДанные. Во второй формуле, при ссылке на ячейку АА3 введена абсолютная ссылка на столбец АА и поэтому при копировании содержимого ячейки АВ3 в ячейку АС3, ссылка в формуле на ячейку АА3 не изменится, но формула будет возвращать уже значение списанных ДС, введенных в ячейку J10 рабочего листа ИсхДанные.
Рис. 19.1. Таблица отображения движения денежных средств в ежедневном разрезе
Следующий элемент создания таблицы — ввод формул определения дат:
- с которой начинается таблица;
- горизонта просмотра;
- контроля за правильностью вводимых дат.
Определение даты, с которой начинается таблица, осуществляется в ячейке В3 по формуле, которая с помощью функции МИН производит поиск минимальной даты в столбце АА, а затем вычитает из нее один день:
=МИН(AA:AA)-1
В ячейке В4 формула добавляющая один день, которая затем копируется в область В4:В200:
=B3+1
Формула контроля введенных дат находится в ячейке В1:
=ЕСЛИ(МАКС(AA:AA)<=МАКС(B3:B200);0;"Обнаружена ошибка в вводе данных по дате!")
которая сравнивает наибольшую дату в столбце АА и если она больше даты, введенной в таблицу формирования оборотов ДС, то возвращает текст: Обнаружена ошибка в вводе данных по дате!. Это говорит о том, что в таблице на рабочем листе ИсхДанные введены даты, которые превышают планируемый горизонт, а значит, при расчетах могут быть присутствовать ошибки.
Формула суммированных оборотов ДДС за день в ячейке С3:
=СУММЕСЛИ($AA:$AA;$B3;AB:AB)
В ячейке D3 формула аналогична, но со знаком минус:
=-СУММЕСЛИ($AA:$AA;$B3;AC:AC)
Знак минус больше необходим для графика, отражающего суммарные обороты расходной и доходной части по этой таблице. В таком случае расходная часть будет находиться в отрицательной области, что визуально более воспринимаемо.
При создании формулы определения сальдо имеющихся всех денежных средств на первый день расчета в ячейке Е3:
=ИсхДанные!D1+ОКРУГЛ(ИсхДанные!D2*ВПР(B3;'D:\Самоучитель\[Курс.xls]Курс'!$A:$D;2;ЛОЖЬ);2)+ОКРУГЛ(ИсхДанные!D3*ВПР(B3;'D:\Самоучитель\[Курс.xls]Курс'!$A:$D;3;ЛОЖЬ);2)
откройте файл Курс, и после создания формулы закройте его. После закрытия файла формула добавит путь нахождения файла, который включает в себя:
- диск — D;
- имя директории — Самоучитель;
- имя файла — Курс;
- имя рабочего листа — Курс.
Тогда эта формула сможет производить вычисления и при необходимости обновить данные без открытия файла Курс. Формула производит суммирование сальдо денежных средств в национальной валюте на начало рассматриваемого периода с сальдо в иностранных валютах, умноженных на их курс по отношению к национальной валюте на дату расчета.
Значение сальдо за последующие дни определяется по формуле в ячейке Е4 суммирующей значение сальдо предыдущего дня с суммой оборотов за день. Формула затем копируется в диапазон ячеек Е4:Е200:
=E3+СУММ(C4:D4)
Рис. 19.2. Формулы вычислений таблицы отображения движения денежных средств в ежедневном разрезе
Автоматизация ввода формул расчета
Ввод дат
После создания формул описанных выше, приступите к записи макроса ВводДат (рис. 19.3.) ввода этих формул и последующей замены их на вычисленные ими значения. Не пытайтесь записывать большие макросы, а ограничьтесь записью отдельных фрагментов выполняемых действий, которые затем будет легко объединить.
Рис. 19.3. Подпрограмма ВводДаты
Макрос выборки данных ДДС по всем валютам
Макрос ВыборкаДанныхЭквивалент (рис. 19.4.) предназначен для:
- ввода формул определения дат и ДДС по каждой операции в диапазоны ячеек АА3:АА5000 и АВ3:АС5000;
- передачи выполнения операций подпрограмме ВводДат, которая по этим данным рассчитает временную ось для создаваемой таблицы;
- ввода формулы определения сальдо эквивалента всех имеющихся ДС на начальную дату расчета.
Рис. 19.4. Подпрограмма ВыборкаДанныхЭквивалент
Выборка данных по различным валютам
Если необходимо определить ДДС по каждой из валют, то тогда в формулы выборки данных в столбцах АВ и АС введите корректировку. Формулы должны возвращать значения столбцов М и N вместо I и J из листа ИсхДанные. В формулу в ячейке АА3 введите дополнительную функцию ЕСЛИ для того, чтобы выборка осуществлялась и по указанной валюте. Например, для российского рубля:
=ЕСЛИ(ИсхДанные!H10=1;ЕСЛИ(ИсхДанные!K10="RUR";ИсхДанные!D10;"");"")
Кроме того этой подпрограмме нет необходимости производить перерасчет валюты по существующему на день расчета курсу. Поэтому при определении остатка ДС в этой валюте ячейке Е3 таблицы просто присваивается значение остатка ДС, введенного в диапазон ячеек D1:D3 на рабочем листе ИсхДанные (см. рис. 17.10.). Например, для определения сальдо ДС по российским рублям ячейке Е3 присваивается значение ячейки D1 на листе ИсхДанные.
Можно производить выборку в различных валютах, причем как непосредственно в этих валютах, так и в пересчете их, каждой в отдельности, в национальную валюту на день расчета.
Рис. 19.5. Подпрограмма ВыборкаДанныхRUR
Расчет ежедневных оборотов ДДС и остатка ДС на каждый день
Выполнение подпрограммы РасчетОборотов (рис. 19.6.) заключается во вводе формул расчета ежедневных оборотов ДДС и остатка ДС в диапазоны ячеек С3:Е200, замене формул на определенные ими значения и последующую очистку содержимого столбцов АА:АС.
Рис. 19.6. Подпрограмма РасчетОборотов
Полный цикл заполнения таблицы суммарных оборотов
Подпрограмма ДенежныеПотокиЭквивалент автоматически выполняет все этапы, описанные выше, и включает в себя две процедуры: ВыборкаДанныхЭквивалент и РасчетОборотов. Точно такие же процедуры выполняют подпрограммы по расчетам с выбранными валютами.
Рис. 19.7. Подпрограммы полного расчета таблицы
Ввод сумм рассчитанных налогов в таблицу движения денежных потоков
Перенос сумм налогов и сроков их оплаты на лист СуммПотоки
Следующая предстоящая задача — создание подпрограммы ввода сумм рассчитанных налогов в таблицу суммарных потоков ДС. Выбор наиболее оптимального пути зависит от ориентации расположения таблицы на листе Налоги. А она имеет горизонтальное расположение. Поэтому первая задача расположить находящиеся в ней значения вертикально.
В таблице суммарных потоков не имеет значение назначение платежа. Таблицу интересует только дата и сумма проведения операции. Для вертикального расположения данных таблицы с рассчитанными налогами предназначена подпрограмма НалогиПереносДанных (рис. 19.8.), которая:
- последовательно копирует содержимое диапазонов таблицы Налоги и производит их вставку на листе СуммПотоки;
- производит сортировку вставленных данных не считая первую строку строкой заголовка.
Рис. 19.8. Подпрограмма транспонирования расположения данных таблицы с рассчитанными налогами
Ввод сумм налогов в таблицу суммарных ДДС
Для определения сумм налогов, выплата которых приходятся на даты, введенные с клавиатуры в ячейки, предназначенные для ввода дат на рабочем листе Налоги (рис. 18.1.), в столбце АС введите формулы, которые в зависимости от дат, введенных в столбце В, будут определять эту сумму. В ячейке АС3 находится формула:
=СУММЕСЛИ($AA$3:$AA$38;B3;$AB$3:$AB$38)
Значения, полученные в результате расчета этой формулы нужно добавить в расходную часть общей таблицы движения ДС. Это производится выделением области АС3:АС200, копированием ее в буфер обмена и последующей вставкой в область D3:D200, но используя при этом диалоговое окно Специальная вставка, в котором необходимо активизировать переключатели Значения и Сложить. Обратите внимание на код VBA вставки значений со сложением. В ней присутствуют только те действия, которые совершаются. В этом и заключается редакция кода VBA.
После этого выделите область расчета остатков ДС на конец дня, введите формулу:
=E3+СУММ(C4:D4)
и нажмите комбинацию клавиш Ctrl+Enter. После этого замените формулы на вычисленные значения в столбцах D:Е и очистите область промежуточных расчетов в столбцах АА:АС. Последовательность этих операций выполняет подпрограмма НалогиВводТаблица (рис. 19.9.).
Рис. 19.9. Подпрограмма НалогиВводТаблица
Цикл полного создания таблицы суммарных оборотов ДС
Полный цикл расчетов для формирования данных таблицы с суммарным потоков ДС осуществляет подпрограмма РасчетТаблицыСНалогами (рис. 19.10.), которая:
- производит перерасчет всех данных на листе ИсхДанные, после чего формирует таблицу с расчетом налогов (РасчетВсехНалогов);
- рассчитывает таблицу суммарных оборотов ДДС без учета налогов (ДенежныеПотокиЭквивалент);
- вводит в таблицу суммарных оборотов ДДС рассчитанные суммы налогов (НалогиПереносДанных и НалогиВводТаблица).
Рис. 19.10. Подпрограмма выполнения полного цикла формирования итоговых данных ДДС
Таблица суммарных оборотов ДС с итоговыми данными
После записи макросов создайте несколько элементов управления Кнопка запускающих на выполнение макросы, расположите их в удобном для вас порядке и назначьте им выполнение макросов. Итоговая таблица с данными, полученными в результате полного цикла расчетов, показана на рис. 19.11.
Рис. 19.11. Рабочий лист СуммПотоки с созданными элементами управления
Итоги
В этой главе, используя несложные подпрограммы, получена таблица суммарных оборотов ДДС по проведению планируемой операции по поставке товара.
Достоинство этой таблицы заключается в том, что по остаткам денежных средств можно судить о том, когда и сколько денежных средств не хватает предприятию для проведения операции. А на основании этих данных можно принимать решения — где брать недостающие ресурсы и сколько за это платить.
|
