Подобрать линию тренда, которая лучше всего описывает фактические данные и на ее основе сделать прогноз на 3 недели вперед

Решение:

1. Ввод исходных данных задачи. В ячейки A1 и B1 введем заголовки исходных данных, в ячейки A2:A12 – номера недель, а в ячейки B2:B12 – соответствующее количество продаж (фактические данные).

2. Построение графика фактических значений показателя. Выделим ячейки B1:B12 (исходные данные вместе с заголовком) и вызовем мастер диаграмм, нажав кнопку на панели инструментов. Построим с его помощью диаграмму типа График:

3. Изображение на графике кривой роста линейной модели. Выполним один щелчок по диаграмме для того, чтобы перейти в режим ее редактирования. Затем подведем курсор к какой-либо точке на графике и снова щелкнем левой кнопкой мыши. Ряд данных на графике выделяется желтым цветом. Затем нужно нажать правую кнопку мыши для вызова контекстного меню (перемещать курсор мыши после выделения ряда нельзя!). В контекстном меню выберем команду Добавить линию тренда.

На экране появляется окно Линия тренда:

В окне Линия тренда на вкладке Тип выберем Линейная, а на вкладке Параметры нужно установить следующие флажки:

· показывать уравнение на диаграмме;

· поместить на диаграмму величину достоверности аппроксимации (R ^2).

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

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

5. Выполнение прогноза. Поскольку нужно выполнить прогноз на 3 недели вперед, допишем номера этих недель (12, 13 и 14) в столбец A. В соответствующие ячейки в столбце B следует занести формулы вычисления теоретического значения по функции тренда. При этом можно записывать сразу числовые значения параметров. Обратите внимание, что уравнение экспоненциальной кривой, полученное на графике, имеет вид

Y = a × e^bt.

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

=16,257*EXP(0,1361*A13).

Затем эту формулу можно скопировать в ячейки B14 и B15 с помощью автозаполнения. В результате получим в ячейках B13:B15 следующие прогнозы:

· на 12-ю неделю – 83 продажи;

· на 13-ю неделю – 95 продаж;

· на 14-ю неделю – 109 продаж.