В предыдущей статье для анализа остатков в зале использовался такой отчет
Это отчет показывает остатки, только на текущую дату, что не всегда удобно, например, при изменении цен или при необходимости поднять информацию на определенную дату.
Для отслеживания движения и остатков товаров по дням я предлагаю еще одну форму отчета
Рассмотрим новый отчет покрупней и по подробней
Расчетный файл в Excel и консультации по этой и другим темам в учебном курсе "Финансовая модель предприятия в Excel"
Первый блок: Движение товаров
В этом блоке отражено ежедневное движение товаров только в натуральном выражении.
Теперь более подробно о формулах. Формулы ссылаются на реестр из предыдущих статей, он имеет такой формат
Начальный остаток вводится одни раз, их можно занести значениями или простой ссылкой на нужное поле из реестра.
Формула для блока "Поступило в зал"
=СУММЕСЛИМН(Таблица1[Количество];Таблица1[Месяц];A5;Таблица1[Дата];B5;Таблица1[Признак];"Зал";Таблица1[Код (артикул)];'форма 2'!$C$3)
Из формулы видно, что их реестра выбираются и суммируются значения по группе признаков: количество, месяц, дата, признак, код (артикул)
Формула для блока "Продано"
=СУММЕСЛИМН(Таблица1[Количество];Таблица1[Месяц];A5;Таблица1[Дата];B5;Таблица1[Признак];"Продажа";Таблица1[Код (артикул)];'форма 2'!$C$3)
Формула аналогичная формуле из предыдущего блока, только отбор идет по другому признаку.
В блоке "Остаток" стоит простоя формула
=K4+C5-G5 (Предыдущий остаток + Поступило в зал - Продано)
Переходим к следующей части таблицы - это стоимостная часть
Так как цены имеют обыкновение меняться, то в форме отчета предусмотрен столбец "Прейскурант" в котором указана дата начала действия прейскуранта. В моем примере данные о ценах содержатся на отдельном листе в таком формате (в следующей статье будет рассмотрен переход на новый прейскурант)
Цены из таблицы с ценами на отдельном листе подтягиваются при помощи функции ВПР
=ВПР($P$2;Прейскурант!$A$3:$B$4;2;0)
При переходе на новый прейскурант формулу нужно будет скорректировать, чтобы она ссылалась на 3-ий столбец.
В принципе, в пределах действия одного прейскуранта, цены в этот отчет можно заносить как значения без формул.
Формулы для расчета стоимости обычные: цена * количество
Теперь вернемся к "старому" формату - самой первой таблице в этом статье и попробуем преобразовать ее, т.к. у этого формата тоже есть свои плюсы, хотя бы потому, что он более компактный
В отличие от первой таблицы, в эту таблицу добавлен остаток на начало. В новом формате остаток, поступления и продажи не имею стоимостного выражения. В стоимостном выражении рассчитывается только стоимость остатка в текущих ценах.
Для начального остатка, поступлений и продаж стоимостные показатели не рассчитываются, так как при изменении цен стоимость конечного остатка в действующих ценах не будет равна по стоимости расчету (остаток на начало + поступило - продано). Обратите внимание, что итог данной таблицы (6205) равен итогу стоимостного блока отчета с остатками по датам.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке