Тема: Условны функции, условное форматирование

Лабораторная работа №12

Финансовые функции МS Ехсеl.

 

Цель: Изучение принципов использования условных функций и условного форматирования.

Изучение финансовых функций, применяемых для анализа выгодности инвестиций в бизнес.

 

I. Условные функции и условное форматирование.

Задание №1

Создать таблицу «Календарь погоды» по предложенному образцу и продолжить заполнение таблицы до конца месяца. На основании данных выполнить следующие расчёты:

1. Вычислить среднемесячную температуру.

2. Подсчитать количество дней, когда шёл снег

=СЧЁТЕСЛИ(B5:AC5;"С")

3. Подсчитать количество дней, когда шёл дождь

4. Подсчитать количество дней, когда не было осадков.

календарь погоды
	1.2.2001	2.2.2001	3.2.2001	4.2.2001	5.2.2001	6.2.2001	7.2.2001	8.2.2001	9.2.2001	10.2.2001	11.2.2001	12.2.2001	13.2.2001	14.2.2001	15.2.2001	16.2.2001	17.2.2001	18.2.2001	19.2.2001	20.2.2001	21.2.2001	22.2.2001	23.2.2001	24.2.2001	25.2.2001	26.2.2001	27.2.2001	28.2.2001
Температура, оС	-5	-7	-9	-6	-3					-1	-3	-7	-5	-2			-2	-12	-5	-4	-4	-1
Осадки	с	с	н	н	н	н	н	д	н	д	с	с	н	н	н	н	с	н	н	с	д	д	д	н	н	н	н	н
всего дней со снегом
всего дней с дождем
всего дней б осадков

 

 

Задание №3

1. В группе в конце семестра были проведены тесты по четырем предметам: экономике, математике, КИТ и русскому языку.

 

	A	B	C	D	E	F
			Результаты тестирования
		Предметы	Отметка о
	Фамилия	Математика	КИТ	Экономика	Русский язык	зачислении
	Кареев
	Воробьев
	Санина
	Осипов
	Полунин
	Шаров
	Андреева

2. По результатам тестирования проводится отбор для подготовки к олимпиаде. Зачисляются те, у кого общий балл не ниже 30, а суммарный балл по КИТ и экономике больше 16. В Отметке о зачислении должна быть запись да или нет.

3. В ячейку F5 внести формулу: =ЕСЛИ(И(СУММ(В5:Е5)>=30; C5 + D5 >16);” да”; ” нет”).

4. С помощью условного форматирования красным цветом отметить учащихся, прошедших отбор. Для этого:

1) выделить столбец F;

2) в главном меню выбрать пункт Формат, затем команду Условноеформатирование;

3) В диалоговом окне Условное форматирование в группе Условие 1 установить параметры в соответствии с образцом:

 

В этом же окне нажать кнопку Формат и установить цвет шрифта (красный).

Задание №3

1. Объявлен набор в школу моделей. Составлен список претендентов:

2. К претендентам предъявляются следующие требования:

для мужчин – рост не ниже 185 см, вес не более 75 кг;

для женщин – рост не ниже 175 см, вес не более 55 кг.

Набирается молодежь не старше 25 лет.

заполнить столбец Принят словами да, нет.

3. Выделить сиреневым цветом тех, кто принят в школу.

 

 

II. Финансовые функции используют для решения задач плани­рования финансовой деятельности, определения прибылей, анализа выгодности капиталовложений, кредитно-инвестиционной политики и т.п. Инвестицией называют вкладывание денег в некоторый бизнес на определенных условиях. Заем в банке называется кредитом, а взнос на банковский счет — депо­зитом. Поступление денег от бизнеса называют рентой.

Основные параметры финансовых функций и их сокра­щенные названия:

процентная ставка (ПС) выражается в процентах и может быть суточной, месячной, годовой и т.п.;

количество периодов (КП) продолжительностью сутки, месяц, год;

периодическая выплата (ПВ) — сумма, выплачиваемая клиентом на протяжении установленного периода (это отрицате­льное число), или сумма, получаемая клиентом на протяжении каждого периода (это положительное число);

сумма взноса (СВ) — сумма инвестиции, капиталовло­жения, начального взноса (это отрицательное число или ноль);

тип операции (Т) — число 0, если выплата осуществляется в конце каждого периода, и число 1, если в начале.

Различают кредитную и депозитную процентные ставки. Кредитная ставка выше депозитной. Процентная ставка должна быть согласована с продолжительностью периода, например, годовая ставка 60% равна месячной ставке 5%. В этой работе считается, что месячная депозитная ставка — 5%, а кредитная — 6%

 

1. Функция для определения будущей стоимости сегодняшних инвестиций. Функция имеет вид БС(ПС; КП; ПВ; СВ; Т).

Если параметр равен 0, его можно не указывать. Если пара­метр пропускают в середине списка параметров, то нужно за­писать два разделителя рядом (в этом случае;).

Задание №1

Инвестор вкладывает в бизнес 2 000 руб (или открывает на эту сумму счет в банке) на условиях 5% ставки прибыли ежемесячно. Какая стоимость инвестиции через 36 месяцев?

Решение задачи дает такая формула:

=БС(5%; 36;;-2000)

Ответ: 11 583,63 руб.

 

Задание № 2.

Клиент открывает счет в банке на условиях 5% ставки прибыли ежемесячно, кладет на счет 2 000 руб и планирует в начале каждого месяца забирать со счета 100 руб. Какая сумма будет на счету через 36 месяцев?

Решение: =БС(5%; 36; 100; -2000; 1)

Ответ: 1 520,82 руб.

 

Задание №3

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

=БС(5%; 36; -100; -2000; 1)

Ответ: 21 646,45 руб.

2. Функции для определения выплат по погашению займа.

Введем новые термины и их сокращенные названия:

· номер периода (НП);

· сумма займа (СЗ);

· конечное значение займа (КЗ).

Функция ПЛТ предназначена для определения суммы периодических выплат погашения долга и имеет вид ПЛТ(ПС; КП; СЗ; КЗ; Т). Такая выплата состоит из двух частей, вычисляемых с помощью двух функций ПРОЦПЛАТ и ОСПЛТ, а именно:

а) выплата по процентам ПРОЦПЛАТ(ПС; НП; КП; СЗ; КЗ; Т);

б) основная выплата ОСПЛТ(ПС; НП; КП; СЗ; КЗ; Т).

Выплата по процентам за каждый период уменьшается, а ос­новная выплата увеличивается, их сумма постоянна и равна ПЛТ.

 

Задание №4

Бизнесмен взял в банке кредит на сумму 2 000 руб сроком на 12 месяцев при условии ежемесячного погашения займа и месячной ставки кредита 6%. Определить величину ежемесячных выплат и ее составные в конце первого месяца.

= ПЛТ(6%; 12; 2000). Ответ: -238,55 руб.

= ПРОЦПЛАТ(6%; 1; 12; 2000). Ответ: -110,00 руб.

= ОСПЛТ6%; 1; 12; 2000). Ответ: -118,55 руб.

 

Рассмотрим функцию КПЕР, вычисляющую количество периодов для погашения суммы займа, предоставленной под некоторую процентную ставку при условии заранее заданной суммы периодических выплат: КПЕР(ПС; ПВ; СЗ; КЗ; Т).

 

Задание №5 (задача 6)

Заем 2 000 руб берут при условии возвращения в конце каждого месяца 200 руб и процентной ставки 6%. Сколько месяцев нужно для возвращения займа?

=КПЕР(6%; -200; 2000). Ответ: 15,73 месяца.

 

Функция СТАВКА(КП; ПВ; СЗ; КЗ; Т; начальное приближе­ние) определяет выгодность предоставления займа, т.е. реальную процентную ставку от предоставления займа на определенную сумму при условии фиксированных периодических выплат на протяжении нескольких периодов. Здесь нужно задать некоторое начальное приближение к искомой процентной ставке, например 0,1 (10%).

 

Задание №6 (задача 7)

Бизнесмен обращается в банк за займом (кредитом) на сумму 2 000 руб на 12 месяцев при условии пе­риодических выплат 200 руб в конце каждого месяца на про­тяжении года. Определить процентную ставку займа. =СТАВКА(12; -200; 2000; 0; 0; 0,1)

Ответ: 3%. Такой кредит для банка невыгодный, если ме­сячная депозитная процентная ставка, например, 5%. Банк кре­дит не даст.