Profit & Loss report, или отчет о прибылях и убытках — один из базовых документов, которые позволяют оценить финансовые результаты компании. С его помощью можно отследить, как обстоят дела у вашего бизнеса: какую выручку получили за конкретный отчетный период, сколько вывели на расходы и какую часть от выручки составляет чистая прибыль.
Вместе с аналитиком Laba Group Валерией Петренко разбираемся, как строить отчет P&L в Excel.
Как сделать отчет о прибылях и убытках в Excel
P&L обычно составляют за месяц, квартал или год. А структура отчета, как правило, состоит из пяти частей:
- выручка от деятельности компании
- себестоимость проданных товаров или услуг
- общие расходы (заработная плата сотрудникам, расходы на рекламу и т. д.)
- прочие расходы, включая налоги, проценты и т. д.
- чистый доход
Разберем пошагово, как составить квартальный отчет о прибылях и убытках компании, которая работает в сфере продаж.
Для начала нужно создать подобный макет:
Чтобы работать с таблицей было удобнее, лучше сразу выделять ключевые моменты. Для этого можно менять цвет ячеек, типы их границ, использовать полужирный шрифт или курсив. Эти и другие опции доступны на вкладке Главная:
Например, чтобы создать заливку, нужно выделить диапазон таблицы и кликнуть на Цвет заливки на вкладке Главная. Кроме того, можно выделять отдельно строки или ячейки.
В результате таблица выглядит так:
Перейдем к заполнению отчета. Данные можно вносить вручную, подсчитав суммы в сторонних таблицах, или подтягивать автоматически — с помощью формул суммирования. Во втором случае нужно составить умную таблицу на одном из листов в Excel. На скрине ниже — пример такой таблицы с данными по продажам:
На основе умной таблицы можно просуммировать выручку по магазинам. В Excel есть несколько формул суммирования с условием, например, СУММЕСЛИ:
=СУММЕСЛИ(диапазон;условие;диапазон суммирования)
- Диапазон суммирования. Ячейки, значения из которых суммируются.
В нашем примере это столбец Сумма.
- Условие. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки нужно суммировать.
В примере задаем следующее условие — просуммировать выручку только по Магазину №1.
- Диапазон. Диапазон ячеек, по которым задается условие.
В примере — столбец Магазин.
Но в нашем случае необходимо учесть два условия — во-первых, номер магазина, а во-вторых, нужно ограничить временной период конкретным кварталом. Поэтому используем формулу СУММЕСЛИМН. Ее синтаксис похож на предыдущую и выглядит так:
=СУММЕСЛИ(диапазон суммирования; диапазон_условия_1;условие_1;диапазон_условия_2;условие_2; ...)
Чтобы заполнить выручку поквартально, прописываем формулу СУММЕСЛИМН в ячейки, которые соответствуют нужному магазину и периоду. На скрине ниже в ячейке D6 приведен пример формулы для подсчета выручки по магазину №1 за первый квартал 2020 года:
По такому же принципу прописываем формулы в остальные ячейки, изменяя только номер магазина и период.
С помощью формулы СУММ подсчитываем итоговые значения по квартальной (строка 11) и годовой выручке (столбец H). На скрине ниже в ячейке H11 приведен пример формулы для подсчета итоговой выручки по всем магазинам:
Чтобы понимать, какой из магазинов приносит наибольшую/наименьшую выручку, добавляем новые столбцы (Главная — Вставить — Вставить столбцы на лист) с процентной частью от общей выручки — и прописываем следующую формулу:
Разберем эту формулу подробнее. Функция ЕСЛИ проверяет значение ячеек на соответствие заданному условию. В нашем примере были заданы такие условия: если в таблице не заполнена выручка по магазину ИЛИ не рассчитана итоговая квартальная выручка, тогда нужно поставить прочерк, в ином случае — разделить выручку по магазину на итоговую.
Чтобы в ячейке отобразились проценты, необходимо на вкладке Главная задать формат ячейки — процентный. В итоге мы получим представление о том, какую часть дохода приносит каждый из магазинов.
Если нам нужно визуально разграничить ячейки, снова возвращаемся к заливке. Чтобы получить такую таблицу:
нужно выделить ячейку (к примеру, D6) и на вкладке Главная — Цвет заливки выбрать белый, а далее на этой же вкладке кликнуть на Границы и определить тип линии:
Чтобы форматирование перенеслось на другие ячейки, нужно выбрать отформатированную ячейку (D6), кликнуть на кисточку на вкладке Главная и далее выделить нужный диапазон ячеек (напр., D7:D10).
Теперь посчитаем валовую прибыль компании. Для этого необходимо рассчитать и заполнить себестоимость проданных товаров.
Все дополнительные строки добавляются через Главная — Вставить — Вставить строки на лист, а форматируются — с помощью кисточки.
Приблизительный макет, с учетом добавленных строк:
Добавляем итоговую строку Валовая прибыль. Чтобы ее заполнить, необходимо в ячейках, соответствующих каждому из кварталов, отнять от полученного дохода себестоимость проданных товаров. Например, в ячейке D19 прописываем: =D11-D17.
По такому же принципу добавляем строки с данными по операционным расходам компании — административные (з/п сотрудникам, арендная плата, коммунальные услуги и т. д.) и затраты на рекламу.
А также добавляем данные по единоразовым расходам. Итоговый результат — сумма операционных и единоразовых расходов. Пример формулы:
Дальше вносим данные по налоговым расходам. И в конце добавляем строку ЧИСТАЯ ПРИБЫЛЬ, в которой прописываем формулу: Валовая прибыль — Расходы — Налоги:
В итоге наш P&L имеет следующий вид:
Анализируя отчет, обращайте внимание на закономерности. Независимо от того, как часто вы рассчитываете прибыли и убытки — ежемесячно или ежеквартально — сравнение данных покажет тенденции вашего бизнеса.
Даже если чистая прибыль примерно одинаковая, отчет поможет увидеть, в каком из периодов валовая прибыль проседает (вместе с сокращением расходов), а в каком — увеличивается.
Хотите получать дайджест статей?
