Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде EXCEL. Практикум

Подождите немного. Документ загружается.

Пример 1.4.1. Даны коэффициенты прямых затрат

и конечный

продукт

для трехотраслевой экономической системы:

(03 0.1

0.4^

0.2 0.5 0.0

(О.З

0.1

0.2

, У =

Г200^

100

UooJ

Требуется определить:

Коэффициенты полных затрат.

Вектор валового выпуска.

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

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

Заполнить схему межотраслевого баланса.

Для решения задачи воспользуемся функциями EXCEL.

Таблица

1.4.2

| 22

Е-А

Х(Ц)

0.3

0.2

0.3

0.7

-0.2

-0.3

2.0408

0.8163

0.8673

775.5102

510.2041

729.5918

232.6531

155.102

232.6531

0.1

0.5

-0.1

0.6122

2.2448

0.5102

51.02041

255.102

51.02041

D Е

0.4

0.2

-0.4

0.8

1.0204

0.4081

1.6836

291.8367

145.9183

200

100

300

В табл. 1.4.2 приведены результаты решения задачи по первым трем

пунктам.

В ячейки B6:D8 запишем элементы матрицы Е-

А.

Массив Е - А

задан как диапазон ячеек. Выделим диапазон B10:D12 для размещения

обратной матрицы В = (Е -

А)"

и введем формулу для вычислений

MOBP(B6:D8).

Затем следует нажать клавиши CTRL+SHIFT+ENTER.

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

ны,

следовательно, матрица

продуктивна (ответ на п.

и 4).

В ячейки G10:G12 запишем элементы вектора конечного про-

дукта Y. Выделим диапазон

В15-.В17

для размещения вектора валового

выпуска X, вычисляемого по формуле X = (Е -

А)"

• Y. Затем вводим

формулу для вычислений

MYMHO}K(B10:D12,G10:G12).

Затем следует

нажать клавиши CTRL+SHIFT+ENTER.

Межотраслевые поставки

вычисляем по формуле

•

Заполняем схему МОБ (табл.

1.4.3).

Таблица

1.4.3

Производящие

отрасли

Условно чистая

продукция

Валовой

продукт

Потребляющие отрасли

232.6

155.1

232.6

155

775.3

51.0

255 0

51.1

153.1

510.1

291.8

0.0

145.9

291.9

729.6

Конечный

продукт

200

100

300

600

Валовой

продукт

775.3

510

729.6

2015

ГЛАВА 2. РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО

ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ

ПОИСКА РЕШЕНИЙ

СРЕДЕ EXCEL

2.1.

ОБЩАЯ ЗАДАЧА

ОПТИМИЗАЦИИ.

ПРИМЕРЫ ЗАДА

ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ

В экономике оптимизационные задачи возникают в связи с много-

численностью возможных вариантов функционирования конкретного

экономического объекта, когда возникает ситуация выбора варианта,

наилучшего по некоторому правилу, критерию, характеризуемому соот-

ветствующей целевой функцией (например, иметь минимум затрат, мак-

симум продукции).

Рассмотрим несколько примеров задач линейного программирова-

ния (ЗЛП).

Задача о размещении средств

Пусть собственные средства банка вместе с депозитами в сумме со-

ставляют 100 млн долл. Часть этих средств, но не менее 35 млн долл.,

должна быть размещена в кредитах. Кредиты являются неликвидными

активами банка, так как в случае непредвиденной потребности в налич-

ности обратить кредиты в деньги без существенных потерь невозможно.

Другое дело ценные бумаги, особенно государственные. Их можно

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

случае, без большого убытка. Поэтому существует правило, согласно

которому коммерческие банки должны покупать в определенной про-

порции ликвидные активы - ценные бумаги, чтобы компенсировать не-

ликвидность кредитов. В нашем примере ликвидное ограничение таково:

ценные бумаги должны составлять не менее 30% средств, размещенных

в кредитах и ценных бумагах.

Пример заимствован из книги Дж Синки «Управление финансами в коммер-

ческом банке».

Обозначим через

средства (млн долл.), размещенные в кредитах,

через

- средства, вложенные в ценные бумаги. Цель банка состоит в

том, чтобы получить максимальную прибыль от кредитов и ценных бу-

маг:

f(x) = С\ Х\ +

Сг

Хп,

где С, - доходность кредитов, С? - доходность

ценных бумаг.

Целевая функция - это выражение, которое необходимо максими-

зировать:

/(JC)

9 X] + 6

Имеем следующую систему линейных ограничений:

Хп < 100

- балансовое ограничение;

> 35 - кредитное ограничение;

Хо >

0.3(Х]

)

- ликвидное ограничение;

X, > 0,

> 0.

Задача оптимального использования ресурсов

Фабрика имеет в своем распоряжении определенное количество ре-

сурсов: рабочую силу, деньги, сырье, оборудование, производственные

площади и т.п. Допустим, например, ресурсы трех видов: рабочая сила, сы-

рье и оборудование - имеются в количестве соответственно 80 (челУдней),

