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

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

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

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


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

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

Положенням (стандартом) 9 "Запаси" встановлено, що для всіх одиниць запасів, які мають однакові призначення й умови використання, застосовується тільки один зі встановлених методів:

- ідентифікованої собівартості відповідної одиниці запасів;

- середньозваженої собівартості (СЗС);

- собівартість перших за часом надходження запасів (ФIФО);

- собівартість останніх за часом надходження запасів (ЛIФО);

- нормативних затрат;

- ціни продажу.

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

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

З наведених вище методів з точки зору автоматизації визначення собівартості запасів цікавими виглядають лише три: середньозваженої собівартості (СЗС), метод ФIФО і метод ЛIФО.

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


Таблиця "Товари на складі"

Відкрийте нову книгу, присвойте їй ім'я, наприклад "ТМЦ", і для кожного місяця звітного року створіть у ній на окремих аркушах таблицю "Товари на складі".

Якщо ви вирішили використовувати таблицю, наприклад, з лютого 2002 р., у книзі має бути створено аркуш "СІЧЕНЬ 2002" (мал. 1), у який слід буде ввести початкові залишки. Як це зробити - див. нижче.

Малюнок 1

На малюнку 1 показано таблицю для обліку запасів на складі і розрахунку собівартості проданої товарної одиниці. Таблиця складається з двох розділів.

1. Основна частина (А3:Мn)1. Загальні графи "Найменування товару", "Код товару", "Оцінка собівартості" і "Кількість проданих товарів за місяць" (стовпчик I, мал. 1) заповнюються вручну. інші показники основної частини розраховуються автоматично.

2. Рух товарів (О3:NNn)2. У цій частині таблиці вручну заповнюються комірки розділів "Придбано": записується дата придбання, кількість отриманих одиниць товару та загальна вартість. Усі інші комірки розділів "Вартість проданих товарів" і "Залишок" заповнюються автоматично. Їх дані використовуються для розрахунку собівартості за методом ФIФО і ЛIФО.


Розділ "Основна частина"

Дані, які записуються в обидва розділи, пов'язані між собою формулами. Почнімо з основної частини (А3:М7, мал. 2). Намалюйте її форму, як показано на малюнку 2, і заповніть "шапку", у якій запишіть такі формули:

- у комірки G4 і I4 - =Е4. У комірку Е4 введіть дату - перше число звітного місяця;

- у комірку L4 - =КОНМЕСЯЦА(E4;0), яка записує останній день звітного місяця.

Малюнок 2

Затінена ділянка основної частини (мал. 2) заповнюється автоматично за допомогою формул, які треба буде ввести тільки в перший рядок (А7:М7) і скопіювати його нижче.

Увага! В комірки D7 усіх аркушів введіть формулу ='СІЧЕНЬ 2002'!D7 (крім першого аркуша, в цьому випадку "СІЧЕНЬ 2002", в який заносяться початкові залишки) і скопіюйте її вниз. Зміна методу оцінки собівартості відбувається шляхом введення відповідного символу ("С", "Ф", "Л") тільки на аркуші початкових залишків (у прикладі - "СІЧЕНЬ 2002"). Формули скопіюйте вниз.

Залежно від особливостей ведення обліку запасів на підприємстві склад загальних граф основної частини таблиці ("№ з/п", "Найменування товару", "Код товару") може бути значно розширено. Це ніяк не вплине на роботу таблиці.

Графа "Оцінка собівартості". У показаній таблиці автоматизовано облік запасу за трьома методами: середньозваженої собівартості, ФIФО і ЛIФО. Для перемикання між цими методами у комірки графи вводяться символи, відповідно "С", "Ф", "Л".

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

Блок "Запас на..." має дві графи.

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

Введіть у комірку Е7 формулу масиву3:

{=СУММ((O$4:FM$4<E$4)*(O$6:FM$6=1)*O7:FM7)}.

2. У комірки графи "Вартість" записується загальна вартість запасу на початок звітного періоду (стовпчик F). Підрахунок загальної вартості здійснюється також за допомогою формули масиву:

{=СУММ((O$4:FM$4<E$4)*(O$6:FM$6=2)*O7:FM7)}.

Блок "Придбано за..." має дві графи.

1. У комірки графи "Кількість" (стовпчик G) за допомогою формули масиву автоматично записується кількість одиниць товарів, придбаних за звітний місяць. Запишіть у комірку G7:

{=СУММ((O$4:FM$4>=E$4)*(O$6:FM$6=1)*O7:FM7)}.

2. У комірках графи "Вартість" (стовпчик Н) групуються дані про сумарну вартість придбаних за звітний місяць товарів. Введіть у комірку Н7:

{=СУММ((O$4:FM$4>=E$4)*(O$6:FM$6=2)*O7:FM7)}.

Зверніть увагу: наведені вище формули масиву лише незначно різняться між собою (виділено тлом). Не забудьте: введення таких формул закінчується одночасним натисненням клавіш Ctrl-Shift-Enter.

Блок "Продано за..." має три графи.

1. У графі "Кількість" зазначається загальна кількість проданих протягом місяця товарів цього найменування. Значення вводиться вручну.

2. У графі "Вартість одиниці" розраховується середня вартість проданого товару залежно від вживаного методу собівартості. Введіть у комірку J7 формулу:

=ЕСЛИ(I7=0;0;ЕСЛИ(D7="С";(F7+H7)/(E7+G7);
ЕСЛИ(ИЛИ(D7="Ф";D7="Л");K7/I7))).

3. У графі "Вартість всього" автоматично записується загальна вартість проданих товарів за місяць. Дані групуються за найменуванням і кодом. Введіть у комірку К7 формулу масиву:

{=ЕСЛИ(D7="С";I7*J7;СУММ(ЕСЛИ(O$6:FM$6=
3;O7:FM7)))}.

Блок "Залишок на..." у двох графах містить дані про залишки кожного найменування товару на кінець звітного періоду.

1. У графі "Кількість" зберігається інформація про кількісний залишок товарів кожного найменування з урахуванням надходжень і продажу останнього дня місяця. У комірку L7 введіть формулу масиву:

