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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание

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

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

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

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

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

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

Пример выполнения.

1. Ввести данные.

2. Построить график с помощью мастера Диаграмм:

1.Вставка

2.Диаграмма

3.Точечная диаграмма (выбрать тип диаграммы)

3. Добавить другие линии тренда. Для этого, щелкнув правой клавишей мыши, выбрать «добавить линию тренда». Выбрать «Линейную». На диаграмме должно отобразиться уравнение линейной функции и величина коэффициента достоверности R^2.

Замечание: обязательно поставить флажки напротив:

4. Поочередно построить 2-3 линии тренда, сохраняя на графике их уравнения и параметр R^2.

Та линия, у которой величина R^2 близка к еденице и отражает наиболее достоверно функциональную зависимость заданных значений.

5. В данном примере заданные значения описываются зависимостью вида: y=x^2-1, т.к. коэффициент достоверности R^2=1.

Индивидуальные задания

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

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

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

Вариант 9

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

Вариант 10

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

Вариант 11

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

Вариант 12

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

Вариант 13

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

Вариант 14

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

Вариант 15

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

Вариант 16

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

Вариант 17

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

Вариант 18

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

Вариант 19

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

Вариант 20

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