Лабораторна робота 20

Обчислення в MS Excel 2016.

Функції прогнозування TREND, GROWTH, FORECAST, LINEST

Функція TREND повертає значення за лінійним трендом. Вона відповідає прямій (використовуючи метод найменших квадратів).

Функція GROWTH повертає значення y для рядів нових значень x, указаних із використанням наявних значень x і y.

Функція FORECAST прогнозує майбутнє значення на основі заданих значень.

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

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

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

Рис.1

Завдання 1. Провести прогноз об’ємів продаж ковбасних виробів у 2021-2025 роках, використовуючи функцію TREND.

Рис.2

Для того, щоб розрахунки були максимально наближені до точних, доцільно закріпити області початкових точок розрахунку у формулі. Для цього необхідно поставити курсор у рядку формул перед С3 і натиснути F4, далі поставити курсор перед С2 і натиснути F4. Результат на рис. 3.

Рис.3

Для того, щоб значення зявились у наступних комірках, необхідно курсор поставити у N3 та коли зявиться зелена рамка на клітинці, взяти за нижній правий куточок і розтягти формули у потрібну кількість клітинок. Результат на рис.4

Рис.4.

Завдання 2. Провести прогноз об’ємів продаж ковбасних виробів у 2021-2025 роках, використовуючи функцію GROWTH, рис.5.

Рис.5

Результат розрахунків, рис.6

Рис.6

Завдання 3. Провести прогноз об’ємів продаж ковбасних виробів у 2021-2025 роках, використовуючи функцію FORECAST, рис.7.

Рис.7

Результат розрахунків, рис.8

Рис.8

Завдання 4. Провести прогноз об’ємів продаж ковбасних виробів у 2021-2025 роках, використовуючи функцію LINEST, рис.9.

Функція LINEST прогнозує показники за допомогою методу «найменших квадратів». Іншими словами, для того щоб здійснити прогнозування необхідно побудувати пряму

Y=mX+b (1)

Крім того, що Х відомі необхідно визначити m та b (рис.9). Спочатку для розрахунку потрібно визначити коефіцієнт m, для цього в комірку D2 необхідно записати формулу ={LINEST(B2:B12;A2:A12)}. Коефіцієнт m розрахований, в нашому прикладі це число -0,241818 (рис.10). Далі визначаємо коефіцієнт b: для отримання результату в Е2 необхідно виділити клітинки D2E2, курсор поставити зверху в рядку формул і натиснути Ctrl+Shift+Enter (рис.11). В результаті виконаних дій b=-477,818 (рис.12).

Рис.9

Рис.10

Рис.11

Рис.12

Далі переходимо до розрахунку прогнозованих показників у комірках B13-B17 (рис. 13).

У комірку B13 записуємо формулу =$D$2хA13+$E$2, яка автоматично розраховує прогнозований показник. В інші комірки копіюємо названу формулу. Отримуємо прогнозовані показники зміни об’ємів продаж ковбасних виробів у 2021-2025 роках (рис.14).

Рис.13

Рис.14

Завдання 5. Провести прогноз об’ємів продаж ковбасних виробів у 2021-2025 роках, використовуючи функцію FORECAST.ETS, рис.15.

Є таблиця з даними, рис. 15

Рис.15

Для побудови прогнозу необхідно перейти в головному меню на Дані (рис.16) виділити діапазон комірок C2:M3 та натиснути на Аркуш прогнозу

Рис.16

Результатом названих дій буде побудова аркушу прогнозу (рис. 17). В рядку Завершення прогнозу необхідно вказати 2025 рік, а потім натиснути кнопку Створити

Рис.17

Буде побудована таблиця та графік прогнозу, рис.18

Рис.18

В таблиці у стовпчику С доцільно округлити числа до потрібної кількості знаків після коми.

А на графіку додати підписи даних. Для цього курсор поставити на лінію графіка і натиснути праву клавішу мишки, рис. 19

Рис.19

Отриманий результат на рис. 20

Рис.20

Завдання 6. Провести прогноз об’ємів виробництва борошна у 2021-2025 роках, використовуючи функції TREND, GROWTH, FORECAST, LINEST та FORECAST.ETS.

	Х	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
Виробництво борошна, тис. т.	У	2315,4	2042,1	2531,7	2368,9	2422,5	2056,4	1974,6	1991,3	1746,2	1737,6	1703,5

Завдання 7. Провести прогноз об’ємів виробництва яблучного соку у 2021-2030 роках, використовуючи функції TREND, GROWTH, FORECAST, LINEST та FORECAST.ETS.

	Х	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
Виробництво яблучного соку, млн. л	У	102,2	95,4	111,3	78,9	83,2	86,1	74,6	74,1	105,9	101	101,7