Тема: Условны функции, условное форматирование
Лабораторная работа №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% . Банк кредит не даст.