Дебет-Кредит
Украинский бухгалтерский еженедельник
#10'2002: Практичная бухгалтерия - Автоматически

Автоматизации бухучёта: Excel

Урок 26
Автоматизация составления проводок операций с помощью EXCEL

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"l

Бухгалтерский учет основных средств с помощью таблицы Excel позволяет не только систематизировать данные и рассчитать основные бухгалтерские показатели, но и автоматизировать составление проводок операций

Дополнительный блок - генератор проводок операции переоценки ОС

Данные, полученные в таблице учета основных средств, легко можно использовать для быстрого генерирования проводок, особенно если при расчете их сумм необходимо учитывать несколько условий. Показательным примером является автоматизация составления бухгалтерских проводок операций переоценки основных средств. Это может быть актуальным на предприятии, учитывая то, что в зависимости от типа переоценки существует восемь различных вариантов проводок этой операции.

Решить такую задачу можно с помощью дополнительного блока (U2:AB9, рис. 1), расположенного над разделом "Переоценка". В блоке есть две области: для ручного ввода данных (U2:Y2) и области автозаполнения (АВ2 - индекс переоценки, Z4:AB9 - рабочая часть). Генерирование проводок в блоке происходит после введения в ячейку V2 регистрационного номера объекта ОС, а в ячейку Х2 - даты проведения переоценки.

Рисунок 1

Автозапись проводок операции переоценки

Значение индекса переоценки, проведенной на указанную в ячейке Х2 дату, записывается в блок автоматически с помощью формулы массива1:

АВ2 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата; ИндБ)))}.

Введение формулы массива закончите нажатием клавиш Ctrl - Shift - Enter.

Автоматический ввод проводок обеспечивается формулами (см. таблицу 1), которые записываются в рабочую часть блока (Z4:AA9). Пусть вас не смущает наличие такого их числа. Работа по вводу формул будет оправдана, так как в результате вы избавитесь от необходимости определять и сравнивать многочисленные критерии и условия, предписанные Положением (Стандартом) 7.

Для автозаписи проводок введите такие формулы:

Таблица 1

Ячейка Формула Ячейка Формула
Z4 {=ЕСЛИ(AB4=0;0;ЕСЛИ(AB2>1;10;131))} АА4 {=ЕСЛИ(AB4=0;0;ЕСЛИ(AB2>1;131;10))}
Z5 {=ЕСЛИ(AB5=0;0;ЕСЛИ(AB2>1;10;423))} АА5 {=ЕСЛИ(AB5=0;0;ЕСЛИ(AB2>1;423;10))}
Z6 {=ЕСЛИ(AB6=0;0;ЕСЛИ(AB2>1;10;0))} АА6 {=ЕСЛИ(AB6=0;0;ЕСЛИ(AB2>1;746;0))}
Z7 {=ЕСЛИ(AB7=0;0;ЕСЛИ(AB2<1;975;0))} АА7 {=ЕСЛИ(AB7=0;0;ЕСЛИ(AB2<1;10;0))}
Z8 {=ЕСЛИ(AB8=0;0;ЕСЛИ(AB2>1;746;0))} АА8 {=ЕСЛИ(AB8=0;0;ЕСЛИ(AB2>1;793;0))}
Z9 {=ЕСЛИ(AB9=0;0;ЕСЛИ(AB2<1;793;0))} АА9 {=ЕСЛИ(AB9=0;0;ЕСЛИ(AB2<1;975;0))}

Автоматическая запись суммы проводки обеспечивается также формулами массива.

АВ4 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ABS(Изн-(Изн/ИндБ));ЕСЛИ(AB2=0;0))))};
АВ5 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ABS(ДК);0)))};
АВ6 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2>1;ABS(ВД);0))))};
АВ7 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2<1;ABS(ВД);0))))};
АВ8 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2>1;ABS(ВД);0))))};
АВ9 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2<1;ABS(ВД);0))))}.

До тех пор, пока соответствующим интервалам ячеек не будут присвоены используемые в формулах имена, после ввода этих формул в ячейках блока будет значение ошибки "#ИМЯ?".

Итак, в формулах массива аргументами являются интервалы ячеек, обозначенные именами. На рис. 2 показано, каким областям таблицы необходимо их присвоить. Каждый интервал должен начинаться с первой (рабочей) строки таблицы (на рис. 2 это строка 12). Так как в таблицу вносятся данные объектов ОС различных групп (без предварительной группировки их по группам), желательно выбрать интервал, обеспечивающий обработку данных возможно большего числа объектов. Например, если вы под именем "Номер" обозначите интервал ячеек А12:А1000, это даст возможность автоматизировать обработку 75 объектов основных средств (при условии, если вы для каждого объекта выделяете в таблице для учетных данных 12 строк, т. е. на 1 год). Учтите, что и другие имена интервалов, показанных на рис. 2, должны иметь формулы типа "='Таблиця ОЗ'!$C$12:$C$1000" (это формула для имени "Дата").

