Порядок решения задачи в MS Excel

Прогнозирование с использованием модели «затраты-выпуск».

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

Используя формулу (9), можно найти прогноз увеличения объема производства отраслей, если увеличится спрос на какую-либо продукцию на величину DY. Тогда выпуск продукции других отраслей увеличиться на величину: DX = (E – A)^-1* D Y. (13)

Пример 3. Пусть в регионе существует три отрасли: сельское хозяйство, промышленность, сфера обслуживания. В таблице 3 указаны коэффициенты прямых затрат а_ij и конечный продукт отраслей Y_j.

Таблица 3

№	Отрасли	Прямые межотраслевые потоки	Конечная продукция
С.хо-во	Промышленность	Сфера обслуживания
	С. Хоз-во		0,2
	Промышленность	0,2		0,1
	Сфера обслуживания		0,1	0,2

Y = 120

При составлении прогноза увеличения объема производства было определено, что сельское хозяйство увеличивает на 10%, а промышленность – на 20% производство (конечного) продукта. Определить увеличение валового объема производства сферы обслуживания на прогнозируемый период, если ее (сферы обслуживания) объем конечного продукта останется без изменений.

1. Разместим на листе рабочей книги матрицу коэффициентов прямых затрат и единичную матрицу (см. рис. 6).

Рис. 6. Исходные данные

2. Определим матрицу (I – A) по формулам как в предыдущем примере (см. рис. 7).

Рис. 7. Вычисление матрицы (I – A)

3. Введем в ячейки G10:G12 исходные значения вектора-столбца конечного продукта;

Для вычисления матрицы коэффициентов полных затрат B= (E – A)^-1, обратной к матрице (E – A):

- выделим диапазон ячеек B10:D12 для размещения обратной матрицы (B);

- выберем стандартную функцию «МОБР» из группы «математические»;

- в качестве аргумента стандартной функции укажем координаты матрицы (E – A), т.е. B5:D7;

- нажмем клавиши «Ctrl + SHIFT + ENTER» для ввода формулы над массивом в выделенный диапазон.

Обратная матрица будет иметь значения (см. рис. 8):

Рис. 8. Значения коэффициентов полных материальных затрат

Округлим эти значения:

Рис. 9. Округленные значения матрицы полных затрат

4. Определим вектор валового выпуска, используя формулу (11):

X = (E – A)^-1*Y.

Для умножения матриц:

- выделим диапазон ячеек для размещения результата умножения матриц (B10:D12);

- выберем функцию МУМНОЖ из категории «математические» в качестве первого аргумента указать координаты матрицы B (диапазон ячеек: B19:B21), а в качестве второго аргумента – координаты вектора-столбца конечного продукта Y (G10:G12).

- нажать клавиши «Ctrl + SHIFT + ENTER» для ввода формулы над массивом в выделенный диапазон.

- В результате получим следующие значения вектора валового выпуска продукции. Округлим результаты до четырех знаков при помощи стандартной математической функции Round. (см. рис.9):

5. Прогнозируемые величины равны по условию задачи:

- Для сельского хозяйства: Y_1н = 10%* Y₁=1,1*200=220

- Для промышленности: Y2_н= 20%*Y2=1,2*100=120

- Для сферы обслуживания:Y3_н=300

6. Пересчитаем валовый объем с новыми значениями вектора Y, используя модель Леоньева и находя X через известноый Y:

7. Затем определим динамику изменения валового объема для каждой отрасли при данном изменении объемов конечного продукта: (Xн1-Xc1)/Xc1*100=

Литература по теме:

1. Шапкин А.С., Мазаева Н.П. Математические методы и модели исследования операций: Учебник. – М.: Издательско-торговая корпорация «Дашков и К», 2004 г.-400с., гл. 6, стр. 155.

2. Экономико-математические методы и прикладные модели: Учеб. пособие для вузов / В.В. Федосеев, А.Н. Гармаш, Д.М. Дайитенбегов и др.; Под ред. В.В. Федосеева. – М.: ЮНИТИ, 1999. – 391 с., гл.6, стр. 231.

3. Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде MS EXCEL / Практикум: Учебное пособие для вузов. – М.: ЗАО «Финстатинформ», 2000. – 136 с., стр.

Дата добавления: 2014-01-14