Практические занятия с применением программы Excel для принятия оптимального управленческого решения
Задача 1 (Ассортиментная задача)
Составить модель оптимального плана выпуска продукции для цеха кондитерской фабрики. Виды выпускаемой продукции (М), виды основного сырья (П) и его запасы, нормы расхода сырья на единицу, уровни прибыли приведены в таблицах. Рассчитать план и провести его анализ.
| Виды сырья | Расход сырья на единицу продукции | Общий запас сырья, ед. | ||
| М1 | М2 | М3 | ||
| П1 | 2 (а11) | 3 (а12) | 1 (а13) | 303 (b1) |
| П2 | 4 (а21) | 2 (а22) | 3 (а23) | 275 (b2) |
| П3 | 3 (а31) | 1 (а32) | 4 (а33) | 200 (b3) |
| Уровень прибыли на единицу продукции | 24 (С1) | 20 (С2) | 28 (С3) |
Для их производства используется основные виды ресурсов (сырья) трёх видов, условно названных П1, П2, П3 (в ед.).
Расход каждого ресурса на производство единицы продукции является заданной величиной, определяется по рецептуре и обозначается символами а11, а12, …, а32, а33, где а – норма расхода, первая подстрочная 1, 2, 3 – номер ресурса, вторая подстрочная 1, 2, 3 – номер ассортиментной группы конфет.
Наличие каждого ресурса для производства всех групп конфет принимается как известная величина и обозначается символами b1, b2, b3.
Прибыль на продукция также принимается как известная величина и обозначается символами С1, С2, С3.
Перечисленные параметры являются известными величинами и выражаются в единых единицах измерения, кроме прибыли. Прибыль или другой какой-либо показатель, являющийся критерием оптимальности, выражается в единицах измерения дохода (например, прибыли), получаемого от производства единицы продукции в денежном или ином выражении.
Поскольку решение задачи заключается в поиске такого плана производства, который обеспечивал бы в принятых условиях наибольший доход, принимаются те величины, которые являются неизвестными и обозначающими количества каждой группы конфет, включаемых в план производства: х1 для М1; х2 для М2; х3 для М3.
Экономико-математическая модель в символическом виде
Система ограничений: 
Целевая функция (суммарный доход) 
Условия неотрицательности переменных 
Символическая модель, наполненная численной информацией, будет иметь следующий вид:
Система ограничений: 
Целевая функция (суммарный доход) 
Условия неотрицательности переменных
Вычислительному процессу оптимизационных задач предшествует построение математической модели и наполнение ее соответствующей информацией. Математическая модель, имея символическое содержание, определяет необходимый объем информации для полноценного решения задачи.
При решении задач можно использовать процедуры, которые реализованы в надстройке Excel Поиск решения.