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

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

Урок 22
Автоматизация бухгалтерского и налогового учета основных средств с помощью EXCEL

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


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

Секреты использования методов амортизации

Методика анализа амортизации, изложенная на прошлом уроке, помогла вам выбрать оптимальный метод (на момент начала эксплуатации объекта основных средств). Однако в процессе эксплуатации основного средства не только рассчитываются показатели бухгалтерского и налогового учета, но и периодически может возникать необходимость изменить условия эксплуатации. В этой статье вы узнаете, как быстро и правильно рассчитать амортизацию любым методом, не отклоняясь от требований Стандарта 7.

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

Рисунок 1

Для удобства понимания формула разбита на логические части. При ее записи в ячейку Q12 (рис. 1) сохраните все (!) символы, показанные в таблице. (Совет для интернет-читателей - надежнее будет скопировать все аргументы формулы прямо из таблицы 1).

Таблица 1

Аморти-
зация по методу
Содержание формулы Аргументы формулы
"Обнуление" ячеек таблицы =ЕСЛИ(ИЛИ(И(O11<>1;O12<>1;O11=O12);O12=0;R11<=J11);0;
Прямолинейный ЕСЛИ(K12=1;(P12-J12)/I12;
Уменьшение остаточной стоимости ЕСЛИ(ИЛИ(И(K12=2;ИЛИ(N12=1;N12=2)));
1*(ЕСЛИ(I12-O12>=12;Z11*(1-СТЕПЕНЬ(J12/P12;12/I12))/12;
(Z11-J12)/(I12+1-O12)));
Ускоренное уменьшение остаточной стоимости ЕСЛИ(И(K12=3;ИЛИ(N12=1;N12=2));Z11*2/I12;
Кумулятивный ЕСЛИ(K12=4;(((P12-J12)*(I12/12-ОКРУГЛВНИЗ((O12-1)/12;0)))/
((ОКРУГЛВНИЗ(I12/12;0)+1)*ОКРУГЛВНИЗ(I12/12;0)/2+(I12-12*
ОКРУГЛВНИЗ(I12/12;0))*(ОКРУГЛВНИЗ(I12/12;0)+1)/12))/(ЕСЛИ
(I12-12*ОКРУГЛВНИЗ((O12-1)/12;0)<12;I12-12*ОКРУГЛВНИЗ((O12-1)/12;0);12));
Производственный ЕСЛИ(K12=5;H12*(P12-J12)/H$11;
Амортизация за последний месяц ЕСЛИ(R11-J11<Q11;R11-J11;
Амортизация в других случаях M12)))))))

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

Заполнение рабочей таблицы

Бухгалтерскому учету в таблице (рис. 1) отведено три раздела, но только в одном из них вам потребуется вносить данные вручную ("Исходные данные"), остальные закрытые для редактирования разделы ("Бухучет", "Переоценка") будут заполняться автоматически.

Для этого достаточно будет внести в ячейки, обведенные линией, нужные формулы.

Формулы для расчета годовой амортизации, которые использовались на прошлом уроке, достаточно просты и очень удобны для проведения предварительного анализа. Однако в практическом учете, когда для расчета амортизации необходимо учитывать много различных факторов, их использование в таком виде становится затруднительным.

Приведенная формула расчета месячной амортизации является универсальным инструментом, с помощью которой независимо от объекта ОС, даты ввода его в эксплуатацию или срока полезного использования, изменения справедливой стоимости или метода амортизации вы получите:

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

Графа Порядковый номер и название

При введении в таблицу нового объекта ОС его порядковый номер и наименование записывается только один раз в строку исходных данных (ячейки А11 и В11, рис. 1). Чтобы иметь возможность идентифицировать данные каждого объекта ОС при выборке по любому критерию, в ячейке А12 записана формула "=ЕСЛИ (R11<=J11;0; A11)", а в ячейке В12 - "=ЕСЛИ(R11<=J11;0;B11).

Графа Дата учета

Здесь указывается дата, на которую рассчитаны данные в бухгалтерском и налоговом учете.

Все ячейки этой графы заполняются автоматически с помощью формулы

"=ЕСЛИ(ИЛИ(E$11=0;R11<=J11);0;ЕСЛИ(C11=0;КОНМЕСЯЦА
(E11;0)+1;ДАТАМЕС(C11;1)))"1 .

Она автоматически вносит значения первого числа каждого последующего после ввода в эксплуатацию объекта ОС месяца. Для активизации формулы внесите в ячейку Е11 дату ввода в эксплуатацию основного средства.

Графа Справедливая стоимость

В ячейку D11 вносится значение первоначальной стоимости основного средства. Остальные ячейки графы "Справедливая стоимость" содержат значения остаточной (балансовой) стоимости основного средства на 1-е число месяца без учета возможной переоценки (в ячейку С12 введена формула "=R12").

Стандарт 7 требует регулярно переоценивать основные средства, если их справедливая стоимость отличается больше чем на 10% от остаточной. Значение справедливой стоимости на конкретную дату учета вводится вручную. Таким способом вы можете проверить необходимость переоценки, на что укажет соответствующий символ в ячейке раздела "Переоценка" (символ "У" в ячейке U14, рис. 2). Чтобы автоматически выделять изменение справедливой стоимости, рекомендую использовать условное форматирование всех ячеек графы (рис. 2). Например, при изменении на 1 декабря 2000 г. справедливой стоимости факса Sharp FO50 ячейка D14 будет выделена серым фоном.

Рисунок 2

Графа Дата начала эксплуатации

В ячейках этой графы отражается не только дата первичного ввода в эксплуатацию объекта ОС (ячейка Е11, рис. 3), но и даты возобновления эксплуатации после ремонта, модернизации, консервации и т. п. Данные в эти ячейки вносятся вручную. Введенная дата должна соответствовать месяцу и году, которые указаны в соответствующей ячейке графы "Дата учета". Чтобы это контролировать, желательно использовать условное форматирование. Для ячейки Е12 воспользуйтесь формулой:

"=И($E12>0;ИЛИ(МЕСЯЦ($C12)<>МЕСЯЦ($E12); ГОД($C12)
<>ГОД($E12)))",

а для остальных используйте команду "Формат по образцу".

Графа Дата вывода из эксплуатации

Один из ключевых моментов в эксплуатации объекта ОС. Эта дата указывает на:

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

Введите в F12 формулу "=ЕСЛИ(E12>0;0;F11)".

Ячейки этой графы остаются пустыми до тех пор, пока в одну из них не будет вручную введена дата приостановки или вывода из эксплуатации. Введенное значение будет повторяться в каждом периоде до тех пор, пока в ячейку графы "Дата начала амортизации" не будет записана дата повторного ввода ОС в эксплуатацию (рис. 3). Если вы ошибочно ввели в ячейку значение, восстановите формулу в ней, скопировав ее из предыдущей ячейки.

Рисунок 3

Обратите внимание: дата вывода (F14, рис. 3) соответствует дате учета (те же месяц и год). Это важно для корректного формульного определения амортизации. Поэтому во избежание ошибочного ввода даты установите для ячейки F12 условное форматирование с помощью формулы (для других ячеек графы используйте команду "Формат по образцу"):

"=И(МЕСЯЦ($C12)<>МЕСЯЦ($F12);ГОД($C12)<>
ГОД($F12); $F12>0;$F12<>$F11)".

Графа Срок полезного использования

При правильном ведении бухгалтерского учета срок полезного использования (СПИ) необходимо пересматривать регулярно. Это связано как с изменением ожидаемых экономических выгод, так и с политикой предприятия в отношении ремонта и обслуживания ОС. Хотя Стандартом 7 явно не предусмотрена необходимость пересмотра СПИ при изменении ликвидационной стоимости и метода амортизации, игнорирование этого приведет к неправильному определению амортизации. Поэтому в приведенной таблице используется формула (ячейка I12, рис. 4), которая обеспечивает автоматическую корректировку СПИ: "=ЕСЛИ (R11<=J11;0;ЕСЛИ(ИЛИ(J11<>J12;K11<>K12);I11-O12;I11))".

Рисунок 4

При неизменности параметров расчета амортизации значение СПИ во всех ячейках графы будет одинаковым.

Например, на предприятии принято решение с 01.12.2000 г. изменить метод уменьшения остаточной стоимости на прямолинейный метод (ячейка К15 показывает изменение метода на начало отчетного периода 01.12.2000 - 01.01.2001 гг.). Так как это произошло спустя 2 месяца с начала амортизации, значение СПИ в ячейке I15 автоматически изменится с 13 на 11 (общий срок полезного использования не изменился). Если с 01.03.2001 г. изменена ликвидационная стоимость с 250 на 100 (ячейка J18), значение СПИ в ячейке I18 изменится с 11 на 8 (событие произошло через 3 месяца от последнего изменения СПИ (см. ячейку О17). Фактически значения в ячейках I15 и I18 показывают количество месяцев, оставшиеся до окончания полезного использования ОС, а общий СПИ при этом не меняется (проверьте, на 01.10.2000 г. осталось 13 месяцев до окончания СПИ, а на 01.04.2001 г. - 8).

Для реального изменения (уменьшения или увеличения) СПИ с конкретного месяца введите нужное количество месяцев, которое будет эксплуатироваться ОС. Например, если в ячейку I18 ввести число 15, это будет означать, что общий СПИ станет равным 20 (15 - 8 + 13). Однако в расчете нормы амортизации с этого периода будет учитываться значение СПИ = 15. (Не забудьте, что при вводе в ячейку любого значения формула стирается).

Графы Ликвидационная стоимость и метод амортизации

Установленная в начале эксплуатации ликвидационная стоимость (J11) и выбранный метод амортизации (К11) автоматически заносятся в остальные ячейки граф с помощью формул:

J12 - "=ЕСЛИ(R11<=J11;0;J11)",
К12 - "=ЕСЛИ(R11<=J11;0;K11)".

При изменении этих параметров значения вносятся вручную. Так как ввод нового значения ликвидационной стоимости или метода амортизации вызывает корректировку амортизации, лучше выделить такие ячейки, используя формулы условного форматирования (например фон):

для ячейки J12 - "=И($J12<>$J11;$J12)",
для К12 - "=И($K11<>$K12;$K12>0)".

Графа Группа ОС

Эта графа может использоваться для фильтрации данных в таблице по принадлежности объекта ОС к группе. Формула в ячейке L12 простая - "=ЕСЛИ(R11<=J11;0;L11)".

Графа Бухучет

Объединение в одной формуле возможности рассчитывать амортизацию всеми методами потребовало использования промежуточных результатов. Запишите в ячейке М12:

"=ЕСЛИ(ИЛИ(O12=0;R11<=J11);0;ЕСЛИ(ИЛИ(N12=
1;N12=2);Q12;M11))".

Графа Символ

Ячейки этой графы - вспомогательные, однако их роль при автоматическом расчете амортизации существенна. Введите в ячейку N12:

"=ЕСЛИ(A12=0;0;ЕСЛИ(ИЛИ(I11<>I12;J11<>J12;
K11<>K12; V11<>1;И(O12=1;O11=0));1;

ЕСЛИ(ИЛИ(МЕСЯЦ(C12)=2;И(E10>0;
ГОД(E10)>ГОД(F9)));2;0)))".

Эта формула возвращает в ячейку значение 1 или 2, используемые в формулах расчета месячной амортизации, которые являются "переключателями" базы расчета амортизации.

Символ "1" будет появляться в ячейке при изменении условий амортизации, которые влияют на расчет нормы амортизации (СПИ, ликвидационная стоимость, метод амортизации, проведение переоценки). Символ "2" указывает на начало отчетного года (для двух методов уменьшения остаточной стоимости это является расчетной базой).

Графа Месяц эксплуатации (амортизации)

Данные в ячейки этой графы заносятся согласно Стандарту 7, учитывая дату начала и окончания амортизации. Каждая строка таблицы содержит данные бухгалтерского и налогового учета на дату, указанную в ячейке графы "Дата учета". Например, если факс введен в эксплуатацию 21.09.2000 г. (Е11), ячейка О15 (рис. 3) указывает на то, что на 01.01.2001 г. на данный объект ОС амортизация начислена за 3 месяца. Вывод из эксплуатации факса 15.12.2000 г. (F14) не повлиял на начисление амортизации за декабрь 2000 г. При вводе его в эксплуатацию 10.03.2001 г. (Е17) амортизация станет начисляться только с 01.04.2001 г., поэтому в ячейке О19 будет записано "4".

Выполнение такого порядка учета периодов амортизации обеспечивается формулой (Е12):

"=ЕСЛИ(ИЛИ(O11=““;R11<=J11);0;ЕСЛИ (F10>0;O11;
ЕСЛИ (I11<>I12;1;O11+1)))".

Графа Первоначальная стоимость

Стандартом 7 первоначальная стоимость определена как историческая (фактическая) себестоимость объекта ОС. В приведенной расчетной таблице ее функции более широкие. Введите в ячейку Р12 формулу (рис. 5): "=ЕСЛИ(R11<=J11;0;ЕСЛИ(ИЛИ(I12<>I11;J12<>J11;K12<>K11; V11<>1);Z11;P11))".

Рисунок 5

Анализ Стандарта 7 позволяет сделать вывод, что первоначальная стоимость в качестве базы (!) для расчета амортизации должна меняться при изменении:

  • срока полезного использования (I12<>I11);
  • ликвидационной стоимости (J12<>J11);
  • метода амортизации (K12<>K11);
  • при переоценке (V11<>1).

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

Например, на предприятии принято решение об изменении с 1 января 2001 г. метода амортизации с прямолинейного на метод уменьшения остаточной стоимости (К15, рис. 5). Для правильного расчета амортизации необходимо на эту дату пересмотреть все компоненты расчета и определить их "начальное" значение:

  • на 01.01.2001 г. срок полезного использования (ячейка I15) изменится с 18 на 15 (прошло 3 месяца от начала амортизации, ячейка О15);
  • первоначальной стоимостью (для расчета) становится остаточная стоимость на эту дату (Z15);
  • с 01.02.2001 г. отсчет количества месяцев эксплуатации опять начинается с 1 (ячейка О16). Эта особенность применяется при использовании кумулятивного метода.

В ячейку Р11 запишите: "=D11".

Графа Месячная амортизация

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

При анализе приведенных в Стандарте 7 методов возникает целый ряд вопросов, связанных с расчетом амортизации. Например, как определить параметры расчета кумулятивного метода с нецелым количеством лет амортизации или как амортизировать объект ОС по методу ускоренного уменьшения остаточной стоимости при сроке полезного использования менее двух лет и т. д., и т. п. Использование прямолинейного метода не вызывает вопросов. Расчет амортизации по другим методам требует пояснения.

Метод уменьшения остаточной стоимости

Согласно Стандарту 7, месячная амортизация по этому методу определяется делением годовой нормы на 12. Это справедливо для случая, когда до конца срока полезного использования остается не меньше одного года, что отражено в формуле записью2 :

"1*(ЕСЛИ(I12-O12>=12;Z11*(1-СТЕПЕНЬ(J12/P12;12/I12))/12"

Если остается меньше года, для обеспечения полной амортизации в течение установленного СПИ необходимо рассчитанную годовую амортизацию делить на оставшееся количество месяцев:

"(Z11-J12)/(I12+1-O12)".

Метод ускоренного уменьшения остаточной стоимости

Это метод, при использовании которого фактическая амортизация происходит быстрее установленного срока полезного использования. Учитывая то, что месячная амортизация рассчитывается делением годовой на 12, в последнем месяце эксплуатации она должна быть равна разнице между остаточной стоимостью на начало месяца и ликвидационной (Q21 = R20 - J11, рис. 6).

