Моделирование средствами Excel

Размещено на http://www.allbest.ru/

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

Тема 1. Экономико-математическая модель межотраслевого баланса (модель Леонтьева). Моделирование средствами Excel

модель межотраслевой баланс международная торговля

Программное обеспечение: Microsoft Excel. Основные сведения

Рассмотрим модель межотраслевого баланса, называемую еще моделью Леонтьева или моделью «затраты-выпуск».

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

Рассмотрим отрасль i, i = 1, 2,…, n. Она выпускает некую продукцию за данный промежуток времени (например, за год) в объеме xi, который еще называют валовым выпуском. Часть объема продукции xi , произведенная i-ой отраслью используется для собственного производства в объеме xii , часть - поступает в остальные отрасли j = 1, 2,…, n для потребления при производстве в объемах xij , и некоторая часть объемом yi - для потребления в непроизводственной сфере, так называемый объем конечного потребления. Перечисленные сферы распределения валового продукта i-ой отрасли приводят к соотношению баланса

, i = 1, 2,…, n .

Введем коэффициенты прямых затрат aij , которые показывают, сколько единиц продукции i-ой отрасли затрачивается на производство одной единицы продукции в отрасли j. Тогда можно записать, что количество продукции, произведенной в отрасли i в объеме xij и поступающей для производственных нужд в отрасль j, равно



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

, i = 1, 2,…, n .(1)

Введя вектор валового выпуска X, матрицу прямых затрат A и вектор конечного потребления Y

модель Леонтьева (1) можно записать в матричном виде

X = AX + Y (2)

Матрица A ? 0, у которой все элементы aij ? 0 (неотрицательны), называется продуктивной матрицей, если существует такой неотрицательный вектор X ? 0, для которого выполняется неравенство

X > AX.

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

Y = X - AX > 0.

Модель Леонтьева с продуктивной матрицей A называется продуктивной моделью.

Для проверки продуктивности матрицы A достаточно существования обратной матрицы B = (E - A)-1 с неотрицательными элементами, где матрица E - единичная матрица

.

С помощью модели Леонтьева (2) можно выполнить три вида плановых расчетов, при условии соблюдения условия продуктивности матрицы A:

1) Зная (или задавая) объемы валовой продукции всех отраслей X можно определить объемы конечной продукции всех отраслей Y

Y = (E - A)X

2) Задавая величины конечной продукции всех отраслей Y можно определить величины валовой продукции каждой отрасли

X = (E - A)-1Y (3)

3) Задавая для ряда отраслей величины валовой продукции, а для всех остальных отраслей - объемы конечной продукции, можно найти величины конечной продукции первых отраслей и объемы валовой продукции вторых.

Матрица

B = (E - A)-1

называется матрицей полных материальных затрат. Ее смысл следует из матричного равенства (3), которое можно записать в виде X = BY. Элементы матрицы B показывают, сколько всего необходимо произвести продукции в i-ой отрасли, для выпуска в сферу конечного потребления единицы продукции отрасли j.

Пример с использованием технологии Excel

Задача. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить:

1) Матрицу коэффициентов полных материальных затрат B

2) Проверить продуктивность матрицы A

2) Вектор валового выпуска X

3) Межотраслевые поставки продукции xij

Математическая модель и последовательность расчетов

Модель Леонтьева имеет вид

X = AX + Y.

Матрица полных материальных затрат B равна

B = (E - A)-1

Продуктивность матрицы A проверяется, по вычисленной матрице B. Если эта матрица существует и все ее элементы неотрицательны, то матрица A продуктивна.

Вектор валового выпуска X рассчитывается по формуле

X = BY

Межотраслевые поставки продукции xij вычисляются по формуле

xij = aij xj

Процесс решения задачи средствами Microsoft Excel

Для решения задачи межотраслевого баланса необходимо уметь выполнять с помощью Excel следующие операции над матрицами:

- Умножение матрицы на вектор

- Умножение двух матриц

- Транспонирование матрицы или вектора

- Сложение двух матриц

1. Задание Исходных данных задачи

