Как сбалансировать в Excel

Обновлено: 02.07.2024

Балансовые отчеты – это финансовые отчеты, в которых показаны активы, обязательства и собственный капитал компании в любой момент времени. Вот как это сделать в Excel.

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

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

Наконец, есть баланс, который также является одним из основных финансовых отчетов. Вот краткое руководство, которое поможет вам создать собственное приложение в Microsoft Excel.

Что такое бухгалтерский баланс и зачем он нужен?

Балансовый отчет позволит вам увидеть разбивку активов, обязательств и собственного капитала вашей компании. С первого взгляда вы увидите, какая часть компании была получена за счет нераспределенной прибыли, собственного капитала и займов.

Располагая этой информацией, вы можете рассчитать рентабельность инвестиций и различные финансовые коэффициенты. Затем вы можете сравнить эти значения с аналогичными компаниями в той же отрасли. Это даст вам представление об эффективности бизнеса по сравнению с аналогами в отрасли.

1. Выберите время покрытия

Как и в случае с другими финансовыми отчетами, вы должны выбрать период для охвата. Обычно это начинается 1 января и заканчивается 31 декабря.

Однако вы также можете выбрать другой период для расчета, называемый финансовым годом, который начинается 1 октября и заканчивается 30 сентября. Обратите внимание, что любой период, который вы используете здесь, должен соответствовать другим вашим отчетам.

2. Подготовьте свои аккаунты

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

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

3. Создайте файл Excel

После того, как вы узнаете период, который вы охватываете, и у вас есть необходимые значения, пришло время создать файл Excel. Откройте новый файл в Microsoft Excel. Поместите балансовый отчет [название компании] в ячейку A1 для облегчения идентификации.

Оставьте место для форматирования, затем в первом столбце третьей строки напишите Активы. В этом разделе вы будете указывать значения для всего, что есть в вашей компании. Затем в третьем столбце той же строки укажите отчетный финансовый год.

После «Активы» необходимо создать соответствующий раздел «Обязательства и собственный капитал». Обязательства — это суммы, которые компания должна третьим сторонам, включая банки, поставщиков, арендодателей и государство.

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

Однако перед созданием раздела «Обязательства и собственный капитал» необходимо сначала разместить подкатегории «Активы». Таким образом, у вас будет меньше проблем с форматированием.

4. Вставьте свои категории

Активы

У каждого бизнеса и отрасли будут свои уникальные подкатегории активов. Тем не менее, это типичные разделы, которые есть у большинства компаний: «Оборотные активы», «Основные или долгосрочные активы» и «Другие активы». Затем они разбиваются на небольшие категории.

Текущие активы — это активы, которые вы можете быстро ликвидировать. Обычно это денежные средства, дебиторская задолженность, товарно-материальные запасы и краткосрочные инвестиции. С другой стороны, основные или долгосрочные активы труднее конвертировать в валюту. Это может быть недвижимость, офисное оборудование, долгосрочные инвестиции и многое другое.

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

Хотя эти категории применимы к большинству предприятий, у вашей компании может быть уникальная категория активов, поэтому вам следует проверить свои операции, прежде чем принимать ее окончательно.

Обязательства и собственный капитал

Активы, обязательства и собственный капитал имеют три основные подкатегории: текущие обязательства, долгосрочные обязательства и собственный капитал. Как следует из этого термина, текущие обязательства – это обязательства, которые компания должна выполнить либо в течение одного года, либо в течение одного операционного цикла (где один операционный цикл означает время, необходимое для преобразования запасов в продажи).

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

В разделе «Долгосрочные обязательства» вы найдете «Долгосрочная задолженность», «Отложенный подоходный налог» и «Пособия пенсионного фонда», если это требуется по закону.

Наконец, собственный капитал состоит из собственного капитала владельца, то есть суммы, которую вы вкладываете в бизнес. Если вы управляете корпорацией, то и этот раздел, и его подраздел называются Акционерный капитал.

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

5. Добавление ваших ценностей

В разделе «Активы» добавьте значения для каждой подкатегории, чтобы узнать, сколько у вас есть для каждого раздела. Затем вам нужно добавить каждую промежуточную сумму, чтобы получить общую стоимость активов вашей компании.

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

