Таблица Excel для учета доходов и расходов ИП
Содержание
Многие предприниматели сталкиваются с трудностями при управлении бюджетом. Эту задачу поможет решить таблица доходов и расходов в Excel. Она используется для фиксации денежных поступлений и отчислений по различным статьям и категориям. В данной статье мы рассмотрим пример, как можно вести бюджет доходов и расходов, используя только стандартные формулы и инструменты Excel.
Создаем макет таблицы
Успех любого предприятия напрямую зависит от того, насколько эффективен финансовый учет и планирование бюджета. Это, в свою очередь, неразрывно связано с фиксацией текущих доходов и расходов, которые могут носить как единовременный, так и регулярный характер.
По закону, каждый индивидуальный предприниматель обязан вести КУДиР — книгу учета доходов и расходов. В этом документе в хронологическом порядке фиксируются все поступления и выбытия средств с основного или вспомогательного баланса.
Отметим, что для некоторых групп предпринимателей вести реестр расходов необязательно. Тем не менее это очень важно, чтобы контролировать бюджет компании и иметь подробное представление о передвижении средств. Именно для этих целей мы создадим таблицу, с помощью которой можно осуществлять контроль затрат и доходов. Таблица в программе Эксель для учета доходов и расходов для ИП создается довольно просто, главное во время ее подготовки строго соблюдать приведенную в материале инструкцию и не пропускать никакие шаги.
Начнем составление с создания двух макетов:
- Составляете документ в Excel и переименовываете первый лист. В нем будет находиться перечень всех статей доходов и расходов, что позволит подсчитывать прибыль и текущие затраты. Также добавляете второй лист — на нем будет отображаться итоговый баланс.
Создаем документ и оформляем два листа - В первом листе добавляем колонки с названиями «Дата», «Группа», «Статья», «Вид деятельности», «Примечания» и «Сумма». Очевидно, что они будут использоваться для отображения информации об операциях, влияющих на бюджет движения денежных средств.
Оформляем колонки на первом листе - На втором листе нужно будет составить итоговый вариант, который учитывает все доходы и расходы предприятия. Для этого сделаем на листе две колонки. В одной из них фиксируются доходы, в другой, соответственно, расходы. Также отчет включает ячейки, в которых указывается баланс по состоянию на начало и конец месяца.
Оформляем лист для проверки баланса
Цифры в ячейки «Остаток» при создании таблицы БДДС вписывать необязательно. В нашем случае это только образец, поэтому все данные вносятся только в качестве примера. При создании ячеек рекомендуем сразу же использовать инструменты форматирования. С их помощью таблица станет более удобной для заполнения и чтения в дальнейшем.
Формируем зависимые списки
Следующая задача — сделать таблицу более удобной. Для этого добавим функции, которые помогут ускорить заполнение документа. Начнем с создания списков, которые помогут при отображении группы операций и вида деятельности. Для этого:
- В листе, на котором указываются расходы и доходы, выделяем ячейку в колонке «Группа». Далее переходим во вкладку «Данные» и в Панели инструментов выбираем «Проверка данных».
Открываем инструмент проверка данных - В открывшемся окне необходимо настроить параметры. Сначала меняем тип данных на «Список». Далее в поле «Источник» указываем варианты. В нашем случае это группы операций «Доходы» и «Расходы». Их указываем через точку с запятой и нажимаем .
Настраиваем параметры списка - В результате в ячейке будет отображена кнопка, с помощью которой можно выбирать один из двух доступных вариантов — прибыль или расход. Так вы сможете значительно быстрее указать поступление или вычет средств с баланса.Список в ячейке
- Аналогичным образом можно оформить колонку «Вид деятельности». В качестве варианта указываем следующие значения: «Финансовые», «Операционные», «Инвестиционные». Отметим, что если вы создаете простую таблицу, в которой планируете фиксировать приход и расход средств, данная колонка не является обязательной. Тем не менее она может помочь во время формирования отчетной документации фирмы.
Добавляем список для типов операций - Далее переходим к созданию зависимого списка. Для этого во втором листе выделяем диапазон ячеек в колонках «Итого доход» и «Итого расход». Длина диапазона на усмотрение пользователя, в нашем конкретном случае он составляет 150 строк. Каждому диапазону присваиваем соответствующее название — «Доходы» или «Расходы».
Присваиваем названия в диапазонах колонок доход и расход - Далее создаем зависимый список. Это нужно для того, чтобы быстро добавлять в таблицу регулярные статьи расходов. При создании диапазонов в листе «Итоговый баланс» автоматически создаются ссылки на соответствующие ячейки. Остается только привязать их к колонке «Статья» в первом листе. Для этого создаем список и в поле «Источник» используем формулу ДВССЫЛ со ссылкой на ячейку «Группа», в которой ранее был список «Доходы» и «Расходы».
Создаем зависимый список через функцию ДВССЫЛ
Далее нужно убедиться в том, что все работает. Для этого в листе «Итоговый баланс» добавим несколько статей регулярных расходов. Отметим, что в нашем случае это только пример, призванный разобраться в специфике работы такой таблицы для бизнеса. Добавляем по несколько статей в категории «Доходы» и «Расходы».
После этого возвращаемся на лист «Перечень доходов и расходов». Выбираем группу «Доходы». В колонке «Статья» должны отображаются те статьи, которые были указаны в соседнем листе.
Аналогичным образом при смене значения в ячейке «Группа» изменяется и список статей. Это очень удобный способ фиксации в том случае, если поступление или вычет средств осуществляются на регулярной основе. Отсутствует необходимость постоянно делать запись вручную — достаточно выбрать нужный вариант из выпадающего списка.
Заполнение таблицы и дополнительные параметры
Прежде чем перейти к дальнейшей настройке документа, добавим новые статьи дохода и расхода предприятия. В первой колонке обязательно указываем дату. Это можно сделать несколькими способами: ввести вручную, либо с помощью формул «ДАТА()» или «СЕГОДНЯ()». Первый вариант удобен в том случае, если документ заполняется задним числом, а второй — если сегодняшним.
Теперь необходимо сделать так, чтобы подсчитывалась сумма доходов и расходов по каждой статье. Так мы сможем узнать, как были получены или потрачены деньги за определенный бюджетный период. Эту задачу можно решить с помощью функции «СУММЕСЛИ». Порядок действий рассмотрим более детально:
- В листе «Итоговый баланс» выделяем ячейку «Сумма» напротив одной из статей доходов. В нашем случае это строчка «Прибыль от продаж». Далее переходим во вкладку «Формулы» и нажимаем «Вставить функцию». В поиске вводим «СУММЕСЛИ» и выбираем данный вариант.
Находим формулу СУММЕСЛИ - Далее на экране отобразится окно аргументов. В первую очередь необходимо указать диапазон — это те ячейки, по которым формула будет выполнять поиск значений. В нашем случае это перечень статей в первом листе. Для простоты выделяем колонки «Статья», «Вид деятельности», «Примечание» и «Сумма» целиком, кликнув по адресу над названием столбца.
Выделяем диапазон - Далее указываем критерий поиска. Для этого мы прописываем формулу напротив строки «Прибыль от продаж». Поэтому она будет указана в качестве критерия. В поле «Диапазон суммирования» вводим адрес ячейки, где прописана сумма по статьям расходов и доходов.
Вводим дополнительные аргументы - Далее необходимо растянуть эту формулу для остальных ячеек колонки «Итого доход». В результате она будет применена к другим категориям. Если добавить новую статью в перечне на листе 1, она также будет добавлена в итоговый лист.
Результат выполнения - Аналогичную формулу применяем в отношении колонки «Итого расход». Так мы сможем подсчитывать все статьи из данной группы, автоматически суммируя общие затраты предприятия.
Применяем формулу для вкладки расходов
Подсчет итогов
Основной механизм подсчетов прибыли и затрат можно считать реализованным. Теперь же необходимо сделать так, чтобы результаты отображались в полях «Остаток на начало месяца». В качестве примера внесем на изначальный баланс 100000 рублей. Теперь необходимо сделать так, чтобы результаты суммирования по статьям доходов и расходов прибавлялись и отнимались от этой цифры. Это можно сделать несколькими способами. В нашем случае целесообразно использовать именованные диапазоны или функцию «СУММ».
В нашем примере диапазон суммирования распространяется на 150 строк. Поэтому именно такое значение будет указано в формуле. В ячейке «Остаток на конец месяца» необходимо прописать простую конструкцию: от остатка на начало месяца отнимаем сумму по статьям расходов и добавляем сумму по статьям доходов. Выглядит конструкция следующим образом:
Можно воспользоваться и более сложными способами суммирования. Однако этот вариант самый быстрый и простой, поэтому вероятность ошибок сведена к минимуму, что очень удобно для неопытных пользователей Экселя. В результате выполнения формулы в ячейке будет отображаться баланс с учетом всех доходов и расходов.
При добавлении новых статей доходов или расходов баланс будет меняться автоматически. В этом легко убедиться, если добавить несколько позиций в первый лист. В нашем случае мы сначала добавили новую категорию «Другие доходы». Затем добавили две статьи, указав суммы для каждой из них.
Соответствующая категория и сумма сразу же отобразились в листе «Итоговый баланс», а цифра в ячейке «Остаток на конец месяца» обновилась автоматически.
В целом, таблицу для учета движения средств организации, пример которой рассмотрен выше, можно считать готовой. Последнее, на что следует обратить внимание — работа с датами. Заполнение этой ячейки рекомендуется выполнять на основании специального формата даты для Excel или с помощью формул. Это позволит сортировать статьи доходов и расходов по различным датам, используя инструмент фильтрации.
Для этого выделяем колонку с датами. Затем во вкладке «Главная» нажимаем «Сортировка и фильтр». Далее в выпадающем окне выбираем функцию «Фильтр».
При помощи фильтра можно сортировать записи за месяц или за целый год. Для примера, мы добавим несколько прошлогодних позиций. При включении фильтра можно выделить соответствующий год в списке. Это позволяет использовать одну и ту же таблицу, чтобы отслеживать приход и расход средств на протяжении нескольких лет.
В качестве альтернативного варианта можно вести отдельные листы с перечнем доходов и расходов для каждого месяца. Для этого достаточно собрать один образец и скопировать его при помощи инструмента «Переместить или скопировать». Чтобы вызвать эту функцию, наведите курсор на название листа и нажмите правую кнопку мыши.
Подводим итоги
Microsoft Excel — многофункциональный инструмент для работы с данными. Используя эту программу, можно создать электронный документ для ведения учета и бюджетирования мелкого или крупного предприятия. В нашем примере предусмотрена несложная таблица с функцией автоматического определения баланса и подсчета сумм по разным статьям доходов и расходов для ИП. Такой вариант таблицы будет наиболее удобным и простым для пользователей, которые ранее не занимались финансовым учётом в Excel.
Загрузка...
