Частотный анализ в среде MS Excel. Метод скользящей средней в Excel (Эксель)

1. Рассчитать коэффициенты сезонности ;
2. Выбрать период для расчета среднего значения;
3. Рассчитать прогноз , т.е. среднее значение умножить на коэффициент сезонности;
4. Учесть дополнительные факторы , которые значительно влияют на продажи;

Рассчитать прогноз по методу скользящей средней очень просто . Для этого берём среднее значение , например, средние продажи за последние 3 месяца и умножаем на коэффициент сезонности к 3-м месяцам - и прогноз на месяц готов. Аналогичным образом делаем и на следующий месяц, только в расчет уже попадет предыдущий прогнозный месяц.

1. Рассчитаем коэффициенты сезонности для прогноза по методу скользящей средней.

Для этого рассчитываем коэффициенты сезонности очищенные от роста , как описано в статье «Как рассчитать коэффициенты сезонности, очищенные от роста?» . Затем определяем коэффициенты сезонности к предыдущим периодам , к 1 месяцу, к 2-м месяца, к 3-м месяцам и т.д. в зависимости от того, за какой период берем среднее значение для прогнозирования продаж. Например, рассчитаем месячные коэффициенты сезонности (см. вложенный файл лист "Расчет коэффициентов")

к 1 месяцу:

• коэффициент января - отношение январского коэффициента сезонности очищенного от роста к декабрьскому;

  февраля - февральского коэффициента к январскому;

  марта - март к февралю;

к 2-м месяцам:

• для января - отношение январского коэффициента сезонности к среднему значению декабря и ноября

  для февраля - февраль делим на среднее значение коэффициентов января и декабря

  для марта - март к среднему февральского и январского коэффициентов

к 3-м месяцам:

• для определения январского коэффициента сезонности к 3-м месяцам мы январский коэффициент сезонности, очищенный от роста, делим на среднее значение коэффициентов сезонности, очищенных от роста, за декабрь, ноябрь, октябрь;

  для февраля - коэффициент февраля делим на среднее значение коэффициентов ноября, декабря и января;

  Для марта - отношение марта к среднему значению коэффициентов сезонности очищенных от роста декабря, января и февраля;

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

2. Выбираем период расчета среднего значения для прогноза по методу скользящей средней.

Для этого делаем прогноз для последнего и предпоследнего периодов, данные за который нам известны, тремя или более способами для определения подходящего периода расчета средней (см. вложенный файл лист «Выбор периода»). И смотрим, какой из вариантов делает более точный прогноз:

1. Рассчитаем прогноз продаж по методу скользящей средней к 1-му месяцу :

Декабрь = объём продаж ноября умножим на декабрьский коэффициент сезонности к предыдущему месяцу.

1. Рассчитаем прогноз продаж по методу скользящей средней к 2-ум месяцам:

Декабрь = средний объём продаж за октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 2-м месяцам.

1. Рассчитываем прогноз по методу скользящей средней к 3-ем месяцам:

Декабрь = средний объём продаж за сентябрь, октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 3-м месяцам.

Сейчас мы рассчитали прогноз тремя способами на декабрь. Аналогичным образом рассчитаем на ноябрь.

Теперь сравниваем фактические значения за ноябрь и декабрь с прогнозными рассчитанными 3-мя способами . Мы видим, что в нашем примере наиболее точно прогноз рассчитан по методу скользящей средней к 2-м месяцам , возьмём его за базу. В вашем случае более точный прогноз может оказаться к предыдущему периоду, к 3-м предыдущим или к 4-м предыдущим периодам.

3. Рассчитаем прогноз продаж по методу скользящей средней.

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

Для прогноза на февраль мы средний объем продаж января и декабря умножаем на февральский коэффициент сезонности.

Следуя данной логике, мы продлеваем расчет прогноза до конца года. Расчет прогноза продаж на год готов.

4. Дополнительные факторы, которые стоит учесть при расчете прогноза продаж.

Для повышения точности прогноза важно:

