Этот материал служит продолжением серии моих предыдущих статей об эффективном управлении запасами. Сегодня мы обсудим тему, как вести учет свойств в Excel. Как привести остатки запасов, заказы, ранее заказанные продукты, ABC-анализ и т д в таблицу Excel.
Несомненно, данную статью можно рассматривать как отдельный материал по учету и планированию товаров и их запасов в excel. Я постараюсь показать все четко и просто, избегая макросов.
Аналитика в Excel
Так что все начинается даже не с аналитики, а просто сортировки данных по продуктам. Excel — отличный инструмент для таких задач. Лучшее еще предстоит найти. По крайней мере, для малого и среднего бизнеса это наиболее эффективный и доступный метод поддержания товарных остатков, не говоря уже об анализе запасов, АВС-анализе, прогнозировании будущих закупок и т.п.
Начнем с самого простого. Тогда вы углубите и расширите возможности ведения товарного учета в excel. Каждый сам выберет, какой уровень будет достаточен для его работы.
Как в Excel вести учет товара, простой шаблон
Начнем с самого простого, а именно, когда организация собирает приложения из магазинов и ей нужно собрать заказы, она размещает заказ у поставщика. (см рис. 1)
В столбце E, используя простую формулу, мы сопоставим запросы от наших разных клиентов. Столбец F — это наш вечный баланс или страховой запас. В столбце G мы получим данные о том, сколько нам нужно заказать у поставщика на основе наших остатков, запросов магазина и страхового запаса.
Рисунок 1 динамическая таблица
Синяя стрелка указывает на вкладки, где «Клиент 1», «Клиент 2» и т.д. Это заявки от наших магазинов или клиентов, см фото 2 и гонки. 3. У каждого клиента свое количество, в нашем случае единица измерения в коробках.
Рисунок 2. Клиент 1
Рисунок 3. Клиент 2
Теперь можно рассмотреть, как вести учет товаров в Excel, когда заказы нужно занести в таблицу. Используя простую формулу, мы сначала суммируем все заказы магазина в столбце E. См рис. 4.
=(‘клиент 1′!D2+’клиент 2’!D2)
рис.4. Сводка заказов в столбце E
Растягиваем формулу вниз по столбцу Е и получаем данные по всем товарам, см рисунок 5. Получаем сводную информацию по всем магазинам. (здесь учитываются только 2 магазина, но думаю суть понятна)
цифра 5
Теперь мы должны учитывать остатки на складах и указанный минимальный страховой запас, чтобы иметь возможность заказать нужное нам количество у поставщика. Также пишем простую формулу:
=(D2-E2)-F2
протягиваем формулу вниз по колонке и получаем 1 ящик препортированной муки по заказу поставщика. Для остальных товаров запасов достаточно.
рис. 6 для заказа поставщика
Обратите внимание, что мы также вычитаем F (страховой запас) из баланса, чтобы он не учитывался в цифрах, полученных по заказу.
Опять же, это только суть расчета.
Мы понимаем, что заказывать 1 коробку, вероятно, не имеет смысла. Наш страховой запас в этом случае не пострадает из-за одной штуки.
Теперь перейдем к более сложным расчетам, где мы будем опираться на исторический анализ продаж с учетом расширенного ABC-анализа, страхового запаса, товаров в пути и т д.
Как в Excel вести учет товара на основе продаж прошлых периодов
Как управлять запасами и строить прогноз закупок в Excel на основе прошлых продаж, используя ABC-анализ и другие инструменты, это уже задача посложнее, но и гораздо интереснее.
Здесь я также дам суть, формулы и логику построения управления запасами в Excel.
Итак, мы загружаем средние продажи в месяц из базы данных. Пока в этом варианте будем считать, что они стабильны.
рисунок 7. Средний объем продаж в месяц
Далее берем средние продажи в столбец G нашего планировщика, то есть в итоговый файл.
Рис. 8. Сводный файл анализа
Делаем это с помощью формулы ВПР.
=ВПР(A:A;’средний месячный объем продаж’!A:D;4;0)
Суть этой формулы в том, что требуемые данные извлекаются по уникальному коду или другому значению, независимо от того, в каком порядке они находятся в источнике данных.
Мы также можем извлечь другие необходимые данные, например, нужную нам информацию, которая уже транспортируется к нам поставщиком, например, товары в пути. Мы также должны их учитывать.
В результате получаем вот такое изображение:
Первый. Мы преобразовали средние продажи в месяц, даже для удобства, в средние продажи в день, используя простую формулу = G/30,5 (см рис. 9). Средние продажи в день — столбец H
рис. 9 заполненный итоговый файл
Второй. Учитывался АВС-анализ по товарам. И они оценили страховой запас относительно важности продукта на основе рейтинга ABC-анализа. (Эту важную и интересную тему оптимизации запасов мы обсуждали в предыдущей статье)
Для продуктов класса А (где А — самый прибыльный продукт) мы размещаем резервный запас в днях по отношению к среднедневным продажам за 14 дней. Смотрим на первую строку и получаем:
3 коробки продаж в день * 14 дней продаж = 42 дня. (У нас есть 41 день, потому что Excel округлял при расчете 90 коробок в месяц / 30,5 дней в месяц.) См формулу
=(Н2*14)
рис. 10 страховых запасов для товаров категории А
В третьих. Для товара B у нас есть запас на 7 дней. См рис. 11. (Для товаров категории С мы ставим страховой запас только на 3 дня)
Рисунок 11. Страховой запас товаров категории Б
Вывод
Таким образом, сахарного песка (см первую строку таблицы) мы должны заказать 11 ящиков. Это включает 50 ящиков в пути, 10 дней доставки при средней продаже 3 ящика в день).
Остаток товара 10 ящиков + 50 ящиков в пути = 60 ящиков на складе. За 10 дней продажи составят 30 коробок (10*3). Наш страховой запас составил 41 ящик. В итоге 60 — 30 — 42 = минус 11 ящиков, которые мы должны заказать у поставщика.
Для удобства можно умножить (-11) в Ecxel на минус 1. Так мы получим положительное значение.
Конечно, здесь я показал только образец и суть того, как вести товарный и складской учет в Excel. Но это уже большой шаг вперед по субъективным ощущениям и возможностям малого бизнеса. И все можно детализировать и доработать. Мы увидим это в следующей главе.
Складской учет товаров в Excel с расширенным АВС анализом.
Учет товарных запасов в Excel можно вести аналитически все более и более углубленно в зависимости от навыков и потребностей.
В предыдущей главе мы использовали АВС-анализ для удовлетворения покупательского спроса и оптимизации страхового запаса, когда сделали больший страховой запас для категории А и минимальный страховой запас для категории С. Если в первой главе, (в простейшем варианте), страховой запас формируем вручную, во второй главе отталкиваемся от среднедневных продаж. Страховой запас создавался за считанные дни. Подробнее об этом мы говорили в моей предыдущей статье.
Здесь мы углубимся в анализ ABC, что поможет нам быть еще более точными.
Если классификация товаров по АВС-анализу проводилась по уровню рентабельности каждого товара, где А — самый прибыльный товар, В — товар со средней рентабельностью и С — товар с наименьшей рентабельностью, то теперь АВС дополнительно классифицируются по следующим критериям:
«А» — товар с ежедневным спросом
«В»: товар со средним спросом (например, 7-15 дней в месяц)
«С» — товар с редким спросом (менее 7 дней в месяц)
Тот же принцип можно использовать не для количества дней в месяце, а для количества месяцев в году.
И зададим еще один критерий. Это количество обращений к нам, к провайдеру.
Здесь количество посещений – это сколько было совершено отдельных заказов, покупок по каждому товару, вне зависимости от количества, стоимости и рентабельности товара. Здесь мы видим картину того, как часто наши клиенты обращаются к нам по каждому продукту. Это подробно обсуждалось в моей статье «Прогнозирование спроса в управлении запасами». XYZ-анализ и другие эффективные инструменты анализа»
«А» — количество посещений от 500 и более
«В» — 150 — 499 попаданий.
«С» — менее 150 посещений в месяц.
В результате продукты с рейтингом AAA являются ТОП-продуктами, требующими особого внимания.
Расширенный АВС анализ в таблице Excel
См рисунок. 12. Серым цветом выделяем столбцы, где учитываем товары по АВС по постоянному спросу в днях и по количеству посещений. Мы также можем перенести эти данные из данных, загруженных из нашей базы данных, с помощью формулы ВПР.
Теперь наш рейтинг ABC-анализа изменился, и это может привести к пересмотру страхового запаса.
Обратите внимание на первую строку, выделенную зеленым цветом. Продукт имеет рейтинг ААА. Также обратите внимание на восьмую строку. Вот рейтинг продукта БАД. Может есть смысл сделать стоковую страховку на этот товар больше указанных 7 дней?
Для ясности мы сделаем это, назначив этому продукту 14-дневный страховой запас. Сейчас страховой запас выше, чем раньше. 44 ящика против 22 ящиков. См рисунок одиннадцать.
Рис. 12 Расширенная квалификация ABC
А как же рейтинг CCC? Нужен ли страховой запас для этого продукта? И вообще, при нехватке оборотных средств и складских площадей, нужен ли этот товар в нашей номенклатуре?
Также интересует продукт с рейтингом CAA.
Прибыль не высокая, но к нам приходят за этим товаром. Я бы тоже обратила особое внимание на этот продукт. Благодаря его высокой оборачиваемости, достигнутой, в том числе, его постоянной доступностью, мы увеличили прибыль.
Управление товарными запасами в Excel. Заключение
В анализ Excel также можно включить товар со знаком сезонности. Вы можете активировать сравнение отклонений заказов от наших филиалов или магазинов, когда мы сразу видим непропорционально маленький или большой заказ. Это защитит нас от нехватки или избыточного запаса.