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

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

Читайте также:
  1. A. Котлове забезпечення з використанням натуральних харчових продуктів
  2. II. Задания по циклическим алгоритмам
  3. Microsoft Excel
  4. Microsoft Excel
  5. А. Построение диаграмм функций полезности, предельных полезностей и кривых безразличия в Excel
  6. АЛГОРИТМ
  7. Алгоритм

 

Рішення задачі прогнозування будемо здійснювати у середовищі
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; просмотров: 241 | Нарушение авторских прав


Читайте в этой же книге: Загальні вимоги до підготовки і виконання лабораторних робіт | Номер варіанта лабораторної роботи студент визначає на основі двох останніх цифр залікової книжки за таблицею, наведеної в додатку 6. | Основні поняття, предмет і метод курсу | Загальні методичні вказівки | Висновок. | Прогнозування за середнім абсолютним приростом | Висновок | Завдання роботи і вихідні дані | Загальні методичні вказівки | Оцінка параметрів рівняння за допомогою методу найменших квадратів |
<== предыдущая страница | следующая страница ==>
Оцінка параметрів рівняння за допомогою пакета прикладних програм MS Excel| Прогнози відрізняються друг від друга і від першого прогнозу.

mybiblioteka.su - 2015-2024 год. (0.009 сек.)