Рисунок 6

Кумулятивный метод

Известно, что годовая норма амортизации по этому методу определяется умножением амортизируемой стоимости на кумулятивный коэффициент (КN). В Стандарте, казалось бы, все просто: чтобы его определить, надо разделить оставшееся до конца СПИ количество лет на сумму лет СПИ. Все ясно, когда СПИ равен целому числу лет. Например, СПИ = 3, тогда за первый год эксплуатации амортизируемая стоимость уменьшится наполовину (К1 = 3 : (1 + 2 + 3) = 1/2), за 2-й - на треть (К2 = 2 : (1 + 2 + 3) = 1/3), за 3-й - на одну шестую (К3 = 1 : (1 + 2 + 3) = 1/6), то есть вся стоимость "самортизируется" до ликвидационной (К1 + К2 + К3 = 1). А как быть, если, например, СПИ = 3,5? Каким будет знаменатель - 6, а может быть, 10 (1 + 2 + 3 + 4)?

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

  • знаменатель КN равен сумме слагаемых, первое из которых равно количеству лет СПИ, а последующие уменьшаются на 1. Например, СПИ = 3,75 года,

SСПИ = 3,5 + 2,5 + 1,5 + 0,5 = 8.

  • числитель КN для каждого года равен количеству лет СПИ, которое последовательно уменьшается на 1. Например, для второго года числитель КN будет равен 2,5 , а для четвертого - 0,5. Теперь проверьте:

1-й год - К1 = 3,5 : 8 = 0,4375;

2-й год - К2 = 2,5 : 8 = 0,3125;

3-й год - К3 = 1,5 : 8 = 0,1875;

4-й год - К4 = 0,5 : 8 = 0,0625.

0,4375 + 0,3125 + 0,1875 + 0,0625 = 1.

Именно такая методика используется в общей формуле расчета амортизации (таблица 1).

При использовании этого метода не путайте год эксплуатации с отчетным годом: при расчете учитывается только период, в течение которого объект ОС находился в эксплуатации (см. рис. 7).

Рисунок 7

Производственный метод

Для расчета амортизации по этому методу основным показателем является объем выпущенной продукции с использованием амортизируемого объекта ОС. На рисунке 8 показана графа "Объем продукции" (скрытая на предыдущих рисунках), в которой в ячейку Н11 вносится плановый объем выпускаемой продукции, а в остальные ячейки графы - фактическая месячная выработка. Для примера, приведенного на рис. 8, плановый объем - это 15000 листов, отпечатанных на принтере RS50. Месячная выработка заносится в соответствующие ячейки Н12:Н23. Обратите внимание: при использовании этого метода планируемый срок полезного использования не участвует в расчете амортизации, а является только сравнительным показателем для фактического срока амортизации (см. I23 и О23).

Графа Остаточная стоимость

В формуле расчета амортизации используется значение ячеек столбца Z из раздела "Переоценка" (см. таблицу 1, аргумент Z11). Учет переоценки будет рассмотрен на следующем уроке, поэтому как демо-вариант в общей формуле можно вместо Z11 записать R11.

Введите в ячейку R11: "=P11-Q11",

а в ячейку R12: "=ЕСЛИ(R11<=J11;0;R11-Q12)" (после заполнения раздела "Переоценка" введите в ячейку R12: "=ЕСЛИ(R11<=J11;0;Z11-Q12)").

Вот и все. Выделите строку №12 с внесенными формулами и скопируйте их вниз. Теперь вы сможете решить любые проблемы, связанные с расчетом амортизации. Вносите исходные данные, остальное за вас сделает Excel. Пользуйтесь.

На следующем уроке рассмотрим автоматизацию переоценки ОС и ее последствия в бухгалтерском учете.


Примечания:

1 Если на вашем ПК нет функции КОНМЕСЯЦА - значит, не установлена соответствующая надстройка. Установите "Пакет анализа" (меню "Сервис" - "Надстройки").

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


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

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