На этом шаге мы рассмотрим особенности использования формул, используемых для расчета кредитной линии.
Как и ранее, первый элемент автоматизации расчетов - предварительное создание формул, проверка правильности проводимых ими расчетов и последующая запись их ввода в макрос. Для проведения расчетов по кредитной линии введите на рабочем листе СуммПотоки формулы, которые показаны на рисунках 1 и 2.
Рис.1 и 2. Формулы расчетов по кредитной линии
Ставка, по которой начисляются проценты по кредитной линии, находится в ячейке ВА1 - ссылка на ячейку рабочего листа Кредит. Далее формулы расчетов по кредитной линии рассматриваются в строке 4.
Для определения, хватает ли денежных средств для проведения предприятием торговых операций, предназначена формула в ячейке ВА4. Формула анализирует ежедневный остаток денежных средств в столбце Е рабочего листа СуммПотоки. Если в ячейке Е4 находится значение со знаком минус, то денежных средств не хватает и при этом формула возвращает значение ячейки Е3, но с противоположным знаком. Если же значение в ячейке Е4 положительно, то необходимость в кредите отсутствует и формула возвращает значение ноль.
Анализ изменения потребности в заемных ресурсах или возможности их погашения по сравнению с предыдущим днем производится формулой в ячейке ВВ4, которая при нарушении условия равенства потребности в заемных ресурсах в эти дни возвращает различие в такой потребности. При положительном знаке полученного значения присутствует необходимость привлечения дополнительных ресурсов, при отрицательном - возможность их погашения. Если формула возвращает значение ноль, то тогда движения по кредитной линии нет.
Необходимость получения дополнительных заемных средств или возможность погашения кредита регистрируется в ячейке ВС4. Формула при положительном значении в ячейке ВВ4 возвращает значение ячейки ВВ4, что говорит о потребности в привлечении заемных ресурсов. Если же в ячейке ВВ4 отрицательное значение, то формула в ячейке BD4 возвращает его с противоположным знаком. Это говорит о возможности погашения кредита.
Формула в ячейке ВЕ4 фиксирует оставшуюся задолженность по кредитной линии на каждый день. Для этого формула производит вычитание всех возвращенных денежных средств по кредитной линии за период с первого дня по текущую дату от суммы всех полученных денежных средств за этот период. Для того, чтобы при копировании формул вниз по столбцу ВЕ не требовалось их редактирования, в первом операторе функций СУММ применяется абсолютная ссылка на строку 3.
Ежедневная сумма процентов по полученным денежным средствам проводится в ячейке BF4. Формула умножает сумму ежедневных остатков по кредитной линии на ставку процентов по кредитной линии и делит полученный результат на количество дней в году, равный 365.
Формула в ячейке ВН4 определяет факт наступления даты выплаты процентов по кредитной линии. Для этого применимы два момента наступления такого случая:
- последний день месяца, если это указано в кредитном договоре;
- заключительная выплата процентов в день погашения всей задолженности по кредитной линии.
Поэтому формула разделена на две части, в каждой из которых, функция ЕСЛИ определяет:
- что номер дня месяца в ячейке В4 больше, чем в ячейке В5 (смотри рисунок 2). Это может быть только в одном случае: когда в ячейку В4 введена дата, содержащая последний день месяца. Тогда номер дня, определяемый с помощью функции ДЕНЬ в ячейке В5, будет однозначно равен единице - первый день месяца. Исходя из этого, из всей суммы начисленных процентов за весь период с первого дня вычитается вся сумма выплаченных ранее процентов;
- день, когда проценты по кредиту не начисляются, о чем укажет значение ячейки BF4 равное нулю. Это предполагает то, что кредитная линия погашена и тогда в этот день производится заключительная выплата процентов, сумма которой определяется как разность всей суммы начисленных процентов и выплаченных за весь период.
Если вышеперечисленные условия не выполняются, то формула возвращает значение ноль.
При необходимости, можно указать выплату процентов не в последний день месяца, а, например, в предпоследний. Для этого в первой части формулы введите изменение, при котором сравнение дат будет происходить не между соседними ячейками, а через одну - между ячейками В4 и В6. Аналогично можно указать любой день месяца, начиная счет с конца месяца. Если же необходимо изменить отсчет с начала месяца, то измените знак ">" на "<".
Если же в кредитном договоре оговорен конкретный день месяца, то введите номер этого дня, например, в ячейку ВН1, и результат, возвращаемой функцией ДЕНЬ(В4), сравните с содержимым ячейки ВН1. При совпадении номеров дней будет производиться расчет выплачиваемых процентов.
Дата выплаты процентов определяется в ячейке BG4, которая возвращает дату платежа при значении в ячейке ВН4, отличном от нуля.
Макрос ввода формул расчета кредита
Создайте макрос для ввода всех описанных выше формул по расчету кредитной линии. Запись макроса ФормулыРасчетаКредита (рисунок 3) заключается в последовательном вводе формул в диапазоны ячеек каждого столбца от строки 3 до строки 200.
Рис.3. Подпрограмма ФормулыРасчетаКредита, предназначенная для ввода формул, и макрос очистки этой области
Макрос очистки области формул расчета кредита
Макрос ОчисткаФормулРасчетаКредита предназначен для очистки области формул расчета кредита.
Рис.4. Числовые данные расчетов по кредитной линии
На следующем шаге мы рассмотрим отчет по кредитной линии.
