Задание 5. Построение линии тренда

Раздаточный материал к лабораторным работам по дисциплине «Информационные системы в экономике»

 

Тема: Задачи анализа и прогнозирования

Цель занятия. Изучение возможностей использования средств MS Excel в целях прогнозирования.

Задание 1. Применение средств деловой графики для прогнозирования.

Вычислить прогнозные значения месячных объемов продаж на январь-июнь 2009 года при условии, что сохранятся существующие тенденции уменьшения производственных затрат и возрастания расходов на рекламу. Это условие означает, что надо также сделать прогноз на эти месяцы значений производственных затрат и затрат на рекламу. Исходные данные приведены на рис. 31

Рис. 31. Пример рабочего листа

Порядок работы

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

Рис. 32. Точечный график

2. Щелкните где-нибудь на диаграмме, чтобы выделить ее.

3. Выберите команду Диаграмма-Добавить линию тренда, чтобы открыть диалоговое окно Линия тренда.

4. В диалоговом окне Линия тренда на вкладке Тип выберите тип линии тренда Степенная (рис.33).

Рис. 33. Диалоговое окно «Линия тренда»

5. На вкладке Параметры диалогового окна Линия тренда предлагается

• определить название линии тренда, которое будут включено в легенду (с помощью переключателей в области Название аппроксимирующей (сглаженной) кривой),

• задать количество периодов, на которые будут прогнозироваться данные (счетчики в области Прогноз).

Две дополнительные опции позволяют отобразить на диаграмме

• уравнение линии тренда (опция Показывать уравнение на диаграмме);

• значение коэффициента детерминации R², определяющее достоверность аппроксимации (опция Поместить на диаграмму величину достоверности аппроксимации (R ^{^}2)).(рис.34).

Рис. 34. Вкладка «Параметры»

6. После того как выбран тип линии тренда и сделаны установки на вкладке Параметры, щелкните на кнопке ОК, чтобы получить линию тренда и прогноз на столько периодов, сколько вы указали (рис.35).

Рис. 35. Линия тренда

Коэффициент детерминации R² характеризует степень близости линии тренда к исходным данным. Он может принимать значения от 0 до 1. Чем больше его значение, тем лучше линия тренда аппроксимирует исходные данные. Чтобы получить числовые значения прогноза, можно поступать следующим образом.

Во-первых, вычисленные числовые значения можно получить визуально из графика линии тренда, если увеличить размер области построения диаграммы и сделать погуще линии сетки.

Во-вторых, можно воспользоваться приведенным уравнением линии тренда. В нашем случае оно имеет вид Y = 993,41Х^0,3351. Подставляя сюда поочередно вместо X номера периодов 26, 27,..., 30, получим искомые прогнозируемые значения. Итак, получили первый прогноз, который запишем в отдельную таблицу (рис.36).

 

Рис. 36. Прогноз объема продаж

Коэффициент детерминации R² в данном случае равен 0,7362.

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

 

Задание 2. Применение встроенных функций для прогнозирования

 

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

Функция ПРЕДСКАЗ имеет синтаксис:

=ПРЕДСКАЗ(х;Значения_Y;Значения_Х)

Здесь:

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

аргумент Значения_Y — одномерный массив значений переменной Y (или ссылка на диапазон ячеек, содержащий этот массив),

аргумент Значения_Х — массив значений фактора X (или ссылка на диапазон ячеек, содержащий этот массив).

Функции ТЕНДЕНЦИЯ и РОСТ имеют одинаковый синтаксис:

=ТЕНДЕНЦИЯ(Значения_Y;Значения_Х;Новые_значения_х;Константа)

=РОСТ(Значения_Y;Значения_Х;Новые_значения_х;Константа)

Здесь:

аргумент Значения_Y — одномерный массив значений переменной Y (или ссылка на диапазон ячеек, содержащий этот массив),

аргумент Значения_Х — массив значений факторов X,, Х₂,..., X_k (или ссылка на диапазон ячеек, содержащий этот массив),

аргумент Новые_значения_х — значения факторов, для которых вычисляется прогнозное значение,

аргумент Константа принимает логическое значение: если он имеет значение ИСТИНА или 1 либо опущен, то коэффициент уравнения регрессии b₀ вычисляется как обычно; если же он имеет значение ЛОЖЬ или 0, то коэффициент b₀ полагается равным 0, и значения коэффициентов уравнения регрессии вычисляются с учетом этого условия.

Если в функциях ТЕНДЕНЦИЯ и РОСТ аргумент Значения_Х опущен, то предполагается, что это массив натуральных чисел {1; 2; 3;...} такого же размера, как и массив аргумента Значения_Y. Если опущен аргумент Новые_значения_х, то по умолчанию предполагается, что он совпадает с аргументом Значения_Х.

Эти функции можно использовать для одновременного вычисления массива прогнозных значений по заданному массиву {х} значений факторов, для чего в качестве аргумента х надо указать массив {х}, а саму функцию применить как формулу массива (нажав комбинацию клавиш <Ctrl+Shift+Enter>) к выделенному диапазону ячеек, в котором будет записан выходной массив прогнозных значений.

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

 

Порядок работы

 

1. Создайте рабочий лист как показано на рис.37.

Рис. 37. Пример рабочего листа

2. Для вычисления прогнозных значений производственных затрат выделите диапазон ячеек F2:F7, введите функцию =ПРЕДСКАЗ(E2:E7;B2:B25;A2:A25)и нажмите комбинацию клавиш <Ctrl+Shift+Enter>.

3. Для вычисления прогнозных значений рекламных затрат выделите диапазон ячеек G2:G7, введите функцию =РОСТ(C2:C25; A2:A25;E2:E7)и нажмите комбинацию клавиш <Ctrl+Shift+Enter>.

4. Для вычисления прогнозных значений объемов продаж выделите диапазон ячеек H2:H7, введите функцию =ТЕНДЕНЦИЯ(D2:D25;A2:A25;E2:E7)и нажмите комбинацию клавиш <Ctrl+Shift+Enter>.

Результаты вычислений прогнозных значений приведены на рабочем листе (рис.38).

Рис. 38. Прогнозные значения

Задание 3. Определение коэффициентов линейной регрессии

Вычислить прогнозные значения месячных объемов продаж на январь-июнь 2009 года с помощью уравнения регрессии первой степени y= mx+b₀ и построить график, используя данные рис.36.

Вычисление коэффициентов линейной регрессии производится с помощью встроенной функции ЛИНЕЙН. Эта функция имеет следующий синтаксис:

=ЛИНЕЙН(Значения_Y;Значения_Х;Константа;Статистика)

Здесь аргумент Значения_Y — одномерный массив (или ссылка на диапазон ячеек, содержащий этот массив) значений переменной Y. Необязательный аргумент Значения_Х — массив (или ссылка на диапазон ячеек, содержащий этот массив) значений факторов X. Если данный аргумент опущен, предполагается, что это массив натуральных чисел {1; 2; 3;...} такого же размера, как и массив Значения_Y. Аргумент Константа — логическое значение, которое указывает, должен ли коэффициент b₀ быть равным 0. Если этот аргумент имеет значение ИСТИНА, 1 или опущен, то коэффициент b₀ вычисляется как обычно. Если аргумент имеет значение ЛОЖЬ или 0, то b₀ полагается равным 0, и значения коэффициентов m_i подбираются с учетом этого условия.

Аргумент Статистика принимает логическое значение, которое указывает, требуется ли рассчитывать дополнительные статистические характеристики регрессии. Если этот аргумент имеет значение ИСТИНА или 1, то функция рассчитывает и выводит эти дополнительные характеристики. Если аргумент Статистика имеет значение ЛОЖЬ, 0 или опущен, то функция возвращает только значения коэффициентов т_i и b₀.

 

Порядок работы

1. Создать рабочий лист (рис.39).

Рис. 39. Пример рабочего листа

2. Выделить две смежные ячейки, например, C2:D2.

3.Ввести функцию =ЛИНЕЙН(B2:B25;A2:A25) и нажать комбинацию клавиш <Ctrl+Shift+Enter>. Тогда в ячейке C2 запишется коэффициент m, а в D2 коэффициент b₀.

4. В ячейку B27 ввести формулу =$C$2*A26+$D$2 и скопировать ее до ячейки B31. Полученные результаты представлены на рис.40.

Рис. 40. Определение коэффициентов линейной регрессии

5. По полученным прогнозным значениям объемов продаж (диапазон B27:B31) построить график (рис.41).

Рис. 41. График объемов продаж на 26-30 периоды

Задание 4. Полиномиальная аппроксимация.

Найти уравнения регрессии первой, второй и третьей степени для исходной функции Y(X), представленной на рис.40, используя средство Поиск решения, ипостроить их кривые.

Порядок работы

 

1.Создать рабочий лист(рис42).

Рис. 42. Пример рабочего листа

2. В диапазоне B8:B17 вычислить квадраты погрешности между фактическим значением Y и полученным из уравнений регрессии первой степени. В общем случае это выражение вида: (<Функция Y(x)> - <Функция регрессии>) ². Для уравнения первого порядка (прямой) (< Функция Y(x)> - (a+bx))². Таким образом, для первой клетки погрешности Прямой - С8 ввести выражение: =(В8-($В$3+$С$3*А8))^{^}2.

3. В ячейке С18 вычислить сумму погрешностей для всех точек =СУММ(С8:С17).

1. Нашей целью является приведение этой погрешности к минимуму путем изменения значений коэффициентов уравнения прямой (клеток ВЗ и СЗ). В исходном состоянии они пустые. Для поиска оптимальных значений в окне Поиск решения в качестве целевой ячейки следует установить клетку С18, а в качестве изменяемых параметров - область ВЗ:СЗ (рис.43).

Рис. 43. Окно Поиск решения

 

Результаты поиска представлены на рис. 44.

 

Рис.44. Результаты поиска коэффициентов a и b

1. Аналогичным образом заполняется столбец D8:D18 погрешностей для полинома второй степени (параболы). Здесь в ячейку D8 ввести выражение:

= (B8-($B$4+$C$4*А8+$D$4*A8 ^2)) ^2.

В окне Поиск решения целевая ячейка - D18, изменяемые параметры - область B4:D4.

Для уравнения третьей степени (гиперболы) в ячейку E8 ввести выражение:

=(B8-($B$5+$C$5*A8+$D$5*A8 ^{^}2+$E$5*А8 ^3))^{^}2.

В окне Поиск решения целевая ячейка - Е18, изменяемые параметры - область В5:Е5.

Результаты решения приведены на рис.45.

Рис.45.Результаты поиска решения

Задание 5. Построение линии тренда

Построить аппроксимирующие зависимости, используя только средства деловой графики Excel.

1. Построить график исходной функции Y(X) (рис. 44).
2. Щелкнуть на нем правой кнопкой мыши, в появившемся контекстном меню выбрать пункт Добавить линию тренда.
3. Выбрать вид уравнения аппроксимации и его степень, во вкладке Параметры установить флаг Показывать уравнение на диаграмме. Аппроксимирующие зависимости представлены на рис.46.

Рис. 46.Аппроксимирующие зависимости