Как посчитать маржу в эксель
Как умножить число на процент и прибавить проценты
Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения.
Сколько составляет процент от числа.
Предположим, вы покупаете новый телевизор за 800 долларов, и за доставку с вас просят 4% от стоимости покупки. Вопрос в том, сколько вы должны заплатить сверх первоначальной цены? Другими словами, что составляет 4% от 800 долларов?
Поможет следующая формула:
Помните, что когда вы записываете в таблицу число, за которым следует знак процента (%), то оно интерпретируется Excel как сотая часть его значения (то есть, сразу делится на 100). Например, 4% фактически сохраняется как 0,04, и Excel использует это значение во всех формулах и расчетах.
Другими словами, формула =A2*11% эквивалентна =A2*0.11. Естественно, вы можете использовать десятичное число, соответствующее процентному соотношению, непосредственно в формуле, если это лучше подходит для ваших таблиц.
Как умножить число на процент.
Часто бывает необходимо умножить число какой-то процент. К примеру, речь идет об изменении текущей цены через какой-то промежуток времени (например, через месяц), чтобы добавить к ней определенный процент.
Чтобы увеличить число на определённый процент, используйте эту формулу:
Например, формула =A1*(1+20%) увеличивает значение в ячейке A1 на 20%.
Чтобы уменьшить на процент:
Например, формула =A1*(1-20%) уменьшает значение в ячейке A1 на 20%.
Умножить на процент: =A2*(1+B2)
Уменьшить на процент: =A2*(1-B2)
Кроме того, как вы видите на рисунке выше, процент может быть выражен и отрицательным числом, что означает уменьшение первоначального значения. В этом случае формула =A2*(1+B2) также отлично подходит.
Как увеличить или уменьшить весь столбец на процент.
Предположим, у вас есть столбец чисел, который вы хотите увеличить или уменьшить на определенный процент. Вы также хотите, чтобы обновленные числа были в том же столбце, а не добавлять новый столбец с формулами.
Вот пошаговая инструкция для решения этой задачи:
Число увеличиваем на процент: =1 + 40%
Естественно, вы можете заменить 40% в приведенных выше формулах любым нужным процентом.
И вот результат – все выбранные числа в столбце А умножены на 40%.
Таким же образом вы можете разделить столбец чисел на определенный процент. Просто введите нужное число в пустую ячейку и следуйте инструкциям выше. В Специальной вставке вместо «умножить» выберите «разделить».
Расчет первоначального значения с учетом процента изменения.
Например, вам предложили купить пылесос за 200 долларов, что на 40% ниже первоначальной цены. Вы хотите узнать, какова была первоначальная цена.
Формула выглядит следующим образом:
Применительно к скидке от первоначальной цены в 40% можно применять любую из следующих формул:
=A2/(100%-B2) или =A2/(1-B2) или =A2/0.6 или =A2/60%
Как посчитать наценку в процентах.
Предположим, товар стоит 100 рублей. Мы знаем также, что на него была сделана наценка 10 рублей. Как посчитать эту наценку в процентах?
Ответ «10 процентов» будет неверным. В данном случае нельзя просто взять процент от итоговой цены. Ведь наценку мы делаем на первоначальную цену без наценки.
Принимаем итоговую цену за 100%. Тогда первоначальная цена будет составлять на 10% меньше, то есть 90%. В итоге формула расчета процента наценки будет такая:
А теперь запишем это на листе Excel:
Здесь у нас нет необходимости умножать на 100%, а просто применяем к ячейке C2 процентный формат:
Вы можете проверить расчёт: добавьте к 90 рублям 11,1% и получите как раз 100.
Как посчитать маржу в процентах.
Прежде всего, давайте определим разницу между понятиями наценки и маржи.
По абсолютному значению (в денежных единицах) они одинаковы. Рассчитываются они как разность между себестоимостью (или закупочной ценой) и ценой реализации товара.
А вот в процентах они совершенно разные.
Наценка всегда рассчитывается в процентах к себестоимости или цене закупки товара. Размер ее может быть от нуля до бесконечности (хоть 1000% накручивай, если купят).
%наценки = (цена – себестоимость)/себестоимость
Маржа показывает долю дохода в цене. Поэтому она должна быть больше 0, и всегда меньше 100%.
Как видите, уровень процентов наценки и маржи могут очень сильно отличаться.
Но при этом они связаны. Поэтому, к примеру, если мы знаем процент наценки, то легко посчитать маржу.
Если мы продали товара на 1000 рублей и применяли при этом наценку в 30%, то отсюда мы сначала можем найти себестоимость товара:
=сумма продаж / (100%+%наценки)
1000 – 769,23 = 230,77
Маржа в процентах: 230,77/1000 = 23,08%
А теперь как посчитать наценку, если знаем маржу? Используем те же цифры.
Отсюда себестоимость 1000 – 230,77 = 769,23 руб.
Процент наценки 230,77/769,23 = 30%
Эти несколько примеров применения процентов, надеюсь, будут полезны. Если у вас есть вопросы – пишите в комментариях.
Посчитать маржинальность в экселе
Чтобы отслеживать, что происходит с бизнесом, нужно считать маржинальность — разницу между себестоимостью товара и выручкой. В целом, чем больше эта разница, тем выше маржинальность, а высокая маржинальность — это высокая прибыль и успешный бизнес.
Как посчитать
В таблице мы собрали формулу расчета маржинальности. Ее можно использовать, чтобы посчитать маржинальность одного товара или бизнеса в целом. Для примера мы заполнили ее простыми цифрами:
Магазин покупает носки по 100 рублей, а продает по 300 рублей. За месяц он продал 200 пар и заработал 40 000 рублей. Маржинальность — 67%.
Вот как это выглядит:
Мы взяли упрощенный пример, чтобы показать, как в целом считают маржинальность. В жизни всё сложнее: к закупочной цене носков прибавятся затраты на доставку, хранение на складе, зарплаты сотрудникам, и такой высокой маржинальности уже не будет.
Чтобы правильно рассчитать маржинальность, в себестоимости товара важно учитывать все расходы на его закупку или производство, доставку, хранение и непосредственную продажу.
Для расчета маржинальности вам нужно заполнить цифры, которые выделены синим. Забиваете данные по своей компании, и получается маржинальность в деньгах и процентах:
Дальше разберем, что с этим знанием делать.
Есть некоторая путаница между маржинальностью, маржой и маржинальной прибылью. В целом маржа и маржинальность — это одно и то же, эти показатели считают в рублях или процентах.
Магазин продает носки по 300 рублей, а покупает у поставщиков по 100, маржа — 200 рублей. В процентах это 67%.
Маржинальная прибыль — это то же самое, но только маржу обычно считают на единицу товара, а маржинальную прибыль с выручки за период, например за месяц.
Магазин носков продал за месяц 100 пар, маржа пары так и осталась 200 рублей, а вот маржинальная прибыль стала 20 000 рублей. В процентах это всё те же 67%.
Предприниматели используют разные термины и формулы, но обычно для расчета маржинальности берут стоимость товара или выручку и делят на себестоимость.
Что делать с результатами
Маржинальность показывает, какую долю в выручке занимает прибыль. Например, маржинальность 10% означает, что 90% денег потратили, чтобы получить эти самые 10%.
Самая большая маржинальность в 100% возникает, если на товар совсем нет расходов, но так не бывает. Еще маржинальность может быть отрицательной, если расходы на товар превышают выручку.
Маржинальность считают, чтобы оценить эффективность компании в целом, но еще этот показатель помогает понять, какие товары приносят больше денег.
Компания производит и продает ламинат и паркет. Выручка от продажи паркета — 200 000 рублей в месяц, а от продажи ламината — 150 000 рублей. Компания решает, что раз паркет приносит больше денег, выгоднее производить его, и начинает больше вкладывать в производство и рекламу паркета.
Если же посчитать маржинальность, окажется, например, что у паркета она 25%, а у ламината 47%. И даже если пока ламинат приносит меньше денег, выгоднее продавать его: доля прибыли в выручке от ламината выше. Компания может расширить ассортимент ламината, разместить рекламу и придумать акции, чтобы привлечь интерес к товару.
Еще маржинальность можно считать каждый месяц и сравнивать с предыдущим. Например, компания увеличила наценку на товар, а маржинальность не изменилась — в этом случае нужно смотреть, почему растут расходы на товар. Возможно, сырье подорожало сильнее, чем казалось сначала, и нужно сменить поставщика.
Читать в Деле
Счет для ИП и ООО в Модульбанке
Удобный сервис, недорогие тарифы, защита от блокировок по 115ФЗ
Расчет наценки и маржи средствами Excel
Excel – это отличный бизнес-инструмент, который можно использовать для разных целей. В том числе, для ценообразования. Допустим, у нас есть определенная норма, сколько накручивать на одной цене, а также есть себестоимость каждого товара. Если есть один товар, то это можно сделать вручную. Но что делать, когда нужно рассчитать маржу сразу большого количества товаров?
И вообще, есть разница между наценкой и маржой? Давайте разберемся с этими ключевыми понятиями, а также определим, как их рассчитывать.
Расчет маржи и наценки в Excel: формулы
Итак, у нас есть два показателя, каждый из которых нам известен. Первый – это цена. Это окончательная стоимость товара, за сколько его нужно продавать или он сейчас продается. Маржа является очень важным параметром, который дает возможность определить множество других характеристик проекта или бизнеса, таких как:
В зависимости от этих результатов и принимаются управленческие решения. Например, осознается необходимость уменьшить расходы, увеличить стоимость товаров или услуг или же отказаться от проектов, которые требовательны к ресурсам, но не приносят целевой прибыли.
Что нужно сделать для того, чтобы определить наценку и маржу?
Формула расчета маржи
Давайте сделаем такую табличку.
В нашем случае нас интересует ячейка, подписанная, как «Маржа». То есть, D2. В ней следует указать такую формулу (показана на скриншоте).
2
Таким образом, маржа у нас стала составлять 33,3%. Простыми словами, треть.
Средневзвешенная маржа от продаж
Если же нам нужно определить среднюю маржу, недостаточно использовать предыдущую формулу, поскольку данные будут неточными. Значительно лучше применять средневзвешенное значение, которое учитывает весовой коэффициент. В нашем случае это объемы продаж.
Есть три способа определения средневзвешенной маржи.
Настоятельно рекомендуется потренироваться в вычислениях перед тем, как использовать эти формулы в работе. Это поможет существенно сэкономить рабочее время.
Формула расчета наценки
Возвращаемся к той таблице, которая нами была создана изначально. Наценка у нас находится в ячейке B2. Следовательно, там вводим такую формулу.
6
В качестве результата формула выводит наценку в 50%. Если посчитать этот показатель вручную, мы получим ту же долю от числа.
В чем разница между маржей и наценкой (пример)
Очень часто люди путают понятия маржи и наценки, поскольку и те, и другие основаны на прибыли и расходах. При этом разница между этими терминами колоссальная. Они просто по разному рассчитываются, как видно из формул, которые были приведены выше.
С помощью наценки предприятия могут получить прибыль с произведенного товара. Если бы ее не было, то торговля стала бы убыточной. А маржой является окончательный результат, который получился после того, как была выполнена наценка. Чтобы было более понятно, давайте приведем формулы, которые описывают каждое из понятий, касающихся рассматриваемой нами темы.
Маржа может быть равна наценке при условии, что вычисление осуществляется в абсолютных показателях (то бишь, числами). Если же определять процент, то наценка всегда будет больше маржи.
Вот таблица, которая демонстрирует эту разницу на примере.
7
Наценка может и превышать 100%, поскольку она рассчитывается, как доля от себестоимости. То есть, если человек добавляет к стоимости сумму в 20 раз больше, чем реально его произвел, то наценка составит 20 тысяч процентов. При этом маржа не может быть больше, чем 99,9999(…).
При этом маржа будет равной наценке, если измерять ее в количестве денег, которые остаются после наценки, исключая себестоимость.
При этом маржа изменяется пропорционально наценке. Чем она больше, тем выше маржа. Поэтому если мы знаем наценку, то можно определить маржу и наоборот. Если перед человеком стоит задача получить конкретную чистую прибыль, то следует определить, какая нужна наценка. И это можно сделать. Простыми словами, чтобы получить маржу, необходимо знать сумму продаж и наценку, а чтобы получить наценку, необходимо иметь сумму продаж и маржу.
Отдельно выделяется такое понятие – как маржинальность. Это фактически мера того, насколько рентабельный бизнес. Чем выше маржинальность, тем выгоднее заниматься определенным бизнесом.
Как это понятие рассматривать на практике? Выражаясь простым языком, маржой является доля прибыли на один чек, выписанный клиенту или покупателю. Предположим, мы рекламируем какой-то проект. Чтобы это сделать, нам понадобилось заплатить 10 тысяч на привлечение одного клиента + операционные затраты на предоставление услуги. В этом случае маржа составила 5 тысяч рублей. В процентном отношении в этом примере маржа составит 33 процента.
Между понятиями маржинальности и рентабельности можно смело ставить знак равно, поскольку эти термины абсолютно идентичны по своему смыслу.
Маржинальность – это не только категория бизнеса, но и маркетинга. В этой сфере под этим термином подразумевается прибыльность конкретного продвигаемого проекта.
Какой уровень маржинальности считать хорошим? Если этот показатель выше 10 процентов, то это считается нормой. Если выше 20-25 процентов, то это говорит о бизнесе, как об очень выгодном. Важно понимать, что маржа на старте бизнеса будет ниже, потому что он не сразу приносит доход. Поэтому нужно регулярно сравнивать этот показатель за разные периоды (что, впрочем, важно и для давно работающего бизнеса). Некоторые компании вообще через некоторое время выходят на маржинальность в 51 процент, что является просто потрясающим показателем.
Кстати, согласно опросам, проведенным маркетинговой платформой HubSpot, 15% вообще не знает, какая маржинальность их бизнеса. Если вы принадлежите к этому числу, то теперь вы знаете, как это сделать в Excel.
Есть еще такой параметр, который называется валовой маржой. Это количество средств, которые остаются свободными после вычета издержек, не связанных непосредственно с конкретным проектом. Это также термин из маркетинга. То есть, в валовую маржу не входят такие расходы, как аренда, зарплаты, аренда, покупка материалов, канцелярии, техники и так далее. Оптимальным значением валовой маржи считается 50-60%.
Чтобы рассчитать этот показатель, необходимо вычесть из дохода те средства, которые были потрачены непосредственно на обеспечение конкретной деятельности. Это можно сделать абсолютно без никаких функций Excel, достаточно только иметь необходимые показатели.
Существует еще несколько видов маржи:
Чтобы рассчитать маржинальность на практике, необходимо следовать такому простому алгоритму:
Все это можно сделать в Excel. Но это уже более продвинутый уровень. Главное – понимать принцип, как это делать, а все остальное – дело техники.
Расчет маржи в процентах, если известна наценка
Чтобы было более понятно, давайте приведем реальный кейс. Предположим, у нас есть организация, которая вышла на такие показатели за отчетный период:
На основе этих данных можно определить маржу. Математические операции следующие:
По аналогии строится расчет маржи при известной наценке и во всех остальных ситуациях.
8
Расчет наценки в процентах, если известна маржа
Теперь представим другой кейс. У нас отчетные показатели следующие:
Далее стандартными математическими операциями получаем неизвестное из уравнения. В качестве него выступает 625. Теперь, когда мы знаем себестоимость, мы можем определить наценку. Это делается в два простых шага, которые мы выучили еще в школе:
Таким образом, рассчитать маржу и наценку в Excel не составляет никакого труда. Хотя эти значения могут быть идентичными в абсолютном выражении, в относительном они имеют огромную разницу, поскольку наценка рассчитывается от себестоимости, а маржа – от общей суммы товара.
В любом случае, оба эти показателя чрезвычайно важны для ведения любого бизнеса, создания социального проекта и ведения другой деятельности, в которой приходится рассчитывать эти показатели и ориентироваться на них.
Наиболее часто используемые формулы в Excel: процент и маржа
Примеры, описанные в этом уроке, доступны в файле Excel: Часто Используемые Формулы.xlsx, только их самостоятельное написание гарантирует, что вы запомните этот урок.
Самые популярные формулы в Excel: расчет процентов и маржи
Зачастую знание самого Excelя оказывается недостаточным и необходимо знание основных математических формул.
Ниже я представляю решение проблем, с которыми чаще всего сталкивается обычный сотрудник или кандидат на должность, где требуется знание Excel.
Формулы расчета процентов в Excel
Процентное увеличение вычисляем по следующей формуле:
Эта формула использовалась в следующем примере для расчета процентного увеличения, которое составило 25%.
Ту же формулу также можно написать в следующей форме:
Прибавление процента = (новая стоимость – старая стоимость) / старая стоимость
Иногда бывает, что процентное изменение за год рассчитывается «с другой стороны», в приведенном ниже примере мы можем сказать, что продажи в 2013 году были на 20% меньше, чем в 2014 году. Получатели такой информации недолго думая запоминают, что разница составляет 20%, тогда как на самом деле, как мы рассчитали в примере 1 (этот пример и предыдущий имеют одни и те же данные), она составляет 25%.
Это является преднамеренным введением в заблуждение получателя информации, и я не рекомендую использовать такой подход, разве что кто-то занимается политикой и должен придерживаться принятых в этой сфере стандартов.
Чтобы найти процентное отношение, например, какого-то товара в общем объеме продаж, мы делим объем продажи этого товара на общий объем продаж.
Процентное отношение = продажи товара A / общий объем продаж
Для увеличения заданного значения на процент, например, для увеличения цены на 23% налога на добавленную стоимость, служит следующая формула:
Стоимость с НДС = стоимость без учета НДС * (1 + процент изменения)
Аналогично выглядит формула уменьшения значения на процент, с той лишь разницей, что вместо знака плюс используется минус:
Новое значение = Старое значение * (1 + проценты изменения)
В следующем примере мы бы хотели бы найти цену без НДС от цены с НДС, НДС составляет 23 %.
Стоимость без НДС = Стоимость с НДС / (1 + % НДС)
Как вы можете видеть, в приведенном примере нам удалось получить первоначальное значение цены без НДС, показанного на двух примерах выше.
Многие люди задают вопрос, почему обратным действием для увеличения значения на какой-то процент не является уменьшение на тот же процент.
Давайте рассмотрим следующий пример, в котором цена была изменена дважды.
Начальная цена составляла 100$, она была увеличена на 10%, а после этого изменения она составляла 110$ (10% от 100 это 10, 10 + 100 = 110).
Как видно, цена не вернулась к своему первоначальному значению. Чем больше было бы процентное изменение, тем больше была ба разница между начальной и окончательной ценой.
В приведенном ниже примере стоимость какой-то инвестиций увеличивается на 10% каждый год, мы хотели бы рассчитать, за сколько лет эта величина удвоится.
В первой из зеленых ячеек вводим формулу увеличения на процент и перетаскиваем ее в следующие ячейки. Удвоения этой суммы мы не должны ждать аж десять лет. Величина будет в два раза больше первоначальной суммы в седьмом году, а в восьмом значительно превысит свою двукратность. Происходит так, потому что процент во втором и каждом последующем инвестиционном году рассчитывается не из первоначальной суммы, а из уже увеличенной суммы.
Тот же результат можно получить гораздо быстрее, используя формулу для сложных процентов.
Окончательная сумма = начальная сумма * (1 + процентная ставка) ^ количество периодов
символ ^ означает степень)
Такой рост величины депозита в последующие периоды типичен для экспоненциальных функций, то есть тех, в которых переменная, в данном случае количество лет, находится в степени.
Формулы расчета маржи в Excel
Вычисление маржи для многих людей является большой проблемой, потому что они думают о добавлении маржи как о добавлении процента к заданной величине.
Фактически, маржа — это не процент от «накладных расходов», а процент, которым является прибыль в конечной цене продукта или услуги.
При данной стоимости и проценте маржи, цена рассчитывается по следующей формуле:
Цена = Стоимость / (1- процент маржи)
Маржа должна быть менее 100%, потому что невозможно продать что-то, зарабатывая при этом 100% и более, при этом каждая хозяйственная деятельность связана с некоторыми издержками.
В то же время, маржа может быть отрицательной, тогда компания продает свои товары или услуги ниже себестоимости и теряет на каждой операции.
Вопреки тому как может казаться, это не редкое явление, в некоторых отраслях, например, при продаже принтеров нормальным явлениям является их продажа ниже себестоимости. Производители покрывают эти потери продавая услуги и картриджи (чернила) с высокой маржей. Такая стратегия используется для упрощения привлечения клиентов.
Чтобы рассчитать маржу, зная стоимость и цену, используем следующую формулу:
Процент маржи = (цена – стоимость) / цена
Однако, если искомым элементом является стоимость, мы используем следующую формулу, такую же, которую мы используем для уменьшения суммы на процент.
Другими словами, мы понижаем цену на размер маржи и получаем стоимость.
Средневзвешенная маржа от продаж
Для вычисления средней маржи мы не можем использовать обычное среднее значение, мы должны вычислить его с использованием средневзвешенного значения, где весом являются объёмы продаж.
Мы можем сделать это тремя способами: