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

Автоматизации бухучёта: Excel

Урок 27
Учёт запасов на складе. Оценка выбытия с помощью EXCEL

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

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

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

- идентифицированной себестоимости соответствующей единицы запасов;

- средневзвешенной себестоимости (СВС);

- себестоимость первых по времени поступления запасов (ФИФО);

- себестоимость последних по времени поступления запасов (ЛИФО);

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

- цены продажи.

С помощью метода идентифицированной себестоимости устанавливают фактическую цену, по которой приобретается каждая единица товарно-материального запаса. Применение такого метода оправдано для торговых предприятий, у которых небольшая номенклатура дорогостоящих товаров (например автомобили). В этом случае совсем несложно определить точную цену каждой проданной единицы товара.

Метод нормативных затрат применяют в основном предприятия, у которых в себестоимости реализуемых ими товаров значительная доля производственных затрат. Так как этот метод предполагает для расчета себестоимости использование предварительно составленной калькуляции нормативных затрат на каждую товарную единицу, основной задачей бухгалтера остается следить за отклонением фактической себестоимости от нормативной. В случае превышения фактической себестоимости над нормативной к последней прибавляют расходы, связанные с изменением нормативов. В этом материале учет с помощью этих методов рассматриваться не будет.

Из приведенных выше методов с точки зрения автоматизации определения себестоимости запасов интересными представляются только три: средневзвешенной себестоимости (СВС), метод ФИФО и метод ЛИФО.

Наиболее распостраненные в торговых предприятиях методы определения себестоимости запасов иногда требуют значительных по объему вычислений. Использование для этих целей электронных таблиц Excel трудно переоценить.


Таблица "Товары на складе"

Откройте новую книгу, присвойте ей имя, например "ТМЦ", и для каждого месяца отчетного года создайте в ней на отдельных листах таблицу "Товары на складе".

Если вы решили использовать таблицу, например, с февраля 2002 г., в книге должен быть создан лист "СіЧЕНЬ 2002" (рис. 1), в который необходимо будет ввести начальные остатки. Как это сделать - см. ниже.

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

Рисунок 1

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

2. Движение товаров (О3:NNn)2. В этой части таблицы вручную заполняются ячейки разделов "Приобретено": записывается дата приобретения, количество полученных единиц товара и общая стоимость. Все остальные ячейки разделов "Стоимость проданных товаров" и "Остаток" заполняются автоматически. Их данные используются для расчета себестоимости по методу ФИФО и ЛИФО.


Раздел "Основная часть"

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

- в ячейки G4 и I4 - =Е4. В ячейку Е4 введите дату - первое число отчетного месяца;

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

Рисунок 2

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

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

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

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

Следующие четыре блока содержат данные по запасам на начало периода, итоговые данные движения запасов (приобретение, продажа) и данные по остатку на конец отчетного периода.

Блок "Запас наѕ" имеет две графы.

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.2002 (Е4).

3. Во втором и третьем блоке (т. к. было две закупки товара) в ячейки T4 и Y4 введите даты закупок (условно 12.02.2002 и 15.02.2002).

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