Добір типу тренду та прогноз

Компанія «Схід» працює на українському ринку, але фінансово прив’язана до іноземної валюти – долару. Тому коливання курсу долару впливає на прибуток. Наприклад, зростання курсу призводить до зменшення попиту та падінню обсягів продажів. Щоб запобігти цьому, слід визначити закон коливання курсу за останніми даними, тобто тренд. Спрогнозувати значення курсу на наступний період планування та скорегувати ціну на продукцію, змінивши тим самим об’єм продажів в більший чи менший бік.

Рис. 11. Дані курсу долару за останні місяці

Excel надає можливість побудувати декілька трендів за наданим рядом значень (рис. 11). Для цього слід побудувати графік та в його параметрах обрати один з припустимих типів тренду (рис. 12).

Рис. 12. Обирання типу тренду

Далі, в цьому вікні на вкладці «Параметры» потрібно увімкнути дві опції: «показывать уравнение на диаграмме» та «поместить на диаграмму величину достоверности аппроксимации R^2».

Саме коефіцієнт апроксимації (детермінації) надасть нам можливість обрати краще рівняння. З курсу економетрії [4] відомо, що чим ближче коефіцієнт R² до одиниці, тим точніше рівняння тренду описує зміни ряду, а отже точніше буде прогнозне значення на основі цього рівняння.

При виконанні такого завдання не потрібно приводити графічні тренди. Замість цього слід надати у звіті таку таблицю:

Рис. 13. Оцінка якості тренду

Очевидно, що найкращий тренд надає поліноміальна залежність четвертої степені. Найгірші – логарифмічна та ступенева. Отже, обираємо для прогнозу рівняння y = 0,0004x^4 - 0,0057x^3 + 0,0174x^2 - 0,0032x + 5,1362. Саме для цього тренду у звіті слід надати графік (рис. 14).

Рис. 14. Рівняння тренду

За здобутим рівнянням прогнозне значення курсу дорівнює Y(9) = 5,136. Отже, якщо ціна глазурованого ізюму складала 2,07$ за курсом 5,09, то при його зростанні приблизно на 0,05 грн (це 0,01$) попит впаде за умовами задачі приблизно на 15 000 кг.

Таким чином можемо скорегувати модель, уточнивши попит, або встановити ціну ізюму 2,06$ для збереження об’ємів продаж.

Зауваження! При розрахунку тренду та прогнозу можна використовувати матеріал курсу «Економетрія». Цей факт надає можливість нарахування додаткових балів при захисті звіту практикуму.

Таблиця підстановки

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

Для цього будуємо таблицю, де заголовки стовпців це ціна ізюму, а заголовки рядків – ринкова вартість винограду (рис. 15).

Рис. 15. Формування таблиці підстановки

У ліву кутову комірку таблиці треба вписати формулу обчислення прибутку, але легше на її послатися – вписати у комірку F6 формулу =D29. Після цього слід виділити усю таблицю F6:P18 та обрати у меню «Данные» засіб «Таблиця подстановки». Далі треба заповнити діалогове вікно, що з’явилося.

Рис. 16. Діалогове вікно таблиці підстановки

Комірка D3 – це ціна глазурованого ізюму у нашій моделі, D6 – ринкова вартість винограду.

Рис. 17. Результат підстановки

Аналіз здобутих даних відкрив важливий факт: при ринковій ціні винограду від 0,15$ до 0,30$ для отримання максимального прибутку слід знижувати ціну ізюму, а от починаючи з 0,35$ ціну слід збільшувати. Максимальні значення прибутку для кожного значення ринкової вартості винограду у таблиці обведені рамками (рис. 17).