Рекомендації до практичних робіт Лабораторна робота 1 на тему Використання статистичних функцій MS Excel для розв’язування задач прогнозування

РЕКОМЕНДАЦІЇ ДО ПРАКТИЧНИХ РОБІТ

Лабораторна робота № 1.2

Тема: Використання статистичних функцій MS Excel для розв’язування задач прогнозування.

Мета: Отримати навички використання засобів прогнозування та аналізу даних в MS Excel.

Порядок виконання роботи

1. Створити на робочому листі табличного процесора MS Excel таблицю 20 Надати робочому листу ім’я Вхідні дані.

Таблиця 20

2. Визначити товар з найдорожчою ціною (використовуючи функцію ДМАКС).

3. Визначити найдешевшу закупівельні ціни серед придбаних партій товару  в березні місяці 2011 року (функція ДМИН). Для цього треба  створити допоміжну таблицю, яка б включала умову.

4. Визначити середню закупівельну ціну товарів, які постачались фірмами “Аванта” та “Орфей” (функція ДСРЗНАЧ).

5. Визначити загальну суму залишку товару по кожній фірмі використовуючи функцію СУММЕСЛИ, (у вікні функціі в поле діапазон вказати діапазон розміщення таблиці без поля «Дата отримання»; в полі крітерій ввести адресу комірки, яка містить назву фірми; в полі діапазон суммування ввести діапазон комірок, що містить дані про залишок товарів разом із заголовком стовбчика).

6. Визначити загальний залишок товарів, які постачались фірмою “Гера” (функція БДСУММ).

7. Створити допоміжну таблицю на листі 2, де потрібно спрогнозувати закупівельну ціну товару для кожної фірми на 2012 рік (функція ТЕНДЕНЦИЯ), відповідно до запланованої кількості товару.

8. Розрахувати ПДВ для кожного товару.

9. За числовими даними, представленими на робочому листі Вхідні дані, побудувати зведену таблицю та розмістити її на листі Зведена таблиця ПДВ. До проекту зведеної таблиці висуваються наступні вимоги:

-  у категорії Стовпчик розмістити Дата отримання;

-  у категорії Рядок розмістити Код товару;

-  у полі даних розмістити суму по полю Сплата ПДВ, Кількість.

10. На листі 3 створити копію таблиці з листа Вхідні дані, де спрогнозувати значення залишку товару за допомогою функції ПРЕДСКАЗ .

11. На листі 3 спрогнозувати значення кількості товару за допомогою функції РОСТ.

12. Дати відповіді на питання:

- Для чого і яким чином можна застосовувати статистичні функції в аналізі діяльності фірми?

- Чим відрізняються функції ТЕНДЕНЦИЯ і ПРЕДСКАЗ?

- Поясніть особливості роботи з функціями прогнозування.

- Поясніть особливості роботи з функціями по обробці списків даних.

- Які відмінності у роботі функцій ДМИН, ДМАКС, ДСРЗНАЧ, БДСУММ від МИН, МАКС, СРЗНАЧ, СУММвідповідно?

13. Виконати прогнозування значення Y для кількох нових значень Х:

Лабораторна робота № 3.1.

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

Мета: Оволодіти практикою застосування вбудованих функцій табличного процесора MS Excel для розрахунку ефективності капіталовкладень та амортизації обладнання.

Зауваження: Робота проводиться із застосуванням фінансових функцій Excel. Виклик функцій відбувається або через пункт меню Вставка- Функция або Формула – Вставка функции, далі вибирається розділ фінансові функції.

Порядок виконання роботи

1. Розв’язати задачу: При якій річній ставці банку  вигідно надати кредит  підприємству в сумі 5 000 000 грн., якщо керівництво підприємства зобов’язується повернути борг через рік – 2 000 000.грн., через два роки – 1 900 000 грн., через три роки -  1600 000 грн.?

2. Розробити форми подання вхідної та вихідної інформації у вигляді таблиці (рис. 1).

3. В комірку В6 введемо довільне значення річної облікової ставки, наприклад 8%.

4. За допомогою функції ЧПС розрахувати чистий поточний об’єм вкладу в комірці В7:=ЧПС(B6;B2:B4).

5. Для автоматизації заповнення таблиці у комірці, що містить назви строків надання кредиту, ввести слова "рік", "роки", "років" в залежності від  кількості років за допомогою функції ЕСЛИ.

=ЕСЛИ(B5=1;"рік";ЕСЛИ(И(B5>=2;B5<=4);"роки";"років"))

6. Визначте чи вигідно буде при даній річній ставці банку надати кредит  підприємству. Для цього уведіть у комірку В8 наступну умову:

=ЕСЛИ(B1<B7;”Вигідно банку надати кредит підприємству”; ЕСЛИ(В7=В1;”Варіанти рівносилі”;”Банку невигідно надати кредит підприємству”)).

7. Застосувати засіб Підбір параметра  (пункт меню Данные) (Рис. 2) для визначення оптимальної річної відсоткової ставки і проаналізувати, яка ефективність надання кредиту.

В результаті дії команди  Підбір параметра було розраховано річну відсоткову ставку для суми займу 5000000 грн. на термін 3 роки при змінних величинах щорічних виплат, яка становить 5%. Це значення автоматично з’явиться в комірці В6.

