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

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

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

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

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

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

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

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

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

Таблиця 1

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

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

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

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

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

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

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

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

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

На другому етапі проведемо порівняння статистичних характеристик
рівнянь, що в кінцевому рахунку дозволяють зробити остаточний вибір (табл.2).

Таблиця 2

Статистичні характеристики рівнянь тренда

№	Параметри і характеристики рівняння	Вид рівняння
Лінійна	Поліноміальна	Степенна
	Коефіцієнт детермінації	0,621	0,675	0,736
	Коефіцієнт кореляції (кореляційне відношення)	0,788	0,809	0,822
	Середня помилка апроксимації	14,77	12,91	13.15
	Абсолютне середнє квадратичне відхилення між фактичними і розрахунковими даними	412,60	382,22	394,23

Виходячи з значень статистичних характеристик доцільно відібрати
степеневе рівняння, яке можна записати таким чином:

,

Приклад розрахунку статистичних показників (табл. 2) у середовищі
MS Excel наведений у додатку 4.

2). На наступним кроці зробимо прогнози об’ємів продажу,
виробничих затрат і розходів на рекламу відповідно початкових даних (табл.1) з використанням засобів побудови діаграм і графіків MS Excel.

Рис.5. Прогнозування об’єму продажів, виробничих затрат та розходів на рекламу

Як видно із рис.5, що:

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

· Неможливо автоматично обчислити прогнозні значення.

3). Обчислимо значення прогнозованих змінних, скориставшись
приведеними рівняннями лінії тренду. Почергово підставляючи замість номера періодів 31, 32, …, 36, отримаємо прогнозні значення (рис.6).

Рис.6. Прогнозування змінних

Цей прогноз запишемо в окрему таблицю (табл.3).

Таблиця 3

Перший прогноз об’ємів продажу на липень – грудень 2011 року

Місяць	Прогноз об’єму продажу, тис. грн.	Місяць	Прогноз об’єму продажу, тис. грн.
Липень	2982,35	Жовтень	3068,06
Серпень	3011,54	Листопад	3095,45
Вересень	3040,09	Грудень	3122,31

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

Обчислимо прогнозні значення виробничих затрат, затрат на рекламу і об’ємів продажу на 31 – 36 періоди (липень – грудень 2011 р.) з
використанням статистичних функцій
Застосування цих статистичних функцій описане в п.2.2, в додатку 1 та
продовженні до додатку 1.

Формули, які обчислюють прогнозні значення, показані на рис.7.

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

Отримаємо ще два прогнози об’ємів продажу на друге півріччя 2011 р.:

· лінійний багатофакторний (фактори – Період, Виробничі
затрати і Затрати на рекламу) (табл.4);

· експоненціальний багатофакторний (табл. 5).

Дата добавления: 2015-08-13