Как посчитать цену продажи зная маржу и себестоимость

Расчет цены в Excel с учетом заданной маржи и себестоимости

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

Что такое маржа

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

1. Маржа – отношение разницы цены и себестоимости к цене.
2. Наценка – отношение разницы цены и себестоимости к себестоимости.

Отсюда и разница.

Расчет цены с учетом маржи и себестоимости

Теперь, зная, чем маржа отличается от наценки, можем вывести формулу цены. Как посчитать цену, зная маржу и себестоимость?

Если М=(Ц-С)/Ц, то Ц=С/(1-М).

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

Составим таблицу для расчетов и предварительно укажем задаваемые параметры (если говорить математическими терминами – заполним дано).

В таблицу вписаны только известные нам пока значения – себестоимости товаров. Задаваемые параметры тоже постоянны. Пойдем по порядку.

Рассчитаем отсрочку по формуле. Умножим себестоимость на банковскую ставку, поделим на число дней в году и на количество дней отсрочки.

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

Теперь можем рассчитать расчетную цену без НДС по экономической формуле.

Ну и цену с НДС (1,18 – это коэффициент НДС).

Далее – валовый доход (разница цены и себестоимости).

Теперь рассчитываем наценку. Она должна получиться одинаковой. В нашем случае формула наценки упрощена, т.к. валовый доход рассчитан операцией ранее.

Наценка действительно получилась одинаковой. Значит, мы на верном пути.

Процент менеджера: 5% от расчетной цены.

Аналогично: ретро-бонус – это 15% от расчетной цены.

Общие затраты: это сумма трех составляющих (проценты менеджеру и оптовику, а также отсрочка).

Маржинальный доход считается как разница валового дохода и затрат.

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

Маржа действительно получилась 40%. Значит все верно. Теперь, пользуясь таблицей, можем изменять значение маржи или себестоимости и сразу видеть все показатели. При этом будет сохранять равенство всех наценок и проверочное значение маржи.

Попробуем изменить маржу на 50% (в задаваемых параметрах). Смотрим, что получается.

Или изменим себестоимость первого товара.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Калькулятор для расчета стоимости (цены) с учетом маржи и себестоимости

Стоимость реализации (выручка или цена конечная потребителю) равна:

Прибыль (валовая прибыль) равна:

Наценка (наценка на себестоимость) составляет:

Как определить стоимость реализации товара или услуги?

Наш портал попытается объяснить все на простых словах.

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

Маржа (%) — разница между ценой и себестоимостью товара и услуги (чаще всего выражается в процентах).

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

Стоимость реализации = Себестоимость / (1 — (Маржа/100)), где

• стоимость реализации в рублях
• себестоимость подставлять в рублях
• маржу подставлять в процентах (%)

Прибыль (руб) (валовая прибыль) — это разница между выручкой и себестоимостью

Прибыль = Стоимость реализации — Себестоимость, где

• прибыль в рублях
• стоимость реализации подставлять в рублях
• себестоимость подставлять в рублях

Наценка (%) — это разница между стоимостью реализации и себестоимостью товаров или услуг, необходимая для покрытия полных затрат и получения прибыли (чаще всего выражается в процентах)

Наценка= (Прибыль/Себестоимость)*100, где

• наценка в процентах (%)
• прибыль подставлять в рублях
• себестоимость подставлять в рублях

Как посчитать маржу и наценку в Excel

Понятие наценки и маржи (в народе еще говорят «зазор») схожи между собой. Их легко спутать. Поэтому сначала четко определимся с разницей между этими двумя важными финансовыми показателями.

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

Формулы расчета маржи и наценки в Excel

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

Формула расчета маржи в Excel

Создайте табличку в Excel, так как показано на рисунке:

В ячейке под словом маржа D2 вводим следующею формулу:

В результате получаем показатель объема маржи, у нас он составил: 33,3%.

Формула расчета наценки в Excel

Переходим курсором на ячейку B2, где должен отобразиться результат вычислений и вводим в нее формулу:

В результате получаем следующий показатель доли наценки: 50% (легко проверить 80+50%=120) .

Разница между маржей и наценкой на примере

Оба эти финансовые показатели состоят из прибыли и расходов. Чем же отличается наценка и маржа? А отличия их весьма существенны!

Эти два финансовых показателя отличаются способом вычисления и результатами в процентном выражении.

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

1. Цена товара = Себестоимость + Наценка.
2. Маржа — является разницей цены и себестоимости.
3. Маржа — это доля прибыли которую содержит цена, поэтому маржа не может быть 100% и более, так как любая цена содержит в себе еще долю себестоимости.

Наценка – это часть цены которую мы прибавили к себестоимости.

Маржа – это часть цены, которая остается после вычета себестоимости.

Для наглядности переведем выше сказанное в формулы:

1. N=(Ct-S)/S*100;
2. M=(Ct-S)/Ct*100.

• N – показатель наценки;
• M – показатель маржи;
• Ct – цена товара;
• S – себестоимость.

Если вычислять эти два показателя числами то: Наценка=Маржа.

А если в процентном соотношении то: Наценка > Маржа.

Обратите внимание, наценка может быть и 20 000%, а уровень маржи никогда не сможет превысить 99,9%. Иначе себестоимость составит = 0р.

Все относительные (в процентах) финансовые показатели позволяют отображать их динамические изменения. Таким образом, отслеживаются изменения показателей в конкретных периодах времени.

Они пропорциональны: чем больше наценка, тем больше маржа и прибыль.

Это дает нам возможность вычислить значения одного показателя, если у нас имеются значения второго. Например, спрогнозировать реальную прибыль (маржу) позволяют показатели наценки. И наоборот. Если цель выйти на определенную прибыль, нужно вычислить, какую устанавливать наценку, которая приведет к желаемому результату.

Пред практикой подытожим:

• для маржи нам нужны показатели суммы продаж и наценки;
• для наценки нам нужна сумма продаж и маржа.

Как посчитать маржу в процентах если знаем наценку?

Для наглядности приведем практический пример. После сбора отчетных данных фирма получила следующие показатели:

1. Объем продаж = 1000
2. Наценка = 60%
3. На основе полученных данных вычисляем себестоимость (1000 — х) / х = 60%

Отсюда х = 1000 / (1 + 60%) = 625

• 1000 — 625 = 375
• 375 / 1000 * 100 = 37,5%

Из этого примера следует алгоритм формулы вычисления маржи для Excel:

Как посчитать наценку в процентах если знаем маржу?

Отчеты о продажах за предыдущий период принесли следующие показатели:

1. Объем продаж = 1000
2. Маржа = 37,5%
3. На основе полученных данных вычисляем себестоимость (1000 — х) / 1000 = 37,5%

• 1000 — 625 = 375
• 375 / 625 * 100 = 60%

Пример алгоритма формулы вычисления наценки для Excel:

Примечание. Для проверки формул нажмите комбинацию клавиш CTRL+~ (клавиша «~» находится перед единичкой) для переключения в соответствующий режим. Для выхода из данного режима, нажмите повторно.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как посчитать цену продажи зная маржу и себестоимость

где ЦП – цена продажи, обеспечивающая Производителю плановую наценку, руб.;

СС – себестоимость, руб.;

ПН – плановая наценка, в долях от 1;

Б1, Б2, …, БN – бонусы, скидки и выплаты от цены продажи, которые делает Производитель клиенту, в долях от 1.

Например, при себестоимости Товара 100 руб., плановой наценке 20%, в случае выплат клиенту ретробонуса 5%, бонуса за покрытие брака – 1%, логистического бонуса – 4%, получим требуемую цену продажи:

Таким образом, чтобы обеспечить себе 20% плановой наценки и при таких условиях договора сделать все указанные выплаты клиенту, Производитель должен будет продать Товар по 133,33 руб.

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

Вот так делать нельзя:

ЦП ≠ СС × (1 + ПН) + Б1% + Б2% + Б3% ≠ 120 + 5% + 1% + 4% ≠ 132,35 руб.

Проверить это легко. Достаточно из полученной вами Цены продажи вычесть все бонусы и выплаты клиенту. В нашем примере:

• 133,33 × 0,05 = 6,67 руб. Это ретробонус – 5%;
• 133,33 × 0,01 = 1,33 руб. Это бонус за покрытие брака – 1%;
• 133,33 × 0,04 = 5,33 руб. Это логистический бонус – 4%.

Итого три вида бонусов дают в сумме: 6,67 + 1,33 +5,33 = 13,33 руб.

Если мы всё правильно рассчитали, вычитая из Цены продажи сумму бонусов и себестоимость, должны получить плановую наценку.

133,33 – 13,33 – 100 = 20 руб.

Получается, данный расчет верен.

1. Рассчитаем цену продажи при предоставлении клиенту бонусов от цены продажи и скидок для проведения акций

Теперь рассмотрим ситуацию, когда Производитель договаривается с сетью (дистрибьютором) о проведении 1 раз в 3 месяца акции с глубокой скидкой, например, в размере 30%, а также о проведении акции 1 раз в квартал со скидкой 20%.

Как правило, 30%-я скидка будет действовать в течение 2-х недель, а 20%-я скидка будет действовать неделю.

Мы хотим рассчитать, какую цену должен предоставить Производитель своему клиенту при условии предоставления скидки 30% и 20%. При этом Производитель должен обеспечить себе плановую наценку.

Для этого нам обязательно нужно знать, какую долю Товара сеть (дистрибьютор) продаст в первой и второй акциях, а какую – в регулярных продажах (без предоставления акционных скидок).

Если взять усредненную текущую ситуацию, то в течении квартала примерно 45% Товара будет продано в период проведения 30%-й акции, 25% Товара продадут в период поведения 20%-й акции, а 30% Товара будут проданы в период отсутствия скидок (период регулярных продаж).

