Прогнозування у середовищі ms Excel

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

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

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

Рис 4.2.26 - Фінансові показники роботи фірм

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Прогнозування за допомогою статистичних функцій.

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

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