{=ЕСЛИ($D7="С";E7+G7-I7;СУММ((O$6: FM$6=4)*
O7:FM7)}.

2. У графу "Вартість" заноситься сумарна вартість товарного залишку. У комірку М7 запишіть:

=ЕСЛИ($D7="С";F7+H7-K7;СУММ((O$6:FM$6=
5)*O7:FM7).

Після того як формули введено, виділіть комірки А7:М7 і скопіюйте їх вниз, враховуючи, що кожний рядок буде відведено для одного найменування товару. Основний розділ готовий.


Розділ "Рух товарів"

Це розділ є базою даних, в якій зберігатиметься поточна інформація про зміну товарного запасу підприємства і яка використовується для отримання підсумкових даних за кожною товарною одиницею. Розділ "Рух товарів" складається з однотипних блоків, що включають по п'ять граф. Кожен такий блок містить дані про товари, які були придбані протягом одного дня (графа №1 і графа №2).

На малюнку 3 показано блок (О3:Sn), що стосується дати 12.02.2002 р. У графах №3 (стовпчик Q), №4 (R) і №5 (S) розраховується зміна запасу товару при його реалізації з використанням одного з методів оцінки вибуття - ФІФО абоЛІФО.

Увага! Нумерація граф від 1 до 5 (комірки О6:S6, мал. 3) має повторюватися в кожному наступному блоці.

Малюнок 3

Для спрощення створення цього розділу таблиці заповніть шаблон для одного дня, а потім скопіюйте його потрібну кількість разів у сусідню комірку (для блоку другого дня це комірка Т3).

У шаблон треба буде ввести такі формули4:

- у комірку О4 запишіть формулу =`СІЧЕНЬ 2002`!O45. Ця формула і дві подібні, наведені нижче, забезпечує перенесення даних з попереднього місяця. На аркуші "Березень 2002" у цій комірці має бути формула =`ЛЮТИЙ 2002`!O4. Це стосується і комірок О7 і Р7;

- у комірку Р4 запишіть формулу =О4 для дублювання дати купівлі (комірка О4 заповнюється вручну). Наявність однакових дат у двох суміжних комірках обов'язкова (!) для автоматизації підрахунку загальної кількості і вартості товарного запасу;

- у комірку О7 введіть формулу =ЕСЛИ($D7="С";'СІЧЕНЬ 2002'!$L7;`СІЧЕНЬ 2002`!R7;

- у комірку Р7 введіть формулу =ЕСЛИ($D7="С";`СІЧЕНЬ 2002`!$M7;`СІЧЕНЬ 2002`!S7;

- у комірку Q7 графи "Вартість проданих товарів" введіть формулу =ЕСЛИ(O7=0;0;(O7-R7)*P7/O7);

- у комірку S7 графи "Вартість" (Залишок) запишіть формулу =P7-Q7;

- у комірку 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))};

- для методу ЛІФО:

{=ЕСЛИ(И($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))}.

Тепер можете копіювати введені в перший блок формули (O3:S7, мал. 3) і вставляти їх послідовно у першу комірку сусіднього блоку (Т3, мал. 3) потрібну кількість разів. Після цього виділяйте весь рядок розділу "Рух товарів" і копіюйте його вниз. Таблиця готова.


Як почати працювати з таблицею. Внесення залишків

Записані в запропонованій електронній таблиці формули дозволяють використовувати її з місяця в місяць протягом необмеженого часу. Кожен аркуш таблиці є не тільки обліковою базою даних товарів, але й дозволяє переносити підсумкові результати звітного місяця в таблицю наступного місяця. Щоб забезпечити це, в комірки блоків "Придбано" внесено формули з посиланнями на відповідні елементи таблиці попереднього місяця (типу {=`СІЧЕНЬ 2002`!R7}). Тому перший аркуш таблиці (у цьому випадку "СІЧЕНЬ 2002") має містити дані про придбані (і не продані!) товари на 31.01.2002 р. Для цього в комірки введіть дані про залишки в кількісному і вартісному вираженні (R7:S9, мал. 4).

Малюнок 4


Як працюють у таблиці методи СЗС, ФІФО і ЛІФО

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

Використання таблиці проілюстровано на прикладі обліку і розрахунку собівартості при вибутті товару "Годинники Aria" (мал. 5).

Малюнок 5

Облікові дані наведено для лютого 2002 р. На 01.02.2002 р. запас цього товару становив 12 шт. Протягом місяця зроблено дві закупівлі товару по 17 шт. і 14 шт. (вартістю відповідно 850 грн і 686 грн). Послідовність дій така:

1. Внесіть найменування товару, його код (В7, С7) і в комірці D7 встановіть символ оцінки собівартості - "С".

2. Встановіть дату початку звітного місяця - 01.02.02 (Е4).

3. У другому і третьому блоці (оскільки було дві закупівлі товару) в комірки T4 і Y4 введіть дати закупівель (умовно 12.02.02 і 15.02.02).

4. У кожному з трьох блоків заповніть дані про кількість і вартість закупівель (O7, P7; T7, U7; Y7, Z7).

5. Введіть у комірку I7 кількість проданих за місяць товарів.

У результаті автоматично буде зроблено розрахунок:

- кількості і вартості придбаних за місяць товарів (G7, H7);

- середньозваженої вартості проданої одиниці товару (J7) і загальної вартості продажу за місяць (К7);

- кількість товарів у залишку і його вартість на початок наступного місяця.

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


Примітки:

1 Кількість рядків у таблиці визначається номенклатурою товарів на підприємстві.

2Кількість стовпчиків у таблиці залежатиме від кількості днів у звітному місяці, коли відбувалося поповнення товарного запасу. На мал. 1 показано фрагмент таблиці, де видно дані щодо товарів, пов'язаних з однією січневою (26.01.02) і двома лютневими закупівлями (12.02.02 і 15.02.02).

3У формулах масиву, введених у таблицю, використовуються посилання на інтервали комірок типу "O$n:FM$n", права межа в яких визначається кількістю облікових днів у таблиці. Наведена формула забезпечує розрахунок даних для 31-го облікового дня.

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

5Передбачається, що таблиця використовується починаючи з лютого і в створеній вами книзі "ТМЦ" є аркуш "СІЧЕНЬ 2002" з такою ж структурою, як аркуш "ЛЮТИЙ 2002".

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