Студопедия

Главная страница Случайная страница

Разделы сайта

АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника






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






Лабораторна робота 7

Засоби прогнозування даних

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

Обладнання та методичне забезпечення: персональні комп'ютери, програмне забезпечення, роздатковий дидактичний матеріал.

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

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

 

Рис.1

 

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

Прибуток обчислюється як добуток норми прибутку і загальних витрат (=D2*E2), а обсяг продаж – як сума загальних витрат і прибутку (=D2+F2). Для решти Типів виконайте Автозаповнення. В підсумковому рядку вираховується загальні суми прибутку і обсягу продаж. За умовою задачі клітинки, що мають змінювати значення для прогнозу поведінки загального обсягу продаж, – це Е2: Е3.

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

 

 

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

 

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

 

 

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

 

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

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

 

 

Рис. 5

Вироби Норма прибутку
Тип 1 9, 0%
Тип 2 3, 0%
Тип 3 15, 0%
Тип 4 12, 0%
Тип 5 7, 0%
Тип 6 8, 0%

Рис. 6

 

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

 

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

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

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

 

Рис. 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 – річна відсоткова ставка.

 

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

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

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

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

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

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

Розв’язок задачі: =БС(5%; 24;; -3000)

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

 

 

 

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

Розв’язок задачі: =БС(5%; 24; 100; -3000; 1)

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

 

 

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

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

Розв’язок задачі: =КПЕР(6%; 300; 3000)

 

 

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

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

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

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

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

=ПЛТ(6%; 12; 3000)

 

 

 

=ОСПЛТ(6%; 5; 12; 3000)

 

 

=ПРПЛТ(6%; 5; 12; 3000)

 

 

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

 

 






© 2023 :: MyLektsii.ru :: Мои Лекции
Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав.
Копирование текстов разрешено только с указанием индексируемой ссылки на источник.