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

Автоматизации бухучёта: 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 - стоимость товаров, купленных 26 января 2002 г., равна нулю (R7:R9). Информация об этих товарах уже не будет использоваться в таблице при расчете себестоимости, поэтому этот блок ячеек можно удалить.

Внимание! Удалять можно только блоки, имеющие нулевые остатки по всем (!) наименованиям товаров.

Обратите внимание на второй блок - его удалять нельзя (один из товаров имеет положительный остаток - W7).

Рисунок 6

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

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

Рисунок 7


Примітки:

1 Размер интервала будет зависеть от количества используемых в таблице блоков.

2Об этом было рассказано в предыдущем уроке №27 (см. "ДК" №14).


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

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