Зробіть прогноз за допомогою трендових моделей.

Є неповні дані стосовно динаміки розглядуваного показника у 2010 році (див. таблицю 1.1). Необхідно додати графік 2010 року на побудовану діаграму та за допомогою побудови лінії тренду обраної математичної моделі отримати прогнозні значення показника у листопаді та грудні 2010 року з певною достовірністю.

Для того, щоб «добудувати» графік на раніше створену діаграму, можна або скопіювати необхідні дані разом з міткою (іменем нового ряду) в буфер обміну та вставити на діаграму за допомогою підпункту „ Специальная вставка ” пункту меню „ Правка”, або скористуватися пунктом меню „ Диаграмма ”, підпунктом „ Добавить данные … ”.

3.1. Перший засіб – копіювання даних нового графіку:

- виділіть необхідний ряд даних (діапазон A6:K6) і скопіюйте його в буфер обміну (пункти меню „ Правка ” – „ Копировать ”, або підпункт контекстного меню „ Копировать ”, або відповідна кнопка панелі інструментів „ Стандартная ”);

- переключіться на лист з діаграмою;

- виберіть пункт меню „ Правка ”, підпункт „ Специальная вставка ”, або відкрийте список команд кнопки „ Вставить ” панелі інструментів „ Стандартная ” та виберіть команду „ Специальная вставка ”;

·
у вікні діалогу, що виникне, задати потрібний параметр – ввімкнути (активізувати) прапорець „ Имена рядов в первом столбце ” (див. рис. 1.11).

Рисунок 1.11 – Діалогове вікно „Специальная вставка”

Натисніть на кнопку „ ОК ”.

3.2. Другий засіб – додавання даних графіку через пункт меню „Диаграмма”, підпункт „Добавить данные …”:

- переключіться на лист з діаграмою;

- виберіть пункт меню „ Диаграмма ”, підпункт „ Добавить данные … ”;

- натисніть на кнопку згортки, яка знаходиться справа в полі „ Диапазон ”, потім натисніть на ярлик листа з даними („ Лист 1 ”) та виділіть необхідний діапазон A6:K6 (див. рис. 1.12), включаючи ім’я (мітку) „ 2010 ”;

- натисніть на кнопку розгортки;

- натисніть на кнопку „ ОК ”.

3.3. Для побудови прогнозної кривої (лінії тренду) на новому графіку:

- виділіть криву, для якої виконується прогноз – графік „ 2010 ”;

- виберіть пункт меню „ Диаграмма ”, підпункт „ Добавить линию тренда ” або викличте контекстне меню і виберіть пункт „ Добавить линию тренда ”;

- у вікні діалогу, що з'явиться, виберіть „ Тип ” лінії тренду з 6 запропонованих - „ Полиномиальная ”, за допомогою лічильника введіть ступінь поліному – „ 6 ”, максимальний в MS Excel (див. рис. 1.13).

Рисунок 1.12 – Процес виділення додаткового ряду при додаванні графіку через пункт меню „Диаграмма”, підпункт „Добавить данные …”

Рисунок 1.13 – Діалогове вікно вибору типу лінії тренду та параметрів

Поліноміальний тип обраний, тому що графік „ 2010 ” має типовий для поліномів вигляд (дані поперемінно зростають і понижуються) і найбільш близька його апроксимація ймовірно буде саме за допомогою цієї лінії тренду. Ступінь „ 6 ” обрано, тому що графік „ 2010 ” містить 5 екстремумів (мінімумів та максимумів), а поліном n-го ступеню може описати кількість екстремумів, що дорівнює n – 1.

- оберіть вкладку „ Параметры ” – за її допомогою можна задати додаткові параметри, що впливають на відображення кривої, яка виконує апроксимацію;

- за допомогою лічильника введіть прогноз „ вперед на: ” – „ 2 ” періоди, тобто місяці листопад та грудень;

- зробіть активним прапорець „ показывать уравнение на диаграмме ” – це дасть можливість отримати рівняння поліному, що описує прогнозну криву;

- зробіть активним прапорець „ поместить на диаграмму величину достоверности аппроксимации (R^2) ”. За допомогою коефіцієнта детермінації R² оцінюється ступінь близькості апроксимації вхідних даних обраною функцією. Якщо є декілька варіантів типів апроксимуючих функцій, які підходять, та немає яких-небудь теоретичних міркувань, можна обрати функцію з найбільшим коефіцієнтом детермінації (таким, що стримиться до 1). При оцінці точності (достовірності) апроксимації та прогнозу говорять, що вона достатньо висока, якщо R² близький до 1. Або що прогноз зроблений з достовірністю R². Прогнозні значення за листопад та грудень можна визначити, спроектувавши відповідні точки на ось значень Y. Для листопада це приблизно 7000, а для грудня 14000, з достовірністю R² = 0.73. Достовірність досить низька (менше 0.9), тому, ймовірно, необхідно шукати інші методи прогнозу.

Натисніть кнопку „ ОК ”. Отриману діаграму разом з кривою апроксимації див. на рис. 1.14.

Рисунок 1.14 – Діаграма – графіки разом з лінією тренду (прогнозною)