Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

Додаток 3_МУ_Excel_стат_аналіз_прогнозування

.pdf
Скачиваний:
47
Добавлен:
28.02.2016
Размер:
1 Mб
Скачать

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ ОДЕСЬКИЙ ДЕРЖАВНИЙ ЕКОНОМІЧНИЙ УНІВЕРСИТЕТ

МЕТОДИЧНІ ВКАЗІВКИ

ДО НАВЧАЛЬНО-КОМП’ЮТЕРНОЇ ПРАКТИКИ

для студентів II курсу денної форми навчання всіх спеціальностей

Частина 2. Статистичний аналіз та прогнозування засобами MS Excel

Усі цитати, цифровий і фактичний

ЗАТВЕРДЖЕНО

матеріал, бібліографічні відомості

на засіданні кафедри ІС в Е.

перевірені, написання сторінок

Протокол № 9

відповідає стандартам.

від “27” квітня 2009 р.

Зауваження рецензентів ураховані

 

 

 

(підпис укладачів)

 

Одеса ОДЕУ 2009

Методичні вказівки до навчально-комп'ютерної практики для студентів II курсу денної форми навчання всіх спеціальностей. Частина 2. Статистичний аналіз та прогнозування засобами MS Excel. (Укл. О.В. Орлик, А.Ю. Вакула, В.С. Зоріна. – Одеса: ОДЕУ, ротапринт, 2009 р. – 40 с.)

Укладачі: О.В. Орлик, канд. екон. наук, доцент А.Ю. Вакула, ст. викладач В.С. Зоріна, ст. викладач

Рецензенти: Є.С. Якуб, д-р ф.-м. наук, професор (зовнішній рецензент) І.В. Бакова, канд. екон. наук, доцент В.І. Могілевський, канд. техн. наук., доцент

Коректор: М.М.Васильєва

2

ВСТУП

Методичні вказівки призначені для проходження студентами 2 курсу денної форми навчання навчально-комп’ютерної практики.

Мета запропонованих методичних вказівок – опанування студентами засобів табличного процесора MS Excel 2003 для проведення статистичного аналізу й прогнозування економічних та соціальних процесів. Це сприятиме у майбутньому написанню ними курсових та дипломних робіт, оформленню звітів з економіки та бухгалтерського обліку.

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

У завданнях використовуються цифрові умовні дані.

3

1. Статистична обробка даних, аналіз та прогнозування в MS Excel. Теоретичні відомості

У науці передбачення називають прогнозуванням.

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

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

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

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

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

Основою прогнозування є числові значення якихось станів явища, що спостерігається. Наприклад, курс цінних паперів. Фіксуючи значення курсу в часі, ми отримаємо табличний опис процесу зміни курсу. Зрозуміло, якщо описати аналітично цей процес, тобто поставити йому у відповідність якусь функціональну залежність ПРОГНОЗ = f (t), де t - якийсь момент часу, то ПРОГНОЗ буде не що інше, як значення f (t) в деякий наперед заданий момент часу t.

Перед тим як розпочати прогнозування, необхідно переконатися у відповідності показників за минулий період. Інакше кажучи, базова лінія, яка визначає динамічний ряд, повинна відповідати таким характеристикам:

1.Базова лінія містить у собі результати спостережень, починаючи з більш ранніх і закінчуючи останніми спостереженнями.

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

3.Спостереження фіксуються в один і той момент тимчасового періоду.

4.Пропуск даних не допустимий. Тому, якщо у дослідженнях відсутні

результати навіть за незначний проміжок часу, бажано поповнити їх хоча б приблизними даними.

Табличний процесор Excel має низку інструментів для проведення аналізу та прогнозу рядів динаміки.

4

Прогнозування із застосуванням методу ковзного середнього

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

метод ковзного середнього.

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

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

1 N

F(t 1) N J 1 At j 1 ,

де: t - число попередніх періодів, що входять у ковзне середнє; Aj - фактичне значення у момент часу j;

Fj - прогнозоване значення у момент часу j.

Засобом для побудови ковзного середнього є використання надбудови Пакет анализа. Якщо дана надбудова відсутня, то активізуйте її пунктом меню

Сервис – Надстройки – Пакет анализа.

Виконуючи команду Сервис – Анализ данных, у діалоговому вікні Анализ данных (Рис. 1) зі списку доступних функцій вибирають пункт Скользящее среднее та натискають ОК.

Рис.1. Вікно Аналіз даних

В наступному діалоговому вікні Скользящее среднее (Рис. 2) задають такі параметри: Входной интервал, Метки в первой строке, Интервал, Выходной интервал, Вывод графика и Стандартные погрешности.

5

Рис.2. Вікно Ковзного середнього

Вхідний інтервал

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

Мітки в першому рядку

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

Інтервал

Вводимо значення, необхідне для розрахунку ковзного середнього. Значення за замовченням дорівнює 3.

Вихідний діапазон

Вводимо посилання на ліву верхню комірку вихідного діапазону. Якщо встановлено прапорець Стандартные погрешности, то вихідний діапазон складається з двох стовпців та значення стандартних погрішностей містяться в правому стовпці. Якщо початкових значень для побудови прогнозу або для обчислення стандартної помилки недостатньо, MS Excel поверне значення помилки #Н/Д.

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

параметри Новый рабочий лист і Новая рабочая книга недоступні.Виведення графіка

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

Стандартні погрішності

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

Метод ковзного середнього застосовувати не складно, тим паче, що він є дуже спрощеним для створення точного прогнозу.

6

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

Приклади реалізації методу ковзного середнього в MS Excel

Приклад 1. Нехай нам відомі значення деякого економічного показника за період 10 місяців (Табл. 1).

Таблиця 1. Значення економічного показника

 

А

В

1

Номер періоду

Показник

2

1

10

3

2

11

4

3

10

5

4

12

6

5

13

7

6

13

8

7

13

9

8

14

10

9

16

11

10

17

Порядок виконання розрахунку:

Розмістимо ці дані в Excel на Листі 1 в комірках В2:В11, а значення тимчасового ряду розташуємо у комірках А2:А11. Припустимо, що для даного динамічного ряду виконуються всі умови відповідності.

Для аналізу даного показника і прогнозування його значень на один період вперед застосуємо метод ковзного середнього.

Для визначення тенденції вибираємо період для ковзного середнього – це 3. Чому 3? Це оптимальне значення величини періоду: якщо вибрати період 2, то за цей період за допомогою ковзного середнього неможливо відобразити тенденцію, а триваліший період занадто згладжує її, спотворюючи значення.

Застосовуючи викладену вище методику, знайдемо прогнозне значення показника на 11-й період.

За допомогою команди меню Сервис - Анализ данных викликати діалогове вікно Анализ данных. Вибрати інструмент аналізу - Скользящее среднее. Указати параметри для обчислення ковзного середнього (Рис. 3):

Входной интервал - блок комірок, що містить дані показника за попередні періоди – В2:В11.

Интервал - 3 (розрахунок середнього з 3 суміжних значень).

Выходной интервал – комірка С3 на робочому листі з даними.

Встановити прапорець Вывод графика.

Натиснути кнопку ОК.

7

Рис.3. Параметри Ковзного середнього

Результат ковзного середнього - таблиця прогнозних значень (Табл. 2) і графік (Рис. 4).

Таблиця 2. Розрахунок прогнозних значень показника (метод ковзного середнього)

 

А

В

С

1

Номер періоду

Показник

Ковзне середнє

2

1

10

 

3

2

11

#Н/Д

4

3

10

#Н/Д

5

4

12

10,33

6

5

13

11,00

7

6

13

11,67

8

7

13

12,67

9

8

14

13,00

10

9

16

13,33

11

10

17

14,33

12

11

 

15,67

Результат виконання розрахунків за методом ковзного середнього починається з повідомлень #Н/Д. Це говорить про недостатню кількість даних для обчислення середнього значення декількох перших результатів спостережень. Як бачимо, Выходной интервал починається з С3, а не з С2. Це говорить про те, що не логічно розглядати деяке число в якості прогнозу на період, на основі якого вираховується цей прогноз. Іншими словами, якби вихідний інтервал починався з С2, то прогноз на 4-й період, записаний в С5, був би побудований на основі 2, 3, 4-го періодів, які записані в B3:B5. У разі, коли вихідний інтервал починається з С3, прогноз на 4-й період, записаний в С5, будується на основі 1, 2, 3-го періодів, які записані в B2:B4.

У результаті набуваємо прогнозного або майбутнього значення показнику, який досліджується. У комірці С12 – значення 15,67 – воно означає майбутнє значення показника в період часу t =11.

8

За результатами аналізу можна зробити висновок, що загальна тенденція –

збільшення значення показника. На графіку можна визначити найбільш імовірне

значення показника для майбутнього облікового періоду – 15,67.

 

 

 

 

 

Ковзне середнє

 

 

 

20

 

 

 

 

 

 

 

 

 

Значення

15

 

 

 

 

 

 

 

 

Фактичний

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Прогноз

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

0

 

 

 

 

 

 

 

 

 

 

1

2

3

4

5

6

7

8

9

10

 

 

 

 

Точка даних

 

 

 

 

 

Рис.4. Графік ковзного середнього показника

Приклад 2. Нам відомі значення валового внутрішнього продукту (ВВП) України (у фактичних цінах) за період 8 років (Табл. 3).

Таблиця 3. Значення ВВП України

 

А

В

1

Роки, t

ВВП, млн грн

2

2000

170070

3

2001

204190

4

2002

225810

5

2003

267344

6

2004

345113

7

2005

441452

8

2006

544153

9

2007

712945

Порядок виконання розрахунку:

Розмістіть ці дані на Листі 2 у комірках В2:В9, а значення тимчасового ряду розташуйте у комірках А2:А9. Застосовуючи викладену вище методику ковзного середнього самостійно спрогнозуйте значення ВВП на 9-й період.

У результаті проведених розрахунків ви отримаєте таблицю прогнозних значень ВВП (Табл. 4) та графік (Рис. 5).

9

Таблиця 4. Результат розрахунку прогнозних значень ВВП

 

А

В

С

1

Роки, t

ВВП, млн грн

Ковзне середнє

2

2000

170070

 

3

2001

204190

#Н/Д

4

2002

225810

#Н/Д

5

2003

267344

200023

6

2004

345113

232448

7

2005

441452

279422

8

2006

544153

351303

9

2007

712945

443573

10

2008

 

566183

 

 

 

 

Ковзне середнє

 

 

 

800000

 

 

 

 

 

 

 

грн

600000

 

 

 

 

 

 

Фактичний

,млн

400000

 

 

 

 

 

 

 

 

 

 

 

 

Прогноз

ВВП

200000

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

0

 

 

 

 

 

 

 

 

2000

2001

2002

2003

2004

2005

2006

2007

 

 

 

 

Роки

 

 

 

 

 

Рис.5. Графік ковзного середнього ВВП

За результатами аналізу можна зробити висновок, що загальна тенденція - збільшення показника ВВП. Найбільш імовірне значення ВВП для майбутнього облікового періоду – 566183 млн грн.

Аналіз та прогнозування на основі трендів

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

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

Лінії тренду зазвичай використовуються в задачах прогнозування. Такі задачі розв’язуються за допомогою методів регресійного аналізу. За допомогою регресійного аналізу можна побудувати лінію тренду вперед або назад, екстраполювати (провести прогноз на основі відомих закономірностей розвитку, які відбиваються у минулому) її межі, в яких дані вже відомі та показати тенденцію їх змін.

10