1. Пример решения оптимизационной задачи при помощи инструмента «Поиск решения» в Ms Excel

Пусть фабрика имеет в своем распоряжении на месяц определенное количество ресурсов: рабочую силу, сырье, оборудование. Эти ресурсы у предприятия имеются в количестве: рабочая сила - 80 (чел./дней); сырье - 480 кг., время работы оборудования - 130 станко/часов. Фабрика может выпускать ковры четырех видов. Информация о количестве единиц каждого ресурса, необходимых для производства одного ковра каждого вида и доходов, получаемых предприятием от реализации единицы каждого вида товара, приведена в таблице 1.1.1

Таблица 1.1.1

Информация о цене выпускаемой продукции и используемых ресурсах

Ресурсы	Нормы расхода ресурсов на единицу изделия				Наличие ресурсов
	Ковер «Лужайка»	Ковер «Силуэт»	Ковер «Детский»	Ковер «Дымка»
Труд(чел./дней),	7	2	2	6	80
Сырье(кг.)	5	8	4	3	480
Оборудование(ст./час.)	2	4	1	8	130
Цена ед. изделия (тыс. руб.)	3	4	3	1

Требуется найти такой план выпуска продукции, при котором общая стоимость продукции будет максимальной.

Обозначим через X₁, X₂, X₃, X₄ искомое количество ковров каждого вида.

1. Т огда целевая функция оптимизационной модели в нашем случае будет выглядеть, как указано в формуле 1.1.1:

f(X) = 3* X₁+ 4*X₂ +3*X₃ +X₄  max, (1.1.1)

2. Все имеющиеся в задаче ограничения запишутся в виде системы уравнений вида 1.1.2:

Ограничения:

7 *X₁+2*X₂+2*X₃+6*X₄ 80

5*X₁+8*X₂+4*X₃+3*X₄ 480 (1.1.2)

2*X₁+4*X₂+*X₃+8*X₄ 130

X₁ ,X₂ ,X₃ ,X₄ 0

X₁ ,X₂ ,X₃ ,X₄- целые

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

Рассмотрим порядок решения оптимизационной задачи.

Введем все необходимые исходные данные для решения задачи как показано на рисунке 1.1.1.

Рис. 1.1.1. Исходные данные для решения ЗЛП

В ячейки B4: E4 введем цены за единицу ковров каждого вида.

В ячейки B7:E7 введем нормы использования ресурса «труд» на единицу ковра каждого вида.

В ячейки B8:E8 введем нормы расхода ресурса «сырье» на единицу ковра каждого вида.

В ячейки B9:E9 введем нормы расхода ресурса «оборудование» на единицу ковра каждого вида.

В ячейки H7 : H9 введем значения ограничений на имеющиеся ресурсы, соответственно на «труд», «сырье», «оборудование».

В нашей задаче искомые оптимальные значения вектора X = (X₁, X₂, X₃, X₄ ) будут помещены в ячейки ВЗ:ЕЗ, оптимальное искомое значение целевой функции — в ячейку F4.

Далее введем все необходимые для расчетов формулы. В ячейку F4 введем формулу для целевой функции. Для ее ввода воспользуемся стандартной математической функцией СУММПРОИЗВ. В качестве первого аргумента данной стандартной функции укажем массив: B3:E3, а в качестве второго – B4:E4.

В ячейки: F7:F9 введем формулы, необходимые для ввода ограничений, в которых выполняется расчет необходимого количества каждого вида ресурса на всю производственную программу. Необходимые для решения задачи данные, в режиме формул представлены на рисунке 1.1.2.

Рис. 1.1.2. Используемые данные в режиме формул

Для поиска оптимального решения запустим инструмент Ms Excel «Поиск решения», выполнив команду «Сервис / Поиск решения».

На экран будет выведено диалоговое окно «Поиск решения» (см. рис.1.1.3).

Рис. 1.1.3. Вид диалогового окна «Поиск решения»

• В диалоговом окне «Поиск решения»:

• укажите адрес целевой ячейки - F4.

• Включите флажок «Равной» в положение - «максимальному значению».

• В поле «Изменяя ячейки» укажите искомый диапазон ячеек – B3:E3.

• Введите поочередно все используемые ограничения. Ограничения можно задавать в произвольном порядке. Для ввода ограничения щелкните по кнопке «Добавить». Появится диалоговое окно «Добавление ограничения» (см. рис 1.1.4).

Рис.1.1.4. Диалоговое окно «Добавление ограничения»

• В поле «Ссылка на ячейку» укажите адрес ячейки. Далее выберите нужный знак сравнения из раскрывающегося списка. В поле «Ограничение» укажите адрес ячейки, в которой находится ограничивающее число или введите само ограничивающее число.

• Щелкните по кнопке «Добавить» для ввода следующего ограничения.

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

• В появившемся диалоговом окне «Параметры поиска решения» (см. рис. 1.1.5) включите флажки «Линейная модель» и «Неотрицательные значения», выберите «ОК» для выхода.

