Excel-таблица
«Расчет рентабельности»
Наценка, скидка, рентабельность,
прибыль, минимальная наценка, НДС и др.

версия 1.0 от 28 апреля 2021

С 21.06.2022 входит в состав таблицы Расчеты с процентами

Таблица выполняет:

• Расчет прибыли и рентабельности с учетом наценки, расходов включаемых в себестоимость (РвС), скидки
• Расчет плана продаж в денежном выражении (выручки) и натуральном выражении (в штуках, метрах, килограммах) исходя из плана прибыли
• Расчет влияния скидки на цену, прибыль, рентабельность, план продаж
• Сравнение по рентабельности и прибыли двух вариантов формирования цены с разными наценками, РвС, скидками, операционными и другими расходами
• Расчет минимальных наценок при которых разные виды прибыли равны 0: валовая, операционная, до налогов, чистая, EBITDA
• Дополнительно: Расчет НДС, сумма с НДС, выделение НДС, сумма без НДС
• Дополнительно: Расчеты с простыми и сложными процентами

Предлагаю вам простую и удобную таблицу для расчета рентабельности. Таблица содержит 2 листа пояснений, 4 листа расчетов рентабельности и 2 дополнительных листа расчетов c НДС (ставку можно менять), простыми и сложными процентами.

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

Лист «Абс»

Расчет рентабельности на единицу товара.
РвС, наценка и скидка в абсолютных числах (деньгах).

Расчет прибыли и рентабельности (см. скриншот ниже) с учетом расходов включаемых в себестоимость (РвС), наценок и скидок.

РвС, наценка и скидка задаются на единицу товара в абсолютных числах (в деньгах).
На этом листе под прибылью понимается
валовая прибыль = выручка - себестоимость.

Задаем:

1. Цену закупки
2. Наценку
3. Расходы включаемые в себестоимость (РвС)
4. Скидку
5. План по прибыли

Таблица рассчитывает:

1. Цену продажи
2. Себестоимость
3. Прибыль на единицу товара
4. Валовую рентабельность (маржу, Gross profit margin)
5. Исходя из плана прибыли и рентабельности рассчитывает план продаж по выручке и план продаж в натуральном измерении (штуках, метрах и т.д.)
6. Исходя из плана продаж в натуральном выражении расчитывает план закупок в денежном выражении.

Кроме того, таблица рассчитывает влияние скидки на перечисленные показатели.

Данные вводятся в ячейки с голубым фоном, все остальное рассчитывает таблица.

Видим, что скидка 5% приводит к уменьшению маржи (валовой рентабельности) на 4.47 процентных пункта, что по отношению к первоначальной марже равной 15%, составляет 29.82%. Иными словами, скидка 5% уменьшает рентабельность почти на треть. При новом уровне рентабельности, чтобы выполнить план продаж по прибыли надо увеличить план продаж по выручке на 42.5%, а план продаж в натуральном выражении на 50%.

Можно сказать, что результат неожиданный: делаем скидку 5%, а чтобы обеспечить ту же прибыль, надо продать товара на 50% больше. Хорошо, что мы это выяснили путем расчетов, а не на реальных продажах, понеся убытки. Но чтобы это выяснить, надо сделать много расчетов, которые не все умеют делать, если вы покупаете описываемую таблицу, она делает расчеты за вас.

Лист «Проц»

Расчет рентабельности на единицу товара.
РвС, наценка и скидка в процентах.

Расчеты выполняемые на этом листе аналогичны расчетам листа «Aбс», с тем отличием, что РвС, наценка и скидка задаются в процентах. Иногда удобнее задавать наценки, расходы и скидки в деньгах, а иногда в процентах.

В исходном варианте таблицы заданы значения аналогичные листу «Aбс». Так, на листе «Aбс» заданы цена закупки 1000 и наценка 200 (что соответствует 20%), а на листе «Проц» заданы цена закупки 1000 и наценка 20%, аналогично заданы проценты для РвС и скидки. Соответственно, результаты расчетов на обоих листах одинаковы. Это сделано чтобы показать что оба подхода равноправны и дают одинаковый результат. Вы можете на этих листах вводить разные наценки и скидки.

Лист «Сравнение 2»

Расчет и сравнение рентабельности двух вариантов.

