Лабораторная работа 9. Прогноз с использованием линии тренда
Excel позволяет сделать приблизительный прогноз при наличии данных за определенный промежуток времени с помощью линии тренда. Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Это форма статистического анализа, используемого для прогнозов. Регрессионный анализ позволяет оценить степень связи между переменными, предлагая механизм вычисления предполагаемого значения переменной из нескольких уже известных значений. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.
Настройки Excel позволяют дополнить линию тренда значением квадрата смешанной корреляции - R в квадрате. Коэффициент R в квадрате отражает близость значений линии тренда к фактическим данным и представляет собой значение в пределах от 0 до 1. Линия тренда в наибольшей степени приближается к представленной на диаграмме зависимости, если значение равно или близко к 1. При аппроксимации данных с помощью линии тренда значение R-квадрат рассчитывается автоматически. Полученный результат можно вывести на диаграмме.
Рассмотрим пример. Используя данные о поступлениях абитуриентов на направления подготовки кафедры систем управления и вычислительной техники за 2009 – 2012 годы (см. таблицу), спрогнозировать число поступающих на 2014 год (построить линию тренда).
Для того чтобы построить линию тренда, необходимо сначала построить график, отражающий динамику числа абитуриентов за приведенный период:
Чтобы построить линию тренда, нужно курсор навести на ломаную, вызвать контекстное меню и выбрать опцию Добавить линию тренда. В новом окне выбрать Линейный тип линии тренда, в группе опций Прогноз установить вперед на: в значение 2 (поскольку нас интересует 2014 год), поставить галочку напротив условия поместить на диаграмму величину достоверности аппроксимации. После закрытия окна увидим:
На графике появилась прямая линия, показывающая, что в 2014 году поступающих на направления обучения кафедры СУиВТ по всем формам обучения будет около 160 человек. Коэффициент достоверности аппроксимации составляет 0,279. Это свидетельствует о том, что вероятность получения спрогнозированного числа составляет 28%.
Задания
Решить следующие задачи:
Задача 1. Проанализировать продажи макаронных изделий за 3 месяца. Исходные данные приведены в таблице:
Продажи: январь - март
Наименование товара
|
январь
|
февраль
|
март
|
Сумма
|
Уд. вес
|
Лапша
|
25000
|
34000
|
66521
|
|
|
Рожки
|
20000
|
76548
|
87654
|
|
|
Ушки
|
14000
|
10876
|
11113
|
|
|
Вермишель
|
47651
|
57654
|
90870
|
|
|
Спагетти
|
164198
|
875432
|
870965
|
|
|
Ракушки
|
112233
|
165743
|
111654
|
|
|
Сумма
|
|
|
|
|
|
Рассчитать значения пустых ячеек.
Определить объем продаж лапши в июне с помощью линии тренда.
Определить коэффициент достоверности прогноза.
Задача 2. Проанализировать расходы на коммунальные услуги за 3 месяца. Исходные данные приведены в таблице:
Рассчитать значения пустых ячеек.
Определить расходы на оплату за пользование телефоном в мае.
Рассчитать коэффициент достоверности прогноза.
Задача 3. Исходные данные смотрите в таблице:
-
В тыс. руб.
|
Товарооборот за 2012 год
|
Сумма
налога
|
Филиал
|
Январь
|
Февраль
|
Март
|
Апрель
|
Май
|
Июнь
|
Июль
|
Август
|
Сентябрь
|
Октябрь
|
Ноябрь
|
Декабрь
|
|
|
|
|
1
|
2250
|
2354
|
2587
|
2459
|
2545
|
2354
|
2201
|
2502
|
2498
|
2526
|
2654
|
2547
|
|
|
2
|
125
|
354
|
541
|
541
|
654
|
654
|
320
|
302
|
425
|
489
|
347
|
410
|
|
|
3
|
845
|
785
|
859
|
941
|
1105
|
747
|
754
|
706
|
902
|
899
|
821
|
854
|
|
|
6
|
654
|
458
|
687
|
645
|
510
|
741
|
321
|
548
|
654
|
507
|
651
|
700
|
|
|
7
|
1154
|
1472
|
1587
|
1345
|
1341
|
1852
|
1747
|
1856
|
1759
|
1801
|
1954
|
1820
|
|
|
Итого
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Чистая прибыль _________
Определить товарооборот за год.
Рассчитать сумму налога, которая равна 15% от суммы товарооборота.
Определить чистую прибыль.
Спрогнозировать товарооборот предприятия за май 2014 года.
Показать результат преподавателю
|
