по состоянию на 25 января 2005 года

<<< Главная страница | < Назад

Оптимизация плана производства в программе Excel

 

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

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

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

Чтобы принять оптимальное решение в той или иной ситуации, экономист должен провести тщательный анализ и перебрать все возможные варианты в рамках определенной экономической модели. Для решения именно таких задач (оптимизации методами линейного и нелинейного программирования) предназначен специальный модуль «Поиск решения» (Solver), который входит в стандартную комплектацию программы MS Excel. На самом деле Solver ¯ это отдельный программный продукт, разработанный совсем не корпорацией Microsoft, а фирмой Frontline Systems (www.solver.com), которая специализируется на экономическом программном обеспечении. Но разработчик согласился бесплатно распространять Solver вместе с программой Excel в пакете MS Office.

Некоторые примеры, приведенные в статье, взяты из документации Microsoft. В составе MS Excel в папке Office\Examples\Solver находится книга с примерами использования поиска решения (Solvsamp.xls). Чтобы применить любой из 6 примеров («Структура производства», «Транспортная задача», «График занятости», «Управление капиталом», «Портфель ценных бумаг» или «Проектирование цепи»), нужно открыть книгу, перейти к нужному листу и выбрать в меню «Сервис» команду «Поиск решения». В приведенных примерах уже подобраны целевая и влияющие ячейки, а также все необходимые ограничения, так что можно самостоятельно во всем разобраться.

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

Упрощенный пример структуры производства включает номенклатуру продукции с искомыми плановыми объемами. Цель производства – максимизация прибыли, из ресурсов учитываются лишь ограничения по комплектующим узлам и деталям.

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

Обычно план по номенклатуре составляется в виде таблицы. Структуру документа можно сначала разметить на бумаге, а можно сразу создавать в электронном виде. Пример из файла документации Microsoft имеет не совсем стандартную таблицу. В нем комплектующие располагаются в столбец, а плановая номенклатура ¯ в строку, хотя в настоящих документах на бумаге номенклатура располагается в столбец. Мы отступили от формальности исключительно ради эффективности визуального восприятия, чтобы таблица удобно располагалась на экране компьютера.

В столбцах А, В и С указаны наименования комплектующих (шасси, кинескоп, динамик, блок питания и электронная плата), запас на складе в штуках, нормы расхода продуктов и расход по плану в штуках (это значение получается путем вычисления). Наименование продукции расположено в строке 8. В строке 9 расположены ячейки искомого плана производства, т.е. указано, сколько телевизоров, стереосистем и акустических систем можно производить. Изменяя содержимое этих ячеек, можно наблюдать изменение потребления ресурсов (колонка «Расход по плану»), изменение прибыли по видам изделий (в отдельных колонках по каждому виду продукции) и общей прибыли в ячейке D18. Именно это значение нам нужно максимизировать, изменяя значения в ячейках D9, E9 и F9 (план производства телевизоров, стереосистем и акустических систем соответственно). Важное условие ¯ расход комплектующих деталей по плану не должен превышать запас на складе, т.е. нельзя произвести телевизоров больше 250 (на складе только 250 кинескопов), стереосистем больше 400 (на складе только 800 динамиков) или акустических систем больше 600 (на складе только 600 электронных плат).

В формулу прибыли на изделие в ячейках D17:F17 входит коэффициент H15, учитывающий уменьшение прибыли с ростом объема. В ячейке H15 содержится значение 0,9, что делает задачу нелинейной. Если изменить H15 на 1,0 (т.е. прибыль не будет зависеть от объема производства) и повторно запустить процесс поиска решения, то задача станет линейной, а оптимальное решение будет другим.

В нашем упрощенном плане производства используется 5 различных комплектующих для 3-х видов изделий. Почти все комплектующие универсальны, т.е. используются для производства разных изделий. Отсюда вытекает проблема: необходимо таким образом распределить ресурсы, чтобы изготовить максимальное количество изделий, т.е. максимально увеличить прибыль. Если все электронные платы использовать для акустических систем, то для производства других видов продукции их не хватит, поэтому все кинескопы останутся на складе, а стоимость произведенной продукции будет минимальной, как и полученная прибыль. После непродолжительных экспериментов, изменяя план производства, мы понимаем, что без помощи компьютерных вычислений, вручную, невозможно определить оптимальное количество телевизоров, стереосистем и акустических систем, производство которых обеспечит максимальную прибыль. Придется прибегнуть к помощи аналитического модуля «Поиск решения».

Запуск программы осуществляется через меню «Сервис», где находится команда «Поиск решения». Если ее там нет, то нужно зайти в меню «Сервис–Надстройки» и активировать там надстройку «Поиск решения», предварительно инсталлировав необходимые файлы из дистрибутива MS Office.

В диалоговом окне программы «Поиск решения» необходимо настроить экономико-математическую модель. Другими словами, нужно указать целевую ячейку, диапазон изменяемых ячеек и ограничения. В нашем случае целевой ячейкой является D18 (прибыль), изменяемыми ячейками ¯ D9, E9 и F9 (количество производимой продукции). Ограничений два: во-первых, расход не должен превышать запас комплектующих, во-вторых, количество каждого вида продукции должно быть больше либо равно нулю, т.е. неотрицательным.

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

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

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

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

