Главная »Управление запасами» Как отслеживать товары в Excel просто и без проблем. Анализ остатков на складе и прогноз закупок 
Этот пост является продолжением моей предыдущей серии статей об эффективном управлении запасами. Сегодня мы разберемся с темой, как отслеживать товары в excel. Как собрать остатки запасов, заказы, ранее заказанные товары, ABC-анализ и т.д. В одну таблицу Excel.
Несомненно, данную статью можно рассматривать как отдельный материал для учета и планирования товаров и их запасов в excel. Постараюсь все показать понятно и просто, избегая макросов.
Аналитика в Excel
Итак, все начинается даже не с анализа, а просто с систематизации данных по продуктам. Excel — отличный инструмент для таких действий. Лучшего пока не нашли. По крайней мере, для малого и среднего бизнеса это наиболее эффективный и экономичный метод поддержания остатков запасов, не говоря уже об анализе запасов, ABC-анализе, прогнозировании будущих покупок и так далее.
Начнем с самого простого. Тогда это углубит и расширит возможности ведения товарного учета в excel. Каждый будет выбирать, на каком уровне хватит для работы.
Как в Excel вести учет товара, простой шаблон
Начнем с самого простого, а именно, когда организация собирает заказы из магазинов, и вам нужно объединить заказы, разместить заказ у поставщика. (см рисунок 1)
В столбце E мы воспользуемся простой формулой, чтобы суммировать запросы наших разных клиентов. Столбец F — это наш бесконечный баланс или страховой запас. В столбце G мы получим данные о том, сколько нам нужно заказать у поставщика на основе наших остатков, заказов в магазине и страхового запаса.
Сводная таблица на рис.1
Синяя стрелка указывает на вкладки «Клиент 1», «Клиент 2» и так далее. Это заявки из наших магазинов или покупателей, см. Рис. 2 и тендеры. 3. У каждого покупателя свое количество, в нашем случае единица измерения находится в коробках.
Рис. 2. Клиент 1
Рис 3. Клиент 2
Теперь мы можем рассмотреть, как отслеживать товары в excel, когда нужно вывести заказы в одной таблице. По простой формуле в первую очередь переносим все заказы из магазинов в столбец E. См. Рисунок 4.
= (‘заказчик 1’! D2 + ‘заказчик 2’! D2)
рисунок 4. Сводка заказов в столбце E
Мы расширяем формулу по столбцу E и получаем данные для всех продуктов (см. Рисунок 5). Мы получили сводную информацию от всех магазинов. (здесь рассматривается всего 2 магазина, но, думаю, суть ясна)
рисунок 5
Теперь нам нужно учесть остатки запасов и указанный минимальный страховой запас, чтобы разместить заказ от поставщика на необходимое нам количество. Также прописываем простую формулу:
= (RE2-MI2) -FA2
растягиваем формулу по столбику и получаем 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-анализ товара. И мы классифицировали страховой запас в зависимости от важности продукта на основе оценки ABC-анализа. (Эту важную и интересную тему по оптимизации складских запасов мы обсуждали в предыдущей статье)
Для продуктов с рейтингом A (где A — самый прибыльный продукт) мы выделили страховой запас в днях по сравнению со средней ежедневной продажей в 14 дней. Смотрим на первую строчку и получаем:
3 коробки продажи в день * 14 дней продажи = 42 дня. (У нас 41 день, потому что Excel округляет в большую сторону при расчете 90 ящиков в месяц / 30,5 дней в месяц). См. Формулу
= (H2 * 14)
рис. 10. Страховой запас для товаров категории А
В третьих. Согласно оценке статьи B, мы предоставили 7-дневный страховой запас. См. Рис. 11. (Для продуктов категории C мы выделили страховой запас только на 3 дня)
Рис 11. Страховой запас для продуктов категории B
Вывод
Итак, нам необходимо заказать 11 ящиков сахарного песка (см. Первую строку таблицы). Здесь мы рассматриваем 50 коробок в пути, 10 дней доставки при средней продаже 3 коробки в день).
Оставшийся запас 10 коробок + 50 коробок в пути = 60 запасных коробок. За 10 дней продажи составят 30 коробок (10 * 3). У нас есть запас в 41 ящике. Следовательно, 60 — 30 — 42 = минус 11 коробок, которые мы должны заказывать у поставщика.
Для удобства можно умножить (-11) в Ecxel на минус 1. Таким образом, мы получаем положительное значение.
Конечно, здесь я показал только один пример и суть того, как вести учет товаров и запасов в Excel. Но уже большой шаг вперед в субъективных ощущениях и возможностях малого бизнеса. И все можно детализировать и уточнить.
