Excel для закупщика: ABC/XYZ, страховой запас, сравнение КП и расчёт НМЦК
Закупщику Excel нужен для четырёх повторяющихся расчётов. Он делит номенклатуру на группы ABC/XYZ, чтобы понять, какие позиции контролировать каждый день, а какие раз в месяц. Считает страховой запас, точку заказа и оборачиваемость, чтобы склад не пустел и не забивался. Сводит коммерческие предложения в таблицу и приводит их к одной цене с учётом НДС, доставки и отсрочки. В госзакупках — обосновывает начальную цену контракта методом сопоставимых рыночных цен. Для этого хватает уверенного базового уровня: формулы, сортировка, сводные таблицы и аккуратная структура листа.
Содержание

Главная трудность не в функциях, а в исходных данных. Выгрузка из учётной системы приходит с разными единицами измерения, дублями и пустыми месяцами, а КП — с ценами то с НДС, то без него. Поэтому любой расчёт начинается с подготовки: одна строка — одна позиция, один столбец — один показатель, единицы приведены к одной. На такой таблице формулы пишутся за минуты, а на неподготовленной дают уверенный, но неверный ответ.
Где таблица уместна, а где нужна учётная система
Снабженец обычно держит несколько постоянных файлов: реестр номенклатуры с группами, лист пополнения, шаблон сравнения КП, журнал поставщиков со сроками и браком. Когда расчёт становится ежедневным и охватывает тысячи артикулов, его переносят в 1С или ERP, а в таблице проверяют настройки автозаказа.
ABC и XYZ: какие позиции держать под контролем
ABC-анализ делит номенклатуру по вкладу в затраты. В учебном примере снабженец завода металлоконструкций выгружает годовые закупки по 180 позициям расходников, сводной таблицей суммирует затраты, сортирует их по убыванию и считает накопленную долю: =СУММ($C$2:C2)/$C$182. Позиции, набирающие первые 80 % затрат, получают группу A, следующие 15 % — B, остальные — C. Границы 80/15/5 — договорённость, а не стандарт: их можно сдвинуть, но не стоит менять от отчёта к отчёту.
XYZ-анализ показывает, насколько ровно расходуется позиция. По помесячному расходу считают коэффициент вариации: =СТАНДОТКЛОН.Г(D2:O2)/СРЗНАЧ(D2:O2). Часто X — до 10 %, Y — до 25 %, Z — выше. Сварочная проволока, которая уходит каждый месяц в одном объёме, попадёт в AX: дорого и предсказуемо, её держат по чёткой точке заказа. Редкий абразив под одного заказчика окажется в CZ, и его разумнее заказывать под потребность.
Анализ портят пустые месяцы. Если позиции не было на складе, нулевой расход значит не «не нужна», а «нечем было выдать». Такие месяцы исключают, иначе стабильная позиция ложно уйдёт в группу Z.
Страховой запас, точка заказа и оборачиваемость
Страховой запас закрывает два риска: расход вырос и поставщик опоздал. Когда истории мало, его считают по максимумам. Проволоки уходит в среднем 30 кг в день, в пиковые дни — 45 кг; поставщик обычно везёт 10 дней, в худшем случае — 14. Страховой запас: 45 × 14 − 30 × 10 = 330 кг. Точка заказа — средний расход за срок поставки плюс страховой запас: 30 × 10 + 330 = 630 кг. Метод максимумов закладывает много запаса, поэтому для групп A и X его со временем заменяют формулой через стандартное отклонение спроса и уровень сервиса.
Оборачиваемость показывает обратную сторону: сколько дней запас лежит без движения. Средний запас делят на среднедневной расход: 900 кг на складе при расходе 30 кг в день — это 30 дней. Для позиции, которую привозят за 10 дней, многовато: деньги заморожены в металле, и точку заказа стоит пересмотреть.
Сравнение КП: приводим к одной цене
Коммерческие предложения сравнивают по полной стоимости для компании, а не по цене за единицу. В шаблоне строки — поставщики, столбцы — цена, ставка НДС, количество, доставка, дни отсрочки и итог, который считается формулой.
Учебный пример: нужно 2 000 кг проволоки. Поставщик А просит 182 ₽ за кг без НДС, доставка 6 000 ₽, оплата вперёд. Б — 225 ₽ с НДС, доставка включена, отсрочка 30 дней. В — 215 ₽ с НДС, самовывоз своим транспортом за 4 500 ₽, отсрочка 14 дней. С НДС 22 % получаем: А — 450 080 ₽, Б — 450 000 ₽, В — 434 500 ₽.
Теперь учитываем деньги во времени. Отсрочка — бесплатный кредит, его ценность считают по внутренней ставке: =Сумма*Ставка*Дни/365. При условных 18 % годовых отсрочка у Б стоит около 6 658 ₽, у В — около 3 000 ₽, а предоплату А за 10 дней до отгрузки, наоборот, прибавляют: около 2 220 ₽. Итог: В — около 431 500 ₽, Б — 443 300 ₽, А — 452 300 ₽. Ставку держат в отдельной ячейке, согласованной с финансистами.
НМЦК по 44-ФЗ: что в расчёте можно корректировать
Для госзаказчика приоритетный метод определения начальной (максимальной) цены контракта — метод сопоставимых рыночных цен, а шаги расчёта описаны в методических рекомендациях, утверждённых приказом Минэкономразвития от 02.10.2013 № 567. Это не обязательный норматив: министерство в письме № Д28и-929 называло их ненормативными. Новых рекомендаций Минфин пока не утвердил, и, как отмечает подборка КонсультантПлюс, на практике ориентируются на документ № 567.
В разделе III рекомендаций есть деталь, которую шаблоны обоснования часто упускают. Цену из реестра контрактов можно поправить с учётом того, как контракт заключён (п. 3.16): после конкурса — повысить не более чем на 10 %, после аукциона — на 13 %, после запроса котировок или предложений — на 17 %, а цену контракта с единственным поставщиком не корректируют. Цены старше шести месяцев приводят к текущему уровню по индексу потребительских цен (пп. 3.14, 3.18), а цен рекомендуется брать не меньше трёх (п. 3.19).
Учебный пример: нужно 40 офисных кресел. Есть три КП — 12 400, 13 100 и 11 900 ₽ — и контракт из реестра по итогам аукциона за 10 600 ₽. Без поправки средняя цена 12 000 ₽, НМЦК — 480 000 ₽. С поправкой на 13 % контрактная цена становится 11 978 ₽, средняя — 12 344,50 ₽, НМЦК — 493 780 ₽. Почти 14 тысяч разницы могут решить, придут ли участники на торги.
Однородность цен проверяют коэффициентом вариации: выше 33 % совокупность считается неоднородной, и нужны дополнительные исследования (п. 3.20.2). Пункт 3.20.1 разрешает считать его стандартными функциями табличных редакторов. Тонкость в том, что на трёх-пяти ценах СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г расходятся заметно: в примере без поправки — 8,79 % против 7,61 %. Сверьте функцию шаблона с формулой в п. 3.20 и не меняйте её от закупки к закупке.
С чего начать и в каком порядке осваивать
Порядок повторяет путь данных в работе закупщика: сначала таблицу приводят в порядок, затем группируют номенклатуру, потом считают пополнение и только после этого сравнивают поставщиков. Точку заказа бессмысленно считать по позициям с перепутанными единицами, а сравнение КП без понятной структуры листа превращается в ручную арифметику.
- Подготовка. Приведите годовую выгрузку закупок к виду «одна строка — одна позиция», уберите дубли, отметьте месяцы без остатка.
- ABC. Посчитайте затраты сводной таблицей и разделите позиции формулой накопленной доли.
- XYZ. Добавьте коэффициент вариации и сведите группы в матрицу 3 × 3.
- Пополнение. Для десяти позиций группы A посчитайте страховой запас, точку заказа и оборачиваемость.
- Шаблон КП. Соберите лист с ячейкой ставки и проверьте его на реальных предложениях.
- НМЦК — если вы работаете с госзаказчиком или участвуете в торгах.
Упражнения и типичные ошибки
ABC полезно пересчитать вручную на двадцати позициях и сравнить с формулой. Для пополнения «проживите» прошлый квартал: возьмите реальные даты поставок и расход и посмотрите, сработала бы ваша точка заказа. Шаблон КП проверяют подложенной ошибкой: впишите цену без НДС в столбец с НДС и посмотрите, поймает ли её проверочная формула.
Новички чаще всего смешивают цены с НДС и без, сравнивают штуки с упаковками, вписывают итог числом поверх формулы. В НМЦК берут цену из реестра без поправки и без пересчёта по сроку давности и не сохраняют источники — а КП с датой и скриншоты нужны, чтобы расчёт можно было повторить.
Чему учат на курсах
Курс полезен, если не хватает уверенности в формулах и сводных таблицах. Универсальный курс по Excel даст технику, а закупочную логику придётся переносить на свои задачи самостоятельно.
Технику можно подтянуть на курсе Skillbox «Excel и Google Таблицы с нуля до PRO»: четыре месяца по 3–5 часов в неделю, базовый блок из 13 модулей, блоки о формулах и очистке данных, сводных таблицах и отчётности по бюджету и KPI, Google Таблицах. Power Query входит только в максимальный тариф, закупочных кейсов в программе нет.
Если нужна связка Excel и закупок, ищите программу, где таблицы разбирают рядом с запасами и поставщиками,.
Такая связка заявлена на четырёхдневном «Практическом курсе для менеджера по закупкам» Moscow Business School: в первый день — бюджет, управление запасами и работа в Excel, затем поставщики, договоры, риски и переговоры. Что именно разбирают в Excel, на странице не раскрыто, так что программу первого дня уточните у школы. Удостоверение о повышении квалификации получают слушатели со средним профессиональным или высшим образованием, остальные — сертификат.
Тем, кто пока работает в Excel неуверенно, подойдёт формат с живыми занятиями и итоговой рабочей таблицей вместо набора разрозненных упражнений.
На курсе Академии ТОП «Excel с нуля до профи» базовый уровень охватывает формулы, фильтры, графики и сводные таблицы, а итог — собственный рабочий файл вроде калькулятора или планировщика, который можно сделать сразу под лист пополнения. Содержание уровня PRO не раскрыто, а срок в каталоге и на сайте школы расходится, поэтому программу и цену стоит уточнить у школы.
Как навык влияет на доход
Отдельной надбавки за Excel открытые данные не показывают, сопоставимой статистики найти не удалось. Показательнее вакансии. Специалисту по закупкам в московском отеле (сентябрь 2026 года, опыт 1–3 года) предлагают 91 000 ₽ на руки; Excel отдельно не назван, но в обязанностях анализ рыночных цен и заявки по остаткам склада. Закупщику оборудования (Москва, удалённо, сентябрь 2026 года, опыт 3–6 лет) — от 70 000 до 200 000 ₽ до вычета налогов: оклад, KPI и до 7 % от валовой прибыли.
Это два предложения работодателей, а не статистика, но механика видна: когда премия зависит от прибыли и экономии, умение посчитать полную стоимость предложения и не заморозить деньги в запасах напрямую влияет на переменную часть. Чтобы оценить рынок в своём городе, отфильтруйте вакансии по региону и опыту и посмотрите, упоминаются ли сводные таблицы, ABC-анализ или расчёт НМЦК.
Что дальше
Начните с одной выгрузки и одного расчёта, который нужен на этой неделе: ABC по годовым закупкам или шаблон сравнения КП. Если со временем захочется перейти от закупок к планированию цепочки целиком, посмотрите рейтинг онлайн-школ по логистике и управлению цепями поставок — он охватывает всё направление, а не только Excel для закупок.