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

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

Урок 29
Аналіз товарно-матеріального запасу з допомогою EXCEL

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


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

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

Коефіцієнт оборотності товарно-матеріального запасу

Тривалість перебування товарів на складі впливає не тільки на розмір додаткових витрат (зокрема на зберігання), а й може істотно змінити собівартість товарів, що реалізуються. Тому важливо визначати мінімально необхідний запас товарів на складі, збільшуючи тим самим оборотність активів. Звичайно, на підприємстві може штучно створюватися надмірний запас товарів. Але таку ситуацію можна вважати радше винятком, і вона може бути виправдана у разі очікування підвищення цін.

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

собівартість проданих товарів
Коефіцієнт оборотності = --------------------------------------------------------
середня величина товарного запасу

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

Знаменник формули - це середня величина товарного запасу на початок і кінець періоду, що аналізується:

Зап.поч + Зап.кін
Середня величина товарного запасу =---------------------------------------------------------------------
2

де

Зап.поч і Зап.кін - товарний запас (у вартісному або кількісному вимірі) відповідно на початок і кінець періоду, що аналізується.


Як автоматизувати розрахунок коефіцієнта оборотності

На підприємствах, що мають широку товарну номенклатуру, важливо контролювати цей показник товарного запасу за кожною номенклатурною одиницею. Це нескладно зробити, використовуючи можливості створеної електронної таблиці, дещо модернізувавши її. Вставте на кожен аркуш таблиці по чотири додаткові графи, як показано на малюнку 1. Це не вплине на розрахунковий алгоритм введених у таблицю формул; їх аргументи автоматично відреагують на цю трансформацію - посилання у формулах будуть відповідно зсунуті.

Малюнок 1

Додаткові графи, в які будуть введені формули, дозволять одержати такі дані:

- дані про продані товари в кількісному або вартісному вираженні наростаючим підсумком з початку періоду, що аналізується (графа №13);

- коефіцієнти оборотності за звітний місяць і весь період, що аналізується (графи №16 і №17);

- тривалість обороту в днях (графа №18).

Для кількісного і вартісного варіантів визначення коефіцієнта оборотності у формулах буде використовуватися перемикач режиму розрахунку. Якщо ви захочете отримати КОБ виходячи з кількісних даних про товарообіг, слід буде ввести в комірку L2 першого аркуша таблиці (мал. 1) символ "К". Якщо ця комірка залишиться порожньою, розрахунок буде "вартісним".

Оскільки на першому аркуші таблиці "Товари на складі" (в наведеному прикладі це аркуш "СіЧЕНЬ 2002") записуються початкові залишки, розрахункові формули слід вводити тільки починаючи з другого аркуша.

Отже, відкрийте аркуш "ЛЮТИЙ 2002" (мал. 2) і введіть у комірку L7 формулу:

=ЕСЛИ('СіЧЕНЬ 2002'!L$2=“К“;
'СіЧЕНЬ 2002'!L7+I7;'СіЧЕНЬ 2002'!L7+K7)
.

Малюнок 2

У графі №131 наростаючим підсумком розраховуватиметься собівартість проданих товарів цієї номенклатури. Для цього підсумовуються дані попереднього і поточного звітного періоду.

На аркуші "БЕРЕЗЕНЬ 2002" в тій самій комірці треба буде ввести формулу:

=ЕСЛИ('СіЧЕНЬ 2002'!L$2=“К“;
'ЛЮТИЙ 2002'!L7+I7;'ЛЮТИЙ 2002'!L7+K7)

і так далі на всіх аркушах. Формули відрізнятимуться тільки посиланнями на аркуш попереднього місяця (виділені тлом).

Увага! Якщо ви почали вести облік, наприклад, з лютого 2002 р. і для введення початкових залишків2 використали аркуш "СіЧЕНЬ 2002", у комірку L7 наведену вище формулу не вводьте, оскільки в цьому випадку немає аркуша попереднього місяця.

