Расчёт закупки в Excel: покрытие спроса и кратность упаковки

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

Согласуйте горизонт и единицы

Пусть B2 — спрос в штуках за день, C2 — покрытие в днях, D2 — доступный остаток в штуках, E2 — подтверждённая поставка, которая придёт вовремя, F2 — число штук в упаковке. Целевой запас равен B2*C2. Потребность равна целевому запасу минус доступное количество, ограниченная снизу нулём.

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

Округляйте количество упаковок вверх

При спросе 3 штуки в день и покрытии 10 дней цель 30. Остаток 8 и входящая поставка 5 дают доступно 13, потребность 17. Если в упаковке 6 штук, надо заказать 3 упаковки, то есть 18 штук. Обычное округление до ближайшего может оставить дефицит.

Формула LET сначала вычисляет need, затем делит на кратность и округляет вверх число упаковок. F2 должно быть положительным целым. Проверку входных значений разместите отдельно: деление на нулевую кратность не является допустимым вариантом закупки.

=LET(target;B2*C2;available;D2+E2;need;МАКС(0;target-available);ОКРУГЛВВЕРХ(need/F2;0)*F2)

Покажите результат в двух единицах

Закупщику полезно видеть потребность в штуках, число упаковок и итоговое количество. Разница между 17 и 18 возникает из-за кратности и должна объясняться, а не выглядеть ошибкой. Если есть минимальная партия, это дополнительное правило после расчёта положительной потребности.

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

Проверьте исключения до отправки заказа

В контрольном наборе нужны дефицит 17 при упаковке 6, точная потребность 12, избыток запаса и нулевой спрос. Результаты 18,12,0 и 0 проверяют округление и нижнюю границу. Затем добавьте неизвестный спрос и просроченную входящую поставку: такие строки требуют отдельного решения, а не нулевого заказа.

Средний спрос может быть искажён отсутствием товара: нулевые продажи в дни дефицита не означают отсутствие покупателей. Оценку спроса готовят отдельно, с учётом доступности и сезонности. Данная формула использует уже согласованное значение, а не прогнозирует его.

После расчёта сгруппируйте заказ по поставщику и проверьте ограничения бюджета, сроков и складской вместимости. Норматив покрытия даёт исходную потребность, но окончательный заказ может учитывать дополнительные условия закупки.

При обновлении книги сохраните дату расчёта и источники остатка и поставок. Закупщику важно понимать, на каком срезе получено количество. Контрольные примеры помогают проверить формулу, а актуальность входных данных определяет, пригоден ли результат сегодня.

Соберите обновляемый отчёт на практике

В практикуме курса вы рассчитаете закупку по нормативу покрытия на основе остатков и движений. Формулы и проверочные результаты помогают увидеть связь исходных данных с заказом. Вся книга затем обновляется на следующем комплекте файлов, поэтому проверяется и расчёт, и регулярный рабочий процесс.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.