Индивидуальное задание №2.2 по дисциплине "Информационные технологии в менеджменте". Вариант 4. Тема:«Работа с MS Excel»

ЗАДАНИЕ 1. Анализ элементарных потоков платежей
Методические указания
1. Для решения задач анализа элементарных потоков платежей применим шаблон, состоящий из двух частей:
- первая часть – это ячейки с исходными данными (диапазон А5:В9);
- вторая часть – это ячейки с формулами для искомых величин (диапазон А13:В17) (рис. 1).

Рисунок 1 – Шаблон для задач анализа элементарных потоков

При отсутствии исходных данных вторая часть будет содержать нулевые значения или сообщения об ошибках.
2. При решении задач формулы необходимо копировать в соседние диапазоны по столбцам, то есть каждая задача должна решаться в своем столбце.
3. Так как предполагается решать большое количество задач, то удобно заголовки (т. е. столбец А) закрепить (пункт меню Окно/Закрепить области), чтобы при задании данных задач заголовки оставались неподвижными и всегда были видны. При закреплении активной должна быть та ячейка, выше и левее которой происходит закрепление, в нашем случае – ячейка В1.
4. Ответы для задач необходимо выделять другим цветом (рис. 2).

Общие задания
Задание 1
Фирма предполагает взять кредит в 100 000 у. е. на 5 лет под 12% годовых. Проценты начисляются ежеквартально и подлежат выплате вместе с основной суммой долга по истечении срока кредита. Определите сумму выплаты на момент погашения кредита.
Задание 2
Рассчитайте текущую стоимость вклада, который через три года составит 15000 тыс. руб. при начислении 20% в год.
Предположим, Вы хотите поместить на банковский счет средства из расчета 7% годовых. Через какое время депозит в 3000 руб. достигнет размера в 5000 руб.?

Рисунок 2 – Вид шаблона после выполнения общих задач


Индивидуальное задание
Вариант 1
Рассчитайте, какая сумма будет на счете через 3 года, если вклад размером 5000 тыс. руб. разместить под 12% годовых, а проценты начислять каждые полгода.

Вариант 2
По вкладу размером 2000 тыс. руб. начисляется 10% годовых. Рассчитайте, какая сумма будет на сберегательном счете через 5 лет, если проценты начисляются ежемесячно.

Вариант 3
Рассчитайте, количество лет, через которое вклад размером 500 тыс. руб. достигнет величины 1 млн. руб. при ежемесячном начислении процентов и ставке 35,18% годовых.

Вариант 4
Какой должна быть годовая процентная ставка по вкладу размером 800 тыс. руб., если его величина к концу года составила 1 млн 200 тыс. руб., а проценты начислялись ежеквартально?

Вариант 5
По вкладу размером 7000 руб. начисляется 10% годовых Рассчитайте сумму, которая будет на счете через 2 года, если проценты начисляются каждый квартал.

Вариант 6
По вкладу размером 1000 млн руб. начисляется 7% годовых. Рассчитайте сумму, которая будет на сберегательном счете через 6 лет, если проценты начисляются ежегодно.

Вариант 7
Рассчитайте количество лет, которое потребуется, чтобы вклад размером 400 тыс. руб. достиг величины 800 тыс. руб. при ежеквартальном начислении процентов и ставке 23,4% годовых.

Вариант 8
Какой должна быть годовая процентная ставка по вкладу размером 100 тыс. руб., если его величина к концу года составила 150 тыс. руб., а проценты начислялись раз в полгода.

Вариант 9
Рассчитайте сумму, которая будет на счете через 5 лет, если вклад размером 3 млн руб. размещен под 7% годовых, а проценты начисляются раз в полгода.

Вариант 10
По вкладу размером 3 млн руб. начисляется 5% годовых. Рассчитайте, какая сумма будет на сберегательном счете через 9 лет, если проценты начисляются ежеквартально.

ЗАДАНИЕ 2. Аннуитеты
Методические указания
1. Постройте самостоятельно шаблон по приведенному примеру (рис. 3).


Рисунок 3 – Вид шаблона для анализа аннуитетов

2. Во вторую (расчетную) часть шаблона поместите соответствующие формулы по аналогии с темой «Элементарные потоки» (рис. 4).
Примечание. Будьте внимательны! Теперь в формулах нужно указывать все аргументы.

