Как посчитать сальдо
Сальдо – это разница между доходом и расходом за выбранный интервал времени. В бухгалтерском учете – это разница между дебетом и кредитом по выбранному счету и за определенный промежуток времени.
Инструкция
Чтобы посчитать данную величину, например, по денежным поступлениям, нужно сложить все поступившие денежные средства за определенный интервал времени и расход этих средств за этот же период. И посчитать разницу между этими двумя цифрами. Она и будет являться сальдо.
Сальдо на начала периода – это остаток денежные средств на начало выбранного периода.
Чтобы посмотреть сальдо в бухгалтерском учете нужно сформировать оборотно-сальдовую ведомость по определенному счету и за определенный период времени. Также можно сформировать «Отчет по прибылям и убыткам» (форма №2) и посмотреть начальное и конечно сальдо.
Существуют формулы, которые используют для расчета сальдо активных и пассивных счетов:
Сальдо конечное по Дебету= Сальдо начальное + Обороты по дебету – Обороты по кредету Сальдо конечное по Кредету = Сальдо начальное + Обороты по кредету – Обороты по дебету
Данная разница очень удобна при составлении актов сверок с контрагентами организации.
Онлайн‑бухгалтерия без авралов и рутины
Ведите учет в удобной онлайн-бухгалтерии: учет, расчет зарплаты и пособий, отправка отчетности в ФНС, СФР, Росстат.
Попробовать
Сегодня все отчеты формируются в бухгалтерской программе. И баланс — не исключение. Такой подход экономит время за счет того, что система заполняет форму автоматически на основании операций, которые бухгалтер заносил в программу весь год.
Однако ошибки не исключены: один бухгалтер забыл сделать проводку, другой перепутал счета, третий не провел операцию и так далее. С автоматическим режимом все эти ошибки останутся незаметными. Поэтому опытные бухгалтеры сверяют баланс, сформированный в учетной базе, с отчетом, собранным вручную по данным из оборотно-сальдовой ведомости.
Оборотно-сальдовая ведомость (ОСВ) — это бухгалтерский регистр, который отражает информацию по счетам бухучета:
- об остатках на начало периода;
- об оборотах в рамках периода;
- об остатках на конец периода.
Порядок заполнения ОСВ не регламентирован ни одним законодательным документом. В основе его заполнения лежит принцип двойной записи по счетам бухгалтерского учета.
Выглядит ОСВ следующим образом. Для удобства каждый отдельный счет можно дополнительно разбить по субсчетам.
| Счет | Остатки на начало периода | Обороты за период | Остатки на конец периода | |||
| Дт | Кт | Дт | Кт | Дт | Кт | |
| 01 | ||||||
| 02 | ||||||
| … | ||||||
| 99 | ||||||
| ИТОГО | (А) | (Б) | (В) | (Г) | (Д) | (Е) |
Ведомость заполнена верно, если соблюдены все три равенства одновременно:
- сумма по дебетовым остаткам на начало периода равна сумме кредитовых остатков на начало периода (А = Б);
- сумма оборотов по дебету равна сумме оборотов по кредиту (В = Г);
- сумма по дебетовым остаткам на конец периода равна сумме кредитовых остатков на конец периода (Д = Е).
ОСВ связана с бухгалтерским балансом. Остатки на конец периода из оборотки переносятся в соответствующие строки бухгалтерского баланса. Но чтобы правильно это сделать, нужно знать соотношение строк бухбаланса со счетами бухгалтерского учета.
Например, ООО «Альфа» на 1 января 2022 года имеет на расчетном счету 1 500 тыс. руб. Поступило на счет за весь 2023 год 5 000 тыс. руб. Потрачено со счета за этот же период — 3 500 тыс. рублей. Остаток по расчетному счету на 31 декабря 2022 года — 3 000 тыс. руб.
ОСВ счета 51 будет выглядеть следующим образом.
| Счет | Остатки на начало периода | Обороты за период | Остатки на конец периода | |||
| Дт | Кт | Дт | Кт | Дт | Кт | |
| 51 | 1 500 | – | 5 000 | 3 500 | 3 000 | – |
Правильно разнести остатки счетов по строкам бухбаланса можно только в том случае, если понятно, какой счет перед нами: активный, пассивный или активно-пассивный.
Активные счета
Активные счета нужны для отражения информации об активах организации. По дебету отражают увеличение стоимости имущества компании, а по кредиту — его выбытие. Остаток по активному счету может быть только дебетовый.
Например, счет 51 «Расчетные счета» аккумулирует информацию о денежных средствах организации на банковском счете. Поступление денег мы отразили по дебету, а расход — по кредиту. Остаток может быть только дебетовый или нулевой, уйти в минус по расчетному счету нельзя.
Дебетовые остатки таких счетов переносят в первый и второй раздел бухгалтерского баланса.
Автоматически рассчитывайте и платите налоги в веб-сервисе Контур.Бухгалтерия! Учет, зарплата, отчетность.
Попробовать бесплатно
Пассивные счета
Пассивные счета раскрывают сведения об обязательствах организации. По кредиту они увеличиваются, а по дебету уменьшаются. Остаток по таким счетам может быть только кредитовый.
Например, ООО «Альфа» получила краткосрочный кредит в размере 2 500 тыс. руб. Сумму займа бухгалтер должен отразить по кредиту счета 66 «Расчеты по краткосрочным кредитам и займам». А его погашение — по дебету данного счета.
В бухбалансе кредитовые остатки пассивных счетов переносят в третий, четвертый и пятый разделы.
Активно-пассивные счета
Активно-пассивные счета раскрывают информацию как об активах, так и об обязательствах организации. Зависит от остатка на конец периода, если дебетовый — это актив, если кредитовый — это обязательство.
Например, счет 62 «Расчеты с покупателями и заказчиками» служит для отражения состояния расчетов с клиентами. Если ООО «Альфа» отгрузит покупателю товар на 500 тыс. рублей в долг, то по счету 62 будет сформирован дебетовый остаток в виде дебиторской задолженности — это актив. А если ООО «Альфа» получит от покупателей аванс на 500 тыс. рублей, то это будет кредиторская задолженность — пассив.
Остатки по активно-пассивным счетам могут быть как дебетовые, так и кредитовые. Первые попадают в первый и второй разделы бухбаланса, а вторые — в третий, четвертый и пятый.
По активно-пассивным счетам есть два вида сальдо:
- развернутое — это когда дебетовые и кредитовые остатки показаны отдельно;
- результирующее — это итоговая разница между кредитовым и дебетовым остатком.
При заполнении бухбаланса используют именно развернутое сальдо. Так как остаток по дебету нужно перенести в актив, а по кредиту — в пассив.
При составлении бухгалтерского баланса по остаткам из ОСВ ориентируйтесь на таблицу ниже. В ней мы показали, как рассчитать значение каждой строки.
| Наименование строки бухгалтерского баланса | Код строки | Бухгалтерские счета |
| Раздел 1. Внеоборотные активы | ||
| Нематериальные активы | 1110 | Дт. 04 — Кт. 05 |
| Результаты исследований | 1120 | Дт. 04 (в части НИОКР) |
| Нематериальные поисковые активы | 1130 | Дт. 08 — Кт. 05 (в части поисковых активов) |
| Материальные поисковые активы | 1140 | Дт. 08 — Кт. 02 (в части поисковых активов) |
| Основные средства | 1150 | Дт. 01 — Кт 02 + Дт. 08 |
| Доходные вложения в матценности | 1160 | Дт. 03 — Кт. 02 (в части доходных вложений) |
| Долгосрочные денежные вложения | 1170 | Дт. 58 (долгосрочные вложения) – Кт. 59 + Дт. 73.1 (по долгосрочным процентным займам работникам) |
| Отложенные налоговые активы | 1180 | Дт. 09 |
| Прочие внеоборотные активы | 1190 | Дт. 07 + Дт. 08 + Дт. 97 (расходы со сроком списания более 12 месяцев после отчетной даты) |
| Раздел 2. Оборотные активы | ||
| Запасы | 1210 | Дт. 10 + Дт. 11 — Кт. 14 + Дт. 15 + Дт. 16 + Дт. 20 + Дт. 21 + Дт. 23 + Дт. 28 + Дт. 29 + Дт. 41 — Кт. 42 + Дт. 44 + Дт. 45 + Дт. 97 (расходы со сроком списания не более 12 месяцев после отчетной даты) |
| Налог на добавленную стоимость по приобретенным ценностям | 1220 | Дт. 19 |
| Дебиторская задолженность | 1230 | Дт. 46 + Дт. 60 + Дт. 62 — Кт. 63 + Дт. 69 + Дт. 69 + Дт. 70 + Дт. 71 + Дт. 73 + Дт. 75 + Дт. 76 |
| Финансовые вложения | 1240 | Дт. 58 (краткосрочные вложения) – Кт. 59 + Дт. 55 + Дт. 73 |
| Деньги и денежные эквиваленты | 1250 | Дт. 50 (кроме 50.3) + Дт. 51 + Дт. 52 + Дт. 55 (кроме 55.3) + Дт. 57 |
| Прочие оборотные активы | 1260 | Дт. 50.3 + Дт. 94 |
| Раздел 3. Капитал и резервы | ||
| Уставный капитал | 1310 | Кт. 80 |
| Собственные акции, выкупленные у акционеров | 1320 | Дт. 81 |
| Переоценка внеоборотных активов | 1340 | Кт. 83 (в части дооценки) |
| Добавочный капитал | 1350 | Кт. 83 (в части добавочного капитала) |
| Резервный капитал | 1360 | Кт. 82 |
| Нераспределенная прибыль (непокрытый убыток) | 1370 | Кт. 99 + Кт. 84; Дт. 99 + Дт. 84; Кт. 84 — Дт. 99; Кт. 99 — Дт. 84 |
| Раздел 4. Долгосрочные обязательства | ||
| Заемные средства | 1410 | Кт. 67 (по задолженности сроком более 12 месяцев) |
| Отложенные налоговые обязательства | 1420 | Кт. 77 |
| Оценочные обязательства | 1430 | Кт. 96 (по обязательствам более 12 месяцев) |
| Прочие обязательства | 1450 |
Кт. 60 + Кт. 62 + Кт. 68 + Кт. 69 + Кт. 76 + Кт. 86 (по задолженности сроком более 12 месяцев) |
| Раздел 5. Краткосрочные обязательства | ||
| Заемные средства | 1510 | Кт. 66 + Кт. 67 (в части обязательств менее 12 месяцев) |
| Кредиторская задолженность | 1520 |
Кт. 60 + Кт. 62 + Кт. 68 + Кт. 69 + Кт. 70 + Кт. 71 + Кт. 73 + Кт. 75 + Кт. 76 (по задолженности сроком менее 12 месяцев) |
| Доходы будущих периодов | 1530 | Кт. 98 |
| Оценочные обязательства | 1540 | Кт. 96 (по обязательствам не более 12 месяцев) |
| Прочие обязательства | 1550 | Кт. 86 (по обязательствам не более 12 месяцев) |
Бухбаланс заполнен правильно, если соблюдено основное равенство бухгалтерского учета: актив равен пассиву, то есть:
Раздел 1 + Раздел 2 = Раздел 3 + Раздел 4 + Раздел 5
Разберем заполнение бухгалтерского баланса на примере небольшой организации. Допустим, за 2022 год ООО «Альфа»:
- продало покупателям товаров на сумму 4 500 тыс. руб, себестоимость товаров на складе — 2 000 тыс. руб;
- получило 4 000 тыс. руб от покупателей на расчетный счет;
- получило беспроцентный займ на 9 месяцев в размере 10 000 тыс. руб, по состоянию на 31 декабря 2022 года его не вернули;
- купило товаров для перепродажи на 5 000 тыс. руб, оплату сразу перечислили поставщику;
- начислило амортизацию основных средств в размере 300 тыс. руб;
- получило от покупателей предоплату в размере 3 000 рублей.
Остатки по счетам на 31 декабря 2021 года:
- уставный капитал — 10 тыс. руб;
- первоначальная стоимость основных средств — 1 500 тыс. руб.
- денежные средства на расчетном счете — 3 200 тыс. руб;
- остатки товаров для перепродажи — 2 000 тыс. руб;
- накопленная амортизация — 300 тыс. руб;
- нераспределенная прибыль — 6 390 тыс. руб.
Построим проводки, которые должен был сделать бухгалтер ООО «Альфа» в рамках года.
| Дт | Кт | Сумма | Описание |
| 62 | 90 | 4 500 | Отразили реализацию товаров покупателям |
| 90 | 41 | 2 000 | Списали себестоимость реализованных товаров |
| 51 | 62 | 4 000 | Получили частичную оплату от покупателей на расчетный счет |
| 51 | 66 | 10 000 | Получили беспроцентный заем |
| 41 | 60 | 5 000 | Поступили товары от поставщика |
| 60 | 51 | 5 000 | Оплатили товары, полученные от поставщика |
| 26 | 02 | 300 | Начислили амортизацию на офисное оборудование |
| 51 | 62 | 3 000 | Получили предоплату от покупателей |
| 90 | 26 | 300 | Списали общехозяйственные расходы |
| 90 | 99 | 2 200 | Отразили финансовый результат в виде прибыли |
| 99 | 84 | 2 200 | Провели реформацию баланса. Признали нераспределенную прибыль отчетного периода |
ОСВ выглядит следующим образом.
| Счет | Остатки на начало периода | Обороты за период | Остатки на конец периода | |||
| Дт | Кт | Дт | Кт | Дт | Кт | |
| 01 | 1 500 | – | – | – | 1 500 | – |
| 02 | – | 300 | – | 300 | – | 600 |
| 41 | 2 000 | – | 5 000 | 2 000 | 5 000 | – |
| 26 | – | – | 300 | 300 | – | – |
| 51 | 3 200 | – | (4 000 + 10 000 + 3 000) = 17 000 | 5 000 | 15 200 | – |
| 60 | – | – | 5 000 | 5 000 | – | – |
| 62 | – | – | 4 500 | (4 000 + 3 000) = 7 000 | 500 | 3 000 |
| 66 | – | – | – | 10 000 | – | 10 000 |
| 80 | – | 10 | – | – | – | 10 |
| 84 | – | 6 390 | – | 2 200 | – | 8 590 |
| 90 | – | – | 4 500 | 4 500 | – | – |
| 99 | – | – | 2 200 | 2 200 | – | – |
| ИТОГО | 6 700 | 6 700 | 38 500 | 38 500 | 22 200 | 22 200 |
Теперь заполним бухгалтерский баланс по состоянию на 31 декабря 2021 года и на 31 декабря 2022 года.
| Наименование строки бухгалтерского баланса | Код строки | На 31.12.2022 | На 31.12.2021 |
| Раздел 1. Внеоборотные активы | |||
| Нематериальные активы | 1110 | 0 | 0 |
| Результаты исследований и разработок | 1120 | 0 | 0 |
| Нематериальные поисковые активы | 1130 | 0 | 0 |
| Материальные поисковые активы | 1140 | 0 | 0 |
| Основные средства | 1150 | (1 500 — 600) = 900 | (1 500 — 300) = 1 200 |
| Доходные вложения в материальные ценности | 1160 | 0 | 0 |
| Долгосрочные финансовые вложения | 1170 | 0 | 0 |
| Отложенные налоговые активы | 1180 | 0 | 0 |
| Прочие внеоборотные активы | 1190 | 0 | 0 |
| ИТОГО по разделу 1 | 1100 | 900 | 1 200 |
| Раздел 2. Оборотные активы | |||
| Запасы | 1210 | 5 000 | 2 000 |
| Налог на добавленную стоимость по приобретенным ценностям | 1220 | 0 | 0 |
| Дебиторская задолженность | 1230 | 500 | 0 |
| Финансовые вложения | 1240 | 0 | 0 |
| Денежные средства и денежные эквиваленты | 1250 | 15 200 | 3 200 |
| Прочие оборотные активы | 1260 | 0 | 0 |
| ИТОГО по разделу 2 | 1200 | 20 700 | 5 200 |
| БАЛАНС (актив) | 1600 | 21 600 | 6 400 |
| Раздел 3. Капитал и резервы | |||
| Уставный капитал | 1310 | 10 | 10 |
| Собственные акции, выкупленные у акционеров | 1320 | 0 | 0 |
| Переоценка внеоборотных активов | 1340 | 0 | 0 |
| Добавочный капитал | 1350 | 0 | 0 |
| Резервный капитал | 1360 | 0 | 0 |
| Нераспределенная прибыль (непокрытый убыток) | 1370 | 8 590 | 6 390 |
| ИТОГО по разделу 3 | 1300 | 8 600 | 6 400 |
| Раздел 4. Долгосрочные обязательства | |||
| Заемные средства | 1410 | 0 | 0 |
| Отложенные налоговые обязательства | 1420 | 0 | 0 |
| Оценочные обязательства | 1430 | 0 | 0 |
| Прочие обязательства | 1450 |
0 |
0 |
| ИТОГО по разделу 4 | 1400 | 0 | 0 |
| Раздел 5. Краткосрочные обязательства | |||
| Заемные средства | 1510 | 10 000 | 0 |
| Кредиторская задолженность | 1520 |
3 000 |
0 |
| Доходы будущих периодов | 1530 | 0 | 0 |
| Оценочные обязательства | 1540 | 0 | 0 |
| Прочие обязательства | 1550 | 0 | 0 |
| ИТОГО по разделу 5 | 1500 | 13 000 | 0 |
| БАЛАНС (пассив) | 1700 | 21 600 | 6 400 |
В оборотно-сальдовой ведомости соблюдено равенство — дебетовые остатки и обороты равны кредитовым Следовательно ведомость заполнена верно. Аналогичный вывод можно сделать и по балансу, так как актив равен пассиву.
Для примера мы использовали малое количество вводной информации. На практике операций обычно гораздо больше: есть начисление зарплаты, удержание и уплата налогов, начисление процентов по кредитам, формирование резервов и так далее. Однако алгоритм заполнения от количества проводок не меняется.
Рекомендуем вам облачный сервис Контур.Бухгалтерия. Система автоматически заполнит бухгалтерскую отчетность за любой период. А для того, чтобы ее проверить, вы всегда сможете сформировать в сервисе оборотно-сальдовую ведомость.
Онлайн‑бухгалтерия без авралов и рутины
Ведите учет в удобной онлайн-бухгалтерии: учет, расчет зарплаты и пособий, отправка отчетности в ФНС, СФР, Росстат.
Попробовать
Со сводными таблицами Excel и диаграммами Power BI легко работать, потому что в них по умолчанию настроено суммирование по полям: перетащил и готово. Если требуется не сумма, а, например, среднее значение или максимум — тоже не проблема.
Достаточно щелкнуть по полю правой кнопкой мыши и выбрать нужное вычисление. Другое дело, если вам в сводной таблице нужно вывести остатки. Представьте себе: вы добавляете в сводную таблицу остатки дебиторской задолженности по месяцам. Что должно быть в итоге за год? Правильно, значение на 31 декабря. А что выдаст сводная таблица? Сумму остатков по месяцам! И в данном случае автосуммирование уже мешает. Я встречал «решение» – отключить итоги. Согласитесь, это не вариант.
А ведь выводить остатки требуется во многих отчетах:
- запасы товаров на складах;
- дебиторская задолженность покупателей;
- ДДС (CF) – движение денежных средств;
- задолженность перед поставщиками и бюджетом;
- численность персонала компании;
- сальдо баланса.
И хорошо бы иметь возможность все эти отчеты строить с помощью сводных таблиц Excel и визуализировать в Power BI. Вопрос – можно ли это сделать, если там данные только суммируются? Да, можно, если пользоваться специальными DAX-формулами, доступными в Power Pivot для Excel и в Power BI.
В этой статье разберём, какие DAX-формулы и в каких случаях выбирать. Выбор подходящей формулы зависит от того, где вы берёте остатки: они где-то есть или вы их считаете сами. А также от поведения самих остатков — нужно чтобы они отображались на последний день периода или «переносились» на следующие даты. Отсюда способы расчёта остатков.
- Данные по остаткам у вас уже есть и вам их просто нужно добавить в отчет:
Способ 1. CLOSINGBALANCE и OPENINGBALANCE.
Способ 2. LASTNONBLANK и FIRSTNONBLANK.
- Остатки нужно рассчитать по формуле: входящие остатки + поступления – выбытия:
Способ 3. TOTALYTD и формулы нарастающих итогов.
- Подстановка остатков по дням и «перенос» внутри периодов:
Способ 4. Формулы с LASTNONBLANK.
Начнём с ситуаций, когда информация об остатках у вас уже есть, например, в выгрузке из учетной системы. И в зависимости от того, как выглядят данные, вы можете выбрать разные способы вычислений.
Способ 1. CLOSINGBALANCE и OPENINGBALANCE
Остатки на конец периода
Группа формул CLOSINGBALANCE (MONTH, QUARTER, YEAR) – формулы «закрытия баланса». Они точно пригодятся в отчёте по остаткам. CLOSINGBALANCEMONTH определяет значение на конец месяца, CLOSINGBALANCEQUARTER (YEAR) – на конец квартала, года.
Когда применять эти формулы. Если у вас есть данные об остатках, а в отчёте нужно показать итоги за период.
Формула для остатков на конец месяца:
сальдо =
CLOSINGBALANCEMONTH (
SUM ( 'данные'[остатки] ),
'Календарь'[Date]
)
Результат вычислений получается аналогичным выражению с ENDOFMONTH.
сальдо =
CALCULATE (
SUM ( 'данные'[остатки] ),
ENDOFMONTH ( 'Календарь'[Date] )
)
Формула ENDOFMONTH (QUARTER, YEAR) находит дату с последним днём месяца, квартала, года.
На что обратить внимание. На вычисления CLOSINGBALANCE влияет то, какой именно столбец с датами указан в DAX-формуле. Так, при вычислениях по кварталам при подстановке дат из справочника дат (а) или сразу из таблицы данных (б) получится два варианта итогов.
а) даты из справочника дат
сальдо =
CLOSINGBALANCEQUARTER (
SUM ( 'данные'[остатки] ),
'Календарь'[Date]
)
Если подставить в формулу даты из Календаря, то итоги квартала будут дублироваться в каждом месяце. Если взять даты из таблицы с данными, получится более компактное представление с одним месяцем в квартале.
б) даты из таблицы данных
сальдо =
CLOSINGBALANCEQUARTER (
SUM ( 'данные'[остатки] ),
'данные'[дата]
)
- Остатки на начало периода
Остатки на начало периода считаются так же просто, как в предыдущих примерах (но есть нюанс).
Вычисления можно сделать с помощью формул «открытия баланса» OPENINGBALANCE (MONTH, QUARTER, YEAR).
OPENINGBALANCEMONTH определяет значение на начало месяца, OPENINGBALANCEQUARTER – на начало квартала, OPENINGBALANCEYEAR – начало года.
Формула для входящих остатков на начало месяца:
сальдо =
OPENINGBALANCEMONTH (
SUM ( 'данные'[остатки] ),
'Календарь'[Date]
)
В вычислениях с OPENINGBALANCE есть небольшой нюанс: результатом будут «переходящие» остатки, то есть цифры на конец предыдущего месяца.
Данные на 1-е число не принимаются во внимание: на рисунке видно, что в июне остатки на 31 мая.
На что обратить внимание. В примерах выше для OPENINGBALANCE и при «закрытии баланса» CLOSINGBALANCE, в таблицах с исходными данными должны быть указаны значения на конец периода, потому что все остальные даты в вычислениях игнорируются. Если данных на конец периода нет, то формула выдаст пустое значение.
Если в вашем сценарии расчета остатки на начало месяца берутся на 1-е число (а не «переходящие» остатки из предыдущего месяца), то вам больше подойдет формула STARTOFMONTH.
сальдо =
CALCULATE (
SUM ( 'данные'[остатки] ),
STARTOFMONTH ( 'Календарь'[Date] )
)
STARTOFMONTH определяет дату начала месяца.
В отличие от примера c OPENINGBALANCE, в итоговую таблицу попадают данные на 1-е число.
Способ 2. LASTNONBLANK и FIRSTNONBLANK
LASTNONBLANK – практически незаменимая формула для моделирования остатков. С её помощью можно найти последнее «непустое» значение. Более компактный вариант вычислений получается с формулой LASTNONBLANKVALUE.
Когда применять. Вычисления с LASTNONBLANK хорошо работают, когда нужно найти последнее «непустое» значение, например покупку клиента, коэффициент или индекс, который вступил в действие с указанной даты.
В случае с товарами такая логика подходит, когда остатки в таблице с исходными данными как бы «зафиксированы» до даты изменения. Нулевые остатки в этом случае явно обозначаются: как только остаток становится равным нулю, в таблице с исходными данными должен быть указан «ноль» ( = 0).
Для правильной работы выражения с LASTNONBLANK может потребоваться отдельная мера для остатков. Если такую меру не создать, то итоги могут отображаться некорректно.
Формула остатков:
'данные'[остатки] )
Сальдо =
CALCULATE (
[Остатки_мера],
LASTNONBLANK (
'Календарь'[Date],
[Остатки_мера] )
)
Сальдо =
LASTNONBLANKVALUE (
'Календарь'[Date],
SUM ( 'данные'[остатки] )
)
Значения на начало месяца легко определить с помощью с FIRSTNONBLANK или FIRSTNONBLANKVALUE.
Сальдо =
FIRSTNONBLANKVALUE (
'Календарь'[Date],
SUM ( 'данные'[остатки] )
)
На что обратить внимание. Аналитики, которые впервые создают отчеты с формулой LASTNONBLANK, могут столкнуться с некорректным расчетом итогов. Ниже примеры двух вариантов формулы LASTNONBLANK – с мерой и без меры. И результаты, в которых немного отличаются итоговые столбцы.
а) Вычисления с мерой
Сальдо =
CALCULATE (
[Остатки_мера],
LASTNONBLANK (
'Календарь'[Date],
[Остатки_мера] )
)
Все последние непустые значения:
б) Вычисления без меры
Сальдо =
CALCULATE (
SUM ( 'данные'[остатки] ),
LASTNONBLANK (
'Календарь'[Date],
SUM ( 'данные'[остатки] ) )
)
Непустые значения за последний день:
Во втором варианте (б) видно, что в итоговый столбец попали значения только на завершающую дату. В первой таблице (а) приведён корректный вариант вычислений, который получается при использовании LASTNONBLANKVALUE. Тот же результат будет, если в LASTNONBLANK выражение SUM('данные'[остатки]) обернуть формулой CALCULATE и записать строку в таком виде: CALCULATE( SUM( 'данные'[остатки]) )
На что ещё обратить внимание. Ещё одна особенность вычислений с LASTNONBLANK состоит в том, что в общий итог таблицы также попадает последнее итоговое непустое значение (см. рисунок ниже). Если такой сценарий у вас не предусмотрен, воспользуйтесь формулой SUMX для суммирования по строкам.
Способ 3. TOTALYTD и формулы нарастающих итогов
Расчет остатков: входящие остатки + поступления — выбытия
В предыдущих случаях мы брали остатки сразу из таблицы данных. А что делать, если их нужно посчитать по формуле? Например, у нас есть данные на начало периода, поступление и расход. Тогда остатки на конец периода можно найти по формуле:
Остаток на конец = Остаток на начало + Поступление – Расход
Иногда в dashboard-ах такое моделирование делают с помощью сложных выражений. Но есть вариант проще – с помощью формул нарастающих итогов TOTALYTD (MTD, QTD). Более детально про эти формулы вы можете прочесть в статье «Нарастающие итоги с помощью DAX».
Остатки к.п. =
TOTALYTD (
[Остатки на начало] + [Поступление] - [Расход],
'Календарь'[Date]
)
На что обратить внимание. Формулы TOTALYTD (MTD, QTD) хорошо работают с датами внутри года (или месяца, квартала). Эти формулы подходят для сценариев, когда входной остаток задается один раз в начале периода, например в начале года. Если расчёт делается для нескольких лет, то применяются формулы нарастающих итогов с CALCULATE. Такие вычисления можно настроить, например, с помощью быстрых мер:
Остатки годы =
CALCULATE (
[Остатки на начало] + [Поступление] - [Расход],
FILTER (
ALLSELECTED ( ‘Календарь'[Date] ),
ISONORAFTER ( ‘Календарь'[Date], MAX ( ‘Календарь'[Date] ), DESC ) )
)
Способ 4. Формулы с LASTNONBLANK
Стоимость при изменении цен, индексы, курсы валют
Особенность формулы LASTNONBLANK – брать последнее значение без суммирования, можно использовать для решения еще одной проблемы, которая возникает у аналитиков. А именно – для «протягивания» данных.
Что имеется в виду: представьте себе, что в одной таблице у вас курсы валют, а в другой – продажи. Эти таблицы связаны по дате через Календарь. И вам нужно по дням перемножить курс на цены. Вроде ничего сложного, но есть нюанс: курсы валют есть не на каждый день. И вам нужно в дни, когда нет курса валют «протянуть» данные из предыдущего дня (почти как команда «Заполнить вниз» в Power Query, но только в разных таблицах и с помощью DAX).
Для многих аналитиков это сложная задача, на которую они могут потратить много времени и сил. А мы разберём формулу, которая помогает при работе с такими данными:
- курсы валют с датами изменений,
- цены с датой вступления в силу,
- скидки по акциям с датами начала акций,
- индексы и коэффициенты, которые вступают в действие с определенного момента.
В общем, это такие сценарии, где у вас есть дата старта действия какого-то показателя. И этот показатель нужно сопоставить с другими данными в последующие дни.
Давайте разберем, как это всё работает. Например, у нас есть две таблицы:
1) запасы товаров в штуках с датами закупки;
2) цены, которые несколько раз менялись за период.
Нужно найти стоимость запасов по формуле: количество х цена.
1) запасы в штуках
2) цены
изменение цен по дням:
После объединения таблиц в модели данных (не путайте с Power Query) получилось, что даты в первой и второй таблице не совпадают. С помощью простого умножения стоимость посчитать не получается. И если в обычном Excel можно было бы просто вручную проставить ссылки на ячейки, то DAX так не работает.
Что же делать? Мы знаем, что в таблице с ценами указана дата старта действия цены. То есть нужно каким-то способом «перенести» цену на следующую дату. И вот для этого как раз пригодится формула LASTNONBLANK.
Формула для цен с учётом даты старта действия:
Цена утвержденная =
CALCULATE (
LASTNONBLANKVALUE (
'Календарь'[Date],
SUM ( 'цены'[Цена] )
),
'Календарь'[Date] <= MAX ( 'Календарь'[Date] )
)
В результате получится таблица с ценами по всем датам периода.
Посчитать стоимость запасов поможет формула SUMX (подробнее про неё можно прочитать в этой статье).
Кстати, можно настроить компактной вид таблицы без перечисления всех дат. Для этого пригодится выражение IF + NOT ISEMPTY
Цена =
IF (
NOT ISEMPTY ( ‘данные_количество’ ),
[Цена утвержденная]
)
Как видите, с помощью CALCULATE и LASTNONBLANK можно настраивать более сложные способы и сценарии моделирования остатков и не только. Так в модель можно добавить не только цены, но и индексы, коэффициенты и так далее. Например, с TOTALYTD определяем количество, а LASTNONBLANK добавляет в расчёт цену и курс валют.
Резюме
Сводные таблицы Excel и визуализации Power BI – это мощный функционал для анализа и представления данных. И эти инструменты хочется использовать не только для построения отдельных аналитических выкладок и презентаций, но и в подготовке вполне стандартных отчетов. И если отчеты по оборотам, где требуются суммы по полям данных, готовятся быстро и просто, то отчеты по остаткам требуют некоторых знаний DAX-формул.
Но, согласитесь, не так и много этих формул: CLOSING / OPENINGBALANCE, формулы с поиском значений по критерию «NONBLANK» и их сочетания с другими функциями. Зато какие возможности. Теперь вы можете с помощью сводных таблиц не только построить баланс, но и корректно рассчитать финансовые коэффициенты, не переживая, что остатки по месяцам просуммируются на конец года 🙂
ИРИНА ***
Мастер
(1219)
11 лет назад
Сальдо — остаток по бухгалтерскому счёту, разность между суммой записей по дебету и кредиту счетов. Счета, по которым ведется бухгалтерский учет в организации, могут быть трех типов: активные, пассивные и составные активно-пассивные. Соответственно, сальдо по каждому типу счетов считается по разным алгоритмам. Бухгалтерское сальдо состоит из дебета и кредита.
Чтобы посчитать данную величину, например, по денежным поступлениям, нужно сложить все поступившие денежные средства за определенный интервал времени и расход этих средств за этот же период. И посчитать разницу между этими двумя цифрами. Она и будет являться сальдо.
2Сальдо на начала периода – это остаток денежные средств на начало выбранного периода.
3Чтобы посмотреть сальдо в бухгалтерском учете нужно сформировать оборотно-сальдовую ведомость по определенному счету и за определенный период времени. Также можно сформировать «Отчет по прибылям и убыткам» (форма №2) и посмотреть начальное и конечно сальдо.
4Существуют формулы, которые используют для расчета сальдо активных и пассивных счетов:
Сальдо конечное по Дебету= Сальдо начальное + Обороты по дебету – Обороты по кредету
Сальдо конечное по Кредету = Сальдо начальное + Обороты по кредету – Обороты по дебету
Данная разница очень удобна при составлении актов сверок с контрагентами организации.
Юрий Максимов
Оракул
(74559)
11 лет назад
Сальдо – это разница между поступлениями и расходами денежных средств за определенный промежуток времени, а также остаток по счету при сверке взаимных платежей двух или нескольких фирм. Сальдо на начало – остаток денежных средств на начало периода, сальдо на конец – остаток денежных средств на конец периода. (Главбух дал/дала значение слова сальдо).
Александра Белова
Профи
(805)
11 лет назад
Смотря по какому счету вы считаете Если счет Активный, то Сальдо=Сальдо начальное (остаток на начало периода) +Обороты по дебету счета – обороты по кредиту счета, если Пассивный, то Сальдо= Сальдо начальное+обороты по кредиту счета-Обороты по дебету счета
Михаил Коростылев
Мастер
(2397)
11 лет назад
сальдо – остаток.. . все зависит о тсчета. . если счет активный д осальдо так считается сальдо на начало + оборот по дебету-минус оборот по кредиту.. . например 10 счет “МАтериалы” – автивный, было у вас на остатке 10 конфет, вы купили еще 2 конфеты а продали 8 конфет, считается так: 10+2-8=4 сальдо на конец будет дебетовым = 4.. если пассивный счет 60,1 – “Расчеты с поставщиками и подрядчиками” сальдо на начало минус оборот по дебиту плюс оборот по кредиту = сальдо на конец, пример: выдолжны были за конфеты 100 рублей заплатили поставщику 80 рублей, он вам дал еще накладную на 40 рублей. . считаем так: 100-80+40=60 сальдо кредитовое 60 рублей…
Что такое сальдо ведомо не только любому бухгалтеру, но и большинству обывателей – слишком термин распространенный. Не каждый даст точное его определение, но даже так, подсознательно мы осознаем, что это результат вычитания одного из другого. Например, экспорта или импорта. Платежей полученных и отправленных. Разность двух значений. Понятия конечного и сальдо начального ясны, напротив, меньше, но мы остановимся подробнее именно на последнем.
Сальдо начальное – что это
Обычно начальное сальдо – это фактическая остаточная сумма на начало учетного периода, находящаяся на бухгалтерском счету. Необходимо отметить, что запись указанной суммы будет зависеть от свойств счета (активного или пассивного он класса):
- слева (поле дебетовое), когда счет носит характер активного;
- справа (поле кредитовое), когда счет носит характер пассивного.
Прочие особенности, в том числе и расчета данного показателя мы предлагаем рассмотреть далее.
Как найти сальдо начальное?
Есть особенности поиска начального сальдо в зависимости от пассивного или активного типа счёта.
Представим гипотетическую ситуацию, когда мы работаем со счётом №66, который представляет группу пассивных.
Начальное сальдо будет записано справа. Итак, в первой таблице рассмотрим движения по счёту за февраль.
|
Д. |
К. |
||
|
Изначальное С. на 01.02.2023 г. |
219 872 рос. руб. |
||
|
Выплаты внешних заимствований 10.02.2023 г. |
119 000 рос. руб. |
||
|
Открытие новой кредитной линии 12.02.2023 г. |
88 237 рос. руб. |
||
|
Полный расчет по внешним заимствованиям 20.02.2023 года |
100 872 рос. руб. |
||
|
О. |
219 872 рос. руб. |
О. |
88 237 |
|
С. на конец месяца = 219 872 + 88 237 – 219 872 рос. руб. = 88 237 рос. руб. |
Итак, рассмотрев февральскую таблицу, логично задаться вопросом. Если мы рассмотрели обороты и нашли значение на конец месяца, то сальдо на начало периода это просто значение? Берётся из воздуха, а не рассчитывается по формуле? Ответ нет. Все расставит по местам мартовская таблица, в продолжение предыдущей.
|
Д. |
К. |
||
|
Изначальное С. на 01.03.2023 г. |
88 237 рос. руб. |
||
|
Выплаты внешних заимствований 10.03.2023 г. |
19 000 рос. руб. |
||
|
Открытие новой кредитной линии 12.03.2023 г. |
9 999 рос. руб. |
||
|
Полный расчет по внешним заимствованиям 20.03.2023 года |
69 237 рос. руб. |
||
|
О. |
88 237 рос. руб. |
О. |
9 999 |
|
С. на конец марта = 88 237 + 9 999 – 88 237 рос. руб. = 9 999 рос. руб. |
Резюмируя рассмотрение таблиц, обращаем внимание, что значение на начало нынешнего периода – это данные, переносимые с предыдущего. В исследованном случае – сальдо на начало марта – расчетное значение конечного сальдо на февраль (88 237 рос. руб. фигурируют в обоих случаях).
Сальдо начальное по кредиту и дебету зависит строго от вида счета, а просчет записи поля дебета является зеркальным тому, что сделано для предложенного примера в счете №66.
Обнаружили ошибку? Выделите ее и нажмите Ctrl + Enter.
Оцените страницу:
Уточните, почему:
не нашёл то, что искал
содержание не соответствует заголовку
информация сложно подана
другая причина
Расскажите, что вам не понравилось на странице:
Спасибо за отзыв, вы помогаете нам развиваться!