Рис. 1.1.5. Вид диалогового окна «Параметры поиска решения»

• Запустите на решение поставленную задачу, выбрав кнопку команды «Выполнить» в диалоговом окне «Поиск решения». На экран будет выведено диалоговое окно «Результаты поиска решения» (см. рис. 1.1.6).

Рис. 1.1.6. Вид диалогового окна «Результаты поиска решения»

• Установите в этом окне переключатель “сохранить найденное решение”. При этом в изменяемые ячейки (B3:E3) и в целевую ячейку будут выведены найденные значения объемов производства (см. рис. 1.1.7) и их общей стоимости.

Рис. 1.1.7. Результат «Поиска решения» на листе рабочей книги

• Выберите в диалоговом окне “Результаты поиска решения” в поле “Тип отчета” – значение: “Результаты” и нажмите кнопку ОК. Перед текущим листом появится лист с названием “Отчет по результатам” (см. рис. 1.1.8).

Рис. 1.1.8. Содержимое отчета по результатам «Поиска решения»

1.2. Задания для выполнения контрольной работы по теме «Оптимизационные модели в менеджменте»

Требования по выполнению и оформлению первого задания контрольной работы

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

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

• титульный лист индивидуальной работы, на котором должны быть указаны: название ВУЗа, название кафедры, название учебной дисциплины, тема индивидуальной работы, вариант индивидуальной работы, группа и фамилия исполнителя, фамилия преподавателя, ведущего практические занятия по данному курсу.

• текст поставленной задачи;

• описание особенностей данного класса оптимизационных задач;

• записанную в формализованном виде оптимизационную модель задачи;

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

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

• «Отчет по результатам поиска решения» с колонтитулом.

Отчет по индивидуальной работе сдать преподавателю в установленный срок на проверку в печатном виде.

Варианты заданий индивидуальной работы

Вариант № 1

Начальнику отдела сбыта компании «Копрессор-К» поставлена задача рассмотреть текущие способы перевозки производимых холодильных установок и предложить альтернативные варианты, направленные на минимизацию затрат. У компании имеется три основных центра производства холодильных установок в городах: Казань, Киров, Ижевск. Ежемесячный выпуск продукции составляет: Казань - 120 единиц; Киров - 40 единиц; Ижевск - 90 единиц.

У данной компании имеется три основных центра сбыта холодильных установок в городах: Лениногорск, Нижнекамск, Бугульма. Потребности центров сбыта в холодильных установках следующие: Лениногорск – 100 штук; Нижнекамск – 80 штук; Бугульма – 70 штук. Данные по транспортным расходам при перевозке единицы изделия из центра производства в центр сбыта приведены в таблице 1.2.1:

Таблица 1.2.1

Стоимость перевозки единицы изделия (руб.)

Стоимость перевозки из центра производства в центр сбыта (руб.)	Центры сбыта
Центры производства:
Казань	30	14	16
Киров	18	8	22
Ижевск	12	6	14

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

Вариант № 2

Производственная компания располагает тремя производственными линиями и двумя площадями первичного складирования. Изделия складируются партиями на площадях первичного складирования и далее отправляются покупателям.

Ежедневно три производственные линии – «А», «Б» и «В» производят соответственно: 20, 50 и 20 партий товара соответственно. Ограничения по зонам хранения таковы: 1 зона - 60 партий и 2 зона - 30 партий. В таблице 1.2.2 дано время, затрачиваемое на передвижение товаров от соответствующей линии производства в соответствующую зону хранения.

Таблица 1.2.2

Время, затраченное на передвижение товаров в минутах

Зона складирования	Производственные линии
	«А»	«Б»	«В»
1	7	4	6
2	4	3	5

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

Вариант № 3

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

Таблица 1.2.3

Валовая прибыль от реализации единицы продукции (руб.)

Ожидаемая валовая прибыль от единицы изделия (руб.)	Центры сбыта
Центры производства:
Казань	2000	3600	3400
Киров	3200	4200	2800
Ижевск	3800	4400	3600

Задача заключается в том, чтобы определить оптимальные маршруты перевозок холодильных установок, максимизирующие общую валовую прибыль за месяц. Месячные объемы производства: Казань - 120 единиц; Киров - 40 единиц; Ижевск - 90 единиц. Потребности центров сбыта: Лениногорск - 100 единиц; Нижнекамск - 80 единиц; Бугульма - 70 единиц.

Вариант № 4

Автомобильная компания имеет три завода в Лос-Анжелесе, Детройте и Новом Орлеане и два распределительных центра в Денвере и Майами. Объемы производства заводов компании в следующем квартале составляют соответственно: 1000, 1500 и 1200 автомобилей. Ежеквартальная потребность составляет 2300 и 1400 автомобилей. Расстояние в километрах между заводами и распределительными центрами приведено в таблице 1.2.4.

Таблица 1.2.4

Расстояние между заводом и распределительным центром (км.)

Лос-Анжелес	1000	2690
Детройт	1250	1350
Новый Орлеан	1275	850

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

