Магазин: наценка и цены в Excel — описание и предпросмотр, скачать — по подписке
Цена продажи от закупочной по наценке (своей или стандартной), маржа в рублях и процентах. Формулы, выпадающие списки и параметры уже настроены; файл проверен пересчётом.
Формулник сам поймёт, что нужно: формула, таблица, документ или правка файла — и проверит результат.
.xlsx для Excel 2016–365, LibreOffice, Р7-Офис; открывается в Яндекс и Google Таблицах.
Скачать .xlsx — по подпискеГлавное в книге
- Лист «Цены»: 7 столбцов; считаются сами — «Цена продажи», «Маржа», «Маржа, %»
- Выбор из списка: «Группа» — без опечаток
- Подсветка: «Маржа, %» — заметно, где проблема
- Лист «Параметры»: 1 параметр — ставки и настройки меняются в одном месте, формулы берут их оттуда
Что внутри
Лист «Цены»
| Товар | Группа | Закупочная цена | Наценка, % | Цена продажи | Маржа | Маржа, % |
|---|---|---|---|---|---|---|
| Чай чёрный 100 г | Бакалея | 120 | формула | формула | формула | |
| Шоколад 90 г | Сладости | 75 | 60 | формула | формула | формула |
| Вода 0,5 л | Напитки | 28 | 12 | формула | формула | формула |
Лист «Параметры»
- Стандартная наценка, %: 35
Как пользоваться
- Внесите закупочную цену; наценку — только если она отличается от стандартной.
- Цена округляется до рубля; жёлтым — маржа ниже 15 %.
- Новые строки добавляйте прямо под таблицей — формулы и проверки продолжатся сами.
Похожие шаблоны
Весь учёт для магазина — комплект книг
Наценка и цены в магазине: цена продажи от закупки, своя наценка группы и контроль маржи
В книге цена продажи получается из закупочной: закупка × (1 + наценка / 100) с округлением до рубля. Наценку можно не вписывать — тогда берётся стандартная с листа «Параметры» (35 %); для отдельных товаров ставится своя. Справа считается маржа в рублях и в процентах от цены продажи, а строки с маржой ниже 15 % подсвечиваются жёлтым.
В образце чай за 120 ₽ со стандартной наценкой стоит 162 ₽, шоколад за 75 ₽ с наценкой 60 % — 120 ₽, вода за 28 ₽ с наценкой 12 % — 31 ₽ и попадает в жёлтые: маржа 9,7 %.
По шагам
- На листе «Справочники» перечислите товарные группы своего магазина: бакалея, сладости, напитки, хозтовары и т. п.
- Внесите товары с группой и закупочной ценой из последней накладной. Наценку пока не трогайте.
- На листе «Параметры» задайте стандартную наценку, по которой у вас идёт большинство товаров.
- Для товаров-«магнитов» (вода, хлеб, сахар) впишите низкую наценку, для импульсных покупок у кассы — высокую.
- После каждого прихода по новой цене меняйте закупку — цена продажи и маржа пересчитаются. Распечатайте жёлтые строки и решите, поднимать ли цену.
Какая нужна наценка, чтобы маржа была 15 %
Порог подсветки в книге задан маржой, а цену вы задаёте наценкой. Перевод простой: наценка = маржа / (1 − маржа). Для маржи 15 % нужна наценка 17,65 %, для 25 % — 33,3 %, для 30 % — 42,9 %. Добавьте столбец с этой формулой рядом с целевой маржой в ячейке J1.
=ОКРУГЛ($J$1/(1-$J$1)*100;2)Цена «на девятку»
Чтобы на ценнике было 169 ₽ вместо 162 ₽, округлите цену вверх до десятков и вычтите рубль. Замените формулу «Цены продажи» или поставьте её в соседний столбец «Цена на ценник». Маржа считайте от ценника.
=ОКРУГЛВВЕРХ([@[Цена продажи]]/10;0)*10-1Поставщик поднял цены на 8 %
Не переписывайте закупку по каждой строке: в свободном столбце умножьте старую закупку на 1,08, скопируйте результат и вставьте как значения поверх «Закупочной цены» только для товаров этого поставщика. Цены продажи обновятся с сохранением наценок.
Образец: что даёт округление до рубля
| Товар | Закупка | Наценка | Цена | Маржа |
|---|---|---|---|---|
| Чай чёрный 100 г | 120 | 35 % (стандартная) | 162 | 25,9 % |
| Шоколад 90 г | 75 | 60 % | 120 | 37,5 % |
| Вода 0,5 л | 28 | 12 % | 31 | 9,7 % |
Частые ошибки
- Наценка вписана как 0,35 вместо 35 — столбец ждёт проценты числом: 0,35 означает наценку 0,35 %, и цена почти равна закупке.
- Цена ниже закупки после округления у дешёвых товаров — округление до рубля у товара за 5 ₽ съедает значимую часть наценки. Для копеечного товара поменяйте округление на два знака.
- Закупку вносят с доставкой не у всех товаров — маржа разных групп становится несравнимой. Решите, включать ли доставку в закупку, и делайте это для всех.
- Наценку подняли выше разрешённой для регулируемых товаров — для большинства товаров цены свободные, но на отдельные группы предельные надбавки устанавливает регион — например, на лекарства из перечня ЖНВЛП; такие товары держите в своей группе с отдельной наценкой.
Вопросы и ответы
Как посчитать цену с наценкой в Excel?
Умножьте закупку на 1 плюс наценку: =ОКРУГЛ(C2*(1+35/100);0) — для наценки 35 % с округлением до рубля.
Как посчитать маржу в процентах?
Разделите разницу цены и закупки на цену продажи: =(E2-C2)/E2. Это маржа; наценка — та же разница, делённая на закупку.
Какую наценку ставить в продуктовом магазине?
Обычно 15–25 % на товары первой необходимости и 30–60 % на сладости, напитки и импульсные товары. Итоговую маржу магазина считайте по выручке и закупке за месяц.
Как сделать стандартную наценку с исключениями?
Как в книге: если у товара наценка не указана, берётся общая. =ЕСЛИ(D2="";Наценка_стандарт;D2) подставляет нужное значение в формулу цены.
Можно ли напечатать ценники из этой таблицы?
Да, через слияние в Word по столбцам «Товар» и «Цена продажи». Готовые ценники по вашему списку соберёт Формулник.