Задачи по логистики. Решение задач с помощью ms excel 46

• Название: Решение задач с помощью ms excel 46
• Анкор: Задачи по логистики
• Дата: 14.05.2022
• Размер: 7.4 Mb.
• Имя файла: d0bcd0b5d182d0bed0b4d0b8d187d0bad0b0-d0b4d0bbd18f-d0bfd180d0bed0.docx
• Тип: Решение; #528805
• страница: 11 из 15

ИНДЕКС СЕЗОННОСТИ И ПРОГНОЗИРОВАНИЕ СБЫТА

Индексы сезонности применяются для прогнозирования сбыта товаров сезонного спроса. Под сезонным спросом понимаются цикличные (повторяющиеся ежегодно) коле­бания объемов потребления товара. Эти колебания могут быть связаны со временем года, погодой или календар­ной датой (1 сентября, Новый год, 8 Марта).

Для прогнозирования сезонных колебаний рассчиты­ваются индексы сезонности (Is). Индекс сезонности харак­теризует в процентах долю объема продаж товара относи­тельно его среднемесячного объема продаж за год.
Расчет индексов сезонности в Excel.

Рассчитать индексы сезонности можно двумя способами.

Если в течении года присутствовала ярко выраженная тенденция (тренд) роста или спада объемов продаж, при­меняется формула:

, где

Ism—индекс сезонности месяца т;

Ymn—значение объема реализации за месяц т, года n;

Ycpn— среднемесячный объем реализации за год n;

n —количество анализируемых лет.

Иначе говоря, прогнозируемый на месяц т индекс се­зонности равен среднему индексу сезонности для этого месяца за n лет.

Если в течение года ярко выраженной тенденции (трен­да) роста или спада объемов продаж нет, применяется формула:

.

Иначе говоря, прогнозируемый на месяц т индекс се­зонности равен отношению среднего значения объема продаж за несколько лет, за указанный месяц к среднеме­сячному объему продаж товара за nлет.

На рис. 9.1 представлена таблица со статистикой про­даж товара за четыре года по месяцам и индексы сезон­ности. Is1 — индекс сезонности, рассчитанный первым способом, Is2 — индекс сезонности, рассчитанный вто­рым способом.


Рис. 9.1. Таблица расчета индексов сезонности

Расчет промежуточных результатов осуществлен с по­мощью следующих формул:

• 
  Столбец F («всего») — простое суммирование объе­мов реализации за указанный месяц по годам
  

=СУММ(В4:Е4).

• 
  Столбец G («в среднем») — отношение объема реа­лизации за все годы к количеству лет
  

=F4/СЧЁТ(В4:Е4).

Для расчета индекса сезонности первым способом первоначально необходимо рассчитать индексы сезон­ности для каждого года раздельно. В таблице индексы сезонности по годам рассчитываются в столбцах Н-К. Расчетная формула имеет вид (для января 2006 года): =В4/($В$16/12)*100. И, наконец, индекс Is1 рас­считывается как среднее значение индексов по годам: =СРЗНАЧ(Н4:К4).

Для расчета индекса сезонности вторым способом не­обходим средний объем продаж за месяц по годам. Он уже рассчитан в столбце G. Среднемесячный объем продаж за все годы рассчитывается в ячейке «G16» по формуле: =СУММ (G4:G15) /12. То есть сумма средних объемов продаж по каждому месяцу делится на количество меся­цев. И последний шаг — делим средний объем продаж за месяц mна среднемесячный объем продаж за четыре года: =G4/$G$16*100.
Внимание! Прежде чем приступать к расчету индексов сезонности необходимо проанализировать имеющуюся статистику про­даж и убрать из нее аномально большие или аномально малые объемы продаж. Например, очень большой объем продаж мог быть вызван разовой, случайной, сделкой с крупным клиентом. Объем такой сделки необходимо убрать из статистики продаж.
Прогнозирование объемов продаж с помощью индек­сов сезонности.

Индексы сезонности можно использовать для прогнози­рования и планирования товарооборота на очередной год.

Допустим, на очередной год вы планируете довести объем продаж вашего товара до 6000 штук. Рассчитав прогнозный среднемесячный объем продаж товара и ум­ножив его на соответствующие индексы сезонности, вы получите прогнозные объемы реализации по месяцам (рис. 9.2).


Рис. 9.2. Таблица расчета плановых объемов продаж по месяцам.

Для расчета планируемых объемов продаж в таблице применены следующие формулы:

• 
  =F15/12 — для расчета планового среднемесяч­ного объема продаж (в ячейке «F15» хранится плановый объем продаж за год);
  
• 
  =В2*$F$16/100 — для расчета планового объе­ма реализации товара за конкретный месяц.
  

Применение индексов сезонности в ХYZ-анализе.

Как уже было изложено выше, товары с ярко выражен­ной сезонностью скорее всего попадут в группу Z. Чтобы применить ХYZ-анализ к товарам сезонного характера, из статистики продаж необходимо изъять сезонную со­ставляющую. Убирается сезонная составляющая просто: фактические объемы продаж това­ра делятся на соответствующие ин­дексы сезонности, после чего прово­дится ХYZ-анализ.