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

передаче товаров в зал, только нужно писать признак «Продажи» и количество – фактически проданное за день.

      Рис. 5. Отражение в реестре продаж

      1.2. Как добавить динамику в реестр (умные таблицы)[1]

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

      Рис. 6

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

      1.3. Сводные отчеты по реестру «Продажи и закупки»

      Учет движения товара в форме реестра позволяет не только работать с фильтрами, но и строить на его основе большое количество аналитических таблиц.

      Вот несколько примеров сводных таблиц.

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

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

      1.4. Отчеты по движению товаров

      В реестре «Продажи и закупки» отражаются следующие движения товара: закупка от поставщика – передача в зал – продажа покупателям.

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

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

      Если весь товар, который поступил от поставщика, передан в зал с наценкой, то остаток будет равен 0.

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

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

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

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

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

      В отчете «Движение товара (закупка)» для того,

1

Реализовать динамику в отчетах по реестру можно без преобразования в «умную таблицу», а при помощи ссылок, см. п. 2.2. (при этом в сводных таблицах диапазон придется обновлять).

2

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