Задача 6 «Прогноз доходу фірми»

Протягом перших десяти місяців року доходи фірми були такими: 200 000, 220 000, 230 000, 225 000, 235 000, 225 000, 230 000, 250 000, 245 000, 280 000. Побудувати лінію тренду і спрогнозувати дохід фірми у листопаді й грудні.

План

1. Поняття про метод найменших квадратів.

2. Реалізація регресійного аналізу в ЕТ.

3. Використання функції TREND.

4. Використання функції LINEST.

5. Поняття про масиви і формули масивів.

6. Побудова ліній тренду за допомогою діаграм.

Теоретичні відомості

1. Поняття про метод найменших квадратів. Метод найменшихквадратів (МНК) застосовують для розв'язування задач про згладження експериментальних даних та апроксимацію (наближення) даних деякою нескладною аналітичною функцією з метою використання цієї функції дляпрогнозування подальших змін даних.

Такий аналіз даних також називають регресійним аналізом.

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

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

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

Це приклади однофакторних задач. У першій задачі факто­ром є дні, які впливають на зміну даних, а у другій — місяці року. Якщо факторів є декілька, то відповідні задачі називають багатофакторними. Приклад двофакторної задачі: є дані про залежність вартості квартири від площі (це майже лінійна залежність) і від віддаленості від центру міста. Потрібно спрог­нозувати вартість квартири конкретної площі в конкретному районі міста. Вартість квартири може залежати також від повер­ху, якості будівельних матеріалів та інших факторів.

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

Заздалегідь складно визначити, який тип функції є оптима­льним для конкретних даних, зокрема, якщо їх багато. Тому якість апроксимації оцінюють на підставі критерію, який нази-вають «критерій R-квадрат» (використовують також позначення г2). Значення г2 для різних функцій (ліній) буде різним. Апрокси­мація вважається тим ліпшою, чим ближче значення г2 до числа 1, та ідеальною, якщо г2=1.

Нехай у деякій однофакторній задачі кількість експеримен­тальних даних п, значення фактора (незалежної величини, аргументу функції) утворюють масив чисел x_yt x₂,..., х_п, зна­чення експериментальних даних утворюють масив у_г, */₂,..., у_л. Нехай для апроксимації вибрано і визначено функцію f(x).

2. Реалізація регресійного аналізу в ЕТ. Розглянемо найпростіший спосіб прогнозування даних. Достатньо виокремити діапазон з експериментальними даними У і перетягнути маркер копіювання на k клітинок (вниз) — отримаємо лінійний прогноз для точок х_п+1> ^x_n+2> •••» x_n+k • Якщо перетягування виконати правою клавішею миші, то з контекстного меню можна вибрати тип апроксимації: лінійною тхЛ-Ь чи експоненціальною bm функцією (залежно від тенденцій у даних). Недоліками такого способу є припущення, що фактором X є масив чисел 1, 2, 3,., а також нереагування прогнозованих значень на зміни в експе риментальних даних.

Інший спосіб розв'язування задачі — це застосування інст румента Регресії, який запускають командою Сервіс => Аналіз даних... =>Регресії => ОК. Він забезпечує лінійну апроксимацію даних і повертає статистичну інформацію, що дає змогу оцінити якість апроксимації.

Головним способом розв'язування задачі лінійної апрок симації і прогнозування даних є використання статистичної функції TREND(діапазон1; діапазон2; діапазонЗ). Діапазон має містити експериментальні дані у_ґ Діапазон2 — значення,х ДіапазонЗ має містити точки для прогнозу х_п+1, х_пі_₂, • ••, ^х_п^л Функціюможна використовувати з двома аргументами або трьома. У першому випадку розв'язують задачу апроксимації експериментальних даних прямою лінією, а в іншому випадку — задачу прогнозування даних.

3. Використання функції TREND. Розглянемо алгоритм розв'язування задачі апроксимації даних прямою лінією, який реалізується статистичною функцією TREND.

1. Утворити вертикальний діапазон з х_і який назвати X.

Діапазон для задачі 6 має містити числа 1, 2,..., 10. Щоб діа-пазон назвати як X, треба його виокремити і виконати команди Вставити > Ім'я > Присвоїти > Введіть X > Додати > ОК.

2. Утворити вертикальний діапазон з у, кому надати ім'я Y.

3. Поряд вибрати порожній вертикальний діапазон з п клітинок.

4. У рядок формул ввести формулу =TREND(Y;X).

5. Натиснути на комбінацію клавіш Shift--Ctrl-Enter — результати f(x_t) заповнять порожній діапазон.

Розглянемо алгоритм прогнозування даних для значень фактора х_пМ,х_п+2,...,х,_н+h

6. Утворити вертикальний діапазон із зазначених значень фактора (тут 11, 12) під діапазоном X і назвати його Z.

7. Поряд вибрати порожній вертикальний діапазон з k клітинок.

8. У рядок формул ввести формулу =TREND(Y;X;Z).

10. Натиснути на комбінацію клавіш Shift+Ctrl+Enter —

результат-прогноз fх., і=/г+1,..., /k, заповнить порожній діапазон.

Тепер можна виконати додаткові обчислення для визна-чення значень т, b та г2 за формулами, які наведені вище.

Якщо виявиться, що апроксимацію виконано невдало (тен-

денція зміни даних є далекою від лінійної, г2<0,8), то слід ви-брати іншу функцію, наприклад GROWTH(), яка здійснює експо-ненціальну апроксимацію, або скористатися графічним способом розв'язування задачі.

4. Використання функції LINEST. Функція LINEST(Y; X;True; True) повертає масив, перший рядок якого — це значення m та b,другий — це стандартні похибки цих двох значень відповідно, третій рядок містить значення r2 і стандартну похибку для функції, четвертий рядок містить дані, які використовуються в теорії F-статистики. Цікавою особливістю функції є те що її можна використати для лінійного багатофакторного регресійного аналізу. Для експоненціального аналізу є функція LOGEST().

5. Поняття про масиви і формули масивів. Одновимірним масивом чисел називають послідовність чисел, взятих у фігурні дужки, наприклад {1; 1,5; 4,2}. Двовимірний масив містить набори чисел, які розмежовані символом двокрапка (якщо числа відокремлюються крапкою з комою і кома є десятковим розділю-вачем) або крапкою з комою (якщо розділювачами є кома та рапка відповідно), наприклад {1; 1,5; 4,2:1; 1,5; 4,2}. Один набір чисел відповідає рядку чисел у зображенні масиву у вигляді матриці. Діапазон з числами можна трактувати як масив чисел (матрицю).

Над матрицями-масивами визначені операції додавання віднімання, множення на число, а також такі функції: множення матриці на матрицю — ММULТ(матриця1; матри ця2), транспонування — TRANSPOSE(Macив чи діапазон), обчислення оберненої матриці — MINVERSE(Macив) та детермі нанта (визначника) матриці — MDЕТЕКМ(матриця).

Зауважимо, що діапазони можна перемножувати, але це не тотожно добуткові масивів-матриць. Добуток двох діапазонів -це діапазон з покомпонентно перемноженими елементами, що можна використати для розв'язування багатьох задач. Напри клад, вартість всіх товарів (одне число) у задачі про товарний чек можна визначити за допомогою такої формули: {=SUM(Kiль. кість* Ціна)}.

Формулу для дій з масивами чи діапазонами називають формулою масиву. Перед виконанням дій з масивами потрібно вибрати порожній діапазон клітинок, де міститиметься результат обчислення формули-масиву. Особливість дій користувача така: після набору формули у рядку формул її вводять у ЕТ не простим натисканням клавіші Enter, а комбінацією клавіш Shift-fCtrl + -Enter. Формула масиву буде записана у фігурних дужка х автоматично (їх не набирають). Формули масивів слід застосу вати в цій роботі для обчислення значень т, b і г2.

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

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

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

1. Лінійна апроксимація — це пряма лінія, яка наближено описує сукупність даних. її застосовують у найпростіших випад ках, коли дані розташовані близько до прямої.

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

3. Поліноміальну апроксимацію використовують для опису величин, які то плавно зростають, то спадають, а також для аналізу великої сукупності даних. Степінь полінома (до шостого) визначають кількістю екстремумів (максимумів і мінімумів) серед даних. Поліном другого степеня може описати лише один максимум або мінімум. Поліном третього степеня може мати два mекстремуми і т. д.

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

5. Експоненційну апроксимацію використовують у тому випадку, якщо швидкість зміни даних безперервно зростає, наприклад, у задачах розмноження вірусів. Неможливо застосувати степеневу й експоненційну апроксимацію, якщо дані містять нульові або від'ємні значення.

6.. Є ще метод апроксимації даних, який називають змінним середнім (лінійною фільтрацією, рос. скользящее среднее). Лінія тренду будується за певною кількістю точок (ця кількість визнаначається параметром Точки). Метод застосовують, коли точок є,багато і вони дуже розкидані. Дані усереднюють попарно чи інакше й одержані середні значення використовують для апроксимаціїї. Якщо значення параметра Точки є 2, першу точку кривої визначають як середнє значення перших двох даних, другу точку як -середнє значення другого і третього даного і т. д. Розглянемо алгоритм додавання лінії тренду до діаграми.

1. Побудувати діаграму для ряду експериментальних даних.

2. Клацнути правою клавішею на маркері ряду даних, для яких треба побудувати лінію тренду — отримаємо контекстне меню ряду даних.

3. Виконати команду Додати лінію тренду. 4. На закладці Тип вибрати тип лінії тренду.

5. На закладці Параметри задати назву кривої (можна не задавати), довжину відрізка (в одиницях зміни аргументу) прог-нозу, координату точки перетину з віссю У (можна не задавати),

зобразити рівняння регресії на діаграмі, розташувати на діаграмі значення г2 (R^2).

6. ОК.

ХІД РОБОТИ