480 (кг) и 130 (станко/ч). Фабрика может выпускать ковры четырех ви-

дов.

Информация о количестве единиц каждого ресурса, необходимых

для производства одного ковра каждого вида, и доходах, получаемых

предприятием от единицы каждого вида товаров, приведена в таблице.

Ресурсы

Труд

Сырье

Оборудование

Цена (тыс р}б )

Нормы расхода ресурсов на

единиц}

изделия

ковер «Лу-

жайка»

ковер «Си-

Л}Э1»

ковер «Дет-

ский»

ковер

«Дымка»

]

Наличие

ресурсов

480

130

Требуется найги такой план выпуска продукции, при котором будет

максимальной общая стоимость продукции.

Обозначим через

Х],Х

, Х

количество ковров каждого типа.

Экономико-математическая модель

задачи.

Целевая функция - это выражение, которое необходимо максими-

зировать:

j{x) =

ЪХ\

АХ

+ ЗХз

Ограничения по ресурсам

1Х

2Х

6Л'

< 80,

5ДГ1

8Х

4ДС,

ЪХ

480,

2Xi +

4Х

8Х

< 130,

Х\,

Л2,

Х-$,

Хц

~2.

Задача о размещении производственных заказов

Необходимо в планируемом периоде обеспечить производство

300

тыс.

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

тырех филиалах предприятия. Для освоения этого нового вида изделий

нужны определенные капитальные вложения. Разработанные для каждого

филиала предприятия проекты освоения нового вида изделия характеризу-

ются величинами удельных капитальных вложений и себестоимостью еди-

ницы продукции

соответствии

таблицей.

Показатель

Себестоимость производства

изделия,

руб

Удельные капиталовложения,

р>б

Филиал предприятия

120

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

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

капитальных вложениях и общие издержки будут изменяться пропор-

ционально изменению объемов производства изделий.

Предположим, что на все филиалы предприятие для освоения

300

тыс.

новых изделий может выделить

млн руб. Необходимо найти

такой вариант распределения объемов производства продукции и капи-

тальных вложений по филиалам, при котором суммарная стоимость из-

делий будет минимальной.

Модель

задачи.

Введем следующие обозначения:

i - номер филиала (i =

...,

п;

- 4);

- объем выпускаемой продукции на i-м филиале предприятия;

Г- суммарная потребность в изделиях (Т= 300 тыс. шт.);

К - выделяемые капиталовложения

(К= 18

млн руб.);

С, - себестоимость производства продукции на i-м филиале

предприятия;

к, - удельные капитальные вложения на единицу продукции на

i-м филиале.

Экономико-математическая модель задачи в символах будет иметь вид:

/(х) =

ХС,-X,->min;

£х,>Г;

i=i

j^k

X,<K;

;=1

Z,>0;

и.

С учетом имеющихся данных модель задачи примет вид:

/(x)

83-X,+89-X2+95-X3+98-X

-*min,

ограничения

> 300 (тыс. шт.),

120Х,

80Х

50Х,

40Х

< 18

(млн руб.),

^1,2,3,4 S

2.2. ТЕХНОЛОГИЯ РЕШЕНИЯ

ЗАДА ЧЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ

ПОИСКА РЕШЕНИЙ

Поиск решения - это надстройка EXCEL, которая позволяет ре-

шать оптимизационные задачи. Если в меню Сервис отсутствует команда

Поиск

решения,

значит, необходимо загрузить эту надстройку. Выберите

команду

Сервисе

Надстройки и активизируйте надстройку Поиск ре-

шения. Если же этой надстройки нет в диалоговом окне

Надстройки,

то

вам необходимо обратиться к панели управления Windows, щелкнуть на

пиктограмме

Установка

удачение

программ и с помощью программы

установки EXCEL (или Office) установить надстройку Поиск решения.

Для решения задачи необходимо:

1 Создать форму для ввода условий задачи.

2 Указать адреса ячеек, в которые будет помещен результат реше-

ния (изменяемые ячейки).

3 Ввести исходные данные.

4 Ввести зависимость для целевой функции.

Ввести зависимости для ограничений.

6. Указать назначение целевой функции (установить целевую

ячейку).

Ввести ограничения.

8 Ввести параметры для решения ЗЛП.

Рассмотрим на примере задачи 2.1.2 технологию решения Задачи

оптимального использования

ресурсов.

Для задачи 2.1.2 подготовим форму для ввода условий

(см.

рис.

2.2.1).

В нашей задаче оптимальные значения вектора X

(Х\,

, Х

)

будут помещены в ячейках ВЗ:ЕЗ, оптимальное значение целевой функ-

ции - в ячейке F4.

3 Введем исходные данные в созданную форму. Получим резуль-

тат, показанный на

рис.

2.2.2.

ШШШЯШШШШШВШШШВШШШЙШШШШВШШШШШМШ

j Переменные

Х2 ХЗ Х4

3 значение ЦФ

;4«оэф вЦФ

Ограничения

|Вид ресурсов ,

леваячасть

знак

праваячасть

