Profit & Loss report, або звіт про прибутки та збитки — один з базових документів, які дозволяють оцінити фінансові результати компанії. З його допомогою можна відстежити, як справи у вашого бізнесу: який виторг отримали за конкретний звітний період, скільки складають витрати і яку частину від виторгу становить чистий прибуток.
Разом з аналітиком Laba Group Валерією Петренко розбираємося, як складати звіт P&L в Excel.
Як створити звіт про прибутки та збитки в Excel
P&L зазвичай роблять за місяць, квартал чи рік. А структура звіту, як правило, складається із п'яти частин:
- виторг від діяльності компанії
- собівартість проданих товарів чи послуг
- загальні витрати (заробітна плата працівникам, витрати на рекламу тощо)
- інші витрати, включаючи податки, відсотки тощо.
- чистий дохід
Розберемо покроково, як скласти квартальний звіт про прибутки та збитки компанії, яка працює у сфері продажу.
Для початку потрібно створити подібний макет:
Щоб працювати з таблицею було зручніше, краще одразу виділяти ключові моменти. Для цього можна змінювати колір клітинок, типи їх меж, використовувати жирний шрифт або курсив. Ці та інші опції доступні на вкладці Основне (Home):
Наприклад, щоб створити заливку, потрібно виділити діапазон таблиці та клацнути на Колір заливки на вкладці Основне. Крім того, можна виділяти окремо рядки або клітинки.
У підсумку таблиця виглядає так:
Перейдемо до заповнення звіту. Дані можна вносити вручну, підрахувавши суми в сторонніх таблицях, або підтягувати автоматично за допомогою формул підсумовування. У другому випадку потрібно скласти розумну таблицю на одному з аркушів Excel. На скрині нижче — приклад такої таблиці з даними з продажу:
На основі розумної таблиці можна підсумувати виторг у магазинах. В Excel є кілька формул підсумовування з умовою, наприклад, SUMIF:
=SUMIF(діапазон;умова;діапазон підсумовування)
- Діапазон підсумовування. Клітинки, значення з яких підсумовуються.
У нашому прикладі це стовпець Сума. - Умови. Умова у формі числа, виразу, посилання на клітинку, тексту або функції, яка визначає, які клітинки потрібно підсумовувати.
У прикладі ставимо таку умову — підсумувати виторг лише для Магазину №1. - Діапазон. Діапазон клітинок, за якими визначається умова.
У прикладі — стовпець Магазин.
Але в нашому випадку необхідно врахувати дві умови — по-перше, номер магазину, а по-друге, потрібно обмежити часовий період конкретним кварталом. Тому використовуємо формулу SUMIFS. Її синтаксис схожий на попередню і виглядає так:
=SUMIFS(діапазон підсумовування;діапазон_умови_1;умова_1;діапазон_умови_2;умова_2; ...)
Щоб заповнити виторг поквартально, прописуємо формулу SUMIFS у клітинки, які відповідають потрібному магазину та періоду. На скрині нижче в клітинці D6 наведено приклад формули для підрахунку виторгу для магазину №1 за перший квартал 2020 року:
За таким же принципом прописуємо формули в інші клітинки, змінюючи тільки номер магазину та період.
За допомогою формули SUM підраховуємо підсумкові значення квартального (рядок 11) і річного виторгу (стовпець H). На скрині нижче в клітинці H11 наведено приклад формули для підрахунку підсумкового виторгу для всіх магазинів:
Щоби розуміти, який з магазинів приносить найбільший/найменший виторг, додаємо нові стовпці (Основне — Вставити — Вставити стовпці до аркуша) з відсотковою частиною загального виторгу — і прописуємо таку формулу:
Розберемо цю формулу докладніше. Функція IF перевіряє значення клітинок на відповідність заданій умові. У нашому прикладі були задані такі умови: якщо в таблиці не заповнено виторг для магазину OR не розраховано підсумковий квартальний виторг, тоді потрібно поставити прочерк, в іншому випадку — розділити виторг для магазину на підсумковий.
Щоб у клітинці відобразилися відсотки, необхідно на вкладці Основне задати формат клітинки — відсотковий. У результаті отримаємо уявлення про те, яку частину доходу приносить кожен із магазинів.
Якщо нам потрібно візуально розмежувати клітинки, знову повертаємось до заливки. Щоб отримати таку таблицю:
потрібно виділити клітинку (наприклад, D6) і на вкладці Основне — Колір заливки вибрати білий, а далі на цій же вкладці клацнути на Межі і визначити тип лінії:
Щоб форматування перенеслося на інші клітинки, потрібно вибрати відформатовану клітинку (D6), клацнути на пензлик на вкладці Основне і далі виділити потрібний діапазон клітинок (напр., D7:D10).
Тепер порахуємо валовий прибуток компанії. Для цього необхідно розрахувати та заповнити собівартість проданих товарів.
Усі додаткові рядки додаються через Основне — Вставити — Вставити рядки до аркуша, а форматуються за допомогою пензлика.
Приблизний макет з урахуванням доданих рядків:
Додаємо підсумковий рядок Валовий прибуток. Щоб його заповнити, необхідно в клітинках, що відповідають кожному з кварталів, відібрати від отриманого доходу собівартість проданих товарів. Наприклад, у клітинці D19 прописуємо: =D11-D17.
За таким же принципом додаємо рядки з даними про операційні витрати компанії — адміністративні (з/п працівникам, орендна плата, комунальні послуги тощо) та витрати на рекламу.
А також додаємо дані про одноразові витрати. Підсумковий результат — сума операційних та одноразових витрат. Приклад формули:
Далі вносимо дані про податкові витрати. І наприкінці додаємо рядок ЧИСТИЙ ПРИБУТОК, у якому прописуємо формулу: Валовий прибуток — Витрати — Податки:
У підсумку наш P&L має такий вигляд:
Аналізуючи звіт, звертайте увагу на закономірності. Незалежно від того, як часто ви розраховуєте прибутки та збитки — щомісяця чи щоквартально — порівняння даних покаже тенденції вашого бізнесу.
Навіть якщо чистий прибуток приблизно однаковий, звіт допоможе побачити, в якому з періодів валовий прибуток просідає (разом зі скороченням витрат), а в якому — збільшується.
Бажаєте отримувати дайджест статей?
