КАФЕДРА  ЭКОНОМИКО-МАТЕМАТИЧЕСКИХ МЕТОДОВ И МОДЕЛЕЙ

     О Т Ч Е Т

о результатах выполнения

компьютерной лабораторной работы №1

«Автоматизированный априорный анализ статистической совокупности

 в среде MS Excel»

Вариант № 12

Задача 1.10.

Менеджер по ценным бумагам намерен разместить 100 000 ф.ст. капитала таким образом, чтобы получать максимальные годовые проценты с дохода. Его выбор ограничен четырьмя возможными объектами инвестиций – A, B, C и O. Объект A позволяет получать 6% годовых, объект B – 8%, объект C – 10%, а объект O – 9% годовых. Для всех четырёх объектов степень риска и условия размещения капитала различны. Чтобы не подвергать риску имеющийся капитал,  менеджер принял решение, что не менее половины инвестиций необходимо вложить в объекты  A и B. Чтобы обеспечить ликвидность не менее 25% общей суммы капитала нужно поместить в объект O. Учитывая возможные изменения в политике правительства, предусматривается, что в объект C следует вкладывать не более 20% инвестиций, тогда как особенности налоговой политики требуют, чтобы в объект A было вложено не менее 30% капитала.

Как распорядиться свободными денежными средствами?

Экономико-математическая модель задачи.

Обозначим через x₁, x₂, x₃, x₄ количество капитала ф.ст., вкладываемого соответственно в объекты A, B, C, O.

Необходимо распорядиться свободными денежными средствами так, чтобы получить максимальные годовые проценты с дохода, следовательно, целевая функция будет иметь вид:

max F(x) = 0,06x₁ + 0,08x₂ + 0,1x₃ + 0,09x₄

Функциональные ограничения:

x₁ + x₂ + x₃ + x₄ = 100000,

x₁ + x₂ ≥ 50000,

x₄ ≥ 25000,

x₃ ≤ 20000,

x₁ ≥ 30000.

Прямые ограничения:

x₁, x₂, x₃, x₄  ≥ 0.

Решение.

1. Запустить Microsoft Excel через меню Пуск. Открыть чистый лист. Создать текстовую форму – таблицу для ввода условий задачи (рис. 1.1).

Рис. 1.1

2. Указать адреса ячеек, в которые будет помещён результат решения (изменяемые ячейки). В данной задаче оптимальные значения компонент вектора X = (X₁, X₂, X₃, X₄) будут помещены в ячейках B3:E3, оптимальное значение целевой функции – в ячейке F4.

3. Ввести исходные данные в форму – таблицу, задав необходимый формат ячеек (рис. 1.2).

Рис. 1.2

4. Ввести зависимость для целевой функции:

♦ курсор в ячейку F4;

♦ нажать на кнопку «Мастер функций», расположенную на панели инструментов;

♦ на экране появится диалоговое окно Мастер функций шаг 1 из 2;

♦ в окне «Категории» выбрать Математические, в окне «Функции» выбрать СУММПРОИЗВ (рис. 1.3);

Рис. 1.3

♦ на экране появится диалоговое окно Аргументы функции;

♦ в строку «Массив1» ввести $B$3:$E$3, а в «Массив2» ввести B4:E4 (рис 1.4);

♦ нажать кнопку «ОК», таким образом в ячейку F4 будет введена формула =СУММПРОИЗВ($B$3:$E$3;B4:E4).

Рис. 1.4

5. Ввести зависимости для ограничений:

♦ ввести в ячейку A8 формулу =СУММ(B3:E3), аналогично предыдущему этапу;

♦ ввести в ячейку A9 формулу =B3+C3;

♦ ввести в ячейку A10 формулу =E3;

♦ ввести в ячейку A11 формулу =D3;

♦ ввести в ячейку A12 формулу =B3.

6. В строке Меню открыть вкладку Сервис. В развернувшемся меню выбрать команду Поиск решения, в результате чего появится диалоговое окно Поиск решения (рис. 1.5).

Рис. 1.5

7. Назначить целевую функцию:

♦ ввести в строку Установить целевую ячейку адрес ячейки $F$4;

♦ выбрать направление целевой функции – Максимальному значению;

♦ ввести в строку Изменяя ячейки адреса искомых переменных $B$3:$E$3.

8. Ввести ограничения:

♦ нажать на кнопку Добавить. В результате появится диалоговое окно Добавление ограничения;

♦ в строке Ссылка на ячейку ввести адрес $A$8;

♦ ввести знак ограничения =;

♦ в строке Ограничение ввести адрес $C$8 (рис. 1.6);

♦ снова нажать на кнопу Добавить, на экране вновь появится диалоговое окно Добавление ограничения;

♦ ввести остальные ограничения задачи по выше описанному алгоритму;

♦ после введения последнего ограничения нажать кнопку «ОК».

Рис. 1.6

На экране появится диалоговое окно Поиск решения с введёнными условиями (рис. 1.7).

Рис. 1.7

9. Ввести параметры для решения ЗЛП:

