Анализ полученных трендов и прогнозирование

Лабораторная работа № 2

Тема: Технологии анализа и прогнозирования на основе трендов

Цель: изучение возможностей и формирование умения использования универсальной компьютерной технологии для решения задач выявления тенденций и прогнозирования развития процесса на основе моделирования рядов динамики (с помощью табличного процессора Excel).

Краткие сведения

Тренд — это функция заданного вида, с помощью которой можно аппроксимировать построенный по данным таблицы график. Тренд служит для выявления тенденций развития процесса, представленного в виде диаграммы, и обеспечивает прогноз на заданный период.

В MS Excel предусмотрено несколько стандартных типов тренда: линейный, логарифмический, степенной, экспоненциальный, полиномиальный, скользящее среднее. Необходимые условия для построения тренда:

- период времени, за который изучается исследуемый процесс, должен быть достаточным для выявления закономерности:

- тренд в анализируемый период должен развиваться эволюционно;

- процесс, представленный диаграммой, должен обладать определенной инертностью.

Тренд можно строить для диаграмм типа:

- линейчатый график,

- гистограмма,

- диаграмма с областями,

- XY-точеная диаграмма.

При установлении наиболее подходящего типа регрессионной зависимости для описания процесса изменения показателей какой-либо величины используют показатель достоверности описания функции. Тип регрессионной линии считается установленным, если величина достоверности аппроксимации R =1. Однако, если аппроксимации R² <0,6 уместно говорить о том, что тип зависимости для описания процесса изменения показателя не подходит.

Если ни в одном из вариантов исследуемых типов регрессионных линий (трендов) величина достоверности аппроксимации не равна единице, то выбирают тот тип, для которого величина достоверности аппроксимации максимальна.

Задание

На основании приведенных данных построить тренды и проанализировать, как описывают процесс динамики продаж линейная, логарифмическая, полиномиальная, степенная и экспоненциальная зависимости. Рассчитать прогноз на основе аппроксимирующих зависимостей, а также с помощью функций ПРЕДСКАЗ, РОСТ и TEНДЕНЦИЯ. Провести анализ с целью определения, какой из примененных методов дает более точный результат.

Постановка задачи,

Имеются две наблюдаемые величины х и у, например, объем реализации фирмы, торгующей кондитерскими изделиями, за ряд лет ее работы. Необходимо выяснить какая из наиболее распространенных функциональных зависимостей подходит для описания процесса реализации товара, и какого результата по объемам продаж можно ожидать в последующие годы работы фирмы. Для того чтобы построить прогноз развития какой-либо ситуации на практике зачастую необходимо знать закономерность изменения исследуемой величины или объекта.

Для выявления тенденций развития процесса продаж необходимо построить тренды и осуществить их анализ. Построим и проанализируем, как описывают процесс динамики продаж линейная, логарифмическая, полиномиальная, степенная и экспоненциальная зависимости.

Технология работы

1. В MS Excel создайте рабочую книгу с листами: Прогнозирование, Линейная, Логарифмическая, Полиномиальная, Степенная, Экспоненциальная и оформите лист Прогнозирование как показано на рис. 1.

Для правильности последующих вычислений в Ехсеl необходимо, чтобы значения периодов были представлены их номерами, начиная с 1 (ячейки А9:Аl9).

Рис. 1 Оформление листа с исходными данными

2. Исходным пунктом моделирования трендов является построение диаграммы.

На основе исходных данных, представленных в таблице, постройте точечную диаграмму со значениями, соединенными сглаживающими линиями без маркеров,

Для построения использовать Мастер диаграмм. Выберите подтип диаграммы «Точечную диаграмму с гладкими кривыми без маркеров». В качестве диапазонов значений для построения диаграммы взять несмежные диапазоны ячеек А8:А19 и С8:С19. В следующем шаге Мастера диаграмм обозначьте ось Х заголовком «Годы», а ось У — заголовком «Объем продаж». На этом же шаге расположите легенду внизу. Поместите диаграмму на имеющемся листе.

Рис.2. Вид оформления диаграммы по объемам реализации товара

Сохраните результат работы в файле.

3. Для свободного размещения на графике текстовых меток тренда, содержащих вид уравнения и коэффициент детерминации (величина достоверности аппроксимации R²), предварительно занесите график в буфер обмена и скопируйте его в начало других пяти листов (Линейная, Логарифмическая, Полиномиальная, Степенная, Экспоненциальная). Если у вас в книге недостает листов, выполните их вставку.

4. Построить линейный тренд для диаграммы. Для этого необходимо:

- установить указатель мыши на линии диаграммы и щелкнуть левой кнопкой мыши так, чтобы на линии появились черные метки

- для выделенной диаграммы вызвать контекстное меню, щелкнув правой кнопкой мыши; выполнить команду Добавить линию тренда

- в диалоговом окне Линия Тренда на вкладке Тип выбрать окно Линейная (рис. 3)

Рис.3. Построение линии тренда

установить следующие параметры (рис.4):

- название аппроксимирующей кривой: автоматическое

- прогноз: вперед на 2 периода;

- показывать уравнение на диаграмме: установите флажок;

- поместить на диаграмму величину достоверности аппроксимации; установите флажок.

- подтвердить действия нажатием кнопки "ОК"

Рис.4. Вид оформления диаграммы и линейного тренда

5. Перейдите на лист Логарифмическая. Постройте аналогичным образом логарифмический тренд для диаграммы.

Рис.5. Вид оформления диаграммы и логарифмического тренда

6. Постройте полиномиальный тренд для диаграммы. Для этого необходимо точно такие же шаги как при линефном и логарифмическом тренде. Единственное при выборе вида тренда выбирается полиномиальный и устанавливаете для полинома степень 2.

Рис.6. Вид оформления диаграммы и полиномиального тренда по динамике объема продаж товара

7. Аналогичным образом построить степенной и экспоненциальный тренды для диаграммы на соответствующих листах книги Excel.

Рис.7. Вид оформления диаграммы и степенного тренда по динамике объема продаж товара

Рис.8. Вид оформления диаграммы и экспоненциального тренда по динамике объема продаж товара

Анализ полученных трендов и прогнозирование

Конечный результат моделирования должен оцениваться пользователем с точки зрения здравого смысла на основе неформального комплекса знаний об условиях развития процесса, о допустимых предельных значениях показателя и т.п. В Excel для анализа трендов автоматически выводится только коэффициент детерминации (R²). Статистики-практики применяют метод сверки контрольных сумм теоретического (сглаженного по тренду) ряда признака с суммой значений исходного ряда. Однако для подсчета этих сумм сначала необходимо построить ряды теоретических значений показателя по найденным уравнениям трендов.

8. Перейдите на лист Прогнозирование.

Скопируйте метки трендов с диаграмм и вставьте их в соответствующие ячейки как показано на рис.9.

Рис.9. Оформление листа для анализа трендов

9. Введите формулы для вычисления значений аппроксимирующих функций в соответствующие ячейки D9, Е9, F9, 09, Н9 (рис.9.).

Скопируйте формулы вниз по столбцам и вставьте соответствующие формулы по строкам.

10. Произведите подсчет контрольных сумм в ячейках С20:Н20 (рис.10 )

Рис.10. Оформление листа для проведения анализа

В результате получили множество числовых рядов исходных данных, сглаженных по исследуемым трендам (D9:DI9; E9:E19; F9:F19; G9:G19; Н9:Н19), множество вспомогательных контрольных сумм (D20:Н20) для выявления наилучшего тренда путем сверки их с главной контрольной суммой (C20).

11. Поместите выводы из анализа полученных результатов исследования динамики продаж с помощью аппроксимации в этом же листе (рис.11.). Проанализировать построенные графики можно, например следующим образом:

Результаты по исследованию динамики продаж с помощью регрессионного анализа. Поскольку величина достоверности аппроксимации R² максимальна для регрессионной линии, описываемой полиномиальной зависимостью второй степени R²=0,5957, то эта зависимость. описываемая уравнением у=-0,5571х2+11,103х+131,19, где х - номер года, у - объем реализации за год, является наиболее подходящей для описания динамики продаж. Контрольная сумма объемов продаж за анализируемый период, вычисленная по этой зависимости. наиболee близка по значению 1893,9954 к контрольной сумме статистических данных объемов продаж 1894,0000. Вывод. Для прогнозирования объемов продаж следует воспользоваться полиномиальной зависимостью.

Рис.12. Оформление листа для анализа трендов с выводами.

12. Рассчитайте прогноз объема продаж на основе функций прогнозирования ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ и расположите результаты вычислений прогноза с помощью функций в соответствующих столбцах. При этом следует учитывать следуюшее.

Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные значения_у и известные значения_х. Возвращает значения у, в соответствии с этой прямой для заданного массива новые значения х. Синтаксис:

ТЕНДЕНЦИЯ(известные_значения_у;известные_значения_х;новые значения х;конст)

Известные_значения_у – множество значений у, которые уже известны для соотношения у = mx + b.

Функция РОСТ возвращает значения в соответствии с экспоненциальным трендом. Рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения у для последовательности новых значений х, задаваемых с помощью существующих х- и у-значений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих х- и у-значений экспоненциальной кривой. Синтаксис:

РОСТ(известные_значения_у;известные_значения_х; новые_значения_х; конст)

Известные_значения_у – это множество значений у, которые уже известны в соотношении у = b*m^x.

Функция ПРЕДСКАЗ возвращает значение линейного тренда. Вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение это у-значение, соответствующее заданному х-значению. Известные значения — это х- и у-значения, а новое значение предсказывается с использованием линейной регрессии. Эту функцию можно использовать для предсказания будущих продаж, потребностей в оборудовании или тенденций потребления. Синтаксис:

ПРЕДСКАЗ(х;известные_значения_у;известные_значения_х)

x – это точка данных, для которой предсказывается значение.

