1. Составим математическую модель, для которой введем обозначения:
xj – количество выпускаемой продукции j-го типа (j=1,4);
bi – количество располагаемого ресурса i-го вида (i=1,3);
aij – норма расхода i-го ресурса для выпуска единицы продукции j-го типа;
cj – прибыль, получаемая от реализации единицы продукции j-го типа.
Тогда математическая модель будет иметь вид:
2. Создадим форму для ввода исходных данных в Excel (рис.1) [5].
Рис.1. Пример исходной формы
3. Введем исходные данные из табл.1 и зависимости (см. видеозапись "Установление зависимостей"):
•в ячейку Е8 поместим целевую функцию
СУММПРОИЗВ(B4:Е4;B8:Е8);
•в ячейку Е11 поместим функцию расчета трудовых ресурсов
СУММПРОИЗВ(B4:Е4;B11:Е11);
•в ячейку Е12 поместим функцию расчета сырьевых ресурсов
СУММПРОИЗВ(B4:Е4;B12:Е12).
•в ячейку Е13 поместим функцию расчета финансовых ресурсов
СУММПРОИЗВ(B4:Е4;B13:Е13).
4.Осуществим поиск решения командой
•Сервис/Поиск решения (Excel 2003);
•Данные/Поиск решения (Excel 2007).
Если команды Поиск решения нет на панели, то ее следует добавить:
•для Excel 2003 в режиме Сервис/ Надстроики активизировать Поиск решения (рис.2);
|
|
Рис.2. Подключение Поиска решения в Excel 2003
•для Excel 2007 нажав кнопку Offis/Параметры Excel выбрать режим Надстройки и в нижней части окна Управление: Надстройки Excel нажить кнопку Перейти. Появится окно Надстройки, где необходимо активизировать Поиск решения (см. видеозапись "Настройка поиска решений");
4. Вызовем функцию Поиск решения командой Сервис/ Поиск решения (Excel 2003) или Данные/ Поиск решения (Excel 2007). Появится диалоговое окно Поиск решения, в которое вводится:
-целевая ячейка - $F$8;
- изменяемые ячейки - $B$5:$E$5;
- ограничения :
$B$5>=$B$6 $C$5>=$C$6 $D$5>=$D$6 $E$5>=$E$6 |
$F$11<=$H$11 $F$12<=$H$12 $F$13<=$H$13 |
4.Осуществим поиск решения командой Выполнить, получим окно результатов, в котором выберем Тип отчета – Результаты и нажмем кнопку ОК. (см. видеозапись "Поиск решения")
В результате получим в таблице с исходными данными значения искомых переменных, расчет ограничений и целевой функции (рис.3) и лист Excel Отчет по результатам (рис.4).
Рис .3. Расчетные значения по модели
5.Проведем анализ Отчета по результатам, который состоит из трех таблиц:
•Таблица 1 приводит сведения о целевой функции (начальное и окончательное значения).
•Таблица 2 приводит значения искомых переменных, полученных в результате решения задачи.
•Таблица 3 приводит отчет по ограничениям. Для каждой переменной и ограничения приведены:
▪полученные в результате расчетов значения;
▪формула ограничений;
▪статус, которое принимает одно из состояний: «связанное» если ресурс использовался полностью и «не связанное» – когда есть остаток по ресурсу;
▪количество неиспользованного ресурса (показано в последней графе).
Рис. 4. Отчет по результатам
Отчет по результатам позволяет определить запасы ресурсов и может быть использован менеджером для планирования запасов и принятия решения по их управлению. Для более глубокого анализа может быть использован отчет по устойчивости и пределам, который возможно получить в окне результатов поиска решений.
Таким образом, для получения максимальной прибыли следует выпускать продукцию двух видов: прод. 1 в количестве 10 ед. и прод. 3 в количестве 6 ед.