Файл: Автоматизация расчетов в бухгалтерском учете с помощью MS Excel.docx
Добавлен: 25.10.2018
Просмотров: 1580
Скачиваний: 9
6. Автоматизация расчетов в бухгалтерском учете с помощью MS Excel
Рассмотрение примера автоматизации расчетов в бухгалтерском учете с помощью MS Excel организации технологии обработки учетных данных с использованием электронных таблиц, конечно же, не претендует на полноту охвата задач бухгалтерского учета. В особенности если проводить сравнение с возможностями, реализованными в специализированных бухгалтерских программах. Однако использование электронных таблиц имеет и свои преимущества. Это наглядность представления данных, оперативность пересчетов, полная управляемость процессом вычислений, причем достигаемая простыми средствами. Главное же то, что используется стандартный и привычный инструмент вычислений – электронные таблицы, которые широко используются во всех офисах мира.
В этой главе показано, как бухгалтер может автоматизировать с помощью Excel учет основных средств, учет материальных ценностей и других направлений бухгалтерского учета. (Более подробно о возможностях использования MS Excel в бухгалтерском учете можно посмотреть в пособии Шуремов Е.Л. Решение задач бухгалтерского учета средствами Microsoft Excel. М.:ФА, 1999. 46 с.)
Автоматизация расчетов по учету основных средств
Технология учета основных средств с использованием MS Excel предполагает выполнение следующих действий.
Формируется таблица, строки которой содержат основные сведения об имеющихся основных средствах, необходимые для выполнения расчетов по начислению износа. Принцип заполнения картотеки таков: одна строка на один объект учета. Таблица содержит формулы, позволяющие по данным о балансовой стоимости и норме амортизации рассчитать износ за текущий период (месяц или квартал) и, с учетом износа на начало периода, рассчитать износ на конец периода. По соответствующим колонкам (балансовая стоимость, износ за период, износ на начало и конец периода) автоматически подводятся итоги, необходимые для отражения информации по основным средствам в главной книге. При необходимости отнесения износа по разным объектам на разные счета затрат таблица может быть дополнена колонкой, в которой проставляются соответствующие счета, на другом листе рабочей книги должны быть определены шаблоны проводок, включающие эти счета и формулы подсчета их сумм, в результате чего проводки формируются автоматически и также автоматически изменяются при внесении изменений в данные картотеки.
При завершении периода файл рабочей книги копируется, ему присваивается новое имя (например, соответствующее названию нового периода), выполняется перенос остатков из колонки, соответствующей износу на конец предыдущего периода в колонку данных износа на начало периода. Далее выполняется необходимая корректировка исходных данных, а данные на текущий период рассчитываются автоматически.
Данная схема весьма напоминает те процедуры, которые выполняются при ведении ручного учета. Отличия состоят в следующем.
При ведении ручного учета никому не придет в голову каждый раз переписывать картотеку при переходе на следующий период. Здесь же нет никаких проблем – копирование выполняет компьютер. В результате мы сохраняем в актуальном виде информацию за каждый отчетный период.
Перенос данных из одной колонки Excel в другую (износ на конец периода в колонку износа на начало периода) также не составляет никаких проблем.
Один раз введенные формулы работают всегда. После переноса данных конца прошлого периода на начало текущего они срабатывают автоматически, в результате чего сразу получаются результаты на конец данного периода. Также автоматически формулы срабатывают и при внесении изменений в картотеку.
Естественно, рассматриваемая технология имеет определенные ограничения. Однако она достаточно проста, вполне работоспособна и может использоваться во многих относительно небольших организациях.
Основная расчетная таблица
Для создания основной расчетной таблицы выполните следующие действия.
Создайте новую рабочую книгу Excel.
В ячейку A1 введите текст «Инвентарный номер».
В ячейку B1 - «Наименование основного средства».
В ячейку C1 - «Стоимость».
В ячейку D1 - «Износ на начало текущего периода».
В ячейку E1 - «Норма амортизации (% в год)».
В ячейку F1 - «Износ за период».
В ячейку G1 - «Износ на конец периода».
В ячейку H1 - «Износ по норме».
Пояснения по содержанию колонок будут даны позже, а их названия могут быть выбраны по вашему усмотрению.
Выделите ячейки A1:H1, отформатируйте их по своему вкусу и установите удобную для просмотра информации ширину колонок. В нашем примере мы выбрали режим форматирования со следующими параметрами выравнивания (Формат Ячейки, вкладка «Выравнивание»): горизонтальное – по центру, вертикальное – по центру, а также установили флажок «Переносить по словам» (рис. 6.1).
Рисунок 6.1 – Установка параметров выравнивания текста в заголовке расчетной таблицы
Далее, в ячейку A2 введите текст «Итоги».
В ячейку C2 - формулу =СУММ(C3:C1000)
В ячейку D2 - формулу =СУММ(D3:D1000)
В ячейку F2 - формулу =СУММ(F3:F1000)
В ячейку G2 - формулу =СУММ(G3:G1000)
Поскольку формулы однотипные, то можно ввести формулу только в ячейку C2, а в другие просто скопировать ее – Excel автоматически подставит идентификаторы нужных колонок. В указанных ячейках будут суммироваться значения по стоимости, износу на начало периода, износу за период и износу на конец периода по всем учитываемым объектам. Диапазон суммирования в нашем примере ограничивается строкой 1000. Если в организации более 1000 объектов основных средств, то следует установить большее значение, если меньше – то меньшее. Однако мы настоятельно советуем указывать диапазон с «запасом», чтобы впоследствии, при пополнении таблицы строками уже не думать, попадают ли нужные показатели в итоги.
Для строки итогов мы выбрали жирный шрифт и пометили ее другим цветом (Формат Ячейки, вкладка Вид, щелкнуть мышью по нужному цвету и по кнопке OK), чтобы она лучше выделялась. Можно выбрать и другое оформление. Итоги по картотеке намеренно вынесены наверх таблицы для того, чтобы они были видны сразу при входе в нее. Кроме того, так будет удобнее пополнять картотеку новыми записями: в этом случае их можно просто вписывать в ближайшую пустую строку. В противном случае (при размещении итогов внизу таблицы) пришлось бы выполнять лишнюю операцию вставки строк.
Подготовительные действия выполнены. Наша таблица приняла вид, показанный на рис. 6.2.
Рисунок 6.2 – Расчетная таблица с установленными формулами расчета итогов
Теперь можно вводить данные по строкам. В каждой строке должна быть сосредоточена необходимая информация по одному объекту учета. Начнем со строки 3.
Порядок заполнения колонок A («Инвентарный номер»), B («Наименование основного средства») и C («Стоимость») очевиден и в комментариях не нуждается.
В колонке D нужно проставить величину износа по данному основному средству на начало периода, начиная с которого предполагается вести учет с использованием электронных таблиц.
В колонку E вводится норма амортизации данного основного средства в процентах за год. Если по какому-то объекту учета износ за текущий период не должен начисляться, то здесь должен быть проставлен ноль.
Следующие три колонки являются расчетными и содержат формулы.
Для лучшего понимания последовательности расчетов начнем с колонки H («Износ по норме»). Данная колонка – чисто технологическая и мы используем ее только для того, чтобы упростить ввод формул в колонках F и G. Введите в ячейку H3 формулу: =C3*E3/1200.
Она означает, что данная графа рассчитывается как произведение стоимости основного средства на норму амортизации, деленную на 1200. В нашем примере в качестве расчетного периода выбран месяц. Поэтому мы используем константу 1200, поскольку для расчета износа за месяц норму амортизации, выраженную в процентах за год, нужно поделить на 12 месяцев и 100%. При начислении износа сразу целый за квартал следует использовать константу 400.
После ввода формулы будет автоматически рассчитано значение износа за данный период. Теперь подумаем: всегда ли это значение можно считать величиной износа за месяц. Нет. Не всегда. Если сумма износа на начало периода, сложенная с этой величиной больше балансовой стоимости объекта учета, то износ в таком размере за данный период начислен быть не может и должен быть принят только в сумме разницы между стоимостью и износом на начало периода. В противном случае износ на конец периода превысит стоимость. Данная ситуация не очень типична, но возможна если в предшествующих периодах износ начислялся по другой норме.
В любом случае мы должны предусмотреть все возможные варианты, чтобы гарантировать себя от возможных ошибок. Поэтому данная графа является только отправной точкой при выполнении последующих расчетов. Для корректного выполнения расчета износа за текущий период в ячейку F3 должна быть введена формула: =ЕСЛИ(D3+H3>C3;C3-D3;H3)
Она означает, что если износ на начало периода (D3), сложенный с износом, рассчитанным в соответствии с нормой амортизации (H3), больше стоимости данного объекта учета, то износ за текущий период может быть начислен в сумме, не превышающей его остаточной стоимости (C3-D3). В противном случае износ за месяц составляет величину, рассчитанную в соответствии с нормой амортизации (H3). Более подробно об использовании функции ЕСЛИ() см. справочную систему Excel.
Износ на конец периода равен износу на начало периода, сложенному с износом за период. Поэтому в ячейку G3 следует ввести формулу: =D3+F3.
После ввода этой формулы строка таблицы полностью сформирована. В качестве примера мы ввели в нее данные, представленные на рис 6.3.
Рисунок 6.3 – Запись о первом объекте картотеки основных средств
Поскольку расчетные графы остальных строк должны содержать идентичные формулы мы просто скопируем имеющиеся формулы, а «умный» Excel самостоятельно «подправит» фигурирующие в них индексы с тем, чтобы они соответствовали номерам строк.
Для этого выделите ячейки F3:H3 и далее «уцепитесь» мышкой за правый нижний угол выделенной области. «Мышиный курсор» должен принять форму черного креста. Теперь «потяните» мышь вниз на столько строк, сколько у Вас есть основных средств (один объект - одна строка). Не повредит «размножить» формулы с запасом.
Все. Таблица готова. Остается только перенести в нее данные картотеки (рис. 6.4.). Поскольку формулы скопированы с «запасом», в тех строках, где не введены необходимые данные, соответствующие ячейки имеют нулевое значение.
Рисунок 6.4 – Пример заполнения расчетной таблицы данными картотеки основных средств
По мере заполнения строк сведениями об объектах основных средств (инвентарный номер, наименование, стоимость, износ на начало периода норма амортизации) величины износа за период и износа на конец периода будут автоматически рассчитываться, а итоги по соответствующим колонкам изменяться.
Автоматизация составления проводок по начислению износа
Если износ по различным объектам учета относится на единственный счет, то можно ограничиться тем, что мы уже сделали, поскольку все необходимые итоговые суммы уже имеются и их можно просто перенести в главную книгу. Если же износ должен относиться на разные счета или субсчета, то следует дополнить таблицу и выполнить следующие действия.
В заголовке (1-ая строка) колонки I напишите «Счет отнесения износа» и перенесите на него формат заголовка таблицы. Отформатируйте эту колонку I так, чтобы все введенные в нее значения воспринимались как текстовые.
Для этого выделите колонку I, вызовите карточку настройки форматов ячеек Excel (Формат - Ячейки), выберите раздел «Число», в списке форматов выберите строку «Текстовый» и нажмите клавишу OK (рис. 6.5). Установите для данного столбца выравнивание по центру.
Рисунок 6.5 – Установка форматирования для колонки ввода счетов отнесения износа.
Хотя коды счетов представлены числами, мы намеренно используем текстовый формат, поскольку обработку кодов счетов удобнее выполнять, рассматривая их как текст.
Для каждого объекта учета (строки) укажите коды счетов, на которые должен быть отнесен износ (рис. 6.6).
Рисунок 6.6 – Расчетная таблица с установленными кодами счетов отнесения износа
Переименуйте «Лист 1» в «Картотека» (Формат Лист Переименовать), потом перейдите на «Лист 2» и переименуйте его в «Проводки».
В ячейку A1 листа «Проводки» впишите текст «Дебетуемый счет», в ячейку B1 - «Кредитуемый счет», а в ячейку C3 - «Сумма». Отформатируйте колонки A и B так, чтобы все вводимые в них значения воспринимались как текстовые и размещались по центру.
Начиная со строки 2, впишите все проводки, которые используются при начислении износа. В колонке A – «Дебетуемый счет», должны быть перечислены все счета, которые хоть один раз были указаны в колонке I листа «Картотека». В противном случае итоги по износу за месяц и итоги по проводкам не сойдутся.
В ячейку C2 листа «Проводки» впишите формулу:
=СУММЕСЛИ(Картотека!I$3:I$1000;A2;Картотека!F$3:F$1000)
Функция СУММЕСЛИ() суммирует ячейки, специфицированные заданным критерием.
В общем виде она имеет следующую структуру:
СУММЕСЛИ(интервал_просмотра; критерий; суммируемый_интервал)
Интервал просмотра – это интервал ячеек, среди которых осуществляется поиск определенной информации.
Критерий – это условие поиска, которое может быть в форме числа, выражения или текста. Критерий определяет правило выбора данных для суммирования.
Суммируемый интервал – это ячейки, из которых выбираются данные для суммирования. При этом они суммируются только тогда, когда ячейка из интервала просмотра удовлетворяет условию, заданному критерием.
Более подробно о функции СУММЕСЛИ см. справочную систему Excel.
В нашем случае функция СУММЕСЛИ() суммирует числа из диапазона F3:F1000 листа «Картотека» в том случае, если значение ячейки A2 листа «Проводки» совпадает с соответствующим значением интервала поиска I3:I1000. Содержательно это означает, что суммируются величины износа за месяц (колонка F) по тем объектам, у которых счет отнесения износа (колонка I) совпадает со счетом, дебетуемым в данной проводке (A2).
Поскольку формула для расчета суммы проводки записана и действует на листе «Проводки», а интервал просмотра и суммируемый интервал заданы на листе «Картотека», то перед обозначениями границ этих интервалов заданы названия данного листа. В противном случае Excel рассматривал бы эти интервалы, как относящиеся к листу на котором размещена формула.