Расчет цены в Excel с учетом заданной маржи и себестоимости
Зачастую расчеты, которые легко произвести на бумаге и калькуляторе, вызывают затруднения при попытке занести их в Excel. Но если научиться это делать, то в будущем можно сэкономить массу времени. Узнаем, как рассчитать цену продукта с учетом заданной маржи.
Что такое маржа
Фактически, это синоним слова наценка, выраженный в денежном эквиваленте. Если же речь идет о процентах, то маржа и наценка будут иметь разные значения. Все дело в определении. Сравните:
Знаменатели в формуле расчета маржи.
- Маржа – отношение разницы цены и себестоимости к цене.
- Наценка – отношение разницы цены и себестоимости к себестоимости.
Отсюда и разница.
Расчет цены с учетом маржи и себестоимости
Теперь, зная, чем маржа отличается от наценки, можем вывести формулу цены. Как посчитать цену, зная маржу и себестоимость?
Если М=(Ц-С)/Ц, то Ц=С/(1-М).
В принципе, все просто. Нужно забить в Excel значения маржи и себестоимости, а затем вписать формулу цены. Но это формула для одного случая, а в экономике затраты, от которых и зависит маржа, постоянно меняются. Поэтому подготовим полный расчет с учетом остальных переменных.
Составим таблицу для расчетов и предварительно укажем задаваемые параметры (если говорить математическими терминами – заполним дано).
В таблицу вписаны только известные нам пока значения – себестоимости товаров. Задаваемые параметры тоже постоянны. Пойдем по порядку.
Рассчитаем отсрочку по формуле. Умножим себестоимость на банковскую ставку, поделим на число дней в году и на количество дней отсрочки.
Видим, что ячейки, в которых прописаны ставка и отсрочка, абсолютны, поэтому заключены в значки доллара. Это сделано для того, чтобы при протягивании формулы, эти значения оставались неизменными.
Теперь можем рассчитать расчетную цену без НДС по экономической формуле.
Ну и цену с НДС (1,18 – это коэффициент НДС).
Далее – валовый доход (разница цены и себестоимости).
Теперь рассчитываем наценку. Она должна получиться одинаковой. В нашем случае формула наценки упрощена, т.к. валовый доход рассчитан операцией ранее.
Наценка действительно получилась одинаковой. Значит, мы на верном пути.
Процент менеджера: 5% от расчетной цены.
Аналогично: ретро-бонус – это 15% от расчетной цены.
Общие затраты: это сумма трех составляющих (проценты менеджеру и оптовику, а также отсрочка).
Маржинальный доход считается как разница валового дохода и затрат.
Готово. Осталось проверить, правильно ли были произведены расчеты. Для этого подсчитаем маржу по еще одной формуле: как отношение маржинального дохода к цене.
Маржа действительно получилась 40%. Значит все верно. Теперь, пользуясь таблицей, можем изменять значение маржи или себестоимости и сразу видеть все показатели. При этом будет сохранять равенство всех наценок и проверочное значение маржи.
Попробуем изменить маржу на 50% (в задаваемых параметрах). Смотрим, что получается.
Или изменим себестоимость первого товара.
Как видим, все показатели автоматически изменяются, а экономические принципы сохраняются. Аналогично можно изменять и другие задаваемые параметры.