182604 (Финансово-экономические расчеты в Excel), страница 2

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

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

х1+х2=0

– х1‑х2=0

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

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

Решение

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

2. Находим градиент функции Z.

grad z = {}

Строим вектор с началом в точке (0; 0) и концом в точке ().

1. Строим прямую, перпендикулярную вектору градиента. Так как по условию мы ищем максимум функции Z, то передвигаем прямую в направлении указанном вектором. Точка максимума – последняя точка области, которую пересечет эта прямая. В нашем случае, искомая точка лежит на пересечении прямых 2х1+3х2<=70 и х1+х2<=24;

2. Решаем систему уравнений

х 1+х2= 24; х1 = 2

2х1+3х2=70; х2 = 22;

Т.е графическое построение дало результат (2; 22).

Максимальное значение функции Z = 20*2+25*22=590.

Решение с помощью пакета Excel

	х1	х2
Значения	2	22
нижняя граница	0	0
верхняя граница	24	24
z	20	25	590		max
	Коэффициенты целевой функции
система ограничений	Коэффициенты			Значения					Фактические ресурсы			Неиспользованные ресурсы
Сырье	2	1	26		<=			60			34
Рабочая сила	2	3	70		<=			70			0
Энергия	2	1	26		<=			50			24
Время работы	1	1	24		<=			24			0
	-1	-1	-24		<=			-10			14

Вывод: Для получения максимального количества единиц продукции предприятию необходимо работать по первому способу 2 часа, а по второму 22 часа. При этом затраты сырья составят 26 ед., рабочей силы 70 ед. и энергии 26 ед. Избыточным является ресурс «сырье» на 34 ед. и ресурс «энергия» на 24 ед., недостаточным – «рабочая сила».

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

A	B	C	D	E		F	G	H
1		х1	х2
2	Значения	2	22
3	нижняя граница	0	0
4	верхняя граница	24	24
5	Z= 20x1+25x2	20	25	СУММПРОИЗВ (C2:D2; C5:D5)		max
6		Коэффициенты целевой функции
7	система ограничений	Коэффициенты			Значения		Фактические ресурсы	Неиспользованные ресурсы
8	Сырье	2	1	СУММПРОИЗВ (C3:D3; C8:D8)		<=	60	G8‑E8
9	Рабочая сила	2	3	СУММПРОИЗВ (C3:D3; C9:D9)		<=	70	G8‑E8
10	Энергия	2	1	СУММПРОИЗВ (C3:D3; C10:D10)		<=	50	G9‑E9
11	Время работы	1	1	СУММПРОИЗВ (C3:D3; C11:D11)		<=	24	G11‑E11
12		-1	-1	СУММПРОИЗВ (C3:D3; C12:D12)		<=	-10	G12‑E12

Список используемой литературы

1. Финансово-экономические расчеты в Excel. – 2-е изд., доп. – М: Информационно-издательский дом «Филинъ», 2006. – 184 с.

2. Методический указания и контрольные задания по дисциплине «Информатика» для студентов заочного факультета экономического направления обучения. Ч. 3/ Сост. В.Н. Черномаз, Т.В. Шевцова, О.А. Медведева – : ДГМА, 2007 – 40 стр.