Обратите внимание, что общие значения в разделе «Активы» и в разделе «Обязательства и собственный капитал» должны совпадать. В противном случае в вашем учете могла быть ошибка.

6. Некоторые полезные формулы

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

Коэффициент долга

Это процент долга компании по отношению к ее активам. Формула для этого: общие обязательства / общие активы. Если вы получите значение больше 100%, это означает, что его долг больше, чем все его активы. Компании с высоким коэффициентом также подвержены более высокому риску дефолта, но этот показатель зависит от отрасли.

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

Текущий коэффициент

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

Оборотный капитал

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

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

Отношение активов к собственному капиталу

Отношение активов к собственному капиталу (A/E) рассчитывается путем деления общей суммы собственного капитала на общую сумму активов. Эта формула показывает, сколько компания финансируется владельцами по сравнению с суммой, финансируемой за счет займов.

Компания с высоким коэффициентом A/E может указывать на то, что большая часть ее финансирования поступает от владельцев, а это означает, что у нее не так много обязательств по оплате. Наоборот, низкий коэффициент A/E означает, что большая часть его активов приходится на займы или кредиты.

Если у компании стабильный денежный поток, низкий коэффициент A/E не окажет большого влияния на ее повседневную деятельность. Однако это оставляет его уязвимым для цен, процентов и резких изменений, что лишает их возможности реагировать.

Где найти шаблоны балансовых отчетов

Хотя теперь вы знаете, как составить балансовый отчет, иногда лучше найти шаблон, которому можно следовать. Вы можете сделать это в Excel, нажав «Файл». Перейдите на вкладку «Создать», затем в строке поиска введите «Баланс». После быстрого поиска Excel предложит вам как минимум три шаблона, которые вы можете использовать.

Отслеживайте свой бизнес

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

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

Рисунок 1. Результат формулы

Синтаксис формулы

Общая формула:

=previous_balance + депозит - снятие

previous_balance — баланс за предыдущий период
депозит — денежный доход
вывод средств – снятие наличных.

Настройка наших данных для примера

Давайте посмотрим на структуру данных, которые мы будем использовать. В столбце B у нас есть даты, в столбце C у нас есть описание депозита или вывода средств. В столбце D есть депозиты, а в столбце D — снятие средств. Наконец, в столбце E мы хотим получить текущий баланс.

Рисунок 2. Данные, которые мы будем использовать в примере

Расчет баланса контрольного регистра

В нашем примере мы хотим получить текущий баланс на 08.01.2019 в ячейке F4. Следовательно, мы должны учитывать предыдущий баланс с F3 и ввод и вывод с D4 и E4.

Формула выглядит следующим образом:

Параметр previous_balance — это ячейка F3. Ячейка D4 – это депозит, а E4 – снятие средств.

Чтобы применить формулу, нам нужно выполнить следующие шаги:

Выберите ячейку F4 и нажмите на нее.
Вставьте формулу: =(B3 – C3)/B3
Нажмите клавишу ввода.
Перетащите формулу в другие ячейки столбца, нажав и перетащив маленький значок "+" в правом нижнем углу ячейки.

Рисунок 3. Расчет баланса контрольного регистра

Предыдущий баланс составляет 1500 долларов США, депозитов на 08.01.2019 нет, а вывод составляет 200 долларов США. Таким образом, остаток в ячейке F4 составляет 1300 долларов США.

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

Я создаю регистр чековой книжки, столбец D – депозиты, столбец E – снятие средств, столбец F – баланс. Как мне получить промежуточную сумму в столбце баланса?

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

У меня есть электронная таблица (чековый регистр), которой я пользуюсь много лет. Внезапно заполнение столбца баланса не рассчитывается. Когда я опускаю угол, формула верна, но результаты неверны. В нем указано количество ячеек, которые я вытащил.

Вы, вероятно, столкнетесь с потребностью в промежуточных итогах, если будете иметь дело с какими-либо ежедневными данными.

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

В этом посте мы рассмотрим несколько способов расчета промежуточного итога для ваших ежедневных данных. Мы узнаем, как использовать формулы на листах, сводные таблицы, Power Pivot с DAX и Power query.

Мы также рассмотрим, что происходит с вычислением промежуточной суммы при вставке или удалении строк данных и как обновить результаты.

Получите файл со всеми примерами.

Подсчет итогов с помощью простой формулы

