Вивчення моделей і методів прогнозування

1. Мета роботи: ознайомитися з моделями і методами прогнозування очікуваних обсягів продажів продукції підприємства, які можна виконувати в Excel 2000.

У результаті виконання роботи студент повинен:

Знати моделі, методи та алгоритми розрахунку прогнозу обсягів продажу продукції на ПЕОМ в Excel 2000.

Уміти створити вхідну базу даних, виконати прогнозні розрахунки на ПЕОМ засобами Excel 2000, побудувати графіки, оформити вихідні результати.

2. Основні теоретичні положення

2.1. Функції Excel 2000, які використовують для побудови прогнозів

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

В Excel 2000 можна використовувати різні методи побудови прогнозуючих функцій. Для цих цілей можна застосовувати вбудовані статистичні функції: ЛИНЕЙН, ТЕНДЕНЦИЯ, РОСТ, а також пакет АНАЛИЗ ДАННЫХ.

Виклик вбудованих функцій виконують звичайним чином. Кожна з зазначених функцій вимагає вказати масиви вихідних даних, а також константи. Функція ЛИНЕЙН вимагає ввести масив значень функції Y, масив значень аргументу X, а потім двічі ввести константу ИСТИНА. Для виводу вихідного масиву необхідно оголосити область для результатів і послідовно натиснути клавіші F2, Ctrl+Shift+Enter.

Функція Excel 2000 ТЕНДЕНЦИЯ вимагає вказати масив значень функції, масив значень аргументу. Щоб одержати прогноз на наступний період, вводять масиви Y і X, а також вказують посилання на комірку з відомим значенням Х.

Функцію Excel 2000 РОСТ застосовують, коли функція Y є нелінійною. Аргументи функції РОСТ аналогічні аргументам функції ТЕНДЕНЦИЯ. Щоб переглянути результати виконання функції ТЕНДЕНЦИЯ, РОСТ, необхідно виконати ті ж дії, що і при виведенні результатів функції ЛИНЕЙН.

2.2 Використання для побудови прогнозів інструментів пакета аналізу Excel 2000

Пакет аналізу може бути доступним або недоступним. У першому випадку для одержання доступу до пакета необхідно виконати команди СЕРВИС АНАЛИЗ ДАННЫХ КОМАНДА АНАЛИЗА. У другому випадку необхідно завантажити відповідну надбудову, виконавши команди: СЕРВИС НАДСТРОЙКИ, активізувати надбудову ПАКЕТ АНАЛИЗА і клацнути на кнопці ОК.

Пакет аналізу містить ряд програм з аналізу даних. Для цілей прогнозування можна використовувати такі готові програмні модулі як ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ, РЕГРЕССИЯ, СКОЛЬЗЯЩЕЕ СРЕДНЕЕ.

Програмний модуль ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ забезпечує розрахунки за моделлю

, (15)

де y, y  прогноз функції в періоді t, t-1 - відповідно;

  параметр згладжування, що визначає "вага" значення y в періоді t;

y  фактичне значення y в періоді t.

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

Параметр згладжування зв'язаний з фактором "затухание" формулою: "затухание" = 1-. Тому досить задатися значенням одного з них.

Програмний модуль ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ вимагає задати наступні опції: вхідний інтервал, тобто масив вхідних даних; фактор "затухание" (1-), вихідний інтервал, у якому будуть зафіксовані розрахункові значення функції та деякі інші. Вхідні дані та розрахункові значення функції повинні розташовуватися на одному листі. Якщо формулу скопіювати на одну комірку вперед, то можна одержати прогноз функції на період Т+1. Можна також вивести графіки. Запускають програмний модуль командами СЕРВИС АНАЛИЗ ДАННЫХ ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ.

Програмний модуль РЕГРЕССИЯ забезпечує побудову моделі лінійної регресії  одно- або багатофакторної, а також розраховує декілька параметрів. Модуль вимагає вказати наступні опції: "выходной интервал Y"; "входной интервал Х"; "уровень надежности" (0,95); "параметр вывода"  задається за замовчуванням. Доцільно також передбачити виведення "остатков" та їхнього графіка, "стандартизованных остатков" і "графика подбора".

Інструмент РЕГРЕССИЯ видає багато корисної інформації, представленої в трьох розділах. У першому вказуються коефіцієнти множинної кореляції і детермінації (чим вони ближче до 1, тих модель більш точна), а також стандартна помилкаS. В другому розділі, зокрема, наводяться значення MS залишків (залишкова дисперсія), критерію Фишера, рівня його значущості (чим цей рівень нижчий, тим модель точніша). У третьому розділі вказуються значення коефіцієнтів регресії (вільний член – "Y-пересечение", коефіцієнти при змінних), середньоквадратична похибка, t  статистика Стьюдента для кожного коефіцієнта, значення верхньої і нижньої меж 95-процент-ного рівня надійності для цих коефіцієнтів. Якщо було передбачено виведення залишків, то додатково виводяться розраховані за рівнянням значення функції та різниці (залишки). Запуск інструмента РЕГРЕССИЯ виконують аналогічно модулеві ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ.

Точність моделей прогнозування оцінюють такими показниками, як S²,Е% та ін.:

S²=, (16)

де - розраховане за моделлю значення функції,

Т  число спостережень,

К  число оцінюваних параметрів моделі.

Е% = , (17)

Чим менше S²іЕ%, тим модель є точнішою.

Прогноз показника Y на період Т+1, тобто на наступний період, одержують шляхом підстановки в отримане рівняння моделі значення t =T+1, де Т  число спостережень передісторії. Інтервальний прогноз Y оцінюють за допомогою формули Y(Т+1)  .

Для прогнозної функції лінійного виду, отриманої за допомогою модулів ЛИНЕЙН, ТЕНДЕНЦИЯ, РЕГРЕССИЯ, значення  можна розрахувати за формулою

 = , (18)

де (,)  статистика Стьюдента, =Т-2 (при Т=9 (,)=2,365)

S середньоквадратична похибка ряду залишків,

W = , (19)

де L = 1, 2 …  число періодів, для яких розраховують прогноз після Т.

У випадку використання модуля ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ  приблизно можна оцінити за формулою

 = (20)

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