Методика выполнения Задания № 5 «Решение задачи линейного программирования в Excel»

Условие:

Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым удельная маржинальная прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта. В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта. В необходимо изготавливать в каждый конкретный месяц.

Таблица 2. Вариант задания №5

Наименование ресурса	Потребление ресурсов на единицу продукта		Планируемый объем ресурсов на следующий месяц
	А	В
Часов машинной обработки	3	13	305
Единиц сырья	14	4	425
Единиц труда	9	7	225

Необходимо определить:

Определите количество единиц продуктов А и В, которые Николай доложен производить в следующем месяце для максимизации маржинальной прибыли.

Выполнение задания:

1. Запись условий неотрицательности

Данное задание продолжает тему, изучаемую в предыдущем задании, однако если в задании 4 применялся графический метод решения, то в настоящем задании применяется метод расчетов значений. Исходя из этого можно воспользоваться математической моделью построенной в предыдущем задании:

Максимизировать: Z= 2500 * х₁ + 3500 * х₂

При условии, что: 3 * х₁+ 10 * х₂≤ 330

16 * х₁+ 4 * х₂≤ 400

6 * х₁+ 6 * х₂≤ 240

х₂≥ 12

х₁≥ 0

2. Ввод формул. На следующем этапе была создана таблица и введены исходные данные

3. Следующим шагом была установлена надстройка «Поиск решения».

4. Для решения задачи была вызвана надстройка «Поиск решения», заполнено диалоговое окно Excel и получено требуемое значение.

Рисунок 2. Результаты расчетов по заданию 5

Методика выполнения Задания № 6 «Решение транспортной задачи с помощью ms Excel»

Условие:

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

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

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

Исходные данные:

Таблица 3. Вариант задания №5. Наличие минеральных удобрений на складах.

Склады	Наличие удобрений, т.
Склад №1	240
Склад №2	205
Склад №3	200
Склад №4	149
Склад №5	276

Таблица 4. Вариант задания №5. Потребность в минеральных удобрениях на различных пунктах.

Пункты	Потребность в удобрениях, т.
1 пункт	220
2 пункт	130
3 пункт	250
4 пункт	300

Таблица 5. Вариант задания №5. Расстояния между складами и пунктами доставки.

	Пункт 1	Пункт 2	Пункт 3	Пункт 4
Склад №1	5	8	4	10
Склад №2	12	6	5	8
Склад №3	14	11	14	4
Склад №4	9	5	13	6
Склад №5	7	11	12	11

Выполнение задания:

1. Для решения задачи были подготовлены подготовим необходимые таблицы в соответствии с условиями задания.

2. Используя исходные данные, на подготовленной таблице были введены требуемые объемы поставок и расстояния между складами и пунктами доставки.

3. Для решения транспортной задачи была использована надстройка «Поиск решения»

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

4. В группе полей «Ограничения» были введены необходимые ограничения:

• $B$4:$B$8 <=$B$11:$B$12. Оно означает, что значение в ячейке В4 должнобыть меньше или равно значению в В11, в В5 меньше или равно, чем в В12, и так далее до В8 и В15. В ячейках с В4 по В8 на листе находятся объемы поставок с конкретных складов. В ячейках с В11 по В15 - запасы на этих же складах. Так как невозможно вывести со склада больше, чем на нем есть, первое значение должно быть не больше второго.

• Второе условие $С$4:$Е$8>=0. Оно означает, что объем перевозок не может быть отрицательным, то есть, если на складе не хватает минеральных удобрений, их не везут с пункта доставки, на который эти минеральные удобрения были завезены ранее. Грузопоток имеет только одно направление - от складов к пунктам доставки удобрений.

• И. наконец, третье, и последнее условие $С$9:$Е$9>=$С$10:$Е$10. Оно означает, что значения в ячейках девятой строки должны быть больше или равны значениям в ячейках десятой строки,, то есть запросы пунктов доставки минеральных удобрений должны быть выполнены полностью. Перевыполнение объема поставок допустимо, а недовыполнение - нет.

5. Введенные условия должны позволить найти наиболее оптимальный вариант решения задачи. Была запущена процедура подбора решения. После нахождения решения появляется диалог вариант решения был занесен на рабочий лист.

Рисунок 3. Результаты расчетов по заданию 6