Оборачиваемость товара: формула в Excel

Введите себестоимость проданного товара за период и запас на начало и конец. Калькулятор даст средний остаток, число оборотов и срок одного оборота — те же числа, что дадут формулы в Excel.

Результат

Средний остаток: 150 ₽
Оборотов за период: 4 раза
Дней на оборот: 30 дней

Средний остаток — 150 ₽, оборотов за период — 4 раза, срок одного оборота — 30 дней. Остатки и оборот вводите в одной оценке.

Оборот и остатки берите в одной оценке: либо всё по себестоимости, либо всё в продажных ценах. Если смешать себестоимость с розницей, коэффициент поедет.

Какая формула у оборачиваемости

Оборачиваемость считают в двух видах: сколько раз за период товар обернулся и за сколько дней проходит один оборот. Первое — коэффициент, второе — срок в днях. Собственнику ближе дни: «этот товар лежит на складе сорок дней» понятнее, чем «обернулся девять раз».

Как перенести формулу оборачиваемости товара в Excel

Сделайте на листе пять ячеек для ввода: себестоимость проданного товара за период, запас на начало, запас на конец, дней в периоде и, если нужен, целевой срок оборота. В ячейку среднего остатка поставьте формулу =(B2+B3)/2, в ячейку оборотов — =B1/B5, в ячейку дней — =B4/B6. Адреса подставьте свои, смысл от этого не меняется.

Если в периоде больше двух точек остатка — например, остаток на конец каждого месяца, — средний остаток считайте по всем точкам через СРЗНАЧ, а не только по началу и концу. Для сезонного товара такой средний честнее: он не даст одному пиковому месяцу перекосить картину.

Защитите ячейки с формулами и оставьте открытыми только ячейки для ввода. Одна случайно стёртая формула в таблице, которой пользуются несколько человек, — обычное дело, и её потом ищут неделю.

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

Где в формулах чаще всего ошибаются

Первая ошибка — разные базы. Оборот взяли по себестоимости, а остаток по продажным ценам: остаток в продажных ценах больше, чем в той же оценке по себестоимости, поэтому коэффициент выходит заниженным и товар кажется медленнее, чем на самом деле. Считать и оборот, и остатки нужно в одной оценке — либо всё по себестоимости, либо всё в продажных ценах.

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

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

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

Два примера на калькуляторе

Магазин за квартал

За период себестоимость проданного товара — 600, запас на начало — 100, на конец — 200. Средний остаток — 150 ₽.

Оборотов за период — 4 раза, срок одного оборота — 30 дней. В Excel те же числа дадут формулы =(начало+конец)/2, =оборот/средний остаток и =дней/обороты.

Посчитать с этими числами

Товар с ровным остатком

Если запас не менялся — 150 на начало и 150 на конец, — средний остаток равен 150 ₽, а оборотов за период выходит 6 раз.

Срок оборота — 15 дней. Такой расчёт удобно брать за точку отсчёта: дальше видно, ускорился товар или замедлился.

Посчитать с этими числами

Частые вопросы

Как в Excel посчитать оборачиваемость товара за месяц?

Возьмите себестоимость проданного товара за месяц и остатки на начало и конец месяца. Средний остаток — полусумма остатков, обороты — себестоимость, делённая на средний остаток, дни на оборот — число дней месяца, делённое на обороты.

Что подставлять в формулу — выручку или себестоимость?

Можно и то и другое, но не смешивая. Если оборот берёте по себестоимости, то и остатки берите по себестоимости. Если по выручке в продажных ценах — остатки тоже в продажных ценах. Смешанный расчёт даёт неверный коэффициент.

Почему оборачиваемость в днях важнее коэффициента?

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

Как учесть сезонность в расчёте?

Берите средний остаток не по двум точкам, а по остаткам на конец каждого месяца периода — через СРЗНАЧ по всем точкам. И считайте оборачиваемость по каждому сезону отдельно: смешав пик и мёртвый сезон в одном годе, вы не увидите ни того, ни другого.

Какой срок оборота считать нормальным?

Единой нормы нет: у скоропортящегося товара норма — дни, у мебели — месяцы. Ориентир задаёт сам бизнес: сколько дней товар может лежать на складе, пока это не съедает прибыль. Это и есть целевой срок, с которым сравнивают факт.

Таблица считает дни, но не показывает, где теряются деньги

Собрать остатки, продажи и маржу в одну картину

Формула в Excel отвечает на один вопрос: сколько дней лежит товар. Но чтобы понять, откуда взялся запас и почему он не уходит, нужны остатки, продажи, закупки и маржа по каждой позиции — в одном месте и в свежем виде.

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

Читать об услуге «Панель собственника»