text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

Как точно посчитать прибыль отдельного товарного бренда с помощью Excel

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

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

Шаблон состоит из четырех разделов (см. рисунок 1. Лист «Содержание»):

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

В связи с тем что область исходных данных рассчитана только на один текущий период, чтобы сохранить информацию о распределении затрат, файл необходимо каждый месяц заполнять и сохранять под новым названием. Например, Brand P&L_jan.xls, Brand P&L_feb.xls и т. д.

Какие исходные данные требуются для расчета прибыли по товарным брендам

Начать работу с моделью расчета прибыли по товарным брендам следует с заполнения области исходных данных.

Для этого потребуются:

Бренды, сегменты рынка, товары. На листе «SKU» (см. рисунок 2. Лист «SKU») необходимо перечислить все бренды и виды товаров, которые продает компания. Кроме того, необходимо указать наименования сегментов рынка, на которых она реализует продукцию каждого бренда.

В примере все бренды разделены между тремя ценовыми сегментами рынка:

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

Если у компании брендов (или товаров какого-либо бренда) меньше, чем в модели, то достаточно очистить содержимое ячеек на листе «SKU», ничего не удаляя на других листах. Если больше, то потребуется добавить строки (столбцы) на всех листах, где присутствуют названия брендов (товаров). Кроме того, необходимо будет проверить все ссылки на диапазоны суммирования.

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

Доходная часть представлена одной статьей – выручка от реализации товаров (работ, услуг). На лист «Выручка от реализации», представленный на рисунке 3, необходимо внести выручку от продаж по базовой цене, скидки в рублях (по данным бухгалтерии), количество проданных товаров каждого вида.

На листах «Затраты на производство», «Прочие расходы себестоимости», «Маркетинговые расходы», «Коммерческие расходы», «Прочие внереализационные расходы» содержится перечень расходов, который при необходимости можно дополнить, сократить, переименовать. Информацию для заполнения этих листов нужно запросить в бухгалтерии.

Все расходы в модели условно разделены на косвенные и прямые.

1. Косвенные расходы – которые нельзя напрямую отнести на себестоимость конкретного вида продукции. Они распределяются между всеми видами товаров пропорционально объемам продаж (на листе «Brand P&L»).

Для удобства последующего финансового анализа в модели косвенные расходы разделены:

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

В столбце «Косвенные периодические расходы» нужно отражать (одной суммой) те расходы, которые относятся только к текущему периоду и зависят от объемов производства. Например, оплату труда производственных рабочих.

Внутри одной статьи могут быть как фиксированные косвенные затраты, так и периодические. Например, затраты на электроэнергию могут совмещать постоянную и переменную (зависящую от объемов производства) составляющие. Их следует внести в оба столбца (см. рисунок 4. Лист «Затраты на производство»), запросив данные о величине каждой составляющей в бухгалтерии.

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

Прямые расходы (на листах «Затраты на производство», «Прочие расходы себестоимости», «Маркетинговые расходы», «Коммерческие расходы», «Прочие внереализационные расходы») детализированы по каждому виду продукции, как показано на рисунке 4. Любые затраты, которые можно отнести на конкретный вид продукции, будь то производственная себестоимость (например, сырье для производства отдельных видов товаров) или коммерческие расходы (например, служебная командировка менеджера по продажам по продвижению конкретного бренда), необходимо включать в область таблицы «Прямые расходы» на каждом из перечисленных листов.

Отчет о прибылях и убытках по брендам формируется исходя из объемов продаж, а не производства. Это следует учитывать при распределении производственной себестоимости (заполняя исходные данные на листах «Затраты на производство», «Прочие расходы себестоимости», нужно указывать их лишь в отношении реализованной продукции). Если объемы производства значительно отличаются от объемов продаж, то можно рассчитать себестоимость единицы произведенной продукции в конкретном периоде и умножить ее на объем продаж в этом же периоде.

Как формировать отчеты о прибылях и убытках по товарным брендам в Excel

Предлагаемая модель предусматривает составление трех вариантов отчета о прибылях и убытках по брендам:

Каждый из них включает показатели:

Все они рассчитаны не только в целом на объем реализованной продукции, но и на единицу товаров каждого вида (эти данные – в скрытых строках, обозначенных значком +).

