Наталья Лидл - Финансовая модель предприятия в Excel

В отчете «Движение товара (продажи)» данная операция отразится следующим образом.

Рис. 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. Отчет «Движение товара (продажи)» с новой ценой

Так как в реестре «Продажи и закупки» хранится вся информация о движении товаров, переоценку остатков следует отразить и там. Для этого нужно присвоить этой операции новый признак «Переоценка».