Задача 1 Работа с финансовыми функциями .

Задание 1.1. Какую сумму необходимо положить на депозит под 16,5% годовых, чтобы получить через три года 44 млн. грн. при полугодовом начислении процентов?

Определение текущей стоимости

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

П3 (норма; Кпер; выплата; бс; тип),

где:

- норма- процентная ставка за один период;

- Кпер - общее число периодов выплат;

- выплата- это выплата, производимая в каждый периода

- бс - будущая стоимость вклада, которую нужно достичь после последней выплаты, если аргумент бс опущен, то он полагается равным 0;

- тшп - это число 0 или 1, обозначающее, когда производится выплата (1 - в начале периода, 0 - в конце периода), если аргумент тип опущен, то он полагается равным 0. Параметр mип нужно указывать, только если выплата не равна 0, т.е. делаются взносы по периодам.

Решение

Для расчета используем функцию ПЗ.

При этом норма = 16,5%, Кпер =3*2, выплата = 4400 тыс грн., бс = 0.

П3(16,5;3;4400;) ⁼ -2735 т.грн.

Результаты решения задачи представлены в таблице 1. Динамика роста стоимости показана на диаграмме 2. Таблица 3 содержит расчетные формулы к решению задачи в пакете Microsoft Excel.

Таблица 1

Текущая стоимость

	A	B	C	D	E	F	G
1	ЗАДАНИЕ №1.1
2	год	ставка	число периодов	выплата	вклад	тип	величина вклада
3	1	16,5%	2	0	4400	0	-3 755
4	2	16,5%	4	0	4400	0	-3 204
5	3	16,5%	6	0	4400	0	-2 735

Диаграмма 2

Таблица 3

Текущая стоимость

	A	B	C	D	E	F	G
1	ЗАДАНИЕ №1.1
2	год	ставка	число периодов	выплата	вклад	тип	величина вклада
3	1	0,165	=2*A3	0	4400	0	=ПЗ(B3/2;C3;D3;E3;F3)
4	2	0,165	=2*A4	0	4400	0	=ПЗ(B4/2;C4;D4;E4;F4)
5	3	0,165	=2*A5	0	4400	0	=ПЗ(B5/2;C5;D5;E5;F5)

Задание 1.2. Определение будущей стоимости

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

Будущая стоимость текущего значения вклада при постоянной процентной ставке рассчитывается с помощью функции:

Б3(норма; число_периодов; выплата; нз; тип),

где:

- норма - процентная ставка за один период;

- число _периодов - общее число периодов выплат;

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

- нз - текущая стоимость вклада (настоящее значение), если аргумент нз опущен, то он полагается равным 0;

- тип - это число 0 или 1, обозначающее, когда производится выплата (1 - в начале периода, 0 - в конце периода). Если аргумент тип опущен ,то он полагается равным 0. Параметр тип нужно указывать только тогда, когда выплата не равна 0, т.е. делаются взносы по периодам.

Решение:

Для расчета применяется формула БЗ, т.к. требуется найти будущее значение выплаченной суммы. В данной задаче при ежемесячном начислении процентов общее число периодов начисления равно 6 (аргумент число_периодов), а процент за период начисления равен 10%/12 (аргумент норма). По условию аргумент нз = -200. Это отрицательна сумма, т.к. деньги были вложены. Выплаты отсутствую, поэтому аргумент выплата отсутствует. Используя функцию БЗ, получим

Б3 (10%/12;6;;-200) = 1 447 грн.

Результаты решения задачи представлены в таблице 4. Динамика роста стоимости показана в диаграмме 5. Таблица 6 содержит расчетные формулы к решению задачи в пакете Microsoft Excel.

Таблица 4

Расчет будущей стоимости

	A	B	C	D	E	F	G
23	ЗАДАЧА 1.2
24	год	ставка	число периодов	выплата	вклад	тип	величина вклада
25	1	0,008333	1	-200	0	0	200
26	2	0,008333	2	-200	0	0	402
27	3	0,008333	3	-200	0	0	605
28	4	0,008333	4	-200	0	0	810
29	5	0,008333	5	-200	0	0	1 017
30	6	0,008333	6	-200	0	0	1 225
31	7	0,008333	7	-200	0	0	1 435

Диаграмма 5

Таблица 6

Расчет будущей стоимости

	A	B	C	D	E	F	G
