---

ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ПРОФЕССИОНАЛЬНОЕ
ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ НОВОСИБИРСКОЙ ОБЛАСТИ
«НОВОСИБИРСКИЙ ПРОМЫШЛЕННО-ЭНЕРГЕТИЧЕСКИЙ КОЛЛЕДЖ»
ПРАКТИЧЕСКАЯ РАБОТА №37
«Финансовое моделирование в Excel: Кредитный калькулятор,
логические функции, подбор параметра, точка безубыточности»
Дата: «___» __________ 2026 г.
Преподаватель: _________________________
2026
ЗАДАНИЕ 1. Кредитный калькулятор
Подпишите лист «Кредит». Исходные данные, создайте таблицу и
внесите данные:
Таблица 1 - исходные данные
Вариант Сумма кредита Ставка Срок (год)
А 500 000 12% 3
Б 500 000 11% 5
В 650 000 13% 5
Добавьте в таблицу столбцы:
1. «Срок (мес.)» - рассчитывается как срок кредита в годах *12.
2. «Месячная ставка» - рассчитывается как годовая ставка, деленная на
12.
3. Ежемесячный платёж. Рассчитать используя формулу:
ПЛТ(ставка; кпер; пс; [бс]; [тип])
Ставка - Обязательный аргумент. Процентная ставка по ссуде.
Кпер - Обязательный аргумент. Общее число выплат по ссуде.
Пс - Обязательный аргумент. Приведенная к текущему моменту
стоимость или общая сумма, которая на текущий момент равноценна ряду
будущих платежей, называемая также основной суммой.
Бс - Необязательный. Значение будущей стоимости, то есть желаемого
остатка средств после последней выплаты. Если аргумент "бс" опущен,
предполагается значение 0 (например, значение будущей стоимости для займа
равно 0). Данный параметр в работе не будем использовать.
Тип - Необязательный аргумент. Число 0 (нуль) или 1, обозначающее,
когда должна производиться выплата. Данный параметр в работе не будем
использовать.
4. Общая сумма выплат.
5. Переплата по кредиту.
Определить:
• В каком варианте самый маленький платёж;
• В каком варианте самая маленькая переплата.
6. Определите, на сколько рублей отличается переплата между самым
выгодным и самым невыгодным вариантом кредита.
7. Сделать вывод, какой вариант кредита выгоднее и почему?
ЗАДАНИЕ 2. Логические функции для расчёта бонусов
Добавьте новый лист, под названием «Бонусы». Менеджер получает
бонус по правилам:
• 10% от продаж, если план выполнен И нет жалоб клиентов;
• 5%, если план выполнен ИЛИ стаж > 2 года;
• 0% в остальных случаях;
• План 100 000.
Таблица 2 - исходные данные
Менеджер Продажи Жалобы Стаж (лет)
Иванов 120 000 0 1,5
Петрова 95 000 0 3
Сидоров 110 000 2 2,5
Орлова 145 000 0 4
Кузнецов 135 000 1 5
1. Добавьте столбец «Бонус (%)» с формулой:
=ЕСЛИ(И(B2>=100000;C2=0);10%;ЕСЛИ(ИЛИ(B2>=100000;D2>2);5%;0%))
2. Добавьте столбец «Сумма бонуса»: =B2*E2
3. Добавьте столбец «Премия за высокие показатели». Если продажи
более 130000 руб., жалобы отсутствуют и стаж превышает 2 года, сотруднику
начисляется дополнительная премия 3000 руб. В противном случае премия
равна 0 руб.
4. Определите сотрудника с максимальной суммой бонусных выплат.
5. Используйте условное форматирование. Зеленый – лучший результат.
Красный – отсутствие бонуса.
6. Проанализируйте: почему Петрова получила бонус, несмотря на
невыполнение плана?
ЗАДАНИЕ 3. Подбор параметра: «Какой платёж я могу себе
позволить?»
Молодой специалист получает зарплату 55000 руб. Банк рекомендует
тратить на кредит не более 35% дохода. Для всех расчётов использовать
годовую процентную ставку 12%.
Требуется:
1. Определить максимальный ежемесячный платёж.
2. С помощью подбора параметра найти максимально возможную сумму
кредита. Для подбора выделите необходимую ячейку, откройте вкладку
Данные → Анализ «что-если» → Подбор параметра.
3. Проверить результат для сроков:
• 3 года;
• 5 лет;
• 7 лет.
Таблица 3 - данные для расчёта
Срок (лет) Максимальная сумма кредита
3 ?
5 ?
7 ?
4. Как изменение срока влияет на доступную сумму кредита?
ЗАДАНИЕ 4. Модель точки безубыточности
Таблица 4 - исходные данные
Параметр Значение
Постоянные затраты (мес.) 200000
Цена за единицу 1500
Переменные затраты на
единицу
900
Прибыль = Выручка − Общие затраты
Создайте лист «Безубыточность». Рассчитайте точку безубыточности в
единицах. В ячейку A1 добавьте текст «Точка безубыточности», в ячейку B9:
=B2/(B3-B4).
1. Рассчитать прибыль для объемов:
• 200;
• 300;
• 400;
• 500 единиц.
2. Добавьте столбцы:
• Объем
• Выручка
• Затраты
• Прибыль
3. Постройте график: две линии — «Выручка» и «Общие затраты».
Отметьте точку пересечения.
4. Рассмотрите три сценария, если цена:
• Базовая 1500.
• Акция 1300.
• Премиум 1700.
5. Определить:
• где точка безубыточности минимальна;
• какой вариант наиболее выгоден.