Рисунок 2

Содержание проводок, записанных в ячейках Z4:AA9, носит общий характер и приемлемо для операций как уценки, так и дооценки. В зависимости от вида проведенной переоценки в таблице будут высвечиваться номера субсчетов, соответствующих конкретной операции.

Для большей наглядности к рабочим ячейкам (Z4:AB9) можно применить условное форматирование. Установите для ячейки Z4 параметры, показанные на рис. 3, и скопируйте этот формат в другие ячейки, используя команду "Формат по образцу".

Рисунок 3

Примеры автоматизации составления бухгалтерских проводок

Пример 1. На предприятии проведена переоценка (в таблицу в ячейку D13 записано новое значение справедливой стоимости - 1400 грн, рис. 2), в результате которой на 1 января 2001 г. первоначальная стоимость объекта группы и его износ были уценены с индексом 0,87 (V13).

Для создания проводок по данной операции введите в ячейку V2 порядковый номер объекта, а в ячейку Х2 - дату периода, на начало которого проведена переоценка (01.01.2001). В результате будут сгенерированы три проводки.

Пример 2. Предварительно уцененный объект ОС при очередной переоценке 01.04.2001 г. был дооценен с индексом 1,33 (рис. 4). Эта операция отразилась в изменении не только первоначальной стоимости объекта и накопленного износа, но и дополнительного капитала, а также доходов отчетного периода. Сгенерированные в блоке проводки и соответствующие им суммы легко проверить по данным, приведенным в основной таблице.

По проводкам, показанным в блоке, первоначальная стоимость объекта (W15, рис. 4) увеличилась на 216,67 грн (66,67 + 20,77 + 129,23), (AB4:AB6). Проверьте: на такую же сумму изменилась и первоначальная стоимость объекта в таблице (W16 - W15) - 866,67 - 650,00 = 216,67.

Рисунок 4

Не забудьте, что переоценку необходимо будет провести для всех объектов группы, к которой принадлежит данный объект. Выборку таких объектов сделайте с помощью фильтров по учетному номеру объекта в таблице и дате, проводимой переоценки. Так как бухгалтерские последствия переоценки в общем случае различные для каждого объекта (предыдущие переоценки могли вызывать как уценку, так и переоценку), проводки по этой операции должны быть созданы для каждого объекта.

Пример 3. На рисунке 5 видно, что в этой же группе есть еще один объект - сканер. На дату текущей переоценки (01.04.2001 г.) его справедливая стоимость составила 280 грн (D119, рис. 5), что составило 89% от остаточной стоимости на эту дату. То есть первоначальная стоимость сканера подлежит уценке до 404,44 грн (W119), накопленный износ - до 124,44 грн.

Рисунок 5

Теперь введите в ячейку V2 учетный номер сканера - 9. В блоке появятся проводки, отражающие уценку сканера.

Обратите внимание, что сгенерированные проводки коренным образом отличаются от показанных на рис. 4.

Это связано с тем, что до текущей уценки сканер был предварительно дооценен.

Аналогичным образом можно получить проводки по каждому объекту, который удовлетворяет критерию переоценки - на дату баланса его остаточная стоимость более чем на 10 процентов отличается от справедливой стоимости.

Как сохранить сгенерированные проводки

Итак, у вас появилась возможность генерировать бухгалтерские проводки операций переоценки. Однако проводки, полученные для одного объекта, будут автоматически заменены другими, если вы введете номер другого объекта или новую дату переоценки. Как быть? Выход простой - сохранить созданные проводки в Журнале хозяйственных операций2.

Пусть на предприятии переоценке подлежат два объекта группы. Сгенерируйте проводки операции переоценки первого объекта и скопируйте ячейки рабочей зоны блока (U4:AB9, рис. 5). Теперь откройте Журнал хозяйственных операций (лист "ЖурналОп", книга "Баланс") и с помощью команды "Специальная вставка" (на этой вкладке установите переключатель "значение") вставьте скопированные ячейки в нижнюю свободную ячейку графы "Содержание операции" (Е54, рис. 6). Те же действия повторите для сгенерированных операций переоценки второго объекта группы.

Рисунок 6

Появившиеся пустые строки между проводками смело удалите. Обратите внимание, что суммы перенесенных проводок находятся в графе "Проверка". Переместите их в графу "Сумма операции". Таким образом данные, полученные в таблице учета основных средств, будут обработаны в Журнале операций и учтены при автоматическом создании бухгалтерской отчетности. Как это происходит, было уже рассказано в предыдущих уроках.


Примечания:

1 1Присвоение интервалам ячеек имен, используемых в формулах массива, рассмотрено ниже.

2О создании Журнала хозяйственных операций было рассказано в уроке №8 (см. "ДК" №12/2001).


Следующий урок

© 2002
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua