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

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

Данные из реестра заносятся при помощи формулы СУММЕСЛИМН

=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Наименование];A6;ПродажиЗакупки[Признак];"Покупка")[2] – эта формула суммирует данные по количеству товара, приобретенного у поставщика.

Так она выглядит в диалоге «Мастера формул».

Рис. 9. Занесение формулы для ячейки В6 (из рис. 7)

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

Рис. 10. Реестр «Продажи и закупки» с добавленным столбцом «Сумма без НДС»

Учет операций в реестре позволяет легко добавлять новые столбцы, на формулы отчетов это не влияет.

Контролировать остатки товара в зале можно при помощи этого отчета.

Рис. 11. Отчет «Движение товара (продажи)»

Рис. 12. Формулы для отчета «Движение товара (продажи)»

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

На следующих рисунках будет показано, как изменения в реестре отражаются на данных отчетов по движению товаров.

Продемонстрируем ситуацию, когда товар принят от поставщика, но в зал не передан.

Рис. 13. Отражение в отчете «Движение товара (закупка)» операций по покупке без последующей передачи в зал

В столбце «Остаток» отчета появилась красная ячейка (выделение сделано при помощи условного форматирования), которая показывает, что 100 единиц товара не были переданы в зал.

После добавления в реестр передачи в зал отчет примет такой вид.

Рис. 14. Отражение в отчете «Движение товара (закупка)» операций по покупке без последующей передачи в зал

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

Рис. 15. Количество поступившего товара с кодом 102 увеличилось на 100 по сравнению с предыдущим отчетом из рис. 10

Посмотрим, как отразится в отчете продажа товара, превышающая остаток.

Рис. 16. Отражение продаж из реестра «Продажи и закупки» в отчете «Движение товара (продажи)»

1.5. Отчет по остаткам товаров в зале на конкретную дату

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

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

Рис. 17. Отчет об остатках товара по дням

Рассмотрим новый отчет более подробно по выделенным блокам.

Первый блок (синий): Движение товаров

Рис. 18. Отчет об остатках товара по дням (количественные показатели)

В этом блоке отражено ежедневное движение товаров только по количественным показателям без цены.

Теперь более подробно о формулах. Формулы ссылаются на реестр «Продажи и закупки».

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

Формула для блока «Поступило в зал»

=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Месяц];A5;ПродажиЗакупки[Дата];B5;ПродажиЗакупки[Признак];"Зал";ПродажиЗакупки[Код (артикул)];'остатки по дням'!$C$3)

Из формулы видно, что из реестра «Продажи и закупки» выбираются и суммируются значения по группе признаков: количество, месяц, дата, признак, код (артикул)

'остатки по дням'!$C$3 – это название листа в книге Excel, на котором находится отчет

Формула для блока «Продано»

=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Месяц];A5;ПродажиЗакупки[Дата];B5;ПродажиЗакупки[Признак];"Продажа";ПродажиЗакупки[Код (артикул)];'остатки по дням'!$C$3)