2. Прогнозування за допомогою вбудованих функцій ms Excel

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

В регресійному аналізі використовується залежність між випадковими результуючими показниками від невипадкових вхідних змінних . Математичною моделлю такого роду залежностей є рівняння виду:

,

де випадкова змінна. Це рівняння називається рівнянням регресії;

функція функцією регресії. Відбір найкращої функції становить завдання регресійного аналізу.

Наведемо список і опис функцій, які використовуються в процесі прогнозування в регресійному аналізу (синтаксис функцій – див. додаток 1).

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

Ці функції використовуються в наступних ситуаціях.

• Функція використовується, якщо функція прогнозування (функція регресії) залежить від одного фактора і передбачається, що між прогнозованої змінної і фактором існує лінійна залежність виду:

• Функція використовується, якщо функція прогнозування лінійно залежить від декілька факторів, а також у випадку поліноміальної залежності прогнозованої змінної від одного фактору. Залежності між прогнозованої змінної і факторами мають вигляд:

лінійна множинна регресія, або

поліноміальна від одного фактора.

• Функція використовується, якщо функція прогнозування експоненціальне залежить від декілька факторів, тобто між прогнозованої змінної і факторами існує залежність виду:

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

• Функція обчислює коефіцієнти в рівнянні лінійної множинної регресії:

,

або ці коефіцієнти в рівнянні поліноміальної регресії ( від одного фактора):

.

• Функція обчислює коефіцієнти в рівнянні експоненціальної регресії :

.

2. Алгоритм прогнозування з використанням вбудованих функцій ms Excel

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

Функції, які реалізують статистичні методи обробки і аналізу даних, в Excel реалізовані у вигляді спеціальних програмних засобів – надстройки “Пакет анализа”.

Якщо у середовищі MS Excel офіс 2003 року, встановлення “Пакет анализа” здійснюється за допомогою команди “Сервис / Надстройка”. Далі необхідно встановити прапорець перед пунктом “Пакет анализа”. Якщо у середовищі MS Excel офіс 2007 року, встановлення настройки “Пакет анализа” здійснюється в наступній послідовності: Далі необхідно встановити прапорець перед пунктом “Пакет анализа”, (рис.2).

Рис.2. Вікно “Надстройки” зі списком настроювань.

Після успішного встановлення “Настройки” в меню “Сервис” з’явиться пункт “Анализ данных” з усіма інструментами статистичного аналізу в MS Excel (рис.3).

Рис.3. Вікно майстра функцій “Анализ данных”

Застосування алгоритму прогнозування розглянемо на прикладі, де у якості вихідних даних використовуються динаміка показників таблиці 1.

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

Таблиця 1

Об’єми продажу по місяцям

№ п/п	Місяць і рік	Виробничі витрати, тис. грн.	Витрати на рекламу, тис.грн.	Об’єми продажу, тис.грн.
1	січень 2009	905,80	199,80	1282,00
2	лютий 2009	902,50	211,50	1292,70
3	березень 2009	903,00	206,80	1228,90
4	квітень 2009	889,80	225,70	1392,60
5	травень 2009	889,80	219,00	1647,30
6	червень 2009	892,80	235,70	1672,90
7	липень 2009	888,30	231,30	1660,50
8	серпень 2009	875,80	241,10	2011,70
9	вересень 2009	883,90	238,10	2351,90
10	жовтень 2009	875,10	248,10	2513,90
11	листопад 2009	871,60	256,90	2468,50
12	грудень 2009	879,80	251,90	2746,20
13	січень 2010	868,20	273,10	1942,70
14	лютий 2010	866,30	264,50	1901,10
15	березень 2010	862,10	267,10	1971,60
16	квітень 2010	866,60	282,90	1989,10
17	травень 2010	862,50	287,50	2139,20
18	червень 2010	863,90	286,30	2474,20
19	липень 2010	858,50	285,30	2393,60
20	серпень 2010	861,70	304,10	2990,10
21	вересень 2010	854,60	302,20	3190,30
22	жовтень 2010	847,00	309,60	3400,40
23	листопад 2010	854,40	310,00	3399,50
24	грудень 2010	842,50	305,90	3793,90
25	січень 2011	842,10	316,00	2584,90
26	лютий 2011	844,20	302,60	2451,70
27	березень 2011	843,60	314,30	2666,00
28	квітень 2011	845,20	311,10	2611,00
29	травень 2011	833,20	317,90	2731,80
30	червень 2011	843,10	329,70	2983,80

1. Побудуємо точковий графік об’єму продажу в залежності від часу і виберемо вид рівняння.

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

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

Статистична адекватність означає відповідність рівняння окремим критеріям, які виражаються системою статистичних характеристик, що розраховуються за допомогою формул (4-8).

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

З урахуванням сутності процесу і тенденції зміни рівнів динамічного ряду на першому етапі встановлюється клас рівняння. (рис.4).

Рис.4. Порівняльний аналіз класу рівняння об’єму продажу в залежності від часу