Решение финансовых и оптимизационных задач в Microsoft Excel (стр. 3 из 4)

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

Задача решается в MicrosoftExcelпри помощи команды Сервис, Поиск решения. Ячейки В13, С13, D13 отведены под значения переменных

, , . В ячейку F12 введена целевая функция (рис 7.2).

Для приведенного на рис 7.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 7.2.

Рисунок 7.1

Рисунок 7.2

В диалоговом окне Поиск решения введены данные, показанные на рис 7.3 и рис 7.4.

Рисунок 7.3

Рисунок 7.4

Из результатов расчета видно (см. рис 7.1), что оптимальным является производство 571 шт. продукции А, 0 шт. продукции В, 71 шт. продукции С. Этот объем производства принесет 4071 у.е. прибыли.

Задача № 8(Вариант 7 Задача № 2)

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

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

– объем перевозок с -го кирпичного завода на -й строительный объект. Целевая функция – это суммарные расходы на производство и транспортировку кирпича после сокращения, т. е.

где

– стоимость перевозки одной тонны кирпича с

-го кирпичного заводана -й строительный объект;

– сокращение объема производства на 1-ом кирпичном заводе;

– сокращение объема производства на 2-ом кирпичном заводе.

Неизвестные в данной задаче должны удовлетворять следующим ограничениям:

- Объемы перевозок и сокращения не могут быть отрицательными;

- Так как модель сбалансирована, то вся продукция должна быть вывезена с заводов, а потребности всех строительных объектов должны быть полностью удовлетворены.

В результате имеем следующую математическую модель:

минимизировать:

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

;

;

;

;

где

– объем производства на -м кирпичном заводе;

– потребность на -м строительном объекте.

Для решения этой задачи с помощью средства поиска решений введем данные, как показано на рис. 8.1.

Рисунок 8.1

В ячейки С5:Е6 введены стоимости перевозок. Ячейки С10:Е11 и I10:I11 отведены под значения неизвестных – объема перевозок и необходимого сокращения объема производства на предприятиях соответственно. В ячейки G10:G11 введены объемыпроизводства на кирпичных заводах, а в ячейки С11:Е13 введена потребность в продукции на строительных объектах (с учетом сокращения объема производства). В ячейку F15 введена целевая функция

=СУММПРОИЗВ(C5:E6;C10:E11)+H5*G10+H6*G11

В ячейки С12:Е12 введены формулы (см. рис. 8.1), определяющие объем продукции, необходимой соответствующему потребителю.

В ячейки F10:F11 введены формулы (см. рис. 8.1), определяющие объем продукции, вывозимой с кирпичных заводов.

Далее выбираем команду Сервис, Поиск решения и заполняем открывшееся диалоговое окно Поиск решения , как показано на рис. 8.2.

Рисунок 8.2

В диалоговом окне Параметры поиска решения (рис. 8.3) устанавливаем флажок Линейная модель и Неотрицательные значения. После

Рисунок 8.3

нажатия кнопки Выполнить средство поиска решений находит оптимальный план поставок продукции и показывает на каких предприятиях необходимо провести сокращение производства (рис 8.4). Т. е. необходимо сократить производство на 140 т на 2-м кирпичном заводе.

Рисунок 8.4

Задача № 9(Задача № 2-17)

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

Для решения данной задачи построим ее математическую модель. Неизвестными в данной задаче являются объемы перевозок. Пусть

– объем перевозок с -го пункта получения сырья на -е предприятие. Целевая функция – это суммарные транспортные расходы, т. е.

где

– элемент матрицы С, задающей тарифы перевозок;

Неизвестные в данной задаче должны удовлетворять следующим ограничениям:

- Объемы перевозок не могут быть отрицательными;

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

В результате имеем следующую математическую модель:

минимизировать:

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

;

;

;

где

– запас сырья на -м пункте его получения;

– потребность в сырье на -м предприятии.

Для решения этой задачи с помощью средства поиска решений введем данные, как показано на рис. 9.1.

Рисунок 9.1

В ячейки С5:F7 введена матрица С (тарифы перевозок). Ячейки С14:F16 отведены под значения неизвестных – объема перевозок. В ячейку G19 введена целевая функция

.=СУММПРОИЗВ(C5:F7;C14:F16)

В ячейки С17:F17 введены формулы (см. рис. 8.1), определяющие объем сырья, необходимого соответствующему предприятию.

В ячейки G14:G16 введены формулы (см. рис. 8.1), определяющие объем сырья, сосредоточенного на пунктах его получения.

Далее выбираем команду Сервис, Поиск решения и заполняем открывшееся диалоговое окно Поиск решения , как показано на рис. 9.2.

Рисунок 9.2

В диалоговом окне Параметры поиска решения (рис. 9.3) устанавливаем флажок Линейная модель и Неотрицательные значения. После

Рисунок 9.3

нажатия кнопки Выполнить средство поиска решений находит оптимальный план перевозок, при котором общая стоимость перевозок является минимальной (рис 9.4).

Рисунок 9.4

Задача № 10(Набор задач № 9.1)