Описание таблицы Excel
«Расчеты с процентами»

версия 1.0 от 21 июня 2022

Расчет рентабельности продаж с учетом:

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

Расчет минимальных наценок (при которых прибыль равна 0) для разных видов прибыли:

  • Валовой
  • Операционной (от основной деятельности)
  • До процентов и налогов (EBIT)
  • До вычета налогов (EBT)
  • Чистой прибыли
  • EBITDA (до процентов и налогов и амортизации)

Расчет кредитов с тремя видами платежей:

  • Дифференцированными. Фиксированная сумма гашения кредита + Процент на остаток
  • Аннуитетными. Фиксированная сумма выплаты с постепенно растущей частью идущей на гашение долга
  • По кредитной карте. Фиксированный процент от остатка долга на гашение + Процент на остаток

Сравнение кредитов с разными условиями

Контроль кредитной карты

  • Контроль фактических операций и остатков по кредитной карте.

Расчеты с НДС:

  • Расчет НДС, сумма с НДС, сумма без НДС, выделение НДС из общей суммы

Простые и сложные проценты

Как получить Демо-версию

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

Таблица состоит из 19 листов: 4 листа пояснений и 15 листов с расчетами.

Листы расчетов простых и сложных процентов, НДС, на мой взгляд, в пояснениях не нуждаются и здесь не показаны, их можно посмотреть в демо-версии.

Об авторе

В. Прохоров
Меня зовут Владимир Прохоров, я занимаюсь экономическими расчетами более 20 лет. С 1992 по 2008 год работал программистом и начальником отдела IT в банках и коммерческих организациях. Имею большой опыт в разработке программного обеспечения для банковских систем и бухгалтерии. С 2008 года разрабатываю Excel-таблицы экономической и управленческой тематики.
ИП c 2011 года, ОГРНИП 311645410900040.
Со мной можно связаться по почте v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru), по телефону +7 927 055 9473, ВКонтакте vk.com/vvprokhorov

Расчеты рентабельности

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

Лист «Абс»

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

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

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

Задаем:

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

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

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

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

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

Расчет рентабельности в Excel абсолютные

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

Как получить Демо-версию

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

Лист «Проц»

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

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

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

Расчет рентабельности в Excel проценты

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

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

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

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

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

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

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

Расчет и сравнение рентабельности в Excel Пример 1

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

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

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

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

Как получить Демо-версию

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

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

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

Расчет и сравнение рентабельности в Excel Пример 2

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

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

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

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

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

Расчет Минимальной наценки в Excel

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

Как получить Демо-версию

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

Листы с расчетами кредитов

Ячейки для ввода отмечены светло-голубым фоном.

Лист «Диф Подробно»

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

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

Расчет эффективной процентной ставки.

Расчет полной стоимости кредита согласно 353-ФЗ от 21.12.2013 (ред. от 03.04.2020).

Условия кредита и расчет итоговых показателей:

расчет дифференцированных платежей по кредиту

Помесячный расчет:

расчет дифференцированных платежей по кредиту помесячно

Дополнительное гашение в сумме 10 000 сокращает срок выплаты кредита на 3 месяца.

Как получить Демо-версию

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

Лист «Анн Подробно»

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

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

Расчет эффективной процентной ставки.

Расчет полной стоимости кредита согласно 353-ФЗ от 21.12.2013 (ред. от 03.04.2020).

Условия кредита и расчет итоговых показателей:

расчет аннуитетных платежей по кредиту

Помесячный расчет:

расчет аннуитетных платежей по кредиту помесячно

Дополнительное гашение в сумме 10 000 сокращает срок выплаты кредита на 4 месяца.

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

Как получить Демо-версию

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

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

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

Случай 1.
Сравнение кредитов А и Б
расчет и сравнение аннуитетных платежей по кредиту

