Для расчета показателей движения ТМЦ на листе «ТМЦ» следует задать формулы, осуществляющие необходимые расчеты. Пример структуры таблицы приведен на рисунке 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-1003 | продукция 13 | ||||||||||||||
| 41-2002 | продукция 22 |
Рисунок 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 | Элеватор | ||||||
| МТС | МТС | ||||||
| РТП | РТП | -15000 | -15000 | ||||
| Сах з-д | Сахарный завод | -20000 | -20000 |
Рисунок 3.9. Общий вид модели учета взаиморасчетов.
В колонке D автоматически рассчитываются платежи партнеру за текущий период. Для ячейки D3 формула выглядит так:
-СУММЕСЛИ(Банк.Контрагент;Банк. Расход)+СУММЕСЛИ(Касса. Контрагент;Kacca.Pacxoд).
Формула суммирует данные расхода на рабочих листах "Банк" и "Касса". Структура листа "Касса" полностью аналогична, представленной листе "Банк". В колонке Е рассчитывается итог по платежам партнера:
=СУММЕСЛИ(Банк. Контрагент;$A3;Банк.Приход)+
СУММЕСЛИ(Касса.Контрагент;$АЗ;Касса.Приход).
Здесь суммируются приходы, зафиксированные на листах "Банк" и "Касса". В колонках F и G рассчитывается итоги по нашим поставкам партнеру и от партнера соответственно:
=СУММЕСЛИ(Продажи.Код;$A3;Продажи.Итого); =СУММЕСЛИ(Приход.Код;$АЗ;Приход.Итого).
Для фиксирования остатка взаиморасчетов применительно к строке 3 используется формула: =C3+D3-E3+F3-G3.
Итог взаиморасчетов равен сумме трех составляющих: остатка на начало периода, разности наших платежей партнеру и партнера нам и разности поставок партнеру и партнера нам. Дебиторская задолженность фиксируется со знаком "плюс", а кредиторская — со знаком "минус".