Вариант № 5

По плану производства требуется выпустить 360 единиц продукции вида «П1», 340 единиц продукции вида «П2», 500 единиц продукции вида «П3» на двух взаимозаменяемых станках: «С1» и «С2» разной производительности. Фонды работы времени каждого из станков, нормы затрат времени и стоимость изготовления продукции по видам приведены в таблице 1.2.5. Требуется распределить производство изделий по станкам таким образом, чтобы стоимость изготовления всей продукции была наименьшей.

Таблица 1.2.5

Нормы затрат времени (мин) и стоимость ед. продукции (тыс. руб.)

Станки	Производительность и стоимость изготовления продукции			Фонд времени
	П1	П2	П3
С1	3 ; 7	4; 12	6; 8	180
С2	7; 9	5; 10	8; 13	180

Вариант № 6

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

Таблица 1.2.6

Время обработки детали по операциям на каждом из агрегатов(мин.)

Агрегаты	Операции
	1	2	3	4
1	5	3	5	7
2	6	2	4	5
3	4	3	6	6
4	3	4	4	3

Вариант № 7

Фирма рекламирует свою продукцию с использованием четырех средств: телевидения, радио, газет и афиш. Из различных рекламных экспериментов, которые проводились в прошлом, известно, что эти средства приводят к увеличению прибыли соответственно на 10, 3, 7 и 4 рубля в расчете на 1 рубль, затраченный на рекламу.

Распределение рекламного бюджета по различным средствам подчинено следующим ограничениям:

а) полный бюджет не должен превосходить 500 000 рублей;

б) следует расходовать не более 40% бюджета на телевидение и не более 20% бюджета на афиши;

в) вследствие привлекательности для подростков радио на него следует расходовать, по крайней мере, половину того, что планируется на телевидение.

Сформулируйте задачу распределения средств по различным источникам как задачу линейного программирования и решите ее.

Вариант № 8

Фирма планирует рекламную кампанию нового продукта. Отведенный на эти цели бюджет составляет 120 000 руб. Предполагается, что тираж рекламных объявлений должен составить не менее 800 млн. экземпляров; объявления будут размещены в шести изданиях. Каждое издание имеет свой тираж (таблица 1.2.13). Стоимость размещения рекламы в каждом выпуске для каждого издания и тираж выпуска приведены в таблице 1.2.13.

Таблица 1.2.13

Информация об изданиях, тиражах и стоимости выпуска (руб.)

№ издания	Стоимость размещения рекламы в одном выпуске издания, руб.	Тираж одного выпуска, млн экз.
1	1474,2	9,9
2	1244,1	8,4
3	1131,0	8,2
4	700,7	5,1
5	530,0	3,7
6	524,4	3,6

_

Необходимо распространить рекламу с минимальными издержками при следующих ограничениях:

1. в каждом издании реклама должна пройти в шести или более выпусках;

2. на любое издание может быть истрачено не более одной трети отпущенной суммы;

3. общая стоимость рекламы в третьем и четвертом изданиях не должна превышать 75 000 руб.

Вариант № 9

Рассмотрим задачу о загрузке самолета грузами пяти типов. Вес, объем, а также стоимость единицы груза каждого типа приведены в таблице 1.2.18.

Таблица 1.2.18

Информация о весе, объеме, стоимости грузов

Груз i	Вес единицы груза (тонны)	Объем единицы груза (куб.м.)	Стоимость единицы груза (тыс. руб.)
1	5	1	4
2	8	8	7
3	3	6	6
4	2	5	5
5	7	4	4

Максимальная грузоподъемность и объем самолета - 112 тонн и 109 куб.м. соответственно. Найдите набор грузов, обеспечивающий максимальную стоимость груза.

Вариант № 10

В связи с закрытием неприбыльной производственной линии на предприятии образовался избыток производственных мощностей. Менеджер рассматривает возможность использования свободной производственной линии для производства одного или более из трех видов продуктов: Х1, Х2 или Х3. В таблице 1.2.28 приведены затраты производственного времени на единицу каждого из этих продуктов.

Таблица 1.2.28

Временные затраты по видам обработки на различные изделия

Вид обработки	Затраты времени на единицу продукции (час.)
	Х1	Х2	Х3
Фрезерование	8	2	3
Токарная	4	3	0
Шлифовальная	2	0	1

Доступные ресурсы времени обработки в неделю: фрезерование – 800ч., токарная обработка – 480 ч., шлифовальная – 320 ч. Маркетологи оценивают возможности реализации так: можно реализовать весь производимый недельный объем продукции видов Х1 и Х2, а продукции вида «Х3» - можно реализовать не более 80 единиц в неделю.

Ожидаемая прибыль за единицу продукции определенного вида: «Х1» - 200 рублей, «Х2» - 60 рублей, «Х3» - 80 рублей.

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

Определите, в каких пределах может изменяться прибыль по видам продуктов без изменения найденного оптимального распределения.

Второе задание контрольной работы