Случай 2.
Приведение кредита к простому виду. Видим, что Кредит 1 (сумма кредита 50'000, 22 процента годовых, 5000 страховка) по сути дела является Кредитом 2 (сумма кредита 45'000, 29.97% годовых, без страховки).
расчет и сравнение аннуитетных платежей по кредиту приведение к простому виду

Случай 3.
Подбор суммы кредита по сумме жалаемого ежемесячного платежа.
расчет и сравнение аннуитетных платежей по кредиту подбор по сумме платежа

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

Лист содержит подробные пояснения.

Как получить Демо-версию

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

Лист «Кред Карта»

Лист предназначен для подробного (помесячного) расчета платежей по кредитной карте с учетом дополнительных платежей (досрочных гашений).

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

Вариант 1. Платим только минимальный платеж. Долг будет погашен более чем через 6 лет. Переплата составит более 46'000. В исходном состоянии таблица содержит 72 строки (месяца), что составляет 6 лет. При необходимости строки можно добавить.

расчет платежей по кредитной карте

Вариант 2. Платим ежемесячно 7000. Долг будет погашен через 17 месяцев. Переплата составит 18'620.

расчет платежей по кредитной карте вариант 2

Как получить Демо-версию

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

Лист «Контроль Кред Карты»

Лист предназначен для учета фактических гашений и заимствований по кредитной карте.

учет платежей по кредитной карте

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



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

Ссылка на скачивание будет отправлена по электронной почте. Стоимость таблицы «Расчеты с процентами» 595 руб., возможна оплата в рассрочку без переплаты четырьмя равными частями по 148.75 руб. Вы получаете таблицу сразу после первой оплаты 148.75 руб. Вторая и последующие оплаты будут списаны каждая через 14 дней, таким образом, общий срок рассрочки составляет 42 дня.

Как оплатить >>>

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

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



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

Видео инструкция по оформлению заказа и оплате  
 Откроется в новом окне 

Телефон для вопросов оплаты и доставки +7 927 055 9473
почта v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru)


Таблица
«Расчеты с процентами»

Стоимость: 595 руб. (или в рассрочку 4 платежа по 148.75 руб.)

Таблицы работают в Excel для Windows и Mac. В ОpenOffice не работают.

Оформить заказ

Оформление заказа, оплата и получение таблиц

  1. Нажать зеленую кнопку, соответствующую нужному варианты таблиц (если вариантов несколько).
  2. В новой вкладке откроется страница оформления заказа.
  3. Ввести код скидки (если он есть).
  4. Ввести адрес своей электронной почты. Нажать кнопку [Оформить заказ]
  5. Откроется страница выбора способа оплаты. Формально способов оплаты три:
    • Robokassa.
    • Юр. лицам через банк. При оформлении заказа получите счет.
    • Физ. лицам через банк. При оформлении заказа получите квитанцию по форме ПД-4.

    Фактически, способов оплаты два: Robokassa и банк. Подробнее о способах оплаты см. ниже.

  6. Выбрать удобный способ и оплатить.

