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

Автоматизації бухобліку: Excel

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

Володимир ЛАВРЄНОВ


Попередні уроки "Автоматизації бухобліку: Excel"

Секрети використання методів амортизації

Методика аналізу амортизації, викладена на минулому уроці, допомогла вам вибрати оптимальний метод (на момент початку експлуатації об'єкта основних засобів). Однак у процесі експлуатації основного засобу не тільки розраховуються показники бухгалтерського і податкового обліку, але й періодично може виникати необхідність змінити умови експлуатації. З цієї статті ви дізнаєтеся, як швидко і правильно розрахувати амортизацію будь-яким методом, не відхиляючись від вимог Стандарту 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). Якщо ви помилково ввели в комірку значення, відновіть формулу в ній, скопіювавши її з попередньої комірки.

Зверніть увагу: дата виведення (F14, мал. 3) відповідає даті обліку (ті ж місяць і рік). Це важливо для коректного формульного визначення амортизації.

Малюнок 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).

Малюнок 8

Графа Залишкова вартість

У формулі розрахунку амортизації використовується значення комірок стовпчика 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