У графі №16 розраховується коефіцієнт оборотності на підставі даних тільки одного місяця. Для того щоб отримати КОБ за рік, результат множиться на 12. У комірку О7 на аркуші "ЛЮТИЙ 2002" запишіть формулу:

=ЕСЛИ('СіЧЕНЬ 2002'!L$2=“К“;
24*I7/(E7+M7);24*K7/(F7+N7))
.

На аркуші "БЕРЕЗЕНЬ 2002" ця формула має точно такий самий вигляд:

=ЕСЛИ('СіЧЕНЬ 2002'!L$2=“К“;
24*I7/(E7+M7);24*K7/(F7+N7))
,

тому її можна скопіювати в комірку О7 на всі аркуші таблиці.

У графі №17 робиться розрахунок КОБ з урахуванням даних за весь період, що аналізується. Формула в комірці Р7 аркуша "ЛЮТИЙ 2002" така:

=ЕСЛИ('СіЧЕНЬ 2002'!L$2=“К“;
24*L7/('ЛЮТИЙ 2002'!E7+M7)/(МЕСЯЦ(E$4)-1);

24*L7/('ЛЮТИЙ 2002'!F7+N7)/(МЕСЯЦ(E$4)-1)).

Наведена формула буде однаковою для всіх аркушів таблиці, тому можете просто скопіювати її в комірки Р7.

Зверніть увагу: порівняно з попередньою в цій формулі з'явився додатковий аргумент (МЕСЯЦ(E$4)-1). Він використовується для того, щоб обчислити коефіцієнт оборотності за рік, враховуючи підсумкові дані періоду, що аналізується.

Наприклад, розраховується річний КОБ за підсумками березня 2002 р. (мал. 3). Оскільки в прикладі облік запасів ведеться з лютого 2002 р., розрахункові дані отримано наростаючим підсумком за два місяці. Величину періоду, що аналізується, визначає аргумент (МЕСЯЦ(E$4)-1), де "1" - номер місяця, в якому введено початкові залишки. У нашому прикладі це січень 2002 р. Він і коригує річний коефіцієнт. Якби початкові залишки були введені в лютому, аргумент, що розглядається, мав би вигляд (МЕСЯЦ(E$4)-2).

Малюнок 3

Коефіцієнти, що розраховуються у двох попередніх графах, дають можливість оцінити швидкість обороту товарного запасу за допомогою відносного вимірника. Абсолютний показник оборотності запасу - тривалість обороту - визначається в графі №18. Введіть у комірку Q7 просту формулу: =360/P7 і скопіюйте її в такі ж комірки інших аркушів. Фактично цей показник дає інформацію про те, на скільки днів вистачить товарного запасу цієї номенклатури за теперішньої інтенсивності поповнення запасу та його реалізації товарів. Розмір періоду оборотності змінюватиметься залежно від того, на чому базується розрахунок КОБ - вартості чи кількості.


Коефіцієнт оборотності з різних поглядів

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

При використанні кількісного розрахунку КОБ зміна методу оцінки собівартості запасів впливає на показники оборотності. Однак кількісний розрахунок КОБ не дасть відповіді на таке, наприклад, запитання: "Як часто доведеться вкладати відповідну суму для підтримання оптимального товарного запасу?".

На малюнку 4 показано залежність періоду оборотності товарного запасу від методу оцінки. Зверніть увагу: період оборотності для методу ЛіФО є вищим, ніж для методу ФіФО. Така ситуація складатиметься за умови підвищення цін на товари, що закуповуються. З наведеного прикладу можна зробити висновок, що вкладення коштів у запаси при використанні методу ЛіФО може бути менш інтенсивним.

Малюнок 4

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


Примітки:

1 Нумерація в основній частині таблиці змінена після додання додаткових граф.

2Як вводити початкові залишки, див. урок №28 ("ДК" №16/2002).

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