1. Из прошлых периодов вычесть факторы , которые значительно повлияли на объем продаж , но в прогнозных месяцах повторяться не будут (акции по стимулированию сбыта, разовая отгрузка крупного нерегулярного клиента, вывод из крупной розничной сети и т.д.).
2. К прогнозируемым месяцам прибавить факторы , которые значительно повлияют на продажи - начало работы с крупными сетями, проведение крупных акций по стимулированию сбыта, вывод новых товаров, рекламные компании и т.д.

Точных вам прогнозов!

Программа Forecast4AC PRO рассчитает прогноз по методу скользящей средней одновременно более чем для 1000 временных рядов одним нажатием клавиши, значительно сэкономив ваше время, одним из 4-х способов:

К среднему за два предыдущих периода

К среднему за три предыдущих периода

К среднему за 4 предыдущих периода

Двойная средняя к 3 и 4 предыдущим периодам

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :

• Novo Forecast Lite - автоматический расчет прогноза в Excel .
• 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

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

Инструмент «Скользящее среднее» можно вызвать в диалоговом окне команды «Анализ данных» из меню «Сервис».

С помощью инструмента скользящей средней я составляю прогноз экономических показателей таблицы 1.1(табл. 3.1).

Таблица 3 .1 ― Оценка тенденции поведения показателей исследуемого динамического ряда методом скользящего среднего

Примечание – Источник: .

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

Рисунок 3.1 – Скользящее среднее

Примечание – Источник: .

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

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

1. Составление линейных прогнозов средствами Excel

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

1. Использование функции линейн для создания модели тренда

Функция рабочего листа ЛИНЕЙН помогает определить характер линейной связи между результатами наблюдений и временем их фиксации и дать ей математическое описание, наилучшим образом аппроксимирующее исходные данные. Для построения модели она использует уравнение вида y=mx+b, гдеy– исследуемый показатель;x=t– временной тренд;b,m– параметры уравнения, характеризующие соответственноy-пересечение и наклон линии тренда. Расчет параметров модели ЛИНЕЙН производят на основе метода наименьших квадратов.

Вызвать функцию ЛИНЕЙН можно в диалоговом окне «Мастер функций» (категория «Статистические»), расположенном на панели инструментов «Стандартные».

Таблица 3.2 ― Расчет и оценка линейной модели тренда с помощью функции ЛИНЕЙН

Скользящее среднее или просто МА (Moving Average) , является среднеарифметическим ценового ряда. Общая формула скользящего среднего следующая:

Где:
МА – скользящее среднее;
n- период усреднения;
Х – значения цены акции.

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

Спрогнозируем с помощью модели скользящего среднего стоимость акций компании Аэрофлот (AFLT) . Для этого экспортируем котировки акции с сайта finam.ru за половину 2009 года. Всего будет 20 значений.

График стоимости акций Аэрофлота за выбранный промежуток времени представлен ниже.

Выбор периода усреднения n в модели скользящего среднего
Использование большего в модели МА(n) приводит к сильному искажению данных, в результате чего существенные значения ценового ряда усредняются, и в результате теряется четкость прогноза, можно сказать что он становится “размытым”. Использование слишком мелкого периода усреднения добавляет в прогноз больше шумовой компоненты. Как правило, период усреднения подбирается эмпирическим путем на исторических данных.

Построим скользящее среднее с периодом усреднения в три месяца MA(3). Для расчета значения скользящего среднего для акции, воспользуемся формулой Excel.

СРЗНАЧ(C2:C4)

В колонке “D” рассчитаны значения скользящего среднего с периодом усреднения 3.

После расчета скользящего среднего построим прогноз на 3 периода вперед (на три месяца вперед). Воспользуемся формулой для определения значения цены акции, первое прогнозное значение будет равно последнему значению скользящего среднего. Оранжевая область это область прогнозов. С22 будет равна значению скользящего среднего, то есть:

С22 =D21 С23=D22 и т.д.

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

Построим прогнозные значения по скользящему среднему для акций Аэрофлота на три месяца вперед. Ниже представлен график и прогнозные значения акции.