1	ЗАДАЧА 1.2
2	год	ставка	число периодов	выплата	вклад	тип	величина вклада
3	1	=0,1/12	=A25	-200	0	0	=БЗ(B25;C25;D25;E25;F25)
4	2	=0,1/12	=A26	-200	0	0	=БЗ(B26;C26;D26;E26;F26)
5	3	=0,1/12	=A27	-200	0	0	=БЗ(B27;C27;D27;E27;F27)
6	4	=0,1/12	=A28	-200	0	0	=БЗ(B28;C28;D28;E28;F28)
7	5	=0,1/12	=A29	-200	0	0	=БЗ(B29;C29;D29;E29;F29)
8		=0,1/12	=A30	-200	0	0	=БЗ(B30;C30;D30;E30;F30)
96	7	=0,1/12	=A31	-200	0	0	=БЗ(B31;C31;D31;E31;F31)

Задача 2 Построение экономической модели вида y=f(x)

Произвести экономический анализ для заданных статистических данных. Сделать выбор.

Цель экономического регрессионного анализа - на основе собранных статистических данных, которые представлены в виде таблицы, найти экономическую модель и произвести на основе этой модели соответствующие экономические расчеты и прогнозы.

Для построения корреляционного поля необходимо выполнить следующие действия:

1. Открыть рабочее окно EXCEL и ввести значения данных х и у.

2. Построить точечную диаграмму.

3. Выполнить пункты меню Диаграмма - Добавить линию тренда. На вкладке Тип выбрать тип диаграммы, (линейная, логарифмическая, полиноминальная, степенная, экспоненциальная).

4. Обратить внимание на то, что в различных вариантах зависимость может быть любого из перечисленных видов. Далее выбрать вкладку Параметры и поставить маркер в окне Показать уравнение на диаграмме.

5. Сделать вывод о виде принятой гипотезы.



X	1,21	1,3	1,56	1,9	2,19	2,35	2,61	3,01	3,16	3,53
Y	75,4	95,8	216,5	599,7	1424,7	2309,7	5029,9	16697,7	26190,4	79471

Решение

Выполняем построение точечной диаграммы и добавляем линию тренда с различными типами диаграммы:

- линейная - логарифмическая

- полиноминальная - степенная



- экспоненциальная

Вывод: проанализировав величину коэффициента достоверности аппроксимации R² для каждого типа зависимости можно сделать вывод, что исходные экономические данные можно аппроксимировать с наибольшей точностью экспоненциальной зависимостью y = 1,9746e^3,0046x, так как R² = 1.

Задача 3. МОДЕЛЬ ЛЕОНТЬЕВА МНОГООТРАСЛЕВОЙ ЭКОНОМИКИ (БАЛАНСОВЫЙ АНАЛИЗ)

Связь между тремя отраслями представлена матрицей прямых затрат А. Спрос (конечный продукт) задан вектором Y. Найти валовой выпуск продукции отраслей Х. Описать используемые формулы, представить распечатку со значениями и формулами.

Работа с матрицами s пакете Excel

В пакете Excel существует несколько функций для работы с матрицами:

1. ТРАНСП- транспонирование матрицы;

2. МОПРЕД- нахождение определителя матрицы;

3. МУМНОЖ-умножение матриц;

4. МОБР-нахождение обратной матрицы.

Все эти функции (кроме ТРАНСП) находятся в категории "Математические", функция ТРАНСП - в категории "Ссылки и массивы".

Для работы с матрицами необходимо сделать следующее:

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

2 Выбрать Вставка функции, найти нужную функцию.

3 Ввести адрес (или адреса) исходной матрицы (непосредственно или курсором). Нажать кнопку "ОК".

Для того, чтобы получить на экране все значения результата, нажать клавиши F2 и одновременно Ctrl+Shift+Enter.

Решение:

1. Вводим исходные данные в ячейки пакета Excel. Матрицу прямых затрат А вводим в ячейки (B2:D4), матрицу спроса в ячейки (G2:G4).

2. Определим матрицу прямых затрат . Вначале найдем матрицу (Е-А).

где Е - единичная матрица, .

Вводим в ячейки (B6:D8) единичную матрицу. Матрицу (Е-А) посчитаем в ячейках (B13:D15) по формуле

.

3. Для вычисления обратной матрицы, сначала вычислим определитель. Для этого выставляем курсор в ячейку, где будет определитель (G14), вызываем Вставку функции, в категории «Математические» выбираем функцию нахождения определителя матрицы МОПРЕД, вводим адрес матрицы МОПРЕД (В13:D15) и нажимаем «ОК». В ячейке G14 появляется значение определителя матрицы.