Общие задания
Задание 1
Определите сумму годового платежа (сумма вносится в конце каждого года) для погашения шестилетнего займа в 100 000 у. е. с процентной ставкой 10% годовых.
Задание 2
Объясните, каким образом можно накопить $5000 за три года, откладывая на свой счет определенную сумму в конце каждого месяца, если норма процента по вкладу составляет 9% годовых и в начальный момент времени на счету находится $1450.
Задание 3
Предположим, что необходимо накопить 4000 тыс. руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Определите, какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых.

Рисунок 4 – Вид шаблона после выполнения общих заданий


Индивидуальное задание
Вариант 1
На сберегательный счет вносятся платежи по 200 тыс. руб. в начале каждого месяца. Рассчитайте сумму, которая окажется на счете через 4 года при ставке 13,5% годовых. Сравните будущее значение счета, если платежи вносятся в конце каждого месяца.

Вариант 2
Определите текущую стоимость обязательных ежеквартальных платежей размером 100 тыс. руб. в течение 5 лет, если процентная ставка составляет 12% годовых.

Вариант 3
Рассчитайте количество лет, через которое обязательные ежегодные платежи размером 150 тыс. руб. принесут доход в 10 млн руб. при ставке 13,5% годовых.

Вариант 4
Предполагается путем ежеквартальных взносов постнумерандо по 35 млн руб. в течение 3 лет создать фонд размером 500 млн руб. Какой должна быть годовая процентная ставка?

Вариант 5
Определите размер ежегодного погашения займа в 50 млн руб., выданного на 3 года под 38% годовых.

Вариант 6
На сберегательный счет вносятся платежи по 100 тыс. руб. в начале каждого месяца. Рассчитайте сумму, которая окажется на счете через 5 лет при ставке 12,45% годовых. Сравните будущее значение счета, если платежи вносятся в конце каждого месяца.

Вариант 7
Определите текущую стоимость обязательных ежеквартальных платежей размером 134 тыс. руб. в течение 6 лет, если процентная ставка составляет 10% годовых.

Вариант 8
Рассчитайте количество лет, через которое обязательные ежегодные платежи размером 100 тыс. руб. принесут доход в 5 млн руб. при ставке 11,2% годовых.

Вариант 9
Предполагается путем ежеквартальных взносов постнумерандо по 4 млн 300 тыс. руб. в течение 7 лет создать фонд размером 220 млн руб. Какой должна быть годовая процентная ставка?

Вариант 10
Определите размер ежегодного погашения займа в 70 млн руб., выданного на 2 года под 43% годовых.


ЗАДАНИЕ 3. Разработка плана погашения кредита
Методические указания
Сформируем шаблон для разработки планов погашения кредитов (рис. 5).

Рисунок 5 – Внешний вид шаблона для плана погашения кредита

Первая часть этого шаблона предназначена для ввода условий, на основании которых получен (выдан) кредит, т. е. для задания величин PV, r, n. Кроме того, как и в предыдущих случаях, необходимо предусмотреть вариант выплат процентов m раз в году, а также различные типы начисления процентов – в начале или в конце каждого периода. По умолчанию определим m=1, тип начисления – 0 (конец периода).
Для записи исходных данных удобно использовать табличную форму с более компактным и наглядным их представлением. С учетом оформления заголовков и таблицы для ввода исходных данных эта часть шаблона будет занимать первые шесть строк ЭТ.
Перед тем как приступить к проектированию второй части шаблона, целесообразно выполнить еще одну полезную операцию – определить собственные имена для ячеек, в которые будут вводиться исходные данные. Предполагаемые имена для ячеек приведены в таблице 2.

Таблица 2 – Предполагаемые имена ячеек

Ячейка	Имя
А6	Сумма
В6	Срок
С6	Выплат
D6	Ставка
E6	Тип

Имена можно использовать в качестве адресных ссылок на ячейки, блоки, отдельные значения или формулы, причем при копировании формул имена остаются ссылкой на ту же ячейку (т. е. фактически имеем дело с абсолютным адресом ячейки). В общем случае, символические имена (именные ссылки) могут быть использованы везде, где можно применить обычные адресные ссылки ППП Excel.
При определении имен следует руководствоваться правилами:
- имя должно начинаться с буквы или символа _;
- использование пробелов в именах недопустимо, в качестве разделителей слов следует применять знак _ (например Число_выплат) ;
- длина имени не должна превышать 255 символов.
Существует несколько способов определения имен. Наиболее простой – использование окна имен (рис. 6), которое расположено в левой части строки формул ППП Excel.

