Лабораторная работа № 11. Консолидация данных
Консолидация является простой и эффективной процедурой, во время которой происходит объединение однотипных данных и подведение итогов. Работа ведется в рамках группы команд «Данные».
Задание 1. Консолидация данных
1. Создать новую рабочую книгу (4 листа). Присвоить листам книги имена: "Январь", "Февраль", "Март", "1 квартал".
2. Разместить на листе "Январь" таблицу:
3. Разместить на листе "Февраль" таблицу:
4. Разместить на листе "Март" таблицу:
5. Перейти на лист "1 квартал" и выделить ячейку А1. Выполнить команду Данные/группа Работа с данными/Консолидация.
6. В поле "Функция" установить "Сумма".
7. В поле "Ссылка" установить фокус ввода и по очереди выделять диапазоны для консолидации, находящиеся на предыдущих листах. Выделение каждого диапазона заканчивать нажатием кнопки "Добавить", при этом адрес диапазона будет помещен в окно "Список диапазонов".
8. Установить флажки "Подписи верхней строки" и "Значение левого столбца". Флажок "Создавать связи с исходными данными" пока не устанавливать. Нажать "ОК". Результат консолидации появится на листе "1 квартал".
9. Исследуйте полученные результаты. Например, измените количественные данные (объем сделок или их количество по какому-либо агенту) на листе "Март", проверьте, изменились ли при этом данные листа "1 квартал" (если все верно сделано, то изменений не будет). А теперь выполните команду Данные/Консолидация и сразу "ОК", убедитесь в обновлении таблицы на листе "1 квартал".
10. Выделите на листе "1 квартал" ячейку А1 и выполните команду Данные/Консолидация и установите флажок "Создавать связи с исходными данными" и "ОК". Внесите количественные изменения в исходные данные листа "Январь" и проследите за тем, как изменились итоги на листе "1 квартал", теперь они будут обновляться автоматически.
Лабораторная работа №12.
Финансовые операции с элементарными потоками платежей
Простейший (элементарный) денежный поток состоит из одной выплаты и последующего поступления либо разового поступления с последующей выплатой, разделенных n периодами времени (например, лет). Пример: срочные депозиты, единовременные ссуды, некоторые виды ценных бумаг.
Операции с элементарными потоками платежей характеризуются следующими параметрами (в скобках приведены функции Excel, предназначенные для их нахождения):
- FVn – будущая стоимость потока за n периодов (БС);
- PVn – современная стоимость потока за n периодов (ПС);
- CFt – величина потока платежей в периоде (ПЛТ);
- r – процентная ставка (СТАВКА);
- n – количество периодов проведения операции (КПЕР).
Для вычисления характеристик финансовых операций с элементарными потоками платежей удобно использовать функции БС, КПЕР, СТАВКА, ПС.
Основные аргументы функций:
Пример оформления листа для решения задач по расчету параметров операций с элементарными потоками платежей приводится на рисунке 21.
Рис. 21. Шаблон для решения задач о простейших потоках
Часть ячеек заполняется базовыми значениями, другие представляют собой расчетные величины.
При заполнении данной формы необходимо обращать внимание на следующее: все аргументы, означающие выплачиваемые суммы должны вводиться отрицательными значениями, и обратно, если аргумент представляет собой получаемые суммы, его значение положительно. Любой элементарный поток платежей должен обязательно содержать оба значения.
Задание 1. Расчет будущей величины элементарного потока платежей.
Пусть сумма в 10 000 рублей помещена в банк на депозит сроком на десять лет. Ставка по депозиту 20% годовых. Проценты начисляются один раз в год. Какая сумма будет находиться на счете в конце срока?
1. Подготовить таблицу-форму для расчетов.
2. В соответствующие ячейки подготовленной таблицы-формы ввести исходные данные – величины PV, n, r.
3. В ячейку, зарезервированную для расчета величины FV ввести функцию БС, используя в качестве аргументов ссылки на соответствующие ячейки.
Указание. Обратите внимание, что аргумент «Ставка» определяется как отношение годовой процентной ставки из условия и количества начислений в году. Аргумент «Число_периодов» определяется как произведение срока проведения операции и количества начислений за год. Такой вариант записи аргументов учитывает случаи, когда начисление процентов осуществляется чаще чем 1 раз в год.
Задание 3. Расчет годовой процентной ставки.
Страховая компания реализует полисы стоимостью 2000 денежных единиц. Определить годовую доходность данной операции, если по условиям договора предполагается выплата 10 000 денежных единиц по истечении 5 лет. Банковская практика предполагает ежеквартальное начисление процентов.
1. Использовать для решения таблицу-форму предыдущей задачи.
2. Для вычисления годовой процентной ставки использовать функцию СТАВКА. Учесть, что данная функция позволяет определить процентную ставку за период начисления процентов (в данном случае – за квартал). Искомый показатель будет получен путем умножения найденного значения на количество начислений за год (здесь – 4).
Лабораторная работа №13.
Денежные потоки в виде серии равных платежей (аннуитеты)
Поток платежей, все элементы которого распределены во времени так, что интервалы между любыми двумя последовательными платежами постоянны, называют финансовой рентой, или аннуитетом.
В финансовой практике часто встречаются так называемые простые, или обыкновенные, аннуитеты, которые предполагают получение или выплаты одинаковых по величине сумм в течение всего срока операции в конце каждого периода (года, полугодия, квартала, месяца).
Простой аннуитет обладает двумя важными свойствами: все его элементы равны между собой и отрезки времени между выплатой/получением сумм CF одинаковы. В отличие от разовых платежей здесь необходимы все характеристики денежных потоков: FV, РV, CF, r, n.
Автоматизация расчетов ведется с помощью функций EXCEL БС, ПС, КПЕР, СТАВКА и функции определения размера периодического платежа ПЛТ, аргументы которой приведены в таблице.
Задание 1.
Корпорация «А» планирует покупку земельного участка стоимостью 1000000 рублей. Какой должна быть величина ежегодного взноса для создания соответствующего фонда в течение 10 лет, если процентная ставка равна 5% годовых?
Указание. Условиями данной операции первоначальной суммы на вкладе в момент времени t = 0 не предусмотрено. Поэтому ПС (начальное значение вклада) принимаем равным 0.
Задание 2.
Работник решил уйти на пенсию. Фирма, в которой он работает, предлагает ему два варианта выплаты пенсии: 1) в виде единовременного пособия в 55000 руб.; 2) ежегодную выплату 10000 руб. на протяжении 10 лет. Какой вариант пенсии можно посоветовать выбрать при ставке депозита 10%?
Задание 3.
Клиент банка осуществил заем в размере $5000 под 6% годовых на 6 месяцев. Определите ежемесячные платежи клиента, при условии их осуществления в конце каждого периода.
Кредит погашается одинаковыми платежами, равномерно распределенными во времени. Возникающие при этом денежные потоки также являются аннуитетами.
Основная задача планирования поступлений (выплат) по кредитам сводится к исчислению составных элементов платежей и распределению их во времени. Используем следующие функции (таблица).
Ниже приведен пример оформления листа для решения таких задач (рис. 22). Верхняя часть данной формы будет заполняться базовыми значениями, нижняя предназначена для вывода результатов от вычислений (за исключением столбца «Номер периода», в который пользователем будет вводиться номер периода выплаты).
Рис. 22. Образец оформления задачи о погашении кредита
Алгоритм расчета основных показателей:
1. Величина периодического платежа: используемая функция – ПЛТ.
2. Баланс на начало периода: для первого периода это начальная сумма кредита, для всех остальных – баланс на конец предшествующего периода.
3. Баланс на конец периода. Это баланс на начало текущего периода выплат плюс величина периодического платежа минус размер части выплаты, идущей на оплату процентов.
4. Выплата по основному долгу: используемая функция – ОСПЛТ.
5. Выплата по процентам: используемая функция – ПРПЛТ
Замечание. Сумма «Выплаты по основному долгу» и «Выплаты по процентам» должна равняться «Величине периодического платежа», независимо от периода.
Задание 4. Разработка плана погашения кредита
Банк выдал кредит в сумме 40 000 ден.ед. на 5 лет под 6% годовых. Погашение кредита должно производиться равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в году. Составьте план погашения кредита. Решите данную задачу, используя методику, рассмотренную выше.