Прогнозирование в среде Excel

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Лабораторная работа

Прогнозирование в среде EXCEL

1. Прогнозы с применением метода скользящего среднего

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

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

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

Анализ ситуации: обслуживание клиентов

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

Полученный по истечении этого времени отчет включает ежедневное количество звонков с жалобами на конкретный программный продукт. Эти данные вы вводите в рабочий лист Excel, расположив их в ячейках А2:А11.

Рис. 1 Вид базовой линии и прогнозных значений

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

Вы решаете воспользоваться трехдневным скользящим средним. Почему трехдневный период? Ответ на этот вопрос таков: скользящее среднее за меньший период может не отразить тенденцию, а за более продолжительный период слишком сгладит ее. Одним из способов создания скользящего среднего в Excel является прямое введение формулы. Таким образом, чтобы получить трехдневное скользящее среднее количества телефонных звонков, вы вводите =СРЗНАЧ(А2:А4) в ячейку В5, а затем с помощью средства Автозаполнение копируете и вставляете эту формулу в ячейки В6:В12.

2. Составление прогнозов с помощью надстроек скользящего среднего

Другим способом применения скользящего среднего является использование надстройки Пакет анализа. Установить эту надстройку можно следующим образом. (Для этого задания используются данные из предыдущего примера).

1. Выберите команду > Сервис > Надстройки.

2. Появится диалоговое окно Надстройки. Установите флажок Пакет анализа и щелкните на кнопке ОК.

3. При необходимости активизируйте рабочий лист, содержащий данные о вашей базовой линии.

4. В меню Сервис вы обнаружите новую команду Анализ данных. Выберите команду Сервис Анализ данных.

5. Появится диалоговое окно Анализ данных, в котором содержатся все доступные функции анализа данных. Из списка выберите инструмент анализа Скользящее среднее и щелкните на кнопке ОК.

6. Появится диалоговое окно - Скользящее среднее.

7. В поле Входной интервал введите данные о всей вашей базовой линии либо укажите диапазон в рабочем листе, ссылка на него появится в этом поле.

8. В поле Интервал введите количество месяцев, которые хотите включить в подсчет скользящего среднего.

9. В поле ввода Выходной интервал введите адрес ячейки, с которой хотите начать выход (либо просто щелкните на этой ячейке в рабочем листе). Щелкните на кнопке ОК.

Excel выполняет вместо вас работу по внесению значений в формулу для вычисления скользящего среднего. Значения скользящего среднего начинаются со значений #H/Д, которые равны значению указанного вами интервала минус один.

3. Составление прогнозов скользящего среднего с помощью диаграмм

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

Можете вычислить скользящее среднее, выполнив следующие действия.

1. Выделите данные своей базовой линии.

Рис. 2 Вид базовой линии и прогнозных значений

2. Щелкните на кнопке Мастер диаграмм, расположенной на стандартной панели инструментов, либо выберите команду Вставка>Диаграмма.

3. На первом шаге выберите тип диаграммы График и щелкните на кнопке Далее.

4. На втором шаге работы средства Мастер диаграмм проверьте правильность ссылок на ячейки базовой линии и щелкните на кнопке Далее.

На третьем шаге выберите параметры графика, включающего как линии, так и маркеры. Щелкните на кнопке Далее.

6. На последнем, четвертом, шаге работы мастера определите местоположение диаграммы: на отдельном или имеющемся листе. Щелкните на кнопке Готово.

7. Если вы решили вставить диаграмму непосредственно в рабочий лист, щелкните на диаграмме, чтобы активизировать ее.

8. Щелкните правой кнопкой мыши на ряде данных диаграммы и из появившегося контекстного меню выберите команду Добавить линию тренда.

9. В появившемся диалоговом окне Линия тренда щелкните на вкладке Тип. Выберите линию тренда Линейная фильтрация, а затем -- необходимые периоды с помощью счетчика Точки. Период -- это количество наблюдений, которое включается в любое вычисление скользящего среднего.

10. Щелкните на кнопке ОК.

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

4. Прогнозирование с помощью функций регрессии Excel

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

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

Составление линейных прогнозов: функция ТЕНДЕНЦИЯ

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

ТЕНДЕНЦИЯ (А1 :А10;В1 :В10)

Замечание

Для ввода формулы массива нажмите комбинацию клавиш <Ctrl+Shift+Enter>.

Упоминалось, что регрессивный анализ позволяет производить перспективную оценку более удаленного будущего. Применяя данные рабочего листа. введите в ячейку В11 число 11, а в ячейку С11 -- следующее:

=ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11)

Первый аргумент -- А1:А10-- определяет данные наблюдений базовой линии (известные-значения-у); второй аргумент -- В1:В10 -- определяет временные моменты, в которые эти данные были получены (известные-значения-х). Значение 11 в ячейке В11 является новым значением- х и определяет время, которое связывается с перспективной оценкой.

Введя в ячейку В11 большее значение, вы сможете прогнозировать данные более позднего временного момента, чем непосредственно следующего за текущим. Число 24, введенное в ячейку В11, будет определять двадцать четвертый месяц, т.е. декабрь 2009 года. Выполняя вычисления с помощью функции тенденция, получаем результат 23,8, который и будет отражать прогнозируемый объем продаж в декабре 2009 года, полученный на основе фактических результатов наблюдений за период с января по октябрь 2008 года.

Кроме того, существует возможность одновременного прогнозирования данных для нескольких новых временных моментов. Например, введите числа 11-24 в ячейки В11-В24 а затем выделите ячейки С11:С24 и введите с помощью формулы массива следующее: {=ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11:В24)}.

Рис. 3 Вид базовой линии и прогнозных значений при использовании функции ТЕНДЕНЦИЯ

Excel вернет в ячейки С11:С24 прогноз на временные моменты с 11 по 24. Данный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А1:А10 и временными моментами базовой линии с 1 по 10, указанными в ячейках В1:В10.

Составление нелинейного прогноза: функция РОСТ

Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между результатом наблюдения и временем, когда это наблюдение было зафиксировано. Однако, если линия резко изгибается в одном из направлений, то это означает, что взаимосвязь показателей носит нелинейный характер. Существует большое количество типов данных, которые изменяются во времени нелинейным cпoсобом. Некоторыми примерами таких данных являются объем продаж новой продукции, прирост населения, выплаты по основному кредиту и коэффициент удельной прибыли. В случае нелинейной взаимосвязи функция Excel РОСТ поможет вам получить более точную картину направления развития вашего бизнеса, чем функция ТЕНДЕНЦИЯ.

5. Анализ ситуации: продажи новой компьютерной программы

Представим, что менеджер по закупкам отдела "Soft-почтой" недавно разослал клиентам каталог, рекламирующий новую программу, получившую очень высокую оценку экспертов. Менеджер считает, что следует заранее заказать дополнительное количество экземпляров, чтобы не оказаться в ситуации, когда CD c программой закончатся раньше, чем перестанут приходить заявки на нее, менеджер начал отслеживать ежедневные заказы на программный продукт и регистрировать объемы продаж.

Рис. 5 Фактические заказы на программный продукт

Поскольку линия имеющихся в наличии товаров резко изгибается вверх, менеджер принимает решение составить прогноз с использованием функции РОСТ. Как и при использовании функции ТЕНДЕНЦИЯ, пользователь в данном случае может генерировать прогнозы, просто подставляя новые значения-х. Чтобы прогнозировать результаты 11--13 недель, следует ввести эти числа в ячейки В12:В14, а затем с помощью формулы массива в диапазон ячеек С2:С14 ввести следующее: {=РОСТ(B2:B11;A2:A11;A2:A14)}.

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

Сравним использование функции РОСТ и ТЕНДЕНЦИЯ применив их к анализу следующих значений.

Рис.4 Использование функции РОСТ и ТЕНДЕНЦИЯ

В ячейках С2:С14 используется следующая формула:

ЕХР(ТЕНДЕНЦИЯ(LN(B2:B11);А2:All;А2:А14))

6. Регрессивный анализ с помощью диаграмм

прогноз скользящий excel диаграмма

Во многих случаях диаграммы Excel бывают очень полезны при создании прогнозов. Иногда возникает необходимость провести регрессивный анализ непосредственно на графике, без введения в рабочий лист значений для прогноза. Это можно сделать с помощью графической линии тренда методом, во многом сходным с методом получения прогноза с применением скользящего среднего на основе графика. Постройте диаграмму на основе данных объема продаж: 593,581,395,625,711,536,565,418,231,243,338,433,714,516,563,656,744,468,594,505,520,685,569,701

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

1. Выберите из контекстного меню команду Добавить линию тренда.

2. Выберите тип линии тренда Линейная.

3. Щелкните на вкладке Параметры.

4. В поле В перед на «ведите количество желаемых периодов, на протяжении которых линия тренда будет прокладываться вперед.

5. При желании можете установить флажок показывать уравнение на диаграмме. В результате уравнение для прогноза разместится на графике в виде текста Excel может расположить уравнение таким образом, что оно перекроет некоторые данные графика или линии тренда (либо (частично) само уравнение), этом случае выделите уравнение, щелкнув на нем мышью, а затем перетащит его в другое, более удобное место.

6. Кликните на кнопке ОК.

Рис. 5 Вид графика объема продаж

7. Прогнозирование с использованием функции экспоненциального сглаживания

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

Разработка перспективных оценок с применением метода сглаживания

Основная идея применения метода сглаживания состоит в том, что каждый новый прогноз, получается, посредством перемещения предыдущего прогноза в направлении, которое дало бы лучшие результаты по сравнению со старым прогнозом. Базовое уравнение имеет следующий вид:

· t -- временной период (например, 1-й месяц, 2-й месяц и т.д.);

· F[t] -- это прогноз, сделанный в момент времени t; F[t+l] отражает прогноз во временной период, следующий непосредственно за моментом времени t;

· а -- константа сглаживания;

· e[t] -- погрешность, т.е. различие между прогнозом, сделанным в момент времени t, и фактическими результатами наблюдений в момент времени t.

Таким образом, константа сглаживания является самокорректирующейся величиной. Сделайте сглаживающий прогноз на основании данных (10,4,5,5,7,8,6,20,19,20) используя формулу =B4+0,8*(A4-A8). Дайте графическое представление полученным результатам (график).

Рис. 6 Данные и результаты прогноза

Размещено на Allbest.ru