Предприятие выпускает три вида продукции используя сырье трех видов эксель

Обновлено: 07.07.2024

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

При решении задач линейного программирования, во-первых, необходимо составить модель, то есть сформулировать условия на математическом языке. После этого решение может быть найдено графически (см., например, здесь), с использованием надстройки Excel «Поиск решения» (рассмотрено в настоящей заметке) или с помощью специализированных компьютерных программ (см., например, здесь).

Рассмотрим линейное программирование в Excel на примере задачи, ранее решенной графическим методом.

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

1. Воспользуемся математической моделью построенной в упомянутой заметке. Вот эта модель:

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

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

2. Создадим экранную форму и введем в нее исходные данные (рис. 1).

Рис. 1. Экранная форма для ввода данных задачи линейного программирования

Обратите внимание на формулу в ячейке С7. Это формула целевой функции. Аналогично, в ячейки С16:С18 введены формулы для расчета левой части ограничений.

3. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

Если надстройки «Поиск решения» вы на ленте Excel не обнаружили, щелкните на кнопку Microsoft Office, а затем Параметры Excel (рис. 3).

Рис. 3. Параметры Excel

Выберите строку Надстройки, а затем в самом низу окна «Управление надстройками Microsoft Excel» выберите «Перейти» (рис. 4).

Рис. 4. Надстройки Excel

Рис. 5. Активация надстройки «Поиск решения»

После загрузки надстройки для поиска решения в группе Анализ на вкладке Данные становится доступна команда Поиск решения (рис. 2).

4. Следующим этапом заполняем окно Excel «Поиск решения» (рис. 6)

Рис. 6. Заполнение окна «Поиск решения»

В поле «Установить целевую ячейку» выбираем ячейку со значением целевой функции – $C$7. Выбираем, максимизировать или минимизировать целевую функцию. В поле «Изменяя ячейки» выбираем ячейки со значениями искомых переменных $C$4:$D$4 (пока в них нули или пусто). В области «Ограничения» с помощью кнопки «Добавить» размещаем все ограничения нашей модели. Жмем «Выполнить». В появившемся окне «Результат поиска решения» выбираем все три типа отчета (рис. 7) и жмем Ok. Эти отчеты нужны для анализа полученного решения. Подробнее о данных, представленных в отчетах, можно почитать здесь.

Рис. 7. Выбор типов отчета

На основном листе появились значения максимизированной целевой функции – 130 000 руб. и изменяемых параметров х₁ = 10 и х₂ = 30. Таким образом, для максимизации маржинального дохода Николаю в следующем месяце следует произвести 10 единиц продукта А и 30 единиц продукта В.

Если вместо окна «Результат поиска решения» появилось что-то иное, Excel`ю найти решение не удалось. Проверьте правильность заполнения окна «Поиск решения». И еще одна маленькая хитрость. Попробуйте уменьшить точность поиска решения. Для этого в окне «Поиск решения» щелкните на Параметры (рис. 8.) и увеличьте погрешность вычисления, например, до 0,001. Иногда из-за высокой точности Excel не успевает за 100 итераций найти решение. Подробнее о параметрах поиска решения можно почитать здесь.

Пример №1 . Предприятие выпускает 2 вида продукции А и В, для производства которых используется сырье трех видов. На изготовление единицы изделия А требуется затратить сырья каждого вида а1,а2,а3 кг соответственно , а для единицы изделия В-b1,b2,b3 кг. Производство обеспечено сырьем каждого вида в количестве P1,P2,P3 кг. Соответственно. Стоимость единицы изделия А составляет С1 руб., а единицы изделия В- С2 руб. Требуется составить план производства изделий А и В, обеспечивающий максимальную стоимость продукции. Решить:
А) геометрически;
В) симплекс-методом.
0.1x₁+0.2x₂+0.4x₃ ≤ 1100
0.05x₁+0.02x₂+0.02x₃ ≤ 120
3x₁+x₂+2x₃ ≤ 8000
3x₁+5x₂+4x₃ → max

Решение. Матрица коэффициентов A = a(ij) этой системы уравнений имеет вид:

Решим систему уравнений относительно базисных переменных: x4,x5,x6
Полагая, что свободные переменные равны 0, получим первый опорный план: X1 = (1100,120,8000)

Итерация №0
Текущий опорный план неоптимален, так как в индексной строке находятся отрицательные коэффициенты. В качестве ведущего выберем столбец, соответствующий переменной x2, так как наибольший коэффициент по модулю.
Вычислим значения D i по строкам как частное от деления: и из них выберем наименьшее:

Пример №2 . Наиболее эффективным для хозяйства является выращивание трех культур: озимой пшеницы, проса, гречихи. Ожидаемый уровень урожайности этих культур, себестоимость центнера продукции, нормы внесен6ия удобрений и затраты труда в расчете на единицу продукции, приведенные в соответствии с ожидаемым уровнем урожайности, заданы таблицей. Известны и наиболее вероятные цены фактической реализации центнера продукции.
Критерий оптимальности – максимум прибыли от реализации данных видов продукции.

Введем систему обозначений:
Х₁ – искомая площадь посева озимой пшеницы (га);
Х₂ – искомая площадь посева проса (га);
Х₃ – искомая площадь посева гречихи (га).

Ограничения:
1. Общая площадь посева культур (га)
Х₁+Х₂+Х₃≤2000
2. Затраты труда (чел.-дни)
Если за единицу измерения неизвестных принят 1 га, то соответственно необходимо рассчитать все нормативы на эту единицу. Затраты труда даны по условию задачи в расчете на 1 ц продукции , но, зная урожайность , легко пересчитать нормативы затрат труда в расчете на 1 га посева. Таким образом ограничение запишется так:
40x₁ + 50x₂ + 45x₃ ≤ 14600
3. Расход минеральных удобрений (ц д.в.)
0.8x₁ + 0.6x₂ + x₃ ≤ 1600

Далее решается согласно алгоритму симплексного метода.

Поиск наибольшего значения

1. Для изготовления цемента двух видов используется сырье трех видов. Запасы сырья известны и равны соответственно: 264, 136 и 266 т. Количество сырья каждого вида, необходимое для производства единицы цемента первого вида соответственно равны: 12, 4 и 3. Для цемента второго вида: 3, 5 и 14. Прибыль от реализации цемента первого вида составляет 6 у.е., от цемента второго вида - 4 у.е. Составить план, обеспечивающий наибольшую прибыль производству:
а) записать математическую модель;
б) решить задачу графическим методом;
в) решить задачу симплекс-методом;
г) к исходной задаче записать двойственную и решить ее, используя соотношение двойственности и решение исходной.
Обозначим границы области многоугольника решений.
Рассмотрим целевую функцию задачи F = 6X₁+4X₂ => max.
Построим прямую, отвечающую значению функции F = 0: F = 6X₁+4X₂ = 0. Будем двигать эту прямую параллельным образом. Поскольку нас интересует максимальное решение, поэтому двигаем прямую до последнего касания обозначенной области. На графике эта прямая обозначена пунктирной линией.

Определив обратную матрицу А -1 через алгебраические дополнения, получим:

Как видно из последнего плана симплексной таблицы, обратная матрица A -1 расположена в столбцах дополнительных переменных .
Тогда Y = C*A -1 =
Оптимальный план двойственной задачи равен: y₁ = 0.29, y₂ = 0.62, y₃ = 0
Z(Y) = 264*0.29+136*0.62+266*0 = 162
2. На трех станциях отправления сосредоточен однородный груз, который следует перевезти в четыре пункта назначения, имеющих потребность в этом грузе. Стоимость перевозок единицы груза от каждой станции до каждого пункта назначения считается известной и содержится в таблице. Требуется составить такой план перевозок, при котором их общая стоимость окажется минимальной.
Решить транспортную задачу методом потенциалов.

Рассмотрим линейное программирование в Excel на примере задачи, ранее решенной графическим методом.

1. Воспользуемся математической моделью построенной в упомянутой заметке. Вот эта модель:

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

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

2. Создадим экранную форму и введем в нее исходные данные (рис. 1).

Рис. 1. Экранная форма для ввода данных задачи линейного программирования

3. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

Рис. 3. Параметры Excel

Рис. 4. Надстройки Excel

Рис. 5. Активация надстройки «Поиск решения»

4. Следующим этапом заполняем окно Excel «Поиск решения» (рис. 6)

Рис. 6. Заполнение окна «Поиск решения»

Рис. 7. Выбор типов отчета

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

Собственные средства банка составляют 100 единиц. Банк получает прибыль, выдавая кредиты и покупая ценные бумаги. Доходность кредитов составляет 15%, ценных бумаг – 10%. Таким образом, годовая прибыль банка составляет:

где x объем средств, выданных в виде кредитов, а y средства, затраченные на покупку ценных бумаг. Используя "Поиск решения" найдём максимальную прибыль банка при условии ограничений:

Создадим таблицу, как указано на рис. 1.

Для ячейки E4 введём формулу: =СУММПРОИЗВ($B$3:$C$3,B4:C4) и скопируем

ее в ячейки E7, E8, E9. Выполним команду Сервис/Поиск решения… Заполним вызванное окно в соответствии с рис. 2.

Ограничения записываются через использование кнопки Добавить, где вносятся обозначения ограничений задачи. рис. 3

После заполнения окна Поиска решений… перейдём по кнопке Параметры в окно Параметры поиска решений (рис. 4) и установим там флажки напротив полей: Линейная модель и Неотрицательные значения.Далее выполним команду ОК, и нажмем клавишу Выполнить в окне Поиска решений.

решении. рис. 5

В результате выполнения задания появилось следующее решение данной

оптимизационной задачи. рис. 6

Процесс изготовления двух видов (А и В) изделий заводом требует, во-первых последовательной обработки на токарных и фрезерных станках, и, во-вторых затрат двух видов сырья: стали и цветных металлов. Данные о потребности каждого ресурса на единицу выпускаемой продукции и общие запасы ресурсов приведены в таблице. Прибыль от реализации единицы изделия А – 3 тыс. руб., а единицы Б – 8 тыс. руб. Определить такой план выпуска продукции, который обеспечивает максимальную прибыль при условии, что время работы фрезерных станков должно быть использовано полностью.

Выполнение: производиться аналогично заданию 1.

Результат на рис.7

Предприятие выпускает три вида продукции А, В и С. Реализация единицы

продукции А даёт прибыль 9 руб., В – 10 руб. а С – 16 руб. Сбыт продукции обеспечен, т.е. её можно производить в любых количествах, но запасы сырья ограничены. В таблице приведены нормы расхода сырья на производство единицы продукции и запасы трёх видов необходимого сырья. Найти план выпуска продукции, при котором прибыль будет максимальна.

Выполнение: производиться аналогично заданию 1.

Результат на рис.8

Предприятие располагает ресурсами сырья трёх видов: С1, С2 и С3. Используя это сырьё, оно выпускает четыре вида продукции: П1, П2, П3 и П4. В таблице указаны затраты каждого вида сырья на изготовление 1 тонны продукции каждого вида и объём ресурсов сырья. Прибыль, получаемая от реализации 1 тонны продукции равна: П1 – 48, П2 – 25, П3 – 56, П4 – 30. Определить ассортимент выпускаемой продукции, при котором прибыль будет максимальной, при условии, что продукции П2 необходимо выпустить не менее 8 т, продукции П4 не более 5 т, а продукции П1 и П3 в отношении 3:1.

Выполнение: производиться аналогично заданию 1.

Результат на рис.9

Месячный фонд зарплаты сотрудников больницы составляет 50000 руб. Штат

больницы и коэффициенты в формуле для расчёта окладов сотрудников приведены в таблице. Оклады определяются по формуле: оклад=А*x+В, где x – оклад санитара. Определить оклады всех сотрудников.

Выполнение: производиться аналогично заданию 1, только здесь ЦФ стремится к опред. значению и изменяем ячейку С3. Все остальные рассчитываюстя по формуле Ax+B.

Результат на рис.10

С трех баз надо перевезти грузы в два магазина. Количество груза (в штуках) на базах – в таблице:

Читайте также: