Програмні засоби роботи зі структурованими документами. Оптимізація пошуку рішення у фінансово-економічних задачах в середовищі Excel: Методичні вказівки до лабораторного заняття № 4

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ № 4

Тема:  ПРОГРАМНІ ЗАСОБИ РОБОТИ ЗІ СТРУКТУРОВАНИМИ ДОКУМЕНТАМИ
Тема заняття: ОПТИМІЗАЦІЯ ПОШУКУ РІШЕННЯ У ФІНАНСОВО - ЕКОНОМІЧНИХ ЗАДАЧАХ В СЕРЕДОВИЩІ EXCEL

Мета:  Навчитися оптимізувати рішення у фінансово-економічних задачах в середовищі Excel

Забезпечення заняття: персональний комп’ютер, принтер, операційна система Windows, табличний процесор Excel, надбудова Excel Поиск решения.

Вимоги до оформлення звіту з лабораторної роботи: Звіт з лабораторної роботи повинен містити: дату, тему, план заняття та короткий виклад завдань, результати роботи, відповіді на запитання.

Критерії оцінювання: Робота виконана в повному обсязі з оформленням звіту та захистом оцінюється в 5 балів, при незначних помилках - 4 бали, при значних помилках - 3 бали, робота виконана з грубими помилками, частково або незахищена - 2 бали.

Зміст заняття

1.  Теоретичні відомості що до задач оптимізації.

2.  Постановка задачі.

3.  Створення економіко - математичної моделі задачі.

4.  Формування вхідних та проміжних даних до задачі.

5.  Знаходження оптимального значення прибутку та оптимального розподілу коштів по об’єктам інвестування за допомогою надбудови  Поиск решения процесора Excel.

6.  Графічний аналіз результатів.

7.  Вивід результатів лабораторної роботи до друку.

Завдання та порядок їх виконання

Завдання 1. Теоретичні відомості що до задач оптимізації

Законспектувати: Технологія пошуку рішення яка використовується для рішення задач оптимізації це підбір даних в одних комірках таблиці Excel  на основі обмежень, накладених на інші комірки. При цьому повинна бути визначена цільова функція, яка пов’язана формулою з комірками в яких відбувається підбір параметрів. Задачу оптимізації пошуку рішення  можна сформулювати у загальному виді у такий спосіб.

Знайти змінні x₁,x₂, …, x_n які задовольняють системі нерівностей або рівнянь, які являються обмеженням для задачі оптимізації

 j_i (x₁, x₂, x₃, …, x_n) <= b_i,  i = 1, 2, …, m

і при цьому досягає максимуму ( мінімуму чи заданого значення ) цільова функція, тобто

Z = f (x₁,x₂, …, x_n ) ® max (min, задане значення)

Умови позитивності змінних, якщо вони є  теж, входять в обмеження: x₁>=0, x₂>=0, x₃>=0, …, x_n>=0.

Завдання 2. Постановка задачі (Записати у зошит).

Задача. Розглянемо задачу про управління портфелем активів. Нехай перед деяким інвестором стоїть проблема прийняття рішення про вкладення капіталу який у нього є. Набір характеристик потенційних об’єктів для інвестування, мають умовні імена від A до F, задаються у таблиці 1. Прийняття рішень залежить від максимального прибутку  вкладених коштів інвестором.

Таблиця 1. Параметри умовних об’єктів

Для  прийняття рішень при придбанні активів повинні бути дотримані певні  умови: 1. Сумарний об’єм капіталу, який повинен бути вкладений, складає $100 000. 2. Доля вкладених коштів в один об’єкт не може перевищувати $25 000.  3. Половина або більша половина коштів від сумарного об’єму капіталу повинна бути вкладена у довгострокові активи  (>=50 000) зі строком погашення 2016 -2018 р. 4. В активи, які мають надійність менше ніж 4 бали, вкладення коштів не повинно перевищувати $30000.

Завдання 3. Створення економіко - математичної моделі задачі

Позначено вкладені кошти в активи фірм як Xa, Xb, Xc, Xd, Xe, Xf. Тоді сумарний прибуток можна представити у такому виді: P= 0,055*Xa+ 0,06*Xb+0,08*Xc+0,075* Xd+0,055* Xe+0,07* Xf. Обмеження за умовами задачі: 1. Обмеження на всю суму коштів: Xa+Xb+ Xc+ Xd+ Xe+ Xf <=100000. 2. Обмеження на розмір долі коштів одного об’єкта: Xa<=25000, Xb<=25000, Xc<=25000, Xd<=25000,  Xe<=25000, Xf <=25000. 3. Обмеження для довгострокових активів:

Xb+Xc>=50000. 4.Обмеження для ненадійних об’єктів: Xc+Xd<=30000.5. Умова на невід’ємність змінних які потрібно знайти: Xa>0, Xb>0, Xc>0, Xd>0,  Xe>0, Xf >0.

Завдання 4. Формування вхідних та проміжних даних до задачі

1.  У середовищі Excel на першому Листі введіть вхідні дані як на рисунку 1, це поки що не оптимальні значення. Оптимальні знайдемо пізніше.

2.  У комірку G 3 (може бути будь - яка вільна комірка) уведіть формулу цільової функції:  = 0,055*A3 +  0,06*B3+ 0,08*C3 + 0,075* D3 + 0,055* E3 + 0,07* F3

3.   У комірку D7 уведіть формулу для розрахунку обмеження для відповідних даних:  =A3+B3+ C3+ D3+ E3+ F3, у комірку D8 уведіть другу формулу для обмеження =B3+ C3 у коміркуD9 уведіть третю формулу =C3+ D3.

Рисунок 1.

Завдання 5. Знаходження оптимального значення прибутку та оптимального розподілу коштів по об’єктам інвестування за допомогою надбудови  Поиск решения процесора Excel:

1. Активізуйте команду Поиск решения із Сервису.

2. У діалоговому вікні Поиск решения введіть комірку з цільовою функцією і установіть
перемикач для Максимального значення. Адресу комірки $G$3 з цільовою функцією записати у зошит.

3. Визначить комірки для зміни $A$3:$F$3 та запишіть їх у зошит.

4.  Додайте обмеження (кнопка Добавить): $A$3<=25000, $A$3>0, $B$3<=25000, $B$3>0, $C$3<=25000, $C$3>0, $D$3<=25000,$D$3>0, $E$3<=25000,$E$3>0, $F$3 <=25000, $F$3 >0; D7<=100000; D8>=50000; D9<=30000.

5. Після введення даних  клацніть по кнопці Выполнить.Переглянете результати у таблиці і зробіть висновки. Результати занести у зошит.

6. Закрийте вікно Результаты поиска решения (установивши перемикач Сохранит найденное решение), клацнув по кнопці ОК.

7. Результати збережіть у папці Група*Прізвище на диску з найбільшим вільним об’ємом у файлі ОптимізаціяПрізвище.

Завдання 6. Графічний аналіз результатів

1.  Побудуйте гістограму (рис. 2) залежності Обсягу оптимальних інвестицій від умовних об’єктів. Збережіть результати у файлі ОптимізаціяПрізвище.

Рисунок 2.

Завдання 7. Вивід результатів лабораторної роботи до друку

Контрольні  питання

1. Що таке технологія пошуку рішення?

2. Як можна сформулювати задачу оптимізації пошуку рішення  у загальному виді?

3. Навіщо потрібно використовувати кнопку  Добавить у вікні Поиск решения.?

4. Що таке цільова функція та її призначення?

5. Чи можна вводити довільні значення у комірки A3:F3, та що означають дві крапки між комірками?

6. Проаналізуйте знайдений результат?