Как строить отчет о прибылях и убытках компании в Excel | Бизнес-школа Laba (Лаба)
Спецпредложение
со скидкой до -50%
-40%
-50%
Получить скидку
Журнал
if locale != request.locale

Поиск

Как строить отчет о прибылях и убытках в Excel

Пошаговая инструкция от аналитика в Laba Group.

cover-605c5368e4a09814017489.pl

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).

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

Все дополнительные строки добавляются через Главная — Вставить — Вставить строки на лист, а форматируются — с помощью кисточки.

Если вам интересна эта статья, то пригодится и наша шпаргалка
Анализ финансов компании
Схема-шаблон для бухгалтеров
  • Как держать финансы компании под контролем 
  • Где найти средства на покрытие текущих обязательств
  • Как предсказывать кассовые разрывы
  • Как говорить о финансах с CEO на одном языке

Приблизительный макет, с учетом добавленных строк:

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

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

А также добавляем данные по единоразовым расходам. Итоговый результат — сумма операционных и единоразовых расходов. Пример формулы:

Дальше вносим данные по налоговым расходам. И в конце добавляем строку ЧИСТАЯ ПРИБЫЛЬ, в которой прописываем формулу: Валовая прибыль — Расходы — Налоги:

В итоге наш P&L имеет следующий вид:

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

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

Хотите получать дайджест статей?

Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
Спасибо за подписку!
Курс по теме:
"Excel для бізнесу"
Программы
Ведет Алексей Вощак
15 декабря 24 января
Алексей Вощак