jtpyfl

II j

сырье

Л >боруд-е

Рис.

2.2.1.

Введена форма для ввода данных.

Весь текст на рис 2 2

является комментарием и на решение задачи не влияет

Ji.

ВС

; D

Переменные

X2 X3

значение

козф

ЦФ

Ограничения

Вид ресурсов

труд

сырье

оборуд-е

ЦФ

левая часть знак правая часть

480

130

Рис.

2.2.2. Данные введены

Введем зависимость для целевой функции

Курсор в F4.

Курсор на кнопку

Мастер

функций.

Ml.

На экране диалоговое окно

Мастер функций

шаг

из 2.

Курсор в окно

Категория

на категорию

Математические.

Ml.

Курсор в окно

Функции

на СУММПРОИЗВ.

Ml.

В массив

ввести

В$3:Е$3.

В массив 2 ввести В6:Е6. —

; кЦ •>

£й

Готово. На

экране:

в F4 введена функция, как показано на

рис.

2.2.3.

Введем зависимость для левых частей ограничений:

Курсор в F4.

Копировать в буфер.

Курсор в F7.

Вставить из буфера.

Курсор в F8.

Вставить из буфера.

Курсор в F9.

Вставить из буфера.

На этом ввод зависимостей закончен.

Обозначим через

М1 с тел)

ющее действие

«один щелчок левой кнопкой мыши»

Адреса ячеек

во

все диалоговые окна

>добно вводить

не с

клавиатуры,

про-

таскивая мышь по ячейкам, чьи адреса следует

ввести

СУММПРОИЗВ

=СУММПРОИЗВ(В$3:Е$3,В4:Е4)

в с о Е

значений

коэф

в ЦФ

Вид

ресурсов

труп

сырье

оборудование

Переменные

Х2 ХЗ Х4

з|

Ограничения

2 2

8 4

! 4 1

ЦФ

1|В(В$ЗЕ$3,1

лввал

часть

знак

праьая

часть

6 <= 80

480

8 <= 130

J£L

СУММПРОИЗВ

Массив1

|В$3:Е$3

Масо«2|в4:Е4

дг-г-п uriiiTi

ffif

МассивЗ

"- •-—ш *

- дало}

Возвращает

сумму

произведений

соответствующих

элементов

массивов.

Массиве.

массив1,массив2,

от 2 до 30 массивов, чьи

компонента

нужно

перемножить

затем

сложить.

Все

массивы должны иметь

одну

ту*.е

размерность

Значение

ОК

Отмена

Рис.

2.2.3.

Вводится

функция

для вычисления целевой функции

Запуск

Поиска решения

После выбора команд

С ервис=$Поиск

решения появится диалоговое

окно Поиск решения.

В диалоговом окне

Поиск решения

есть три основных параметра:

•

Установить целевую

ячейку

•

Изменяя

ячейки

• Ограничения

Сначала нужно заполнить поле «Установить целевую ячейку». Во всех

задачах для

среде iea Поиск решения

оптимизируется результат в одной из

ячеек рабочего листа. Целевая ячейка связана с другими ячейками этого ра-

бочего листа с помощью формул. Средство

Поиск решения

использует фор-

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

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

для целевой ячейки или же установить конкретное значение.

Второй важный параметр средства Поиск решения - это параметр

Изменяя ячейки. Изменяемые ячейки - это те ячейки, значения в кото-

рых будут изменяться для того, чтобы оптимизировать результат в целе-

вой ячейке. Для поиска решения можно указать до 200 изменяемых яче-

ек. К изменяемым ячейкам предъявляется два основных требования: они

не должны содержать формул, и изменение их значений должно отра-

жаться на изменении результата в целевой ячейке. Другими словами,

целевая ячейка зависима от изменяемых ячеек.

Третий параметр, который нужно вводить для Поиска решения -

это

Ограничения.

6. Назначение целевой функции (установить целевую ячейку).

• Курсор в поле «Установить целевую ячейку».

• Ввести адрес $F$4.

• Ввести направление целевой функции: Максимальному значению.

Ввести адреса искомых переменных:

• Курсор в поле «Изменяя ячейки».

• Ввести адреса В$3:Е$3.

Ввод ограничений.

• Курсор в поле «Добавить». Появится диалоговое окно Добавле-

ние

ограничения

(рис.

2.2.4).

• В поле «Ссылка на ячейку» ввести адрес $F$7.

• Ввести знак ограничения <.

• Курсор в правое окно.

• Ввести адрес $Н$7.

•

Добавить.

На экране опять

диалоговое

окно Добавление

ограничения.

• Ввести остальные ограничения.

• После ввода последнего ограничения ввести ОК.

На экране появится диалоговое окно Поиск решения с введенными

условиями (рис. 2.2.5).

8. Ввод параметров для решения ЗЛП (рис. 2.2.6).

• Открыть окно

Параметры поиска

решения.

• Установить флажок Линейная модель, что обеспечивает приме-

нение симплекс-метода.

• Установить флажок

Неотрицательные

значения.