Получить информацию о долях продаж в акциях и в регулярных продажах можно разными способами: от представителя отдела закупок клиента; через EDI (SLSRPT и INVRPT); купить данные о продажах; договориться с клиентом о раскрытии информации о продажах за уступку со своей стороны; профильные форумы, TG-каналы, группы в соцсетях; приобретение отчетов и аналитики; покупка данных у организаций, оказывающих услуги мерчендайзинга; самостоятельные замеры.

Рассчитать цену продажи, при которой Производитель сохранит для себя плановую наценку можно по второй формуле Дубовика.

где ЦП – цена продажи, обеспечивающая Производителю плановую наценку, руб.;

СС – себестоимость Товара, руб.;

ПН – плановая наценка, которую хочет себе обеспечить Производитель, в долях от 1;

ДРП – доля регулярных продаж – процент продаж товара вне периода проведения акций, в долях от 1;

СРП – скидка регулярных продаж – скидка на Товар, предоставляемая вне периода проведения акций, в долях от 1;

ДА1 – доля акции 1 – доля акционных продаж Товара в 1-й акции, в долях от 1;

АС1 – акционная скидка 1 – скидка на Товар, предоставляемая от ЦП в 1-й акции, в долях от 1;

ДАZ – доля акции Z – доля акционных продаж Товара в Z-й акции, в долях от 1;

АСZ – акционная скидка Z – скидка на Товар, предоставляемая от ЦП в Z-й акции, в долях от 1;

Б1, …, БN – бонусы, скидки и выплаты от ЦП, которые делает Производитель клиенту, в долях от 1.

Например, себестоимость Товара 100 руб., плановая наценка Производителя 20%.

Производитель предоставляет клиенту бонус на маркетинговые услуги – 2% и ретробонус – 4%.

Производитель согласовал с сетью (дистрибьютором) проведение акций со скидкой 30% и 20%.

От закупщика сети (дистрибьютора) известно, что до этого похожие акции показывали следующие распределения продаж: 45% Товара предположительно будет продано в период проведения 30%-й акции, 25% Товара продадут в период поведения 20%-й акции, а 30% Товара будут проданы в период регулярных продаж (отсутствия скидок).

Воспользуемся второй формулой Дубовика:

Вернемся к примеру, который я сформулировал в начале этого материала:

Производитель хочет продать свой Товар в розничную сеть (или дистрибьютору).

Себестоимость Товара для Производителя 100 руб.

Задача: рассчитать, по какой цене Производитель должен продать Товар в сеть (дистрибьютору), чтобы обеспечить себе 20% наценки.

Условия сети (дистрибьютора): ретробонус 5%, а также раз в три месяца глубокая скидка 30% для проведения акции.

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

Допустим, проанализировав текущую ситуацию по аналогичной товарной группе, закупщик клиента сообщил, что в течении квартала примерно 65% Товара будет продано в период проведения акции, а 35% Товара продадут в период отсутствия скидок.

Подставляем значения во вторую формулу Дубовика:

где ЦП – цена продажи, обеспечивающая Производителю плановую наценку, руб.;

СС = 100 руб. – себестоимость Товара, руб.;

ПН = 0,2 – плановая наценка 20%, которую себе хочет обеспечить Производитель, в долях от 1

ДРП = 0,35– доля регулярных продаж 35% – процент продаж товара вне периода проведения акций, в долях от 1;

СРП = 0 – скидка регулярных продаж 0% – скидка на Товар, предоставляемая вне периода проведения акций, в долях от 1;

ДА1 = 0,65 – доля акции 1 (65%) – доля акционных продаж Товара в 1-й акции, в долях от 1

АС1 = 0,3 – акционная скидка 1 (30%) – скидка на Товар, предоставляемая от ЦП в 1-й акции, в долях от 1;

Б1 = 0,05 – ретробонус (5%), который выплачивает Производитель клиенту, в долях от 1.

То есть для того, чтобы заработать 20% плановой наценки, в случае приведенных условий предоставления ретробонуса и акционной скидки, производитель должен продавать клиенту по 156,91 руб. товар с себестоимостью 100 руб.

Проверим это значение:

35% Товара будет продано по цене 156,91 руб. За вычетом ретробонуса 5% Производитель получит выручку 149,06 руб. Наценка составит 49,06 руб.

65% Товара будет продано по цене 109,84 руб. За вычетом ретробонуса 5% Производитель получит выручку 104,35 руб. Наценка составит 4,35 руб.

0,35×49,06 + 0,65×4,35 = 20 руб.

Расчет сделан верно.

Итого, Производитель должен зарабатывать 49% наценки в регулярных продажах и 4,35% наценки в акции, чтобы в приведенных условиях обеспечить себе суммарную плановую наценку 20%.

1. Важные выводы для покупателей в магазинах

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

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

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

≡ Законы экономики неумолимы. Никто не будет работать себе в убыток.

Нужна помощь в организации вашего ценообразования и системы скидок? Свяжитесь со мной.