Студентам ИТ / 3 ЛП_ИТ / ИТ_прогнозирования / ИТ_фин_произв_бизнесом_Excel

80,00
70,00
60,00
50,00												NPV А
40,00
												NPV В
30,00
20,00
10,00
0,00
0,00%	2,50%	5,00%	7,50%	10,00%	12,50%	15,00%	17,50%	20,00%	22,50%	25,00%	27,50%	30,00%
Рис. 2.1. Зависимости NPV проектов от дисконтной ставки.

Решите самостоятельно следующие задачи:

Задача 2.1.

Проект рассчитан на три года и требует начальных инвестиций в размере 10 млн. руб. и имеет предполагаемые денежные поступления в размере : 3 млн. руб., 4 млн. руб., 7 млн. руб. Рассчитать NPV в предположении ставки 10 %, 10,5%, 11%, …, 20%. Постройте график зависимости NPV от размера дисконтной ставки.

Задача 2.2.

Проект рассчитан на три года и требует начальных инвестиций в размере 10 млн. руб. и имеет предполагаемые денежные поступления в размере : 3 млн. руб., 4 млн. руб., 7 млн. руб. Определить IRR для этого проекта, если ставка дисконтирования равна 10 %.

Задача 2.3.

В конце 2000 года руководству одной из фирм предложили участвовать в строительстве и эксплуатации нового офиса в течение 6 лет. Строительство должно начаться 1 января 2001 и закончиться 31 декабря 2001 года. В 2001 году здание строилось, и прибыли не давало. Чистая прибыль от сдачи здания в аренду, а также процентные ставки государственных облигаций указаны в таблице. Рассчитать приведенную к начальному моменту стоимость (PV) проекта, если платежи и поступления имеют место в конце каждого года. Рассчитать чистую приведенную стоимость проекта, если фирме предложили 33% участие в строительстве за 450 тыс. долл.

Год	Арендные		Затраты,	тыс.	Чистая прибыль,	Процентная
	платежи,	тыс.	долл.		тыс. долл	ставка
	долл
2002	325		200		125	6,00
2003	425		250		175	6,25
2004	525		300		225	6,50
2005	525		300		225	6,75
2006	525		325		200	7,00

11

Лабораторная работа № 3

Оптимизационные методы в инвестициях: анализ пределов безопасности инвестиций, оптимизация инвестиционного портфеля.

Анализ пределов безопасности инвестиций.

Математическая оптимизация представляет собой процесс нахождения экстремума (максимума или минимума) некоторой определенной функции при заданных ограничениях (условная оптимизация) или без ограничений (безусловная оптимизация). Методы решения подообных задач осуществляются в рамках специального научного направления - математического программирования. Microsoft Excel представляет пользователю специальное средство – Поиск решения (меню Сервис) для решения задач математического программирования.

Поиск решения позволяет анализировать задачи трех типов:

-линейные (все зависимости между переменными задачами линейны);

-нелинейные (между переменными задачи существует хотя бы одна непропорциональная зависимость);

-целочисленные (результаты решения должны быть целыми числами).

Говоря «языком» электронных таблиц, Поиск решения удобно использовать в тех случаях, когда необходимо найти оптимальное или заданное значение для отдельной ячейки путем подбора значений других ячеек с учетом требуемых ограничений.

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

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

Изменяемые ячейки (искомые переменные) – ячейки , значения которых будут изменяться до тех пор, пока не будет найдено решение. Возможно задать до 200 изменяемых ячеек, которые могут содержать как формулы, так и ссылки на блок, либо несмежные ячейки. Несмежные ячейки должны разделяться точкой с запятой.

Внимание ! При сохранении полученного решения формулы в изменяемых ячейках будут заменены постоянными (найденными) значениями.

Ограничение – значение ячейки, которое должно находиться в определенных пределах или удовлетворять целевым критериям. Ограничения могут налагаться как на целевую, так и на изменяемые ячейки. Для одной модели могут быть определены по два ограничения для каждой изменяемой ячейки (верхний и нижний пределы), а также до 100 дополнительных. Как правило, ограничения накладываются путем использования операторов сравнения: <= (меньше или равно), >= (больше или равно), =. Ограничения целочисленности применяют в случаях, когда используемая в задаче величина или искомый результат должны принимать одно из двух значений – «Да» или «Нет», 0 или 1, либо когда дробные значения результатов недопустимы (например, при расчете объектов инвестиций, служащих, машин, станков и т.п.).

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

Количество выполняемых при поиске решений итераций, точность решения и необходимое время можно изменять через опцию Параметры диалогового окна Поиск решения.

После завершения поиска решения Microsoft Excel предлагает три варианта продолжения работы:

-сохранить полученное решение или восстановить исходные значения на рабочем листе;

-сохранить полученное решение в виде именованного сценария;

-просмотреть один из встроенных отчетов о ходе решения.

12

Задача 3.1: определить допустимую величину ошибок оценки значений потока платежей (CF), при которых обеспечивается безубыточность операций (т.е. нулевое значение NPV).

1. Подготовьте шаблон согласно таблице 3.1 и задайте формулы для вычислений согласно таблице 3.2.

В этой таблице в ячейках С10 С16 вводятся значения ошибок, которые первоначально (по умолчанию) равны 0. Ячейки блока D10.D16 содержат значения потока платежей, скорректированные на величины ошибок (базовая формула для формирования этого блока должна быть задана в ячейке D10 и скопирована требуемое число раз). Поскольку по умолчанию величины ошибок равны 0, значения скорректированного потока платежей первоначально совпадают с исходными.

Таблица 3.1

	A	B	С	D
1	Анализ пределов безопасности инвестиций
2
3	Ставка дисконта, d =	0,1
4	Срок реализации, n =	6
5	Ставка реинвест., r =	0,1
6
7		CF*	Ошибки	CF
	Даты платежа:	(плановый):	(относительные):	(скорректированный):
8
9	25.01.95	-100000,00 р.	0,00	-100000,00 р.
10	25.01.96	25000,00 р.	0,00	25000,00 р.
11	25.01.97	30000,00 р.	0,00	30000,00 р.
12	25.01.98	35000,00 р.	0,00	35000,00 р.
13	25.01.99	40000,00 р.	0,00	40000,00 р.
14	25.01.00	45000,00 р.	0,00	45000,00 р.
15	25.01.01	50000,00 р.	0,00	50000,00 р.
16
17	NPV =	57273,71 р.	0,00	57273,71 р.
18	PI =	1,57		1,57
19	IRR =
20	MIRR =	19 %		19 %

		Таблица 3.2
Ячейка		Формула
C18	=СУММ(C11:C16)/B5
D10 D16	=B10*(1 – С10)	=B16*(1 – С16)
B18	=ЧИСТНЗ($B$4;B10:B16;A10:A16)
B19	=-B18/B10+1
B20	=МВСД(B10:B16;$B$4;$B$6)
D18	=ЧИСТНЗ($B$4;D10:D16;A10:A16)
D19	=-D18/D10+1
D20	=МВСД(D10:D16;$B$4;$B$6)

2. В качестве целевой используйте ячейку D18, содержащую формулу для вычисления NPV. Ее величина зависит от значений потока платежей (блока ячеек D10.D16). В результате решения задачи величина NPV должна стать равной 0.

13

Вкачестве ограничений используйте условие D10 >= -100000 и D10 <= - 90000, что соответствует положительности и фиксированной минимально-возможной величине инвестируемой суммы (ошибка выделенного кредита не более 10%).

Вкачестве изменяемых используйте ячейки С10 С16, выделяя их с помощью левой кнопки мыши.

3. Выберите в главном меню тему Сервис, опцию Поиск решения и заполните поля появившегося окна диалога (см.рис.3.1). Для ввода ограничений нажмите кнопку Добавить и введите все ограничения. Завершение ввода ограничений подтвердите нажатием кнопки ОК (см. рис.2). После нажатия кнопки Выполнить Поиск решения на экране появится окно

Результаты поиска решения. Нажмите кнопку ОК. В полученной таблице в ячейках С10
С16 изменятся значения.
Поиск решения				?
Установить целевую ячейку:		$D$18		Выполнить
Равной:	максимальному значению		значению: 0
	минимальному значению			Закрыть
Изменяя ячейки:
$C$10:$С$16			Предположить
Ограничения:				Параметры
$C$10:$С$16 >=0			Добавить
$D$9 <= - 90 000			Изменить
$D$9 >= $B$10
			Удалить	Восстановить
				Справка
	Рис. 3.1 Окно Поиск решения.
Добавить ограничения				?
Ссылка на ячейку:		Ограничение:
$C$10:$С$16		>=	0
ОК	Отмена		Добавить	Справка
	Рис. 3.2. Добавить ограничения.

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

36%.

Оптимизация инвестиционного портфеля.

Пример. Имеется шесть проектов для потенциального инвестирования. На каждый проект инвестор планирует выделить 80000 р., 60000 р., 70000 р., 100000 р., 40000 р., 110000 р.

Планируемые чистые современные стоимости проектов составляют 15000 р., 19000 р., 42000 р.,

14

45000 р., 12000 р., 16000 р. соответственно. Требуется определить набор (портфель) проектов, при котором суммарные инвестиции I не превышают ограниченного бюджета инвестора в

250000 р.

Для решения задачи необходимо подготовить шаблон рабочего листа Excel, как показано в таблице 3.3.

Для решения задачи выберите в главном меню тему Сервис, опцию Поиск решения и заполните поля диалога появившегося «висящего» окна Поиск решения, как показано на рис.

3.3.

Обратите внимание на то, что в окне Ограничения: введены ограничения целочисленности на параметры xm, что соответствует инвестированию (xm = 1) или не инвестированию (xm = 0) проекта.

							Таблица 3.3
	A	B	С	D	E		F
1	Отбор инвестиционных проектов в условиях ограниченного бюджета
2
3		Коэфф-ты	Коэфф-нты
	Сп-ок	NPVm	Im		Ц		О
	проек	Целевого	крит-рия	xm
	-тов:	крит-рия	огран-ния О
		Ц
4
5	A	15 000р.	80 000р.	0	=B5*D5		=C5*D5
6	B	19 000р.	60 000р.	0	=B6*D6		=C6*D6
7	C	42 000р.	70 000р.	0	=B7*D7		=C7*D7
8	D	45 000р.	100 000р.	0	=B8*D8		=C8*D8
9	E	12 000р.	40 000р.	0	=B9*D9		=C9*D9
10	F	16 500р.	110 000р.	0	=B10*D10		=106*D10
11
12		Бюджет =	250 000р			I =	=CУММ(F5:F10)
13				NPV =	=CУММ(E5:E10)

Поиск решения				?
Установить целевую ячейку:		$E$13		Выполнить
Равной:	максимальному значению		значению:	0
	минимальному значению			Закрыть
Изменяя ячейки:
$D$5:$D$10			Предположить
Ограничения:				Параметры
$D$5:$D$10 <=1			Добавить
$D$5:$D$10 = целое			Изменить
$D$5:$D$10 >= 0
$F$12 <= $C$12			Удалить	Восстановить
				Справка
		Рис. 3.3. Окно Поиск Решения.

15

Полученное в результате решение показано в таблице 3.4.

Согласно полученному решению, оптимальный портфель должен состоять из проектов B, C и D. Суммарная величина NPV при этом составит NPV =106 000 р., а суммарная величина инвестиций I = 230 000 р., т.е. почти равна бюджету инвестора.

										Таблица 3.4
	A	B		С		D		E		F
5	A		15 000р.		80 000р.		0		0,00р.	0,00р.
6	B		19 000р.		60 000р.		1		19 000р.	60 000р.
7	C		42 000р.		70 000р.		1		42 000р.	70 000р.
8	D		45 000р.		100 000р.		1		45 000р.	100 000р.
9	E		12 000р.		40 000р.		0		0,00р.	0,00р.
10	F		16 500р.		110 000р.		0		0,00р.	0,00р.
11
12			Бюджет =		250 000р.				I =	230 000р.
13						NPV =			106 000р.

Лабораторная работа № 4

Решение оптимизационных задач управления

Ресурсное планирование объема выпускаемой продукции. Цех предприятия производит два вида продукции (Продукт_1 и Продукт_2). Следует рассчитать оптимальные недельные объемы Q1 и Q2 производства этих продуктов с точки зрения максимизации прибыли. Прибыль (целевая функция PROF) от каждого продукта составляет: PROF1 = 5 ден. единиц, PROF2 = 5,5 ден. единиц.

На производстве действуют ограничения b1 – по сырью, b2 – трудовым ресурсам и b3 – транспортным расходам:

–для Продукта_1 требуется a11 = 3 единицы сырья, для Продукта_2 требуется a12 = 6 единицы сырья. Всего цех располагает b1 = 18 единицами сырья;

–для изготовления Продукта_1 требуется a21 = 6 рабочих, для Продукта_2 требуется a22 = 4 рабочих. В цехе всего b2 = 24 рабочих;

–транспортные расходы на перевозку Продукта_1 составляют a31 = 2 единицы, а Продукта_2 – a32 = 1 единицу. Эти затраты не могут быть менее b3 = 2 единиц (цена аренды одного автомобиля минимальной грузоподъемности в течение дня). Считается, что вся дневная продукция цеха может быть вывезена на одном грузовике.

Кроме того, очевидно, что ни одна из переменных (число единиц продукции) не может

быть менее нуля. Q1 , Q2 0.

Отсюда запишем соотношения, из которых можно вычислить оптимальные объемы

производства Продукта_1				и Продукта_2:
3 * Q1 + 6		* Q2	18		– потребность в сырье,
6 * Q1	+ 4	* Q2	24		– трудовые ресурсы,
2 * Q1	+ 1	* Q2	2		– транспортные расходы,
Q1	0, Q2 0				– условие неотрицательности,
PROF	= 5 * Q1		+ 5,5 * Q2		max – целевая функция.

Создайте шаблон для решения поставленной задачи:

Ограничения вносятся в верхнюю часть таблицы 4.1. Коэффициенты отношений – в область C2:D4, правая часть уравнений – в F2:F4. Коэффициенты целевой функции – в C6:D6. В процессе расчетов в области Е2:Е4 отображаются вычисляемые (фактические) значения правой части неравенств. Сюда вводятся формулы:

E2=СУММПРОИЗВ(C$7:D$7;C2:D2),

16

E3=СУММПРОИЗВ(C$7:D$7;C3:D3),

E4=СУММПРОИЗВ(C$7:D$7;C4:D4).

							Таблица 4.1
		A	B	C	D	E	F
	1	№	Вид ресурса	Продукт 1	Продукт 2	Вычисленные	Заданные
						значения	ограничения
	2	1	Сырье	3	6	18,0	18
	3	2	Труд	6	4	24,0	24
	4	3	Транспорт	2	1	7,5	2
	5					Прибыль:
	6		Целевая	5,00	5,50	23,25
			функция
	7		Результаты	3,00	1,50

Аналогично значение целевой функции (прибыль) равно

E6=СУММПРОИЗВ(C$7:D$7;C6:D6).

Если размерность системы уравнений (как в нашем случае) невелика, можно воспользоваться более простыми функциями (таблица 4.2):

		Таблица 4.2
		E
	1	Вычисленные
		значения
	2	=C2*С$7+D2*D$7
	3	=C3*С$7+D3*D$7
	4	=C4*С$7+D4*D$7
	5	Прибыль:
	6	=C6*С$7+D2*D$7

E2=C2*С$7+D2*D$7, E4=C4*С$7+D4*D$7,

E3=C3*С$7+D3*D$7, E6=C6*С$7+D2*D$7.

Результат (оптимальное количество Продукта 1 и Продукта 2) формируется в области С7:D7. Клетки, в которых вычисляются какие-то значения, выделены жирным шрифтом. Остальное – исходные данные.

Для оптимизации в Excel используется инструмент Поиск решения, вызываемый через меню Сервис, который предъявляет окно (рис. 4.1). Сначала задается ячейка, содержащая оптимизируемое значение (здесь Е2), затем указывается его желаемое значение (у нас максимальное). Можно задать не только максимальное/минимальное значения, но и любую произвольную величину, введя ее в специальное поле (Равной значению:).

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

После ввода всех ограничений и других условий, следует нажать кнопку Выполнить для решения поставленной задачи.

Если вычисления оказались успешными, Excel предъявит окно итогов, аналогичное окну рис. 4.1. Их можно сохранить или отказаться (Восстановить исходные значения). Сохраним их. Кроме того, можно получить один из трех видов отчетов (Результаты, Устойчивость, Пределы), позволяющие лучше осознать полученные результаты, в том числе, оценить их достоверность.

17

Поиск решения				?
Установить целевую ячейку:		$E$6		Выполнить
Равной:	максимальному значению		значению: 0
	минимальному значению			Закрыть
Изменяя ячейки:
$C$7:$D$7		Предположить
Ограничения:				Параметры
$C$7:$D$7 >=0		Добавить
$E$2 <= $F$2		Изменить
$E$3 <= $F$3
				Восстановить
$E$4 >= $F$4		Удалить
				Справка
			Рис.4.1.
Добавить ограничения				?
Ссылка на ячейку:		Ограничение:
$C$7:$D$7		>=	0
ОК	Отмена		Добавить	Справка

Рис. 4.2.

Планирование транспортных перевозок.

Пусть с трех складов требуется развести грузы в объемах 50, 30 и 40 тонн потребителям в 2 пункта доставки в объеме 40 и 80 тонн (таблица 4.3). Известна цена перевозки единицы груза с каждого склада в каждый пункт доставки (столбцы С и Е). Наша задача заключается в следующем: определить такие объемы перевозок со складов в пункты доставки, чтобы стоимость транспортировки была минимальной. В таблице 4.3 искомые значения уже вычислены и обведены жирной рамкой. Видим, что стоимость всего «мероприятия» составляет

1300 руб.

						Таблица 4.3
	A	B	C	D	E	F
1			Пункт доставки 1		Пункт доставки 2
	Наличие груза		Цена	Объем	Цена	Объем
2	на складах		перевозки	груза	перевозки	груза
3	Склад 1	50	10	6,40	5	43,60
4	Склад 2	30	20	30,00	25	0,00
5	Склад 3	40	15	3,60	10	36,40
6	ВСЕГО	120		40,00		80,00
7
8	Целевая функция:		1300,00 р

Рассмотрим, как были получены эти значения. Прежде всего, в ячейку С8 заносим целевую функцию. Здесь это стоимость всех «элементарных» перевозок, вычисляемая как

18

сумма произведений цены на объем груза

С8=C3*D3+E3*F3+C4*D4+E4*F4+C5*D5+E5*F5.

Для решения снова используем инструмент Поиск решения, где введем следующие параметры

Установить целевую ячейку C8 равной минимальному значению.

Изменяя ячейки:	D3:D5;F3:F5.
Ограничения:
B3=D3+F3	–	объем вывоза с	1-го склада,
B4=D4+F4	–	объем вывоза со	2-го склада,
B5=D5+F5	–	объем вывоза с	3-го склада,
D6=D3+D4+D5	–	объем доставки в 1-й пункт,
F6=F3+F4+F5	–	объем доставки в 2-й пункт,
F3:F5>=0	–	условие положительности стоимости,
D3:D5>=0	–	условие положительности стоимости.

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

D3:D5=целое и F3:F5=целое.

Результаты в этом случае будут таковы: в первый пункт доставки направляются грузы в объеме 7т, 30т, 3т с каждого из трех складов соответственно, во второй – 43т, 0т, 37т. Значение целевой функции осталось как и прежде – 1300 руб.

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

компании. В таблице 4.4 отражены наличные деньги, которые могут быть получены на вложение одного доллара. Например, проект А – инвестиции, которые можно сделать в начале первого года на два следующих года, причем в конце этого же года можно возвратить 30 центов на вложенный доллар, а в конце следующего года можно дополнительно получить еще 1 долл. Максимальная сумма, которая может быть вложена в проект составляет 500 000 долл. Проект В аналогичен проекту А, но вложение денег можно сделать только в конце следующего года и т.д. Деньги, полученные в результате инвестиций можно реинвестировать в соответствии с предложенной схемой. В дополнение к этому компания может получать по 6 % годовых за краткосрочный вклад всех денег, которые не были вложены в инвестиции в данном году. Всего у компании имеется 1000 000 долл. для инвестиций. Сформировать инвестиционный портфель таким образом, чтобы прибыль была максимальна.

					Таблица 4.4
Год	Эффективность инвестиционного проекта на один вкладываемый доллар
	А	В	С	Д	Е
Первый	-1, 00	0	-1,00	-1,00	0
Второй	+0,30	-1,00	+1,10	0	0
Третий	+1,00	+0,30	0	0	-1,00
Четвертый	0	+1,00	0	+1,75	+1,40

Введем обозначения: a1, b2, c1, d1, e3 – инвестиции в проекты A, B, C, D, E соответственно, индексы указывают первый, второй, третий годы вложения инвестиций. Обозначим s1, s2, s3 – суммы, которые можно вложить в банк на короткий срок под 6% годовых соответственно в первом, втором, третьем годах.

Соотношения, из которых можно вычислить оптимальные величины вложений: а) в первом году во все проекты должна быть вложена сумма 1000 000 долл.:

19

1000 000 – (a1 + c1 + d1 + s1)= 0;

б) баланс на второй год:

0,3 a1 - b2+ 1,1 c1 + 1,06 s1 - s2 = 0;

в) баланс на третий год:

a1 + 0,3 b2 - e3 + 1,06 s2 - s3 = 0;

с) максимальный доход к четвертому периоду:

b2 + 1,75 d1 + 1,4 e3 + 1,06 s3 → max;

д) в проект А за первый год не может быть вложено более 500 000 долл.: a1≤500 000;

е) необходимое условие неотрицательности переменных: a1, b2, c1, d1, e3, s1, s2, s3 ≥ 0.

1.Создайте шаблон для решения поставленной задачи.

2.Найдите оптимальные значения a1, b2, c1, d1, e3, s1, s2, s3, используя инструмент Поиск решения.

Результаты решение задачи и образец оформления представлены на рис.4.3.

Рис.4.3.

Лабораторная работа № 5

Имитационное моделирование состояний производственных и коммерческих операций (управление состоянием и структурой бизнеса).

Задача. Моделируется реальная ситуация производственных или коммерческих процессов

втечение года в условиях инфляции. Пусть начальная себестоимость единицы продукции (переменные затраты) 10 000 р. снижалась на 1 %, а ее начальная рыночная цена 11 000 р. повышалась на 4,5 % в месяц. В качестве других структурных параметров бизнеса выбраны: коэффициенты налога на прибыль – 0,22 % и НДС –20 %, коэффициент реинвестиции прибыли

впроизводство 52 % с ежемесячным снижением на 1 %. Запланированные начальные постоянные затраты в 50 000 р. повышались на 10 % в месяц. При этом необходимо учесть равномерно распределенные помехи – колебания постоянных затрат при различных уровнях (амплитудах) помех.

Управление в виде собственных и заемных средств составляло соответственно 2 * 50 000, 00 р. + 2 * 70 000, 00 р. в течение первых двух месяцев. Необходимо определить вероятный период окупаемости бизнеса по критерию чистой приведенной стоимости при различных уровнях помех. Чистая приведенная стоимость рассчитывается при норме дисконтирования в 10 %. Необходимо также определить минимально допустимую величину коэффициента реинвестиции. Тем самым, в задаче необходимо не только провести имитационное моделирование состояний бизнеса, но и промоделировать его структурную (в данном случае параметрическую) перестройку.

Решение.

Оформите шаблон, фрагмент которого (для двух периодов состояний - январь, февраль) показан в таблице 5.1, присвоив книге имя Бизнес, а листу Excel имя Состояния. Шаблон должен быть оформлен по декабрь месяц включительно.

20