На ранее описанных листах акцент был сделан на скидку, но часто бывает полезно сравнить два варианта с разными исходными данными: закупочными ценами, РвС, наценками и скидками. Этот вариант расчета выполняется на листе «Сравнение 2». Здесь также можно учитывать влияние операционных и других расходов из Отчета о прибылях и убытках (финансовых результатах).

Сделать сравнение двух вариантов может быть полезно во многих случаях. Рассчитаем, например, такой простой и популярный прием: делаем наценку побольше, а потом даем скидку. Т.е. якобы делаем скидку, а на самом деле не делаем, остаемся, как говорят «при своих».

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

Вариант 1: наценка 20%, скидка 0%.
Вариант 2: наценка 25%, скидка 5%.

На первый взгляд кажется, что Вариант 1 равносилен Варианту 2, посмотрим что покажет расчет.

Смотрим самый информативный столбец — столбец относительных изменений (Изменение - %%), видим что в Варианте 2:

• SAL1 (Выручка с учетом скидки) уменьшилась на 1.04%
• GPM (Валовая рентабельность, Gross profit margin) уменьшилась на 5.96%
• OPM (Операционная рентабельность, Operating profit margin) уменьшилась на 20%
• ROS (Рентабельность до налогов, Рентабельность продаж, Return on Sales) уменьшилась на 24.21%
• NPM (Рентабельность по чистой прибыли, Net profit margin) уменьшилась на 30.53%

Самый важный показатель — чистая прибыль уменьшилась на 31.25%.

Расчет показывает, что Вариант 2 приводит к большим потерям.

При подробном взгляде может возникнуть вопрос — почему прибыль уменьшилась сильнее, чем соответствующая рентабельность? Потому что прибыль = выручка * рентабельность. Т.к. выручка тоже уменьшилась, прибыль уменьшилась в большей степени, чем рентабельность.

Сделаем еще одну попытку. Вариант 1 — базовый, Вариант 2 уменьшим скидку.

Вариант 1: наценка 20%, скидка 0%.
Вариант 2: наценка 25%, скидка 4%.

Получилось именно то, что мы хотели. Предварительный расчет рентабельности позволяет принять правильное решение и избежать крупных убытков. Очень полезно иметь инструмент, позволяющий быстро и безошибочно выполнять такие расчеты.

Лист «Min Наценка»

Расчет минимальных наценок для валовой, операционной, чистой и других видов прибыли.

Здесь под минимальной наценкой понимаем такую наценку при которой прибыль равна 0. Так как, согласно ОПУ, есть несколько видов прибыли, таблица вычисляет несколько минимальных наценок.

Лист по своей структуре повторяет Отчет о прибылях и убытках (финансовых результатах).

Видим, что существующая наценка 20% близка к минимальной наценке для cамого важного показателя — чистой прибыли 16%. Иными словами, при существующей наценке и существующем уровне расходов имеем очень маленький запас доходности и небольшие неблагоприятные изменения могут привести к тому, что прибыль сменится убытками. Нужно подумать о повышении наценки и сокращении расходов. Хорошо это выяснить не путем реальных потерь, а путем расчетов.

Посмотрите подробнее в Демо-версии

Как получить таблицу

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

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

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

Я гарантирую:

• Доставка в течение от нескольких минут до нескольких часов после прихода платежа. Сроки прохождения платежа для различных способов оплаты см. ниже.
• Все таблицы заполнены данными, которые служат примером и помогают понять логику работы.
• Таблицы снабжены подробными пояснениями по заполнению и работе.
• Отвечаю на любые вопросы по работе с таблицами. Конечно, предполагается что вы знакомы с предметной областью и имеете базовые навыки работы с Excel.

При выходе новой версии:

• Стоимость обновления зависит от того, насколько увеличивается функциональность новой версии таблицы.
• Сообщения о новых версиях получают только подписчики рассылки. Форма подписки внизу страницы.

Таблица
«Расчет рентабельности»
Стоимость: 595 руб.

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Владимир Прохоров, 28 апреля 2021

P.S.
Если вы хотите получить рабочую версию одной таблицы и демо-версии всех остальных моих таблиц, подпишитесь на ознакомительную серию из 9 писем с кратким описанием таблиц

Посмотрите полный список таблиц

Финансовый анализ. Главная страница