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

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

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

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

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

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

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

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

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

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

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

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

3. Изберите команду Диаграмма-Добавить линию тренда, чтоб открыть диалоговое окно Линия Задание 5. Построение линии тренда тренда.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тут:

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

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

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

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

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

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

Тут:

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

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

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

аргумент Константа воспринимает логическое значение: если он Задание 5. Построение линии тренда имеет значение Правда либо 1 или опущен, то коэффициент уравнения регрессии b0 рассчитывается как обычно; если же он имеет значение Ересь либо 0, то коэффициент b0 полагается равным 0, и значения коэффициентов уравнения регрессии рассчитываются с учетом этого условия.

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

Эти функции можно использовать для одновременного вычисления массива прогнозных значений по данному массиву {х} значений причин, зачем в качестве аргумента х нужно указать Задание 5. Построение линии тренда массив {х}, а саму функцию применить как формулу массива (нажав комбинацию кнопок ) к выделенному спектру ячеек, в каком будет записан выходной массив прогнозных значений.

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

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

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

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

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

3. Для вычисления прогнозных значений маркетинговых издержек выделите спектр ячеек G2:G7, введите функцию =РОСТ(C2:C25; A2:A25;E Задание 5. Построение линии тренда2:E7)и нажмите комбинацию кнопок .

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

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

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

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

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

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

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

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

Аргумент Статистикапринимает логическое значение, которое показывает, требуется Задание 5. Построение линии тренда ли рассчитывать дополнительные статистические свойства регрессии. Если этот аргумент имеет значение Правда либо 1, то функция рассчитывает и выводит эти дополнительные свойства. Если аргумент Статистика имеет значение Ересь, 0 либо опущен, то функция возвращает только значения коэффициентов тi и b0.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В окне Поиск решенияцелевая Задание 5. Построение линии тренда ячейка - 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. Построение полосы Задание 5. Построение линии тренда тренда

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

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

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



zadanie-5-postroenie-linii-trenda.html
zadanie-5-primenenie-normalnogo-zakona-raspredeleniya.html
zadanie-5-rabota-po-sostavleniyu-pretenzij-postavshikam.html