Вызовите Microsoft Excel.

Введите матрицу A в ячейки с адресами А2:С4 и вектор Y в ячейки с адресами Е2:Е4 (рис. 1).

Рис. 1. Задание исходных данных и последовательное выполнение плановых расчетов

2. Вычисление матрицы коэффициентов полных материальных затрат B.

2.1 Введите единичную матрицу Е в ячейки с номерами А7:С9.

2.2 Вычислите матрицу Е - А. Матрица Е - А является разностью двух матриц Е и А. Для вычисления разности двух матриц необходимо проделать следующее:

- установите курсор мыши в левый верхний угол (это ячейка с адресом А12) результирующей матрицы Е - А, которая будет расположена в ячейках с адресами А12:С14;

- введите формулу =А7-А2 для вычисления первого элемента результирующей матрицы Е - А, предварительно установив английскую раскладку клавиатуры;

- введенную формулу скопируйте во все остальные ячейки результирующей матрицы. Для этого, установите курсор мыши в ячейку А12; наведите указатель мыши на точку в правом нижнем углу ячейки, так чтобы указатель мыши принял вид крестика; при нажатой левой кнопке мыши протяните указатель до ячейки С12, а затем так же протяните указатель мыши до ячейки С14.

В результате в ячейках А12:С14 появится искомая матрица, равная разности двух исходных матриц Е и А.

2.3 Вычислите матрицу B = (E - A)-1 , являющейся обратной по отношению к матрице Е - А. Матрица Е - А расположена в ячейках с адресами А12:С14. Для вычисления матрицы В необходимо проделать следующее:

- выделите диапазон ячеек А17:С19 для размещения матрицы В;

- нажмите на панели инструментов кнопку Вставка, а затем кнопку Функция. В появившемся окне в поле Категория выберите Математические, а в поле Выберите функцию - имя функции МОБР. Щелкните на кнопке ОК;

- появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы Е - А и введите диапазон матрицы Е - А (диапазон ячеек А12:С14) в рабочее поле Массив (протащив указатель мыши при нажатой левой кнопке от ячейки А12 до ячейки С14);

- нажмите комбинацию клавиш Ctrl+Shift+Enter. Обратите внимание, что нажимать надо не клавишу ОК(!), а именно комбинацию клавиш Ctrl+Shift+Enter.

В диапазоне ячеек А17:С19 появится искомая обратная матрица (E - A)-1 , равная матрице B.

3. Проверка продуктивности матрицы А.

Поскольку матрица В найдена, следовательно она существует. Все элементы матрицы В неотрицательны, поэтому матрица В - продуктивна.

4. Вычисление вектора валового выпуска X.

Вычисление вектора валового выпуска X находим по матричной формуле X = BY, в которой матрица В вычислена, а вектор Y задан.

Вычисление вектора X = BY производится с помощью операции умножения матриц, а в данном случае - умножения матрицы В на вектор Y. Для этого необходимо:

- выделить диапазон ячеек Е7:Е9, где будет расположен вектор Х. Обратите внимание, что по правилам умножения матриц, размерность результирующей матрицы Х должна быть равна количеству строк матрицы В на количество столбцов матрицы Y. В нашем случае, размерность вектора Х равна: три строки на один столбец;

- нажать на панели инструментов кнопку Вставка, а затем кнопку Функция. В появившемся окне в поле Категория выберите Математические, а в поле Выберите функцию - имя функции МУМНОЖ. Щелкните на кнопке ОК;

- появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходных матриц В и Y и введите диапазон матрицы В (диапазон ячеек А17:С19) в рабочее поле Массив 1 (протащив указатель мыши при нажатой левой кнопке от ячейки А17 до ячейки С19), а диапазон вектора Y (ячейки Е2:Е4) в рабочее поле Массив 2 (рис. 2);



Рис. 2. Диалоговое окно умножения матриц МУМНОЖ

- нажмите комбинацию клавиш Ctrl+Shift+Enter. Обратите внимание, что нажимать надо не клавишу ОК(!), а именно комбинацию клавиш Ctrl+Shift+Enter.

В диапазоне ячеек Е7:Е9 появится искомый вектор Х.

5. Вычисление межотраслевых поставок продукции xij

Межотраслевые поставки продукции xij вычисляются по формуле

xij = aij xj,

где aij - элементы исходной матрицы А, расположенной в ячейках А2:С4, xj - элементы вектора Х, найденного выше в п. 4 и расположенные в ячейках Е7:Е9.

Для проведения вычислений xij необходимо проделать следующее.

5.1 Вычислить транспонированный вектор Хт относительно вектора Х. При этом вектор-столбец Х станет вектором-строкой Хт. Это необходимо для согласования размерностей дальнейшего умножения элементов векторов.

С этой целью:

- выделить указателем мыши при нажатой левой кнопке ячейки Е12:G12, в которых будет располагаться транспонированный вектор Хт ;

- нажать на панели инструментов кнопку Вставка, а затем кнопку Функция. В появившемся окне в поле Категория выберите Ссылки и массивы, а в поле Выберите функцию - имя функции ТРАНСП (рис. 3). Щелкните на кнопке ОК;

- появившееся диалоговое окно ТРАНСП мышью отодвиньте в сторону от исходного вектора Х и введите диапазон вектора Х (диапазон ячеек Е7:Е9) в рабочее поле Массив (протащив указатель мыши при нажатой левой кнопке от ячейки Е7 до ячейки Е9);

- нажмите сочетание клавиш Ctrl+Shift+Enter.

Рис. 3. Диалоговое окно транспонирования матрицы ТРАНСП

В результате в поле ячеек Е12:G12 расположится транспонированный вектор Хт .

5.2 Вычислить межотраслевые поставки продукции xij . Для этого проделать следующие операции:

- поставить курсор мыши в ячейку А22, в которой будет расположено значение x11. В этой ячейке набрать формулу =A2*E12, которая означает, что x11 = a11 x1 .

