Определения наиболее оптимального объема выпуска продукции

 

Деревообрабатывающее предприятие СП ООО «Данинвест» выпускает четыре основных вида продукции для изготовления которых используется распиловочное, калибровочное, строгальное и торцовочное оборудование. Количество человеко-часов (ресурсов), необходимых для изготовления одного кубического метра каждого вида продукции приведены в таблице на рисунке 6.1. В этой же таблице указаны: максимально возможное количество человеко-часов для каждого вида оборудования, ограничения, обусловленные спросом на выпуск продукции второго и третьего видов, и прибыль от реализации одного изделия каждого вида. Будем предполагать, что в общем случае прибыль с увеличением выпуска продукции может уменьшаться. Степени влияния объема выпуска на прибыль по каждому изделию также приведены в таблице. Заметим, что если степень влияния равна единице, то увеличение объема выпуска изделия не приводит к уменьшению прибыли. Требуется определить такой объем выпуска продукции, который обеспечивает предприятию наибольшую прибыль.

 

Рисунок 3.1. Нормы расхода ресурсов

Для решения задачи на персональном компьютере с использованием EXCEL необходимо:

1.Ввести исходные данные в ячейки рабочего листа EXCEL;

2.Разметить блоки ячеек, необходимые для моделирования объема выпуска продукции, а также для формирования элементов математической модели и целевой функции;

3.Сформировать на рабочем листе EXCEL элементы математической модели и целевую функцию;

4.Настроить программу "Поиск решения" и выполнить ее.

Ввод исходных данных

Исходными данными для решения задачи определения наиболее прибыльного объема выпуска продукции являются:

-имеющиеся в наличии ресурсы;

-нормы расхода ресурсов на выпуск одного изделия;

-максимальная и минимальная величина спроса на изделия;

-прибыль от реализации одного изделия;

-степень влияния объема выпуска изделия на прибыль.

Для ввода данного в ячейку рабочего листа EXCEL необходимо:

1. Выделить ячейку;

2. Набирать вводимое данное на клавиатуре;

3. Нажать клавишу Enter.

Рабочий лист EXCEL c введенными исходными данными для решения задачи показан на рисунке 3.2.

 

Рисунок 3.2 Разметка блоков ячеек рабочего листа EXCEL

Кроме исходных данных, на рабочем листе EXCEL для решения задачи определения наиболее оптимального объема выпуска продукции необходимо предусмотреть:

1.Блок ячеек "Оптимальный выпуск", в котором будет моделироваться объем выпуска продукции;

2.Блок ячеек "Фактическое использование", в котором будет моделироваться фактическое использование ресурсов;

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

4.Ячейку "Итоговая прибыль", в которой будет моделироваться получение прибыли от реализации всей продукции.

Рабочий лист EXCEL с размеченными блоками ячеек показан на рисунке 6.3. Теперь в этих блоках ячеек можно формировать элементы математической модели и целевую функцию.

 

Рисунок 3.3 Формирование элементов математической модели

 

Элементами математической модели задачи определения оптимального объема выпуска продукции является фактическое использование i-го ресурса.

Перед этим на рабочем листе EXCEL целесообразно блоку ячеек “Оптимальный выпуск”, в котором будет моделироваться выпуск готовой продукции, присвоить имя, например, _Х.

Для формирования математической модели выполним следующие действия:

1. Заполним ячейки блока “Оптимальный выпуск (блок В18:E18) числами 0,01;

2. Выделим первую ячейку блока “Фактически использовано” (ячейка G5);

3. Наведем курсор на кнопку - автосуммирование и щелкнем левой клавишей мыши;

4. Нажмем клавишу Delete;

5. Выделим блок "Оптимальный выпуск" (блок В18:E18);

6. Нажмем клавишу *;

7. Выделим первую цифровую строку блока “Нормы расхода ресурсов на одно изделие” (блок В5:Е5);

8. Активируем строку формул, наведя на нее курсор, и щелкнем левой клавишей мыши;

9. Нажимем одновременно три клавиши “Ctrl”+”Shift”+”Enter”;

10.Скопируем из ячейки G5 формулу {=СУММ(_х*B5:E5)} в остальные ячейки блока “Фактически использовано” (блок G5:G11).

Формируем целевой функции

Для формирования целевой функции выполняем следующие действия:

1. Выделяем первую ячейку блока “Прибыль по изделиям” (ячейка В20);

2. Вводим с клавиатуры формулу=В14*МАКС(В18;0)^В16;

3. Нажмаем клавишу Enter;