4. Для нахождения обратной матрицы используем математическую функцию МОБР. Обратную матрицу находим функцией МОБР. Для этого выделяем блок ячеек, где должна находится обратная матрица (B17:D19), вызываем Вставку функции, в категории «Математические» выбираем функцию нахождения обратной матрицы МОБР, вводим адрес матрицы MOBP(B13:D15), нажимаем «ОК». Для получения на экране значения коэффициентов обратной матрицы, нажимаем клавиша F2 и Ctrl+Shift+Enter одновременно.

5. Вектор валового выпуска определяется по формуле

,

Находим вектор решений системы уравнений умножением обратной матрицы на вектор-столбец , используя встроенную математическую функцию МУМНОЖ .

Для этого выделяем блок, где будет находится вектор - (G17:G19). Вызываем Вставку функции в категории "Математические", выбираем функцию МУМНОЖ, вводим адрес обратной матрицы (B17:D19) и вектора Y (G2:G4): МУМНОЖ(B17:D19;G2:G4), нажимаем «ОК» Для получения на экране значения решения, нажимаем клавиша F2 и Ctri+Shift+Enter одновременно.

В результате решения было определено, что для удовлетворения спроса необходимо произвести продукции в1-й, 2-й и 3-й отраслях на 100, 100 и 90 д.е. соответственно.

Затраты (отрасли)	Выпуск(потребление)	Конечный продукт	Валовой выпуск
	1	2	3
1	0,2	0,1	0,4	66	150
2	0,1	0,1	0,3	81	140
3	0,3	0,15	0,2	14	100

	A	B	C	D	E	F	G
1	РАСЧЕТ ВАЛОВОГО ВЫПУСКА ПРОДУКЦИИ
2		0,2	0,1	0,4			66
3	А=	0,1	0,1	0,3		Y=	81
4		0,3	0,15	0,2			14
5
6		1	0	0
7	Е=	0	1	0
8		0	0	1
9
10
11	Решение задачи
12
13		0,8	-0,1	-0,4
14	E-A=	-0,1	0,9	-0,3		D=	0,409
15		-0,3	-0,15	0,8
16
17		1,650367	0,342298	0,953545			150
18	E-A (-1)=	0,415648	1,271394	0,684597		(E-A)(-1)*Y=	140
19		0,696822	0,366748	1,735941			100
20

	A	B	C	D	E	F	G
1	РАСЧЕТ ВАЛОВОГО ВЫПУСКА ПРОДУКЦИИ
2		0,2	0,1	0,4			66
3	А=	0,1	0,1	0,3		Y=	81
4		0,3	0,15	0,2			14
5
6		1	0	0
7	Е=	0	1	0
8		0	0	1
9
10
11	Решение задачи
12
13		=B6-B2	=C6-C2	=D6-D2
14	E-A=	=B7-B3	=C7-C3	=D7-D3		D=	=МОПРЕД(B13:D15)
15		=B8-B4	=C8-C4	=D8-D4
16
17		=МОБР(B13:D15)	=МОБР(B13:D15)	=МОБР(B13:D15)			=МУМНОЖ(B17:D19;G2:G4)
18	E-A (-1)=	=МОБР(B13:D15)	=МОБР(B13:D15)	=МОБР(B13:D15)		(E-A)(-1)*Y=	=МУМНОЖ(B17:D19;G2:G4)
19		=МОБР(B13:D15)	=МОБР(B13:D15)	=МОБР(B13:D15)			=МУМНОЖ(B17:D19;G2:G4)

ЗАДАЧА 4

Хлебопекарный цех выпекает два вида хлеба -А и В. На производство 1 т. хлеба А потребуется 700 кг муки; хлеба В - 820 кг. Расход рабочего времени основного оборудования цеха на 1 т. хлеба А и в соответствуют 1,2 и 2,2 ч. Цех располагает запасом муки в кол-ве 14340 кг. Резерв рабочего времени оборудования - 36,1 ч. Прибыль от реализации одной тонны хлеба А - 22д.е. , хлеба В -30 д.е. Спланировать работу цеха так, чтобы прибыль была максимальной, если выпуск хлеба В должен быть не менее 12 т.

Решение:

1. Формализация задачи

Обозначим: выпуск хлеба А через x₁; выпуск хлеба В через x_2;

Тогда целевая функция - прибыль от реализации - равна:

z=22x₁+30x₂

На выпуск 1 т. хлеба А требуется 700 кг муки; хлеба В - 820 кг, общий выпуск не превысит 14340 кг

700x₁+820x2?14340

Расход рабочего времени соответственно 1,2 и 2,2 часа на каждый вид продукции. Фонд рабочего времени- 36,1 ч.

1,2x₁+2,2x₂?36,1

Так как минимальное количество хлеба В должно быть не менее 12 т., то справедливо: x₁?0, x₂?12

Полученная математическая модель задачи о смесях:

z=22x1+30x2(max)	x1?0
0,7x1+0,82x2?14,34	x2?12
1,2x1+2,2x2?36,1

2. Графическое решение формализованной задачи

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

2. Находим градиент функции z: grad z = {22;30}. Строим вектор с началом в т. (0;0) и концом в точке (22;30).

3.Строим прямую, перпендикулярную вектору градиента. Передвигаем эту прямую в направлении, указанном вектором. Самая последняя прямая которую пересекает прямая, и есть точка максимума.

3. Решение задачи с помощью пакета Excel

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

Прежде, чем воспользоваться этой программой, введем исходные данные:

1. В ячейки C3 и D3 вводим значения точки максимума соответственно

2. Вводим коэффициенты целевой функции 50 и 60 в ячейки C6 и D6 соответственно.

3. В ячейку F6 вводим формулу для вычисления целевой функции. Для этого вызываем Вставка функции - «Математические» - СУММПРОИЗВ и вводим ячейки C$3:D$3 и C6:D6. Формат функции; =СУММПРОИЗВ(С$3:0$3;С6:D6).

4. В ячейки C4:D4 вводим нижние границы равные 0. Нижняя граница показывает, что переменные не отрицательные.

5. Вводим коэффициенты системы ограничений в ячейки C10:D11.

6. Вводим правые части системы ограничений в ячейки Н10:Н11.

7. В ячейку F10 вводим формулу расчета выполнения ограничений =СУММПРОИЗВ(С$3:D$3;C10:DО). Копируем эту формулу в ячейки F10, F11.

8. В ячейку I10 вводим формулу расчета неиспользованных ресурсов =H10-F10. Копируем эту формулу в ячейки I10, I11

После ввода исходных данных вызываем программу Поиск решения из пункта меню Сервис.

В окно Поиска решения вводим значения в ячейках:

1. Вводим $F$6 в окно «Установить целевую ячейку», выставляем ее «Равной минимальному значению».

2. В окошко «Изменяя ячейки» вводим $C$3:$D$3.

3. В окошке «Ограничения» выбираем пункт «Добавить»

«Ссылка на ячейку» - СЗ, знак - >=, «Ограничение» - С4. Появляется ограничение:

$С$3>=$С$4. Аналогично вводим:

$D$3>=$D$4;

$F$10<=$H$10;

$F$11<=$H$11;

5. После этого нажимаем «Выполнить», далее Тип отчета -«Результаты». Получаем решение в ячейках СЗ и D3 - значения переменных, в ячейках F6 - значение целевой функции, в ячейках F10:F11 - значения ограничений к в ячейках I10:I11 - разницу между исходными ресурсами и использованными.

4. Точное (алгебраическое) решение формализованной задачи

Решается система двух уравнений с двумя неизвестными.

{	0,7x1+0,82x2=14,34 [1]
	1,2x1+2,2x2=36,1 [2]

из [1] x₂=14,34-0,7x₁/0,82; из [2] x₂=36,1-1,2x₁/2,2,

приравниваем полученные выражения,

170-0,4x₁/0,3=330-0,6x₁/0,7;

x₁=3,5; x₂=14,5

	A	B	C	D	E	F	G	H	I
1			Переменные
2			X1	X2
3		Значения	3,5	14,5
4		Ниж.граница	0	12
5		Верхн.граница
6		F	22	30		512	max
7		Коэффициенты целевой ф-ции
8
9			Коэф-ты			Значение		Факт.ресурсы	Неисп.ресурсы
10	Сис-ма ограничений	0,7	0,82		1,4340	<=	14,340	0
11			1,2	2,2		36,1	<=	36,1	0

	A	B	C	D	E	F	G	H	I
1			Переменные
2			X1	X2
3		Значения	3,5	14,5
4		Ниж.граница	0	12
5		Верхн.граница
6		F	22	30		=СУММПРОИЗВ(C3:D3;C6:D6)	max
7		Коэффициенты целевой ф-ции
8
9			Коэф-ты			Значение		Факт.ресурсы	Неисп.ресурсы
10	Сис-ма огранич		0,7	0,82		=СУММПРОИЗВ(C3:D3;C10:D10)	<=	14,340	=H10-F10
11			1,2	2,2		=СУММПРОИЗВ(C3:D3;C11:D11)	<=	36,1	=H11-F11

Экономический вывод.

Максимальная прибыль равна 512 д.е. и достигается при выпуске :

- хлеба А в количестве 3,5 т.

- хлеба В в количестве 14,6 т.

При этом неиспользованные ресурсы равны 0