Как посчитать процентное распределение в Excel по формуле
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Формула процентного распределения в Excel
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
Если ТРУИП (товары, работы, услуги, имущественные права) используются в облагаемых и не облагаемых налогом на добавленную стоимость операциях, «входной» НДС нельзя принять к вычету в полном объеме, нужно распределить его исходя из пропорции, указанной в п. 4 ст. 170 НК РФ.
Стоимость отгруженных ТРУИП, операции по реализации которых подлежат налогообложению (освобождены от налогообложения) / Общая стоимость отгруженных ТРУИП за налоговый период.
АО производит как облагаемую, так и не облагаемую НДС продукцию. АО начало свою деятельность в январе, за месяц приобрело материалов на сумму 1 200 000 руб. (в т. ч. НДС 20000 руб.). Все материалы оплачены и переданы в производство к 31 марта. 10 % этих материалов было использовано в не облагаемой НДС деятельности, 5 % в облагаемой и не облагаемой и 85 % только в облагаемой.
Продажи за 1-й квартал составили 3 000 000 руб. (без НДС), 200 000 руб. из которых относились к необлагаемой деятельности.
Рассчитать сумму НДС к возмещению и сумму НДС, которая пойдет в расходы 1-го квартала.
Шаг 1. Определим отношение не облагаемой НДС выручки к общей выручке.
200 000 / 3 000 000 = 6,7 %
Шаг 2. На основе пропорции, полученной на шаге 1, распределим общую стоимость товаров и услуг.
Материалы, используемые для облагаемой и не облагаемой НДС деятельности
Распределено на не облагаемую НДС деятельность: (50 000 * 6,7 %)
Распределено на облагаемую НДС деятельность: (50 000 * 93,3 %)
Шаг 3. Используя пропорцию, полученную на шаге 1, определим сумму «входного» НДС к возмещению.
НДС на материалы, подлежащий списанию (включенный в стоимость материалов):
Входящий НДС по материалам, используемым как в облагаемой, так и не облагаемой НДС деятельности (200 000 * 5 %)
НДС, списанный со стоимостью материалов (10 000 * 6,7 %)
В примере в расчет пропорции включена только выручка – облагаемая и не облагаемая НДС. Но на практике при расчете указанной пропорции учитывают и другие операции.
Включите в расчет пропорции: выручку, проценты к получению при предоставлении заемных средств (письма Минфина от 06.03.2018 № 03-07-11/14170, от 16.01.2017 № 03-07-11/1282), прибыль от реализации ценных бумаг, доходы от производных финансовых инструментов, прочие доходы, отраженные на счете 91/1 (реализация спецодежды, трудовых книжек, продажа основных средств и т. д.).
Обратите внимание, сумму самого займа не учитывайте при расчете, только проценты (подп. 4 п. 4.1 ст. 170 НК РФ, письма Минфина от 02.04.2009 № 03-07-07/27, от 28.04.2008 № 03-07-08/104, письмо ФНС России от 06.11.2009 № 3-1-11/886). В письме от 10.01.2020 г. № 03-07-14/216 Минфин уточнил информацию о процентах: проценты по предоставляемому займу – учитываются при расчете вышеуказанной пропорции, а вот проценты за пользование чужими средствами (ст. 395 ГК РФ), взысканные по суду, – не учитываются.
Не включается в пропорцию то, что не соответствует понятию реализации: дивиденды, штрафы по хозяйственным договорам, курсовые разницы, финансовая помощь, полученные проценты по депозитам (письмо Минфина от 16.01.2017 № 03-07-11/1282).
При расчете пропорции иногда возникают сложности
1. Отгрузка не совпадает с передачей права собственности. Когда учесть реализацию?
Дата отгрузки = дата первого по времени составления первичного документа, оформленного на покупателя (заказчика), перевозчика (письмо Минфина от 01.03.2012 № 03-07-08/55).
Если товар не отгружается и не транспортируется, но происходит передача права собственности на него, днем отгрузки признается дата передачи права собственности (п. 3 ст. 167 НК РФ, письмо Минфина от 01.11.2012 № 03-07-11/473).
Согласно п. 1 ст. 167 НК РФ моментом определения налоговой базы по НДС является день отгрузки (передачи) ТРУИП либо день получения предоплаты, следовательно, даже если право собственности на отгружаемые ценности не передано – в пропорцию операции все равно включаются.
2. Выполнение работ не совпадает с датой подписания акта заказчиком
Если подрядчик обратится в суд, который подтвердит выполнение работ – НДС начисляется на дату вступления в законную силу решения суда (письмо Минфина от 02.02.2015 № 03-07-10/3962). Суды поддерживают такое мнение (см. постановление Восьмого ААС от 16.12.2014 № 08АП-11734/2014 по делу № А70-5069/2014).
Вывод: включайте в пропорцию сумму реализации работ при условии подписания акта заказчиком.
3. Корректировки реализации (в т. ч. ошибки) – влияют ли на состав пропорции?
4. Отсутствие отгрузок – пропорции нет?
НК РФ не уточняет порядок действий. Минфин предлагает ориентироваться на собственную учетную политику в целях налогообложения (см. письмо от 11.03.2015 № 03-07-08/12672).
Варианты расчета вычетов по НДС при отсутствии отгрузок в отчетном периоде:
— пропорционально стоимости отгруженных товаров (работ, услуг) за последующий налоговый период;
— пропорционально стоимости отгруженных товаров (работ, услуг) за предыдущий налоговый период;
— пропорционально всем прямым затратам за текущий налоговый период (квартал);
— пропорционально какому-либо показателю прямых затрат (оплата труда, материальные затраты) за текущий налоговый период (квартал).
5. На основе какой пропорции распределять расходы из авансового отчета – в периоде поездки или в периоде утверждения отчета?
Если поездка сотрудника относится как к облагаемой НДС деятельности организации, так и к необлагаемой деятельности, то НДС, предъявленный перевозчиком, подлежит вычету и учитывается в стоимости услуг в соответствующем квартале в суммах, определенных исходя из пропорции, рассчитанной за этот же квартал. При этом затраты на проезд сотрудников учитываются в составе расходов на дату утверждения авансового отчета сотрудника на основании билета (маршрут-квитанции), приложенного к этому авансовому отчету (п.п. 5, 7, 16, 18 ПБУ 10/99, пп. 5 п. 7 ст. 272 НК РФ).
НДС, предъявленный перевозчиком, также принимается к вычету в периоде, в котором утвержден авансовый отчет работника (п.п. 2, 7 ст. 171, п. 1 ст. 172 НК РФ, п. 18 Правил ведения книги покупок).
6. Как составить пропорцию при безвозмездной передаче (п. 2 ст.154 НК)?
Налогоплательщик, являющийся передающей стороной, при расчете пропорции для распределения входного НДС в стоимость операций, облагаемых НДС, должен включить рыночную стоимость безвозмездно переданных ТРУИП. У принимающей стороны получение ценностей безвозмездно не окажет влияния на распределение входного НДС, так как передающая сторона не предъявляет НДС со стоимости безвозмездной операции, а уплачивает налог в бюджет самостоятельно.
Вывод: для правильного распределения налоговых вычетов нужно придерживаться правил НК РФ при включении доходов в расчет пропорции.
Пропорциональное распределение доли общества при выходе участника из ООО
Инструкция по пропорциональному распределению доли
Распределение доли после выхода участника Общества
Законом установлено, что в течение одного года со дня перехода доли или части доли в Уставном капитале к Обществу они должны быть по решению общего собрания участников Общества распределены между всеми участниками Общества пропорционально их долям в Уставном капитале Общества или предложены для приобретения всем, либо некоторым участникам Общества.
В первом случае мы говорим о пропорционально распределении доли общества. Во втором случае имеется ввиду именно непропорциональное распределение. В этом случае между обществом в лице его руководителя и покупателем заключается договор купли-продажи.
Когда происходит пропорциональное распределение
В случае наличия у участников желания оставить прежним соотношения своих долей в уставном капитале, они принимают решение о пропорциональном распределении доли общества. Также необходимо, чтобы при расчете распределения доли была возможность математически верно рассчитать новое процентное соотношение долей.
Правильное пропорциональное распределение
Рассмотрим, как правильно распределить доли между участниками ООО при выходе одного или нескольких участников на примере:
В составе ООО три участника с нижеуказанными долями. Для удобства определим участникам ООО порядковые номера: 1, 2, 3. Размер уставного капитала – 10 000 рублей.
Из состава выходит участник №3. Задача: определить доли оставшихся участников после его выхода.
1 – 45% или 4 500 рублей или 45/100
2 – 30% или 3 000 рублей или 30/100
3 – 25% или 2 500 рублей или 25/100
Поскольку выходит участник №3 с долей 25% или 2 500 рублей, то нам необходимо распределить его долю среди оставшихся участников пропорционально их долям в уставном капитале ООО.
Для этого нам нужно, определить долю, принадлежащую ООО — 2 500 рублей или 25% или 25/100.
Определяем сумму долей, принадлежащих оставшимся участникам:
Доля участника №1 + доля участника №2 = 45% + 30% = 75%
В том числе доля участника №1 = 45/75
В том числе доля участника №2 = 30/75
Далее необходимо долю, принадлежащую ООО (2 500 рублей) умножить на вышеуказанные доли участников:
Участник №1 = 2 500 * 45/75 = 1 500 рублей
Участник №2 = 2 500 * 30/75 = 1 000 рублей
Т.е., из доли, принадлежащей ООО, участнику №1 переходит 1 500 рублей, а участнику №2 – 1 000 рублей.
Таким образом, после распределения доли ООО доли оставшихся участников увеличиваются не только в денежной выражении, но и в процентном:
Участник №1 = 4 500 + 1 500 = 6 000 рублей
Участник №2 = 3 000 + 1 000 = 4 000 рублей
Процентное выражение долей станет следующим (для этого необходимо денежное выражение доли участника поделить на общий размер уставного капитала и умножить на 100):
Участник №1 = 6 000 / 10 000 * 100 = 60%
Участник №2 = 4 000 / 10 000 * 100 = 40%
Также существует более простая формула для распределения доли принадлежащей Обществу между остальными участниками пропорционально.
Новая доля участника = сумма уставного капитала / (разделить на) суммарную долю в процентах остающихся участников х (умножить на) долю конкретного участника до распределения доли.
Таким образом будет вычислена новая доля участника в денежном выражении. Чтобы рассчитать в процентах нужно эту сумму разделить на сумму всего уставного капитала и умножить на 100.
Необходимые документы
Чтобы уведомить налоговую о факте пропорционального распределения доли одновременно с выходом участника потребуется следующий пакет документов:
Обратите внимание, что форма Р14001 с 25 ноября 2020 года больше не применяется. Новая форма Р13014 утверждена Приказом ФНС России от 31.08.2020 N ЕД-7-14/617@.
В практике программирования часто встречается задача распределить сумму по элементам пропорционально значениям. Например, распределить поступившую оплату по статьям или по договорам пропорционально долгу. Если использовать простую формулу пропорции, то на округлениях может образоваться ошибка в ту или иную сторону. Обычно это решается подсчетом размера ошибки и добавлением остатка на одну из статей распределения с помощью цикла. Между тем, это можно выполнить в запросе полностью.
Итак, предположим, у нас есть таблица Долги с полями Договор и Долг. Кроме того у нас есть параметр СуммаОплаты.
Первым действием нам необходимо в поле РаспределеннаяОплата распределить сумму оплаты по договорам в соответствии с коэффициентом Долг/СуммаДолга с округлением до 2 знака после запятой (сумма долга подсчитывается с помощью вложенного запроса). В этом же запросе в поле Порядок одновременно пронумеруем договоры в порядке убывания суммы. Это нам необходимо для распределения образовавшегося «излишка». Нумерация выполняется путем самосоединения таблицы с условием на долг по договору, а также на код договора, на случай если долг окажется одинаковым.
Мы получим таблицу:
Вторым действием вычислим разницу между суммой оплаты и суммой распределения. Получив несколько копеек отклонения в ту или иную сторону, добавим по одной копейке на каждый договор. Для этого сравним сумму отклонения, умноженной на 100 и поле Порядок. Распределение выполнится таким образом, что начиная с самого большого долга, будет добавлено ровно столько копеек, на сколько было отклонение. Количество копеек отклонения заведомо не может быть больше количества договоров. В запрос для наглядности добавлены поля по всем этапам вычисления.
В результате получим таблицу:
Все, оплата распределена.
Если убрать из запроса лишние поля, то итоговый результат будет выглядеть следующим образом:
Идея, думаю, понятна. Таким же образом можно перераспределить положительные и отрицательные суммы между договорами и так далее. Как ни странно, в Интернете я похожего решения не нашел, везде пишут «запросом невозможно, делайте в цикле». Поэтому описал максимально подробно смысл каждого действия. Пользуйтесь на здоровье.
Специальные предложения
(6) иной раз изящность решения задачи (ну скажем выпендрился и написал распределение в запросе) нафиг не нужно из-за своей сложности восприятия через некоторое время. Я сторонник не пихать все и вся в запросы без необходимости. Да и на практиче возникают ситуации и у меня были, когда нужно распределить 4 числа (БУ НУ ПР ВР) параллельно по 4-м базам (БУ НУ ПР ВР) и потом еще вывести погрешности на «чистую воду» и все проровнять (решалась задача постатейного закрытия 20, 23 счетов в БП 2.0, где субконто СтатьяЗатрат не оборотное, а потом все переделывалось под БП 3.0).
К слову в БП 2.0/3.0 стандартные алгоритмы некорректно распределяют амортизацию, когда она распределяется по способам отражения, где очень много прописано разных дробных коэффициентов. Ошибка выражается в том, что правило БУ = НУ + ПР + ВР не выполняется, а должно. Пришлось написать обработку выравнивающую положение дел уже после расчета и распределения амортизации. Увы, но переписка с 1С ни к чему не привела, они тупо отписались, что не смогли воспроизвести ошибку. хотя все предоставлял им.
Желаю вам приятного геммороя при раскапывании проблем, возникающих при вкрячивании таких запросов например в проведение документов (реверанс в сторону 1С).
А что делать, если есть еще одна группировка. И нужно распределять внутри группировки
Т.е. если добавить к примеру в задаче контрагента. И распределять долг контрагента по его договорам?
Обновление06.08.15 16:30
Код открытНе указано
См. также
Аналог PIVOT в запросе 1С (как выполнить транспонирование таблицы в запросе 1С) Промо
В статье показывается простой метод реализации аналога оператора PIVOT в запросе 1С без использования соединений.
