Практикум по дисциплине
ИТ финансового планирования
Пример расчета финансовых показателей с использованием MSExcel.
Цель работы: Получение знаний и навыков по формулировке задачи финансового менеджмента и разработке моделей её решения.
В ходе работы решаются задачи:
- математическая формулировка из области профессиональной деятельности;
- выбор финансовой функции рабочего листа для автоматизации расчета;
- использование информационной технологии работы с табличным процессором для проведения финансовых расчетов.
Постановка задачи
Для покупки автомобиля берется кредит в банке в размере 500 000 тыс. руб. с банковским процентом 21% годовых, сроком на 7 лет. В течение второй половины периода кредитования выдается субсидия в размере 20% от суммы кредита, также происходит изменение банковского процента в 1,3 раза в первой половине периода кредитования. Рассчитать и отобразить в графическом виде:
- выплаты по отдельным годам (кварталам) при фиксированном банковском проценте;
- выплаты по отдельным годам (кварталам) с учетом полученной субсидии и увеличенном банковском проценте;
- построить графики выплат.
Разработка компьютерной модели решения задачи
Для расчета периодического платежа будем использовать финансовую функцию Excel ПЛТ.
Функция возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Синтаксис: ПЛТ(Cтавка;Кпер;Пс;Бс;Тип)
Ставка — процентная ставка по ссуде.
Кпер — общее число выплат по ссуде.
Пс — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение Бс равно 0.
Тип — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата. Аргумент равен 0 или опущен - в конце периода. Аргумент равен 1 - в начале периода.
Если Бс =0 и Тип =0, то функция ПЛТ рассчитывает по формуле
Где Р = Пс, i = Cтавка, n = Кпеp.
Рабочий лист Excel, разработанный для проведения расчета, изображен с формулами на рисунке 1.
Ячейка В7хранит количество ежегодных выплат. Так как в нашем примере выплаты производятся каждый квартал, то общее количество выплат будет равно 28.
Ячейка В12 осуществляет расчет размера периодической ежеквартальной выплаты.
Рисунок 1.
Ячейка В13 осуществляет расчет общей выплачиваемой суммы при ежеквартальной выплате.
Ячейка В14содержит расчет общей суммы комиссионных, получаемых банком при ежеквартальной выплате.
Результаты расчета отображены на рисунке 2.
Рисунок 2.
Из рисунка следует, что периодический ежеквартальный платеж равен 34,48 тыс. руб., сумма комиссионных равна 465,40 тыс. руб.
1. Составим график ежеквартальных платежей с расчетом основных платежей и платежей по процентам. Результаты отображены на рисунке 3.
Рисунок 3.
Общая сумма платежа =ПЛТ($C$3/4;$E$3*4;-$B$3;;0).
Платеж по процентам =ПРПЛТ($C$3/4;A7;$E$3*4;-$B$3). Функция ПРПЛТ возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.
Основные платежи =ОСПЛТ($C$3/4;A7;$E$3*4;-$B$3). Функция ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.
2. Составим график ежеквартальных платежей с расчетом основных платежей и платежей по процентам при условии, что через 2 года банковский процент увеличится в 1,3 раза. Результаты отображены на рисунке 4.
Формула для расчета общей суммы платежа после повышения процента измениться следующим образом =ПЛТ($F$7/4;5*4;-$F$9;;0).
Платеж по процентам станет равен =ПРПЛТ($F$7/4;A21-8;5*4;-$F$9).
Основные платежи =ОСПЛТ($F$7/4;A21-8;5*4;-$F$9).
Из рисунка 4 следует, что общая сумма платежей в данном случае увеличится на 93,91 тыс. руб. и составит 1059,31 тыс. руб., сумма комиссионных составит 559,31 тыс. руб.
3. Составим график ежеквартальных платежей с расчетом основных платежей и платежей по процентам при условии, что через 4 года мы воспользуемся субсидией для погашения долга в размере 20% от суммы кредита. Результаты отображены на рисунке 5.
Формула для расчета общей суммы платежа после применения субсидии измениться следующим образом =ПЛТ($C$3/4;3*4;-$E$9).
Платеж по процентам станет равен =ПРПЛТ($C$3/4;A30-16;3*4;-$E$9).
Основные платежи =ОСПЛТ(($C$3)/4;A30-16;3*4;-$E$9).
Из рисунка 5 следует, что общая сумма платежей в данном случае уменьшится на 16,7 тыс. руб. и составит 928,1 тыс. руб., сумма комиссионных составит 428,1 тыс. руб.
Рисунок 4.
Рис.5.
Вывод:
Вариант №3 предпочтительнее остальных, так как в этом случае сумма комиссионных составит 428,1 тыс. руб., что на 16,7 тыс. руб. меньше, чем в варианте 1 и на 131,21 тыс. руб. меньше, чем в варианте 2. Общие выплаты по кредиту в данном случае составят 928,1 тыс. руб.
Задания
Задача 1
Выпуск Продукта I рассчитан на 6 лет, начиная с 01.01.20ХХг. Объем выпуска продукта 100 шт. в месяц при стоимости 100 руб. за штуку. Выпуск Продукта 2 рассчитан на то же количество лет, составляет 140 шт. в месяц при цене 90 руб. за штуку.
В каком месяце выручка от продаж Продукта 1 превысит выручку от продаж Продукта 2, если увеличение цены единицы продукции за счет инфляции для Продукта 1 составит 30% годовых, а для Продукта 2 составит 20%? После решения задачи на основе полученных результатов должно быть принято решение о выборе одного из проектов при условии, что они альтернативные
Задача 2
Предприятие планирует сбыт продукции по цене 650 руб. в количестве 500 шт. в месяц. Начало проекта 01.01.20XXг., длительность 1 год. Общие издержки составляют 300000 шт. в месяц. Ставка дисконтирования — 10%. Расчет провести по месяцам.
Определить величину чистого приведенного денежного потока (NPV), сделать вывод о принятии или не принятии проекта.
Задача 3
Предприятие планирует сбыт продукции в количестве 1000 шт. по цене 150 руб. с 01.01.20XX в течение 1 года. Прямые издержки на производство продукции составляют 50 руб. за единицу продукции. Предприятие берет банковский кредит 100 000 руб. под 24% годовых. Дата поступления кредитной суммы 01.01.20XX, срок — 11 мес. Проценты за получение банковского кредита относить на прибыль. Масштаб установить по месяцам. Полный возврат кредита планируется к концу срока. База начисления процентов снижается с течением времени пропорционально сумме погашения основной суммы кредита.
Рассчитать сумму выплаты процентов по кредиту и погашения основного долга при условии ежемесячного погашения суммы основного долга.
Задача 4
Планируется выпуск продукции, объемы продаж которой подвержены сезонности и составляют ежемесячно 100 шт. по цене 10 руб. для каждого продукта.
Продукт 1: с января по июнь объемы падают на 5% ежемесячно, с июля по декабрь увеличиваются по 2%.
Продукт 2: с января по апрель объемы меньше максимального значения на 25%; май, июнь, июль — реализуется весь товар, начиная с августа и до конца года объемы снова падают на 25%.
Рассчитать, какой Продукт будет максимально реализован за каждый месяц и общее количество проданной продукции ежемесячно (в шт.).
Задача 5
Предприятие берет кредит в банке 100 000 руб. на месяц с отсрочкой выплаты по процентам на месяц (ставка 10% ежемесячно) и вкладывает в депозит 10 000 руб., приносящий 5%-й доход ежемесячно. Предприятие планирует сбыт продукции в количестве 100 шт. по цене 150 руб. Прямые издержки на производство продукции составляют 30 руб. за единицу продукции.
Рассчитать NPV и срок окупаемости инвестиций.
Задача 6
Начало проекта длительностью 3 года — 01.01.20ХХг. Выпуск продукта планируется осуществлять на трех производственных линиях мощностью 100 единиц в месяц каждая, пуск которых планируется соответственно в 1, 7 и 23 месяцах проекта. Продукция будет полностью продаваться в том же месяце, в котором она произведена, по цене 100 руб. за единицу при суммарных производственных издержках 90 руб. за единицу.
Без учета налогов рассчитать NPV проекта в рублях при условии дисконтирования денежных потоков раз в год по ставке 30% годовых,
Задача 7
Задача 8.