Рисунок 6 – Окно имен и строка формул Excel

Для этого необходимо:
1) сделать ячейку активной (т. е. установить в нее указатель);
2) щелкнуть мышью по окну имен. При этом ссылка на ячейку будет выделена, а указатель примет вид вертикальной черты;
3) ввести с клавиатуры требуемое имя, нажать клавишу <Enter>.
Вторая часть шаблона должна содержать результаты вычислений по периодам, формулы приведены в таблице 3.

Таблица 3 – Формулы для вычисления

Ячейка	Формула
C9	= −ПЛТ(СТАВКА/ВЫПЛАТ;СРОК*ВЫПЛАТ;СУММА;;ТИП)
F9	= СРОК * ВЫПЛАТ
B12	= СУММА – Е12
C12	= −ОСПЛТ(СТАВКА/ВЫПЛАТ;A12;СРОК*ВЫПЛАТ;СУММА;;ТИП)
D12	= −ПРПЛТ(СТАВКА/ВЫПЛАТ;A12;СРОК*ВЫПЛАТ;СУММА;;ТИП)
E12	= −ОБЩДОХОД (СТАВКА/ВЫПЛАТ; СРОК * ВЫПЛАТ; СУММА; 1; А12; ТИП)
F12	= −ОБЩПЛАТ (СТАВКА/ВЫПЛАТ; СРОК * ВЫПЛАТ; СУММА; 1; А12; ТИП)

Все функции заданы с отрицательным знаком, это обеспечивает возможность ввода исходных данных и получения результатов с положительным знаком.
Ячейка С9 содержит формулу расчета периодического платежа, а F9 – общего числа периодов проведения операции. Значение последней показывает также предел копирования формул блока А12/F12.

Общие задания
Методические указания
Будьте внимательны! Задания данной темы выполняются на одном и том же шаблоне. Поэтому после каждого выполненного задания покажите результат преподавателю.

Задание 1
Банком выдан кредит в 10 тыс. руб. на 5 лет под 12% годовых, который должен быть погашен равными долями, выплачиваемыми в конце каждого года. Разработайте план погашения кредита.

Задание 2
Разработайте план погашения кредита, полученного на следующих условиях:
50 тыс. сроком на 7 лет под 8% годовых при выплате раз в год.

Индивидуальное задание
Вариант 1
Разработайте план погашения кредита, полученного на следующих условиях:
100 тыс. руб. сроком на 10 лет под 5% годовых при выплате раз в квартал.

Вариант 2
Разработайте план погашения кредита, полученного на следующих условиях: 75 тыс. руб. сроком на 3 года под 12% годовых при выплате раз в месяц.

Вариант 3
Разработайте план погашения кредита, полученного на условиях:
10 тыс. руб. сроком на 3 года под 12% годовых при выплате раз в год.

Вариант 4
Разработайте план погашения кредита, полученного на условиях:
70 тыс. руб. сроком на 4 года под 10% годовых при выплате раз в месяц.

Вариант 5
Разработайте план погашения кредита, полученного на условиях:
100 тыс. руб. сроком на 8 лет под 8% годовых при выплате раз в квартал.

Вариант 6
Разработайте план погашения кредита, полученного на условиях:
20 тыс. руб. сроком на 4 года под 11% годовых при выплате раз в год.

Вариант 7
Разработайте план погашения кредита, полученного на условиях:
120 тыс. руб. сроком на 9 лет под 7% годовых при выплате раз в квартал.

Вариант 8
Разработайте план погашения кредита, полученного на условиях:
50 тыс. руб. сроком на 5 лет под 12% годовых при выплате раз в месяц.

Вариант 9
Разработайте план погашения кредита, полученного на условиях:
30 тыс. руб. сроком на 3 года под 15% годовых при выплате раз в год.

Вариант 10
Разработайте план погашения кредита, полученного на условиях:
60 тыс. руб. сроком на 6 лет под 10% годовых при выплате раз в месяц.