Задача об оптимальном использовании ограниченных ресурсов № 1.10
Задача о назначениях № 2.10

ЗАДАЧА ОБ ОПТИМАЛЬНОМ ИСПОЛЬЗОВАНИИ ОГРАНИЧЕННЫХ РЕСУРСОВ № 1.10
Менеджер по ценным бумагам намерен размесить 100 000 ф. ст. капитала таким образом, чтобы получать максимальные годовые проценты с дохода. Его выбор ограничен четырьмя возможными объектами инвестиций – А, В, С и О. Объект А позволяет получать 6 % годовых, объект В – 8 %, объект С – 10 %, а объект О – 9 % годовых. Для всех четырех объектов степень риска и условия размещения капитала различны. Чтобы не подвергать риску имеющийся капитал, менеджер принял решение, что не менее половины инвестиций необходимо вложить в объекты А и В. Чтобы обеспечить ликвидность, не менее 25 % общей суммы капитала нужно поместить в объект О. Учитывая возможные изменения в политике правительства, предусматривается, что в объект С следует вкладывать не более 20 % инвестиций, тогда как особенности налоговой политики требуют, чтобы в объект А было вложено не менее 30 % капитала.
Как распорядиться свободными денежными средствами?
Решение.
1. Запустим Excel, выбрав Microsoft Excel из подменю Программы главного меню Windows. На чистом листе Excel создадим текстовую форму – таблицу для ввода условий задачи. ( рис. 1.1)
Рис. 1.1
2.Укажем адреса ячеек, в которые будет помещен результат решения (изменяемые ячейки). Обозначим через Х1, Х2, Х3 и Х4 объекты инвестиций А, В, С и О. В данной задаче оптимальные значения компонент вектора Х=( Х1, Х2, Х3, Х4) будут помещены в ячейках В3:Е3, оптимальное значение целевой функции – в ячейке F4.
Ведем исходные данные задачи в созданную форму – таблицу, представленную на рис. 1.2
Рис. 1.2
Введем зависимость для целевой функции в ячейку F4. Для этого воспользуемся «Мастером Функций». В диалоговом окне Мастера Функций выберем категорию Математические, а функцию выберем СУММПРОИЗВ. В строку массив1 введем В$3:Е$3, а в строку массив2 введем В$4:Е$4. (рис. 1.3.)
Рис. 1.3
Введем зависимость для ограничений (рис. 1.4).
Рис. 1.4
Воспользуемся командой Поиск решения (в меню Сервис). И заполним окно Поиск решения как на рис. 1.5, а окно Параметры поиска решения как на рис. 1.6. В окне Поиск решения нажать на кнопку выполнить. Через непродолжительное время появится диалоговое окно Результаты поиска решения и исходная таблица с заполненными ячейками В3:Е3 для значений Хj и ячейка F4 с максимальным значением целевой функции ( рис. 1.7)
Рис. 1.5

Рис. 1.6


Рис. 1.7
Полученное решение означает, что максимальные годовые проценты 8 100 менеджер может получить при вложении 30 000 ф. ст. в объект инвестиций А, 20 000 ф. ст. в В, 20 000 ф. ст. в С и 30 000 ф. ст. в О.
ЗАДАЧА О НАЗНАЧЕНИЯХ № 2.10
Фирма получила заказы на выполнение ремонтных работ на пяти объектах (евроремонт пяти квартир). Для выполнения этих заказов фирма располагает шестью бригадами, каждая из этих бригад выполняет один заказ «под ключ». Ниже в таблице приведены оценки времени (в днях), необходимого бригадам для выполнения всех работ и сдачи объектов заказчикам (исходя из состава и квалификации работников бригады).
Время выпол-
нения, ч
Бригада

Объект 1

Объект 2

Объект 3

Объект 4

Объект 5

Р1
47
60
25
63
68

Р2
48
57
33
56
71

Р3
45
53
20
62
61

Р4
48
60
18
65
74

Р5
44
66
21
61
76

Р6
42
54
29
55
69


Оценки даны бригадирами, и опыт их работы в фирме дает руководству основание доверять им.
Распределить объекты работ между бригадами, чтобы общее количество человекодней, затраченное на выполнение работ на всех пяти объектах, было минимальным.
Решение.
Создадим матрицу назначений по должностям. Для этого выполним резервирование изменяемых ячеек: в блок ячеек С3:G8. Таким образом, резервируется место, где после решения задачи будет находиться распределение рабочих бригад по объектам, обеспечивающее минимальное время на выполнение ремонтных работ.
Введем условия назначения каждой из бригад на ремонт только одного объекта. Для этого в ячейке В3 введем формулу =СУММ(C3:G3). Скопируем данную формулу в остальные ячейки: В4, В5, В6, В7, В8.
Теперь введем условие заполнения всех объектов. Для этого в ячейку С9 введем формулу =СУММ(C3:C8). Скопируем данную формулу в остальные ячейки: D9, E9, F9, G9. В ячейку В9 введем формулу =СУММ(B3:B8), которая пригодится для введения ограничений в Поиске решений.
Введем исходные данные. Осуществим ввод условной мощности бригады ( в ячейки В13:В18 вводится «1»), потребности в ремонте («1» - в С12:G12) и ввод времени, необходимого каждой бригаде для ремонта того или иного объекта (блок С13:G18) (рис. 2. 1).
Рис. 2.1
Назначим целевую функцию. Для этого в ячейку С20 (в данной ячейке будет помещаться значение целевой функции после решения задачи) с помощью Мастера функций введем следующую формулу =СУММПРОИЗВ(C13:G18;C3:G8).
Воспользуемся командой Поиск решения. Заполним открывшиеся окно как на рис. 2.2, а окно Параметры поиска решения как на рис. 2.3



Рис. 2.2
Рис. 2.3
7. В окне Поиск решения нажать на кнопку выполнить. Через непродолжительное время появится диалоговое окно Результаты поиска решения и исходная таблица с заполненными ячейками С3:G8 для значений и ячейка C20 с минимальным значением целевой функции ( рис. 2.4)
Рис. 2.4
Вывод.
Минимальное общее количество 233 человекодней, затраченное на выполнение работ на пяти объектах, будет достигнуто при ремонте 2-ой бригадой – 4-ого объекта, 3-ей – 5-ого объекта, 4-ой – 3 –ого объекта, 5-ой – 1- ого объекта, 6-ой – 2- ого объекта. При этом 1-ая бригада в ремонте данных объектов не участвует.