Финансово-экономические расчеты в Excel

Министерство образования и науки Украины

Донбасская государственная машиностроительная академия

Кафедра прикладной математики

Контрольная работа

по дисциплине «Информатика»

2007

Задание 1 задача 20.2

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

 

Решение

 

	A	B	C	D	E	F	G
1	РАСЧЕТ ТЕКУЩЕГО ВКЛАДА
2	ГОД	СТАВКА	ЧИСЛО	ВЫПЛАТА	ВКЛАД, тыс. грн	ТИП	ВЕЛИЧИНА
3		(ГОД)	ПЕРИОДОВ				ВКЛАДА, тыс. грн
4	1	0,105	=A4	0	-1000	1	=БС (B4; C4; D4; E4; F4)
5	2	0,105	=A5	0	-1000	1	=БС (B5; C5; D5; E5; F5)
6	3	0,105	=A6	0	-1000	1	=БС (B6; C6; D6; E6; F6)
7	4	0,105	=A7	0	-1000	1	=БС (B7; C7; D7; E7; F7)
8	5	0,105	=A8	0	-1000	1	=БС (B8; C8; D8; E8; F8)
9	6	0,105	=A9	0	-1000	1	=БС (B9; C9; D9; E9; F9)
10	7	0,105	=A10	0	-1000	1	=БС (B10; C10; D10; E10; F10)
11	8	0,105	=A11	0	-1000	1	=БС (B11; C11; D11; E11; F11)

Для расчета текущей стоимости вклада будем использовать функцию БЗ (норма; число_периодов; выплата; нз; тип), где норма – процентная ставка за один период. В нашем случае величина нормы составляет 10,5% годовых. Число периодов– общее число периодов выплат. В нашем случае данная величина составляет 8 лет. Выплата – выплата, производимая в каждый период. В нашем случае данная величина полагается равной -1000. НЗ – текущая стоимость вклада. Равна 0. Тип – данный аргумент равен 1 так как выплаты производятся в начале года.

Получим следующее выражение БЗ (10,5%; 8; 0; – 1000; 1) = 2222,79 тыс. грн.

Расчет будущей стоимости вклада по годам приведен в таблице.

Таблица – Расчет будущего вклада

	A	B	C	D	E	F	G
1	РАСЧЕТ ТЕКУЩЕГО ВКЛАДА
2	ГОД	СТАВКА	ЧИСЛО	ВЫПЛАТА	ВКЛАД, тыс. грн	ТИП	ВЕЛИЧИНА
3		(ГОД)	ПЕРИОДОВ				ВКЛАДА, тыс. грн
4	1	0,105	1	0	-1000	1	1105,00
5	2	0,105	2	0	-1000	1	1221,03
6	3	0,105	3	0	-1000	1	1349,23
7	4	0,105	4	0	-1000	1	1490,90
8	5	0,105	5	0	-1000	1	1647,45
9	6	0,105	6	0	-1000	1	1820,43
10	7	0,105	7	0	-1000	1	2011,57
11	8	0,105	8	0	-1000	1	2222,79

Гистограмма, отражающая динамику роста вклада по годам представлена ниже.

Рисунок 1 – Динамика роста вклада по годам

Вывод: Расчеты показывают, что на счете через 8 лет будет 2222,79 тыс. грн.

 

Задание 1 задача 20.1

Рассчитайте текущую стоимость вклада, который через 7 лет составит 50 000 грн при ставке процента 9% годовых.

Решение

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

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

где норма = 9% – процентная ставка за один период;

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

выплата = 0 – Ежегодные платежи;

бс = 50 000 – будущая стоимость

При этом:

ПС (9%; 6; 50000) = -29813,37 тыс. грн.

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

РАСЧЕТ ТЕКУЩЕЙ СТОИМОСТИ
ГОД	СТАВКА	ЧИСЛО	ТИП	Текущая стоимость, тыс. грн
	(ГОД)	ПЕРИОДОВ
1	9%	6	0	-29813,37
2	9%	5	0	-32496,57
3	9%	4	0	-35421,26
4	9%	3	0	-38609,17
5	9%	2	0	-42084,00
6	9%	1	0	-45871,56
7	9%	0	0	-50000,00

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

	A	B	C	D		E
1	РАСЧЕТ ТЕКУЩЕЙ СТОИМОСТИ
2	ГОД	СТАВКА	ЧИСЛО	ТИП	Текущая стоимость, тыс. грн
3		(ГОД)	ПЕРИОДОВ
4	1	0,09	6	0	=ПС (B4; C4; 50000; E4)
5	2	0,09	5	0	=ПС (B5; C5; 50000; E5)
6	3	0,09	4	0	=ПС (B6; C6; 50000; E6)
7	4	0,09	3	0	=ПС (B7; C7; 50000; E7)
8	5	0,09	2	0	=ПС (B8; C8; 50000; E8)
9	6	0,09	1	0	=ПС (B9; C9; 50000; E9)
10	7	0,09	0	0	=ПС (B10; C10; 50000; E10)

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

Вывод: Таким образом при заданных условиях текущая стоимость вклада составляет 29813,37 тыс. грн.

Задание 2 вариант 4

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

Х	1,08	1,53	2,05	2,58	3,02	3,58	4,06	4,56	5,01	5,51
Y	1,04	4,09	6,39	6,15	6,18	5,42	6,53	8,04	12,3	19,3

 

Решение

1.  Вводим значения X и Y, оформляя таблицу;

2.  По данным таблицы строим точечную диаграмму;

3.  Выполнив пункты меню Диаграмма – Добавить линию тренда, получаем линию тренда;

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

y =0,8836x2 – 3,008x + 6,0631 – уравнение зависимости;

R² = 0.8102 – величина достоверности аппроксимации;

Вывод: На основе собранных статистических данных, находим экономическую модель – принятая гипотеза имеет полиномиальную зависимость и выражается уравнением

y = 0,8836x2 – 3,008x + 6,0631

R2 = 0,8102

Экономическое прогнозирование на основе уравнения данной зависимости отличается достоверностью в области начальных значений параметра X – величина ε принимает малые значения и неточностью в долгосрочном периоде – в области конечных значений параметра X.

Задание 3. вариант 17

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

	Выпуск(потребление)					Решение
	Первой отрасли	Второй отрасли	Третьей отрасли		Конечный продукт	Валовой выпуск
	0,05	0,1	0,3		50	100,00
A=	0,1	0,1	0,3	Y=	65	120,00
	0,3	0,25	0,2		28	110,00

Решение

Данная задача связана с определением объема производства каждой из N отраслей, чтобы удовлетворить все потребности в продукции данной отрасли. При этом каждая отрасль выступает и как производитель некоторой продукции и как потребитель своей и произведенной другими отраслями продукции. Задача межотраслевого баланса – отыскание такого вектора валового выпуска X, который при известной матрице прямых затрат обеспечивает заданный вектор конечного продукта Y.

Матричное решение данной задачи:

X = (E-A)^-1Y. [2]

Из существующих в пакете Excel функций для работы с матрицами при решении данной задачи будем использовать следующие:

1.  МОБР – нахождение обратной матрицы. Возвращает обратную матрицу для матрицы, хранящейся в массиве. Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную – это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0.

2.  МУМНОЖ – умножение матриц. Возвращает произведение матриц. Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2. Количество столбцов аргумента массив1 должно быть таким же, как количество сток аргумента массив2, и оба массива должны содержать только числа. Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки.

3.  МОПРЕД – нахождение определителя матрицы. Определитель матрицы – это число, вычисляемое на основе значений элементов массива. Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.

Также при решении данной задачи использовали сочетание клавиш:

F2 CTRL + SHIFT + ENTER – для получения на экране всех значений результата.

E=	1	0	0
	0	1	0
	0	0	1
	0,95	-0,1	-0,3
E-A=	-0,1	0,9	-0,3	det (E-A)=	0,51
	-0,3	-0,25	0,8
	1,271562346	0,305569246	0,591424347
(E-A) – 1 =	0,335140463	1,320847708	0,620995564
	0,581567275	0,527353376	1,665845244

Вывод: Таким образом для удовлетворения спроса на продукцию первой отрасли в 50 д.е., 2‑ой в 65 д.е., 3‑ей в 28 д.е., необходимо произвести продукции первой отрасли 100 д.е., 2‑ой 120 д.е. и 3‑ей 110 д.е.

Лист с формулами

А	В	С	D	E	F	G	H
1		Выпуск(потребление)
2		Первой отрали	Второй отрали	Третьей отрали		Конечный продукт	Валовый выпуск
3		0,05	0,1	0,3		50	МУМНОЖ (С16:Е18; G3:G5)
4	A=	0,1	0,1	0,3	Y=	65	МУМНОЖ (С16:Е18; G3:G5)
5		0,3	0,25	0,2		28	МУМНОЖ (С16:Е18; G3:G5)
6
7				Решение
8	E=	1	0	0
9		0	1	0
10		0	0	1
11
12		С8‑C3	D8‑D3	Е8‑Е3
13	E-A=	С9‑C4	D9‑D4	Е9‑Е4		det (E-A)=	МОПРЕД (С12:Е14)
14		С10‑C5	D10‑D5	Е10‑Е5
15
16		МОБР (С12:Е14)	МОБР (С12:Е14)	МОБР (С12:Е14)
17	(E-A) – 1 =	МОБР (С12:Е14)	МОБР (С12:Е14)	МОБР (С12:Е14)
18		МОБР (С12:Е14)	МОБР (С12:Е14)	МОБР (С12:Е14)

Задание 4. вариант 10

Предприятие может выпускать продукции по двум технологическим способам производства. При этом за 1 час по первому способу производства оно выпускает 20 единиц продукции, по второму способу 25 единиц продукции. Количество произведенных факторов, расходуемых за час при различных способах производства, и располагаемые ресурсы этих факторов на каждый день работы представлены в таблице. Спланировать работу предприятия так, чтобы получить максимум продукции, если общее время работы предприятия по двум технологическим способам не менее 10 и не более 24 часов.

Факторы	Способ производства		Ресурсы
	1	2
Сырье	2	1	60
Рабочая сила	2	3	70
Энергия	2	1	50

Обозначим количество часов работы предприятия по первому способу х1 а по второму х2. При этом за 1 час по первому способу производства оно выпускает 20 единиц продукции, по второму способу 25 единиц продукции. Таким образом суммарное количество единиц продукции должно быть максимальным при решении уравнения z=20х1+25х2. Составим систему ограничений.

z=20x1+25x2 – max

2x1+x2<=60 – ограничение на использования сырья

2x1+3x2<=70 – ограничение на использования рабочей силы

2x1+x2<=50 – ограничение на использование энергии

10<x1+x2<24 – ограничение времени работы предприятия

Преобразуем последнее уравнение в более удобную для решения форму.

х1+х2<=24 х1>=0

– х1‑х2<=-10 х2>=0

Графическое решение задачи

Необходимо найти значения (х1, х2), при которых функция Z= 20x1+25x2 достигает максимума. При этом х1 и х2 должны удовлетворять системе ограничений, приведенной ранее:

Решение

1.  Строим область, являющуюся пересечением всех полуплоскостей, уравнения которых приведены в системе ограничений. Например, полуплоскость 2x1+x2<=60; представляет собой совокупность точек, лежащих ниже прямой, соединяющей точки с координатами (0:60) и (30; 0). Аналогично – остальные.