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

5.1 При исследовании спроса на реализуемый то­вар-новинку в фирменном магазине в течение 20 дней было зафиксировано определенное количество поступивших на не­го жалоб со стороны покупателей (таблица 3.9, ячейки В2:В21). Выяснить, существует ли какая-либо тенденция поступления жалоб с помощью инструмента Скользящее сред­нее.

Выполнение:

Инструмент Скользящее среднее можно вызвать в диалоговом окне команды Анализ данных из меню Сервис. Открывшееся окно диалога предлагает пользователю за­дать следующие основные параметры (рисунок 3.10):

1) Входной диапазон (интервал) - это диапазон ячеек В2:В21 (см. таблицу 3.9);

2) Интервал усреднения примем равным 3, т.е. бу­дем определять трехдневное скользящее среднее;

3) Выходной диапазон (интервал) за­даем именем ячейки С2. При этом следует иметь в виду, что первые несколько ячеек выходного диапазона будут всегда содержать значение #Н/Д. Количество таких ячеек равно значению выбранного интервала усреднения минус один. Эта ситуация связана с недостаточным количеством базовых данных для вычисления среднего значения первых результа­тов наблюдений;

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

Рисунок 3.10 - Окно диалога Скользящее среднее

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

	А	В	С
	День месяца	Количество жалоб
			#Н/Д
			#Н/Д
			10,333
			11,667
			12,667
			11,667
			13,333
			11,333
			13,333
			14,667
			14,667
			13,667
			15,667
			17,333

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

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

Выполнение:

- на отдельном рабочем листе Excel составим разработочную таблицу следующего вида (таблица 3.10);

Таблица 3.10 - Методика составления прогноза товарооборота на основе скользящего среднего средствами Excel (разработочная таблица)

- учитывая, что объем товарооборота представляет собой абсолютный стои­мостный показатель, для расчета скользящего среднего соз­дадим в разработочной таблице колонку относительных ве­личин, характеризующих динамику товарооборота предпри­ятия — цепных темпов прироста (вводим в ячейку С4 формулу =В4/В3*100-100 и копируем ее в ячейки С5:С18) (столбец С таблицы 3.10);

- вы­равнивание динамического ряда, состоящего из 15 цепных темпов прироста товарооборота, проведем, используя интер­вал усреднения, равный, например, 5. Для расчета показате­лей выровненного ряда (столбец D таблицы 3.10) воспользуемся другим способом создания скользящего среднего в Excel, т.е. прямым введением формулы в соответствующую ячей­ку рабочего листа. Так, чтобы получить пятимесячное сколь­зящее среднее цепных темпов прироста, в ячейку D8 таблицы 10 вводится формула =СРЗНАЧ(С4:С8). Затем эта формула с помощью средства Автозаполнение копируется и вставляет­ся в ячейки D9:D18;

- для составления прогноза товарооборота на 17-й месяц в таблице 3.10 необходимо занести еще три формулы: расчета среднего изменения темпов его прироста: =(D18-D8)/10 (ячейка Е18), расче­та прогнозируемого цепного темпа прироста на 17-й месяц: =D18+2*E18 (ячейка С20), расчета прогнозируемого объема товарооборо­та: B18*(C20+100)/100 (ячейка В20). Результатом всех проведенных операций становится итоговая таблица 3.11, содержащая все промежуточ­ные элементы и значения единой цепи составления прогноза товарооборота.

Таблица 3.11 - Прогноз товарооборота на основе скользящего среднего средствами Excel (итоговая таблица)