Отчет о прибылях и убытках по брендам за текущий период. Это основной отчет, который автоматически составляется по исходным данным о брендах, доходах и расходах (см. рисунок 5. Лист «Brand P&L»). А уже на его основе формируется ежемесячный архив сведений по месяцам (на листах с «Январь» по «Декабрь» в разделе «Отчет о прибылях и убытках по брендам за период» – см. рисунок 1. Лист «Содержание») и два других варианта отчета о прибылях и убытках – накопительным итогом с начала года (лист «Brand P&L YTD») и с начала года в детализации по месяцам (лист «Brand P&L по месяцам»).

Отчет заполняется автоматически. Чтобы убедиться в том, что он сформирован корректно, можно сделать проверку контрольных значений в ячейках В48:B54. Если все показатели из исходных данных распределились между брендами и видами товаров верно, то значения во всех этих ячейках будут равны нулю. В противном случае нужно искать ошибку (особенно актуально это может быть после добавления (удаления) столбцов или строк в модель).

Отчет о прибылях и убытках по брендам накопительным итогом с начала года. Как можно понять из названия отчета, расположенного на листе «Brand P&L YTD», он позволяет увидеть результаты деятельности (выручку, затраты, доходы, убытки) накопительным итогом с начала года, сравнить их с показателями предыдущего года за сопоставимый период (квартал, полугодие).

Формируется он автоматически при заполнении листов из области «Отчет о прибылях и убытках по брендам за период» по месяцам (листы с «Январь» по «Декабрь»).

Чтобы заполнять эти листы данными за каждый месяц, необходимо каждый раз копировать отчет за текущий период (лист «Brand P&L») и вставлять как значения на лист с названием нужного месяца.

Так как файл каждый месяц предстоит сохранять под новым названием (в связи с тем что область исходных данных рассчитана только на один текущий период), все ссылки на область с исходными данными на закладках с «Январь» по «Декабрь» лучше сохранять как значения. Иначе в файле будут присутствовать внешние ссылки, которые могут некорректно отображаться. Кроме того, есть риск несанкционированного изменения данных за прошедшие периоды.

Сохранять информацию и переносить ее на листы с названиями месяцев следует так.

1. На листе «Brand P&L» выделить всю область с цифрами B10:CL45 (при условии что в модель не добавляли строки или столбцы) и копировать ее (см. рисунок 6. Копирование данных отчета о прибылях и убытках).

2. Перейти на лист, соответствующий текущему периоду: «Январь», «Февраль», «Март» и т. д., и, используя инструмент «Специальная вставка», внести сведения в ячейку B10 как значения (см. рисунок 7. Вставка значений).

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

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

Отчет о прибылях и убытках по брендам помесячно. С помощью этого отчета (лист «Brand P&L по месяцам») удобно анализировать, как меняется прибыль по брендам от месяца к месяцу. К примеру, у некоторых из них может быть выражена сезонность – всплески продаж в одном месяце и провалы в другом. Он также позволяет изучать динамику доходов и расходов в течение нескольких месяцев, оценивать результаты маркетинговой акции, сравнивать объемы продаж по конкретным месяцам и конкретному бренду в текущем и прошлом годах (например, когда акцию не проводили).

Этот отчет формируется автоматически после заполнения листов из области «Отчет о прибылях и убытках по брендам за период» по месяцам (листы «Январь» – «Декабрь»).

В агрегированном виде он выглядит точно так же, как отчет о прибылях и убытках по брендам накопительным итогом с начала года (лист«Brand P&L YTD»). Однако если первый из них разгруппировать, нажав на 2 или значок + вверху таблицы, то по каждому бренду отобразятся показатели с детализацией по месяцам.

Данных по каждому виду товаров в отдельности этот отчет не содержит.

Наталья Гусева , финансовый менеджер компании Estillo Borse

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

Например, компания проводит промоакцию по продвижению брендов C, D, E и F в среднем ценовом сегменте, чтобы увеличить объемы продаж в последующие три месяца после окончания акции. Продолжительность акции – три месяца. Так, общий промежуток времени, на который следует отнести расходы на акцию, составляет шесть месяцев. Значит, если расходы на промоакцию составляют 450 тыс. руб., то их следует разделить на шесть месяцев и распределить между всеми товарами продвигаемых брендов. Предположим, в брендах C, D, E и F в рамках акции представлено всего 10 видов товаров. На каждый из них ежемесячно приходится 7,5 тыс. руб. затрат (450 тыс. руб. : 10 видов товаров : 6 мес.). Сумму 450 тыс. руб. будут отражать в отчете о доходах и расходах по брендам в течение шести месяцев (что позволит корректно оценивать эффективность акции), хотя в бухучете эти расходы, скорее всего, признают в период получения документов (например, счета-фактуры), что может быть на несколько месяцев позже проведения акции.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.