Сверка в Excel – легко и быстро
В выпуске «Прогрессивного бухгалтера» № 3, апрель 2019 г., мы рассмотрели возможности отчета «Акт сверки» в «1С». Но бывают случаи, при которых акт длиной с «Войну и мир» и непрост в понимании. В этом случае на помощь придет Excel.
- Почему 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