5. Технология прогноза объема продаж с помощью Мастер функций

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

На рис. 8.43 показан вызов функции ТЕНДЕНЦИЯ для прогноза объема продаж на некоторый товар по временному ряду.

В ячейках В4:В8 находятся данные об объеме продаж за прошедшие 5 лет. В ячейку B10 будет возвращено прогнозное значение объема продаж. Для открытия диалога Мастер функций нажата кнопка f_x на панели управления. Функция ТЕНДЕНЦИЯ расположена в категории Статистические (левое окно диалога). Для того чтобы вызвать функцию ТЕНДЕНЦИЯ, надо щелкнуть мышью (левая кнопка) по названию функции в правом окне и нажать кнопку ОК.

Рис. 8.43. Вызов функции ТЕНДЕНЦИЯ

Заполнение диалога функции ТЕНДЕНЦИЯ показано в табл. 8.67 и на рис. 8.44.

Таблица 8.67

Окно диалога функции ТЕНДЕНЦИЯ	Формула
Изв_знач_У	В4:В8
Изв_знач_Х	A4:A8
Нов_знач_Х	A10
Константа	Заполнять не надо

Рис. 8.44. Диалог функции ТЕНДЕНЦИЯ

Функция ТЕНДЕНЦИЯ строит прогноз на основе модели:

у = а·х + b,

где а и b - параметры; у - прогнозируемая переменная (в данном случае объем продаж); х - независимая переменная (в данном случае время).

Известные значения х - это пять предплановых лет, пронумерованных от 1 до 5. Известные значения у - объемы продаж в эти годы. Новое значение х - это номер планового года, в данном случае 6.

После ввода формул в окна диалога надо нажать кнопку ОК. Результат вычислений показан на рис. 8.45.

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

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

Таблица 21

Данные для составления тренда

№ п/п	Месяц	Объем продаж (тыс. руб.)			Линейный тренд
1	январь	50000,00	1	50000,00	47269,17
2	февраль	100000,00	4	200000,00	80616,37
3	март	140000,00	9	420000,00	113963,57
4	апрель	200000,00	16	800000,00	147310,77
5	май	210000,00	25	1050000,00	180657,97
6	июнь	250000,00	36	1500000,00	214005,17
7	июль	200000,00	49	1400000,00	247352,37
8	август	275000,00	64	2200000,00	280699,57
9	сентябрь	310000,00	81	2790000,00	314046,77
10	октябрь	338287,30	100	3382873,00	347393,97
11	ноябрь	300000,00	121	3300000,00	380741,17
12	декабрь	320000,00	144	3840000,00	414088,37
13	январь	360000,00	169	4680000,00	447435,57
14	февраль	400000,00	196	5600000,00	480782,77
15	март	460000,00	225	6900000,00	514129,97
16	апрель	550000,00	256	8800000,00	547477,17
17	май	600000,00	289	10200000,00	580824,37
18	июнь	650000,00	324	11700000,00	614171,57
19	июль	780000,00	361	14820000,00	647518,77
20	август	800000,00	400	16000000,00	680865,97
21	сентябрь	945000,00	441	19845000,00	714213,17
22	октябрь	800000,00	484	17600000,00	747560,37
23	ноябрь	700000,00	529	16100000,00	780907,57
24	декабрь	600000,00	576	14400000,00	814254,77
300		10338287,30	4900	167577873,00

Произведем расчеты исходя из системы уравнений:

Подставляя значения из таблицы получим уравнение линии тренда: . Чтобы получить цифровые значения линий тренда за каждый месяц, подставим данные в уравнение – это последовательность чисел от 1 до 24.

Рассчитаем сезонную компоненту (S) для уравнений тренда. Из фактических данных вычитаем значения линий тренда для каждого сезона При помощи MS Excel получены следующие параметры (рис. 8.46).

Рис. 8.46. Расчет сезонной компоненты (S) для уравнений тренда в MS Excel

При помощи Excel выполним расчет значений модели (T+S) (рис. 8.47).

Рис. 8.47. Расчет значений модели (T+S) в MS Excel

Для построения прогноза по объемам продаж по месяцам на 2011 год товаров фирмы составим таблицу (рис. 8.48). Выполним расчеты прогнозных значений модели T+S в MS Excel.

Рис. 8.48. Расчеты прогнозных значений модели T+S при помощи MS Excel

На основе полученных данных построим прогноз объемов продажи товаров на 2011 год (рис. 8.49).

Рис. 8.49. Прогноз объема продаж теплоэффективных блоков «Теплостен»

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