где p – риски соответствующих банков, s – сумма вклада в данный
банк. Контрольные вопросы:
1. Каковы составляющие модели оптимизации?
2. Что такое целевая функция?
3. В чем различие простых и сложных ограничений?
4. Как рассчитывается средневзвешенное значение?
5. Какую дополнительную информацию выдает средство поиска решения?
Практическое занятие № 8.
Использование информационной технологии подбора параметра
Цель занятия: освоить методику применения средства подбора параметра для решения нелинейных однопараметрических задач с помощью Excel.
План занятия:
1. Изучить постановку задачи.
2. Создать на листе Excel таблицу с исходными данными. Ввести в таблицу необходимые формулы.
3. Определить процентную ставку с помощью подбора параметра. 4. Решить самостоятельно три нелинейные задачи.
Порядок выполнения работы
Решение задач с помощью команды «Подбор параметра»
Задача. Определить, при какой ежемесячной процентной ставке можно за год накопить 5 тыс. грн., внося каждый месяц платеж на 10% больше предыдущего, начав с первого платежа 100 грн.
С помощью одной лишь финансовой функции решить эту задачу нельзя, так как в ней слишком много неизвестных. Для начала необходимо смоделировать реальный поток платежей. Сформируйте таблицу, заполнив только колонки А, В, С, F.
В январе Платеж составляет 100 грн., а каждый следующий мы наращиваем на 10 % относительно предыдущего.
Найдем накопленную к концу года сумму, нарастив каждый платеж по предполагаемой ставке (предположим, ставка =0,12) на соответствующее число процентных периодов (первый платеж на 12 месяцев вперед, второй - на 11 и т.д). это можно сделать двумя способами:
вручную, т.е. будущая стоимость каждого платежа будет зависеть от того, сколько месяцев на этот платеж будут производиться начисления. Например, на январский платеж в 100 грн. начисления будут производиться 12 раз, в феврале 11раз и т.д. формула будет иметь вид: сумма*(1 ставка) числопериодов При помощи функции БЗ. Эта функция определена следующим образом:БЗ(ставка;кпер;плата;нз;тип)
Ставка - это процентная ставка за период.
Кпер - это общее число периодов выплат годовой ренты.
Плата - это выплата, производимая в каждый период;
Нз - общая сумма всех будущих платежей с настоящего момента.
Тип — это число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным 0.
Тип | Когда нужно платить |
0 | В конце периода |
1 | В начале периода |
Необходимо зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, предполагается вложить 1000 рублей под 6% годовых (что составит в месяц 6%/12 или 0,5%) и вкладывать по 100 рублей в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце 12 месяцев?
Перейдите на лист 2.
В любую ячейку занесите формулу БЗ и заполните параметры:
Процентная ставка – 0,5%.
Число периодов – 12.
Плата – (-100). Отрицательное число, т. к. поток платежей идет от нас.
Нз – (-1000).
Окончательно получаем: БЗ(0,5%; 12; -100; -1000; 1) равняется 2301,40 р.
Перейдите на лист 1.
Вычислите Будущуюстоимостькаждогоплатежа любым способом. Результат приведен ниже. Вычислите накопленную к концу года сумму.С помощью Подбора параметра найдем истинное значение процентной ставки.
Установите курсор в ячейку Е14. Выберите в меню Сервис вкладку Подборпараметра и установите следующие значения: Нажмите ОК. Просмотрите окно Результатподборапараметра.ОК.
В конечном итоге истинное значение процентной ставки равно 14, 72 %. Соответственно изменяются будущие стоимости каждого платежа при найденной процентной ставке и сумма к концу года равна 5 000 грн.
Найдите корни следующих уравнений. Значение первого приближения выберите из предлагаемого отрезка изоляции корня. Составьте на листе 3 таблицу, содержащую результаты вычислений (необходимо с помощью подбора параметра решить любые 2 уравнения из списка):№ | функция | Параметр | невязка |
1 | x3 0.2 x2 0.2 x 1.2 0 | Значение x | формула |
.. | ………………… | …………….. | ……………. |
Контрольные вопросы:
1. В каких случаях используется средство подбора параметра?
2. Как подготовить данные для использования подбора параметра?
3. Что такое абсолютная адресация ячеек?
4. Каковы особенности решения уравнений со многими корнями с помощью средства подбора параметра?
Практическая работа № 9.
Использование формул массивов для решения систем линейных уравнений
Цель занятия: Освоить методику применения формул массивов и изучить их особенности на примере решения системы линейных уравнений.
План занятия:
Изучить особенности применения формул массивов.Следуя указаниям, приведенным ниже, решить предлагаемую систему уравнений.
Решить самостоятельно три системы уравнений и проверить полученные значения корней.Порядок выполнения работы
Решим следующую систему линейных уравнений:
Занесем основную матрицу системы в ячейки А1-D4 листа1:2 | 1 | -5 | 1 |
1 | -3 | 0 | -6 |
0 | 2 | -1 | 2 |
1 | 4 | -7 | 6 |
0 |
9 |
-5 |
0 |
Полученные значения представляют корни исходной системы.
В качестве проверки подставьте эти значения в уравнения и определите невязки.Решите следующие системы уравнений (для самостоятельного решения выберите 2 любых системы):
7,9 x1 5.6 x2 5.7 x3 7.2 x4 6.688.5 x1 4.8 x2 0.8 x3 3.5 x4 9.95
4.3 x1 4.2 x2 3.2 x3 9.3 x4 8.6
1. 3.2 x1 1.4 x2 8.9 x3 3.3 x4 1
322. 42
x1 3 x2 2 x3 2 x5 0,5
3 x1 4 x2 5 x3 x4 3 x5 5.4
2 x1 5 x2 3 x3 2 x4 2 x5 5 x2 2 x3 5 x4 3 x5 7.5