8. Розв’язати задачу: Підприємство бере кредит 100 000 грн. Необхідно розрахувати розміри основних платежів, сплати за відсотками загальної щорічної сплати та залишку боргу (таблиця 1.) на прикладі позики 100 000 грн. строком на 5 років при річній ставці 3%, застосувавши функції ПРПЛТ та ОСПЛТ.

Таблиця 1

9. Для визначення розміру грошових коштів на рахунку наприкінці двох років, якщо фізична особа вкладе по 300 грн. щомісячно, поточна сума внеску 7200 грн. при річній ставці 6%, застосуйте фінансову функцію БС. Формула =БС(6%;2;300*12;7200) повертає значення 15505,97 грн. 

10. Для визначення розміру річної відсоткової ставки за один період виплат,  якщо необхідно отримати 8000 грн. на протязі 7 років при щомісячній сплаті 150 грн., застосуйте фінансову функцію СТАВКА. Формула =СТАВКА(7*12;-150;8000) повертає значення розміру щомісячної відсоткової ставки в 1%, а для розрахунку річної відсоткової ставки значення 1% поможемо на 12 і отримаємо значення 12%.

11. Припустимо, що ви погодилися на купівлю кондомініуму за 120 000 $. Протягом наступних 5 років ви очікуєте отримати 25000$, 27000$, 35000$, 38000$ та 40000$ чистого рентного прибутку. Формула: =ВСД(-120000;25000;27000;35000;38000;40000) повертає внутрішню швидкість обороту, яка становить 11%. Якщо бар’єрна ставка дорівнює 10%, можна розглядати купівлю кондомініуму як привабливе вкладення. (Дані розмістити в окремих комірках, під час застосування формули, звертатись до адрес комірок).

12. Припустимо, що ви погодилися на купівлю кондомініуму за 120 000 $. Протягом наступних 5 років ви очікуєте отримати 25000$, 27000$, 35000$, 38000$ та 40000$ чистого рентного прибутку. Фінансова ставка дорівнює 10%, а ставка реінвестування - 8%. Використовуючи формулу =МВСД(-120000;25000;27000;35000;38000;40000;10%;8%), ми отримаємо модифіковану внутрішню швидкість обороту, яка дорівнює 10%.

13. Припустимо, що необхідно визначити амортизацію приладу з початковою вартістю 5000 грн, яка має час життя 5 років (60 місяців) і ліквідну вартість 100 грн. Формула =ДДОБ(5000;100;60;1) повідомляє, що амортизація методом двократного обліку за перший місяць складає 166,67 грн.

14. Припустимо, що ви купили обладнання на суму 15000 грн. в кінці першого кварталу поточного року і це майно через 5 років буде мати ліквідну вартість 2000 грн. Щоб визначити амортизацію цього майна за наступний рік (з 4 по 7 квартал його використання), введіть формулу = ПУО(15000;2000;20;3;7). Амортизація за цей період складе 3670,55 грн.

15. Для розрахунку величини амортизації обладнання за 10 років     експлуатації загальною початковою вартістю 340000 дол.США та     остаточною вартістю 10000 дол.США методом рівномірної амортизації застосуйте наступну формулу = АПЛ(340000;10000;10), яка повертає значення амортизації за кожен рік у сумі  33000 грн.

16. Для розрахунку величини амортизації по рокам обладнання за 10 років експлуатації загальною початковою вартістю 340000 дол.США та остаточною вартістю 10000 дол.США методом постійного обліку амортизації застосуйте функцію АСЧ. У таблиці 2. наведені формули і результати обчислення.

Таблиця 2.

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

Контрольні завдання

1. Визначити розмір грошових коштів на рахунку наприкінці 3 років, якщо фізична особа вкладе по 200 грн. щомісячно, загальна сума внеску 7800 грн. при річній ставці 9% за умови, що відсотки нараховуються що місяця.

2. Визначити розмір річної відсоткової ставки за один період виплат, якщо необхідно отримати 8000 грн. на протязі 7 років при щомісячній сплаті 150 грн.

3. Розрахувати величину амортизації обладнання за 10 років експлуатації загальної початкової вартістю 340000 грн. та остаточною вартістю 1000 грн. методом рівномірної амортизації.

4. Визначити розміри основних платежів по рокам на прикладі позики 100000 грн. строком на 5 років при річній ставці 3%.

5. Визначити розміри сплати по відсоткам загальної щорічної сплати на прикладі позики 100000 грн. строком на 5 років при річній ставці n% (n – номер студента за списком у журналі групи).

6. Розрахувати чистий поточний об’єм вкладу при річній відсотковій ставці 10%, щомісячній сплаті 400 грн. строком на 7 років.

7. Визначити кількість періодів виплат (у роках) кредиту розміром 30000 грн. при річній ставці 12%, щомісячній сплаті 380 грн.

8. Визначити розмір щомісячної сплати кредиту, розміром 30000 грн. при річній відсотковій ставці 9%, строк сплати кредиту 7 років.

 Лабораторна робота № 3.2

Тема: Застосування кореляційно-регресійного аналізу в табличному процесорі MS Excel для визначення параметрів функціональної залежності між результативним фактором та факторами-показниками при обґрунтуванні бізнес-плану створення нової  структурної одиниці

Мета: оволодіти методикою застосування кореляційно регресійного аналізу в табличному процесорі MS Excel для визначення параметрів функціональної                       

Порядок виконання роботи

