Практикум по курсу Экономикоматематические модели и методы

• Название: Практикум по курсу Экономикоматематические модели и методы
• Дата: 28.05.2022
• Размер: 1.54 Mb.
• Имя файла: l.r-1_mob.docx
• Тип: Практикум; #554648

Лабораторный практикум по курсу
«Экономико-математические модели и методы»

Лабораторная работа №1

«Модель межотраслевого баланса»

Подготовила А.А. Коробецкая

Работа посвящена изучению модели межотраслевого баланса (МОБ) и расчетам по ней средствами MS Excel 2007.

Лабораторная работа содержит три задания:

1. 
   Выполнение расчетов с матрицами в MS Excel 2007.
   
2. 
   Нормативный расчет межотраслевого баланса.
   
3. 
   Статистический расчет межотраслевого баланса.
   

Рассматривается инструмент «Поиск решения».
Каждое задание оформляется на отдельном листе в общей книге Excel. На каждом листе указывается номер и название задания, ФИО и группа выполнившего, номер варианта.

Отчет по работе выполнять не требуется, к сдаче предоставляется только файл Excel.

Содержание

Задание 1.Операции с матрицами 5

Подготовка к работе. Поиск решения

«Поиск решения» – это инструмент, позволяющий подбирать значения ячеек, так чтобы найти минимум, максимум или определенное значение.

Кнопка «Поиск решения» находится на вкладке «Данные»:



По умолчанию «Поиск решения» в Excel отключен и кнопка не отображается. Чтобы включить его, необходимо выполнить следующую последовательность действий.

1. 
   В меню Office нажать кнопку «Параметры Excel»
   

2. 
   В окне параметров Excel выбрать «Надстройки», выделить пункт «Пакет анализа» и нажать кнопку «Перейти...»
   

3. 
   В окне «Надстройки» поставить галочку «Поиск решения» и нажать «OK»
   

После нажатия на кнопку «Поиск решения» появляется окно:



Таким образом, для поиска решения необходимо задать:

1. 
   целевую ячейку – ячейка с формулой, для которой ищется максимум, минимум или корень;
   
2. 
   изменяемые ячейки – одна или несколько ячеек с управляемыми переменными; их значения входят в формулу целевой ячейки;
   
3. 
   ограничения (необязательно) – диапазоны изменения переменных, условия на значений и др.
   

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

Пример

Найти минимум функции: f(x) = 10 – 2x + 0,5x⁴

Для решения потребуется как минимум две ячейки: в одну будет помещено решение (значение x), в другую – сама функция, которую нужно минимизировать.



Поиск решения: целевая ячейка – ячейка с формулой для f(x), «изменяя ячейки» – ячейка для x, ограничений нет.





Результат:

1. Операции с матрицами

Задание

Заданы матрицы A и B, вектор C и константы a, b. Вычислить:

1. 
   определители матриц ;
   
2. 
   транспонированные матрицы ;
   
3. 
   обратные матрицы ;
   
4. 
   произведение матриц ;
   
5. 
   значение .
   

Решить систему уравнений вида , где – вектор переменных. Решение найти двумя способами:

1. 
   с помощью матричных операций;
   
2. 
   с помощью «Поиска решений».
   

Пример варианта задания

	a =	14			b =	6
	6	17	34			40	-12	87			-316
A =	-22	11	22		B =	37	21	-14		С =	546
	81	-12	-108			-34	-11	40			338

Указания к выполнению

Для выполнения операций с матрицами в Excel присутствует несколько функций и специальный режим редактирования, когда несколько ячеек представляют собой одно целое (матрицу или вектор).

1. 
   Создайте и сохраните новую книгу Excel. Переименуйте «Лист 1» в «Задание 1». Добавьте на лист «Задание 1» шапку (см. скриншот). Введите исходные данные и настройте форматирование ячеек. Матрицы и вектор записываются в виде таблиц.
   

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

2. 
   Выполнение расчетов.
   

Для расчета определителя в Excel используется формула МОПРЕД(массив). Это обычная формула, результатом является число.





Транспонирование матрицы выполняется с помощью функции ТРАНСП(диапазон), но результатом в данном случае будет не одно число, а тоже матрица, т.е. несколько ячеек одновременно. В Excel существует способ для ввода общей формулы для диапазона ячеек. Для этого:

1. 
   выделите диапазон ячеек, в которых будет размещен результат (B17:D19);
   
2. 
   нажмите F2 на клавиатуре – Excel перейдет в режим ввода формулы;
   
3. 
   введите =ТРАНСП(B10:D11), но не выходите из ячейки;
   
4. 
   нажмите Ctrl+Shift+Enter.
   

