Файл: Автоматизация расчетов в бухгалтерском учете с помощью MS Excel.docx
Добавлен: 25.10.2018
Просмотров: 1428
Скачиваний: 8
Мы рассмотрим более общий случай, когда справочник на листе «Обороты» включает сведения о ТМЦ разной экономической природы, учитываемых на разных счетах.
Для решения этой задачи справочник должен быть дополнен сведениями о счетах, на которых учитываются конкретные виды материальных ценностей. Мы будем хранить эту информацию в столбце L.
Введите в ячейку L1 текст «Счет» и отформатируйте столбец L так, чтобы все вводимые в него значения воспринимались как текстовые. Далее, для каждого вида ТМЦ проставьте код счета, на котором он учитывается.
В нашем примере мы ввели данные, приведенные на рис. 6.17.
Рисунок 6.17 – Справочник ТМЦ с включенными сведениями о счетах.
Теперь следует перейти на лист «Проводки».
В ячейку A1 введите текст «Дебет», в ячейку B1 – «Кредит», в ячейку C1 – «Сумма», а в ячейку D1 – «Комментарий».
Методика заполнения таблицы на этом листе состоит в следующем.
Задаются все проводки, которые так или иначе возникают в связи с движением материальных ценностей, занесенных в справочник на листе «Обороты». Дебетуемый счет проводки вводится в соответствующую строку столбца A, а кредитуемый счет – в столбец B. Формулы для расчета суммы проводки зависят от ее содержания.
Формула для расчета суммы «приходной» проводки имеет следующий вид:
=СУММЕСЛИ(Обороты!$L$4:$L$2000;A2;Обороты!$F$4:$F$2000)
Она суммирует значения из столбца F (приход в стоимостном выражении) листа «Обороты» по тем строкам, в столбце L которых содержится код счета, совпадающий с кодом счета, дебетуемым в данной проводке (A2).
Данная формула может быть скопирована для всех «приходных» проводок на листе «Проводки», то есть тех, в которых дебетуется счет, на котором учитывается какой-либо вид материальных ценностей, из представленных в таблице на листе «Обороты».
Формулы расчета сумм «расходных» проводок, то есть тех, в которых кредитуется тот или иной счет учета материальных ценностей, составляются по идентичным правилам, но в качестве критерия отбора (второй аргумент функции СУММЕСЛИ) используется ссылка на ячейку из столбца B, а суммирование производится по столбцу H. Например, в рассматриваемом нами примере, для строки 5 формула будет выглядеть следующим образом:
=СУММЕСЛИ(Обороты!$L$4:$L$2000;B5;Обороты!$H$4:$H$2000)
Подсчет сумм проводок по учету НДС, полученному с разными видами материальных ценностей, можно организовать исходя из тех соображений, что субсчета 19-го счета в точности соответствуют счетам учета разных категорий материальных ценностей. На наш взгляд, эту задачу удобнее всего решать путем предварительного «сведения» итоговых сумм НДС по всем приходам на листе «Обороты». Само по себе «сведение» сумм по отдельным позициям учета материальных ценностей не представляет особого интереса. Однако «привязав» полученные суммы НДС к конкретным видам ценностей легко «разбросать» их впоследствии по конкретным субсчетам счета 19 «НДС по приобретенным материальным ресурсам».
Итак, в колонке M листа «Обороты» будем подсчитывать итоги по НДС, полученному при поступлении каждого вида материальных ценностей. Введем в ячейку M1 текст «НДС», в ячейку M3 формулу подсчета итогов по колонке =СУММ(M4:M2000), а в ячейку M5 формулу подсчета итогов по суммам НДС, поступившим с данным видом ТМЦ:
=СУММЕСЛИ(Приход!$A$2:$A$5000;A5;Приход!$G$2:$G$5000)
Далее указанная формула должна быть скопирована во все последующие строки, содержащие сведения о материальных ценностях.
Теперь можно на листе «Проводки» задать формулы для расчета сумм проводок по учету НДС, поступившего с материальными ценностями. Если на счете 19.1 учитывается НДС, поступивший с товарами, то для проводки Д19.1 К60 формула для расчета суммы может быть записана в следующем виде:
=СУММЕСЛИ(Обороты!$L$4:$L$2000;»41»;Обороты!$M$4:$M$2000)
Ее отличие от ранее рассмотренных формул состоит в том, что она суммирует числа в столбце M листа «Обороты», а критерий поиска задан в виде константы – кода счета «41». Данная формула суммирует НДС для товаров, то есть тех строк справочника на листе «Обороты», в которых проставлен код счета 41. Аналогично следует построить формулы и для материальных ценностей, учитываемых на других счетах.
В условиях нашего примера были получены результаты, представленные на рис. 6.18.
Рисунок 6.18 – Автоматически сформированные проводки по движению материальных ценностей и смежным операциям
Таблица легко может быть дополнена для формирования и подсчета сумм проводок других типов. В частности, для МБП можно составить проводки, отражающие начисление износа по передаваемым в эксплуатацию ценностям, базируясь на общих суммах расхода по ценностям, относящимся к данной категории.
Переход к следующему периоду
Переход к следующему периоду осуществляется по методике уже известной нам по предыдущей главе. Рабочая книга копируется в новый файл и в нем значения остатков материальных ценностей на конец периода стандартными средствами Excel переносятся в колонку остатков на начало периода как значения (но не как формулы!). Далее, из новой рабочей книги удаляется вся оперативная информация, относящаяся к предшествующему периоду (данные листов «Приход» и «Расход»). В результате обороты обнуляются, итоги на начало периода становятся равными итогам на конец предшествующего периода и цикл операций повторяется заново для текущего периода.
Обработка проводок
В данной главе мы рассмотрим простую технологию ведения синтетического учета с использованием MS Excel. Она позволяет на основе вводимых проводок и входящих остатков счетов на начало периода автоматически получать обороты за этот период, исходящие остатки счетов на его конец и наглядно представлять эту информацию.
Суть предлагаемого подхода состоит в следующем.
На одном листе рабочей книги Excel строится макет оборотной ведомости счетов. В нем для каждого используемого счета (субсчета) указываются его код, наименование, входящий остаток на начало текущего периода и задаются формулы для автоматического расчета дебетового и кредитового оборотов и исходящего остатка на конец данного периода. На другом листе рабочей книги вводятся проводки, которые и являются основанием для расчета оборотов счетов. Ввод новой проводки автоматически инициирует пересчет оборотов и исходящих остатков счетов.
В целях упрощения расчетных формул и компактности представления данных на экране остатки представляются свернуто, одной колонкой: дебетовые со знаком «плюс», а кредитовые – со знаком «минус».
Для первого периода входящие остатки счетов вводятся вручную. Далее, при переходе к следующему периоду (месяцу или кварталу – как кому нравится) файл рабочей книги копируется, ему присваивается новое имя (например, соответствующее названию нового периода) и выполняется перенос остатков из колонки исходящих остатков файла предшествующего периода в колонку входящих остатков файла текущего периода. Эта операция выполняется стандартными средствами копирования значений Excel. В новом файле ячейки с проводками, относящимися к старому периоду, очищаются. Вследствие этого обороты нового периода обнуляются. Далее начинается ввод новых проводок, которые автоматически обновляются обороты, исходящие остатки и т.д.
Естественно, приведенная технология имеет определенные ограничения, которые подробнее будут рассмотрены далее. Однако она вполне работоспособна и, как показала практика, вполне применима в практической учетной работе относительно небольших предприятий.
Таблица для ввода проводок
Создайте новую рабочую книгу Excel. Листу 1 присвойте имя «Счета» (Формат Лист Переименовать). Далее, перейдите на Лист 2 и присвойте ему имя «Проводки».
На листе «Проводки» в ячейку A1 введите текст «Дебет», в ячейку B1 – «Кредит», в ячейку C1 – «Сумма», в ячейку D1 – «Комментарий».
В эту таблицу, начиная со второй строки, будут вводиться проводки по принципу: одна строка – одна проводка. При этом в столбце A должен задаваться код дебетуемого счета, в столбце B – код кредитуемого счета, в столбце C – сумма проводки, а в столбце D – текстовый комментарий в произвольной форме.
В качестве кодов счетов в нашей таблице, можно использовать, вообще говоря, любые комбинации символов. В рассматриваемом примере мы будем использовать счета без субсчетов и с субсчетами. Для правильной интерпретации счетов в формулах обязательно отформатируйте столбцы A и B так, чтобы все значения в них воспринимались как текстовые. Для этого выделите колонки A и B, вызовите карточку настройки форматов ячеек Excel (Формат Ячейки), выберите раздел «Число», в списке форматов выберите строку «Текстовый» и нажмите клавишу OK.
Для чего мы требуем форматировать столбцы A и B как текстовые? Дело в том, что сюда будут вводиться коды счетов и, несмотря на то, что они имеют числовые значения значительно удобнее, если их числовые коды будут восприниматься как текст. Если этого не сделать, то, например, код «68.1» или «68/1» (счет с субсчетом) Excel будет воспринимать как текст, а код «50» (счет без субсчета) как число. В этом случае расчетные формулы, которые будут использоваться на листе «Счета» будут отрабатывать неверно.
Конечно, можно каждую ячейку указанных колонок форматировать отдельно. Но это явно не рационально. Поэтому лучше сразу задать единообразное форматирование для всех ячеек колонки.
Теперь можно вводить проводки. Мы в качестве примера ввели данные, представленные на рис 6.19. Они пригодятся нам при проверке правильности задания формул на листе «Счета».
Рисунок 6.19 – Пример ввода данных на листе «Проводки»
Таблица оборотной ведомости счетов
Перейдите к листу «Счета».
Лист «Счета» должен включать таблицу для расчета оборотов и исходящих остатков счетов. Для ее создания выполните следующие действия.
В ячейку A1 введите текст «Счет».
В ячейку B1 - «Название счета».
В ячейку C1 - «Входящий остаток».
В ячейку D1 - «Дебетовый оборот».
В ячейку E1 - «Кредитовый оборот».
В ячейку F1 - «Исходящий остаток».
Поскольку столбец A будет содержать коды счетов, то по рассмотренным выше соображениям его следует отформатировать так, чтобы все его ячейки воспринимались как текстовые. Ячейки B1:F1 отформатируйте по своему вкусу и установите удобную для просмотра информации ширину этих колонок. В нашем примере для первой строки мы установили горизонтальное и вертикальное выравнивание «по центру» (Формат – Ячейки, вкладка «Выравнивание»).
Во второй строке расчетной таблицы будут подводиться итоги по входящим и исходящим остаткам, дебетовым и кредитовым оборотам. Они нужны для выполнения контрольных функций.
В ячейку A2 введите текст «Итоги».
В ячейку C2 - формулу =СУММ(C3:C1000)
В ячейку D2 - формулу =СУММ(D3:D1000)
В ячейку E2 - формулу =СУММ(E3:E1000)
В ячейку F2 - формулу =СУММ(F3:F1000)
Поскольку формулы однотипные, то можно ввести формулу только в ячейку C2, а в другие просто скопировать ее – Excel автоматически подставит идентификаторы нужных колонок. В указанных ячейках будут суммироваться значения остатков на начало периода, дебетового и кредитового оборотов и остатков на конец периода по всем используемым счетам и субсчетам. В нашем примере диапазон суммирования ограничивается строкой 1000. Если на предприятии используется большее число счетов и субсчетов, то следует установить большее значение. Если меньше, то можно установить меньший диапазон суммирования. Однако лучше указать диапазон с «запасом», чтобы впоследствии, при пополнении таблицы строками уже не думать, попадают ли нужные показатели в итоги.
Для строки итогов мы выбрали жирный шрифт и пометили ее другим цветом (Формат Ячейки, вкладка Вид, щелкнуть мышью по нужному цвету и по кнопке OK), чтобы она лучше выделялась. Вы можете выбрать иное оформление. Итоги намеренно вынесены наверх таблицы для того, чтобы они были видны сразу при входе в нее. Кроме того, так удобнее пополнять таблицу записями о новых счетах: в этом случае их можно просто вписывать в ближайшую пустую строку. В противном случае (при размещении итогов внизу таблицы) пришлось бы выполнять лишнюю операцию вставки строк.
Подготовительные действия выполнены. На рис. 6.20 показан фрагмент того, что получилось.
Рисунок 6.20 – Заголовок расчетной таблицы с установленными формулами подсчета итогов
Теперь заполним строку 3.
В ячейку A3 введем код счета. Напоминаем, что используемая нами модель компьютерного учета такова, что в качестве кода счета можно вводить произвольную комбинацию символов. В нашем примере будут использоваться как синтетические счета, так и субсчета, составные элементы которых разделяются символом «точка». Например, «50», «68.1», «68.2» и т.д. (кавычки не вводятся). Можно использовать субсчета второго, третьего и более высоких уровней, а также сокращенные текстовые наименования. Например, «41.1.101», «60.1.25.133», «61.Инфополис.Дог№34» и т.д.
В нашем примере в ячейку A3 введен код «01».
В ячейку B3 следует ввести название счета. В нашем примере это текст «Основные средства».
Ячейка C3 должна содержать остаток данного счета на начало периода. Для первого периода он вводится вручную. В нашем примере мы задали значение 10000.
Для счетов, имеющих дебетовое сальдо, входящий остаток вводится со знаком «плюс», для счетов, имеющих кредитовое сальдо – со знаком «минус». В принципе, можно было бы использовать и более привычную двухколоночную запись, но это сделало бы таблицу хуже обозреваемой и усложнило бы расчетные формулы.
Обращаем внимание, что используемый нами одноколоночный принцип записи остатков счетов исключает возможность использования развернутых сальдо, то есть имеющих одновременно ненулевое значение и по дебету и по кредиту. Это может иметь место для некоторых счетов учета взаиморасчетов, например, для счета 76 «Расчеты с разными дебиторами и кредиторами». В принципе, проблема преодолима, но данной публикации мы не будем ее рассматривать.
Теперь введем формулу для расчета дебетового оборота.
В ячейку D3 листа «Проводки» впишите формулу:
=СУММЕСЛИ(Проводки!A$2:A$5000;$A3;Проводки!$C$2:$C$5000)
Подробно порядок использования функции СУММЕСЛИ() разбирался ранее. Поэтому здесь мы не будем подробно останавливаться на структуре параметров и правилах использования этой функции и ограничимся лишь общими комментариями.
Приведенная формула просматривает диапазон строк 2-5000 листа «Проводки» и в тех строках, где значение в столбце A совпадает со значением ячейки A3 листа «Счета» суммирует числа столбца C. Поскольку на листе «Проводки» в столбце A мы записываем код дебетуемого счета, в столбце C – сумму проводки, а в ячейке A3 листа «Счета» у нас содержится код счета, то это означает, что приведенная формула будет складывать суммы проводок только тогда, когда дебетуемым счетом проводки является счет из текущей строки расчетной таблицы листа «Счета». Тем самым, будет рассчитан дебетовый оборот счета, указанного в данной строке расчетной таблицы.