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

Автоматизации бухучёта: 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)
.

В графе №131 нарастающим итогом будет рассчитываться себестоимость проданных товаров данной номенклатуры. Для этого суммируются данные предыдущего и текущего отчетного периода.

Рисунок 2

На листе "БЕРЕЗЕНЬ 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