Розв’язати задачу: визначити тісноту зв’язку між результативним показником - прибуток від реалізації та кожним з показників-факторів (торгова площа, чисельність робітників, індекс інфляції, вартість основних засобів, власний капітал), використовуючи функцію КОРРЕ (статистична функція). Записати формулу залежності між ними та параметри цієї залежності  за допомогою функції ЛИНЕЙН (статистична функція).  Необхідні дані наведені в таблиці (Рис. 3). Результати проілюструвати  відповідними графіками.

1. Створити на робочому листі подану в лабораторній роботі таблицю, значення факторів якої будуть використовуватися в подальшому для проведення кореляційно-регресійного аналізу. На робочому листі таблиця займе комірки А1:G21.

2. Для розрахунку коефіцієнтів кореляції та визначення ступеня зв’язку між результативним показником (фактичний розмір прибутку від реалізації) та окремими факторами (торгова площа, чисельність працівників, індекс інфляції, вартість основних засобів, власний капітал) побудуйте на цьому ж робочому листі таблицю (Рис.4).

На робочому листі таблиця займе комірки A24:D29. Розрахувавши коефіцієнт кореляції визначаємо за допомогою функції ЕСЛИ ступінь зв’язку.

Усі коефіцієнти кореляції (К) оцінюються за такими критеріями:

• К<0,5 - слабка залежність (фактор можна далі не досліджувати, оскільки він є не впливовим на результативний показник);

• 0,5<K<0,7 - середній зв’язок (варто досліджувати вплив фактора на результативний показник далі);

• K>0,7 - зв’язок сильний (фактор обов’язково включають до подальшого дослідження).

3. Для перевірки лінійності зв’язку між прибутком реалізації та чисельністю працівників, між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів (відібрані ті фактори, які суттєво пов’язані з результативним показником) застосуйте статистичну функцію =ЛИНЕЙН(B2:B21;D2:D21;1;1) , вигляд у вікні Майстра функцій:

Після натискання кнопки Готово у комірці B34 з’явиться тільки перша величина з масиву параметрів лінійної регресії - значення коефіцієнта а. Для того, щоб на робочому листі MS Excel з’явилися всі величини зазначеного вище масиву, необхідно за допомогою мишки виділити блок комірок B34:C38, поставити курсор в кінець рядка формул та одночасно натиснути комбінацію клавіш Ctrl+Shift+Enter. Після цих дій у блоці комірок B34:C38 з’являться всі параметри лінійної регресії, значення яких представлені в  таблиці 3.

Таблиця 3.

4. Виходячи з одержаних параметрів лінійної регресії, запишіть рівняння Y=0,274X+28,71, яке описує лінійний зв’язок між прибутком, одержаним від реалізації, та чисельністю працівників.

5. Розрахуйте теоретичні значення Y згідно з функцією Y=0,274X+28,71 та порівняємо їх з експериментальними. Для цього побудуйте на робочому аркуші табличного процесора таблицю 4.

Таблиця 4.

На робочому листі табличного процесора ця таблиця займає блок комірок A39:C59.

6. За даними таблиці побудуйте діаграму у вигляді графіка з зображенням різниці між Y-теоретичним та Y-експериментальним за допомогою майстра діаграм MS Excel.

7. Для перевірки лінійності зв’язку між прибутком реалізації та індексом інфляції, між прибутком реалізації та вартістю основних засобів, слід повторити наведене дослідження у пункті 3, 4, 5. Аналогічно, побудуйте таблиці розрахунку теоретичних значеньрозмірівприбутку, виходячи з одержаних параметрів лінійної регресії. Побудуйте за результатами досліджень діаграми.

8. Тепер дослідіть лінійність зв’язку між результативним показником - прибутком реалізації та сукупністю факторів: чисельність працівників, індекс інфляції та вартістю основних засобів. А також обрахуйте коефіцієнти лінійного рівняння виду Y=a₁X₁+a₂X₂+a₃X₃+b та параметри лінійної множинної регресії за допомогою статистичної функції ЛИНЕЙН, яка буде мати такий синтаксис = ЛИНЕЙН(B2:B21;D2:F21;1;1). Вона поверне масив параметрів лінійної множинної регресії, значення яких представлені в таблиці 5.

Таблиця 5. 

9. Виходячи з одержаних параметрів лінійної регресії, запишіть рівняння Y= а₁₁*X₁- а₁₂*Х₂+ а₁₃*Х₃+ а₁₄, яке описує лінійний зв’язок між прибутком, одержаним від реалізації, та факторами: чисельністю працівників, індексом інфляції та вартістю основних засобів. Розрахуйте теоретичні значення Y згідно з функцією Y= а₁₁*X₁- а₁₂*Х₂+ а₁₃*Х₃+ а₁₄ та порівняйте їх з експериментальними. Для цього побудуйте на робочому аркуші таблицю 6.

Таблиця 6.

10. За даними таблиці побудуйте діаграму у вигляді графіка з зображенням різниці між Y- теоретичним та Y-експериментальним за допомогою Майстра діаграм MS Excel.

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

Контрольне завдання

Провести кореляційно-регресійний аналіз зв’язку зміни   балансового прибутку підприємства та факторів, що його ймовірно обумовлюють, за даними таблиці 7.

       Таблиця 7.

Лабораторна робота № 3.3

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

Мета: Оволодіти методикою проведення імітаційного та оптимізаційного моделювання засобами   табличного процесора MS Excel

Зауваження: В роботі застосовується команда Підбір параметрів, яка викликається через пункт меню Данные

Порядок виконання роботи

Розв’язати задачу:змоделювати вплив окремих факторів (ціни реалізації, обсягу товарообороту, середньої торгівельної надбавка та витрат обігу) на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис.грн. Використати засіб Підбір параметра. Необхідні дані (значення економічних показників підприємства) наведені в таблиці 8. Побудувати результативну таблицю та розробити три варіанти сценарію: оптимістичний, песимістичний та реалістичний, кожен з яких зображує реальні зміни показників, які впливають на прибуток підприємства.

Таблиця 8.

1. Спочатку проаналізуйте вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Змінити назву робочого аркуша з "Лист1" на "Імітаційне моделювання", а назву робочого аркуша "Лист2" на "Оптимізаційне моделювання".

2. На робочому аркуші Імітаційне моделювання побудувати  таблицю показану на Рис. 6, значення та формули для розрахунків взяти з таблиці 8.

Таблиці надати заголовок: Вплив ціни реалізації на зміну чистого прибутку торгівельного підприємства.

3. Встановити курсор в комірці В8 і змінити ціну реалізації, за допомогою команди Підбір параметра (пункт меню Данные) встановивши значення чистого прибутку 60 тис.грн встановити в комірці В18. У діалоговому вікні команди, слід зазначити абсолютні адреси комірок та значення, які представлені на Рис. 7.

Після натискання в кнопки ОК, програма Підбір параметра змінить значення ціни реалізації з 5,00 грн. на 5,18 грн. в комірці В8 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю результати своєї роботи (таблиця 9).

Таблиця 9.

Вплив ціни реалізації на зміну чистого

прибутку торговельного підприємства

4. Далі проводимо дослідження, яким чином впливає обсяг товарообороту на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Побудуйте таблицю початкових значень економічних показників, аналогічну, таблиці 8, починаючи з 23 рядка. При створенні таблиці використовувалися числові дані та формули (у  комірці В28 записана формула =В26*В27/100; у комірці В26 - значення 5000; у комірці В31 - формула =В32*В26/100; у комірці В33 - формула =В28-В29; у комірці В35 - формула =В33*(100-В34)/100). Також зверху таблиці ввести заголовок: Вплив обсягу товарообороту на зміну чистого прибутку торгівельного підприємства. Результати розрахунків приведені в таблиці 10.

Таблиця 10.

Вплив обсягу товарообороту на зміну чистого

прибутку торговельного підприємства

5. Встановити курсор в комірці В26 і активізувати команду Підбір параметра. Встановити курсор в комірці В35, значення чистого прибутку 60 тис.грн, відповідно змінивши обсяг товарообороту (комірка В26). У діалоговому вікні зазначаються абсолютні адреси комірок та значення подані на Рис. 8.

Після натискання кнопки ОК програма Підбір параметра змінить значення обсягу товарообороту з 5000 грн. на 5181 грн. в комірці В8 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю результати своєї роботи (таблиця 11).

Таблиця 11.

Вплив обсягу товарообороту на зміну чистого

прибутку торговельного підприємства

6. Далі необхідно дослідити, яким чином впливає середня торговельна надбавка на зміну чистого прибутку торговельного підприємства від 42 до 60 тис.грн. Побудуйте таблицю початкових значень економічних показників, аналогічну першій таблиці 8, починаючи з 40 рядка. При створенні таблиці використовуйте числові дані та формули (у  комірці В43 записана формула =В41*В42; у комірці В45 - формула =В43*В44/100; у комірці В46 - формула =В47+В48; у комірці В48 - формула =В49*В43/100; у комірці В50 - формула =В45-В46; у комірці В52 - формула =В50*(100-В51)/100). Таблиці надати заголовок: Вплив середньої торгівельної надбавки на зміну чистого прибутку торговельного підприємства. Результати розрахунків приведені в таблиці 12.

Таблиця 12.

Вплив середньої торгівельної надбавки на зміну

чистого прибутку торговельного підприємства

7. Необхідно встановити курсор на комірку В44 та викликати команду Підбір параметра, для того щоб встановити в комірці В52 значення чистого прибутку 60 тис. грн, відповідно змінивши значення середньої торговельної надбавки (адреса комірки В44). У діалоговому вікні слід зазначити абсолютні адреси комірок та значення показані на рис. 9.

Після натискання кнопки ОК, програма Підбір параметра змінить значення середньої торгівельної надбавки з 25,00% на 25,51 % в комірці В52 для встановлення значення чистого прибутку в 60 тис. грн. та запише в таблицю 13. результати своєї роботи.

Таблиця 13.

Вплив середньої торгівельної надбавки на зміну

чистого прибутку торговельного підприємства

8. Далі проводимо дослідження, яким чином впливають витрати обігу на зміну чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. Для цього побудуйте таблицю початкових значень економічних показників, аналогічну таблиці 8, починаючи з 57 рядка. При створенні таблиці використовувалися числові дані та формули (у  комірці В60 записана формула =В58*В59; у комірці В62 - формула =В60*В61/100; у комірці В63 - значення 1190; у комірці В65 - формула =В66*В60/100; у комірці В67 - формула =В62-В63; у комірці В69 - формула =В67*(100-В68)/100).  Також зверху таблиці ввести заголовок: Вплив витрат обігу на зміну чистого прибутку торгівельного підприємства. Результати розрахунків представлені в таблиці 14.

