Анализ полученных трендов и прогнозирование
Конечный результат моделирования должен оцениваться пользователем с точки зрения здравого смысла на основе неформального комплекса знаний об условиях развития процесса, о допустимых предельных значениях показателя и т.п. В Excel для анализа трендов автоматически выводится только коэффициент детерминации (R2). Статистики-практики применяют метод сверки контрольных сумм теоретического (сглаженного по тренду) ряда признака с суммой значений исходного ряда. Однако для подсчета этих сумм сначала необходимо построить ряды теоретических значений показателя по найденным уравнениям трендов.
8. Перейдите на лист Прогнозирование.
Скопируйте метки трендов с диаграмм и вставьте их в соответствующие ячейки как показано на рис.10.
Рис.10. Оформление листа для анализа трендов
9. Введите формулы для вычисления значений аппроксимирующих функций в соответствующие ячейки D9, E9, F9, G9, H9 (рис.10.).
Скопируйте формулы вниз по столбцам.
10. Произведите подсчет контрольных сумм в ячейках С20:H20 (рис.11)
Рис.11. Оформление листа для проведения анализа
В результате получили множество числовых рядов исходных данных, сглаженных по исследуемым трендам (D9:D19; E9:E19; F9:F19; G9:G19; H9:H19), множество вспомогательных контрольных сумм (D20:H20) для выявления наилучшего тренда путем сверки их с главной контрольной суммой (C20).
11. Поместите выводы из анализа полученных результатов исследования динамики продаж с помощью аппроксимации в этом же листе (рис.12.). Проанализировать построенные графики можно, например следующим образом:
Результаты по исследованию динамики продаж с помощью регрессионного анализа.
Поскольку величина достоверности аппроксимации R2 максимальна для регрессионной линии, описываемой полиномиальной зависимостью второй степени R2=0,5957, то эта зависимость, описываемая уравнением
y=-0,5571x2+11,103x+131,19,
где х - номер года,
у - объем реализации за год,
является наиболее подходящей для описания динамики продаж.
Контрольная сумма объемов продаж за анализируемый период, вычисленная по этой зависимости, наиболее близка по значению 1893,9954 к контрольной сумме статистических данных объемов продаж 1894,0000.
Вывод. Для прогнозирования объемов продаж следует воспользоваться полиномиальной зависимостью.
Рис.12. Оформление листа для анализа трендов с выводами.
12. Рассчитайте прогноз объема продаж на основе функций прогнозирования ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ и расположите результаты вычислений прогноза с помощью функций в соответствующих столбцах. При этом следует учитывать следующее.
Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x. Синтаксис:
ТЕНДЕНЦИЯ(известные_значения_y;известные_значения_x;новые_значения_x;конст)
Известные_значения_y — множество значений y, которые уже известны для соотношения y = mx + b.
Функция РОСТ возвращает значения в соответствии с экспоненциальным трендом. Рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x - и y -значений. Функция рабочего листа РОСТ может применяться также для для аппроксимации существующих x- и y-значений экспоненциальной кривой. Синтаксис:
РОСТ(известные_значения_y;известные_значения_x;новые_значения_x;конст)
Известные_значения_y — это множество значений y, которые уже известны в соотношении y = b*m^x.
Функция ПРЕДСКАЗ возвращает значение линейного тренда. Вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y-значение, соответствующее заданному x-значению. Известные значения — это x- и y-значения, а новое значение предсказывается с использованием линейной регрессии. Эту функцию можно использовать для предсказания будущих продаж, потребностей в оборудовании или тенденций потребления. Синтаксис:
ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)
x — это точка данных, для которой предсказывается значение.
Известные_значения_y — это зависимый массив или интервал данных.
Известные_значения_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
| День | ||||||||
| Количество проданных ящиков деталей |
Вариант 2
| Неделя | ||||||||||
| Количество поступивших упаковок продукции |
Вариант 3
| День | |||||||||
| Количество отпущенных флаконов пеногерметика |
Вариант 4
| День | |||||||||
| Количество заказанных пачек медикамента С |
Вариант 5
| Месяц | |||||||||||
| Количество заказов на переплетные работы |
Вариант 6
| Час | |||||||||
| Количество проданных бутылок напитка К |
Вариант 7
| Неделя | ||||||||
| Количество проданных подержанных машин |
Вариант 8
| День | |||||||||
| Количество заказов на хлебобулочное изделие N |
Вариант 9
| Месяц | |||||||||||
| Количество проданных сувениров А |
Вариант 10
| Неделя | ||||||||
| Количество заказов на установку машинной сигнализации |
Вариант 11
| Неделя | |||||||||||
| Количество заказов на ремонт стиральных машин |
Вариант 12
| День | ||||||||
| Количество абитуриентов интересующихся специальностью Z |
Вариант 13
| Месяц | ||||||||
| Количество заказов на литературу типа Х |
.
Вариант 14
| День | |||||||||
| Количество проданных флаконов шампуня В |
Вариант 15
| Неделя | ||||||||
| Количество проданных ящиков кондитерской продукции типа Ш |
Контрольные вопросы
1. Что отражает величина достоверности аппроксимации?
2. Дайте определение тренда.
3. В каких случаях необходимо использовать построение трендов?
4. На основе каких данных выбирается наилучшая регрессионная линия?
5. Как изменить формат представления регрессионной линии?
6. Какие типы регрессионных зависимостей Вам известны?
7. Опишите действия необходимые для построения линии тренда по построенной диаграмме.
8. Возможен ли ретроспективный анализ данных с использованием линий тренда?
9. Возможно ли использование регрессионных зависимостей при решении задач по оптимизации ресурсов и запасов?
10. Опишите ситуации, в которых правомочно представление нескольких графиков в одной системе координат.