Поиск решения MS EXCEL (1.4). Распределение ресурсов (ограничение - требование к качеству продукции)

Создадим модель для нахождения наилучшего распределения ресурсов, при котором минимизируются затраты, понесенные за несколько периодов (Allocation Problem). В качестве ограничения используем требования к качеству продукции. Расчет будем проводить с помощью надстройки Поиск решения.

Задача оптимального распределения ресурсов (распределительная задача) заключается в отыскании наилучшего распределения ресурсов, при котором либо максимизируется результат, либо минимизируются затраты.

Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь.

Задача

Компания владеет 4-мя карьерами, из которых она может добывать известняк различного качества. Особенно важны 2 характеристики: содержание в породе кальция и магния.
Компания должна выполнить заказ на поставку 6000 тонн известняка (в течение 1 периода, года). Продукция должна содержать в среднем 0,9 единиц кальция на тонну и 2,3 единицы магния.
Годовые затраты на эксплуатацию каждого карьера фиксированы и составляют значительную величину. Каждый карьер имеет свою производительность. Суммарная мощность карьеров избыточна для выполнения заказа. Чтобы минимизировать затраты необходимо один из карьеров закрыть (не эксплуатировать в этом году).
Какой карьер выгоднее всего закрыть? (пример с сайта ]]>www.solver.com]]>)

Создание модели

На рисунке ниже приведена модель, созданная для решения задачи (см. файл примера).

Переменные (выделено зеленым). В качестве переменных модели следует взять бинарные значения (0 или 1), означающие 0 – карьер закрыт, 1 – открыт. Также необходимо варьировать количество известняка, выпускаемое каждым карьером, чтобы рассчитать содержание кальция и магния.

Ограничения (выделено синим). Среднее содержание кальция и магния должно удовлетворять требования спецификации. Заказ должен быть выполнен. Объем выпущенной карьером продукции не должен превосходить его максимальной производительности.

Целевая функция (выделено красным). Расходы на содержание карьеров должны быть минимальны.

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

Результаты расчетов

Как показывают расчеты выгоднее закрыть 2-й карьер.
В статье Поиск решения MS EXCEL (1.5). Распределение ресурсов (ограничение - требования к качеству продукции, несколько периодов) рассмотрена задача выполнения заказа в течение 5 лет.

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

 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)
Яндекс.Метрика