Решение задач оптимизации средствами программ Excel

Предприятие имеет запасы 4-х видов ресурсов (мука, жиры, сахар, финансы), с которых производится 2 виды продуктов (хлеб и батон). Известны:

                    нормы расходов ресурсов на производство единицы продукции;

                    запасы ресурсов;

                    цены продуктов;

                    спрос на хлеб.

Найти оптимальный план производства, при котором прибыль от реализации произведенной продукции должна быть максимальный. При чем прибыль равна выручка – затраты = цена ед. продукции * план ед. продукции – план ед. продукции^0,7

Экономико-математическая модель.

o                      Найти план (количество хлеба и батонов) такой, чтобы

o                      Прибыль =0,99*Хлеб+1,21*Батон –Хлеб^0,7-Батон^0,7 - mах

o                      При ограничениях:

0,6*Хлеб+0,5*Батон<=120
0,05*Хлеб+0,08*Батон<=70
0,2*Хлеб+0,6*Батон<=65
0,2*Хлеб+0,24*Батон<=50
Хлеб, Батон>=0

Реализация в Excel.

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

                    в столбце «Использовано» в каждую ячейку вводим формулу вычисления количества использованных ресурсов: =СУММПРОИЗВ(Норма; План);

                    в ячейку с Прибылью вводим формулу =СУММПРОИЗВ(Цена; План)- Хлеб^0,7-Батон^0,7.

Запускаем программу Поиск решений командой Данные/Анализ/Поиск решения (В Excel 2007) Сервис/Поиск решения (В Excel 2003 и ниже). В полях Установить целевую ячейку, Изменяя ячейки, Ограничения вводим соответствующие адреса ячеек. Не забываем фиксировать в окне Параметры поиска решений переключатель на позицию Неотрицательные значения. Нажимаем кнопку Выполнить и в появившемся окне Результаты поиска решения выводим отчет по устойчивости.

Анализ результата.

Оптимальный план производства (151,9; 57,7) обеспечивает максимальную прибыль в размере 169,5 д. ед.

Множители Лагранжа указывают на увеличения прибыли при увеличении дефицитного  запаса ресурсов на 1 ед.[5]

Список литературы

Гутовская Г.В., Шалагинова Н. И. Использование Excel для реше­ния финансово-экономических задач // Информатика и образование. 2003. №3-5.

Курицкий Б. Я. Поиск оптимальных решений средствами Excel 7.0 в примерах. СПб.: BHV-Санкт-Петербург, 1997.

Леоненков А. Решение задач оптимизации в среде Excel, БХВ – Санкт-Петербург, 2005.

Решение задач прикладной информатики в менеджменте туризма на Excel / Ф.А. Гурьянова, Л.А. Родигин, А.И. Сеселкин, М.: Советский спорт, 2003. – 112 с., ил.

Сидоров М. Е. Решение задач оптимального планирования в таб­лицах Excel//Информатика и образование. 2001. №1.

[1] Кузьмичев А. И., Медведев М. Г. – Математическое программирование в Excel: Начал. пособ. – К. Вид-во Європ. Ун-ту, 2005 – 320 с.

[2] Кузьмичев А. И., Медведев М. Г. – Математическое программирование в Excel: Начал. пособ. – К. Вид-во Європ. Ун-ту, 2005 – 320 с.

[3] Кузьмичев А. И., Медведев М. Г. – Математическое программирование в Excel: Нач. пособ. – К. Вид-во Європ. Ун-ту, 2005 – 320 с.

[4] Кузьмичев А. И., Медведев М. Г. – Математическое программирование в Excel: Нач. пособ. – К. Вид-во Європ. Ун-ту, 2005 – 320 с.

[5] Кузьмичев А. И., Медведев М. Г. – Математическое программирование в Excel: Нач. пособ. – К. Вид-во Європ. Ун-ту, 2005 – 320 с.