Завдання 1.3. Лінійний прогноз (регресія)
Підготувати лінійну вибірку даних, що складаються з двох стовпців (X і У),
наприклад:
прибуток і ціна товару,
прибуток і обсяги продажів,
витрати і дохід,
і.т.ін.
Не менш 20-ти наборів даних.
Виконати регресійний аналіз і спрогнозувати значення на наступний період за допомогою функції ТЕНДЕНЦІЯ.
Виконати перевірку лінійності/ не лінійності вибірки, побудувавши графік сформованого масиву даних: якщо цей взаємозв'язок носить лінійний характер, то лінія на графіку буде або прямою, або злегка похиленою в одну або іншу сторону, або горизонтальною. Це і буде найкращою підказкою про те, що взаємозв'язок є лінійним, і тому, в даному випадку, функція ТЕНДЕНЦІЯ - найзручніший спосіб регресійного аналізу. Однак, якщо лінія різко згинається в одному з напрямків, то це означає, що взаємозв'язок показників носить нелінійний характер.
Підготувати не лінійну вибірку даних, що складаються з двох стовпців (X і У), наприклад:
кількість співробітників і обсяг продукції, що випускається;
обсяг продажів нової продукції, приріст населення;
виплати по основному кредиту, рівень зарплат.
Виконати регресійний аналіз і спрогнозувати значення на наступний період за допомогою функції РОСТ.
Зробити висновки.
Приклад:
Використання функції робочого аркуша ТЕНДЕНЦІЯ - це найпростіший спосіб обчислення регресійного аналізу. Припустимо, результати ваших спостережень внесені в осередку А1: А10, а дні місяця розташовані в осередках В1: В10, як на рис. 2. Виділіть комірки С1: С10 і введіть наступну формулу, використовуючи формулу масиву:
= ТЕНДЕНЦІЯ (А1: А10; В1: В10)
Ви отримаєте результат, показаний на рис 2.:
Рис.1.4. Приклад масиву даних для виконання регресійного аналізу
Розглядаючи даний метод прогнозування, слід звернути особливу увагу на наступні моменти.
Кожен результат в осередках С1: С10 виходить на основі однієї і тієї ж формули масиву, усередині якої "заховано" більш складний вираз. В даному випадку формула має наступний вигляд:
осередок С1: = 9,13 + 0,61 * 1;
осередок С2: - 9,13 + 0,61 * 2;
осередок СЗ: = 9,13 + 0,61 * 3.
Значення 9,13 являє собою довжину відрізка, який відсікається на осі ординат лінією прогнозу, тобто значення прогнозу в початковий момент. Значення 0,61 одно кутовому коефіцієнту лінії прогнозу, іншими словами, значення прогнозу змінюються в результаті змін дат проведення спостережень.
Оскільки всі значення прогнозу складаються на основі одних і тих же показників відрізка, який відсікається на осі ординат, і кутового коефіцієнта, прогноз не відображає змін, що відбуваються в часовому ряді. Наприклад, дані ряду різко змінюються між восьмим (10) і дев'ятим результатами спостережень (16). Ця зміна впливає на всі значення прогнозу, навіть значення прогнозу тимчасового відрізка (2), хоча і розташовується на шість результатів спостережень раніше, ніж ця зміна фактично відбулася.
В даному прикладі функція ТЕНДЕНЦІЯ обчислює прогноз, заснований на зв'язку між фактичними результатами спостережень і числами 1-10, які можуть відображати чи перших десять днів місяця, або перших десять місяців року. Excel висловлює перший аргумент як аргумент відомі-значення -у функції ТЕНДЕНЦІЯ, а другий - як аргумент функції відомі значення -х.
Якщо ви пропонуєте функції тенденція тільки перший аргумент – відомі значення у, то Excel вважає, що другий аргумент відомі значення х - являє собою ряд, що починається з 1 і закінчується числовим значенням відомі значення у, зазначеним вами. Якщо прийняти, що числа 1-20 розташовані в осередках В1: В20, то дві нижченаведені формули будуть еквівалентні:
= ТЕНДЕНЦІЯ (А1: А20)
= ТЕНДЕНЦІЯ (А1: А20; Bl: B2.0)
Уже згадувалося, що регресійний аналіз дозволяє виробляти перспективну оцінку більш віддаленого майбутнього. Однак регресійний прогноз, приклад якого наведено на рис. 2, поширюється за межі даних самого останнього фактичного спостереження. Але на практиці бажано скласти прогноз хоча б на перший, наступний за цим період тимчасового ряду (тобто на той, для якого ще немає результатів спостереження). Далі описується, як це можна зробити за допомогою функції ТЕНДЕНЦІЯ.
Застосовуючи дані робочого листа, представлені на рис. 2, введіть в комірку В11 число 11, а в комірку СІ - наступне:
= ТЕНДЕНЦІЯ (А1: А10; В1: B10; B11)
У прикладах функції тенденція, наведених вище, використовуються формули масиву. Є декілька причин для введення формул масиву. Одна з причин полягає в тому, що формули, результатом яких є масив значень, повинні вводитися як формули масиву. Попередні приклади повертали масив результатів. Даний приклад повертає тільки одне значення, тому формула може вводитися, як звичайно.
На рис 3. продемонстровані описані вище дії. Перший аргумент - А1: А10 - визначає дані спостережень базової лінії (відомі значення y), другий аргумент - В1: В10 - визначає тимчасові моменти, в які ці дані були отримані (відомі значення х). Значення 11 в комірці В11 є новим значенням x і визначає час, який зв'язується з перспективною оцінкою.
Рис.1.5. Результати прогнозу
Формула фактично говорить про наступне: "Якщо відомо, яким чином у значення в діапазоні А1: А10 співвідносяться з x значеннями в діапазоні В1: В10, то який результат y значення ми отримаємо, знаючи нове значення х тимчасового моменту, рівне 11 ? Отримане значення 15,87 є прогнозом на основі фактичних даних коли настане одинадцятий часовий відлік.
Ввівши в комірку В11 більше значень, ви зможете прогнозувати дані, більш пізнього тимчасового моменту, ніж безпосередньо наступного за поточним. Припустимо, що результати спостережень в осередках А1: А10 відображають щомісячні обсяги продажів з січня по жовтень 2011 року. В цьому випадку число 24, введене в комірку В11, визначатиме двадцять четвертий місяць, тобто грудня 2012 року. Виконуючи обчислення за допомогою функції ТЕНДЕНЦІЯ, отримуємо результат 23,8, який і буде відображати прогнозований обсяг продажів в грудні 2012 року, отриманий на основі фактичних результатів спостережень за період з січня по жовтень 2011 року.
Крім того, існує можливість одночасного прогнозування даних для декількох нових тимчасових моментів. Наприклад, введіть числа 11-24 в осередку В11: В24, а потім виділіть комірки С11: С24 і введіть за допомогою формули масиву наступне:
= ТЕНДЕНЦІЯ (А1: А10; В1; В10; В11: В24).
Excel поверне в осередки С11: С24 прогноз на тимчасові моменти з 11 по 24. Даний прогноз базуватиметься на зв'язку між даними спостережень базової лінії діапазону А1: А10 і тимчасовими моментами базової лінії з 1 по 10, зазначеними в осередках В1: В10.
Приклад: книжкова торгівля.
Уявімо, що менеджер із закупівель відділу «Книга-поштою» нещодавно розіслав клієнтам новий каталог, що рекламує роман, який отримав дуже високу оцінку критиків. Менеджер вважає, що слід заздалегідь замовити додаткову кількість примірників, щоб не опинитися в ситуації, коли книга закінчиться раніше, ніж перестануть приходити заявки на неї. Менеджер почав відслідковувати щоденні замовлення на роман, і реєструвати обсяги продажів, як це показано на рис. 4.
Тут демонструється, яким чином фактичні та прогнозовані дані фіксуються на стандартному лінійному графіку. Оскільки лінія наявних товарів різко вигинається вгору, менеджер приймає рішення скласти прогноз з використанням функції РОСТ. Як і при використанні функції ТЕНДЕНЦІЯ, користувач в даному випадку може генерувати прогнози, просто підставляючи нові значення х.
Щоб спрогнозувати результати 11-13 тижнів, слід ввести ці числа в осередку В12: В14, а потім за допомогою формули масиву в діапазон комірок С12: С14 ввести наступне:
= РОСТ (А2: А11; В2: В11; В12: В14)
Рис. 1.6. Функція РОСТ при прогнозуванні нелінійних базових ліній (нелінійних трендів)
В осередках С12: С14 наведені значення попередньої оцінки кількості замовлень, яке може очікувати менеджер в наступні три тижні за умови, що поточна тенденція зростання залишиться незмінною. Однак слід враховувати, що такий оптимістичний прогноз на практиці, ймовірно, зазнає певних змін. Якщо при обчисленні прогнозу кількість планованих замовлень перевищить кількість клієнтів, від нього, швидше за все, слід просто відмовитися.
А що було б, якби в вищенаведеному прикладі замість функції РОСТ використовувалася функція ТЕНДЕНЦІЯ? В цьому випадку, оскільки аргумент відомих значень х носить лінійний характер, функція ТЕНДЕНЦІЯ видасть лінійні значення.
З рис. 5 видно, що ряд ТЕНДЕНЦІЯ в стовпці D описує пряму графіка. крива РОСТ в стовпці С набагато точніше відображає тенденцію перших десяти результатів спостережень, ніж лінія ТЕНДЕНЦІЯ.
На практиці буває краще скористатися не функцією РОСТ, а функцією ТЕНДЕНЦІЯ, навіть коли базова лінія носить нелінійний характер.
Рис. 1.7. Лінія тренда, побудована за допомогою функції РОСТ, дає на
основі нелінійної базової лінії набагато більш точний прогноз, ніж лінія тренда, побудована за допомогою функції ТЕНДЕНЦІЯ
Завдання 1.4. Регресійний аналіз
Це найбільш поширений спосіб показати залежність якоїсь змінної від інших, наприклад, як залежить рівень ВВП від величини іноземних інвестицій або від кредитної ставки Нацбанку або від цін на ключові енергоресурси.
Моделювання дозволяє показати величину цієї залежності (коефіцієнт), завдяки яким можна робити безпосередньо прогноз і здійснювати планування, спираючись на ці прогнози. Також, спираючись на регресійний аналіз, можна приймати управлінські рішення спрямовані на стимулювання пріоритетних причин які впливають на кінцевий результат, власне модель і допоможе виділити ці пріоритетні чинники.
Загальний вигляд моделі лінійної регресії:
Y=a0+a1x1+...+akxk
де a — параметри (коефіцієнти) регресії, x — фактори що впливають, k — кількість факторів моделі.
Вхідні дані
Серед вихідних даних нам необхідний якийсь набір даних, який би представляв із себе кілька послідовних або пов'язаних між собою величин підсумкового параметра Y (наприклад, ВВП) і така ж кількість величин показників, вплив яких ми вивчаємо (наприклад, іноземні інвестиції).
На малюнку вище показана таблиця з цими самими вихідними даними, в якості Y виступає показник економічно активного населення, а кількість підприємств, розмір інвестицій в капітал і доходів населення - це фактори, що оказують вплив.
За малюнком також можна зробити помилковий висновок, що мова в моделюванні може йти тільки про динамічні ряди, тобто моменти у рядах зафіксовані послідовно в часі, але це не так, з тим же успіхом можна моделювати і в розрізі структури, наприклад, величини зазначені в таблиці можуть бути розбиті НЕ по роках, а по областях.
Для побудови адекватних лінійних моделей бажано щоб вихідні дані не мали сильних перепадів або обвалів, в таких випадках бажано проводити згладжування.
Пакет аналізу
Параметри моделі лінійної регресії можна розрахувати і вручну за допомогою Методу найменших квадратів (МНК), але це досить затратно по часу. Трохи швидше це можна порахувати по цьому ж методу за допомогою застосування формул в Excel, де самі обчислення робитиме програма, але проставляти формули все одно доведеться вручну.
В Excel є надбудова Пакет аналізу, який є досить потужним інструментом у допомогу аналітику. Цей інструментарій, крім усього іншого, вміє розраховувати параметри регресії, по тому ж МНК, всього в кілька кліків, власне, про те як цим інструментом користуватися далі і піде мова.
Активуємо Пакет аналізу
За замовчуванням ця надбудова відключена і в меню вкладок ви її не знайдете, тому покроково розглянемо як її активувати.
У Ексель, зліва вгорі, активуємо вкладку Файл, в меню, шукаємо пункт Параметри та натискаємо на нього.
У вікні, ліворуч, шукаємо пункт Надбудови і активуємо його, в цій вкладці внизу буде випадаючий список управління, де за замовчуванням буде написано Надбудови Excel, праворуч від списку буде кнопка Перейти, на неї і потрібно натиснути.
Спливаюче віконце запропонує вибрати доступні надбудови, в ньому необхідно поставити галочку навпроти Пакет аналізу і заодно, про всяк випадок, Пошук рішення (згодиться для наступних завдань), а потім підтвердити вибір клікнувши по кнопці ОК.
Інструкція з пошуку параметрів лінійної регресії за допомогою Пакету аналізу
Після активації надбудови Пакета аналізу вона буде завжди доступна у вкладці головного меню Дані під посиланням Аналіз даних
В активному віконці інструменту Аналізу даних зі списку можливостей шукаємо і вибираємо Регресія
Далі відкриється віконце для налаштування і вибору вихідних даних для обчислення параметрів регресійної моделі. Тут потрібно вказати інтервали вихідних даних, а саме описуваного параметра (Y) і факторів (Х), що впливають на нього, як на малюнку нижче, інші параметри, в принципі, необов'язкові до налаштування.
Після того як вибрали вихідні дані і натиснули кнопочку ОК, Excel видає розрахунки на новому аркуші активної книги (якщо в настройках не було виставлено інакше), ці розрахунки мають такий вигляд:
Ключові осередки залито жовтим кольором, саме на них потрібно звертати увагу в першу чергу, інші параметри значимості також важливі, але їх детальний розбір вимагає мабуть окремої тими.
Одже, 0,865 - це R2 - коефіцієнт детермінації, що показує що на 86,5% розрахункові параметри моделі, тобто сама модель, пояснюють залежність і зміни досліджуваного параметра - У від досліджуваних факторів - іксів. Якщо узагальнено, то це показник якості моделі і чим він вищий тим краще. Ясна річ, що він не може бути більше 1 і вважається непогано, коли R2 вище 0,8, а якщо менше 0,5, то резонність такої моделі можна сміливо ставити під велике питання. Тепер перейдемо до коефіцієнтів моделі 2079,85 - це a0 - коефіцієнт який показує якою буде Y у випадку, якщо всі використовувані в моделі фактори будуть рівні 0, мається на увазі що це залежність від інших неописаних в моделі факторів;
-0,0056 - a1 - коефіцієнт, який показує вагомість впливу фактора x1 на Y, тобто кількість підприємств у межах даної моделі впливає на показник економічно активного населення з вагою всього -0,0056 (досить маленька ступінь впливу). Знак мінус показує що цей вплив негативний, тобто чим більше підприємств, тим менше економічно активного населення, як би це не було парадоксально за змістом; -0,0026 - a2 - коефіцієнт впливу обсягу інвестицій в капітал на величину економічно активного населення, відповідно до моделі, це вплив також негативний; 0,0028 - a3- коефіцієнт впливу доходів населення на величину економічно активного населення, тут вплив позитивний, тобто відповідно до моделі збільшення доходів сприятиме збільшенню величини економічно активного населення.
Зберемо розраховані коефіцієнти до моделі:
Y = 2079,85 - 0,0056x1 - 0,0026x2 + 0,0028x3