Финансовая грамотность
материал

Практическая работа по теме: Подбор параметра. Организация обратного расчета в Excel

Цель занятия: Изучение технологии подбора параметра при обратных расчетах.

Ход работы:

Запустить Microsoft Excel и создайте в своей папке файл Подбор параметра_1.xlsx

ЗАДАЧА 1. КРЕДИТ НА КВАРТИРУ

1. Оформите на Листе1 таблицу как показано на рисунке:

2. Создайте две копии для Листа 1

3. Эти три листа назовите соответственно: Кредит_1, Кредит_2, Кредит_3

Задание 1. (подбор параметра для вычисления суммы кредита)

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

1. Введите новые данные на листе Кредит_1:

• в ячейку В4 – число 180 (15 лет, умноженных на 12 месяцев)
• в ячейку В5 – 5,75%
• перейдите на страницу Ленты – Данные

• В разделе Работа с данными разверните кнопку Анализ «что-если» и выберите команду Подбор параметра
• откроется диалоговое окно Подбор параметров в котором:
• в поле Установить в ячейке введите   В6 или щелкните на ячейке В6;
• в поле Значение введите число –11000 (число должно быть отрицательно, что указывает на то, что ежемесячный платеж заемщик отдает, а не получает)
• в поле Изменения значения ячеек введите В3 или щелкните по ячейке В3

Ответ: Максимальная сумма кредита 1 324 647 руб.

Задание 2. (подбор параметра для вычисления процентной ставки)

Кредит в размере 850 000 руб. берется на 30 лет с максимальными ежемесячными платежами 5000 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1. Перейдите на лист Кредит_2 и введите новые данные:

• в ячейку В3 – число 850000

• в ячейке В4 – 360

2. Выберите команду Подбор параметров (см. задание 1):

• в поле Установить в ячейке введите – В6 или щелкните на ячейке В6;
• в поле Значение введите число –5000
• в поле Изменения значения ячеек введите В5

Ответ: Можно согласиться на процентную ставку 5,82 %.

Задание 3. (подбор параметра для вычисления срока погашения кредита)

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

1. Перейдите на лист Кредит_3 и введите новые данные:

• в ячейку В3 число 2250000
• в ячейке В5 – 7 %
• выберите команду Подбор параметров (см. предыдущее задание)
• в поле Установить в ячейке введите – В6 или щелкните на ячейке В6;
• в поле Значение введите число –14230

2. В поле Изменения значения ячеек введите В4

Ответ: Срок погашения кредита 439 месяцев (примерно 36,6 лет)

ЗАДАЧА 2. ССУДА НА ПОКУПКУ МАШИНЫ

1. Оформите на свободном листе таблицу как показано на рисунке

Задание 1. (подбор параметра для вычисления размера ссуды)

Ссуда берется на 6 лет с процентной ставкой 2,9 % при условии, что сумма ежемесячных платежей не должна превышать 1395 руб. Каков максимальный размер ссуды?

1. Введите новые данные на листе Ссуда_1:

• в ячейку –  2,9 %
• в ячейку В7 – 72 (6лет умножить на 12 месяцев)

2. Выберите команду Подбор параметров

• в поле Установить в ячейке введите   В6 или щелкните на ячейке В6;
• в поле Значение введите число –1395
• в поле Изменения значения ячеек введите В3

Ответ: Максимальный размер ссуды 92085,41 руб.

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ

Задание 2. (подбор параметра для вычисления срока погашения ссуды)

Каков срок погашения ссуды, если сумма ссуды равна 180000 руб., процентная ставка 1,7% годовых, а ежемесячные платежи равны 3250 руб.?

1. Введите новые данные на листе Ссуда_2 и выполните подбор параметров самостоятельно:

Ответ (проверьте себя): ссуда берется на 58 месяцев.

Задание 3. (подбор параметра для вычисления процентной ставки)

Ссуда в размере 130000 руб. берется на 5 лет с максимальными ежемесячными платежами 2390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1. Введите новые данные на листе Ссуда_3 и выполните подбор параметров самостоятельно:

Ответ (проверьте себя): возможная процентная ставка 3,93%

2. Сохраните файл Подбор параметра_1.xlsx

3. Откройте файл Зарплата.xlsx

4. Сохраните файл под именем Подбор параметра_2.xlsx

5. На рабочем листе Зарплата октябрь используя режим подбора параметра, определить, при каком значении %Премии общая сумма заработной платы будет равна 250000 р.

Анализ результатов работы и формулировка выводов

В отчете необходимо предоставить: в своей папке файлы: Подбор параметра_1.xlsx, Подбор параметра_2.xlsx  

(с использованием материалов учителя экономики Присталова М.Ю.)

Задача 1 Выбор модели компьютера

Николай планирует купить ноутбук для работы, затратив при этом не более 85 000 руб. без учета стоимости доставки. Так как Николай в основном планирует заниматься компьютерной графикой и моделированием движения объектов, для него важны следующие технические параметры компьютера: мощная видеокарта с поддержкой технологии CUDA, процессор не ниже четырехъядерного Core i7 и не менее 16 ГБ оперативной памяти.

Задание:

1. Зайдите на сайты нескольких магазинов, продающих ноутбуки. Или можете воспользоваться любым сервисом-агрегатором.

2. Воспользуйтесь фильтрами и выберите параметры в соответствии с требованиями Николая.

3. Составьте и заполните таблицу с вариантами покупки (не менее 5 вариантов) следующего вида:

4. На основании полученных данных выберите наилучшее предложение. Обоснуйте выбор.

Задача 2 Калькулятор ОСАГО

Стоимость полиса ОСАГО рассчитывается на основе базового тарифа и страховых коэффициентов по следующей формуле:

Т = ТБ * КТ * КБМ * КО * КВС * КМ * КПр * КС

Базовая тарифная ставка является постоянной величиной, установленной на текущий год. Страховая компания имеет право изменять это значение не более чем на 20 % в большую или меньшую стороны. Остальные коэффициенты могут принимать различные значения, исходя из параметров автомобиля и характеристик водителя.

Задание:

В электронной таблице составьте модель, рассчитывающую стоимость страхового полиса ОСАГО, используя прилагаемые данные с размерами коэффициентов (файл электронной таблицы).

Задача3 (повышенного уровня сложности)

Поездка в отпуск (Сравнение вариантов по времени и цене)

Житель Санкт-Петербурга Денис планирует съездить в Таиланд. Он накопил денег на путешествие и рассматривает варианты покупки авиабилетов в столицу этой страны. Предельная сумма, которую Денис готов потратить на билет туда и обратно, составляет 40 000 рублей, но его цель — купить билеты как можно дешевле. При этом Денис не хочет тратить на дорогу туда и обратно в общей сложности больше 36 часов и, чтобы не испортить впечатления от поездки, на обратном пути он хочет провести в дороге как можно меньше времени. Варианты перелета с двумя и более пересадками он не рассматривает. Денис нашел в интернете следующие предложения по перелетам:

Задание

1. Используя электронные таблицы, выберите варианты, удовлетворяющие ограничениям.

2. Результат отсортируйте: а) в порядке возрастания цены;

б) в порядке возрастания общего времени перелета туда-обратно;

в) в порядке возрастания времени перелета обратно.

3. Какие варианты являются наиболее предпочтительными для Дениса, исходя из условий задачи?