Как рассчитать комиссионное вознаграждение в экселе
Обновлено: 04.07.2024
Поговорим о том, как оперативно подготовить отчеты о самых выгодных клиентах, используя Excel. Это решение поможет оперативно найти среди клиентов лидеров по объемам продаж и прибыли. Навыки программирования не потребуются, затраты времени минимальны. Отчеты сформируем «сводными таблицами».
В первую очередь важно идентифицировать клиентов-лидеров. Результаты анализа пригодятся для формирования кредитной, ценовой, ассортиментной и маркетинговой политики, принятия обоснованных управленческих решений.
К сведению
Подготавливаем данные для сводной таблицы Excel
Почему именно сводные таблицы Excel? Это продиктовано двумя важными факторами:
1) учет продаж ведут в специализированных базах данных, набор собственных отчетов в которых ограничен, а для создания дополнительных нужны услуги программиста. Одновременно большинство современных учетных систем позволяют рядовым пользователям экспортировать данные в Excel;
2) сведения о продажах в нужных аналитиках ведут в Excel.
Чтобы воспользоваться инструментом Excel «Сводные таблицы», важно корректно подготовить таблицу-источник.
Требования к таблице-источнику:
• все столбцы таблицы должны иметь уникальные заголовки;
• названия столбцов («шапка») должны помещаться в одну строку, многоуровневые «шапки» недопустимы;
• в каждом столбце данные одного типа — только текст, только суммы и т. д.;
• внутри таблицы не должно быть полностью пустых строк и столбцов;
• не должно быть объединенных ячеек;
• таблицу следует преобразовать в «умную»:
вкладка Главная → Стили → Форматировать как таблицу.
Дополнительное требование — в таблице-источнике нужно сформировать данные в разрезе аналитик, которые:
• во-первых, необходимы для того, чтобы построить отчеты в соответствии со спецификой компании и требованиями руководства. Как правило, это данные по клиентам, видам и линейке продукции, торговым маркам, регионам, порядку расчетов и т. д.;
• во-вторых, это достоверные, полные, актуальные данные.
К сведению
Порядок расположения столбцов в таблице значения не имеет. Перечень граф в таблице-источнике определяется учетной политикой и спецификой компании.
В таблице 1 представлены данные о продажах условной компании-производителя обуви, сумок и прочей кожгалантерейной продукции. Как видим, при ее составлении соблюдены все требования, которые мы перечислили.
И теперь нам нужно определить, кто из клиентов наиболее выгодный для компании: ООО «Крокус» (строка 212), которое закупило сапоги женские на сумму 128 216 руб., или ООО «Стэп» (строка 214), которое закупило туфли женские на сумму 143 152 руб.
Чтобы ответить на этот вопрос, необходимо просуммировать отгрузочные накладные (счета на оплату) согласно аналитикам и сравнить результаты. Оптимально с поставленной задачей справится сводная таблица.
Составляем отчеты о прибыльности клиентов
На этом этапе мы с помощью сводных таблиц определяем клиентов-лидеров, приносящих компании максимум валовой прибыли.
Отчеты в сводных таблицах предназначены:
• для систематизации анализа больших объемов данных;
• подведения промежуточных итогов и вычисления количественно-стоимостных данных, обобщения данных по категориям и подкатегориям, создания пользовательских вычислений и формул;
• развертывания и свертывания уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим критериям;
• перемещения строк в столбцы или столбцов в строки;
• фильтрации, сортировки, группировки и условного форматирования наиболее важных и часто используемых подмножеств данных для привлечения внимания руководства к нужным показателям;
• формирования кратких наглядных отчетов.
Отчет в виде сводной таблицы часто используют, если нужно проанализировать связанные итоги, особенно в тех случаях, когда нужно сложить длинный перечень показателей (например, перечень сделок (см. табл. 1)), а объединенные данные или промежуточные итоги позволят взглянуть на данные с различных точек зрения или сравнить значения для схожих данных.
Вернемся к нашей задаче — определить клиентов, которые приносят компании максимальную выгоду. Воспользуемся критерием валовой прибыли и создадим еще одну таблицу (табл. 2):
вкладка Вставка → Таблицы → Сводная таблица → На новый лист.
Список полей сводной табл. 2 (см. также рис. 1):
• названия строк — Клиент. Поля, перенесенные в эту область, размещаются в левой части сводной таблицы и представляют собой уникальные значения.
• названия столбцов — Линейка продукции. Область подходит для тех полей, по которым требуется видеть разбивку показателей;
• значения — Валовая прибыль. По полям, которые перенесены в эту область, выполняются все расчеты исходных данных;
• фильтр отчета. Эта область является необязательной и находится в верхней части сводной таблицы. Помещение полей в область фильтра отчета позволяет фильтровать данные в таблице. Мы применять фильтр не будем.
Рис. 1. Список полей сводной таблицы
Чтобы визуализировать наиболее выгодных клиентов, но при этом обойтись без дополнительных диаграмм, в табл. 2 применим условное форматирование двух видов (рис. 2):
1) вкладка Главная → Стили → Условное форматирование → Правила выделения ячеек → Больше — задано условие «Форматировать ячейки, которые БОЛЬШЕ 500 000 руб.» и в нашем примере выбрана зеленая заливка и темно-зеленый текст (см. табл. 2). При необходимости экономист может задать другой нужный ему критерий визуализации;
2) вкладка Главная → Стили → Условное форматирование → Гистограммы (красные).
Такие виды условного форматирования будем применять и для других отчетов.
Рис. 2. Визуализация наиболее выгодных клиентов
Далее определим процентный вклад клиентов в прибыль (табл. 3).
Список полей сводной табл. 3:
• названия строк — Клиент;
• значения — валовая прибыль.
В таблице 3 применены дополнительные вычисления:
вкладка Параметры → Дополнительные вычисления → % от суммы по столбцу.
Мы рекомендуем
Если у вас есть статистика показателей за аналогичный период прошлого года или предшествующий отчетный период, сравните эти показатели и отследите динамику: изменилась ли тройка лидеров, кто сдвинулся вниз по списку и почему — увеличилось количество клиентов и объемы продаж или произошли структурные изменения постоянной группы клиентов. По результатам этого анализа можно будет принимать соответствующие управленческие решения.
В. Волкова,
эксперт
Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 9, 2020.
Основное преимущество интернет-сервисов по учёту инвестиций в том, что в любой момент вы можете посмотреть состояние дел в вашем портфеле из любой точки мира. Но что делать, если вы не хотите тратить лишние деньги или не доверяете Интернету? В таком случае можно вести учёт сделок самостоятельно в Excel. Минус этого подхода в том, что вам необходим именно тот компьютер, на котором хранится отчётность. Хотя с применением облачных технологий этот недостаток легко устраняется. Неудобством является то, что вы не сможете оценить стоимость портфеля в режиме онлайн, как это доступно в интернет-сервисах, но и эта проблема решается, если вы аккуратный инвестор и регулярно проверяете свой портфель.
Далее на примере показано, как можно вести отчётность. Подразумевается, что все разделы отчётности — листы одной книги Excel, т.е. физически всё хранится в одном файле.
1. Движение денежных средств по счёту
Простая таблица из трёх столбцов: «Дата», «Зачисление», «Отзыв». Желательно, чтобы была ещё и сводная статистика по зачисленным и отозванным денежным средствам за всё время инвестирования.
Для определения «Зачислил, Всего» вам нужно суммировать все значения в столбце «Зачисление». Для получения значения «Отозвал, Всего» — суммируете значения в столбце «Отзыв». «Сальдо» определяется как разница между «Отозвал, Всего» и «Зачислил, Всего». В том, что сальдо отрицательное, нет ничего страшного, если вы вносите на счёт намного большие суммы, чем отзываете. Важнее другое: сумма сальдо и текущего баланса счёта должна быть положительной. Это означает, что в случае, если вы закроете счёт и выведете все деньги, то окажетесь с прибылью. В нашем примере 31.12.18 года на счёте было 820 тысяч рублей, сальдо равнялось − 606 тысяч. Если бы вы закрыли счёт и вывели все деньги в последний день 2018 года, то ваша прибыль составила бы 214 тысяч рублей. Это накопленный итог за всё время инвестирования.
Обратите внимание, что в таблице присутствуют даты 31.12.18 и 01.01.19, но в эти дни не производилось ни зачисление, ни отзыв денег. Они необходимы для определения доходности вложений в течение года.
Одна полезная функция, встроенная в Excel — возможность фиксировать строки, чтобы они не прокручивались. Обратите внимание, что на рисунке после третьей строки идёт 121-я. Фиксация строк делает работу с длинными таблицами намного удобнее.
2. Состав портфеля
Следующий лист в книге — «Состав портфеля на отчётную дату». В нашем примере отчётными датами выступают последние дни кварталов.
На рисунке биржевые тикеры акций используются для краткости. Значения для количества акций и стоимости соответствующей позиции можно посмотреть в терминале. «Доля в портфеле» определяется как отношение стоимости соответствующей позиции к общей стоимости портфеля. Диаграмма приведена для наглядности.
Лист с составом портфеля позволяет отслеживать динамику акций. Если вы собираетесь проводить ребалансировку, то столбец с долями акций в портфеле подскажет, как её осуществить.
3. Динамика портфеля по годам
Состав портфеля на определённую отчётную дату может быть весьма информативен, но если нужно подсчитать доходность вложений за длительный период, то необходимы дополнительные данные. Помимо ежеквартальной отчётности желательно вести годовую.
«Результат управления» рассчитывается как разница между стоимостью активов в конце года и начале, с поправкой на сальдо ввода и вывода денежных средств. «Прирост капитала» рассчитывается как отношение результата управления к стоимости активов на конец года.
«Чистая доходность» — это внутренняя норма доходности ваших вложений, учитывающая ввод и вывод денежных средств со счёта. Смысл внутренней нормы доходности поясним на примере.
На рисунке вы видите, что по итогам 2016 года «Чистая доходность» составила 25,52%. Представьте, что вы нашли депозит в банке со ставкой 25,52% и совершили следующие действия:
- внесли в начале года 341 246 рублей;
- в течение года внесли суммарно 212 600 рублей, такими же частями и в те же даты, что и на брокерский счёт;
- в течение года вывели 8 360 рублей, такими же частями и в те же даты, что и с брокерского счёта.
В этом случае в конце года на вашем депозите было бы 646 751 рубль.
При отслеживании динамики портфеля по годам желательно сравнивать «Чистую доходность» портфеля с Индексом Мосбиржи. Об этом читайте в статье «Почему стоит ориентироваться на индекс при покупке акций».
4. Учёт дивидендов и купонов
Если ваш инвестиционный подход ориентирован на получение дивидендов и купонов, то важно отслеживать суммарные дивидендные и купонные выплаты по итогам того или иного года.
Биржевые тикеры акций и обозначения 3М, 6M, 9M, Y используются для краткости. Если дивиденды пока не известны, например, по акциям «Газпрома», в таблице стоят нули.
5. Сделки с ценными бумагами
Учёт сделок показан на примере акций «ФосАгро». «Сумма покупки» и «Сумма продажи» учитывают комиссии брокера. «Сальдо» определяется как разница между суммой продажи ценных бумаг и суммой покупки. В нашем примере ни одна акция не продавалась, поэтому сальдо отрицательное. Если текущая стоимость акций будет выше 98 тысяч рублей, то при продаже акций и закрытии позиции будет прибыль. Если в финансовом результате учитывать полученные дивиденды, то для получения прибыли нужно продать акции дороже, чем за 94 325 рублей. «Цена безубытка» определяется как отношение сальдо, взятого со знаком «—», к количеству акций в портфеле. «Цена безубытка с учётом» дивидендов определяется аналогично, только в расчётах используется «Сальдо с учётом дивидендов». «Количество ц\б» рассчитывается автоматически на основании содержимого столбцов «Количество» в разделах «Покупка» и «Продажа».
Для облигаций учёт будет аналогичным, но с учётом НКД и номинала при покупке и продаже.
На каждую позицию в вашем портфеле следует завести соответствующий лист в книге Excel. Поскольку позиций в вашем портфеле может быть много, то будет лучше вынести листы со сделками в отдельную книгу. Финансовые результаты сделок можно вести на отдельном листе.
Для большей информативности удобно завести в таблице с финансовыми результатами отдельный столбец, в котором содержится тип дохода: дивиденды, купоны, закрытие позиции. Он будет полезен при составлении сводной таблицы для группировки финансовых результатов, т.к. доступна сумма по каждой категории.
В нашем примере ведётся учёт финансового результата без разделения на типы доходов, поэтому группировка выполнена по годам.
Напоминаем, что любые вопросы, предложения и пожелания можно и нужно озвучивать нам через форму обратной связи. Нам важно ваше мнение – вместе мы сделаем «Открытый журнал» ещё лучше!
Способы дополнительного заработка на своем портфеле Дивиденды: консервативные и спекулятивные стратегии заработка Как получать выгоду от владения дивидендными акциями? Как вести себя во время коррекции на фондовом рынке Как работают эти разновидности экзотических деривативов Готовимся к неожиданностям на рынке деривативов: матрица стратегий на любой случай О трейдерах, практикующих высокорисковую стратегию быстрого заработка Дайте мне торговать с плечом, и я переверну этот мирМосква, ул. Летниковская, д. 2, стр. 4
Будьте в курсе новых публикаций!Подпишитесь на дайджест «Открытого журнала» и получайте подборку публикаций за неделю.
Размещённые в настоящем разделе сайта публикации носят исключительно ознакомительный характер, представленная в них информация не является гарантией и/или обещанием эффективности деятельности (доходности вложений) в будущем. Информация в статьях выражает лишь мнение автора (коллектива авторов) по тому или иному вопросу и не может рассматриваться как прямое руководство к действию или как официальная позиция/рекомендация АО «Открытие Брокер». АО «Открытие Брокер» не несёт ответственности за использование информации, содержащейся в публикациях, а также за возможные убытки от любых сделок с активами, совершённых на основании данных, содержащихся в публикациях. 18+
АО «Открытие Брокер» (бренд «Открытие Инвестиции»), лицензия профессионального участника рынка ценных бумаг на осуществление брокерской деятельности № 045-06097-100000, выдана ФКЦБ России 28.06.2002 (без ограничения срока действия).
ООО УК «ОТКРЫТИЕ». Лицензия № 21-000-1-00048 от 11 апреля 2001 г. на осуществление деятельности по управлению инвестиционными фондами, паевыми инвестиционными фондами и негосударственными пенсионными фондами, выданная ФКЦБ России, без ограничения срока действия. Лицензия профессионального участника рынка ценных бумаг №045-07524-001000 от 23 марта 2004 г. на осуществление деятельности по управлению ценными бумагами, выданная ФКЦБ России, без ограничения срока действия.
Читайте также: