Результаты по исследованию динамики продаж с помощью регрессионного анализа

Поскольку величина достоверности аппроксимации R² максимальна для регрессионной линии, описываемой полиномиальной зависимостью второй степени R²=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

Вариант 9

Вариант 10

Вариант 11

Вариант 12

Вариант 13

.

Вариант 14

Вариант 15

Контрольные вопросы

1. Что отражает величина достоверности аппроксимации?

2. Дайте определение тренда.

3. В каких случаях необходимо использовать построение трендов?

4. На основе каких данных выбирается наилучшая регрессионная линия?

5. Как изменить формат представления регрессионной линии?

6. Какие типы регрессионных зависимостей Вам известны?

7. Опишите действия необходимые для построения линии тренда по построенной диаграмме.

8. Возможен ли ретроспективный анализ данных с использованием линий тренда?

9. Возможно ли использование регрессионных зависимостей при решении задач по оптимизации ресурсов и запасов?

10. Опишите ситуации, в которых правомочно представление нескольких графиков в одной системе координат.