♦ в диалоговом окне Поиск решения нажать на кнопку Параметры. На экране появится диалоговое окно Параметры поиска решения (рис. 1.8).

Рис. 1.8

♦ установить флажки в окнах Линейная модель (это обеспечит применение симплекс-метода) и Неотрицательные значения  (это обеспечит выполнение прямых ограничений задачи);

♦ нажать на кнопку «ОК». На экране появится диалоговое окно Поиск решения;

♦ нажать на кнопку Выполнить.

Через некоторое время появится диалоговое окно Результаты поиска решения (рис. 1.9) и исходная таблица с заполненными ячейками B3:E3 и ячейка F4 с максимальным значением целевой функции (рис. 1.10).

Рис. 1.9

Рис. 1.10

Полученное решение означает, что максимальные годовые проценты с дохода по инвестициям в размере  8100 ф.ст. менеджер по ценным бумагам получит в том случае, если вложит по 30000 ф.ст. капитала в объект А и O и по 20000 ф.ст. – в объекты B и С.

Задача 2.10.

Фирма получила заказы на выполнение ремонтных работ на пяти объектах (евроремонт пяти квартир). Для выполнения этих заказов фирма располагает шестью бригадами, каждая из этих бригад выполняет один заказ «под ключ». Ниже в таблице приведены оценки времени (в человеко-днях), необходимого бригадам для выполнения всех работ и сдачи объектов заказчикам (исходя из состава  квалификации работников).

Оценки даны бригадирами, и опыт их работы в фирме даёт руководству основания доверять им.

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

Данная задача относится к специальным задачам линейного программирования и является задачей о назначениях.

Экономико-математическая модель задачи в общем виде.

Обозначим  – оценку времени, необходимого i-ой бригаде для выполнения всех работ и сдачи j-ого объекта заказчику;  – выполнение i-ой бригадой всех работ на j-ого объекте ( означает выполнение работ,  означает их невыполнение).

Целевая функция:

min F(x) =

Функциональные ограничения:

, i=1, …, n – условие того, что каждая бригада может отремонтировать только одну квартиру;

, j=1, …, m – условие того, что каждая квартира может быть отремонтирована только одной бригадой.

Прямые ограничения:

≥0, i=1, …, n, j=1, …, m.

Но исходная задача является задачей открытого типа, так как число бригад не равно числу квартир. Чтобы решить, её нужно сначала привести к закрытому типу. Для этого вводим фиктивную квартиру (), потому что число бригад превышает число квартир на 1. ЭММ задачи примет вид:

Целевая функция:

min F(x) = 47x₁₁ + 60x₁₂ + 25x₁₃ + 63x₁₄ + 68x₁₅ + 0x₁₆ +

                  48x₂₁ + 57x₂₂ + 33x₂₃ + 56x₂₄ + 71x₂₅ + 0x₂₆ +

                  45x₃₁ + 53x₃₂ + 20x₃₃ + 62x₃₄ + 61x₃₅ + 0x₃₆ +

                  48x₄₁ + 60x₄₂ + 18x₄₃ + 65x₄₄ + 74x₄₅ + 0x₄₆ +

                  44x₅₁ + 66x₅₂ + 21x₅₃ + 61x₅₄ + 76x₅₅ + 0x₅₆ +

                  42x₆₁ + 54x₆₂ + 29x₆₃ + 55x₆₄ + 69x₆₅ + 0x₆₆ .

Функциональные ограничения:

x₁₁ + x₁₂ + x₁₃ + x₁₄ + x₁₅ + x₁₆ =1,

x₂₁ + x₂₂ + x₂₃ + x₂₄ + x₂₅ + x₂₆ =1,

x₃₁ + x₃₂ + x₃₃ + x₃₄ + x₃₅ + x₃₆ =1,

x₄₁ + x₄₂ + x₄₃ + x₄₄ + x₄₅ + x₄₆ =1,

x₅₁ + x₅₂ + x₅₃ + x₅₄ + x₅₅ + x₅₆ =1,

x₆₁ + x₆₂ + x₆₃ + x₆₄ + x₆₅ + x₆₆ =1;

x₁₁ + x₂₁ + x₃₁ + x₄₁ + x₅₁ + x₆₁=1,

x₁₂ + x₂₂ + x₃₂ + x₄₂ + x₅₂ + x₆₂ =1,

x₁₃ + x₂₃ + x₃₃ + x₄₃ + x₅₃ + x₆₃ =1,

x₁₄  + x₂₄  + x₃₄  + x₄₄ + x₅₄ + x₆₄ =1,

x₁₅ + x₂₅ + x₃₅ + x₄₅ + x₅₅ + x₆₅ =1,

x₁₆ + x₂₆ + x₃₆ + x₄₆ + x₅₆ + x₆₆ =1.

Прямые ограничения:

≥0, i=1, …, n, j=1, …, m.

Решение.

1. Открыть чистый лист Microsoft Excel и создать форму для решения задачи – матрицу распределения заказов.

Для этого резервировать изменяемые ячейки: в блок ячеек B3:G8 вводятся «1». После решения задачи там будет находиться распределение заказов между бригадами.

2. Введение условия , i=1, …, n. Для этого:

♦ курсор в ячейку A3;

♦ нажать на кнопку «Мастер функций», расположенную на панели инструментов;

♦ на экране появится диалоговое окно Мастер функций шаг 1 из 2;

♦ в окне «Категории» выбрать Математические, в окне «Функции» выбрать СУММ;

♦ на экране появится диалоговое окно Аргументы функции (рис. 2.1);

Рис. 2.1

♦ в строку «Число» ввести B3:G3, нажать «ОК»;

♦ копировать формулу из ячейки А3 в ячейки А4:А8.

Введение условия , j=1, …, m. Для этого нужно:

♦ поставить курсор в ячейку B9;

♦ ввести в ячейку B9 формулу =СУММ(B3:B8) по вышеописанному алгоритму;

♦ скопировать формулу из ячейки B9 в ячейки C9:G9.

Таким образом, получим матрицу распределения заказов (рис. 2.2).

Рис. 2.2

3. Ввести исходные данные.

Введём оценки времени, необходимого i-ой бригаде для выполнения всех работ и сдачи j-ого объекта заказчику, в блок ячеек  B12:G17. Введём  условную мощность бригады (в ячейки A12:A17 вводится «1») и  потребность в выполнении заказа на ремонт квартиры (в ячейки B11:G11 – «1») (рис. 2.3).

Рис. 2.3

4. Назначение целевой функции.

Для вычисления значения целевой  функции необходимо зарезервировать ячейку и ввести туда формулу для её вычисления:

♦ установить курсор в ячейку B19. В данную ячейку будет помещаться значение целевой функции после решения задачи;

♦ нажать на кнопку «Мастер функций», расположенную на панели инструментов;

♦ на экране появится диалоговое окно Мастер функций шаг 1 из 2;

♦ в окне «Категории» выбрать Математические, в окне «Функции» выбрать СУММПРОИЗВ;

♦ на экране появится диалоговое окно Аргументы функции;

♦ в строку «Массив1» ввести B12:G17, а в «Массив2» ввести B3:G8 (рис 2.4);

Рис. 2.4

♦ нажать кнопку «ОК», таким образом в ячейку B19 будет введена формула =СУММПРОИЗВ(B12:G17;B3:G8) (рис. 2.5)

Рис. 2.5

В ячейке B19 появится некоторое число, равное произведению «1» на оценки времени, необходимого i-ой бригаде для выполнения всех работ и сдачи j-ого объекта заказчику.

5. В строке Меню открыть вкладку Сервис. В развернувшемся меню выбрать команду Поиск решения, в результате чего появится диалоговое окно Поиск решения (рис. 2.6).

Рис. 2.6

6. Назначить целевую функцию:

♦ ввести в строку Установить целевую ячейку адрес ячейки $B$19;

♦ ввести направление целевой функции – Минимальному значению;

♦ ввести в строку Изменяя ячейки адреса искомых переменных $B$3:$G$8.

7. Ввести ограничения:

♦ нажать на кнопку Добавить. В результате появится диалоговое окно Добавление ограничения;

♦ в строке Ссылка на ячейку ввести адрес $A$3:$A$8;

♦ ввести знак ограничения =;

♦ в строке Ограничение ввести адрес $A$12:$A$17 (рис. 2.7);

Рис. 2.7

♦ снова нажать на кнопу Добавить, на экране вновь появится диалоговое окно Добавление ограничения;

♦ в строке Ссылка на ячейку ввести адрес $B$9:$G$9, ввести знак ограничения =, в строке Ограничение ввести адрес $B$11:$G$11;

♦ нажать кнопку «ОК».

На экране появится диалоговое окно Поиск решения с введёнными условиями (рис. 2.8).

Рис. 2.8

8. Ввести параметры для решения ЗЛП:

♦ в диалоговом окне Поиск решения нажать на кнопку Параметры. На экране появится диалоговое окно Параметры поиска решения (рис. 2.9).

Рис. 2.9

♦ установить флажки в окнах Линейная модель и Неотрицательные значения  (выполнение бригадой заказа принимает значение «1» или «0», т.е. не может быть отрицательной величиной);

♦ нажать на кнопку «ОК». На экране появится диалоговое окно Поиск решения;

♦ нажать на кнопку Выполнить.

Через некоторое время появится диалоговое окно Результаты поиска решения (рис. 2.10) и матрица распределения заказов с заполненными ячейками B3:G8 и ячейка B19 с минимальным значением целевой функции (рис. 2.11).

Рис. 2.10

Рис. 2.11

В матрице распределения заказов содержится схема выполнения бригадами ремонта квартир (1 – выполняет 0 – не выполняет).

Вывод. Минимальное время, затраченное на выполнение работ на всех пяти объектах, составляет 233 человека-дня и будет достигнуто при следующем распределении:

• бригада P₂ выполняет ремонтные работы на объекте 4;

• бригада P₃ выполняет работы на объекте 5;

• бригада P₄ выполняет работы на объекте 3;

• бригада P₅ выполняет ремонтные работы на объекте 1;

• бригада P₆ выполняет все работы на объекте 2.