Реферат: Решение финансовых и оптимизационных задач в Microsoft Excel

;

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

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

Для решения этой задачи с помощью средства поиска решений введем данные, как показано на рис. 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)

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

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

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

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

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

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

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

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

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

;

;

;

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

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