Способы оплаты:

  1. Robokassa. Сервис приема платежей в интернете. Можно оплачивать используя компьютер (телефон) или через терминалы приема платежей и салоны связи. Возможна оплата в рассрочку без переплаты четырьмя равными частями. Этот вид оплаты называется «Поделить на 4 платежа». Вы получаете таблицу сразу после первой оплаты. Вторая и последующие оплаты будут списаны каждая через 14 дней, таким образом, общий срок рассрочки составляет 42 дня.
    • Карты Visa, MasterCard, Maestro, Мир. В том числе карты иностранных банков.
    • Интернет-клиент Альфа-Банк, Русский Стандарт, Промсвязьбанк и др.
    • Электронные деньги ЮMoney (Яндекс.Деньги), QIWI, WebMoney, Wallet One (W1), Элекснет
    • Терминалы
    • Салоны связи Евросеть, Связной

    Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».

  2. Банк. Можно оплачивать с расчетного счета организации, через Сбербанк Онлайн, сервисы интернет-клиент других банков или через кассу любого банка.
    На расчетный счет 40802810556000029171
    Индивидуальный предприниматель Прохоров Владимир Викторович,
    ИНН 645400330452,
    КПП 0  (у ИП нет реквизита КПП, если требуется ввести — вводите 0),
    ПОВОЛЖСКИЙ БАНК ПАО СБЕРБАНК,
    кор.сч. 30101810200000000607,
    БИК 043601607.
    Обратите внимание, что указание формы собственнсти (Индивидуальный предприниматель) является обязательным.

    После оформления заказа можно распечатать счет для юр. лиц
    или квитанцию по форме ПД-4 для физ. лиц.

    Также можно получить счет, написав на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru).

    В назначении платежа обязательно указывайте номер счета, например:
    По счету № ...  За электронные таблицы «Расчеты с процентами»
    . Без НДС.


    При платежах не из России в начале назначения платежа укажите код валютной операции {VO10100}
    Например, назначение платежа:
    {VO10100} По счету № ...  За электронные таблицы «Расчеты с процентами»
    . Без НДС.

    Где VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.

Доставка:

Cсылка на скачивание таблиц будет отправлена на указанный в заказе e-Mail после поступления денег. Придет письмо с темой «Вы оплатили счет №…». Если его долго нет, проверьте папку СПАМ.

Для способа 1 (Robokassa) письмо со ссылкой отправляется автоматически, сразу после поступления денег. Обычное время поступления денег — минуты. Сервис Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».

Для способа 2 (Банк) письмо со ссылкой отправляется после поступления платежа на расчетный счет. Время поступления денег от нескольких часов до 2 рабочих дней, обычно, в пределах рабочего дня. Юридическим лицам и ИП высылаю pdf-копии накладной по форме Торг 12. Оригинал накладной высылается по запросу, в запросе укажите, пожалуйста, почтовый адрес.

Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ.

Дальнейшая переписка по поводу полученных таблиц

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

Оформить заказ

Владимир Прохоров, 21 июня 2022

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

Вы получаете:
  1. Рабочие версии двух таблиц: Список покупок и Чек лист — контрольный список. Ссылка на скачивание в первом письме.
  2. Демо-версии всех моих таблиц: Платежный календарь, Расчет себестоимости, Расчет инвестиционных проектов, Фин.анализ МСФО, Фин.анализ РСБУ, Точка безубыточности, Анализ ассортимента, Оценка стоимости бизнеса, Диаграмма Ганта, Личное планирование. Ссылка на скачивание в первом письме.
  3. Скидку 40% (4706 руб.) на комплект из пяти моих таблиц. Как получить скидку — во втором письме.
  4. Скидку порядка 20% на отдельные таблицы. Как получить скидку — в письме с описанием конкретной таблицы.
  5. Скидку 20% на новые таблицы. Срок действия скидки трое суток с момента отправки письма о новой таблице.
  6. Сообщения о новых таблицах.
  7. Сообщения о новых версиях таблиц.
  8. Сообщения об акциях скидок. Обычно срок действия скидки трое суток с момента отправки письма о скидке.
  9. Сообщения об исправленных вариантах при обнаружении ошибок.

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

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

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

Вводите адрес способом копирования/вставки. При ручном вводе велика вероятность ошибки. Я это вижу по адресам подписчиков ***@gmai.com, ***@gmail.ru, ***@mall.ru и подобным. Сколько опечаток делают в той части адреса, которая стоит до @, трудно представить.

Если письмо с подтверждением подписки не пришло в течение нескольких минут — адрес с ошибкой или письмо попало в спам.


Введите свой E-mail и нажмите кнопку

Потом проверьте свой почтовый ящик и
папку СПАМ

Нажимая кнопку «Получить …» вы даете согласие
на обработку персональных данных

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

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