как быстро сверить акт сверки в экселе
Сверка в Excel – легко и быстро
Нет времени читать?
В выпуске «Прогрессивного бухгалтера» № 3, апрель 2019 г., мы рассмотрели возможности отчета «Акт сверки» в «1С». Но бывают случаи, при которых акт длиной с «Войну и мир» и непрост в понимании. В этом случае на помощь придет Excel.
Почему Excel
Перевод бухгалтерского учета в специализированные программы можно сравнить с переходом из каменного века к веку железному. В современных условиях было бы невозможно вести учет обложившись кипой бумаг или же делая расчеты в различных файлах. Но есть вещи, которые не теряют свою полезность, несмотря на прогресс. Вот и в некоторых вопросах старый добрый Excel не потерял своей полезности в плане сверки данных больших актов, хотя во многих других случаях функциональность «1С» все больше оставляет его «без работы».
Несколько лет назад нам нужно было выровнять взаиморасчеты с поставщиком за три года. 52 548 строк – это были продажи и премии, курсовые разницы и возвраты, взаимозачеты… Сверяли месяц, но итог не шел. У сотрудника уже замылился глаз, тогда эту стопку бумаги передали мне, сказали – осталась неделя. Мне стало скверно, потому что поняла: я неделю его только листать буду, не то что сверять.
Но когда кажется, что выхода нет, к нам приходит вдохновение и свежие мысли. Я запросила акт сверки поквартально в формате Excel и вывела за те же периоды и в том же формате наш. Скопировала данные в один файл и приступила к сортировке и сверке.
Как подготовиться к сверке
Для сверки нам нужно получить 2 колонки – дебет и кредит. Для этого форматируем акт следующим образом: копируем данные из акта сверки – нам нужны только колонки: дата, документ, дебет, кредит – шапка и сальдо не требуются, вставляем их на другой лист книги, далее снимаем объединение ячеек – жмем правой кнопкой по выделенному фрагменту (весь наш акт) и выбираем «Формат ячеек», во вкладке «Выравнивание» убираем все из пункта «Объединение ячеек».
Удаляем пустые столбцы, если строки получились слишком широкими, их высоту можно изменить через «Автоподбор высоты строки».
Далее нам требуется установить фильтр надо всеми столбцами. Для этого выделяем нужные нам столбцы, во вкладке «Главная» в правом углу экрана кликаем «Сортировка и фильтр», выпадает список функций, выбираем «Фильтр».
После этого ставим фильтр надо всеми столбцами.
Сортировка данных в акте
Древние римляне говорили: «Разделяй и властвуй» – в нашем случае тоже можно применить этот метод. Если в вашем акте не только продажи, но и другие операции, то сделайте в фильтре текстовый отбор и разложите их на отдельные листы книги Excel. Допустим, мы хотим сверить только «Поступления товаров и услуг», корректировки сверим потом. Через настраиваемый фильтр отбираем ПТУ и копируем на отдельный лист.
После этого делаем отбор в столбцах с нашими данными, только в поле содержит вбиваем «прих».
Далее выделяем столбец с суммами и делаем сортировку по возрастанию.
Дальнейший отбор и сверка
Появляется окошко, в нем жмем «Сортировка».
Суммы выстроятся в порядке возрастания. Тоже самое делаем для данных нашей организации. В следующий столбец забиваем формулу: в пустой ячейке ставим знак равно (=), следом выбираем ячейку с суммой из первого столбца, далее ставим знак минус (-) и выбираем ячейку с суммой из второго столбца, щелкаем клавишей «Enter». Чтобы протянуть формулу для всех ячеек столбца, наводим курсор на правый нижний угол ячейки с уже рассчитанной разницей (неважно, равна она 0 или нет), у нас появляется черный крестик, мы, нажав и не отпуская левую кнопку мыши, протягиваем формулу на все последующие ячейки в столбце. Так у нас появился столбец расчета, в котором мы видим, по каким строкам у нас идет разница в суммах.
Далее ставим фильтр на столбец расчета и отбираем ячейки в которых есть расхождения.
Для удобства можно выделить их другим цветом, записать на листке номер ячейки и снять отбор.
Далее мы идем к строке, в которой пошел «минус» и смотрим, в чем причина разногласий. Сравнивая номера и даты документов, мы поймем, внесено ли у нас на неверную сумму или же просто нет документа.
На первый взгляд может показаться, что нужно сделать слишком много отборов и сортировок. Но в условиях многостраничных актов сверки, мы, потратив на это 15 минут, сэкономим несколько дней и освободимся от нудной кропотливой работы.
Автор: Надежда Игнатьева,
И.О. заместителя руководителя отдела бухгалтерского учета компании «ГЭНДАЛЬФ»
Почему в отчете «Акт сверки» поможет старый добрый Excel
Прогресс и автоматизация бухгалтерии — это, разумеется, прекрасно. Но если ваш «Акт сверки» — длиной с «Войну и мир», и понять его сложнее, чем французские вставки без перевода, на помощь может прийти старый добрый Excel. Случаями из практики делится Надежда Игнатьева, и.о. заместителя руководителя отдела бухгалтерского учета нашей компании.
Несколько лет назад нам нужно было выровнять взаиморасчеты с поставщиком за три года. 52 548 строк — это были продажи и премии, курсовые разницы и возвраты, взаимозачеты. Сверяли месяц, но итог не шел. У сотрудника уже замылился глаз, тогда эту стопку бумаги передали мне, сказали — осталась неделя. Мне стало скверно, потому что поняла: я неделю его только листать буду, не то, что сверять.
Но когда кажется, что выхода нет, приходит вдохновение и свежие мысли. Я запросила акт сверки поквартально в формате Excel и вывела за те же периоды и в том же формате наш. Скопировала данные в один файл и приступила к сортировке и сверке.
Как подготовиться к сверке
Для сверки нам нужно получить две колонки — дебет и кредит. Для этого форматируем акт следующим образом: копируем данные из акта сверки — нам нужны только колонки: дата, документ, дебет, кредит — шапка и сальдо не требуются, вставляем их на другой лист книги, далее снимаем объединение ячеек — жмем правой кнопкой по выделенному фрагменту (весь наш акт) и выбираем «Формат ячеек», во вкладке «Выравнивание» убираем все из пункта «Объединение ячеек».
Удаляем пустые столбцы, если строки получились слишком широкими, их высоту можно изменить через «Автоподбор высоты строки».
Далее нам требуется установить фильтр надо всеми столбцами. Для этого выделяем нужные нам столбцы, во вкладке «Главная» в правом углу экрана кликаем «Сортировка и фильтр», выпадает список функций, выбираем «Фильтр».
После этого ставим фильтр надо всеми столбцами.
Сортировка данных в акте
Древние римляне говорили: «Разделяй и властвуй» — в нашем случае тоже можно применить этот метод. Если в вашем акте не только продажи, но и другие операции, то сделайте в фильтре текстовый отбор и разложите их на отдельные листы книги Excel. Допустим, мы хотим сверить только «Поступления товаров и услуг», корректировки сверим потом. Через настраиваемый фильтр отбираем ПТУ и копируем на отдельный лист.
После этого делаем отбор в столбцах с нашими данными, только в поле содержит вбиваем «прих».
Далее выделяем столбец с суммами и делаем сортировку по возрастанию.
Дальнейший отбор и сверка
Появляется окошко, в нем жмем «Сортировка»
Суммы выстроятся в порядке возрастания. Тоже самое делаем для данных нашей организации. В следующий столбец забиваем формулу: в пустой ячейке ставим знак равно (=), следом выбираем ячейку с суммой из первого столбца, далее ставим знак минус (-) и выбираем ячейку с суммой из второго столбца, щелкаем клавишей «Enter». Чтобы протянуть формулу для всех ячеек столбца, наводим курсор на правый нижний угол ячейки с уже рассчитанной разницей (неважно, равна она 0 или нет), у нас появляется черный крестик. Нажав и не отпуская левую кнопку мыши, протягиваем формулу на все последующие ячейки в столбце. Так формируется столбец расчета, в котором мы видим, по каким строкам идет разница в суммах.
Далее ставим фильтр на столбец расчета и отбираем ячейки в которых есть расхождения.
Для удобства можно выделить их другим цветом, записать на листке номер ячейки и снять отбор.
Далее мы идем к строке, в которой пошел «минус» и смотрим, в чем причина разногласий. Сравнивая номера и даты документов, мы поймем, внесено ли у нас на неверную сумму или же просто нет документа.
На первый взгляд может показаться, что нужно сделать слишком много отборов и сортировок. Но в условиях многостраничных актов сверки, мы, потратив на это 15 минут, сэкономим несколько дней и освободимся от нудной кропотливой работы.
Корона
Сверка в 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
Сверка с контрагентом через Акт сверки в формате MS Excel
Обработка позволяет загрузить стандартный Акт сверки в формате MS Excel, сохранённый из 1С Бухгалтерия 3.0, 2.0, 7.7.
По возможности автоматически определяется организация, контрагент, договор и период расчётов.
За этот же период загружаются документы из текущей базы согласно установленным параметрам аналогично встроенному в 1С Бухгалтерию документу Акт сверки расчётов с контрагентом и устанавливаются соответствия.
В результате можно видеть различия, совпадения и полный список. В подвале отображаются итоги с результатами сверки – разница оборотов по дебету/кредиту и по количеству документов.
Двойной клик по строке открывает первичный документ.
После загрузки можно менять период – сальдо по загруженным данными пересчитываются.
Возможно загружать в текущую базу как акты сверки, присланные контрагентами, так и свои, выгруженные из других баз или сделанные ранее в этой же базе для сравнения.
Если организации первичные документы заносят в базу разные сотрудники, то в конце периода удобно проконтролировать правильность занесения данных по Акту сверки расчётов от контрагента.
Обработку можно запускать как из внешнего файла, так и добавить в дополнительные обработки базы в какой-либо раздел.
Проверено в конфигурации Бухгалтерия предприятия, редакция 3.0 (3.0.74.58).