Табличная модель
Обычно план-прогноз движения средств разрабатывается в виде таблицы. Это прогнозная или плановая оборотно-сальдовая ведомость для нескольких периодов планового горизонта. В ней отражаются сальдо будущих бухгалтерских счетов на начало периодов, дебетовые и кредитовые обороты (приход и расход средств, поступления и выплаты) и конечные сальдо периодов. Вначале структура документа составляется вчерне на бумаге или сразу в виде электронной таблицы. Пример приведен в табл. 9.1.
Таблица 9.1
В верхней строке электронной таблицы Excel даны имена колонок A, B, C... В первой колонке – номера строк.
Исходные данные для расчетов в таблице окружены пунктирной рамкой. Искомые показатели окружены сплошной жирной рамкой.
В графе А даны наименования показателей и операций по движению средств на плановом расчетном счете. В диапазоне В6:С8 как исходные данные введены сроки депозитов и их доходность. В колонках B и G представлены сокращенные оборотно-сальдовые ведомости на каждый месяц полугодового планового периода. В колонке H – ведомость за 7-й месяц, но уже без инвестиций в депозитные сертификаты. Эта колонка позволяет учесть поступления от предшествующих полугодовых инвестиций. В ячейке H8 вычисляется процентная прибыль по сертификатам за полгода инвестиций. Это цель нашего плана, главный критерий его качества.
В строке 11 дается начальное сальдо для каждого месяца. Оно выполняется копированием конечного сальдо предыдущего месяца из строки 18. Только начальная сумма 1-го месяца не вычисляется, а задается конкретным числом, как начальное условие.
В проекте таблицы поступление средств (дебет счета, входной поток, приход) вычисляется в строках 12 (погашение основных сумм депозитов) и 13 (поступление процентных платежей за пользование депозитом).
В строках с 14-й по 16-ю отводится место для кредитования счета (расходов, выплат) по еще неизвестным при разработке плана инвестициям в 1-месячные, 3-месячные и 6-месячные депозитные сертификаты.
В строке 17 (Расходы) как исходное данное задан прогноз расходов и поступлений на счет по всем другим операциям, кроме операций по депозитным сертификатам. Поступления даны со знаком минус.
В строке 18 вычисляется конечное сальдо каждого месяца как сумма начального сальдо с возвратом основных сумм депозитов и процентов по ним за вычетом инвестиций в депозиты и прогнозируемых расходов.
Формулы таблицы
После составления плановой таблицы необходимо связать причинно-следственные показатели формулами для вычислений. Представление формул и чисел исходных данных дано в табл. 9.2.
Таблица 9.2
В таблице формулы по месяцам идентичны, поэтому для компактности таблицы мы скрываем 3, 4 и 5-й месяцы.
Управление экспериментами
Осуществляется вручную или с помощью программы оптимизации Поиск решения. Управляющими данными являются суммы инвестиций на покупку депозитных сертификатов в строках 14:16. Управление осуществляется с целью максимизации процентной прибыли в ячейке Н8.
Для расширения экспериментов, где есть над чем подумать финансовому менеджеру, можно менять доходность депозитов, динамику прогноза расходов, вводить ограничения для показателей и др.