Реестр «Продаж и закупок» имеет такой формат.
Рис. 1. Реестр «Закупок и продаж»
Этот реестр был сделан в качестве примера для торгового предприятия, в котором товар закупается для последующей перепродажи.
Если предприятие занимается производством, то процесс закупки можно заменить процессом передачи готовой продукции из производства для последующей продажи.
Как и для любого реестра, создание этого реестра начинается с «шапки», в процессе работы с реестром «шапка» может меняться, Excel позволяет делать это достаточно легко. Состав столбцов «шапки» определяются исходя из той информации, которая у вас есть в первичных документах, и из тех признаков, которые необходимо отследить.
Пояснения по «шапке» на рис. 1.
1. Месяц и дата (число месяца). Все параметры даты: число, месяц, год (в данном примере его нет) лучше заносить в разные столбцы, так легче работать с формулами и фильтрами.
2. Наименование – наименование у товара должно быть уникальным и во все реестры должно заноситься одинаково, иначе отчеты и фильтры будут работать некорректно и не позволят получить необходимую информацию.
3. Столбец «Код (артикул)» – это уникальный код товара в вашей учетной системе. Его основная цель – идентифицировать товар для последующей аналитики. При помощи кода можно, например, проводить анализ продаж товаров в зависимости от поставщика, партии, акции, региона продаж и т. д. В качестве этого кода может быть использован штрих-код или артикул продавца, в большинстве случаев так даже удобней, но, если нужно отслеживать более мелкие признаки, нужна будет своя параллельная кодировка. Для удобства в реестре может быть два столбца, например, штрих-код и внутренний код.
При помощи кодов можно вводить группировки товара, что будет полезно при большой номенклатуре или при значительном разбросе цен на похожие товары.
4. Столбец «Признак» – это признак, обозначающий движение товара, например:
остаток – остаток товара в зале на начало;
покупка – поступление товара от поставщика;
зал – передача товара в зал по цене продажи;
продажа – продажа товара в зале конечным покупателям;
заказ – предварительный заказ (без движения товара);
заказ выполнен / не выполнен – результат исполнения заказа.
Этот признак присваивать нужно обязательно, и для однотипных процессов названия должны быть написаны всегда одинаково, иначе формулы и фильтры будут работать некорректно.
5. Прейскурант – цена, по которой товар продается в зале.
6. Столбцы «Поставщик», «Доставка», «Оплата (отсрочка), «Примечание (поставщик)», Документ покупки» – столбцы, характеризующие работу с поставщиком. Наименование поставщика, как все наименования, должно быть одинаковым во всех реестрах и отчетах. Остальные столбцы заполняются в произвольной форме и носят справочный характер.
В данном реестре отражаются только поставщики товара, который предназначен для продажи. Учет прочих поставок материалов и услуг в данном реестре не ведется, при необходимости для них нужно сделать отдельный реестр.
6) Столбцы «Количество», «Сумма», «Цена ед., с НДС», «Ставка НДС», «Цена ед., без НДС» – блоки про цену и количество закупок и продаж.
7) Оставшаяся часть столбцов реестра характеризует работу с покупателем, этот блок заполняется в произвольной форме и носит справочный характер.
Важно!!! Все наименования товаров, названия поставщиков и покупателей в реестре пишутся всегда одинаково. Чтобы легче было заполнять названия, в соответствующей ячейке нужно нажать на правую кнопку мыши и выбрать пункт «Выбрать из раскрывающего списка». Если таблицы Excel являются только дополнительным аналитическим инструментом, и параллельно учет ведется в бухгалтерской программе, лучше заносить названия контрагентов и товаров как в этой программе.
В нашем примере рассмотрен случай, когда весь товар хранится в зале, склада нет. При наличии склада в столбце «Признак» нужно будет указывать признак склада.
Если на момент начала ведения реестра в торговом зале были остатки, то их можно занести таким образом.
Рис. 2. Занесение остатков товара в реестр «Продаж и закупок»
Далее рассмотрим отражение в реестре закупки, передачи в зал и продажи.
Результат заполнения в реестре покупки товара у поставщика имеет следующий вид.
Рис. 3. Занесение данных по покупке товара у поставщика
Передача товара в зал отражается отдельной строкой. Наименование товара, артикул, количество переносим из строк о покупке. Сумма определяется расчетным путем: цена из прейскуранта умножается на количество. НДС также выделяется расчетным путем, исходя из действующего режима налогообложения. Признак ставим «Зал».
Рис. 4. Отражение в реестре передачи товара в зал
Продажа в зале отражается аналогично передаче товаров в зал, только нужно писать признак «Продажи» и количество – фактически проданное за день.
Рис. 5. Отражение в реестре продаж
Реестр «Продажи и закупка» будет обновляться ежедневно. Чтобы при каждом обновлении не переписывать ссылки и формулы, реестр нужно преобразовать в «умную таблицу». (Для этого нужно выделить таблицу, войти в меню в раздел «Вставка» и нажать кнопку «Таблица», после чего таблица примет формат как на рисунках выше.)
Рис. 6
При занесении новых данных формат «умной таблицы» распространяется на новую строку автоматически, при этом переносятся все формулы, которые были в реестре. Для нашего реестра формула расчета цены для «Покупки» отличается от формул для строк с признаками «Зал» и «Продажа», поэтому при работе с умными таблицами нужно быть внимательным, но из соответствующих строк формула легко копируется и вставляется в нужном формате.
Учет движения товара в форме реестра позволяет не только работать с фильтрами, но и строить на его основе большое количество аналитических таблиц.
Вот несколько примеров сводных таблиц.
Рис. 7. Сводные таблицы по реестру «Продажи и закупки»
Сводные таблицы удобно использовать для быстрой аналитики и проверки правильности занесения данных, но удобнее работать в отчетах с постоянными формами.
В реестре «Продажи и закупки» отражаются следующие движения товара: закупка от поставщика – передача в зал – продажа покупателям.
В процессе передачи товара в зал ему присваивается цена продажи, поэтому важно, чтобы эта операция была занесена в реестр. В отчете «Движение товара (закупка)» сравнивается количество товара, поступившего от поставщика и переданного в зал.
Рис. 8. Отчет «Движение товара (закупка)»
Если весь товар, который поступил от поставщика, передан в зал с наценкой, то остаток будет равен 0.
В этой же форме можно провести предварительную проверку уровня наценки. Для этого в столбце «Рентабельность» рассчитывается соотношение «суммы продаж, без НДС» и «суммы покупки, без НДС». Из таблицы видно, почему нельзя для расчета рентабельности использовать суммы с НДС. При разных режимах налогообложения у покупателя и продавца рентабельность будет рассчитываться некорректно.
Данные из реестра заносятся при помощи формулы СУММЕСЛИМН
=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Наименование];A6;ПродажиЗакупки[Признак];"Покупка")[2] – эта формула суммирует данные по количеству товара, приобретенного у поставщика.
Так она выглядит в диалоге «Мастера формул».
Рис. 9. Занесение формулы для ячейки В6 (из рис. 7)
В отчете «Движение товара (закупка)» для того, чтобы просуммировать данные по закупкам без НДС, в реестр «Продажи и закупки» был добавлен столбец «Сумма без НДС».
Рис. 10. Реестр «Продажи и закупки» с добавленным столбцом «Сумма без НДС»
Учет операций в реестре позволяет легко добавлять новые столбцы, на формулы отчетов это не влияет.
Контролировать остатки товара в зале можно при помощи этого отчета.
Рис. 11. Отчет «Движение товара (продажи)»
Рис. 12. Формулы для отчета «Движение товара (продажи)»
В отчете стоимость рассчитывается только для остатков, так как отчет формируется на текущую дату, а цены на товар могут меняться, поскольку реестр «Продажи и закупки» ведется в рамках года.
На следующих рисунках будет показано, как изменения в реестре отражаются на данных отчетов по движению товаров.
Продемонстрируем ситуацию, когда товар принят от поставщика, но в зал не передан.
Рис. 13. Отражение в отчете «Движение товара (закупка)» операций по покупке без последующей передачи в зал
В столбце «Остаток» отчета появилась красная ячейка (выделение сделано при помощи условного форматирования), которая показывает, что 100 единиц товара не были переданы в зал.
После добавления в реестр передачи в зал отчет примет такой вид.
Рис. 14. Отражение в отчете «Движение товара (закупка)» операций по покупке без последующей передачи в зал
В отчете «Движение товара (продажи)» данная операция отразится следующим образом.
Рис. 15. Количество поступившего товара с кодом 102 увеличилось на 100 по сравнению с предыдущим отчетом из рис. 10
Посмотрим, как отразится в отчете продажа товара, превышающая остаток.
Рис. 16. Отражение продаж из реестра «Продажи и закупки» в отчете «Движение товара (продажи)»
В п. 1.3 на рис. 6 был рассмотрен отчет «Движение товаров (продажи)», в котором рассчитывались остатки в торговом зале. Это отчет показывает остатки только на текущую дату, что не всегда удобно, например, при изменении цен или при необходимости поднять информацию на определенную дату.
Для отслеживания движения и остатков товаров по дням нужно сделать более сложный отчет.
Рис. 17. Отчет об остатках товара по дням
Рассмотрим новый отчет более подробно по выделенным блокам.
Первый блок (синий): Движение товаров
Рис. 18. Отчет об остатках товара по дням (количественные показатели)
В этом блоке отражено ежедневное движение товаров только по количественным показателям без цены.
Теперь более подробно о формулах. Формулы ссылаются на реестр «Продажи и закупки».
Начальный остаток вводится один раз, его можно занести значениями или простой ссылкой на нужное поле из реестра.
Формула для блока «Поступило в зал»
=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Месяц];A5;ПродажиЗакупки[Дата];B5;ПродажиЗакупки[Признак];"Зал";ПродажиЗакупки[Код (артикул)];'остатки по дням'!$C$3)
Из формулы видно, что из реестра «Продажи и закупки» выбираются и суммируются значения по группе признаков: количество, месяц, дата, признак, код (артикул)
'остатки по дням'!$C$3 – это название листа в книге Excel, на котором находится отчет
Формула для блока «Продано»
=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Месяц];A5;ПродажиЗакупки[Дата];B5;ПродажиЗакупки[Признак];"Продажа";ПродажиЗакупки[Код (артикул)];'остатки по дням'!$C$3)
Формула для блока «Продано» аналогична формуле из блока «Поступило в зал», только отбор идет по другому признаку: ПродажиЗакупки[Признак];"Продажа" вместо ПродажиЗакупки[Признак];"Зал" из предыдущей формулы.
В блоке «Остаток» стоит простая формула
=K4+C5-G5 (Предыдущий остаток + Поступило в зал – Продано)
Переходим к следующей части таблицы – это стоимостная часть.
Рис. 19. Отчет об остатках товара по дням (стоимостной блок)
Так как цены имеют обыкновение меняться, то в форме отчета предусмотрен столбец «Прейскурант», в котором указана дата начала действия прейскуранта. В нашем примере данные о ценах содержатся на отдельном листе в таком формате.
Рис. 20. Таблица с ценами (Прейскурант)
Цены из таблицы с ценами на отдельном листе подтягиваются при помощи функции ВПР
=ВПР($P$2;Прейскурант!$A$3:$B$4;2;0)
При переходе на новый прейскурант формулу нужно будет скорректировать, чтобы она ссылалась на 3-й столбец.
Рис. 21. Переход на новый прейскурант
Если номенклатура небольшая, то в пределах действия одного прейскуранта цены в этот отчет «Остатки товара по дням» можно заносить как значения без формул. (Ведение таблицы с ценами на отдельном листе полезно не только для отчетов, но и позволяет анализировать динамику цен.)
Формулы для расчета стоимости обычные: цена * количество.
На практике ведение отчета остатков по датам можно совмещать с отчетом «Движение товара (продажи)», который формируется на текущую дату. Во-первых, отчет на текущую дату более компактный и удобный, во-вторых, для сверки данных в двух отчетах (остаток на рис. 16 равен остатку на рис. 19).
Рис. 22. Отчет «Движение товара (продажи)»
Изменение цен включает в себя две операции: 1) переоценка остатков в зале; 2) изменение цены передачи в зал.
Например, цены изменяются с 15.02. В нашей модели цены отражаются на отдельном листе «Прейскурант» в таком формате.
Рис. 23. Прейскурант
Из рис. 24 видно, что 15.02 цена на Товар 2 изменится. Согласно отчету об остатках товаров на 14.02 в зале «Товара 2» остается 180 единиц.
Рис. 24. Остатки товаров на 14.02
Следующая строка отчета на 15.02 будет содержать уже новые цены по Товару 2.
Рис. 25. Изменение цен в отчете
На рис. 22 видно, что 15.02 поменялось название прейскуранта (столбец «Прейскурант») и цена на Товар 2, стоимость пересчитывается автоматически.
Важно!!! При изменении прейскуранта нужно проконтролировать корректность ссылок в формулах, так как в нашем случае (см. рис. 20) новый прейскурант – это новый столбец, ссылок в формулах на него просто нет.
В таблице с ценами на листе «Прейскурант» изменение цен обязательно нужно отражать в новом столбце. Если просто поменять данные в столбце «В» на 01.01.2020, то все цены в отчете изменятся на новые, что приведет к ошибкам при расчете сумм товарных остатков за предыдущие периоды.
В сокращенном формате отчета тоже нужно изменить актуальные цены.
Рис. 26. Отчет «Движение товара (продажи)» с новой ценой
Так как в реестре «Продажи и закупки» хранится вся информация о движении товаров, переоценку остатков следует отразить и там. Для этого нужно присвоить этой операции новый признак «Переоценка».
Рис. 27. Отражение в реестре «Продажи и закупка» переоценки остатков
Внимание!!! В столбце «Прейскурант» реестра «Продажи» нужно будет поменять цену. Если цена заносилось формулой ВПР, то при изменении ссылок в «умной таблице» – реестре «Продажи» могут поменяться цены за предшествующий период (так произошло у меня, но после нажатия кнопки отмены действия формула стала работать правильно).
Если не получается корректно отразить цены за предыдущий и текущий период в одном реестре, то их можно заносить не формулой, а просто значением. Или цены предыдущего периода при каждом изменении копировать и вставлять как значения, чтобы формулы ссылались только на актуальный прейскурант.
Теперь отразим передачу товара в зал по новым ценам. Следите, чтобы формулы в столбце «Прейскурант» работали правильно и подтягивали актуальную цену (напоминаю, что, если с формулами сложно, лучше заносить как значение).