- введенную формулу скопируйте во все остальные ячейки первой строки (в ячейки А22:С22, протащив мышью крестик в правом нижнем углу от ячейки А22 при нажатой левой кнопке мыши, до ячейки С22. При этом будут вычислены x12 = a12 x2 и x13 = a13 x3.

Затем в ячейке А23 наберите формулу =A3*E12 и повторяя аналогичную процедуру, получите значения x21 = a21 x1 , x22 = a22 x2 и x23 = a23 x3 . Повторите аналогичные действия для ячеек А24:С24.

В результате все межотраслевые поставки продукции будут найдены и расположатся в матрице с ячейками А22:С24.

Тема 2. Экономико-математическая модель международной торговли (линейная модель обмена). Моделирование средствами Excel

Основные сведения

Рассмотрим бюджеты n стран, которые обозначим как x1, x2, … , xn.

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

Обозначим через xij количество средств страны j расходуемое на закупку товаров из страны i, при этом xjj - затраты на закупку товаров внутри страны j. Тогда сумма всех затрат страны j, идущее на закупку товаров как внутри страны, так и на импорт из других стран должна равняться национальному доходу страны xj, т.е.

,

j = 1, 2,…, n .(4)

Разделив обе части равенства (4) на xj и введя коэффициенты

Получим

,

j = 1, 2,…, n(5)

Коэффициенты равны доли национального дохода страны j расходуемую на закупку товаров у страны i.

Матрица A коэффициентов

(6)

называется структурной матрицей торговли. Понятно, что сумма элементов каждого столбца равна единице.

С другой стороны, количество средств страны j расходуемое на закупку товаров из страны i и равное xij, является выручкой для страны i за свой товар, который у нее закупила страна j. Суммарная выручка i-ой страны равна

,

i = 1, 2,…, n(7)

Так как

,

то

и равенство (7) можно записать в виде

,

i = 1, 2,…, n .(8)

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

В сбалансированной системе международной торговли не должно быть дефицита, другими словами, у каждой страны выручка от торговли должна быть не меньше ее национального дохода, т.е.

, i = 1, 2,…, n .

Одновременное выполнение этих неравенств может иметь место только в том случае, если

, i = 1, 2,…, n , (9)

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

Равенства (9), с использованием (8), можно записать в матричном виде

AX = X(10)

где А - структурная матрица (6) международной торговли; Х - вектор национальных доходов стран

.

Матричное уравнение (10) соответствует задаче на собственное значение и собственный вектор матрицы А.

Очевидно, что собственное значение матрицы А, согласно уравнению (10), равно 1, а собственный вектор, соответствующий этому собственному значению, равен Х.

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

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

Моделирование с использованием технологии Excel

Определение собственного вектора X матрицы А с помощью средств Microsoft Excel невозможно.

Поэтому математическую модель международной торговли сводят к задаче линейного программирования.

Для этого, систему уравнений

(A - E)X = 0,

где Е - единичная матрица

которая получается из уравнений (10) переносом правой части в левую, трактуют как ограничения-равенства.

Кроме того, вводят новое ограничение-неравенство

,

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

В качестве целевой функции вводится сумма бюджетов всех стран, которая должна достигать максимума:

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

Необходимо найти максимум целевой функции

при ограничениях:

Пример с использованием технологии Excel

Задача. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 7680 млн.ден.ед.

Математическая модель

при ограничениях:

Решение задачи средствами Excel

Методика решения задачи линейного программирования с помощью средств Поиска решения Excel подробно рассматривалась в Лабораторной работе №1 и поэтому здесь уже рассматриваться не будет.

Рис. 4. Исходные данные в Excel

Задание исходных данных на рабочем листе Excel приведено на рис.4.

В ячейки В2:Е6 занесены коэффициенты при системе ограничений, в ячейках G2:G6 содержатся ограничения в правых частях, в ячейки I2:I6 занесены формулы левых частей ограничений, ячейки В9:Е9 содержат изменяемые переменные . Например, в ячейке I2 записана формула ограничений =СУММПРОИЗВ(В2:Е2;В9:Е9). Аналогичные формулы записаны в ячейках I3:I6. Формула целевой функции =СУММ(В9:Е9) занесена в ячейку С10.



Рис. 5.Решение задачи средствами Excel

Процесс решения - занесение в окно Поиск решения ячейки с формулой целевой функции, занесение изменяемых ячеек, внесение ограничений приведено на рис. 5. В окне Параметры необходимо отметить: Линейная модель, Неотрицательные значения, Автоматическое масштабирование.

На рис. 5 приведены также результаты решения, согласно которым национальные доходы четырех стран равны соответственно 1015.359, 1458.228, 3251.308, 1955.105 млн.ден.ед. Из содержимого ячеек I2:I6 видно, что все ограничения выполнены. Значение целевой функции (ячейка С10) равно 7680 млн.ден.ед.

Индивидуальные задания по Теме 1

Задание 1. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 2. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 3. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 4. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 5. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 6. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 7. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 8. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 9. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 10. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 11. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Задание 12. Экономическая система состоит из трех отраслей, для которых матрица прямых затрат A и вектор конечного продукта Y известны:

.

Определить: 1) Матрицу коэффициентов полных материальных затрат B. 2) Проверить продуктивность матрицы A. 2) Вектор валового выпуска X. 3) Межотраслевые поставки продукции xij

Индивидуальные задания по Теме 2

Задание 1. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 4590 млн.ден.ед.

Задание 2. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 15055 млн.ден.ед.

Задание 3. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 9000 млн.ден.ед.

Задание 4. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 59550 млн.ден.ед.

Задание 5. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 15590 млн.ден.ед.

Задание 6. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 51503 млн.ден.ед.

Задание 7. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 25590 млн.ден.ед.

Задание 8. Найти национальные доходы четырех торгующих стран в сбалансированной системе международной торговли, если структурная матрица торговли этих четырех стран равна

,

а сумма бюджетов стран не превышает 83355 млн.ден.ед.

Литература

Орлова И.В. Экономико-математическое моделирование: Практическое пособие по решению задач. - М.: Вузовский учебник, 2004

Размещено на Allbest.ru