Основную формулу промежуточного итога можно создать с помощью оператора +.

< бр />

Однако для выполнения этой задачи нам потребуется использовать две разные формулы.

=C3 будет первой формулой и будет только в первой строке промежуточной суммы.
=C4+D3 будет во второй строке и может быть скопировано в оставшиеся строки для промежуточного итога.

Что происходит с промежуточным итогом, когда мы вставляем или удаляем строки в наших данных?

Вставка новой строки приведет к пробелу в промежуточной сумме. Чтобы исправить это, нам нужно скопировать формулу из первой ячейки над вновь вставленными строками до последней строки.

Подсчет итогов с помощью формулы SUM

Мы можем избежать неудобного использования двух разных формул в нашем столбце промежуточных сумм, используя функцию СУММ вместо оператора +. Когда функция СУММ встречает текстовую ячейку, она обрабатывает ее так же, как если бы она содержала 0.

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

Эта формула будет ссылаться на заголовок столбца, содержащий текст для первой строки, но это нормально, поскольку он обрабатывается как 0.

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

Промежуточные итоги с частично фиксированным диапазоном

Еще один вариант с функцией СУММ – ссылаться только на столбец "Продажи" и использовать ссылку на частично фиксированный диапазон.

Если мы используем следующую формулу =СУММ($C$3:C3) , мы можем скопировать и вставить ее вниз по диапазону. Он не будет ссылаться ни на какие заголовки столбцов, а указанный диапазон будет увеличиваться с каждой строкой.

К сожалению, здесь тоже будут те же проблемы (и решения) с вставкой или удалением строк.

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

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

Это трюк, который включает временное переключение стиля ссылок Excel с A1 на R1C1. Затем определение именованного диапазона с использованием нотации R1C1. Затем переключите базовый стиль обратно на A1.

В стиле ссылки R1C1 ссылки на ячейки определяются тем, насколько далеко они находятся от ячейки, использующей ссылку. Например, в этой формуле =R[-2]C[3] относится к ячейке на 2 вверх и на 3 справа от ячейки.

Мы можем использовать эту относительную ссылку, чтобы создать именованный диапазон, который всегда находится на одну ячейку выше ссылающейся ячейки с помощью формулы =R[-1]C .

< бр />

Чтобы изменить стиль ссылки, перейдите на вкладку "Файл" и выберите "Параметры". Перейдите в раздел «Формула» в меню «Параметры Excel», установите флажок «Стиль ссылок R1C1» и нажмите кнопку «ОК».

Теперь мы можем добавить наш именованный диапазон. Перейдите на вкладку "Формула" на ленте Excel и выберите команду "Определить имя".

Вставьте имя, например «Выше», в качестве имени диапазона. Добавьте формулу =R[-1]C в поле Относится к вводу и нажмите кнопку OK.

Теперь мы можем переключить Excel обратно на стиль ссылки по умолчанию. Перейдите на вкладку «Файл» > «Параметры», раздел «Формула» > снимите флажок «Стиль ссылок R1C1» > затем нажмите кнопку «ОК».

Теперь мы можем использовать формулу =СУММ([@Продажи],выше) в нашем столбце промежуточных сумм.

Именованный диапазон выше всегда будет ссылаться на ячейку непосредственно выше. Когда мы вставляем или удаляем строки, соответствующий именованный диапазон корректируется соответствующим образом, и никаких действий не требуется.

На самом деле, если мы поместим наши данные в таблицу Excel, формула будет автоматически заполняться для любых новых строк, поскольку формула едина для всего столбца. Для копирования каких-либо формул не требуется никаких действий.

Подсчет итогов с помощью сводной таблицы

Сводные таблицы очень удобны для обобщения данных любого типа. В них есть нечто большее, чем просто сложение, подсчет и нахождение средних значений. Есть много других встроенных типов вычислений, и на самом деле есть вычисление промежуточной суммы!

Во-первых, нам нужно вставить сводную таблицу на основе данных. Выберите ячейку внутри данных, перейдите на вкладку «Вставка» и выберите команду «Сводная таблица». Затем перейдите в окно «Создать сводную таблицу», чтобы выбрать, где вы хотите создать сводную таблицу: на новом листе или где-то на существующем.

< бр />

