Блок учета хозяйственной деятельности

Основная таблица учета движения ТМЦ

Больше всего времени при работе с учетным приложением тратится на заполнение таблицы движения ТМЦ, поэтому, чем удобнее будет организован ввод в эту таблицу, тем больше пользы принесет разработанное учетное приложение.

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

Таблица 2. Формат журнала движения ТМЦ

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

При заполнении таблицы для части полей необходимо организовать выбор соответствующего значения из ранее заполненных справочников для следующих полей: Наименование продукта, Поставщик, Клиент, Сотрудник, ответственный за сделку.

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

Обратите внимание, что каждая строчка таблицы отражает только ОДНУ ОПЕРАЦИЮ из трех возможных – это или приход товара на склад, или его списание со склада (расход), или списание бракованного товара.

Таким образом, если регистрируется приход товара, то поля «Объем продаж товара», «Выручка за товар» не заполняются. Наоборот, если регистрируется расход, то поля «Объем выпуска (закупки) товара», «Затраты на производство товара (закупочная стоимость)» остаются пустыми. Таким образом, беглого взгляда на таблицу достаточно, чтобы увидеть позиции прихода и расхода.

Для выполнения автоматических расчетов затрат по выпуску (закупке) и выручке необходимо «вытаскивать» необходимые поля (цена закупки, цена продажи, вес единицы измерения) из справочников. Для этого также нужно использовать функцию ВПР().

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

Для реализации этого механизма необходимо подсчитывать текущий остаток по каждому товару, для этого удобно использовать справочник товаров, дополнив его столбцом «Наличие на складе, шт», значения в котором должны обновляться после каждой операции прихода-расхода. Для выполнения сравнения понадобится функция СУММEСЛИ().

Требования к итоговой версии таблицы учета движения ТМЦ

1. Обеспечение удобства ввода данных. Использовать выпадающие списки для выбора типа операции, товара, клиента, поставщика, ответственного.

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

3. Обеспечение наглядности таблицы учета. Выделять одним цветом строки с операциями прихода, другим цветом - строки с расходом. Использовать условное форматирование и создание собственных формул.

Используя формулу ЕСЛИ(), выводить или не выводить соответствующую единицу измерения товара. Так, если выбрана операция «Приход», выводить единицу измерения после поля регистрации прихода и не выводить ее для расхода, и наоборот.

4. Вычисление суммарной прибыли и уровня рентабельности всей хозяйственной деятельности. Прибыль и рентабельность должны пересчитываться по итогам каждой операции прихода-расхода. Требуется вычислять суммарную прибыль и рентабельность по данным движения всех товаров.

5. Вычисление прибыли и рентабельности по каждому товару.

Для расчета этих показателей требуется знать способ распределения постоянных затрат на выпускаемую и (или) производимую продукцию.

Учет постоянных затрат при производстве или закупке товара

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

Очевидно, чем дороже товар и чем больше его произведено, тем большая доля постоянных затрат должна быть отнесена к этому товару. Для расчета затрат по i-му товару (i = 1,…. N) будем использовать формулу:

Затраты i = Затр_перем i + Затр_пост i (1)

где Затр_пост i – доля постоянных затрат, отнесенная к i-му товару

Затр_пост i = k * Затр_перем i (2)

Формула (2) отражает тот факт, что вклад постоянных затрат должен быть прямо пропорционален переменным затратам, т.е. чем больше мы тратим средств на производство (приобретение) товара, тем большая доля постоянных затрат должна приходиться на этот товар.

Из (1) и (2) имеем:

Затраты i = Затр_перем i * (1 +k) (3)

Затр_перем i = Сi * Vi (4)

где Сi – себестоимость производства 1 кг товара в случае выпуска, или закупочная цена 1 ед. измерения товара в случае его закупки, и соответственно Vi – объем произведенного товара (в кг) в случае собственного производства, или количество единиц измерения закупленного товара.

По условиям задачи общие постоянные затраты нам известны. Для определения k используем следующие соотношения:

Затр_пост 1 + Затр_пост 2 + … + Затр_пост N = Затр_пост

k * (Затр_перем 1 + Затр_перем 2 + … + Затр_перем N) = Затр_пост

k = Затр_пост / Затр_перем

Отметим важный смысл коэффициента k: через этот коэффициент определяется вклад постоянных затрат Вклад_пост_затр (в процентах) в себестоимость товара. Для оценки этого вклада запишем:

Вклад_пост_затр, % = Затр_пост i / Затраты i * 100 %

Вклад_пост_затр, % = k / (1+k) * 100 % (5)

Формула (5) важна тем, что дает понимание, за счет каких показателей добиваться повышения эффективности производства. Если k << 1, соответственно вклад постоянных затрат мал, то нужно сокращать переменные затраты. Если же значение k большое, то кроме сокращения переменных затрат имеет смысл подумать о способах сокращения постоянных затрат.