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

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

Урок 28
Облік запасів на складі. Оцінка вибуття з допомогою EXCEL.

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


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

На минулому уроці було розглянуто приклад обліку запасів за допомогою електронної таблиці за методом середньозваженої собівартості. При цьому для розрахунку використовувалися дані тільки граф №1 і №2 з розділу "Рух товарів". інші графи залишилися невикористаними, оскільки основне їх призначення - накопичення даних для розрахунку собівартості за методом ФІФО або ЛІФО.

Використання методу ФІФО

Згідно з п. 19 Положення (стандарту) 9 "Запаси", оцінка запасів за методом ФІФО базується на припущенні, що запаси використовуються в тій послідовності, в якій вони надходили на підприємство (відображені в бухгалтерському обліку). Запаси, які вибувають першими, оцінюються за собівартістю запасів, які першими надійшли.

Розрахунок собівартості вибулих запасів за методом ФІФО потребує періодичного відображення надходження запасів на підприємство. Для дотримання цієї вимоги запис даних в електронну таблицю про нові придбання товарів має здійснюватися з дотриманням послідовності дат надходження запасів на склад.

Наприклад, дані про запаси, придбані 15 лютого 2002 р. (стовпчики Y, Z, мал. 1) мають бути розміщені в блоці, який розташований праворуч від блоку з даними про запаси, придбані 12 лютого 2002 р. Якщо після 15.02.20002 р. буде зроблено чергове поповнення товарного запасу, тоді в комірку AD4 (мал. 1) слід буде ввести дату придбання, а у відповідні комірки цього блоку записати кількість і вартість запасів, що надійшли.

Малюнок 1

Як було сказано на минулому уроці, в електронній таблиці розрахунок собівартості вибулих запасів за методом ФІФО базується на визначенні кількості товарів (графа №4 в кожному блоці), що залишилися в запасі після віднімання проданої кількості. Якщо на вашому підприємстві прийнято рішення про використання методу ФІФО, введіть у комірку R7 формулу масиву (формула ФІФО)

{=ЕСЛИ(И($D7=“Ф“;$I7>СУММ(($O$6:O$6=1)* $O7:O7));0;
ЕСЛИ(И($D7=“Ф“;$I7+O7>СУММ(($O$6:O$6=1)*$O7:O7));
СУММ(($O$6:O$6=1)*$O7:O7)-$I7;O7))}

і скопіюйте її у відповідні комірки цього ж рядка. Щоб використати розрахунок собівартості за вибраним методом, досить буде виділити комірки 7-го рядка розділу "Рух товарів" (О7:NN7)1 і скопіювати їх униз таблиці.

 

 

Як працює таблиця при використанні методу ФІФО

Отже, ви почали вести електронний облік руху запасів на складі з лютого 2002 р. Для цього використовується аркуш "ЛЮТИЙ 2002". Усі підсумкові дані про товари за попередній період, які мають бути заздалегідь внесені в аркуш "СІЧЕНЬ 2002"2, будуть автоматично перенесені в перший блок розділу "Рух товарів" з прив'язкою до конкретного дня їх придбання. Наприклад, при заповненні таблиці за лютий 2002 р. в перший рядок першого блоку (О7:Р7, мал. 2) занесено дані про залишок годинників Aria (за кількістю і вартістю), придбаних минулого місяця (26.02.2002 р.). Про особливості перенесення підсумкових даних за звітний місяць у таблиці наступних місяців буде розказано нижче.

Малюнок 2

Протягом звітного місяця в розділ "Рух товарів" послідовно вносяться дані про запаси, що надійшли на склад (їх кількість і вартість).

Для того щоб активізувати розрахунок собівартості за методом ФІФО, введіть у комірку графи "Оцінка собівартості" символ "Ф". Залежно від кількості проданих товарів цього найменування змінюється їх вартісна оцінка, тому щоб отримати дані про собівартість реалізованих за місяць товарів, треба зазначити їх загальну кількість.

Наприклад, за результатами торгової діяльності за лютий 2002 р. підприємством було реалізовано 27 шт. годинників Aria. Це значення треба ввести в комірку I7. Розрахунок інших показників проводиться автоматично. Погляньмо, як змінилася таблиця. Вартість 27 реалізованих годинників (1362,00 грн - комірка К7, мал. 3) складатиметься з двох вартісних груп - товарів, придбаних 26.01.2002 р. (12 шт.) і 12.02.2002 р. (15 шт.). Вартість першої групи дорівнює 612,00 грн (Q7, мал. 3), а вартість другої, розрахованої за формулою =(850 : 17) х 15, дорівнює 750,00 грн (V7). Таким чином, собівартість реалізованої товарної одиниці дорівнюватиме 50,44 грн (J7).

Малюнок 3

Для порівняння: собівартість цих же реалізованих товарів, розрахована за методом середньозваженої собівартості (СЗС), була б нижчою і дорівнювала 1348,74 грн. Перевірте це, ввівши в комірку D7 символ "С". Слід зазначити, що в цьому випадку порівняльне зниження собівартості відбувається за умови зростання цін на запаси, що придбаваються. При падінні цін спостерігатимемо обернену картину - збільшення собівартості у разі застосування методу СЗС замість методу ФІФО.

Використання методу ЛІФО

Метод ЛІФО оцінює вартість залишку товарного запасу за вартістю товарів, придбаних першими. У принципі, механізм визначення вартості запасу за методом ЛІФО такий само, як і за методом ФІФО. Відмінність полягає в тому, що вартість проданих товарів, як припускається, дорівнює вартості товарів, які надійшли на підприємство останніми. Так само, як і при використанні методу ФІФО, розрахунок собівартості за методом ЛІФО потребує періодичного і послідовного відображення в таблиці надходжень товарних запасів на склад підприємства.

Для того щоб автоматизувати оцінку товарного запасу за методом ЛІФО (з можливістю перемикання на метод СЗС), у комірку R7 введіть формулу масиву (формула ЛІФО):

{=ЕСЛИ(И($D7=“Л“;$I7-O7>СУММ((T$6:$FM$6=1)*T7:$FM7));0;
ЕСЛИ(И($D7=“Л“;$I7>СУММ((T$6:$FM$6=1)*T7:$FM7));
O7+СУММ((T$6:$FM$6=1)*T7:$FM7)-$I7;O7))}

Структура цієї формули подібна до формули ФІФО. Однак у ній є одна важлива відмінність - при копіюванні формули ФІФО в будь-який із сусідніх блоків усі її аргументи автоматично змінюються, дозволяючи включити в ділянку розрахунку будь-яку кількість блоків (формула при копіюванні зсувається праворуч, а ділянка розрахунку завжди залишається зліва).

У формулі ЛІФО ділянка розрахунку жорстко обмежена абсолютними посиланнями $FM$6 і $FM7 (формула при копіюванні зсувається праворуч, і ділянка розрахунку також залишається праворуч). інтервал комірок, що використовується в наведеній формулі ЛІФО (T:FM), дасть можливість забезпечити облік і розрахунок запасів, придбання яких здійснювалося щодня протягом місяця (тобто 31 блок). Залишилося тільки скопіювати перший рядок робочої частини таблиці з введеними формулами вниз - і можна користуватися. Для активізації розрахунку за методом ЛІФО введіть у комірку графи "Оцінка собівартості" символ "Л".

Проілюструймо сказане на прикладі використання методу ЛІФО. У лютому 2002 р. на підприємстві було реалізовано 27 шт. годинників Aria (I7, мал. 4), причому останню закупівлю цього товару в лютому зроблено 15.02.2002 р. (Y4, Z4). Розрахунковий цикл за методом ЛІФО починається з визначення умовного залишку товару, придбаного останнім. Якщо він більший від нуля, цикл продовжується. У цьому прикладі собівартість реалізованих товарів становитиме 1336,00 грн (686,00 грн - вартість 14 шт. (АА7) і 650,00 грн - вартість 13 шт. за ціною 850,00 : 17).

