Лабораторна робота 6 Прогнозування у середовищі MS Excel

Лабораторна робота 6 Прогнозування у середовищі MS Excel

У Лабораторній розглядаються засоби табличного процесора MS Excel для розв’язування задач прогнозування, які широко використовуються в економічних розрахунках.

1. Підбір параметра

Ця операція використовується для прогнозування значення однієї комірки при зміні значення іншої; такі комірки мають бути пов'язані формулою.

Розглянемо таблицю з фінансовими показниками роботи фірм:

Нехай, наприклад, треба визначити, при яких значеннях інвестиційних витрат фірми «Алмаз» (тут — 140,33) термін окупності інвестицій становив би 1,15 (у таблиці — 0,71).

Для цього спочатку встановлюють курсор у комірку F3, після чого активізують команди Дані – Аналіз «Якщо» Підбір параметра. В результаті на екрані дисплея з'являється вікно, показане на рис. 1. У ньому в полі Установить в ячейке визначають значення (по умовам задачі — 1,15), яке потрібно знайти для активної комірки F3, а в полі Изменяя значение ячейкивводять адресу комірки ВЗ, значення якої необхідно змінити, й активізують кнопку ОК

Після цього в наступному вікні (рис. 2) виводиться результат підбора параметру.

Якщо рішення знайдено, то при активізації кнопки ОК нове значення залишається в комірці F3 (отримали значення інвестиційних витрат – 229,46), при активізації кнопки Отмена попереднє значення відновлюється.

2. Прогнозування за допомогою сценаріїв

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

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

Розв’язання.

Прибуток обчислюється як добуток норми прибутку і загальних витрат, а обсяг продаж–як сума загальних витрат і прибутку. В підсумковому рядку вираховується загальні суми прибутку і обсягу продаж. За умовою задачі клітинки, що мають змінювати значення для прогнозу поведінки загального обсягу продаж, - це Е3:Е8.

Побудова сценаріїв починається по команді Дані – Аналіз «Якщо» - Диспетчер сценаріїв . Відкривається діалогове вікно Диспетчер сценариев, у якому треба натиснути кнопку Добавить. У наступному вікні Изменение сценария(рис.3) задають ім’я для створення сценарію, заносять діапазон клітинок, значення яких підлягають зміні, і натискають кнопку ОК.

Відкривається вікно Значения ячеек сценария (рис 4) із набором значень клітинок для сценарію.

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

Для створення наступного варіанту сценарію в цьому вікні достатньо натиснути кнопку Добавить. Повторно відкривається вікно Значения ячеек сценария (рис. 4), у яке треба ввести новий набір значень норми прибутку. Закінчив будувати всі варіанти сценаріїв в останньому вікні Значения ячеек сценария натискуємо кнопку ОК. На екрані з’являється вікно диспетчера сценаріїв (рис 5), яке містить імена всіх побудованих варіантів сценаріїв.

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

За даними сценаріїв на окремому робочому аркуші можна створити звіт або зведену таблицю. Для цього натискається кнопка Отчет і у вікні, що з’являється, вибирається тип звіту і натискається ОК. Нижче показаний тип звіту Структура (рис. 7).

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

3. Використання фінансових функцій

Фінансові функції використовують для розв’язування задач планування фінансової діяльності, визначення прибутків, аналізу вигідності капіталовкладень, кредитно-інвестиційної політики тощо. Інвестицією називається вкладення грошей у деякий бізнес (або банк) на певних умовах. Позика у банку називається кредитом, а внесок на рахунок у банк – депозитом. Надходження грошей від деякого бізнесу називають рентою. Розглянемо приклади використання фінансових функцій.

В MS Excel аргументами фінансових функцій, які далі будуть розглядатися,  є такі величини:

• ставка – процентна ставка за період;

• кпер – загальна кількість платежів або періодів виплат або платежів;

• плт – виплата, яка здійснюється кожний період і не змінюється за весь час виплати;

• пс – теперішня вартість, тобто загальна сума, що рівноцінна на теперішній момент серії майбутніх платежів;

• бс – майбутня вартість або баланс, який треба досягти після останньої виплати;

• тип – логічне значення (0 або1); число 0 або його відсутність, якщо виплата здійснюється в кінці кожного періоду і число 1, якщо на початку.

Значення аргументів ставка та кпер залежать від режиму виплат (методу нарахування відсотків). У таблиці 1 показані значення таких аргументів, де N – кількість років, а K – річна відсоткова ставка.

Якщо аргумент функції має значення 0, то його можна не вказувати. Аргументи у списку аргументів розділяються крапкою з комою, і якщо аргумент функції пропускають , то в цьому списку два розділювача (у даному випадку ;) мають бути поруч.

Розрізняють кредитну і депозитну процентні ставки, кредитна ставка є вищою за депозитну. Процентна ставка має бути узгодженою з тривалістю періоду (див. вище наведену таблицю). У прикладах, що будуть далі розглядатися, прийнята місячна депозитна ставка 5%, а кредитна – 6%.

Далі розглянемо приклади застосування фінансових функцій.

1. Функція БС(ставка;кпер;плт;пс;тип) призначена для обчислення майбутньої вартості теперішніх інвестицій пс на основі періодичних постійних (рівних по величині сум) виплат плт і постійної процентної ставки.

• Задача 1 (БС). На депозит поставлено 5000 грн. під n^* відсотків річних на 5 років. Відсотки нараховуються в кінці кожного місяця. Яка сума буде на рахунку після закінчення терміну.

2. Функція СТАВКА(кпер;плт;пс;тип;предположение) обчислює реальну процентну ставку  від надання позики на певну суму пс за умови фіксованих періодичних виплат плт протягом деякої кількості періодів, що дозволяє визначити банку вигідність надання такої позики. Для цієї функції потрібно задати деяке початкове наближення (предположение) до шуканої процентної ставки. Якщо таке наближення не задано, то воно приймається рівним 0,1 (10%).

• Задача 2 (СТАВКА). Під яку відсоткову ставку річних надати кредит у розмірі 100 + n грн., щоб через рік отримати 130+ n грн. Відсотки нараховуються раз на місяць.

3. Задача 3. (КПЕР) Через який час (в роках) сума рівна 6000 грн. досягне суми 10000 грн. при нарахуванні за складною відсотковою ставкою n % річних на початку кожного кварталу.

Задача 4. (ПЛТ, ОСПЛТ, ПРПЛТ) Банк видав кредит в розмірі 1000 грн. під n відсотків річних на 3 роки. Визначити розмір щомісячних виплат за ануїтетом.

Побудувати таблицю залежності Основної виплати та відсоткової виплати за кожен із періодів  (1;36)

Функція ПС(ставка;кпер;плт;бс;тип) обчислює сьогоднішню вартість низки майбутніх надходжень (ренти) від банку. Це дає змогу визначити вигідність інвестицій (капіталовкладень) у деякий бізнес.

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

Задача 5 (ПС, СТАВКА). Для деякого бізнесу потрібно вкласти 3200 грн. А бізнес протягом 5 місяців буде приносити по 1000 грн. доходу в кінці місяця. Депозитна ставка n % банку. Чи варто займатися цим бізнесом.

6. Функція ЧПС(ставка; значение1;значение2;…)

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

Задача 6. У бізнес треба вкласти сьогодні 10000 грн. На наступні 3 роки очікуються річні доходи від бізнесу 3000 грн., 4200 грн., 6300 грн. Витрати залучення капіталу 10% річних. Розрахувати сьогоднішню вартість бізнесу і оцінити , чи є цей бізнес вигідним.

З повагою ІЦ “KURSOVIKS”!