Задания для самостоятельного выполнения

Лабораторная работа№2 по ИТУ

Решение задач оптимизации с помощью MS EXCEL

Практическая работа с инструментом ПОДБОР ПАРАМЕТРА

Средство Подбор параметра — простой, сохраняющий время и легкий в использовании инструмент, предназначенный для вычисления входного значения (параметра) некоторой формулы для того, чтобы формула возвращала требуемый результат. Средство Подбор параметра применяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающее желаемый результат вычислений.

Другими словами, это средство применяется в случае, если на рабочем листе имеется одна ячейка с числовым значением, а другая — с формулой, зависящей от значения в первой ячейке, и необходимо подобрать такое значение в первой ячейке, чтобы в ячейке с формулой получилось заданное вами значение.

Рассмотрим примеры.

Создайте рабочий лист, показанный на рисунке

На этом рабочем листе сумма кредита, срок погашения кредита (в месяцах) и годовая процентная ставка представлены в виде чисел, а ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Ставка; Кпер; Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В5/12), аргумент Кпер — количество периодов погашения кредита (ячейка В4), аргумент Пс — сумма кредита (ячейка ВЗ).

Пример 2: кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб. Какова максимальная сумма кредита?

 

1. В ячейку В4 введите число 180 (15 лет, умноженных на 12 месяцев). В ячейку В5 введите 5,75%.

2. Выберите вкладку Данные\Анализ «что-если»\Подбор параметра. Откроется диалоговое окно Подбор параметра.

3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.

4. В поле ввода Значение введите число -11000.

5. В поле ввода Изменяя значение ячейки введите ВЗ или щелкните на ячейке ВЗ.

6. Щелкните на кнопке ОК.

7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

8. Если все выполнено верно, должно получиться 1324646,72р.

 

Задания для самостоятельного выполнения

При выполнении заданий у Вас должна быть заготовка как на рисунке с введенной в ячейку В6 финансовой функцией ПЛТ и данными, соответствующими условию задачи. Открываете окно Подбор параметра, вводите данные и щелкаете по кнопке ОК. Получаете результат.

Задача 1: каков срок погашения кредита, если сумма кредита равна 2 250 000 руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?

 

Задача 2: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

Расчеты по депозитам

Создайте рабочий лист, показанный на рисунке

Рис. 2.

На этом рабочем листе начальная сумма депозита, срок хранения депозита (в месяцах) и годовая процентная ставка представлены в виде чисел, а конечная сумма депозита рассчитывается с помощью финансовой функции =БС(Ставка; Кпер;;—Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку депозита (поэтому в нашей формуле этот аргумент равен В20/12), аргумент Кпер — срок хранения депозита (ячейка В19), аргумент Пс — начальная сумма депозита (ячейка В18). Эта функция подобна функции ПЛТ из предыдущего примера, но вычисляет не ежемесячные платежи, а конечную накопленную сумму.

 

При выполнении заданий у Вас должна быть заготовка (см рис. 2) с введенной в ячейку В21 функцией БС и данными, соответствующими условию задачи. Открываете окно Подбор параметра, вводите данные и щелкаете по кнопке ОК. Получаете результат.

 

Синтаксис формулы =БС следующий: =БС(Ставка; Кпер;;—Пс;;)

Первое поле Ставка. Это годовая ставка, если процент высчитывается один раз в месяц, то делить придётся на 12.

Второе поле Кпер. Это количество периодов, отображающих нахождение в банке. В нашем случае это количество месяцев.

Третье поле Плт. Выплата, которую мы будем снимать каждый период. Но мы не будем ничего снимать, поэтому оставляем незатронутой или ставим 0.

Чётвертое поле Пс. Приведенная стоимость – та сумма, которую мы изначально инвестируем. Важный момент, т.к. мы её отдаём и для нашего кармана это временный минус, то и вводить нужно число отрицательное.

Пятое поле Тип. Заполнение этого поля имеет значение, в случае заполнения поля Плт. Если Плт не трогали, то и Тип можно оставить не тронутым. Есть только два варианта заполнения поля 0 или 1. Ноль вводиться при выплате в начале периода, а единица при выплате в конце периода.

Задача 3: депозит открывается на 3 года с процентной ставкой 12,75% годовых. Какова должна быть начальная сумма депозита, если конечная сумма должна быть порядка 150 000 руб.?

Задача 4: на какой срок необходимо открыть депозит, чтобы при процентной ставке 10,7% годовых из начальной суммы 125000 руб. получить конечную сумму 200 000 руб.?

Задача 5: депозит в размере 250 000 руб. открывается на 2 года с целью получить конечную сумму не менее 300 000 руб. Какая при таких условиях может быть минимальная процентная ставка?

 

Отчет выполнить в файле MS Word b и сохранить на сервере в личной папке, озаглавленной Вашей фамилией. В отчете приведите ответы на контрольные вопросы.

Контрольные вопросы:

1. Какую роль выполняет проектная диаграмма Ганта?

2. Приведите скриншоты построенной Вами диаграммы Ганта.

3. Для чего применяется средство Подбор параметра?

4. Как в MS Excel применить средство Подбор параметра?

5. Какую последовательность действий надо выполнить в открытом диалоговом окне Подбор параметра?

6. Ответы на решаемые задачи. Укажите номер задачи и полученный ответ.