Инструкция по использованию Microsoft Excel для решения задач

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

Рассмотрим решение межотраслевого баланса на ЭВМ в соответствии с моделью Леонтьева на следующем примере.

Имеется баланс трех взаимосвязанных отраслей за предыду­щий период:

Производство	Потребление	Конечный продукт
Отрасль 1	Отрасль 2	Отрасль 3
Отрасль 1 Отрасль 2 Отрасль 3

1. Найти валовый продукт каждой отрасли, чистую продукцию каж­дой отрасли, матрицу коэффициентов прямых затрат.

2. Какой будет конечный продукт каждой отрасли, если валовый станет равен, соответственно 100, 150 и 200.

3. Какой будет валовый продукт каждой отрасли, если конечный продукт первой отрасли необходимо увеличить на 50 %, второй уменьшить на 4 единицы, а третьей увеличить на 6 единиц.

Подготавливаем таблицу исходных данных в электронной таблице Excel.

1. Для нахождения валового продукта каждой отрасли в ячей­ку F3 вводим формулу «=СУММ(В3:Е3)» (для ее ввода достаточно нажать кнопку автосуммы со значком Σ). Результат – 124. Автозапол­нением переносим результат ячейки на F4 и F5. Для рассчета чистой прибыли вводим в ячейку В6 формулу «=F3-B3-B4-B5», в С6 формулу «=F4-C3-C4-C5», в D6 формулу «=F5-D3-D4-D5». Находим коэффици­енты прямых затрат. Для этого каждый столбец матрицы В3-D5 нужно разделить на соответствующий валовой продукт. В ячейку В7 вводим «=B3/$F$3» (чтобы сделать абсолютную ссылку $F$3 нужно щелкнуть по ячейки F3 и нажать клавишу F4). Автозаполняем В7 на В8 и В9. Аналогично вводим в С7 «=C3/$F$4» и автозаполняем на С8 и С9. Вводим в D7 «=D3/$F$5» и автозаполняем на D8 и D9. Матрица коэф­фициентов затрат рассчитана.

2. Так, как новый валовой продукт каждой отрасли равен, со­ответственно 100, 150 и 200, то вводим эти числа в ячейки Н3, Н4 и Н5. По формуле, новый конечный продукт равен Y = (E - A)X. Для ее использования вводим единичную матрицу. В А11 вводим подпись

«Е=», а в В11-D13 вводим числа

Рассчитываем матрицу

(Е-А). Вводим в А15 подпись «(Е-А)=», а в В15 «=B11-B7». Автозаполняем ячейку на В15-D17. Для вычисления результата – новых значений конечного продукта в ячейку G3 вводим функцию перемно­жения матриц – МУМНОЖ (категория «Математические»). Аргумен­ты функции: в поле «массив 1» даем ссылку B15:D17 (матрица Е-А), в поле «массив 2» - H3:H5 (новый валовой продукт). Далее обводим ячейки G3-G5 курсором мыши, выделяя их, и нажимаем F2 и Ctrl+Shift+Enter. Результат – новый конечный продукт.

3. Если конечный продукт первой отрасли нужно увеличить на 50 %, то он станет 124,5, если второй уменьшить на 4, то он станет 93, если третий увеличить на 6 единиц, он будет 138. Вводим в ячейки G7-G9 числа 124,5; 93; 138. В соответствии с формулой Леонтьева новый валовый продукт находим по формуле X = (E - A)^-1Y. Для расчета обратной матрицы в ячейку Е15 вводим подпись «(Е-А) обрат.», а в F15 ставим формулу расчета обратной матрицы МОБР (категория «Математические»). Аргумент функции – ссылка на B15-D17. Обво­дим курсором ячейки F15-H17 и нажимаем F2 и Ctrl+Shift+Enter. Для вычисления новых значений валового продукта в ячейку Н7 вводим функцию перемножения матриц – МУМНОЖ. Аргументы: в поле «массив 1» даем ссылку F15:H17, в поле «массив 2» - G7:G9. Далее обводим ячейки Н7-Н9 и нажимаем F2 и Ctrl+Shift+Enter. Результат – новый валовой продукт. Задача решена.

Задание. Межотраслевой баланс производства и распределе­ния продукции для 4 отраслей имеет

Производя­щие отрасли	П отребляющие отра с ли	Валовой продукт
	1 x11 x21 x31 x41
		x12	x13	x14	X1
		x22	x23	x24	X2
		x32	x33	x34	X3
		x42	x43	x44	X4

Матрица межотраслевых материальных связей x_ij и матри­ца валового выпуска X _j приведены в таблице по вариантам.

Вари­ант	xij	X j	Вари­ант	xij	X j
	60 50 5 90 60 20 60 10 85 85 75 40 5 15 10 5	800 400 800750		30 90 85 60 25 80 70 40 50 75 85 40 70 80 60 20

	90 100 60 85 70 25 100 65 35 70 85 10 25 65 65 90	775 825 825 600		25 20 20 5 60 45 90 50 95 15 15 65 45 45 10 35
	30 35 40 55 5 5 5 95 65 10 0 15 80 20 80 35	550 600 575 520		60 40 30 65 85 55 15 55 20 70 50 55 55 85 60 30
	0 5 80 95 15 60 20 40 55 50 20 40 0 35 10 60	550 750 525 820		80 45 85 95 25 35 20 30 15 15 55 75 95 5 5 95
	15 70 40 30 15 55 30 45 60 65 25 90 40 80 5 60	725 850 500 620		65 50 5 80 15 20 45 25 90 70 20 85 45 85 70 95
	25 50 30 20 35 45 20 25 30 55 45 60 20 30 25 50	800 750 500 520		55 40 35 20 25 30 45 35 70 80 20 65 35 55 60 75

1.Найти конечный продукт каждой отрасли, чистую про­дукцию каждой отрасли, матрицу коэффициентов прямых затрат.

2.Какой будет конечный продукт каждой отрасли, если ва­ловой продукт первой отрасли увеличится в 2 раза, у второй увели­чится на половину, у третьей не изменится, у четвертой – умень­шится на 10 процентов.

3.Найти валовой продукт, если конечный станет равен 700, 500, 850 и 700.

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

ПРИМЕРНЫЕ ВОПРОСЫ НА ЗАЩИТЕ РАБОТЫ

1. Для чего предназначены балансовые модели?

2. Как производится автозаполнение ячейки?

3. Какая функция предусмотрена для перемножения матриц?

4. Какое сочетание клавиш предусмотрено для перемножение матриц?

5. Как рассчитывается обратная матрица?