Малюнок 4

Отже, на прикладах торгової діяльності одного місяця ми розглянули автоматизацію розрахунку собівартості запасів. Для того щоб в електронній таблиці забезпечити безперервний обліково-розрахунковий процес, треба перенести підсумкові дані звітного місяця на аркуш наступного місяця.

Перенесення підсумкових даних на аркуш наступного місяця

Припускається, що у вас уже створено шаблон таблиці для наступного місяця, в який скопійовано необхідні формули. Процес перенесення даних залежатиме від того, який метод розрахунку собівартості використовується на підприємстві для цієї групи товарів.

Метод середньозваженої собівартості. При відкритті наступного місяця перенесення підсумкових даних попереднього місяця відбувається автоматично. Для розгляду особливостей цього методу використовуємо приклад, показаний на малюнку 5.

Малюнок 5

Отже, відкривши аркуш "БЕРЕЗЕНЬ 2002", вам треба буде врахувати таке:

- у комірках графи "Оцінка собівартості" має стояти символ "С";

- у комірку Е4 має бути записана дата 01.03.2002;

- комірки графи "Кількість" (блок "Продано за...") мають бути порожніми.

При автоперенесенні даних комірки розділу "Рух товарів" будуть заповнені значеннями попереднього місяця. Оскільки для методу середньозваженої вартості немає потреби відстежувати черговість придбання товарів, то треба залишити без зміни комірки тільки блоку №1 (О3:Sn, мал. 5). У всіх інших блоках слід видалити:

- дані про кількість і вартість придбаних товарів (виділено фоном);

- дату придбання (або змінювати її в міру придбання товарів).

Метод ФІФО. На відміну від методу середньозваженої собівартості, для методу ФІФО важливо зберегти інформацію про послідовність придбання товарного запасу. Як і для методу СЗС, переконайтеся, що в основному розділі таблиці стоїть символ "Ф", дата початку звітного місяця і порожні комірки графи "Кількість" ("Продано за...").

Малюнок 6

Характерною особливістю методу ФІФО є те, що зменшення запасу товарів умовно починається з товарів, куплених раніше. Тому може скластися ситуація, коли залишок товарів, придбання яких прив'язане до конкретної дати, дорівнюватиме нулю. Цей варіант показано на малюнку 6 - вартість товарів, куплених 26 січня 2002 р., дорівнює нулю (R7:R9). інформація про ці товари вже не використовуватиметься в таблиці при розрахунку собівартості, тому цей блок комірок можна видалити.

Увага! Видаляти можна тільки блоки, що мають нульові залишки за всіма (!) найменуваннями товарів.

Зверніть увагу на другий блок - його видаляти не можна (один із товарів має додатний залишок - W7).

Метод ЛІФО. Аналогічно до методу ФІФО, при використанні методу ЛІФО також важливо зберегти інформацію про послідовність придбання товарного запасу. У цьому випадку першим зменшуватиметься запас товарів, придбаних останніми. Однак після перенесення даних з попереднього місяця позбуватися зайвих блоків вам не доведеться. Досить буде замість старої дати "нульового" блоку внести нову дату придбання товарів і записати кількість і вартість купівлі.

Наприклад, першу березневу закупівлю товарів було зроблено 05.03.2002 р. Просто введіть цю дату в комірку Y4, а кількість і вартість запишіть у комірках стовпчиків Y і Z (формули можна "забивати").

Малюнок 7


Примітки:

1 Розмір інтервалу залежатиме від кількості блоків, що використовуються в таблиці.

2Про це було розказано в попередньому уроці №27 (див. "ДК" №14).

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