Функции для анализа инвестиционных проектов

Теоретические сведения и рекомендации к выполнению заданий

Тема №3. Финансовые функции Excel

Цель: ознакомиться с встроенными финансовыми функциями Excel.

Финансовые вычисления включают в себя всю совокупность методов и расчетов, которые используются при принятии управленческих решений - от элементарных арифметических операций до сложных алгоритмов построения многокритериальные моделей, позволяющих получить оптимальные характеристики коммерческих сделок в зависимости от различных условий их проведения. На данный момент стандартный курс финансовых вычислений включает в себя следующие основные темы:

- Логика финансовых операций (временная ценность денег, операции наращивания и дисконтирования и т.п.);

- Простые проценты (операции наращивания и дисконтирования, налоги, инфляция, замена платежей); сложные проценты (эквивалентность ставок, операции с валютой и т.п.);

- Денежные потоки;

- Анализ эффективности инвестиционных проектов;

- Оценка финансовых активов.

Наличие такого широкого круга задач привело к тому, что программы расчета основных финансовых показателей были реализованы на уровне, доступном пользователю, который не обладает глубокими математическими знаниями. В Excel реализовано 15 встроенных и 37 дополнительных финансовых функций. В случае необходимости применения дополнительных финансовых функций необходимо установить надстройку Пакет анализа.
По типу решаемых задач все финансовые функции Excel можно разделить на следующие условные группы:

- Функции для анализа аннуитета и инвестиционных проектов;

- Функции для расчета амортизационных платежей;

- Функции для анализа ценных бумаг;

- Вспомогательные функции.

Функции каждой группы имеют практически одинаковый набор обязательных и дополнительных (необязательных) аргументов. В финансовой практике встречаются операции, которые характеризуются возникновением потоков платежей, которые распределены во времени. Потоки платежей, при которых выплаты денежных средств осуществляются равными суммами через одинаковые промежутки времени, называются обычным аннуитет. Такие потоки возникают при проведении кредитно-депозитных операций, формировании различных фондов, долгосрочной аренды, и т.д.
Количественный анализ таких операций сводится к вычислению следующих основных характеристик:

1) текущей величины потока платежей;

2) будущей величины потока платежей;

3) нормы доходности в виде процентной ставки;

4) числа периодов проведения операций.

К встроенным функциям для анализа аннуитетов и инвестиционных проектов относят: БС, ПО, КПЕР, НПЗ, ВНДОХ, МВСД, НОРМА, ОСНПЛАТ, ПЛПРОЦ, ППЛАТ, ПРОЦПЛАТ.
БС - возвращает будущее значение (стоимость) вклада на основе периодических постоянных платежей и постоянной процентной ставке.

Формат функции: БС (ставка, Кпер, Плт, Пс, Тип).

Ставка - это процентная ставка за период. Кпер - общее количество периодов выплат годовой ренты. Плт - это выплата, которая производится в каждый период; это значение не может изменяться на протяжении всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, не включая других налогов и сборов. Если аргумент отсутствует, то тогда должно быть указано значение аргумента Плт.
Пс - это текущая стоимость, или общая сумма всех будущих платежей с текущего момента. Если аргумент Плт отсутствует, то он считается равным 0. В таком случае должно быть указано значение аргумента Пс. Тип - это число 0 или 1, которое обозначает, когда должна производиться выплата.

Замечание:
1) нужно убедиться, что аргументы Ставка и Кпер используются в согласованных единицах измерения. Например, если проводятся помесячные платежи по четырехлетнему займу из расчета 19% годовых, то норма должна быть 19% / 12, а число_периодив - 4 * 12.
2) Все аргументы, которые записаны отрицательным числами; обозначают выплачиваемые деньги, получаемые - положительное число.

Пример 1. Клиент вносит в банк ежеквартально 1500 грн. под 19% годовых. Какая сумма будет накоплена за три года?

Решение представим в таблице Excel.

.

В строке формул прописаны параметры функции БС. Результат в ячейке В5.

Пример 2. Банк выдал кредит в сумме 75000грн. под 23% годовых сроком на три года с единовременным погашением в конце периода. Какую сумму нужно вернуть банку по истечении срока?

Решение. В строке формул прописаны параметры функции БС. Результат в ячейке Е5.

Пример 3. Фирма создает фонд, для этой цели вносит в банк 20000грн. единовременно под 16,5% годовых и затем ежемесячно пополняет вклад на 5000грн. Какая сумма будет накоплена за два года?

Решение. В строке формул прописаны параметры функции БС. Результат в ячейке Н6.

Варианты заданий по теме «Решение финансовых задач средствами Excel»

Цель работы: Использование возможностей финансовых функций Excel.

Варианты

Расчеты на основе постоянной процентной ставки (функция БC)

Вариант № 1.

Рассчитать, какая сумма окажется на счету, если 27000 грн. вложенные на 3 года под 13,5% годовых. Проценты начисляются каждые полгода.

Вариант № 2.

Допустим, есть два варианта инвестирования средств в течение 4-х лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 300000 грн. Определить, сколько денег окажется на счету в конце 4-го года для каждого варианта.
Вариант № 3.

Рассчитать, какая сумма будет на счету, если сумма в размере 5000 грн. вложена под 12% годовых на 3 года, а проценты начисляются каждые полгода.

Вариант № 4.

По вкладу в размере 2000 грн. начисляется 10% годовых. Рассчитать, какая сумма будет на сберегательном счету через 5 лет, если проценты начисляются ежемесячно.

Вариант № 5.

На сберегательный счет вносятся платежи по 200 грн. в начале каждого месяца. Рассчитайте, какая сумма окажется на счету через 4 года при процентной ставке 13,5% годовых.

Расчет периодических платежей (функции ППЛАТ, ПЛПРОЦ, ОСНПЛАТ, ОБЩПЛАТ)

Вариант № 6.

Определить размеры периодических взносов в фонд в размере 100000 грн., сформированный за два года ежемесячными платежами, если процентная ставка составляет 20% годовых.

Вариант № 7.

Какую сумму необходимо ежемесячно вносить на счет, чтобы через три года получить 10000 грн., если годовая процентная ставка 18,6%?

Вариант № 8.

Определить ежемесячные выплаты по займу в 10000 грн., который взят на семь месяцев под 9% годовых.

Вариант № 9.

Рассчитать размер ежегодных выплат 15-летней ипотечной ссуды в 800000грн. со ставкой 12% при начальном взносе 20% суммы.

Расчет платежей по процентам

Вариант № 10.

Определить платежи по процентах по пятилетнему займу размером 160 тис.грн., который выдан под 22% годовых, если проценты начисляются ежемесячно.

Вариант № 11.

Составить таблицу платежей по процентах за каждый год по пятилетнему займу размером 160 тис.грн., который выдан под 22% годовых, за двенадцатый месяц, если проценты начисляются ежемесячно.

Расчет основных платежей по займу

Вариант № 12.

Определить основные платежи по займу в 1 млн. грн. выданному на 3 года под 21% годовых, за третий год.

Расчет суммы платежей по процентам по займу

Вариант № 13.

Определить платежи по процентах по займу в 5 млн.грн, выданному на 2 года под 15% годовых, за второй год, если проценты начисляются ежемесячно.

Тема №4. Комплексное использование встроенных функций Excel (математические, статистические, финансовые, модуль «Поиск решения»)

Цель. Изучить действие и возможности использования некоторых встроенных функций Excel.

Комплексная задача №1

Рассмотрим комплексную задачу. Пусть имеются данные по продаже автомобилей в течение трех месяцев крупным фирмам. Часть покупок оплачено, на другую часть составлены договора на отсрочку оплаты. По имеющимся данным нужно провести анализ, а именно:

1. Определить количество и стоимость оплаченных и неоплаченных автомобилей.

2. Определить количество и стоимость автомобилей, проданных в каждом месяце.

3. Определить стоимость минимального и максимального заказов в каждом месяце.

4. Определить, какая марка автомобилей пользуется наибольшим спросом.

5. Сумму 5% от объема продаж положили в банк под 6% годовых. Вычислить прибыль от такого вложения, если счет закрыть через 1,2,3,4,5 лет.

6. Для закупки автомобилей в нескольких банках был взят кредит на разные сроки под разный процент. Определить сумму ежемесячных выплат по всем кредитам.

Как видим, ответить на некоторые из этих вопросов не так просто, для этого нужно определенные экономические знания. Однако использование встроенных математических, статистических и финансовых функций Excel дает возможность справиться с заданием без особых проблем.

1. Определим количество и стоимость оплаченных и неоплаченных автомобилей. Используем для этого встроенную математическую функцию СУММЕСЛИ – суммирование элементов диапазона по выбранному критерию. В строке формул прописано заполнение соответствующего диалогового окна.

2. Определим количество и стоимость автомобилей, проданных в каждом месяце. В качестве критерия выбора элементов суммирования используется название месяца.

3. Определим стоимость минимального и максимального заказов в каждом месяце. Для этого используем статистические функции МИН и МАКС.

4. Определим, какая марка автомобилей пользуется наибольшим спросом. Теперь уже понятно, что используем функцию СУММЕСЛИ, а в качестве критерия – марку автомобиля.

5. Сумму 5% от объема продаж положили в банк под 6% годовых. Вычислить прибыль от такого вложения, если счет закрыть через 1,2,3,4,5 лет.

Решение. 5% от суммы составит 573800*5% = 28690 д.е. Теперь используем финансовую функцию БС (будущая стоимость); прибыль рассчитываем как разность между полученной и вложенной суммой.

6. Для закупки автомобилей в нескольких банках был взят кредит на разные сроки под разный процент. Определить сумму ежемесячных выплат по всем кредитам.

В строке формул прописаны параметры встроенной финансовой функции ПЛТ.

Таким образом, ответили на все вопросы, поставленные в задаче.

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

Комплексная задача №2

Имеются данные по производству и доставке товара в магазины.

Молокозавод	Себестоимость изготовления 1л молока	Дневная выработка
А1	0,7
A2	0,65
A3	0,75

Магазин	B1	B2	B3	B4	B5
Дневной заказ
Цена продажи 1л., грн.	1,3	1,5	1,25	1,4	1,3

Молокозавод	Транспортные расходы на доставку 1л. молока в магазин, грн.
А1	А2	А3	А4	А5
А1	0,2	0,4	0,3	0,3	0,5
A2	0,4	0,2	0,5	0,3	0,4
A3	0,6	0,4	0,2	0,5	0,3

Требуется:

1. Составить оптимальный план доставки продукции с заводов в магазины, выбрав критерием оптимальности минимальную стоимость доставки без учета прибыли магазинов.
2. Составить оптимальный план доставки продукции с заводов в магазины, выбрав критерием оптимальности максимальную прибыль каждого магазина в отдельности и всех магазинов вместе без учета стоимости доставки.
3. Составить оптимальный план доставки продукции с заводов в магазины, выбрав критерием оптимальности максимальную прибыль всех магазинов вместе с учетом стоимости доставки.

Решение. Построим математическую модель задачи. Для этого приведем таблицу к стандартному виду транспортной задачи.

Обозначим переменные x₁₁, x₁₂, ……x₃₅, гдех_ij – количество литровых пакетов молока, поставляемое от поставщика і потребителю j.

Дання задача несбалансирована: , так как (5000+7000+3000=15000л.) ≥ (4000+1900+3300+3100+2000=14300л.). Система ограничений по спросу (количество доставленного товара соответствует спросу на этот товар):

х₁₁ + х₂₁ + х₃₁ = 4000

х₁₂ + х₂₂ + х₃₂ = 1900

х₁₃ + х₂₃ + х₃₃ = 3300

х₁₄ + х₂₄ + х₃₄ = 3100

х₁₅ + х₂₅ + х₃₅ = 2000

Система ограничений по предложению (поставщик не может поставить больше, чем требуется потребителю):

х₁₁ + х₁₂ + х₁₃ + х₁₄ + х₁₅ ≤ 5000

х₂₁ + х₂₂ + х₂₃ + х₂₄ + х₂₅ ≤ 7000

х₃₁ + х₃₂ + х₃₃ + х₃₄ + х₃₅ ≤ 3000

Цель: найти такие значения переменных x₁₁, x₁₂, ……x₃₅, чтобы полностью реализовать спрос (поскольку в данной задаче спрос меньше предложения) и при этом минимизировать расходы на доставку товара (доставить минимум целевой функции F(x)).

1. Составим оптимальный план доставки продукции с заводов в магазины, выбрав критерием оптимальности минимальную стоимость доставки без учета прибыли магазинов. Тогда целевая функция (суммарные затраты на доставку товара):

F(x) = 0,2 х₁₁ + 0,4 х₁₂ + 0,3 х₁₃ + 0,3 х₁₄ +0,5 х₁₅+

+0,4 х₂₁ +0,2 х₂₂ +0,5 х₂₃ + 0,3 х₂₄ + 0,4 х₂₅ +

+0,6 х₃₁ +0,4 х₃₂ +0,2 х₃₃ + 0,5 х₃₄ + 0,3 х₃₅ → min

По аналогии с решением традиционной транспортной задачи (см. лекцию 6), заполним

матрицу доставки (при изучении материала обращайте внимание на адреса ячеек и информацию в строке формул!)

Теперь можно заполнить модуль «Поиск решения».

Результат решения (оптимальный план поставки продукции):

Т.о., нужно поставить с молокозавода А 4000 пакетов в первый, 377 пакетов в третий и 623 пакета в четвертый магазин; с молокозавода В 1900 пакетов во второй, 2477 пакетов в четвертый и 1923 пакета в пятый магазин; с молокозавода С 2923 пакета в третий и 77 пакетов в пятый магазин. При этом расходы на доставку составят 3600грн. Прибыль магазинов от реализации продукции составит (СУММПРОИЗВ B19:F21;B25:F27) = 9270грн.

2. Составим оптимальный план доставки продукции с заводов в магазины, выбрав критерием оптимальности максимальную прибыль каждого магазина в отдельности и всех магазинов вместе без учета стоимости доставки.

Для этого составим матрицу прибыли (разность между стоимостью продажи и себестоимостью молока без учета стоимости доставки).

Замечание: обратите внимание на ссылки в строке формул! (расчеты проведены в ячейке В19).

Заполним «Поиск решения», установив значение целевой ячейки равной максимальному значению:

Целевая функция – суммарная прибыль от реализации продукции (G28 - max):

Результат расчета – максимально возможная прибыль 9340грн. При этом транспортные расходы составят (СУММПРОИЗВ B3:F5;B25:F27) = 3670грн.

3. Составим оптимальный план доставки продукции с заводов в магазины, выбрав критерием оптимальности максимальную прибыль всех магазинов вместе с учетом стоимости доставки. Для этого найдем разность между прибылью магазинов и затратами на доставку; полученное значение примем в качестве целевой функции.

Результат исполнения модуля «Поиск решения»:

Таким образом, варьируя условием задачи и исходными данными, можно принимать управленческое решение, максимизируя прибыль и минимизируя расходы по операциям.

Задача №1

1. На рабочем листе с именем «Телефоны» постройте таблицу, которая содержит сведения о продаже телефонов.

№ пор.	Изготовитель	Модель	Цена	Количество	Покупатель
	CONCORDE	C-1930			Телеком
	CONCORDE	C-2000 AO			Телеком
	CONCORDE	C-508M			Оргтехника
	CONCORDE	C-518			Телемарк
	GENERAL-ELECTRIC				Нота бене
	GENERAL-ELECTRIC				Нота бене
	GENERAL-ELECTRIC				Телемарк
	GENERAL-ELECTRIC				Нота бене
	GENERAL-ELECTRIC				Телемарк
	PANASONIC	KX-100			Телеком
	PANASONIC	KX-15			Нота бене
	PANASONIC	KX-17			Оргтехника
	PANASONIC	KX-27			Телемарк
	PANASONIC	KX-T2365			Нота бене
	PANASONIC	KX-TS5			Телеком

2. Для каждой марки телефонов определить:

а) минимальную и максимальную цену реализации;

б) минимальное и максимальное количество реализованных телефонов.

3. Для каждого покупателя определить количество приобретенных им моделей и общее количество товаров.

4. Для каждой марки телефонов вычислить среднюю цену и стандартное отклонение.

5. За данными таблицы вычислите суммы, которые будут накоплены на банковском счету через 5, 10, 15, 20 лет, если размер ежемесячных перерасчётов составляет 200, 500, 1000 у.е. Выплаты производятся вначале периода. Годовая процентная ставка – 11%.

6. В таблице приведены данные о получении предприятием кредитов и сроки их погашения по годам. Выплаты должны выполняться в конце каждого месяца. Вычислить размер выплат для каждого из кредитов

№ пор.	Размер кредита	Процентная ставка	Срок	ППЛАТ
		4%
		5%
		4%
		4%
		5%

Задача №2

1. Создайте таблицу с информацией о продаже принтеров в первом квартале текущего года.

№ пор.	Месяц	Название принтера	Покупатель	Количество	Стоимость, грн.	Примечание
	Январь	Minolta Page Pro	СЛОН			Не сплачено
	Январь	EPSON Stylus	Преспроф			Сплачено
	Февраль	HP Laser Jet	Сим-Сим			Сплачено
	Февраль	XEROX	СТБ			Не сплачено
	Февраль	HP Laser Jet	Квадро			Не сплачено
	Февраль	Minolta Page Pro	Спортпрогноз			Сплачено
	Март	EPSON Stylus	Вектраприват			Не сплачено
	Март	HP Laser Jet	Сим-Сим			Сплачено
	Март	HP Laser Jet	Фортуна			Сплачено
	Март	XEROX	Преспроф			Не сплачено

1. Вычислите количество и стоимость оплаченных и неоплаченных заказов, а также их стоимостную долю в общей сумме.
2. Определите количество и принтеров, проданных в каждом месяце.
3. Определите стоимость минимального и максимального заказов в каждом месяце.
4. Выясните, какой принтер пользуется наибольшим спросом, вычислив количество проданных принтеров каждого вида и их долю в общем количестве.
5. Сумму в размере 8% объема продаж в первом квартале положили в банк под 16% годовых. Вычислить прибыль от такого вложения, если счет закрыть через 1,2,3,4,5 лет.
6. Для закупки принтеров в нескольких банках взят кредит на разные сроки и под разные проценты. Вычислить размер ежемесячных выплат по всем кредитам.

№ банка	Размер кредита	Ставка	Срок	Выплата
		16,5%
		18%
		17,6%
		18,5%
		20%
Итого

Задача №3

Имеются данные по ассортименту товара, информация о текущем плане производства и имеющихся ресурсах, а также матрица расходов каждого вида ресурса на соответствующий вид товара.

Требуется:

1. Составить проектный план производства, максимизирующий прибыль от реализации всей продукции с учётом ограничений на запасы ресурсов.
2. Сравнить текущий и проектный планы.

Таблица «Товары»

№ пор.	Название товара (сока)	Прибыль от реализации 1 л товара, грн.	Текущий план производства	Проектный план производства
	Яблочный	1,70
	Апельсиновый	1,80
	Грейпфрутовый	1,65
	Апельсиново-грейпфрутовый	2,20
	Томатный	1,50
	Виноградный	1,60
	Мультивитамин 1	1,85
	Мультивитамин 2	1,90
	Мультивитамин 3	1,80

Таблица «Ресурсы»

№ пор.	Название ресурса	Запас ресурса	Единица измерения
	Человеческие ресурсы		час
	Яблоки		кг
	Апельсины		кг
	Грейпфруты		кг
	Виноград		кг
	Томаты		кг
	Лимоны		кг

Таблица «Технологические ограничения»

	Название товара - сок
Яблочный	Апельсиновый	Грейпфрутовый	Апельсиново-грейпфрутовый	Томатный	Виноградный	Мультивитамин 1	Мультивитамин 2	Мультивитамин 3
Название ресурса	Человеческие ресурсы	0,5	0,4	0,4	0,6	0,4	0,5	0,7	0,7	0,7
Яблоки								0,5	0,2
Апельсины		1,5		1,2				0,9	1,1
Грейпфруты			1,7	0,8			0,5	0,2	0,1
Виноград						1,6	0,2	0,5	0,7
Томаты					1,8
Лимоны							0,3	0,1	0,2

Таблица «Плановые расходы ресурсов»

№ пор.	Название ресурса	Текущие расходы	Проективные расходы
	Человеческие ресурсы
	Яблоки
	Апельсины
	Грейпфруты
	Виноград
	Томаты
	Лимоны

Примечание.

1. Внесите в столбец «Проектный план производства» предполагаемые значения неизвестных, подлежащих определению (количество сока каждого вида).

2. При вычислении текущих и проектных затрат используйте функцию МУМНОЖ (категория «Математические») (нужно перемножить матрицу затрат и столбец «Проектный план производства»).

3. Для получения оптимального плана используйте модуль «Поиск решения».

Дата добавления: 2014-11-08