4. Скопируем формулу из ячейки В20 на все остальные ячейки блока “Прибыль по изделиям” (блок В20:E20);

5. Выделяем ячейку “Итоговая прибыль” (ячейка G22);

6. Наводим курсор на кнопку - автосуммирование и щелкаем левой клавишей мыши;

7. Нажмаем клавишу Delete;

8. Выделяем блок “Прибыль по изделиям” (блок В20:E20);

9. Нажмаем клавишу Enter.

После формирования элементов математической модели и целевой функции задачи определения наиболее оптимального объема выпуска продукции рабочий лист EXCEL примет вид, показанный на рисунке 3.4.

 

Рисунок 3.5. формирование элементов математической модели

 

Теперь можно приступить к настройке программы “Поиск решения”.

Настройка программы “Поиск решения”

Для настройки программы “Поиск решения” на решение задачи определения наиболее прибыльного объема выпуска продукции выполните следующие действия:

1. Выделяем целевую ячейку “Итоговая прибыль” (ячейка G22);

2. Устанавливаем курсор в строке главного меню на пункте “Сервис” и щелкаем левой клавишей мыши;

3. Устанавливаем курсор на пункте “Поиск решения” меню “Сервис” и щелкаем левой клавишей мыши;

Надо убедиться, что в поле “Установить целевую ячейку” окна диалога программы “Поиск решения” указана ячейка $G$22;и что переключатель установлен на значение “Равной максимальному значению”

 

Рисунок 3.6. Значение «Равное максимальному значению»

 

5. Устанавливаем курсор в поле “ Изменяя ячейки” и щелкаем левой клавишей мыши;

6. Выделяем блок ячеек “Оптимальный выпуск” (блок В18:Е18);

7. Устанавливаем курсор на кнопку “ Добавить” и щелкаем левой клавишей мыши. Появится окно команды “Добавление ограничения”, показанное на рисунке 3.7

 

Рисунок 3.7 Окно команды “Добавление ограничения”,

 

8.Выделяем блок ячеек “Фактически использовано” (блок G5:G11);

9.Оператор сравнения <= уже выбран;

10.Устанавливаем курсор на поле “Ограничение” и щелкните левой клавишей мыши;

11.Выделяем блок ячеек “Наличие ресурсов” (блок F5:F11) и убеждаемся, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке 6.7;

Рисунок 3.8. Окно диалога команды “Добавление ограничения”

 

12.Устанавливаем курсор на кнопку “ Добавить” и щелкаем левой клавишей мыши;

13.Выделяем блок ячеек “Оптимальный выпуск” (блок В18:Е18);

14.Устанавливаем курсор на стрелку прокрутки значений оператора сравнения и щелкните левой клавишей мыши;

15.Устанавливаем курсор на значение, >= и щелкаем левой клавишей мыши;

16.Устанавливаем курсор на поле “Ограничение” и щелкаем левой клавишей мыши;

17.Набераем на клавиатуре цифру 0.

18.Устанавливаем курсор на кнопку “Добавить” и щелкаем левой клавишей мыши;

19.Выделяем ячейку С18;

20.Установливаем курсор на стрелку прокрутки значений оператора сравнения и щелкаем левой клавишей мыши;

21.Устанавливаем курсор на значение >=и щелкаем левой клавишей мыши;

22.Установаем курсор на поле “Ограничение” и щелкаем левой клавишей мыши;

23.Выделяем ячейку С12, окно диалога команды “Добавление ограничения”.

24.Установливаем курсор на кнопку “Добавить”

25.Выделяем ячейку D18;

26.Убеждаемся, что оператор сравнения <=уже выбран;

27.Установливаем курсор на поле “Ограничение” и щелкните левой клавишей мыши;

28.Выделяем ячейку D13.

29.Установливаем курсор на кнопку “Добавить” и щелкаем левой клавишей мыши;

30. Устанавливаем курсор на кнопку “Отмена”

31.Появившееся окно программы “Поиска решения”;

32. Устанавливаем курсор на кнопку “Выполнить” и щелкаем левой клавишей мыши;

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

 

Рисунок 3.9 Решение задачи

 

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

Широкий класс задач математического программирования предполагает наличие условий численности переменных. Для решения таких задач необходимо при формировании ограничений задачи для целочисленных переменных, кроме основных ограничений, добавить ограничения на целочисленных. Это делается с помощью команды “Добавление ограничения” путем указания в поле “ Ограничение” константы “цел”.

В ходе проведенной работы мы установили, что наиболее оптимальный выпуск