Добавьте поле «Дата» в область «Строки» сводной таблицы, затем добавьте поле «Продажи» в область «Значения» сводной таблицы. Теперь добавьте еще один экземпляр поля "Продажи" в область "Строки".

Теперь у нас должно быть два одинаковых поля "Продажи", одно из которых будет называться "Сумма продаж2". Мы можем переименовать эту метку в любое время, просто набрав поверх нее что-то вроде Текущий итог.

Щелкните правой кнопкой мыши любое значение в поле "Сумма продаж2" и выберите "Показать значение как", затем выберите "Промежуточный итог".

Мы хотим отобразить промежуточный итог по дате, поэтому в следующем окне нам нужно выбрать дату в качестве базового поля.

Вот и все, теперь у нас есть новый расчет, который отображает промежуточную сумму наших продаж в сводной таблице.

Что произойдет, если мы добавим или удалим строку в наших исходных данных, как это повлияет на промежуточный итог? Расчеты сводной таблицы являются динамическими и будут учитывать любые новые данные при расчете промежуточного итога, нам просто нужно обновить сводную таблицу.

Щелкните правой кнопкой мыши в любом месте сводной таблицы и выберите в меню "Обновить".

Подсчет итогов с помощью показателей Power Pivot и DAX

Первая пара шагов для этого точно такая же, как при использовании обычной сводной таблицы.

Выберите ячейку внутри данных, перейдите на вкладку «Вставка» и выберите команду «Сводная таблица».

< бр />

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

Поместите поле "Дата" в область "Строки", а поле "Продажи" - в область "Значения" сводной таблицы.

С Power Pivot нам нужно будет создать любые дополнительные вычисления, которые мы хотим, используя язык DAX. Щелкните правой кнопкой мыши имя таблицы в окне «Поля сводной таблицы», затем выберите «Добавить меру», чтобы создать новый расчет. Обратите внимание, что это доступно только для модели данных.

< бр />

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

В окне меры нам нужно добавить имя меры. В этом случае мы можем назвать новый показатель Промежуточный итог.
Нам также нужно добавить указанную выше формулу в поле "Формула".
Самое интересное в Power Pivot — возможность присваивать показателю числовой формат. Мы можем выбрать формат валюты для нашей меры. Всякий раз, когда мы используем эту меру в сводной таблице, формат будет применяться автоматически.

Нажмите кнопку OK, и новая мера будет создана.

В окне "Поля сводной таблицы" появится новое поле. Слева у него есть небольшой значок fx, обозначающий, что это показатель, а не обычное поле данных.

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

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

Подсчет итогов с помощью Power Query

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

< бр />

Сначала нам нужно импортировать таблицу в power query. Выберите таблицу данных, перейдите на вкладку «Данные» и выберите параметр «Из таблицы/диапазона». Откроется редактор мощных запросов.

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

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

< бр />

Нам нужно добавить индексный столбец. Это будет использоваться в расчете промежуточной суммы позже. Перейдите на вкладку "Добавить столбец" и нажмите маленькую стрелку рядом со столбцом индекса, чтобы вставить индекс, начинающийся с 1, в первую строку.

Нам нужно добавить новый столбец в наш запрос для расчета промежуточной суммы. Перейдите на вкладку "Добавить столбец" и выберите команду "Пользовательский столбец".

< бр />

Мы можем назвать столбец "Промежуточный итог" и добавить следующую формулу.

Функция List.Range создает список значений из столбца "Продажи", начиная с 1-й строки (0-й элемент), который охватывает несколько строк на основе значения в индексном столбце.

Затем функция List.Sum складывает этот список значений, который является нашей промежуточной суммой.

Нам больше не нужен индексный столбец, он выполнил свою задачу, и мы можем его удалить. Щелкните правой кнопкой мыши заголовок столбца и выберите Удалить из вариантов.

У нас есть промежуточный итог, и мы закончили работу с редактором запросов. Мы можем закрыть запрос и загрузить результаты в новый рабочий лист. Перейдите на вкладку "Главная" редактора запросов и нажмите кнопку "Закрыть и загрузить".

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

С помощью необязательного шага сортировки, описанного выше, если мы добавим даты в исходные данные не по порядку, power query отсортирует по дате и вернет правильный порядок по дате для промежуточного итога.

Выводы

Существует множество различных вариантов расчета промежуточных итогов в Excel.

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

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

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

Читайте также: