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

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

Рис. 1 – Экранная форма задания 2

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

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

Для построения следует использовать Мастер диаграмм (Вставка / Диаграммы). Выберите подтип диаграммы «Точечная с маркерами». В качестве диапазонов значений для построения диаграммы выделите диапазон ячеек А8:В19. Обозначьте ось Х заголовком «Номер года», а ось У - заголовком «шт.». Расположите легенду снизу. Поместите диаграмму на имеющемся листе (рис. 2, 3).

Рис. 2 – Выбор подтипа диаграммы

Рис. 3 – Диаграмма исходных данных

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

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

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

2) в контекстном меню выполнить команду Добавить линию тренда;

3) в диалоговом окне Формат линии трендавыбрать с помощью селекторной кнопки Линейная (рис.4);

4) установить следующие параметры: название аппроксимирующей кривой: автоматическое, прогноз: вперед на 1 период;

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

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

7) подтвердить действия нажатием кнопки «Закрыть» (см. рис. 4).

Рис. 4 – Диалоговое окно «Формат линии тренда»

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

Рис. 5 – Логарифмический тренд

6. Постройте полиномиальный тренд для диаграммы на листе Полиномиальный(рис 6). Не рекомендуется использование степени выше 4.

Рис. 6 – Полиномиальный тренд

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

Рис. 7 – Степенной тренд

Рис. 8 – Экспоненциальный тренд

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

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

Скопируйте формулы трендов и коэффициенты аппроксимации с диаграмм и вставьте их в соответствующие ячейки (D4:H5) как показано на рис. 9.

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

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

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

Рис. 9 – Экранная форма листа «Прогнозирование»
после копирования формул

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

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