Решение производственной задачи в Excel
Предлагаю вашему вниманию шаблон для определения оптимального плана выпуска продукции в Excel.
Данная классическая задача относиться к задачам линейного программирования и сводиться к нахождению такого плана выпуска продукции, при котором прибыль от ее реализации (целевая функция) является максимальной.
Казалось бы, чем больше выпуск, тем больше прибыль, однако мы интуитивно понимаем, что существуют некие ограничения.
В данном случае это ограничения по объему выпуска связанные с возможностью продать продукцию (или производственной мощностью оборудования) и наличием дефицита ресурсов для производства, например отсутствием на складе нужного материала или отсутствием нужных специалистов в достаточном количестве.
Также мы можем иметь ограничение по минимальному уровню выпуска, например, в производство запускаются партии не меньше 100 единиц продукции.
Таким образом, мы имеем три вида ограничений:
1) По максимально возможному объему выпуска;
2) По минимально возможному объему выпуска;
3) По наличию ресурсов (материальных, трудовых, финансовых и т.д.).
Соответственно, с учетом этих ограничений мы должны так скомбинировать производственную программу, чтобы прибыль была максимальной.
Прибыль у нас будет определяться маржинальной прибылью на единицу продукции, т.е. разницей между ценой реализации единицы продукции и переменными затратами на ее производства.
Этот параметр будем считать постоянным и известным для каждого вида продукции.
Я не буду приводить здесь систему линейных уравнений, по которым решается эта задача, так как мы будем искать решение не на бумаге, а посредством программы Excel. Желающие же смогут легко найти всю теоретическую базу необходимую для решения этой классической задачи.
Для поиска оптимального решения в программе Excel необходимо чтобы была установлена надстройка «Поиск решения«.
Для установки соответствующей надстройки необходимо перейти на вкладку Файл, пункт Параметры и в отрывшемся окне выбрать пункт Надстройки.
В открывшемся диалоговом окне выбрать надстройку Поиск решения (если надстройка еще не активна) и нажать кнопку Перейти (1)
В открывшемся окне установить соответствующую галочку (2) и нажать кнопку Ок.
Далее для удобства присваиваем диапазонам, в которых находятся наши данные имена.
Так, ячейке, в которой у нас будет находиться целевая функция, дадим имя «Прибыль». Она должна стремиться к максимуму.
Диапазон с данными о маржинальной прибыли на единицу продукции назовем «Маржа».
Диапазон с данными оптимального выпуска назовем «Выпуск». Этот параметр мы и будем оптимизировать.
Соответственно прибыль у нас рассчитается по формуле:
=СУММПРОИЗВ(Маржа;Выпуск)
Диапазону с данными о наличии того или иного ресурса присвоим имя «Наличие».
Ограничение по максимальному выпуску назовем «Максимум» а по минимальному соответственно «Минимум».
В область «Норма расхода ресурсов» шаблона вводим данные о соответствующих нормах расхода того или иного ресурса на производство единицы продукции по каждому виду продукции и ресурса.
Потребность в каждом виде ресурса мы рассчитываем как сумму произведений нормы расхода на объем выпуска по каждому виду продукции.
Например для первого вида ресурса формула будет выглядеть так:
=СУММПРОИЗВ(Выпуск;$C14:$V14),
где диапазон $C14:$V14 содержит данные о норме расхода данного вида ресурса по всем видам продукции.
Соответственно диапазону данных с потребностью в ресурсах на выполнение плана выпуска мы присвоим имя «Потребность».
Теперь, когда мы присвоили всем рабочим диапазонам соответствующие имена, мы можем приступать к формированию параметров поиска решения.
Все ограничения теперь интуитивно понятны и выглядят следующим образом:
Выпуск <= Максимум
Выпуск => Минимум
Выпуск = целое (целое число для штучных единиц продукции)
Потребность <= Наличие
Оптимизируем Прибыль до максимального значения.
Теперь, после того как мы сформировали логику поиска решения остается самое простое, внести параметры поиска решения в соответствующую надстройку.
На вкладке Данные вызываем Надстройку Поиск решения и вносим соответствующие параметры и ограничения.
Ограничения вносим используя кнопку Добавить.
Все. Теперь остается Нажать копку Найти решение и в нашем диапазоне «Выпуск» появится оптимальный план выпуска при котором значение прибыли примет максимально возможное для данных условий значение.
Для удобства в шаблоне присутствует макрос, который при нажатии на кнопку «Найти решение» осуществляет запуск надстройки поиска решений.
Чтобы он корректно работал, необходимо в редакторе VBAустановить ссылку на SOLVER.
Как это сделать вы найдете на листе «Справка» в шаблоне.
Для того чтобы увидеть файл в полном размере нажмите «квадратики» в правом нижнем углу.
Comments are currently closed.