Таблиця 14.

Вплив витрат обігу на зміну

чистого прибутку торговельного підприємства

9. Встановити курсор на комірку В63 та викликати команду Підбір параметра, для того щоб встановити в комірці В69 значення чистого прибутку 60 тис.грн., відповідно змінивши значення середньої торгівельної надбавки (адреса комірки В63. У діалоговому вікні слід зазначити абсолютні адреси комірок та значення показані на Рис. 10.

Після натискання ОК програма Підбір параметра змінить значення витрат обігу з 1190 на  1164 в комірці В63 для встановлення значення чистого прибутку в 60 тис.грн. та запише в таблицю 15 результати своєї роботи.

Таблиця 15.

Вплив витрат обігу на зміну

чистого прибутку торговельного підприємства

10. Враховуючи попередні розрахунки та результати програми Підбір параметра, можна зробити висновок, що для збільшення чистого прибутку торгівельного підприємства від 42 до 60 тис. грн. необхідно вибрати одне з наступних рішень:

• збільшити ціну реалізації з 5,00 грн. до 5,18 грн.;

• збільшити обсяг товарообороту з 5000 до 5181 тис. грн.;

• збільшити середню торговельну надбавку на 0,51 % (25,51-25,00=0,51);

• зменшити витрати обігу з 1190 до 1164.

11. Для дослідження впливу всіх факторів одночасно (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку підприємства від 42 до 60 тис. грн. проведемо оптимізаційне моделювання за допомогою програми Пошук рішення (пункт меню Данные). Перейти на робочий аркуш з назвою Оптимізаційне моделювання та створити таблицю (рис.11). Ввести заголовок: Вплив множини факторів на зміну чистого прибутку торговельного підприємства.

12. Завантажимо програму Пошук рішення. В діалогове вікно програми (Рис. 12) ввести цільову функцію - чистий прибуток (комірка $В$18); керовані змінні - середня ціна реалізації (комірка $В$8), обсяг товарообороту (комірка $В$9), середня торгівельна надбавка (комірка $В$10), витрати обігу (комірка $В$12); поставити умови: рівень змінних витрат £ 10.8 ($В$15£10.8), прибуток від реалізації ³ 60 ($В$16³60). 

Для додавання обмежень необхідно натиснути кнопку Додати, та у вікні конструктора обмежень (рис. 13) ввести відповідні абсолютні адреси комірок, значення, оператори порівняння.

Програма Пошук рішення буде виконувати оптимізацію при наступних параметрах (їх можна переглянути або змінити, натиснувши кнопку Параметри). На екрані з’явиться вікно Параметри пошуку рішення (рис. 14).

Після заповнення діалогового вікна Пошук рішення слід натиснути кнопку Виконати. Через декілька секунд з’явиться вікно Результати пошуку рішення (рис. 15), в якому вибрати опцію Зберегти шукане рішення, та натиснути кнопку ОК.

Після зазначених операцій значення факторів - керованих змінних - в таблицях дещо змінилося. Так, середня торгівельна надбавка та середня ціна реалізації залишилися незмінними (25 % та 5 грн, відповідно); а, обсяг товарообороту збільшився до 5102,86 та змінні витрати обігу зменшилися до 551,1. Результати оптимізації представлені в таблиці 16.

Таблиця 16.

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

Програма Пошук рішення видає результати проведеної оптимізації у вигляді трьох звітів: звіт по результатах (рис. 16.), звіт по стійкості (рис. 17) та звіт по межах (рис. 18.), які автоматично розташовуються на окремих робочих аркушах. Видача звітів відбувається при їх замовленні у вікні Результати пошуку рішення.

13. Самостійно провести імітаційне та оптимізаційне моделювання  впливу  факторів (ціна реалізації, обсяг товарообігу, середня торгівельна надбавка, витрати обігу) на зміну чистого прибутку торговельного підприємства від 42 до 80 тис. грн., а також при ставці податку на прибуток відповідно нормам поточного року.

14. Зберегти результати на сервері.

Практична робота № 4.1

Тема: Оптимізація портфелю цінних паперів.

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

Зауваження: в практичній роботі застосовується команда Поискрешения, яка завантажується за допомогою пункту меню Данные.

Порядок виконання роботи

1. Виконати формування оптимального портфелю з максимізацією дохідності при обмеженому риску, який вимірюється дисперсією портфелю і мінімізацією ризику при заданій величині дохідності.

2. За допомогою табличного процесора Excel побудувати табличну модель. Вхідні дані для формування портфелю цінних паперів наведено в таблиці 21:

• В клітинку С6 увести дохідність без ризикових активів – 6%;

• В клітинку С7 увести дохідність ринку – 15%;

• В клітинку G6 увести значення дисперсії ринку 3%;

• Діапазон клітин А10:А13 заповнити назвами акцій компанії, діапазон клітин В10:В13 – даними по Бета акцій Bi, С10:С13 – даними по залишковій дисперсії акцій Vi;

• В клітини Е10:Е14 занести процентні долі кожного цінного паперу  в складі портфелю. В клітині Е16 обчислюється сума долей паперів в портфелі. Вона повинна дорівнювати 100%.

В діапазоні клітин F10:F14 розрахувати портфельні Бета для кожного паперу з врахуванням його долі в портфелі.

3. Це добуток елементів векторів Бета і Доля: Bpi=Wi*Bi

4. Визначити в клітинці F16 Бета портфелю (Bp) як суму скалярного добутку даних векторів.

5. Обчислити Долю дисперсії кожного паперу в портфелі в діапазоні клітин G10:G14, як добуток квадратів долей паперів у портфелі і вхідних даних залишкової дисперсії Vi. Vpi=Wi2*Vi

6. Обчислити загальну портфельну дисперсію Vp в клітині G18. Дохідність портфелю – в клітинці Е18. Дисперсія і Дохідність є цільовими (критеріальними) клітинками у варіантах задач вибору найкращого (оптимального) портфелю цінних паперів. Формули розрахунку і числові значення вхідних даних подано в таблиці 22.

Результати розрахунків наведені в таблиці 23.

7. Скласти оптимальні портфелі акцій вручну. Скопіювати таблицю 23 на новий лист.

8. Сформувати портфель з максимальною дохідністю і мінімальним ризиком: змінюючи питому вагу акцій в портфелі, інакше дані в діапазоні Доля, добитись максимальної дохідності портфелю в клітинці Е18 і одночасного мінімального ризику (дисперсії) в клітинці G18. Одночасно слідкувати щоб сума ваги портфелю в клітинці Е16 завжди дорівнювала 100%.

9. Сформувати портфель з максимальною дохідністю і обмеженим ризиком: задати довільне числове значення ризику портфеля (дисперсії), змінювати питому вагу акцій в портфелі, інакше дані в діапазоні Доля, добитися максимальної дохідності портфелю в клітинці Е18 і одночасно не перевищувати заданого значення ризику (дисперсії) в клітинці G18. Контролювати щоб сума ваги (долей) портфелю в клітинці Е16 завжди дорівнювала 100%. 

10. Сформувати портфель з заданим значенням дохідності і мінімальним ризиком: задати довільне числове значення дохідності портфеля, змінювати питому вагу акцій в портфелі, інакше дані в діапазоні Доля, добитися мінімального значення ризику (дисперсії) в клітинці G18 і одночасно дохідності портфелю в клітинці Е18, яке не перевищує задане. Сума ваги портфелю в клітинці Е16 завжди повинна дорівнювати 100%.

11. Провести комп’ютерний пошук портфелю з максимальною дохідністю і обмеженим ризиком. Скопіювати таблицю 3 на новий лист.

12. Скласти оптимальний план за допомогою програми оптимізації, для чого виконати команду меню Поиск решения. У діалоговому вікні Поиск решения, в полі цільова клітинка, потрібно задати адреси діапазону Доля шуканої ваги активів в портфелі Е10:Е14. В полі Ограничения увести 3 рядки умов, які обмежують область допустимих рішень даної задачі:

• Перше обмеження забороняє від’ємні значення долей активів у портфелі: Е10:Е14>=0;

• Друге обмеження вимагає, щоб сума долей активів у портфелі складала 100%: Е16=1;

• Третя нерівність обмежує портфельний ризик на рівні 7,1%.

Діалогове вікно Поиск решения с координатно-математичною  моделлю задачі вибору оптимального портфелю показано на рис. 63.

Зведені параметри моделі подані в таблиці 24.

Таблиця 24.

13. Провести комп’ютерний пошук портфелю з заданою дохідністю і мінімальним ризиком. В рядках 21:29 збережені дві моделі оптимізації портфелів: модель максимізації дохідності і модель мінімізації ризику.

14. Для рішення потрібно завантажити модель мінімізації і завантажити команду меню Сервис - Поиск решения. У діалоговому вікні оптимізатора потрібно натиснути кнопку Параметры. З’явиться вікно Загрузка модели (рис. 64). У вікні Загрузка модели необхідно увести діапазон моделі мінімального ризику D21:D29 і натиснути кнопку ОК.

15. Далі з’явиться вікно для підтвердження завантаження нової моделі.  Для підтвердження натиснути ОК.

16. На екрані з’явиться вікно невірної формули обмежень. Натиснути кнопку ОК. Перевірити модель у вікні Поиск решений. У діалоговому вікні Поиск решения з моделлю мінімізації ризику вибрати кнопку Добавить (рис.65).

17. Добавити обмеження, як показано на малюнку 66. і натиснути кнопку ОК.

18. Для запуску оптимізатора у вікні Поиск решения потрібно натиснути кнопку Выполнить. У рядку повідомлень з’являється повідомлення Постановка задачи ..., що вказує на початок роботи програми. По закінченню розрахунків з’являється діалогове вікно Результаты поиска решения(рис. 67).

19. У полі Тип отчета задати тип звіту, який записується на окремий лист книги:

• звіт Результаты використовується для створення звіту, який складається з цільової клітини в списку впливових клітин моделі, їх вхідних і кінцевих значень, а також формул обмежень і додаткових відомостей про накладенні обмеження;

• звіт Устойчивость використовується для створення звіту, який містить відомості про чутливість рішення до малих змін в формулі моделі або в формулах обмежень. В звіт по нелінійним моделям включаються обмежені затрати, фіктивні ціни, а також діапазони обмежень;

• звіт Пределы використовується для створення звіту, який складається з цільової клітини і списку впливових клітин моделі, їх значень, а також нижніх і верхніх обмежень.

20. Вибрати Тип отчета Результаты. Встановити прапорець Сохранить найденное значение і натисніть кнопку ОК, в результаті буде створено автоматично на новому листі від назвою Отчет по результатам як показано в таблиці 25.

Зберегти результати на сервері у власній папці. Закрити програму

Практична робота № 4.2

Тема роботи: Технологія оптимізації, оптимальний бізнес-план, план по продукції.        

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

Визначення проблеми: На виробничому підприємстві „Електроприлад” склад готової продукції пустує. В цей же час цехи заповнені незавершеною продукцією. Немає реалізації, прибутку, зарплати, розвитку. Був складений план виробництва, в якому не були враховані обсяги запасів матеріалів і комплектуючих на складах і обмежені можливості постачальників вузлів і деталей.

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

Завдання: В ТП Excel скласти план по номенклатурі згідно таблиці 1.

Найменування продукції розташовані в рядку 8. В рядку 9 розташовані клітини плану виробництва.

Мета виробництва – максимально можливий прибуток, який обчислюється в клітині D18.

Потрібно визначити: планову кількість продукції і прибуток.

Вихідними даними для розрахунків є норми витрат комплектуючих на виробництво одного виробу. Вони задаються матрицею D11:F15.

Планові затрати комплектуючих на виробництво всіх типів виробів обчислюються у стовпчику. С як сума добутку планової кількості продуктів на питомі норми затрат комплектуючих.

Прибуток по кожному типу виробів обчислюється в рядку 17.

Коефіцієнт зменшення віддачі відображає ефективність росту продажу за рахунок росту затрат на рекламу та інші витрати в системі маркетингу і збуту.

Математична модель

Уведемо позначення:

i - номер рядка, ресурсу;

j - номер стовпчика, продукту;

Xj – планова кількість j-го продукту;

Рj – прибуток на одиницю j-го продукту;

Ві – обмежений запас і-го ресурсу на складі;

Rij – норма витрату і-го ресурсу на одиницю j-го продукту;

Сі – планова сума витрату і-го ресурсу по всім продуктам.

В загальному вигляді дана модель економіко-математичної постановки задачі буде мати наступний вигляд:

Ci=Rij*Xj,

Цільова функція (максимізувати прибуток)

Р=Pj*Xj    max

при обмеженнях Ci <= Ві  та невід’ємній кількості продуктів  Xj>=0.

Формули табличної моделі

Після створення планової таблиці необхідно зв’язати показники формулами для обчислень.

В колонці С (планові витрати комплектуючих) введені формули обчислення суми добутку норм витрат ресурсів на планову кількість продукції.

В рядку 17 (Прибуток по видам виробів) числа 75, 50, 35 означають прибуток на одиницю продукції, яка перемножується на кількість виробів по плану і корегується піднесенням до степеню коефіцієнта зменшення прибутку з клітини Н15.

В рядку 18 знаходиться сума прибутку по всій продукції з рядка 17.

Настройка економіко-математичної моделі

Для обробки таблиці в ТП Excel оптимізатором необхідно визвати діалогове вікно Поиск решения і налаштувати економіко-математичну модель. Різниця економіко-математичної постановки задачі оптимізації в табличному процесорі Excel від традиційної економіко-математичної постановки в тому, що в формулах задаються не символьні позначення змінних і параметрів, а координати клітин таблиці в яких знаходяться ці змінні. ТП Excel дозволяє вписувати в формули символьні імена клітин, але програма Поиск решения в 70% випадків імена не сприймає. Тому необхідно використовувати посилання на клітини.

Вікно Поиск решения викликається командою меню Сервис-> Поиск решения.

Поле Установить целевую ячейку слугує для вказівки цільової клітини, значення якої необхідно максимізувати, мінімізувати або встановити рівним заданому числу. Ця клітина повинна містити формулу. В даному прикладі це клітина $D$18 (Прибуток).

Кнопка Равной призначена для вибору варіанта оптимізації значення цільової клітини (максимізація, мінімізація або підбір заданого числа). В даному прикладі для максимізації прибутку потрібно натиснути кнопку максимальному значению.

Поле Изменяя ячейки слугує для вказівки клітин, значення яких змінюються в процесі пошуку рішення до того часу, поки не будуть виконані накладені обмеження і умова оптимізації значення клітини, вказаної в полі Установить целевую ячейку. В поле Изменяя ячейки уводяться імена або адреси клітин. В даному прикладі потрібно вказати діапазон клітин $D$9:$F$9, який містить вихідні значення плану випуску продукції. Клітини, в яких змінюються значення повинні бути зв’язані з цільовою клітиною.

Поле Предположить  використовується для автоматичного пошуку клітин, які впливають на формулу, посилання на яку задана в полі Установить целевую ячейку. Результат пошуку відображується в полі Изменяя ячейку.

Команда Добавить призначена для відображення діалогового вікна Добавить ограничение.

Команда Изменить слугує для відображення діалогового вікна Изменить ограничение.

Команда Выполнить слугує для запуску пошуку рішення поставленої задачі.

Команда Закрыть призначена для виходу з вікна діалогу без запуску пошуку рішення поставленої задачі. При цьому зберігаються установки, які зроблені у вікнах діалогу після натиснення на кнопку Параметры, Добавить, Изменить або Удалить.

Кнопка Параметры слугує для відображення діалогового вікна Параметры поиск решения, в якому можна завантажити або зберегти модель, яку оптимізуємо і вказати передбачувані варіанти пошуку рішення.

Кнопка Восстановить слугує для очищення полів вікна діалогу і поновлення значень параметрів пошуку рішень, які використовуються по замовченню.

Діалогові вікна Изменить та  Добавить обмеження однакові, рис.2

В полі Ссылка на ячейку заноситься адреса або ім’я клітини або діапазону, на значення яких накладаються обмеження.

Настройка параметрів алгоритму і програми виконується в діалоговому вікні Параметры поиска решения, рис. 3.

У вікні встановлюються обмеження на час рішення задач, вибираються алгоритми, задається точність рішення, надається можливість для збереження варіантів моделі.

Поле Максимальное время слугує для обмеження часу, пошуку рішення задачі.

Поле Предельное число итераций слугує для управління часом рішення задачі, шляхом обмеження числа проміжних обчислень. Час  і число проміжних обчислень задаються в секундах і не перевищують 32 767. Значення 100, які використовується по замовченню, підходить для рішення більшості простих задач.

Поле Относительная погрешность слугує для вказівки точності, з якою визначається відповідність клітини цільовому значенню або наближенню до вказаних меж. Поле повинно містити число з інтервалу від 0 до 1. Чим менше число, тим вище точність результатів.

Поле Допустимое отклонение слугує для вказівки допуску на відхилення від оптимального рішення. При введенні більшого допуску пошук рішення закінчується швидше.

Поле Сходимость результатів рішення застосовується тільки до нелінійних задач. Коли відносна зміна значення в цільовій клітині за останні 5 ітерацій стає менше числа, вказаного в полі Сходимость, пошукприпиняється. Чим менше значення у полі Сходимость, тим вище точність результатів. Прапорець Линейная модель слугує для прискорення пошуку рішення лінійної задачі оптимізації або лінійної апроксимації нелінійної задачі.

Прапорець Неотрицательные значения дозволяє встановити нульову нижню межу для тих впливових клітин, для яких вона не була вказана в полі Ограничение діалогового вікна Добавление ограничений.

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

Прапорець Показывать результаты итераций надає можливість призупинити пошук рішення для перегляду результатів окремих ітерацій.

Команда Сохранить модель викликає на екран діалогове вікно Сохранить модель, в якому можна задати посилання на область клітин призначену для зберігання моделі оптимізації (рис.4).

У вікні Сохранить модель в полі Задайте область модели уведіть посилання на верхню клітину стовпчика, в якому буде розміщена модель оптимізації.

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

Обчислення і результати рішення задачі

Для запуску оптимізатора у вікні Поиск решения потрібно натиснути кнопку Выполнить. У рядку повідомлень з’являється повідомлення Постановка задачи ..., що вказує на початок роботи програми. По закінченню розрахунків з’являється діалогове вікно Результаты поиска решения (рис.5).

Поле Тип отчета задає тип звіту, який записується на окремий лист книги.

Звіт Результаты використовується для створення звіту, який складається з цільової клітини в списку впливових клітин моделі, їх вхідних і кінцевих значень, а також формул обмежень і додаткових відомостей про накладенні обмеження.

Звіт Устойчивость використовується для створення звіту, який містить відомості про чутливість рішення до малих змін в формулі моделі або в формулах обмежень. В звіт по нелінійним моделям включаються обмежені затрати, фіктивні ціни, а також діапазони обмежень.

Звіт Пределы використовується для створення звіту, який складається з цільової клітини і списку впливових клітин моделі, їх значень, а також нижніх і верхніх обмежень.

Хід виконання роботи

Завдання 1. Ручний пошук оптимального плану.

Змінюючи кількість продукції в рядку 9, потрібно збільшити прибуток в клітині D18. Витрати не повинні перевищувати обсяг запасів на складі (колонка С). 

Завдання 2. Комп’ютерний пошук оптимального плану.

1. Завантажити команду меню Сервис>Поиск решения.

2. Перевірити настройку моделі в діалоговому вікні (рис.1).

Мета – отримання найбільшого прибутку, клітина D18. Дані що змінюються вказані в діапазоні D9:F9. Обмеження С11:С15<=В11:В15 уведені для того, щоб кількість комплектуючих, які використовуються, не перевищувало їх запасу на складі. Кількість виробів не може бути від’ємним: D9:F9>=0.

3. У вікні Поиск решения натиснути кнопку Выполнить і отримати готове рішення.

4. В формулу прибутку на вироби в клітинах D17:F17 входить показник степені Н15, який враховує зменшення питомого прибутку з ростом об’єму виробництва. Якщо значення в клітині Н15 не дорівнює 0, то задача нелінійна і у вікні параметрів потрібно зняти прапорець лінійної моделі.

5. Якщо змінити значення в клітині Н15 на 1,0 (прибуток не залежить від об’єму виробництва і повторно запустити процес пошуку рішення, знайдене раніше рішення буде іншим. Дана зміна робить задачу лінійною. У вікні параметрів можна включити прапорець лінійної моделі.

Аналіз результатів: В табл.1. дано оптимальне рішення, знайдене програмою Поиск решения. Досягнуто максимального прибутку при обмежених ресурсах на складі.

З повагою ІЦ "KURSOVIKS"!