Вычисление показателей движения товарно-материальных ценностей

Для расчета показателей движения ТМЦ на листе «ТМЦ» следует задать формулы, осуществляющие необходимые расчеты. Пример структуры таблицы приведен на рисунке 6. В таблице по каждому виду материальных ценностей в колонке С проставляется код субсчета, на котором этот виц ценностей учитывается. В колонках D и Е задаются остатки на начало периода в натуральном (D) и стоимостном выражении (Е). Первый раз они вводятся вручную, далее копируются из остатков на конец прошлого периода.

В колонке F автоматически рассчитывается приход данного вила ценностей в натуральном выражении, в колонке G — в стоимостном, а в колонке Н -суммируется НДС подлежащий оплате в связи с поступлением ценностей данного вида.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Код	Название	Начальный. остаток			Приход			Сред. Цена	Продажи		Остаток		Реализация
ТМЦ	ТМЦ	Счет	Кол	Сумма	Кол	Сумма	НДС		Кол	Себест-ть	Кол	Сумма	Сумма	Прибыль	НДС
41-1001	продукция 11	41	200	2400	0	0	0	12	0	0	200	2400	0	0	0
41-1003	продукция 13	41	200	8400	0	0	0	42	0	0	200	8400	0	0	0
41-2002	продукция 22	41	0	0	0	0	0	0	0	0	0	0	0	0	0

Рисунок 3.8. Структура таблицы движения ТМЦ

Для строки 3 соответствующие формулы выглядят следующим образом:

=СУММЕСЛИ(Приход.Код;$АЗ;Приход.Кол); =СУММЕСЛИ(Приход.Код;$АЗ;Приход.Сумма);

=СУММЕСЛИ(Приход,Код;$АЗ;Приход.НДС).

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

В колонке I рассчитывается средневзвешенная цена данного вида ценностей. Формула в строке 3 выглядит так:

=EСЛИ(D3+F3>0;(E3+G3)/(D3+F3);0).

Формула означает, что если данный вид материальных ценностей имеется в наличии, то средняя цена фиксируется как сумма остатка на начало и прихода за период в стоимостном выражении, отнесенная к сумме остатка и прихода в натуральном выражении. Если ценности данного вида нет в наличии, то ее средняя цена фиксируется как нулевая. В колонке J по данным листа «Продажи» рассчитывается итог по продажам каждого вида материальных ценностей в натуральном выражении. Для третьей строки формула выглядит таким образом: =СУММЕСЛИ(Продажи.Код;$A3;Продажи.Кол).

В колонке К производится расчет себестоимости проданного товара путем перемножения проданного количества на средневзвешенную цену; =J3*I3.

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

В колонке L фиксируются остатки товаров в натуральном выражении, а в колонке М — в оценке по себестоимости. Здесь формулы :

=D3+F3-J3 — для ячейки L3;

=E3+G3-K3 —для ячейки МЗ.

В столбце N вычисляется сумма реализации в продажных ценах за вычетом НДС. Для ячейки N3 формула выглядит так:

=СУММЕСЛИ(Продажи.Код;$АЗ;Продажи.Сумма).

В колонку О сведены данные о прибыли от реализации как разницы между суммой продаж без НДС и себестоимостью проданного товара. Формула для ячейки 03: =N3-K3.

В столбце Р показан НДС реализованного товара, который также определяется путем условного суммирования данных на листе «Продажи»:

=СУММЕСЛИ(Продажи.Код;$АЗ;Продажи.НДС).

При необходимости можно дополнить таблицу формулами расчета других показателей.

Для формирования автопроводок определите на листе «ТМЦ» имена:

ТМЦ.Счет — для диапазону $C$3 :$С$4000;

ТМЦ.Приход.Сумма - для диапазона $G$3 :$G$4000:

ТМЦ.Приход.НДС — для диапазона $H$3:$H$4000;

ТМЦ.Продажи.Себест -для диапазона $К$3:$К$4000;

ТМЦ.Продажи.Сумма-для диапазона $N$3:$N$4000;

ТМЦ.Продажи.НДС -для диапазона $Р$3:$Р$4000.

Тогда на листе «Авто про водки» можно определить основные проводки по движению товаров с автоматически рассчитываемыми суммами.

УЧЕТ ВЗАИМОРАСЧЕТОВ

Формулы, подводящие итоги взаиморасчетов с каждым партнером, разместите на листе «Контрагенты». Там пока заведен только справочник контрагентов, который уже активно задействован на других рабочих листах.

