как быстро сделать акт сверки в экселе
Сверка в Excel – легко и быстро
Нет времени читать?
В выпуске «Прогрессивного бухгалтера» № 3, апрель 2019 г., мы рассмотрели возможности отчета «Акт сверки» в «1С». Но бывают случаи, при которых акт длиной с «Войну и мир» и непрост в понимании. В этом случае на помощь придет Excel.
Почему Excel
Перевод бухгалтерского учета в специализированные программы можно сравнить с переходом из каменного века к веку железному. В современных условиях было бы невозможно вести учет обложившись кипой бумаг или же делая расчеты в различных файлах. Но есть вещи, которые не теряют свою полезность, несмотря на прогресс. Вот и в некоторых вопросах старый добрый Excel не потерял своей полезности в плане сверки данных больших актов, хотя во многих других случаях функциональность «1С» все больше оставляет его «без работы».
Несколько лет назад нам нужно было выровнять взаиморасчеты с поставщиком за три года. 52 548 строк – это были продажи и премии, курсовые разницы и возвраты, взаимозачеты… Сверяли месяц, но итог не шел. У сотрудника уже замылился глаз, тогда эту стопку бумаги передали мне, сказали – осталась неделя. Мне стало скверно, потому что поняла: я неделю его только листать буду, не то что сверять.
Но когда кажется, что выхода нет, к нам приходит вдохновение и свежие мысли. Я запросила акт сверки поквартально в формате Excel и вывела за те же периоды и в том же формате наш. Скопировала данные в один файл и приступила к сортировке и сверке.
Как подготовиться к сверке
Для сверки нам нужно получить 2 колонки – дебет и кредит. Для этого форматируем акт следующим образом: копируем данные из акта сверки – нам нужны только колонки: дата, документ, дебет, кредит – шапка и сальдо не требуются, вставляем их на другой лист книги, далее снимаем объединение ячеек – жмем правой кнопкой по выделенному фрагменту (весь наш акт) и выбираем «Формат ячеек», во вкладке «Выравнивание» убираем все из пункта «Объединение ячеек».
Удаляем пустые столбцы, если строки получились слишком широкими, их высоту можно изменить через «Автоподбор высоты строки».
Далее нам требуется установить фильтр надо всеми столбцами. Для этого выделяем нужные нам столбцы, во вкладке «Главная» в правом углу экрана кликаем «Сортировка и фильтр», выпадает список функций, выбираем «Фильтр».
После этого ставим фильтр надо всеми столбцами.
Сортировка данных в акте
Древние римляне говорили: «Разделяй и властвуй» – в нашем случае тоже можно применить этот метод. Если в вашем акте не только продажи, но и другие операции, то сделайте в фильтре текстовый отбор и разложите их на отдельные листы книги Excel. Допустим, мы хотим сверить только «Поступления товаров и услуг», корректировки сверим потом. Через настраиваемый фильтр отбираем ПТУ и копируем на отдельный лист.
После этого делаем отбор в столбцах с нашими данными, только в поле содержит вбиваем «прих».
Далее выделяем столбец с суммами и делаем сортировку по возрастанию.
Дальнейший отбор и сверка
Появляется окошко, в нем жмем «Сортировка».
Суммы выстроятся в порядке возрастания. Тоже самое делаем для данных нашей организации. В следующий столбец забиваем формулу: в пустой ячейке ставим знак равно (=), следом выбираем ячейку с суммой из первого столбца, далее ставим знак минус (-) и выбираем ячейку с суммой из второго столбца, щелкаем клавишей «Enter». Чтобы протянуть формулу для всех ячеек столбца, наводим курсор на правый нижний угол ячейки с уже рассчитанной разницей (неважно, равна она 0 или нет), у нас появляется черный крестик, мы, нажав и не отпуская левую кнопку мыши, протягиваем формулу на все последующие ячейки в столбце. Так у нас появился столбец расчета, в котором мы видим, по каким строкам у нас идет разница в суммах.
Далее ставим фильтр на столбец расчета и отбираем ячейки в которых есть расхождения.
Для удобства можно выделить их другим цветом, записать на листке номер ячейки и снять отбор.
Далее мы идем к строке, в которой пошел «минус» и смотрим, в чем причина разногласий. Сравнивая номера и даты документов, мы поймем, внесено ли у нас на неверную сумму или же просто нет документа.
На первый взгляд может показаться, что нужно сделать слишком много отборов и сортировок. Но в условиях многостраничных актов сверки, мы, потратив на это 15 минут, сэкономим несколько дней и освободимся от нудной кропотливой работы.
Автор: Надежда Игнатьева,
И.О. заместителя руководителя отдела бухгалтерского учета компании «ГЭНДАЛЬФ»
Акт сверки взаиморасчетов — правильный бланк Excel 2021
Подготовке акта сверки взаиморасчетов часто не уделяется должного внимания. А зря — ведь именно правильно оформленный акт может сыграть решающую роль в суде. На что важно обратить внимание при проведении сверки взаиморасчетов и как правильно составить акт?
Составление Акта сверки взаиморасчетов необходимо обычно в том случае, когда двум юридическим лицам надо удостоверить произведенные между друг другом перечисления. Чаще всего такие акты сверок производятся по конкретным договорам или счетам-фактурам, также возможны сверки и по контрактам за определенный временной период.
Стоит отметить: несмотря на то, что данный документ не имеет обязательного значения и создается только по взаимному согласию сторон, он распространен во всех сферах деятельности и в ходу у большинства организаций.
Инструкция по заполнению акта сверки
В «шапке» нужно вписать данные о том, за какой период проводится акт сверки, а также указать договор, который послужил основанием для создания данного документа. Здесь же следует вписать название компаний в соответствии с учредительными документами (можно без таких подробностей, как КПП, ИНН, ОГРН и т.д.).
Вторая часть документа включает в себя таблицу, в которую каждая из сторон вносит сведения, содержащиеся в ее бухгалтерском учете по перечислениям согласно рассматриваемому договору или за определенный период времени. Они должны полностью совпадать. Под таблицей каждое предприятия вписывает имеющуюся задолженность. Эти данные также должны быть одинаковы. Если долги отсутствуют, это необходимо письменно отметить.
В завершение документ должен быть обязательно подписан руководителями предприятий с обязательным указанием должностей и расшифровкой подписей. Оформленный по всем правилам делопроизводства документ при наличии подписей директоров предприятий обретает юридическую силу.
Когда составляется акт сверки
В нормативных документах содержится лишь косвенное указание на то, что необходимо составлять акт сверки расчетов. Так, в п. 27 Положения по ведению бухучета, утвержденного Приказом Минфина РФ от 29.07.1998 № 34н, сказано, что перед составлением годовой бухгалтерской отчетности нужно обязательно провести инвентаризацию расчетов со всеми контрагентами. Очевидно, речь идет о контрагентах, с которыми были взаимоотношения в течение последнего года, а также о тех, с кем не было операций, но есть остатки расчетов.
Если же в налоговом учете создается резерв по сомнительным долгам, то инвентаризацию дебиторской задолженности нужно проводить на конец каждого отчетного (налогового) периода, то есть ежеквартально или даже ежемесячно (п. 4 ст. 266 НК РФ).
Кроме того, проведение инвентаризации расчетов может быть установлено материнской компанией для своих «дочек». Сроки обязательной сверки расчетов также могут быть включены в договор с контрагентом.
Акт сверки расчетов, подписанный обеими сторонами, — самое достоверное подтверждение сальдо расчетов с контрагентом. Более того, если в учете и отчетности отражена существенная сумма, которая не подтверждена актом сверки, то аудитор вряд ли ее признает.
Для чего нужен акт сверки расчетов с контрагентами
Своевременно и правильно оформленный акт сверки расчетов с контрагентами позволяет исключить ошибки в бухгалтерском и налоговом учете.
Если задолженность, отраженная в акте сверки, совпадает по данным организации и данным контрагента, то это означает, что все хозяйственные операции с указанным контрагентом отражены в учете правильно и своевременно и операции по отгрузке товаров, оказании услуг, выполнении работ, по получению и перечислению денежных средств не пропущены и не «задвоены».
Таким образом, акт сверки позволяет не только выявить ошибки в ведении бухучета, но и избежать разногласий с контрагентами.
Кроме этого, если дебитор организации подписывает документ, то он соглашается с состоянием расчетов и выражает готовность погасить свою задолженность.
Сроки подписания акта сверки взаиморасчетов
Как правило, при составлении акта стороны самостоятельно оговаривают сроки его подписания. Так как оформление осуществляется на добровольной основе, каких-то строгих ограничений со стороны законодательства здесь не существует. Срок определяется исключительно сторонами. Если данная информация не указана в акте, срок следует уточнить в сопроводительном письме.
Как уже было сказано, в сопроводительном письме необходимо указать, сколько дней имеется у контрагента на подписание акта. Если с его стороны не последовало никаких действий, можно отправить документы заказным письмом с обязательным уведомлением.
Таким образом, если дело дойдет до судебного разбирательства, у составителя акта будет документальное подтверждение того, что он пытался мирно разрешить ситуацию.
Практика показывает, так как составление акта сверки не является обязательным, некоторые организации вообще отказываются от данной процедуры. Однако многие компании уже оценили преимущества составления данного документа.
Корона
Сверка в Excel – легко и быстро
В выпуске «Прогрессивного бухгалтера» № 3, апрель 2019 г., мы рассмотрели возможности отчета «Акт сверки» в «1С». Но бывают случаи, при которых акт длиной с «Войну и мир» и непрост в понимании. В этом случае на помощь придет Excel.
Почему Excel
Перевод бухгалтерского учета в специализированные программы можно сравнить с переходом из каменного века к веку железному. В современных условиях было бы невозможно вести учет обложившись кипой бумаг или же делая расчеты в различных файлах. Но есть вещи, которые не теряют свою полезность, несмотря на прогресс. Вот и в некоторых вопросах старый добрый Excel не потерял своей полезности в плане сверки данных больших актов, хотя во многих других случаях функциональность «1С» все больше оставляет его «без работы».
Несколько лет назад нам нужно было выровнять взаиморасчеты с поставщиком за три года. 52 548 строк – это были продажи и премии, курсовые разницы и возвраты, взаимозачеты… Сверяли месяц, но итог не шел. У сотрудника уже замылился глаз, тогда эту стопку бумаги передали мне, сказали – осталась неделя. Мне стало скверно, потому что поняла: я неделю его только листать буду, не то что сверять.
Но когда кажется, что выхода нет, к нам приходит вдохновение и свежие мысли. Я запросила акт сверки поквартально в формате Excel и вывела за те же периоды и в том же формате наш. Скопировала данные в один файл и приступила к сортировке и сверке.
Как подготовиться к сверке
Для сверки нам нужно получить 2 колонки – дебет и кредит. Для этого форматируем акт следующим образом: копируем данные из акта сверки – нам нужны только колонки: дата, документ, дебет, кредит – шапка и сальдо не требуются, вставляем их на другой лист книги, далее снимаем объединение ячеек – жмем правой кнопкой по выделенному фрагменту (весь наш акт) и выбираем «Формат ячеек», во вкладке «Выравнивание» убираем все из пункта «Объединение ячеек».
Удаляем пустые столбцы, если строки получились слишком широкими, их высоту можно изменить через «Автоподбор высоты строки».
Далее нам требуется установить фильтр надо всеми столбцами. Для этого выделяем нужные нам столбцы, во вкладке «Главная» в правом углу экрана кликаем «Сортировка и фильтр», выпадает список функций, выбираем «Фильтр».
После этого ставим фильтр надо всеми столбцами.
Сортировка данных в акте
Древние римляне говорили: «Разделяй и властвуй» – в нашем случае тоже можно применить этот метод. Если в вашем акте не только продажи, но и другие операции, то сделайте в фильтре текстовый отбор и разложите их на отдельные листы книги Excel. Допустим, мы хотим сверить только «Поступления товаров и услуг», корректировки сверим потом. Через настраиваемый фильтр отбираем ПТУ и копируем на отдельный лист.
После этого делаем отбор в столбцах с нашими данными, только в поле содержит вбиваем «прих».
Далее выделяем столбец с суммами и делаем сортировку по возрастанию.
Дальнейший отбор и сверка
Появляется окошко, в нем жмем «Сортировка».
Суммы выстроятся в порядке возрастания. Тоже самое делаем для данных нашей организации. В следующий столбец забиваем формулу: в пустой ячейке ставим знак равно (=), следом выбираем ячейку с суммой из первого столбца, далее ставим знак минус (-) и выбираем ячейку с суммой из второго столбца, щелкаем клавишей «Enter». Чтобы протянуть формулу для всех ячеек столбца, наводим курсор на правый нижний угол ячейки с уже рассчитанной разницей (неважно, равна она 0 или нет), у нас появляется черный крестик, мы, нажав и не отпуская левую кнопку мыши, протягиваем формулу на все последующие ячейки в столбце. Так у нас появился столбец расчета, в котором мы видим, по каким строкам у нас идет разница в суммах.
Далее ставим фильтр на столбец расчета и отбираем ячейки в которых есть расхождения.
Для удобства можно выделить их другим цветом, записать на листке номер ячейки и снять отбор.
Далее мы идем к строке, в которой пошел «минус» и смотрим, в чем причина разногласий. Сравнивая номера и даты документов, мы поймем, внесено ли у нас на неверную сумму или же просто нет документа.
На первый взгляд может показаться, что нужно сделать слишком много отборов и сортировок. Но в условиях многостраничных актов сверки, мы, потратив на это 15 минут, сэкономим несколько дней и освободимся от нудной кропотливой работы.
В процессе постоянного сотрудничества компании накапливают значительный объем взаимных обязательств и сделок, которые могут быть проведены с использованием различных форм взаиморасчетов, включая отсрочку платежа или предоплату. Несмотря на использование электронных систем бухгалтерского учета, не исключено под влиянием человеческого фактора наличие неточностей в оприходовании средств, подписании и проводке актов приемки-передачи, материальных ценностей, списывании кредиторской задолженности.
В случае утери документов, неточностей оформления возможно возникновение несогласованных данных в хозяйственном учете двух контрагентов. В этом случае проводится акт сверки взаиморасчетов, который представляет собой специальный вид документа в регламентированной форме, в котором отражаются расчеты и сделки между двумя организациями. Чаще всего такой документ является естественной формой сверки для взаимного контроля задолженностей, рабочего персонала, который имеет доступ к бланкам строгой отчетности.
Подобную форму сверки применяют в следующих случаях:
при отсрочке платежа и отгрузке товаров или при предоставлении услуг без предварительной оплаты
при предоставлении стандартного пакета услуг на регулярной основе
при наличии широкого ассортимента товаров, которые могут отгружаться покупателю по его требованию неоднократно
при изменении стоимости услуг
при большом количестве договоров с целью сверки взаиморасчетов по ним
Несмотря на общие требования по содержанию данного документа, он не имеет строгой формы, определенной нормативами. Формат документа может быть относительно свободным или определенным в рамках принятой Учетной политики предприятия. Существуют устоявшиеся требования:
документ составляется в 2х экземплярах
подписывается главным бухгалтером и директором, подтверждается мокрой печатью
должен быть указан срок, в течение которого проводится сверка
при фактах отгрузки товаров указывается номер и дата доверенности и товарно-транспортной накладной, при оказании услуг
Автоматизация акта сверки
Честь и слава почтальонам,
Утомленным, запыленным…
© C.Маршак
Это про Ваших бухгалтеров:)
Я так понял, что дело это частое поэтому и нужно автоматизировать.
Вот вам на выбор:
1. Вариант хард — сохраняете свой Акт сверки в Экселе (это стандартная функция), а затем извращаетесь стандартным функционалом Экселя для сравнения с актом покупателя. Танцы с бубном конечно, но работает, если хорошо в Экселе разбираетесь.
2. Вариант полу-лайт — сохраняете акт покупателя в структурированный текстовый файл. Пишите (или заказываете обработку) которая бы подтянула этот файл в массив для сравнения (это называется ТаблицаЗначений), и сравнила с Вашим актом сверки уже в 1С. В этом случае для каждого покупателя нужно написать отдельную часть по загрузке правильной структуры массива, а сравнение уже будет проходить по единому алгоритму. До известной степени под нужную структуру входящего текстового файла можно подлезть и средствами Экселя, но тут нужно смотреть как и что к вам приходит и насколько Вы сможете это в экселе форматировать.
3. Вариант лайт — Договариваетесь с покупателями о формате электронной прередачи актов сверки и пишете (или заказываете) одну обработку на все случаи жизни, показываете бухгалтерам как и чем загружать и забываете о проблеме до тех пор пока акты приходят в согласованном формате.
А выбор за Вами…
Корректировка долга
Актуально на: 2 марта 2017 г.
Чтобы быть уверенными, что в бухгалтерском учете расчетов с контрагентами нет ошибок, а величина дебиторской и кредиторской задолженности соответствует реальности, проводится инвентаризация. Когда проводить инвентаризацию расчетов с покупателями и заказчиками, а также поставщиками и подрядчиками и как оформлять ее результаты, мы рассказывали в наших консультациях.
Если в результате инвентаризации расчетов обнаружены ошибки, организации необходимо будет произвести необходимые корректировки задолженности. Какими проводками эти корректировки сопроводить, расскажем в этом материале.
Корректировка задолженности: проводки
Если в ходе инвентаризации, например, при согласовании акта сверки с контрагентом, у вас была обнаружена ошибка, ее нужно обязательно исправить (п. 4 ПБУ 22/2010).
При корректировке задолженности по акту сверки проводки и дата их составления будут зависеть от того, когда такая ошибка была выявлена и является она существенной или нет.
Представим порядок исправления ошибок отчетного года в таблице (п.п. 5-14 ПБУ 22/2010):
| Когда обнаружена ошибка | Характер ошибки | Когда исправляется | Как исправляется |
|---|---|---|---|
| До окончания года | Любая | В месяце выявления | По соответствующим счетам учета |
| После окончания года, но до даты подписания бухгалтерской отчетности | В декабре отчетного года | ||
| После даты подписания бухгалтерской отчетности, но до даты ее утверждения | Существенная | ||
| После утверждения бухгалтерской отчетности | В месяце выявления | Через счет 84 «Нераспределенная прибыль (непокрытый убыток)» | |
| После даты подписания бухгалтерской отчетности | Несущественная | Через счет 91 «Прочие доходы и расходы» |
Необходимо иметь в виду, что ошибка признается существенной тогда, когда она в отдельности или в совокупности с другими ошибками за один и тот же отчетный период может повлиять на экономические решения пользователей, принимаемые на основе бухгалтерской отчетности за этот отчетный период (п. 3 ПБУ 22/2010).
При этом критерии существенности каждая организация определяет для себя самостоятельно и закрепляет это в своей Учетной политике.
Проиллюстрируем приведенный в таблице порядок исправления ошибок на примерах.
1) На основании акта сверки 16.08.2016 организация обнаружила, что 14.06.2016 она неправильно отразила стоимость приобретенных материалов: вместо 2 360 000 рублей (в т.ч. НДС 18%), материалы были оприходованы по стоимости 236 000 рублей (в т.ч. НДС 18%).
Поскольку ошибка выявлена до окончания года, то независимо от того, является ошибка существенной или нет, в бухгалтерском учете организации в августе 2016 года нужно доотразить:
Дебет счета 10 «Материалы» — Кредит счета 60 на сумму 1 800 000 ((2 360 000 – 236 000)/1,18)
Дебет счета 19 «НДС по приобретенным ценностям» — Кредит счета 60 на сумму 324 000 (1 800 000*18%)
2) Проведя сверку с покупателем, организация 17.02.2017 обнаружила, что 23.11.2016 она ошибочно отразила выручку (НДС не облагается): вместо 229 000 рублей выручка была признана в размере 1 229 000 рублей.
Отчетность уже была подписана, но не утверждена, и ошибка признана несущественной. Следовательно, в феврале 2017 года организация должна отразить:
Дебет счета 91, субсчет «Прочие расходы» — Кредит счета 62 «Расчеты с покупателями и заказчиками» на сумму 1 000 000 (1 229 000 – 229 000)
Если же ошибка была признана существенной, то исправить ее нужно 31.12.2016 следующей записью:
Дебет счета 62 – Кредит счета 90 «Продажи» СТОРНО на сумму 1 000 000
В случае, если эта существенная ошибка была выявлена уже после утверждения отчетности (к примеру, в июле 2017 года), то в июле 2017 году ошибку придется исправить так:
Дебет счета 84 – Кредит счета 62 на сумму 1 000 000
Почему в отчете «Акт сверки» поможет старый добрый Excel
Прогресс и автоматизация бухгалтерии — это, разумеется, прекрасно. Но если ваш «Акт сверки» — длиной с «Войну и мир», и понять его сложнее, чем французские вставки без перевода, на помощь может прийти старый добрый Excel. Случаями из практики делится Надежда Игнатьева, и.о. заместителя руководителя отдела бухгалтерского учета нашей компании.
Несколько лет назад нам нужно было выровнять взаиморасчеты с поставщиком за три года. 52 548 строк — это были продажи и премии, курсовые разницы и возвраты, взаимозачеты. Сверяли месяц, но итог не шел. У сотрудника уже замылился глаз, тогда эту стопку бумаги передали мне, сказали — осталась неделя. Мне стало скверно, потому что поняла: я неделю его только листать буду, не то, что сверять.
Но когда кажется, что выхода нет, приходит вдохновение и свежие мысли. Я запросила акт сверки поквартально в формате Excel и вывела за те же периоды и в том же формате наш. Скопировала данные в один файл и приступила к сортировке и сверке.
Как подготовиться к сверке
Для сверки нам нужно получить две колонки — дебет и кредит. Для этого форматируем акт следующим образом: копируем данные из акта сверки — нам нужны только колонки: дата, документ, дебет, кредит — шапка и сальдо не требуются, вставляем их на другой лист книги, далее снимаем объединение ячеек — жмем правой кнопкой по выделенному фрагменту (весь наш акт) и выбираем «Формат ячеек», во вкладке «Выравнивание» убираем все из пункта «Объединение ячеек».
Удаляем пустые столбцы, если строки получились слишком широкими, их высоту можно изменить через «Автоподбор высоты строки».
Далее нам требуется установить фильтр надо всеми столбцами. Для этого выделяем нужные нам столбцы, во вкладке «Главная» в правом углу экрана кликаем «Сортировка и фильтр», выпадает список функций, выбираем «Фильтр».
После этого ставим фильтр надо всеми столбцами.
Сортировка данных в акте
Древние римляне говорили: «Разделяй и властвуй» — в нашем случае тоже можно применить этот метод. Если в вашем акте не только продажи, но и другие операции, то сделайте в фильтре текстовый отбор и разложите их на отдельные листы книги Excel. Допустим, мы хотим сверить только «Поступления товаров и услуг», корректировки сверим потом. Через настраиваемый фильтр отбираем ПТУ и копируем на отдельный лист.
После этого делаем отбор в столбцах с нашими данными, только в поле содержит вбиваем «прих».
Далее выделяем столбец с суммами и делаем сортировку по возрастанию.
Дальнейший отбор и сверка
Появляется окошко, в нем жмем «Сортировка»
Суммы выстроятся в порядке возрастания. Тоже самое делаем для данных нашей организации. В следующий столбец забиваем формулу: в пустой ячейке ставим знак равно (=), следом выбираем ячейку с суммой из первого столбца, далее ставим знак минус (-) и выбираем ячейку с суммой из второго столбца, щелкаем клавишей «Enter». Чтобы протянуть формулу для всех ячеек столбца, наводим курсор на правый нижний угол ячейки с уже рассчитанной разницей (неважно, равна она 0 или нет), у нас появляется черный крестик. Нажав и не отпуская левую кнопку мыши, протягиваем формулу на все последующие ячейки в столбце. Так формируется столбец расчета, в котором мы видим, по каким строкам идет разница в суммах.
Далее ставим фильтр на столбец расчета и отбираем ячейки в которых есть расхождения.
Для удобства можно выделить их другим цветом, записать на листке номер ячейки и снять отбор.
Далее мы идем к строке, в которой пошел «минус» и смотрим, в чем причина разногласий. Сравнивая номера и даты документов, мы поймем, внесено ли у нас на неверную сумму или же просто нет документа.
На первый взгляд может показаться, что нужно сделать слишком много отборов и сортировок. Но в условиях многостраничных актов сверки, мы, потратив на это 15 минут, сэкономим несколько дней и освободимся от нудной кропотливой работы.