В бизнесе, как и в любой другой деятельности человек, хочет знать, а что будет дальше. Даже трудно себе представить богатство того счастливца, который с 100% точностью мог бы угадывать будущее. Но, к сожалению (или, же к счастью) дар предвидения встречается крайне редко. НО… стараться хотя бы в общих чертах представить будущую бизнес ситуацию предприниматель просто обязан.

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

Чаще всего в практике маркетинговых исследований прогнозируются следующие величины:

• Объемы продаж
• Размер и емкость рынка
• Объемы производства
• Объемы импорта
• Динамика цен
• И проч.

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

1. Сбор вторичной информации по проблеме (желательно как количественной, так и качественной). Так, например если Вы прогнозируете размер своего рынка, нужно собрать статистическую информацию по рынку (объемы производства, импорта, динамику цен, объемы продаж и проч.) так и тенденции, проблемы или возможности рынка. Если вы прогнозируете объем продаж, тогда вам нужны данные о продажах за период. Для прогнозирования, чем больше исторических данных вы рассмотрите, тем лучше. Желательно прогнозирование дополнить анализом влияющих на прогнозируемое явление факторов (можно SWOT, PEST анализ или любой другой). Это позволит понимать логику развития, и вы сможете таким образом проверять правдоподобность той или иной модели тренда.

2. Далее желательно проверить количественные данные . Для этого нужно сравнить значения одних и тех же показателей, но полученных из разных источников. Если все сходиться можно «загонять» данные в Excel. Также данные должны соответствовать следующим требованиям:

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

3. Проверив данные, можно применять различные методики прогнозирования . Начать я бы хотел с самого простого метода – МЕТОДА СКОЛЬЗЯЩЕГО СРЕДНЕГО

МЕТОД СКОЛЬЗЯЩЕГО СРЕДНЕГО

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

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

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

Итак, как это делать в Excel

1. Допустим, что у Вас есть объемы месячных продаж за последние 29 месяцев. И вы хотите определить, какой объем продаж будет в 30 месяце. Но, если честно, вовсе не обязательно при расчете прогнозных значений оперировать 30 историческими значениями, ведь этот метод будет использовать для расчета среднего лишь несколько последних месяцев. Поэтому для расчета достаточно лишь несколько прошлых месяцев.

2. Приводим эту таблицу в вид понятный Excel, т.е. чтобы все значения были в одном ряду.

3. Далее вводим формулу расчета среднего по предыдущим трем (четырем, пяти? как сами выберите) значениям (см. в ). Наиболее удобно все-таки использовать для расчета последние 3 значения, т.к. если учитывать больше, данные будут чересчур усредняться, если меньше – не будут точными.

4. Используя функцию автозаполнения для всех последующих значений вплоть до 30, прогнозного месяца. Таким образом, функция рассчитает прогноз на июнь 2010 г. Согласно прогнозным значениям в июне продажи составят около 408 единиц товара. Но обратите внимание, что если тенденция падения постоянна, как в нашем примере, расчет прогноза по средней будет немного завышенным, или будет как бы «отставать» от реальных значений.

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

Цель работы : Приобрести навыки решения задач частотного анализа с помощью функции рабочего листа анализа MS Excel .

Краткая теория

При анализе экономических показателей часто возникает вопрос, как часто встречаются показатели в заданных интервалах значений.

Функция ЧАСТОТА рабочего листа анализа MS Excel относится к категории статистических функций и возвращает распределение частот в виде вертикального массива. Для данного множества значений и заданного множества карманов (интервалов) частотное распределение подсчитывает, сколько значений попадает в каждый интервал.

В качестве массива данных может быть одномерный или двумерный массив (например, A 4: D 15).

Синтаксис: ЧАСТОТА (массив_данных; массив_карманов)

Для частотного анализа можно использовать команду Сервис/Анализ данных. Анализ данных является одной из надстроек Excel . Если в меню отсутствует эта команда, то следует выполнить команду Сервис/ Надстройки и установить соответствующий флажок в окне Надстройки .

Задание 1

С помощью функции Частота для выборки множества сумм заказов () введите в диапазон подсчитайте, сколько значений попадают в заданные интервалы значений. Например, от 0 до 1000, от 1001 до 1500, от 1501 до 2000, от 2001 до 2500, свыше 2500.