Известные_значения_у – это зависимый массив или интервал данных.

Известные_значения_х – это независимый массив или интервал данных.

13. Сделайте сравнительный анализ используемых методов прогнозирования.

14. Сохраните результаты работы в файле.

Индивидуальное задание

Создайте новую рабочую книгу.

1. Выберите таблицу с данными согласно своему индивидуальному варианту.

2. Сохраните результат работы в файл.

3. В ячейку А1 введите — описание переменной х, в ячейку В1 — описание переменной у,
4. Осуществите ввод исследуемых данных в столбцы А и В ниже описанных переменных.

5. Оформите созданную расчетную таблицу

6. Сохраните результат работы в файл.

7. Установить курсор в ячейку С1 и постройте диаграмму «Объем реализации продукции за наделю» по диапазону значений столбца В.

8. Произведите оформление построенной диаграммы

9. Сохраните результат работы в файл.

10. Выберите Зависимость 1 согласно индивидуальному варианту тип для первой линии тренда.

11. Постройте первый тренд для диаграммы.

12. Произведите настройку оформления вида полученного тренда

13. Выберите Зависимость 2 согласно индивидуальному варианту тип для второй линии тренда.

14. Постройте второй тренд для диаграммы.

15. Произведите настройку оформления вида построенных трендов

16. Произведите анализ полученных результатов.

17. Сохраните результат работы в файл.

18. Предъявите работу преподавателю.

Заключительные действия

19. Закройте все открытые файлы электронной таблицы.

20. Закончите работу с MS Excel.

Вариант 1.

День	1	2	3	4	5	6	7	8
Кол-во проданных ящиков деталей	13	19	29	30	37	44	49	55

Исследуемые зависимости: линейная, степенная.

Вариант 2.

Неделя	1	2	3	4	5	6	7	8	9	10
Кол-во поступивших упаковок продукции	9	16	20	27	34	39	44	52	58	64

Исследуемые зависимости: экспоненциальная, логарифмическая.

Вариант 3.

День	1	2	3	4	5	6	7	8	9
Кол-во отпущенных флаконов пеногерметика	7	17	19	28	35	42	41	52	57

Исследуемые зависимости: полиномиальная, экспоненциальная.

Вариант 4.

День	1	2	3	4	5	6	7	8	9
Кол-во заказанных пачек медикамента С	12	21	30	36	44	54	61	70	78

Исследуемые зависимости: логарифмическая, линейная.

Вариант 5.

Месяц	1	2	3	4	5	6	7	8	9	10	11
Кол-во заказов на переплетные изделия	12	17	23	32	35	40	48	54	59	65	72

Исследуемые зависимости: степенная, полиномиальная.

Вариант 6.

Час	1	2	3	4	5	6	7	8	9
Кол-во проданных бутылок напитка К	10	18	22	28	34	39	46	51	54

Исследуемые зависимости: линейная, экспоненциальная.

Вариант 7.

Неделя	1	2	3	4	5	6	7	8
Кол-во проданных подержанных машин	12	18	25	32	40	46	53	60

Исследуемые зависимости: экспоненциальная, линейная.

Вариант 8.

День	1	2	3	4	5	6	7	8	9
Кол-во заказов на хлебобулочные изделие N	14	23	30	39	45	54	63	70	78

Исследуемые зависимости: полиномиальная, линейная.

Вариант 9.

Месяц	1	2	3	4	5	6	7	8	9	10	11
Кол-во проданных сувениров А	15	22	26	33	40	45	52	58	63	69	78

Исследуемые зависимости: логарифмическая, экспоненциальная.

Вариант 10.

Неделя	1	2	3	4	5	6	7	8
Кол-во заказов на установку машинной сигнализации	9	15	24	29	38	46	52	58

Исследуемые зависимости: степенная, логарифмическая.

Вариант 11.

Неделя	1	2	3	4	5	6	7	8	9	10	11
Кол-во заказов на ремонт стиральных машин	9	12	17	23	30	36	40	48	54	65	76

Исследуемые зависимости: линейная, полиномиальная.

Вариант 12.

День	1	2	3	4	5	6	7	8
Кол-во абитуриентов интересующихся специальностью Z	13	19	26	30	37	44	49	55

Исследуемые зависимости: экспоненциальная, линейная.

Вариант 13.

Месяц	1	2	3	4	5	6	7	8
Кол-во заказов на литературу типа X	12	18	25	32	40	46	53	60

Исследуемые зависимости: полиномиальная, экспоненциальная.

Вариант 14.

День	1	2	3	4	5	6	7	8	9
Кол-во проданных флаконов шампуня В	7	17	19	28	35	42	41	52	57

Исследуемые зависимости: логарифмическая, линейная.

Вариант 15.

Неделя	1	2	3	4	5	6	7	8
Кол-во проданных ящиков кондитерской продукции типа Ш	9	15	24	29	38	46	52	58

Исследуемые зависимости: степенная, полиномиальная.

Дата добавления: 2019-03-09; просмотров: 453; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!