В поле «Предельное число итераций» нужно установить количество промежуточных вычислений. К сожалению, это количество ограничено 32 767, так что действительно серьезных задач решить в программе Solver не удастся (для этого предназначены другие программные продукты).

Поле «Относительная погрешность» служит для задания точности (допустимой погрешности), с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число в интервале от 0 до 1. Чем меньше относительная погрешность, тем выше точность результатов.

Поле «Допустимое отклонение» служит для задания допуска на отклонение от оптимального решения.

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

Флажок «Линейная модель» в параметрах работы алгоритма служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.

Флажок «Неотрицательные значения» позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле «Ограничение» диалогового окна «Добавить ограничение».

Флажок «Автоматическое масштабирование» служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине, например максимизация прибыли в процентах по отношению к вложениям, исчисляемых в млн руб.

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

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

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

Дополнительно можно установить один из двух алгоритмов оптимизации: метод Ньютона или сопряженных градиентов (переключатель «Метод поиска»).

После изменения (в случае необходимости) вышеописанных параметров можно сохранить настройки поиска («Сохранить модель») или просто согласиться с ними для выполнения текущей задачи без сохранения на будущее («ОК»). Теперь программу «Поиск решения» можно запускать на исполнение. Команда «Выполнить» служит для запуска поиска решений поставленной задачи.

Если поиск решения успешно завершен, в диалоговом окне «Результаты поиска решения» выводится одно из следующих сообщений:

Решение найдено. Все ограничения и условия оптимальности выполнены.

Это означает, что все ограничения соблюдены с установленной точностью и найдено заданное значение целевой ячейки.

Поиск свелся к текущему решению. Все ограничения выполнены.

Значит, относительное изменение значения в целевой ячейке за последние 5 итераций стало меньше установленного значения параметра «Сходимость» в диалоговом окне «Параметры поиска решения».

Если поиск не может найти оптимальное решение, то в диалоговом окне «Результаты поиска решения» выводится одно из следующих сообщений:

Поиск не может улучшить текущее решение. Все ограничения выполнены.

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

Поиск остановлен (истекло заданное на поиск время).

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

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

Значения целевой ячейки не сходятся.

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

Поиск не может найти подходящего решения.

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

Поиск остановлен по требованию пользователя.

Это говорит о том, что нажата кнопка «Стоп» в диалоговом окне «Текущее состояние поиска решения» после прерывания поиска решения в процессе выполнения итераций.

Условия для линейной модели не удовлетворяются.

Установлен флажок «Линейная модель», однако итоговый пересчет порождает такие значения, которые не согласуются с линейной моделью. Это означает, что решение недействительно для данных формул листа. Чтобы проверить линейность задачи, нужно установить флажок «Автоматическое масштабирование» и повторно запустить задачу. Можно снять флажок «Линейная модель» и снова запустить задачу.

При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения.

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

Мало памяти для решения задачи.

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

Другой экземпляр Excel использует SOLVER.DLL.

Видимо, запущено несколько копий Excel, в одном из которых используется файл Solver.dll.

В нашем случае поиск решения прошел успешно и на экране появилось окошко «Результаты поиска решения» с выбором, что делать с результатами поиска: сохранить или восстановить исходные значения. Кроме этого, можно создать отчеты 3-х разных типов: «Результаты», «Устойчивость» и «Пределы».

Отчет «Результаты» состоит из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о наложенных ограничениях.

Отчет «Устойчивость» используется для создания отчета, содержащего сведения о чувствительности решения к малым изменениям в формуле модели или в формулах ограничений. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел. В случае нелинейных моделей отчет содержит данные для градиентов и множителей Лагранжа. В отчет по нелинейным моделям включаются ограниченные затраты, фиктивные цены, а также диапазоны ограничений.

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

Итак, после того как мы создали необходимые отчеты и нажали кнопку «Сохранить найденное решение», страница с планом производства приобретает новый вид. План выпуска различных изделий принимает оптимальные значения, а прибыль становится максимальной при ограничениях ресурсов на складе. Полностью израсходованы динамики в количестве 800 шт. и электронные платы в количестве 600 шт. Ограничения по этим ресурсам сдерживают дальнейшее увеличение прибыли плана по портфелю продукции, так что нужно делать дополнительные заказы у поставщиков. В то же время запасы на складе шасси, кинескопов и блоков питания почти на 30 % выше плановой потребности, т.е. остались лишние комплектующие.

Оптимальный план производства в программе Solver вычисляется практически мгновенно и сразу появляется на экране компьютера директора фирмы или экономиста. Профессионал может грамотно использовать эту информацию, рассмотреть несколько вариантов и определить самый оптимальный.

 

АНАТОЛИЙ АЛИЗАР

 

<<< Главная страница | < Назад

pravo.kulichki.ru ::: pravo.kulichki.com ::: pravo.kulichki.net

2004-2015 Республика Беларусь