Порядок действий :

1. На рабочем листе MS Excel введите данные об объемах заказов в 20 филиалах фирмы за сентябрь в виде таблицы, фрагмент которой показан на рисунке.

	№ филиала	Сентябрь
		1230		1000
				1500
		….		2000
				2500

1. В свободный диапазон клеток (столбец) введите верхние границы интервалов (Например, D 2=1000, D 3=1500, D 4=2000, D 5=2500).
2. Выделите блок ячеек столбца, смежного со столбцом интервалов (E 2: E 21). Для того, чтобы подсчитать количество значений, превышающих нижнюю границу интервала, выделяется диапазон, на одну ячейку больше, чем диапазон интервалов.
3. В диапазон E 2: E 6 введите формулу { =ЧАСТОТА(E 2: E 15; J 2: J 6)}.

Для этого воспользуйтесь мастером функций (Вставка/ Функция). В категории «Статистические» выберите из списка функцию «Частота». В диалоговом окне функции ЧАСТОТА заполните поля массива выборки и массива интервалов (рисунок 1). Не выходя из окна диалога нажмите комбинацию клавиш < Ctrl / Shift / Enter > для расчета элементов массива.

Рисунок 1 – Пример заполнения диалогового окна функции Частота.

1. Постройте диаграмму по полученным результатам.
2. Сохраните файл.

Задание 2

Создайте на рабочем листе двумерный массив, содержащий статистические данные о росте людей различных возрастных категорий. Проведите частотный анализ результатов с помощью функции ЧАСТОТА и Анализа данных (пункт меню Анализ данных/ Гистограмма) .

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

Часть 2

Решение задач прогнозирования в среде MS Excel . Метод скользящего среднего

Цель работы : Приобрести навыки прогнозирования экономической деятельности предприятия с применением статистического программного пакета MS Excel.

Краткая теория

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

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

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

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

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

Если для прогноза наиболее значимыми являются последние результаты наблюдений, то используют метод экспоненциального сглаживания. В методе экспоненциального сглаживания каждое значение участвует в формировании прогнозируемых значений с переменным весом, который убывает по мере «устаревания» данных. В инструменте анализа MS Excel « Экспоненциальное сглаживание» весовой коэффициент, или параметр сглаживания, определяется параметром Фактор затухания . Обычно для временных рядов в экономических задачах величину параметра сглаживания задают в интервале от 0,1 до 0,3. Начальное расчетное значение в процедуре Экспоненциальное сглаживание пакета Анализа MS Excel принимается равным уровню первого члена ряда. Метод обеспечивает хорошее согласование исходных и расчетных данных для первых значений ряда. Если конечные вычисленные значения значительно отличаются от соответствующих исходных, то целесообразно изменить величину параметра сглаживания. Оценить величины расхождений можно на основе стандартных погрешностей и графика, которые пакет Анализа позволяет вывести вместе с расчетными значениями ряда.

Рассмотрим возможности прогнозирования показателей деятельности предприятия, занимающихся предоставлением услуг связи.

Задание к лабораторной работе (часть 2)

Задание 1 : Вычислить прогнозируемое значение величины объема продукции (услуг) предприятия методом скользящего среднего.

Порядок выполнения задания:

Создадим на рабочем листе столбец, содержащий данные об объеме услуг в млн. руб, оказанных предприятием за последние 10 лет. Выявим тенденцию изменения показателя с помощью скользящего среднего. Выберем трехгодичный период скользящего среднего, так как за меньший период скользящее среднее может не отразить тенденцию, а за более продолжительный – сгладит ее.

Для вычислений воспользуемся способом прямого введения формулы. Чтобы получить трехлетнее скользящее среднее объема выполненных услуг для нашего примера, введем в ячейку B 5 формулу для вычисления =СРЗНАЧ(A 2: A 4). Скопируем формулу в интервал B 6: B 11.

Рисунок 1 – Вычисление простого скользящего среднего

Проиллюстрируем результаты графиком, отражающим динамику изменения исходных данных и скользящего среднего.

Рисунок 2 – График тенденции изменения показателя объема услуг, полученной методом простого скользящего среднего

Другим способом решения является использование для определения скользящего целого Пакета анализа . Пакет анализа является надстройкой MS Excel (выберите пункт меню Сервис/ Надстройки и установите флажок Пакет анализа ).

Порядок действий

1. Выполнить команду Сервис/Анализ данных и выбрать из списка инструментов анализа Скользящее среднее.
2. В диалоговом окне укажите параметры для вычисления скользящего среднего:

• В качестве входного интервала выделите блок ячеек, содержащий данные об объеме услуг.
• Укажите Интервал- 3 (по умолчанию используется 3), в качестве выходного интервала – любую ячейку рабочего листа (просто щелкните на ячейке рабочего листа, с которой должны выводиться результаты);

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

Проанализируйте используемые расчетные формулы и полученные результаты.

Аналогично вычислите пятилетние простые скользящие средние. Сравните результаты сглаживания для двух вариантов расчета.

Задание 2: Вычислить прогнозируемое значение величины объема продукции (услуг) предприятия методом экспоненциального сглаживания.

Порядок действий:

1. На листе MS Excel создайте список, содержащий данные о численности сотрудников фирмы за последние 10 лет. Данные введите произвольно, но так, чтобы прослеживалась тенденция.
2. Проведите сглаживание временного ряда с использованием экспоненциальной средней с параметрами сглаживания 0,1 и затем 0,3. По результатам расчетов постройте график и определите, какой из полученных временных рядов носит более гладкий характер.

Воспользуйтесь командой Сервис/Анализ данных и выберите из списка инструментов анализа Экспоненциальное сглаживание. Укажите параметры для вычисления скользящего среднего:

• В качестве входного интервала выделите блок ячеек, содержащий данные о численности.
• Укажите Фактор затухания . В качестве выходного интервала – любую ячейку рабочего листа.
• Задайте вывод графика и стандартных погрешностей.

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

Часть 3

Решение задач прогнозирования с помощью функций рабочего листа и маркера заполнения

Краткая теория

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

Существует несколько методов подбора кривых. Одним из самых простых является визуальный метод. Если на графике недостаточно просматривается тенденция развития (тренд), то производят, как описано выше, сглаживание ряда, а затем подбирается кривая, соответствующая новому ряду. В этом случае также применяются современные программные средства компьютерных систем. В MS Excel встроены специальные функции, позволяющие рассчитывать прогнозируемые значения на определенный период.

Excel проводит линейную экстраполяцию, т.е. рассчитывает наиболее подходящую прямую, которая проходит через серию заданных точек. Задача заключается в нанесении на график набора точек, а затем в подборе линии, по которой можно проследить развитие функции с наименьшей ошибкой. Эта линия называется линией ТРЕНДА. Пользователь может использовать результат вычислений для анализа тенденций и краткосрочного прогнозирования.

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

• С помощью маркера заполнения
• С помощью функций рабочего листа

Первый способ

Линейное приближение

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

Экспоненциальное приближение

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

Второй способ

В MS Excel встроены статистические функции рабочего листа.

ТЕНДЕНЦИЯ() - возвращает значения в соответствии с линейной аппроксимацией по методу наименьших квадратов.

РОСТ() - возвращает значения в соответствии с экспоненциальным трендом.

Использование этих функций – еще один способ вычисления регрессионного анализа.

Формат

ТЕНДЕНЦИЯ (изв_знач_Y; изв_знач_X; нов_знач_X; константа)

Функция РОСТ возвращает значения в соответствии с экспоненциальным трендом.

Задание к лабораторной работе (часть 3)

Задание 1:

Рассчитайте линейный и экспоненциальный прогноз на один год и на последующие три периода (до 2011 года) с помощью маркера заполнения.

Задание 2:

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

В строке формул рабочего листа должна появиться формула для расчета элементов массива, например,

{ = ТЕНДЕНЦИЯ (B 3: G 3; B 2: G 2; B 2: H 2)}

Определите, какая модель является наиболее точной.

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