Overview

Шаблон - Модель прогнозирования продаж в MS Office Excel
скачать (9831 kb.)

Доступные файлы (1):

1.xls

9831kb.

15.11.2011 21:02

скачать

содержание

1.xls

1 2 3 4 5 6 7 8 9 ... 49

описание шаблона
36 периодов
24 периода
степень
логариф

Sheet 1: описание шаблона

ОПИСАНИЕ МОДЕЛИ ПРОГНОЗИРОВАНИЯ ПРОДАЖ

Модель построена на следующем алгоритме (F=T+S+E)

Использовались следующие шаги:

1. Определение трендов (T1, T2, T3, T4), для построения моделей прогнозирования. В качестве альтернатив используются 4 линии тренда: линейная, логарифмическая, экспоненциальная, степенная (эти линии на практике показывают наиболее точные результаты).

2. Определение величины сезонной компоненты (S).

3. Определение ошибок модели , (E) (среднеквадратическое отклонение для каждого периода (СКО))

4. Определение точности всей модели, {(1- СКО для всей модели)*100%}

5. Определение доверительного интервала {F*(1-СКО);F*(1+СКО)}

6. Построение прогноза

7. Построение прогноза с учетом коэффициентов сезонности

^ ПРАВИЛА ПОЛЬЗОВАНИЯ ШАБЛОНОМ

Прогноз можно построить на основе фактических данных за 36 или 24 периода.

Для этого в файле предназначены два листа, которые соответствующе называются

Рассмотрим последовательность шагов например шаблона для 36 периодов (для 24-х периодов последовательность аналогична)

1. Необходимо ввести данные фактического т/о за 36 периодов в ячейки выделенные желтым цветом (для примера там уже имеются цифры).

2. Линейная и экспоненциальная модель рассчитывается автоматически

3. Для Логарифмической и Степенной модели необходимо ввести данные уравнения тренда в ручную. Для этого переходим на лист под названием "Логарифм" и видим диаграмму с графиком и уравнением тренда. Уравнение имеет вид y=c*lnx+b, где с и b константы, а ln - функция натурального логарифма; соответственно в желтые ячейки вводим значения констант (для 36 периодной модели свой график, для 24-х периодной модели свой). Потом переходим на лист "Степень" и видим диаграмму с графиком и уравнением тренда. Уравнение имеет вид y=с*xb , где с и b константы; соответственно в желтые ячейки вводим значения констант (для 36 периодной модели свой график, для 24-х периодной модели свой)

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

5. Алгоритм расчета коэффициентов сезонности. A) Сначала определяем среднемесячный оборот за каждые прошедшие 12 периодов (год): Vср.мес.i = Vi/12. b) После этого рассчитываются сезонные колебания товарооборота по месяцам за каждый год: Sji = (Vji/Vср.месi)*100. c) Определяется сумма по годам сезонных колебаний каждого месяца за анализируемый период n лет (n=3 года для 36 периодов и 2 года для 24-х): Cj = ∑iSji,%. Далее рассчитываются индексы сезонности j-го месяца I сезонj - как среднее значение сезонных отклонений путем деления найденной суммы на количество лет - n. Данный алгоритм автоматически запрограммирован в шаблоне.

6. Следующим шагом необходимо определить какой из прогнозных т/о следует брать за основу. Основными критериями может служить точность моделей, темпы роста. Может возникнуть ситуация, например когда одна из моделей будет иметь точность 99,6%, а другая 99,5%, но прогнозные товарообороты различаться; тогда можно взять например среднюю из товарооборотов этих моделей или смотреть темпы роста; в любом случае решения принимать Вам, а этот шаблон будет мощным инструментом для принятия решения.

7. Необходимо полученный т/о ввести в оранжевую ячейку и ниже товарооборот будет рассчитан по периодам в соответствии с коэффициентами сезонности за три года (для 36-ти периодного шаблона) и за два года (для 24-ти периодного шаблона)

8. Расчеты для 24-х периодного шаблона аналогичны.

Примечание: К прогнозному товарообороту дополнительно можно применять механические или аналитические корректирующие коэффициенты (КК) (в соответствие с контекстом ситуации и конъюнктурой). Например механический КК это отношение полученного темпа роста путем одной из моделей данного шаблона к прогнозируемому темпу роста на анализируемом рынке (России, региона, города, смотря какие данные имеются). Аналитические КК это когда анализируются факторы оказавшие влияние на рост товарооборота в прошлом периоде, например в магазине была расширена торговая площадь что привело в текущем году к росту т/о на 30%, вопрос, будет ли расширение в следующем году или может откроется еще один магазин и т.д.

^ Заключение: УДАЧНЫХ И МАКСИМАЛЬНО ТОЧНЫХ ПРОГНОЗОВ!

1 2 3 4 5 6 7 8 9 ... 49

Скачать файл (9831 kb.)

Шаблон - Модель прогнозирования продаж в MS Office Excel - файл 1.xls

Доступные файлы (1):

1.xls

Overview

Sheet 1: описание шаблона