Дополните его. Общий вид представлен на рисунке 8.

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

Код	Название	Нач. остаток	Платежи		Поставки		Кон. Остаток
Нефт	Нефтебаза		Наши	Партнера	Наши	Партнера
Э №1	Элеватор	10000		0	0	0	10000
МТС	МТС	9000	0	0	0	0	9000
РТП	РТП	-15000	0	0	0	0	-15000
Сах з-д	Сахарный завод	-20000	0	0	0	0	-20000

Рисунок 3.9. Общий вид модели учета взаиморасчетов.

В колонке D автоматически рассчитываются платежи партнеру за текущий период. Для ячейки D3 формула выглядит так:

-СУММЕСЛИ(Банк.Контрагент;Банк. Расход)+СУММЕСЛИ( Касса. Контрагент;Kacca.Pacxoд).

Формула суммирует данные расхода на рабочих листах "Банк" и "Касса". Структура листа "Касса" полностью аналогична, представленной листе "Банк". В колонке Е рассчитывается итог по платежам партнера:

=СУММЕСЛИ(Банк. Контрагент;$A3;Банк.Приход)+

СУММЕСЛИ(Касса.Контрагент;$АЗ;Касса.Приход).

Здесь суммируются приходы, зафиксированные на листах "Банк" и "Касса". В колонках F и G рассчитывается итоги по нашим поставкам партнеру и от партнера соответственно:

=СУММЕСЛИ(Продажи.Код;$A3;Продажи.Итого); =СУММЕСЛИ(Приход.Код;$АЗ;Приход.Итого).

Для фиксирования остатка взаиморасчетов применительно к строке 3 используется формула: =C3+D3-E3+F3-G3.

Итог взаиморасчетов равен сумме трех составляющих: остатка на начало периода, разности наших платежей партнеру и партнера нам и разности поставок партнеру и партнера нам. Дебиторская задолженность фиксируется со знаком "плюс", а кредиторская — со знаком "минус".

ПЕРЕХОД К СЛЕДУЮЩЕМУ ПЕРИОДУ

Общая схема перехода к новому периоду такова. Файл рабочей книги с текущей информацией копируется, и копии присваивается новое имя. Далее в полученной копии остатки на конец периода средствами Excel записываются как входящие остатки данного периода, переносятся только их значения, а не формулы. Для этого используется функция «Специальная вставка». Обороты прошлого периода во вновь созданной копии необходимо обнулить: стираются все введенные ранее вручную проводки данные документов по движению товарно-материальных ценностей и прочие вспомогательные данные, относя­щиеся к оборотам прошлого периода. После чего можно начинать ввод информации нового текущего периода. При этом старая информация всегда со­храняется в отдельном файле.

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

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

Теперь нужно перенести остатки по взаиморасчетам с контрагентами на листе "Контрагенты". Здесь показатели зависят от данных па листах«Банк», "Касса» и «ТМЦ», поэтому перенос обязательно следует выполнить до сброса оборотов на этих листах. Методика переноса остатков та же, что и на листе «Субсчета», — с помощью функции «Специальная вставка». Обороты на листе «Контрагенты» трогать не надо.

Перейдите на лист «Банк». В качестве входящего остатка ячейка Л, введите исходящий остаток ячейка J4. Далее сотрите записи по движению средств на расчетном счете. Обороты по банку обнулялись. Проверьте это по листам "Автопроводки", "ГлКнига", "Субсчета".

Выполните точно такие же действия на листе "Касса". Далее следует перейти на лист «ТМЦ» и перенести остатки там. Выделите нужный диапазон ячеек и столбцах, содержащих остатки на конец периода в натуральном и стоимостном выражении, и с помощью режима специальной вставки перенесите значения и столбцы D и Е.

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

Теперь нужно перенести суммы износа на конец периода в суммы износа на начало периода на листе "Учет основных средств" (УОС); Это действие выполняется аналогично тому, что было проделано на листе "Субсчета". После переноса сумм износа формулы на листе "УОС" сработают, и будет автома­тически рассчитан износ на конец нового периода. Необходимо также проставить нормы амортизации у тех объектов учета, которые были введены в эксплуатацию в прошлом периоде. Сведения о поступивших в текущем периоде объектах предварительно вносятся на лист "УОС"- с нулевой нормой амортизации, чтобы износ за данный период не начислялся.

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