В результате формула будет распространена (не растянута!) на весь диапазон B17:D19. Теперь редактировать его можно только целиком, используя сочетание клавиш Ctrl+Shift+Enter. Формула при этом отображается в фигурных скобках.





Чтобы оформить верхний или нижний индексы (в данном случае ^T), во время набора текста выделите только нужный символ, правый клик – «Формат ячеек...», вкладка «Шрифт», поставить галочку «надстрочный» или «подстрочный».

3. 
   Формулы для следующих расчетов:
   

МОБР(диапазон) – обратная матрица;

МУМНОЖ(диапазон1;диапазон2) – умножение двух матриц.

Они также вводятся как формулы диапазона.

4. 
   Арифметические действия с матрицами можно выполнять двумя способами: а) с отдельными ячейками (поэлементно); б) целиком для матриц, используя формулы для диапазона.
   

5. 
   Решение системы уравнений в матричном виде.
   

Из курса линейной алгебры известно, что решение уравнения вида



можно найти как

.

Таким образом, достаточно взять обратную матрицу и умножить ее на вектор С.



Для использования «Поиска решения» необходимо задать целевую функцию. В данном случае можно записать:

.

Введем эту формулу и подготовим место для значений X.





Значения ячеек I37:I39 необходимо установить равными нулю. Но в «Поиске решения» в качестве целевой функции можно задать только одну ячейку. Поэтому I37 мы будем использовать как целевую функцию, а для I38 и I39 зададим ограничения:



В результате получим:



Таким образом, оба метода дают одинаковый результат.

Замечание: Вектор BX после поиска решений содержит очень малые, близкие к 0 значения (2E-13 = 2∙10^-13 и т.д.). Это отличие от нуля возникает из-за ошибки округления.

Задание 2. Нормативный расчет МОБ

Задание

Заданы матрица коэффициентов прямых затрат A и вектор конечного потребления Y.

1. 
   Вычислить матрицу коэффициентов полных затрат B.
   
2. 
   Проверить продуктивность матрицы A.
   
3. 
   Заполнить таблицу МОБ:
   
   1. 
      Вычислить вектор валового продукта X.
      
   2. 
      Рассчитать объемы межотраслевых поставок продукции x_ij.
      
   3. 
      Вычислить добавленную стоимость Z.
      

Пример варианта задания

	0,15	0,16	0,03	0,11	0,08	0,11		11500
	0,12	0,11	0,14	0,18	0,13	0,17		3100
A =	0,09	0,11	0,13	0,11	0,18	0,15	Y =	31000
	0,04	0,12	0,15	0,09	0,06	0,15		6700
	0,1	0,11	0,16	0,07	0,09	0,13		12900
	0,1	0,18	0,19	0,09	0,11	0,16		20000

Указания к выполнению

Перейдите на «Лист 2» и переименуйте его в «Задание 2». Скопируйте вариант задания, заполните «шапку».

1. 
   Вычислим матрицу коэффициентов полных затрат по формуле:
   

Для этого сначала необходимо вычислить матрицу , где E – единичная матрица.

Для заполнения E удобнее сначала ввести везде 0 с помощью «растягивания», а затем уже вписать 1 по диагонали.



2. Все значения матрицы B положительные, следовательно, матрица A продуктивна и обеспечивает положительную величину конечного производства по всем отраслям.

На диагонали матрицы B значения > 1, а остальные ячейки меньше 1.

3. Расчертим место для таблицы межотраслевого баланса (раскрашивать необязательно):



Чтобы вставить значок суммы, нажмите на ленте на вкладке «Вставка» кнопку «Символ» , выберите шрифт «(обычный текст)», набор «греческие и коптские символы» и найдите значок суммы (греческая заглавная «сигма»). Нажмите «Вставить».



Порядок заполнения таблицы:

1. 
   Скопировать значения конечного потребления Y_i из исходных данных.
   

2. 
   Вычислить столбец валового продукта X_i по формуле:
   

X = (E – A)^–1 Y

3. 
   Заполнить строку X_j путем транспонирования столбца X_i.
   
4. 
   Рассчитать объемы межотраслевых поставок продукции по формуле:
   

x_ij = a_ij X_j

Чтобы было удобно растягивать формулы, зафиксируйте в формуле Excel только номер строки для X_j (два раза нажать F4 или вручную вписать $):

=B8*B$49

5. 
   Вычислить итоги Σ_i и Σ_j как суммы соответствующих строк и столбцов.
   
6. 
   Вычислить добавочную стоимость Z_j по формуле:
   

Z_j = X_j – Σ_j



Проверьте себя!

Σ_i + Y_i = X_i

Σ Z_j= Σ Y_i

Σ X_j= Σ X_i

Небольшие расхождения (как и в примере) возможны из-за ошибок округления.

Задание 3. Статистический расчет МОБ

Задание

Народное хозяйство некоторой страны было разбито на пять основных отраслей:

1. 
   Тяжелая промышленность.
   
2. 
   Легкая промышленность.
   
3. 
   Строительство.
   
4. 
   Сельское хозяйство.
   
5. 
   Транспорт.
   

Статистические данные содержат следующие сведения (в млн. д.е.):

1. 
   межотраслевой баланс за прошлый год;
   
2. 
   оплата труда по отраслям;
   
3. 
   суммарная амортизация по отраслям;
   
4. 
   валовой продукт по отраслям.
   

1. 
   Вычислить коэффициенты прямых затрат. Какие отрасли какой продукции потребляют больше всего? меньше всего?
   
2. 
   Вычислить коэффициенты полных затрат. Проверить продуктивность экономики. Сколько всего продукции должна произвести каждая отрасль для производства конечной продукции на 1 млн. д.е.?
   
3. 
   Определить добавочную стоимость и валовую прибыль по отраслям. Есть ли среди отраслей убыточные?
   
4. 
   Рассчитать суммарные объемы промежуточного и конечного потребления. Чему равен ВВП страны? Какая отрасль занимает первое место по производству конечной продукции? по общему объему производства?
   

Пример варианта задания

Отрасль		Межотраслевой обмен
		1	2	3	4	5
1	Тяжелая промышленность	23900	2600	5300	8800	11400
2	Легкая промышленность	4200	4200	10600	9600	2300
3	Строительство	13600	500	1800	7000	6400
4	Сельское хозяйство	0	3700	2200	22700	1200
5	Транспорт	7300	3900	8900	13900	4600
	Оплата труда	7500	600	1700	3700	3700
	Амортизация	12000	300	600	2600	5300
	Валовой продукт	80800	33200	47200	72700	41000

Указания к выполнению

Перейдите на «Лист 3», переименуйте его в «Задание 3». Заполните «шапку» и вставьте исходные данные.



Добавьте в таблицу межотраслевого баланса недостающие строки и столбцы. Под таблицей выделите место для матрицы коэффициентов прямого и полного потребления.

1. 
   Вычислим коэффициенты прямых затрат по формуле:
   

a_ij = x_ij / X_j



Вывод:

Тяжелая промышленность больше всего потребляет собственной продукции (a₁₁ = 0,296), и не потребляет продукцию сельского хозяйства (a₄₁ = 0).

Легкая промышленность также больше всего потребляет собственной продукции, но не намного больше, чем продукцию сельского хозяйства и транспорта (a₂₂ = 0,127, a₄₂ = 0,111, a₅₂ = 0,118). Строительство в легкой промышленности почти не ведется (a₃₂ = 0,015).

В строительстве больше всего используется продукции легкой промышленности (a₂₃ = 0,22), а меньше всего – собственной и сельского хозяйства (a₃₃ = 0,38, a₄₃ = 0,047).

В сельском хозяйстве больше всего потребляется собственной продукции (a₄₄ = 0,312), меньше всего – строительной (a₂₅ = 0,056).

Транспортная отрасль больше всего потребляет продукции тяжелой промышленности (a₁₅ = 0,278), меньше всего – легкой (a₂₅ = 0,029).

2. 
   Вычислим матрицу полных затрат по аналогии с заданием 2:
   

Вывод

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

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

Для легкой промышленности – на сумму 1,25 млн.д.е.

Для строительства – на сумму 1,19 млн.д.е.

Для сельского хозяйства – на сумму 1,56 млн.д.е.

Для транспорта – на сумму 1,31 млн.д.е.

3. 
   Вычислим валовую прибыль P по отраслям, как разницу между валовым выпуском и всеми видами затрат (A – амортизация, L – оплата труда):
   

P_j = X_j – Σ_j – A_j – L_j



Добавочную стоимость Z можно вычислить по любой из двух формул:

Z_j = X_j – Σ_j

или

Z_j = L_j + A_j +P_j



Вывод

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

При этом валовой продукт сельского хозяйства более чем в 2 раза превышает значение легкой промышленности. Но и затраты на производство в легкой промышленности значительно ниже по всем строкам.

4. 
   Вычислим суммарные объемы промежуточного и конечного потребления.
   

Сначала транспонируем строку с валовым производством, чтобы получить валовое потребление.

Промежуточное потребление равно сумме потребления по строкам.

Конечное потребление равно разнице между валовым и промежуточным потреблением.

Также добавим строку с итоговыми (суммарными) показателями.

Окончательно матрица межотраслевого баланса примет вид:



Вывод

ВВП страны равен 94 300 млн. д.е. Наибольший объем конечного потребления принадлежит сельскому хозяйству (45% ВВП), наименьший – легкой промышленности и транспорту (по 2,5% ВВП).