ЛИНЕЙН(извест_значен_у;извест_значен_х;конст;статистика)

ПРОГНОЗУВАННЯ У СЕРЕДОВИЩІ MS EXCEL

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

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

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

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

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

Рис. 1 - Вікно команди Подбор параметра

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

Рис. 2 - Результат підбору параметру.

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

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

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

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

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

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

Рис. 3 – Вікно Изменение сценария

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

Рис. 4 – Визначення значень клітинок для сценарію

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

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

Рис. 5 – Перелік побудованих сценаріїв

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

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

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

Рис. 7 – Структура сценарію

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

В цьому розділі розглянута лінійна парна регресія. Для апроксимації набору спостережень використовується пряма лінія y=a*x+b. Методом найменших квадратів треба обчислити такі значення коефіцієнтів a та b, щоб пряма лінія щонайкраще відповідала наявним даним. Для розв’язування цієї задачі в MS Excel є статистична функція

ЛИНЕЙН(извест_значен_у;извест_значен_х;конст;статистика)

Аргументи извест_значен_у; извест_значен_х – це множини значень, які отримані в результаті спостережень. Обов’язковим є тільки масив {у}. Якщо множина {х} опущена, то передбачається що це масив {1, 2, 3,…} такого ж розміру, як масив {у}.Третій і четвертий аргументи можна опустити.

Значення коефіцієнтів лінії регресії функція ЛИНЕЙН повертає у вигляді вектора – рядка (a, b). Для його обчислення треба виділити дві суміжні клітинки у рядку електронної таблиці і застосувати формулу масиву.

Задача 1. Невелика фірма мала продажі на суму 3100 грн., 4500 грн., 4400 грн., 5400 грн., 7500 грн. і 3100 грн. за перші шість місяців звітного року. Побудувати рівняння прямої парної регресії і спрогнозувати, яким може буде обсяг продаж у дев’ятому місяці.

Для розв’язування задачі складена наведена нижче електронна таблиця.

Обчислені значення коефіцієнтів лінії регресії містяться у клітинках В6:С6. В ці клітинки записана формула масиву {=ЛИНЕЙН(B3:G3)}, де B3:G3 – діапазон клітинок, в якому знаходяться відомі суми щомісячних продаж. В результаті обчислень отримано рівняння регресії Обсяг продаж=1000*N+2000, де N – номер місяця. Якщо в це рівняння підставити N=9, то по прогнозу обсяг продаж у вересні місяці може сягнути 11000 грн.

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

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

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

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

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

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

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

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

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

Таблиця 1 – Значення аргументів

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

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

У клітинки електронної таблиці фінансові функції, як і будь-які інші, доцільно вводити за допомогою майстра вставки функції, який можна запустити командою Вставка - Функция або натисканням однойменної кнопки на панелі інструментів Стандартная.

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

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

Задача 1. Інвестор вкладає в бізнес 3000 грн. (чи відкриває на цю суму рахунок у банку) на умовах 5% ставки прибутку щомісяця. Яка вартість інвестиції через 24 місяця?

Розв’язок задачі: =БС(5%;24;;-3000) Відповідь: 9675,30 грн.

Аргументу пс (-3000) надано від’ємне значення, і це означає не отримання, а вкладення грошей.

Задача 2. Клієнт відкриває рахунок у банку на умовах 5% ставки прибутку щомісяця, кладе на рахунок 3000 грн. і планує на початку кожного місяця забирати з рахунку 100 грн. Яка сума буде на рахунку через 24 місяця?

Розв’язок задачі: =БС(5%;24;100;-3000;1) Відповідь: 5002,59 грн.

Аргументу плт (100) надано додатне значення, і це означає отримання грошей.

2. Функція КПЕР(ставка;плт;пс;бс;тип) обчислює кількість періодів, потрібних для погашення суми позики пс, наданої під деяку постійну процентну ставку за умови наперед заданої суми періодичних постійних виплат плт.

Задача 3. Позику 3000грн. беруть за умови повернення в кінці кожного місяця 300 грн. і процентної ставки 6%. Скільки місяців потрібно для повернення позики?

Розв’язок задачі: =КПЕР(6%;300;3000) Відповідь: 15,7 місяців.

3. Функція ПЛТ(ставка;кпер;пс;бс;тип) призначена для визначення суми періодичних виплат для погашення боргу пс. Така виплата складається з двох частин:

а) основна виплата ОСПЛТ(ставка;период;кпер;пс;бс;тип);

б) виплати за процентами ПРПЛТ(ставка;период;кпер;пс;бс;тип).

Задача 4. Бізнесмен взяв у банку кредит на суму 3000 грн. терміном на 12 місяців за умови щомісячного погашення позики і місячної ставки 6%. Визначити величину щомісячних виплат і її складові в кінці 5 місяця.

Розв’язки задачі:

=ПЛТ(6%;12;3000) Відповідь: -357,33 грн.;

=ОСПЛТ(6%;5;12;3000) Відповідь: -224,51 грн.;

=ПРПЛТ(6%;5;12;3000) Відповідь: -133,32 грн.

Отримали від’ємні числа, це означає, що бізнесмен віддає гроші банку на погашення кредиту.

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

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

Задача 5. Нехай для ведення деякого бізнесу потрібно вкласти сьогодні 3500 грн., а далі бізнес протягом 5 місяців приноситиме по 1000 грн. доходу (ренти) в кінці місяця. Депозитна ставка банку 5%. Чи варто займатися цим бізнесом?

Розв’язок задачі: =ПС(5%;5;1000) Відповідь: -4329,43 грн.

Вартість бізнесу (гранично допустима інвестиція) складає 4329,43 грн. Оскільки цю суму потрібно вкладати, число отримане від’ємним. Бізнес вигідний, бо для його ведення потрібно лише 3500 грн. Якби потрібно було більше, ніж 4329,43 грн., то такий бізнес був би збитковим.

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

Задача 6. Бізнесмен звертається до банку за позикою (кредитом)т на суму 3000 грн. на 12 місяців за умови періодичних виплат 300 грн. протягом року в кінці кожного місяця. Визначити процентну ставку позики.

Розв’язок задачі: =СТАВКА(12;-300;3000) Відповідь: 2,92%.

Якщо місячна депозитна ставка встановлена на рівні 5%, то такий кредит для банку є невигідним. Банк кредиту не надасть.

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

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

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

Розв’язок задачі: =ЧПС(10%;3000;4200;6300) Відповідь: 11307,29 грн.

Оскільки затрати, вкладені у бізнес, 10000 грн. менше за його сьогоднішню вартість 11307,29 грн., то такий бізнес є вигідним. За 3 роки ведення цього бізнесу очікується прибуток 1307, 29 грн.

Поиск по сайту: