text
Журнал

Как рассчитать оптимальный товарный запас с помощью Excel

Рассчитать оптимальный с точки зрения удовлетворения спроса товарный запас компании можно с помощью инструмента в Excel. В основе действия предлагаемого файла лежит алгоритм Хольта-Винтерса, который учитывает не только основную тенденцию (тренд) продаж, но и периодические колебания (сезонность) спроса.

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

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

На этапе ввода данных о прежних продажах программа автоматически определяет, можно ли применять предлагаемый инструмент: он эффективен только для нормального (не редкого) спроса.

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

Работа с инструментом расчета оптимального товарного запаса начинается с заполнения листа «Продажи», представленного на рисунке 1. Здесь нужно внести данные о продажах (в штуках или в других натуральных единицах) за предыдущие периоды. Информацию можно сгруппировать по дням, неделям или месяцам. Выборка должна содержать несколько десятков элементов (в пределах 100) – этого достаточно для того, чтобы установить общую тенденцию и выявить сезонность спроса.

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

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

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

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

Как подготовить данные для прогнозирования спроса

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

Эти операции выполняются на листе «Фильтрация», представленном на рисунке 3.

На листе две таблицы, которые заполняются автоматически. Таблица 2 применяется для фильтрации нетипичных продаж, таблица 1 – для восстановления спроса.

Нетипичные продажи выявляются следующим образом. В столбец «Объем спроса» (в таблице 2 на листе «Фильтрация») автоматически вносятся данные с листа «Продажи» обо всех объемах продаж, которые совершались за рассматриваемый период (в примере – за 72 месяца), причем значения располагаются по возрастанию. Затем автоматически рассчитывается, сколько раз был реализован каждый объем (это можно определить, если значение из столбца «Спрос» разделить на соответствующий ему показатель столбца «Объем спроса»). Далее определяется вероятность спроса на продукцию компании для каждого объема. Например, две единицы продукции были проданы лишь в одном месяце. С учетом того, что анализируются данные за 72 месяца, вероятность такого спроса равна 1,39 процента (1 мес. : 72 мес. × 100%).

После этого рассчитывается кумулятивная вероятность каждого из объемов спроса, которые были за анализируемый период. Этот показатель позволяет оценить вероятность того, что объем спроса достигнет значения, меньшего или равного каждому из тех, которые представлены в столбце «Объем спроса». Например, чтобы рассчитать ее для объема спроса, равного 79 единицам (см. таблицу 2 на рисунке 3), нужно просуммировать три значения вероятности – для спроса в 2, 3 и 79 единиц (т. е. по всем предыдущим и текущей строкам таблицы).

Чтобы отфильтровать нетипичные продажи в начале и конце столбца «Объем спроса» (т. е. наибольшие и наименьшие значения), необходимо выявить те из них, для которых кумулятивная вероятность меньше заданного нижнего порога или выше верхнего. Нижний порог задается пользователем на основе экспертной оценки. Верхний рассчитывается автоматически как 100 процентов минус значение нижнего порога.

Чем однороднее спрос, тем меньше должен быть нижний порог. В большинстве случаев подойдет значение, равное 2–5 процентам. Если в ряд неотфильтрованных данных попадают нежелательные продажи (те, которые были недостаточно часто, если, например, нужно выявить только типичный объем продаж, наблюдавшийся в большинстве случаев) – порог можно увеличить. Если не попадают значимые для анализа продажи (которые были относительно редко, но которые важно принять во внимание) – порог рекомендуется уменьшить.

Программа автоматически определяет, на какие значения будут заменены нетипичные продажи. В рассматриваемом примере (см. рисунок 3. Лист «Фильтрация») продажи, кумулятивная вероятность которых меньше 4 процентов (с объемом 2 и 3 ед.), заменяются на объем, равный 79 единицам. А вместо тех, кумулятивная вероятность которых больше 96 процентов (231 и 268 ед.), указывается объем 217 единиц. Важно понимать, что если продажи с наименьшим объемом характерны для деятельности компании, то они не будут отфильтрованы, так как для них кумулятивная вероятность будет выше установленного порога.

На графике «Устранение нетипичных продаж», представленном на листе «Фильтрация» (по вертикальной оси указывается объем продаж, по горизонтальной – месяцы), показан восстановленный спрос – без самых редких продаж с наибольшими и наименьшими объемами.

Как построить прогноз спроса

После того как построен восстановленный спрос за предыдущий период (выявлены нетипичные продажи и заменены на типичные), можно приступать к прогнозированию спроса на листе «Прогноз» (см. рисунок 4. Лист «Прогноз»). Так будет определено одно из слагаемых для расчета оптимального товарного запаса.

На этом листе пользователю нужно задать только период сезонности S – то есть длину одного ее цикла: например, для годовой сезонности, если данные представлены по месяцам, период равен 12.

Кроме того, потребуется подобрать коэффициенты сглаживания ряда, тренда и сезонности ɑ, ß, Ɣ, при которых прогноз будет максимально точным. Для этого необходимо последовательно перебрать все значения этих параметров в диапазоне от 0 до 1 и найти такое сочетание, при котором созданная модель как можно точнее повторяла бы восстановленный спрос в прошедшем периоде. Тогда ошибка прогнозирования будет минимальна. Вручную ничего считать не придется. В модели задействован инструмент Excel «Поиск решения». Он позволяет задать переменные, значения которых нужно подбирать для минимизации заданного показателя.

На рисунке 5 продемонстрировано, как задать условия для инструмента «Поиск решения», чтобы минимизировать ошибку прогнозирования (целевую ячейку).

Таблица, представленная на листе «Прогноз», содержит данные о спросе (гр. «Известный спрос»), полученные автоматически с листа «Фильтрация» (из гр. «Восстановленный спрос» таблицы 2), а также прогноз спроса на следующие шесть месяцев (гр. «Спрос и прогноз» и гр. «Прогноз») – в примере период прогнозирования выбран произвольно. В столбце «Прогноз» отражены сведения не только на будущий период (шесть месяцев), но и за прошедшее время (72 месяца, за которые есть фактические данные). Это нужно для того, чтобы можно было сопоставить факт и прогноз за прошедший период (см. график на листе «Прогноз») и оценить, насколько точно построена модель. Прогноз строится с месяца, следующего за окончанием одного цикла сезонности, то есть в данном примере с 13-го месяца, при этом используются показатели из скрытых вспомогательных столбцов («Уровень», «Разницы», «Тренд», «Сезонность»).

Столбец «Спрос и прогноз» содержит данные о спросе за прошедший период (они соответствуют сведениям в гр. «Известный спрос») и прогнозе на следующие полгода (используются показатели гр. «Прогноз»).

Как определить оптимальный товарный запас

Расчет товарного запаса, оптимального с точки зрения удовлетворения спроса потребителей, выполняется на отдельном листе (см. рисунок 6. Лист «Оптимальный запас»).

На нем нужно заполнить только данные о горизонте планирования и об уровне сервиса первого рода – вероятности, с которой не будет дефицита на протяжении всего времени поставки. Например, если уровень сервиса составляет 98 процентов, это означает, что при поддержании страхового запаса на рассчитанном уровне только в двух случаях из ста этого запаса может не хватить для удовлетворения спроса в планируемом периоде. При этом нужно учитывать, что для разных товаров требуется обеспечивать отсутствие дефицита с различной вероятностью (например, в зависимости от дохода, который они приносят компании, от того, насколько они востребованы покупателями и т. д.). На листе «Оптимальный запас» представлены расчеты запаса для различных уровней сервиса по анализируемой продукции – с 95 до 99 процентов (пять сценариев).

Оптимальный запас определяется в трех вариантах с учетом:

Для расчета последнего используется следующая формула.

Формула. Расчет оптимального товарного запаса

Страховые запасы автоматически рассчитываются с помощью функции Excel НОРМСТОБР.

В таблице также представлены значения вспомогательных показателей, которые используются для определения страхового запаса на случай колебания сроков поставки и общего страхового запаса. Это средний спрос за месяц и стандартное отклонение сроков поставки (которое показывает, насколько разбросаны эти сроки относительно их среднего значения). Они не зависят от уровня сервиса, и поэтому их значения одинаковы во всех столбцах таблицы. Первый показатель (средний спрос за месяц) автоматически рассчитывается на основе данных с листа «Прогноз» (гр. «Спрос и прогноз») с помощью функции Excel СРЗНАЧ – по данным о спросе за весь прошлый период (в примере – 72 месяца) или за его часть (например, за последние 10 месяцев). Второй (стандартное отклонение) – с помощью функции СТАНДОТКЛОН по данным с листа «Сроки поставок».

Как видно из расчетов (см. рисунок 6. Лист «Оптимальный запас»), чем выше уровень сервиса, тем больше будет товарный запас.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!

Зарегистрируйтесь или войдите на сайт:

Вы получите доступ к уникальному контенту, который закрыт для незарегистрированных пользователей!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
×

Вам предоставлен неограниченный доступ к «Системе Финансовый директор»

Активируйте доступ прямо сейчас.
Завтра он сгорит!

 
10:11:49


Также, вы сможете прочитать свежий номер журнала Финансовый Директор, архив с 2008 года, скачать книги и формы документов, а также воспользоваться всеми сервисами  для подписчиков.

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